第 11 周：AI 辅助 Excel 数据应用

本周学习怎样把 AI 当结对开发者：由你定义需求、拆分任务、判断代码是否合理、运行测试并整理文档。AI 负责加速，你仍然对最终结果负责。

建议用时：8-10 小时。

本周目标#

学完本周，你应该能够：

• 把业务需求写成 AI 可以理解和验证的任务说明。
• 使用 DeepSeek、通义千问或 Kimi 的网页版辅助编写代码。
• 识别 AI 生成的错误 API、遗漏规则和不可运行代码。
• 读取 CSV，计算借阅得分并生成四张 Excel 工作表。
• 实现班级汇总、阅读之星和异常名单的排序规则。
• 使用正常、边界、重复和错误数据验证程序。
• 写出一份别人能按步骤复现的 说明文档.docx。
• 在 90 分钟限制内完成任务，而不是把时间全花在反复问 AI 上。

11.1 AI 能做什么，不能替你做什么#

AI 适合：

• 帮你把需求拆成函数。
• 提供某个库的最小示例。
• 解释报错和定位思路。
• 生成测试数据或检查遗漏。
• 帮你整理文档结构。

AI 不能替代：

• 确认题目真正要求的字段、文件名和排序规则。
• 判断生成结果是否与本地数据一致。
• 运行代码和检查实际输出。
• 保证使用的 API 或依赖在环境中存在。
• 对提交结果承担责任。

不要把题目粘贴给 AI 后直接复制完整答案。至少要完成“分步提问、运行、验证、修正、解释”这一轮闭环。

11.2 允许使用的工具#

正式第三阶段只允许使用：

• DeepSeek 网页版。
• 通义千问网页版。
• Kimi 网页版。

本课程不训练其他 AI 工具，也不建议把比赛任务发送到未允许的服务。

请求中不要放不必要的个人信息。当前第三阶段数据是训练用虚构数据，但仍应只提交完成任务所需的内容。

11.3 一个有效的 AI 请求#

不要只写：

帮我写一个 Excel 统计程序。

这种请求缺少输入、输出和规则，AI 只能猜。

更好的结构：

任务：用 Python 和 openpyxl 处理 CSV 并生成 Excel。

输入：
- 文件：模拟赛/data/stage3_borrowings.csv
- 字段：reader_id,name,class_name,category,borrow_count,overdue_count
- 所有数字字段读取后要转成 int。

输出：
- 文件：模拟赛/output/阅读活动统计.xlsx
- 工作表：原始数据、班级汇总、阅读之星、异常名单。

规则：
1. 借阅得分 = borrow_count * 5 - overdue_count * 3。
2. 班级汇总按平均借阅得分降序。
3. 阅读之星取前 5 名，得分降序；并列时借阅次数降序，再按 reader_id 升序。
4. 异常名单：overdue_count >= 3 或 score < 0，两个条件都满足时原因同时写明。
5. 不修改输入，异常时给清楚提示。

请先给出数据流和函数拆分，不要立刻输出 300 行代码。

先要设计，再要实现。这样更容易发现 AI 是否误解了字段和排序规则。

11.4 分阶段与 AI 协作#

第一轮：确认需求#

让 AI 复述：

• 输入和输出路径。
• 四张表的字段。
• 计算公式。
• 三个排序和异常规则。

如果复述漏了任何一条，先纠正，不要进入写代码阶段。

第二轮：函数拆分#

推荐结构：

load_records()
build_original_sheet()
build_class_summary()
build_star_sheet()
build_exception_sheet()
style_worksheet()
save_workbook()
main()

函数边界能让你分别测试读取、汇总和输出。

第三轮：分功能生成代码#

一次只让 AI 完成一个函数。例如：

只实现 calculate_score 和 load_records。
使用 csv.DictReader。
required 字段缺少时抛出 ValueError，并包含 CSV 行号。
不要生成 Excel 部分。

每段代码都先运行，再继续。

第四轮：让 AI 做代码审查#

把已跑通的代码交给 AI，并提出具体审查点：

