AND / OR语句的多个值 [英] Multiple values for an AND/OR statement

问题描述

是否可以组合 AND （或 OR ）语句标准，以避免重复单元名称？

例如，我想看看单元格C2是否包含2,3,5,7或10中的任何一个。我想避免写入

  IF（AND（C2 = 2，C2 = 3 ... etc. 
  pre> 
 
 
，并将其简化为数字数组，如
  IF（AND（C2 = [2,3,5,7,10] ... 
  
不幸的是我有很多不只是5个数字来添加，所以它变得非常费力，任何人都有一个比重复单元格名称= __一遍又一遍的更容易的方法。
解决方案
您可以使用这样的数组常量
 
 
 
  = IF（OR（C2 = {2 ，3,5,7,10}），是，否） 
 
 
 
 .....或一个大一组数字，你可以把所有的数字在一个单元格范围内，例如 Z2：Z100 并做同样的
 
 
 
  = IF（OR（C2 = $ Z $ 2：$ Z $ 100），是，否） 
 
 
 
虽然当您使用范围而不是数组常量时，公式将成为数组公式，所以需要用 CTRL  +  SHIFT  +  ENTER  
 
 
 
确认更好地使用 COUNTIF 并避免数组输入... 
 
 
 
  = IF COUNTIF（$ Z $ 2：$ Z $ 100，C2）> 0，是，否） 
 
Is it possible to combine AND (or OR) statement criteria to avoid repeating a cell name? 

For example, I want to see if cell C2 contains the any of the numbers 2,3,5,7, or 10. I want to avoid writing 
IF(AND(C2=2,C2=3... etc. 
and simplify it to an array of the numbers like 
IF(AND(C2=[2,3,5,7,10]... 
Unfortunately, I have a lot more than just 5 numbers to add so it's getting be very laborious. Anyone have an easier way than repeating the cell name=__ over and over?
解决方案
You can use an "array constant" like this

=IF(OR(C2={2,3,5,7,10}),"Yes","No")

.....or for a large set of numbers you could put all the numbers in a cell range, e.g. Z2:Z100 and do the same

=IF(OR(C2=$Z$2:$Z$100),"Yes","No")

although when you use a range rather than an array constant the formula becomes an "array formula" so needs to be confirmed with CTRL+SHIFT+ENTER

perhaps better to use COUNTIF and avoid "array entering"...

=IF(COUNTIF($Z$2:$Z$100,C2)>0,"Yes","No")

                        
这篇关于AND / OR语句的多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！