【Excel】別のセルを参照するデータの入力規則(データバリデーション)設定方法|実例4選

データの入力規則(Data Validation)は、Excelにおける重要な機能のひとつです。本記事では、「別のセルを参照するデータの入力規則」を作成する方法を、具体的な例を交えながらわかりやすく解説します。入力規則を活用すると、リストがより柔軟で使いやすいものになります。列の各セルに個別にデータを入力する代わりに、セル内のドロップダウンリストから必要なデータを選択できるようになるのです。ここでは、連動型(依存型)リストの作成手順に加えて、特定範囲へのデータ入力を制限する方法もご紹介します。

Excelの「データの入力規則」とは?

データの入力規則とは、セルに入力できるデータの種類に関するルールを事前に定義しておけるExcelの機能です。データを入力する際に任意の条件を適用できるため、誤入力を未然に防ぐことができます。入力規則には多くの種類があり、たとえば次のような設定が可能です。

• セルには数値のみ、または文字列のみ入力を許可する
• 特定の範囲内の数値だけを受け付ける
• 指定した範囲外の日付や時刻の入力を制限する

このように、入力規則を利用することで、データを使用する前に正確性や品質を確認でき、入力・保存されるデータの一貫性を維持するための複数のチェック機能を備えることができます。

Excelでデータの入力規則を設定する基本手順

Excelで入力規則を使うには、まずルールを定義します。その後に入力されたデータに対して、設定したルールが自動的に適用されます。データがルールを満たしていればセルへの入力が許可され、満たさない場合はエラーメッセージが表示されます。

ここでは、「生徒ID」「氏名」「年齢」を含むサンプルデータセットを使って、「年齢は18歳未満でなければならない」という入力規則を作成してみましょう。

まず、セルD11を選択します。次に、リボンの[データ]タブに移動し、[データツール]グループにある「データの入力規則」のドロップダウンオプションを選択します。

すると、[データの入力規則]ダイアログボックスが表示されます。[設定]タブを選択し、[入力値の種類]で「整数」を選びます。続いて「空白を無視する」にチェックを入れ、[データ]欄では「次の値より小さい」を選択し、最大値として18を設定します。最後に[OK]をクリックしましょう。

この状態で年齢に「20」と入力すると、入力規則で設定した上限を超えているため、エラーが表示されます。これが入力規則による制御の効果です。

別のセルを参照するデータの入力規則|4つの設定例

Excelで別のセルを参照する入力規則を使うには、いくつかの方法があります。本記事では、INDIRECT関数や名前付き範囲を活用した連動リストの作成方法、セル参照の使い方、そして値の入力制限の設定方法という4つの例を紹介します。いずれの方法も比較的簡単なので、手順に沿ってじっくり理解していきましょう。

方法1:INDIRECT関数を使った連動リストの作成

最初の方法は、INDIRECT関数を利用するものです。入力規則ダイアログボックス内でこの関数を使用すると、特定のセルの値に応じてドロップダウンリストの内容が切り替わります。ここでは、2つの商品とそれぞれの種類を含むデータセットを使用します。

手順を詳しく見ていきましょう。

手順

• まず、3つの列をそれぞれテーブルに変換します。

• 続いて、セル範囲B5〜B6を選択します。
• すると、[テーブルデザイン]タブが表示されます。
• リボンの[テーブルデザイン]タブを開きます。
• [プロパティ]グループでテーブル名を変更します。

• 同様に、セル範囲D5〜D9を選択します。
• [プロパティ]グループでテーブル名を変更します。

• 最後に、セル範囲F5〜F9を選択します。
• 先ほどと同じ手順でテーブル名を変更します。

• 次に、リボンの[数式]タブに移動します。
• [定義された名前]グループから「名前の定義」を選択します。

• [新しい名前]ダイアログボックスが表示されますので、名前を設定します。
• [参照範囲]欄には、以下のように入力します。

=Items[Item]

• [OK]をクリックします。
• 続いて、入力規則を追加する新しい2つの列を作成します。
• セルH5を選択します。

• リボンの[データ]タブを開き、[データツール]グループの「データの入力規則」を選択します。

• [データの入力規則]ダイアログボックスが表示されます。
• 上部の[設定]タブを選択します。
• [入力値の種類]で「リスト」を選びます。
• 「空白を無視する」と「ドロップダウン リストから選択する」にチェックを入れます。
• [元の値]欄に以下を入力します。

=Item

• 最後に[OK]をクリックします。

これで、アイスクリームかジュースを選択できるドロップダウンリストが完成しました。

• 次に、セルI5を選択します。
• リボンの[データ]タブから、再び「データの入力規則」を開きます。

• [データの入力規則]ダイアログボックスが表示されます。
• 上部の[設定]タブを選択します。
• [入力値の種類]で「リスト」を選びます。
• 「空白を無視する」と「ドロップダウン リストから選択する」にチェックを入れます。
• [元の値]欄に以下を入力します。

=INDIRECT(H5)

• 最後に[OK]をクリックします。

これで、商品に応じてフレーバーを選択できるドロップダウンリストが作成されました。ここでは、アイスクリーム用のフレーバーリストが表示されています。

• 商品リストで「ジュース」を選ぶと、フレーバーの候補もそれに応じて自動的に変わります。

方法2:名前付き範囲を活用する

2つ目の方法は、名前付き範囲を利用するものです。テーブルの範囲に名前を付けておき、その名前を入力規則ダイアログボックスで使用します。ここでは、「洋服」「色」「サイズ」を含むデータセットを使用します。

手順は以下の通りです。

手順

• まず、データセットからテーブルを作成します。
• セル範囲B4〜D9を選択します。

