Office 2019 Excel 2019

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

投稿日:2020年3月20日 更新日:

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

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

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

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

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

データの入力規則のプルダウンメニュー

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

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

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

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

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

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

データの入力規則を設定する前の表

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

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

[データ]タブの[データツール]グループにある[データの入力規則]

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

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

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

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

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

[OK]ボタンをクリックします。

[データの入力規則]ダイアログボックス

設定した入力規則の確認

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

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

データの入力規則を設定したドロップダウンリスト

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

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

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

参照リストに項目を追加

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

[データの入力規則]ダイアログボックスで[元の値]のリスト範囲を修正

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

[同じ入力規則が設定されたすべてのセルに変換を適用する]のチェックボックスをオン

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

編集後のドロップダウンリスト

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

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

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

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

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

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

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

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

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

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

[ホーム]タブの[スタイル]グループにある[テーブルとして書式設定]

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

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

[テーブルとして書式設定]ダイアログボックス

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

テーブルの作成完了

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

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

ドロップダウンリストの表示

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

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

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

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

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

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

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

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

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

テーブルのサイズ変更

ここでは、セル[D6](下川)までドラッグします。テーブルの範囲が縮小されます。

右下隅にマウスを合わせてドラッグ

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

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

参照リストのテーブルをサイズ変更した後のドロップダウンリスト

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

テーブルの不要項目を削除

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

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

[データの入力規則]ダイアログボックス

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

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

テーブルのサイズ変更と解除(自動拡張の設定はオートコレクトで)

Excelでテーブルを作成した後、テーブルのサイズを変更する方法です。 また、テーブルを通常の表に変換する方法と、自動拡張の設定についても解 ...

続きを見る

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

範囲が可変する名前定義

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

ピボットテーブルの元データをテーブルにするとデータソースの更新が便利

ピボットテーブルを作成した後、ピボットテーブルの元データの範囲が変わると、その度に[オプション]タブの[データソースの変更]をクリックしてダ ...

続きを見る

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

グラフのデータ範囲を可変にして自動でグラフを拡張する2つの方法

Excelでグラフを作成して、グラフの参照元の範囲を拡張すると、グラフのデータ範囲も拡張しなければなりません。 この操作を行わず、グラフの参 ...

続きを見る

参照リストのテーブルが別シートにある場合

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

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

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

Sheet1の入力規則を設定する表

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

Sheet2のテーブル(参照リスト)

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

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

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

[データの入力規則]ダイアログボックス

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

ドロップダウンリストの表示

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

テーブルに項目を追加

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

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

ドロップダウンリストの表示

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

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

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

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

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

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

[数式]タブの[定義された名前]グループにある[名前の定義]

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

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

[新しい名前]ダイアログボックス

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

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

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

[元の値]のボックス内でクリックします。

[データの入力規則]ダイアログボックス

キーボードから[F3]キーを押します。

キーボード[F3]キー

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

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

[名前の貼り付け]ダイアログボックス

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

これで完了です。

[データの入力規則]ダイアログボックスの[元の値]に名前を設定

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

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

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

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

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

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

ドロップダウンリストの表示

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

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

[名前の管理]ダイアログボックス

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

UNIQUE関数で重複しない値をリスト化して表内の該当セルを塗りつぶす

Excel2021とMicrosoft 365のExcelでは、UNIQUE(ユニーク)関数を使用することができます。 UNIQUE関数を使 ...

続きを見る

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

ドロップダウンリストから選択して自動的にセルに塗りつぶしを設定

Excelで表を作成してセルに入力する文字列が決まっている場合は、データの入力規則を使ってドロップダウンリストから選択して入力できるようにす ...

続きを見る

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

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

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

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

絞り込みドロップダウンリストの作成(データの入力規則)

Excelでは入力規則と関数を使って、リストの中から1つ選択すると、次のリストは前のリストに該当する内容の中からしか選択できないようにするこ ...

続きを見る

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

表をテーブルに変換してデータベース管理などの作業効率をアップ

Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...

続きを見る

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

created by Rinker
★★★★★ 分かりやすい!Excelの基本はこれでOK♪
Amazonのレビューを見る

Profile

執筆者/はま

-Office 2019 Excel 2019

Copyright © 2001-2024 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2024 AllRights Reserved Powered by AFFINGER4.