【Excel】入力規則のドロップダウンリストに別シートのデータを使う方法

Excelで入力規則のドロップダウンリストを作成する際、リストの元データを別のシートに置きたいと思ったことはありませんか。

シートごとにデータを整理したい、あるいはリストの項目を非表示にしておきたい場合に、この方法は非常に役立ちます。

この記事では、Excelの入力規則で別シートのデータを参照し、ドロップダウンリストを作成する具体的な手順を解説します。

これにより、リストの管理が容易になり、より効率的にデータを入力できるようになります。

別シートのデータをドロップダウンリストの元にする理由

Excelで入力規則のドロップダウンリストを設定する際、リストの元データを別のシートに配置するのにはいくつかのメリットがあります。

まず、作業シートがすっきりと整理される点が挙げられます。入力用のシートと、リスト用のデータを保持するシートを分けることで、データの可視性が向上します。

次に、リストの項目を他のユーザーから隠したい場合にも有効です。リスト用のシートを非表示にすれば、入力規則はそのまま機能しつつ、リストの内容を意図せず変更されたり、見られたりすることを防げます。

また、リストの項目が多くなる場合、作業シートに直接記述すると煩雑になりがちですが、別シートにまとめておくことで、一覧性が高まり、管理しやすくなります。

さらに、VBA(Visual Basic for Applications)などを使ってリストの内容を動的に変更する場合でも、元データを別シートに集約しておけば、コードの記述やデバッグが容易になることがあります。

別シートのデータを参照する入力規則ドロップダウンリストの作成手順

ここでは、具体的な手順をステップごとに解説します。例として、「Sheet1」のA1セルにドロップダウンリストを作成し、そのリストの項目を「リストデータ」という名前の別シートのA列に入力する場合を想定します。

1. リスト項目を別シートに入力する
   まず、ドロップダウンリストに表示させたい項目を、別シートに入力します。ここでは「リストデータ」シートのA1セルからA5セルに「りんご」「みかん」「ぶどう」「いちご」「もも」と入力したとします。
2. 入力規則を設定したいセルを選択する
   次に、ドロップダウンリストを表示させたいセルを選択します。ここでは「Sheet1」のA1セルを選択します。
3. 「データの入力規則」ダイアログを開く
   「Sheet1」のA1セルが選択された状態で、Excelのリボンメニューから「データ」タブをクリックします。「データの入力規則」ボタンをクリックします。
4. 「設定」タブでリストの種類を選択する
   「データの入力規則」ダイアログボックスが表示されます。「設定」タブが選択されていることを確認してください。「入力値の種類」のドロップダウンリストから「リスト」を選択します。
5. 「元の値」に別シートのセル範囲を指定する
   「元の値」の入力欄に、ドロップダウンリストの項目が入力されているセル範囲を指定します。ここでは、「リストデータ」シートのA1セルからA5セルを指定します。入力欄に直接「=リストデータ!$A$1:$A$5」と入力するか、入力欄の右側にある上向き矢印ボタンをクリックして「リストデータ」シートに切り替わり、A1セルからA5セルまでをドラッグして選択し、再度下向き矢印ボタンをクリックしてダイアログボックスに戻ります。セル範囲が絶対参照($A$1:$A$5)になっていることを確認してください。
6. 「OK」をクリックして設定を完了する
   「OK」ボタンをクリックすると、設定が完了します。「Sheet1」のA1セルにドロップダウンリストが表示され、クリックすると「リストデータ」シートのA1セルからA5セルに入力した項目が表示されるようになります。

別シートのデータを参照する際の注意点とトラブルシューティング

別シートのデータを入力規則のドロップダウンリストの元にする際に、いくつか注意すべき点や、発生しやすい問題とその対処法があります。

リストの項目が正しく表示されない

ドロップダウンリストをクリックしても何も表示されない、あるいは意図しない項目が表示される場合、以下の点を確認してください。

原因1:セル範囲の指定ミス

「元の値」に指定したセル範囲が間違っている可能性があります。例えば、入力した項目が入っていないセル範囲を指定していたり、範囲が途切れていたりすると正しく表示されません。

1. 入力規則の設定を再確認する
   入力規則を設定したセルを選択し、「データ」タブの「データの入力規則」を開きます。「設定」タブの「元の値」欄を確認し、別シートの正しいセル範囲が指定されているか確認してください。
2. セル範囲を再指定する
   もし間違っていれば、再度正しいセル範囲をドラッグして指定し直してください。

原因2:リスト項目が連続していない

Excelの入力規則でリストとして参照できるのは、連続したセル範囲です。リスト項目間に空白セルがあると、そこまでしかリストとして認識されません。

1. 空白セルを削除する
   別シートのリスト項目で、意図しない空白セルがあれば削除してください。
2. リスト項目を詰める
   空白セルを削除した後は、リスト項目を上に詰めて、連続した範囲になるように調整してください。

原因3:「元の値」の数式が間違っている

手入力で「元の値」を指定した場合、シート名やセル参照が間違っている可能性があります。特に、シート名にスペースが含まれる場合は、シート名をシングルクォーテーションで囲む必要があります(例:`=’リスト データ’!$A$1:$A$5`)。

1. 数式を確認・修正する
   「元の値」欄の数式がExcelの標準的な書式になっているか確認し、必要に応じて修正してください。

