【エクセルのリスト】新規リストを追加した時に再設定無しでリストに表示させる方法

エクセルでリストの設定を行い使用することはよくありますね。
最も標準的な方法で設定をすると、ある問題にぶち当たりますね。

その問題は、

新規項目をリストに使いした時に反映されない！！

という事態に陥りますね。

そこで、リストが増えたらその都度リストの範囲を設定し直すか、あらかじめリストの範囲を広めに設定しておくということをやりますね。

しかし、どちらの方法もスマートさに欠けますね。

なおかつ、ファイルの容量がとてつもなく大きくなり起動が遅くなるというデメリットも生まれますね。

そこで、前回紹介した、テーブルの機能を利用します。

リストの準備

使用するデータは『エクセルのデータ入力で次の行に数式が反映される！事前にコピーしなくて済む方法紹介』で使用したものを使用します。

このデータの商品マスターを使用します。

商品マスターの商品コードが受注管理のシートでリスト化されています。

今の状態では、商品コードが増えても受注管理のシートには反映されないためその都度リストの設定が必要です。

今回は商品コードが増えても受注管理のシートに自動で反映される方法を紹介します。

データベースのテーブル化

『エクセルのデータ入力で次の行に数式が反映される！事前にコピーしなくて済む方法紹介』では、受注管理をテーブル化しましたが、今回は商品マスターもテーブル化します。
テーブル化の手順は、『エクセルのデータ入力で次の行に数式が反映される！事前にコピーしなくて済む方法紹介』の時と同様です。

テーブル化するところを選択して、挿入からテーブルをクリックします。先頭行をテーブルの見出しとして使用するにチェックを入れてください。
この時にテーブルの名前を必ず付けてください。

今回は「商品マスターテーブル」とします。

テーブル化の手順は以上で終わりです。

テーブル名は次のリストの設定で使用します。
分かりやすい名前を設定することをおすすめします。

リストの設定

次にリストを設定していきます。

受注管理のシートで商品コードのデータの入力規則が「元の値」が

「=商品マスター!$B$3:$B$15」

となっていると思います。

それを

「=INDIRECT(“商品マスターテーブル[商品コード]”)」

と表記します。

ここで使用しますINDIRECT（インダイレクト）関数は、指定される文字列への参照を返す関数です。
つまり、商品マスターテーブルの商品コードの行をリストとして返します。

という意味になります。

今回の場合すでに下の行まで入力規則が入力されていますので、「同じ入力規則が設定されたすべてのセルに変更を適用する」にチェックを入れると自動的に下の行の入力規則も変更されます。

これで、リストの設定は完了です。

=INDIRECT(“テーブル名[リストに表示させたい見出し]”)

また、これ作業に付帯してVLOOKUP関数も変更するとより使いやすくなります。

現在の設定は、VLOOKUPの範囲指定が商品マスターが増える前の段階の範囲になっています。
その後マスターが増えた結果、マスターの一部しか範囲選択されていません。
そのため、リストを選んでもVLOOKUPでエラーが表示されるものも出てきます。

そこで、VLOOKUP関数を変更します。

現在は商品マスターのB3からD10までの範囲ですが、範囲をB列からD列と範囲指定します。

範囲指定の表記は$B:$Dとなります。

なので単価を返すG3の関数は

=IFERROR(VLOOKUP($D3,商品マスター!$B:$D,3,FALSE),””)

となります。