Excelで別のシートを参照したデータの入力規則リストを使う方法(6選)

Excelでは「データの入力規則」を活用することで、セルに入力できる値を制限できます。本記事では、別のシートにあるデータを参照して入力規則リストを設定する方法を、6つの具体例に沿って詳しく解説します。以下はサンプルデータセットの概要です。現在、「商品(Product)」と「カテゴリー(Category)」の2つの列があり、解説の進行に合わせてデータセットは少しずつ変化していきます。

別のシートから入力規則リストを使う6つの方法

1. 別のシートの入力規則リストでドロップダウンリストを作成する

このデータセットには2つの列があります。顧客が購入した商品を、別のシートにある入力規則リストを使って入力していきます。それでは手順を見ていきましょう。

手順:

• まず、「dropdown」シートでセル範囲B5:B11を選択します。
• 次に、「データ」タブ >>> 「データの入力規則」をクリックします。

すると、「データの入力規則」ダイアログボックスが表示されます。

• 「入力値の種類(Accept)」のドロップダウンメニューから「リスト(List)」を選択します。

ポイント: 「空白を無視する(Ignore blank)」と「ドロップダウンリストから選択する(In-cell dropdown)」には、デフォルトでチェックが入っています。入っていない場合はチェックを付けておきましょう。

• 続いて、「元の値(Source)」ボックスをクリックします。
• そして、「source」シートに切り替えます。

• 次に、セル範囲B5:B11を選択します。これが入力規則のリストになります。
• 最後に「OK」をクリックします。

セルに矢印アイコンが表示されます。

• この矢印アイコンをクリックします。

別のシートから参照した入力規則リストが表示されます。

• あとは、リストから任意の項目を選択するだけです。

選択した値がデータセットに入力されます。

このように、Excelでは別のシートにある入力規則リストを使って、データを効率よく入力できます。

関連記事: Excelでテーブルからデータの入力規則リストを作る方法(3つの方法)

2. 別のシートの入力規則リストで日付範囲を設定する

次に、データセットには商品の在庫日(Stock Date)列が空欄の状態です。これを入力規則リストで埋めていきます。今回は「日付(Date)間」オプションを使用します。

手順:

• まず、セル範囲C5:C11を選択します。
• 次に、「データの入力規則」ダイアログボックスを開きます。
• そして、以下のように設定します。
  • 入力値の種類: 日付(Date)
  • データ: 次の値の間(between)

• 続いて、「source」シートからセル参照を指定します。
  • 開始日: 「source」シートのセルB14
  • 終了日: 「source」シートのセルB15

なお、場合によっては絶対参照($マーク付きのセル参照)を使う必要があります。絶対参照にしておけば、他のセルへコピーしても参照先がずれません。

• 最後に「OK」をクリックします。

これで、設定した条件以外の値を入力するとエラーメッセージが表示されるようになります。このメッセージは自由にカスタマイズ可能です。

メッセージを変更するには、「データの入力規則」ダイアログボックスの「エラーメッセージ(Error Alert)」タブを開きます。

• まず、「タイトル(Title)」に任意の文字を入力します(省略可)。
• 次に、「エラーメッセージ(Error message)」に表示したい内容を入力します。
• 最後に「OK」をクリックします。

カスタムエラーメッセージが問題なく機能していることが確認できます。

残りのセルにも日付を手動で入力してみましょう。日付範囲外の値を入力すると警告メッセージが表示されます。このように、入力規則リストを使えばセルへの入力をしっかり制限できます。

関連記事: Excelで名前付き範囲とVBAを使ったデータの入力規則リストの活用法

3. 別のシートの入力規則リストで時刻の上限を設定する

この例では、入力規則リストを使ってセルに入力できる時刻を制限します。さらに、そのリストは別のシート上にあります。目的は、「在庫時刻(Stock Time)列」に条件内の時刻だけを入力できるようにすることです。

手順:

• まず、セル範囲D5:D11を選択します。
• 次に、「データの入力規則」ダイアログボックスを開きます。
• そして、以下のオプションを選択します。
  • 入力値の種類: 時刻(Time)
  • データ: 次の値の間(between)
  • 開始: 「source」シートのセルF14
  • 終了: 「source」シートのセルF15
• 最後に「OK」をクリックします。

これで、「在庫時刻」列には午前8時から午後5時までの時刻のみ入力できるようになりました。

関連コンテンツ: Excelで複数選択対応の入力規則ドロップダウンリストを作成する方法

類似の読み物:

• Excelで入力規則ドロップダウンリストのオートコンプリートを使う方法(2つの方法)
• 別のセルの値に基づくExcelのデータ入力規則
• Excelで英数字のみ許可するデータ入力規則(カスタム数式を使用)
• Excelで複数条件に対応するカスタムデータ入力規則を適用する方法(4つの例)

