分享

成为Excel大神,这8个公式必须学会,建议收藏!

 Excel不加班 2023-03-24 发布于广东

跟卢子来重新认识这些曾经的经典公式,都是早期前辈付出了极大的心血才研究出来。以下的所有公式都是数组公式,需要按Ctrl+Shift+Enter三键结束。

1.统计不重复项目数

=SUM(1/COUNTIF(A2:A72,A2:A72))

2.提取不重复项目

=INDEX(A:A,SMALL(IF(MATCH($A$2:$A$72,$A$2:$A$72,0)=ROW($2:$72)-1,ROW($2:$72),4^8),ROW(A1)))&""

3.筛选某个项目的所有明细

=IFERROR(INDEX(A:A,SMALL(IF($A$2:$A$72=$J$1,ROW($2:$72)),ROW(A1))),"")

4.将字符内的数字提取出来

=-LOOKUP(1,-MID(A2,MIN(FIND(ROW($1:$10)-1,A2&1/17)),ROW($1:$15)))

5.按分隔符号拆分科目

=TRIM(MID(SUBSTITUTE($A2,"-",REPT(" ",99)),COLUMN(A1)*99-98,99))

6.将金额依次拆分到后面的单元格

=LEFT(RIGHT(TEXT($B2*100," ¥000;;"),COLUMNS(F:$P)))


7.将小写金额转变成大写金额

=IF(G17<0,"无效数值",IF(G17=0,"",IF(G17<1,"",TEXT(INT(G17),"[dbnum2]")&"元")&IF(INT(G17*10)-INT(G17)*10=0,IF(INT(G17)*(INT(G17*100)-INT(G17*10)*10)=0,"","零"),IF(AND((INT(G17)-INT(G17/10)*10)=0,INT(G17)>0),"零"&TEXT(INT(G17*10)-INT(G17)*10,"[dbnum2]")&"角",TEXT(INT(G17*10)-INT(G17)*10,"[dbnum2]")&"角"))&IF((INT(G17*100)-INT(G17*10)*10)=0,"整",TEXT(INT(G17*100)-INT(G17*10)*10,"[dbnum2]")&"分")))

8.全年12个月按条件统计

按姓名统计Sheet1到Sheet12的C列。

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW($1:$12)&"!b:b"),B7,INDIRECT("Sheet"&ROW($1:$12)&"!c:c")))


如果工作表名称是1月到12月,那就略作改变即可。

=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$12)&"月!b:b"),B7,INDIRECT(ROW($1:$12)&"月!c:c")))

这些很复杂,又经常用到的经典公式,你会几个?

陪你学Excel,一生够不够?

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

    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多