条件付き集計で計算も楽々!SUMIFの基本を覚える

エクセルを使っていて「集計」で困ったことはないだろうか。本稿では「集計を極める」の第一歩として、SUMIF関数を使った集計テクニックの基本を確認してみたい。

エクセルを使っていて、数字の合計を出すには「sum関数」を使う。

こんなことは、エクセルを少しでも使ったことがある人からすれば、当たり前のことだと思うだろう。

しかし、仕事を効率化する上では、「sum関数」だけでは十分ではない。

「集計を極める」シリーズでは、この「集計」に関するテクニックの数々を紹介していきたい。

さっそく本題に入ろう。

仕事をしていると、こんな悩みに直面したことはないだろうか。

たとえば、こんなデータ(図表1)があったとする。

図表1 新型コロナウイルス感染症用のワクチン接種実績データ(※クリックで拡大)

これは、政府CIOポータルサイト『新型コロナワクチンの接種状況(一般接種(高齢者含む))概要』のページで手に入る「ワクチン接種記録システム(VRS)」の生データを加工したものだ。

VRS生データのダウンロード方法
  • 次の文字列をウェブブラウザのURL欄に打ち込むと、その時点の最新データが取得可能
  • <code> https://vrs-data.cio.go.jp/vaccination/opendata/latest/prefecture.ndjson</code>
  • 上記文字列のうちの「latest」以降の部分を「{dt}/prefecture.ndjson」(※)に変えると過去データの入手が可能(※なお、{dt}は「yyyy-mm-dd」形式で日付を入力。たとえば「2021年7月11日時点のデータ」なら、{dt}の部分を「2021-07-11」に変換)

「ndjson」という見慣れない形式のデータだが、ワードパッドやエクセルなどで開いていただいて問題ない。Windowsの場合は「プログラムから開く」でエクセルなどを指定してやれば開ける。

そのうえで、各データセルに対し、次の関数を入れてやれば、該当するデータを取り出すことが可能だ。

  • 都道府県コード(数字2桁):=MID(セル,36,2)
  • 男女:=MID(セル,50,1)
  • 年齢:=MID(セル,60,3)
  • 回数:=MID(セル,97,1)
  • 接種数:=MID(セル,FIND(“count”,セル)+7,FIND(“}”,セル)-FIND(“count”,セル)-7)-0
  • 接種日:=MID(セル,10,10)-0

これが、原始的ではあるが、最も簡単なやり方だろう(ほかにもやり方はいくつかあり、機会があれば説明するかもしれないが、本稿では深入りしない)。

では、このVRSデータ、どうやって加工するのが良いか。

データの構成要素は、「都道府県コード」、「男女の別」、「年齢(『65歳以上』、『64歳以下』、『不明』の3区分)」、「回数(『1回目』、『2回目』の2区分)」であり、これに「接種数」と「接種日」が記載されている。

本稿ではまず、「単一の条件式での集計」のやり方について考えてみたい。

ここでは「日付別の接種回数」を、ひとつの関数によって一発で集計する方法を紹介する。

これが、「sumif」関数だ。

基本的な書式は、つぎのとおり。

SUMIF(範囲、検索条件、合計範囲)

元のシートにデータ数が非常に多いため、ここでは別シートを挿入し(豆知識:マウスを使わなくても、キーボード上だと、「Shift+F11」で簡単に挿入できる)、「Sheet1」のG列を「範囲」に、「Sheet2」のA2セルを「検索条件」に、「Sheet1」のF列を「合計範囲」に、それぞれ指定してみよう(図表2)。

図表2 数式の入力(※クリックで拡大)

そして、「エンターキー」を叩くと、こんな具合に数値が計算できた(図表3)。

図表3 計算結果(※クリックで拡大)

この「sumif」が優れているのは、いちいち元シートの日付欄を手作業で並べ替えたりしなくても、条件に合致するデータを自動で引っ張ってくれたうえで合計してくれる点にある。本当に便利なのだ。

事例は一般高齢者向けのワクチン接種が始まった2021年4月12日を起点にしているが、ためしにA3セルに「+A2+1」と入力し(図表4)、B3セルでは「Ctrl+D」を押してみよう(図表5)。

図表4 A3セルに「+A2+1」と入力(※クリックで拡大)

図表5 B3セルで「Ctrlを押しながらD」(※クリックで拡大)

※ちなみに「Ctrl+D」は「上のセルの内容をそのまま下にコピーする」、である。

いかがだろうか。

あとはコピーしてペーストするだけで(あるいは「Ctrl+D」を押すだけで)、計算式をどんどんとコピーすることができる(図表6図表7)。

図表6 A4セルを選択し、「Shift+Space」で行選択のうえ「Ctrl+D」(※クリックで拡大)

図表7 図表6の状態のまま「Shift+↓キー」をで範囲を選択し「Ctrl+D」(※クリックで拡大)

あとは必要な日数に達するまで同じ作業をしていただければよい。

以上、本稿では「集計を極める」のごく基本の部分をお話した次第である。