Excelの条件付き書式機能の10のクリエイティブな使い方 - TechRepublic

導入

条件付き書式を使用すると、特定の条件が満たされた場合にのみ、指定した書式を適用できます。ここでは、条件付き書式を本来の用途を超えて活用する独創的な方法をいくつかご紹介します。これらのテクニックは、Excelの条件付き書式機能に関する基本的な知識を前提としています。

n

nt

n

注：この情報をブログ記事としてご覧になりたい場合は、Five Appsブログのこちらの記事をご覧ください。また、ここで紹介したテクニックを紹介するサンプルExcelファイルをダウンロードすることもできます。

n

写真: iStockphoto.com/NadyaPhoto

ビジネスルール違反を区別する

条件付き書式を使用すると、ビジネスルールに違反している箇所を特定できます。例えば、この図は、8時間を超える勤務日をハイライト表示するシンプルなタイムキーピングシートを示しています。なぜでしょうか？それは、8時間を超える勤務には組織で承認が必要だからです。

ビジネスルール違反を区別する

ご覧のとおり、時刻値を扱うのは少し複雑です。このソリューションでは、8時間を超える時刻値を表すために「>.34」を使用していますが、これはほとんどの場合に機能します。ただし、8や8:00という時刻値は使用できません。Excel 2007および2010にあらかじめ定義されている「より大きい」ルールを使用することもできます。このルールでは、より正確な値である0.333333…が自動的に使用されます。

シンプルなアイコンを表示する

条件付き書式（2007および2010）を使用すると、値そのものよりも解釈しやすいアイコンを表示できます。例えば、単純なチェックマークは、yes、on、trueなどのテキスト値よりもすぐに認識できるかもしれません。ここでは、先ほど見たルール違反に対するアイコンによる解決策を示しています。

シンプルなアイコンを表示する

この効果を得るには、E列の値を選択し、デフォルトのアイコンセットのいずれかを適用します。次に、「ルールの管理」を使用して結果を操作します。必ず最初に「アイコンの順序を反転」をクリックしてください。

単一の値に基づいて行を強調表示する

ntFiltersは表示内容を絞り込むのに便利ですが、レコードを比較したい場合もあります。そのような場合、条件付き書式を使えばレコードを区別できます。以下は、条件付き書式で調味料のレコードのみを強調表示した商品データセットです。

単一の値に基づいて行を強調表示する

Excelがレコード全体（行全体）の書式を設定できるように、データ範囲全体（列見出しは選択しないでください）を選択します。上の図は、数式ベースの設定を示しています。$G2コンポーネントは相対アドレスを作成し、行ごとに更新されます（G4、G5、G6…）。参照セルの値が文字列「調味料」と一致すると、Excelは行全体を強調表示します。

ダイナミックレコードハイライトを作成する

レコード全体をハイライト表示するのは便利です。しかし、条件付き書式をもっと条件付きにしたい場合はどうすればよいでしょうか？例えば、ユーザーがその場でカテゴリを選択できるようにしたいとします。

ダイナミックレコードハイライトを作成する

まず、高度なフィルター機能を使用して、一意のリストを目立たない場所にコピーします。次に、データの検証機能を使用してリストを作成します。

ダイナミックレコードハイライトを作成する

リストを配置したら、条件付き書式の式を更新して、入力リストのセルを参照するようにします。式は行内のセルを参照する代わりに、B2の検証リストを参照します。検証リストから項目を選択すると、条件付き書式が更新されます。

値を比較する

場合によっては、値を比較する必要があります。例えば、在庫レベルを追跡するために、手持ち在庫と再発注レベルを比較する場合があります。条件付き書式を使用すれば、再発注のタイミングをユーザーに通知できます。

値を比較する

書式設定する値（この場合はB2:B46）を選択します（行全体または在庫値の1つを強調表示できます）。次に、ここに表示されている書式を適用します。

リストを比較する

条件付き書式ルールを使用して、2 つのリスト間の不一致を見つけることができます。

リストを比較する

このルールは、列 A の各値を列 B の対応する値と比較します。同じでない場合、Excel は列 A の値を強調表示します。代わりに列 B の値を強調表示するには、列 B の値を選択し、ルールの数式を更新して列 A の値を参照します。

交互のバンドを作成する

多くのシートでは、読みやすさを向上させるために、1行おきにハイライト表示（バンド表示）されています。表機能には、バンドを含む定義済みの書式がいくつか用意されていますが、単純なデータセットではなく表が作成されます。表が不要な場合は、条件付き書式を使用して、交互に表示されるバンドを作成してください。

交互のバンドを作成する

ここで示すルールは、交互のバンド効果を実現するためにセルを強調表示します。

重複を見つける

重複する値やレコードを見つけるにはフィルターを使うこともできますが、条件付き書式を使えば重複する値を即座に特定できます。例えば、このシートでは1つの列に重複した値が表示されています。

重複を見つける

書式設定する値を選択し、ここに表示される数式ベースのルールを適用します。

重複を見つける

最初の出現を無視して後続の値のみを強調表示するには、次の数式を使用します。

n

=COUNTIF($A$2:$A2,A2)>1

n

複数の列にわたって重複する値をチェックしたい場合は、上の図のように値を連結し、結果に同様のルールを適用します。また、シートの背景色に合わせたフォント色を選択することで、重複を非表示にすることもできます（必ずしも推奨されるわけではありません）。

矛盾を見つける

データの検証は重要な作業であり、Excelの条件付き書式は不一致を警告することで役立ちます。上の図は、クロスフッティングと呼ばれる一般的な会計ツールを示しています。クロスフッティングとは、行と列の小計を比較して合計を二重チェックするプロセスです。この手法はF16セルに使用されています。条件付き書式を追加することで、2つの合計値が一致しない場合でも、その不一致を見逃しにくくなります。

矛盾を見つける

いずれかのクロスフット式を選択し、ここに示すルールを適用します。

最小または最大の重複を見つける

上記のように、値 3 は列内の最小値ですが、Excel では値 5 の各出現が強調表示されます。

最小または最大の重複を見つける

通常の参照を使用する場合、このルールは不安定になります。そのため、条件付き書式ルールを適用する前に、データセットに範囲名を適用してください。ここで示すルールでは、「List」という範囲の値「3」が重複している場合にのみ、その値が強調表示されます。（重複する最大値を検索するには、MIN()関数をMAX()関数に置き換えてください。）