Excelで論理関数を使用する方法：IF、AND、OR、XOR、NOT

論理関数は、Excelで最も人気があり便利なものです。他のセルの値をテストし、テストの結果に応じてアクションを実行できます。これは、スプレッドシートのタスクを自動化するのに役立ちます。

IF関数の使い方

IF関数はExcelの主要な論理関数であるため、最初に理解する必要があります。この記事全体で何度も登場します。

IF関数の構造を見てから、その使用例をいくつか見てみましょう。

IF関数は、次の3ビットの情報を受け入れます。

= IF（logical_test、[value_if_true]、[value_if_false]）

logic_test：これは関数がチェックする条件です。
value_if_true：条件が満たされた場合、またはtrueの場合に実行するアクション。
value_if_false：条件が満たされない場合、またはfalseの場合に実行するアクション。

論理関数で使用する比較演算子

セル値を使用して論理テストを実行するときは、比較演算子に精通している必要があります。これらの内訳は、以下の表で確認できます。

それでは、実際の例をいくつか見てみましょう。

IF関数の例1：テキスト値

この例では、セルが特定のフレーズと等しいかどうかをテストします。IF関数では大文字と小文字が区別されないため、大文字と小文字は考慮されません。

次の数式を列Cで使用して、列Bに「完了」というテキストが含まれている場合は「いいえ」を表示し、その他のテキストが含まれている場合は「はい」を表示します。

= IF（B2 = "Completed"、 "No"、 "Yes"）

IF関数では大文字と小文字は区別されませんが、テキストは完全に一致している必要があります。

IF関数の例2：数値

IF関数は、数値の比較にも最適です。

次の数式では、セルB2に75以上の数値が含まれているかどうかをテストします。含まれている場合は「合格」という単語を表示し、含まれていない場合は「不合格」という単語を表示します。

= IF（B2> = 75、 "合格"、 "不合格"）

IF関数は、テストの結果に異なるテキストを表示するだけではありません。また、これを使用してさまざまな計算を実行することもできます。

この例では、顧客が一定の金額を費やした場合に10％の割引を提供します。例として£3,000を使用します。

= IF（B2> = 3000、B2 * 90％、B2）

数式のB2 * 90％の部分は、セルB2の値から10％を引くことができる方法です。これを行うには多くの方法があります。

重要なのは、value_if_trueまたはvalue_if_falseセクションで任意の数式を使用できることです。また、他のセルの値に応じてさまざまな数式を実行することは、非常に強力なスキルです。

IF関数の例3：日付値

この3番目の例では、IF関数を使用して期日のリストを追跡します。列Bの日付が過去の場合は、「期限切れ」という単語を表示します。ただし、日付が将来の場合は、期日までの日数を計算してください。

次の数式は列Cで使用されます。セルB2の期日が今日の日付よりも短いかどうかを確認します（TODAY関数はコンピューターの時計から今日の日付を返します）。

Original text

Contribute a better translation

