エクセルでフィルターをかけたセルで重複を除いてカウントする方法
久しぶりにエクセルで悪戦苦闘したのでメモ。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()は、列に対しての表示・非表示の条件は無視しますので計算結果に影響はありません。
もしかしてもっとうまい方法があるのかもしれませんが、悪しからず。
大量のデータの場合は、すごく計算がかかりますので、若干注意を。