Excel：有没有一个函数可以返回满足匹配条件的范围？ [关闭]

给定一个值与后续行（同一列上）进行比较，并返回直到该值第一次更改的行的范围。

Example:
   A
1  cat
2  cat
3  cat
4  dog
5  cat

返回的范围是A1:A3。

Assuming you have cat in cell c1, the following formula will return the first continuous matching range. Enter with ctrl+shift+enter

=COUNTIF(OFFSET(A1:A5,MATCH(C1,A1:A5,0)-1,0,MATCH(TRUE,OFFSET(A1:A5,MATCH(C1,A1:A5,0),0)<>C1,0)),"cat")

If your data always start in A1 and you want the whole range matching the first value, all the way until the next value, use the following array formula (entered with ctrl+shift+enter):

=OFFSET($A$1,0,0,MATCH(TRUE,A:A<>A1,0)-1)