複数の商品の合計金額を計算する関数を教えてください。

エクセル2013を使用しています。

Ｘさんは商品Ａを1個、商品Ｃを2個
Ｙさんは商品Ｂを2個、商品Ｃを1個
　　商品Ａ　商品Ｂ　商品Ｃ　合計金額
Ｘ　　１　　　０　　　２　　　？
Ｙ　　０　　　２　　　１　　　？

Ｘさんの合計金額セル(E2)に
=IF(ISNUMBER(B2),B2*100,"0")+IF(ISNUMBER(C2),C2*150,"0")+IF(ISNUMBER(D2),D2*200,"0")
としたのですが、単価が変わるたびにすべての数字を変更しなければいけません。
そこで「商品マスタ」みたいなシートを用意しました。
商品名　単価
商品Ａ　１００
商品Ｂ　１５０
商品Ｃ　２００

この時の合計金額を計算する関数って有るのでしょうか？
商品マスタから取ってくるのは「VLOOKUP」で、ＩＦ関数と複合となるとは思うのですが・・・
よろしくお願いいたします。

No.1 ベストアンサー 回答者： tom04 回答日時： 2016/08/08 20:51

こんばんは！

今回の質問の場合は3つだけの条件なので関数でやってみました。
↓の画像のような配置だとするとE2セルに
=VLOOKUP(B$1,G:H,2,0)*B2+VLOOKUP(C$1,G:H,2,0)*C2+VLOOKUP(D$1,G:H,2,0)*D2

という数式を入れフィルハンドルで下へコピーしています。

※　今後商品数がもっと増える場合、関数でやると
商品数だけVLOOKUP関数の足し算になりダラダラと長い数式になってしまいますので、
別案として「ユーザー定義関数」（この質問に関する関数）を作ってみてはどうでしょうか？

Alt＋F11キー　→　メニュー　→　挿入　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面をとじて）
通常のワークシート関数のような使い方をします。

Function goukei(myRng As Range, myArea As Range) 'この行から//
Dim c As Range, r As Range, myVal As Variant
For Each c In myRng
Set r = myArea.Find(what:=Cells(1, c.Column), LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then
myVal = myVal + c * r.Offset(, 1)
End If
Next c
goukei = myVal
End Function 'この行まで//

以上の下準備ができた状態で
E2セルを選択　→　数式バーの左の「fx」をクリック　→　「関数の分類」の右側▼をクリック　→　「ユーザー定義」を選択
先ほどのユーザー定義関数が選択されているはずですので「OK」
「Myrng」の窓で画像配置だとB2～D2を選択
「myArea」の窓でG・H列を選択（列すべてを選択しても構いません）
これで数式バーの表示が
=goukei(B2:D2,G:H)
となり、結果が表示されているはずです。
あとはフィルハンドルで下へコピーしてみてください。

※　商品名は必ず1行目にある！という前提のコードです。
これで商品数が極端に増えても同じ数式で対応できるはずです。m(_ _)m

この回答へのお礼

アドバイスありがとうございます。
思い通りのことが出来ました。＼(^o^)／
VBAは少しかじったのですが、ユーザー定義関数は未知の部分でした。
まだまだ勉強不足ですね(^^;)
ありがとうございましたm(__)m

お礼日時：2016/08/09 17:23

No.2 回答者： tom04 回答日時： 2016/08/08 21:08

No.1です。

前回のVBAのコードの操作で一部説明が抜けていました。

＞Alt＋F11キー　→　メニュー　→　挿入　→　VBE画面のカーソルが・・・
は
＞Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが・・・
です。

どうも失礼しました。m(_ _)m

No.3 回答者： usa3usa 回答日時： 2016/08/09 06:09

私ならば、

商品A 商品B 商品C 合計金額
単価 100 150 200
X 1 0 2 100 0 400 500
Y 0 2 1 0 300 200 500

のように、単価の行、小計の列を追加して合計金額を求めます。
小計と合計の項の式は、
　=B$2*B3　＝C$2*C3　=D$2*D3 =SUM(E3:G3)
　=B$2*B4　＝C$2*C4　=D$2*D4 =SUM(E4:G4)
となります。
単価が変わった場合、追加した単価の行の値を変えるだけで再計算してくれます。

参考まで。

この回答へのお礼

アドバイスありがとうございます。
一覧の表として見た時に小計がうるさくなりそうで他の方法を考えていました。(^^;)
ありがとうございましたm(__)m

お礼日時：2016/08/09 17:11