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

『集計を極める』シリーズでは、エクセルを使用したさまざまな「マニアック集計テクニック」を取り扱っています。SUMIF関数をうまく使うと、複数の条件に合致する集計も可能です。本稿ではこれらのうちのひとつである『複数の条件に合致する数値を集計する基本的テクニック』という記事を転載しておきます。

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

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

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

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

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

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

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

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

このファイルを開いていただければわかるが、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セルをタテヨコにコピー)

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

読者コメント一覧

  1. チキンサラダ より:

    なるほど。これは賢いですね。
    別の手段として、sumifs は如何でしょうか?

    1. ななっしー より:

      記事タイトル通りのことができる SUMIFS なんて関数もあるのですね。
      図表9を再現できました(グーグルスプレッドシートで)。
      それはそれとして
      「論理積がなければ文字列結合すればいい」
      は SUM 以外にも応用が効きそう。

  2. 七味 より:

    元データと集計用とシートを2枚使うなら、e列は集計用シートに書くのが好みなのです♪

  3. 匿名 より:

    こんにちは。
    今回の記事を読んでいてふと超初心者時代のことを思い出しました。
    sumifと言う(当時)素晴らしく画期的な関数を覚えたての頃の話です。ちなみにExcelは独学で学んでいました。
    そこそこ長文&スマホよりの入力で見えづらいかもしれませんがご容赦ください。

    今回の記事の例で言えば図表7の部分の
    =sumif(Sheet1!$E:$E,Sheet3!$A2&Sheet3!B$1,Sheet1!$D:$D)
    を当時の私は
    =sumif(Sheet1!E:E,Sheet3!A2&Sheet3!B1,Sheet1!D:D)
    と入力していました。

    要するに絶対参照のことを全く知らなかったのですね。
    そんな私でしたので、オートフィルを使って数式をコピーしたら参照がズレることにイライラしながらズレた部分を修正していました。
    また、ズレることが当然と認識していたのでしばらくは都度修正のやり方でやってたんですが(それでも単純にsumifのことを知らなかった頃より知ってからのことが作業の時間が大幅に短縮できていたので満足でした。)、ある日誰かのファイルを見たときに数式に入っていたこの「$」はなんだと疑問に思いつつも、いつもどおりオートフィルで数式をコピーしたら自分が思い描いている理想のコピーが出来上がっているではありませんか。
    それで気になって調べてみたら絶対参照の事が判明した次第です。

    それからは条件付きの集計の関数を使う際には必ず絶対参照の有無を思考に入れるようにはなったのですが、周りを見回すと必要に応じて使っているだけの方は結構この事を知らないのですよね。
    私は作業者なので同じ部署の人間は一人も知らないですし、案外事務所の方も知らないものです。

    今回の記事は主にsumifのちょっと応用した使い方の記事であり若干趣旨から外れますが、懐かしさを覚えたので失敗談をコメントさせて頂きました。

  4. netuser より:

    皆さん既にご存じかも知れませんが、関数をコピーして使うときなどにf4キーがとても便利です。
    関数の中のセル番地(例えばA2)にカーソルを合わせてf4キーを押すと
    A2→$A$2→A$2→$A2→A2
    と順次切り替わります。

    とても重宝してます。

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

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

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

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

コメントを残す

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