オートフィルタ後のデータ集計について

質問

Excel2007を使用しています。

A列：業者コード
B列：業者名称
C～G列：業務種別（委託可能=1、委託不可=0のフラグ）
H列：本店/支店フラグ
I列：住所

というリストがあります。
A～G列は業者の基本情報、H～I列が所在地情報になります。

例えば某業者が本店を東京に構え、支店を東京、埼玉、神奈川に持っているとした場合、同一の基本情報＋各店舗の情報が続いたレコードという構成になるため、所在地情報と同数の4レコードが存在することになります。

このようなリストに対し、以下のような集計をセルの埋め込み関数のみで行いたいのですが、どのように集計したら実現できるのでしょうか？

・複数の項目に対し、オートフィルタによるフィルタリングを行う
・フィルタリング後のレコードから、業者数を求める

具体例を挙げると、以下のようになります。

----
・業務種別１及び３を請け負える業者
　→C列及びE列両方に対し、"1"（請負可能）でフィルタ
・東京、埼玉、神奈川のいずれかに営業拠点を持つ業者
　→I列に対し"東京""埼玉""神奈川"のいずれかを含む条件でフィルタ

結果のリストに業者が何社含まれているのかを特定のセルに表示させる
（同名の企業を誤ってカウントしないため、A列の業者コードから業者数をカウントする）
----

SUMPRODUCTとCOUNTIFを組み合わせる方法で試してみましたが、フィルタで隠れている行まで集計の対象となってしまい、正確な業者数を取得する事ができません。
フィルタリング後のレコードに対してのみ、重複をのぞいた形で業者数をカウントする方法を教えてください。

Excel2007で無理ならば、Excel2003でも構いません。
また、両方で可能だけど式は変わってくるよ、という事であれば両方教えていただけると助かります。

よろしくお願いします。

回答No.3

keithin · ベストアンサー

99行までデータがあるとして
=COUNT(1/FREQUENCY(MATCH(IF(SUBTOTAL(3,INDIRECT("A"&ROW(A1:A100))),A1:A100,"")&"",A1:A100&"",0),MATCH(IF(SUBTOTAL(3,INDIRECT("A"&ROW(A1:A100))),A1:A100,"")&"",A1:A100&"",0)))-2
をCtrl+Shift+Enterで入力してみます。

お礼

回答ありがとうございます。

次のようなデータに対し、ご回答いただいた式のＡ100をＡ32に修正したもので集計してみました。

コード,社名,業務1,業務2,業務3,業務4,業務5,住所
100,A社,1,1,1,0,1,埼玉
100,A社,1,1,1,0,1,東京
100,A社,1,1,1,0,1,神奈川
100,A社,1,1,1,0,1,千葉
100,A社,1,1,1,0,1,栃木
200,B社,1,0,1,1,0,東京
200,B社,1,0,1,1,0,神奈川
200,B社,1,0,1,1,0,群馬
300,C社,0,1,1,1,1,千葉
300,C社,0,1,1,1,1,栃木
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京
300,C社,0,1,1,1,1,東京

どこの項目でフィルタを掛けても、必ず"3"が返ってきてしまいます。
上記のデータの場合、例えば住所の列に対して「東京」のフィルタを掛けたときの結果は"3"で正しいのですが、
これを「埼玉」とした場合の結果は"1"である必要があります。
「千葉」ならば"2"です。

回答No.4

keithin

ご説明したことをやらずに出来ないと言われて，再度回答するのは大変に苦痛です。

再掲：
＞をCtrl+Shift+Enterで入力してみます。

という手順を行っていません。

お礼

ありがとうございました。
できました。

苦痛を感じさせてしまい申し訳ありませんでしたが、やらずにいたのではなく、
見落としてしまい気づいていなかっただけです。

回答No.2

KURUMITO

A列で表示されている業者コードの数を求めるのでしたら

=SUBTOTAL(3,A２:A１０００)などの式で

お礼

回答ありがとうございます。
ですが、これですと業者コードの列に対してSUBTOTALを使った場合、
業者数ではなくレコード数が求められてしまいました。

集計したいのは「業者数」ですので、レコードとは少々異なります。

例えばフィルタの結果、同じ業者の別拠点のデータが残った場合、
それが２レコードであれば、ほしい数値は２ではく１になります。

回答No.1

KURUMITO

オートフィルタなどで絞り込んだデータを対象に計算するのでしたらSUBTOTAL関数を使います。
例えばB２セルからB１００セルのうちでオートフィルタを実施した場合の集計は次のような式になります。

=SUBTOTAL(9,B2:B100)

参考になりましたら

お礼

回答ありがとうございます。
ですが、頂いた回答ですと業者数ではなく業者コードの合計が求められてしまいました。
集計したいのは「業者数」ですので、業者コードを合計してしまっては求めたい値と違います。