複数の条件に合致する数値を集計する基本的テクニック
『集計を極める』シリーズでは、エクセルを使用したさまざまな「マニアック集計テクニック」を取り扱っています。SUMIF関数をうまく使うと、複数の条件に合致する集計も可能です。本稿ではこれらのうちのひとつである『複数の条件に合致する数値を集計する基本的テクニック』という記事を転載しておきます。
| エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。 |
『集計を極める』シリーズでは、エクセルを使ったさまざまな「マニアック集計テクニック」を紹介していく。本稿では以前の『条件付き集計で計算も楽々！SUMIFの基本を覚える』で紹介した「SUMIF関数」の応用技をひとつ紹介したい。
『集計を極める』シリーズでは、エクセルを使ったさまざまな「マニアック集計テクニック」を紹介していく。
さて、『条件付き集計で計算も楽々！SUMIFの基本を覚える』や『データを数える「COUNTIF」関数の基本を覚える』では、SUMIF関数とCOUNTIF関数について取り上げた。
ただ、ビジネスマンであれば、もっと高度な集計をしなければならないケースも多々あるだろう。
その際に、とくにビジネス上、必要とされるスキルがあるとすれば、「複数の条件で集計するテクニック」だろう。
ここで、本日はこんなサンプルを準備した。
※数値は架空のものである。また、ウィルスチェック等は終えているので、安心して開いていただいて構わない。
このファイルを開いていただければわかるが、Sheet1のA列には「ワクチン接種日」、B列には「都道府県」が、C列には「性別」が、D列には「ワクチン接種回数」が、それぞれ入力されている。
ここでは、「SUMIF」関数を使い、Sheet1のB列（図表１）を手掛かりに、Sheet2に集計作業をしてみよう（図表２。ただし、先ほどのサンプルファイルでは、すでに計算式はSheet2に入力済みである）。
図表１ Sheet1のB列（都道府県）
図表２ Sheet2に集計する
計算式は、こうだ。
=SUMIF(Sheet1!B:B,Sheet2!A2,Sheet1!D:D)
すると、こんな具合に集計が出来上がる（図表３）。
図表３ 計算結果（Sheet2）
すなわち、「４月12日と13日の２日間におけるワクチン接種総数を、東京都、神奈川県、大阪府、京都府、北海道の５都道府県の別に集計する」、という作業が、これで出来上がった。
具体的には、東京都が41回、神奈川県が264回、大阪府が15回、京都府が104回、北海道が18回、といった具合だ（※ただし、数値は架空のデタラメなものであり、現実のワクチン接種実績ではない）。
しかし、ビジネスマンであれば、「もっと複雑な集計をやってみたい」と思われるだろう。
たとえば、「４月12日、13日の両日における、男女別の集計」をやってみたいと思う人もいるかもしれない。これについてサンプルファイルのSheet3に入力している（図表４）。
図表４ 日付別・男女別集計（Sheet3）
その集計テクニック、最もやりやすいのは、Sheet1のE列あたりに、「日付」のA列、と「男女」のC列をつなげたデータを作ってしまうことだ（図表５、図表６。ただし、先ほどのサンプルファイルでは、すでに計算式はSheet2に入力済みである）。
図表５ Sheet1のE列に数式を入力
図表６ 入力結果（Sheet1）
すると、E列に、「44298男性」「44298女性」「44299男性」「44299女性」などの数値が出現した。
この「44298」、「44299」は、エクセル上の日付のシリアル値だ。見た目は違和感があるかもしれないが、計算上は何の問題もなく、エクセルが日付として認識してくれる。
そして、Sheet3に戻ろう。
B2セルに、こんな計算式を入力してやる（図表７。ただし、先ほどのサンプルファイルでは、すでに計算式はSheet2に入力済みである）。
=sumif(Sheet1!$E:$E,Sheet3!$A2&Sheet3!B$1,Sheet1!$D:$D)
図表７ 計算式の入力（Sheet3）
ここで、SUMIF関数を思い出していただきたい。
SUMIFの書式は、「範囲、検索条件、合計範囲」だった。
ここで「範囲」には、先ほどの図表５～６で求めた、Sheet3のE列を指定している。「$」を付けて絶対参照にしている理由は、Sheet3の図表が複数の行と列にまたがっているため、そのまま計算式をコピー＆ペーストして範囲がズレてしまうことを防ぐためだ。
次に、「検索条件」がひとつのポイントであろう。
というのも、Sheet3のA2セル（「2021/04/21」という日付、シリアル値でいえば「44299」）とB1セル（「男性」という値）を「&」で結合しているからだ。この部分は、エクセル的には「『44299男性』という値」を意味している。
そのうえで、入力するに際しては、日付（A2）セルについては「A」を不変、「2」を可変とするため、数式は「$A2」としており、また、性別（B1）セルについては「B」を可変、「1」を不変とするため、数式は「B$1」としている点にも注意を払ってほしい。
最後に「合計範囲」は、図表２～３と同じく、Sheet1のD列だが、これも絶対参照としている。
すなわち、このB2セルに入力した
=sumif(Sheet1!$E:$E,Sheet3!$A2&Sheet3!B$1,Sheet1!$D:$D)
という計算式は、
- Sheet1のE列を見に行き、
- A2&B1、すなわち「44299男性」に合致する値を探し出し、
- それに対応するD列（接種数）を集計せよ
という指示語なのだ。
その結果、次の計算結果が出てくる（図表８）。タテヨコにコピーすれば、図表９のとおりだ。
図表８ 計算結果（Sheet3のB2セル）
図表9 計算結果（Sheet3のB2セルをタテヨコにコピー）
これらのテクニック、是非とも業務にお役立ていただきたいと思う次第である。
本文は以上です。
日韓関係が特殊なのではなく、韓国が特殊なのだ―――。
— 新宿会計士 (@shinjukuacc) September 22, 2024
そんな日韓関係論を巡って、素晴らしい書籍が出てきた。鈴置高史氏著『韓国消滅』（https://t.co/PKOiMb9a7T）。
日韓関係問題に関心がある人だけでなく、日本人全てに読んでほしい良著。
読者コメント欄はこのあとに続きます。当ウェブサイトは読者コメントも読みごたえがありますので、ぜひ、ご一読ください。なお、現在、「ランキング」に参加しています。「知的好奇心を刺激される記事だ」と思った方はランキングバナーをクリックしてください。
ツイート @新宿会計士をフォロー読者コメント一覧
※【重要】ご注意：他サイトの文章の転載は可能な限りお控えください。
やむを得ず他サイトの文章を引用する場合、引用率（引用する文字数の元サイトの文字数に対する比率）は10％以下にしてください。著作権侵害コメントにつきましては、発見次第、削除します。
※現在、ロシア語、中国語、韓国語などによる、ウィルスサイト・ポルノサイトなどへの誘導目的のスパムコメントが激増しており、その関係で、通常の読者コメントも誤って「スパム」に判定される事例が増えています。そのようなコメントは後刻、極力手作業で修正しています。コメントを入力後、反映されない場合でも、少し待ち頂けると幸いです。
※【重要】ご注意：人格攻撃等に関するコメントは禁止です。
当ウェブサイトのポリシーのページなどに再三示していますが、基本的に第三者の人格等を攻撃するようなコメントについては書き込まないでください。今後は警告なしに削除します。なお、コメントにつきましては、これらの注意点を踏まえたうえで、ご自由になさってください。また、コメントにあたって、メールアドレス、ＵＲＬの入力は必要ありません（メールアドレスは開示されません）。ブログ、ツイッターアカウントなどをお持ちの方は、該当するＵＲＬを記載するなど、宣伝にもご活用ください。なお、原則として頂いたコメントには個別に返信いたしませんが、必ず目を通しておりますし、本文で取り上げることもございます。是非、お気軽なコメントを賜りますと幸いです。
コメントを残す
【おしらせ】人生で10冊目の出版をしました
  | 自称元徴用工問題、自称元慰安婦問題、火器管制レーダー照射、天皇陛下侮辱、旭日旗侮辱…。韓国によるわが国に対する不法行為は留まるところを知りませんが、こうしたなか、「韓国の不法行為に基づく責任を、法的・経済的・政治的に追及する手段」を真面目に考察してみました。類書のない議論をお楽しみください。 |
