ちょっとヤヤコシイのですが、例えばお菓子のパック販売で、毎月どのパックがどれだけ販売されたかをカウントし、パック数毎に変化する係数は、C3は5、D3は10と列が右方向に移動するごとに5ずつ加算されL3で50になります。最後にM3に係数の合計を表示したいとき・・・
上のデータに対してM3に・・・
=(C3*1)+(D3*2)+(E3*3)+(F3*4)+(G3*5)+(H3*6)+(I3*7)+(J3*8)+(K3*9)+(L3*10)
と記述すれば答えは得られますが、もしパック数が100まであったとしたら、この記述で計算式を書くのは無骨ですね・・・
そこでSUMPRODUCTとCOLUMNを使って・・・
=SUMPRODUCT(C3:L3,COLUMN(C3:L3)-COLUMN(C3)+1)
をM3に記述すれば、仮にパック数が100まであったとしても簡単に修正できます。
SUMPRODUCT(引数1,引数2)
［引数1］計算の対象となる要素を含む最初の配列引数を指定
C3〜L3
［引数2］計算の対象となる要素を含む、2 から 255 個までの配列引数を指定
COLUMN([範囲])
COLUMN(C3:L3)C3を1としてL3を10とする
COLUMN(C3:L3)-COLUMN(C3)+1
COLUMN(C3:L3)
COLUMN関数は、そのセルの列番号を返します。
=COLUMN(C3) なら 1、
=COLUMN(D3) なら 2、
=COLUMN(E3) なら 3 です。
よって、
COLUMN(C3:L3) は、
1, 2, 3, 4, …, 10 という配列になります。
COLUMN(C3) これはA列の列番号なので、
COLUMN(C3) = 1 です。
そこで、
COLUMN(C3:L3)-COLUMN(C3) とすると、
1, 2, 3, 4, …, 10 - 1 なので、
0, 1, 2, 3, 4, …, 9 になります。
そこで、
COLUMN(C3:L3)-COLUMN(C3)+1
と最後に +1を加えると、
0, 1, 2, 3, 4, …, 9 + 1 なので、
1, 2, 3, 4, …, 10 になります。
つまり、この式は
C3:L3の各セルについて、「C列から何番目か」を取得するための式です。
=SUMPRODUCT(C3:L3,COLUMN(C3:L3)-COLUMN(C3)+1)
となっているので、内部的には、
C3 × 1 D3 × 2 E3 × 3
...
L3 × 10
を全部計算して合計しています。
したがって、
C3 = 7 D3 = 0 E3 = 3
なら、
7×1 + 2×0 + 3×3
= 7 + 0 + 9
= 16
となります。
