Excel データ検証のマスタリー:包括的なステップバイステップ ガイド

このチュートリアルでは、データ検証の目的から Excel ワークシートに適用する方法まで、データ検証に関するすべてを学びます。

上の画像では、「在庫数量」という名前の列にデータ検証を適用しているため、セルへの入力として数値のみが保存されます。テキスト値を入力すると、「この値は、このセルに定義されたデータ検証制限と一致しません」というメッセージ ボックスが表示されます。これがデータ検証の仕組みです。

⏷Excelでのデータ検証
⏷データ検証の利用目的
⏷データ検証入力の種類
⏷1. Excel でデータ検証を適用するにはどうすればよいですか?
   ⏵1.1. Excel セルにデータ検証を適用する
   ⏵1.2. 1 つのセルに複数のデータ検証ルールを適用する
   ⏵1.3。別のセルに基づいてデータ検証を適用する
   ⏵1.4。チェックボックスを使用してデータ検証を適用する
⏷2.データ検証を使用してドロップダウン リストを作成する
⏷3. Excel で色を使用してデータ検証を使用する
⏷4. Excel でのカスタム データ検証のアプリケーション
   ⏵4.1.データ検証で英数字のみの入力を許可する
   ⏵4.2 カスタム データ検証で数値のみを許可する
   ⏵カスタム Excel データ検証ルールが機能しない
⏷5.データ検証の編集
⏷6.データ検証を他の Excel セルにコピーします。
⏷データ検証ルールを含むセルを検索する
⏷Excel のデータ検証を削除しますか?
⏷データ検証を適用するための役立つヒント
⏷データ検証の制限
⏷データ検証の問題とその解決方法

この Excel ブログ投稿を通じて、

• データ検証を適用する（セル内、複数のルール、別のセルに基づくルール、チェックボックス付き）
• データ検証を使用してドロップダウン リストを作成する
• データ検証を使用する
• カスタム データ検証を適用する (英数字のみ、数字のみ)
• データ検証の編集
• データ検証の検索
• データ検証を削除する

注: Microsoft 365 を使用しました。 この記事用のデータセットを準備します。前述のメソッドは Excel 2007 以降のバージョンに適用できます。

Excel のデータ検証とは何ですか?

Excel のデータ検証は、セルに入力されるデータの種類を制御できる機能です。入力できる内容のルールを設定することで、精度と一貫性を維持するのに役立ちます。たとえば、エントリを整数、小数、日付、または事前定義されたリストに制限できます。

Excel でデータ検証を使用する目的は何ですか?

Excel ワークシートでデータ検証を使用できるのは、次の理由からです。

• セルに間違ったデータを入力するのを防ぎます。
• データを均一な形式に保ちます。
• データ入力が簡素化され、高速化されます。
• 全体的なデータの信頼性が向上します。
• ポジティブなユーザー エクスペリエンスを創出します。
• 独自の要件に合わせて数式を使用したカスタム ルールを許可します。

Excel でのすべての種類のデータ検証入力とは何ですか?

1 – [設定] タブ
2 つの入力ボックスがあります:許可 とデータ 。データ検証のルールを設定します。

a) ボックスを許可:
データ検証のタイプを選択できます。

• 整数: 整数のみを許可します。
• 10 進数: 設定された精度で 10 進数を許可します。
• リスト: リストから選択できるドロップダウン リストを作成します。
• 日付: エントリが指定された日付範囲内にあることを確認します。
• 時間: 時間のみを許可します。
• テキストの長さ: 許可される文字数を制御します。
• カスタム: これにより、数式を使用して独自のルールを設定できます。

b) データ ボックス:
データ内 [許可 ] タブで選択した検証に基づいて基準を定義する必要があります。 タブ。

• 整数: 最小値と最大値を指定します。
• 10 進数: 許可される小数点以下の桁数を設定します。
• リスト: 受け入れ可能なエントリのリストを作成します。
• 日付/時刻: 日付または時刻の最小値と最大値を設定します。
• テキストの長さ: 最小文字数と最大文字数を決定します。
• カスタム: 検証用のカスタム数式を入力します。

