Excelテーブルで重複入力を防止し別表の最大値を上限にする入力規則の作り方

Excelのテーブルで顧客番号や伝票番号を管理していると、「同じ番号を二重入力してしまった」「別シートの最大No.を超えた番号を入れてしまった」という事故がよく起きます。本記事では、Excelテーブルに入力規則（データの入力規則）を設定し、「重複の禁止」と「別表の最大値を上限にする」という2つの条件を、テーブルの行数増減にも自動追従する形で同時に満たす方法を、実務向けに詳しく解説します。

シナリオと課題整理：ExcelテーブルのNo.管理で困るポイント

まず、この記事で扱う前提シナリオを整理します。

入力用テーブル：tblDataEntry の No. 列（tblDataEntry[No.]） に、作業者が番号を入力する。
マスタ側テーブル：tblClientInfo の No. 列（tblClientInfo[No.]） に、既存の顧客番号やIDが格納されている。

このとき、現場でよく起きる課題は次の2つです。

課題	内容	よくある対処	問題点
課題①	入力テーブル `tblDataEntry[No.]` で番号の重複入力を防ぎたい	`=COUNTIF($A$7:$A$84,A7)=1` のように固定範囲で入力規則をかける	テーブルの行数が増減すると範囲が追従せず、重複チェックが漏れる
課題②	マスタ側 `tblClientInfo[No.]` の最大値を上限にしたい（それ以上の番号は入力させたくない）	上限値を手入力で「1000」などと入力規則に設定する	マスタ側に新しい顧客が追加されても、上限値を手動で更新しないといけない

この記事では、これらを Excelテーブル＋構造化参照＋COUNTIF＋MAX を組み合わせて、動的に解決する方法を解説します。

今回実現するゴール

最終的に、入力テーブル tblDataEntry[No.] に対して次のような入力ルールを実現します。

同じ番号は 一度しか入力できない（重複禁止）
tblClientInfo[No.] の最大値を上限として、それより大きい番号は入力できない
No. は 数値のみ 許可する（文字列や記号をブロック）
空白セルは許可（業務要件に応じて切り替え可）
テーブルの行が増減しても、自動的にチェック範囲が拡張・縮小される

ポイントは、これらをすべて 1つの「カスタム」入力規則の数式 にまとめていることです。

サンプル構成：テーブル名と列名を確認する

実際に設定する前に、使うテーブルと列名を整理しておきます。テーブル名は「テーブルデザイン」タブから変更できます。

役割	テーブル名	主に使う列	想定例
入力用テーブル	`tblDataEntry`	`tblDataEntry[No.]`	日々の入力を行うシートのテーブル
マスタ用テーブル	`tblClientInfo`	`tblClientInfo[No.]`	顧客マスタや取引先マスタのテーブル

以降の数式は、このテーブル名・列名を前提に書いています。ご自身のブックでは、実際の名前に置き換えてご利用ください。

重複入力を防ぐ基本：COUNTIF＋INDEXで「テーブル列全体」を動的参照

まずは課題①、「重複入力の防止」からです。通常、重複チェックには次のような式を使います。

=COUNTIF($A$7:$A$84,A7)=1

しかし、この式は セル範囲を固定 しているため、テーブルに行を追加・削除しても、範囲が変化しません。そこで、テーブルの列全体を参照するために INDEX関数を使って構造化参照を包む のがコツです。

部分	意味
`tblDataEntry[No.]`	テーブル `tblDataEntry` の `No.` 列（構造化参照）
`INDEX(tblDataEntry[No.],0)`	行番号に 0 を指定することで、その列全体を配列として取得する
`[@No.]`	現在入力中の行の `No.` 列（構造化参照の「行参照」）
`COUNTIF(列全体, 現在行の値)`	列全体の中で、現在入力している値が何回出てくるかをカウント
`=1`	1回だけ出現していればOK（2回以上あれば重複なのでNG）

構造化参照が入力規則で使えない場合の代替式