【おしらせ】人生で９冊目の出版をしました
 | 日本経済の姿について、客観的な数字で読んでみました。結論からいえば、日本は財政危機の状況にはありません。むしろ日本が必要としているのは大幅な減税と財政出動、そして国債の大幅な増発です。日本経済復活を考えるうえでの議論のたたき台として、ぜひとも本書をご活用賜りますと幸いです。 |
なるほど。これは賢いですね。
別の手段として、sumifs は如何でしょうか？
記事タイトル通りのことができる SUMIFS なんて関数もあるのですね。
図表9を再現できました（グーグルスプレッドシートで）。
それはそれとして
「論理積がなければ文字列結合すればいい」
は SUM 以外にも応用が効きそう。
元データと集計用とシートを2枚使うなら、e列は集計用シートに書くのが好みなのです♪
こんにちは。
今回の記事を読んでいてふと超初心者時代のことを思い出しました。
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のちょっと応用した使い方の記事であり若干趣旨から外れますが、懐かしさを覚えたので失敗談をコメントさせて頂きました。
皆さん既にご存じかも知れませんが、関数をコピーして使うときなどにｆ４キーがとても便利です。
関数の中のセル番地（例えばA2）にカーソルを合わせてｆ４キーを押すと
A2→$A$2→A$2→$A2→A2
と順次切り替わります。
とても重宝してます。