2 – [入力メッセージ] タブ:
このタブを使用して、メッセージをカスタマイズして指示を与えることができます。

「セルが選択されたときに入力メッセージを表示」ボックス: ユーザーが検証されたセルを選択するとメッセージを表示します。

「タイトルとメッセージの入力」ボックス: 通知のタイトルとメッセージをカスタマイズします。

3 – [エラー アラート] タブ:

a) 「無効なデータが入力された後にエラー警告を表示する」: ユーザーが無効な情報を入力した場合に通知を受け取るようにします。

b) スタイル:
このボックスからエラー アラートのタイプを選択します:

• 停止（進入禁止）
• 警告 (ブロックせずに通知します)
• 情報 (ブロックせずに情報を提供します)。

c) タイトルとエラー メッセージ: エラー アラートのタイトルとメッセージをカスタマイズします。

ガイド 1 – Excel でデータ検証を適用する方法

• 検証を行うセルまたは範囲を選択します。
• 「データ」に移動します。
• [データ検証] をクリックします。 」をクリックし、必要な検証タイプを選択します。
• 要件に基づいてすべてのタブで条件を設定し、[OK] をクリックします。 ” を使用して検証を適用します。

さらに具体的な例については、以下をお読みください。

ケース 1.1 – Excel セルにデータ検証を適用する方法

以下のようなデータセットがあり、「生年月日」という名前の列にデータ検証を適用します。 ” 日付のみを保存します。

• F6:F19 の範囲を選択します。 .
• データ をクリックします。 タブをクリックしてデータ検証を選択します。

• 日付を選択します 許可 で ボックス。
• 「より大きい」を選択します
• 開始日として 1900 年 1 月 1 日 (mm/dd/yyyy) を設定します。 .
• [OK] をクリックします。 .

• 列のセルは、mm/dd/yyyy 形式で日付を入力として受け入れます。そうしないと、Excel に「この値は、このセルに定義されたデータ検証制限と一致しません。」というエラー メッセージが表示されます。

• 適切なデータを入力すると許可されます。

ケース 1.2 – 1 つのセルに複数のデータ検証ルールを適用する方法

以下にデータセットがあり、セル E6 で入力を取得して検証します。 基準 1 (価格が 50 を超える) と基準 2 (リスト内の製品のみ) に基づきます。

• セルE6を選択します .
• データ をクリックします。 タブをクリックし、データ検証に移動します。

• [カスタム ] を選択します。 の許可します。
• 式 で次の式を使用します。 ボックス:

=OR(E6>$B$6, COUNTIF($B$9:$B$12,E6)=1)

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

• これにより、セルE6のデータ検証が有効になります。 。 40 を入力すると、条件を満たしていないため、すぐにエラー メッセージが表示されます。

• 70 (50 より大きい) を入力すると、基準 1 を満たすため、この値が有効なデータとして受け入れられます。

• セルE6に「ココナッツ」と入力すると 、基準 2 を満たすため、これも受け入れられます。

ケース 1.3 – Excel の別のセルに基づいてデータ検証を適用する方法

セルF6:F11にデータがあるとします。 。これを使用して、「Designation」という名前の列の値を検証します。

• F5:F11 の範囲を選択します。 .
• Ctrl + T を押します。
• テーブルにヘッダーがあることを確認します。  オプション。
• [OK] をクリックします。 .

• 作成したテーブルを選択します。
• [テーブル デザイン] をクリックします。
• テーブル名を設定します。 「情報」として。

• D6:D16 の範囲を選択します。 .
• データ検証を挿入します。 .

• リストを選択します の場合は許可します。
• [ソース] ボックスに以下の数式を使用します。

=INDIRECT(“Info [Designation]”)

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

