Excel の数式を使用した条件付き書式

条件付き書式は、Microsoft Excel エクスペリエンスの不可欠な部分になりました。これは、ランダムまたは大規模なデータ グループ内の特定の値を識別するのに役立ちます。特定の値を識別するだけでなく、特定の範囲に属するかどうか、文字列値、数値、エラーなどを条件付き書式で識別できます。名前が示すように、条件が真かどうかに基づいてセルを書式設定する Excel です。このチュートリアルでは、Excel の数式を使用した条件付き書式について説明します。

Excel の式で条件付き書式を使用するビデオ チュートリアル

このビデオ講義では:

• 数式ベースの条件付き書式を使用してセルを書式設定する方法を学びます。
• ISTEXT 関数について学習します .
• 式で絶対参照を使用する方法をもう一度学習します。

デモンストレーションに使用するワークブックは、以下のダウンロード リンクからダウンロードできます。

Excel の数式で条件付き書式を使用する方法

Excel で数式を使用して条件付き書式を使用するという主なテーマは、非常に簡単です。条件付き書式機能を選択する必要があります。次に、あらかじめ決められたオプションを選択する代わりに、新しいルールを選択し、それを入力する式を選択する必要があります。

式によって、通常、ここで条件付き書式設定ボックスに条件を入力することに注意してください。条件が true の場合、フォーマットが適用され、その逆も同様です。

これは、Excel で数式を使用した条件付き書式の詳細なデモです。

手順:

• まず、数式で書式設定するセル範囲を選択します。
• ホームに移動します
• 次に、条件付き書式を選択します スタイルから グループ セクション。
• その後、[新しいルール] を選択します ドロップダウン メニューから

• その結果、 新しい書式設定ルール ボックスがポップアップします。
• 次に、[この式が真である場合の値の書式設定] の下のフィールドに式を入力できます:

• [書式] をクリックして、書式スタイルを選択することもできます。
• 上記のすべての手順とセルの書式設定スタイルが完了したら、[OK ] をクリックします。

たとえば、これらの手順をすべて実行した後、図に示す次の式を入力します。

スプレッドシートの範囲は B3:B6 でした .

ここでは、セル値が 3 より大きいという条件を入力しました。したがって、3 より大きい値が存在するこの選択では、フォーマットが適用されます。

Excel の式で条件付き書式を使用する 21 の例

このセクションでは、Excel の条件付き書式設定機能で数式を使用するさまざまな例を示します。この機能を使用して、それぞれを別の機能と組み合わせる可能性は非常に大きいです。そのため、参考になると思われる主要な式をいくつか取り上げてみました。特定のものだけが必要な場合は、上の表で見つけることができます。

1.テキスト値の書式設定

まず、数値と文字列の両方の値を含むこのデータセットについて考えてみましょう。

数式を使用した条件付き書式の助けを借りて、Excel で文字列値を簡単に選択できます。そのためには、ISTEXT 関数を利用する必要があります .次の手順に従って、Excel の条件付き書式設定でこの関数を使用して書式設定する方法を確認してください。

手順:

• まず、ヘッダーを除くデータセット内のすべてのセルを選択します。
• ホームに移動します
• その後、条件付き書式を選択します スタイルから グループ セクション。
• 次に、[新しいルール] を選択します ドロップダウン メニューから

• フォーマット ルール内 ボックスで、まず [数式を使​​用して書式設定するセルを決定する] を選択します。 [ルールの種類を選択] の下のオプション .
• 次に、次の式を この式が真の場合の値の書式設定 に挿入します .

=ISTEXT(B5)

• 次に、ご希望のフォーマット タイプを選択してください。

• 最後に、[OK] をクリックします。 .

この式を条件付き書式で使用すると、Excel がデータセット内のすべてのテキストを書式設定することに気付くでしょう。

2.別のセルと等しいセルを強調表示

それでは、値を一致させる別の例を見てみましょう。

このデータセットには大量のデータ コレクションがあります。 E5 のセル値のみに一致するものをフォーマットします .

次の手順に従って、その方法を確認してください。

手順:

• まず、ヘッダーを除くデータセット内のすべてのセルを選択します。
• ホームに移動します
• その後、条件付き書式を選択します スタイルから グループ セクション。
• 次に、[新しいルール] を選択します ドロップダウン メニューから

• フォーマット ルール内 ボックスで、まず [数式を使​​用して書式設定するセルを決定する] を選択します。 [ルールの種類を選択] の下のオプション .
• 次に、次の式を この式が真の場合の値の書式設定 に挿入します .

=B5=$E$5

• 次に、ご希望のフォーマット タイプを選択してください。

