雲端硬碟分享

比對資料
=IF(ISERROR(VLOOKUP(C4,$R$4:$R$28,1,FALSE)),"V","")

學生成績單

完成SUM, AVERAGE, MAX, MIN
COUNT, COUNTA
COUNTIF
IF

使用COUNTIF製作次數分配表

01_Excel_學生成績單_01，下載

巢狀IF
=IF(Q2>=60,IF(Q2>=70,IF(Q2>=80,"A","B"),"C"),"D")


多重條件
=IF(AND(Q2>=70,S2<=10,C2="男"),"師大附中",IF(AND(Q2>=70,S2<=10,C2="女"),"北一女","加油!!"))


ROUND, ROUNDUP, ROUNDDOWN, INT
01_Excel_學生成績單_02，下載


稱謂
=LEFT(B2,1)&"同學"&RIGHT(B2,2)

郵件帳號
=LEFT(D2,FIND("@",D2)-1)"))

郵件域名
=RIGHT(D2,LEN(D2)-FIND("@",D2))
=MID(D2,FIND("@",D2)+1,LEN(D2)-FIND("@",D2))

01_Excel_學生成績單_03，下載

Excel高階函數應用專題班_第4期

範例下載，Download