複数の条件に合致する数値を集計する基本的テクニック

『集計を極める』シリーズでは、エクセルを使ったさまざまな「マニアック集計テクニック」を紹介していく。本稿では以前の『条件付き集計で計算も楽々!SUMIFの基本を覚える』で紹介した「SUMIF関数」の応用技をひとつ紹介したい。

『集計を極める』シリーズでは、エクセルを使ったさまざまな「マニアック集計テクニック」を紹介していく。

さて、『条件付き集計で計算も楽々!SUMIFの基本を覚える』や『データを数える「COUNTIF」関数の基本を覚える』では、SUMIF関数とCOUNTIF関数について取り上げた。

データを数える「COUNTIF」関数の基本を覚える

本シリーズ『集計を極める』では、エクセルを使用していて集計するテクニックについて、いくつか取り上げていきたいと思っている。本稿で取り上げるのは、「COUNTIF関数」…

MID関数の極意「長さが変わる文字列」の切り出し方

本稿では『文字列操作を極める』シリーズの第1回として、LEFT関数、MID関数を使った初歩的な文字列操作「切り出しテクニック」を紹介したいと思う。

ただ、ビジネスマンであれば、もっと高度な集計をしなければならないケースも多々あるだろう。

その際に、とくにビジネス上、必要とされるスキルがあるとすれば、「複数の条件で集計するテクニック」だろう。

ここで、本日はこんなサンプルを準備した。

サンプル:「ワクチン接種実績」

※数値は架空のものである。また、ウィルスチェック等は終えているので、安心して開いていただいて構わない。

このファイルを開いていただければわかるが、Sheet1のA列には「ワクチン接種日」、B列には「都道府県」が、C列には「性別」が、D列には「ワクチン接種回数」が、それぞれ入力されている。

ここでは、「SUMIF」関数を使い、Sheet1のB列(図表1)を手掛かりに、Sheet2に集計作業をしてみよう(図表2。ただし、先ほどのサンプルファイルでは、すでに計算式はSheet2に入力済みである)。

図表1 Sheet1のB列(都道府県)

図表2 Sheet2に集計する

計算式は、こうだ。

=SUMIF(Sheet1!B:B,Sheet2!A2,Sheet1!D:D)

すると、こんな具合に集計が出来上がる(図表3)。

図表3 計算結果(Sheet2)

すなわち、「4月12日と13日の2日間におけるワクチン接種総数を、東京都、神奈川県、大阪府、京都府、北海道の5都道府県の別に集計する」、という作業が、これで出来上がった。

具体的には、東京都が41回、神奈川県が264回、大阪府が15回、京都府が104回、北海道が18回、といった具合だ(※ただし、数値は架空のデタラメなものであり、現実のワクチン接種実績ではない)。

しかし、ビジネスマンであれば、「もっと複雑な集計をやってみたい」と思われるだろう。

たとえば、「4月12日、13日の両日における、男女別の集計」をやってみたいと思う人もいるかもしれない。これについてサンプルファイルのSheet3に入力している(図表4)。

図表4 日付別・男女別集計(Sheet3)

その集計テクニック、最もやりやすいのは、Sheet1のE列あたりに、「日付」のA列、と「男女」のC列をつなげたデータを作ってしまうことだ(図表5図表6。ただし、先ほどのサンプルファイルでは、すでに計算式はSheet2に入力済みである)。

図表5 Sheet1のE列に数式を入力

図表6 入力結果(Sheet1)

すると、E列に、「44298男性」「44298女性」「44299男性」「44299女性」などの数値が出現した。

この「44298」、「44299」は、エクセル上の日付のシリアル値だ。見た目は違和感があるかもしれないが、計算上は何の問題もなく、エクセルが日付として認識してくれる。

そして、Sheet3に戻ろう。

B2セルに、こんな計算式を入力してやる(図表7。ただし、先ほどのサンプルファイルでは、すでに計算式はSheet2に入力済みである)。

=sumif(Sheet1!$E:$E,Sheet3!$A2&Sheet3!B$1,Sheet1!$D:$D)

図表7 計算式の入力(Sheet3)

ここで、SUMIF関数を思い出していただきたい。

SUMIFの書式は、「範囲、検索条件、合計範囲」だった。

ここで「範囲」には、先ほどの図表5~6で求めた、Sheet3のE列を指定している。「$」を付けて絶対参照にしている理由は、Sheet3の図表が複数の行と列にまたがっているため、そのまま計算式をコピー&ペーストして範囲がズレてしまうことを防ぐためだ。

次に、「検索条件」がひとつのポイントであろう。

というのも、Sheet3のA2セル(「2021/04/21」という日付、シリアル値でいえば「44299」)とB1セル(「男性」という値)を「&」で結合しているからだ。この部分は、エクセル的には「『44299男性』という値」を意味している。

そのうえで、入力するに際しては、日付(A2)セルについては「A」を不変、「2」を可変とするため、数式は「$A2」としており、また、性別(B1)セルについては「B」を可変、「1」を不変とするため、数式は「B$1」としている点にも注意を払ってほしい。

最後に「合計範囲」は、図表2~3と同じく、Sheet1のD列だが、これも絶対参照としている。

すなわち、このB2セルに入力した

=sumif(Sheet1!$E:$E,Sheet3!$A2&Sheet3!B$1,Sheet1!$D:$D)

という計算式は、

  • Sheet1のE列を見に行き、
  • A2&B1、すなわち「44299男性」に合致する値を探し出し、
  • それに対応するD列(接種数)を集計せよ

という指示語なのだ。

その結果、次の計算結果が出てくる(図表8)。タテヨコにコピーすれば、図表9のとおりだ。

図表8 計算結果(Sheet3のB2セル)

図表9 計算結果(Sheet3のB2セルをタテヨコにコピー)

これらのテクニック、是非とも業務にお役立ていただきたいと思う次第である。