リンクユニット

Office 2019 Excel 2019

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

設定した入力規則の確認

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

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

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

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

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

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

参照リストに項目を追加

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

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

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

blank

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

blank

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

テーブルの作成完了

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

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

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

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

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

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

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

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

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

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

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

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

テーブルのサイズ変更

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

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

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

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

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

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

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

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

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

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

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

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

テーブルのサイズ変更と解除

Excelでテーブルを作成した後、テーブルのサイズを変更する方法です。 テーブルの機能については、テーブルの便利機能(テーブルに変換して効率 ...

続きを見る

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

範囲が可変する名前定義

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

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

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

入力規則を設定するシートは、[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]です。

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

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

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

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

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

ドロップダウンリストから選択して自動的に背景色を付ける

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

続きを見る

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

データの入力規則を使った絞り込みリストの作成

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

続きを見る

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

テーブルの便利機能(テーブルに変換して効率アップ)

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

続きを見る

Profile

執筆者/はま

もっと理解したいあなたへ

おすすめ記事と広告

-Office 2019 Excel 2019

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

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