Excelの関数で、検索条件（複数）を満たす場合値を返す方法について質

質問

Excelの関数で、検索条件（複数）を満たす場合値を返す方法について質問です。

セルA２にある文字列が、別ファイルのA列にある文字列を含む場合セルB２に○を、含まない場合は×を入力する関数を教えて下さい！！

---------
book1.xls シート１ A２にある文字列が、
book2.xls シート１ A列にある文字列のいずれかを含む場合、
book1.xls シート１ B２に「○」を、含まない場合は「×」を結果として反映させたいです。
※book1.xlsのリストは全角カナ、book2.xlsのリストは半角カナ です。
※book2.xls シート１にある検索条件は行列共に増える可能性があります。

マクロやVBAではなく関数で行うことは可能でしょうか？

勉強不足ですみません。
色々調べて試しているのですが、なかなかうまくいかないのでお力をお貸し下さい。

よろしくお願いします。

回答No.8

tom04 · ベストアンサー

No.6です！
たびたびごめんなさい。

今後Book2の行・列ともに増える可能性があります！の件に対応できるように
前回の方法を少し発展してみました。
もう一度画像をアップします。
Book2のデータにまず、入力することがない！という「＃」を入力し、それを数式の範囲に指定します。
（このデータ量は適宜変更してみてください。）
そして数式に入力セルすべてを範囲指定しておき、「＃」が入力してあるセルに
増えていくデータを入力するとBook1に反映されると思います。

数式は前回と同じですが、Booi1のC2セルに
=IF(B2="","",IF(OR(ISNUMBER(FIND([Book2]Sheet1!$A$1:$B$10,B2))),"○","×"))
（前回同様配列数式ですので、Shift+Ctrlキーを押しながらEnterキーで確定です）

これをオートフィルで下へコピーします。

以上、何度も失礼しました。m(__)m

お礼

丁寧な回答を頂き、ありがとうございます。
とてもわかりやすく、勉強になりました。
感謝感激です。

関数って思っていた以上にいろんな事ができて
奥が深いですね！！

今まではVBAマクロばかりやっていましたが
これを機に関数ももっと勉強しようと思います！

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

回答No.7

MackyNo1

COUNTIF関数では参照するファイルを開いていないと#VALUEエラーとなりますので、以下のような配列数式を使う必要があります。

=IF(MAX(INDEX(([Book2.xls]Sheet1!$A1:$A1000=ASC(A2))*1,)),"○","×")

お礼

回答ありがとうございます。

ただ、この数式ですと完全一致の場合のみ「○」になり、
Book2の文字列が含まれる場合は「○」とならないのでは？？？

参考にさせて頂き、引き続き勉強します！
ありがとうございます！

回答No.6

tom04

こんにちは！
いくつか問題点があると思います。
（1）Book1とBook2の表示が全角・半角の違い　→　これはどちらかに作業用の列を設けることによって解決します。

（2）Book2のデータが行・列とも今後増える可能性がある　→　これは関数では難しいように思われます。

（3）＞マクロやVBAではなく関数で行うことは可能でしょうか？　→　（1）・（2）の制約条件があるために関数だけで！となるとかなり複雑な数式が必要になると思われます。
（実際に今後データが増える件に関して対応できるかどうかは判りません）

ただ、問題点だけ指摘しても解決の糸口にはならないと思いますので、
無理やりって感じの方法です。
ただし、Book2のデータはお示しされているものだけとします。
（↓の数式に空白部分を範囲指定してしまうとすべてに「○」が付いてしまいます。）

↓の画像で説明します。
まず、Book1のB列に作業用の列を設けます。
作業列B2セルに
=ASC(A2)
としてフィルハンドルの（+）マークでダブルクリック

結果のC2セルに

=IF(B2="","",IF(OR(ISNUMBER(FIND([Book2]Sheet1!A$1:A$7,B2))),"○","×"))

これは配列数式になってしまいますので、この画面からコピー＆ペーストしただけではエラーになると思います。
C2セルに貼り付け後、F2キーを押す、またはC2セルをダブルクリック、または数式バー内で一度クリックします。
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定します。

このC2セルをオートフィルで下へコピーすると画像のような感じになります。

今現在、関数での方法といえばこの程度しか思いつきません！
他に良い方法があれば読み流してくださいね。m(__)m

回答No.5

