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

久しぶりにエクセルで悪戦苦闘したのでメモ。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() ¹ は、フィルタで活用できる集計関数である。
例えば任意のセルに、

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

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

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

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

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

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

=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列値だけが合算され、結果、該当支店数を重複なしで得ることができる。⁴

##数値フィルタ適用時に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()は、列に対しての表示・非表示の条件は無視しますので計算結果に影響はありません。

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

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

2010以降では、この関数の上位の集計関数としてaggregate()がある。 ↩
第1引数が集計方法の定数で、第2引数が範囲。定数の101以上は、非表示のものは集計対象から除外する、という条件を持つ。詳しくは他のドキュメントや検索結果など参照のこと。 ↩
表の編集の際は、適宜かけたフィルタの取り外しと再適用をして欲しい。 ↩
もしかしたら、環境によっては(?)、round()などで小数点切り上げを行った方がよいかもしれない。 ↩