Excelのバージョンや設定によっては、入力規則の数式で構造化参照を直接書くとエラーになる環境があります。その場合は、列全体は構造化参照＋INDEXで確保しつつ、現在行だけセル番地で指定 する形にします。

手順：最初のデータセル（例：A7）をアクティブにしてから入力規則を設定する。

=COUNTIF(INDEX(tblDataEntry[No.],0),A7)=1

列全体に適用すると、A7がA8、A9…と自動的に相対参照でずれていきます。

空欄を許可したい場合

番号が未確定の行などでは、空欄のままにしておきたいこともあります。その場合は、OR で「空欄ならOK」という条件を先に通します。

=OR([@No.]="", COUNTIF(INDEX(tblDataEntry[No.],0),[@No.])=1)

これで、

空欄 → 許可
値あり → 列全体で1回だけなら許可

という動作になります。

Excelでの具体的な設定手順（重複チェック）

ここまでの内容を、実際の操作手順としてまとめます。

入力テーブル tblDataEntry 内の No. 列のデータ部分 を選択します。
見出し行を除いて、最初のデータセルからテーブルの最下行までを選択します（テーブル内ならどこか1セル選んで列選択してもOK）。
リボンの [データ] タブ → [データの入力規則] をクリックします。
[設定] タブで、[許可] を「ユーザー設定」 に変更します。
[数式] に次の式を入力します（空欄も許可する重複チェック例）。
=OR([@No.]="", COUNTIF(INDEX(tblDataEntry[No.],0),[@No.])=1)
[エラーメッセージ] タブで、エラー表示を分かりやすく設定します。
例：タイトル「入力不可」、メッセージ「No.が重複しています。別の番号を入力してください。」

これで、テーブルの行が増えても、重複チェックが自動的に拡張される 入力規則が完成します。

重複＋最大値上限を同時に満たす入力規則

ここからが本題の課題②です。「重複を防ぐ」だけでなく、別表のNo.列の最大値を上限として、それを超える番号も禁止する という条件を組み込みます。

利用するのは、

COUNTIF：重複チェック
MAX：別表の最大値
ISNUMBER：数値かどうかのチェック
AND / OR：条件の組み合わせ

これらを1つの式にまとめることで、No.列のルールを一元管理できるようにします。

完成形の構造化参照バージョン

入力テーブル tblDataEntry[No.] に次の入力規則を設定します。

=OR(
  [@No.]="",
  AND(
    ISNUMBER([@No.]),
    COUNTIF(INDEX(tblDataEntry[No.],0),[@No.])=1,
    [@No.]&lt;=MAX(INDEX(tblClientInfo[No.],0))
  )
)

この式の動作を整理すると、次のようになります。

条件	説明
`[@No.]=" "`	No. が空欄なら、無条件で許可（OR の1つ目の条件）
`ISNUMBER([@No.])`	No. が数値かどうか判定。文字列や記号はブロック（必要なければ削除可）
`COUNTIF(INDEX(tblDataEntry[No.],0),[@No.])=1`	入力テーブル内で同じ番号が1回だけ → 重複でなければOK
`[@No.]<=MAX(INDEX(tblClientInfo[No.],0))`	マスタの `tblClientInfo[No.]` の最大値を上限として、それ以下ならOK
`AND(...)`	空欄以外のときは、上の3条件をすべて満たす必要がある
`OR(空欄, AND(...))`	空欄は許可、それ以外は「数値かつ一意かつ最大値以下」であれば許可

構造化参照がエラーになる環境向け（A7からの相対参照版）

入力規則の数式で構造化参照が使えない場合は、次のように「列全体は INDEX＋構造化参照」、「現在行の値はセル番地」で書き換えます。

前提：No. 列の最初のデータセルが A7 であるとして、A7 をアクティブにして入力規則を設定する。

=OR(
  A7="",
  AND(
    ISNUMBER(A7),
    COUNTIF(INDEX(tblDataEntry[No.],0),A7)=1,
    A7&lt;=MAX(INDEX(tblClientInfo[No.],0))
  )
)

