第 10 周：Excel 综合应用与第二阶段模拟

本周把 turtle、算法、文本文件、正则、目录管理和 openpyxl 放进同一场 60 分钟限时任务。目标不是写最多代码，而是在时间压力下稳定完成输入读取、结果计算、文件写入和格式检查。

建议用时：8-10 小时，其中第二阶段模拟与复盘约 2 小时。

本周目标#

学完本周，你应该能够：

• 把综合任务拆成可独立测试的小步骤。
• 批量填写 Excel 公式，并建立统计工作表。
• 使用 copy_worksheet() 创建数据副本。
• 把 CSV 数据整理成 Excel 明细表和汇总表。
• 按指定字段、排序规则和异常规则组织输出。
• 在 60 分钟内完成第二阶段五道实操题。
• 按评分点做提交前检查，而不是只看程序是否运行。

10.1 综合题的基本方法#

面对一个大任务，先写下五句话：

1. 输入文件在哪里？
2. 需要读取什么字段？
3. 中间要完成哪些计算？
4. 输出文件和字段是什么？
5. 有哪些排序、并列、空值和异常规则？

例如 Excel 成绩题：

输入：模拟赛/data/stage2.xlsx 的“成绩”工作表
读取：A-F 列原有数据
计算：总分、三科平均分
输出：模拟赛/output/第二阶段_成绩.xlsx
规则：公式覆盖第 2 行至最后一行，保留输入原件

写清这段需求，能避免“代码写完了，才发现输出文件名不对”。

10.2 批量填写 Excel 公式#

再次确认范围：

from openpyxl import load_workbook

wb = load_workbook("模拟赛/data/stage2.xlsx")
ws = wb["成绩"]

ws["G1"] = "总分"
ws["H1"] = "平均分"

for row_index in range(2, ws.max_row + 1):
    ws[f"G{row_index}"] = f"=SUM(D{row_index}:F{row_index})"
    ws[f"H{row_index}"] = f"=AVERAGE(D{row_index}:F{row_index})"

检查公式时不能只检查第二行：

常见错误是公式行号固定为 2，导致所有学生都重复计算 S001。

10.3 统计工作表#

第二阶段要求统计信息科技、数学和英语平均分。可以创建一个新表：

summary_ws = wb.create_sheet("统计")
summary_ws.append(["科目", "平均分"])
summary_ws.append(["信息科技", "=AVERAGE(成绩!D2:D11)"])
summary_ws.append(["数学", "=AVERAGE(成绩!E2:E11)"])
summary_ws.append(["英语", "=AVERAGE(成绩!F2:F11)"])

公式中的 成绩! 表示引用“成绩”工作表。表名没有空格时，多数情况下可以直接写中文表名；若表名含空格或特殊符号，使用单引号包住：

=AVERAGE('成绩 2026'!D2:D11)

表头也要清晰#

至少写出：

科目 | 平均分

若题目只说“设置清晰表头”，没有要求样式，内容正确即可；有条件时再设置粗体、列宽和居中。

10.4 复制工作表#

copied_ws = wb.copy_worksheet(ws)
copied_ws.title = "数据副本"

复制后：

• 新表和原表位于同一个工作簿。
• 新表初始名称类似 成绩 Copy。
• 应立即改为题目指定名称。
• 复制会带上单元格内容和公式。

复制后检查：

print(wb.sheetnames)
print("数据副本" in wb.sheetnames)
print(copied_ws["G2"].value)

10.5 另存输入工作簿#

import os

output_path = "模拟赛/output/第二阶段_成绩.xlsx"
os.makedirs(os.path.dirname(output_path), exist_ok=True)
wb.save(output_path)
wb.close()

如果直接覆盖 模拟赛/data/stage2.xlsx，下一次测试就失去原始输入，也违反题目“不得覆盖输入文件”的要求。

保存完成后做三项检查：

1. 输出路径存在。
2. 输入文件修改时间或内容没有变化。
3. 重新 load_workbook(output_path) 能读到预期工作表和公式。

10.6 从 CSV 到 Excel 的完整流程#

第三阶段会读取 CSV 并生成多张统计表。典型架构：

