《Excel项目进度：燃尽图与任务完成趋势》

《Excel项目进度：燃尽图与任务完成趋势》完整实战手册一、燃尽图原理：剩余工作量vs时间1.1什么是燃尽图（BurndownChart）燃尽图是敏捷开发中最核心的可视化工具之一，它回答两个根本问题："我们还剩多少工作没做？"以及"按当前速度，我们什么时候能做完？"坐标轴定义：X轴（横轴）：时间，通常是一个迭代（Sprint）的日历天数（Day1→Day10）Y轴（纵轴）：剩余工作量，单位可以是故事点（StoryPoints）、工时（Hours）或任务数（TaskCount）三条核心线条：线条颜色建议数学本质敏捷意义理想线灰色虚线线性递减完美情况下的消耗基准实际线蓝色实线每日实际剩余团队真实的交付节奏预测线橙色点划线实际线的线性回归延伸基于当前速度的完工预测1.2燃尽图的四种健康状态解读状态A：理想燃尽（实际线紧贴理想线）表现：实际线呈阶梯状均匀下降，在迭代最后一天触及0意义：团队估算精准，节奏稳定，风险极低注意：极少出现，不必强求状态B：提前完成（实际线提前触及0）表现：第7天已燃尽，后3天保持0意义：团队可能过度承诺（Under-commitment），下一个迭代可适当增加工作量风险：也可能是需求被砍，需确认范围未变状态C：延期风险（实际线持续高于理想线）表现：实际线平缓，与理想线开口越来越大意义：团队速度低于预期，存在延期风险行动：ScrumMaster应介入，识别阻塞（Blockers）状态D：范围蔓延（实际线不降反升）表现：实际线在某天后向上跳升，之后继续下降意义：迭代中途新增需求或发现隐藏工作量行动：立即与ProductOwner确认范围，必要时终止迭代或移出低优先级需求1.3Excel实现燃尽图的优势与边界优势：零成本，无需采购Jira/AzureDevOps等工具完全可定制，适合中小团队或临时项目离线可用，适合保密环境或外网隔离场景边界（审核声明）：本手册所述Excel方案适用于轻量级敏捷跟踪、小型团队（5-9人）、临时项目或Jira等系统的补充分析。它不替代专业项目管理软件在大型组织、复杂依赖管理、自动化工作流、企业级权限控制等方面的核心功能。当团队规模超过15人或项目存在跨团队依赖时，建议迁移至Jira、AzureDevOps、M等专业工具。二、每日记录计划剩余工时和实际剩余2.1建立"迭代跟踪"工作表新建Excel工作簿，创建第一个工作表命名为"迭代跟踪"。这是整个燃尽图的数据源。标准表结构（以10天迭代为例）：行ABCDEFG1迭代信息2迭代名称迭代13开始日期2026-06-014结束日期2026-06-145迭代天数106总工作量80故事点78日期Day序号计划剩余实际剩余当日完成范围变更备注92026-06-010808000迭代启动102026-06-021727550112026-06-03264687012..................2.2关键字段公式设计B列（Day序号）：手动输入0到N，或公式自动生成=B8+1//从第9行开始，向下填充C列（计划剩余）：理想线的基础数据，线性递减//C9单元格（Day0）=总工作量（引用B6）

=$B$6

//C10单元格（Day1）及向下

=$B$6-($B$6/$B$5)*B10公式解析：总工作量-(总工作量/迭代天数)×当前Day序号。结果向下取整：=ROUND($B$6-($B$6/$B$5)*B10,0)D列（实际剩余）：手工录入，每日站会后由ScrumMaster更新规则：只统计已完全完成（Done）的任务剩余工作量未完成的工作，即使已部分开发，仍按原估算计入剩余E列（当日完成）：自动计算前一日到当日的工作量差值//E10单元格（Day1）

