高中信息技术必修一《数据与数据的处理》教学设计：基于真实情境的表格数据清洗与可视化实践

高中信息技术必修一《数据与数据的处理》教学设计：基于真实情境的表格数据清洗与可视化实践一、教材与学情分析浙教版高中信息技术必修一第一章“数据与计算”第四节“常用表格数据的处理”，是连接初中电子表格基础操作与高中数据科学核心素养的关键桥梁。教材以“某校运动会报名统计”“气象数据分析”“图书借阅记录”为主线，串联数据获取、清洗、分析、可视化全流程。核心知识点聚焦于结构化数据的特征识别、脏数据的特征判别与处理策略、透视表多维分析逻辑、条件格式与图表的动态呈现。学生经初中学业水平考试，普遍掌握单元格格式设置、简单函数公式（SUM、AVERAGE、IF）、基础图表生成等操作技能。但存在三类典型认知偏差：一是“工具至上”，将电子表格视为排版工具，忽视数据结构设计；二是“清洗缺位”，面对真实场景中的合并单元格、隐藏字符、数据类型不一致、异常值等“脏数据”无从下手；三是“分析浅表”，透视表仅停留在行列拖拽，缺乏“度量值”“维度”“粒度”建模思维，难以支撑多维度业务洞察。二、核心素养导向的教学目标1.信息意识：能在真实情境中识别结构化与非结构化数据边界，判断数据源可靠性，建立“数据即资产”的安全合规意识。2.计算思维：掌握数据清洗的标准化流程（字段拆分、类型统一、缺失值填补、异常值修正），构建“宽表转长表”“一维转二维”的数据重塑模型，利用透视表实现“切片切块钻取”多维分析。3.数字化学习与创新：迁移Pythonpandas库核心逻辑（read_csv,dropna,pivot_table），对比代码与界面操作异同，设计动态仪表盘原型，解决真实决策问题。4.信息社会责任：遵循《数据安全法》《个人信息保护法》，匿名化处理敏感字段，规范引用开放数据源，输出可复现的分析报告。三、重难点突破策略重点：结构化数据规范建立（首行表头、无合并单元格、一列一类型）、PowerQuery/文本分列/函数组合（TRIM,CLEAN,SUBSTITUTE,TEXTSPLIT）清洗脏数据、透视表计算字段与分组分析。难点：非标准表头多行合并还原为单行字段、文本型数值批量转换数值型、基于业务逻辑的异常值界定与修正策略、透视表“显示值为”进阶计算（同比、环比、占比、累计）。四、教学过程设计（共6课时）（一）第一课时：情境导入与数据“体检”——识别结构化数据规范1.真实情境引入投屏展示某区教育局下发的“20232024学年高中学生体质健康测试汇总表”（.xlsx）。表件特征：前三行为合并单元格标题，第四行为空行，第五行含“班级、姓名、性别、身高(cm)、体重(kg)、肺活量(ml)、50米跑(秒)、立定跳远(cm)、坐位体前屈(cm)、引体向上/仰卧起坐(个）”十列表头，第六行起为数据，但存在：A列班级缺失（依靠合并单元格暗示）、E列体重混入“kg”后缀文本、H列立定跳远含空值与“缺考”文本、I列坐位体前屈含负数文本“－12.5”（全角符号）、末行含汇总行“平均值”。2.思维可视化任务：数据“体检报告”分组协作，学生在协作文档（飞书文档/腾讯文档）填写：|检测维度|发现问题|违反规范|潜在风险|修正方案||:|:|:|:|:||表头结构|前3行合并标题，第4行空行，第5行为有效表头|首行非字段名，存在合并单元格|筛选/透视表失效，无法识别字段|删除前4行，取消合并，首行定为字段名||字段原子性|“体重”列含单位“kg”、“身高”含“cm”|一列多属性（数值+单位）|文本分列/替换删除单位，列名含单位||数据类型一致性|“立定跳远”混数值与“缺考”文本；“坐位体前屈”全角负号|列类型非原子，文本污染数值|缺失值标准化为空/NaN，全角转半角，文本转数值||记录完整性|班级列利用合并单元格填充|记录不自包含，排序后信息丢失|向下填充班级，取消合并||污染行|末行“平均值”混入明细数据|统计行参与明细计算|删除或移至独立工作表|3.核心概念建模：整洁数据三原则引导学生归纳HadleyWickham整洁数据标准：①每个变量是一列（Eachvariableformsacolumn）。②每个观测是一行（Eachobservationformsarow）。③每个值是一个单元格（Eachcellisasinglevalue）。对比“宽表”（学科分列：语文、数学、英语）与“长表”（字段：学科、成绩），演示透视表切换宽长表（数据→透视表→行:姓名,列:学科,值:成绩→取消汇总→表格式设计→为选项卡显示报表）。明确：清洗目标是产出“整洁长表”供分析。4.课堂练习：规范化“图书借阅流水账”提供原始流水账：单元格内“《三体》刘慈欣科幻2024/03/15”。要求：文本分列（固定宽度/分隔符空格），处理书名含空格问题（公式法：`=TEXTSPLIT(A2,,"《","》",,1)`配合`TEXTAFTER/TEXTBEFORE`），建立书名、作者、分类、借阅日期四字段标准表。引导发现`TEXTSPLIT`动态数组优势：源数据更新，结果自动溢出，无需下拉填充。（二）第二课时：深度清洗——函数与PowerQuery双轨并行5.函数组合拳：标准化“脏”字段针对体测表痛点，现场编写并解析公式：①去除不可见字符：`=CLEAN(TRIM(E2))`解释：TRIM删除首尾空格（含），CLEAN删除不可打印字符（ASCII031）。②批量剥离单位转数值：`=SUBSTITUTE(E2,"kg","")`或`=VALUE(TEXTBEFORE(E2,"kg"))`。双重否定``强制文本转数值，VALUE显式转换，TEXTBEFORE更稳健处理无单位情况。③全角转半角修正负号：`=NUMBERVALUE(SUBSTITUTE(I2,"－",""))`。NUMBERVALUE支持自定义小数点/组分隔符，兼容性强于VALUE。④班级向下填充（动态数组）：`=TOCOL(IF(A2:A100<>"",A2:A100,NA()),3)`配合`SCAN`或相对引用`=IF(A2="",B1,A2)`下拉。讲解动态数组`=LET(rng,A2:A100,SCAN("",rng,LAMBDA(a,v,IF(v="",a,v))))`单公式解决百万行填充，不污染撤销栈。6.PowerQuery(获取和转换)：可复现的清洗流水线演示：数据→获取数据→自文件→自工作簿→PowerQuery编辑器。步骤记录面板（AppliedSteps）即代码日志：```powerqueryletSource=Excel.Workbook(File.Contents("体测原始.xlsx"),null,true),Sheet1=Source{[Item="Sheet1",Kind="Sheet"]}[Data],RemovedTop=Table.Skip(Sheet1,4),//删除前4行Promoted=Table.PromoteHeaders(RemovedTop,[PromoteAllScalars=true]),//首行升级为表头ChangedType=Table.TransformColumnTypes(Promoted,{{"班级",typetext},{"姓名",typetext},{"性别",typetext},{"身高",typenumber},{"体重",typetext},{"肺活量",Int64.Type},{"50米跑",typenumber},{"立定跳远",typetext},{"坐位体前屈",typetext},{"引体向上",typetext}}),CleanWeight=Table.TransformColumns(ChangedType,{{"体重",eachNumber.From(Text.BeforeDelimiter(_,"kg")),typenumber}}),CleanJump=Table.TransformColumns(CleanWeight,{{"立定跳远",eachif_="缺考"thennullelseNumber.From(_),typenullablenumber}}),CleanReach=Table.TransformColumns(CleanJump,{{"坐位体前屈",eachNumber.From(Text.Replace(_,"－","")),typenumber}}),FillClass=Table.FillDown(CleanReach,{"班级"}),RemovedTotal=Table.SelectRows(FillClass,each[姓名]<>"平均值"and[姓名]<>null)inRemovedTotal```重点讲解：`Table.TransformColumnTypes`类型强制转换报错处理（忽略错误/替换错误）、`Table.FillDown`填补合并单元格遗留空值、步骤重命名（如“删除标题行”、“体重去单位”）构建可读脚本。点击“关闭并上载到”→“仅创建连接”→“添加到数据模型”，为后续透视表奠基。7.对比迁移：Pythonpandas逻辑映射展示JupyterNotebook代码片段，建立操作代码心智映射：```pythonimportpandasaspddf=pd.read_excel("体测原始.xlsx",header=4)header=4对应跳过前4行df.columns=["班级","姓名","性别","身高","体重","肺活量","50米跑","立定跳远","坐位体前屈","引体向上"]df["体重"]=df["体重"].str.replace("kg","").astype(float)df["立定跳远"]=pd.to_numeric(df["立定跳远"].replace("缺考",pd.NA))df["坐位体前屈"]=df["坐位体前屈"].str.replace("－","").astype(float)df["班级"]=df["班级"].ffill()forwardfill向下填充df=df[df["姓名"]!="平均值"].dropna(subset=["姓名"])df.to_excel("体测清洗后.xlsx",index=False)```引导学生对比：PQ步骤即pandas链式调用，M语言`each_`对应lambda`x:x`，核心均为“数据帧”流式变换。（三）第三课时：透视表建模——从“会拖拽”到“懂建模”8.数据模型接入插入→透视表→选择“将此数据添加到数据模型”。解释：数据模型基于VertiPaq列式存储引擎，压缩率高，支持DAX计算，突破1048576行限制，为PowerBI迁移铺路。9.维度与度量值建模实战任务：生成“各班级、性别维度的体测项目平均分、及格率、优秀率看板”。字段列表区操作：行区：班级、性别（层级钻取）列区：体测项目（需先将宽表“逆透视”为长表：PowerQuery中选中项目列→转换→逆透视列→属性列命名“项目”，值列命名“成绩”）值区：成绩→值字段设置→汇总方式：平均值→数字格式：保留1位小数→重命名“平均分”。新建度量值（DAX入门）：`及格率:=DIVIDE(CALCULATE(COUNTROWS('体测'),'体测'[成绩]>=60),COUNTROWS('体测'))`格式：百分比。`优秀率:=DIVIDE(CALCULATE(COUNTROWS('体测'),'体测'[成绩]>=90),COUNTROWS('体测'))`。讲解：度量值随上下文动态计算，非存储列，节省内存。CALCULATE修改筛选上下文是DAX核心。10.“显示值为”进阶：同比环比与累计模拟“月度借阅量”数据（年、月、借阅量）。透视表行区放年、月，值区放两次“借阅量”。第二个“借阅量”右键→显示值为→“同比%（按年）”/“环比%（按月）”/“按字段汇总累计（月）”。原理剖析：同比=(本期值去年同期值)/去年同期值。透视表自动按层级匹配。若数据缺失月份，需在源数据补全零值行或使用DAX`TOTALYTD`/`SAMEPERIODLASTYEAR`精确控制。11.切片器与时间轴联动仪表盘插入切片器：班级、性别、项目。插入时间轴：测试日期（需源数据含日期列）。设置报表连接，实现多图表联动筛选。格式化：切片器列数4、按钮大小调整、时间轴滚动条样式。输出：无代码动态仪表盘原型。（四）第四课时：条件格式与图表——让数据“说话”12.条件格式三层递进基础：高亮“体重”异常值（<30或>100）。公式：`=OR($E2<30,$E2>100)`绝对引用列、相对引用行。进阶：数据条可视化“肺活量”分布。规则管理→显示数据条→最小值/最大值设为“数值”2000/6000，颜色渐变，隐藏数值仅显示条形。高级：色阶实现“热力图”矩阵。选中透视表平均分区域→色阶→三色：红(低)黄(中)绿(高)。结合“图标集”三角箭头标识同比增减。13.组合图表设计：双轴与动态标题任务：展示“各班级平均分（柱状）与及格率（折线）”对比。步骤：选中透视表数据→插入→组合图表→平均分：簇状柱形图（主轴）、及格率：带标记折线图（次轴）。次轴格式：最小值0，最大值1，显示百分比。动态图表标题：单元格G1输入`=Slicer_班级&"体测成绩分析"`（需切片器连接单元格或用CUBE函数获取选中项），图表标题框选=$G$1。美化：删除网格线、图例下移、数据标签显示数值、配色遵循WCAG2.1AA对比度标准（色盲友好色板：0072B2,D55E00,009E73）。（五）第五课时：项目实战——“校园图书借阅行为分析”完整闭环14.项目启动与角色分工分组4人：数据工程师（清洗建模）、分析师（透视建模）、可视化设计师（仪表盘美化）、产品经理（需求文档、汇报PPT）。数据集：某校图书馆导出原始借阅记录3万行（含：借阅证号、书名、作者、ISBN、分类号、借阅时间、归还时间、馆藏地、是否逾期、读者类型、年级、班级）。故意植入：ISBN科学计数法显示、分类号层级编码（如I247.5）、借阅/归还时间为文本格式、逾期天数缺失、读者类型编码不一（1/学生、2/教师、S/学生）。15.全流程实施清单阶段一数据理解（10min）：字段词典编制、数据画像（PowerQuery查看列分布、列质量、列轮廓）。阶段二清洗建模（20min）：•ISBN：文本格式导入或`=TEXT(A2,"0")`还原。•时间列：`=DATEVALUE(TEXTBEFORE(B2,""))`+`=TIMEVALUE(TEXTAFTER(B2,""))`拆分日期时间，或PQ`DateTime.FromText`。•逾期天数：`=IF([归还时间]>"",[归还时间][借阅时间],TODAY()[借阅时间])`计算列。•分类解析：`=LEFT([分类号],1)`提取一级大类（I文学、T工业技术等），建立维度表映射中文名。•读者类型标准化：`=SWITCH([类型],"1","学生","2","教师","S","学生","未知")`。•逆透视：无需，源已为长表事实表。建立星型模型：事实表(借阅记录)+维度表(图书维度、读者维度、时间维度、馆藏地维度)。PQ中引用查询去重生成维度表，加载到数据模型建立一对多关系。阶段三多维分析（15min）：核心指标体系：•借阅总量(DistinctCount借阅证号)•人均借阅量•逾期率•热门图书TOP10(TOPN筛选)•各年级/类别偏好矩阵(透视表：行年级、列一级分类、值借阅量/人均)•借阅时间热力图(行小时、列星期、值借阅量→条件格式色阶)阶段四仪表盘交付（10min）：布局：左侧切片器（年级、类别、时间粒度）、顶部KPI卡片、中部趋势图（月度借阅量同比）、右部热力图、底部明细表（钻取明细）。导出：另存为.xlsx，或发布到PowerBIService（演示账号）体验云端协作。16.成果汇报与同伴评审各组3分钟汇报：业务问题→清洗关键决策→核心发现（如：高三学生借阅量下降40%，工科类书籍逾期率最高、周五晚高峰明显）→改进建议（延长周五开馆、推送逾期预警、采购高三考研资料）。评审量表：数据规范性(20)、清洗逻辑严密性(20)、分析深度与洞察(30)、可视化表达力(15)、汇报沟通(15)。（六）第六课时：拓展迁移与伦理反思17.技术迁移：Excel到Python/BI的职业路径现场演示：将PowerQueryM代码复制到PowerBIDesktop高级编辑器，零成本迁移。展示Python自动化脚本：`openpyxl`批量合并50个班级工作表、`pandas`批量清洗、`matplotlib/seaborn`出图、`pythonpptx`自动生成分析报告PPT。强调：Excel是原型验证工具，代码是规模化生产工具，思维模型（清洗建模分析可视化）通用。18.数据伦理与法律红线案例研讨：某生将清洗后含真实姓名、学号、成绩的体测数据上传至GitHub公开仓库用于作业展示。讨论焦点：•《个人信息保护法》第二十六条：处理敏感个人信息需单独同意。•《数据安全法》第二十一条：重要数据出境安全评估。•最小化原则：分析仅需“班级、性别、成绩”，姓名、学号必须脱敏（哈希/掩码/泛化）。•知识产权：图书借阅数据归学校所有，未经授权不得对外公开。实操演示：Excel内置“检查文档”→“检查隐藏内容”删除隐藏行/列/批注；PowerQuery中`Table.TransformColumns`对姓名列应用`Text.Start(_,1)&""`掩码；输出报表前执行“最终检查清单”。19.核心素养自我评估量表学生扫码填写电子量表（李克特5级）：•我能独立完成从脏数据到整洁数据的标准化清洗流程设计。•我能解释透视表“值区”与“度量值”的区别，并编写简单DAX。•我能根据分析目的选择合适的图表类型并优化视觉编码。•我能识别数据处理中的隐私风险并采取脱敏措施。•我能将Excel分析流程迁移至Python/PowerBI环境。五、教学评价体系设计1.过程性评价（60%）•每课时“出口票”微测验（雨课堂/学习通）：单选判断清洗函数选型、透视表上下文理解、图表误读辨析。•PowerQuery步骤记录导出（.txt）提交，评价步骤命名规范性、逻辑闭环性。•协作文档贡献度（版本历史）、小组互评分。2.终结性评价（40%）•实操考核（45min）：提供1万行原始电商订单数据（含退单、缺省值、日期格式混杂），要求：清洗建模、建立“月度品类销售额/退单率/客单价”透视表、制作动态仪表盘、导出PDF报告。•迁移创新加分项：提交Python自动化清洗脚本或PowerBI.pbix文件，并在报告中阐述“若数据量增至1亿行，Excel方案瓶颈及迁移架构”。六、教学反思与迭代优化1.脚手架递进设计：首课时“体检表”直观暴露痛点，第二课时双轨教学（函数即时反馈+PQ工程化）照顾不同基础学生，第三课时“逆透视建模度量值”打通任督二脉，项目课时实现知识迁移内化。实测：引入PQ后，学生清洗3万行数据耗时从2课时压缩至20分钟，错误率降低80%。2.真实性与认知负荷平衡：原始数据“脏”度校准至关重要。过脏（如嵌套JSON、严重编码