WPS考试Excel综合题通关：SUMIFS、VLOOKUP、MID、TEXT函数实战拆解

最近在准备计算机二级WPS考试的同学，尤其是刷到题库第2套的同学，大概率会被Excel部分的第8题“卡”一下。这道题往往综合了多个核心函数和数据处理技巧，比如SUMIFS、VLOOKUP、MID、TEXT等，题目描述可能有些绕，数据源也略显复杂，导致很多朋友知道要用某个函数，但就是写不对公式，或者结果总差那么一点。

别担心，这篇文章就是为你准备的“通关秘籍”。我将以“WPS考试题库第2套Excel第8题”为蓝本，为你彻底拆解这类综合题的解题思路。无论你是正在备考的考生，还是想系统提升WPS表格实战能力的办公族，都能从中学到一套清晰的解题方法论。本文不仅会给出题目的分步操作详解，更会深入讲解每个函数背后的逻辑、参数设置技巧以及常见的“坑点”，确保你下次遇到同类问题能举一反三，独立解决。

1. 题目背景与核心需求分析

在动手操作之前，我们必须先读懂题目。通常，这类综合题会提供一个包含多列数据的表格（例如员工信息、销售记录、成绩单等），并要求你根据特定条件进行计算、查找或数据重组。

典型题目场景还原：假设我们有一个名为“员工绩效表”的数据源，包含以下列：员工ID、姓名、部门、入职日期、基本工资、绩效系数、项目奖金等。题目可能要求你：

1. 多条件求和：计算“销售部”且“绩效系数大于1”的员工“基本工资”总和。
2. 条件查找与计算：根据提供的“员工ID”，查找其对应的“姓名”和“部门”，并计算其“总薪资”（基本工资*绩效系数+项目奖金）。
3. 数据提取与格式化：从“员工ID”（格式如DEP00120230101，前3位部门代码，后8位入职日期）中，分别提取出“部门代码”和“入职年份”，并将入职年份格式化为“XXXX年”的形式。
4. 结果汇总与判断：将上述所有计算结果汇总到一个新的“统计结果”区域，并可能要求使用IF函数对总薪资进行等级评定（如“优秀”、“合格”、“待改进”）。

核心考察点：这道题本质上是在考察你对以下几个知识点的综合应用能力：

• SUMIFS/COUNTIFS：多条件求和与计数，这是Excel/WPS数据分析的基石。
• VLOOKUP/XLOOKUP(如果WPS版本支持)：精确查找并返回关联数据。
• 文本函数 (MID,LEFT,RIGHT,TEXT)：从字符串中提取特定部分或进行格式转换。
• 逻辑函数 (IF,AND,OR)：进行条件判断。
• 简单算术运算与单元格引用：公式的基础。

理解题目要求是成功的第一步。请务必花一两分钟，在WPS表格中定位好数据源区域和需要填写结果的目标区域，并用自己的话复述一遍题目要求。

2. 解题环境与准备工作

工欲善其事，必先利其器。在开始解题前，请确保你的操作环境已就绪。

2.1 软件与版本

• 软件：金山WPS Office 表格组件。个人版、教育版或专业版均可，功能上对于此类题目没有差异。
• 版本：建议使用较新的稳定版本（如WPS 2019或更新版本），以确保函数功能完整。你可以在WPS表格中点击左上角“文件”->“帮助”->“关于WPS表格”查看版本信息。
• 重要提示：请务必使用官方正版WPS软件。网络上流传的所谓“破解版”或“免费永久使用”版本不仅存在安全风险（可能携带病毒或恶意软件），其稳定性也无法保障，在考试或重要工作中可能导致文件损坏或功能异常，切勿使用。

2.2 文件与数据准备

• 打开题库提供的“第2套”Excel文件，找到对应的“Excel”工作表。
• 识别数据区域：通常，原始数据会集中在一个区域，例如从A列到G列，第1行是标题行。请确认你的数据范围，假设数据位于A1:G100。
• 定位答题区域：题目会明确指示将结果填写在何处，例如在I列、J列或某个指定的“统计区域”。找到这些空白单元格。
• 备份习惯：在开始复杂的公式操作前，可以先将原始文件“另存为”一份副本，例如命名为“第2套Excel_练习备份.xlsx”，以防操作失误。

