参考01：Excel入门

数据处理与基本函数命令

任务1：汉字笔画排序

把你宿舍的全部同学的姓名复制到下面，并按笔画顺序降序进行排列！并填写处最终排序序号。

案例情景：

  • 你宿舍有4个同学，分别是：张三、李四、王五、赵六、张二哥。
  • 请使用Excel，将这5个同学的姓名按笔画顺序降序进行排列！并填写出最终排序序号。

Excel中可以进行如下过程的操作：

  • 在Excel中输入姓名，例如A1为“张三”，A2为“李四”，A3为“王五”，A4为“赵六”,A5为“张二哥”。

  • 在excel中使用排序功能，将姓名按笔画顺序降序进行排列。

提示

注意：

例如，“张三”的第一个字“张”的笔画数为7，第二个字“三”的笔画数为3，两个字笔画总数为10；“张二哥”的第一个字“张”的笔画数为7，第二个字“二”的笔画数为2，第三个字“哥”的笔画数为10，三个字的笔画总数为19

方法一：计算姓名笔画数总数，然后进行由大到小排序（目前Excel中没有提供直接计算笔画数的函数）。

  • 例如，我们知道“张三”的笔画数（10）。同理，可以得到其他同学的笔画数，例如“张二哥”的笔画数为19。上述操作获得的是姓名的笔画总数的排序关系，也即“张二哥”（19）排在“张三”（10）前面。

方法二：使用Excel的排序功能，根据姓名的每一个字进行笔画数计算和依次由大到小排序。

  • 例如，“张三”的第一个字“张”的笔画数为7，第二个字“三”的笔画数为3，两个字笔画总数为10；“张二哥”的第一个字“张”的笔画数为7，第二个字“二”的笔画数为2，第三个字“哥”的笔画数为10，三个字的笔画总数为19。

  • 排序功能在excel中，位于“数据”选项卡下，选择“排序”功能。

  • 排序功能中，可以选择主要关键字和次要关键字，例如主要关键字为笔画数，次要关键字为姓名。

  • 排序功能中，可以选择升序或降序，例如降序。

  • 上述操作获得的排序关系，也即：“张（7）三（3）”（共10划）排在“张（7）二（2）哥（10）”（共19划）前面。

任务2：格式、日期、文本

Exercise1.1 请利用有关Excel函数，操作并得到如下信息？

Excel日期

Excel日期格式为mm/dd/yyyy，例如2025-03-20。

今天日期为2025-03-20，在Excel中输入=TODAY()，即可得到今天日期“2025-03-20”。

今天日期转换为长日期格式，要借助TEXT函数，输入=TEXT(TODAY(),"yyyy-mm-dd")，即可得到长日期格式。

  • 例如，今天日期为2025-03-20，在Excel中输入=TEXT(TODAY(),"yyyy年-mm月-dd日")，即可得到长日期格式“2025年3月20日”。

任务3：查找匹配

示例背景

等级规则

  • 总成绩 < 200 → 差
  • 200 ≤ 总成绩 < 230 → 中
  • 230 ≤ 总成绩 < 260 → 良
  • 总成绩 ≥ 260 → 优

数据准备

  • 学生总成绩：存储在 A2:A11，数值为：
    270, 241, 206, 200, 210, 240, 268, 245, 280, 216
  • 辅助表：存储在 D2:E5，按升序排列：
最低分 等级
0 差
200 中
230 良
260 优

VLOOKUP函数

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

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]),具体的参数如下：

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

下面举例说明。

请用VLOOKUP函数确定每位同学总成绩的等级。其中等级划分标准为：<200（ 差）；200230（中）；230260（良）；>260（优）。10为同学的总成绩分别为：270、241、206、200、210、240、268、245、280、216。

我们可以将等级划分标准写入一个区域，例如D1:E5，其中D1为等级划分标准下限值(lower)，E5为等级类别(cat)。

方法一：对第一个同学的成绩，使用VLOOKUP函数查找其对应的等级，然后对excel单元格进行下拉填充获得其他同学的等级。

  • 首先在Excel单元格输入：=VLOOKUP(B2, $D$1:$E$5, 2, TRUE)，即可得到第一个同学的成绩对应的等级。

  • 然后对excel单元格进行下拉填充，即可得到其他同学的成绩对应的等级。

要注意，我们使用VLOOKUP函数时，需要将df_criteria区域(D1:E5)设置为绝对引用（$D$1:$E$5）。

方法二：使用Vlookup函数，对全部同学的成绩进行匹配，一次性得到全体同学的成绩对应的等级。

  • 首先选中待求解的单元格区域，例如C2:C11。

  • 然后输入公式：=VLOOKUP(B2:B11, $D$1:$E$5, 2, TRUE)，然后按Ctrl+Shift+Enter，即可得到全体同学的成绩对应的等级。

match 和 index 函数

上述VLOOKUP函数，可以利用match函数和index函数来实现。

  • 首先使用match函数，查找每个同学的成绩在df_criteria数据集中的位置。

  • 然后使用index函数，根据match函数返回的位置，返回对应的等级。

可视化流程

flowchart TD
    A[输入总成绩] --> B[MATCH 定位行号]
    B --> C[INDEX 返回等级]
    C --> D[填充公式至所有行]

具体操作步骤如下：

1. 使用 MATCH 定位行号

在单元格 B2 输入以下公式，并向下填充至 B11：

=MATCH(A2, D$2:D$5, 1)

参数解释：

  • A2：要查找的总成绩（如 270）。
  • D$2:D$5：绝对引用的辅助表“最低分”列。
  • 1：近似匹配模式，查找 ≤ 目标值的最大值。

结果示例：

总成绩 MATCH 结果 说明
270 4 匹配 260（第4行）
241 3 匹配 230（第3行）
200 2 匹配 200（第2行）

2. 使用 INDEX 返回等级

在单元格 B2 输入完整公式，并向下填充：

=INDEX(E$2:E$5, MATCH(A2, D$2:D$5, 1))

参数解释：

  • E$2:E$5：辅助表的“等级”列（绝对引用）。
  • MATCH(...)：嵌套函数返回行号。

最终结果：

总成绩 公式 等级
270 =INDEX(E$2:E$5, MATCH(A2,D$2:D$5,1)) 优
241 =INDEX(E$2:E$5, MATCH(A3,D$2:D$5,1)) 良
206 =INDEX(E$2:E$5, MATCH(A4,D$2:D$5,1)) 中

关键细节

注记

注意事项

  1. 辅助表必须按升序排列，否则 MATCH 近似匹配会失效。
  2. 使用混合引用 D$2:D$5 和 E$2:E$5 防止填充公式时范围偏移。
  3. 边界值验证：
    • 成绩 259 → 匹配 230 → 返回“良”。
    • 成绩 260 → 匹配 260 → 返回“优”。

函数对比表

特性 VLOOKUP INDEX+MATCH
查找方向 仅支持从左到右 支持任意方向
列变化影响 列索引变化需调整公式 动态适应列变化
性能 大数据量较慢 更高效（尤其重复区域查找）