Excel菜单功能及函数在表格处理中的应用探索

王维利

摘要：Excel在教学管理、账务管理等工作的表格数据处理中应用广泛，巧妙使用Excel的函数、功能，如VLOOKUP、RANK等函数及排序、分类汇总、数据透视表等功能，并根据处理要求使用适当的运算符及自定义表达式，会提高表格数据处理的效率和准确性。

关键词：Excel;函数;公式;分类汇总;名次;VLOOKUP;RANK;拆分工作簿

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

文章编号：1009-3044（2020）23-0079-03

在日常办公事务中，经常需要使用Excel对含有大量数据的表格进行处理，如果主要依靠肉眼判断、对比和人工编辑，极易出现处理错漏，并且效率极低。作者总结在近几年工作中解决Excel数据处理方面部分问题的一些有效做法，并以实例说明，以期与广大读者共享。

1从相关表中提取匹配数据

在数据处理中，有时需从几个相关表中提取某列值匹配的数据行中的内容，合并生成一个新表。此问题可通过合理使用VLOOKUP函数、文本连接运算符&、分列功能解决。

VLOOKUP函数的语法格式为：VLOOKUP （待匹配值，包含返回值和匹配值的关联数据区域，返回值在关联区域中的列序号，匹配方式 1/TRUE-近似或0/FALSE-精确）。

例如，学生成绩表中只有学号、课程代码、成绩等数据，而公布成绩时，需要将学生的姓名、班级、院系及课程名称、学分等数据跟各科成绩放在同一表格中，学生的详细信息存在于学籍表中，而课程信息在课程信息表中，此时就需要从这两个表中找到匹配行，提取出所需列的值放入学生成绩表中。下面分两种情况说明提取数据的操作方法。

1.1提取匹配的一列数据

这种情况可直接使用VLOOKUP函数提取所需数据填入指定位置。

例如，现需将学生姓名添加到学生成绩表中，先在成绩表中的学号、成绩两列之间插入新列——姓名，使表中A、B、C列分别为学号、姓名、成绩，然后在单元格B2中输入公式=VLOOKUP（A2，学籍表！$A$2：$B$8，2，0），即从学籍表（A、B列分别为学号、姓名）的$A$2：$B$8区域中精确查找学生成绩表中A2单元格中的学号值，并从关联区域中学号值匹配的数据行第2列中取值，数据放入公式所在单元格中。然后将此公式复制到学生成绩表的姓名列中需要填入数据的其他单元格，即可达到要求。

操作中，请注意引用关联数据区域时须使用绝对引用，以防止将含有关联数据区域相对引用的公式复制到其他单元格，导致在错误的数据区域中进行匹配而取出不合理的数据值。

1.2提取多列数据

这种情况既可使用多次提取单列数据的方式完成，也可设法一次性提取多列数据，并进行必要的处理实现。第二种方法的操作过程是：先在要从中提取数据的源表中，将需要提取的多列数据使用特定分隔符（如*）进行连接并把结果放入一新列中，并按照提取单列数据的方法将所需数据放入目标表，再用数据分列功能将放入的数据拆分成所需的多列。

例如，现要将学生的姓名、班级、学院三列的信息加入成绩表中，可按如下步骤操作：

1）在学籍表（B、C、E列依次为姓名、班级、学院）中将这三列数据连接后放在一个新列G（列标题为“姓名班级学院”）中。在单元格G2中输入公式=B2&"*"&C2&"*"&E2，确定后用复制公式的方法把其他各行中的姓名、班级、学院信息也连接后放到此列中;

2）使用VLOOKUP函数提取学籍表中匹配行的G列数据放入学生成绩表的相应列;

3）使用数据分列功能将学生成绩表中“姓名班级学院”列拆分成三列。先将此列中的数据以“粘贴值”的方式复制到新列中，再用“数据-分列”功能拆分列，注意要使用*号作为分隔符号进行分列。

2以多列数据作为关键字进行分类汇总

这个任务可通过如下操作实现：使用字符串连接运算符&连接需要作为分类关键字的多列数据，作为新列放到数据表中，然后依据此列进行所需的数据分类汇总。

例如，在学生评教表中有每个学生對所有任课教师的评分，且存在同一教师给多个班级上同一门课的情况，若需分别统计各班学生对各教师所任教课程的评价平均分，则需以班级、课程、教师为关键字进行分类汇总。具体操作如下：

1）将各行中班级、课程代码、教师工号的值连接后放于表格的新列——班级课程教师;

2）以“班级课程教师”列为分类字段，对“评价分”字段进行平均值汇总;

3）将汇总数据复制到新工作表中，以便进行其他处理。方法是：隐藏明细数据，只显示汇总数据，选择所需的汇总数据，将工作表中的定位条件设置为“可见单元格”，用复制、粘贴的方法将汇总数据复制到新工作表中。

如果在汇总数据表中需要对原通过连接操作生成的分类字段进行还原，则须在对分类依据的多个字段值进行连接时放入特定的分隔符号，那么此时只需按照指定分隔符进行数据分列即可。

3排名问题

排名问题，即按照表格中某一列或几列数据的升序（或降序）给出各数据行的位次，如班级将所有学生按成绩排定名次、生产企业按月产量对各个车间进行排名等，需要在名次列中填入对应的名次。这类问题可使用函数和公式两种办法来解决，下面分别进行说明。

3.1使用RANK函数实现

RANK函数的语法格式为：RANK（待排位的值，排位的数据区域，排位方式），其中排位方式指定要进行升序排位还是降序排位，0-按降序排位（默认），不等于0的值-按升序排位。

例如，要对如图1所示的成绩表按总成绩进行排名。

