~~~~~~条件的分割线~~~~~~ 1.条件判断 IF(AND(条件1,条件2,...,条件n),结果1,结果2) IF(OR(条件1,条件2,...,条件n),结果1,结果2) 问题:销售额大于回款额,并且销售额大于5000,回款额大于3000的正式员工的考评成绩为优,其余为良好 公式:=IF(AND(C2>D2,C2>5000,D2>3000,B2='正式'),'优秀','良好') ------------------------------------ 2.条件求和 SUMIF(条件区域,条件,求和区域) --推送46 SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,...) --推送46 SUMPRODUCT((条件区域=条件)*求和区域) --推送22 SUMPRODUCT((条件区域1=条件1)*(条件区域2=条件2)*...*求和区域) --推送22 问题:1.求正式员工的总销售额;2.求销售二部正式员工的总销售额 公式1: =SUMIF(B2:B14,A17,D2:D14) =SUMIFS(D2:D14,B2:B14,A22,C2:C14,B22) 公式2: =SUMPRODUCT((B2:B14=A17)*D2:D14) =SUMPRODUCT((B2:B14=A21)*(C2:C14=B21)*D2:D14) ------------------------------------ 3.条件计数 COUNTIF(条件区域,条件) --推送60 COUNTIFS(条件区域1,条件1,条件区域2,条件2,...) --推送60 SUMPRODUCT((条件区域=条件)*1) --推送22 SUMPRODUCT((条件区域1=条件1)*(条件区域2=条件2)*...*(条件区域n=条件n)) --推送22 问题:1.求正式员工的人数;2.求销售二部正式员工的人数 公式1: =COUNTIF(B2:B14,A17) =COUNTIFS(B2:B14,A22,C2:C14,B22) 公式2: =SUMPRODUCT((B2:B14=A17)*1) =SUMPRODUCT((B2:B14=A22)*(C2:C14=B22)) ------------------------------------ 4.条件平均值 AVERAGEIF(条件区域,条件,平均值区域) --推送86 AVERAGEIFS(平均值区域,条件区域1,条件1,条件区域2,条件2,...) --推送86 问题:1.求正式员工的平均销售额;2.求销售二部正式员工的平均销售额 公式: =AVERAGEIF(B2:B14,A17,D2:D14) =AVERAGEIFS(D2:D14,B2:B14,A21,C2:C14,B21) ------------------------------------ 5.区间条件 VLOOKUP(查找值,条件区域,返回列,模糊查找) --推送12 问题:根据销售额,确定各员工的业绩评级 公式:=VLOOKUP(D8,$D$2:$E$5,2,TRUE) ------------------------------------ 6.多条件查找 LOOKUP(1,0/(条件区域1=条件1)*(条件区域2=条件2)*...*(条件区域n=条件n),返回值区域) --推送37 问题:查找编号为A003的销售二部的正式员工的姓名 公式:=LOOKUP(1,0/((D2:D14=A17)*(B2:B14=B17)*(C2:C14=C17)),A2:A14) ------------------------------------ 7.多条件最值 =MAX(IF(条件区域=条件,对比数据)) --推送72 =MIN(IF(条件区域=条件,对比数据)) --推送72 =MAX(IF((条件区域1=条件1)*(条件区域2=条件2)*...*(条件区域n=条件n),对比数据)) --推送72 =MIN(IF((条件区域1=条件1)*(条件区域2=条件2)*...*(条件区域n=条件n),对比数据)) --推送72 问题:1.求正式员工的最大/最小销售额;2.求销售二部正式员工的最大/最小销售额 公式1: =MAX(IF(B2:B14=A17,D2:D14)) =MIN(IF(B2:B14=A17,D2:D14)) 公式2: =MAX(IF((B2:B14=A22)*(C2:C14=B22),D2:D14)) =MIN(IF((B2:B14=A22)*(C2:C14=B22),D2:D14)) PS:上述公式为数组公式,需同时按下Ctrl Shift Enter三键才能执行 ------------------------------------- 怎么样,通过表妹的细致总结,小伙伴们对于【条件】问题是不是可以手到擒来,轻松应对了呢?勤奋好学的你,赶快自己动手试试吧~~ |
|