摘要：在昨天的文章中，我们已成功将每个欠料项对应的全部可替代物料（包括主料、替代料1、替代料2……末级替代料）通过公式聚合，并回填至一维欠料明细表中。该信息以逗号分隔的文本形式呈现，完整表达了某一BOM位置的所有可用选项。
在昨天的文章中，我们已成功将每个欠料项对应的全部可替代物料（包括主料、替代料1、替代料2……末级替代料）通过公式聚合，并回填至一维欠料明细表中。该信息以逗号分隔的文本形式呈现，完整表达了某一BOM位置的所有可用选项。
接下来，我们需要将这一增强信息融入到二维缺料分析视图中，取代原先仅显示“末级替代料”的聚合方式，从而让采购和计划人员在宏观视图中也能一目了然地掌握“哪里缺料、有哪些料可选”。
原来的二维的聚合显示公式为：
=LET(A,合并数据!A2#,PIVOTBY(INDEX(A,,6),INDEX(A,,4),INDEX(A,,12),SUM,0,,,0))
这个效果只有末级替代料，为了让采购更好的查看所有可替代项，把公式更改为：
=LET(A,合并数据!A2#,PIVOTBY(合并数据!N2#,INDEX(A,,4),INDEX(A,,12),SUM,0,,,0))
公式说明：
PIVOTBY(...) 第1参数 INDEX(A,,6):第6列：仅显示“末级替代料编码” 合并数据!N2#列：通过 ARRAYTOTEXT 合并后的“完整可替代料列表”
第2参数 INDEX(A,,4) → 需求日期 保持不变
值字段第3参数 INDEX(A,,12) → 欠料数量（负值） 保持不变
聚合方式 SUM：按组汇总缺料量 保持不变
空值显示 0：缺数显示为0 保持不变
每一行的“行头”不再是单一的末级替代料编码，而是形如 "A03,A03-1,A03-2,A03-3" 的完整替代组；
所有在该替代组下产生的缺料（无论原属哪一级替代料）都被自动归集到同一行；输出结果既保留了时间维度的分布，又增强了物料选择的透明度。
提升采购决策效率：无需切换明细表，即可在二维视图中看到“该位置有哪些料可用”；
避免遗漏可用资源：防止因只关注末级替代料而忽略更优的前级替代方案；
支持灵活备料策略：可根据交期、库存、成本等因素，在同一行内选择最合适的替代料进行采购或调拨；
增强报表实用性：真正实现“一张表看全貌”——既知缺多少，也知有哪些可选。
至此，我们已完成从多级净需求计算 → 缺料汇总 → 二维分析 → 替代料扩展展示的完整逻辑闭环，整个MRP替代分析系统已全面搭建完成。然而，随着工作表数量的增加（如“第1级净需求”“第2级净需求”“合并数据”“二维欠料”“BOM输出”等），手动切换工作表变得低效且容易出错。
为提升工作簿的整体可操作性与专业度，接下来我们将新增一个目录工作表，并为其创建自动化的超链接导航系统，实现一键跳转。
=LET(A,SHEETSNAME(,1,1),VSTACK({"序号","工作表名称"},HSTACK(SEQUENCE(ROWS(A)),A)))
公式解释：
SHEETSNAME(,1,1) ：提取工作表名称
定义为A 接收所有可见工作表的名称列表
SEQUENCE(ROWS(A)) 生成从1开始的序号列，数量与工作表数一致
HSTACK(...) 将序号列与工作表名列水平拼接
VSTACK(...) 添加表头行，形成完整表格结构
为工作表名称添加超链接
仅显示名称还不够，我们需要让每一项可点击跳转。使用 HYPERLINK 函数实现：
=MAP(TRIMRANGE(B2:B1000),LAMBDA(X,IF(X="","",HYPERLINK(CONCAT("#'",X,"'!A1"),"点击进入"))))
公式解释：
RIMRANGE(B2:B1000) 自定义或命名函数，用于清理数据范围：去除空值、错误值、空白字符串，确保只处理有效的工作表名
MAP(...) 对 TRIMRANGE 返回的每个有效名称逐一应用后续逻辑
LAMBDA(X, ...) 定义对每个名称 X 的操作
IF(X="", "", ...) 如果单元格为空，则返回空，避免生成无效链接
CONCAT("#'", X, "'!A1") 构造超链接的目标地址：
- #：表示当前工作簿内跳转
- 'X'：将含空格或特殊字符的工作表名用单引号包裹（Excel标准语法）
- !A1：跳转至该工作表的A1单元格（可设为任意锚点）
"点击进入" 超链接的显示文本，用户看到的是“点击进入”，点击后跳转
HYPERLINK(...) 创建可点击链接，支持鼠标点击跳转
交互性强：告别手动标签切换，实现目录驱动的快速导航；
自动化维护：当新增或重命名工作表后，只需刷新数据，链接自动更新；
兼容复杂表名：通过 CONCAT 和单引号包裹，支持包含空格、连字符等工作表名（如 '第4级净需求 ', 'BOM 输出'）；
统一入口：所有用户从“目录”开始操作，确保流程一致、不易出错。
插入形状
进入第一个工作表（如“BOM输出”或“第1级净需求”）
点击菜单栏【插入】→【形状】，选择一个合适的按钮样式（如矩形圆角）
绘制一个适当大小的形状
命名与美化
选中形状，在左上角名称框中将其重命名为：返回目录
双击形状，输入文字：“返回目录”或“↩ 目录”
设置字体、颜色、填充色等，使其美观醒目
设置超链接
右键点击该形状 → 选择【链接】（或按快捷键 Ctrl + K）
在弹出的“插入超链接”窗口中：
选择“本文档中的位置”
在列表中找到并选择工作表 “目录”
地址将自动显示为：#目录!A1
点击“确定”完成链接设置
复制到其他工作表
选中已设置好的“返回目录”形状
复制（Ctrl + C）
依次切换到其他需要返回功能的工作表（如“第2级净需求”、“合并数据”、“二维欠料”等）
在每个工作表中粘贴（Ctrl + V）
所有复制的按钮将自动继承原始链接，点击即可返回“目录”页
为什么使用“形状”而不是“单元格”？
避免公式溢出冲突 ：若使用单元格放置链接，当相邻区域使用动态数组公式（如 # 引用）自动扩展时，可能覆盖该单元格，导致公式错误或链接失效
固定位置显示：形状可自由定位并锁定在某一角落（如右上角），不受数据区域扩展影响
视觉清晰：形状按钮更醒目，用户易于识别为“功能控件”而非普通数据
交互友好：支持鼠标悬停、点击反馈，提升用户体验
从 多级替代逻辑推演 到 数据合并与筛选，再到 二维分析与目录导航，我们完整构建了一套基于Excel动态数组的智能MRP替代分析系统。
这套系统具备：
✅ 自动化计算能力
✅ 多级替代支持
✅ 缺料精准识别
✅ 采购灵活选择
✅ 可视化展示
✅ 交互式导航
真正实现了从“手工查料”到“智能决策”的跨越。
如需模板或函数封装建议，欢迎继续交流！
来源：古哥计划