请按以下清单审查，不要重写全部代码：
1. 是否有字段名写错或类型未转换？
2. 班级平均分是否按人数正确计算？
3. 阅读之星三项并列规则是否完整？
4. 异常原因两个条件同时满足时是否都保留？
5. 是否可能覆盖输入文件？
6. 哪些边界测试还缺失？

11.5 核对 AI 代码的三个问题#

拿到每段代码后问：

1. 它从哪里读取数据？ 路径和编码是否正确？
2. 它怎样计算？ 公式、分母、排序键是否符合题目？
3. 它把结果写到哪里？ 文件名、工作表名和字段是否准确？

如果回答不清楚，代码即使看起来合理也不能直接使用。

常见 AI 错误#

• 使用了环境中未安装的库。
• 把 openpyxl 的工作表对象当成字典。
• ws.append 的列顺序与表头不一致。
• 平均分除以固定人数，而不是当前班级人数。
• 只按一个字段排序，遗漏并列规则。
• 异常原因只保留第一个条件。
• 把结果保存回输入 CSV。
• 忘记 os.makedirs()，输出目录不存在时失败。
• 声称公式会自动计算，但 openpyxl 本身不计算。

11.6 项目数据流#

CSV 文本
  ↓ csv.DictReader
字段完整、数字已转换的记录列表
  ↓ 计算
每条记录增加 borrow_score
  ↓ 分组和排序
班级汇总、阅读之星、异常名单
  ↓ openpyxl
四张工作表的 Excel 文件

先把数据流跑通，再添加样式。不要在尚未确认统计结果时花大量时间调颜色。

11.7 核心实现：读取和计算#

import csv

REQUIRED_FIELDS = (
    "reader_id",
    "name",
    "class_name",
    "category",
    "borrow_count",
    "overdue_count",
)


def calculate_score(borrow_count, overdue_count):
    return borrow_count * 5 - overdue_count * 3


def load_records(input_path):
    records = []

    with open(input_path, "r", encoding="utf-8", newline="") as file:
        reader = csv.DictReader(file)

        if reader.fieldnames is None:
            raise ValueError("CSV 缺少表头")

        missing_headers = [
            field for field in REQUIRED_FIELDS
            if field not in reader.fieldnames
        ]
        if missing_headers:
            raise ValueError(f"CSV 缺少字段：{missing_headers}")

        for line_number, row in enumerate(reader, start=2):
            missing_values = [
                field for field in REQUIRED_FIELDS
                if row.get(field) is None or row[field].strip() == ""
            ]
            if missing_values:
                raise ValueError(
                    f"CSV 第 {line_number} 行缺少值：{missing_values}"
                )

            try:
                borrow_count = int(row["borrow_count"])
                overdue_count = int(row["overdue_count"])
            except ValueError as error:
                raise ValueError(
                    f"CSV 第 {line_number} 行数字格式错误：{error}"
                ) from error

            record = {
                "reader_id": row["reader_id"].strip(),
                "name": row["name"].strip(),
                "class_name": row["class_name"].strip(),
                "category": row["category"].strip(),
                "borrow_count": borrow_count,
                "overdue_count": overdue_count,
            }
            record["borrow_score"] = calculate_score(
                borrow_count,
                overdue_count,
            )
            records.append(record)

    if not records:
        raise ValueError("CSV 中没有读者数据")

    return records

enumerate(reader, start=2) 中的 2 表示第一条数据在 CSV 文件第 2 行，因为第 1 行是表头。错误信息带行号，便于修正数据。

11.8 核心实现：班级汇总#

def build_class_summary(records):
    groups = {}

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

    summary = []
    for class_name, group in 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["borrow_score"] for item in group)
            / reader_count
        )

        summary.append(
            {
                "class_name": class_name,
                "reader_count": reader_count,
                "borrow_total": borrow_total,
                "overdue_total": overdue_total,
                "score_average": score_average,
            }
        )

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

平均分应按“该班级读者得分总和 / 该班级人数”，不是除以全部学生人数。

11.9 核心实现：阅读之星#

