【Excel】「データの入力規則」が設定されたセルに色を付ける!Excelの条件付き書式と組み合わせる技

Excelで入力規則を設定したセルを視覚的に分かりやすくしたい場面があります。例えば、特定のリストからしか選択できないセルや、特定の数値範囲のみ入力可能なセルに色を付けることで、入力ミスを防ぎやすくなります。しかし、Excelの標準機能だけでは、入力規則が設定されているセルに自動で色を付けることはできません。この記事では、Excelの「条件付き書式」機能と「データの入力規則」機能を組み合わせることで、入力規則が設定されたセルに自動で色を付ける方法を解説します。これにより、データ入力時の効率と正確性を向上させることができます。

通常、データの入力規則を設定しただけでは、そのセルが特別な入力規則を持っていることは見た目では分かりません。入力規則が設定されていることを後から確認するには、セルを選択して数式バーを確認するか、リボンの「データ」タブにある「データの入力規則」をクリックする必要があります。これでは、多くのセルがある場合に、どのセルにどのような規則が適用されているかを把握するのが困難になります。この問題を解決するために、条件付き書式を活用します。条件付き書式を使えば、セルの値や状態に応じて自動的にセルの書式(色、フォントなど)を変更できます。この機能を使って、入力規則が設定されているセルを特定し、色を付けることで、入力規則の存在を視覚的に示すことが可能になります。

入力規則が設定されたセルを特定する仕組み

Excelで「データの入力規則」が設定されているセルに条件付き書式で色を付けるには、そのセルが入力規則を持っているかどうかを判定する数式が必要です。Excelには、セルに入力規則が設定されているかどうかを直接判定する関数は用意されていません。そのため、間接的な方法で判定する必要があります。一般的に、入力規則は特定の条件を満たす値のみを許可します。例えば、リストからの選択、数値の範囲、日付の範囲、文字列の長さなどが条件となります。これらの条件を数式で表現し、その条件が満たされている場合に色を付ける、というアプローチが考えられます。しかし、これは入力規則の内容によって数式が異なり、汎用性が低くなります。より汎用的な方法として、入力規則が設定されていること自体を判定するテクニックが用いられます。それは、入力規則が設定されているセルには、必ず「入力規則」という名前の定義済み名前が存在するという性質を利用する方法です。この名前定義を利用することで、入力規則の有無を判定する数式を作成できます。

入力規則が設定されたセルに色を付ける手順

この方法では、「入力規則」という名前定義を利用します。この名前定義は、Excelが自動的に作成するものではなく、手動で作成する必要があります。この名前定義を作成することで、条件付き書式で入力規則の有無を判定できるようになります。

1. 名前定義の作成
   まず、色を付けたいセル範囲を選択します。次に、Excelの左上にある「名前ボックス」(数式バーの左隣)に「入力規則」と入力し、Enterキーを押します。これにより、選択した範囲に対して「入力規則」という名前が定義されます。この名前定義は、条件付き書式で参照する際のキーとなります。
2. 条件付き書式の適用
   引き続き、色を付けたいセル範囲が選択された状態で、「ホーム」タブの「条件付き書式」をクリックします。「新しいルール」を選択し、「数式を使用して、書式設定するセルを決定」を選びます。
3. 数式の入力
   「次の数式を満たす場合に値を書式設定」という欄に、以下の数式を入力します。

   =ISREF(入力規則)

   この数式は、「入力規則」という名前定義が参照可能かどうかを判定します。名前定義が参照可能であれば、そのセルには入力規則が設定されているとみなされます。

4. 書式の設定
   数式を入力したら、「書式」ボタンをクリックします。表示される「セルの書式設定」ダイアログで、「塗りつぶし」タブを選択し、好きな背景色を選んで「OK」をクリックします。
5. ルールの確定
   最後に、「新しい書式ルール」ダイアログで「OK」をクリックします。これにより、選択した範囲内で「入力規則」という名前定義が適用されているセルに、指定した色が自動で適用されます。

応用:特定の入力規則を持つセルのみ色を付ける

