2025/1/2：SUM関数（スピルVer.）&SUMPRODUCT関数

2025年1月7日 02:28

≪毎週木曜12:15～ZOOMで開催中！Excel関数お勉強会の内容です。》

あけましておめでとうございます。
新年早々の関数教室は、初の夜21:05～21:40で開催でした。
正月真っ最中だというのに、来て下さった皆様ありがとうございます✨

去年につづき、今年もゆるゆる好き勝手やっていく所存ではございますが、
どうか見放さずに、何卒今年もよろしくおねがいします°˖☆◝(⁰▿⁰)◜☆˖°

※そして早速1/9 (木) は関数教室をお休みします。ごめん。

◇関数のざっくり紹介

■ SUM関数

恐らく大半の人が最初に使う関数じゃないかな？と思われる「SUM関数」。「,」で区切った引数（値・参照・配列・範囲）をすべて「足し算」する関数。総和。
スピルするVer.（Excel2021以降）から、配列制限が1段階解放された（※華月の個人的な解釈）ことで、SUMPRODUCTに近い事ができる関数へと大幅にパワーアップした。

SUM 関数は値を追加します。個々の値、セル参照、セル範囲、またはこれらすべての組み合わせを加算できます。
=SUM(数値 1, [数値 2], ...)

SUM 関数 - Microsoft サポート

{3,4}とか書いているのは「配列定数（はいれつていすう）」といいます。
存在を知らなかった人はググってみてね！

■ SUMPRODUCT関数

その昔、まだExcelの世界がスピルしなかった時代・・・SUMPRODUCTは、CSE数式化せずにセル上で配列処理を行える数少ない関数だった・・・。

みんな大好きSUMPRODUCT！！汎用性が高く美しいというのも全くもってその通り！

画像元ネタ：「葬送のフリーレン」第1巻より
山田鐘人 (原著), アベツカサ (イラスト)

葬送のフリーレン (1) (少年サンデーコミックス) | 山田鐘人, アベツカサ |本 | 通販 | Amazon

「,」で区切った引数（値・参照・範囲）や、関数内の配列の同枠数の値を「掛け算」して、結果が配列の場合はそれをすべて「足し算」する関数。
SUM（総和）＆PRODUCT（積）。

掛けてから足す・・・記事を読んでくれている人は何か気づいたね？
そう・・・SUMPRODUCTのパワーアップ版がMMULTなのです！('-'*)
だから、MMULTの前にSUMPRODUCTは使えるようになっておいて欲しい。
（処理が脳内でイメージできるように）

※注意：配列や範囲で計算させる場合は、サイズをそろえる必要がある。
※ちなみに、マイナーだけど…ちゃんと「PRODUCT関数」も存在するよ！

SUMPRODUCT 関数は、対応する範囲または配列の積の合計を返します。既定の演算は乗算ですが、加算、減算、除算も可能です。
=SUMPRODUCT(array1, [array2], [array3], ...)

SUMPRODUCT 関数 - Microsoft サポート

サイズが揃っていないと、内部処理の時点で掛け算する相手がいないからエラーになっちゃう。

■SUM関数がSUMPRODUCT化！？

Excel2019までは、スピルが実装されておらず、基本的に配列計算を行う時はCSE数式として決定（配列制限の解除）しないと、配列の一番左上の値しかとってこなかったり、そもそもエラーを出して計算してくれなかったりしていました…(´;ω;｀)

Excel2021からは、スピル実装にあわせて配列処理が解放されたことで、セル上での配列処理が簡単に行えるようになりました。
その結果何が起こったかというと、普通のSUMの引数内でも配列同士の四則演算（足し算・掛け算など）などが行えるようになり・・・それって私の仕事奪いに来たってこと！？ぐらいの勢いで、SUMPRODUCTのような配列処理が行えるようになったのです。（結局最後足しちゃうのも同じだし）

◇ SUM・SUMPRODUCTを使った配列処理例

関数紹介で書いたような、カンマで区切るような使い方は正直そんなに使いません・・・。
なぜなら、配列を比較して「TRUE/FALSE（1/0）」にしたものを、掛け算（AND）や足し算（OR）して、結果的に条件に一致する行や列を特定させるような使い方をすることが多いからです。
（それをカウントすればCOUNTIFS、合計すればSUMIFSになる感じ）

※以下説明で、数式を長々書いていますが、お好みで「Altキー」＋「Enterキー」して、演算子のあたりで数式を改行させたり、Excel2021以降ならLET関数を使って項目ごとにまとめたりした方が見やすくていいと思います。

■基本　～これを把握すれば後は応用～

➊条件式でTRUE/FALSEを出し、1/0に変換したものを掛け合わせることで、配列内での条件を満たす対象の位置を特定する。

※TRUE/FALSEは、四則演算をしたりN関数を使えば1/0に変換できます。（お互い掛けたり足したりするならN関数も不要）

❷配列内での対象位置が分かれば、欲しい数値や連番の配列を掛けたり、
INDEX関数で文字列などを取得できる。