リスト項目を追加・削除した場合の更新方法

別シートのリスト項目を後から追加したり削除したりした場合、入力規則のドロップダウンリストは自動では更新されません。

対処法1:セル範囲を広げる(手動)

リスト項目を追加した場合、単純に「元の値」のセル範囲を広げることで対応できます。例えば、A1からA5までだった範囲をA1からA6に広げます。

1. 入力規則を開く
   入力規則が設定されているセルを選択し、「データ」タブの「データの入力規則」を開きます。
2. 「元の値」を修正する
   「設定」タブの「元の値」欄のセル範囲を、追加した項目が含まれるように広げます。
3. 「OK」をクリックする
   設定を保存します。

ただし、この方法は項目を削除した場合にも範囲を狭める作業が必要となり、項目数が多いと手間がかかります。

対処法2:テーブル機能とOFFSET関数(またはINDIRECT関数)を使う

リスト項目を頻繁に追加・削除する場合、Excelの「テーブル」機能と組み合わせるのが最も効率的です。

1. リスト項目をテーブルとして書式設定する
   別シート(例:「リストデータ」シート)のリスト項目(例:A1セルから開始)を選択し、「挿入」タブの「テーブル」をクリックします。「テーブルの作成」ダイアログで、範囲が正しいか確認し、「先頭行をテーブルの見出しとして使用する」にチェックを入れて「OK」をクリックします。
2. 名前を定義する
   テーブルの列全体を参照できるように、名前を定義します。リスト項目が含まれる列(例:A列)のいずれかのセルを選択し、数式バーの左にある「名前ボックス」に、分かりやすい名前(例:`FruitList`)を入力してEnterキーを押します。これで、その列全体が「FruitList」という名前で参照できるようになります。
3. 入力規則の「元の値」を名前で指定する
   入力規則を設定したいセル(例:「Sheet1」のA1セル)を選択し、「データ」タブの「データの入力規則」を開きます。「設定」タブの「入力値の種類」で「リスト」を選択し、「元の値」に「`=FruitList`」と入力します。

この方法を使うと、テーブルに項目を追加・削除するだけで、ドロップダウンリストが自動的に更新されるようになります。テーブルの範囲は自動で拡張・縮小されるため、手動での範囲変更は不要です。

非表示シートのリストが参照できない場合

リストデータがあるシートを非表示にした場合でも、入力規則は正しく機能します。しかし、非表示シートのセル範囲を直接ドラッグして「元の値」に指定しようとすると、シートがアクティブでないため難しい場合があります。

1. シート名を直接入力する
   非表示シートのセル範囲は、シート名を直接入力して指定してください。例えば、非表示シートの名前が「PrivateList」で、セル範囲がB2からB10であれば、「`=PrivateList!$B$2:$B$10`」のように入力します。
2. シートの表示・非表示を切り替える
   どうしてもドラッグで選択したい場合は、一度シートを表示させてからセル範囲を選択し、再度非表示にすることも可能です。シート見出しを右クリックし、「表示する」を選択すると非表示シートの一覧が表示されます。

Excelの入力規則と別シート参照の使い分け

Excelの入力規則で別シートのデータを参照する方法は、多くの場面で有効ですが、状況によっては他の方法が適している場合もあります。

入力規則のドロップダウンリストが適している場面

・あらかじめ決められた選択肢の中からユーザーに選ばせたい場合。
・入力ミスを防ぎ、データの統一性を保ちたい場合。
・リストの項目が比較的少なく、頻繁に変更されない場合。
・リストを非表示にしておきたい場合。

他の方法が適している場面

・リストの項目が非常に多い、あるいは頻繁に変動する場合。
　→テーブル機能やPower Queryの活用を検討します。

・リスト項目をユーザー自身が追加・編集できるようにしたい場合。
　→入力規則の代わりに、別のシートにリストを作成しておき、ユーザーに直接編集してもらう方法もあります(ただし、誤操作のリスクは高まります)。

・リスト項目が他のデータと連動して変化する場合。
　→VLOOKUP関数やXLOOKUP関数、Power Queryの結合機能などを利用して、動的にリストを作成・更新する方法が考えられます。

比較項目	入力規則(別シート参照)	テーブル機能+入力規則	Power Query
設定の容易さ	容易	容易	やや複雑
リストの自動更新	不可(手動範囲変更必要)	可能	可能(更新操作必要)
データソースの柔軟性	静的	静的	動的(外部ファイル・Web等)
管理の手間	範囲変更が必要な場合あり	少ない	初期設定・更新操作あり
非表示設定	可能	可能	可能

このように、Excelの機能はそれぞれ得意なことが異なります。今回の別シート参照は、静的なリストを効率的に管理するための強力な手段です。

今回の記事では、Excelの入力規則で別シートのデータをドロップダウンリストの元にする方法を詳しく解説しました。

これにより、リストの管理が格段に容易になり、作業シートの整理や、リスト内容の保護といった目的も達成できます。

ぜひ、このテクニックを活用して、Excelでのデータ入力作業をより効率的かつ正確に進めてください。

さらに、リストの項目が頻繁に変わる場合は、テーブル機能との連携を試すことで、自動更新が可能な状態にできます。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】