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

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

SUMIF関数を振り返る

以前の『条件付き集計で計算も楽々!SUMIFの基本を覚える』などでは、「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」で数式を入力する必要がある。

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

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