时间戳

首页 > 解决方案 > 是否可以使用公式在跳过选定单元格上方和下方的数组中找到最大值（中间）

excel - 是否可以使用公式在跳过选定单元格上方和下方的数组中找到最大值（中间）

问题描述

是否可以使用公式来执行此操作，因为选定的不是固定到特定的单元格，而是可以在列中的任何位置？

*更新更多细节，这不是1次选择，选择过程不断发生，当我们获得最大值后，数字将+1并放入该选择单元格。

然后重复此过程以进行下一个选择。

或者有什么替代建议吗？

谢谢

在此处输入图像描述

标签： excelexcel-formula

解决方案

这是我不太喜欢的使用 FILTER 的解决方案。它使用蛮力，看起来不优雅。我确信有更好的解决方案。

在此处输入图像描述

首先，创建一个帮助列来对您的项目进行分组：

=IF(A2 = "", "", COUNTIF(A$2:A2, "") + 1)

将 COUNTIF() 添加到 B2:B14。COUNTF() 周围的 IF() 只是告诉它返回一个空白字符串，如果它在 Ax 中没有任何内容的行上。我将 +1 与 COUNTIF() 结果一起使用，因为过滤器不适用于 0。（#%^@！Excel 无法区分空单元格和其中包含 0 的单元格。这怎么可能？是的。）

现在在 C 中添加另一个辅助列来获取 Ax 中的数字部分：

=NUMBERVALUE(MID(A2, 2, 50))

这将获取 Ax 中第一个字符之后的所有内容并将其转换为一个数字。这是我的懒惰 - 在 Excel 中获得一个数字的最大值比获得一个字符串要容易得多。

因此，现在您将项目与 Col B 中的组 ID 和 C2:C14 中的 A2:A14 的数字部分分组。现在我们可以使用 FILTER() 从每个组中获取最大值。

MAX(FILTER(C2:C14, B2:B14 = B2))

这将返回 7。它根据 B2 的值和 B2:B14 中的值过滤 C2:C14。这很好，但 7 不是我们想要的。我们想要A07。所以我们可以在它周围包裹一个 XLOOKUP() （或另一个查找函数）以在 Col A 中找到相应的值：

XLOOKUP(MAX(FILTER(C2:C14, B2:B14 = B2)), FILTER(C2:C14, B2:B14 = B2), FILTER(A2:A14, B2:B14 = B2)))

这有点难看。我们第一个 FILTER() 和 MAX() 告诉 XLOOKUP() 我们正在寻找什么值。第二个过滤器告诉 XLOOKUP() 我们要在哪里搜索该值：B:B 等于 B2 中的值。最后一个过滤器告诉 XLOOKUP() 从哪里获取返回值，即 A 列。

在每组的最后一行显示结果更容易，但也可以在组之间的间隙中显示。要显示 Col A 中一组项目的最大值，我们可以在 XLOOKUP() 前面添加这个 IF()：

IF(OR(B3 = B2, B2 = ""), "", XLOOKUP(MAX(FILTER(C2:C14, B2:B14 = B2)), FILTER(C2:C14, B2:B14 = B2), FILTER(A2:A14, B2:B14 = B2)))

IF() 将 B 的当前行值与 B 的下一行值进行比较，以查看它们是否在同一组中。当他们不在同一个组中时，必须是最后一行。我们还 OR() 检查空白行，因此我们也不会在空白行中显示结果。

您可以使用以下函数从一组文本项中获取最大值，而不是使用 MAX()：

LOOKUP(2,1/(COUNTIF(A2:A4,">"& A2:A4)=0), A2:A4)

那不是我的解决方案。它很聪明，效果很好。在这种情况下，使用 MAX() 更容易说明。

如果您无权访问 FILTER() ，则需要一些其他数组技巧才能使其正常工作。希望你有一个带有 FILTER() 的版本。

推荐阅读