【Excel】空白と重複を除いた件数を条件付きでカウントする【Excel 2013】

:::note warn
こちらの記事は2021以前のバージョンの内容です。
2021以降のバージョンをご利用の場合は、以下の記事をご参照ください。
:::
https://qiita.com/Davoi/items/afc93ac7292cec69c1cf

## 概要
SUMPRODUCT関数を使って、空白と重複を除いた件数を条件付きでカウントする数式を紹介します。

例として、以下のExcelではF列に都道府県が空白と重複込みで入力されています。
ここから、空白と重複を除き、かつ、「一番右の文字が"県"」の条件に一致するデータの件数をカウントする数式を作ります。

|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578020/81a69758-d6c6-f452-8d4d-a6e4761c8b3d.png)|
|:--|

### 数式
#### 条件付き
><b>=SUMPRODUCT(((<font color="Blue">F3:F17</font><>"")/COUNTIFS(<font color="Blue">F3:F17</font>,<font color="Blue">F3:F17</font>&""))*(RIGHT(<font color="Blue">F3:F17</font>,1)="県"))</b>

上のExcelで黄色いセルに入力されている数式がこちらです。
<font color="Blue">青色</font>の部分がカウントする範囲です。4か所ありますが全て同じ範囲を指定してください。
<b>(RIGHT(<font color="Blue">F3:F17</font>,1)="県")</b>の部分がカウント条件です。こちらも必要に応じて変更してください。
例えば、3文字の都道府県を集計したければ<b>(LEN(<font color="Blue">F3:F17</font>)=3)</b>に変える等です。

#### 条件無し
><b>=SUMPRODUCT((<font color="Blue">F3:F17</font><>"")/COUNTIFS(<font color="Blue">F3:F17</font>,<font color="Blue">F3:F17</font>&""))</b>

条件が必要なく、単に空白と重複を除いた件数をカウントしたい場合はこちらの数式になります。

#### 複数条件
><b>=SUMPRODUCT(((<font color="Blue">F3:F17</font><>"")/COUNTIFS(<font color="Blue">F3:F17</font>,<font color="Blue">F3:F17</font>&""))*<font color="Red">(RIGHT(<font color="Blue">F3:F17</font>,1)="県")</font>*<font color="Red">(LEN(<font color="Blue">F3:F17</font>)=3)</font>)</b>

複数の条件を付ける場合は*を使って横に並べていきます。

## 解説
「数式の検証」を使って流れを追ってみましょう。
わかりやすいよう、F3～F8の範囲までデータを減らしています。
数式の条件は同じで、空白と重複を除き、かつ、「一番右の文字が"県"」の条件に一致するデータの件数をカウントします。

|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578020/147bfb14-a0ed-f75a-dc33-759a426e73f2.png)|
|:--|


###### Step 1
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578020/aa89126e-05f4-f4e1-efd5-0fddb433cf2c.png)
数式の検証を開始しました。
計算の途中結果を表形式で書いていきます。

|データ|
|---|
|東京都|
|千葉県|
|（空白）|
|千葉県|
|神奈川県|
|東京都|

###### Step 2
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578020/d949782f-02d2-2b71-4458-11a4d199dc62.png)
まず<b>F3:F8<>""</b>の部分が計算されました。
空白でなければTrue、空白ならFalseが入ります。

|データ|<>""|
|---|---|
|東京都|True|
|千葉県|True|
|（空白）|False|
|千葉県|True|
|神奈川県|True|
|東京都|True|

###### Step 3
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578020/2b98dc42-b613-ec8b-0096-c73fbe7b4c7c.png)
次にCOUNTIFSの2つ目の引数、検索条件部分の<b>F3:F8&""</b>が計算されます。
空白（何も入力されていないセル）の数をカウントする必要があるため、範囲の後ろに<b>&""</b>を付けています。

###### Step 4
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578020/a8cf4d5e-effe-fc79-5310-000b3e50112e.png)
<b>F3:F8&""</b>が計算されました。これでCOUNTIFSの式が以下の表のように出来上がった形になります。

|データ|<>""|COUNTIFS|
|---|---|---|
|東京都|True|F3～F8から"東京都"の数をカウントする|
|千葉県|True|F3～F8から"千葉県"の数をカウントする|
|（空白）|False|F3～F8から""の数をカウントする|
|千葉県|True|F3～F8から"千葉県"の数をカウントする|
|神奈川県|True|F3～F8から"神奈川県"の数をカウントする|
|東京都|True|F3～F8から"東京都"の数をカウントする|

###### Step 5
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578020/72372039-7e78-4364-31a0-ac7490a3e7a8.png)
<b>COUNTIFS(F3:F8,F3:F8&"")</b>が計算されました。結果は以下の表の通りです。

