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

『エクセルの達人』(エク達)からの転載コンテンツも、そろそろ終盤に近付きつつあります。本稿はエクセルを使用した「スーパーパワフルな条件一致集計テクニック」として、「配列」を使った入力について紹介します。

エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

条件付き集計で計算も楽々!SUMIFの基本を覚える』では、「SUMIF関数」を使った集計術を紹介した。ただ、このSUMIFだと、集計する条件が複数になってきたときに対応できないというデメリットがある。これについて解決策は大きく2つあるが、本稿ではそのうちのひとつである「配列SUM関数」について紹介したい。

SUMIF関数を振り返る

以前の『条件付き集計で計算も楽々!SUMIFの基本を覚える』などでは、「SUMIF」関数を使った集計術について紹介した。

元データからその条件に合致する値のみを引っ張り、それを集計してくれるという、大変便利な関数である。

たとえば、こんなデータがあったとしよう(図表1)。

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

この図表は、東京、大阪、名古屋の各支店について、1月から3月までのA商品、B商品の2つの商品の売上高を一覧にしたものだ(なお、シート名は「Data」だったとする)。

SUMIF関数を使えば、たとえば支店ごと、月次ごと、商品ごとに売上高を簡単に集計することができる。

SUMIFの難点は「集計条件がひとつしか指定できないこと」

ここで、SUMIFの書式は次のとおりだ。

=SUMIF(範囲,検索条件,合計範囲)

「Data」シートのA列が支店名、B列が月、C列が商品名であるので、たとえば別シートを立ち上げ、支店ごとに集計したければ①、月次ごとに集計したければ②、商品ごとに集計したければ③の数式を入力してやればよい。

  • ①支店ごと:=SUMIF(Data!A:A,Sheet2!A2,Data!D:D)
  • ②月次ごと:=SUMIF(Data!B:B,Sheet2!A2,Data!D:D)
  • ③商品ごと:=SUMIF(Data!C:C,Sheet2!A2,Data!D:D)

実際に計算した結果が、図表2だ。

図表2 集計結果

ただ、この集計には、ひとつの難点がある。

たとえば、「東京支店における毎月の売上高の合計」、「大阪支店における商品別の売上高の合計」、「商品ごと毎月の売上高の合計」、といった、「複数の条件に合致する計算」ができないことだ。

配列SUM関数で複数条件をひっかける

これには、解決方法は2つある。

ひとつは、元シートの集計行を加工してしまう、という方法、もうひとつは「配列」を使う方法だ。

本日はこのうち、難しい方の「配列」を使った集計方法を説明してみる。

配列については、以前の『タテヨコ変換の基本形②配列数式を利用した自動的入替』で紹介した「TRANSPOSE関数」でも使用した。

具体的には、「Ctrl+Shift+Enter」で入力するという関数で(図表3)、入力に成功すればセル内で数式が{}で括られる。

 

図表3 Ctrl+Shift+Enter

実際にやってみよう。

本稿では支店ごと・商品ごとという2つの条件で集計してみたい。

まず用意するのはこんなシートだ(図表4)。

図表4 複数条件での集計用のシート

あらかじめ、A列に支店名、B列に商品名を入力してある。

そのうえで、C2セルに、こんな式を入力してみよう(ただし、まだエンターは押さないでほしい)。

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

この数式の意味は、「A2セルの内容と『Data』シートのA1からA19セルまで(※絶対参照)の範囲に合致」、かつ「B2セルの内容と『Data』シートのC1からC19まで(※絶対参照)の範囲に合致」する行のD列の数値を合算せよ、というものだ。

配列の入力の独自性に注意!

ただ、このままエンターを押しても数値は正しく計算されない。かならず、「Shift+Enter」で入力しなければならない。

もし数式の入力に成功したら、前後に{}が自動的に入力されるはずだ(図表5)。

図表5 配列の入力結果

あとは、この数式をそのまま下のセルにコピー&ペーストしてやれば良い(図表6)。

図表6 コピー&ペースト

今回の数式は参照する条件を2つに設定したが、べつに3つでも4つでも構わない。また、条件式については「イコール」でなくても「不等号」などであっても構わない。

ただし、この数式には、注意点がいくつかある。

まず、カッコの書き方が独特だ。カッコを付す場所と個数については、慣れていてもよく間違える。

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

次に、「Data!$A$1:$A$19=A2」のなど部分、横着して「Data!$A:$A=A2」、などと書いてしまうと、正確に集計してくれない。あくまでも列名称だけでなく、具体的なセルを指定してやらないといけないのだ。大変に使い勝手が悪い。

さらには、いちど入力した配列は崩すのが難しい。うっかり間違えて、全範囲を指定して「Ctrl+Shift」で数式を入力してしまうと、修正が困難だ。

したがって、面倒でも慣れるまでは、ひとつひとつ、「Ctrl+Shift」で数式を入力する必要がある。

ただし、この数式については、元シートをいじる必要もなく、集計先シートのみで対応が可能であること、条件がいくら増えても構わないこと、といったメリットがあるので、多少入力が面倒であっても実務上は大変使い勝手が良い。

是非、試してみてほしい。

エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

読者コメント一覧

  1. ななっしー より:

    クエリ「A列とC列でグループ化して、A列、C列、D列合計値を表示せよ」に相当するエクセル関数ですね
    (グループ化は手動ですが)。

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

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

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

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

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

コメントを残す

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