夜雨聆风今天学习什么？
今天我们来学习如何用Python分析Excel数据，让Python帮你做数据分析！
数据分析基础
什么是数据分析？
数据分析就是从数据中找出规律和信息。
常见分析：
统计总分、平均分 找出最高分、最低分 排名 分类统计
统计分析
案例1：成绩统计分析
import openpyxl
wb = openpyxl.load_workbook('成绩单_完整版.xlsx')
ws = wb.active
print("成绩统计分析报告")
print("=" * 50)
# 收集数据
students = []
math_scores = []
chinese_scores = []
english_scores = []
for row in ws.iter_rows(min_row=2, max_row=6, values_only=True):
name, math, chinese, english, avg, grade = row
students.append((name, math, chinese, english, avg, grade))
math_scores.append(math)
chinese_scores.append(chinese)
english_scores.append(english)
# 基本统计
print(f"学生人数：{len(students)}")
print(f"数学平均分：{sum(math_scores)/len(math_scores):.1f}")
print(f"语文平均分：{sum(chinese_scores)/len(chinese_scores):.1f}")
print(f"英语平均分：{sum(english_scores)/len(english_scores):.1f}")
# 找出最高分和最低分
print(f"\n数学最高分：{max(math_scores)}")
print(f"数学最低分：{min(math_scores)}")
print(f"语文最高分：{max(chinese_scores)}")
print(f"语文最低分：{min(chinese_scores)}")
print(f"英语最高分：{max(english_scores)}")
print(f"英语最低分：{min(english_scores)}")
# 等级分布
grade_count = {}
for name, math, chinese, english, avg, grade in students:
if grade in grade_count:
grade_count[grade] += 1
else:
grade_count[grade] = 1
print("\n等级分布：")
for grade, count in grade_count.items():
print(f" {grade}：{count}人")
wb.close()
案例2：排名分析
import openpyxl
wb = openpyxl.load_workbook('成绩单_完整版.xlsx')
ws = wb.active
# 收集数据
students = []
for row in ws.iter_rows(min_row=2, max_row=6, values_only=True):
name, math, chinese, english, avg, grade = row
students.append((name, math, chinese, english, avg))
# 按平均分排名
students.sort(key=lambda x: x[4], reverse=True)
print("学生排名（按平均分）：")
print("=" * 40)
for rank, (name, math, chinese, english, avg) in enumerate(students, 1):
print(f"第{rank}名：{name}，平均分：{avg}")
# 按数学排名
students_by_math = sorted(students, key=lambda x: x[1], reverse=True)
print("\n数学排名：")
print("=" * 40)
for rank, (name, math, chinese, english, avg) in enumerate(students_by_math, 1):
print(f"第{rank}名：{name}，数学：{math}")
wb.close()
数据筛选
案例3：筛选优秀学生
import openpyxl
wb = openpyxl.load_workbook('成绩单_完整版.xlsx')
ws = wb.active
# 筛选平均分>=90的学生
print("优秀学生（平均分>=90）：")
print("=" * 40)
for row in ws.iter_rows(min_row=2, max_row=6, values_only=True):
name, math, chinese, english, avg, grade = row
if avg >= 90:
print(f"{name}：平均分{avg}，等级{grade}")
# 筛选数学>=90的学生
print("\n数学优秀学生（数学>=90）：")
print("=" * 40)
for row in ws.iter_rows(min_row=2, max_row=6, values_only=True):
name, math, chinese, english, avg, grade = row
if math >= 90:
print(f"{name}：数学{math}")
wb.close()
生成分析报告
案例4：生成完整分析报告
import openpyxl
from datetime import datetime
wb = openpyxl.load_workbook('成绩单_完整版.xlsx')
ws = wb.active
# 创建新工作簿保存报告
report_wb = openpyxl.Workbook()
report_ws = report_wb.active
report_ws.title = "分析报告"
# 写入报告标题
report_ws['A1'] = '学生成绩分析报告'
report_ws['A1'].font = openpyxl.styles.Font(bold=True, size=16)
report_ws['A2'] = f'生成时间：{datetime.now().strftime("%Y-%m-%d %H:%M:%S")}'
# 收集数据
students = []
math_scores = []
chinese_scores = []
english_scores = []
all_scores = []
for row in ws.iter_rows(min_row=2, max_row=6, values_only=True):
name, math, chinese, english, avg, grade = row
students.append((name, math, chinese, english, avg, grade))
math_scores.append(math)
chinese_scores.append(chinese)
english_scores.append(english)
all_scores.extend([math, chinese, english])
# 写入统计信息
report_ws['A4'] = '一、基本信息'
report_ws['A5'] = f'学生人数：{len(students)}'
report_ws['A6'] = f'科目数量：3'
report_ws['A7'] = f'总成绩数：{len(all_scores)}'
# 写入平均分统计
report_ws['A9'] = '二、平均分统计'
report_ws['A10'] = f'数学平均分：{sum(math_scores)/len(math_scores):.1f}'
report_ws['A11'] = f'语文平均分：{sum(chinese_scores)/len(chinese_scores):.1f}'
report_ws['A12'] = f'英语平均分：{sum(english_scores)/len(english_scores):.1f}'
report_ws['A13'] = f'总平均分：{sum(all_scores)/len(all_scores):.1f}'
# 写入最高分最低分
report_ws['A15'] = '三、最高分与最低分'
report_ws['A16'] = f'最高分：{max(all_scores)}'
report_ws['A17'] = f'最低分：{min(all_scores)}'
# 写入等级分布
report_ws['A19'] = '四、等级分布'
grade_count = {}
for name, math, chinese, english, avg, grade in students:
if grade in grade_count:
grade_count[grade] += 1
else:
grade_count[grade] = 1
row_num = 20
for grade, count in grade_count.items():
report_ws[f'A{row_num}'] = f'{grade}：{count}人'
row_num += 1
# 写入学生明细
report_ws[f'A{row_num+1}'] = '五、学生成绩明细'
row_num += 2
report_ws[f'A{row_num}'] = '姓名'
report_ws[f'B{row_num}'] = '数学'
report_ws[f'C{row_num}'] = '语文'
report_ws[f'D{row_num}'] = '英语'
report_ws[f'E{row_num}'] = '平均分'
report_ws[f'F{row_num}'] = '等级'
row_num += 1
for name, math, chinese, english, avg, grade in students:
report_ws[f'A{row_num}'] = name
report_ws[f'B{row_num}'] = math
report_ws[f'C{row_num}'] = chinese
report_ws[f'D{row_num}'] = english
report_ws[f'E{row_num}'] = avg
report_ws[f'F{row_num}'] = grade
row_num += 1
# 保存报告
report_wb.save('成绩分析报告.xlsx')
print("分析报告生成完成！")
动手实践
今天的任务：
统计Excel数据 分析数据规律 生成分析报告
练习1：分析班级成绩
import openpyxl
wb = openpyxl.load_workbook('成绩单_完整版.xlsx')
ws = wb.active
# 分析函数
def 分析成绩(ws):
students = []
for row in ws.iter_rows(min_row=2, values_only=True):
if row[0] isnotNone:
students.append(row)
# 计算各项统计
math_total = sum(s[1] for s in students)
chinese_total = sum(s[2] for s in students)
english_total = sum(s[3] for s in students)
math_avg = math_total / len(students)
chinese_avg = chinese_total / len(students)
english_avg = english_total / len(students)
# 找出最高分学生
max_math_student = max(students, key=lambda x: x[1])
max_chinese_student = max(students, key=lambda x: x[2])
max_english_student = max(students, key=lambda x: x[3])
print("班级成绩分析：")
print("=" * 50)
print(f"学生人数：{len(students)}")
print(f"数学平均分：{math_avg:.1f}")
print(f"语文平均分：{chinese_avg:.1f}")
print(f"英语平均分：{english_avg:.1f}")
print(f"数学最高分：{max_math_student[0]}，{max_math_student[1]}分")
print(f"语文最高分：{max_chinese_student[0]}，{max_chinese_student[2]}分")
print(f"英语最高分：{max_english_student[0]}，{max_english_student[3]}分")
分析成绩(ws)
wb.close()
本章小结
今天学到了什么？
可以统计Excel数据 可以分析数据规律 可以筛选数据 可以生成分析报告
明天预告：
明天我们将学习Word文档处理！
练习题
如何计算一列数据的平均值？ 如何筛选出符合条件的数据？ 如何生成简单的分析报告？
学习心得
今天的学习心得：_______________
遇到的问题：_______________
明天要重点学习：_______________