概要
Excel 2021から使用可能になったUNIQUEとFILTER関数に、COUNTA関数を組み合わせ、空白と重複を除いた件数を条件付きでカウントする数式を紹介します。
例として、以下のExcelではF列に都道府県が空白と重複込みで入力されています。
ここから、空白と重複を除き、かつ、「一番右の文字が"県"」の条件に一致するデータの件数をカウントする数式を作ります。
数式
条件付き
=COUNTA(UNIQUE(FILTER(F3:F17, (F3:F17<>"" )* (RIGHT(F3:F17,1)="県"))))
上記の画像にて、黄色いセル（D3セル）に入力されている数式がこちらです。
青色の部分がカウントする範囲です。2か所ありますが同じ範囲を指定してください。
赤色の部分(RIGHT(F3:F17,1)="県")がカウント条件です。ここではカウントする範囲と同じ範囲を指定し、「一番右の文字が"県"」という条件を表す数式を入れています。
条件は必要に応じて変更してください。例えば、3文字の都道府県を集計したければ(LEN(F3:F17)=3)に変える等です。
条件は別の列を指定してもOK
行の範囲が一致していれば、条件は別の列を指定しても構いません。
以下の例では、「G列（得点）の値が50より大きい」という条件に絞り込んだ上で、F列の件数を空白と重複を除いてカウントしています。
=COUNTA(UNIQUE(FILTER(F3:F17, (F3:F17<>"" )*(G3:G17>50))))
条件無し
=COUNTA(UNIQUE(FILTER(F3:F17, (F3:F17<>"" ))))
条件が必要なく、単に空白と重複を除いた件数をカウントしたい場合はこちらの数式になります。
複数条件
=COUNTA(UNIQUE(FILTER(F3:F17, (F3:F17<>"" )*(RIGHT(F3:F17,1)="県")*(LEN(F3:F17)=3))))
複数の条件を付けたい場合は、*と条件式を横に追加していきます。
解説
「数式の検証」を使って流れを追ってみましょう。
わかりやすいよう、F3～F8の範囲までデータを減らしています。
数式の条件は同じで、空白と重複を除き、かつ、「一番右の文字が"県"」の条件に一致するデータの件数をカウントします。
Step 1
数式の検証を開始しました。計算の途中結果を表形式で書いていきます。
| データ |
|---|
| 東京都 |
| 千葉県 |
| （空白） |
| 千葉県 |
| 神奈川県 |
| 東京都 |
Step 2
まずF3:F8<>""の部分が計算されました。各行には空白でなければTrue、空白ならFalseが入ります。
| データ | 空白ではない |
|---|---|
| 東京都 | True |
| 千葉県 | True |
| （空白） | False |
| 千葉県 | True |
| 神奈川県 | True |
| 東京都 | True |
Step 3
次に、RIGHT(F3:F17,1)が計算されました。各行には右端から数えて一つ目の文字（右端の文字）が入ります。
| データ | 空白ではない | 右端の文字 |
|---|---|---|
| 東京都 | True | "都" |
| 千葉県 | True | "県" |
| （空白） | False | "" |
| 千葉県 | True | "県" |
| 神奈川県 | True | "県" |
| 東京都 | True | "都" |
Step 4
続いて、先ほど算出したRIGHT(F3:F8,1)の結果が"県"と一致するかどうかが計算されました。各行には右端の文字が"県"ならTrue、そうでなければFalseが入ります。
| データ | 空白ではない | 右端の文字 = "県" |
|---|---|---|
| 東京都 | True | False |
| 千葉県 | True | True |
| （空白） | False | False |
| 千葉県 | True | True |
| 神奈川県 | True | True |
| 東京都 | True | False |
Step 5
<>""の結果と、RIGHTの結果 = "県"の結果を乗算します。
Step 6
<>""の結果と、RIGHTの結果 = "県"の結果を乗算した結果が表示されました。Trueは1、Falseは0として扱われますのでTrue * Trueが1、それ以外の組み合わせの計算結果は0になります。つまり、両方の条件を満たす行のみが1と表示されます。
| データ | 空白ではない | 右端の文字 = "県" | 両方の条件を満たす |
|---|---|---|---|
| 東京都 | True | False | 0 (False) |
| 千葉県 | True | True | 1 (True) |
| （空白） | False | False | 0 (False) |
| 千葉県 | True | True | 1 (True) |
| 神奈川県 | True | True | 1 (True) |
| 東京都 | True | False | 0 (False) |
Step 7
ここからは関数が順次実行されます。まずFILTER関数が実行され、先ほどの計算結果で1となった行のみに絞られました。
| データ | 空白ではない | 右端の文字 = "県" | 両方の条件を満たす |
|---|---|---|---|
| 千葉県 | True | True | 1 (True) |
| 千葉県 | True | True | 1 (True) |
| 神奈川県 | True | True | 1 (True) |
Step 8
次にUNIQUE関数が実行され、重複した行が除かれました。
| データ | 空白ではない | 右端の文字 = "県" | 両方の条件を満たす |
|---|---|---|---|
| 千葉県 | True | True | 1 (True) |
| 神奈川県 | True | True | 1 (True) |
Step 9
最後に、COUNTA関数が実行され、残った行の件数が計算されます。これにて、『空白と重複を除き、かつ、「一番右の文字が"県"」の条件に一致するデータの件数』を無事にカウントできました。
動作環境
- Microsoft Excel 2024
参考
こちらの記事は以下の【Excel】空白と重複を除いた件数を条件付きでカウントする【Excel 2013】という記事をリニューアルしたものです。もしExcel 2021以前のバージョンの話をお調べの場合は、以下の記事をご参照ください。