【例10-9】多条件计数1
【例10-9】多条件计数2
• • • • • • • • • • • • • • • • =COUNTIF($I$3:$I$18,">=90") =COUNTIFS($I$3:$I$18,">=80",$I$3:$I$18,"<90") “=B22/COUNT($I$3:$I$18)” {=SUM(IF($I$3:$I$18>=80,IF($I$3:$I$18<90,1,0)))} {=SUM(($I$3:$I$18<60))}或者{=SUM(($I$3:$I$18<60)*1)} {=SUM(($I$3:$I$18>=80)*($I$3:$I$18<90))} =SUMPRODUCT(($I$3:$I$18>=80)*($I$3:$I$18<90)) =SUM(COUNTIF($I$3:$I$18,">="&{80,90})*{1,-1}) {=COUNT(0/(($I$3:$I$18>=80)*($I$3:$I$18<90)))} =COUNTIFS($C$3:$C$18,"女",$I$3:$I$18,">=90") {=SUM(IF($C$3:$C$18="女",IF($I$3:$I$18>=90,1,0)))} {=SUM(($I$3:$I$18>=90)*($C$3:$C$18="女"))} =SUMPRODUCT(($C$3:$C$18="女")*($I$3:$I$18>=90)) {=COUNT(0/(($C$3:$C$18="女")*(I7:I19>=90)))} =COUNTIFS($D$3:$D$18,$E$42,E$3:E$18,">"&AVERAGE(E$3:E$18)) =COUNTIF(OFFSET(D2,MATCH($F$46,$D$3:$D$18,),MATCH($F$47,$E$2:$G$2,0 ),COUNTIF($D$3:$D$18,$F$46)),">="&$F$48)
•
• • • •
•
【例10-10】多条件求和1
• •
•
•
“{=SUM(($C$2:$C$40=J3)*($D $2:$D$40=K3)*$G$2:$G$40)}” “{=SUM(($C$2:$C$40=J3)*($D $2:$D$40=K3)*$H$2:$H$40)}” “{=SUM(IF(($C$2:$C$40=J3)*( $D$2:$D$40=K3),$G$2:$G$40))} ” “{=SUM(IF(($C$2:$C$40=J3)*(
统计单元格区域中n个最大值、 最小值
统计单元格区域中除0外的单元 格的平均值
统计单元格区域数组取整后的和
统计单元格区域每隔n个数的和
整数的各数位求和
在单元格区域中查找指定项目
在单元格区域中查找极值数据 的位置
在单元格区域中查找长度最大 的文本
在单元格区域中查找与指定值 最接近的值
【说明】 数组公式“{=INDEX(A1:A10,MATCH(MIN(ABS(B1A1:A10)),ABS(B1-A1:A10),0))}”中，MATCH(MIN(ABS(B1A1:A10)),ABS(B1-A1:A10),0)返回最小差值（MIN(ABS(B1A1:A10))，即2）在差值数组（ABS(B1-A1:A10)，即 {8;3;2;7;12;17;22;27;32;37}）中第一次出现的位置，即行号n （本例为3），然后使用INDEX函数返回第n行1列（本例为第 3行1列）单元格的值
【例10-11】多条件求和以及不 重复数据个数的统计1
【例10-11】多条件求和以及不 重复数据个数的统计2
• • • • “{=H2:H40-I2:I40}” “=ROUND(SUMPRODUCT(G2:G 40*H2:H40),0)” “{=ROUND(SUM(G2:G40*H2:H4 0),0)}” “{=SUM(IF((D2:D40="海鲜 ")*(MONTH(J2:J40)=2), I2:I40, 0))}” “{=SUM((D2:D40="海鲜 ")*(MONTH(J2:J40)=2)*I2:I40)}” “=SUMPRODUCT((D2:D40="海 鲜 ")*(MONTH(J2:J40)=2)*I2:I40)” “{=SUM(((D2:D40="点心 ")+(D2:D40="饮料 "))*(G2:G40*I2:I40))}” “{=SUM(IF((D2:D40="点心 ")+(D2:D40="饮料 "),G2:G40*I2:I40))}” “=SUMIFS(H2:H40,H2:H40,">=50
【例10-6】VLOOKUP批量查找
【例10-7】提取不重复值1
• • • •
•
“=IFERROR(LOOKUP(1,0/ISNA(MATCH($C$2:$C$41,H$3:H3,0)),$C$2:$C$41),"" )” “=IFERROR(LOOKUP(1,0/(COUNTIF($I$3:I3,$C$2:$C$41)=0),$C$2:$C$41),"")” “{=INDEX(C:C,MATCH(0,COUNTIF(J$3:J3,$C$2:$C$42),0)+1)&""}” “{=IFERROR(INDEX($C$2:$C$41,MATCH(0,COUNTIF($K$3:K3,$C$2:$C$41),0)) &"","")}” “{=INDEX(C:C,SMALL(IF(MATCH($C$2:$C$41,$C$2:$C$41,0)=ROW($2:$41)1,ROW($2:$41),4^8),ROW(1:1)))&""}”
【例10-8】条件计数
• • • • • • • • • • •
“{=E3:E17+F3:F17+G3:G17}” “{=ROUND(H3:H17/3,0)}” “=COUNTIF($D$3:$D$17,"一班")” “=COUNTIFS($D$3:$D$17,"一班",$C$3:$C$17,"男")” “{=SUM(IF(D3:D17="一班",1,0))}” “{=SUM((D3:D17="一班"))” “{=SUM(IF(D3:D17="一班",IF(C3:C17="男",1,0)))}” “{=COUNT(0/(D3:D17="一班"))}” “{=COUNT(0/((D3:D17="一班")*(C3:C17="男"))))}” “{=SUM((D3:D17="一班")*(C3:C17="男"))}” “=SUMPRODUCT((D3:D17="一班")*(C3:C17="男"))”
【例10-3】数据反序
• 【参考步骤】 • （1）定义数据名称。选中A1:A7单元格区域，在名称文本框中输 入Data，按回车键确认。 • （2）选中B1:B7单元格区域，输入数组公式 “{=INDEX(Data,ROWS(Data)-ROW(Data)+1)}”
【例10-4】数据动态排序
【例10-5】筛选非重复项数据
【例10-7】提取不重复值2
•
•
• •
“{=INDEX(C:C,MIN(IF(COUNTIF($M$3:M3,$C$2:$C$41)=0,ROW($2:$41),4^8))) &""}” “{=IFERROR(OFFSET($C$1,MATCH(0,COUNTIF(R$1:R1,$C$2:$C$41),0),0),"")} ” “{=OFFSET($C$1,SMALL(IF(MATCH($C$2:$C$41,$C$2:$C$41,0)=ROW($C$2:$ C$41)-1,ROW($C$2:$C$41)-1,65536),ROW(A1)),0)&""}” “{=IFERROR(INDEX(D:D,SMALL(IF(FREQUENCY($D$2:$D$41,$D$2:$D$41),R OW($2:$41)),ROW(1:1))),"")}”
• •
•
•
•
• • •
•
•
“=SUMIF(H2:H40,">=50")SUMIF(H2:H40,">100")” “{=SUM(IF((H2:H40>=50)*(H2:H40<=100),H2:H40 ))}” “{=SUM((H2:H40>=50)*(H2:H40<=100)*(H2:H40)) }” “=SUMPRODUCT((H2:H40>=50)*(H2:H40<=100)* (H2:H40))” “{=SUM(1/COUNTIF(E2:E40,E2:E40))}”返回行或列中最后位置的值
比较两个单元格区域数据的异同
【例10-1】单条件筛选数据
【例10-2】多条件筛选数据
• 【参考步骤】 • （1）定义数据名称。选中A1:A10单 元格区域，在名称文本框中输入Data ，回车键确认。 • （2）选中B1:B10单元格区域，输入 数组公式 “{=IFERROR(INDEX(Data, SMALL(IF((Data>=0)*(Data<=100),R OW(Data)),ROW(Data))),"")}”。 • （3）选中C1:C10单元格区域，输入 数组公式 “{=IFERROR(INDEX(Data, SMALL(IF((Data<0)+(Data>100),RO