[EXCEL] 補助事業の件数や金額を、補助率別に集計する（COUNTIFS/SUMIFS関数）

▶MAP▶PDF版▶表示▶関数▶ショトカ▶操作/コピー▶実務▶NG▶検索

前記事「[EXCEL] 分数の表示方法（表示の基本）」の続きです。
（補助率を分数でちゃんと入力したい場合は、こちらを先にどうぞ）

【まとめ】
・単一又は複数条件に合致するデータの数はCOUNTIFS関数で出す（COUNTIFSは使わない）。
・単一又は複数条件に合致するデータの数量の合計はSUMIFS関数で出す（SUMIFは使わない）。
・「＄」を用いて、絶対参照と相対参照を使い分け、１つの計算式をコピーすることで、他のセルにも適用させる。

【説明】
前回は、分数の表示方法について記載しましたが、この記事の前座です。

こんな表があるとします。

計算はシンプルです。
補助率×総事業費＝補助額　です（算数的には総事業費×補助率が正しいでしょうけれど、後の表との関係上、こうしています）。
総事業費は単に事業費でもいいでしょう。
ただし、補助額と混同しない（されない）表現がいいでしょう。

この表について、「補助率」ごとに件数や総事業費、補助額を集計したい、とします。

例えばこんな風に。

どうするか？

このくらいの件数なら、手計算（電卓）でも簡単でしょう（私は嫌ですが）。
でも、計算式を入れて集計することをお勧めします。
なぜなら、使い回しが利くから。
手計算（電卓）は、表の数値が変わる度にやり直しです。
例えば、総事業費が変わるとか、補助率が変わるとか。
よくある話です。
その度に全て手計算するのは非効率的です。
計算式を入れておき、計算の元となる数値が変わったら、元の表をコピーして、それを修正します。
それを「ver2」とか「R2505xx版」などのわかりやすいシート名にしてから修正します。
そうすれば元の数値が変わっても、いちいち計算しなおす必要がなくなりますし、元の数値に戻るのも簡単です。

では、計算式はどう入れるか？

COUNTIFS関数とSUMIFS関数を使った基本的な計算です。でも、できない人が結構いる印象です（私の所属する組織では）。

〇補助率別の件数を出す　COUNTIFS関数

まず、件数。
H8セル、つまり、「補助率　2/3」の件数を出すなら・・・