noname#204879

》 book1.xls シート１ A２にある文字列が、
》 book2.xls シート１ A列にある文字列のいずれかを含む場合、

それって、日本語として正しいですか？

　 book1.xls シート１ A２にある文字列が、
　 book2.xls シート１ A列にある いずれかのセルの文字列と等しい場合、

ということでは？

補足

ご指摘ありがとうございます。
わかりづらくて申し訳ありません。

》 book1.xls シート１ A２にある文字列が、
》 book2.xls シート１ A列にある文字列のいずれかを含む場合、

完全一致のものだけを結果として「○」とするのではなく
その文字列を含むものも「○」としたいのです。

例えば
book1.xls シート１ A２にある文字列が「パインジュース」で
book2.xls シート１ A列にあるいずれかのセルの文字列に「パイン」があった場合
結果を「○」としたいのです。

「～と等しい」としてしまうと完全一致のみ対象になってしまうのではないかと思い、おかしな表現になってしまいました。
すみませんでした。

回答No.4

KURUMITO

=IF(A2="","",IF(COUNTIF([Book2.xls]Sheet1!A:A,ASC(TRIM(A2)))>0,"〇","×"))
この式の意味ですがブック2のシート1のＡ列にＡ2セルのデータがあるかどうかをCOUNTIF関数で調べていますが、調べるに当たってはＡ2セルのデータについてTRIM関数でスペースなどが誤って入力されていることをなくした状態にした後でASC関数で文字を半角に変換し、それ文字列がブック2のＡ列に有るかどうかを調べています。もしも文字列がブック2のＡ列に有った場合には〇を無ければ×を表示することになります。
TRIM関数を使っているのはデータを入力する際に誤って文字列の後に空白を入れてしまったような場合にはその空白を取ってから半角文字にしてブック2のＡ列のデータと比較をしていることになります。

お礼

丁寧な回答を頂き、ありがとうございます。

ただ、この数式ですと完全一致の場合のみ「○」になり、
Book2の文字列が含まれる場合は「○」とならないのでは？？？

引き続き、勉強します！！

回答No.3

imogasi

>検索条件（複数）を満たす
こんな場を複数条件とは言わない。
A列（例名前）とD列（例年齢）の両方に条件該当を考えるのが複数条件。
ーー
難しさ
（１）他ブックに検索表がある。
他ブックの場合の番地の参照の仕方を
http://www.excel-jiten.net/formula/ref_other_books.html
の最後のほうの記事で勉強すること。
その他ブックは開いておくこと。
ーー
（２）一方は全角、検索表の方は半角と言う不統一
検索データを半角にして、統一して考える。
ーーー
例データ
まず第1ステップ。検索表が同一シートの場合の例です
例データ
Sheet1　A-D列
カンダ #N/A カンダ ○
ウエノ #N/A ウエノ ○
シブヤ #N/A #N/A (チェック)
シナガワ #N/A シナガワ ○
検索表
F列
ウエノ
アサクサ
カンダ
シンジュク
シナガワ
B2の式　=VLOOKUP(A2,$F$1:$F$100,1,FALSE)　下方向に式を複写
C2の式　=VLOOKUP(ASC(A2),$F$1:$F$100,1,FALSE)　下方向に式を複写
D2の式　=IF(ISERROR(VLOOKUP(ASC(A2),$F$1:$F$100,1,FALSE)),"(チェック)","○")
下方向に式を複写
このD列を納得してから、検索表をブック２に置くとVLOOKUP関数の第2引数をどう変えるべきか
勉強したほうが、質問者のためになるとおもう。
やってみてください。

お礼

ご指摘ありがとうございます。

確かに複数条件とは異なりますね・・・。
おかしな表現ですみませんでした。

正直VBAでやってしまいたいところですが
「関数で」と依頼されているので四苦八苦しています。

参考にさせて頂き、色々やってみます。
ありがとうございます！

回答No.2

KURUMITO

次の式がベターでしょう。

=IF(A2="","",IF(COUNTIF([Book2.xls]Sheet1!A:A,ASC(TRIM(A2)))>0,"〇","×"))

回答No.1

KURUMITO

Book2を立ち上げておいてからＢｏｏｋ１を開きＢ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(COUNTIF([Book2]Sheet1!A:A,ASC(TRIM(A2)))>0,"〇","×"))