該当項目（数値）の値が欲しいだけなら、そのまま項目のj数値と掛け算。
行位置が欲しいなら連番（SEQUENCEが便利）、行番号が欲しいならROW（範囲）を掛ける。

▼「＊」でAND
※同じ行（横長の配列の場合は列）に
　「０」が1つでもあれば０（FALSE）/ なければ１（TRUE）

▼「＋」でOR
※同じ行（横長の配列の場合は列）に
　「１」が1つでもあれば１以上（TRUE）/ なければ０（FALSE）
※複数の項目でTRUE（1）だった場合は、足すと2以上になるので、
　条件に当てはまった場合に金額などの数値とかける場合は、
　「>0」で比較して「TRUE/FALSE」にしておくこと。

■ 複数条件に該当するものを調べる ①

▼色が「赤紫」…の『件数』と『合計金額』
=SUMPRODUCT((【色範囲】="赤紫")*1)
=SUMPRODUCT((【色範囲】="赤紫")*【金額範囲】)

▼点数が「200以上」色が「オレンジ」…の『件数』と『合計金額』
=SUMPRODUCT(
(【点数範囲】>=200)*
(【色範囲】="オレンジ"))
=SUMPRODUCT(
(【点数範囲】>=200)*
(【色範囲】="オレンジ")*
【金額範囲】)

※Excel2021以降は、（）の中身はそのまま SUMPRODUCT ➡ SUM でOK

■ 複数条件に該当するものを調べる ②

マトリックス検索（？）もなんのその！
※複数該当するときは注意が必要。（別途処理を入れて何とでもなりますが）

▼種類が「弓」ランクが「A」で「2回目」…の『攻撃力』の値
=SUMPRODUCT(
(【種類範囲】="弓")*
(【ランク範囲】="A")*
(【回数範囲（1行目）】="2回目")*
(【項目範囲（2行目）】="攻撃力")*
【データ範囲（6行×4列の部分）】)

▼種類が「剣」ランクが「A」or「B」で「1回目」…の『攻撃力』合計
=SUMPRODUCT(
(【種類範囲】="剣")*
((【ランク範囲】="A")+(【ランク範囲】="B"))*
(【回数範囲（1行目）】="1回目")*
(【項目範囲（2行目）】="攻撃力")*
【データ範囲（6行×4列の部分）】)

※「"攻撃力"」の部分を「"消費"」に変えれば、消費の値が出ます。
※Excel2021以降は、（）の中身はそのまま SUMPRODUCT ➡ SUM でOK

▼解説（サイズ違うんじゃないの？と思った人向け）

※掛け算のサイズが違うように感じるかもしれないけど、
　(縦6*横1)の配列と（縦1*横4）の配列を掛け合わせることで、縦6*横4サイズの配列になるので、
最終的には、縦6*横4サイズ同士での掛け算として処理されている。

■ 何種類のデータがあるかを調べる（ユニーク数を調べる）

▼会社名のユニーク数（種類数）
=SUMPRODUCT(1/COUNTIF(【データ範囲】,【データ範囲】))

※Excel2021以降は、（）の中身はそのまま SUMPRODUCT ➡ SUM でOK

▼解説（何でそうなるのかよくわからんと思った人向け）

⇩

※自身の分身（1/9とか）を全部（9つとか）あわせると「1」になる
　× ユニーク数（種類）分それがある
　＝合計がユニーク数として出る。

▼365の関数を使って書いた式の例

◇ SUM・SUMPRODUCT配列処理での注意点

・SUMPRODUCTで「空セル1セル」を引数とすると＃VALUE!エラーになる。➡IF関数などで、空セルの場合は0に変えてしておくと良い。

・直接的なSUM/SUMPRODUCT関数の問題ではないけれど・・・
配列を足し算でOR条件判定させると、当然結果は１より大きくなる場合があるので　そのまま掛け算すると意図しない数字になる。
➡配列処理が終わった段階で「^0」（0以外の数字は1になる）や「>0」でもして「TRUE/FALSE（1/0）」にしてから数字とかけ合わるようにする。
※ただし、０に対して「^0」するとエラーになるので注意。
※あえてエラーを利用するとき以外は「>0」が無難
（ただし結果は「TRUE/FALSE」なので、N関数か*1などを忘れずに。）

・直接的なSUM/SUMPRODUCT関数の問題ではないけれど・・・
四則計算で配列計算をする際、文字列を直接計算対象にしてしまうと、＃VALUE!エラーになる。
➡配列サイズが揃っている状態ならカンマで区切って引数を分けると良い。
※ただし、この時「SUMPRODUCT」ではなく「SUM」にしてしまっていると、引数を分けたことで単なる足し算になってしまうため、配列同士の「掛け算」（AND条件含む）したい場合は、SUMPRODUCTにした方がいい。

◇あとがき

いつか配列処理が普通となり、本来の意味でこうなる日が来る事を祈って。