VBAを使わずに関数で求めたいです。
関数得意な皆様にお知恵を拝借したく。
よろしくお願いします。
画像のように検索させたい単語(F列)が複数あります。
これをA列の各セル内で検索をさせます。
セル内でヒットする単語があったら、どの単語にヒットしたのか表示させたいです。
B列の結果のように。
IFを繋げて単語ひとつずつ検索するのは数十個あるので現実的ではなく、
また作ったとしてもうんざりするくらい式が長くなってしまいます。
excel2010なので64個までは繋げられるかとは思いますが・・・
簡潔な式で上手く検索する方法はないでしょうか?
どうぞよろしくお願いします。
No.5ベストアンサー
- 回答日時:
>>=LOOKUP("ー-",MID(A2,FIND($F$2:$F$5,A2),LEN($F$2:$F$5)))
>もしよろしければ意味を教えて頂けると嬉しいです!
1. FIND($F$2:$F$5,A2)
は皆さんと同じ、4行1列の配列を返す数式。=FIND(検索文字列,対象)
2. LEN($F$2:$F$5)
も4行1列の配列。=LEN(文字列)
3. MID(A2,FIND($F$2:$F$5,A2),LEN($F$2:$F$5))
も中身が配列になっているので4行1列の配列。=MID(文字列,開始位置,文字数)
4. =LOOKUP("ー-",MID(A2,FIND($F$2:$F$5,A2),LEN($F$2:$F$5)))
VLOOKUP関数の検索の型をTRUE とか、MATCH関数の照合の型を1として考えてください。
で仮に=MATCH(8^8,B:B,1) としたとき、B列に入力してある数値の一番下、その行番号を返します。
ただし、数値は8の8乗(16777216)未満であること。
を踏まえて、
今度はその文字列版。"ー-"は単純に文字として大きいと判断されます。
"ー"(長音)だけでも良かったのですが、遊び心ですm(_ _)m
だから、投稿した後、もうちょっと顔文字っぽく
=LOOKUP("ーー",MID(A2,FIND($F$2:$F$5,A2),LEN($F$2:$F$5)))
とすればよかったと、若干後悔してました。(ってそんなことで後悔するなって)
5. エラー処理は当方、EXCEL2003なのでIFERROR関数を使えないから
=IF(COUNT(FIND($F$2:$F$5,A2)),LOOKUP("ーー",MID(A2,FIND($F$2:$F$5,A2),LEN($F$2:$F$5))),"なし")
Find関数で見つからない場合エラー値しか返さないのでCOUNTの結果は0。つまり、Falseと判断される
逆の場合、1(以上)でTrueと判断される
注1. 数字だけを検索することがないと判断してます
注2. 検索するセル範囲(F列)は すべて文字が埋まっているものとしています。
おぉぉー、ご丁寧に全ての解説をありがとうございます!
エラー処理はIFERRORで代用ができました!
>"ー-"は単純に文字として大きいと判断されます
これ、これです!検索したときもこの「文字として大きい」という説明が出ていましたが、
これが私には少々理解しにくいんですよね・・・
他にも使ってみて理解を深めていくしかないですね。
とにかく問題が解決できました。
大変勉強になりました。
本当にありがとうございました!
No.3
- 回答日時:
1.範囲 C1:C5 を選択
2.[数式]→[定義された名前]→[選択範囲から作成]を選択
3.“上端行”だけにチェックを入れて Enterキーをパシーッ
4.セル B2 に次の配列数式を入力して、このセルのフィル
__ハンドルを「エイヤッ!」とダブクリ
__=IFERROR(INDEX(検索したい単語,MATCH(TRUE,ISNUMBER(FIND(検索したい単語,A2)),0)),"なし")
No.2
- 回答日時:
こんばんは!
一例です。やり方だけ・・・
↓の画像のようにF列検索文字列データ範囲に空白がないようにしておきます。
(まずA列に入ることはないであろうと思われる「アスタリクス」で埋めています)
検索単語が増える場合はアスタリクスの部分に追加していきます。
とりあえず10行目までの数式にしています。
画像ではB2セルに
=IF(A2="","",IF(OR(ISNUMBER(FIND(F$2:F$10,A2))),INDEX(F$1:F$10,SUMPRODUCT(ISNUMBER(FIND(F$1:F$10,A2))*ROW(F$1:F$10))),"なし"))
配列数式になりますので、Ctrl+Shift+Enterで確定!
この画面からコピー&ペーストする場合は
上記数式をドラッグ&コピー → B2セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Ctrl+Shiftキーを押しながらEnterキーで確定!
B2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
こんな感じではどうでしょうか?m(_ _)m
すごい!
値が返りましたー!!!
自分は普段ISNUMBERを使うことがありませんので発想になかったです、
また配列は苦手なので大変助かりました!
大変助かりました、ありがとうございました!
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBA 検索と入力 Excel ブック ぶぶぶ シート ししし 列V 検索対象の列です 最終行は、お 6 2023/05/17 01:40
- Access(アクセス) AccessVBAで降順にするテーブル作成クエリを使用して作成したテーブルを削除し同一のテーブル作成 1 2023/01/06 11:17
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Excel(エクセル) 【Excel】指定のセル内容を基に別シートのセルを検索して選択する【VBA】 1 2022/06/16 16:16
- Excel(エクセル) Excelヘルプの原文を表示する最速の方法(手順)には? 1 2023/08/11 11:30
- Excel(エクセル) エクセルの印刷範囲をページ単位で可変にする方法 3 2022/05/23 13:04
- Excel(エクセル) VLOOKUPで、検索先が3シートに分かれていて、番号の大小で検索するとエラーになります。 6 2023/07/14 19:09
- Access(アクセス) Excel や Access のフォームの中でいわゆるインターネットの検索窓のようなものを構築できま 9 2022/05/21 12:39
- SEO 意図しないページが検索ヒットする問題 2 2022/05/19 14:35
- Visual Basic(VBA) EXCEL VBA 単語置き換え について質問です ブック名 ぶぶぶ シート名 ししし セル V3〜 3 2023/03/08 01:41
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
数学 Tan(θ)-1/Cos(θ)について...
-
4つのパターンを表示するEXACT...
-
エクセルの数式で教えてください。
-
エクセルで指定した日付、店舗...
-
Excel:一部のフォントでセルの...
-
Excel初心者です。 詳しい方、...
-
Excel初心者です。 詳しい方、...
-
Excel 2019 のピボットテーブル...
-
スマートな関数を教えて下さい。
-
Excelのグラフ軸について
-
EXACT関数とIF関数の組み合わせ...
-
Excel ウインドウ枠の固定をす...
-
エクセルで日付の入ったセルの...
-
excelの不要な行の削除ができな...
-
INDIRECTを使わず excelで複数...
-
各ページの1番上の表示について
-
スプレッドシートの関数VLOOKUP...
-
エクセルでセルに「氏名を入力...
-
関数を教えて下さい。
-
Excelのif関数で文字が見えなく...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報
ベストアンサーをつけると締め切られるということで、
どこで全体のお礼をつけたらいいのかわからなかったので、補足欄に記載しちゃいます。
すみません。
皆さん参考になる式を教えてくださり、大変勉強になりました!&助かりました!
本当にありがとうございました。
ベストアンサーは初めて見る検索値の方法をお教え下さったCoalTarさんにさせて頂きます。
ありがとうございました!