免费送给想要成为业务数据分析师的童鞋(适用于Excel初、中级段位)
文章目录
- 前言
- 一、多条件匹配查找(用好,锁行锁列,方便拖拽公式)
- 1.LOOKUP函数实现多条件匹配
- 2.多条件匹配求和:SUMIFS、SUMPRODUCT(适用于数字条件相加)
- 3.辅助列+VLOOKUP函数实现多条件匹配
- 4.数组公式:多条件匹配结果区域。相同条件,匹配最大(或最小)的区域
- 5.数据透视表和高级筛选
- 二、大小排序
- 1.不出现相同排名(以出现顺序进行优先排列)
- 三、MID+FIND函数嵌套提取Excel单元格括号里的内容
- 总结:条条大路通罗马,适合自己的才是最重要的
前言
如果三年前,我能看到类似这样的文章,就不会走太多的弯路,致敬还在拼搏和奋斗中的你们,加油!!!
一、多条件匹配查找(用好,锁行锁列,方便拖拽公式)
1.LOOKUP函数实现多条件匹配
LOOKUP高手最常用的一个函数,有些小套路:
=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),结果区域)
公式为:
=LOOKUP(1,0/(($A
2
:
2:
2:A
15
=
F
2
)
∗
(
15=F2)*(
15=F2)∗(B
2
:
2:
2:B
15
=
G
2
)
)
,
15=G2)),
15=G2)),C
2
:
2:
2:C$15)
优势:不使用辅助列一步到位,增加条件也方便,适用场合广泛,数字还是文本都可以。
2.多条件匹配求和:SUMIFS、SUMPRODUCT(适用于数字条件相加)
公式为:=SUMIFS(D:D,A:A,F2,B:B,G2)
SUMIFS(求和列,条件列1,条件1,条件列2,条件2……)
不足:当要匹配的结果不是数字时,就不能使用这个方法,同时当满足多个条件的数据不是唯一值时,结果可能错误。
同理(区域只能是该列的第几行到N行,不能是列的区域):
=SUMPRODUCT(($A
2
:
2:
2:A
15
=
F
2
)
∗
(
15=F2)*(
15=F2)∗(B
2
:
2:
2:B
15
=
G
2
)
∗
15=G2)*
15=G2)∗D
2
:
2:
2:D$15)
和SUMIFS方法差不多,SUMPRODUCT函数的功能是计算多个数组或区域的数据乘积之和
不足:数组计算,数据量大时比较卡,对函数的用法不理解容易出错,并只对数字的匹配结果适用。
3.辅助列+VLOOKUP函数实现多条件匹配
VLOOKUP(查找值,查找区域,第几列找,精确查找)
公式为:=VLOOKUP(G2&“-”&H2,$A
1
:
1:
1:E$15,4,0)
公式对于匹配结果为数字或者非数字的时候都可以使用,适用面比SUMIFS更广泛。
不足:要增加辅助列才能使用
4.数组公式:多条件匹配结果区域。相同条件,匹配最大(或最小)的区域
=MAX((条件区域1=条件1)*(条件区域2=条件2)结果区域),这个公式是一个数组公式,只适合结果为数字的情况。
还可以这样写:
={MAX(IF((Sheet2!
A
:
A:
A:A=
B
9
)
∗
(
S
h
e
e
t
2
!
B9)*(Sheet2!
B9)∗(Sheet2!E:
E
=
E=
E=F9)(Sheet2!
I
:
I:
I:I<=AA
2
)
,
S
h
e
e
t
2
!
2),Sheet2!
2),Sheet2!I:$I))}
={MAX(IF((Sheet2!$A:$A=$B9)*(Sheet2!$E:$E=$F9)*(Sheet2!$I:$I<=AA$2),Sheet2!$I:$I))}
查找最大值
同理:
=MIN((条件区域1=条件1)*(条件区域2=条件2)*结果区域),这个公式是一个数组公式,只适合结果为数字的情况。
5.数据透视表和高级筛选
其实如果不用公式的话,数据透视表和高级筛选或者切片器也是更好的选择,这些方法会在今后,有时间时抽空,分享给大家
二、大小排序
试过网上的各种方式(我觉得还是这种,比较合适):
1.不出现相同排名(以出现顺序进行优先排列)
=RANK(B2,$B$2:$B$11)+COUNTIF($B$2:B2,B2)-1
参考链接:
https://baijiahao.baidu/s?id=1655083909022930257&wfr=spider&for=pc
三、MID+FIND函数嵌套提取Excel单元格括号里的内容
=MID(B2,FIND(“(”,B2)+1,FIND(“)”,B2)-1-FIND(“(”,B2))
最后,就可以把括号 “()” 里面的字符取出来了
总结:条条大路通罗马,适合自己的才是最重要的
更多推荐
工作中Excel常用公式(面试必备)
发布评论