excel多条件专辑

IF函数多条件判断

   多条件是我们在处理EXCEL数据时必须要面对的问题,多条件计数求和,多条件查找...为了方便大家学习,本文特对多条件的处理进行一个全面的总结,希望能对同学们有所帮助.本文由EXCEL精英培训兰色幻想编写.转截请注明作者和转自EXCEL精英培训.

    IF函数可以单条件判断,如:

        =IF(A1<60,"不及格","不及格")

    通过嵌套也可以实现多条件判断,如

       =IF(B21<60,"不及格",IF(B21<70,"及格",IF(B21<85,"良好","优秀")))

    通过和OR或AND的配合可以实现混合判断.如:

       =IF(AND(B2<>"",C2<>""),C2/B2,"0%")

    如果有更多的条件,甚至超过7个条件的判断怎么办呢?我们可以用定义名称其他他方式达到,不过这些都太过麻烦,这里提供一个使用VLOOKUP函数替换IF完成多条件判断的例子

     在excel中函数最多只能嵌套七层，IF函数也不能例外，遇到需要进行多次判断的怎么办呢？可以用VLOOKUP函数替代。

    例如：下表中需要根据提供的销售额判断提成比率，这里可能有很多，为了演示方便，只列中三种。这种情况下怎么判断呢？

公式1：=vlookup（C2，A$1：B$100，2，0）

     如果区域不想放在单元格区域，可以直接写成常量数组，即：

     =VLOOKUP(C2,{"销售额","提成比率";"电视",0.1;"洗衣机",0.05;"吸油烟机",0.06},2,0)

     如果IF是进行的区间判断，怎么用VLOOKUP替换呢？答案是可以用vlookup的模糊查找功能。看下例：

  

   公式为：=VLOOKUP(D2,A1:B11,2)

示例附件下载

excel多条件专辑

COUNTIF函数多条件计数

 COUNTIF函数可以进行条件计数,但一般它只能有一个条件,如何实现多个条件呢

  1 多项目条件:   excel中countif能用来统计符合多个条件的单元格是可以实现的，不过要配合sum函数的使用，例：

    =sum(countif(a:A,{"电视机","冰箱"}))

  2 区间条件: 计算入库金额大于10000且小于20000的入库次数

  3 更多条件我们有3种解决方案

     1) 用SUMPRODUCT函数完成多条件计数,如

        3月份A产品的销售次数:=SUMPRODUCT((MONTH(A3:A9)=3)*(B3:B9="A"))

     2) Excel2007后新添了多条件计数的函数COUNTIFS,它可以实现多条件计数

       =COUNTIFS(A2:A11,"公司1",B2:B11,"人事部")

     3) 数据库函数DCOUNT 因为它需要有一个条件区域,用起来不方便,所以不再详述.

excel多条件专辑

SUMIF多条件求和

 SUMIF函数和COUNTIF函数用法差不多.多条件的处理方法如下:

    1 多项目求和

      =SUM(SUMIF(B31:B35,{"A","C"},C31:C35))

    2 03版本可以用SUMPRODUCT函数替代.

        =SUMPRODUCT((MONTH(A3:A9)=3)*(B3:B9="A")*C3:C9)

    3 07版本可以用SUMIFS替换:

        =SUMIFS(D2:D11,A2:A11,"公司1",B2:B11,"人事部")

 

 

 

excel多条件查找15种思路

示例

题目：如下图所示，根据第9行的产品和型号，从上面表中查找“销售数量”，结果如C10所示

 

excel多条件查找15种思路

SUM函数

公式{=SUM((A2:A6=A9)*(B2:B6=B9)*C2:C6)}

公式简介：使用(条件)*（条件)因为每行符合条件的为0，不符合的为1，所以只有条件都符合的为非零数字。所以SUM求和后就是多条件查找的结果

 

SUMPRODUCT函数

公式：=SUMPRODUCT((A2:A6=A9)*(B2:B6=B9)*C2:C6)

公式简介：和SUM函数用法差不多，只是SUMPRODUCT函数不需要数组运算

 

MAX函数

 

{=MAX((A2:A6=A9)*(B2:B6=B9)*C2:C6)}

SUM是通过求和把符合条件的提出来，这里是使用MAX提取出最大值来完成符合条件的值提取。

 

lookup函数

 

公式1=LOOKUP(A9&B9,A2:A6&B2:B6,C2:C6)

公式简介：LOOKUP函数可以直接进行数组运算。查找的连接起来，被查找区域也连接起来。

公式2：=LOOKUP(1,0/((A2:A6=A9)*(B2:B6=B9)),C2:C6)

公式3=LOOKUP(1,0/((A2:A6&B2:B6)=(A9&B9)),C2:C6)

