ドロップダウンリスト

[EXCEL] VLOOKUPの範囲を切替えるドロップダウンリスト(動画あり)

EXCEL屋へ寄せられるご質問には VLOOKUP関連が多いのですが、その中から「検索範囲を切り替えたい」というご質問にお答えしたいと思います。

<ご質問>
VLOOKUPの範囲をチェックボックスのチェックに合わせて変えたいのですが可能でしょうか。

チェックボックスは「複数選択」(AND)となり、かなり複雑になりますので、今回はドロップダウンリスト「択一選択」(OR)で複数の「表」を切り替える方法についてご説明いたします。

スポンサーリンク




テーブル設計上の問題

VLOOKUP関数はEXCEL上でデータベースのような「検索」が手軽に行えるため便利な反面、後々のメンテナンスも考慮しておく必要があります。本格的なデータベースならテーブル、検索項目の重複が起こらないように設計されるのですが、EXCELの場合「検索対象」が増えた場合、仕方なくテーブル(表)を増やさざるを得なくなってしまう、といった問題がよく発生します。

よくあるテーブルの増殖

①VLOOKUP(日,2018売上表,売上,0)で、2018年のセール

②日は日付ではなく「初日、2日め‥」といった普段使っている表現です。

③該当する「セール」終了後にまとめられた「実績表」をそのまま使っています。

①2018年、2019年、2020年と年度で分けていたエクセルシートでしたが「統一してほしい」との指示がありましたが「日付」の名称が「重複」するため、縦につなげることができず、仕方なく表が3つできてしまいました。

②ドロップダウンリストで「年度」を切り替えたら「検索元」も切り替えたいのですが、どうすれば良いのでしょうか?

テーブル切替えのしくみ

名前を付ける

①まず2018年の表に「名前」を付けるため、表を選択します。

②「数式」タブ「名前の定義」をクリックします。

③数字から始まる名前は登録できないので、アンダーバーを付けて「_2018」としました。「OK」で2018年の表範囲に名前が付きました。

④ドロップダウンリストの中身も名前と一致した内容とします。

2019年、2020年も同様に「名前」を付けます。

「数式」タブ「名前の管理」から確認できます。

関数内で名前を使う

VLOOKUP式の中で、検索範囲を B2 としてみましたが、「エラー」となります。B2 とすれば、B2 に表示されている「名称」を範囲としてくれるのかな?と期待してもダメです。

次に直接、数式中に _2019 と表の名前を入れてみたところうまく行きましたが、これでは数式自体を切り替えなくてはなりません。

INDIRECT関数で成功

INDIRECT関数とは、指定する文字列への参照、つまりセルB2 に表示されている文字列 _2020 への参照(名前を付けたセル範囲)を返すのです。

ポイント解説動画

エクセル屋の解説動画は毎日増えています。「知ってるんだけど確認したい」時に便利なよう、ほとんどが1分以内でナレーション無し-まわりに気兼ねなく何度でもくり返し確認できます。下のボタンからチャンネル登録をお願いいたします。

EXCEL屋動画チャンネル


スポンサーリンク