Excel常用公式大全共23条,助你纵横职场,碾压90%的同事!

=SUM('*'!C:C)

=SUM('1月:7月'!C:C)

=--B2

=SUM(C2:C8)

=SUMPRODUCT(--B2:B8)

=VLOOKUP(A2,H:I,2,0)

=VLOOKUP(查找值,要在哪个区域查找,返回区域第几列,0)

=SUM(D2:D5)

=SUMPRODUCT(B2:B5,C2:C5)

=SUMIF(B:B,G2,E:E)

=SUMIF(条件区域,条件,求和区域)

=SUMPRODUCT((MONTH($A$2:$A$9)=G2)*$E$2:$E$9)

=SUMPRODUCT((条件区域=条件)*求和区域)

=SUMPRODUCT((TEXT($A$2:$A$9,"emm")="2021"&G2)*$E$2:$E$9)

=N(B2)

=IF(N(B2)>=60,"及格","不及格")

=IF(D2=E2,"正确","错误")

=IF(ROUND(D2,0)=E2,"正确","错误")

=IF(COUNTIF(B:B,B2&"*")>1,"重复","")

=IF(MOD(MID(B2,17,1),2),"男","女")

=TEXT(MID(B2,7,8),"0-00-00")

=DATEDIF(D2,TODAY(),"y")

=DATEDIF(D2,TODAY(),"y")&"岁"&DATEDIF(D2,TODAY(),"ym")&"个月"

=YEAR(A2)

=MONTH(A2)

=DAY(A2)

=TEXT(A2,"aaaa")

=TEXT(A2,"dddd")

=SUBTOTAL(3,B$2:B2)*1

=ROW(A1)

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

=INDEX($B:$B,SMALL(IF($A$2:$A$19=$D2,ROW($2:$19),4^8),COLUMN(A1)))&""

=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$16=$H$1,ROW($2:$16)),ROW(A1))),"")

=IFERROR(INDEX(B:B,SMALL(IF(A$2:A$16=D2,ROW($2:$16)),COUNTIF(D$2:D2,D2))),"")

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

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

=TEXTJOIN("，",1,IFERROR(VLOOKUP(N(IF({1},--TRIM(MID(SUBSTITUTE(D2,"，",REPT(" ",50)),{1,2,3,4,5,6,7,8,9}*50-49,50)))),A:B,2,0),""))

一次报名成为VIP会员，所有课程永久免费学，永久答疑，仅需1500 元，待你加入。

报名后加卢子微信chenxilu2019，发送报名截图邀请进群。