[EXCEL] 集計入門　クロス集計、どうやる？ピボットテーブルか？ COUNTIFSか？SUMPRODUCT関数か？　その２　COUNTIFS編（おすすめ）

お役所のエクセル EXCELでつまずかない！公務員の実務で必須の操作をまとめて解決

2025年7月17日 21:21

【まとめ】
・COUNTIFS関数を使うとクロス集計ができる（他の関数でもできる）。
・「～以上」なら　">="＆セル番地、
　「～未満」なら、"＜"&セル番地、
と記号を””で囲み＆でセル番地とつなぐ
・集計ミスがあることを想定して、全体の件数と突合する数式も作っておく

【説明】

以下のデータ表。
職員（従業員）の通勤方法と所要時間の一覧です。

このデータ表から、以下の集計表を作ります。

前記事ではピボットテーブルで挑戦してみましたが、うまくいきませんでした。

今回は、COUNTIFS関数を使ってやってみます。

よろしければ、下のファイルをダウンロードして、実際に手を動かしながらやってみましょう。
見ているだけだとよくわかりません。
理解するなら、それが一番です。

COUNTIFS関数で「徒歩」の人数を出す

まず、集計表の左上スミ、「徒歩」の欄。
とりあえず、「徒歩」の件数（人数）を出します。

以下の数式を入れます。
=COUNTIFS(C:C,F5)

=COUNTIFS(C:C,F5)　は
「通勤方法」が入っているC列に、F5の「徒歩」がいくつあるか調べる数式です。

ただし、これだと、判るのは「徒歩」の件数（人数）だけ。
「徒歩」の全件数（全人数）である「３」が入ってしまっています。
所要時間は、まだ反映されていません。

「所要時間」で集計してみる

上の数式に、所要時間の条件を追加します。
ただし、追加する条件が複雑になるので、まずは、「所要時間」別の人数を集計してみます。
集計表の下に「所要時間」だけを集計する表を作ってみます（上の表をコピー）。

まずは、左上スミのG15セル。

ここは「0分以上20分未満」の数です。
最初に「0分以上」を出します。
「所要時間」は全て「0分以上」なので、あえて集計しなくてもいいのですが、このセルの数式を他のセルにもコピーしたいので、数式を入れます。

数式は以下の通りとなります。
=COUNTIFS(D:D,">="&G12)

=COUNTIFS(D:D,">="&G12)　の式は、
所要時間が入っているD列の数値が、G12 （＝０）以上であるものの件数を調べる、というものです。

「以上」なので「＞＝」となりますが、COUNIFSの場合、この条件を当てはめるには「””」で括ったうえで、＆でセル（または数値）と繋ぎます。
参照元のトレースを示すと以下の通りです。

以上で「0分以上」の件数が集計できました（実際は全人数の「１５と同じ）。

数式内に数値は入れない

=COUNTIFS(D:D,">="&G12)　は、 G12が０なので
=COUNTIFS(D:D,">="&0)　でも同じ結果になります。
しかし、後者だと数式をコピーした際に、数式の中を一つ一つ修正しないといけません。
それは面倒＆間違いの元です。
数式には、基本、数値は直接入れず、セル番地を入れます。

次は「20分未満」の条件追加

