エクセルで特定の文字列を含む値を重複を除いて数える

いつもお世話になります。
今回の質問ですが、例えば、セルA1からA1000に、いろんな人の名前がフルネームで入っているとします。
同じ人物が複数回登場することはありますが、同姓同名の人はいないものと考えてください。
そして、このリストの中に、「山田」さんは何名いるのかを、同人物の重複を除いて数えたいわけです。

重複して数えていいならば、「=COUNTIF(A1:A1000,"山田"&"*")」で、
山田さんに限らずに、重複を除いた人数を調べたいならば、「=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))」でそれぞれ調べられますが、山田さんの重複を除いた人数となると、どうすればよいでしょうか。

私に考えつく方法では、B1に「=COUNTIF(A1,"山田"&"*")」と入力し、以下B列にこれをコピーをしておけば、「=SUMPRODUCT((B1:B1000)/COUNTIF(A1:A1000,A1:A1000))」で求めたい値を得られますが、できれば作業列は使用しないでこの値を求めたいです。

No.2 ベストアンサー 回答者： Chiquilin 回答日時： 2014/10/29 09:55

FINDは論外です。

「森」さんの時「大森」さんを数えてしまいます。
というか数式自体が煩雑だし OFFSETは計算を重くするだけで必要
すらありません。

=COUNT(1/(MATCH(A1:A1000,A1:A1000,0)=ROW(A1:A1000))/(LEFT(A1:A1000,2)="山田"))
Ctrl + Shift + Enterで確定

LEFTは必要なら SEARCHと ISNUMBERの組合せに変更。

> できれば作業列は使用しないでこの値を求めたいです。
無意味なこだわりだと思います。

この回答へのお礼

お二人ともありがとうございます。助かりました。

お礼日時：2014/11/03 01:36

No.1 回答者： keithin 回答日時： 2014/10/29 01:26

たとえば

=SUMPRODUCT(ISNUMBER(FIND("山田",IF(FREQUENCY(MATCH(OFFSET(A1,,,COUNTA(A:A),1),A:A,0),MATCH(OFFSET(A1,,,COUNTA(A:A),1),A:A,0)),OFFSET(A1,,,COUNTA(A:A),1),"")))*1)



＞できれば作業列は使用しないでこの値を求めたい

無駄な努力です。