☰

在一列中查找零值并获取下一列中高于/低于的值

tag-icon • tag-icon worksheet-function microsoft-excel-2010
在一列中查找零值并获取下一列中高于/低于的值

我有一个来自传感器的温度列表，其中包括传感器关闭时的零重置值。

A       B       C
res      0
tmp   20,5
tmp   20,3
res      0
tmp   20,4
tmp   20,1
tmp   20,2
res      0
res      0
tmp   20,6

我能够使用以下公式找到这样的行并从中获取值B，即零：

=INDEX(B1:10,MATCH("res",A1:A10,0))

但这对我没有好处。我想要的是让公式找到包含的行res，A然后查看B列并找到上方或下方的第一个非零值（如果找不到这样的行），例如A1。

所以我的数据应该是这样的：

A       B       C
res      0    20,5
tmp   20,5    20,5
tmp   20,3    20,3
res      0    20,3
tmp   20,4    20,4
tmp   20,1    20,1
tmp   20,2    20,2
res      0    20,2
res      0    20,2
tmp   20,6    20,6

将不胜感激任何对此提供的帮助。

答案1

如果您希望在数据开头允许任意数量的重置，并且不使用任何辅助列，则公式为小的复杂的：

工作表截图

输入以下公式C1，然后按 ctrl-enter/copy-paste/fill-down/auto-fill 将其填充到表格其余列中：

=
INDEX(
  (B:B),
  IFERROR(
    1/(1/SUMPRODUCT(LARGE(ROW(A$1:A1)*(A$1:A1<>"res"),1))),
    ROW()-1+MATCH("tmp",A1:INDEX(A:A,ROWS(A:A)),0)
  )
)


解释：

该LARGE(ROW(A$1:A1)*(A$1:A1<>"res"),1)函数用于返回res从当前行向上的最大行号（即最接近的）非行。（SUMPRODUCT()封装它只是为了确保将内部范围作为数组进行评估，而无需将公式输入数组。）如果有不这种非res行，SUMPRODUCT(LARGE(…))计算结果为0。

对于非零价值，计算结果为IFERROR(1/(1/value),expression)价值（即最接近的向上非res行号）。对于零价值，其计算结果为表达（因为1/(1/0))导致#DIV/0!错误）。

因此，对于从第一行开始的每个连续重置值，ROW()-1+MATCH("tmp",A1:INDEX(A:A,ROWS(A:A)),0)都会进行评估。这只会返回从当前行向下的第一行的行号，即有效的温度读数。（只是从对应于当前行A1:INDEX(A:A,ROWS(A:A))的列中的单元格的范围，即对于在中输入的公式，到列的最后一个单元格的范围。）AA1C1AINDEX(A:A,ROWS(A:A))

最后，我们就可以提取出适当的温度读数。INDEX((B:B),row_number)


逐步执行公式C10应该会使上述内容更加清晰：

  • ROW(A$1:A10)*(A$1:A10<>"res")
    → {1;2;3;4;5;6;7;8;9;10}*({"res";"res";"tmp";"tmp";"res";"tmp";"tmp";"tmp";"res";"res"}<>"res")
    → {1;2;3;4;5;6;7;8;9;10}*{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE}
    → {1;2;3;4;5;6;7;8;9;10}*{0;0;1;1;0;1;1;1;0;0}
    →{0;0;3;4;0;6;7;8;0;0}
  • 1/(1/SUMPRODUCT(LARGE({0;0;3;4;0;6;7;8;0;0},1)))
    → 1/(1/SUMPRODUCT(8))（因为LARGE()返回第 n 个最大值，其中 n 是第二个参数）→ 1/(1/8)
    → 1/0.125
    →8
  • =INDEX((B:B),IFERROR(8,…))
    → =INDEX(B:B,8)
    →=20,2


笔记：

  • 多行、美化的公式实际上可以按原样输入。
  • 周围的括号(B:B)仅用于强制B:B将其保持在自己的一行上。


如果您想要一个更简单的公式，那么您需要使用辅助列（D）：

工作表截图

输入以下公式C1，然后按 ctrl-enter/copy-paste/fill-down/auto-fill 将其填充到表格其余列中：

=IF(A1="res",IF(ROW()=1,D1,INDEX(C:C,ROW()-1)),B1)

输入以下公式D1，然后按 ctrl-enter/copy-paste/fill-down/auto-fill 将其填充到表格其余列中：

=IF(A1="res",D2,B1)


解释：

  • 辅助列：

    • 如果当前行包含重置值，则列中的公式D将从正下方的单元格复制该值。
    • 否则，它将从同一行或同一列中获取值B。
    • 从列中的最后一行数据向上计算，可以得出D1列中第一个有效读数B。

  • 主要栏目：

    • 对于重置的行，列中的公式会检查它是否在第一行，如果是，则使用与同一行对应的C列单元格的值，即。如果公式在列的任何其他行中，则它会从紧邻上方的单元格中获取值。DD1C
    • 如果该行是有效的温度行，则公式将从B与同一行对应的列中的单元格中获取温度读数。

答案2

您只需查找“res”并获取上面的值即可。
即使有多个零值，它们也应该指向上面的非零值，因为我们刚刚更改了它。

因此在 C1 中

=B1

然后C2

=IF(A2<>"res",B2,C1)

然后向下填充。
只要 B1 不为 0，此方法即可。
您也可以添加公式来处理此问题，但由于它只有一个单元格，我认为手动检查会更容易，因为您将另一个公式放在它旁边。

相关内容