入力規則のリストが可変（増減）するならテーブルに変換して自動修正

Excelの入力規則でドロップダウンリストから選択できるように設定することは多いですね。

でも、そのリストが増減する場合、その度に入力規則の設定でリスト範囲を修正するのは面倒です。

リストをテーブルに変換すると、テーブルを修正するだけで、［データの入力規則］ダイアログボックスを表示して範囲を修正する必要はありません。

テーブルは、項目を追加すると自動拡張されますし、範囲を縮小する場合もドラッグするだけです。

参照リストを別シートに設定していても、テーブルとは別に参照リストの範囲に名前を付けることで自動的に項目を追加できるようになります。

または、INDIRECT関数を使用して設定します。

参考Microsoft 365のExcelでは、ドロップダウンリストにオートコンプリート機能が追加されています。

ドロップダウンリストにオートコンプリート機能が追加

データの入力規則の設定（ドロップダウンリストから選択）

まず、入力規則の設定です。

以下のような表があって、セル［B2］からセル［B11］までに、リストから入力できるように設定します。

ドロップダウンリストに表示する参照データは、セル［D2］からセル［D7］です。このデータも先に準備しておきます。

まず、セル［B2］からセル［B11］を範囲選択します。

［データ］タブの［データツール］グループにある［データの入力規則］をクリックします。

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

［設定］タブの［入力値の種類］の▼をクリックして、一覧から［リスト］を選択します。

［ドロップダウンリストから選択する］のチェックボックスがオンになっていることを確認します。既定でオンになっていますが、念のため確認してください。

［元の値］のボックス内をクリックします。そして、セル［D2］からセル［D7］を選択します。

すると、［元の値］のボックスに［=$D$2:$D$7］と表示されます。

［OK］ボタンをクリックします。

参考［データの入力規則］ダイアログボックスの［入力値の種類］で［リスト］を選択すると、2つの設定項目が表示されます。

• 空白を無視する
• ドロップダウンリストから選択する

この2つの設定内容については、以下の記事で解説しています。

設定した入力規則の確認

セル［B2］をクリックすると、▼ボタンが表示されます。

▼をクリックすると、ドロップダウンリストが表示され、その中から選択できるようになっています。

参照リストに項目を追加（テーブルではない場合）

ドロップダウンリストの項目を増やすことになった場合の方法です。

D列の参照リスト（氏名）に名前を追加します。ここでは、「佐藤」さんを追加します。

再度、［データ］タブの［データの入力規則］ダイアログボックスを表示して、［元の値］のリスト範囲を修正します。

上記のように入力規則を設定したセルを1つしか選択していなくても、［同じ入力規則が設定されたすべてのセルに変換を適用する］のチェックボックスをオンにすると、以下のようにまとめて設定できます。

設定後、確認するとドロップダウンリストの項目に追加されています。

毎回、［データの入力規則］ダイアログボックスを表示して範囲を修正するのは面倒ですね。

そこで、テーブルを活用しましょうということです。

今、［データの入力規則］を使ってドロップダウンリストの設定をしている方も、今後、リストの増減がありそうと思われる場合は先に設定しておかれるといいと思います。

参照リストをテーブルに変換

上記のドロップダウンリストから選択できるようにした表を、そのまま使って解説します。

参照リストであるセル［D1］からセル［D7］のどのセルでも構わないので、アクティブにしておきます。

そして、［ホーム］タブの［スタイル］グループにある［テーブルとして書式設定］をクリックします。

スタイルの中から、任意のものを選択します。ここでは、［白,テーブルスタイル(淡色)１］を選択します。

テーブルのスタイルは問題ありません。スタイルはクリアできます。

スタイルをクリアする場合

以下のように［テーブルとして書式設定］ダイアログボックスが表示されます。

自動的に範囲選択され、セル範囲も表示されています。［先頭行をテーブルの見出しとして使用する］のチェックボックスをオンにして［OK］ボタンをクリックします。

テーブルに変換されます。これで完了です。

ドロップダウンリストの確認

セル［B2］を選択して、ドロップダウンリストを表示して確認します。

参照リストに項目を追加（テーブルの場合）

D列の参照リスト（氏名）に項目を追加します。ここでは、「佐藤」さんを追加します。

そして、セル［B2］を選択して、ドロップダウンリストを表示すると追加した「佐藤」の名前が現れます。

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

参考もし、テーブルの範囲が拡張されていないようであれば、［オートコレクトのオプション］の設定を確認してください。

テーブル範囲の自動拡張の設定

参照リストの範囲を縮小する場合

参照リストのテーブルで範囲を縮小する場合は、テーブルの右下にカーソルを合わせて、斜めの矢印に変わったときに上へドラッグします。

ここでは、セル［D6］（下川）までドラッグします。テーブルの範囲が縮小されます。

セル［B2］を選択して、ドロップダウンリストを表示すると、以下のようにリスト項目は少なくなります。

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

不要になった参照リストの項目は、［Delete］キーで削除しても構いません。

注意ここでは［データの入力規則］ダイアログボックスで参照リストの範囲を設定した後に、参照リストをテーブルに変換しています。

先に参照リストをテーブルに設定した場合は、［データの入力規則］ダイアログボックスを表示させて、［元の値］に参照範囲を設定してください。

