1 line
30 KiB
Plaintext
1 line
30 KiB
Plaintext
{"ID":"20251029115611-lmsvsa1","Spec":"2","Type":"NodeDocument","Properties":{"id":"20251029115611-lmsvsa1","title":"数据库节省成本","type":"doc","updated":"20251122085830"},"Children":[{"ID":"20251029115617-2wrrn8k","Type":"NodeParagraph","Properties":{"id":"20251029115617-2wrrn8k","updated":"20251029115617"},"Children":[{"Type":"NodeText","Data":"好的，请允许我基于您提供的详细信息，为您整理一份可以直接使用的汇报内容。这将清晰地展示您的工作价值。"}]},{"ID":"20251029115617-mmz81q2","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20251029115617-mmz81q2","updated":"20251029115617"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"主题：关于使用Excel VBA成功替代陈旧Access系统，实现物料需求自动计算的汇报"}]},{"ID":"20251029115617-o0imcq1","Type":"NodeParagraph","Properties":{"id":"20251029115617-o0imcq1","updated":"20251029115617"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"汇报对象："},{"Type":"NodeText","Data":" 部门领导、相关同事"}]},{"ID":"20251029115617-mq2h5ts","Type":"NodeParagraph","Properties":{"id":"20251029115617-mq2h5ts","updated":"20251029115617"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"核心摘要："},{"Type":"NodeText","Data":"\n我开发了一个Excel VBA工具，成功解决了因技术淘汰而无法进行月度物料需求计算的紧急问题。该工具不仅"},{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"零成本"},{"Type":"NodeText","Data":"避免了每年上千元的软件采购费用，还"},{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"完美继承了原有业务逻辑"},{"Type":"NodeText","Data":"，实现了对8万行级BOM数据的自动化、准确、高效处理，保障了生产计划的连续性。"}]},{"ID":"20251029115617-8cbm0yp","Type":"NodeThematicBreak","Properties":{"id":"20251029115617-8cbm0yp","updated":"20251029115617"}},{"ID":"20251029115617-uryxaio","Type":"NodeHeading","HeadingLevel":4,"Properties":{"id":"20251029115617-uryxaio","updated":"20251029115617"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"一、 项目背景与亟待解决的痛点"}]},{"ID":"20251029115617-hek8zcj","Type":"NodeParagraph","Properties":{"id":"20251029115617-hek8zcj","updated":"20251029115617"},"Children":[{"Type":"NodeText","Data":"每月处理订单和预测（Focst）后，我们需要根据SAP的YIVM3001表计算成品对应的物料需求。"}]},{"ID":"20251029115617-o1oqg55","Type":"NodeList","ListData":{},"Properties":{"id":"20251029115617-o1oqg55","updated":"20251029115617"},"Children":[{"ID":"20251029115617-8giian1","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20251029115617-8giian1","updated":"20251029115617"},"Children":[{"ID":"20251029115617-nud02wv","Type":"NodeParagraph","Properties":{"id":"20251029115617-nud02wv","updated":"20251029115617"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"业务量巨大"},{"Type":"NodeText","Data":"：涉及的成品和原料繁多，导出的完整BOM表高达"},{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"8万行"},{"Type":"NodeText","Data":"，完全无法依赖手动计算。"}]}]},{"ID":"20251029115617-fxu44p2","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20251029115617-fxu44p2","updated":"20251029115617"},"Children":[{"ID":"20251029115617-m93k0hm","Type":"NodeParagraph","Properties":{"id":"20251029115617-m93k0hm","updated":"20251029115617"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"核心系统崩溃"},{"Type":"NodeText","Data":"：原先负责此任务的Microsoft Access 2003数据库及文件（.dab格式）因Windows系统更新已无法安装运行，导致关键业务链条中断。"}]}]},{"ID":"20251029115617-ktun5wo","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20251029115617-ktun5wo","updated":"20251029115617"},"Children":[{"ID":"20251029115617-rlmnk6z","Type":"NodeParagraph","Properties":{"id":"20251029115617-rlmnk6z","updated":"20251029115617"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"官方解决方案成本高、流程复杂"},{"Type":"NodeText","Data":"：调研发现，新版的Access 2025无法直接读取旧文件，必须通过Access 2007进行转换。但购买Access 2007订阅（约"},{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"90元/月/人"},{"Type":"NodeText","Data":"）不仅增加持续成本，且购买渠道难寻。此路不通，业务面临停滞风险。"}]}]}]},{"ID":"20251029115617-d4xb3fy","Type":"NodeHeading","HeadingLevel":4,"Properties":{"id":"20251029115617-d4xb3fy","updated":"20251029115617"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"二、 我的解决方案：自主开发的Excel VBA工具"}]},{"ID":"20251029115617-4tk076p","Type":"NodeParagraph","Properties":{"id":"20251029115617-4tk076p","updated":"20251029115617"},"Children":[{"Type":"NodeText","Data":"面对上述困境，我决定利用公司已广泛部署的Excel软件，开发一个VBA宏程序来替代原有Access数据库。"}]},{"ID":"20251029115617-wg2ucv5","Type":"NodeList","ListData":{},"Properties":{"id":"20251029115617-wg2ucv5","updated":"20251029115617"},"Children":[{"ID":"20251029115617-izn3x9o","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20251029115617-izn3x9o","updated":"20251029115617"},"Children":[{"ID":"20251029115617-j448e6y","Type":"NodeParagraph","Properties":{"id":"20251029115617-j448e6y","updated":"20251029115617"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"核心技术"},{"Type":"NodeText","Data":"：使用VBA中的"},{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"字典（Dictionary）对象"},{"Type":"NodeText","Data":"作为核心引擎。"}]}]},{"ID":"20251029115617-4mmjo8j","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20251029115617-4mmjo8j","updated":"20251029115617"},"Children":[{"ID":"20251029115617-arx1sg3","Type":"NodeParagraph","Properties":{"id":"20251029115617-arx1sg3","updated":"20251029115617"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"实现逻辑"},{"Type":"NodeText","Data":"："}]},{"ID":"20251029115617-ogxla8j","Type":"NodeList","ListData":{"Typ":1},"Properties":{"id":"20251029115617-ogxla8j","updated":"20251029115617"},"Children":[{"ID":"20251029115617-46ojz23","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"MS4=","Num":1},"Properties":{"id":"20251029115617-46ojz23","updated":"20251029115617"},"Children":[{"ID":"20251029115617-efopp16","Type":"NodeParagraph","Properties":{"id":"20251029115617-efopp16","updated":"20251029115617"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"数据读取"},{"Type":"NodeText","Data":"：自动将BOM表和Focst计划表读取到内存中。"}]}]},{"ID":"20251029115617-5o8jvb4","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"Mi4=","Num":2},"Properties":{"id":"20251029115617-5o8jvb4","updated":"20251029115617"},"Children":[{"ID":"20251029115617-1qpeegb","Type":"NodeParagraph","Properties":{"id":"20251029115617-1qpeegb","updated":"20251029115617"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"高效关联"},{"Type":"NodeText","Data":"：利用字典实现BOM和Focst数据的快速匹配和链接（类似数据库的关联查询）。"}]}]},{"ID":"20251029115617-tracha2","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"My4=","Num":3},"Properties":{"id":"20251029115617-tracha2","updated":"20251029115617"},"Children":[{"ID":"20251029115617-318tgy7","Type":"NodeParagraph","Properties":{"id":"20251029115617-318tgy7","updated":"20251029115617"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"精准计算"},{"Type":"NodeText","Data":"：严格遵循原有业务逻辑执行计算："},{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"周物料需求 = 成品清单用量 × BOM单件用量 × 成品周需求"},{"Type":"NodeText","Data":"。"}]}]}]}]},{"ID":"20251029115617-62ve258","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20251029115617-62ve258","updated":"20251029115617"},"Children":[{"ID":"20251029115617-f2nhwqq","Type":"NodeParagraph","Properties":{"id":"20251029115617-f2nhwqq","updated":"20251029115617"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"核心优势"},{"Type":"NodeText","Data":"：逻辑与原有数据库完全一致，确保了计算结果的"},{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"准确性和可靠性"},{"Type":"NodeText","Data":"，平滑过渡，业务人员无需重新学习。"}]}]}]},{"ID":"20251029115617-io71kb1","Type":"NodeHeading","HeadingLevel":4,"Properties":{"id":"20251029115617-io71kb1","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"三、 为本公司带来的直接价值与收益"}]},{"ID":"20251029115617-io9z9ti","Type":"NodeParagraph","Properties":{"id":"20251029115617-io9z9ti","updated":"20251029115617"},"Children":[{"Type":"NodeText","Data":"这项成果不仅仅是一个工具替换，更是一次高效的成本节约和效率提升。"}]},{"ID":"20251029115617-uqi92d8","Type":"NodeTable","TableAligns":[1,1],"Properties":{"colgroup":"|","id":"20251029115617-uqi92d8","updated":"20251029115617"},"Children":[{"Type":"NodeTableHead","Data":"thead","Children":[{"Type":"NodeTableRow","Data":"tr","Children":[{"Type":"NodeTableCell","Data":"th","TableCellAlign":1,"Children":[{"Type":"NodeText","Data":"收益维度"}]},{"Type":"NodeTableCell","Data":"th","TableCellAlign":1,"Children":[{"Type":"NodeText","Data":"具体表现"}]}]}]},{"Type":"NodeTableRow","Data":"tr","Children":[{"Type":"NodeTableCell","Data":"td","TableCellAlign":1,"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"1. 直接成本节约"}]},{"Type":"NodeTableCell","Data":"td","TableCellAlign":1,"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"实现零成本替代"},{"Type":"NodeText","Data":"：直接避免了每年约 "},{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"1080元（90元/月 * 12月）"},{"Type":"NodeText","Data":" 的软件订阅费用，且无需任何一次性采购支出。"}]}]},{"Type":"NodeTableRow","Data":"tr","Children":[{"Type":"NodeTableCell","Data":"td","TableCellAlign":1,"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"2. 效率与准确性"}]},{"Type":"NodeTableCell","Data":"td","TableCellAlign":1,"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"处理速度快"},{"Type":"NodeText","Data":"：一键自动处理"},{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"8万行"},{"Type":"NodeText","Data":"数据，将原本数小时的手工/半自动工作缩短至"},{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"几分钟内"},{"Type":"NodeText","Data":"完成，效率提升超过90%。"}]}]},{"Type":"NodeTableRow","Data":"tr","Children":[{"Type":"NodeTableCell","Data":"td","TableCellAlign":1},{"Type":"NodeTableCell","Data":"td","TableCellAlign":1,"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"100%准确可靠"},{"Type":"NodeText","Data":"：自动化计算杜绝了人为错误，保证了生产计划依据的数据精准无误。"}]}]},{"Type":"NodeTableRow","Data":"tr","Children":[{"Type":"NodeTableCell","Data":"td","TableCellAlign":1,"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"3. 可维护性与未来"}]},{"Type":"NodeTableCell","Data":"td","TableCellAlign":1,"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"摆脱技术枷锁"},{"Type":"NodeText","Data":"：彻底摆脱了对老旧、淘汰技术（Access 2003）的依赖，工具可在任何安装现代Office的电脑上运行。"}]}]},{"Type":"NodeTableRow","Data":"tr","Children":[{"Type":"NodeTableCell","Data":"td","TableCellAlign":1},{"Type":"NodeTableCell","Data":"td","TableCellAlign":1,"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"易于维护和扩展"},{"Type":"NodeText","Data":"：VBA代码结构清晰，未来若业务逻辑变化，可快速修改调整，适应性远强于固化的旧系统。"}]}]},{"Type":"NodeTableRow","Data":"tr","Children":[{"Type":"NodeTableCell","Data":"td","TableCellAlign":1,"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"4. 业务连续性"}]},{"Type":"NodeTableCell","Data":"td","TableCellAlign":1,"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"保障核心流程"},{"Type":"NodeText","Data":"：在关键时刻确保了物料需求计算这一生产计划核心环节的顺利进行，避免了因工具缺失导致的业务中断风险。"}]}]}]},{"ID":"20251029124637-e3ov87c","Type":"NodeParagraph","Properties":{"id":"20251029124637-e3ov87c","updated":"20251029124637"}},{"ID":"20251029132013-343wnen","Type":"NodeParagraph","Properties":{"id":"20251029132013-343wnen","updated":"20251029132013"},"Children":[{"Type":"NodeText","Data":"•业务需求：每月需依据SAP 导出的完整BOM（约8 万行）和Forecast数据，计算成品对应的物料需求。"}]},{"ID":"20251029132013-i5ms0w9","Type":"NodeParagraph","Properties":{"id":"20251029132013-i5ms0w9","updated":"20251029132013"},"Children":[{"Type":"NodeText","Data":"•技术障碍：负责该计算的Access 2003数据库已无法在公司的Windows环境中安装运行，只有少数老电脑（特定硬件/系统）才能打开。"}]},{"ID":"20251029132013-1f4etyf","Type":"NodeParagraph","Properties":{"id":"20251029132013-1f4etyf","updated":"20251029132013"},"Children":[{"Type":"NodeText","Data":"•风险与成本："}]},{"ID":"20251029132013-pp61htw","Type":"NodeParagraph","Properties":{"id":"20251029132013-pp61htw","updated":"20251029132013"},"Children":[{"Type":"NodeText","Data":"◦单点故障：一旦该专用电脑出现硬件或系统问题，整个物料需求计算流程立即中断，直接影响生产计划。"}]},{"ID":"20251029132013-s0l59bl","Type":"NodeParagraph","Properties":{"id":"20251029132013-s0l59bl","updated":"20251029132013"},"Children":[{"Type":"NodeText","Data":"◦操作不便：所有使用者必须到指定电脑前手工操作，导致工作效率低下、协作受限。"}]},{"ID":"20251029132013-jrsmcm1","Type":"NodeParagraph","Properties":{"id":"20251029132013-jrsmcm1","updated":"20251029132013"},"Children":[{"Type":"NodeText","Data":"3 潜在费用：若采用官方推荐的新版Access进行迁移，需额外购买软件授权（约2百多元 /月/人），并需额外购买2007版本用来进行的文件转换。"}]},{"ID":"20251029132025-64w723u","Type":"NodeCodeBlock","IsFencedCodeBlock":true,"Properties":{"id":"20251029132025-64w723u","updated":"20251029132043"},"Children":[{"Type":"NodeCodeBlockFenceOpenMarker","Data":"```"},{"Type":"NodeCodeBlockFenceInfoMarker","CodeBlockInfo":"dW5kZWZpbmVk"},{"Type":"NodeCodeBlockCode","Data":"•业务需求：每月需依据SAP 导出的完整BOM（约8 万行）和Forecast数据，计算成品对应的物料需求。\n\n•技术障碍：负责该计算的Access 2003数据库已无法在公司的Windows环境中安装运行，只有少数老电脑（特定硬件/系统）才能打开。\n\n•风险与成本：\n\n◦单点故障：一旦该专用电脑出现硬件或系统问题，整个物料需求计算流程立即中断，直接影响生产计划。\n\n◦操作不便：所有使用者必须到指定电脑前手工操作，导致工作效率低下、协作受限。\n\n3 潜在费用：若采用官方推荐的新版Access进行迁移，需额外购买软件授权（约2百多元 /月/人），并需额外购买2007版本用来进行的文件转换。\n"},{"Type":"NodeCodeBlockFenceCloseMarker","Data":"```"}]},{"ID":"20251029135844-06cceap","Type":"NodeParagraph","Properties":{"id":"20251029135844-06cceap","updated":"20251029135844"},"Children":[{"Type":"NodeText","Data":"您提供了一个非常详细和透彻的技术总结，完美地拆解了两个核心Excel公式的逻辑和工作原理。下面我将您的总结进行梳理和精炼，并补充完整数据库的设计逻辑，形成一个结构清晰的完整说明。"}]},{"ID":"20251029135844-4u6c7dy","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20251029135844-4u6c7dy","updated":"20251029135844"},"Children":[{"Type":"NodeText","Data":"核心公式逻辑总结"}]},{"ID":"20251029135844-fsxwdhb","Type":"NodeHeading","HeadingLevel":4,"Properties":{"id":"20251029135844-fsxwdhb","updated":"20251029135844"},"Children":[{"Type":"NodeText","Data":"1. 公式一：计算提前期（L/T）内的总需求"}]},{"ID":"20251029135844-ip7wjqp","Type":"NodeParagraph","Properties":{"id":"20251029135844-ip7wjqp","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"目标："},{"Type":"NodeText","Data":" 动态计算从固定列（P列）开始，到“提前期周数”所对应的列为止的求和。"}]},{"ID":"20251029135844-f22jw0l","Type":"NodeList","ListData":{},"Properties":{"id":"20251029135844-f22jw0l","updated":"20251029135844"},"Children":[{"ID":"20251029135844-dp4dhwm","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20251029135844-dp4dhwm","updated":"20251029135844"},"Children":[{"ID":"20251029135844-wfwtz2y","Type":"NodeParagraph","Properties":{"id":"20251029135844-wfwtz2y","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"基础思路："},{"Type":"NodeText","Data":" "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"=SUM(P1:XX1)"},{"Type":"NodeText","Data":"，其中 "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"XX"},{"Type":"NodeText","Data":" 需要根据“提前期周数”动态确定。"}]}]},{"ID":"20251029135844-jico59k","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20251029135844-jico59k","updated":"20251029135844"},"Children":[{"ID":"20251029135844-il6r9qa","Type":"NodeParagraph","Properties":{"id":"20251029135844-il6r9qa","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"核心挑战："}]},{"ID":"20251029135844-2sgilyt","Type":"NodeList","ListData":{"Typ":1},"Properties":{"id":"20251029135844-2sgilyt","updated":"20251029135844"},"Children":[{"ID":"20251029135844-k7c1qzz","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"MS4=","Num":1},"Properties":{"id":"20251029135844-k7c1qzz","updated":"20251029135844"},"Children":[{"ID":"20251029135844-7uvvoon","Type":"NodeParagraph","Properties":{"id":"20251029135844-7uvvoon","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"列偏移："},{"Type":"NodeText","Data":" 求和起点是P列（第16列），所以结束列号需要是 "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"15 + L/T周数"},{"Type":"NodeText","Data":"。"}]}]},{"ID":"20251029135844-dloyg5r","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"Mi4=","Num":2},"Properties":{"id":"20251029135844-dloyg5r","updated":"20251029135844"},"Children":[{"ID":"20251029135844-7uc49c7","Type":"NodeParagraph","Properties":{"id":"20251029135844-7uc49c7","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"数字转列标："},{"Type":"NodeText","Data":" Excel无法直接使用数字（如 "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"16"},{"Type":"NodeText","Data":"）来表示列范围（如 "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"P"},{"Type":"NodeText","Data":"），需要一个函数将数字转换为字母列标。"}]}]},{"ID":"20251029135844-votnv07","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"My4=","Num":3},"Properties":{"id":"20251029135844-votnv07","updated":"20251029135844"},"Children":[{"ID":"20251029135844-x4bkyt5","Type":"NodeParagraph","Properties":{"id":"20251029135844-x4bkyt5","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"字符串拼接："},{"Type":"NodeText","Data":" 直接拼接 "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"”P1:” \u0026amp; “XX1”"},{"Type":"NodeText","Data":" 会导致Excel无法识别，需要使用 "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"INDIRECT"},{"Type":"NodeText","Data":" 函数将文本字符串转换为有效的单元格引用。"}]}]}]}]},{"ID":"20251029135844-c7uyons","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20251029135844-c7uyons","updated":"20251029135844"},"Children":[{"ID":"20251029135844-pqfp2kq","Type":"NodeParagraph","Properties":{"id":"20251029135844-pqfp2kq","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"解决方案："}]},{"ID":"20251029135844-0uhfsti","Type":"NodeList","ListData":{"Typ":1},"Properties":{"id":"20251029135844-0uhfsti","updated":"20251029135844"},"Children":[{"ID":"20251029135844-2tcvquk","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"MS4=","Num":1},"Properties":{"id":"20251029135844-2tcvquk","updated":"20251029135844"},"Children":[{"ID":"20251029135844-pgexnnx","Type":"NodeParagraph","Properties":{"id":"20251029135844-pgexnnx","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"自定义函数"},{"Type":"NodeText","Data":" "},{"Type":"NodeTextMark","TextMarkType":"strong code","TextMarkTextContent":"NT"},{"Type":"NodeText","Data":""},{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"："},{"Type":"NodeText","Data":" 将列号数字转换为字母列标（如 "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"16"},{"Type":"NodeText","Data":" -\u003e "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"”P”"},{"Type":"NodeText","Data":"）。"}]}]},{"ID":"20251029135844-6o5o8sr","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"Mi4=","Num":2},"Properties":{"id":"20251029135844-6o5o8sr","updated":"20251029135844"},"Children":[{"ID":"20251029135844-es9mbg5","Type":"NodeParagraph","Properties":{"id":"20251029135844-es9mbg5","updated":"20251029135844"},"Children":[{"Type":"NodeText","Data":""},{"Type":"NodeTextMark","TextMarkType":"strong code","TextMarkTextContent":"INDIRECT"},{"Type":"NodeText","Data":""},{"Type":"NodeText","Data":" "},{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"函数："},{"Type":"NodeText","Data":" 拼接完整的引用字符串 "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"”P” \u0026amp; ROW() \u0026amp; “:” \u0026amp; NT(15+L/T周数) \u0026amp; ROW()"},{"Type":"NodeText","Data":"，并将其转换为可计算的区域。"}]}]},{"ID":"20251029135844-wn6xx05","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"My4=","Num":3},"Properties":{"id":"20251029135844-wn6xx05","updated":"20251029135844"},"Children":[{"ID":"20251029135844-ik2fuo7","Type":"NodeParagraph","Properties":{"id":"20251029135844-ik2fuo7","updated":"20251029135844"},"Children":[{"Type":"NodeText","Data":""},{"Type":"NodeTextMark","TextMarkType":"strong code","TextMarkTextContent":"ROW()"},{"Type":"NodeText","Data":""},{"Type":"NodeText","Data":" "},{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"函数："},{"Type":"NodeText","Data":" 使公式能随行向下填充时自动调整行号，实现动态引用。"}]}]}]}]}]},{"ID":"20251029135844-855pu87","Type":"NodeParagraph","Properties":{"id":"20251029135844-855pu87","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"最终公式："},{"Type":"NodeText","Data":"\n"},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"=SUM(INDIRECT(\"P\" \u0026amp; ROW() \u0026amp; \":\" \u0026amp; NT(15+$J3) \u0026amp; ROW()))"},{"Type":"NodeText","Data":"\n（假设L/T周数在J列）"}]},{"ID":"20251029135844-26p3lg9","Type":"NodeThematicBreak","Properties":{"id":"20251029135844-26p3lg9","updated":"20251029135844"}},{"ID":"20251029135844-oss7bu5","Type":"NodeHeading","HeadingLevel":4,"Properties":{"id":"20251029135844-oss7bu5","updated":"20251029135844"},"Children":[{"Type":"NodeText","Data":"2. 公式二：动态生成VLOOKUP的列索引"}]},{"ID":"20251029135844-felti1f","Type":"NodeParagraph","Properties":{"id":"20251029135844-felti1f","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"目标："},{"Type":"NodeText","Data":" 根据一个设定的开始日期（如 "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"$P$2"},{"Type":"NodeText","Data":"），动态计算出后续每一列对应的是预测表中的第几周，从而作为 "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"VLOOKUP"},{"Type":"NodeText","Data":" 的列索引。"}]},{"ID":"20251029135844-xxuhgsq","Type":"NodeList","ListData":{},"Properties":{"id":"20251029135844-xxuhgsq","updated":"20251029135844"},"Children":[{"ID":"20251029135844-h9qhxr1","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20251029135844-h9qhxr1","updated":"20251029135844"},"Children":[{"ID":"20251029135844-przgh13","Type":"NodeParagraph","Properties":{"id":"20251029135844-przgh13","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"基础思路："},{"Type":"NodeText","Data":" 构建一个从2开始递增的序列（因为VLOOKUP索引1对应料号列，索引2开始才对应周数据）。"}]}]},{"ID":"20251029135844-98wpftv","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20251029135844-98wpftv","updated":"20251029135844"},"Children":[{"ID":"20251029135844-zukhe44","Type":"NodeParagraph","Properties":{"id":"20251029135844-zukhe44","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"实现方法："}]},{"ID":"20251029135844-lzh0mxm","Type":"NodeList","ListData":{"Typ":1},"Properties":{"id":"20251029135844-lzh0mxm","updated":"20251029135844"},"Children":[{"ID":"20251029135844-yuepbd3","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"MS4=","Num":1},"Properties":{"id":"20251029135844-yuepbd3","updated":"20251029135844"},"Children":[{"ID":"20251029135844-ce67jej","Type":"NodeParagraph","Properties":{"id":"20251029135844-ce67jej","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"计算周偏移："},{"Type":"NodeText","Data":" "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"($P$2 - ForecastStartDate) / 7"},{"Type":"NodeText","Data":"。这决定了从预测表的第几周开始显示。"}]}]},{"ID":"20251029135844-auby0n7","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"Mi4=","Num":2},"Properties":{"id":"20251029135844-auby0n7","updated":"20251029135844"},"Children":[{"ID":"20251029135844-ko5ldgs","Type":"NodeParagraph","Properties":{"id":"20251029135844-ko5ldgs","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"生成递增序列："},{"Type":"NodeText","Data":" "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"COLUMN() - COLUMN($N$1)"},{"Type":"NodeText","Data":"。假设从N列开始公式，此部分会随着列向右移动而生成0, 1, 2, 3...的序列。"}]}]},{"ID":"20251029135844-c8wn783","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"My4=","Num":3},"Properties":{"id":"20251029135844-c8wn783","updated":"20251029135844"},"Children":[{"ID":"20251029135844-wyfctq5","Type":"NodeParagraph","Properties":{"id":"20251029135844-wyfctq5","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"合并为最终索引："},{"Type":"NodeText","Data":" "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"ROUND(周偏移量, 0) + 递增序列"},{"Type":"NodeText","Data":"。结果是一个整数，直接作为 "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"VLOOKUP"},{"Type":"NodeText","Data":" 的第三个参数。"}]}]}]}]}]},{"ID":"20251029135844-c665pss","Type":"NodeParagraph","Properties":{"id":"20251029135844-c665pss","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"最终索引公式："},{"Type":"NodeText","Data":"\n"},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"=ROUND(($P$2 - INDIRECT(\"Forecast!$C1\")) / 7, 0) + COLUMN() - COLUMN($N$1)"},{"Type":"NodeText","Data":""}]},{"ID":"20251029135844-p3urh3l","Type":"NodeParagraph","Properties":{"id":"20251029135844-p3urh3l","updated":"20251029135844"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"动态效果："},{"Type":"NodeText","Data":" 修改 "},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"$P$2"},{"Type":"NodeText","Data":" 的日期，整个需求预测表的时间轴和引用的数据列会自动更新。"}]},{"ID":"20251029135844-6riuvmu","Type":"NodeCodeBlock","IsFencedCodeBlock":true,"Properties":{"id":"20251029135844-6riuvmu","updated":"20251029135844"},"Children":[{"Type":"NodeCodeBlockFenceOpenMarker","Data":"```"},{"Type":"NodeCodeBlockFenceInfoMarker","CodeBlockInfo":"bWVybWFpZA=="},{"Type":"NodeCodeBlockCode","Data":"flowchart TD\n P2[修改0000表的开始日期（P2）] --\u003e A[重算周偏移量\u003cbr\u003e（基准周数）]\n A --\u003e B[更新所有列的\u003cbr\u003eVLOOKUP索引公式]\n B --\u003e C[刷新整个需求\u003cbr\u003e预测时间序列]\n C --\u003e D[重新计算每行的\u003cbr\u003eL/T内总需求]"},{"Type":"NodeCodeBlockFenceCloseMarker","Data":"```"}]},{"ID":"20251029135844-j3u7aee","Type":"NodeThematicBreak","Properties":{"id":"20251029135844-j3u7aee","updated":"20251029135844"}}]} |