• 最後に、[OK] をクリックします。 .

これは、Excel の数式を使用して条件付き書式を使用して、別のセルと等しいセルを強調表示する方法です。

3.別のセルに基づく Excel の条件付き書式

ここで、平等であることから少しシフトしましょう。このセクションでは、別のセルの値よりも大きいか小さいかに基づいて、データセットをフォーマットします。そして、Excel の数式メソッドを使用して、条件付き書式を使用してそれらを強調表示します。

これを次のデータセットで実行します。

範囲 C5:C9 をフォーマットします セル E5 の値に基づく .

次の手順に従って、その方法を確認してください。

手順:

• まず、ヘッダーを除くデータセット内のすべてのセルを選択します。
• ホームに移動します
• その後、条件付き書式を選択します スタイルから グループ セクション。
• 次に、[新しいルール] を選択します ドロップダウン メニューから

• フォーマット ルール内 ボックスで、まず [数式を使​​用して書式設定するセルを決定する] を選択します。 [ルールの種類を選択] の下のオプション .
• 次に、次の式を この式が真の場合の値の書式設定 に挿入します .

=C5>$E$5

• 次に、ご希望のフォーマット タイプを選択してください。

• 最後に、[OK] をクリックします。 .これがデータセットになります。

• 同様に、より低い値に対して同じプロセスを繰り返すと、このようなデータセットが得られます。

これは、Excel で別のセルの値に基づく数式で条件付き書式を使用する方法です。

4. Excel の IF 式を使用した条件付き書式

IF 関数を含む数式を利用する方法を見ていきます。 Excel の条件付き書式の数式として。条件付き書式ボックスで関数を直接使用することはできません。ただし、関数を使用して値を取得し、Excel で条件付き書式を使用してセルをさらに書式設定するために使用できます。

まず、この例のデータセットを見てみましょう。これは、このタスクを実行しているものです。

手順:

• まず、セル E5 を選択します 次の式を書き留めてください。

=IF(D5>C5,"Profit","Loss")

• 次に Enter を押します

• フィル ハンドル アイコンをクリックして列の最後までドラッグし、残りのセルの数式を複製します。

• 次に、ヘッダーを除くデータセット内のすべてのセルを選択します。
• ホームに移動します
• その後、条件付き書式を選択します スタイルから グループ セクション。
• 次に、[新しいルール] を選択します ドロップダウン メニューから

• フォーマット ルール内 ボックスで、まず [数式を使​​用して書式設定するセルを決定する] を選択します。 [ルールの種類を選択] の下のオプション .
• 次に、次の式を この式が真の場合の値の書式設定 に挿入します .

=E5="Profit"

• 次に、ご希望のフォーマット タイプを選択してください。

• 最後に、[OK] をクリックします。 .

これがデータセットになります。

これが IF の利用方法です。 Excel の条件付き書式設定式の関数。

5.複数の条件を使用してセルを強調表示

それでは、最初のデータセットに戻りましょう。

ここで、5,6 であるか、テキスト「cat」を含むすべてのセルを強調表示します。

Excel の条件付き書式の数式ボックスでこれを行うには、OR 関数が必要です。 .

次の手順に従って、その方法を確認してください。

手順:

• まず、ヘッダーを除くデータセット内のすべてのセルを選択します。
• ホームに移動します
• その後、条件付き書式を選択します スタイルから グループ セクション。
• 次に、[新しいルール] を選択します ドロップダウン メニューから

• フォーマット ルール内 ボックスで、まず [数式を使​​用して書式設定するセルを決定する] を選択します。 [ルールの種類を選択] の下のオプション .
• 次に、次の式を この式が真の場合の値の書式設定 に挿入します .

=OR(B5=5,B5=6,B5="cat")

• 次に、ご希望のフォーマット タイプを選択してください。

• 最後に、[OK] をクリックします。 .

データセットを見てみましょう。

これが OR の使い方です 複数の条件に対する Excel の条件付き書式設定式の関数。

6.重複行をハイライト

次に、行全体が別の行と一致する行全体のセルを強調表示します。少しトリッキーです。 COUNTIF が必要になります と連結

デモンストレーション用のサンプル データセットを見てみましょう。

ここでは、3 行目と 6 行目が完全に一致していることがわかります。次の手順に従って、これらの関数を利用して Excel の列で重複する値を強調表示する方法を確認してください。

手順:

• まずセルを選択 F5 次の式を書き留めてください。

=CONCATENATE(B5,C5,D5,E5)

• 次に Enter を押します .

• もう一度セルを選択し、塗りつぶしハンドル アイコンをクリックしてドラッグし、残りのセルに参照用の数式を入力します。

