優先順位をつけたエクセルスプレッドシートの平均

Question

優先順位をつけて合計する Excel 式 (または任意の Excel ソリューション) の書き方について助けが必要です。

次のような取引データがあります:

Date  Unit  Price  Buy/Sell 
7/11   3     10     Buy 
7/12   2      9     Sell
7/13   4      8     Buy 
7/14   3     11     Sell

購入したすべてのユニットではなく、手持ちのユニットの平均購入価格を計算します。手元に 2 ユニットあり、FIFO を思い出してください。したがって、平均は 8 ドルになります。

10 ドルで 3 個、8 ドルで 4 個購入し、最初の 5 個が販売されました。

Excel でこの問題を解決するアイデアはありますか?

Answer 1

ワークシート関数を使用してこれを行う簡単な方法はありません。時間の経過とともに変化する任意の長さの購入価格のリストを追跡する必要があるためです。

ユニット数が正の整数であるという仮定の下で、ヘルパー列を使用してこれを実行できます。データが列 AD にあり、ヘルパー列が E であるとします。

E2に =IF(D2="Buy",E1&REPT(C2&",",B2),MID(E1,1+FIND("*",SUBSTITUTE(E1,",","*",B2)),32767))

=AVERAGE()そして、最後のセルには、以下のいずれかの方法を使用して数式に変換する必要があるテキスト文字列が含まれます。https://stackoverflow.com/questions/4471884/文字列式を実際の式に変換する

残念ながら、セルの長さの制限である 32768 文字に達する可能性があります。