一文の中に名簿中の名前がいくつあるか

質問

WINDOWS７、エクセル２００３です。
　　　　　　　　　　　　Ａ　　　　　　　　　　　　　　　　Ｂ　　　　　Ｃ　　　　　 Ｄ　　　　　Ｅ　　　
１　　山田、伊東風邪。山本ノロウィルス。 青田　　　　　３　
2　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　山田
3　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　上田
4　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　伊東
5　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　山本

上の表のように、Ａ列には短文が入っていて、何人かの名前が記入されています（文があっても名前なしの時もあります）。
Ｄ列には、最大30人の名前が記入されています。
Ａ列の短文の中に、Ｄ列の名前が何人出現しているか、人数をＥ列に返したい。

朝から調べましたが分かりませんでした。SUMPRODUCTとかCOUNTIFを使うのだろうとは見当がつきましたがよく分かりません。
よろしくお願いします。

回答No.4

MarcoRossiItaly

既出の ISNUMBER(FIND) の代わりとして COUNTIF 関数を入れることも可能です。

E1　=sumproduct(countif(a1,"*"&D$2:D$31&"*")*(D$2:D$31<>""))

もしも同じ人物が 1 文の中に複数回出てくるという場合、上式や既出の回答の数式では、同じ人物を 1 人として数えます。次式は、延べ人数を求めます。

E1　=sumproduct((len(a1)-len(substitute(a1,D$2:D$5,)))/(len(D$2:D$5)+(D$2:D$5="")))

お礼

MarcoRossiItaly　様

お礼が遅くなりました。
画像付きで、しかも同一人物が複数回出た時に1人とカウントする場合と延べでカウントする場合と両方教えて頂きました。
役に立ちます。
ありがとうございました。

回答No.3

keithin

SUMPRODUCTはイマイチどうでもよくて、中で何を計算するかでやり口は様々あります。
=SUMPRODUCT(COUNTIF(A1,"*"&D1:D30&"*")*(D1:D30<>""))
とか
=SUMPRODUCT(1*(SUBSTITUTE(A1,D1:D30,"")<>A1))
とか
=COUNT(0/FIND(IF(D1:D30<>"",D1:D30),A1))　をCtrl+Shift+Enter
とか

＞式の理解に苦しみましたが、本やネットを動員してなんとかわかってきました。

ツールメニューのワークシート分析の中にある「数式の検証」という機能を利用します。
ただしこれは、まず最初は「正しく動作する事が分かっているお手本の数式」を理解するのに使います。
計算ステップが「どうしてこういう具合に進むのか、なぜそうなるのか」正しく理解できたら、次のステップとして新しい数式を工夫するのにも利用する事ができるようになります。

回答No.2

bunjii

>SUMPRODUCTとかCOUNTIFを使うのだろうとは見当がつきましたがよく分かりません。
SUMPRODUCT関数については既に回答が出ていますので割愛します。

COUNTIF関数では条件を設定することが困難となります。
代わりにIF関数で配列値処理すればCOUNT関数で計数できます。

=COUNT(IF(FIND(B2:B31,A1)*ISTEXT(B2:B31),1,""))

また、SUM関数では配列値を工夫して計数できます。

=SUM(NOT(ISERR(FIND(B2:B31,A1)))*ISTEXT(B2:B31))

上記の式は配列値を扱いますので数式バーへ入力後、Ctrl+Shift+Enterで確定してください。

お礼

bunjii様
ありがとうございました。
一つの目的に対して、いろいろな解決法があることがわかりました。
式の理解に時間がかかりましたが、なんとかわかってきました。
勉強になりました。

回答No.1

keithin

=SUMPRODUCT(ISNUMBER(FIND(D2:D31,A1))*(D2:D31<>""))
みたいな。

お礼

keithin様
ありがとうございました。
さっそく入力したところ、見事に解決してすっきりしました。
もっとも、式の理解に苦しみましたが、本やネットを動員してなんとかわかってきました。