列全体に適用すると、A7 が A8、A9…と相対参照でずれていきます。tblDataEntry[No.] と tblClientInfo[No.] の部分はテーブル名さえ合っていれば、テーブルサイズの増減に自動で追従します。

入力規則の設定手順（重複＋上限チェック版）

重複チェックと上限チェックを同時に行う入力規則の設定手順をまとめます。

入力テーブルの No. 列のデータ部分を選択します。
リボンの [データ] タブ → [データの入力規則] をクリックします。
[設定] タブで [許可] → [ユーザー設定] を選びます。
[数式] に次の式を貼り付けます（構造化参照が動く環境の場合）。
=OR( [@No.]="", AND( ISNUMBER([@No.]), COUNTIF(INDEX(tblDataEntry[No.],0),[@No.])=1, [@No.]<=MAX(INDEX(tblClientInfo[No.],0)) ) )
[エラーメッセージ] タブで、ユーザーに伝わるメッセージを設定します。
例：
・タイトル：入力不可
・メッセージ：「No. が重複しているか、顧客No.の最大値を超えています。数値で一意のNo.を入力してください。」
[OK] を押して閉じます。

これで、次のような動作になります。

すでに存在するNo.を入力 → エラーメッセージが表示され、入力が拒否される
マスタの最大No.より大きい番号を入力 → エラーメッセージが表示される
文字列（例："ABC"）を入力 → ISNUMBER が偽になり、エラー
空欄のまま → 許可

よく使うバリエーションの一覧（コピペ用）

実務では「空欄を許可したいかどうか」「数値以外も許可するか」など、細かい要件が変わることがあります。よくあるパターンを表にまとめます。

目的	空欄	数値以外	入力規則の数式（構造化参照版）
重複のみ禁止	許可	許可	`=OR([@No.]="", COUNTIF(INDEX(tblDataEntry[No.],0),[@No.])=1)`
重複のみ禁止	不許可	許可	`=COUNTIF(INDEX(tblDataEntry[No.],0),[@No.])=1`
重複＋上限	許可	不許可（数値のみ）	`=OR([@No.]="", AND(ISNUMBER([@No.]), COUNTIF(INDEX(tblDataEntry[No.],0),[@No.])=1, [@No.]<=MAX(INDEX(tblClientInfo[No.],0))))`
重複＋上限	不許可	不許可（数値のみ）	`=AND(ISNUMBER([@No.]), COUNTIF(INDEX(tblDataEntry[No.],0),[@No.])=1, [@No.]<=MAX(INDEX(tblClientInfo[No.],0)))`

考え方はシンプルで、

空欄を許可したい → 先頭に OR([@No.]="", ...) を追加
数値だけ許可したい → AND の条件に ISNUMBER([@No.]) を追加

というルールで組み合わせているだけです。

関数の補足：INDEXを使う理由とINDIRECTを避ける理由

なぜ INDEX(tblDataEntry[No.],0) を使うのか

構造化参照は、そのまま入力規則の数式に書ける場合もありますが、環境によってはエラーになることがあります。また、構造化参照を配列として扱いたいとき、INDEX(列参照,0) という書き方が非常に強力です。

メリット1：テーブルの行が増減しても、列全体を常に参照してくれる
メリット2：INDEX は非ボラタイル関数のため、INDIRECT より計算が重くなりにくい
メリット3：列の挿入・削除などにも比較的強い

例えば、

INDEX(tblDataEntry[No.],0) → テーブル列 No. の全行
INDEX(tblClientInfo[No.],0) → マスタ側の No.列全体

というイメージで覚えておくと便利です。

INDIRECT を避けたほうがよい理由

INDIRECT 関数を使うと、「テーブル名を文字列から組み立てる」といった柔軟なこともできますが、

ブック内のどこかが変更されるたびに 必ず再計算される（ボラタイル関数）
テーブル名を変更したとき、文字列側も手動で直す必要がある

などの理由から、実務では「壊れやすい・重くなりやすい」式になりがちです。今回のような テーブル列全体を参照する用途では、INDEX(…,0) のほうが安全 です。