=C9-D10+E9//前日计划剩余-当日实际剩余+前日范围变更（后续解释）更简洁的版本（仅看实际剩余的变化）：=D9-D10+F10//前日实际剩余-当日实际剩余+当日新增若结果为负，表示当日完成量超过预期（或存在范围缩减）。F列（范围变更）：手工录入，记录当日新增或移除的工作量正值：新增需求、发现技术债务、任务拆分后工作量增加负值：需求取消、任务移出迭代0：无变更实际剩余调整逻辑：当F列有值时，D列应反映"加入变更后的新总剩余"例如Day3新增10点，原实际剩余60，则新实际剩余=60+10=70，当日完成7点，则录入D列=63。2.3数据录入规范与防错设计数据验证设置：选中D列（实际剩余）和F列（范围变更）【数据】→【数据验证】→【自定义】公式：=ISNUMBER(D9)确保只能输入数字条件格式预警（异常值高亮）：选中D列→【条件格式】→【新建规则】→【使用公式】公式：=D9>C9（实际剩余>计划剩余）格式：浅红色填充（表示进度落后）日期自动填充：//A10单元格（Day1日期）

=WORKDAY.INTL($B$3,B9,1)//跳过周末，B9为Day序号若团队周六也工作，可调整参数或手动录入。三、自动绘制理想线与实际线3.1创建基础燃尽图（折线图）操作步骤：选中"迭代跟踪"表中B8:D18（Day序号+计划剩余+实际剩余）【插入】→【图表】→【散点图】→带平滑线的散点图（或折线图）初始图表生成后，进行以下优化：理想线格式化：选中理想线（灰色系列）→右键【设置数据系列格式】线条→颜色：#B4B4B4（浅灰），宽度：1.5磅，短划线类型：虚线标记→标记选项：无（理想线不需要数据点）实际线格式化：选中实际线（蓝色系列）线条→颜色：#4472C4（深蓝），宽度：2.5磅标记→标记选项：内置圆形，大小：6，填充：白色，边框：蓝色1.5磅添加数据标签（显示剩余数值），位置：靠上3.2添加"已完成"柱状图（组合图）为直观展示每日交付，可将E列（当日完成）添加为柱状图：操作步骤：右键图表→【选择数据】【添加】→系列名称：当日完成→系列值：选中E9:E18右键新系列→【更改系列图表类型】将"当日完成"改为簇状柱形图，并勾选次坐标轴次坐标轴最大值设为与主坐标轴相同（如80），保持视觉一致柱形填充：绿色（#70AD47），透明度：50%，边框：无3.3图表美化（发布会/汇报级）背景与网格：图表区→填充：无填充（使用幻灯片/工作表背景）绘图区→填充：无填充网格线→水平网格线：浅灰色虚线，垂直网格线：无坐标轴：X轴（Day）：边界最小0，最大10（或迭代天数），主要刻度1Y轴（剩余工作量）：边界最小0，最大自动（或总工作量×1.2），标题："剩余故事点"次坐标轴（若存在）：标签位置：无（隐藏）标题与图例：图表标题：动态引用迭代名称="迭代燃尽图-"&迭代跟踪!$B$2图例位置：底部，字体：微软雅黑10pt添加"今日"垂直参考线（动态）：假设今天是Day4，添加一条垂直线标记当前进度：新增辅助列"今日线"：除Day4外均为#N/A，Day4=Y轴最大值=IF(B9=$B$11,$B$6*1.2,NA())//假设B11是今日Day序号添加到图表中，改为带数据标记的折线图，无线条，标记为垂直线或添加误差线更简单的做法：插入形状直线，每日手动移动（汇报时）3.4理想线公式详解（自动适配任意迭代）若迭代天数不固定，理想线应自动计算：在辅助列中（如H列）建立"理想线Y值"：//H9（Day0）

=$B$6

//H10（Day1）向下填充

=MAX($B$6-($B$6/$B$5)*B10,0)使用MAX确保最后一天不会为负数（即使提前完成，理想线也止于0）。四、预测完成日期（斜率计算）4.1基于实际速度的线性预测核心假设：团队将以过去N天的平均速度继续工作。速度（Velocity）计算：每日完成的故事点//平均速度（排除范围变更影响）

