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

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

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

SUMIF関数を振り返る

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

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

たとえば、こんなデータがあったとしよう（図表１）。

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

この図表は、東京、大阪、名古屋の各支店について、１月から３月までのＡ商品、Ｂ商品の２つの商品の売上高を一覧にしたものだ（なお、シート名は「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)

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

図表２　集計結果

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

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

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

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

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

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

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

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

 

図表３　Ctrl+Shift+Enter

実際にやってみよう。

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

まず用意するのはこんなシートだ（図表４）。

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

あらかじめ、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」で入力しなければならない。

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

図表５　配列の入力結果

あとは、この数式をそのまま下のセルにコピー＆ペーストしてやれば良い（図表６）。

図表６　コピー＆ペースト

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

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

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

=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」で数式を入力する必要がある。

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

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

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