このセルは「0分以上」「20分未満」です。
今の=COUNTIFS(D:D,">="&G12)　では「0分以上」の全ての件数(人数）が入ってしまっていますので、
この数式に「20分未満」の条件を追加します。

未満は「<」なので、「"<"&20」となりますが、「20」という数値ではなく、「20」が入っているセルである「G14」セルを入れて「"<"&G14」とします。

集計するデータは、「０分以上」と同じD列ですので、
D:D,"<"&G14　となります。

具体的な操作方法は、
=COUNTIFS(D:D,">="&G12)　の最後のカッコを取り（バックスペース）、区切り用の「,」を入れたら、D列をクリック、再度「,」を入れたら、"<"&G14　と入れてENTER、です（閉じカッコは不要）。

結果、以下の数式が入ります。

=COUNTIFS(D:D,">="&G12,D:D,"<"&G14)

「参照元のトレース」を表示してみると、「20」にも「・」が付いています（わかりづらいですけど）。
集計結果も、15から３に変わっています。

これで「０」分以上、「20」分未満、の件数（人数）が出ました。

「徒歩」の数式に、「0」分以上「20」分未満の条件を足す

後は、これを上の表の「G5」セルの数式に当てはめます。
つまり「通勤方法」が「徒歩」である件数を出してある数式に、「０」分以上「20」分未満という条件を足すのです。

G5セルには、以下の数式が入っています。
=COUNTIFS(C:C,F5)
G15には、以下の数式が入っています。
=COUNTIFS(D:D,">="&G12,D:D,"<"&G14)

これを合体すると・・・
=COUNTIFS(C:C,F5,D:D,">="&G12,D:D,"<"&G14)
となります。

ただし、実際は「０」分（G2セル）と「２０」分（G4セル）は、上の表のセルを参照するので、
G12をG2に、G14をG4にします。
結果、
=COUNTIFS(C:C,F5,D:D,">="&G2,D:D,"<"&G4)
となります。

COUNTIFS関数は単純に
「件数を数える範囲１、件数を数える条件１、件数を数える範囲２，件数を数える条件２，件数を数える範囲３、件数を数える条件３・・・」
と続いていきます。
なお、条件は１つだけでもいいので、COUNTIFは使いません。

今回は「件数を数える範囲」は全てD列で同じですが、これは省略はできないので、全ての「件数を数える条件」の前にD列を入れます。

実際に数式を入れる際には、
=conと入れてから、↓ で=COUNTIFS　を選んだら、Tabで確定し、
C列（通勤方法）を選択 ⇒区切り用の「,」を入力 ⇒ F5の「徒歩」を選択 ⇒ D列（時間）を選択 ⇒ 「, 」を入力 ⇒ ">="&G2 と入力 ⇒「, 」を選択 ⇒ D列（時間）を選択 ⇒「, 」を入力 ⇒ "<"&G4 と入力 ⇒ Enter　で

=COUNTIFS(C:C,F5,D:D,">="&G2,D:D,"<"&G4)

という数式が入ります。

後は、この数式を他のセルにコピーすればいいだけです。

コピーの前に、絶対参照・相対参照を設定する

後はコピーするだけ。
でも・・・
数式を横にコピーすると、集計対象であるD列も横にずれてしまいます。「徒歩」もずれています。

また、下にコピーしても、「分」がずれてしまいます。

これを防ぐために、「コピーする前」に「セルがずれる／ずれない」ように数式を修正します。
コピーしてからだと、一つ一つのセルを修正する必要があり、面倒＆ミスの元だからです。

具体的には、
=COUNTIFS(C:C,F5,D:D,">="&G2,D:D,"<"&G4)　を
=COUNTIFS($C:$C,$F5,$D:$D,">="&G$2,$D:$D,"<"&G$4)　とします。

・Ｃ列（「通勤手段」の列）

「通勤手段」のある「C列」は、どのセルでも集計対象です。
どのセルにコピーしても同じでないと困ります。
従って、「C:C」を、コピーしてもずれない「絶対参照」である「$C:$C」にします。
具体的には、「C:C」にカーソルを当てて、Ｆ４キーを１回押すだけです（数式入力時にＦ４を押しても同じ）。
「＄」が付けばコピーしてもずれません。

・F5（「徒歩」が入っているセル）
検索条件であるＦ５セルは、下にはずれてほしいのですが、横にずれると困ります。
そのため「F5」は「$F5」にします。
（「F5」⇒Ｆ４キーを３回押下⇒「$F5」）
列番号は「＄」が付く「絶対参照」に、
行番号は「＄」が付かない「相対参照」にするのです。

・Ｄ列（「所要時間」の列）
どのセルでも、この列を集計します。
ですので、コピーしてもずれないように「D:D」を「$D:$D」とします（Ｆ４キー１回押下）。

ここまでの操作で
=COUNTIFS(C:C,F5,D:D,">="&G2,D:D,"<"&G4)　が
=COUNTIFS($C:$C,$F5,$D:$D,">="’&G2,$D:$D,"<"&G4)　となりました。

ただ、このままコピーすると、今度は、所要時間が正しく出ません。

下にコピーしたら・・・

「0」分以上「20」分未満が、ずれてしまっているので、計算できません。

「○分」以上、「●分未満」は、下にコピーした際に、「行」がずれてはいけませんが、横にコピーした際には「列」がずれないと困ります。

従って、行番号（数字）には「＄」を付ける「絶対参照」とする一方で、列番号（アルファベット）には「＄」を付けない「相対参照」とします。

こちらも同じく、「G2」「G4」にカーソルを当ててクリックしてから、Ｆ４キーを２回押します。
Ｆ４キーを押すごとに「＄」の付き方が変わるので、２回目で「G$2」「G$4」となったら、Enterで確定します。

＊「＄」はF４を押す度に、付き方が変わります。
G2 ⇒ F4キー押下 ⇒ ＄G＄2　⇒再度F4キー押下 ⇒ G＄2 ⇒ 再度F4キー押下 ⇒ ＄G2 ⇒ 再度F4キー押下 ⇒ G2 ⇒再度F4キー押下 ⇒ 再び＄G＄2 ⇒ 以下繰り返し
「間違えた」と思っても、F4キーを押していれば大丈夫です。

上の操作で、
=COUNTIFS($C:$C,$F5,$D:$D,">="&G$2,$D:$D,"<"&G$4)
となったら、後はほかのセルにコピーします。
結果は以下の通り。

「参照元のトレース」を表示してみると、ちゃんと検索対象がずれるべきものはずれて、ずれてはいけないものはずれていないのがわかります。

１件足りない？

しかし・・・
全体のデータは15件なのに、合計は14件となっています。
これはおかしい。
合計欄にはあらかじめ「=SUM(L5:L9)」という数式が入っています（後で、タテでもヨコでも範囲選択して、Alt+Shift+＝で出してもいい）。

これは、「80分以上～」の欄にも、以下の通り「～未満」という検索が入っているからです。

「80分以上（の全て）」の場合は、最後の「未満」の条件を削ります。
具体的には、
=COUNTIFS($C:$C,$F5,$D:$D,">="&K$2)
とし、他のセルにもコピーします。

すると・・・

「80分以上、空白未満」が、単に「80分以上（のすべて）になり、上の表のように集計できます。

検算セルを入れておく

上の例のように、各セルの集計結果の合計が全体の件数に会わない場合もあります。
どこかのセルがおかしい場合です。

個々セルの合計≠全体の数

これは実際よく起きます。

全体件数がこの程度ならいいのですが、件数がたくさんあると、集計漏れに気づかない場合があります。
それを防ぐためには、全体件数も集計しておきます。

下図では、合計欄の下に
=COUNTA(B:B)-1
と入れて、「氏名」の全人数を出しています。
具体的には「B列」にあるデータの数を、COUNTA関数で出しています。
最後の「-1」は「人数」という項目名の分、１を引いているものです。

この数字と、上の合計欄が合わなければ、エラーメッセージを出すようにしておけば、集計ミス防止になります。

=IF(L10<>L11,"←エラー！","")
という数式で、L10の計と、L11の氏名の数を突合しています。（上の例は「80分以上」のところを修正する前のもの）

かなりくどくなってしまいましたが、以上で、COUNTIFS関数を使った集計は終わりです。

入力済みファイルもおいておきます。

右の欄の「平均」と「データバー」の出し方は、別記事にします。

この手の集計には、SUMPRODUCT関数というものも使えます。
ちょっと、とっつきづらいところもありますが、複数条件を行うクロス集計では、結構使えます。
次回は、そのSUMPRODUCT関数を使った集計方法をお示しします。
COUNTIFS関数を使っても、SUMPRODCT関数を使っても、答えは同じになりますので、使いやすい（理解しやすい）方を使えばいいと思います。
＊個人的には、今のところ、COUNTIFS関数の方が使いやすいですし、他の人（後任）も同じではないかと思います。
が、選択肢（別解）といてSUMPRODUCT関数もお示ししたいと思います。

以上、参考になれば幸いです。
もし何か「発見」があったら、「スキ」「フォロー」「チップ」していただける嬉しいです！