☰

需要最大值（例如 F:F 中的 1-100），但我可能需要从列表中撤回一些单元格

tag-icon • tag-icon microsoft-excel worksheet-function
需要最大值（例如 F:F 中的 1-100），但我可能需要从列表中撤回一些单元格

需要最大值（例如 1-100 内F:F）。但我可能需要从列表中撤回一些单元格。

例如，我有F:F。每个单元格将包含 1-100 之间的值，但我可能不需要某些单元格，但每次都不需要相同的单元格。

是否有一个公式可以查找（非功能性）列表，例如：

=MAX(F:F)-(F4,F8,F163,F354,F574)

我知道这不起作用，但我会不时地从此列表中添加和删除单元格，因为列表非常广泛，例如：

=MAX(F1:F5,F7:F16,F18:F41,F43:F66,F68:F91,F93:F197,F199:F219,F221:F291,F293:F312,F314:F900)

我知道这有效，但是天啊。每次都必须更改，这太可怕了。有没有更简单的方法？

答案1

您可以使用此数组公式：

=MAX(IF(ISERROR(MATCH("F" & ROW($F$1:INDEX($F:$F,MATCH(1E+99,$F:$F))) & "|",$H$1:INDEX($H:$H,MATCH("ZZZ",$H:$H))&"|",0)),$F$1:INDEX($F:$F,MATCH(1E+99,$F:$F))))

笔记：

  1. 作为数组公式，退出编辑模式时必须使用 Ctrl-Shift-Enter 而不是 Enter 进行确认。如果操作正确，Excel 会将{}公式放在一边。

  2. 要忽略的单元格必须一次将一个单元格地址放入一列中。我的单元格地址在 H 列中。

描述：

  1. 作为数组公式，我们希望限制对数据集的引用。 将会$F$1:INDEX($F:$F,MATCH(1E+99,$F:$F))做到这一点。 它将创建一个范围，包括 F1 到 F 列中最后一个包含数字的单元格。 H 列引用查找从 H1 到最后一个包含字符串的单元格的每个单元格。

  2. 然后，If 函数将返回一个数字和的数组FALSE。当行号等于单元格引用时，结果为 False。

  3. 添加是"|"为了确保我们不会得到假阴性。如果没有它，H 列中的引用F2将排除以 开头的所有单元格引用，F2包括F20, F21...

  4. MAX() 将忽略FALSE数组中返回的 s 并查找 IF 语句返回的数组中剩余的最大数字。

在此处输入图片描述

您还可以使用这个类似的公式，它不需要 Ctrl-Shift-Enter，而是正常输入。

=AGGREGATE(14,6,$F$1:INDEX($F:$F,MATCH(1E+99,$F:$F))/(ISERROR(MATCH("F" & ROW($F$1:INDEX($F:$F,MATCH(1E+99,$F:$F))) & "|",$H$1:INDEX($H:$H,MATCH("ZZZ",$H:$H))&"|",0))),1)

它仍然被视为数组公式，因此我们需要限制数据引用。

相关内容