登陆注册
6516

比LOOKUP函数更好用,这才是Excel真正的多条件查找之王!

站长网2023-07-29 10:31:382

VIP学员的问题,根据目的国、重量、渠道3个条件,查找对应的总费用。比如国家为US,重量60G,A渠道,在右边对应表的第一个区间,对应75和18,总费用=60*75/1000 18。

多条件查找对应值,昨天卢子用LOOKUP函数解决,如果是查找数字,用SUMIFS函数会更好。

查找运费:

=SUMIFS($K:$K,$H:$H,$A2,$M:$M,C$1,$I:$I,"<="&$B2,$J:$J,">="&$B2)

函数语法:支持引用整列,写区域比LOOKUP函数方便,运算效率更快。

=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,条件区域n,条件n)

最小重量<=重重,最大重量>=重量,两个都满足,就代表在区间内。比如1<=60,100>=60,都满足,证明在区间1~100。

同理,处理费,只需将K列改成L列即可。

=SUMIFS($L:$L,$H:$H,$A2,$M:$M,C$1,$I:$I,"<="&$B2,$J:$J,">="&$B2)

将这2个公式整合起来,就可以得到总费用。

=SUMIFS($K:$K,$H:$H,$A2,$M:$M,C$1,$I:$I,"<="&$B2,$J:$J,">="&$B2)*$B2/1000 SUMIFS($L:$L,$H:$H,$A2,$M:$M,C$1,$I:$I,"<="&$B2,$J:$J,">="&$B2)

上面的问题条件太多,看起来比较费劲,来一些简单的案例再进行说明。

1.统计每个目的国的金额

除了可以多条件求和,单条件求和也可以。=SUMIFS(E:E,D:D,A2)

2.统计每个目的国、渠道的金额=SUMIFS(F:F,E:E,A2,G:G,B2)

3.统计每个目的国、渠道的最大、最小金额

IFS系列函数,除了SUMIFS函数外,还有很多,语法都一样。比如多条件求最大值MAXIFS函数。

=MAXIFS(G:G,F:F,A2,H:H,B2)

最小值MINIFS函数。=MINIFS(G:G,F:F,A2,H:H,B2)

4.查找编码、渠道对应的金额

左边的编码是文本格式,右边的编码是数字格式。

用LOOKUP函数查找的时候需要统一格式,而SUMIFS函数不需要。

=SUMIFS(F:F,E:E,A2,G:G,B2)

假如存在查找不到的,直接返回0,而LOOKUP返回错误值#N/A。

SUMIFS函数比LOOKUP函数有3大优势:

01 引用整列运算效率很快

02 格式不同也可以统计03 查找不到返回0,不需要嵌套IFERROR处理错误值

作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)

0002
评论列表
共(0)条