また、ここでは一列のテーブルで解説していますが、複数列のテーブルであっても設定は同じです。

参考テーブルのサイズ変更については、以下の記事で解説しています。

テーブルではなく、名前定義に関数を使って設定する方法もあります。

範囲が可変する名前定義

ピボットテーブルもテーブルを元に作成すると、［更新］ボタンをクリックするだけでデータソースが更新されるようになります。

グラフの参照元範囲もテーブルにすると、新規データを追加しても自動的にグラフに反映されるようになります。

参照リストのテーブルが別シートにある場合（参照範囲に名前）

参照リストは、別シートに作成される場合も多いと思います。

入力規則を設定するセル範囲と、参照リストが別シートの場合は、もう1つ設定が必要になります。

入力規則を設定するシートは、［Sheet1］です。

参照リストのテーブルは［Sheet2］にあります。

Sheet1のセル［B2］からセル［B11］を範囲選択して、［データの入力規則］の設定をします。

［ドロップダウンリストから選択する］のチェックボックスはオンになっていることを確認します。既定でオンになっていますが、念のため確認してください。

［元の値］は、［=Sheet2!$A$2:$A$7］となります。

セル［B2］を選択して、ドロップダウンリストを表示すると、以下のように参照リストの項目が表示されます。

［Sheet2］の参照リストに項目を追加してみます。ここでは、「田中」を追加します。

［Sheet1］のセル［B2］を選択してドロップダウンリストを表示してみます。

追加した項目は表示されません。

参照リストの範囲に名前を付ける

参照リストを別シートに設定しても、自動的に項目を追加できるようにする方法です。

参照リストの範囲に名前を付けます。

テーブルを設定すると、テーブルの名前も自動的に付けられていますが、それとは別にセル範囲に名前を付けます。

［Sheet2］のセル［A2］からセル［A7］までを範囲選択します。

そして、［数式］タブの［定義された名前］グループにある［名前の定義］をクリックします。

［新しい名前］ダイアログボックスが表示されます。［名前］のテキストボックスに任意の名前を入力します。

ここでは、［担当者］という名前にします。［OK］ボタンをクリックします。

参考セル範囲に名前を付ける方法と管理については、以下の記事で解説しています。

データの入力規則の［元の値］に名前を設定

［Sheet1］に戻って、入力規則の設定を変更します。

セル［B2］からセル［B11］を範囲選択して、［データの入力規則］ダイアログボックスを表示します。

［元の値］のボックス内でクリックします。

キーボードから［F3］キーを押します。

［名前の貼り付け］ダイアログボックスが表示されます。

［Sheet2］の参照リストに設定した名前が表示されていると思いますので選択して、［OK］ボタンをクリックします。

［データの入力規則］ダイアログボックスの［元の値］は［=担当者］となります。［OK］ボタンをクリックして閉じます。

これで完了です。

参照リストのテーブルに項目を追加してみます。

［Sheet1］のセル［B2］を選択して、プルダウンメニューを表示してみます。

追加した項目が表示されています。

もちろん、テーブルの範囲を縮小すると項目を減らすこともできます。

参照リストを別シートに設定する場合は、テーブルのリスト範囲に名前を付けて、その名前を使って入力規則の設定をするということです。

参考［数式］タブの［定義された名前］グループの［名前の管理］をクリックすると、以下のようになっています。

［名前の管理］ダイアログボックスを表示するショートカットキーは［Ctrl］+［F3］です。

参照リストのテーブルが別シートにある場合（INDIRECT関数）

もう1つの方法として、入力規則の元の値にINDIRECT関数を使用する方法があります。

まず、リストとしているテーブル名とフィールド名を確認しておきます。

シート［Sheet2］のテーブル内のセルを選択して、［テーブルデザイン］タブを表示します。

［プロパティ］グループの［テーブル名］に名前が表示されています。ここでは［テーブル2］ですね。

リストとなるフィールド名は、［氏名］です。

シートを［Sheet1］に切り替えて、入力規則を設定している範囲を選択します。

［データ］タブの［入力規則］をクリックして［データの入力規則］ダイアログボックスを表示します。

元の値に、INDIRECT（インダイレクト）関数を使った数式を入力します。

INDIRECT 関数 - Microsoft サポート（Microsoft）

［テーブル2］の［氏名］フィールドを参照するという数式になります。

［OK］ボタンをクリックして、リストを拡張しても自動的にドロップダウンリストに反映されることを確認してください。

参考以下の記事では、絞り込みリストを作成する方法を解説していますが、この場合も参照リストをテーブルにすると自動で項目を増減ができるようになります。

以下の記事でも、INDIRECT関数を使用しています。

入力規則のリストにUNIQUE関数のスピル範囲演算子を使って範囲を指定すると、データを追加しても重複しない値が自動的にドロップダウンリストに追加されます。

入力規則（ドロップダウンリスト）と条件付き書式を組み合わせる方法も解説しています。

入力規則でドロップダウンリストから選択できるように設定しても、コピーして貼り付けられた値やオートフィルで入力された値は受け入れられてしまいます。

Excelには、無効データをチェックする機能もあります。

無効データをチェックするには

テーブルには、とても便利な機能があります。