=AVERAGE(E10:E18)//当日完成列的平均值剩余天数预测：=ROUND(D18/AVERAGE(E10:E18),1)//当前实际剩余/平均速度（假设D18是最后一天已录入的实际剩余）预测完成日期：=TODAY()+(D18/AVERAGE(E10:E18))或使用工作日计算：=WORKDAY.INTL(TODAY(),ROUNDUP(D18/AVERAGE(E10:E18),0),1)4.2使用LINEST进行线性回归预测（更严谨）Excel的LINEST函数可计算实际线的斜率和截距，从而得到预测线。操作步骤：在辅助区域（如J9:K10）输入数组公式：=LINEST(D9:D18,B9:B18,TRUE,FALSE)（选中2×2区域，输入公式后按Ctrl+Shift+Enter）结果解读：J9：斜率（Slope）——通常为负值，表示每日减少的工作量K9：截距（Intercept）——Day0时的理论剩余J10：斜率的标准误差K10：截距的标准误差预测线Y值公式（在辅助列L中）：=$J$9*B9+$K$9//斜率×Day+截距向下填充，得到预测线的每个点。将L列添加到图表中，格式化为橙色点划线，宽度1.5磅。预测完成日期（预测线触及0的日期）：=ROUND(-$K$9/$J$9,0)//截距/斜率绝对值=预计总天数若结果>迭代天数，则存在延期风险。4.3预警公式（自动判断健康度）在"迭代跟踪"表顶部添加状态看板：指标公式状态判断当前延期风险=IF(LINEST预测天数>B5,"高风险","正常")条件格式红/绿速度偏差=TEXT((实际平均速度-理想速度)/理想速度,"+0.0%;-0.0%;0%")正=快，负=慢建议动作=IF(延期风险="高风险","需移除低优先级需求","保持节奏")文本提示理想速度公式：=$B$6/$B$5//总工作量/迭代天数五、新增需求对燃尽图的影响5.1范围蔓延的可视化挑战在敏捷实践中，迭代中途新增需求（ScopeChange）是常态而非例外。Excel燃尽图必须能诚实反映这一现实，而非掩盖问题。错误做法：将新增工作量直接算入"当日完成"，使实际线看起来下降很快，掩盖范围增长。正确做法：在图表中明确区分"原范围燃尽"和"范围变更"。5.2实现"范围变更线"数据准备：在"迭代跟踪"表中新增辅助列G（范围变更，已在第二节定义），并增加一列"调整后理想线"：I列（调整后理想线）：包含范围变更后的新基线//I9（Day0）

=$B$6

//I10（Day1）