|データ|<>""|COUNTIFSの結果|
|---|---|---|
|東京都|True|2|
|千葉県|True|2|
|（空白）|False|1|
|千葉県|True|2|
|神奈川県|True|1|
|東京都|True|2|

###### Step 6
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578020/f5287538-1ff8-e775-27cd-67ceada17d30.png)
<b>F3:F8<>""</b>の結果を<b>COUNTIFS(F3:F8,F3:F8&"")</b>の結果で除算します。
Trueは1、Falseは0として扱われますので、結果は以下の表の通りになります。

空白のセルは<b><>""</b>の結果が0になるため、除算の結果も0になりカウントから除外されるという仕組みになります。
また、COUNTIFSの条件に<b>&""</b>を付けていないと、空白の検索結果が0になり、#DIV/0!のエラーが発生します。

|データ|<>""|COUNTIFSの結果|<>"" ÷ COUNTIFSの結果|
|---|---|---|---|
|東京都|True|2|0.5|
|千葉県|True|2|0.5|
|（空白）|False|1|0|
|千葉県|True|2|0.5|
|神奈川県|True|1|1|
|東京都|True|2|0.5|

###### Step 7
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578020/c8673384-988f-ad8f-40da-70c8f2e003f7.png)
続いて<b>RIGHT(F3:F8,1)</b>が計算されます。

###### Step 8
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578020/9cc14b77-44cc-890d-443c-9de0b976df56.png)
<b>RIGHT(F3:F8,1)</b>が計算されました。結果は以下の表の通りです。
右端から数えて1文字が表示されています。

|データ|<>"" ÷ COUNTIFSの結果|RIGHTの結果|
|---|---|---|
|東京都|0.5|"都"|
|千葉県|0.5|"県"|
|（空白）|0|""|
|千葉県|0.5|"県"|
|神奈川県|1|"県"|
|東京都|0.5|"都"|

###### Step 9
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578020/57d3ad7a-7cef-fd90-2a7e-26d8eeb1f5c6.png)
続いて<b>RIGHT(F3:F8,1)="県"</b>が計算されました。
右端から数えて1文字が"県"ならTrue、違ければFalseが入ります。

|データ|<>"" ÷ COUNTIFSの結果|RIGHTの結果|RIGHTの結果 = "県"|
|---|---|---|---|
|東京都|0.5|"都"|False|
|千葉県|0.5|"県"|True|
|（空白）|0|""|False|
|千葉県|0.5|"県"|True|
|神奈川県|1|"県"|True|
|東京都|0.5|"都"|False|

###### Step 10
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578020/c951c762-c49d-8de1-8b9c-845e5dffa4af.png)
大詰めです。「<b><>"" ÷ COUNTIFSの結果</b>」と「<b>RIGHTの結果 = "県"</b>」を乗算します。

|データ|<>"" ÷ COUNTIFSの結果|RIGHTの結果 = "県"|
|---|---|---|
|東京都|0.5|False|
|千葉県|0.5|True|
|（空白）|0|False|
|千葉県|0.5|True|
|神奈川県|1|True|
|東京都|0.5|False|

###### Step 11
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578020/37b4cc72-2a48-7a6b-542b-e8d1dfd84b34.png)
「<b><>"" ÷ COUNTIFSの結果</b>」×「<b>RIGHTの結果 = "県"</b>」の計算結果は以下の通りになりました。
前述の通り、Trueは1、Falseは0になるため、「<b>RIGHTの結果 = "県"</b>」の条件に一致しないデータはカウントから除外されるという仕組みです。


|データ|<>"" ÷ COUNTIFSの結果|RIGHTの結果 = "県"|最終結果|
|---|---|---|---|
|東京都|0.5|False|<b>0</b>|
|千葉県|0.5|True|<b>0.5</b>|
|（空白）|0|False|<b>0</b>|
|千葉県|0.5|True|<b>0.5</b>|
|神奈川県|1|True|<b>1</b>|
|東京都|0.5|False|<b>0</b>|

###### Step 12
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578020/389d1208-eb43-3a1f-1a9c-39f0f25a008c.png)
最後に、SUMPRODUCTで今まで計算した値が合計されます。
0 + 0.5 + 0 + 0.5 + 1 + 0 = <b>2</b>となり、これで空白と重複を除き、かつ、「一番右の文字が"県"」の条件に一致するデータの件数をカウントできました。

|データ|最終結果|
|---|---|
|東京都|0|
|千葉県|0.5|
|（空白）|0|
|千葉県|0.5|
|神奈川県|1|
|東京都|0|
|||
|<b>合計</b>|<b>2</b>|


## 動作環境
- Microsoft Excel 2013

※Microsoft Excel 2024でも動作することを確認していますが、冒頭にも記載した通り、2021以降のバージョンであればよりシンプルに数式を書けますので、そちらの記事をご参照ください。

https://qiita.com/Davoi/items/afc93ac7292cec69c1cf