动态数组公式：动态获取某列中首次出现#N/A值之前一行的数据

标签：动态数组

如下图1所示，在数据中有些为值错误#N/A数据，如果想要获取第一个出现#N/A数据的行上方行的数据（图中红色数据，即图2所示的数据），如何使用公式解决？

图1

图2

如示例图2所示，可以在单元格G2中输入公式：

=LET(data,A2:E18,i,MIN(IFERROR(BYCOL(data,LAMBDA(x,MATCH(TRUE,ISNA(x),0))),""))-1,DROP(TAKE(data,i),i-1))

即可获得想要的数据。

如果想要只获取第5列#N/A值上方的数据，则将公式稍作修改为：

=INDEX(LET(data,A2:E18,i,MIN(IFERROR(BYCOL(data,LAMBDA(x,MATCH(TRUE,ISNA(x),0))),""))-1,DROP(TAKE(data,i),i-1)),,5)

也可以使用公式：

=LET(d,FILTER(E2:E18,NOT(ISNA(E2:E18))),DROP(d,ROWS(d)-1))

如果数据区域中#N/A值的位置发生改变，那么上述公式会自动更新为最新获取的值。

自从Microsoft推出动态数组函数后，很多求解复杂问题的公式都得到的简化，很多看似无法用公式解决的问题也很容易用公式来实现了。

当然，也可以使用VBA来解决：

Sub CopyValues()
 Dim rng As Range
 Dim i As Long
 
 With Worksheets("Sheet1")
   Set rng = .Range("A2:E18").Find("#N/A", , xlValues, xlWhole, , , False)
   If Not rng Is Nothing Then
     i = rng.Row - 1
     .Range("A" & i & ":" & "E" & i).Copy .Range("G3")
   End If
 End With
End Sub