=I9-($B$6/$B$5)+G10//前日调整后剩余-理想日消耗+当日变更或更简洁：=$B$6+SUM($G$9:G10)-($B$6/$B$5)*B10这表示：原始总工作量+累计变更-理想消耗。实际线（调整后）：D列本身已包含范围变更（因为每日录入时已将新增计入剩余），所以D列就是"调整后实际线"。图表实现：理想线（原始）：灰色虚线（C列）——展示最初承诺理想线（调整后）：灰色点线（I列）——展示变更后的新基准实际线：蓝色实线（D列）——真实进度范围变更标记：在发生变更的日期，添加误差线或数据标签显示+X点5.3范围变更瀑布图（补充分析）若某次迭代范围变更频繁，建议增加"范围变更瀑布图"：数据结构：日期原估算新增移除净变更当前基线Day08000080Day3015-51090Day5080898图表类型：瀑布图（Excel2016+内置）选中数据→【插入】→【瀑布图】设置"总计"为基线值用于向ProductOwner和团队透明展示变更历史5.4敏捷实践建议（非Excel操作）当Excel燃尽图显示范围蔓延时：冻结范围：与PO协商，从迭代待办列表（SprintBacklog）中移出等量的低优先级任务，保持总工作量可控延长迭代：仅在极端情况下考虑，通常违反敏捷时间盒原则记录技术债务：若新增来自技术债务暴露，应在回顾会议（Retrospective）中讨论估算精度六、累计流量图（CFD）展示各阶段任务6.1CFD原理与价值累计流量图（CumulativeFlowDiagram,CFD）是Kanban方法的核心工具，它展示：各阶段任务数量随时间的累积在制品（WIP）的厚度（某阶段两条线之间的垂直距离）前置时间（LeadTime）的趋势（从进入到完成的水平距离）与燃尽图的互补关系：燃尽图：回答"还剩多少？"CFD：回答"任务卡在哪个阶段？"6.2建立"CFD数据源"工作表新建工作表命名为"CFD数据"，记录每日各阶段任务数：日期Day待办分析中开发中测试中已完成已发布06-010122530006-021103631006-032846420录入规则：每日站会后统计各看板列的任务卡片数数字为该时刻的快照，非累计值各阶段之和=迭代总任务数（若范围未变）6.3绘制CFD（堆积面积图）操作步骤：选中"CFD数据"表中A1:G11（日期+各阶段列）【插入】→【图表】→【面积图】→堆积面积图初始图表生成后优化：系列顺序调整（关键！）：右键图表→【选择数据】确保系列顺序为：已发布→已完成→测试中→开发中→分析中→待办（从上到下）这样图表底部是"已完成"（稳定增长的基线），顶部是"待办"（逐渐收缩）配色方案（看板标准色）：阶段颜色含义待办#F2F2F2（浅灰）未启动分析中#FFC000（琥珀）进行中开发中#5B9BD5（蓝）核心工作测试中#ED7D31（橙）验证中已完成#70AD47（绿）已验收已发布#4472C4（深蓝）已上线格式化：图例：顶部，显示系列名称数据标签：无（CFD不需要精确数值标签，看趋势即可）网格线：水平浅灰虚线标题："累计流量图-各阶段任务分布"6.4从CFD读取关键指标在制品（WIP）厚度：在Day5处画垂直线，测量"开发中"与"测试中"两条线之间的垂直距离：=开发中_Day5-测试中_Day5若持续>团队WIP限制（如3个任务），则该阶段存在瓶颈。平均前置时间估算：观察"待办"线与"已完成"线在水平方向上的平均距离。Excel中可通过辅助计算：//假设任务从Day0进入待办，DayN完成

=SUMPRODUCT(已完成列)/COUNTIF(已完成列,">0")//平均完成天数瓶颈预警（条件格式）：在"CFD数据"表中，若某阶段连续3天增长而其他阶段停滞：=IF(AND(开发中_Day3>开发中_Day2,开发中_Day2>开发中_Day1,测试中_Day3=测试中_Day2),"测试瓶颈","")七、团队速度估算与迭代规划7.1速度（Velocity）追踪表新建工作表"速度追踪"，记录历史迭代表现：迭代开始日期结束日期计划故事点完成故事点完成率团队人数可用工作日速度/人天迭代12026-05-012026-05-14807290%5501.44迭代22026-05-152026-05-28858094%5501.60迭代32026-05-292026-06-11908594%6601.42关键公式：完成率：=完成故事点/计划故事点速度/人天：=完成故事点/(团队人数*可用工作日/迭代天数)或直接=完成故事点/可用工作日7.2滚动平均速度（预测下期容量）使用最近3个迭代的平均速度作为下期规划依据：=AVERAGE(D2:D4)//D列为完成故事点置信区间（80%概率）：使用CONFIDENCE.NORM或保守取最低值：=MIN(D2:D4)//保守承诺

=AVERAGE(D2:D4)//乐观承诺

=PERCENTILE.INC(D2:D4,0.3)//30%分位数，偏保守7.3迭代规划计算器在"速度追踪"表顶部建立规划区：下期规划

━━━━━━━━━━━━━━━━━━━━

团队可用人数：[5]人

迭代工作日：[10]天

预计请假/会议：[20%]

历史平均速度：[82.3]点/迭代

━━━━━━━━━━━━━━━━━━━━

建议承诺范围：[66-74]故事点

保守承诺（80%）：[66]故事点

标准承诺（50%）：[74]故事点公式：//有效容量