上記の方法は、セルに何らかの入力規則が設定されていれば色が付くというものです。しかし、特定の種類の入力規則(例えば、リストからの選択のみ)を持つセルにのみ色を付けたい場合もあります。その場合は、入力規則の内容を判定する数式を別途作成する必要があります。

リストからの選択を許可するセルに色を付ける

リストからの選択のみを許可する入力規則が設定されているセルに色を付けたい場合、その入力規則は通常「データの入力規則」ダイアログの「設定」タブで「入力値の種類」が「リスト」になっており、「元の値」にリストの範囲が指定されています。

この条件を判定する直接的な関数はありませんが、VBAを使用することで実現可能です。ただし、VBAを使用しない標準機能の範囲では、この特定の条件を判定することは困難です。

標準機能で実現できる範囲としては、入力規則が設定されているかどうかを判定する上記の方法が最も汎用的です。もし特定の条件での色分けが必要な場合は、手動で色を付けるか、VBAの利用を検討することになります。

数値範囲の入力規則を持つセルに色を付ける

例えば、特定の数値範囲(例:1から100まで)のみを許可する入力規則が設定されているセルに色を付けたい場合を考えます。この場合、条件付き書式で「セルの値が」「次の値より大きい」「次の値より小さい」といった条件を指定することで、その範囲外の値を入力したセルに色を付けることは可能です。しかし、「その範囲の入力規則が設定されている」という状態自体を判定する数式ではありません。

入力規則が設定されているセルに色を付けるという目的においては、前述の「入力規則」という名前定義を利用する方法が、最もシンプルで汎用性が高いと言えます。特定の条件に絞る場合は、その条件を数式で表現できるかどうかが鍵となります。

注意点とよくある失敗

名前定義「入力規則」の誤解

最もよくある失敗は、「入力規則」という名前定義の作成方法を誤ることです。単にセルにデータの入力規則を設定しただけでは、「入力規則」という名前定義は自動的に作成されません。この名前定義は、条件付き書式で参照するために手動で作成する必要があります。名前ボックスに正確に入力し、Enterキーを押すことを忘れないようにしてください。

数式の誤り

条件付き書式で入力する数式 =ISREF(入力規則) が間違っている場合、期待通りに色が付きません。大文字・小文字は区別されませんが、スペルミスや「入力規則」の部分を他の言葉にしてしまうと機能しません。数式を正確に入力しているか再度確認してください。

範囲の指定ミス

条件付き書式を適用する範囲と、名前定義「入力規則」を作成する範囲が一致していないと、意図したセルに色が付きません。色を付けたいすべてのセル範囲を選択した状態で、名前定義を作成し、条件付き書式を設定するようにしてください。もし、後から範囲を変更したい場合は、条件付き書式の設定画面でルールの適用範囲を編集できます。

「セルの値」ではなく「数式」を使うことの重要性

条件付き書式の設定で、「セルの値が」「次の値の間」などのプリセットされた条件を使うのではなく、「数式を使用して、書式設定するセルを決定」を選択することが重要です。これにより、より複雑な条件判定が可能になります。今回のケースでは、名前定義の存在を判定するために数式が必要となります。

名前定義の削除と再作成

もし、誤った設定をしてしまったり、設定をやり直したい場合は、まず「数式」タブの「名前マネージャー」から「入力規則」という名前定義を削除します。その後、再度正しい手順で名前定義を作成し、条件付き書式を設定してください。名前マネージャーで定義を確認・編集・削除できます。

まとめ

この記事では、Excelの「データの入力規則」が設定されたセルを、「条件付き書式」と「名前定義」を組み合わせて視覚的にハイライトする方法を解説しました。まず、「入力規則」という名前定義を手動で作成し、次に条件付き書式で =ISREF(入力規則) という数式を用いて、その名前定義が参照できるセルに色を付けます。このテクニックにより、データ入力時の規則設定の有無が一目で分かり、入力ミスを減らすことができます。さらに、特定の入力規則の種類に絞って色を付ける応用についても触れましたが、標準機能では限界があることも理解いただけたかと思います。この機能を活用して、より効率的で正確なデータ管理を実現してください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】