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

質問

いつもお世話になります。
今回の質問ですが、例えば、セル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 · ベストアンサー

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

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

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

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

お礼

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

回答No.1

keithin

たとえば
=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)

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

無駄な努力です。