目的,透過Excel函式,利用學號方便的查詢學生成績,具體如下:
1、學生成績表:
不同年紀,不同班級的學生成績記錄混在一起
2、學號的設定:如10101
1,年級;
01,一班;
01,學生在班級中的序號;
10203為一年級二班03號同學;
3、姓名、班級資訊的自動獲取
學號:F7,需要手動輸入;
姓名:=IFERROR(VLOOKUP(F7,X2:Y19,2,FALSE),“查無資訊”),自動獲取;
年級:=IFERROR(IFS(LEFT(F7,1)=“1”,“一”,LEFT(F7,1)=“2”,“二”,LEFT(F7,1)=“3”,“三”,LEFT(F7,1)=“4”,“四”,LEFT(F7,1)=“5”,“五”),“”)
班級:=IFERROR(IFS(MID(F7,3,1)=“1”,“一”,MID(F7,3,1)=“2”,“二”,MID(F7,3,1)=“3”,“三”,MID(F7,3,1)=“4”,“四”,MID(F7,3,1)=“5”,“五”),“”)
VLOOKUP函式:
按列查詢,最終返回該列所需查詢列序所對應的值;
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value
為需要在資料表第一列中進行查詢的數值。Lookup_value 可以為數值、引用或文字字串。當vlookup函式第一引數省略查詢值時,表示用0查詢。
Table_array
為需要在其中查詢資料的資料表。使用對區域或區域名稱的引用。
col_index_num
為table_array 中查詢資料的資料列序號。col_index_num 為 1 時,返回 table_array 第一列的數值,col_index_num 為 2 時,返回 table_array 第二列的數值,以此類推。如果 col_index_num 小於1,函式 VLOOKUP 返回錯誤值#VALUE!;如果 col_index_num 大於 table_array 的列數,函式 VLOOKUP 返回錯誤值#REF!。
Range_lookup
為一邏輯值,指明函式 VLOOKUP 查詢時是精確匹配,還是近似匹配。如果為FALSE或0,則返回精確匹配,如果找不到,則返回錯誤值 #N/A。如果 range_lookup 為TRUE或1,函式 VLOOKUP 將查詢近似匹配值,也就是說,如果找不到精確匹配值,則返回小於 lookup_value 的最大數值。如果range_lookup 省略,則預設為近似匹配。range_lookup 最好是明確指定,預設是模糊匹配!
IFERROR 函式:
如果公式的計算結果為錯誤,則返回您指定的值;否則將返回公式的結果。使用 IFERROR 函式來捕獲和處理公式中的錯誤。
IFERROR(value, value_if_error)
value
必需,檢查是否存在錯誤的引數。
value_if_error
必需,公式的計算結果為錯誤時要返回的值。計算得到的錯誤型別有:#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!。
LEFT
根據所指定的字元數,LEFT 返回文字字串中第一個字元或前幾個字元
LEFT
(
text
,num_chars)
Text
是包含要提取的字元的文字字串。
Num_chars
指定要由 LEFT 提取的字元的數量。(Num_chars 必須大於或等於零。如果 num_chars 大於文字長度,則 LEFT 返回全部文字。如果省略 num_chars,則假設其值為 1。)
年級獲取公式也可省略num-chars為:=IFERROR(IFS(LEFT(F7)=“1”,“一”,LEFT(F7)=“2”,“二”,LEFT(F7)=“3”,“三”,LEFT(F7)=“4”,“四”,LEFT(F7)=“5”,“五”),“”)。
4、自動獲取各科成績
以語文為例:
=IFERROR(VLOOKUP($F$7,$X$3:$AI$19,3,FALSE),“”)
5、自動獲取總分
=SUMPRODUCT((X3:X19=F7)*(Z3:AI19))
6、演示
7、求助:
如何在左側的排名中自動顯示該學號對應學生總分的班級排名?