• 少しごちゃごちゃしているように見えますが、今は無視してください。 B5:B10 の範囲を選択します .
• ホームに移動します
• その後、条件付き書式を選択します スタイルから グループ セクション。
• 次に、[新しいルール] を選択します ドロップダウン メニューから

• フォーマット ルール内 ボックスで、まず [数式を使​​用して書式設定するセルを決定する] を選択します。 [ルールの種類を選択] の下のオプション .
• 次に、次の式を この式が真の場合の値の書式設定 に挿入します .

=COUNTIF($F$5:$F$10,$F5)>1

• 次に、ご希望のフォーマット タイプを選択してください。

• 最後に、[OK] をクリックします。 .これがデータセットになります。

• F の列ヘッダーを右クリックしてみましょう [非表示] を選択します コンテキスト メニューから。

これがその後の最後のスプレッドシート ビューになります。

7.数式を含むセルの値を強調表示

それでは、数式を含むデータセットを見てみましょう。

数式を使用して条件付き書式を使用して、これらのセルを強調表示します。このため、ISFORMULA 関数を使用する必要があります。 .

次の手順に従って、その方法を確認してください。

手順:

• まず、ヘッダーを除くデータセット内のすべてのセルを選択します。
• ホームに移動します
• その後、条件付き書式を選択します スタイルから グループ セクション。
• 次に、[新しいルール] を選択します ドロップダウン メニューから

• フォーマット ルール内 ボックスで、まず [数式を使​​用して書式設定するセルを決定する] を選択します。 [ルールの種類を選択] の下のオプション .
• 次に、次の式を この式が真の場合の値の書式設定 に挿入します .

=ISFORMULA(B5)

• 次に、ご希望のフォーマット タイプを選択してください。

• 最後に、[OK] をクリックします。 .

これがデータセットになります。

8.特定の地域の売り上げを強調する

この例では、特定の地域に属するデータセットからの売上を強調します。次のデータセットを見てみましょう。

ここで、アリゾナのものを強調する必要があるとしましょう。そのためには、次の手順に従います。

手順:

• まず、ヘッダーを除くデータセット内のすべてのセルを選択します。
• ホームに移動します
• その後、条件付き書式を選択します スタイルから グループ セクション。
• 次に、[新しいルール] を選択します ドロップダウン メニューから

• フォーマット ルール内 ボックスで、まず [数式を使​​用して書式設定するセルを決定する] を選択します。 [ルールの種類を選択] の下のオプション .
• 次に、次の式を この式が真の場合の値の書式設定 に挿入します .

=$D5="Arizona"

• 次に、ご希望のフォーマット タイプを選択してください。

• 最後に、[OK] をクリックします。 .

これらの手順の結果として、Excel はアリゾナ州からの売り上げを記録します。

9.列の違いを強調

隣接する列とは異なる列を持つ行を強調表示することもできます。たとえば、次の例を見てみましょう。

Excel で数式を使用して条件付き書式を使用してそれらを強調表示するには、次の手順に従います。

手順:

• まず、ヘッダーを除くデータセット内のすべてのセルを選択します。
• ホームに移動します
• その後、条件付き書式を選択します スタイルから グループ セクション。
• 次に、[新しいルール] を選択します ドロップダウン メニューから

• フォーマット ルール内 ボックスで、まず [数式を使​​用して書式設定するセルを決定する] を選択します。 [ルールの種類を選択] の下のオプション .
• 次に、次の式を この式が真の場合の値の書式設定 に挿入します .

=$B5<>$C5

• 次に、ご希望のフォーマット タイプを選択してください。

• 最後に、[OK] をクリックします。 .

これがこれらのステップの最終製品になります。

10.数式を使用して欠損値を強調する

このセクションでは、数式を使用して、Excel の条件付き書式で欠落している値を強調表示します。たとえば、次のデータセットを見てみましょう。

リスト 2 には、リスト 1 で使用できたいくつかの欠落した値があります。それらを強調表示します。以下の手順に従って方法を確認してください。

手順:

• まず、ヘッダーを除くデータセット内のすべてのセルを選択します。
• ホームに移動します
• その後、条件付き書式を選択します スタイルから グループ セクション。
• 次に、[新しいルール] を選択します ドロップダウン メニューから

• フォーマット ルール内 ボックスで、まず [数式を使​​用して書式設定するセルを決定する] を選択します。 [ルールの種類を選択] の下のオプション .
• 次に、次の式を この式が真の場合の値の書式設定 に挿入します .

=COUNTIF($D$5:$D$10,$B5)=0

