このページで学べること｜SUMIF・COUNTIF・SUMIFS・ROWの違いと使い方
表が単純であれば、SUMやCOUNTを使えばいい。
だけど、実際の業務で使う表はそんなに単純ではないことが往々にしてありますよね。
このページでは、集計することにおいて一歩先に進んで、条件に合ったもののみを集計する方法を学びます。
この物語の案内役MISATOです。
今回は、集計に「条件」を加えた学びを実践していきましょう。
- 条件を指定して集計することができる
- 通し番号を入れて表を整えることができる
- 実務に応用することができる
それでは、まずSUMIF関数からスタートです！
【SUMIF関数の使い方】条件を指定して足し算をする
ここでは、条件にあったデータの集計をしているようです。
私は男女別に集計ができるわ
条件判定のIFの機能を持っているSUM関数さんですね。ではよろしくお願いします。
「男性」の納税額を集計するわね。
まず、「男性」という条件にあったデータを探すわ。
今回は、C2:C6の範囲から男性のみをピックアップするということですね。
そうよ。ここではH1に「男性」と入力されているので、このセルを利用させてもらうわ。
=SUMIF(C2:C6,H1 までがここで整いましたね。そして最後に集計範囲を指定する、と。
条件を指定した後に、合計したい数値データの範囲を指定するわ。
今回は、条件範囲と合計範囲を別々に指定するわよ。
=SUMIF(条件範囲,条件,合計範囲)
H2に入力した式： =SUMIF(C2:C6,H1,E2:E6)
SUMIF関数
私は条件が1つだけの時に対応可能。だから最初に条件範囲を入力してね！
でも、条件が先か、集計範囲が先かつい忘れてしまいそう…
自動で式に文字色やヒントが表示されるから、忘れたらそれを見たらいいわ。
式の色に対応して、表にも破線で色が入り対応しているので助かります！
【COUNTIF関数の使い方】条件にあったセルの個数を数える
次は、僕が男女の人数を出そうと思います。
意外とシンプルな式ですね！
範囲を選んで、条件に合うものを数えているだけですしね。
さっきも話していましたが、式も色分けされているから不安になったらそれを見てくださいね。
=COUNTIF(条件範囲,条件)
H3に入力した式 =COUNTIF(C2:C6,H1)
ちなみに、条件にしたい単語はどこかのセルに事前に入れる必要はあるのですか？
いえ、今回はH1に入力されていたので、それを利用したまでです。
式に「男性」と入力しても同じ結果が得られますよ。
ただし、文字を入れる時には “” で文字を囲むことが必要です。
H3に次の式を入力してもOK： =COUNTIF(C2:C6,“男性”)
【SUMIFS関数の使い方】複数条件の足し算をする
せっかくだから「世代別」「男女別」に集計してみない？
私ならもっと条件を増やして集計できるわよ。
わわわ。一気に式が複雑に見えてきました。一つずつお願いします。
OK！まず私は「複数」条件の合計を出す関数。
２つでも３つでも条件を重ねることができるわ。
だから、式の最初に合計範囲をまず指定してから、後に条件を重ねていく、ってわけ。
それで =SUMIFS(E2:E6 が最初なのですね。その後に条件を追加していけば良い、と。
つまり、今回は２つの条件
①C2:C6の範囲からH6と合致するもの（つまり男性）
②D2:D6の範囲からG7に合致するもの（つまり20代）のデータを集計したいという式ね。
=SUMIFS(合計範囲,条件範囲①,条件①,条件範囲②,条件②)
=SUMIFS(E2:E6,C2:C6,H6,D2:D6,G7)
または
=SUMIFS(E2:E6,C2:C6,“男性”,D2:D6,“20代”)
SUMIFさんは、条件範囲から始まる
SUMIFSさんは、合計範囲から始まるのがポイントですね！
⚠️ トラブル発生！データの追加が発生
どうやら先ほどの表に追加のデータがあるようです。
行を追加したら、No.がずれてしまいますね。
【ROW関数の使い方】通し番号を振る
通し番号で楽をしたいなら、俺に任せてくれよ！
連番大好きROW関数さん！式の解説をお願いできますか？
ここでは僕の本来の使い方とは違う、裏技的な使い方を教えるよ。
その方が俺の持ち味が活かせるんだ。
むしろ、今はこの裏技がメインの使い所になっているかな。
…と、言うと？
カッコの中には何も書かずに、その後ろに行との差を書く。
例えば、5行目に「4」と表示させたいなら、-１と書くんだ。
=ROW()基準行との差
A5に入力した式： =ROW()-1
では、6行目に4と表示させたいときは？−２ですか？
ご名答！その場合は =ROW()-2 で合ってるよ！！
本来の僕は「今いる行番号」を返す関数なんだけど、
このように連番を作る使い方が、実務ではよくされているよ。
応用集計だけでなく、ROWさんのおかげで表の整え方も学ぶことができました！
今日のところはここまでとしましょう。
《実践編》例）テスト結果集計
ここからは、みなさんの実生活にどう活用してできるのかを解説していきます。
今回は、学生さんのテスト結果を集計してみます。
以下のシートをご覧ください。
黄色で示したセルに今回学んだ関数を活かすことができます。
（L列のみ、今回紹介した関数の応用版が適用されています）
◯A列にROW関数を入力し、表を整えます
①国語の合計点： =SUMIF(C2:C9,I2,D2:D9)
または =SUMIF(C2:C9,”国語”,D2:D9)
②国語のテストの回数： =COUNTIF(C2:C9,I2)
または =COUNTIF(C2:C9,”国語”)
❸国語目標未達成回数： =COUNTIFS(C2:C9,I2,F2:F9,”×”)
または =COUNTIFS(C2:C9,”国語”,F2:F9,”×”)
※未紹介関数です
④国語目標との点数差： =SUMIFS(G2:G9,C2:C9,I2,F2:F9,”×”)
または =SUMIFS(G2:G9,C2:C9,”国語”,F2:F9,”×”)
まとめ
SUM関数は数値の合計、COUNT関数は個数を数える。
それぞれにIFが付くと、条件を加えて反映させることができます。
SUMの例で復習をします。
条件が1つの場合は、SUMIF。
数式は、最初に条件を示してから、合計したい範囲を指定します。
条件範囲→条件→合計範囲
条件が複数の場合はSUMIFS。
数式の書き方は、まず合計したい範囲/個数を数えたい範囲を指定してから、条件を追加してきます。
合計範囲→条件範囲①→条件①→条件範囲②→条件②→…
このように、IFには条件判別のお仕事が得意！
次回は、そんなIF関数が登場します。お楽しみに！！
