Excelの関数で、検索条件(複数)を満たす場合値を返す方法について質問です。
セルA2にある文字列が、別ファイルのA列にある文字列を含む場合セルB2に○を、含まない場合は×を入力する関数を教えて下さい!!
---------
book1.xls シート1 A2にある文字列が、
book2.xls シート1 A列にある文字列のいずれかを含む場合、
book1.xls シート1 B2に「○」を、含まない場合は「×」を結果として反映させたいです。
※book1.xlsのリストは全角カナ、book2.xlsのリストは半角カナ です。
※book2.xls シート1にある検索条件は行列共に増える可能性があります。
マクロやVBAではなく関数で行うことは可能でしょうか?
勉強不足ですみません。
色々調べて試しているのですが、なかなかうまくいかないのでお力をお貸し下さい。
よろしくお願いします。
No.8ベストアンサー
- 回答日時:
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.6
- 回答日時:
こんにちは!
いくつか問題点があると思います。
(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
- 回答日時:
》 book1.xls シート1 A2にある文字列が、
》 book2.xls シート1 A列にある文字列のいずれかを含む場合、
それって、日本語として正しいですか?
book1.xls シート1 A2にある文字列が、
book2.xls シート1 A列にある いずれかのセルの文字列と等しい場合、
ということでは?
この回答への補足
ご指摘ありがとうございます。
わかりづらくて申し訳ありません。
》 book1.xls シート1 A2にある文字列が、
》 book2.xls シート1 A列にある文字列のいずれかを含む場合、
完全一致のものだけを結果として「○」とするのではなく
その文字列を含むものも「○」としたいのです。
例えば
book1.xls シート1 A2にある文字列が「パインジュース」で
book2.xls シート1 A列にあるいずれかのセルの文字列に「パイン」があった場合
結果を「○」としたいのです。
「~と等しい」としてしまうと完全一致のみ対象になってしまうのではないかと思い、おかしな表現になってしまいました。
すみませんでした。
No.4
- 回答日時:
=IF(A2="","",IF(COUNTIF([Book2.xls]Sheet1!A:A,ASC(TRIM(A2)))>0,"〇","×"))
この式の意味ですがブック2のシート1のA列にA2セルのデータがあるかどうかをCOUNTIF関数で調べていますが、調べるに当たってはA2セルのデータについてTRIM関数でスペースなどが誤って入力されていることをなくした状態にした後でASC関数で文字を半角に変換し、それ文字列がブック2のA列に有るかどうかを調べています。もしも文字列がブック2のA列に有った場合には〇を無ければ×を表示することになります。
TRIM関数を使っているのはデータを入力する際に誤って文字列の後に空白を入れてしまったような場合にはその空白を取ってから半角文字にしてブック2のA列のデータと比較をしていることになります。
丁寧な回答を頂き、ありがとうございます。
ただ、この数式ですと完全一致の場合のみ「○」になり、
Book2の文字列が含まれる場合は「○」とならないのでは???
引き続き、勉強します!!
No.3
- 回答日時:
>検索条件(複数)を満たす
こんな場を複数条件とは言わない。
A列(例名前)とD列(例年齢)の両方に条件該当を考えるのが複数条件。
ーー
難しさ
(1)他ブックに検索表がある。
他ブックの場合の番地の参照の仕方を
http://www.excel-jiten.net/formula/ref_other_boo …
の最後のほうの記事で勉強すること。
その他ブックは開いておくこと。
ーー
(2)一方は全角、検索表の方は半角と言う不統一
検索データを半角にして、統一して考える。
ーーー
例データ
まず第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列を納得してから、検索表をブック2に置くとVLOOKUP関数の第2引数をどう変えるべきか
勉強したほうが、質問者のためになるとおもう。
やってみてください。
ご指摘ありがとうございます。
確かに複数条件とは異なりますね・・・。
おかしな表現ですみませんでした。
正直VBAでやってしまいたいところですが
「関数で」と依頼されているので四苦八苦しています。
参考にさせて頂き、色々やってみます。
ありがとうございます!
No.2
- 回答日時:
次の式がベターでしょう。
=IF(A2="","",IF(COUNTIF([Book2.xls]Sheet1!A:A,ASC(TRIM(A2)))>0,"〇","×"))
No.1
- 回答日時:
Book2を立ち上げておいてからBook1を開きB2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(A2="","",IF(COUNTIF([Book2]Sheet1!A:A,ASC(TRIM(A2)))>0,"〇","×"))
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの条件付き書式で*を使いたい 4 2022/05/13 16:49
- その他(データベース) Accessのクエリで1フィールドの抽出条件設定をNullでなく全角半角含む空白のみの文字列でない文 1 2023/04/24 15:20
- Visual Basic(VBA) VBA 検索と入力 Excel ブック ぶぶぶ シート ししし 列V 検索対象の列です 最終行は、お 6 2023/05/17 01:40
- Excel(エクセル) エクセルの数式について ブック内の別シートの値の含まれたセルの個数を集計したい 全シート一覧のシート 1 2022/07/21 19:28
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Visual Basic(VBA) VBA 「,」・空白・カタカナ等の複数条件のマクロ 2 2023/08/23 11:57
- Excel(エクセル) Excel 値を返す数式についてです 3 2022/11/21 20:08
- Excel(エクセル) PowerQueryに詳しい方教えてください(Office365) 1 2022/07/24 21:11
- Excel(エクセル) capeofdragonと申します Excel2016を使っておりまして 半角又は全角の任意文字列が 2 2022/10/31 13:51
- Excel(エクセル) エクセル VBA セルの結合 2 2022/09/07 11:48
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
数学 Tan(θ)-1/Cos(θ)について...
-
スマートな関数を教えて下さい。
-
Excel ウインドウ枠の固定をす...
-
EXACT関数とIF関数の組み合わせ...
-
4つのパターンを表示するEXACT...
-
関数を教えて下さい。
-
Excel:一部のフォントでセルの...
-
エクセルで指定した日付、店舗...
-
エクセルでセルに「氏名を入力...
-
Excel 2019 のピボットテーブル...
-
スプレッドシートの関数VLOOKUP...
-
エクセルで日付の入ったセルの...
-
Excelファイルの「数式」タブ→...
-
excelの不要な行の削除ができな...
-
Excelのif関数で文字が見えなく...
-
各ページの1番上の表示について
-
エクセル関数に詳しい方、教え...
-
INDIRECTを使わず excelで複数...
-
Excel 2019 は、SPILL機能があ...
-
UNIQUE関数が使えないバージョ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報