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

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

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

当ウェブサイ

イントロダクション

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

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

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

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

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

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

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

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

配列SUM関数の威力と限界

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

図表4 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などのDBソフトのPivotクエリなどを使うことができれば問題は解決するのだが、当ウェブサイトをご覧の方は、「エクセルの世界で何とか収めなければならないビジネスマン」が多いだろう。

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

読者コメント一覧

  1. 矢塚 より:

    複数条件の合計には「SUMIFS関数」を使っていますが、データ側の加工は盲点でした。
    式のシンプル化による、作成速度向上やミスの低減が期待できる場面がありそうです。

    勉強になりました。ありがとうございます。

  2. ほたか より:

    このような場合、自分ならSUMIFS関数を使うと思います。
    =SUMIFS(C4,C1,”東京支店”,C3,”A商品”)
    といった感じ(ワタシR1C1形式じゃないと数式が書けない…)でやってます。

※【重要】ご注意:他サイトの文章の転載は可能な限りお控えください。

やむを得ず他サイトの文章を引用する場合、引用率(引用する文字数の元サイトの文字数に対する比率)は10%以下にしてください。著作権侵害コメントにつきましては、発見次第、削除します。

※現在、ロシア語、中国語、韓国語などによる、ウィルスサイト・ポルノサイトなどへの誘導目的のスパムコメントが激増しており、その関係で、通常の読者コメントも誤って「スパム」に判定される事例が増えています。そのようなコメントは後刻、極力手作業で修正しています。コメントを入力後、反映されない場合でも、少し待ち頂けると幸いです。

※【重要】ご注意:人格攻撃等に関するコメントは禁止です。

当ウェブサイトのポリシーのページなどに再三示していますが、基本的に第三者の人格等を攻撃するようなコメントについては書き込まないでください。今後は警告なしに削除します。なお、コメントにつきましては、これらの注意点を踏まえたうえで、ご自由になさってください。また、コメントにあたって、メールアドレス、URLの入力は必要ありません(メールアドレスは開示されません)。ブログ、ツイッターアカウントなどをお持ちの方は、該当するURLを記載するなど、宣伝にもご活用ください。なお、原則として頂いたコメントには個別に返信いたしませんが、必ず目を通しておりますし、本文で取り上げることもございます。是非、お気軽なコメントを賜りますと幸いです。

コメントを残す

メールアドレスが公開されることはありません。