读取文件 -> 转为有字段名的字典 -> 计算派生字段 -> 分组统计 -> 排序 -> 写入 Excel

读取：

import csv

records = []
with open(input_path, "r", encoding="utf-8", newline="") as file:
    reader = csv.DictReader(file)
    for row in reader:
        row["borrow_count"] = int(row["borrow_count"])
        row["overdue_count"] = int(row["overdue_count"])
        records.append(row)

计算：

for record in records:
    record["score"] = (
        record["borrow_count"] * 5
        - record["overdue_count"] * 3
    )

分组：

class_groups = {}
for record in records:
    class_name = record["class_name"]
    if class_name not in class_groups:
        class_groups[class_name] = []
    class_groups[class_name].append(record)

汇总：

summary_rows = []
for class_name, group in class_groups.items():
    reader_count = len(group)
    borrow_total = sum(item["borrow_count"] for item in group)
    overdue_total = sum(item["overdue_count"] for item in group)
    score_average = sum(item["score"] for item in group) / reader_count
    summary_rows.append(
        {
            "class_name": class_name,
            "reader_count": reader_count,
            "borrow_total": borrow_total,
            "overdue_total": overdue_total,
            "score_average": score_average,
        }
    )

排序：

summary_rows.sort(
    key=lambda item: item["score_average"],
    reverse=True,
)

写入：

summary_ws.append(
    ["班级", "读者人数", "借阅总次数", "逾期总次数", "平均借阅得分"]
)

for item in summary_rows:
    summary_ws.append(
        [
            item["class_name"],
            item["reader_count"],
            item["borrow_total"],
            item["overdue_total"],
            round(item["score_average"], 2),
        ]
    )

这是一套通用数据处理骨架。第 11 周会继续补并列规则、异常名单和说明文档。

10.7 多条件和并列规则#

排序规则一多，就要保持优先级清楚。第三阶段阅读之星要求：

1. 得分从高到低。
2. 得分相同时，借阅次数从高到低。
3. 仍相同时，读者编号从小到大。

可以构造排序键：

ranked = sorted(
    records,
    key=lambda item: (
        -item["score"],
        -item["borrow_count"],
        item["reader_id"],
    ),
)

负号把“越大越靠前”变成普通升序。读者编号保持升序，所以不加负号。

如果担心字符串编号规则不同，也可以分两阶段排序，利用 sorted() 的稳定性，但直接构造元组键更明确。

10.8 输出前检查清单#

10.9 第二阶段限时策略#

第二阶段模拟题限时 60 分钟，共 40 分。

建议分配：

若某题卡住超过计划时间 3 分钟，先保留可运行部分，转做下一题。最后再回来补。不要在一道题上失去整场时间。

10.10 第二阶段模拟#

现在完成第二阶段模拟题。

规则：

• 60 分钟。
• 关闭网络。
• 不查看第一或第三阶段材料。
• 不提前查看参考答案。
• 所有输出写入 模拟赛/output/。
• 提交前不要覆盖输入文件。

开始前先建立输出目录，并确认输入文件名称。

10.11 提交前逐项验收#

文件清单#

• task1_turtle.py
• task2_algorithms.py
• task3_report.py
• task4_files.py
• task5_excel.py
• 第二阶段_成绩报告.txt
• 第二阶段_编号.txt
• 第二阶段_成绩.xlsx
• 归档目录和备份文件

运行与内容#

• 所有 Python 文件能从正确目录运行。
• 报告每位学生一行，最高分并列取先出现者。
• 班级平均分保留两位小数，顺序正确。
• 编号去重、数值升序，且保留前导零。
• 归档目录存在，旧副本不存在，备份文件存在。
• Excel 表头、公式、统计表、数据副本均正确。
• 输入工作簿未被修改。

10.12 评分与复盘#

完成后打开参考答案与评分。

复盘记录#

如果失分来自路径或文件名，不要简单记成“粗心”。把它转成可执行检查项，例如“运行前打印并核对输出绝对路径”。

10.13 独立练习#

A. 快速判断题#