公式4=LOOKUP(1,1/(((A2:A6=A9)+(B2:B6=B9))=2),C2:C6)

 

MIN+IF函数

公式=MIN(IF((A2:A6=A9)*(B2:B6=B9),C2:C6))

 

SUM+IF函数

公式

     =SUM(IF(A2:A6=A9,IF(B2:B6=B9,C2:C6,0),0))

 

INDEX+MATCH函数组合

公式1：{=INDEX(C2:C6,MATCH(A9&B9,A2:A6&B2:B6,0))}

公式2：{=INDEX(C2:C6,MATCH(1,(A9=A2:A6)*(B9=B2:B6),0))}

 

OFFSET+MATCH函数

公式

       =OFFSET(C1,MATCH(A9&B9,A2:A6&B2:B6,0),)

 

INDIRECT+MATCH函数

公式

    =INDIRECT("C"&MATCH(A9&B9,A1:A6&B1:B6,0))

 

VLOOKUP+CHOOSE函数

公式 ：

    =VLOOKUP(A9&B9,CHOOSE({1,2},A2:A6&B2:B6,B2:C6),2,0)

 

HLOOKUP+TRANSPOSE+CHOOSE函数

公式

    =HLOOKUP(A9&B9,TRANSPOSE(CHOOSE({1,2},A2:A6&B2:B6,B2:C6)),2,0)

 

VLOOKUP+IF函数

公式1 =VLOOKUP(A9&B9,IF({1,0},A2:A6&B2:B6,B2:C6),2,0)

公式2=VLOOKUP(A9&"|"&B9,A31:D35,4,0)‘添加辅助列

 

SUMIFS函数

 

excel2007中开始提供的函数SUMIFS

=SUMIFS(C1:C6,A1:A6,A9,B1:B6,B9)

 

数据库函数

=DSUM(A1:C6,3,A8:B9)

=DGET(A1:C6,3,A8:B9)

=DAVERAGE(A1:C6,3,A8:B9)

=DMAX(A1:C6,3,A8:B9)

=DMIN(A1:C6,3,A8:B9)

=DPRODUCT(A1:C6,3,A8:B9)

 

 

excel多条件专辑

LOOKUP函数多条件

很多人会对=Lookup(1,0/(条件1*条件2*……),引用区域)或者=Lookup(2,1/(条件1*条件2*……),引用区域)这么一个公式中的2、1产生疑问，到底是啥意思呢，我来说说：
2就是2，1就是1，0就是0，没什么意思。——呵呵。
但你应该问的是——为什么要这样写公式，其实关键在于第2参数的分母。
比如Lookup(1,0/(条件1*条件2*……),引用区域)中：
条件——就是逻辑判断，比如A1=B1、A1>B1、A1<>""等等，产生的是逻辑值True和False
条件1*条件2*……——就是逻辑值相乘，逻辑值运算的规则就相当于True=1、False=0，所以True*True=1、True*False=0，x/True=x本身、x/False=#DIV/0！错误值等等。
所以Lookup第2参数返回的是0/True=0和0/False=#DIV/0！，即都比第1参数“1”小。
Lookup函数的特性是要求第2参数按升序排列，即使不是按升序排列，它也是认为升序的情况下来执行——采用“二分法”按二分法原理，Lookup函数忽略错误值在第2参数中（即{0,=#DIV/0！,0,……}组成的数组）找1，肯定找不到，返回的是最后一个0出现的位置。
最后一个0意味着什么——意味着最后一个0/True——既然是True就意味着最后一个条件成立的记录的位置
找到了这个“位置”去对应第3参数引用区域中的位置——于是就返回了最后一个满足条件的记录了。

 

 

VBA多条件查找筛选

如何按多个条件用VBA进行查询呢,下面这个示例可能会有一些启示.

upload/2011_10/11101015221620.rar

 

 

 

excel多条件专辑

多条件模糊匹配求和

有网友问了下面的多条件求和问题：

我使用SUM进行多条件求和，但现在遇到的问题是如果我想对含有特定内容的单元格条件求和时却无法使用通配符。

如：=SUM((A28:A41="5M")*(C28:C41="*MCD*"),J28:J41)

我得到的结果为0

实际在C28:C41单元格中可能有LKK\MCD      MCD\OPI     LKK\MCD\OPI  等多种情况存在。
而我现在想统计C28:C41中含有MCD得单元格，并对对应的J28:J41求和

A列为公司   C列为项目   J列为费用。
现在其实就是对5M公司MCD项目求费用总和。


答：正确的公式如下：

第1页：IF函数多条件判断

第2页：COUNTIF函数多条件计数

第3页：SUMIF多条件求和

第4页：多条件查找

第5页：LOOKUP函数多条件

第6页：VBA多条件查找筛选

第7页：多条件模糊匹配求和