EXCELで特定の範囲に存在する文字列をカウントする関数

質問

EXCELで特定の範囲に存在する文字列をカウントする関数は=COUNTA()で良いのですが、佐藤、鈴木、鈴木、佐藤、遠藤　と存在した場合に　5ではなく、3とカウントする関数はご存知ないでしょうか？

回答No.4

nishi6 · ベストアンサー

A1～A5に、佐藤、鈴木、鈴木、佐藤、遠藤と入力されているとします。

　=SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5))

で重複を除いた件数が計算できます。

補足

理想的ご回答と期待したのですが・・・
COUNTIF(A1:A5,A1:A5)の段階で演算結果が0となってしまい。
=SUMRODUCT(1/0)　で エラーとなってしまいます。　
何か違っていますでしょうか？

回答No.7

imogasi

# ５の解答者です。お礼を頂き有難うございます。私も相当苦労しましたので、意味が難しいとおっしゃっているので、少し
補足します。B列とC列についてです。
（１）DCOUNTA(範囲）は範囲セル内で佐藤が何人いるか数えます。３人いるからB列３です。A2の佐藤（自分）以外は３－２
＝２人下行にいる＝ダブりあり、と言うことです。C列で－１
しているのは自分の分を引いているわけです。A3の鈴木では
佐藤＋鈴木（探索する苗字にプラス＝加わることがミソ）が範囲内に６人いると出ているわけです。B2より
３増えたのですが、それは鈴木が３人いるということで、A3の
鈴木を除くと、あと３－２＝２人下行にいると言うことになります。数が変化しない行がありますが（例A5の佐藤）、これは
既に前行で佐藤が出てきて（A2に）、自分の名前（A5の佐藤）
が増えても、表の中の佐藤＋鈴木＋山内の数は変わらないことを意味します。こう言う風にして、ダブリ数をD列に出し、最後にD13で合計しています。後は全行数からダブリで増えた行数（D１３)を引けば、ダブリ除きの行数が出ます。

お礼

ありがとうございます。
すごい力作と思います。今回はシートがいくつもあって、ちょっと大変な作業になりそうなので別な方法にしました。この関数も非常に参考になり有用と思います。また何かの機会に=DCOUNTA()も利用させて頂きます。ご協力ありがとうございます。

回答No.6

nishi6

＞COUNTIF(A1:A5,A1:A5)の段階で演算結果が0となってしまい。
＞=SUMRODUCT(1/0)　で エラーとなってしまいます。　
＞何か違っていますでしょうか？　

=SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5))
は１つの式です。２つのセルに分解すると、機能しません。

お礼

失礼しました。
ただ、範囲内に文字列がすべて埋まっているとよいのですが、途中で空白のセルがあると#DIV/0 のエラーになるようです。でも、これはすごいですね。セル選択で一度空白セルのみ選択してからスペースでも代入しておけば何とかなりそうです。配列をつかっていると思うのですが、これも理解を超えてしまいました。でもすぐに使えそうです。ありがとうございます。

回答No.5

imogasi

（１）データとしてA2からA12まで名前を入れる。A1名前、B1に０を入れる。
（２）B2に式=DCOUNTA($A$1:$A$12,$A$1,$A$1:A2)をいれる。
最下行が$A$12のところに来る。最下行まで複写する。
（３）C2に式=IF(B2>B1,B2-B1-1,0)をいれる。最下行まで複写
する。
（４）結果は下記の通りとなる。
（５）C13に=SUM(C2:C12)をいれる。
（６）B13に最終行－１をいれる。
（７）D13に=B13-C13をいれる。D13の６が求めるものである。
佐藤、鈴木、山内、山田、大木、大久保の６名。
C列には重複いている名前の数を求めている。
氏名 0
佐藤 3 2
鈴木 6 2
山内 8 1
佐藤 8 0
鈴木 8 0
佐藤 8 0
山田 9 0
大木 10 0
山内 10 0
鈴木 10 0
大久保 11 0

お礼

B13に最終行の１１を参照しＤ１３の５を引くと６となり、重複を除いたカウントができるようです。計算式が複雑で小生の理解を超えてますが、これでなんとか行けそうです。ありがとうございます。

回答No.3

papayuka

再びこんにちは。

もうひとつ。
先頭にタイトル行を入れ、フィルタと　=SUBTOTAL　を使う。

例）
タイトル行を含み、A1～A6にデータがあるとして、

=SUBTOTAL(3,A2:A6)

とA7にでも入れ、データ－フィルタ－フィルタオプションで「重複するレコードは無視」でフィルタを掛ける。

お礼

subtotal(3,___)は知ってましたが、フィルタオプションで「重複するレコードは無視」は知りませんでした。非常に便利ですね。これも使えます。ありがとうございます。

回答No.2

papayuka

こんにちは。

A1からデータがあるとして、ソートして良いなら、隣りのセルにでも

=IF(A1<>A2,1,0)

といれて、データ範囲までコピー、それを集計する。

ソートしたくないなら、予めインデックスを振っておいて、上記関数をいれ、結果を値に変えてからインデックスでソートし直すとか。。

回答No.1

Good-S15

こんばんは。

COUNTIF関数で、検索条件を「*藤」とかでもダメですかね？
ダメならば、VBAでMID関数やLEFT関数やRIGHT関数を使用しないとできないのでは？

お礼

想定される名前が不特定なため、今回のものにcountif関数は使用できないようです。ご回答ありがとうございます。