=SUM('*'!C:C)
=SUM('1月:7月'!C:C)
=--B2
=SUM(C2:C8)
=SUMPRODUCT(--B2:B8)
=VLOOKUP(A2,H:I,2,0)
=VLOOKUP(查找值,要在哪個區(qū)域查找,返回區(qū)域第幾列,0)
=SUM(D2:D5)
=SUMPRODUCT(B2:B5,C2:C5)
=SUMIF(B:B,G2,E:E)
=SUMIF(條件區(qū)域,條件,求和區(qū)域)
=SUMPRODUCT((MONTH($A$2:$A$9)=G2)*$E$2:$E$9)
=SUMPRODUCT((條件區(qū)域=條件)*求和區(qū)域)
=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,"重復(fù)","")
=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))),"")
=IF(C4<0,"無效數(shù)值",IF(C4=0,"",IF(C4<1,"",TEXT(INT(C4),"[dbnum2]")&"元")&IF(INT(C4*10)-INT(C4)*10=0,IF(INT(C4)*(INT(C4*100)-INT(C4*10)*10)=0,"","零"),IF(AND((INT(C4)-INT(C4/10)*10)=0,INT(C4)>0),"零"&TEXT(INT(C4*10)-INT(C4)*10,"[dbnum2]")&"角",TEXT(INT(C4*10)-INT(C4)*10,"[dbnum2]")&"角"))&IF((INT(C4*100)-INT(C4*10)*10)=0,"整",TEXT(INT(C4*100)-INT(C4*10)*10,"[dbnum2]")&"分")))
=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),""))
