excel - 如何连续获得前 n 个非零值？

Question

我目前有这个公式，但它以我想要的相反顺序返回非零值。

这是公式：

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

当我希望它以另一种方式工作时，这将返回 BE 列中的第一个非零值，即从右到左的第一个实例。当我在右边起草公式时，我希望它从右到左拾取下一个非零值。

如果我能弄清楚这一点，以及如何返回列标题，那就太好了。

标签： excelexcel-formula

Answer 1

Screenshot/here refer.

The formula you provided returns the last non-zero value which is also what you state you're trying to find:

"I want...the first instance from right to left."

To avoid ambiguity, I provide solutions for the first such occurrence (and corresponding heading), and a revised method to determine the last occurrence (or first occurrence "from right to left").

First occurrence (working from left to right)

Function can be simplified significantly to the following:

=INDEX(A2:H2,0,MATCH(1,--(A2:H2<>0),0))

Corresponding heading:

=INDEX($A$1:$H$1,0,MATCH(1,--(A2:H2<>0),0))

Per @ScottCraner, this also works (albeit uses the same unnecessarily long function):

=INDEX($R275:$BE275,SMALL(IF($R275:$BE275<>0,COLUMN($R275:$BE275)- MIN(COLUMN($R275:$BE275))+1),COLUMNS($A:A)))

---

Last occurrence

Revised function to consider (requires Office 365 compatible version of Excel, and customisation to return corresponding heading)..

=LET(reverse,INDEX(A2:H2,0,SEQUENCE(1,COUNTA(A2:H2),COUNTA(A2:H2),-1)),INDEX(reverse,MATCH(1,--(reverse<>0),0)))