12个经典函数组合

01

VLOOKUP + IF

用VLOOKUP函数进行查找时,如果查找值不在首列,会出现错误结果。这时需要结合IF函数来实现正常查找。如下图表格,输入公式:

=VLOOKUP(F2,IF({1,0},B2:B11,A2:A11),2,0)

说明:利用IF({I,0},区域1,区域2)对查找的数据区域进行重新构建。

02

VLOOKUP + MATCH

这个函数组合经常用来进行交叉查询,如下图表格,输入公式:=VLOOKUP(A11,$A$1:$F$7,MATCH(B11,$A$1:$F$1,0),0)

说明:先用MATCH(B11,$A$1:$F$1,0)确定姓名所在列,再用VLOOKUP函数进行查找引用。

03

VLOOKUP + INDIRECT

VLOOKUP跨表或多表查询时,经常结合INDIRECT函数。如下图表格,输入公式:

=IFERROR(VLOOKUP($B2,INDIRECT(C$1&'!B:C'),2,0),'')

说明:这里用VLOOKUP函数和INDIRECT函数结合,对各个明细表B、C两列数据查找区域进行动态引用。公式中还结合了IFERROR函数进行容错处理。

04

SUM+SUMIF

这对函数组合用来进行条件求和,如下图表格,输入公式:

=SUM(SUMIF(A2:A11,{'李霞霞';'戴梦梦'},D2:D11))

05

SUM + COUNTIF

SUM函数结合COUNTIF函数使用,可以用来统计不重复个数。如下图表格,输入公式:

=SUM(1/COUNTIF(A2:A14,A2:A14)),按Ctrl+Shift+Enter组合键完成。

说明:

COUNTIF(A2:A14,A2:A14):对每个单元格进行统计判断;1/COUNTIF(A2:A14,A2:A14):删除重复值,假如只有出现一个值,1除以1就等于1,若是出现2个,那么1除以2等于1/2,所有1/2求和也等于1,相当于获取不重复人数。

06

OFFSET+ MATCH

这对函数组合用来进行多条件查找引用。如下图表格,输入公式:

=OFFSET(A1,MATCH(H2,A2:A11,0),MATCH(G2,B1:E1,))

说明:先用MATCH函数分别定位出指定月份和产品在A2:A11和B1:E1区域中的位置,作为OFFSET函数的第2和第3个参数,然后以A1为基准位置偏移对应的行数和列数即可。

07

INDEX + MATCH

这对函数组合,也是常用于数据的查询引用。如下图表格,输入公式=INDEX(B2:B26,MATCH(D2,A2:A26,0))

说明:先判断姓名在指定区域的位置,再结合INDEX函数获取相应的手机号。

08

INDEX + SMALL + IF

非常经典的一个函数组合,被称之为查找三剑客,用来获取满足条件的多个值。如下图表格,输入公式:

=INDEX(B:B,SMALL(IF($A$1:$A$11=$E$2,ROW($A$1:$A$11),4^8),ROW(A1)))&''

说明:

SMALL函数用来定位所有E2在A列中的位置(从小到大)4^8这里指的是一个比较大的数,在这个IF函数公式中,如果单元格区域A1:A11的值等于E2,就显示E2在A列中所在的行号,如果不等于就显示一个较大的数当我们利用SMALL函数得到行号之后,结合INDEX函数一对多查找需要的值最后的&''是用来进行容错处理。

09

INDIRECT+MATCH

INDIRECT函数结合MATCH函数使用,可以实现逆向查询。如下图表格,输入公式:

=INDIRECT('A'&MATCH('王五',C2:C8,0)+1)

说明:先用MATCH函数返回王五在查找区域中处于第4行,即'A'&MATCH('王五',C2:C8,0)+1返回的结果为A5,然后结合INDIRECT引用函数返回该单元格的引用。

10

LEFT/RIGHT + LEN + LENB

这两对函数组合可以用来在混合文本中提取指定的内容。如下图表格,输入公式:

=LEFT(C2,LENB(C2)-LEN(C2))

=RIGHT(C2,2*LEN(C2)-LENB(C2))

11

SUMPRODUCT + SUBSTITUTE

SUMPRODUCT函数结合SUBSTITUTE函数使用,可以用来对带单位的数据进行求和统计。如下图表格,输入公式:

=SUMPRODUCT(SUBSTITUTE(C2:C11,'元','')*1)&'元'

说明:SUBSTITUTE(D2:D10,'元','')先将C列中的“元”全部替换为空值,乘以1将文本转换为数值,再利用SUMPRODUCT函数求和。

12

IF + AND/OR

IF函数和AND函数结合,主要用来获取同时满足多个条件的值。如下图表格,输入公式:

=IF(AND(B2='女',C2>85),'优秀','')

IF函数和OR函数结合,则是用来获取满足任意一个条件的值。如下图表格,输入公式:

=IF(OR(C2>90,D2>90),'优秀','')

(0)

相关推荐