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函数优化了函数表达，使得查找匹配函数的使用门槛进一步降低，为高校学生数据管理提供更加方便使用的工具。