• これにより、テーブルに基づいてドロップダウン リストが作成されます。次に、「指定」列で、リストを使用してデータを入力します。

• 必要に応じて値を選択できます。

ケース 1.4 – Excel のチェックボックスを使用してデータ検証を適用する方法

チェック ボックスを使用する場合は、チェック ボックスをクリックしてエントリのデータ検証を有効にする必要があります。

以下のデータセットについて考えてみましょう。チェック ボックスを使用して、「Name」と「Leave (Taken)」という名前の 2 つの列にデータ検証を適用します。

• [開発者] に移動します。  挿入して  チェックボックス。

• チェックボックスをオンにします。
• 右クリックして [テキストの編集] を選択します。

• 名前を「Validate Name」に変更します。

• 繰り返して別のチェックボックスを追加し、名前を「休暇の検証 (取得)」に変更します。

• 最初のチェックボックスをオンにします。
• 右クリックして [コントロールの書式設定] を選択します。 .

• コントロールをクリックします。 オブジェクトの書式設定タブ ダイアログボックス。
• セル $G$6 を選択します セルリンク内 .
• [OK] をクリックします。 .

• チェックボックスに基づいて、TRUE が返されます。 または 誤り 隣接するセルG6にあります。
• G7 へのリンク もう 1 つのチェックボックスは「休暇 (取得済み) を検証する」とチェックします。

• C6:C16 の範囲を選択します。 .
• データ検証を挿入します。 .

• [カスタム ] を選択します。
• この数式を数式ボックスにコピーします。

=IF($G$6, ISTEXT(C5:C16),FALSE)

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

• データ検証がアクティブ化されます。 [名前] 列に「Marylin Pittaman」と入力しようとすると、[名前の検証] チェック ボックスがオンになっていないため、エラー メッセージ ボックスが返されます。

• [名前の検証] チェックボックスをオンにすると、入力として「Marylin Pittaman」が受け入れられます。これにより、チェック ボックスが [名前] 列のセルに入力されたデータを検証します。

• D6:D16 の範囲を選択します。 .
• 新しいデータ検証を挿入します。 .
• [許可] で [カスタム] を選択します。 ボックス。
• 次の式を数式 に入力します。 ボックス。

=IF($G$7, ISNUMBER(D6:D16),FALSE)

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

• 2 番目のチェックボックスをオフにすると、「Leave (Taken)」列に数字を入力できなくなります。

• [休暇 (取得) を検証する] チェックボックスをオンにすると、セルに任意の数値を入力できるようになります。

• 両方のチェックボックスをオンにすると、列にデータを入力できます。結果は以下の画像のようになります。

ガイド 2 – Excel でデータ検証を含むドロップダウン リストを作成する方法

シート内の既存のデータから、次の図に示すようなドロップダウン リストを作成するとします。

• D6:D16 の範囲を選択します。 .
• データ検証を挿入します。 .

• リストを選択します 許可 で ボックス。
• ソース をクリックします。 アイコン。

• F6:F11 の範囲を選択します。 。これらの項目はドロップダウン リストに追加されます。

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

• 任意のセルをクリックします。
• 右側のドロップダウン アイコンを選択します。
• ドロップダウン リストが表示され、必要な項目を選択できます。

• 以下の画像に示すように、ドロップダウン リストを使用して列を埋めました。

ガイド 3 – Excel でカラーを使用してデータ検証を使用する方法

以下のデータセットがあるとします。 「取得した数値」列では、整数 (40 より大きい) のみを入力として許可し、条件付き書式設定を使用して条件に基づいてそれらの値を強調表示します。

• D6:D16 の範囲を選択します。 .
• データ検証を挿入します。
• 整数を選択します 許可 で ボックス。
• 間 を選択します データボックスに入力します。
• 40 を設定します 最小値として 100 最大値として。
• [OK] をクリックします。 .