実務でハマりがちなポイントと対策

既に存在する重複は自動で直らない

入力規則は「新しく入力される値」をチェックする仕組みです。そのため、入力規則を設定した時点で、すでに列内に重複があっても、自動で修正されたり、強制的にエラーになったりはしません。

既存のデータをクリーンアップしたい場合は、

[データ] → [重複の削除] で重複レコードを整理する
必要であれば、一時的に並べ替え・フィルタ・ピボットテーブルなどで重複を確認する

といった手動のメンテナンスを一度行うのがおすすめです。

1 と 01 は別物：No.列は「数値」で統一する

Excelでは、

1（数値）
"01"（文字列）

は別の値として扱われます。No.列で "01" と 1 を混在させると、

見た目は同じでも、COUNTIF 的には別物としてカウントされる
VLOOKUP / XLOOKUP などの検索で一致しないトラブルが起きる

といった問題の原因になります。

今回の入力規則に ISNUMBER を入れているのは、No.列を数値で統一するための「ガードレール」 という意味合いもあります。桁数を揃えたい場合は、

セルの表示形式で 0000 のように設定する（値は数値のまま）

という方法を使うと、0001 のように表示しつつ、内部的には数値として扱うことができます。

地域設定によっては区切り文字が「;」になる

日本語版Excelでも、環境によっては関数の区切り文字が , ではなく ; になっていることがあります。その場合、

AND(ISNUMBER([@No.]), COUNTIF(...), ...)

のような式は、

AND(ISNUMBER([@No.]); COUNTIF(...); ...)

といった形に読み替えてください。「式を貼り付けたらエラーが出たら、カンマとセミコロンを見直す」 のがトラブルシューティングの定番です。

「整数」などのモードとカスタム式は併用できない

入力規則では、

「整数」「小数」「リスト」などの組み込みモード
「ユーザー設定」のカスタム数式

は 同時に使えません。今回のように、「数値であること」「重複していないこと」「最大値以下であること」を 1つの式の中でまとめている理由 はここにあります。

強制したいルールが複数ある場合は、

AND と OR を使って、1つの論理式としてまとめる

という考え方にしておくと、後から条件を追加・変更するときも楽になります。

条件付き書式で「重複セルを光らせる」視覚的な補助

入力規則だけでも十分な抑止力になりますが、「どこが重複しているかを一目で見たい」ケースも多いと思います。その場合は、入力規則とは別に 条件付き書式 を併用するのがおすすめです。

重複セルをハイライトする条件付き書式の例

tblDataEntry[No.] のデータ部分を選択します。
[ホーム] → [条件付き書式] → [新しいルール] をクリックします。
「数式を使用して、書式設定するセルを決定」を選びます。
次の数式を入力します（A7が先頭セルの場合）。
=COUNTIF(INDEX(tblDataEntry[No.],0),A7)>1
書式ボタンから、背景色や文字色を設定します。

これで、同じNo.が2つ以上存在するセルは自動的に色がつきます。入力規則でエラーを出しつつ、条件付き書式で視覚的な確認も補助する と、ダブルチェックの体制が作れます。

まとめ：ExcelテーブルのNo.列は「1つの式で守る」

最後に、この記事の内容をコンパクトに振り返ります。

重複防止の基本形：
=COUNTIF(INDEX(tblDataEntry[No.],0),[@No.])=1
テーブルの列全体を INDEX(…,0) で動的に参照し、テーブルの行増減に自動追従 させます。
空欄許可や数値限定：
OR と ISNUMBER を組み合わせて、
=OR([@No.]="", AND(ISNUMBER([@No.]), ...))
のように1つの式でまとめます。
別表の最大値を上限にする：
MAX(INDEX(tblClientInfo[No.],0)) でマスタ側の最大No.を取得し、
[@No.]<=MAX(INDEX(tblClientInfo[No.],0))
という条件を AND に組み込みます。
完成形の入力規則（構造化参照版）：
=OR( [@No.]="", AND( ISNUMBER([@No.]), COUNTIF(INDEX(tblDataEntry[No.],0),[@No.])=1, [@No.]<=MAX(INDEX(tblClientInfo[No.],0)) ) ) これを tblDataEntry[No.] のデータ部分に「ユーザー設定」の入力規則として設定すれば、重複禁止＋上限チェック＋数値限定 が、テーブルの増減にも強い形で実現できます。
INDIRECT より INDEX：
実務で長く使うブックでは、なるべく INDIRECT を避け、INDEX(…,0) で列全体を扱うと、壊れにくく、計算も軽くなります。
条件付き書式で視覚的な保険：
=COUNTIF(INDEX(tblDataEntry[No.],0),A7)>1 などで重複セルをハイライトすれば、目視確認もしやすくなります。

