今回は僕が分析の集計作業時に最もよく使うsumifs関数についてご紹介します。
基本的な使い方からちょっとした応用まで網羅的にまとめたので、これができれば集計作業も楽になること間違いなしです！ぜひご活用ください。
sumifs関数とは
sumifs関数とは、複数の条件に一致するセルを検索して、そのセルと同じ行・列にある数値を足しあげる関数です。近い機能を持つ関数としては、sumif関数・countifs関数・countif関数などがあります。
sumifs関数の基本的な構文はこうなります。
sumif関数との違い
集計時にsumif関数とsumifs関数のどちらを使用するか悩む方も多いのではないでしょうか。
個人的にはsumif関数ではなくsumifs関数を使うことをオススメしています。理由は指定する条件が増えた際の調整が楽だからです。
「sumifs関数はsumif関数の指定する条件を複数にしただけじゃないの？」と思いそうですが、面倒なことに若干違うのですよね。というのも引数(指定する範囲)の順番が全く同じだと、条件1つだけの時のsumifs関数と同じ記述なのでsumif関数を使用する必要がなくなるんです笑。
sumif関数の基本構文はこちら。
ご覧頂くと分かる通り、合計対象範囲と条件指定の順番がsumifs関数の時と逆になってますね。「だったらsumifs関数だけにしてしまえばいいのに」という声が聞こえてきそうですが仰る通り。なので今回は集計時に使えるsumifs関数の使い方を徹底解説したいと思います。
sumifs関数 基本の使い方
それではsumifs関数の基本の使い方を解説します。
効率的な集計方法になるように、この流れに沿って少しずつ修正を加える形でご紹介しますので、順を追って見ていって頂けたらと思います。
①sumifs関数で複数の条件指定をして集計する
まずは基本中の基本というか、もはやsumifs関数ってこのために使うんだよね？っていう機能について見ていきましょう。
今回はこのようなデータをもとにご紹介します。
なおB列には集計用に日付から年月を取得する関数を入れています。
=text(A2,“yyyy-mm”)
まずはこれを月別×案件別に集計してみます。(案件は月別に存在してるので集計にはなってませんが次のステップのためにこういう集計にしています)
今回は全て列として集計してみましたが、ざっとこのような形になります。集計したら集計の漏れや重複が起きてないか確認しておきましょう。
画像内ではI列に入っている関数を文字列でJ列に表示していますが、ここでのポイントは「列全体を指定すること」です。
エクセルではできない機能ですが、スプレッドシートの場合、列でも行でも指定したセル以降すべての範囲を取る場合は「B2:B」や「B2:2」のように指定できます。
なおsumifs関数の場合は、条件指定の範囲それぞれと、合計する範囲は同じでなければいけません。今回のように合計する範囲が「E2:E」であれば条件範囲は「A2:A」や「B2:B」のように同じである必要があるのでご注意ください。
②文字列にワイルドカードを使う
続いて、先ほどの表の集計に際して、文字列で集計していた箇所にワイルドカードを使用して部分一致で集計してみましょう。
今回は案件名を月ごとの区別をなくして案件種別で集計したい場合にどうするかを見ていきます。
案件名の末尾に月が入っているので、「*」を後方にだけつけて前方一致にしました。
③文字列指定をセル参照に変更して効率的に集計する
さて、ここまで見てきて「毎回文字列で指定するのめんどくさっ」って思いませんでしたか？
個人的には、まとめるためとはいえ1つ1つを手打ちで更新して行くのが非常にめんど臭かったのですが、そのめんど臭さを解消するために使えるのが「セル参照」という方法なのです。
これはsumifs関数だけではなくどの関数でも使える基本的なテクニックなんですが、関数内に文字列で指定しているものがあると修正・応用が利きにくかったり、事故る原因になったりするので基本的にはセル参照にすることをオススメします。
ということで、先ほどまで見ていた表をセル参照で集計して見ましょう。
先ほどは「月」と「案件名」を文字列で指定していましたが、今回はそれぞれG列・H列を参照する形に変更しました。こうすることで関数内の記載ミスでの集計ミスを防げますね！
セル参照にワイルドカードを使う
セル参照にした場合でもワイルドカードは使えますので、こちらも先ほど同様に案件名を月ごとの区別をなくして案件種別で集計したい場合にどうするかを見ていきます。
各月の案件別金額はこのようになりました。
セル参照をしているものに対してワイルドカードを使用する際は、ワイルドカードが文字列になるので、文字列をダブルクォーテーションで囲んだ上でセルと「*」を「&」で繋いであげましょう。
なお文字列同様、ワイルドカードは関数内でも文字列内でも問題ありませんので、例えば集計用関数の「H14&”*”」を「H14」にして、H14セルにある「Webコンサル支援」を「Webコンサル支援*」にしても成立します。
だいぶ綺麗になってきましたね！あとは集計関数を他セルに転用する手間を減らす一工夫をしておきましょう！
現状だと1つ目のセルに関数を入れた後に下のセルにも反映しようとドラッグすると、条件指定範囲・合計範囲のセル指定も同様に動いてしまいますよね。次のステップではこれを解消していきます。
セル指定を相対参照にして更に効率的に集計する
最後のステップでは、セル指定に対して相対参照・絶対参照を使う方法を見ていきましょう。
セル指定している箇所で一切動かさない範囲は絶対参照に、セル移動に伴って行Noや列Noを動かしたい範囲は相対参照にします。
I2セルに入っている関数をJ2セルに入れていますが、見てみると合計範囲や条件参照範囲は絶対参照に、集計用の条件が入っているセルは相対参照になっていますね。
こうすることで、I2セルを下にドラッグした場合「$」が前についている範囲は一切動きませんので、動くのは「$G2」の行Noと「$H2」の行Noのみとなります。
絶対参照というのは「どの位置であってもそこを参照せよ」という位置指定の仕方で、相対参照は「関数を入力するセルから見て●●の位置にあるセルを参照せよ」ということなんですね。
なおワイルドカードもこれまで同様に使用可能です。
sumifs関数 応用編
さて、ここまではsumifs関数の基本の使い方を見てきましたが、ここからは応用編と題して「sumifs関数使ってこういうことやれますよ」という便利な使い方をご紹介します！
日付の範囲を指定して集計してみる
期間A~Bの間のデータを取得したいというケースはままあると思います。その場合にはsumifs関数の中に条件を指定してあげましょう。
例えば、8月の日別セッション数の表から週ごとのセッション数合計を算出したい場合、以下のような関数を用いて、①期間がH3セルに入っている日付以上であり、②期間がI3セルに入っている日付以下である、という2つの条件を用いれば集計が可能です。
sumifs関数でOR条件が使える！？
sumifs関数は条件を複数指定して、それに当てはまる数値を足し上げる関数ですので、基本的に条件Aと条件Bの両方に当てはまった場合に足し上げるというAND条件での集計になります。
sumifs関数でOR条件で足し上げたい場合は、以下のようにsumifs関数を2つ繋げて足し上げましょう。
まとめ
いかがでしたか。
sumifs関数はシンプルで応用が利くのでいろんな集計方法に使えると思います。ぜひ基本をマスターしていろんな集計方法に応用して見てください！