• これにより、この範囲のデータ検証が有効になります。 40 ～ 100 の数値を入力できます。40 未満の数値 (つまり 34) を入力すると、エラー メッセージ ボックスが返されます。

• 範囲D6:D16を選択します。
• [条件付き書式]をクリックします。 をクリックして新しいルールを選択します。

• [次の内容を含むセルのみを書式設定する] をクリックします。 ”。
• 間を選択します。
• [次のセルのみを書式設定] の下のボックスに数値 40 と 59 を入力します。 ” オプション。
• [フォーマット ] をクリックします。 色を追加します。

• [塗りつぶし ] をクリックします。 セルの書式設定で ダイアログボックス。
• 希望の色を選択します。
• [OK] をクリックします。 .

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

• 列内で、60 未満の数値が選択した色で強調表示されます。

• 新しい条件付き書式ルールを使用して、60 ～ 100 の範囲で繰り返します。

• 別の塗りつぶしの色を選択します。

• これにより、以下の画像に示すように、60 を超える数値が選択した色で強調表示されます。

ガイド 4 – Excel でのカスタム データ検証の適用

画像に示すように、[データ検証] ダイアログ ボックスのカスタム ルールからデータのルール/式を設定しました。

ケース 4.1 – データ検証で英数字のみの入力を許可する方法

英数字は、アルファベット (文字) と数字 (数字) の組み合わせです。以下にデータセットがあり、「モデル」列に英数字を追加します。

• 範囲D6:D16を選択します。
• データ検証に移動します。 .
• [カスタム ] を選択します。 許可 で ボックス。
• 次の式を使用します。

