如何编写if语句代码以将一个工作表中的两个单元格的值与另一工作表中的另一个单元格进行比较 [英] how to write an if statement code to compare two cell's value in one sheet to another cell in another sheet

问题描述

我有两列(开始日期，结束日期)，并且我想要一个代码，如果开始日期和结束日期包含另一个工作表中的任何日期，则将其两个单元格的颜色更改为红色

我尝试使用以下规则使用条件格式:= AND(C2< =假期！$ A $ 2:$ A $ 15，D2> =假期！$ A $ 2:$ A $ 15)

解决方案

为我自己测试了以下公式，并认为它应该起作用！有效地而不是简单的AND语句，因为我们要与值数组而不是单个单元格进行比较，所以我们需要使用一些方便的数组公式.这篇文章很好地解释了实际情况:

i have two columns (Start Date, End Date) and i want a code that changes the color of both their cells to red if the start date and end date contained any date located in another sheet

I tried using conditional formatting using this rule: =AND(C2<=Holidays!$A$2:$A$15,D2>=Holidays!$A$2:$A$15)

解决方案

Tested out the below formula for myself and think it should work! Effectively rather than a simple AND statement, since we're comparing against an array of values rather than single cells, we need to use some handy array formulas. This post gives a good breakdown of what's going on under the hood: https://exceljet.net/formula/index-and-match-with-multiple-criteria

Apply this formula in conditional formatting, assuming the range you're formatting starts at C2.

=SUM((Holidays!$A$2:$A$15>$C2)*(Holidays!$A$2:$A$15<$D2)) > 0

Also, wanted to call out the absolute references specified for columns. I've specified **$**C2 and **$**D2 in my formula, which means that no matter which cell in your conditional formatted range it'll evaluate off the values in column C and D. In your current formula, cell C2, would evaluate on C2 and D2, but cell D2 would then evaluate off D2 and E2. This'd be why when you tried the manual formatting only the start date cells (column C) were formatting correctly.

Hope this helps!

Update::

If you had a table format like in the attached screenshot, you would apply the conditional formatting formula:

=SUM((Holidays!$A$2:$A$15>$C2)*(Holidays!$A$2:$A$15< $D2)) > 0

To cell range $C$2 : $D$3

If you're changing the cell range (say you wanted to apply to $C$10 : $D$14), you'd just change the $C2 and $D2 in the formula --> $C10 and $D10.

这篇关于如何编写if语句代码以将一个工作表中的两个单元格的值与另一工作表中的另一个单元格进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！