主页 / computer / 问题 / 1892617
Accepted
Jake Nixon
Asked: 2025-04-18 02:13:05 +0800 CST

如何编写一个 Excel 公式来执行多次 xlookup 遍历，其中每次遍历都引用前一次遍历的最大返回值？

  • 772

我有两个 Excel 表，分别名为 FacilityLoss 和 MappingTable。FacilityLoss 表包含两列：FiberNum 和 Loss。FiberNum 包含 1-30 的行值，Loss 包含每条编号光纤的记录光纤损耗值。

我有一个名为“Tolerance”的单元格，它指定了最大允许损失值。

MappingTable 有两列：UUT 光纤和设施光纤。UUT 列包含 1-20 行值。设施光纤列需要填写损耗值低于容差值的设施光纤。我们希望优先考虑 UUT 光纤和设施光纤之间的 1:1 连接，以提高设置的清晰度。

我目前在设施光纤列的单元格中有以下公式：

=IF(XLOOKUP([@[UUT Fiber]],FacilityLoss[Fiber Num],FacilityLoss[Loss])<=Tolerance,[@[UUT Fiber]],"")

正如您所看到的，它对于良好的光纤正常工作，但我需要它在第二次通过时从下一个最高的光纤开始恢复计数。

包含两个表格（MappingTable 和 FacilityLoss）的 Excel 屏幕截图

我不需要无限递归；只需几次就可以了。我尝试创建一个辅助列，用于填充空白值，即使跳过多行，数值也会增加1，但我无法正常工作。

我还为公差单元实现了一个公式，该公式可以根据所请求的光纤数量计算最佳公差：

=SMALL(FacilityLoss[Loss],MAX(MappingTable[UUT Fiber]))

如何修改 MappingTable 列 FacilityFiber 中的 XLOOKUP() 公式以达到我想要的效果？以下是手动填充的正确值的屏幕截图：

手动更正的 MappingTable

我正在使用适用于 Microsoft 365 MSO（版本 2502 内部版本 16.0.18526.20286）64 位的 Microsoft Excel。

microsoft-excel

2 个回答

  1. Best Answer

    根据提出的解决方案的想法，可以使用以下公式：

    =IF(XLOOKUP([@[UUT Fiber]], FacilityLoss[Fiber Num], FacilityLoss[Loss])<=Tolerance, [@[UUT Fiber]], 
MIN(IF((FacilityLoss[Loss]<=Tolerance)*(FacilityLoss[Fiber Num]>MAX(ROWS(MappingTable), 
MappingTable[[#Headers], [Facility Fiber]]:OFFSET([@[Facility Fiber]],-1,0))), FacilityLoss[Fiber Num])))

    纤维

    • 2

  2. 这几乎是一个框架挑战，并且不使用Xlookup。如果我正确理解了约束条件，那么这里有一个替代解决方案：

    =IF(E3<=$G$3,D3,INDEX(FILTER($D$23:$D$32,$E$23:$E$32<=$G$3),COUNTIF($E$3:E3,">"&$G$3)))

    这将粘贴到单元格 B3，然后复制/粘贴到单元格 B22

    打破这个

    • 该部件在公差范围内then将光纤编号（1-20）分配给相应的设施if
    • 该else部件将光纤编号 (21-30) 分配给if公差范围之外的设施。本部分的技巧是使用 创建一个满足公差的光纤编号数组，filter然后index使用 并根据 1-20 中超出公差的项目子集的位置对该数组进行计数，countif并部分锚定数组进行计数。

    假设是将调整公差（可能使用解算器）以获得 20 条“最佳”股线。

    屏幕截图显示解决方案

    • 1

相关问题