Excelの入力規則リストを可変にする方法

Excelで入力規則を設定すると、データ入力の正確性が向上し、ミスを防ぐことができます。

しかし、入力規則のリストが固定されていると、新しいデータが追加されたり変更されたりする際に手間がかかります。

そこで、今回はExcelの入力規則リストを可変にする方法をご紹介します。新しいデータが追加された際に簡単に対応できるようにするための設定方法をご説明します。

Excelの入力規則リストを可変にするとは？

Excelで「データの入力規則」機能によりプルダウンリストを挿入すると、設定したデータ（D2:D7）から選択できるようになります。

しかし、設定したデータ（D2:D7）の下D8セルに、「黒田」と担当者を追加してもプルダウンリストには反映できていません。

以下でご紹介する方法で、指定したデータの増減に合わせて自動で範囲を可変させることができます。

Excelの入力規則リストを可変にする方法をご紹介します。

OFFSET関数とCOUNTA関数で入力規則リストを可変にする方法は、以下のとおりです。

①プルダウンリストを設置したいセル（例：B2）、②「データ」タブ、③「データの入力規則」アイコンの順に選択します。

「データの入力規則」ダイアログボックスが表示されます。

①「入力値の種類」から「リスト」を選択します。

②「元の値」で「=OFFSET($D$2,0,0,COUNTA($D:$D),1)」と入力し、③「OK」ボタンを押します。

OFFSET関数は、指定したセルまたはセル範囲から指定された行数と列数だけ移動した位置にあるセル範囲を返します。書式は「=OFFSET(参照,行数,列数,[高さ], [幅])」となります。

OFFSET関数の4つ目の引数「[高さ]」にCOUNTA関数を指定し、リストのデータ数を取得することで縦方向のセル数が自動的に修正されるという仕組みになっています。

COUNTA関数は空白以外のセルの個数を数える関数で、書式は「=COUNTA(値1,[値2]...)」のようにセル範囲を選択するだけのシンプルな構造となっております。

D列の「担当者」に「黒田」「日高」と項目を追加してみます。

赤矢印で示す通り、プルダウンリストに自動で反映され、可変にすることができました。

テーブルへ変換し入力規則リストを可変にする方法は、以下のとおりです。

①プルダウンリストに設定したいデータ（D2:D7）から、任意のセル（例：D2）を選択します。

②「ホーム」タブ、③「テーブルとして書式設定」、④任意のテーブルデザインの順に選択します。

「テーブルの作成」ダイアログボックスが表示されます。

自動で範囲選択がされるので、範囲が正しいか確認し「OK」ボタンを押します。

D1:D7範囲をテーブルに設定することができました。

①プルダウンリストを設置したいセル（例：B2）、②「データ」タブ、③「データの入力規則」アイコンの順に選択します。

「データの入力規則」ダイアログボックスが表示されます。

①「入力値の種類」から「リスト」を選択します。

②「元の値」で、テーブルにした見出し行を除く範囲「=$D$2:$D$7」を入力し、③「OK」ボタンを押します。

D列の「担当者」に「黒田」と項目を追加してみます。

赤矢印で示す通り、プルダウンリストに自動で反映され、可変にすることができました。

参照元のリストをテーブルに変換すると、リストに増減があっても自動的にプルダウンリストに反映されます。

テーブルの場合、項目を追加すると自動で拡張されるようになっているためです。

もし、項目を追加してもテーブルが拡張されない場合は、下記の記事「テーブルを自動拡張する設定方法」セクションの方法をご確認ください。