个人微信号 | (ID:LiRuiExcel520)
微信服务号 | 跟李锐学Excel(ID:LiRuiExcel)
微信公众号 | Excel函数与公式(ID:ExcelLiRui)
采儿工作10年了,一般的Excel公式都能顺手拈来,但是昨天却因报表做错被老板骂哭了。
原来,这次的报表难度大幅增加,不但涉及到混杂数值提取、多条件查询,而且还涉及跨多工作表查询和汇总,再加上老板催的急,难怪连一贯干练的采儿也被难住了。
为了让更多有经验的白领面对困境时,不再遭遇这种窘境,我专门整理出下面五套经典公式模板。
A列数据由文本和数字混杂构成,要求提取字符串中的数字。
数组公式如下,按Ctrl+Shift+Enter输入
=-LOOKUP(0,-MID(A2,MIN(FIND(ROW($1:$10)-1,A2&56^7)),ROW(INDIRECT("1:"&LEN(A2)))))
一句话解析:
使用FIND和ROW函数定位数字起始位置,然后再利用MID函数截取数字,最后用LOOKUP函数完成提取。
下图中左侧数据源包含不同店铺的商品销量,要求根据店铺和商品多条件查询对应的销量。
公式如下:
=LOOKUP(1,0/(($B$2:$B$13=F2)*($C$2:$C$13=G2)),$D$2:$D$13)
一句话解析:
这又是LOOKUP万能公式的经典应用,在Excel特训营二期初级班和八期进阶班中都有多个案例超清视频讲解,下方扫码进知识店铺查看。
要求根据A列的金额,转为会计行业规范的人民币大写形式。
这个需求无论是在填写支票、发票时还是在写合同时都会经常遇到。
下面的公式拿来即用,不必死背,直接在工作中套用即可。
公式如下
=IF(ROUND(A2,2)<0,"无效数字",IF(ROUND(A2,2)=0,"零",IF(ROUND(A2,2)<1,"",TEXT(INT(ROUND(A2,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A2,2)*10)-INT(ROUND(A2,2))*10=0,IF(INT(ROUND(A2,2))*(INT(ROUND(A2,2)*100)-INT(ROUND(A2,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(A2,2)*10)-INT(ROUND(A2,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A2,2)*100)-INT(ROUND(A2,2)*10)*10)=0,"整",TEXT((INT(ROUND(A2,2)*100)-INT(ROUND(A2,2)*10)*10),"[dbnum2]")&"分")))
下图中数据源分散在北京、上海、广州3张工作表中,要求根据订单编号在多工作表中查询对应的业务员、产品、销量。
公式如下
=IFERROR(VLOOKUP($A2,INDIRECT(INDEX({"北京","上海","广州"},MATCH(0,0/COUNTIF(INDIRECT({"北京","上海","广州"}&"!A:A"),$A2),))&"!A:D"),COLUMN(B:B),),"")
一句话解析:
使用COUNTIF函数和INDEX+MATCH组合找到目标数据所在工作表,再用INDIRECT函数实现跨表引用,最后利用VLOOKUP查询。
还是上面的数据源,要求根据产品对多工作表按条件跨表汇总。
公式如下
=SUM(SUMIF(INDIRECT({"北京","上海","广州"}&"!c:c"),A2,INDIRECT({"北京","上海","广州"}&"!d:d")))
一句话解析:
借助INDIRECT函数实现跨表引用,再使用SUMIF函数条件求和,最后利用SUM函数汇总多工作表返回的结果。
希望这篇文章能帮到你!怕记不住可以发到朋友圈自己标记。
>>推荐阅读 <<
(点击蓝字可直接跳转)
VLOOKUP遇到她,瞬间秒成渣!
99%的财务会计都会用到的表格转换技术
86%的人都撑不到90秒,这条万能公式简直有毒!
最有用最常用最实用10种Excel查询通用公式,看完已经赢了一半人
以一当十:财务中10种最偷懒的Excel批量操作
为什么要用Excel数据透视表?这是我见过最好的答案
如此精简的公式,却刷新了我对Excel的认知…
错把油门当刹车的十大Excel车祸现场,最后一个亮了…
让人脑洞大开的VLOOKUP,竟然还有这种操作!
Excel动态数据透视表,你会吗?
让VLOOKUP如虎添翼的三种扩展用法
这个Excel万能公式轻松KO四大难题,就是这么简单!
SUM函数到底有多强大,你真的不知道!