エクセルでフィルターをかけたセルを重複を除いて都度計算でカウントする方法

#エクセルでフィルターをかけたセルで重複を除いてカウントする方法

久しぶりにエクセルで悪戦苦闘したのでメモ。`subtotal()`関数の応用例。SQLだと簡単なんでしょうけどね。
Excel 2007で試しましたが、他のバージョンでも可能でしょう。

##フィルター適用時にsubtotal()で集計処理をする

例えば次のようなフィルタ済みのExcelデータを考える。

*（サンプル表： キャンペーン対象商品販売実績）*

|     |  [A]   |  [B]   |  [C]   |  [D]   |   [E]   |    [F]    |
|:----|:-------|:-------|:-------|-------:|--------:|----------:|
|[1]  |支店名▼|エリア▼|商品▼  |単価▼  |販売個数▼ |     売上▼|
|[2]  |A       |東北    |100     |  ¥3,000|      342| ¥1,026,000|
|[3]  |A       |東北    |101     | ¥30,000|       12|   ¥360,000|
|[4]  |A       |東北    |102     | ¥12,000|       65|   ¥780,000|
|[5]  |B       |関東    |101     | ¥30,000|      123| ¥3,690,000|
|[6]  |B       |関東    |102     | ¥12,000|       45|   ¥540,000|
|[7]  |C       |東京    |100     |  ¥3,000|      601| ¥1,803,000|
|[8]  |D       |関東    |100     |  ¥3,000|       32|    ¥96,000|
|[9]  |D       |関東    |102     | ¥12,000|       43|   ¥516,000|

エクセルの関数`subtotal()` [^1] は、フィルタで活用できる集計関数である。
例えば任意のセルに、

```
=subtotal(109,$E$2:$E$9)
```

と数式を入れ [^2]、フィルタで*エリア*を絞り込むと、表示されているエリアの*販売個数*のみを合算した値が得られる。




##フィルター適用時にsubtotal()で対象の種類を重複を除いてカウントする

サンプル表中で、例えば関東エリアの支店数はいくつあるか、を求める。
単純にA列に対する計算として、

```
=subtotal(103,$A$2:$A$9)    # 103 は非表示行を無視したcounta()の実行
```

とすると、重複を含めたカウントをしてしまうので、*関東*でフィルタをすると、4、となる。
こういう場合はダミー列を新たに作って、`countif()` を活用すると良いらしい。次のような計算式を新たに挿入したB列に入れ、オートフィルする [^3]。

```
=1/COUNTIF(A$2:A$9,A2)    # A2の部分は、コピペやオートフィルなどで適宜変わる。以下同様。
```

そうすると、次のようなデータになる。

|     |  [A]   |  [B]   |  [C]   |  [D]   |   [E]  |   [F]   |     [G]   |
|:----|:-------|-------:|:-------|:-------|-------:|--------:|----------:|
|[1]  |支店名▼|        |エリア▼|商品▼  |単価▼  |販売数▼ |     売上▼|
|[2]  |A       |0.333333|東北    |100     |  ¥3,000|      342| ¥1,026,000|
|[3]  |A       |0.333333|東北    |101     | ¥30,000|       12|   ¥360,000|
|[4]  |A       |0.333333|東北    |102     | ¥12,000|       65|   ¥780,000|
|[5]  |B       |     0.5|関東    |101     | ¥30,000|      123| ¥3,690,000|
|[6]  |B       |     0.5|関東    |102     | ¥12,000|       45|   ¥540,000|
|[7]  |C       |       1|東京    |100     |  ¥3,000|      601| ¥1,803,000|
|[8]  |D       |     0.5|関東    |100     |  ¥3,000|       32|    ¥96,000|
|[9]  |D       |     0.5|関東    |102     | ¥12,000|       43|   ¥516,000|

このB列の計算結果を対象に、前回同様任意のセルに、

```
=subtotal(109,$B$2:$B$9)
```

