Excelで配列の乗算を合計するには？

質問

【単価表】
品物　,価格
－－－,－－
トマト,100
みかん,150
りんご,200
【売上表】
売上　,１日目,２日目,・・・
－－－,－－－,－－－,・・・
トマト,　　１,　　１,
みかん,　　１,　　２,
りんご,　　１,　　１,
みかん,　　１,　　１,
トマト,　　１,　　２,
－－－,－－－,－－－,・・・
合計　,　　　,　　　,

補助列を使わずに売上表の合計を1セルで算出するにはどうしたらいいでしょうか？
単価表は変わるので、IF文などで一つ一つチェックして合計するのではなく、単価表と売上表を配列として扱って算出したいのですが。

Excelにお強い方、教えてください。
よろしくお願いします。

回答No.2

Wendy02 · ベストアンサー

こんばんは。

配列としては２次元であり、その２次元配列を使った３次元計算になるので、ワークシートの計算では、別の補助列を用いない限りは、出来ないと思います。

そこで、ユーザー定義関数を作ってみました。

以下を、標準モジュールに登録します。
(記録マクロに自動的に登録される場所）
'------------------------------------

Function DPRODUCTSUM(参照範囲 As Range, アイテム範囲 As Range, 数量範囲 As Range) As Variant
Dim SocData1() As String
Dim SocData2() As Double
Dim i As Long, j As Long, n As Long
Dim Sum As Double
Application.Volatile
On Error GoTo EndLine
ReDim SocData1(1 To 参照範囲.Rows.Count)
ReDim SocData2(1 To 参照範囲.Rows.Count)
For i = 1 To 参照範囲.Rows.Count
　SocData1(i) = 参照範囲.Cells(i, 1).Value
　If VarType(参照範囲.Cells(i, 2)) = vbDouble Then
　　SocData2(i) = 参照範囲.Cells(i, 2).Value
　End If
Next i
For j = 1 To アイテム範囲.Rows.Count
　For n = 1 To UBound(SocData1)
　　If SocData1(n) = CStr(アイテム範囲.Cells(j, 1).Value) Then
　　　 If VarType(数量範囲.Cells(j, 1)) = vbDouble Then
　　　　　　Sum = Sum + 数量範囲.Cells(j, 1).Value * SocData2(n)
　　　 End If
　　End If
　Next n
Next j
EndLine:
If Err.Number > 0 Then
　DPRODUCTSUM = CVErr(xlErrNum)
Else
DPRODUCTSUM = Sum
End If
End Function

'------------------------------------

使い方は、

=DPRODUCTSUM(参照範囲,アイテム範囲,数量範囲)

参照範囲に、

トマト,100
みかん,150
りんご,200

を入れます。Sheet2!$A$1:$B$3
(引数では、Sheet2!$A$1:$A$3としても、隣に数値がありさえすれば、値をピックアップします。）

アイテム範囲

トマト
みかん
りんご
みかん
トマト
$A$1:$A$5

数量範囲
　1
　1
　1
　1
　1
$B$1:$B$5

=DPRODUCTSUM(Sheet2!$A$1:$B$3,$A$1:$A$5,B1:B5)
のようにして使います。

なお、これは、再計算関数ですから、何も、入力しなくても、保存するときに、保存することを聞いてきます。なお、どのブックでも使いたい場合は、アドインにして、グローバル関数にしなければなりません。現在は、登録したブックのみの計算になります。

お礼

す、すごい。

この短時間で、こんなマクロを作ってしまうなんて。
確かにできました。
標準関数では実現できないのであれば、このユーザ関数は非常に役に立ちます。

どうもありがとうございます。
感謝多謝。

回答No.4

imogasi

短くしてみました。
標準モジュールにコピペ
Function kasan()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
d1 = sh2.Range("A65536").End(xlUp).Row
d2 = sh2.Range("IV2").End(xlToLeft).Column
For i = 2 To d1
For j = 2 To d2
t = t + sh2.Cells(i, j) * WorksheetFunction.VLookup(sh2.Cells(i, "A"), sh1.Range("A2:B4"), 2, False)
Next j
Next i
kasan = t
End Function
修正箇所
d1 = sh2.Range("A65536").End(xlUp).Row
のA65536のところを「合計を出す式」を入れるセルの直前の行に変えてください。
同じくd2 = sh2.Range("IV2").End(xlToLeft).Column
のIV2も調節してください。
表の範囲sh1.Range("A2:B4")のA2:B4も調整してください。
For i = 2 To d1
For j = 2 To d2
の２も調整してください。
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
のSheet1、２も実際のシート名に変えてください。
使い方は＝Kasan（）です。
質問の例では１６５０です。

お礼

参考になりました。
ただ、毎回設定を変えるよりは、多少長くなっても汎用的に使える方が便利かと。

どうもありがとうございました。

回答No.3

BLUEPIXY

>質問の通り、重複した行がある状態で実現したいのです。
同じ価格があっても、売上表に対応する価格の欄を作ってやるだけ（重複した行に対応する価格欄を作る）でよいと思います。

回答No.1

BLUEPIXY

売上表が
トマト,2
みかん,2
りんご,1
の様に集約されていないのが変だと思うのですが、
もしこのように（同じ順序で）集約されていたら
=SUMPRODUCT(単価表の価格の範囲の絶対参照,売上の個数範囲の相対参照)
例
=SUMPRODUCT($B$4:$B$6,B10:B12)
で簡単に求めることができます。

補足

早々の回答ありがとうございます。

実際の表はもう少し複雑なので、質問の通り、重複した行がある状態で実現したいのです。

実際には、
単価表はランク（ａランク、ｂランク・・・）。
売上表の品名は人とランク（ａランクの人、ｂランクの人・・・）
となっています。