• リボンの[挿入]タブに移動します。
• [テーブル]グループから「テーブル」を選択します。

すると、以下のような結果が得られます。

• 次に、リボンの[数式]タブに移動します。
• [定義された名前]グループから「名前の定義」を選択します。

• [新しい名前]ダイアログボックスが表示されるので、名前を設定します。
• [参照範囲]欄には、以下のように入力します。

=Table1[Dress]

• [OK]をクリックします。

• 同じ手順でもう一度「名前の定義」を開き、今度は以下のように設定します。

=Table1[Color]

• [OK]をクリックします。

• サイズ列についても同じ手順で名前を定義します。

• 次に、新しい3つの列を作成します。

• セルF5を選択します。
• リボンの[データ]タブに移動し、[データツール]グループの「データの入力規則」を選択します。

• [データの入力規則]ダイアログボックスが表示されます。
• 上部の[設定]タブを選択します。
• [入力値の種類]で「リスト」を選びます。
• 「空白を無視する」と「ドロップダウン リストから選択する」にチェックを入れます。
• [元の値]欄に以下を入力します。

=Dress

• 最後に[OK]をクリックします。

これで、洋服を選択できるドロップダウンリストが作成されました。

• 同じ要領で、セルG5を選択し、[データ]タブから「データの入力規則」を開きます。
• [設定]タブで「リスト」を選択し、「空白を無視する」「ドロップダウン リストから選択する」にチェックを入れます。
• [元の値]欄に以下を入力します。

=Color

• [OK]をクリックすると、色を選択できるドロップダウンリストが完成します。

• さらに、セルH5についても同様の手順で設定します。[元の値]欄には以下を入力します。

=Size

• [OK]をクリックすると、サイズを選択できるドロップダウンリストが完成します。

方法3:セル参照を直接指定する

3つ目の方法は、入力規則にセル範囲を直接参照させるものです。入力規則ダイアログボックス内でセル参照を指定することで、ドロップダウンリストを簡単に作成できます。ここでは、「都道府県」と「売上金額」を含むデータセットを使用します。

手順は以下の通りです。

手順

• まず、都道府県と売上金額を入力する新しい2つのセルを作成します。
• セルF4を選択します。

• リボンの[データ]タブに移動し、[データツール]グループの「データの入力規則」を選択します。

• [データの入力規則]ダイアログボックスが表示されます。
• 上部の[設定]タブを選択します。
• [入力値の種類]で「リスト」を選びます。
• 「空白を無視する」と「ドロップダウン リストから選択する」にチェックを入れます。
• セル範囲B5〜B12を選択します。
• 最後に[OK]をクリックします。

これで、任意の都道府県を選択できるドロップダウンリストが作成されました。

• 次に、対応する都道府県の売上金額を取得したいので、セルF5を選択します。
• そこに、VLOOKUP関数を使った以下の数式を入力します。

=VLOOKUP(F4,$B$5:$C$12,2,0)

• Enterキーを押して数式を確定させます。

• この後、ドロップダウンリストで都道府県を変更すると、売上金額が自動的に更新されます。

方法4:データの入力規則で値の入力を制限する

最後の方法は、入力規則を使って値の入力自体を制限するものです。あらかじめルールを適用しておけば、指定範囲内のデータは入力を許可し、範囲外のデータにはエラーを表示させることができます。ここでは、「注文ID」「商品」「注文日」「数量」を含むデータセットを使用します。

手順

• この方法では、注文日を2021年1月1日から2022年5月5日の間に制限します。範囲外の日付はエラーとなります。
• まず、セルD10を選択します。
• リボンの[データ]タブに移動します。
• [データツール]グループから「データの入力規則」を選択します。

• [データの入力規則]ダイアログボックスが表示されます。
• 上部の[設定]タブを選択します。
• [入力値の種類]で「日付」を選びます。
• 「空白を無視する」にチェックを入れます。
• [データ]欄で「指定の範囲間」を選択します。
• 開始日と終了日を設定します。
• 最後に[OK]をクリックします。

• この状態で、セルD10に範囲外の日付を入力すると、エラーが表示されます。

隣接するセルの値に基づくデータの入力規則の設定方法

隣接するセルの値を条件にして、入力規則を設定することもできます。たとえば、隣のセルに特定のテキストが入力されている場合にのみ、次の列への書き込みを許可するといった制御が可能です。ここでは、「試験」「評価」「理由」を含むデータセットを使用します。「試験の評価がHard(難しい)である場合にのみ、理由の列に記入できる」ように設定してみましょう。

手順は以下の通りです。

手順

• まず、セル範囲D5〜D9を選択します。

• リボンの[データ]タブに移動し、[データツール]グループの「データの入力規則」を選択します。

• [データの入力規則]ダイアログボックスが表示されます。
• 上部の[設定]タブを選択します。
• [入力値の種類]で「ユーザー設定」を選びます。
• [数式]欄に、以下の数式を入力します。

=$C5="Hard"

• 最後に[OK]をクリックします。

• これで、隣接するセルの値がHardの場合にのみ、理由の列に記述を追加できます。
• 一方、隣接セルの値が異なる場合に記述しようとすると、エラーが表示されます。

まとめ

本記事では、Excelのデータの入力規則を使ってリストを作成する方法を解説しました。INDIRECT関数を利用した別セル参照による連動型リストの作成や、入力規則を使ったデータ入力の制限方法など、実務ですぐに役立つテクニックをご紹介しています。統計処理など、さまざまな業務の場面で活用できる内容ですので、ぜひ参考にしてください。この記事についてご不明点やお困りのことがあれば、ぜひコメントでお知らせください。

関連記事

• カスタム数式を使って英数字のみ入力を許可するExcelデータの入力規則