「データの入力規則」を使って、「ドロップダウンリスト」を自動的に追加、削除する方法

Excelでデータ入力時に、ある一定の値に制限させたい場合、「データの入力規則」機能を使うことがよくあります。

例えば「0～9999」までの「整数」のみや、「2023/01/01～2023/12/31」までの「日付」のみ入力可能にする方法は、特につまづく設定もありませんが、「リスト」の場合は意図しない値が反映されてしまうことがあります。

誤ったデータを入力させないためにも、適切な設定を行いましょう。

【おすすめ】「ドロップダウンリスト」をテーブル参照して作成

上図のようなドロップダウンリストを作成する場合、最もおすすめする方法は「テーブル」を作成して、その項目から選ぶやり方です。

step
1新規シート作成

ドロップダウンリストに表示する項目用に新規シートを作成して、シート名を分かりやすい名前に変更します。

必ずしも別シートにする必要はありませんが、分けて管理する方が後々修正しやすいです。

step
2リスト表示する項目作成

ドロップダウンリストに表示する項目をセルに入力します。先頭行にはテーブルの見出しを入力しておくと分かりやすいでしょう。

step
3テーブルの作成

先程入力したリスト全体を範囲選択した状態で、[挿入（タブ）]➔[テーブル]またはCtrl＋Tを押して、テーブルを作成します。

「先頭行をテーブルの見出しとして使用する」場合は、 チェックを忘れずに。

step
4テーブル名を付ける

[テーブルデザイン（タブ）]➔[テーブル名:]に、作成したテーブルの名前を付けます。ここでは例として「T_好きな食べ物リスト」と入力しています。

（デフォルト名の「テーブル1」のままでも構いませんが、その場合は 「T_好きな食べ物リスト」を「テーブル1」に置き換えてください。）

step
5リストの値にINDIRECT関数を使ってテーブル名を指定する

元のシートに戻って、ドロップダウンリストを設定したいセルを選択した状態で[データ（タブ）]➔[データの入力規則]を押します。

設定ダイアログが表示されるので、「入力値の種類」を「リスト」に変更し、「元の値」に 「=INDIRECT("T_好きな食べ物リスト")」 と入力してください。

設定は以上です。

設定したセルを選択すると右端に マークが出てくるのでそれを押すと、ドロップダウンリストが表示されます。

データの入力規則の「元の値」にリスト項目を絶対参照する方法は、

（ 例 ：「=好きな食べ物!$A$2:$A$6」 等）もあるのですが、では何故わざわざ面倒くさいテーブルを作成する方を解説したのかというと、絶対参照の場合は項目を追加した場合、「元の値」も変更する必要があるのです。

項目の追加が度々発生する場合や、ブック作成者以外が項目を追加したい場合等は、設定方法が分からないといった状況が必ず出てきます。

その点、紹介した方法だと、追加で項目が増えたとしても、ドロップダウンリスト表示用のシートに戻って、

項目を追加すれば、ドロップダウンリストにも自動的に反映されて便利なので、こちらがおすすめです。

ただし、削除の場合はセルの値を消すのではなく、項目リストの行を削除してください。

セルの値を消すだけだと、ドロップダウンリストに空白行が出来てしまいますのでご注意ください。

INDIRECT関数とは

実はINDIRECT関数を使わなくても、テーブルの値は参照出来ます。

事実、セルに入力した場合は、「=テーブル名」だけでテーブルの値を参照可能です。

ですが、「データの入力規則」設定ダイアログの「元の値」に「=T_好きな食べ物リスト」と入力すると、エラーが表示されます。

これを回避するために、INDIRECT関数を利用しているのです。

ますます意味不明だと思いますので、以下簡単にINDIRECT関数のご説明をします。

Excel関数

=INDIRECT(参照文字列,[参照形式]) （[参照形式]は省略可。省略時はTRUE）

説明

指定された参照文字列を「セル番地」とみなした参照を返す関数です。 参照先を動的に変更したい場合に使用します。 例）上図の状況のとき、「=INDIRECT("A"&COUNTA(A:A))」と入力すると、A列の最終行の値を表示します。 「=COUNTA(A:A)」 はA列のデータが入力されているセルの個数を返す関数です。A列のデータの個数はA1～A4の4つなので、結果 上記式は「=INDIRECT("A"&4)」となり、最終的に 「=A4」 と同じ意味になります。 セルA5に「ほげほげ」と入力すると、セルC12はA列の最終行を取得するため「ほげほげ」と表示されます。 [参照形式]をFALSEにすると、参照文字列は「R1C1形式」で入力します。 例）「=INDIRECT("R2C1",FALSE) 」と入力すると、「ゴリミ」と表示されます。「R」はRow（行）、「C」はColumn（列）を示しており、「R2C1」は2行目1列という意味になります。

分からなくても特に問題はありませんが、INDIRECT()関数は非常に便利なのでぜひこの機会に覚えてください。