XLOOKUP函數在高校學生數據管理中的應用研究

關鍵詞：Excel；Xlookup函數；學生數據管理

中圖分類號：TP311 文獻標識碼：A

文章編號：1009-3044（2024）28-0115-03

EXCEL是一款入門級的數據處理軟件，其中的LOOKUP、VLOOKUP、HLOOKUP、INDEX、FIND 等函數可以滿足不同數據庫組間的數據對接與信息共享[1]，大大降低了數據處理與管理的門檻，在財務管理[2]、人事管理[3]、學生管理[4]等領域中大量應用。但在實際應用中上述函數仍然不能覆蓋一些應用情景，為此微軟公司于2019 年推出了XLOOKUP 函數和FILTER函數，基本覆蓋了上述函數的應用情景，并擁有一些新的特性，能很好地滿足高校學生信息管理的有關需求。

1 Xlookup 函數介紹

1.1 Xlookup 函數語法規則

XLOOKUP函數是微軟在OFFICE 2019中開始推出的一款查找函數，該函數將INDEX 和 MATCH 函數的參數合并到一個函數中，能夠搜索數據區域中的值，并返回找到的匹配結果，可以說是現階段最強大的查找函數[5]。

XLOOKUP函數的語法規則為：

=XLOOKUP（lookup_value，lookup_array， return_ar?ray，[if_not_found]，[match_mode]， [search_mode]）。

其中，lookup_value表示查找值，lookup_array表示要搜索的數組或區域，return_array表示要返回的數組或區域。

如果找不到有效的匹配項，可以提供if_not_found 參數，表示返回什么文本；如果缺少if_not_found參數，則會返回#N/A。此外，還可以指定match_mode參數和search_mode參數，分別用于指定匹配類型和要使用的搜索模式。其中6個參數的具體說明如表1所示。

1.2 XLOOKUP 函數與VLOOKUP函數的差異

1） 查找方式。VLOOKUP函數只支持按列查找，而XLOOKUP函數支持按行和列進行查找，查找方式更加靈活。

2） 查找范圍。VLOOKUP函數只能在指定的查找區域中查找，而XLOOKUP函數的查找區域可以更加靈活，可以包含查找值和返回值的全部區域。

3） 返回值。VLOOKUP函數只能返回查找到的第一個值，而XLOOKUP函數可以返回查找到的所有匹配值。

4） 參數。VLOOKUP 函數的參數相對較少，而XLOOKUP函數的參數更多，可以更加細致地控制查找過程和結果。

5） 運行性能。VLOOKUP 函數的第二個參數[table_array]需要從查找列延到返回列，使得實際函數運行過程中會引用到更多的和最終結果無關的單元格，影響函數大規模運行時的性能。

綜上所述， XLOOKUP函數相對于VLOOKUP函數具有更多的優點和特性，可以更好地滿足不同場景下的查找需求。

2 Xlookup 函數的應用實例

為了使讀者直觀地理解XLOOKUP函數的優勢之處，現利用XLOOKUP函數對常見的高校學生管理數據進行操作。

2.1 普通查找返回單列數據

如根據學號在學生信息表中查找姓名。

第1步，在J2單元格中輸入“=XLOOKUP”，此時會彈出函數的6個參數。

第2步，填寫XLOOKUP函數必須的3個參數和按需填寫選填的參數。

1） lookup_value，選擇要查找的值，即I2單元格中的學號，輸入I2；

2） lookup_array，確定要查找的區域，即C列中的所有學號，輸入C：C；

3） return_array，確定返回的區域，即B列中的所有姓名，輸入B：B；

4） if_not_found，確定未查找到后返回的值，可輸入“無該生數據”，不輸入則返回 #N/A。此時J2單元格中的公式為“=XLOOKUP（I2，C：C，B：B，"無該生數據"）”。

第3步，填充J3、J4單元格，將光標移至J2單元格右下角，當光標變成實心十字時，雙擊即可完成填充，得到有關學生的姓名。本案例中，學號列位于姓名列之前，VLOOKUP函數針對此類情況必須要對原始數據的順序進行調整，而XLOOKUP函數更加靈活無需調整，同時也不需要像VLOOKUP函數一樣輸入列序號參數，查找匹配的方式更加直觀。此外，[if_not_found]參數的設置也使得公示不需要再嵌套IFNA、IFERROW函數，使得公式表達更為簡潔。

2.2 查找返回多列數據

如根據學號在學生信息表中查找班級、專業、所在年級。

第1步，在J2單元格中輸入“=XLOOKUP”，此時會彈出函數的6個參數。

第2步，填寫XLOOKUP函數的參數。

1） lookup_value，選擇要查找的值，即I2單元格中的學號，輸入I2；

2） lookup_array，確定要查找的區域，即C列中的所有學號，輸入C：C；

3） return_array，確定返回的區域，即D列中的班級、E列中的專業、F列中的所在年級，輸入D：F。

此時J2單元格中的公式為“=XLOOKUP（J2，C：C，D：F，"無該生數據"）”。

第3步，填充J3、J4單元格，將光標移至J2單元格右下角，當光標變成實心十字時，雙擊即可完成填充，得到有關學生的班級、專業、所在年級。本案例中，需要查找返回多列數據，VLOOKUP函數針對此類情況需要重復輸入多次函數，并使用絕對引用以避免查找列數據的變動，但XLOOKUP函數可以數組形式返回多列數據，實現多列數據的快速查找匹配。

