エクセル関数Subtotalを使用して、グループ数をカウントするには

質問

A　　　　B
　　グループ　担当
１　２１　　　B
２　２１　　　B
３　１８　　　A
４　２５　　　C　
５　２５　　　C
６　　４　　　B
７　　２　　　A
８　　５　　　A
というようなデータがあります。
フィルタを使用して、subtotal関数等を使用して担当別グループ数を
A9に表示したいのです。
たとえば、担当Bをフィルタで選ぶと、A9にはグループ数２．
担当Aをフィルタで選ぶと、A9にはグループ数３という感じにしたいのです。
SubtotalとCount等の関数を組み合わせるとできるのでしょうか？
どうぞ教えてください。

回答No.3

keithin

アイデア次第で簡単な方法は色々あります。

添付図では
A2に　=IF(MATCH(B2,B:B,0)=ROW(),COUNT($A$1:A1)+1,"")　を以下コピー
B10に　=SUBTOTAL(2,A2:A9)
としてみました。

回答No.2

noname#204879

添付図参照
D2: =IF(B2=D$1,A2,"")
E2: =IF(D2="","",1/COUNTIF(D$2:D$9,D2))
E1: =SUM(E2:E9)

セル E1 を他の担当者名（ここでは A）に変更したりしてみてください。

回答No.1

MackyNo1

SUBTOTAL関数では重複のないデータの組み合わせの数を計算することができません。

フィルタ機能で対応する場合は、たとえばD1：E1セルに項目名の「グループ」と「担当」をコピーしておき、担当の下のE1セルに検索対象の文字列（たとえばB）を入力して、「データ」「フィルタ」「フィルタオプションの設定」で検索条件範囲に「D1:E2」を指定し「重複するデータは無視する」にチェックをいれ「OK」します。

これで重複のないデータが抽出できますので、SUBTOTAL関数で個数をカウントしてください。

ちなみに、この操作を新しいマクロの記録で記録しておけば、ボタン一発で自動的にセルに入力した（入力規則でドロップダウンリストから選択するとよい）検索条件のデータの個数を表示することができます。

ちなみに、行の最後に集計結果を表示したいなら、SUBTOTAL関数は1行空白行を開けた次の行に入力するようにしてください（必要に応じて空白行は非表示にする）。

この集計を関数で実行したい場合は以下のような配列数式を利用することになります。

たとえばD2セル以下に担当が入力されている場合、E2セルに以下の式を入力してCtrl+Shift+Enterで確定して配列数式にしてから下方向にオートフィルします。

=COUNT(1/(MATCH(D2&$A$1:$A$100,$B$1:$B$100&$A$1:$A$100,)=ROW($B$1:$B$100)))

ちなみに、D2セル以下の重複のない担当の一覧も数式だけで対応することができますが、配列数式は表示データ数が多くなるとシートの動きが重くなるので、目的に応じてフィルタオプションなどの方法と適宜使い分けるようにしてください。