2.3 核心概念回顾：绝对引用与相对引用在编写涉及多个条件的公式时，引用方式至关重要。

• 相对引用 (如 A1)：当公式被复制到其他单元格时，引用的地址会相对变化。例如，在B2输入=A1，复制到C3会变成=B2。
• 绝对引用 (如 $A$1)：无论公式复制到哪里，引用的地址固定不变。按F4键可以快速切换引用类型。
• 混合引用 (如 $A1 或 A$1)：锁定行或锁定列。
• 应用场景：在SUMIFS的条件范围参数中，通常使用绝对引用来锁定整个条件区域，防止复制公式时区域错位。而在求和区域，根据题目要求可能是相对或绝对引用。

3. 核心函数深度拆解与解题思路

我们将题目拆解成几个子任务，并逐个击破。请对照你的题目要求，理解每个函数的用法。

3.1 任务一：多条件求和 —— SUMIFS函数

场景：计算“销售部”（部门）且“绩效系数>1”的员工“基本工资”总和。

函数语法：=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• sum_range：要求和的实际数值区域，例如“基本工资”列。
• criteria_range1：第一个条件所在的区域，例如“部门”列。
• criteria1：第一个条件，例如"销售部"。
• [criteria_range2, criteria2]：可选，第二个条件区域和条件，以此类推。

解题步骤与公式示例：假设数据如下：

• 部门列在C2:C100
• 绩效系数列在F2:F100
• 基本工资列在E2:E100
• 结果需要放在I2单元格。

1. 公式构建：

   =SUMIFS($E$2:$E$100, $C$2:$C$100, "销售部", $F$2:$F$100, ">1")

   • $E$2:$E$100：对“基本工资”列进行求和（绝对引用，锁定区域）。
   • $C$2:$C$100：第一个条件区域是“部门”列。
   • "销售部"：条件为文本，必须用英文双引号括起来。
   • $F$2:$F$100：第二个条件区域是“绩效系数”列。
   • ">1"：条件为数值比较，同样需要用双引号括起来。

2. 关键点：

   • 区域大小必须一致：所有criteria_range必须和sum_range具有相同的行数。
   • 条件书写：文本条件直接写，数值比较条件如">1"、"<=100"要加引号。如果是引用单元格条件，如">"&J1（J1单元格存放数值1），则用&连接符。
   • 绝对引用：这里使用了$锁定区域，是为了防止公式在其他位置被误用。如果题目要求向下填充公式计算其他部门，则需要调整引用方式。

3.2 任务二：条件查找与计算 —— VLOOKUP与算术运算

场景：根据“员工ID”（假设在L2单元格）查找“姓名”和“部门”，并计算“总薪资”。

函数语法 (VLOOKUP)：=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value：要查找的值，例如某个员工ID。
• table_array：查找的表格区域，必须包含查找列和返回列。
• col_index_num：返回数据在table_array中的列序号（从1开始计数）。
• [range_lookup]：通常填FALSE或0，表示精确匹配。

解题步骤：

1. 查找姓名（假设姓名在B列，员工ID在A列）：

   // 在 M2 单元格输入公式 =VLOOKUP($L$2, $A$2:$G$100, 2, FALSE)

   • 在$A$2:$G$100这个区域中，查找L2的值。
   • 找到后，返回这个区域中第2列（即B列，姓名）的数据。

2. 查找部门（部门在C列，是第3列）：

   // 在 N2 单元格输入公式 =VLOOKUP($L$2, $A$2:$G$100, 3, FALSE)