• 次に、ご希望のフォーマット タイプを選択してください。

• 最後に、[OK] をクリックします。 .

Excel は、この条件付き書式の数式のセルを強調表示します。

11.セルをハイライトするシンプルな検索ボックスの作成

いろいろなバリエーションを組み合わせて、楽しいものを作ってみましょう。これはそのためのデータセットです。

ここでは、セル E4 に値を入力します Excelは前の範囲でそれらを強調表示し、すべて式メソッドを使用した条件付き書式を使用します。 ISNUMBER の組み合わせが必要になります と検索 このメソッドの関数。

次の手順に従って、その方法を確認してください。

手順:

• まず、ヘッダーを除くデータセット内のすべてのセルを選択します。
• ホームに移動します
• その後、条件付き書式を選択します スタイルから グループ セクション。
• 次に、[新しいルール] を選択します ドロップダウン メニューから

• フォーマット ルール内 ボックスで、まず [数式を使​​用して書式設定するセルを決定する] を選択します。 [ルールの種類を選択] の下のオプション .
• 次に、次の式を この式が真の場合の値の書式設定 に挿入します .

=ISNUMBER(SEARCH($E$4,B5))

• 次に、ご希望のフォーマット タイプを選択してください。

• 最後に、[OK] をクリックします。 .

次に、データセットを見てください。ゲームという単語を含むテキストがマークされます。

セル E4 の値を変更すると 、たとえば、「健康」ハイライトが変わります。

12.平均未満の値をハイライト

以前のデータセットの 1 つをもう一度見てみましょう。

ここでは、Excel で数式を使用して条件付き書式の基本的なアプリケーションを使用します。ここでは、列 E で平均よりも小さい値を強調表示します。 . AVERAGE 関数が必要です

次の手順に従って、その方法を確認してください。

手順:

• まず、ヘッダーを除くデータセット内のすべてのセルを選択します。
• Then go to the Home tab on your ribbon.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule from the drop-down menu.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=E5<AVERAGE($E$5:$E$10)

• Next, select your preferred format type.

• Finally, click on OK .

This will highlight the values that are less than average in the dataset.

13. Highlight Values That Are Greater Than Average

Similar, to the previous one, we are going to highlight the values that are greater than average in this section using the formula method of conditional formatting in Excel. We will have to use the AVERAGE function for this task too. The dataset will be the same.

Follow these steps to see the formula and how you can use it.

Steps:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home tab on your ribbon.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule from the drop-down menu.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=E5>AVERAGE($E$5:$E$10)

• Next, select your preferred format type.

• Finally, click on OK .

Thus Excel will highlight all the values that are higher than average in column E .

14. Find Nearest Value Including Exact Match

Now let’s take another unique example that is practical in data cleaning and analysis. We are going to find the nearest value to a number in a random set of data.

This is the dataset of this example.

As you can see from the figure, we are going to find the value that is closest to the one in cell C13 . If there is the same value in the dataset, it will highlight that cell instead of the closest value. For this purpose, we will utilize the MIN , ABS , and OR functions.

Follow these steps to see how you can do that.

Steps:

• First, we need to perform some calculations. We need to find the smallest difference from this value in that set of data. For that select cell C14 and write down the following formula.

=MIN(ABS(B5:D11-(C13)))

• Then press Enter .

• Now select all the cells in the dataset excluding headers.
• Then go to the Home tab on your ribbon.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule from the drop-down menu.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=OR(B5=$C$13-$C$14,B5=$C$13+$C$14)

• Next, select your preferred format type.

• Finally, click on OK .

This will highlight the value that is closest to the value of 1, with conditional formatting formula in Excel.

15. Find Top 3 Values

Let’s go back to the random set of data.

This time we are going to find the top 3 values here using conditional formatting with a formula in Excel. The formula consists of the LARGE function .

Follow these steps to see how we can do that.

Steps:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home tab on your ribbon.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule from the drop-down menu.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=B5>=LARGE($B$5:$D$11,3)

• Next, select your preferred format type.

• Finally, click on OK .

As you can see Excel will thus mark the top 3 values from the range using conditional formatting formula.

16. Find Bottom 3 Values

Again, we will use the same dataset as before. But this time we will use that to highlight the bottom 3 values from the dataset. Just like before we are gonna need the SMALL function for the formula. To see how we can do that, follow these steps.

Steps:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home tab on your ribbon.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule from the drop-down menu.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=B5<=SMALL($B$5:$D$11,3)

• Next, select your preferred format type.

• Finally, click on OK .

This time, Excel will mark the bottom 3 values because of the conditional formatting formula.

17. Show Temperature with Color Scale

