概要
エクセルのリストや表にデータを記入するときに、以下の問題によく遭遇するはずです。
- テーブル内またはテーブル間でのデータに矛盾やズレ/表記ゆれが生じる。
- 入力するときに、入力内容に悩む。
転記する場合は、コピー元を探す手間がかかる。
このような問題は、
対策案としては以下が有効です。
- 入力できる値を制限する。 規則外の場合にすぐ気付けるようにする 。
- 候補をプルダウン表示可能にする。
エクセルで実装するときには、
- 共通項目は、1つの候補リストで管理できる。
- 候補リストの修正が簡単に行える。
- 「外部参照」への機能拡張もしやすい。
本記事では、
- 「入力が自由」「手がかりがない」と何が問題なのか。
- どのような解決策があるか。
- どうやってエクセルで実装するのか。
について説明します。
エクセルのバージョン
本記事は、 エクセル2024を基に作成しています 。
環境によっては、UIなどの細かな点が異なる可能性があります。
課題：入力が自由 / 手がかりがない
セルに入力できる値（文字列 / 数値）が自由である場合や、
- テーブル内 または テーブル間で、 データに矛盾やズレ / 表記ゆれが生じる 。
- 入力するときに、入力内容を 考える時間が生じる 。
転記する場合は、コピー元を 探す手間がかかる 。
テーブルサイズが大きくなること、または、テーブル数が増えることにより、
制限するルールや候補が不確定で決められない状況では、
解決策：入力制限 / 候補を設定する
上記の問題の解決策としては、以下があげられます。
- 入力できる値を制限する。または、 規則外の場合にすぐ気付けるようにする 。
- 候補をプルダウン表示可能にする。
また、共通の項目（列 / フィールド）間で、
- 入力制限 / 候補リスト1つを、共通の項目（テーブル内 / 外の列）で使い回す 。
エクセルでの実装方法
エクセルで上記を実装する方法を説明します。
使用するエクセル機能
- テーブル ：以下の2種類のデータを別々に格納。
- データの候補/制限リスト
- データ格納テーブル
- データの入力規則 ：入力値の候補表示。規則外の入力を制限。
- 条件付き書式 ：規則外の値をコピペしたときにハイライト表示。
複数のテーブルは同シートでなくてもよい
複数あるテーブルは、
それぞれ異なるシート上でも、同シート上でも構いません。
ただし、ここでは同一ファイル（ブック）内であると仮定します。
「データの入力規則」と「条件付き書式」を併用する理由
「データの入力規則」機能だけでも、入力値の制限と候補の表示は可能 です。
この対応策として 「条件付き書式」を使い、
※1 セルの左上に小さく警告マーク（緑色の三角形）が表示されることもありますが、目立たないため見逃す可能性があります。
「データの入力規則」と「条件付き書式」機能をまとめると以下の通りになります。
| 機能名 | 機能 | 機能するタイミング |
|---|---|---|
| データの入力規則 | 入力制御、候補表示 | 直接入力時 |
| 条件付き書式 | 規則外の入力をハイライト表示 | 直接入力時、 コピペによる入力時 |
実装手順
リストを使って候補を表示 / 入力制限をする方法（手順1 ~ 3）を説明します。
1. テーブルを用意する
「データ格納テーブル」と「候補/制限リスト」の2種類のテーブルを用意します。
作成位置のセルを選択 > 挿入タブ > テーブルボタン（Ctrl + t）
です。
各テーブルは、1つのシート上にまとめても良いですが、
データ格納テーブル
データを入力し、格納するテーブルです。
このテーブルは、データ入力で必要分だけ作成することになります。
候補/制限リスト
データ入力時に候補となる値（文字列、数値）をまとめたリストです。
データ格納テーブル内で、候補表示/制限設定をしたい列（フィールド）の種類分のリストを作成する必要があります。
2. データの入力規則を設定する
入力候補をプルダウン表示できるようにしつつ、
手順は以下の通りです。
- 設定するセル範囲を選択する。
- データの入力規則ウィンドウを表示する。
- 規則を設定するセル範囲を選択 > データタブ > データの入力規則ボタン
- 入力値の種類 を設定する。
- データの入力規則ウィンドウの 設定タブを開いた状態にする。
- 入力値の種類：で リストを選択。
- ドロップダウンリストから選択するにチェック。
- 空白を入力可能にする場合は、空白を無視するにチェック。
- 元の値 を設定する。
- 候補/制限リストの範囲を選択する（ヘッダーは選択不要）※2 。
- OKボタンを押してデータの入力規則ウィンドウを閉じる。
※2 INDIRECT関数を使えば構造化参照を使用できますが、ここでは不要です。
設定内容が同じセルであれば一度に修正可能
設定を修正したいとき、
設定内容が同じセルであれば一度に修正できます。
データの入力規則ウィンドウで、設定を変更し、OKボタンを押す前に
設定タブ上の同じ入力規則が設定されたすべてのセルに変更を適用するにチェックを入れるだけです。
3. 条件付き書式の設定をする
規則外の値が記入されたセルを、
手順は以下の通りです。
- 設定するセル範囲を選択する。
- 新しい書式ルールウィンドウを表示する。
- ホームタブ > 条件付き書式ボタン > セルの強調表示ルール > その他のルール
- ルールに数式を使用する選択をする。
- ルールの種類を選択してください：欄で 数式を使用して、書式設定するセルを決定 を選択する。
- 数式を設定する（次の数式を満たす場合に値を書式設定：欄に記入）。
- 空白を入力可能（規則範囲内）とするとき ：=AND(
1の範囲の先頭セルの位置（相対参照）<>"", COUNTIF(候補/制限リストの範囲（絶対参照）, 1の範囲の先頭セルの位置（相対参照）)=0)を入力 - 数式例：=AND(E4<>"", COUNTIF($B$4:$B$8, E4)=0)
- 空白を入力不可（規則範囲外）とするとき ：=COUNTIF(候補/制限リストの範囲（絶対参照）,
1の範囲の先頭セルの位置（相対参照） )=0を入力 - 数式例：=COUNTIF($B$4:$B$8,E4)=0
- 空白を入力可能（規則範囲内）とするとき ：=AND(
- ハイライト時の書式を設定する。
- 書式ボタン > 塗りつぶしタブ > 赤色
（好みのスタイルで良い）
- 書式ボタン > 塗りつぶしタブ > 赤色
設定を修正するには
条件付き書式の設定を修正するときは、以下の手順で設定ウィンドウを表示できます。
- 条件付き書式ルールの管理ウィンドウ を開く
- ホームタブ > 条件付き書式ボタン > ルールの管理 > その他のルール
- ルールの設定画面を開く
- 修正したいルールを選択 > ルールの編集
さまざまなタイプの入力制限を設定する場合
候補/制限リストのように、
データの整合性を維持するのに役立つので、
| 制限の例 【許容値の例】 | 「データの入力規則」の設定例 ※3 | 「条件付き書式」の設定例 ※3 |
|---|---|---|
| リスト内 の項目のいずれか （リスト：みかん、りんご、ぶどう） 【りんご】 | リスト （=$B$4:$B$8） | =COUNTIF($B$4:$B$8,E4)=0 |
| YES/NOのいずれか 【YES】 | リスト （YES,NO） | =AND(E4<>"YES", E4<>"NO") |
| 0～10の 整数 のみ 【3】 | 整数 （次の値の間 0 と 10） | =NOT(AND( |
| 0以上の小数のみ 【1.5】 | 小数点数 （次の値以上 0） | =NOT(AND( |
| 1～10文字の 文字列 のみ 【Excel】 | 文字列（長さ） （次の値の間, 1, 10） | =NOT(AND(LEN(E4)>=1, |
| 2024/1/1以降の 日付 のみ 【2025/10/20】 | 日付 （次の値以上 2024/1/1） | =NOT(AND(ISNUMBER(E4), |
| 電話番号のみ 【090-1234-5678】 | ユーザー設定 （=AND(COUNTIF(E4, | =NOT(AND( |
| メールアドレスのみ 【user | ユーザー設定 （=ISNUMBER(FIND("@",E4))） | =NOT(ISNUMBER(FIND("@",E4))) |
| 重複なし 【データA】 | ユーザー設定 （=COUNTIF($E:$E,E4)=1） | =COUNTIF($E:$E,E4)>1 |
| 重複のない整数のみ 【1】 | ユーザー設定 （=AND( | =NOT(AND( |
| （上記）＋空白のみ 【（空白）】 | 空白を無視するにチェック | =AND(E4<>"", 上記の式 ) |
※3 表中の数式で使用しているセル参照は、以下の通り。
- $B$4 : リストの範囲の例
- E4 : 自身のセルの例（設定しているセル自身のセルアドレス）。設定セルを 範囲 で指定している場合は先頭セルのみを指定する（例：A1~A9セルに設定しているとき A1）。
「データの入力規則」と「条件付き書式」間での数式の変換
「データの入力規則」の数式（ユーザー設定のとき）を否定したものが、「条件付き書式」の数式に相当します。
=NOT(...)で囲う、または、一番外側のNOT(...)を削除することで、数式を変換可能です。
「データの入力規則」でリスト以外を使用する場合、
データタブ > データの入力規則ボタン > データの入力規則ウィンドウ中の 入力時メッセージ`タブ）。
エクセルで実装後の使い方
上記で作成したエクセルファイルは、
また、この機能の動作時に使われる「 候補/制限リスト 」を修正したい場合についても述べます。
データの入力（手入力）
データ格納テーブルに、手入力（セルを選択して記入する方法）でデータを追加・編集するときについてです。
データを追加する場合は、以下の手順を行います。
- 新しいテーブル行を追加する ※4。
- データ格納テーブルの末尾（右下）のセルを選択 > tabを押す。
- 記入したいセルに移動して記入する。
- 候補が設定されている列の場合 ：
セルの右側の▼マークから候補をプルダウンを表示して選択可 （Alt + ↓）。 手入力も可能。 - 候補が設定されていない列の場合 ：
入力時メッセージが設定されていれば、 「何を入力したら良いか」が表示される ので、それに従い手入力する。
- 候補が設定されている列の場合 ：
- セルが赤くハイライトされていないか確認する。
- セルが赤く表示される場合、 入力値が想定外の形式（入力制限外）となっているため、修正する 。
データを編集する場合は、上記の2, 3を行います。
※4 テーブルに集計行がなく、かつテーブル下が空白の場合、「行の追加」はスキップ可能。
データの入力（コピペ）
データ格納テーブルに、コピペ（コピー&ペースト）でデータを追加・編集するときについてです。
データを追加する場合は、以下の手順を行います。
- コピーしたいセル or 行をコピーする（Ctrl + c）
- 新しいテーブル行を追加する ※4。
- データ格納テーブルの末尾（右下）のセルを選択 > tabを押す。
- 貼り付け先のセルを選択し、「 値 」を貼り付ける（Ctrl + Shift + v ※5）
- 通常の貼り付け（Ctrl + v）を行うと、「データの入力規則」と「条件付き書式」の設定が消えるので注意
- セルが赤くハイライトされていないか確認する。
- セルが赤く表示される場合、 入力値が想定外の形式（入力制限外）となっているため、修正する 。
データを編集する場合は、上記の1, 3, 4を行います。
※5 旧バージョンのエクセルの場合 Ctrl + Alt + v → v → Enter
候補/制限リストの修正
データ格納テーブルにデータを入力するときの
リスト内に重複がある場合は無視されます。
まとめ
- 「入力値を制限、規則外の場合にハイライト」
- 「候補をプルダウン表示」
を利用することで、
- 「データの矛盾、ズレ/表記ゆれの防止」
- 「入力時での内容を考える時間、転記時でのコピー元を探す手間の短縮」
が期待できます。
これは、エクセルの
次のステップ：複数ファイル構成との比較
本記事では、テーブルを候補/規則リストとして、複数のテーブルから参照する構成を取り扱っています。
- 複数テーブルを1テーブルにまとめる
- 追加で使用する機能： リレーションシップ / ピボットテーブル
- 複数のテーブルを連結した1つのテーブル（読み取り専用）が作成できます。
このテーブルは、 閲覧性・検索性が高く、関数無しで集計も可能 です。
- 候補/制限リストを外部から参照する
- 追加で使用する機能： パワークエリ
- 別ファイル内のデータを読み取り、候補/制限リストで使用します。
異なる複数のファイルに、1つの候補/制限を適用できるようになります。 ルールを1か所で管理できる ため、データの管理・整合性の維持が容易になります。