Navigate
X

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

＜※本稿の元記事は、『エク達』に掲載した『条件付き集計で計算も楽々！SUMIFの基本を覚える』という記事です。＞エクセルを使っていて「集計」で困ったことはないだろうか。本稿では「集計を極める」の第一歩として、SUMIF関数を使った集計テクニックの基本を確認してみたい。

※本稿の元記事は、『エク達』に掲載した『条件付き集計で計算も楽々！SUMIFの基本を覚える』という記事です。『エクセルの達人（エク達）コンテンツ移管につきまして』でもお伝えしましたが、今後、少しずつ『エク達』側のコンテンツをこちらに転記していきます。基本的にはこれから当面、毎週土日の午後は、『エク達』のコンテンツをこちらに転載していく予定です＜文体等については元記事のとおりです＞。

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

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

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

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

さっそく本題に入ろう。

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

たとえば、こんなデータ（図表１）があったとする。

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

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

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

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

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

  • 都道府県コード（数字２桁）：=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

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

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

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

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

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

これが、「sumif」関数だ。

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

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

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

図表２　数式の入力（※クリックで拡大）

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

図表３　計算結果（※クリックで拡大）

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

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

図表４　A3セルに「+A2+1」と入力（※クリックで拡大）

図表５　B3セルで「Ctrlを押しながらD」（※クリックで拡大）

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

いかがだろうか。

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

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

図表７　図表６の状態のまま「Shift+↓キー」をで範囲を選択し「Ctrl+D」（※クリックで拡大）

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

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

新宿会計士:

View Comments (12)

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

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

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

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

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

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

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

    • がみ様

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Leave a Comment
Related Post
  1. 「乗数の総和」・SUMPRODUCT関数を活用する
  2. 単にセル合計が知りたいときの「ちょっとした表示技」
  3. 複数条件で集計する「セル結合値を使ったSUMIF」