如何从一个表到另一个表中的指定单元格找到最接近的列值，以确保它在给定日期或之后？

How to find the nearest value in a column from one table to a specified cell in another table ensuring it is on or after a given date?

提问人：zack_la 提问时间：7/12/2023 最后编辑：zack_la 更新时间：7/13/2023 访问量：76

问：

我希望有人能帮我为我的问题写一个 excel 公式。

我有两张桌子：

表A

编号 类型 日期 数
CNWIUBETOBNSO482 美国广播公司 02 六月， 23 997
CNWIUBETOBNSO482 ABCEX公司 08 六月23 27294
CNWIUBETOBNSO482 ABCEX公司 10 六月，23 943
CNWIUBETOBNSO482 ABCEX公司 17 六月，23 1000
CNWIUBETOBNSO482 ABCEX公司 17 六月，23 1000
CNWIUBETOBNSO482 美国广播公司 25 六月，23 86000
CNWIUBETOBNSO482 ABCEX公司 25 六月，23 4849

表 B.

编号 日期 基础 新编号
CNWIUBETOBNSO482 01 六月23 29000
CNWIUBETOBNSO482 05 六月23 950
CNWIUBETOBNSO482 11 六月，23 999
CNWIUBETOBNSO482 21 六月，23 1009
CNWIUBETOBNSO482 21 六月，23 85000
CNWIUBETOBNSO482 21 六月，23 4760

enter image description here

这是我想在 K 列“newnumber”中的内容：

因此，对于单元格 K1，如果出现以下情况，我希望在 D 列中具有最接近 J1 的值：

  • ID 匹配
  • 表 A 中该行的日期在 I1 中单元格的日期上或之后
  • 类型以“EX”结尾

表 A 中 D 列的每个单元格只能使用一次，以便输入到 K 列中。

因此，理想情况下，如果它有效，它看起来像这样：

enter image description here

请注意，单元格 K5 没有值，因为表 A 中单元格 I5 中日期之后的值尚未被 K 列中的其他单元格使用过 - 这是我最努力的事情。另请注意，单元格 K6 为空，因为尽管 D 列中有一个接近的数字，但该类型不会以“EX”结尾，并且没有其他尚未使用的值。

谢谢你，我希望这是有道理的。如有任何需要我进一步澄清的问题，将不胜感激。

我真的不知道从哪里开始。我尝试了 =IF（And（...）） 语句与 ISNUMBER（SEARCH（...） 相结合，但在无数次尝试后，这一直给我所有 N/A。

Excel 日期 if 语句 excel-公式 匹配

评论

1赞 JvdV 7/12/2023
请添加 markdown 格式的示例数据，以便我们复制/粘贴到 excel 并使用。
0赞 zack_la 7/12/2023
好了，现在好了，对不起，耽搁了
1赞 JvdV 7/12/2023
我这边的第二个问题：那么你是如何确定 K5 是空的，因为之前使用了 4849 的值？由于按时间顺序，这是不正确的。如果按时间顺序工作，那么 K6 和 K7 都应该是空的，而 K5 应该包含 4849。
1赞 zack_la 7/12/2023
我应该把这一点说得更清楚。分配给 K 列的值应根据 D 列中数字的接近程度进行优先级排序，而不是按时间进行优先级排序。因此，一旦在列表中分配了最近的数字，就不能使用它，然后分配下一个最接近的数字，（只要它在相应的日期之后。
1赞 JvdV 7/12/2023
感谢您的回复。现在这是有道理的。

答：

1赞 Foxfire And Burns And Burns 7/12/2023 #1

好吧，使用 Excel365，我很确定使用 FILTER 会容易得多，但无论如何，这可能适合您：

=IFERROR(INDEX($D$2:$D$8;SUMPRODUCT(MAX(--($A$2:$A$8=H2)*--(RIGHT($B$2:$B$8;2)="EX")*--($C$2:$C$8>=I2)*--(ABS($D$2:$D$8-J2)=MIN(ABS($D$2:$D$8-J2)))*ROW($D$2:$D$8)))-1);"-")

该公式是一个数组公式，因此必须按 ++ 输入，否则将无法正常工作。CTRLSHIFTENTER

enter image description here

请注意，我的参数分隔符是;

评论

0赞 zack_la 7/12/2023
我希望你不介意，你是否介意非常简短地解释一下你提供的公式背后的逻辑，即如果可能的话，每个函数在做什么？
0赞 JvdV 7/12/2023
这可能无法按预期工作。将 D8 中的值更改为 1000，您就会明白原因。
0赞 zack_la 7/12/2023
啊，好地方，你是对的。
0赞 Foxfire And Burns And Burns 7/13/2023
@JvdV忘记添加公式必须作为数组公式输入。如果我将 D8 中的值更改为 1000，则单元格 k5 和 k7 返回 1000，因为该值与所有条件匹配。我不明白为什么你说这可能无法按预期工作
2赞 JvdV 7/13/2023
是的，如果我们改为 1000。在这种情况下，1009 会得到一个新数字 1000，应该保持空，因为现在已使用且不再可用。这个问题会扰乱大脑，但这样看：第一个要填写的单元格是，因为在考虑所有条件时，这将是与所有可能数字最接近的匹配。填写后，它将有效地使搜索范围为下一个最小的匹配项并报废使用的 .D8K5K7D8K2:K7K3ABS()D2:D8K3D2:D4D6:D8D5

上一个：条件 MODE 函数的参数

下一个：如果此条件匹配，则从 R 中的另一列返回值？