def build_reading_stars(records, limit=5):
    ranked = sorted(
        records,
        key=lambda item: (
            -item["borrow_score"],
            -item["borrow_count"],
            item["reader_id"],
        ),
    )
    return ranked[:limit]

三个排序键分别对应：

1. -borrow_score：得分越高越靠前。
2. -borrow_count：借阅次数越多越靠前。
3. reader_id：编号越小越靠前。

如果数据量少于 5，切片会安全返回现有数量，不会报错。

11.10 核心实现：异常名单#

def build_exception_records(records):
    result = []

    for record in records:
        reasons = []

        if record["overdue_count"] >= 3:
            reasons.append("逾期次数较多")
        if record["borrow_score"] < 0:
            reasons.append("得分过低")

        if reasons:
            item = record.copy()
            item["reason"] = "、".join(reasons)
            result.append(item)

    result.sort(key=lambda item: item["reader_id"])
    return result

两个条件可能同时成立，所以先收集原因列表，再统一拼接。不要在两个独立 if 中直接覆盖同一个字符串。

11.11 写入 Excel#

from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter


def style_worksheet(ws):
    header_fill = PatternFill("solid", fgColor="DCE6F1")

    for cell in ws[1]:
        cell.font = Font(bold=True)
        cell.fill = header_fill
        cell.alignment = Alignment(
            horizontal="center",
            vertical="center",
        )

    ws.freeze_panes = "A2"
    ws.auto_filter.ref = ws.dimensions

    for index in range(1, ws.max_column + 1):
        ws.column_dimensions[get_column_letter(index)].width = 14


def build_workbook(records, summary, stars, exceptions, output_path):
    wb = Workbook()

    original_ws = wb.active
    original_ws.title = "原始数据"
    original_ws.append(
        [
            "读者编号",
            "姓名",
            "班级",
            "图书类别",
            "借阅次数",
            "逾期次数",
            "借阅得分",
        ]
    )
    for item in records:
        original_ws.append(
            [
                item["reader_id"],
                item["name"],
                item["class_name"],
                item["category"],
                item["borrow_count"],
                item["overdue_count"],
                item["borrow_score"],
            ]
        )

    summary_ws = wb.create_sheet("班级汇总")
    summary_ws.append(
        ["班级", "读者人数", "借阅总次数", "逾期总次数", "平均借阅得分"]
    )
    for item in summary:
        summary_ws.append(
            [
                item["class_name"],
                item["reader_count"],
                item["borrow_total"],
                item["overdue_total"],
                round(item["score_average"], 2),
            ]
        )

    star_ws = wb.create_sheet("阅读之星")
    star_ws.append(
        [
            "排名",
            "读者编号",
            "姓名",
            "班级",
            "图书类别",
            "借阅次数",
            "逾期次数",
            "借阅得分",
        ]
    )
    for rank, item in enumerate(stars, start=1):
        star_ws.append(
            [
                rank,
                item["reader_id"],
                item["name"],
                item["class_name"],
                item["category"],
                item["borrow_count"],
                item["overdue_count"],
                item["borrow_score"],
            ]
        )

    exception_ws = wb.create_sheet("异常名单")
    exception_ws.append(
        ["读者编号", "姓名", "班级", "逾期次数", "借阅得分", "异常原因"]
    )
    for item in exceptions:
        exception_ws.append(
            [
                item["reader_id"],
                item["name"],
                item["class_name"],
                item["overdue_count"],
                item["borrow_score"],
                item["reason"],
            ]
        )

    for ws in wb.worksheets:
        style_worksheet(ws)

    summary_ws["E2"].number_format = "0.00"
    for row_index in range(2, summary_ws.max_row + 1):
        summary_ws.cell(row=row_index, column=5).number_format = "0.00"

    wb.save(output_path)
    wb.close()

数字格式只在 Excel 中控制显示。Python 汇总时仍然保留实际浮点值。

11.12 主程序与异常提示#

import os


INPUT_PATH = "模拟赛/data/stage3_borrowings.csv"
OUTPUT_PATH = "模拟赛/output/阅读活动统计.xlsx"