=COUNTIFS(C:C,G6)　です。
C列の件数を数える、ただし、Ｃ列がＧ６(2/3）のものだけ、という意味です。
COUNTIFS関数は条件に合ったもののデータの数を数えます。
COUNTIFS（数える範囲,条件）です。
範囲は、C4:C8　のように列番号＋行番号で指定もできますが、お勧めしません。
C:C　のように列番号だけで指定することをお勧めします。
なぜなら、その方が楽で間違えないからです。
詳細はこちらをどうぞ。
[EXCEL]　セルを行番号で範囲指定してはいけない（SUMIFS/COUNTIF/XLOOKUP関数等）

なお、COUNTIFS関数は複数の条件を設定できますが、条件が一つだけでも使えます。
従って、条件が一つしか設定できない　COUNTIF関数は使わずに、常にCOUNTIFS関数を使うことをお勧めします。
理由は、２つの関数を使い分けする必要がないからです。
だったら、覚える関数は少ない方がいいからです。

〇補助率別の総事業費・補助額を出す。SUMIFS関数

次に、補助率別の総事業費・補助額を出します。
これには　SUMIFS関数を使います。
SUMIFS関数は、
条件に合致したものの数値を足し上げる関数です。
=SUMIFIS（足し上げる数値,条件がある範囲,条件）で記述します。

具体的には以下の通りとなります。
I６セルの場合・・・

=SUMIFS(D:D,$C:$C,$G6)　となります。
D列（総事業費）の数値を足し上げる、ただし、C列（補助率）がG6（2/3）と同じものだけ、ということです。

以上が、条件にあったものの数値を出すSUMIFS関数です。

〇SUMIF関数は使ってはいけない

SUMIFS関数は複数の条件を設定することができますが、COUNTIFS関数と同じく、条件は１つでも構いません。
従って、条件が一つしか設定できないSUMIF関数の代わりに使うことができます。
というより、条件が一つの場合でも、SUMIF関数は使ってはいけません。

COUNTIF関数は使う必要がない、と言えますが、SUMIFS関数は使ってはいけません。
なぜなら、SUMIF関数とSUMIFS関数は記述方法が異なるためです。
２つの関数の使い分けは（頭の中の）混乱を招くからです。
詳しくはこちら
[EXCEL]使ってはいけない関数３選　①VLOOKUP、②SUMIF、③COUNTIF　初心者向けエクセル情報を鵜呑みにするなかれ

これは、エクセル本やWEBなどで語られることが少ない内容ですが、注意した方がいいところです。でないと、無断な「脳」作業（どちらを使うか、どう使うか考える）が生じます。

〇コピーするときにずれないように（絶対参照）、ずれるように（相対参照）

前述の式のうち、「$C:$C」のように「＄」が付いているものがあります。
これは、タテ（行）やヨコ（列）にコピーした際に、範囲が変わらないようにするためです（絶対参照）。
まずはC列（補助率）が「2/3」に合致する「総事業費」で集計しますが、同じ計算式を「補助額」でも使いまわせるよう、つまり「総事業費」欄のセルをコピーできるように、あらかじめ「＄」を付けておくのです。
実際は、表の一番上の I4セルに計算式を入れる際に、「＄」を付けるだけです。
「＄」はセルを選んだ後、F4を押していくことで付いたり消えたりします。F4を押す度に「＄」の付き方が変わります。
どう変わるかは、試してみてください。
４パターンを繰り返します。

「$G6」の「G」に「＄」（絶対参照）が付いているのも同じ理由です。
ただし、「６」には「＄」が付いていません（相対参照）。
これは、違う行にコピーしたときに、条件としてみる「補助率」が行によって異なるので、コピーするとずれるようにするためです。

「補助額」も同じ理屈で計算式を入れます。
というか、最初に「総事業費」のI4セルに計算式を入れれば、あとは、それを他のセルにコピーすればおしまいです。

セル毎に、あるいは「総事業費」と「補助額」ごとに計算式を入れる必要はありません（別々に作る必要がある場合もあります）。

コピーした計算式が正しいかは、「参照元のトレース」を出せば、「見える化」できます。

「参照元のトレース」は、Alt ⇒M⇒P で出ます。
詳しくはこちら
[Excel］激推し：ファイルを引き継いだらやること ⑤計算式（参照しているセル）を確認する その２「参照元のトレース」

もし、最初に計算式を入れる「総事業費」の一番上の I4セルで、
=SUMIFS(D:D,$C:$C,G4)のようにG4（補助率のセル）のGに「＄」を付けないと（相対参照）、横の「補助額」にコピーすつと、GがHに変わってしまいます。

するとどうなるか・・・

集計の条件として、G列の「補助率」ではなく、「件数」のＨ列を見てしまうので、正しく集計できません。
このくらいの表であれば、誤りに気付きやすいのですが、データ量が多くなると、ミスに気付かない場合があります。
ミスしていないかの確認方法はいろいろ考えられますが、一番手っ取り早いのは、前述の「参照元のトレース」です。
ただし、この参照元のトレースは。別記事でも触れているとおり、参照元が別シートだと、どこを参照しているかがわかりません。
とはいえ、同一シート内での参照元はすぐにわかりますので、私は、計算式を入れたり、コピーするたびに、Alt⇒M⇒Pでトレースを出して確認しています。この流れ打ち（順番内）は、ほぼ無意識でできています。

参照元のトレースは　Alt⇒M⇒A⇒A　で消えます。ファイルの保存でも消えます。



以上、参考になれば幸いです。

もし何か「発見」があったら、「スキ」「フォロー」「チップ」していただける嬉しいです！

（作業　２日　２Ｈ）