學生成績單

排除不及格總分
=SUMIF(F2:O2,">=60")


排除不及格平均
=SUMIF(F2:O2,">=60")/(COUNT(F2:O2)-COUNTIF(F2:O2,"<60"))

01_Excel_學生成績單_0808.xls，Download

英文名***
01_Excel_學生成績單_0808_2.xls，Download

First-空格
=FIND(" ",A2)

Second-空格
=FIND(" ",A2,B2+1)

First-Name
=LEFT(A2,B2-1)

Second-Name
=MID(A2,B2+1,C2-B2-1)

Third-Name
=RIGHT(A2,LEN(A2)-C2)

First-***
=LEFT(D2,LEN(D2)-3)&"***"

Second-***
=MID(A2,B2+1,C2-B2-1-3)&"***"

Third-***
=MID(A2,C2+1,LEN(A2)-C2-3)&"***"

完整名
=G2&" "&H2&" "&I2