def main():
    try:
        os.makedirs(os.path.dirname(OUTPUT_PATH), exist_ok=True)

        records = load_records(INPUT_PATH)
        summary = build_class_summary(records)
        stars = build_reading_stars(records)
        exceptions = build_exception_records(records)
        build_workbook(
            records,
            summary,
            stars,
            exceptions,
            OUTPUT_PATH,
        )

        print(f"已读取 {len(records)} 条记录")
        print(f"班级数量：{len(summary)}")
        print(f"阅读之星：{len(stars)}")
        print(f"异常读者：{len(exceptions)}")
        print("已保存：", OUTPUT_PATH)

    except FileNotFoundError:
        print("错误：找不到输入文件", INPUT_PATH)
    except PermissionError:
        print("错误：没有读取或写入权限")
    except ValueError as error:
        print("数据错误：", error)
    except Exception as error:
        print("程序运行失败：", type(error).__name__, error)


if __name__ == "__main__":
    main()

最后一个 except Exception 用于给比赛现场一个总体提示，但前面的特定异常仍然优先处理。基础练习中不能把所有异常都藏在同一个模糊提示里。

11.13 整合检查#

把以上函数按下面顺序组织到 reader_stats.py：

1. 标准库导入。
2. openpyxl 导入。
3. 常量和字段列表。
4. calculate_score()。
5. load_records()。
6. build_class_summary()。
7. build_reading_stars()。
8. build_exception_records()。
9. style_worksheet()。
10. build_workbook()。
11. main()。
12. if __name__ == "__main__": main()。

AI 生成代码时可能把这些顺序打乱，但函数之间的依赖关系必须正确。

11.14 验收测试#

正常数据#

使用第三阶段输入文件，预期：

• 原始数据 12 条。
• 班级汇总 3 个班。
• 阅读之星 5 名。
• 异常名单 3 名。

边界数据#

错误数据#

• CSV 文件不存在。
• 缺少表头字段。
• 某行缺值。
• 借阅次数是 "abc"。
• 输出目录不可写。

每个错误都应有提示，不能悄悄生成一张不完整的表。

排序测试#

专门构造同分记录：

• 同分时借阅次数更多者在前。
• 同分同借阅次数时，编号更小者在前。

不要只在正式数据上看到“看起来正确”就结束，正式数据可能没有命中全部并列规则。

11.15 预期的班级汇总#

当前输入数据计算后，班级汇总应为：

如果结果不一致，依次检查：

1. 借阅得分公式是否写对。
2. 分组人数是否正确。
3. 是否把逾期次数错误地加进得分。
4. 是否交换了借阅和逾期列。
5. 排序是否使用平均分。

11.16 说明文档要写什么#

按比赛要求创建 说明文档.docx，建议结构：

校园阅读月数据统计程序说明

1. 运行环境与命令
2. 输入与输出文件
3. 功能一：原始数据
4. 功能二：班级汇总
5. 功能三：阅读之星
6. 功能四：异常名单
7. 测试结果
8. 已知限制

必须写清的内容#

• Python 和 openpyxl 版本。
• 程序文件名。
• 从哪个目录运行。
• 实际运行命令。
• 输入文件路径。
• 输出文件路径。
• 每个工作表包含什么。
• 每个统计和排序规则。
• 实际验证结果或截图。

示例运行说明：

将终端工作目录切换到 study/py，
执行：python reader_stats.py
程序读取：模拟赛/data/stage3_borrowings.csv
生成文件：模拟赛/output/阅读活动统计.xlsx

命令中的 python 要与你实际环境一致。如果现场使用 python3，文档也必须写 python3。

功能说明模板#

对每张表写四部分：

1. 用途：这张表展示什么。
2. 字段：每列是什么。
3. 规则：怎样计算和排序。
4. 预期结果：行数、关键数据或截图。

例如：

功能二：班级汇总
用途：比较各班阅读活动整体表现。
字段：班级、读者人数、借阅总次数、逾期总次数、平均借阅得分。
规则：按班级分组，平均借阅得分 = 班级得分总和 / 班级人数，
保留两位小数，并按平均借阅得分从高到低排列。
实际结果：3 个班级，第一名高一(3)班，平均得分 27.25。