1. 批量公式为什么不能用固定的 D2:F2？
2. copy_worksheet() 复制后还应做什么？
3. data_only=True 一定能读到公式计算结果吗？
4. 第二阶段的输出目录应该在哪里？
5. 排序得分降序、读者编号升序，排序键可以怎样写？
6. 为什么每次测试前要保留输入原件？
7. 公式字符串漏写 = 会发生什么？
8. 如果题目要求平均分保留两位小数，写公式时是否还要对公式结果做 round()？
9. 一个 60 分钟任务卡住 15 分钟时应该怎样处理？
10. 提交前为什么要重新打开生成的文件检查？

B. 编程题#

1. 为第 2 行到最后一行批量生成总分公式。
2. 创建“统计”表并写入三科平均分公式。
3. 复制成绩表并命名为“数据副本”。
4. 读取 CSV 的借阅次数和逾期次数，计算借阅得分。
5. 按班级汇总人数、借阅总次数和平均借阅得分。
6. 按得分降序、借阅次数降序、编号升序对记录排序。

10.14 练习答案与解释#

A. 快速判断题答案#

1. 因为每名学生占一行，公式的行号必须随当前学生变化。
2. 修改复制表的标题，并检查内容和目标工作表名称。
3. 不一定。没有缓存计算值时可能读到 None。
4. 模拟赛/output/。
5. key=lambda item: (-item["score"], item["reader_id"])。
6. 便于重复运行、对比结果，并避免覆盖后无法恢复。
7. 会被当成普通文本，而不是公式。
8. 平均分公式用 AVERAGE；是否需要显示两位小数通过单元格数字格式控制，round() 适用于 Python 计算值，不改变 Excel 公式本身。
9. 先记录当前状态，转向其他题，完成可得分部分后再回来。
10. 检查文件能打开、工作表存在、公式和值正确，而不是只相信 save() 没有报错。

B. 编程题参考答案#

1. 批量总分公式：

   for row_index in range(2, ws.max_row + 1):
       ws[f"G{row_index}"] = f"=SUM(D{row_index}:F{row_index})"
   

2. 统计平均分：

   summary_ws = wb.create_sheet("统计")
   summary_ws.append(["科目", "平均分"])
   summary_ws.append(["信息科技", "=AVERAGE(成绩!D2:D11)"])
   summary_ws.append(["数学", "=AVERAGE(成绩!E2:E11)"])
   summary_ws.append(["英语", "=AVERAGE(成绩!F2:F11)"])
   

3. 复制工作表：

   copied_ws = wb.copy_worksheet(ws)
   copied_ws.title = "数据副本"
   

4. 计算借阅得分：

   score = int(row["borrow_count"]) * 5 - int(row["overdue_count"]) * 3
   

5. 班级汇总：

   summary.sort(key=lambda item: item["average"], reverse=True)
   

   完整汇总还要从每个班级列表计算人数、借阅总和、逾期总和和平均分。

6. 多字段排序：

   ranked = sorted(
       records,
       key=lambda item: (
           -item["score"],
           -item["borrow_count"],
           item["reader_id"],
       ),
   )
   

10.15 常见错误清单#

• 公式只覆盖第二行。
• 新建统计表时覆盖原有同名表，或未检查表名。
• 引用了错误工作表或错误列范围。
• 复制工作表后忘记改标题。
• 保存到输入路径。
• 输出目录不存在导致保存失败。
• 只运行程序，不打开输出检查。
• 排序只实现主规则，忽略并列规则。
• 把带前导零的编号转成整数后，输出时丢失零。
• 时间分配失控，最后一题没有完成。

10.16 过关检查#

• 能按行号批量生成正确的 Excel 公式。
• 能建立统计表并写清表头。
• 能使用 copy_worksheet() 并命名副本。
• 能把 CSV 数据处理成 Excel 汇总表。
• 能实现主排序和至少两条并列排序规则。
• 能在 60 分钟内完成第二阶段全部任务。
• 第二阶段达到 28/40 分。
• 所有输出文件存在、可打开、格式符合要求。
• 输入工作簿和数据文件均未被覆盖。
• 第二次运行程序仍能生成一致结果。