比LOOKUP函数更好用,这才是Excel真正的多条件查找之王!
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)
太好用了!学了16年Excel,才知道有如此牛逼的新函数
与30万粉丝一起学Excel昨天的案例,卢子无意间发现了一个超牛逼的新函数,值得再发一篇文章说明。VIP学员的问题,左边的明细大概有2000行,每10行复制后转置粘贴到右边,有没简单的方法?早期要构造一个5行10列的序列,都要用ROWCOLUMN组合很久才搞出来。=COLUMN(A1)ROW(A1)*10-10站长网2023-07-29 13:50:430001AI一键换脸离线版来了,解压即用!
Roop换脸Roop是由民间大神s0md3v,制作的一款开源AI视频换脸项目。项目官方介绍如下:你只需要一张所需脸部的图像,没有数据集,无需训练,你就可以将拍摄视频其中的面孔替换为你选择的面孔。之前我也介绍过如何本地部署Roop:AI一键换脸,无限且免费!站长网2023-07-30 13:00:030002ps产品光圈特效视频:极坐标制作圆环图层复制叠加颜色减淡混合
ps产品光圈特效视频|ps极坐标圆环制作视频|ps图层复制叠加视频|ps颜色减淡混合视频本视频教程由部落窝教育分享。站长网2023-07-27 09:17:510001一组TEXT的另类用法,无脑套用
TEXT函数判断收入增减=TEXT(C2-B2,"比上年多0元;比上年少0元;与上年相同")第二参数具备判断功能:C2-B2大于0,显示“比上年多n元”C2-B2小于0,显示“比上年少n元”C2-B2等于0,显示“与上年相同”TEXT计算课程总时长=TEXT(SUM(B2:B9),"h小时m分钟")SUM计算网课总时长后转换为更接地气的表示格式xx小时xx分钟。甚至可以精确到秒:站长网2023-07-27 10:20:230000