最近、友人を訪ねました。友人は明らかにスプレッドシートアプリケーションで作成された印刷物に取り組んでいました。それは郵便番号順に並べられた顧客名と住所のリストで、友人はそれぞれの郵便番号地域に何人の顧客がいるかを手作業で数えていました。ソフトウェアでできる作業を手作業で時間を無駄にしているのを見るのは、本当に残念です。
参照: Google Workspace vs. Microsoft 365: チェックリスト付き比較分析 (TechRepublic Premium)
当然のことながら、私はこのプロセスに介入し、もっと良い方法があることを指摘せざるを得ませんでした。このチュートリアルでは、私が彼らに見せた方法、つまりExcelのCOUNTIF()関数を使って、リスト内で特定の値（この場合は郵便番号）が出現する回数を返す方法を皆さんにもお見せします。その過程で、COUNTIF()の基本も学び、この多用途な関数を皆さん自身の作業に活用できるようにします。そして、SUBTOTAL()を使ってフィルタリング時にカウントする方法を学びます。
このデモでは、Windows 10 64ビットシステム上のMicrosoft 365を使用していますが、この関数は以前のバージョンのExcelでも使用できます。Web版Microsoft Excelは、ここで使用する両方の関数をサポートしています。
さらに詳しく知りたい場合は、こちらのプロジェクト デモ ファイルをダウンロードしてください。
ジャンプ先:
- COUNTIF引数
- ExcelでCOUNTIF関数を使う方法
- Excel で複数の項目をカウントするにはどうすればよいですか?
- Excel でフィルターされたリストをカウントするにはどうすればよいですか?
- 追加リソース
COUNTIF引数
どちらの関数を使う前に、COUNTIF() の引数を見てみましょう。COUNTIF() は、指定した条件を満たすセルの数を返します。今回の場合は、指定した範囲内で特定の郵便番号が出現する回数を数えます。
COUNTIF() は次の構文を使用します。
COUNTIF(range,criteria)
ここで、「範囲」はカウントする値のリストを識別し、「条件」はカウントの条件を表します。
先に進む前に、COUNTIF() には1つの制限があることを知っておくことが重要です。criteria 引数は、リテラル文字列値を使用する場合、255文字までに制限されます。この制限に遭遇する可能性は低いですが、もし遭遇した場合は、連結演算子を使って文字列を連結し、&より長い文字列を作成できます。
COUNTIFのトラブルシューティング
COUNTIF() 関数が何も返さず、値が存在することが分かっている場合は、次のアクションとヒントを検討してください。
- 必ず値を区切ってください。例えば、「apples」は参照範囲内で「apple」という単語が出現する回数をカウントします。引用符を省略するとカウントされません。数値には区切り文字は必要ありませんが、日付には
#区切り文字を使用します。 - 値を確認してください。参照範囲には、他の文字の前後に不要なスペース文字が含まれている可能性があります。TRIM() を使用して、必要な値のみを取得してください。
- ファイルが開いているかどうかを確認してください。COUNTIF () が別のブックを参照している場合、そのファイルは開いている必要があります。開いていないと、関数は #VALUE! エラーを返します。
- 条件テキストをよく見てみましょう。COUNTIF () の条件値は大文字と小文字が区別されません。ただし、条件に波括弧が含まれているとエラーが発生するため、値を貼り付ける場合は注意してください。通常は問題にならないはずです。
- セルの書式設定に依存しないでください。COUNTIF () は、塗りつぶしやフォントの色の値に基づいてセルをカウントできません。
この関数について理解できたので、簡単な例を使って使用してみましょう。
ExcelでCOUNTIF関数を使う方法
まずはCOUNTIF()の簡単な使い方から始めましょう。図Aにあるように、この関数は
=COUNTIF(Table1[ZIPS],10123)
2 の値を返します。
図A
これは、郵便番号の値「10123」が「Table1」というテーブルに2回出現しているためです。Tableオブジェクトを使用していない場合は、次のように範囲参照を使用してください。
=COUNTIF(A2:A21,10123)
構造化参照に慣れていない場合、Table1[ZIPS] は混乱を招く可能性があります。サンプルデータは Excel の Table オブジェクトとしてフォーマットされています。Table1 は Table オブジェクトの名前、[ZIPS] は列名です。
単一の郵便番号を指定するのは簡単ですが、すべての郵便番号を含めることでこの数を拡張することが必要になる可能性があります。
Excel で複数の項目をカウントするにはどうすればよいですか?
COUNTIF() を使用するときはリテラル値を指定できますが、条件引数はセルまたは範囲参照をサポートします。
この関数の柔軟性を示すために、サンプルデータ内の各郵便番号の出現回数をカウントしてみましょう。通常、郵便番号は氏名、住所、市区町村、都道府県などの他の住所値と関連付けられます。郵便番号の値のみをカウントする場合、これらの値は無関係であるため、この例は意図的にシンプルにしています。
参照: Microsoft 365 サービス利用ポリシー (TechRepublic Premium)
Microsoft 365 を使用している場合は、次の式を使用して、並べ替えられた郵便番号値の一意のリストを生成します (図 B )。
=SORT(UNIQUE(Table1[ZIPS]))
図B
SORT() と UNIQUE() はどちらも動的配列関数で、Excel 365 でのみ使用できます。この例では、式は D2 に 1 つだけあります。しかし、式は下のセルに渡され、返された値を配列として返します。スピルオーバーエラーが発生した場合は、式の下のセルで配列がブロックされている可能性があります。
郵便番号の一意のリストができたら、図Cに示すように、COUNTIF()を使用して各郵便番号の値の数を返すことができます。
=COUNTIF(Table1[ZIPS],D2)
それを残りのセルにコピーします。
図C
動的配列の詳細については、「Excel スプレッドシートで並べ替えられた一意のリストを作成する方法」を参照してください。
Excel 365 より前のバージョンで複数の項目をカウントするにはどうすればよいですか?
Excel 365 より前のバージョンの Excel をお使いの場合、同じ結果を得るには少し手間がかかります。郵便番号の一意のリストが並べ替えられていることが重要であれば、先に進む前にソースデータを並べ替えてください。
これを行うには、列Aの任意のセルをクリックし、「データ」タブの「並べ替えとフィルター」グループにある「昇順で並べ替え」ボタンをクリックします。または、「ホーム」タブの「編集」グループにある「並べ替えとフィルター」をクリックすることもできます。
列 A の値から一意の郵便番号リストを作成するには、次の手順を実行します。
- データセット内の任意のセルのグループをクリックします。この例では、A1:A21 を選択しました。
- [データ] タブをクリックし、[並べ替えとフィルター] グループの [詳細設定] をクリックします。
- [別の場所にコピー] オプションをクリックします。
- Excelではリスト範囲として$A$1:$A$21が表示されます。表示されない場合は、手動で修正できます。
- 条件範囲がある場合はそれを削除します。
- [コピー先コントロール] をクリックし、G1 などの選択されていないセルをクリックします。
- 「一意のレコードのみ」オプションをチェックします (図 D )。
図D
- [OK]をクリックします。
この機能は、A1の見出しテキストと書式設定もコピーします。どちらのコピーも回避することはできませんが、どちらも作業の妨げにはならないので問題ありません。
この時点で、残っているのは、列 G の一意のリストのエントリに基づいて、列 A の一意のエントリをカウントする関数だけです。次に、セル H2 に次の関数を入力します。
=COUNTIF(Table1[ZIPS],G2)
これを残りのセルにコピーします。図Eに示すように、この関数は最初の関数と同じカウントを返します。
図E
セルH9の太字の20に気づきましたか？これはSUM()関数で、カウントされたエントリ数が元のエントリ数と一致することを保証します。列Aのソースデータには20個のエントリがあったので、カウントされた一意のエントリの総数も同じになるはずです。
COUNTIF() はリスト内の特定の値を数えるのに便利ですが、フィルターされたリスト内の項目を数えたい場合もあります。次に、その方法を説明します。
Excel でフィルターされたリストをカウントするにはどうすればよいですか?
COUNTIF() 関数は多くの状況でうまく機能しますが、フィルタリングされたリストの結果に基づいてカウントしたい場合はどうすればよいでしょうか？このような状況では、COUNTIF() 関数は機能しません。この関数は引き続き正しい結果を返しますが、フィルタリングされたセットの正しいカウントは返しません。代わりに、フィルタリングされたリストをカウントするには SUBTOTAL() 関数を使用する必要があります。
ExcelのSUBTOTAL()関数は、フィルタリングに対応している点で非常に特殊です。具体的には、数学的な計算結果に関わらず、フィルタリングされたリストに含まれる値のみを評価します。この関数の構文は次のとおりです。
SUBTOTAL(number,reference)
「数値」は計算式を、「参照」は値を指定します。デフォルトでは、数値は109（SUM()）です。数値の完全なリストについては、表Aを参照してください。
表A
| 非表示の行を含む | 非表示の行を除外する | 関数 |
|---|---|---|
| 1 | 101 | 平均 |
| 2 | 102 | カウント |
| 3 | 103 | カウント |
| 4 | 104 | マックス |
| 5 | 105 | 分 |
| 6 | 106 | 製品 |
| 7 | 107 | 標準偏差 |
| 8 | 108 | STDEVP |
| 9 | 109 | 和 |
| 10 | 110 | VAR |
| 11 | 111 | ヴァープ |
前のセクションでは、COUNTIF() はソースデータが通常のデータ範囲か Table オブジェクトかを考慮せずに計算しました。このソリューションを機能させるには、Table オブジェクトを使用する必要があります。データ範囲を Table オブジェクトに変換するには、データ範囲内の任意の場所をクリックし、Ctrl + T キーを押して「OK」をクリックして変換を確定します。これにより、ヘッダーセルにフィルタードロップダウンが自動的に表示されます。
フィルタリングを開始する前に、次のようにテーブルに特別な行を追加する必要があります。
- テーブル内の任意の場所をクリックします。
- コンテキスト テーブル デザイン タブをクリックします。
- [表スタイルのオプション] グループで、[合計行] 項目をクリックします (図 F )。
図F
図Fに示すように、この行にはデフォルトで合計値を計算する SUBTOTAL() 関数が設定されています。この場合、合計値ではなく個数を求めます。SUBTOTAL() 関数の引数を変更するには、A22 をクリックし、図Gに示すドロップダウンリストから「個数」を選択します。
図G
ご覧の通り、様々な機能から選択できます。図Hはカウントを示しており、20となっています。
図H
元のSUBTOTAL()関数の最初の引数は109で、これはSUM()を表します。合計関数をCountに変更すると、SUBTOTAL()はその引数を103に更新し、これはCOUNT()を表します。
フィルタリングプロセスを開始する
合計行が配置され、カウントが表示されたら、フィルタリングを始める準備が整いました。まずは、A1のフィルタリングドロップダウンをクリックし、以下の操作を行ってください。
- チェックを外します（すべて選択）。
- 10125 オプションをチェックします (図 I )。
図I
- [OK]をクリックします。
図 Jに示すように、フィルターされたセットには 2 つの項目が含まれており、SUBTOTAL() 関数は 20 ではなく 2 を返します。この関数は他の関数とは異なり、フィルターを適用すると SUBTOTAL() が更新されるため、特別です。
図J
もう一度試してみましょう。ただし今回は、2 つの郵便番号をチェックします (図 K )。
図K
図 Lに示すように、SUBTOTAL() は両方の郵便番号値の数、つまり 7 を返します。SUBTOTAL() は、高度なフィルター機能を使用して適用するあらゆるフィルターを処理できるほど柔軟性があります。
図L
追加リソース
Tableオブジェクトの合計行でCOUNTIF()関数またはSUBTOTAL()関数を使用する場合でも、値のカウントは簡単です。カウントについて詳しくは、TechRepublicの別のチュートリアル「ExcelでUNIQUE()関数を使用して一意の値の数を返す方法」が参考になります。
次に読む: Microsoft Project の代替となる 8 つのベストな選択肢 (無料 & 有料) (TechRepublic)