=ISNUMBER(SUMPRODUCT(SEARCH(MID(B5, ROW(INDIRECT("1:"&LEN(D6))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

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

• これにより、範囲内の英数字のみが許可されます。

• セルには英数字のみを保存できます。

ケース 4.2 – Excel のカスタム データ検証で数値のみを許可する

以下の画像のようなデータセットがあるとします。  「在庫数量」列には数値のみを許可したいと考えています。

• 範囲D6:D14を選択します。
• データ検証を挿入します。 .
• [カスタム ] を選択します。 許可 で ボックス。
• 式 で次の式を使用します。 ボックス:

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

• これにより、列のセルに数値のみを入力できるようになります。それ以外の場合は、エラー メッセージが表示されます。

• 「在庫数量」列に数値のみが含まれるデータセットが完成しました。

カスタム Excel データ検証ルールが機能しない:理由と解決策

• 入力したカスタム数式が正しいことを確認してください。数式に構文エラーやタイプミスがないか再確認してください。
• カスタム数式が他のセルを参照している場合は、セル参照が正確であること、および参照されるセルに期待値が含まれていることを確認してください。
• データ検証ルールを適用するための正しいセル範囲を選択していることを確認してください。
• 検証ルールに違反した場合に備えて、エラー アラートが正しく設定されているかどうかを確認します。場合によっては、エラー アラートが表示されなかったり、選択したアラート スタイルが状況と一致しない場合があります。
• セルの形式が検証ルールに適切であることを確認します。たとえば、ルールで日付をチェックする場合、セルは日付として書式設定する必要があります。
• 古い Excel バージョンを使用している場合は、互換モードの問題に注意してください。互換モードでは、特定の機能が期待どおりに動作しない場合があります。
• ワークシートが保護されている場合は、データ検証の変更が許可されているかどうかを確認します。保護設定により、新しいルールの適用が制限される場合があります。
• 条件付き書式ルールとの競合がないか確認します。矛盾するルールは相互にオーバーライドする可能性があります。

ガイド 5 – Excel でデータ検証を編集する方法

以下のようなデータセットがあり、データ検証を使用した既存のドロップダウン リストがあります。これを編集し、新しい項目「代表者」をドロップダウン リストに追加します。

• 「指定」列の任意のセルを選択します。
• [ソース] ボックスに「, 代表者」を追加します (カンマを含む)。
• [これらの変更を同じ設定の他のセルに適用する] チェックボックスをオンにします。 ” を他のセルに適用します。
• [OK] をクリックします。 .

• これにより、新しい項目「代表者」がリストに追加されます。

• このリストを使用して、セルへのデータ入力を完了できます。

ガイド 6 – データ検証を他の Excel セルにコピーする方法

「在庫数量」列に整数のみのルールによるデータ検証が含まれるデータセットがあります。このデータ検証を「販売数量」という名前の列にコピーします。

• 「在庫数量」の任意のセルを選択します。
• Ctrl + C を押します。 .
• 「販売数量」列のすべてのセルを選択します。
• 右クリックし、[形式を選択して貼り付け] をクリックします。 メニューから。

• [検証 ] をクリックします。 [貼り付け] の下 [OK] を選択します。 .

• 「販売数量」のセルに数値を入力すると許可されます。ただし、テキストを入力すると、エラー メッセージ ボックスがポップアップ表示されます。

• 列のセルに数値を入力できます。

データ検証ルールを含むセルを検索する方法

下の画像には、データ検証を含むデータセットがありますが、どの列に含まれるかはわかりません。

• [ホーム ] をクリックします。 [検索と選択] に移動します。 .
• データ検証を選択します  リストから。

• これにより、データ検証を含む列またはセルが選択されます。 (つまり、D6:D16 ).

Excel でデータ検証を削除する方法

以下のデータセットには、「指定」列にデータ検証が含まれています。削除しましょう。

• 範囲 D6:D16 を選択します。 列内で。
• [データ ] をクリックします。 [データ検証] に移動します。 .
• [すべてクリア] をクリックします。 「データ検証」ダイアログボックスで。
• [OK] を選択します .

• これにより、選択したセルの既存のデータ検証が削除されます。

Excel でデータ検証を適用するための役立つヒント

• 一貫性があり、エラーのない入力を行うには、ドロップダウンを使用します。
• ユーザー ガイダンスとして有益なメッセージを提供します。
• 無効なエントリに対するアラートを設定します。
• 柔軟なドロップダウンには名前付き範囲を使用します。
• 特定の検証には数式を使用します。
• ルールを適用するためにシートを保護することを検討してください。
• ルールを最終決定する前に徹底的にテストする

Excel でのデータ検証の制限は何ですか?

• エラーは、次のセルに移動した後にのみ検出されます。
• ルールはシートごとに個別に設定する必要があります。
• ルールは複数のセルに適用されるのではなく、セル レベルで適用されます。
• ソースリストが変更されても、ドロップダウンは自動的に更新されません。
• 組み込みの日付範囲オプションはある程度制限されています。
• エラー アラートのカスタマイズ オプションは限られています。
• 特定の特殊文字を制限または許可する組み込みオプションはありません。

データ検証に関する問題とその解決方法

他にも、データ検証がコピー＆ペーストで正しく機能しない、Excel でデータ検証がグレー表示されるなど、いくつかの問題があります。記事を参照して、これらの一般的な問題について学び、その解決方法を学ぶことができます。

練習用ワークブックをダウンロード

Excel でのデータ検証:ナレッジ ハブ

• Excel のセルに制限を設定する方法
• 特定の値を超えない Excel の数式
• Excel のテーブルからのデータ検証リスト
• 別のシートからのデータ検証リスト
• 日付形式の Excel データ検証
• Excel で色付きのデータ検証を使用する方法
• Excel で無効なデータを丸で囲む
• Excel のチェックボックス コントロールを使用したデータ検証
• [修正] Excel でのコピー＆ペーストでデータ検証が機能しない
• Excel データ検証がグレー表示される
• 1 つのセル内の複数のデータ検証
• Excel でデータ検証をコピーする方法
• Excel のデータ検証リストから空白を削除する
• Excel のデータ検証制限を削除する
• Excel カスタム データ検証

<

ソリューション付きの高度な Excel 演習を無料で入手しましょう!