全国计算机一级等级考试中Excel函数的重难点分析

摘要：随着信息时代的展开和计算机的遍布，Excel在各行各业中的使用越来越普遍。Excel以超強处理数据能力和丰富的绘图表能力，受到各行业人员的重视和青睐，掌握Excel，可以让你的工作事半功倍。因此数据处理成了中等职业学校学生必备的一项技能。计算机一级等级证书更是纳入广东省职业院校高考证书，其中Excel电子表格操作题在计算机一级等级考试中所占分值20分，是所有操作题中最难的。所以想要考证结果优秀，掌握该部分内容至关重要。该部分主要考查函数和公式的应用，条件格式，表格样式等，其中函数是重点。

关键词：计算机等级考试;一级MS office;Excel函数和公式

中图分类号：TP317      文献标识码：A

文章编号：1009-3044（2022）07-0128-02

1 2021年全国计算机一级考试分析

在2020年12月发布的考试时间及相关安排中，发布了考试环境更新的通知，一级计算机基础的应用软件升级到MS office版（中文专业版）。

2021年3月27日，全国计算机等级考试如期举行，某中职学校信息技术部高一学生参加了本次考试。信息技术部20级共4个班，学生人数共计186人，无人缺考。其中175人顺利通过本次考试，通过率为94%，保持高水平，36人考试成绩达到90分以上，优秀率达19%。

1.1 考试难度分析

根据考试组织方公布数据显示：本次考试得分率最低的模块是：Excel电子模块。新环境下，电子表格模块新增了多个条件的平均值和求和函数，多套题目涉及多重IF函数和VLOOKUP函数，很多学生无从下手，导致后面的图表和筛选等操作都没法继续做。有68%的学生电子表格部分得分为0，电子表格模块难度总体上升。

1.2 提高考试优秀率分析

考证的另外两个模块，Word和PPT虽然题量大，知识点多，但是只要熟悉软件界面便可得分。Excel却要求学生必须理解透彻函数，是一个学以致用的过程。同时题目常分两部分，第二部分的数据处理必须在第一部分完成基础上完成，所以第一部分的函数至关重要。Excel虽然难度大，但是反而容易得分，因为知识点相对集中分值大。所以想要等级考试结果优秀，学习好Excel函数是关键。

新题库新内容，为了提高考证优秀率，本文将对考证中的操作题Excel中常见函数进行详细介绍。

2 Excel重点函数分析

2.1 多重IF函数

通过分析题库内容，得知条件格式和多重IF函数作为考点出现的频率最高，多重IF函数最多的时候涉及5个条件，所以难度较大，需要加强训练，除了题库中涉及的6道题，还需要另外添加练习题目，帮助学生理解多重IF的解题思路，同时建议将题库中涉及的6道多重IF题目做成微课，时长10分钟，让学生有针对性地巩固学习。学生只有多练习，才能掌握本类型题目的解题技巧，才能从容作答。下面以无忧模拟题库中第10套题为例，对本函数进行讲解。

本题中绩效评分有5个等级，需要用4重IF函数，即4个IF函数[1]。跟剥洋葱的方法相似，先从最小值开始判断，在F3单元格写入=IF（C3>=60，"2000"，"800"），第一个IF函数用于判断绩效评分是否大于等于60，其中第一个参数是判断条件，第二个参数是条件正确返回的值，即2000，第三个参数是条件不正确返回的值，即800。第二个IF函数用于判断绩效评分是否大于等于70，第一个参数是c3>=70，第二个参数是当表达式正确时返回值，即4000，复制第一个IF函数作为第三个参数，即c3<70时的返回值。即在F3单元格写入=IF（C3>=70，4000，IF（C3>=60，2000，800））。第三个IF函数用于判断绩效评分是否大于等于80，当表达式正确时返回6000，作为第二个参数，第三个参数是绩效评分小于80，即第二个IF函数。用同样的方法推导第三个IF函数用于判断绩效评分是否大于等于90，F3单元格最终结果是=IF（C3>=90，8000，IF（C3>=80，6000，IF（C3>=70，4000，IF（C3>=60，2000，800））））。通过上面的例子可知，多重IF函数的关键是第一个IF函数，通常从最小值开始比较，每一重IF函数的参数都是由上一重函数组成。

2.2 条件函数

另外一个出现频率较高的函数是sumif、countif和averageif[2]。这三个是带条件的求和、计数、求平均可放在一起对比学习，前提是掌握sum、count和average这三个基础函数。在基础函数之上添加条件，条件在第2个参数，对应是填写Criteria选项。在新题库中添加countifs和averageifs两个函数，是countif和averageif的进阶要求，条件从原来的一个变成了多个，一个原则是条件对应在Criteria选项，条件所在列在本条件的上面选项即Criteria_Range，需要用功能键F4固定。条件和条件所在列都是成对呈现。Sum_range和average_range是对数据求和或求平均所在的列，对应一列数据。

如图1的K3单元格，求一组，二组和三组人数，可用countif函数[3]。光标放在k3单元格，在countif函数的第一个参数写$B$2：$B$100，第二个参数是条件，即J3，其中第一个参数是条件所在的“组别”列，即B列。技巧是条件所在列在条件上方。且条件所在列需要用功能键F4固定。

如图1的L3单元格，求各组奖金平均值，可用averageif函数。光标放在L3单元格，在averageif函数的第一个参数range写$B$2：$B$100，即条件所在的组别列;第二个参数Criteria是条件，即J3;第三个参数Average_range填$F$2：$F$100，即奖金列，同样需要用功能键F4固定。