と入力し、*エリア*を絞り込むと、該当エリアのB列値だけが合算され、結果、該当支店数を重複なしで得ることができる。[^4]




##数値フィルタ適用時にsubtotal()で対象の種類を重複を除いてカウントする

売上げが100万円以上の支店はいくつあるか、を得てみたい。

上の表のままで*売上*の数値フィルターを*1,000,000以上*と絞り込んでしまうとどうだろう。残念だが求める結果は得られない。このロジックでは、**該当の支店の行は全て表示されているか、または全て表示されていないか**、のいずれかの場合のみ正しい値が得られるためである。

B列の計算がフィルタの表示・非表示切り替えごとに、表示されているものを対象にして都度計算されれば良いはずだ。
次のように工夫してみる。

A列B列の間に列を挿入し（新B列）、この対象セルに次の計算式を入れ、オートフィルで埋める。

```
=if(subtotal(103,A2),A2,"")
```

次に、旧B列で、現在C列となっている列の対象セルに、次の計算式を入れ（変更）、オートフィルで埋める。

```
=1/countif(B$2:B$9,B2)
```

そうすると、表は次のようになる。

|     |  [A]   |  [B]   |  [C]   |  [D]   |  [E]   |   [F]  |   [G]   |     [H]   |
|:----|:-------|:-------|-------:|:-------|:-------|:-------|:--------|:----------|
|[1]  |支店名▼|        |        |エリア▼|商品▼  |単価▼  |販売数▼ |     売上▼|
|[2]  |A       |A       |0.333333|東北    |100     |  ¥3,000|      342| ¥1,026,000|
|[3]  |A       |A       |0.333333|東北    |101     | ¥30,000|       12|   ¥360,000|
|[4]  |A       |A       |0.333333|東北    |102     | ¥12,000|       65|   ¥780,000|
|[5]  |B       |B       |     0.5|関東    |101     | ¥30,000|      123| ¥3,690,000|
|[6]  |B       |B       |     0.5|関東    |102     | ¥12,000|       45|   ¥540,000|
|[7]  |C       |C       |       1|東京    |100     |  ¥3,000|      601| ¥1,803,000|
|[8]  |D       |D       |     0.5|関東    |100     |  ¥3,000|       32|    ¥96,000|
|[9]  |D       |D       |     0.5|関東    |102     | ¥12,000|       43|   ¥516,000|

最後に任意のセルに、該当支店数を求める式、

```
=subtotal(109,$C$2:$C$9)
```

を入力する。

B列は、表示されている時はA列がコピーされるが、表示されていないときには空文字列になる。C列はそれを元にして計算用の値を出す、といった具合。
以上の状態で、*売上* に対して*1,000,000以上* と数値フィルタをかけると、C列が計算しなおされ、該当する支店数が計算される。

（以上）



# おわりに

エクセルの関数で表示・非表示を判断できる関数はあるのかな、と調べてみたものの、結局 `subtotal()` しかそういった機能は持ち合わせていないようでした。なので、これをもう一段階使っている、というのがミソでしょうか。
なお、計算で用いた列は非表示にして構いません。`subtotal()`は、列に対しての表示・非表示の条件は無視しますので計算結果に影響はありません。


もしかしてもっとうまい方法があるのかもしれませんが、悪しからず。

大量のデータの場合は、すごく計算がかかりますので、若干注意を。


-----

[^1]: 2010以降では、この関数の上位の集計関数として`aggregate()`がある。
[^2]: 第1引数が集計方法の定数で、第2引数が範囲。定数の101以上は、非表示のものは集計対象から除外する、という条件を持つ。詳しくは[他のドキュメント](https://support.office.com/ja-JP/article/SUBTOTAL-%E9%96%A2%E6%95%B0-7b027003-f060-4ade-9040-e478765b9939)や検索結果など参照のこと。
[^3]: 表の編集の際は、適宜かけたフィルタの取り外しと再適用をして欲しい。
[^4]: もしかしたら、環境によっては(?)、`round()`などで小数点切り上げを行った方がよいかもしれない。