複数条件でもラクラク集計：「配列SUM関数」の威力
『エクセルの達人』（エク達）からの転載コンテンツも、そろそろ終盤に近付きつつあります。本稿はエクセルを使用した「スーパーパワフルな条件一致集計テクニック」として、「配列」を使った入力について紹介します。
| エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。 |
『条件付き集計で計算も楽々！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」で数式を入力する必要がある。
ただし、この数式については、元シートをいじる必要もなく、集計先シートのみで対応が可能であること、条件がいくら増えても構わないこと、といったメリットがあるので、多少入力が面倒であっても実務上は大変使い勝手が良い。
是非、試してみてほしい。
| エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。 |
本文は以上です。
日韓関係が特殊なのではなく、韓国が特殊なのだ―――。
— 新宿会計士 (@shinjukuacc) September 22, 2024
そんな日韓関係論を巡って、素晴らしい書籍が出てきた。鈴置高史氏著『韓国消滅』（https://t.co/PKOiMb9a7T）。
日韓関係問題に関心がある人だけでなく、日本人全てに読んでほしい良著。
読者コメント欄はこのあとに続きます。当ウェブサイトは読者コメントも読みごたえがありますので、ぜひ、ご一読ください。なお、現在、「ランキング」に参加しています。「知的好奇心を刺激される記事だ」と思った方はランキングバナーをクリックしてください。
ツイート @新宿会計士をフォロー読者コメント一覧
※【重要】ご注意：他サイトの文章の転載は可能な限りお控えください。
やむを得ず他サイトの文章を引用する場合、引用率（引用する文字数の元サイトの文字数に対する比率）は10％以下にしてください。著作権侵害コメントにつきましては、発見次第、削除します。
※現在、ロシア語、中国語、韓国語などによる、ウィルスサイト・ポルノサイトなどへの誘導目的のスパムコメントが激増しており、その関係で、通常の読者コメントも誤って「スパム」に判定される事例が増えています。そのようなコメントは後刻、極力手作業で修正しています。コメントを入力後、反映されない場合でも、少し待ち頂けると幸いです。
※【重要】ご注意：人格攻撃等に関するコメントは禁止です。
当ウェブサイトのポリシーのページなどに再三示していますが、基本的に第三者の人格等を攻撃するようなコメントについては書き込まないでください。今後は警告なしに削除します。なお、コメントにつきましては、これらの注意点を踏まえたうえで、ご自由になさってください。また、コメントにあたって、メールアドレス、ＵＲＬの入力は必要ありません（メールアドレスは開示されません）。ブログ、ツイッターアカウントなどをお持ちの方は、該当するＵＲＬを記載するなど、宣伝にもご活用ください。なお、原則として頂いたコメントには個別に返信いたしませんが、必ず目を通しておりますし、本文で取り上げることもございます。是非、お気軽なコメントを賜りますと幸いです。
コメントを残す
【おしらせ】人生で10冊目の出版をしました
  | 自称元徴用工問題、自称元慰安婦問題、火器管制レーダー照射、天皇陛下侮辱、旭日旗侮辱…。韓国によるわが国に対する不法行為は留まるところを知りませんが、こうしたなか、「韓国の不法行為に基づく責任を、法的・経済的・政治的に追及する手段」を真面目に考察してみました。類書のない議論をお楽しみください。 |
【おしらせ】人生で９冊目の出版をしました
 | 日本経済の姿について、客観的な数字で読んでみました。結論からいえば、日本は財政危機の状況にはありません。むしろ日本が必要としているのは大幅な減税と財政出動、そして国債の大幅な増発です。日本経済復活を考えるうえでの議論のたたき台として、ぜひとも本書をご活用賜りますと幸いです。 |
クエリ「A列とC列でグループ化して、A列、C列、D列合計値を表示せよ」に相当するエクセル関数ですね
（グループ化は手動ですが）。