3. 计算总薪资（假设基本工资在E列，绩效系数在F列，项目奖金在G列）：

   // 在 O2 单元格输入公式 =VLOOKUP($L$2, $A$2:$G$100, 5, FALSE) * VLOOKUP($L$2, $A$2:$G$100, 6, FALSE) + VLOOKUP($L$2, $A$2:$G$100, 7, FALSE)

   • 这个公式嵌套了三个VLOOKUP，分别取出基本工资、绩效系数和项目奖金再进行计算。
   • 更优做法：可以先在相邻单元格用VLOOKUP分别取出这三个值，再引用单元格进行计算，这样公式更清晰易读。

   // 假设 P2=基本工资， Q2=绩效系数， R2=项目奖金 // P2公式：=VLOOKUP($L$2, $A$2:$G$100, 5, FALSE) // Q2公式：=VLOOKUP($L$2, $A$2:$G$100, 6, FALSE) // R2公式：=VLOOKUP($L$2, $A$2:$G$100, 7, FALSE) // 然后在 O2 计算：=P2*Q2+R2

3.3 任务三：数据提取与格式化 —— MID, TEXT函数

场景：从“员工ID”（如DEP00120230101）中提取“部门代码”（前3位）和“入职年份”（第4-7位），并格式化年份。

函数语法：

• MID(text, start_num, num_chars)：从文本字符串中指定位置开始提取特定数量的字符。
• TEXT(value, format_text)：将数值转换为按指定格式显示的文本。

解题步骤：

1. 提取部门代码（前3位）：

   // 假设员工ID在 A2 单元格 =LEFT(A2, 3) // 或使用 MID =MID(A2, 1, 3)

   LEFT函数更简洁，直接从左边取3位。

2. 提取入职年份（第4-7位）：

   =MID(A2, 4, 4)

   • 从第4个字符开始，提取4个字符，得到"2023"。

3. 格式化年份： 提取出的"2023"是文本，如果需要转换为“2023年”的格式，可以使用TEXT函数，但需要先将其转为数值。

   =TEXT(VALUE(MID(A2,4,4)), "0年")

   • VALUE(MID(...))：将提取出的文本"2023"转换为数值2023。
   • TEXT(..., "0年")：将数值格式化为“2023年”的样式。
   • 简化方案：如果结果允许是文本，可以直接拼接：=MID(A2,4,4)&"年"。

3.4 任务四：结果汇总与判断 —— IF函数

场景：根据计算出的“总薪资”进行等级评定。

函数语法 (IF)：=IF(logical_test, value_if_true, value_if_false)

解题步骤：假设总薪资在O2单元格，评定规则：>=10000为“优秀”，>=6000为“合格”，否则为“待改进”。

=IF(O2>=10000, "优秀", IF(O2>=6000, "合格", "待改进"))

这是一个嵌套IF函数：

1. 首先判断O2>=10000是否成立，成立则返回“优秀”。
2. 如果不成立，则进入第二个IF判断O2>=6000，成立则返回“合格”。
3. 如果还不成立，则返回“待改进”。

4. 完整实战案例：分步操作演示

现在，我们将上述所有知识点串联起来，模拟完成一道综合题目。假设我们有一个简单的数据表（Sheet1）和一个答题表（Sheet2）。

4.1 数据源 (Sheet1)

4.2 答题要求 (Sheet2)在Sheet2中完成以下计算：

1. A2单元格：计算销售部绩效系数大于1的员工基本工资总和。
2. B2单元格：根据Sheet2!D2单元格输入的员工ID（例如SLS00120230101），查找并返回其姓名。
3. C2单元格：根据同一员工ID，查找并返回其部门。
4. D2单元格：从该员工ID中提取其入职年份（格式为“XXXX年”）。
5. E2单元格：计算该员工的总薪资（基本工资*绩效系数+项目奖金）。
6. F2单元格：根据总薪资评定等级（>=10000优秀，>=6000合格，否则待改进）。

4.3 分步操作与公式输入

步骤1：计算多条件求和在Sheet2!A2单元格输入：

=SUMIFS(Sheet1!$E$2:$E$100, Sheet1!$C$2:$C$100, "销售部", Sheet1!$F$2:$F$100, ">1")

• 注意跨表引用，使用Sheet1!来指定数据源工作表。
• 假设数据有100行，实际范围请根据你的数据调整。

步骤2：查找姓名在Sheet2!B2单元格输入：

=VLOOKUP($D$2, Sheet1!$A$2:$G$100, 2, FALSE)

• $D$2是存放待查员工ID的单元格（绝对引用）。
• 在Sheet1的A到G列中查找，返回第2列（姓名）。