4. 別のシートの入力規則リストで特定の数値より大きい値のみ許可する

この例では、データセットに新たな列を追加します。整数に対して「より大きい(greater than)」という条件を使い、入力規則リストを適用します。

手順:

• まず、セル範囲E5:E11を選択します。
• 次に、「データ」タブから「データの入力規則」ダイアログボックスを開きます。

その際、類似の警告メッセージが表示されることがあります。「いいえ(NO)」をクリックしてください。

• 続いて、ダイアログボックスで以下を設定します。
  • 入力値の種類: 整数(Whole number)
  • データ: 次の値より大きい(greater than)
  • 最小値: 「source」シートのセルF7
• 最後に「OK」をクリックします。

これで、「販売数量(Amount Sold)列」には0より大きい値のみ入力できるようになります。この入力規則リストを活用すれば、誤った値の入力を防げます。

関連コンテンツ: Excelの入力規則リストから空白を削除する方法(5つの方法)

5. 別のシートの入力規則リストで文字数を制限する

この例では新しいデータセットを使用します。商品の販売に関わる担当者(Salesperson)を入力します。ここでは短い名前だけを入力したいので、セルに入力できる文字数を制限する必要があります。

手順:

• まず、セル範囲C5:C11を選択します。
• 次に、「データ」タブから「データの入力規則」ダイアログボックスを開きます。
• そして、以下のオプションを設定します。
  • 入力値の種類: 文字列長(Text length)
  • データ: 次の値の間(between)
  • 最小値: 「source」シートのセルB40
  • 最大値: 「source」シートのセルB41
• 最後に「OK」をクリックします。

こうして、入力規則リストを活用し、残りのセルも効率的に入力できます。

関連記事: Excelで1つのセルに複数のデータ入力規則を適用する方法(3つの例)

6. 別のシートの入力規則リストで連動ドロップダウンリストを作成する

最後に、入力規則リストを使って連動型ドロップダウンメニューを作成します。ここではINDIRECT関数と名前付き範囲(Named Range)を利用します。

手順:

まず、名前付き範囲を作成します。

• まず、セル範囲B29:C33を選択します。
• 次に、「数式」タブ >>> 「選択範囲から作成(Create from Selection)」をクリックします。

ダイアログボックスが表示されます。

• 「最上行(Top row)」にチェックを入れます。
• 最後に「OK」をクリックします。

画面右上には名前ボックス(Name Box)があります。ここをクリックすると、作成した名前付き範囲を確認できます。

クリックすると、「Beverages(飲料)」と「Snacks(スナック)」という2つの名前付き範囲が登録されているのが分かります。

• 次に、セルB4を選択します。
• そして、「データ」タブから「データの入力規則」ダイアログボックスを開きます。
• 続いて、以下のように設定します。
  • 入力値の種類: リスト(List)
  • 元の値: セル範囲B29:C29を選択
• 最後に「OK」をクリックします。

これで、セルB5に2つのカテゴリーが表示されるようになりました。次のステップは、セルC5に連動するリストを作成することです。

• まず、セル範囲C5:C11を選択します。
• 次に、「データ」タブから「データの入力規則」ダイアログボックスを開きます。
• そして、以下の値を設定します。
  • 入力値の種類: リスト(List)
  • 元の値: 次の数式を入力します。

=INDIRECT(B5)

• 最後に「OK」をクリックします。

セルC5には、「Snacks(スナック)」カテゴリーの商品が表示されていることが確認できます。

さらに、セルB5を「Beverages(飲料)」に変更すると、セルC5の内容も自動的に切り替わります。

この操作を残りのセルにも適用すれば、すべてのセルを連動する値で埋めることができます。

関連記事: Excelで色を使ってデータ入力規則を活用する方法(4つの方法)

練習セクション

各セクションの練習用データセットをExcelファイルに同梱しています。ぜひ実際に試して、これら6つのテクニックをマスターしてください。

まとめ

本記事では、別のシートからデータの入力規則リストを利用する6つの具体例をご紹介しました。もし不明な点や困ったことがあれば、お気軽にコメント欄でお知らせください。ご覧いただきありがとうございました。それでは、快適なExcelライフを!

関連記事

• Excelのデータ入力規則の数式でIFステートメントを使う方法(6つの方法)
• Excel VBAで配列からデータ入力規則リストを作成する方法
• Excelのデータ入力規則でカスタムVLOOKUP数式を使う方法
• [解決済み] Excelでコピペ時にデータ入力規則が機能しない問題の対処法
• Excel VBAで入力規則リストに初期値を設定する方法(マクロとユーザーフォーム)