今回は、ExcelのCOUNTIFS関数を使って、複数条件の件数を集計しやすくする方法を紹介します。
COUNTIFSでできること
COUNTIFSは、複数の条件に合う行の件数を数える関数です。担当者別の対応件数、ステータス別の未処理件数、期間内の申請件数、商品区分ごとの登録件数など、日々の表で使いやすい集計に向いています。
COUNTIFは条件が1つのときに使います。COUNTIFSは条件を追加できるため、「部署が営業部で、状態が未対応」「日付が今月で、担当者が佐藤さん」のように、複数の条件を同時に満たす行を数えられます。
COUNTIFSは、表の中から条件に合うレコード数を確認するための関数です。合計金額を出す関数ではないため、金額や数量を足したい場合はSUMIFSを使います。まず、件数を数えたいのか、値を合計したいのかを切り分けます。
基本の書き方を押さえる
COUNTIFSの基本形は、条件範囲と条件をセットで並べる形です。1つ目の範囲と条件、2つ目の範囲と条件、という順番で指定します。
たとえば、A列に部署、B列にステータスがあり、営業部の未対応件数を数えたい場合は、部署列に「営業部」、ステータス列に「未対応」という条件を指定します。条件範囲は同じ行数にそろえる必要があります。A2からA100までを指定したら、B列もB2からB100までにします。
範囲の行数がずれていると、正しく計算できません。表を作るときは、見出し行を除いたデータ部分だけを範囲にするか、テーブル機能を使って範囲を管理すると扱いやすくなります。
COUNTIFSで確認したい点
- 条件範囲の行数がそろっているか
- 条件に使う文字が表の値と一致しているか
- 日付や数値が文字列として入力されていないか
- 空白や余分なスペースが混ざっていないか
条件をセル参照にする
COUNTIFSの条件は、数式の中に直接書くこともできますが、実務ではセル参照にするほうが便利です。集計表の左側に担当者名や部署名を並べ、そのセルを条件として参照すれば、数式をコピーして使い回せます。
たとえば、集計表のA列に部署名、B列に未対応件数を出す場合、B列の数式ではA列の部署名を条件として参照します。これにより、部署名を変えるだけで結果も変わります。
ステータス名も見出しセルに置くと、縦横にコピーできる集計表を作れます。行に部署、列にステータスを置けば、簡単なクロス集計のように使えます。ピボットテーブルほどの機能は不要でも、決まった条件で毎回同じ集計をしたい場面に向いています。
条件をセルに出しておくと、数式の意味が見えるため、後から確認する人にも伝わりやすくなります。
日付条件を扱う
COUNTIFSでは、日付を条件にした集計もできます。今月の件数、指定期間内の件数、締切日を過ぎた件数などを数えるときに使います。
期間を指定する場合は、開始日以上、終了日以下のように2つの条件を同じ日付列に指定します。条件には比較演算子を使います。比較演算子とセル参照を組み合わせる場合は、文字列としての記号とセル参照をつなげます。
日付条件でつまずきやすいのは、見た目は日付でも実際は文字列になっているケースです。左寄せになっている、日付の計算ができない、並べ替えが期待通りにならない場合は、日付として認識されているか確認します。
日付集計の工夫
- 開始日と終了日は集計表のセルに置く
- 条件式に直接日付を書かず、セル参照で管理する
- 終了日は「以下」にするか、翌日未満にするかを統一する
- 時刻を含むデータでは終了条件に注意する
時刻を含むデータでは、終了日を「その日以下」にしても、同日の午後のデータが外れる場合があります。日付と時刻が混ざる表では、翌日の0時より前という条件にするなど、データの持ち方に合わせて考えます。
ワイルドカードで文字条件を広げる
COUNTIFSでは、条件にワイルドカードを使えます。アスタリスクは任意の文字列を表し、疑問符は任意の1文字を表します。商品名や案件名の一部で件数を数えたいときに便利です。
たとえば、備考欄に「再確認」を含む行を数えたい場合、条件に「*再確認*」のような形を使います。前後に文字があっても該当します。部署名の前後に余分な文字がある場合にも使えますが、条件を広げすぎると意図しない行まで数えることがあります。
ワイルドカードを使う前に、データの入力ルールを整えることも大切です。「未対応」「未 対応」「未処理」が混在していると、関数だけでは正しい集計になりにくくなります。表記を統一できるなら、まず入力側を整えます。
空白と空白以外を数える
COUNTIFSは、空白や空白以外の条件にも使えます。未入力の確認、入力済み件数の確認、担当者が未設定の行の抽出前チェックなどに役立ちます。
空白を数える場合は、条件に空文字を指定します。空白以外を数える場合は、空白ではない条件を使います。ただし、見た目は空白でもスペースが入っているセルは空白扱いにならないことがあります。入力ミスが疑われる場合は、TRIM関数や検索で余分なスペースを確認します。
また、数式で空文字を返しているセルは、扱いが想定と異なる場合があります。COUNTIFSで思った件数にならないときは、実際にセルが完全な空白なのか、数式の結果として空欄に見えているのかを確認します。
集計表を作るときの配置
COUNTIFSを使った集計表は、元データと集計結果を分けると見やすくなります。元データの横に集計を置くと、列が増えたときに見通しが悪くなることがあります。別シートに集計表を作ると、入力と確認の役割が分かれます。
集計表では、条件セルを固定する場所と、コピーで動かしたい場所を整理します。行方向にコピーするときは列を固定する、列方向にコピーするときは行を固定する、といった参照の使い分けが必要です。絶対参照と相対参照を混ぜると、同じ数式を広い範囲にコピーできます。
見出しには、条件として使っている値と同じ表記を使います。見出しに「未処理」と書き、元データでは「未対応」と入力されていると件数が合いません。入力規則のリストを使って表記をそろえると、COUNTIFSの結果も安定します。
結果が合わないときの見直し
COUNTIFSの結果が想定と違う場合、数式より先にデータを確認します。条件範囲、条件の文字、日付形式、空白、フィルター状態を順に見ると原因を見つけやすくなります。
フィルターで絞り込んだ表を見ながらCOUNTIFSを使うと、表示されていない行も数えられる点に注意します。COUNTIFSは通常、非表示の行も対象にします。フィルター後の表示行だけを数えたい場合は、別の方法を検討します。
また、全角と半角の違い、前後のスペース、改行がセル内に入っている場合も件数がずれます。特に外部システムから貼り付けたデータでは、見えない文字が混ざることがあります。補助列で文字を整えてから集計すると安定します。
まとめ
ExcelのCOUNTIFSは、複数条件に合う件数を数えるときに役立つ関数です。部署、担当者、ステータス、日付などを組み合わせれば、日々の確認表や管理表で使いやすい条件集計を作れます。
条件を数式に直接書くより、セル参照にして集計表として配置すると、条件変更やコピーがしやすくなります。日付、空白、ワイルドカードを扱う場合は、データの形式や入力ルールも確認します。
結果が合わないときは、範囲の行数、条件文字、日付の認識、余分なスペースを順に見直します。COUNTIFSを使い慣れると、表の確認作業を自分で組み立てやすくなります。