步骤3：查找部门在Sheet2!C2单元格输入：

=VLOOKUP($D$2, Sheet1!$A$2:$G$100, 3, FALSE)

步骤4：提取并格式化入职年份在Sheet2!D2单元格（假设这是显示结果的单元格，注意不要和输入ID的单元格冲突，这里假设ID输入在G2，结果在D2）输入：

=TEXT(VALUE(MID($G$2, 7, 4)), "0年")

• 假设ID格式为SLS00120230101，部门代码SLS(3位)+序号001(3位)+年份2023(4位)+月日0101(4位)。所以年份从第7位开始，取4位。
• 重要：请根据你题目中ID的实际结构调整MID函数的参数（start_num和num_chars）。

步骤5：计算总薪资在Sheet2!E2单元格输入：

=VLOOKUP($G$2, Sheet1!$A$2:$G$100, 5, FALSE) * VLOOKUP($G$2, Sheet1!$A$2:$G$100, 6, FALSE) + VLOOKUP($G$2, Sheet1!$A$2:$G$100, 7, FALSE)

或者使用更清晰的中间单元格法。

步骤6：评定等级在Sheet2!F2单元格输入：

=IF(E2>=10000, "优秀", IF(E2>=6000, "合格", "待改进"))

4.4 验证结果

1. 在Sheet2!G2（或其他你指定的ID输入单元格）输入一个存在的员工ID，例如SLS00120230101。
2. 观察B2:F2单元格是否正确显示了“张三”、“销售部”、“2023年”、总薪资=8000*1.2+2000=11600以及等级“优秀”。
3. 检查A2单元格的求和结果是否正确。

5. 常见问题与排查思路

在操作过程中，你可能会遇到以下问题：

6. 最佳实践与应试技巧

掌握函数是基础，但高效准确地解题还需要一些“软技能”。

6.1 公式编写与调试技巧

• 分步验证：对于复杂的嵌套公式（如多个VLOOKUP相乘相加），不要试图一步写完。可以先在空白单元格分别写出各个部分（如单独查找基本工资、绩效系数），验证结果正确后，再组合成最终公式。
• 使用F9键局部计算：在编辑栏选中公式的一部分，按F9键，可以立即计算该部分的结果，方便调试。查看后按Esc退出，避免破坏公式。
• 善用“插入函数”对话框：对于不熟悉的函数，点击公式栏前的fx按钮，打开函数参数对话框，可以可视化地填写每个参数，并有简要提示。
• 命名区域：如果数据区域固定，可以将其定义为名称（如选中A2:G100，在左上角名称框输入Data），这样公式中可以用Data代替$A$2:$G$100，更易读。

6.2 数据准备与格式检查

• 清除多余空格：数据中的首尾空格是导致VLOOKUP匹配失败的常见原因。可以使用TRIM()函数或“数据”->“分列”功能（固定宽度，不分割）来清理。
• 统一数字格式：确保参与计算的列（如基本工资、绩效系数）是“常规”或“数值”格式，而非文本格式。
• 检查数据一致性：确保作为查找依据的列（如员工ID）没有重复值，且格式完全一致。

6.3 应试与效率提升建议

• 先读题，后动手：花1-2分钟完整阅读题目要求，在脑海中规划好每个结果对应的单元格和大概使用的函数。
• 从简单到复杂：先完成单一步骤的计算（如简单的求和、提取），再处理需要嵌套或组合的复杂公式。
• 利用填充柄：如果同一公式需要向下或向右填充，写好第一个公式后，使用单元格右下角的填充柄拖动，WPS会自动调整相对引用。
• 保存与复查：完成所有操作后，务必保存文件。然后，改变几个输入条件（如换一个员工ID），检查所有结果是否联动更新正确，这是最好的复查方法。

通过以上系统的拆解和练习，相信你对WPS表格中这类综合应用题已经有了清晰的认识。核心在于分解任务、理解函数、谨慎引用、逐步验证。多找几套题库的类似题目进行练习，熟能生巧。在实际工作和学习中，这套数据分析的思路同样适用，祝你备考顺利，技能大涨！