中継列を省いてスッキリ : SUMPRODUCT と 配列数式

行ごとにいったん値を算出してその全行分を集計するような表構成はよくあります。そのような値を中継する列を設けることなくシンプルに作表できる方法を2つ紹介します。SUMPRODUCT関数と、配列数式です。

SUMPRODUCT関数で算出する

売上(売価×個数)を合算する

SUMPRODUCT関数の基本形の例です。

=SUMPRODUCT(C2:C6*D2:D6)

シンプルな数式ですが、(505×10)+(450×30)+(100×4)+… と 範囲内の全行分を計算した値を求められます。

公式な構文は =SUMPRODUCT(C2:C6,D2:D6) のように「,」で区切るのですが、「*」でも機能します。演算種別が分かりやすいよう「*」がお勧めです。

利益(売価と原価の差×個数)を合算する

SUMPRODUCT関数の拡張形です。3列以上で乗算以外の四則演算を使った例です。

=SUMPRODUCT((C2:C6-B2:B6)*D2:D6)

((505-300)*10)+((450-400)*30)+((100-90)*4)+… と 範囲内の全行分を計算した値を求められます。

配列数式で算出する

配列数式を使うと、SUM関数でもSUMPRODUCT関数と同じことが実現できます。

売上(売価×個数)を合算する

=SUM(C2:C6*D2:D6)

利益(売価と原価の差×個数)を合算する

=SUM((C2:C6-B2:B6)*D2:D6)
新旧Excelの配列数式
旧版のExcelでは、上記数式の確定時にShift + Ctrl + Enter を押す固有の操作が必要です。この操作はSCEと呼ばれています。
Microsoft 365のExcelでは、Spill(動的配列数式)機能により、Enterキーでの通常確定で配列数式として扱われるようになってます。SCEで確定すると旧版と同様に {=SUM(C2:C6*D2:D6)} のような数式になりますが、再選択時には =SUM(C2:C6*D2:D6) に戻ります。

 

タイトルとURLをコピーしました