=团队人数*迭代工作日*(1-请假比例)

//建议承诺下限（保守）

=历史平均速度*0.8

//建议承诺上限（标准）

=历史平均速度*1.07.4速度趋势图图表类型：组合图（柱状图+折线图）柱状图：每个迭代的计划故事点（灰色）vs完成故事点（绿色/红色）折线图：完成率趋势（次坐标轴百分比）条件格式：完成率<80%：柱状图红色完成率80-100%：柱状图绿色完成率>100%：柱状图深蓝（范围可能未控制好）八、多项目并行查看8.1数据架构设计当团队同时负责多个项目（或一个产品多个模块）时，需重构数据结构：新建工作表"项目总览"，结构：项目迭代日期Day计划剩余实际剩余范围变更总工作量项目A迭代106-0105050050项目A迭代106-0214548050项目B迭代106-0103030030项目B迭代106-02127280308.2使用数据透视表筛选操作步骤：选中"项目总览"全表→【插入】→【数据透视表】放置到新工作表"透视分析"行：日期、Day列：项目值：实际剩余（求和）动态图表：基于透视表插入折线图插入切片器：【数据透视表分析】→【插入切片器】→勾选"项目"点击切片器按钮，可快速切换查看单个项目或多个项目对比8.3多项目仪表盘布局新建工作表"管理仪表盘"，使用相机功能（或链接图片）将各项目燃尽图汇总：布局建议（A4横向打印友好）：┌─────────────────────────────────────────┐

│多项目进度总览日期：2026-06-03│

├─────────────────┬───────────────────────┤

│[项目A燃尽图]│[项目B燃尽图]│

│状态：正常│状态：高风险│

│预计完成：06-10│预计完成：06-18│

├─────────────────┴───────────────────────┤

│[汇总CFD-所有项目任务阶段分布]│

├─────────────────┬───────────────────────┤

│团队速度趋势│迭代健康度雷达图│

└─────────────────┴───────────────────────┘相机功能使用：选中项目A燃尽图所在单元格区域【开始】→【复制】→下拉箭头→复制为图片（或早期版本使用相机工具）粘贴到仪表盘，实现"实时联动"（源数据更新时，仪表盘图片同步更新）8.4项目健康度评分卡在仪表盘添加KPI区：项目进度偏差范围稳定性速度趋势健康度建议A-5%稳定↑🟢健康保持B+20%2次变更↓🔴风险回顾阻塞公式：//进度偏差

=(实际剩余-计划剩余)/计划剩余

//健康度

=IF(进度偏差>0.1,"🔴",IF(进度偏差>0,"🟡","🟢"))九、集成Jira/Trello数据的方法9.1从Jira导出数据（CSV）操作步骤：Jira→【Filters】→【Advancedissuesearch】输入JQL：project=PROJANDsprintinopenSprints()点击【Export】→【CSV(Allfields)】或【CSV(Currentfields)】保存CSV文件到本地关键字段映射：Jira字段Excel列用途Issuekey任务ID唯一标识Summary任务名称展示StoryPoints故事点燃尽图Y轴Status状态CFD阶段Sprint迭代筛选Created创建日期前置时间计算Updated更新日期最后活跃Assignee负责人负载分析9.2使用PowerQuery自动刷新（Excel2016+）操作步骤：Excel→【数据】→【获取数据】→【自文件】→【从文本/CSV】选择Jira导出的CSV→【导入】在PowerQuery编辑器中：删除无用列（保留上述关键字段）将"StoryPoints"设为数值类型将"Status"映射为CFD阶段（如"ToDo"→待办，"InProgress"→开发中）使用条件列功能：if[Status]="Done"then"已完成"elseif[Status]="InProgress"then"开发中"else"待办"【关闭并加载】→选择"仅创建连接"或"表"【数据】→【刷新全部】→或设置自动刷新（连接属性中勾选"刷新频率"）刷新自动化：//在ThisWorkbook中添加，打开时自动刷新

PrivateSubWorkbook_Open()

