重複をカウントするのはExcelでよく使われるタスクで、シンプルなCOUNT()関数やフィルタリングされたデータセットを使えば簡単にこなせます。しかし、値そのものよりもカウント数の方が重要な場合は、少し難しく感じるかもしれません。例えば、ある値が1回、2回、3回、4回…といった具合に、何回出現するかを知りたい場合があります。つまり、単純なカウントの条件付きカウントが必要なのです。幸いなことに、この問題は一見複雑そうに見えます。この記事では、補助列とシンプルなマトリックスを使ったシンプルな解決策を紹介します。式を使った解決策が必要な場合は、そちらも紹介します。
Windows 10 64ビットシステムでExcel 2016を使用していますが、このソリューションはバージョン2007から2016まで対応しています。COUNTIFS()関数はExcel 2003以前ではサポートされていません。任意のデータセットで作業することも、デモ用の.xlsxファイルをダウンロードすることもできます。
参照: Excelで効果的で使いやすいスライサーを作成する方法
準備
条件付きカウントとはどういう意味か疑問に思われるかもしれません。ExcelのCOUNTIFS()関数がまさにその機能を果たします。しかし、今回は条件付きでカウントしているのは値ではなく、個数です。図Aはシンプルなデータセットを示しています。最初のステップは、同じデータセット内で特定の値が出現する回数を数えることです。これは、次のようなシンプルなCOUNT()関数を使えば簡単に実現できます。
COUNT(列,値)
ここで、columnはカウントする値を含む列を参照し、valueは同じ列内のセルです。列Cのカウント関数は次のようになります。
=COUNTIF(B:B,B3)
列参照 (B:B) を使用して、列 B 内の各値の出現回数をカウントします。
- Mike と Susan のデータ セットにはレコードが 1 つだけあります。
- John と George にはデータ セット内に 2 つのレコードがあります。
- Dawn のデータ セットには 5 つのレコードがあります。
図A
COUNT() を使用して、同じ値が出現した回数を返します。
列参照は強力なツールですが、多少の制約があります。実際のデータセットの上下にあるB列の空白セルは、他の用途には使用できません。さて、一人あたりのレコード数を知ることが目的ではなく、あるレコードが何回繰り返されているかを知りたいとしましょう。つまり、1回、2回、3回…といった具合に出現するレコードの数を知りたいのです。
参照: Excelのヒント: VBAを使用してセルと範囲を効率的に選択する方法
条件付きでカウントを数える
単純なデータ セットでは、COUNT() 関数の結果を確認して、次のことをすぐに判断できます。
- マイクとスーザンの 2 人がそれぞれ 1 つのレコードを持っています。
- ジョンとジョージの二人にはそれぞれ2つのレコードがあります。
- 3つや4つのレコードを持っている人はいません。
- ドーンという人物は 5 つのレコードを持っています。
- 5 件を超えるレコードを持つ人はいません。
名前は省略しても結果は同じくらい重要です。ここで求めているのは条件付きカウントであり、実際にカウントされる値ではありません。結果を明確にするために、名前について言及しただけです。
図 B は、COUNTIFS() 関数を使用して、列 C の COUNT() 関数の結果を条件付きでカウントするマトリックスを示しています。
図B
単純な行列は、COUNT() 関数の結果をカウントします。
この関数は次の形式になります。
COUNTIFS(カウント範囲,条件)
ここで、countrange はカウントする値の範囲であり、criteria はカウントに含めるために値が満たす必要がある条件です。
3行目のCOUNTIFS()関数:
=COUNTIFS($C$3:$C$13,F$2)/F$2
単純行列のヘッダー値を基準として使用します。その結果を基準で割ります。
しかし、このマトリックスは全体像を示すものではありません。少なくとも1つの値に5つのレコードがあることは明らかですが、マトリックスにはそれが反映されていません。
表現ソリューション
マトリックスはヘッダー行までしか使えません。ヘッダー行を拡張することはできますが、どこまで拡張すれば十分なのでしょうか？より動的な解決策としては、同じ式を使ってC列のCOUNT()の結果を参照する方法があります。図Cにその式を示します。
=COUNTIFS($C$3:$C$13,$C3)/$C3
COUNT() 関数の結果を基準として使用します。
図C
カウント値を参照する場合、マトリックス式はより動的になります。
実際の結果を参照することで、式はあらゆる可能性を考慮していることを保証します。行列も、どの程度まで拡張するかを事前に知っていれば、同様のことを行うことができます。幸いなことに、条件付きでカウントを数えるのにESPは必要ありません。
欠点としては、列Dの式は、その目的を理解していないユーザーを混乱させる可能性があります。列Cにはcountが表示され、列Dには「count」の繰り返し回数が表示されます。表Aで簡単に確認できます。
表A
データを他の人と共有する際には、カウント列を含めないことをお勧めします。この解決策により、別の形式で提示した場合に意味のある情報が得られる可能性があります。
その他の解決策
このコラムは読者の方からヒントを得て書きました。この方法を採用したのは、少なくとも私にとっては手っ取り早い方法だったのと、フィルタリングや小計といった私がよく利用する組み込み機能は、面倒な手続きが多すぎると思ったからです。別の解決策をお持ちの方はいらっしゃいますか？もしあれば、下のコメント欄でぜひ共有してください。
Officeに関するご質問をお送りください
読者からの質問には可能な限りお答えしますが、必ずお答えできるとは限りません。リクエストがない限り、ファイルは送信しないでください。添付ファイル付きの初回サポートリクエストは未読のまま削除されます。ご質問を明確にするために、データのスクリーンショットを送信していただくことも可能です。お問い合わせの際は、できるだけ具体的にご記入ください。例えば、「ワークブックのトラブルシューティングを行い、問題点を修正してください」という質問では返答がないかもしれませんが、「この数式が期待どおりの結果にならない理由を教えていただけますか？」という質問であれば、回答が得られる可能性があります。ご使用のアプリとバージョンを明記してください。読者サポートにあたり、TechRepublicから時間や専門知識の報酬を受け取ることはありません。また、サポートした読者から料金を請求することもありません。お問い合わせは[email protected]までお願いいたします。
Office のその他のヒントとハウツー
- Office Q&A: Excel テーブルに対応するためにユーザーフォーム VBA コードを更新する方法
- Outlookの予定表ウィンドウで効率的に作業するための8つの簡単なヒント
- VBAを使用してWordユーザーフォームに入力されたデータを検証する方法
- Outlookをモバイルデバイスと同期するための10のヒント