截图不是替代文字#

截图可以证明程序运行和结果，但不能代替运行命令、路径和规则说明。评委需要知道怎样自己复现，而不是只看一张结果图。

11.17 90 分钟时间分配#

不要在最后 5 分钟才寻找输出文件。每完成一个功能就较早检查一次。

11.18 独立练习#

A. 快速判断题#

1. AI 生成的代码是否可以直接提交？
2. openpyxl 公式会自动计算吗？
3. 班级平均分的分母应该是什么？
4. 三项排序规则应该怎样写排序键？
5. 两个异常条件同时满足时，原因应该怎样处理？
6. 输出目录不存在时应该先做什么？
7. 说明文档必须包含哪三类可复现信息？
8. 为什么截图不能代替运行命令？
9. 如何测试“少于五名阅读之星”？
10. AI 建议使用未安装库时应该怎样检查？

B. 实践题#

1. 给 load_records() 增加“借阅次数不能为负数”的校验。
2. 给班级汇总增加“逾期率”列，并写清计算规则。
3. 把阅读之星从 5 名改成参数 limit 控制。
4. 构造三条同分记录，验证两级并列规则。
5. 写一份 300 字以内的运行说明。
6. 用 AI 审查代码，但要求它只指出问题，不重写代码。

11.19 练习答案与提示#

A. 快速判断题答案#

1. 不可以。必须运行、核验、理解并整理。
2. 不会。文件保存公式，Excel/WPS 打开时才计算。
3. 当前班级的读者人数。
4. (-borrow_score, -borrow_count, reader_id)。
5. 收集所有原因，用分隔符合并，不能只保留一个。
6. 使用 os.makedirs(..., exist_ok=True) 创建。
7. 运行环境与命令、输入输出路径、实际验证结果。
8. 评委无法只凭图片确定程序如何运行、读取哪个文件。
9. 构造只有两三条记录的测试数据，检查程序返回 2 或 3 条而不报错。
10. 先确认环境是否已有该库，再要求 AI 提供只使用标准库或已允许库的方案。

B. 实践题提示#

1. 数字转换后检查：

   if borrow_count < 0:
       raise ValueError(f"第 {line_number} 行借阅次数不能为负数")
   

2. 逾期率可定义为：

   该班逾期总次数 / 该班借阅总次数
   

   借阅总次数为 0 时要单独处理。

3. 已有函数签名：

   def build_reading_stars(records, limit=5):
       return sorted(records, key=... )[:limit]
   

4. 三条记录应同时覆盖：

   • 同分不同借阅次数。
   • 同分同借阅次数不同编号。

5. 说明文档从“在哪个目录、输入什么、运行什么、输出什么”开始。

6. 审查提示要限制 AI 的输出格式，例如“每条问题包含行号、风险、验证方法，不提供完整重写代码”。

11.20 常见错误清单#

• 把 AI 的回答当成事实，没有运行。
• 使用未允许或未提供的第三方包。
• 没有把 CSV 数字字段转成整数。
• 平均分分母使用全部读者数。
• 阅读之星只实现得分排序。
• 异常原因只记录一个条件。
• 工作表字段顺序错误。
• 输出文件名或目录错误。
• 未关闭工作簿。
• 文档中的命令与实际运行方式不一致。
• 截图存在，但没有路径和规则说明。

11.21 过关检查#

• 能把需求拆成输入、处理、输出和测试四部分。
• 能根据明确提示与 AI 分阶段协作。
• 能识别并修正至少一种 AI 常见错误。
• 能完成 CSV 读取、得分计算和班级汇总。
• 能实现阅读之星的三级排序。
• 能实现异常名单的双条件与原因合并。
• 四张工作表的字段、顺序和数值符合要求。
• 正常、边界、错误和并列测试全部通过。
• 写出可复现的 说明文档.docx。
• 在 90 分钟内完成完整数据应用。