2.3 多條件查找數據

2022級和2023級都有名為孫藝的學生，僅知道學生姓名和年級的情況下，XLOOKUP函數也可進行多條件復合查找該生學號。

第1步，在K2單元格中輸入“=XLOOKUP”，此時會彈出函數的6個參數。

第2步，填寫XLOOKUP函數必須的3個參數和按需填寫選填的參數。

1） lookup_value，選擇要查找的值，即I2單元格中的所在年級和J2單元格中的姓名，輸入I2&J2；

2） lookup_array，確定要查找的區域，即F列中的所在年級和B列中的姓名，輸入F：F&B：B；

3） return_array，確定返回的區域，即C列中的學號，輸入C： C。此時K2 單元格中的公式為“ =XLOOKUP（I2&J2，F：F&B：B，C：C）”。

第3步，填充K3單元格，將光標移至K2單元格右下角，當光標變成實心十字時，雙擊即可完成填充，得到有關學生的學號。這樣的多條件查找數據利用VLOOKUP函數實現時需要先對查找值和查找范圍的數據先進行處理后再進行匹配，使用XLOOKUP函數時則無須進行數據處理，直接在公式中處理即可。

2.4 橫向查找數據

如有些數據的字段名稱在一列中，需要根據學號在學生信息表中橫向查找姓名。

第1步，在J2單元格中輸入“=XLOOKUP”，此時會彈出函數的6個參數。

第2步，填寫XLOOKUP函數必須的3個參數和按需填寫選填的參數。

1） lookup_value，選擇要查找的值，即I2單元格中的學號，輸入I2；

2） lookup_array，確定要查找的區域，即單元格B3 到G3中的所有學號，輸入$B$3：$G$3；

3） return_array，確定返回的區域，即單元格B4到G4中中的所有姓名，輸入$B$4：$G$4。

第3步，填充J3、J4單元格，將光標移至J2單元格右下角，當光標變成實心十字時，雙擊即可完成填充，得到有關學生的姓名。VLOOKUP函數無法實現這樣的橫向查找，需要對數據轉置處理或使用HLOOKUP 函數。

3 討論

高校學生數據管理具有數據量大、更新次數頻繁、涉及條口多的特點，為此各地高校在學生管理實踐中都逐步開始了信息化建設，投入使用了學生基本數據平臺，但這些平臺很難滿足后續逐漸膨脹的各類數據需求，所以Excel依然是使用頻率相對頻繁的數據管理和分析軟件，熟練掌握以上4種匹配方法可應對絕大多數的數據管理需求。普通查找返回單列數據是最基本的匹配方法，可用于單列數據的查找匹配。查找返回多列數據是在普通查找返回單列數據的基礎上，將返回值擴展到相鄰的列，可用于多列數據的新增處理，但因為溢出數組無法直接調整，建議后續處理時建議去除公式保留值。多條件查找數據一般適用于缺乏唯一識別字段數據的查找匹配，此類數據往往需要結合多列數據才能生成唯一字段，但在應用時需要注意數據量的大小，數據量過大時該公式運行效率較慢，建議新建輔助列結合多列數據生成可供匹配的唯一字段。橫向查找數據適用于從原始數據中提取需要的列，只要提供表頭即可從原始數據中獲取全部所需數據，對于多組表頭順序不一致的原始數據清洗有很好的效果。此外，還有一些注意事項：

第一，XLOOKUP函數同EXCEL所有函數一樣，都需要將輸入法切換到半角模式，否則會無法識別提示錯誤；第二，當需要引用大量數據時，可使用“Shift+Ctrl+方向鍵”實現對單列、單行非空數據的快速選中引用；第三，雙擊填充單元格時需要保證該列范圍內的單元格均為空白單元格，否則只會填充到第一個非空單元格為止；第四，對于需要定期校核的數據，可通過“&”將多列待校核數據合并為一列數據，這樣只需要執行一次匹配就可以執行多列數據的同時校核；第五，數據量較大時進行批量應用公式前建議做好保存工作，通過小批量運行測試公式性能，避免宕機造成數據丟失；第六，數據處理完成后建議按需將數值型數據保存為文本型數據，這樣可以避免諸如身份證號、手機號等數據信息丟失，也有助于導入到其他數據庫軟件中；第七，XLOOKUP 函數由微軟公司在EXCEL2019起加入，因此在低版本EXCEL中無法被正常識別，建議在最終結果中刪除所有公式保留值以確保數據跨設備傳遞中顯示正常。

4 結束語

以VLOOKUP函數為代表的傳統查找匹配函數要求首列/首行必須為查找依據列，且無法進行簡單的多條件匹配，在實際使用中對基礎數據有一定要求。XLOOKUP函數解決了上述問題，給出了更為簡潔有力的解決方案，有效地降低了工作量，提高了高校學生信息管理的準確性。盡管這些需求同樣通過SQL、ACCESS等專業數據庫軟件，或者通過嵌套函數實現，但XLOOKUP函數優化了函數表達，使得查找匹配函數的使用門檻進一步降低，為高校學生數據管理提供更加方便使用的工具。