如图1的K9单元格，求各组工资等级A的人数，条件有2个，分别为小组和等级，可用countifs函數[4]。光标放在K9单元格，在countifs函数的第一个参数Criteria_range1写$B$2：$B$100;第二个参数Criteria1是条件1，即J9;第三个参数Criteria_range2是条件2所在列工资等级列，即$H$2：$H$100;第四个参数Criteria2，是条件2，即“A”。

通过上面的countif、averageif和countifs三个函数的对比，可以得出条件所在列在条件上方，掌握这三个函数的关键是把条件找出来，问题就迎刃而解。因此可知，averageifs和countifs函数一样，只是averageifs多了第5个参数，求数据的平均值。

2.3 VLOOKUP函数

另外一个重点考查函数是VLOOKUP[5]，10道模拟题中出现5次，并且这个函数比较难理解，分为精确查找和模糊查找，因此在本文将重点介绍该函数。VLOOKUP是查找选用函数，主要是在某个区域查找某个值，并把该值所在行中某个数据返回。此函数有四个参数：lookup_value是查找值，是一个数;table_array是查找区域，至少包含两列数据，需要用功能键F4固定;第三个Col_index_num是引用列，是一个数，即引用列在查找区域第N列;Range_lookup是0或者1，在计算机一级等级考试中只考查精确查找，所以是填1。本函数重点是把握第二个参数，是查找区域，至少包含查找列和引用列。因为所有的查找值都是在该查找区域的第一列查找，所以查找列在该查找区域中的第一列且需要用功能键F4进行固定。下面以无忧模拟题库中第8套题为例，对本函数进行讲解[5]。

如图2，根据“产品单品价格表”填写“某公司员工销售情况表”中的“产品单价”列。通过分析得知，本题是通过产品名称找产品单价，因此查找值为“某公司员工销售情况表”中B3单元格，即第一参数为B3;第二个参数为查找范围：$G$3：$H$7，即在“产品单品价格表”中的第一列“产品名称”列找查找值，找到了返回对应的产品单价，因此第二个参数需要包括产品名称和产品单价两列;第三个参数是返回列在查找区域第N列，在本实例中产品单价在查找区域$G$3：$H$7是第二列，因此第三个参数填写2;第4个参数是1，精确查找，只有当“某公司员工销售情况表”的产品名称和“产品单品价格表”中的产品名称一样，才能把单价返回“某公司员工销售情况表”C列的产品单价。

VLOOKUP有在原来表查找和选用，还有跨表选用。本实例是原表查找引用。跨表应用只需要修改第二个参数，在查找区域前添加“工作表名！”。

VLOOKUP函数经常会出现错误值N/A，N/A意思是 Not Applicable（不适用，即值不可用），下面提供几种导致N/A错误和对应的解决方法[6]。

1） 查找值在查找区域不存在

当查找区域中没有查找值，即找不到查找值，就会提示N/A错误。

2） 查找区域引用有误

按照VLOOKUP的查找规则，虽然查找区域内容可以多列，但是查找值只会在查找区域的第一列进行查找，所以当查找区域的首列没有查找值，也会出现N/A错误[1]。

3） 查找区域没有使用绝对引用

根据VLOOKUP的查找规则，查找区域必须是固定区域，需要用功能键F4固定，即使用绝对引用。没有固定会导致后面的数据在不完整的查找区域内容查询，最终也会导致N/A错误。

4） 查找值有空格，或是两者单元格格式不一样

VLOOKUP要求查找值跟查找区域内的数据必须一模一样，不能有空格，更不能两者格式不一样，否则表面看起来很相似，但是还是会出现N/A错误[7]。

当查找值和查找区域都是规范的，但是因为查找区域中没有查找值，出现了N/A错误，页面显得不美观，可以用IFNA（）来解决，尝试将错误值#N/A改为空白。IFNA函数作用是如果表达式解析为N/A，则返回指定的值，否则返回表达式的结果。

第一个参数是表达式，第二个参数是当第一个参数表达式的结果是N/A，则返回指定的值，这里可以设置为空白，即一对英文双引号里面是一个空格。

3 结束语

总之，随着信息技术的不断更新发展，对学生的信息技术素养要求越来越高。计算机一级考证考查范围越来越广，难度也在逐步上升。要求授课老师不断丰富教学手段，利用无忧模拟考证软件精准辅导，提高学生考试通过率和优秀率。

参考文献：

[1] 郑忠秀.浅谈“全国计算机一级等级考试”中Excel函数的应用技巧[J].饮食科学，2017（24）：176.

[2] 李慧毅.浅析全国计算机等级考试（一级MS Office）中的函数应用[J].现代职业教育，2020（38）：200-201.

[3] 吴巧平.“项目学习”在昆明实验中学初中信息技术教学中的实证研究——以《初中数据统计》为例[D].昆明：云南师范大学，2013.

[4] 刘中旭.浅谈全国计算机等级考试一级计算机基础及MS Office中常见函数的应用[J].商情，2017（33）：169.

[5] 余溢多.全国计算机等级考试一级MS Office上机学习指导[J].计算机光盘软件与应用，2013，16（2）：133-134.

[6] 龙锦萍，邵菲.浅析如何提高全国计算机等级考试一级MS Office的过关率[J].好家长，2017（54）：225.

[7] 殷文俊.浅谈Excel中的几个常用函数——以安徽考区计算机一级考试为例[J].考试周刊，2016（A5）：6-7.

【通联编辑：谢媛媛】

收稿日期：2021-11-28

作者简介：陈少燕（1989—），女，广东潮州人，中学一级，本科，研究方向为计算机应用。