【Excel時短】連動プルダウンリストでデータを自動反映させる方法【入力ミス防止】

連動プルダウンリストでデータを自動反映させる方法

仕事などでよく申請書や依頼書等のフォーマットを準備することがありますが、こちらが意図していない値を入力して返されると、「依頼者に確認する」という余計な作業が発生してしまいます。ワタシはそんな作業が大嫌い。。。

そこで、依頼者が入力ミスした場合、確認作業が必要な重要項目に対し、リストを選択するだけで他の項目が自動反映されるフォーマットを作成してしまいましょう。参照データのメンテナンスは必須ですが、ミス防止になりますし、なにより依頼者にメールで確認>レスを待つ行為をせずに済みます。

nojo

入力ミスの確認作業ほど、ムダなことはないと思うのよね。

同じ悩みを抱えていらっしゃる方のお役に立てれば幸いです。

目次

やりたいことのイメージと前準備

やりたいことはinputシートのB2セルをリスト化し、そのリストの値によって、B3以降のセルに値を自動反映させることです。

B2セルのリスト選択によって、値を自動反映している様子
B2セルのリスト選択によって、値を自動反映している様子

そのためにシートを2つ準備します。ひとつはinput用のシート、もうひとつは自動反映するデータをまとめた参照シートを準備して下さい。

シート名:input
シート名:input
シート名:参照
シート名:参照

再度、上図を例にしてあてはめると、

  1. inputシートのB2で”いちご”を選択
  2. inputシートのB3:B8に参照シートのB7:G7のデータを自動反映させる

になります。

inputシートのB2セルをリスト化する

STEP
「データ」>「データの入力規則」>【データの入力規則】をクリック
Excel データ > データの入力規則
Excel データ > データの入力規則
STEP
『設定』タブ>【入力値の種類】で「リスト」を選択
Excel データの入力規則 設定
Excel データの入力規則 設定

【元の値】は参照データを選択します。赤枠で示している上向き矢印をクリックします。

Excel データの入力規則 > 元の値の範囲選択へ

シート:参照のリスト化したいデータ範囲を選択します。ここでは商品名(A2:A7)を範囲選択し、右側の水色下向き矢印をクリックすると、上図の【データの入力規則画面】に戻りますので、「OK」ボタンで確定させて下さい。

Excel データの入力規則 > 元の値の範囲選択
Excel データの入力規則 > 元の値の範囲選択

参照シートのデータに名前を定義する

リストにデータを連動させるために、参照シートのデータに名前の定義を行っていきます。簡単・単純作業ですが、リストの数(ここで言うところの「商品名」)だけ行う必要があります。

nojo

リストが多い場合はちと面倒ですが、入力ミス防止をモチベーションにやっちまいましょう。2~3分あればできますしね。

STEP
データを選択>「数式」>「名前の定義」をクリック

まずは、”りんご”から名前の定義を行います。りんごのデータ範囲であるA2:G2を選択し、「数式」>「名前の定義」をクリックして下さい。

Excel データ範囲選択 > 数式 > 名前の定義
Excel データ範囲選択 > 数式 > 名前の定義
STEP
適切な名前を付ける>「OK」ボタンをクリック

【新しい名前】ウィンドウが表示されます。名前や参照範囲には既に選択したデータが入っていますので、変更する必要がなければ、そのまま「OK」ボタンで確定させて下さい。

ここまでの2手順を”いちご”まで、繰り返しましょう。

Excel 名前の定義 > 新しい名前
Excel 名前の定義 > 新しい名前
STEP
「数式」>「名前の管理」で確認する

“いちご”まで作業できたら、登録した名前を確認してみましょう。すべて名前が定義できていることを確認できたら、「閉じる」ボタンをクリックします。

Excel 数式 > 名前の管理
Excel 数式 > 名前の管理

inputシートに関数を設定する

最後の工程です。inputシートに関数を設定していきます。関数は、

  • VLOOKUP
  • INDIRECT
  • IF

の組み合わせで使用します。IF関数はリストが選択されていないときのエラー表示防止で使います。

Excel セル選択 > 数式を入力
Excel セル選択 > 数式を入力

まずは、B3セルを選択し、

=IF($B$2=””,””,VLOOKUP($B$2,INDIRECT($B$2),2,FALSE))

を入力してください。あとは、B4:B8まで数式をオートフィルでコピーし、FALSEの前の数値をひとつずつ増やすだけです。

言葉だけだと心もとないので、以下、わかりやすい一覧表を作成しました。関数のコピペ等に利用されて下さい。

セル項目名関数参照データ
B3商品コード=IF($B$2=””,””,VLOOKUP($B$2,INDIRECT($B$2),2,FALSE))参照シート!B列
B4種別コード=IF($B$2=””,””,VLOOKUP($B$2,INDIRECT($B$2),3,FALSE))参照シート!C列
B5担当者=IF($B$2=””,””,VLOOKUP($B$2,INDIRECT($B$2),4,FALSE))参照シート!D列
B6発注コード=IF($B$2=””,””,VLOOKUP($B$2,INDIRECT($B$2),5,FALSE))参照シート!E列
B7発注元コード=IF($B$2=””,””,VLOOKUP($B$2,INDIRECT($B$2),6,FALSE))参照シート!F列
B8承認者=IF($B$2=””,””,VLOOKUP($B$2,INDIRECT($B$2),7,FALSE))参照シート!G列
セル、項目名、関数、参照データの一覧表

ちなみにFALSE(完全一致)の前の数値は、列番号となっています。こういうとき、R1C1形式への切り替えができたら、数式を考えるときに分かりやすくなります。お時間のある時に設定されてみてはいかがでしょうか?

関数入力が終わりましたら、ひとつずつリストを選択して動作確認を行いましょう。自動反映するデータに誤りがなければ完了です。

最後まで読んで下さり、ありがとうございました!

よかったらシェアしてね!
目次
TOP
目次
閉じる