SUMPRODUCTを覚えた日から、Excelの「作業列」が一本もいらなくなった
「金額」という名前の列を、私はこれまで何百本、いや何千本つくってきただろう。
単価と数量が並んだ表があって、売上の合計を出したい。
やることは決まっている。まず横に空いた列を用意して、=単価×数量 を1行ぶん書く。
あとは下までコピー。最後にその列を SUM で足す。これで合計が出る。
長いことこれが当たり前だった。誰に習ったわけでもなく、自然とそうなっていた。
でも、この「金額」列は、本当は一度も要らなかった。
計算のためだけに一本増やして、行が増えるたびにコピー漏れを心配して、印刷のときは邪魔だから隠して。
そういう手間を、私はずっと自分で発明して、自分で背負っていた。
種明かしをすると、単価×数量 を掛けてから足す、という処理は、作業列ゼロ・たった1つのセルで終わる。
使う関数は SUMPRODUCT。名前だけは、たぶん見たことがあると思う。
ただ、多くの人はこれを「単価×数量の合計を出す関数」だと思っている。
私も長いあいだそうだった。それは、この関数の力の、たぶん1割も使っていない。
SUMPRODUCT の本当の凄みは、掛けて足すだけではない。
「東日本の、商品Aだけ」といった条件つきの集計。
「AまたはC」というOR条件。「数量が40以上の件数」というカウント。
行と列の交点を一気に埋めるクロス集計。テストの配点を効かせた加重平均。
これが全部、同じ1つの関数で書ける。しかも考え方は最後までひとつだけだ。
受託でよそ様の台帳を触っていると、この関数を知らないばかりに、作業列とオートフィルタと電卓で半日かけている現場によく出くわす。
答えは合っている。でも、かけている時間がもったいない。
まずは、私が長年やっていた「作業列だらけ」の表を見てほしい。
E列の「金額」が、まさにあの作業列だ。
これでも売上合計240,000円はちゃんと出る。出るのだが、列を一本増やしている。
そして厄介なのはこの先だ。「東日本のA商品だけ合計して」と言われた瞬間、この作り方では手が止まる。
作業列を足して、オートフィルタをかけて、目で拾って、電卓を叩く。ここで数字がずれる。
その全部を、SUMPRODUCT は1セルで肩代わりする。
ここから先は、掛けて足すの基本から、条件つき集計・クロス集計・加重平均まで、実際にExcelが返した本物の数字と数式でひとつずつ見せていく。
読み終わるころには、あなたの表からも「金額」という名前の作業列が消えているはずだ。
まず基本。SUMPRODUCTは「掛けて、足す」
最初に、いちばん素直な使い方から。
SUMPRODUCT は、指定した範囲どうしを同じ位置で掛け算して、その全部を足す関数だ。
書き方はこれだけ。
=SUMPRODUCT(単価の範囲, 数量の範囲)
さっきの表なら、こう書く。
=SUMPRODUCT(C4:C11, D4:D11)
C4×D4、C5×D5……と1行ずつ掛けて、その合計を返す。
つまり「金額」の作業列を作ってから SUM していたのと、まったく同じことを、1セルでやる。
右の表を見てほしい。SUMPRODUCT も、作業列に =C*D を書いて SUM した方も、どちらも240,000円だ。
答えは同じ。違うのは、SUMPRODUCT は列を一本も増やしていないこと。
そして、この「掛けて足す」は売上に限った話ではない。
「重み×点数」で加重平均を出すときも、「面積×単価」で見積もりを積むときも、構造はまったく同じだ。
掛けてから足す、が出てきたら、まず SUMPRODUCT を思い出せばいい。
ここまでは、正直まだ「便利な時短」でしかない。
この関数が化けるのは、次の章からだ。
種明かし。なぜ「条件」が書けるのか
SUMPRODUCT が条件つき集計に化ける理由は、Excelのちょっとした性質にある。
セルに =(A4="東日本") と書くと、Excelは TRUE か FALSE を返す。
ここまでは知っている人も多いと思う。問題はこの先だ。
この TRUE / FALSE を計算に混ぜると、自動で 1 と 0 に化ける。
TRUE×1 は 1、FALSE×1 は 0。Excelがそう決めている。
だから、条件に1を掛けると「当たったら1、外れたら0」の列ができる。
その 1 か 0 を、金額に掛けたらどうなるか。
当たった行の金額はそのまま残り、外れた行の金額は 0×金額=0 で消える。
残った金額だけを足せば、それは「条件に当たった行の合計」だ。
言葉だと回りくどいので、実際に列を並べて見せる。
C列が =(地域="東日本") の結果。TRUE と FALSE が並んでいる。
D列はそれに ×1 しただけ。ちゃんと 1 と 0 になっている。
E列は =(地域="東日本")×金額。東日本の行だけ金額が残り、それ以外は0円だ。
このE列を上から足すと126,000円。これが東日本の売上合計になる。
そして、この一連の流れをまるごと1式にしたのが、いちばん下のこれだ。
=SUMPRODUCT((地域="東日本")*金額)
答えは同じ126,000円。E列を作らずに、頭の中の「当たった行だけ残して足す」を1セルでやっている。
×1 は仕組みを見せるために置いただけで、金額に直接掛けるなら省いていい。
ちなみに、人が書いた SUMPRODUCT を見ると、--(地域="東日本") のようにマイナスが2つ並んでいることがある。
これも ×1 と同じで、TRUE/FALSE を 1/0 に変えるための昔ながらの書き方だ。
マイナスを2回かけると符号が元に戻る。負の負は正だから、TRUE が1、FALSE が0になる。
×1 でも -- でも結果は同じ。人の式を読めるようにだけしておけば、書くときはどちらでもいい。
この「条件を書くと1/0になって、掛けると効く」という感覚さえ掴めば、あとは応用するだけだ。
条件を重ねる。掛け算が「AND」になる
条件を2つ以上にしたいときは、条件どうしを掛け算(*)でつなぐ。
これがそのまま「AND（かつ）」になる。
理由はさっきと同じで、両方とも当たった行だけ 1×1=1 が残り、片方でも外れると 1×0=0 で消えるからだ。
「東日本の、商品Aだけ」の売上ならこう書く。
=SUMPRODUCT((地域="東日本")*(商品="A")*金額)
条件のカッコを掛けて、最後に金額を掛ける。それだけだ。
東日本かつ商品Aは54,000円。西日本かつ商品Bは60,000円。
条件は掛け算でいくつでも重ねられる。3つでも4つでも、* でつなぐだけ。
「同じことは SUMIFS でもできる」と思った人は鋭い。実際その通りだ。
単純なAND集計なら、SUMIFS の方が読みやすいことも多い。
ただ、SUMPRODUCT の強みは、この掛け算の考え方が、この先のOR条件・カウント・クロス集計まで一直線につながっていることにある。
道具をひとつ覚えれば、そこから全部が地続きになる。SUMIFS ではここまで伸びない。
現場でこの形が効く場面は、思い出すときりがない。
「渋谷店の、7月だけ」の売上。「担当が田中で、カテゴリが備品」の合計。
条件が2つ3つと重なるほど、オートフィルタと電卓の出番が増えて、そのぶん数字がずれていく。
その全部を、条件を * でつないだ1式に置き換えられる。手作業が減れば、事故も減る。
ひとつ注意。文字の条件は完全一致で判定される。
前後に空白が混じっていたり、全角と半角がゆれていたりすると、Excelは別物として扱う。
条件で集計する前に、名寄せで表記をそろえておくのが安全だ。
「AまたはB」と「件数を数える」
次はOR条件。「AまたはC」のように、どちらかに当たれば拾いたいとき。
このときは、条件どうしを足し算(+)でつなぐ。
=SUMPRODUCT(((商品="A")+(商品="C"))*金額)
カッコの中で条件を足すと、どちらかが当たった行が1になる。それを金額に掛けて足す。
件数を数えたいときは、金額の代わりに ×1 を掛ければいい。
金額を足す代わりに「1」を足していくので、当たった行の数がそのまま出る。
商品AまたはCの売上は148,000円。数量が40以上の件数は4件。
さらに「東日本で、数量が40以上」のように、不等号と別列の条件を混ぜたカウントも一発だ。
=SUMPRODUCT((地域="東日本")*(数量>=40))
これで2件。COUNTIFS でも書けるが、条件が増えるほど SUMPRODUCT の方が素直に書けることが多い。
ひとつだけ落とし穴がある。OR条件を + で作るとき、同じ行が両方の条件を満たすと、そこが 1+1=2 になる。
金額を足すぶんには倍にならないよう気をつける必要があるし、件数だと二重に数えてしまう。
だからORでつなぐ条件は、商品AとC のように互いに重ならない値で使うのが基本だ。
OR条件が活きるのは、たとえば「電話またはメールで来た問い合わせ」の件数。
複数の拠点をまとめて「本店または支店A」で合算するときも同じだ。
別々に SUMIF を書いて、あとで足し合わせる。あの二度手間をやらずに済む。
クロス集計を、たった1つの式で
ここが SUMPRODUCT のいちばん気持ちいいところだと思う。
行に地域、列に商品を並べた集計表。あの「交点を全部埋める」やつを、1つの式のコピーだけで作れる。
交点のマスに入れる式はこうだ。
=SUMPRODUCT((地域列=行の見出し)*(商品列=列の見出し)*金額列)
行の見出しと列の見出しを掛けて、金額に掛ける。AND条件そのものだ。
コツは、見出しのセルを絶対参照にしておくこと。
行見出しは列を固定して $G4、列見出しは行を固定して H$3。
こうすれば、左上のマスに書いた1式を、表全体にコピーするだけで全交点が埋まる。
東日本のAは54,000、西日本のBは60,000。右端が地域計、下が商品計。
そして右下の総合計は240,000円。冒頭の売上合計とぴったり一致する。
この「総計が元の合計と合う」というのは、ただの結果ではなく検算になる。
どこかのマスの条件を書き間違えていれば、総計がずれて気づける。
ピボットテーブルでも同じ表は作れる。ただ、ピボットは元データを更新したら「更新」を押す必要がある。
SUMPRODUCT で組んだクロス表は数式だから、明細を直した瞬間に勝手に追従する。
貼って渡す資料や、毎月使い回すテンプレートには、この「勝手に追従する」が効く。
月×商品カテゴリ、担当者×ステータス、店舗×曜日。
見たい切り口が「行と列」で表せるなら、この形がそのまま使える。
見出しのセルを差し替えれば、別の切り口の表も、式のコピーだけで作れる。
加重平均。「数の多い方に引っぱられた平均」
最後は加重平均。名前は難しそうだが、やっていることは掛けて足して、割るだけだ。
=SUMPRODUCT(値, 重み)/SUM(重み)
値に重みを掛けて足し、最後に重みの合計で割る。これで「重みを効かせた平均」が出る。
なぜこれが要るのか。単純な平均だと、実態からずれることがあるからだ。
左を見てほしい。単価をただ平均すると875円になる。
でも実際には、安い商品がたくさん売れている。販売数で重みをつけた平均単価は750円だ。
「1個あたりいくらで売れたのか」を知りたいなら、正しいのは加重の750円のほうだ。
右は成績の例。小テスト20%、中間30%、期末50%という配点で、点数はそれぞれ80・70・90点。
単純平均なら80.0点。でも配点50%の期末が90点だから、加重平均は82.0点になる。
通知表の評定に近いのは、当然この82.0点のほうだ。
ひとつだけ、絶対に忘れてはいけないことがある。最後の /SUM(重み) だ。
これを忘れると、ただ「掛けて足しただけの大きな数」が出てしまう。平均にならない。
割るところまでがワンセット、と覚えておけばいい。
使いどころは、思ったより広い。
アンケートの5段階評価を、回答人数で重みづけした平均満足度。
同じ商品を違う値段で仕入れたときの、平均仕入単価。
「数が多い方に引っぱられた、実態に近い平均」が欲しいときは、だいたいこれで足りる。
もう一歩。知っていると効く小技
掛けて足す、という土台が分かると、そこから少し伸ばせる。
ひとつは、掛ける範囲は2つに限らないこと。
=SUMPRODUCT(数量, 単価, 為替レート)
このように3つ以上の範囲を、同じ位置で掛けて足すこともできる。
数量×単価×為替、のような「三段掛け」の合計が、やはり1セルで出る。
もうひとつ。条件は文字だけでなく、日付や数値の範囲でも書ける。
(日付>=期間の始まり)*(日付<=期間の終わり)
この形を条件に挟めば、期間で絞った集計になる。
始まりと終わりを別のセルに置いておけば、そのセルを変えるだけで対象の期間が切り替わる。
ここまで来ると、SUMPRODUCT は「集計の下書き用紙」のようになってくる。
やりたいことを、条件の掛け算と足し算で書き下す。たいてい、それで1式に収まる。
つまずきやすい所を、先につぶしておく
便利な関数ほど、静かにハマる所がある。先に言っておく。
まず、掛け合わせる範囲は行数をそろえること。
C4:C11 と D4:D10 のように長さが違うと、うまく計算されなかったりエラーになる。始まりと終わりの行を必ず合わせる。
次に、さっきも触れたが、文字の条件は完全一致だ。
見た目が同じでも、全角空白や余分なスペースが混じっていれば別物になる。集計の前に整えておく。
そして、列全体を巻き込みすぎないこと。
A:A のように列まるごとを何本も掛けると、何十万行ぶんを毎回計算することになって重くなる。
必要な範囲だけを指すか、テーブル化して範囲を自動で伸ばすのが行儀がいい。
もうひとつ、掛け合わせる範囲に文字や空白が混じっていると #VALUE! になることがある。
金額の列に「-」や「未定」といった文字が入っていると、掛け算でつまずくのだ。
集計に使う列は数値だけにそろえておく。空欄は0にしておくと安全だ。
最後に、SUMIFS との使い分けについて。
単純なAND集計だけなら、SUMIFS の方が読みやすいことも多い。無理に SUMPRODUCT で書く必要はない。
SUMPRODUCT を選ぶのは、OR条件・複雑なカウント・クロス集計・加重平均のように、SUMIFS では素直に書けない場面だ。
道具は使い分ければいい。ただ、その引き出しを持っているかどうかで、詰まる場面の数がまるで変わる。
おわりに──「掛けて足す」がひとつ言えれば十分
長く書いてきたが、この記事で言いたかったことは、実はひとつしかない。
「掛けて足す」は、SUMPRODUCT で1セルにできる。
条件つきも、ORも、カウントも、クロスも、加重平均も、全部その延長線上にある。
TRUE/FALSE が 1/0 になって、掛けると効く。この感覚さえ掴めば、あとは組み合わせるだけだ。
私はこれを知ってから、「金額」という名前の作業列を作らなくなった。
表がひとつぶんスッキリして、条件が増えても慌てなくなった。
今日の売上表から、まずは作業列を一本、消してみてほしい。
最後に、この記事で使った表をそのまま配っている。
掛けて足す基本・AND条件・OR条件とカウント・クロス集計・加重平均 の式が全部入った練習ブックだ。
数字を自分のデータに差し替えれば、そのまま自分の集計に使える。
下のLINEに登録して、「掛け算」 とひとこと送ってほしい。ダウンロードリンクを自動でお返しする。
Excel・関数・自動化の「これ手作業でやってた……」を減らすネタを、LINEでも配っている。
気が向いたら、覗いてみてほしい。
