20150623-成績單

V2 =IF(T2="V",IF(U2="V","推甄成功","再加油!"),"")

排名 R2=RANK(Q2,$Q$2:$Q$13)
三主科平均  S2=AVERAGE(F2:H2)
確認主科平均 T2=IF(S2>=80,"V","")
確認排名 U2=IF(R2<=8,"V","")

推甄完整版01
=IF(IF(AVERAGE(F2:H2)>=80,"V","")="V",IF(IF(RANK(Q2,$Q$2:$Q$13)<=8,"V","")="V","推甄成功","再加油!"),"")

推甄完整版02
=IF(AVERAGE(F2:H2)>=80,IF(RANK(Q2,$Q$2:$Q$13)<=8,"推甄成功","再加油!"),"")


COUNTIF應用
---
85分以上(>=85)
F16=COUNTIF(F2:F13,">=85")

70-85分(>=70, <85)
F17=COUNTIF(F2:F13,">=70")-F16

60-70分(>=60, <70)
F18=COUNTIF(F2:F13,">=60")-F17-F16

60分以下(<60)
F19=COUNT(F2:F13)-SUM(F16:F18)

數值小數位數處理
---
INT 取整數
Y2=INT(S2)

ROUND 四捨無入
Z2=ROUND(S2,2)

ROUNDUP 無條件進位
AA=ROUNDUP(S2,2)

ROUNDDOWN 無條件捨去
AB=ROUNDDOWN(S2,2)

文字函數練習
---
姓氏02
F2=IF(LEN(B2)>3,LEFT(B2,2),LEFT(B2,1))

名字02
G2=IF(LEN(B2)<3,RIGHT(B2,1),RIGHT(B2,2))

匿名
H2=F2&"Ｏ"&IF(LEN(B2)<3,"",RIGHT(B2,1))