Excelテーブルは、正しく入力規則と構造化参照を組み合わせることで、ちょっとした「業務アプリ」のように振る舞わせることができます。今回のパターンをひな形として、自社のマスタテーブルや受付テーブルに合わせてアレンジすれば、入力ミスによる手戻りを大きく減らすことができるはずです。

この記事を書いた人

佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

Excelテーブルで重複入力を防止し別表の最大値を上限にする入力規則の作り方

シナリオと課題整理：ExcelテーブルのNo.管理で困るポイント

今回実現するゴール

サンプル構成：テーブル名と列名を確認する

重複入力を防ぐ基本：COUNTIF＋INDEXで「テーブル列全体」を動的参照

おすすめの入力規則（重複チェックのみ）

構造化参照が入力規則で使えない場合の代替式

空欄を許可したい場合

Excelでの具体的な設定手順（重複チェック）

重複＋最大値上限を同時に満たす入力規則

完成形の構造化参照バージョン

構造化参照がエラーになる環境向け（A7からの相対参照版）

入力規則の設定手順（重複＋上限チェック版）

よく使うバリエーションの一覧（コピペ用）

関数の補足：INDEXを使う理由とINDIRECTを避ける理由

なぜ INDEX(tblDataEntry[No.],0) を使うのか

INDIRECT を避けたほうがよい理由

実務でハマりがちなポイントと対策

既に存在する重複は自動で直らない

1 と 01 は別物：No.列は「数値」で統一する

地域設定によっては区切り文字が「;」になる

「整数」などのモードとカスタム式は併用できない

条件付き書式で「重複セルを光らせる」視覚的な補助

重複セルをハイライトする条件付き書式の例

まとめ：ExcelテーブルのNo.列は「1つの式で守る」

この記事を書いた人

コメント

コメントするコメントをキャンセル

Excelテーブルで重複入力を防止し別表の最大値を上限にする入力規則の作り方

シナリオと課題整理：ExcelテーブルのNo.管理で困るポイント

今回実現するゴール

サンプル構成：テーブル名と列名を確認する

重複入力を防ぐ基本：COUNTIF＋INDEXで「テーブル列全体」を動的参照

おすすめの入力規則（重複チェックのみ）

構造化参照が入力規則で使えない場合の代替式

空欄を許可したい場合

Excelでの具体的な設定手順（重複チェック）

重複＋最大値上限を同時に満たす入力規則

完成形の構造化参照バージョン

構造化参照がエラーになる環境向け（A7からの相対参照版）

入力規則の設定手順（重複＋上限チェック版）

よく使うバリエーションの一覧（コピペ用）

関数の補足：INDEXを使う理由とINDIRECTを避ける理由

なぜ INDEX(tblDataEntry[No.],0) を使うのか

INDIRECT を避けたほうがよい理由

実務でハマりがちなポイントと対策

既に存在する重複は自動で直らない

1 と 01 は別物：No.列は「数値」で統一する

地域設定によっては区切り文字が「;」になる

「整数」などのモードとカスタム式は併用できない

条件付き書式で「重複セルを光らせる」視覚的な補助

重複セルをハイライトする条件付き書式の例

まとめ：ExcelテーブルのNo.列は「1つの式で守る」

この記事を書いた人

関連記事

コメント

コメントする コメントをキャンセル

コメントするコメントをキャンセル