☰

行内の各値の vlookup の合計

tag-icon • tag-icon microsoft-excel worksheet-function spreadsheet google-spreadsheets
行内の各値の vlookup の合計

これを行う方法があるはずですが、まだ苦労しています...

==データ==

シート1

        A           B
+---------------+-------+
| Fishy_Product | Price |
+---------------+-------+
| Cod           | $9.99 |
| Trout         | $8.99 |
| Haddock       | $6.99 |
| Kippers       | $3.99 |
+---------------+-------+

シート2

       A            B         C       D        E
+------------+-------------+-----+--------+---------+
|            | Total Spend | Cod | Trout  | Haddock |
+------------+-------------+-----+--------+---------+
| Customer 1 | ?           |   1 |        |       3 |
| Customer 2 | ?           |     |      2 |         |
| Customer 3 | ?           |   2 |      2 |       2 |
+------------+-------------+-----+--------+---------+

==課題==

顧客ごとに計算する必要があります総支出シート2の「購入」とシート1の「製品/価格」を掛け合わせることで計算できます。例えば、総支出顧客 1 の場合: =(C2*vlookup(c1,'Sheet1!A:B,2,0))+(D2*vlookup(D1,'Sheet1!A:B,2,0))+(E2*vlookup(E1,'Sheet1!A:B,2,0))など

しかし新しい製品が常に Sheet1 に追加されます。

「キッパー」が列 F として追加されるとすぐに、手動でループバックしてすべての数式を更新する必要があります。(新しい製品が Sheet1 に追加されるたびに、使用されている数式により、Sheet2 の新しい列として自動的に表示されます)。個人的な怠惰はさておき、更新が必要なときに気付かず、間違った合計で作業する可能性があるという懸念があります。

計算する方法はありますか？総支出新しい項目/列が追加されるたびに数式を手動で更新する必要はありませんか?

この件について私を助けてくれる方には、事前に感謝いたします。

PS 私はGoogleスプレッドシートで作業していることを付け加えておきます

答え1

SUMPRODUCT()計算には以下を使用します:

ここに画像の説明を入力してください

式B8:

=SUMPRODUCT((A$2:A$5=C$7:E$7)*(B$2:B$5*C8:E8))

下にドラッグしますが、商品が追加されても自動的に更新されないことに注意してください。

明確に言えば、製品の順序は関係ありません。

ここに画像の説明を入力してください

結果は同じです。

答え2

データの方向を少し変更するだけで、この問題は解決できます。

：警告：

示されている方法は、Excel だけでなく Google スプレッドシートでも機能します。

ここに画像の説明を入力してください

  • セルシート2 H19 の数式と下方向への塗りつぶし:

     =IFERROR(SUMPRODUCT(VLOOKUP(Sheet1!G13,$G$19:$K$22,{3,4,5},FALSE))*Sheet1!H13,"")

注意

SUMPRODUCT に引数が 1 つだけある場合、SUM とほぼ同じように動作します。ただし、SUMPRODUCT に引数が複数ある場合は、引数の積の合計を返します。ここでは、引数が 1 つだけの場合の動作を示します。

{3,4,5}はデータ範囲$G$19:$K$22の列I、J、Kであり、調整可能です。

必要に応じて数式内のセル参照を調整します。

答え3

すべてのアイテムを収容できるように、VLookup テーブルに大きな領域を選択します。たとえば、5000 行を選択します。新しいアイテムが追加されるたびに、アイテムを含む 2 つの列 A と B を A で再度並べ替えてください。並べ替えた瞬間に正しく動作するので、問題はありません。

ありがとう。

関連情報