Excel函数：VLOOKUP() – 统计学原理

函数介绍

Vlookup()函数用于在表格或区域中查找数据，并返回指定列的值。

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

具体的参数如下：

第一个参数lookup_value：要查找的值。
第二个参数table_array：要查找的区域。
第三个参数col_index_num：要返回的值的列号。
第四个参数range_lookup：是否精确匹配。

操作实训

假设有一个学生成绩表，表格数据如下（Excel工作表Sheet1，单元格范围A1:I11）：

学生成绩表{#tbl-student-score .striped .hover}
学号	姓名	寝室	性别	高数	英语	体育	合计	等级
20010506	赵一	302	女	98	87	85	270	?
20010507	王二	201	男	76	85	80	241	?
20010508	张三	202	男	65	56	85	206	?
20010509	李四	202	男	75	60	65	200	?
20010510	郑五	301	女	72	58	70	200	?
20010511	朱六	302	女	85	79	76	240	?
20010512	陈七	201	男	95	90	83	268	?
20010513	胡八	202	男	83	80	82	245	?
20010514	周九	301	女	48	61	72	280	?
20010515	肖十	202	男	67	76	73	216	?

Excel操作任务如下：

（1）利用函数计算合计成绩和平均成绩。
（2）请用“条件格式”命令找出不及格的同学，将其学号用红色标出，并将其相应成绩用红色标记。
（3）将表格按合计成绩（降序）+高数成绩（升序）排列。
（4）请用VLOOKUP函数确定每位同学总成绩的等级。其中：<200 = 差；200~230 =中；230~260= 良；>260 =优

Excel示例

请用VLOOKUP函数确定每位同学总成绩的等级（Excel工作表Sheet1，单元格范围I2:I11）。

步骤1：确定等级划分标准（Excel工作表Sheet1，单元格范围L1:M5）：

等级划分标准{#tbl-grade-standard .striped .hover}
区隔	等级
0	差
200	中
230	良
260	优

步骤2：使用VLOOKUP函数确定每位同学总成绩的等级（Excel工作表Sheet1，单元格范围I2:I11）：

# 计算1：使用VLOOKUP函数确定每位同学总成绩的等级（Excel工作表`Sheet1`，单元格范围`I2:I11`）。

step 1 : 选中目标单元格区域（例如I2:I11）。
step 2 : 在公式输入窗口输入公式：`=VLOOKUP(I2:I11, $L$1:$M$5, 2, TRUE)`。
step 3 : 按`Ctrl+Shift+Enter`，即可得到每位同学总成绩的等级。

`=VLOOKUP(I2:I11, $L$1:$M$5, 2, TRUE)`的具体参数解读如下：
# 参数1：要查找的值(I2:I11)，即每位同学的总成绩
# 参数2：要查找的区域($L$1:$M$5)，即等级划分标准
# 参数3：要返回的值的列号(2)，即等级
# 参数4：是否精确匹配(TRUE)，即TRUE表示近似匹配，FALSE表示精确匹配

最终结果如下（Excel工作表Sheet1，单元格范围I2:I11）：

最终结果{#tbl-final-result .striped .hover}
学号	姓名	寝室	性别	高数	英语	体育	合计	等级
20010506	赵一	302	女	98	87	85	270	优
20010507	王二	201	男	76	85	80	241	良
20010508	张三	202	男	65	56	85	206	中
20010509	李四	202	男	75	60	65	200	中
20010510	郑五	301	女	72	58	70	200	中
20010511	朱六	302	女	85	79	76	240	良
20010512	陈七	201	男	95	90	83	268	优
20010513	胡八	202	男	83	80	82	245	良
20010514	周九	301	女	48	61	72	280	优
20010515	肖十	202	男	67	76	73	216	中

技术总结

Excel小技巧

（1）多个单元格批量计算公式组合键盘快捷键：Ctrl+Shift+Enter，可以按住Ctrl键，然后按住Shift键，然后按Enter键，即可一次性输入多个单元格的公式。

（2）IFERROR()函数嵌套组合VLOOKUP()函数：用于处理公式计算错误。

# 函数定义：
=IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), error_value)
# 函数示例：
=IFERROR(VLOOKUP(I2:I11, $L$1:$M$5, 2, TRUE), "无等级信息")

具体参数解读如下：

参数1：要查找的值(A1)，即要查找的值。
参数2：要查找的区域(B1:C10)，即要查找的区域。
参数3：要返回的值的列号(2)，即要返回的值的列号。
参数4：是否精确匹配(FALSE)，即是否精确匹配。
参数5：如果公式计算错误，则返回的值(“Not Found”)，即如果公式计算错误，则返回的值。