VBAを利用せずリストを予測候補表示(サジェスト機能)で絞り込む方法!エクセルのドロップダウンリスト(プルダウンメニュー)は便利なようで結構めんどい。。。 365・2019・2016・2013・2010・2007対応

目次

最後に入力フォームシート

「予測検索」シートの「A2」セルには、データの入力規則でセル範囲の名前「=町名」という名前を設定しています。

データの入力規則に名前を定義

「データの入力規則」の「元の値」は、可変式データリストを使用しています。

名前の定義で「元の値」を設定

名前の定義「町名」は、可変式リストを利用しています。

可変式リスト

セルの名前を可変式(メンテナンスいらず)にする方法はこちらの記事をご覧ください。

今回は、可変式リストのカウントを、「郵便番号」シートのC列をカウントしています。

データのカウントに検索候補として表示される個数をカウント

検索候補として表示される数をカウントすることによって、エラーのあるデータは表示されないようになっています。

検索候補としてカウントしているのでエラー値はリストに表示されない

エラーではない個数をカウントしていますので、上記の「#NUM!」エラー以降のデータは表示されません。可変式リストの便利なところですね。

検索候補の個数をカウントしているのでエラー値はリストに表示されない

最後の関数の解説 VLOOKUP関数と思わず使ったIFERROR関数

VLOOKUP関数は便利なのですが、ちょっとした勘が必要な関数でもあります。

今回は、「予測検索」シートの「B3」セルに利用しています。

ここに来てすみません、最後の最後に思わず「IFERROR関数」を使ってしまいました。

「予測変換」シートのB3セルには「=iferror(vlookup(a3,郵便番号,2,false,””)」という式を入力しています。

IFERROR関数は単純で「=IFERROR(論理式,エラーの場合の処理)」となります。

「ISERROR」関数よりも単純で、エラーでなければ最初の論理式の結果を表示エラーの場合はどうするか(ここでは””、空白にする)を定義します。

今回は、VLOOKUP関数がエラーでないときはその値を返し、エラーのときは””(空白)にするという意味です。

ISERRORはエラーのときの処理を別に設定する

改めて関数の解説 VLOOKUP関数

VLOOKUP関数は、「=VLOOKUP(検索する値(ここではA3セルに入力された値),検索する範囲(ここでは町名と郵便番号の2列のリスト範囲),リスト範囲の何列目のセルの値を表示するか(ここでは2列目B列),FALSE(完全一致)かTRUE(最も近い値))」と4つの引数を必要とします。

検索する範囲は名前で定義しています。A列が町名、B列が郵便番号となっています。

VLOOKUP関数で検索する範囲を名前で定義

ということで、今回のVLOOKUP関数では、「予測変換」シートB3セルに入力された値と完全一致する値を「郵便番号」シートの値から検索し、完全一致したセルの行を検索し、その行から指定した列(2列目、B列)の値を表示するということになります。

検索した値と同じ行にあるデータを表示する

これで完成です。お疲れさまでした。

ついに完成!
1 2 3 4 5

関連コンテンツ

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメント一覧 (9件)

  • コメント失礼いたします。
    予測候補表示の記事を拝見させていただきました。とても手順がわかりやすく、途中まで順調でしたが、最後の入力フォームシートのプルダウンリストが表示されず、設定した名前である『=検索候補』と表示されてしまいます。検索候補のシートには予測候補の表示はされている状態です。
    シートが完成しない原因がわからず、解明できておりません。
    原因がわかればお教えいただきたいと存じます。

    • Y様
      コメントありがとうございます。

      データの入力規則は設定されているでしょうか?

      ご確認いただけたら幸いです。

      • 早速のご返答ありがとうございます。
        入力規則は設定済みとなっております。
        また、名前の定義についてもOFFSET、COUNTを使用しておりますが、プルダウンによる候補が表示されません。

  • お返事ありがとうございます。

    データ入力規則は
    入力値の種類:リスト
    元の値:検索候補(名前) とし、検索候補の定義にはOFFSET、COUNTを使用しております。

    • Y様
      コメントありがとうございます。

      そうであれは、名前の定義がうまくいっていない可能性があります。

      名前の定義の数式をコメント欄に貼っていただけたら問題点が分かるかもしれません。

      何卒よろしくお願いいたします。

  • お返事ありがとうございます。
    名前の定義の数式をお送りいたします。
    シート名については、データーベースシート=リストとなっております。

    =OFFSET(検索候補!$A$1,0,0,COUNT(リスト!$B$1:$B$170))

    ご確認のほどよろしくお願いいたします。

    • ご返信遅くなり申し訳ありません。

      リストがB列になっている以外は問題ないように思えます。
      後考えられる原因としては、セルが文字列等の設定になっていることくらいしか分かりません。
      ご確認いただけたら幸いです。

      何卒よろしくお願いいたします。

  • ご丁寧に対応いただきありがとうございます。
    確認いたしましてセルの設定から見直したいと思います。

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

    • またわからないことがあればご遠慮なくコメントいただけたらと思います。

      何卒よろしくお願いいたします。

コメントする

CAPTCHA


このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

目次