In this next example, let’s try another interesting usage of formula and conditional formatting in Excel. Here is the dataset for that.

We have taken two columns because we will use a formula to change the color based on the input in cell E5 . Also, there is a blank row at the start of the dataset. We will need that to utilize the formula we are going to use. The formula consists of IF and AND 機能。 To see how you can change the color scheme of these temperatures based on the current temperature, follow these steps.

Steps:

• First, select cell C6 and write down the following formula in it.

=IF(B6=$E$5,"",IF(AND(B6<$E$5,$E$5<B5),"",B6))

• Then press Enter .

• Now select the cell again and click and drag the fill handle icon to replicate the formula for the rest of the cells in the column.

• Next, go to Conditional Formatting from the Styles group of the Home
• Then hover your mouse over the Color Scales .
• After that, select your preferred color scale.

• The temperature scale for conditional formatting based on the formula for current temperature will now be complete.

• If we change the value of the current temperature in cell E5 , the temperature scale will change accordingly.

This is how we can show the temperature with color scale utilizing formula and conditional formatting in Excel.

18. Highlight Alternate Rows with Conditional Formatting

Now let’s go back to one of the previous datasets of random numbers.

With this dataset, we will highlight alternate rows with a color using the conditional formatting formula in Excel. For the formula, we will need the INT and MOD functions.

The steps to do that are below.

Steps:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home tab on your ribbon.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule from the drop-down menu.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=INT(MOD(ROW(),2))

• Next, select your preferred format type.

• Finally, click on OK .

This will highlight the alternate rows using the conditional formatting formula in Excel.

19. Highlight Cells with Error

Next up, we will be using the conditional formatting formula in Excel to highlight cells with errors. This is particularly helpful with a large number of data in a sheet and where finding errors manually is tiresome. For demonstration, let’s take a look at the following dataset.

There contains two errors in the dataset. We are going to use the ISERROR function in the conditional formatting formula to detect them. The formula used below will help us identify them using conditional formatting in Excel.

Steps:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home tab on your ribbon.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule from the drop-down menu.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=ISERROR(B5)

• Next, select your preferred format type.

• Finally, click on OK .

Excel will thus highlight all the errors as a result of using the conditional formatting formula.

20. Create Checklist with Conditional Formatting

Now let’s try another fun application of conditional formatting with a formula in Excel. This time we will make a checklist beside a set of data. With the options checked, the original data will change its format. We will use the following dataset.

This is a standard to-do list. Once we check a checkbox in the status column, the task beside it will change the format, let’s say a strikethrough. The basic idea is to use the boolean value of the checkbox to format in column B . Also, we will need the Developer tab to insert checkboxes. If you don’t have one in your ribbon, click here to display the Developer tab on your ribbon .

Follow these steps to see how we can use the Excel conditional formatting formula to create a checklist in detail.

Steps:

• First, go to the Developer tab on your ribbon.
• Then select Insert from the Controls group section.

• After that, select the Check box (Form Control) from the drop-down menu.
• Then place the box in its appropriate place.

• Next, right-click on the box and select Format Control from the context menu.

• Now go to the Control tab on the box and select cell C5 as its linked cell.

• Then click on OK .
• Now let’s remove the alt text and place it in the middle of the cell to make it look good.

• Now repeat the process for all of the tasks.

• Once checked, there will be a TRUE/FALSE value on the cells depending on it.

• So let’s use a white font color to make them invisible.

• Now select the task range you want to format.
• Then go to the Home tab on your ribbon.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule from the drop-down menu.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=C5=TRUE

• Next, select your preferred format type.

• Finally, click on OK .

Now once we check a check box beside a task, it will format the task beside it. We did all of that using the help of the conditional formatting formula in Excel.

21. Highlight Weekends in a Week

Let’s try another example where we will use a formula to highlight weekends in calender week in Excel. We will use the OR and WEEKDAY functions to create this formula. As for the dataset, we will use the following.

Now follow these steps to see how we can highlight weekends in Excel using conditional formatting using the formula.

Steps:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home tab on your ribbon.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule from the drop-down menu.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=OR(WEEKDAY($B5)=1,WEEKDAY($B5)=7)

• Next, select your preferred format type.

• Finally, click on OK .

Thus it will highlight the weekends from the calender week, all with the help of conditional formatting in Excel.

結論

So these were the methods and different examples of using a formula for conditional formatting in Excel. I hope you have grasped the idea of the usage of the feature and can now use it accordingly. Hopefully, you have found this guide helpful and informative. If you have any questions or suggestions let us know in the comments below.

For more guides like this, visit Exceldemy.com .