EXCEL2007の文字列検索方法で困っています。

質問

EXCEL2007の文字列検索方法で困っています。

ひとつのセルに複数の全角、または半角文字列が「、(カンマ)」で区切って入力されています。

例、足利商事、織田商店、徳川物産など

これとは別紙に対象先となるリストがあって

例、

明治商事
徳川物産
大正商事
平成物産・・・

ひとつのセルに、対象となるリスト内にある企業名があったら「1」カウントアップ、「2」
あったら、2というように該当件数を横に表示したいのですが、適当な関数が見つかりません。

なにか、良い方法はありますでしょうか。よろしくお願いします。

回答No.7

KURUMITO · ベストアンサー

Ｂ1セルには次の式を入力してください。

=IF(A1="","",SUM(IF(ISERROR(FIND(INDIRECT("Sheet2!A1:A"&COUNTA(Sheet2!A:A)),A1)),0,1)))

その式を入力した最後の部分では・・・・0,1)))の後にカーソルが点滅している状態になりますね。通常はそこでＥｎｔｅｒキーを押すことでセルへの式の入力が完了しますが、この場合には単にＥｎｔｅｒキーを押すのではなく、キーボード上のＳｈｉｆｔキーとＣｔｒｌキーを同時に押しながらＥｎｔｅｒキーを押してください。すると数式バーに表示される式の形が前の部分と後ろの部分にカッコが付いた状態に変わります。
先に示したカッコの付いた状態の式をそのままコピーしてＢ1セルに入力しても全く意味がありません。

補足

KURUMITO様、できました！ありがとうございます。

なんで今までできなかつたのか、判りませんが今朝指示された通りにしたら、
意図通りの答えが表示されました。何度も何度も、ご丁寧な回答ありがとうございました。

本当にありがとうございました。

回答No.6

MackyNo1

>「配列数式」という言葉は何年もＥＸＣＥＬは操作していますが初めて聞きました。この機能は私のＥＸＣＥＬでは機能しない？機能かも知れません。

そんなことはありません。

Ｎｏ３でも回答したように、数式を貼り付けた後、カーソルが数式の最後にある状態でＣｔｒlとＳｈｉｆｔキーを押しながら、Ｅｎｔｅｒキーを押してみてください。

ちなみに、この操作を行わないでも、そのまま計算できる数式はＮｏ３ですでに回答した通りです。

配列数式を覚えると、複雑な計算を簡単な数式で計算できるなど、数式の利用範囲が大きく変わりますので、以下のサイトを参考にして勉強されると良いと思います。

http://pc.nikkeibp.co.jp/pc21/special/hr/

お礼

MachyNo1様、ご回答ありがとうございました。

ＥＸＣＥＬも2.1の頃から使っていますが、まだまだ「奥」が深いと思いました。

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

回答No.5

KURUMITO

シート1のＢ1セルに次の式を入力し、確定するに当たって通常はＥｎｔｅｒキーのみを押せばよいのですが、この式を配列数式とするためにＣｔｒｌキーとＳｈｉｆｔキーを押しながらＥｎｔｅｒキーを押します。

=IF(A1="","",SUM(IF(ISERROR(FIND(INDIRECT("Sheet2!A1:A"&COUNTA(Sheet2!A:A)),A1)),0,1)))

上記の操作をすることで式は次のようになります。

{=IF(A1="","",SUM(IF(ISERROR(FIND(INDIRECT("Sheet2!A1:A"&COUNTA(Sheet2!A:A)),A1)),0,1)))}

つまり両側に{　}の付いた状態の式になります。

補足

KURUMITO様、本当に何度もご回答ありがとうごさいます。

今回は返事された内容の［　］付きの部分をコピーしましたが、先頭が「＝」で始まっていないため、
ＥＸＣＥＬ2007が数式という判断をせず、式がそのまま・・・

{=IF(A1="","",SUM(IF(ISERROR(FIND(INDIRECT("Sheet2!A1:A"&COUNTA(Sheet2!A:A)),A1)),0,1)))}

と表示されるだけです。

「配列数式」という言葉は何年もＥＸＣＥＬは操作していますが初めて聞きました。この機能は私のＥＸＣＥＬでは機能しない？機能かも知れません。何回も、色々とお手数をかけて申し訳ありませんでした。

私の能力ではこれ以上の結果は得られないと思いますので、諦めます。

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

回答No.4

KURUMITO

