Excel Match If Greater than Zero

问题:

I'm working in excel and i want to use the formula MATCH to retrieve the row of each number greater than zero in a column. For example, having the next column

Number 
  0
  0
  6
  1
  0
  8
  0 
  0
  0
  0
  10

I want to obtain in other column the following:

Rows
  3
  4
  6
 11

¿Is it posible to do this with the MATCH formula? If not, then ¿How can achieve this?

回答1:

Assuming your data is in the range A2:A12, then try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

In B2

=IFERROR(SMALL(IF($A$2:$A$12>0,ROW($A$2:$A$12)-ROW($A$2)+1),ROWS(B$2:B2)),"")

Confirm with Ctrl+Shift+Enter and then copy it down until you get blank cells.

Adjust the ranges as per requirement, but don't refer the whole column reference like A:A in the formula.

回答2:

B1:    =MATCH(TRUE,A:A>0, 0)                              Ctrl+Shift+Enter

B2:    =IFERROR(MATCH(TRUE,(A:A>0)*ROW(A:A)>B1, 0),"")    Ctrl+Shift+Enter

copy B2 and paste along column B.

回答3:

Assuming your data is in the range A2:A12.

1. Select the cells B2:B12. Press F2. It will allow you to enter values in the B2 cell.
2. Copy paste the below formula in the B2 and then press Ctrl+Shift+Enter

=IFERROR(AGGREGATE(15,6,IF(A2:A12<>0,ROW()-ROW(A1),NA),ROW()-ROW(B1)),"")

Note if you select only B2 and enter the formula then you will get only the top one value. Adjust ranges accordingly if required.