複数条件で集計する「セル結合値を使ったSUMIF」

エクセルを使用した集計における重要な技があるとしたら、それは「条件に合致する値を集計する」というものです。著者自身はエクセル評論家として、以前、『エクセルの達人』（エクタツ、現在は閉鎖済み）というウェブサイトを運営していたのですが、当該サイトで好評を博したコンテンツのひとつである「シンプルな条件付き集計」が、いよいよ当ウェブサイトに搭乗です。

『エクセルの達人』（エクタツ）とは、「エクセル評論家」でもある新宿会計士が2021年８月から10月にかけて運営していたウェブサイト。現在は閉鎖済みだが、主要なコンテンツについては現在でも当ウェブサイト『エクタツ』にて閲覧可能。

当ウェブサイ

イントロダクション

以前の『複数条件でもラクラク集計：「配列SUM関数」の威力』では、シンプルな「SUMIF関数」では対応できない、「集計する条件が複数となった場合の条件付集計法」のうちのひとつを取り上げた。

新宿会計士の政治経済評論

 

2 Pockets

2022.02.19

複数条件でもラクラク集計：「配列SUM関数」の威力

https://shinjukuacc.com/20220219-99

『エクセルの達人』（エク達）からの転載コンテンツも、そろそろ終盤に近付きつつあります。本稿はエクセルを使用した「スーパーパワフルな条件一致集計テクニック」として、「配列」を使った入力について紹介します。エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。『条件付き集計で計算も楽々！SUMIFの基本を覚える』では、「SUMIF関数」を使った...

ただ、じつはこの「複数条件での集計技」については、配列SUM関数だけでなく、もうひとつ、簡単なやり方がある。それが、「結合値SUMIF関数」だ。その威力と限界とは？

複数条件で集計することができないSUMIF関数の欠点

『条件付き集計で計算も楽々！SUMIFの基本を覚える』でも取り上げたとおり、「SUMIF」関数は、ビジネスマンにとっては「リストから条件に合致する値を選び集計してくれる」という意味では、大変に使い勝手が良い関数だ。

新宿会計士の政治経済評論

 

1 Pocket

2021.10.16

条件付き集計で計算も楽々！SUMIFの基本を覚える

https://shinjukuacc.com/20211016-99

＜※本稿の元記事は、『エク達』に掲載した『条件付き集計で計算も楽々！SUMIFの基本を覚える』という記事です。＞エクセルを使っていて「集計」で困ったことはないだろうか。本稿では「集計を極める」の第一歩として、SUMIF関数を使った集計テクニックの基本を確認してみたい。※本稿の元記事は、『エク達』に掲載した『条件付き集計で計算も楽々！SUMIFの基本を覚える』という記事です。『エクセルの達人（エク達）コンテンツ移管につきまして』でもお伝えしましたが、今後、少しずつ『エク達』側のコンテンツをこちらに転記していきま...

エクセルの初心者ならば誰でも知っているであろう一般的な「SUM」関数をちょっと応用しただけであるにも関わらず、こちらの「SUMIF」関数については、知っているだけで仕事の幅が大きく広がることは間違いない。

ただ、『複数条件でもラクラク集計：「配列SUM関数」の威力』でも取り上げたが、この「SUMIF関数」は、「１つの条件に合致するデータ」を集計するための関数であり、「複数の条件に合致するデータ」を集めることには不向きだ。

たとえば次のようなデータ（図表１。なお、シート名は「Data」とする）が存在した場合において、通常の「SUMIF」関数だと、「支店」、「月次」、「商品」などの条件でしか集計することができない。

図表１　支店別・月別・商品別売上高

配列SUM関数の威力と限界

そこで、昨日は「配列SUM関数」というテクニックを取り上げた次第だ。

ただし、この「配列SUM関数」には、いくつかの問題点がある。

１つ目は、「配列」、つまり「入力する際にCtrlとShiftを押しながらEnterを押す」という、やや複雑な操作が求められること。

２つ目は、範囲指定するときに、「元データのA列、B列」といった大雑把な指定ができず、「元データのA1からA19まで」、という具合に、細かく指定してやらなければならないこと。

そして３つ目は、いったん入力した配列を変更することが難しい、ということだ。

ほかにも、「計算式が長くなり過ぎてミスしやすい」など、さまざまな問題もあるのだが、とりあえず使い勝手がよくないことだけは間違いないだろう。

もっと簡単に、複数条件でひっかけてみる

では、もう少し簡単な方法はないだろうか。

昨日は、「複数条件でひっかけて集計するアプローチには、おおきく２つある」、「配列SUM関数はそのうちの複雑な方のやり方だ」と申し上げた。

じつは、やり方がひとつあるのだ。

「コロンブスの卵」ではないが、発想を転換すればよい。元データを、たとえば次のように加工してやれば良いのだ（図表２、図表３）。

図表２　E2セルへの数式の入力

図表３　入力後にその数式をコピー＆ペースト

つまり、「支店名」と「月」をつなげた条件指定欄をE列に作ってやるのだ。

そのうえで、昨日も作った「別シート」のD2セルに、こんな関数を入れてみよう。

=SUMIF(Data!$E:$E,A2&B2,Data!$D:$D)

この関数が意味するところは、「A2セルとB2セルの値をそのままつなげた値に合致するものを『Data』シートのE列から探し出し、それと一致した行のD列の値を合計せよ」、である。

配列SUM関数と同じ結果が得られた

では、この数式で、果たして正しく集計できるだろうか。

この数式をコピーした結果が、図表４だ。

図表４　SUMIF関数の入力結果

いかがだろうか。

昨日紹介した「配列SUM関数」と同じ計算結果が表示されていることがわかるだろう。

この「結合値を使ったSUMIF関数」のテクニックは、「配列」を使わず、複数の条件に該当するものを集計して来ることができるという意味で、非常にパワフルなものだ。

つまり、SUMIF関数をマスターしているほどのエクセル中級者であれば、自身の知識をうまく応用して、複数条件に合致する列を元シートに作ってやることで、目的を達成することができるのである。

また、計算式も、昨日紹介した以下のものと比べたら、かなりシンプルでもある。

=SUM(IF((Data!$A$1:$A$19=A2)*(Data!$C$1:$C$19=B2),Data!$D$1:$D$19))

計算ミスも生じ辛くなるなど、メリットは多い。

元データを加工しなければならない

ただし、この方法には非常に辛い欠点もある。

それは、「元データを加工しなければならない」ことだ。

ことに、大勢の人が使用している生データをそのまま使って集計しなければならないような場合、元データをいったんどこかに転記し、それをさらに集計するなどの必要性もあるかもしれないし、データも膨張してしまうし、転記過程でミスが生じる可能性も高まる。

したがって、この「結合値を使ったSUMIF」は、あくまでも個人の手元でデータを使うようなケースなどに限られてしまうのだ。

本当はMicrosoft AccessなどのＤＢソフトのPivotクエリなどを使うことができれば問題は解決するのだが、当ウェブサイトをご覧の方は、「エクセルの世界で何とか収めなければならないビジネスマン」が多いだろう。

したがって、「エクセルの実を用いて複数条件にひっかけた集計」をする場合には、昨日紹介した「配列SUM関数」、本稿で紹介した「結合値SUMIF関数」のいずれかを、場面に応じて使い分けるのが良いと考えられる。