= IF（B2
ネストされたIF式とは何ですか？
You may have heard of the term nested IFs before. This means that we can write an IF function within another IF function. We may want to do this if we have more than two actions to perform.
One IF function is capable of performing two actions (the value_if_true and value_if_false ). But if we embed (or nest) another IF function in the value_if_false section, then we can perform another action.
Take this example where we want to display the word “Excellent” if the value in cell B2 is greater than or equal to 90, display “Good” if the value is greater than or equal to 75, and display “Poor” if anything else.
=IF(B2>=90,"Excellent",IF(B2>=75,"Good","Poor"))
We have now extended our formula to beyond what just one IF function can do. And you can nest more IF functions if necessary.
Notice the two closing brackets on the end of the formula—one for each IF function.
There are alternative formulas that can be cleaner than this nested IF approach. One very useful alternative is the SWITCH function in Excel.
The AND and OR Logical Functions
The AND and OR functions are used when you want to perform more than one comparison in your formula. The IF function alone can only handle one condition, or comparison.
Take an example where we discount a value by 10% dependent upon the amount a customer spends and how many years they have been a customer.
On their own, the AND and OR functions will return the value of TRUE or FALSE.
The AND function returns TRUE only if every condition is met, and otherwise returns FALSE. The OR function returns TRUE if one or all of the conditions are met, and returns FALSE only if no conditions are met.
These functions can test up to 255 conditions, so are certainly not limited to just two conditions like is demonstrated here.
Below is the structure of the AND and OR functions. They are written the same. Just substitute the name AND for OR. It is just their logic which is different.
=AND(logical1, [logical2] ...)
Let’s see an example of both of them evaluating two conditions.
AND Function example
The AND function is used below to test if the customer spends at least £3,000 and has been a customer for at least three years.
=AND(B2>=3000,C2>=3)
You can see that it returns FALSE for Matt and Terry because although they both meet one of the criteria, they need to meet both with the AND function.
OR Function Example
The OR function is used below to test if the customer spends at least £3,000 or has been a customer for at least three years.
=OR(B2>=3000,C2>=3)
In this example, the formula returns TRUE for Matt and Terry. Only Julie and Gillian fail both conditions and return the value of FALSE.
Using AND and OR with the IF Function
Because the AND and OR functions return the value of TRUE or FALSE when used alone, it’s rare to use them by themselves.
Instead, you’ll typically use them with the IF function, or within an Excel feature such as Conditional Formatting or Data Validation to perform some retrospective action if the formula evaluates to TRUE.
In the formula below, the AND function is nested inside the IF function’s logical test. If the AND function returns TRUE then 10% is discounted from the amount in column B; otherwise, no discount is given and the value in column B is repeated in column D.
=IF(AND(B2>=3000,C2>=3),B2*90%,B2)
The XOR Function
In addition to the OR function, there is also an exclusive OR function. This is called the XOR function. The XOR function was introduced with the Excel 2013 version.
This function can take some effort to understand, so a practical example is shown.
The structure of the XOR function is the same as the OR function.
=XOR(logical1, [logical2] ...)
When evaluating just two conditions the XOR function returns:
TRUE if either condition evaluates to TRUE.
FALSE if both conditions are TRUE, or neither condition is TRUE.
This differs from the OR function because that would return TRUE if both conditions were TRUE.
This function gets a little more confusing when more conditions are added. Then the XOR function returns:
TRUE if an odd number of conditions return TRUE.
FALSE if an even number of conditions result in TRUE, or if all conditions are FALSE.
Let’s look at a simple example of the XOR function.
In this example, sales are split over two halves of the year. If a salesperson sells £3,000 or more in both halves then they are assigned Gold standard. This is achieved with an AND function with IF like earlier in the article.
But if they sell £3,000 or more in either half then we want to assign them Silver status. If they don’t sell £3,000 or more in both then nothing.
The XOR function is perfect for this logic. The formula below is entered into column E and shows the XOR function with IF to display  “Yes” or “No” only if either condition is met.
=IF(XOR(B2>=3000,C2>=3000),"Yes","No")
The NOT Function
The final logical function to discuss in this article is the NOT function, and we have left the simplest for last. Although sometimes it can be hard to see the ‘real world’ uses of the function at first.
The NOT function reverses the value of its argument. So if the logical value is TRUE, then it returns FALSE. And if the logical value is FALSE, it will return TRUE.
This will be easier to explain with some examples.
The structure of the NOT function is;
=NOT(logical)
NOT Function Example 1
In this example, imagine we have a head office in London and then many other regional sites. We want to display the word “Yes” if the site is anything except London, and “No” if it is London.
The NOT function has been nested in the logical test of the IF function below to reverse the TRUE result.
=IF(NOT(B2="London"),"Yes","No")
This can also be achieved by using the NOT logical operator of . Below is an example.
=IF(B2"London","Yes","No")
NOT Function Example 2
The NOT function is useful when working with information functions in Excel. These are a group of functions in Excel that check something, and return TRUE if the check is a success, and FALSE if it is not.
For example, the ISTEXT function will check if a cell contains text and return TRUE if it does and FALSE if it does not. The NOT function is helpful because it can reverse the result of these functions.
In the example below, we want to pay a salesperson 5% of the amount they upsell. But if they did not upsell anything, the word “None” is in the cell and this will produce an error in the formula.
The ISTEXT function is used to check for the presence of text. This returns TRUE if there is text, so the NOT function reverses this to FALSE. And the IF performs its calculation.
=IF(NOT(ISTEXT(B2)),B2*5%,0)
Mastering logical functions will give you a big advantage as an Excel user. To be able to test and compare values in cells and perform different actions based on those results is very useful.
This article has covered the best logical functions used today. Recent versions of Excel have seen the introduction of more functions added to this library, such as the XOR function mentioned in this article. Keeping up to date with these new additions will keep you ahead of the crowd.