ARTICLE · 1337864
一张 Excel 管住一个月的生产:5 张表、5300 条公式,全自动甘特图和产出日历
现在做生产管控，低代码和多维表当然更规范：多人协同、权限分级、操作留痕，开箱就有。但今天要拆的这份 计划和进度台账1.xlsx 想说明另一件事——一张单机 Excel，也能做出相当复杂、而且相当自动化的管理表格。
它只有 5 张工作表：生产计划、生产任务单、生产完调度台账、工段产出报表、成品产出日历。没有 VBA、没有宏、没有外部链接，5,300 多条公式把「下计划 → 派工 → 报产 → 汇总判断 → 看板」串成一个闭环。最妙的两处是：甘特图不是图表，是三条条件格式规则；日历里没有一天是硬编码的，换个月份星期自己就对齐了。
对做生产管理的人来说，这份表值得看的不是某个函数怎么写，而是它把「人要填的东西」压到了多薄：全表真正需要手工录入的，只有调度台账里的 5 列。
一句话概括这套结构：主数据和事务数据分家，用任务单编号当主键；所有看板都是对同一份流水账的重新聚合。这也是所有 MES / ERP 的最小原型。
说明｜本文所有公式、单元格区域、条件格式规则与示例数值，均来自对该 xlsx 的只读解析；原文件未作任何修改。公式中已去掉 WPS 保存时自动附加的 _xlfn. / _xlfn._xlws. 前缀，可直接复制使用。
一、先看全貌：五张表，一条数据链
把文件按「谁产生数据、谁消费数据」拆开，结构一下子就清楚了：
数据是这样流动的：生产计划 生成任务单编号 → 生产任务单 按工段把任务分派下去 → 车间每天在 调度台账 报一条产出 → 这条产出同时被计划表的每日网格、任务单的完成数、报表的日期格、日历的累计数读走。
整份表只有两个「总开关」：生产计划!L1（年）和 O1（月）。改这两个格，五张表的标题、日期轴长度、甘特条的参照、日历的起始周一，全部跟着重算。这种「参数集中」的做法，是模板能跨月复用的前提。
二、第一张表：改两个单元格，甘特图自己长出来
2.1 一个标题、一个月首、一条日期轴
公式 ①｜B1 动态标题复制公式
=L1&"年"&O1&"月生产计划"L1 / O1 是手填的年和月。报表与日历的标题也都引用这两格（=生产计划!L1&"年"&生产计划!O1&"月在制品追踪表"），所以全工作簿的月份永远一致。
公式 ②｜P2 本月 1 号复制公式
=DATE($L$1,$O$1,1)把「年、月」两个数字变成一个真正的日期序列值。后面所有日期计算都以它为基准，避免了从文本标题里抠数字（旧版曾用 REGEXP + CONCAT 从「2026年11月生产计划」里提年月，兼容性很差）。
公式 ③｜P3 横向溢出整月日期复制公式
=SEQUENCE(1, DAY(EOMONTH(DATE(YEAR(P2),MONTH(P2),1),0)), DATE(YEAR(P2),MONTH(P2),1), 1)拆开看：EOMONTH(月初,0) 取本月最后一天 → DAY() 得到天数（28/29/30/31）→ SEQUENCE(1, 天数, 月初, 1) 生成 1 行 N 列、步长为 1 的日期序列。一个公式溢出到 P3:AS3，2 月不会多出两格，31 天月也不会少一格。
第 4 行是第 3 行的镜像（P4 = P3、Q4 = Q3 …），用来给表头做「日」格式；第 31 列 AT 则加了空值守卫：
公式 ④｜AT4 / AT5 短月守卫复制公式
=IF(AT3="","",AT3)日期轴最多预留 31 列。短月时 AT3 是空的，AT4 返回空，AT5:AT59 的 SUMIFS 也用 IF(AT$3="","",…) 直接不计算。这就是「预留上限 + 空值守卫」的自适应手法，报表和日历里还会再见到两次。
2.2 主键与缺口：让编号自己生成
公式 ⑤｜C5 任务单编号复制公式
=IFERROR(--IF(AND(E5<>"",N5<>""),TEXT(N5,"yyyymm")&TEXT(ROW()-4,"0000"),""),"")只有「图号 E」和「计划开始时间 N」都填了才生成编号：TEXT(N5,"yyyymm") 取年月 + TEXT(ROW()-4,"0000") 取 4 位流水号（第 5 行 → 0001）。前面的 -- 把文本转成数值，外层 IFERROR 让空行保持空白。于是 2026110001 这种编号既是主键，又自带月份信息。
公式 ⑥｜L5 缺口复制公式
=I5*J5-K5订单台数 × 每台用量 − 期末结存 = 还差多少。M 列计划数量是人工填的，但数据验证限制它必须 ≥ L 列缺口，否则弹「计划量不够」——业务规则被写进了单元格，而不是写在制度里。
公式 ⑦｜P5 每日完成回填复制公式
=SUMIFS(生产完调度台账!$L$3:$L$200,生产完调度台账!$A$3:$A$200,$C5,生产完调度台账!$D$3:$D$200,P$3,生产完调度台账!$B$3:$B$200,$D5)P5:AS59 共 55 行 × 30 列都是这一条公式的变体：按「任务单编号 + 工段 + 当日日期」三个条件，去台账把当天产出取回来。绿色计划条底下叠着实际完成数，一格同时承担了「计划」和「实绩」两种信息。
原理｜注意混合引用：$C5 锁列不锁行（同一行取同一个任务单编号），P$3 锁行不锁列（同一列取同一个日期）。一个公式拖满整个矩形，就成了一张会自动刷新的二维透视表——这正是后面第四张表的核心手法。
2.3 甘特图其实是三条条件格式规则
第一条是灵魂：行方向取 N/O 的起止日期，列方向取第 3 行的日期，两者一夹就是这条任务的计划区间。把它应用到 1705 个格子（55 行 × 31 列），Excel 会为每格判断一次，于是「图表」就长在单元格里了。计划日期一改，横条立刻变长变短；插入删除行也不用重设数据源。
第二条里的 ISNUMBER(P$3) 很容易被忽略：短月时后面几列是空的，空白在 Excel 里会被当成 0 号日期，WEEKDAY 算出来可能是周末，于是空白列被误涂成灰色。加一层数字判断就干净了。
划重点｜配套的三条数据验证：G5:G59 去向只能选「转序 / 成品库」；M5:M59 计划数量 ≥ 缺口（出错标题「计划量不够」）；N5:O59 要求 $M5>0 才允许填日期。把校验放在录入阶段，比事后对账便宜得多。
三、第二张表：选一个工段，只出这个工段的任务单
B2 是一个数据验证下拉（下料 / 焊一 / 焊二 / 新产品 / 涂装 五个工段）。除了这一格，整张任务单没有一个字是手填的。
公式 ⑧｜A4 按工段过滤并重排列序复制公式
=FILTER( CHOOSECOLS(生产计划!$C$5:$O$200, 2,1,3,4,6,5,11,12,13), (IFERROR(FIND($B$2,生产计划!$D$5:$D$200),0)) * (生产计划!$M$5:$M$200>0), "")两层嵌套，逐个说清楚：
CHOOSECOLS 一口气完成两件事：挑列和重排。计划表 C:O 共 13 列，按 2,1,3,4,6,5,11,12,13 重排后，正好对上任务单 A:I 的表头顺序，不需要任何中间辅助列。
过滤条件是两个布尔数组相乘：IFERROR(FIND($B$2, D列),0) 判断工段名是否包含 B2（找不到就归 0），再乘 计划数量>0。原文件里有两单被自动排除：2026110004 属于焊一工段，2026110005 计划数量为 0。缓存结果正好 9 行。
公式 ⑨｜J4 累计完成数复制公式
=IF(B4="","",SUMIFS(生产完调度台账!$L$3:$L$200,生产完调度台账!$A$3:$A$200,B4))按任务单编号回台账汇总产出。配合条件格式 AND($G4>0,$J4>=$G4)，完成数达标的那一行会自动加删除线变灰——又是一个「不用图表、不用 VBA」的可视化。
原理｜FILTER 的第三参数 "" 不能省：没有匹配结果时返回空白而不是 #CALC!。溢出区域下界预留到第 25 行，新增计划时结果自动往下长；公式只在 A4 一格，其余格子是溢出内容，改不动也删不掉。
四、第三张表：全表只录 5 列，其余 8 列查回来
这是整套表最关键的设计。车间报产只需要回答五个问题：哪一单、哪天、做了多少、废了多少、转出多少。图号、零件、工序、工段、去向、计划量、起止日期这些主数据，如果让人再填一遍，迟早和计划表对不上。
公式 ⑩｜B3 等 8 列同构的 VLOOKUP复制公式
=IF($A3="","",IFERROR(VLOOKUP($A3,生产计划!$C$4:$O$200,2,0),""))只换第 3 参数（列序号）就能复制出 8 列：工段=2、图号=3、零件=4、去向=5、工序=6、计划量=11、开始=12、完成=13。外层 IF 让空行保持空白，IFERROR 让查不到的编号显示空而不是 #N/A。
4.1 同一张表右侧：四个条件格做出模糊查询
公式 ⑪｜R5 四条件 FILTER 调度查询复制公式
=FILTER(A3:N200, (IFERROR(FIND(T2,B3:B200),0)) * ← 工段包含 (IFERROR(FIND(V2,E3:E200),0)) * ← 图号包含 IF(X2=0, 1, ← 起始日期留空则放行 (D3:D200>=X2) * (D3:D200<=IF(Z2=0,DATE(9999,12,31),Z2))), "无匹配记录")四个条件相乘：工段模糊包含、图号模糊包含、起始日期、结束日期。
踩坑提醒｜两个容易写错的点：标签在 S2/U2/W2/Y2，输入框是它右边一格 T2/V2/X2/Z2，别对着标签取错单元格；FIND(空,文本) 返回 1，所以条件留空时自动变成「不筛选」，而 IFERROR 把找不到的情况归 0 —— 一放一收，正好构成模糊查询。
五、第四张表：把每天的产出「分配」到格子里，再自己下结论
5.1 先拉出本工段的图号清单
公式 ⑫｜A5 本工段图号清单复制公式
=FILTER(CHOOSECOLS(生产计划!$E$5:$M$59,1,2,4,7,9),生产计划!$D$5:$D$59=$B$2,"本月无计划")从计划表 E:M 里挑 5 列：图号、零件名称、工序、期末结存（当作月初结存）、计划数量，正好对应 A:E 表头。这里用的是精确匹配=$B$2，与任务单里的 FIND 模糊包含不同——报表要按工段严格归集，不能把名字相近的工段算进来。
5.2 日期轴自动停在月末
公式 ⑬｜G3 / H3 月度守卫复制公式
=IF(G3="","",IF(MONTH(G3+1)<>生产计划!$O$1,"",G3+1))G3 是 =DATE(生产计划!$L$1,生产计划!$O$1,1)；H3 起逐格 +1，但每加一天先判断 MONTH(下一天) 是否还等于 O1 的月份——一旦跨月就返回空，后面的格子跟着连锁变空。28/29/30/31 天全自动适配，不需要每月手动删列。第 4 行 =G3 镜像给表头显示「日」。
5.3 三条件分配：SUMIFS 代替透视表
公式 ⑭｜G5 某天 × 某工段 × 某图号的产出复制公式
=IF(OR($A5="",G$3=""),"",SUMIFS(生产完调度台账!$L$3:$L$200,生产完调度台账!$D$3:$D$200,G$3,生产完调度台账!$B$3:$B$200,$B$2,生产完调度台账!$E$3:$E$200,$A5))日期 = 表头 G$3、工段 = $B$2、图号 = $A5，三个维度一锁，台账里那一条产出就自动落到正确的格子。向右拖满 31 列、向下填满行，就是一张打开即最新、不用右键刷新的产出透视表。OR($A5="",G$3="") 让空行和短月的空列不参与计算。
后面三列把这套数字收口：
公式 ⑮｜AN5 自动结论（写成人话）复制公式
=IF(A5=0,"",IF(F5>0,"计划未完成",IF(AL5>E5,"超计划产出或重复录入，请排查","")))不是冷冰冰的数字，而是中文判断：还欠着就说「计划未完成」，产出超过计划数就提示「超计划产出或重复录入，请排查」。原文件里 154-00-00004 这一行计划数为 0 却录了 25 件产出，结论列立刻报出异常——表格自己会查错。
结论｜AN 列还有条件格式按 $AL5>$E5 标红、$F5>0 标灰、AND($F5<=0,$E5<>0) 提示；F 列欠品 >0 着色；周末与今日列沿用第一张表同款规则。三张表的可视化语言是统一的，看表的人不用重新学一遍。
六、第五张表：换个月份，日历自己把星期对齐
这张表最讨喜。它不插入任何日期控件，也不用硬编码的日历数字，42 个日期格全部由一条公式推出来。
公式 ⑯｜B7 本月 1 号所在周的周一复制公式
=DATE(生产计划!L1,生产计划!O1,1)-WEEKDAY(DATE(生产计划!L1,生产计划!O1,1),2)+1WEEKDAY(日期,2) 让周一=1、周日=7。用本月 1 号减去它的星期序号再加 1，就退回那周的周一。2026-11-01 是周日（7），所以 B7 = 11/1 − 7 + 1 = 10/26（周一）。接下来 C7=B7+1 … H7=G7+1、B9=H7+1 …… 一路加到第 6 周，日期天然落在正确的星期列下。
公式 ⑰｜B8 一格里的「累计入库 / 累计计划」复制公式
=SUMIFS(台账!$L$3:$L$200, 台账!$D$3:$D$200,"<="&B7, 台账!$F$3:$F$200,"*"&$I$3&"*", 台账!$H$3:$H$200,"成品库") & "/" & SUMIFS(计划!$M$5:$M$200, 计划!$N$5:$N$200,"<="&B7, 计划!$F$5:$F$200,"*"&$I$3&"*", 计划!$G$5:$G$200,"成品库")两次 SUMIFS 都用 "<="&日期 取累计值（不是当天值），产品名用 "*"&$I$3&"*" 模糊匹配，再用「去向 = 成品库」只算入库；中间 &"/"& 把两个数字拼成 39/15 这样的字符串。
I3 是零件名称下拉，来源直接指向 生产完调度台账!$F$3:$F$200；F5 = 生产计划!$O$1 把月份单独放一格，专门给条件格式用：MONTH(B$7)<>$F$5 时把上月尾、下月头的日期和数值整片压灰。
图中 11/12 那格从 0/0 跳到 39/0、次日变成 39/15，正是原文件缓存里的真实结果——累计入库 39 件、累计计划 15 件。换产品、换月份，42 个日期格和 42 个累计数一起重算，没有一行需要手改。
七、想复刻？按这 8 步搭
- 先定参数
：在生产计划表留两个单元格放年、月（L1 / O1），所有标题、日期轴、日历基准都引用它们，绝不从标题文本里抠数字。 - 建主数据表
：列好订单、工段、图号、零件、去向、工序、台数、台量、结存、计划量、起止日期；用 TEXT(日期,"yyyymm")&TEXT(ROW(),"0000")生成任务单编号当主键。 - 给主键加约束
：编号公式要求「图号 + 开始日期」都填了才生成；计划量用数据验证卡住下限；去向做成下拉。 - 铺日期轴
：用 SEQUENCE + EOMONTH溢出本月天数，预留 31 列并在第 31 列加空值守卫；条件格式画甘特条、周末、今日线。 - 建事务流水表
：只留 5 列手填（编号、日期、产出、废品、转出），其余全部 VLOOKUP查回来；编号列做数据验证下拉，日期列做窗口校验。 - 做派工与查询
： FILTER + CHOOSECOLS按工段派工，右侧再用四条件FILTER做模糊调度查询，条件留空要能放行。 - 做报表与日历
：报表用 SUMIFS(日期 × 工段 × 图号)把流水分配到格；日历用WEEKDAY找周一、逐格 +1，数值行用两次累计SUMIFS拼实绩/计划。 - 补上结论列与异常提示
：欠品、超产、完工，用一列 IF写成中文；再配条件格式着色。表格从「记录数字」升级成「替你判断」。
划重点｜区域范围统一收在 3:200 / 5:59 这类有限区间，不要写 A:A 整列。这份文件有 5,300 多条公式，其中上千条是 SUMIFS；整列引用会让每次重算都扫 100 万行，一改一个卡。
八、值得抄的是结构，不是公式
低代码和多维表现在确实能更规范地完成协同、权限和留痕。但把这套 Excel 的结构看明白，换任何工具都用得上：
- 主数据 / 事务数据分家
：计划表只放「要做什么」，台账只放「实际做了什么」，靠任务单编号关联。这是所有 MES 的最小原型。 - 录入口越薄，数据越准
：能查回来的不让人再填，能算出来的不让人再抄，能用数据验证挡住的错误别留到对账时。 - 用 SUMIFS 代替透视表
：预先铺好维度网格，公式自己把流水分配到格子里，打开即最新，不用刷新。 - 让表格自己说结论
：把异常写成中文提示，比让人盯着一堆数字自己判断可靠得多。 - 参数集中、可视化统一
：一个年月开关驱动全簿；周末、今日、完成、超产在几张表里用同一套颜色语言。
结论：一套「一人维护、单机可跑、当月几十行计划」的小厂管控，用 Excel 把参数、主键、录入口、聚合、可视化这五件事理顺，已经足够自动化；而且这套思路可以直接搬进任何系统。
九、几个必须先知道的坑
- 版本
： SEQUENCE/FILTER/CHOOSECOLS需要 Excel 365、2021 或新版 WPS。原文件由 WPS 保存，公式在 XML 里带_xlfn._xlws.FILTER、_xlfn.CHOOSECOLS这类前缀属正常兼容写法，用旧版 Excel 打开会显示#NAME?。 - 日期是数字
：文件里 46327就是 2026-11-01。所有比较、"<="&单元格都依赖它是真日期，别把日期列存成文本。 - 溢出区域不能手填
：A4、A5、R5 这些是动态数组的锚点，右侧/下方的溢出格里手动写东西会触发 #SPILL!。 - 条件格位置
：台账里标签在 S2/U2/W2/Y2，输入框在 T2/V2/X2/Z2；写公式或做说明时别错位。 - 条件格式优先级
：今日列和周末列可能重叠，最终颜色由规则顺序决定；原文件把计划条设为优先级 1（最高）。 - 空值守卫
：第 31 列、报表第 31 天、日历第 6 周，都要用 IF(上一格="","",…)或MONTH()<>月份处理短月，否则末尾会多出莫名其妙的 0 或灰格。 - 主键一变，全链要重算
：任务单编号由「开始日期 + 行号」生成，改动开始日期会换编号，台账里已录的产出就对不上了。上线前最好把编号冻结成值，或改成不会随日期变化的方式。
