如何在excel中同时检查多个单元中的多个条件?

How to examine multiple conditions in multiple cells at the same time in excel

如何在excel中同时检查多个单元中的多个条件?

State of art

In cell A1, I have room number which must be cleaned (e.g. 102, 420, ...)
In cell B1, I have month name
In cell C1, I have the floor number
In cell D1, I want to insert "Yes" or "No" to indicate if the room in that month must be cleaned or not.

在A1单元格中，我有房间号，需要清洗(例如102、420、…)在B1单元格中，我在C1单元格中有个月名，在D1单元格中有楼层号，我想插入“是”或“否”，以表明那个月的房间是否必须打扫。

Conditions

I want to insert a formula in D1 according to the following conditions:

我想根据以下条件在D1中插入一个公式:

If A1 is "100" or "200" or "300" and at the same time
If B1 is "April" or "August" or "December" and at the same time
If C1 is "1" or "3" or "5"

若A1为"100"或"200"或"300"同时若B1为" 4 "或" 8 "或" 12 "同时C1为"1"或"3"或"5"

then insert "Yes", otherwise "No".

然后插入“Yes”，否则为“No”。

1 个解决方案

#1

Something like that:

这样的:

=IF(AND(OR(A1=100,A1=200,A1=300),OR(B1="April",B1="August",B1="December"),OR(C1=1,C1=3,C1=5)),"Yes", "No")

For longer expressions you might consider using MATCH as a shortcut, for instance

例如，对于较长的表达式，可以考虑使用MATCH作为快捷方式

NOT(ISNA(MATCH(A1,{100,200,300},0))) in place of

伊朗学生通讯社((匹配(A1,{ 100200300 },0)))的地方

AND(OR(A1=100,A1=200,A1=300)

和(或(A1 = 100 A1 = 200,A1 = 300)

where {100,200,300} is the array to match against. If the vales of the array are saved in cells K1, K2, K3 this becomes shorter by using the Excel range K1:K3:

其中{100,200,300}是要匹配的数组。如果数组的值保存在单元格K1 K2 K3中，通过使用Excel范围K1:K3这将变得更短:

NOT(ISNA(MATCH(A1,K1:K3,0)))

伊朗学生通讯社((匹配(A1,K1:K3,0)))