☰

获取连续最后 5 个非零值

tag-icon • tag-icon microsoft-excel microsoft-excel-2013
获取连续最后 5 个非零值

我有一个包含许多行的大型电子表格，其中的数据可以是零和正/负值，但都是数字值。我想编写一个公式（如果需要，可以编写五个公式）来告诉我每行的最后五个非零值是什么。

示例（一行数据）：1 - 5 - 0 - 8 - 10 - 0 - 7 - 0 - 3 - 4

在上述示例数据中，理想情况下公式（或 5 个公式）将返回：4 - 3 - 7 - 10 - 8

非常感谢您的帮助，我一直在为此绞尽脑汁。

-谢谢

答案1

您可以使用公式和几个辅助数组来实现这一点。假设您的数据位于 A 到 J 列，从第 1 行开始。使用 L 到 P 列来获取五个结果。R 到 AA 列是第一个辅助数组，AC 到 AL 列是第二个辅助数组。您可以隐藏辅助数组或将它们移动到其他地方（只需调整列引用计算）。

让我们从第一个辅助数组开始。Excel 有各种从左到右工作的函数，但没有从右到左工作的函数（除非您使用的是以色列版 :-) ）。第一个辅助数组只是反转了数据的顺序。单元格 R1 将包含：

    =OFFSET($A1,0,27-COLUMN(), )

将其复制到单元格 S1 至 AA1。

第二个辅助数组确定非零值的位置和序列号。单元格 AC1 包含：

    =IF(R1=0,"",COLUMN()-28-COUNTIF($R1:R1,0))

将其复制到 AD 至 AL 列。

现在来看看结果值。在 L1 中输入此公式，并通过 P1 复制到 M1：

    =INDEX($R1:$AA1, ,MATCH(1,$AC1:$AL1,0))

编辑 M 到 P 公式，将 MATCH 函数中的搜索参数替换为 2 到 5，因此 M 到 P 公式将是：

    =INDEX($R1:$AA1, ,MATCH(2,$AC1:$AL1,0))
    =INDEX($R1:$AA1, ,MATCH(3,$AC1:$AL1,0))
    =INDEX($R1:$AA1, ,MATCH(4,$AC1:$AL1,0))
    =INDEX($R1:$AA1, ,MATCH(5,$AC1:$AL1,0))

现在，可以根据需要将所有公式复制到工作表中。

答案2

假设你的值在 A1:J1 中，这数组公式**例如在 L1 中：

=INDEX($A1:$J1,LARGE(IF($A1:$J1<>0,COLUMN($A1:$J1)-MIN(COLUMN($A1:$J1))+1),COLUMNS($A:A)))

向右复制另外 4 列。

这也可以向下复制以得到 A2:J2、A3:J3 等中数据的类似结果。

问候

**数组公式的输入方式与“标准”公式不同。您不必直接按 ENTER，而是先按住 CTRL 和 SHIFT，然后按 ENTER。如果您操作正确，您会注意到 Excel 在公式周围放置了花括号 {}（但不要尝试自己手动插入这些括号）。

相关内容