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

<※本稿の元記事は、『エク達』に掲載した『条件付き集計で計算も楽々!SUMIFの基本を覚える』という記事です。>エクセルを使っていて「集計」で困ったことはないだろうか。本稿では「集計を極める」の第一歩として、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」(※クリックで拡大)

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

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

読者コメント一覧

  1. TDa より:

    XXXif関数は日頃使う場面も多いし、countifとかにも応用が効いて最初に取り上げるのにいい題材ですね。

    あと思ったのがCtrl-Dって意外と知らない人が多いんですよね。知ってるとめちゃめちゃ使うんですけど。
    他にはCtrl-Y(直前の動作繰り返し)とかCtrl-;(日付の挿入)あたりが知ってると便利なやつかなぁ。

  2. 七味 より:

    最近はエクセルよりgoogleノスプレッドシートを使うことが多いけど、エクセルの豆知識はスプレッドシートでも使えることが多くて重宝するのです♪

    sumifも便利だけど、sumは配列も引数にできるから、filterを使って条件に会う配列を作ってsumに食わせたりしてるのです♪
    ・・・10月15日の65歳以上の接種人数みたく、いくつか条件を設定するときに便利なのです♪

    1. 新宿会計士@ekutatu より:

      https://ekutatu.com/ では配列sumについても議論していますのでご参照ください。いずれ当ウェブサイトにも転記すると思います。

  3. がみ より:

    エクセルは便利で優れたツール(私が使いこなせてるとは申しません。普段使いの領域だけの知識しかないし…)なんですが、パワーポイントで会議やってる会社ってアホ多くありません?

    派手で華やかで仕事やってる感満載なんですが、内容がないプレゼンが多い気がするのは私だけなんでしょうか?

    よくよく考えると説得された事が少ないような気がする…

    1. 七味 より:

      がみ様

      エクセルもパワポもワードも、ただのツールだから目的に応じて使い分ければいいと思うのです♪

      ただ会議って、複数の関係者で意見を出しあったり、何かを決める場だから、エクセルってあんまし向かない気がするのです♪
      エクセルって、細かい数字の管理とかには向くけど、会議の場で欲しいのは、考え方の方向性とか、現状とか将来予測を把握できる資料であって、そのバックデータじゃないと思うのです♪
      まぁ、そのバックデータが信用できなくて、「元データを見せて♪」みたいなこともあるけど・・・・

    2. 匿名29号 より:

      パワーポイントは基本的に、言いたいことを箇条書きにするだけのツールだと考えています。ただ、それに加えて図表を加えたり、フォントを変えたり装飾を施す機能が満載なので、ともすれば装飾だけで内容が無いものでも一見もっともらくなります。
      要はプレゼンを受ける側がそれを見抜けるか眼力を試されるツールとも言えますね。
      しかし上司の内容のない長々としたプレゼンに対して、あくびを噛み殺しながら聞いている振りを強制されるツールでもあります。そういうのは大抵の場合、結論は何なのかはっきりしないか、「当たり前」の凡庸なものなので、さっさとトイレ休憩に立ちましょう。

  4. 匿名 より:

    会社の上司(課長)が毎日忙しいと言いつつ副資材を計算機で計算し、合計を発注書のエクセルに手入力して完成させるのを毎日絶望的に眺めてます。ちなみにデータは会社のシステムからエクセルないしcsv形式で引っ張って来れますので、それこそsumifがわかればすごく楽なんですけどね。
    もちろん勉強を勧めましたが、余裕がない・忙しいとのことでした。余裕あっても勉強してませんがね。勉強するほうが精神的に苦痛なのでしょう。

    1. 新宿会計士@ekutatu より:

      勉強することで失われる数時間と、勉強しないことで余分に掛かる数千時間を比べないんですね。

  5. だんな より:

    ピポットテーブルと何が違うのかわからん。

  6. 七味 より:

    エクセルは便利だけど、便利すぎて・・・・・・
    庶務系のお仕事で、誰かが作ったのが代々受け継がられてるのがあったのです♪
    複雑怪奇な処理をsumifだけじゃなくて、vlookupなんかも使って、それはそれは便利だったのです♪
    あるときある人が出てくる結果になんかおかしなとこがあるのに気付いたのです♪

    エクセルでの計算を、いくつかのステップにわけて、手計算で確認してくと、途中から結果が間違ってることが判明したのです♪
    更にセルに埋められてる式を追いかけていくと、参照してるセルがずれていることがわかったのです♪

    どうやら、長い年月使ってるうちに、セルとか行の削除や追加してるうちに、参照先がずれてしまってたみたいなのです♪

    あまり使わないとこで、しかも0が基本だから、気づくのが遅れたんだねってことになったけど、いつ頃からずれていたのか、全くわかんなかったのです♪

    そして、そのファイルは封印されて、新たにファイルが作られたのです♪
    多分、封印されたファイルからシートをコピーして流用したりせずに、いちから作り直してたと思うけど・・・・・・

    その後のことはわかんないけど、同じ過ちが繰り返されないことを祈ってるのです♪

    教訓 エラートラップは大切だけど、やりすぎるとエラーそのものに気がつけなくなるのです♪

    1. だんな より:

      七味さま
      祈っても駄目です。
      Computerized System Validation(CSV)をして、元ファイル使用法の手順を決める事をお勧めします。

  7. ななっしー より:

    「芸術的な」エクセルブック、ありますねえ。
    そのくせセルのロックも、入力セルを太枠にしてもいなかったり。
    すると、入力するアルバイトやレポートを見る上司が操作をまちがえるとすぐにファイルがこわれる。
    入力、データ、計算、レポートをひとまとめに出来るのが表計算のメリットだけど、保守性/相互運用性を考えると別々の方がいい。
    のだけどそうすると表計算の必然性が「見慣れたグリッドレイアウト」だけになっちゃうのよねえw
    やっぱり個人で完結する、あるいは同程度のスキルを持つ人の集団内で完結する作業向きのツールですね。

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

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

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

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

コメントを残す

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