ThisWorkbook.Connections("Query-Jira").Refresh

EndSub9.3从Trello导出数据操作步骤：Trello→打开看板→【...】菜单→【打印和导出】→【导出JSON】使用在线工具或Python脚本将JSON转为CSV（Excel直接解析JSON较复杂）或使用TrelloPower-Up：【Butler】自动化导出至GoogleSheets，再从Sheets链接到Excel简化方案：Trello的导出CSV（需付费Workspace功能）直接获得：卡片名称、列表（阶段）、标签、成员、截止日期9.4字段映射与转换表在Excel中建立"映射表"工作表，处理Jira/Trello状态到本地CFD阶段的转换：原始状态来源系统本地阶段说明ToDoJira待办未启动InProgressJira开发中开发阶段CodeReviewJira测试中归入验证DoneJira已完成验收通过ClosedJira已发布已上线转换公式（使用VLOOKUP）：=VLOOKUP(Jira状态,映射表!$A:$C,3,FALSE)9.5重要声明：Excel的定位本手册所述Jira/Trello集成方案，仅用于将专业工具中的原始数据导入Excel进行补充分析、离线汇报或临时轻量级跟踪。Excel无法替代Jira的以下核心能力：实时协作与评论自动化工作流（Workflow）精细权限控制（IssueLevelSecurity）插件生态（如Zephyr测试管理、Tempo工时）企业级SSO与审计日志建议工作流：日常迭代执行在Jira中完成→每日/每周通过导出CSV在Excel中生成燃尽图/CFD用于汇报→回顾会议后优化下一迭代计划。十、打印迭代报告10.1一页纸迭代报告设计新建工作表"迭代报告"，设计为A4纵向打印布局：═══════════════════════════════════════════════════

迭代回顾报告-迭代3（2026.05.29-06.11）

═══════════════════════════════════════════════════

【燃尽图快照】【关键指标】

[插入燃尽图链接图片]计划故事点：90

完成故事点：85

完成率：94.4%

平均速度：8.5点/天

范围变更：+5点（1次）

预测偏差：-1天

【累计流量图快照】

[插入CFD链接图片]

阶段瓶颈：测试阶段在Day6-8出现堆积，建议下个迭代增加测试资源

【已完成工作】【待改进】

•用户登录模块重构•代码评审周期过长（平均2天）

•支付接口V2对接•需求变更通知不及时

•管理后台仪表盘•技术债务未预留缓冲

【下个迭代计划】

建议承诺：72-80故事点（考虑6月假期因素）

═══════════════════════════════════════════════════10.2打印区域与页面设置操作步骤：选中"迭代报告"中A1:I45区域【页面布局】→【打印区域】→【设置打印区域】【页面布局】→【纸张大小】→A4【方向】→纵向（或横向，若图表较宽）【缩放】→调整为"1页宽×1页高"【页边距】→窄（上下1.27cm，左右1cm）图表打印优化：燃尽图和CFD在各自工作表中调整为无背景填充（图表区格式→填充→无填充）这样在白底报告上打印更清晰线条加粗至2.5磅以上，确保黑白打印可读10.3导出PDF与分发操作路径：【文件】→【导出】→【创建PDF/XPS】选项：勾选"标准（联机发布和打印）"页范围：仅"迭代报告"工作表（点击"选项"选择）文件名：迭代3回顾报告_20260611.pdf邮件正文模板：团队，

附件为迭代3的回顾报告（燃尽图与CFD分析）。

关键结论：

-完成率94.4%，略低于目标，主因是Day7的范围变更

-测试阶段在制品堆积，建议下个迭代WIP限制设为3

请查收，我们将在周五回顾会议上详细讨论。

ScrumMaster10.4报告自动化（一键生成）使用VBA将燃尽图、CFD、数据表复制到报告页：Sub生成迭代报告()

DimwsReportAsWorksheet

DimwsBurnAsWorksheet

DimwsCFDAsWorksheet

SetwsReport=ThisWorkbook.Worksheets("迭代报告")

SetwsBurn=ThisWorkbook.