操作方法：先在第一个数据行的“名次”列单元格中输入公式：=RANK（B2，$B$2：$B$10，0），确定后再将公式复制到其他需要填入排名的单元格中。排名数据区域一般要用绝对引用方式，以免公式复制到其他单元格后得到错误排名，因为所有行的排名都是相对于同一组数据值而言的。

3.2使用自定义表达式实现

使用自定义表达式可按如下过程操作：

1）对要排名数据按排名规则进行排序，再在第1行数据的“名次”单元格中输入1;

2）在第2行数据“名次”格中输入公式：=IF（B3=B2，K2，K2+1），确定后即可填入对应的名次;

3）将上述公式复制到“名次”列中其他需填入名次值的单元格中。

使用这种方法与直接使用RANK函数得到的排名一般是相同的，但有一种情况例外：当参与排名的数据有相同值时，如[5，3，3，2]这组数据，数据值2用表达式进行排名的结果是3，而用RANK函数操作的结果是4。

3.3依据多列数据进行排名

在工作中，有时会遇到需要按照多列数据排名的问题，如成绩表中的排名规则是：优先按总分排名，若总分相同，则按英语排名，若英语也相同再按电子电路进行排名。这种情况，难以直接使用RANK函数实现，而使用自定义表达式就比较方便。操作过程与上述使用表达式按单列数据排名相同，但有两点要注意：

1）将数据表按排名规则排序时，需按多列进行排序，注意关键字列的先后顺序;

2）在第2行的“名次”单元格中输入的公式需修改如下：

操作完成后的结果如图2所示。

4识别关键字列值相同的行

如需识别某列数据值相同的行，可先按关键字列排序，再合理使用表达式检测。

例如，为各班级排完课表后，得到如下安排表，现需判断哪些班级是合班上课。已知判断不同行政班合班上课的规则是：周次、星期、节次、课程、教师值均相同。此时可按如下方法进行操作：

1）按上述关键字段所在列进行排序，使这些字段值相同的数据行相邻;

2）在H3单元格输入公式：=AND（C3=C2，D3=D2，E3=E2，F3=F2，G3=G2），确定后将此公式复制到H列后面的单元格，即可在上述关键字值相同的一组数据行中除第一行外显示TRUE;

3）在I2单元格输入公式：=H3，确定后将公式复制到I列其余单元格，即可将关键字值相等的多行中的第1行也标记为TRUE;

4）在J2单元格中输入公式：=IF（OR（I2，J2），"合班"，"不合班"），确定后将公式复制到J列其他单元格，即可正确标识出各班课程安排是否合班的情况。结果如图3所示。

上述方法也可用来检测课表安排中班级时间冲突（按班级、周次、星期、节次排序后使用公式操作，但使用上述公式时需适当修改）、教师时间冲突（按教师、周次、星期、节次排序后使用公式操作）等情况。

5将数据表拆分成多个独立工作簿

拆分工作表为独立工作簿的步骤如下：1）按拆分依据列排序;2）将数据拆分成多个工作表;3）将工作簿中的各个工作表拆分成独立的工作簿。

例如，有教师评价班级数据表，含有部门、班级、平均评价分、参评教师数4列，需将表中数据按部门拆分成独立的工作簿，可按如下方法进行操作。

5.1将表中数据按部门列进行排序

使用排序功能操作。

5.2利用插入数据透视表实现按部门拆分成多个工作表

1）插入-数据透视表，将数据透视表设置为插入到当前工作表中。

2）在“数据透视表字段”框中合理分布各字段：以部门为筛选字段，班级为行字段，平均评价分、参评教师数为值字段，值字段的汇总方式为“平均值”（也可适当使用其他方式）。

3）在“数据透视表工具-设计”页面中设置如下：分类汇总-不显示;总计-对行和列禁用;报表布局-以表格形式显示。

4）在“数据透视表工具-选项”页面中设置如下：在“数据透视表”区域中，选择“选项-显示报表筛选页”，并设置报表筛选页字段为“部门”。

5.3将各个工作表拆分成独立工作簿

1）右键单击工作表标签，选择“查看代码”，在出现的代码框中将如下代码复制进去。

Private Sub 分拆工作表（）

Dim sht As Worksheet

Dim MyBookAs Workbook

Set MyBook = ActiveWorkbook

For Each sht In MyBook.Sheets

sht.Copy

ActiveWorkbook.SaveAs Filename：=MyBook.Path& "＼" &sht.Name， FileFormat：=xlNormal     '將工作簿另存为EXCEL默认格式

ActiveWorkbook.Close

Next

MsgBox "文件已经被分拆完毕！"

End Sub

2）运行代码，方法是使用“运行-运行子过程/用户窗体”菜单项或按F5功能键，即可将工作表中的数据按部门拆分到独立的工作簿中。

6结束语

本文针对Excel表格处理中的部分问题，结合实际工作经验，对关联数据提取、数据排名、分类汇总、工作表拆分成工作簿等问题的操作方法进行了探索、整理，以期对今后的数据处理工作有所启发和帮助。

参考文献：

[1] 江红， 余青松. Excel数据处理与分析教程[M]. 北京：清华大学出版社， 2015.

[2] 李博.Excel 数据匹配研究[J].现代信息科技， 2010， 3（1）： 13-15.

[3] 潘皎. 浅析EXCEL函数在学生信息管理中的应用[J]. 黑龙江教育学院学报，2019，38（10）：148-150.

[4] 吴莹. Excel中VLOOKUP和MATCH函数的应用[J]. 电脑知识与技术， 2019，15（2）：276-277.

【通联编辑：王力】