Microsoft Excelデータの検証は、誤った情報を防ぐために不可欠です。例えば、電話番号ではない電話番号は、誰にとっても役に立ちません。これは、英数字を入力した場合や、数字が少なすぎたり多すぎたりした場合に発生することがあります。意図的に入力するわけではありませんが、間違いは起こり得ます。電話番号の有効性を確認するために、カスタム数値形式を使用することもできますが、それだけでは十分ではありません。
このチュートリアルでは、カスタム数値書式とデータ検証を組み合わせて、ユーザーに正しい桁数の数値を入力させる方法を紹介します。残念ながら、ユーザーが間違った桁数を入力するのを防ぐことはできません。デモファイルをダウンロードできます。
参照: 誰もが知っておくべき Windows、Linux、Mac のコマンド (無料 PDF) (TechRepublic)
Windows 10 64ビットシステムでMicrosoft 365を使用していますが、以前のバージョンのExcelも使用できます。Web版Excelは、カスタム書式とデータ検証をサポートしています。
Excelで要件を決定する方法
電話番号は10桁の数字で構成され、通常は(xxx)xxx-xxxxという形式です。電話番号に求められる要件を詳しく説明しましょう。
- 10文字以上である必要があります
- (xxx) xxx-xxxx 形式で表示する必要があります
- 数字である必要があります
最初に目につくのは (xxx) xxx-xxxx という形式です。これはカスタム形式を使えば解決できます。他の2つはデータ検証が必要です。カスタム形式は不適切な入力を拒否しないため、入力は受け付けますが、形式は適用されません。
電話番号をフォーマットするときに役立つカスタム形式が 2 つあります。
- (###) ###-#### — # 文字は数字のプレースホルダーです
- (000) 000-0000 — 0 文字も数字のプレースホルダーですが、入力桁数が足りない場合は 0 が表示されます。
図Aは、同じ電話番号に両方のカスタム書式を適用した例です。どちらのカスタム書式もすべての問題を解決できるわけではないことがすぐにわかります。入力値が10桁の数字であれば、どちらの書式も期待どおりに機能します。しかし、そうでない場合は、どちらの書式もうまく機能しません。
- 入力値に数値以外の文字が含まれている場合、どちらの形式も機能しません。
- 入力値が 10 桁未満の場合、どちらの形式も期待どおりに機能しません。
- 入力値が 10 桁を超える場合、どちらの形式も期待どおりに機能しません。
図A
結論としては、カスタム フォーマットとデータ検証の両方が必要であるということです。
Excelでカスタム書式を適用する方法
このソリューションでは、カスタム書式とデータ検証の組み合わせが必要です。例として、Tableオブジェクトに同じ入力値を入力し、カスタム書式とデータ検証がどのように処理されるかを見てみましょう。
まず、図 Bに示す Table オブジェクトの列 C に次のようにカスタム フォーマットを作成します。
- C3 を右クリックし、表示されるサブメニューから [セルの書式設定] を選択します。
- [カテゴリ] リストで、リストの下部にある [カスタム] をクリックします。
- 「種類」コントロールで「一般」を選択し、「(###) ###-####」と入力してEnterキーを押します。リストに既にカスタム書式がある場合は、それを選択します。「)」と「#」の間にはスペース文字が1つ入ります。
- [OK]をクリックします。
図B
C3 は空なので、Excel ではまだ何もする必要はありませんが、入力値が 10 桁の数字である場合、形式によって値が期待どおりに表示されることがわかります。
次のステップは、データ検証を追加することです。
Excelでデータの検証を適用する方法
Excelのデータ検証機能を使用すると、データ入力を制限し、データの正確性を確保できます。ほとんどのデータ検証コントロールは、ビジネスルールに準拠しています。例えば、入力を日付や数値のみに制限したい場合があります。これらは簡単な例ですが、要件によってははるかに複雑な場合があり、データ検証はまさにそのニーズに応えます。今回は式を使用します。
次に、他の可能性のあるエラーに対処するためのデータ検証ルールを入力しましょう。
- 必要に応じてC3を選択します。
- データタブをクリックします
- [データ ツール] グループで、[データの入力規則] オプションをクリックします。
- 表示されるダイアログで、[許可] ドロップダウンから [カスタム] を選択します。
- 数式コントロールに「=AND(ISNUMBER(C3),LEN(C3)=10」と入力します (図 C )。
- [OK]をクリックします。
図C
式 =AND(ISNUMBER(A2),LEN(A2)=10 は、次の 2 つの条件をチェックします。
- エントリは数字ですか?
- 数字はちょうど10文字ですか?
いずれかの条件が「いいえ」の場合、データ検証によってエントリが拒否されます。
それでは、2つの機能がどのように連携して電話番号の適切な書式設定を強制するかを見てみましょう。C3に「1234567890」と入力し、Tabキーを押します。図Dに示すように、カスタム書式がこの値を適切に処理します。
図D
図Eは、a234567890と入力した結果を示しています。入力値に数値以外の文字（e）が含まれているため、データ検証でエラーが表示されます。「再試行」をクリックし、aを1に置き換えてTabキーを押します。データ検証で入力が受け入れられます。ExcelのTableオブジェクトを使用しているため、カスタム書式とデータ検証の両方が新しいレコードにコピーされることに注意してください。
図E
12345678と12345678901を入力しても、データ検証に失敗します。前者は桁数が少なすぎ、後者は桁数が多すぎます。図Fに示すように、データ検証で両方が受け入れられるように、必要な修正を行ってください。
図F
数字の誤入力を完全に防ぐことはできません。しかし、これら2つの機能を組み合わせることで、他の入力ミスを防ぐことができます。多くのユーザーは、これら2つの機能を組み合わせることで入力制御を強化できることに気づいていません。