回答Ｎｏ1，2です。
補足の内容ではシート2のセルに式を入力しておられるようですが私の文章をよく読まれているのでしょうか？　式を入力するのはシート1のＢ1セルです。シート2のＢ1セルに式を入力すれば下方にオートフィルドラッグしてもシート2のＡ列には明治商事、徳川物産などがそれぞれ単独で連続したセルに入力されているのですから、1が答えとなるのは当然です。
式はシート1のＢ1セルに入力します。

補足

KURUMITO様、本当に何度もご回答ありがとうごさいます。

仰る通り、式を入力していたシートは「シート2のB1セル」でした。ただ、式をシート1のB1セルに
コピーし直しても結果は下記のようになります。・・・

シート1
A1 B1
安藤産業、豊臣物産、徳川商事 1
三井産業、石田物産、織田商店 0
柳生産業、徳川商事 0

念のため、B1に入力されている式もコピーしました。

=IF(A1="","",SUM(IF(ISERROR(FIND(INDIRECT("Sheet2!A1:A"&COUNTA(Sheet2!A:A)),A1)),0,1)))

シート2
　　A1
徳川商事
豊臣物産
石田産業
織田商店
柳生産業

どうしてでしょう？、それと式をご回答からコピーする際に「Ctrl+Shift+Enter」の意味が
わかりません。指示通り、やってはいますが普通に貼り付けされるだけで、他の方が回答されて
いるような[ ]では囲まれません。

よろしくお願いします。

回答No.3

MackyNo1

明治商事と大明治商事のような名前がなければ、No1の回答で問題なく表示できると思いますが、配列数式の入力の仕方がわからないということでしょうか？

配列数式にするには、数式入力後（数式の末尾にカーソルがある状態で）、CtrlキーとShiftキーを押しながらEnterキーを押します（数式が「｛」と「｝」で囲まれる）。

Ctrl+Shift+Enterしないでも計算できる配列数式にするなら、例えば以下のようなCOUNTIF関数を使った数式でも計算できます。

=SUMPRODUCT(COUNTIF(A1,"*"&INDIRECT("Sheet2!A1:A"&COUNTA(Sheet2!A:A))&"*"))

回答No.2

KURUMITO

回答Ｎｏ1です。
あなたが補足で説明されたと同じ内容であると解釈してＮｏ1の答えを書いています。
シート1のＡ1セルに足利商事、織田商店、徳川物産などがカンマ付きでいくつか入力されており、その中にシー2のＡ列に示した文字列が幾つ含まれているかを求める式です。
シート1のＢ1セルに回答1の式を入力し、確定するに当たってはShifキーおよびCtrlキーを押しながらEnterキーを押してください。その後にＢ1セルの式を下方にオートフィルドラッグすればよいでしょう。
こちらが提案された方法を試験されたのでしょうか？その結果がどうなったかなどを追加で質問してください。

補足

KURUMITO様、何度もご回答ありがとうごさいます。

結論から言って、仰ってる方法をやってみましたが、シート2のB1(データが集計されるところ)には、すべて「1」が表示されます。私のイメージではシート1のB1以降にシート2の内容と同じものがあれば、
その個数が表示されるように考えていました。

やはり、無理でしょうか。お手数掛けてすいません。

回答No.1

KURUMITO

シート1のＡ1セルから下方に一つのセルに足利商事、織田商店、徳川物産などの文字が入っているセルが並んでいるとすます。
シート2のＡ1セルから下方に明治商事、徳川物産などの文字が一つずつのセルに入っているとします。行の間に空白の行は無いものとします。
そこでシート1のＢ1セルには次の式を入力し式を確定する際にはCtrl+Shift+Enterキーを同時に押します。

=IF(A1="","",SUM(IF(ISERROR(FIND(INDIRECT("Sheet2!A1:A"&COUNTA(Sheet2!A:A)),A1)),0,1)))

補足

問題の説明がいまいち、上手ではないので、もう一度整理します。

ひとつのセルに複数の全角、または半角文字列が「、(カンマ)」で区切って入力されています。

シートA
　　 ___________A列 ______________ B C D E 　 ・・・・・・　
例、 1 |足利商事、織田商店、徳川物産|　　n件
____________________________

これとは別シートに対象先となるリストがあって

例、

シートB
　　　　　　A列 　　　　　B列　　　　C列
　　1行　明治商事
　　2　　徳川物産
　　3　　大正商事
　　4　　平成物産
　　　　　　・
　　　　　　・
　　　　　　・

上図でシートAのA1のセル内容の中にA1「明治商事」からA4「平成物産」までのうち、シートBの
リスト内にある企業名があったらシートAのB1を1カウントアップ、2つあったら、2というように該当件数を横に表示したいのですが、適当なEXCEL関数または式が判りません。

なにか、良い方法はありますでしょうか。よろしくお願いします。