首页 > Excel专区 > Excel教程 >

excel图表如何查找指定数据

Excel教程 2022-01-08 21:15:37

excel图表如何查找指定数据?用一个示例来说明,如下图1所示,是一个记录员工值班日期的表,在安排每天的值班时,需要查看员工最近一次值班的日期,以免值班时间隔得太近。例如,可以查到张无忌最近是2019年9月9日值班,因此下一天的值班就不会安排张无忌了。现在就是要求给出张无忌后,获得他最近值班的日期2019年9月9日,对于其他的员工也是这样。

图1

下面,我们分别使用公式和VBA来解决。

使用INDEX+SUMPRODUCT+MAX+ROW函数

公式如下:

=INDEX($B$2:$B$10,SUMPRODUCT(MAX(ROW($A$2:$A$10)*($D$2=$A$2:$A$10))-1))

公式先比较单元格D2中的值与单元格区域A2:A10中的值,如果相同返回TRUE,不相同则返回FALSE,得到一个由TRUE和FALSE组成的数组,然后与A2:A10所在的行号组成的数组相乘,得到一个由行号和0组成的数组,MAX函数获取这个数组的最大值,也就是与单元格D2中的值相同的数据在A2:A10中的最后一个位置,减去1是因为查找的是B2:B10中的值,是从第2行开始的,得到要查找的值在B2:B10中的位置,然后INDEX函数获取相应的值。之所以使用SUMPRODUCT函数,是因为该函数可以处理数组公式,而无须在公式输入完成后按Ctrl+Shift+Enter组合键。

结果如下图2所示。

图2

使用LOOKUP函数

公式如下:

=LOOKUP(2,1/($A$2:$A$10=$D$2),$B$2:$B$10)

公式中,比较A2:A10与D2中的值,相等返回TRUE,不相等返回FALSE,得到由TRUE和FALSE组成的数组,然后使用1除以这个数组,得到由1和错误值#DIV/0!组成的数组,由于这个数组中找不到2,LOOKUP函数在数组中一直查找,直至最后一个比2小的最大值,也就是数组中的最后一个1,返回B2:B10中对应的值,也就是要查找的数据在列表中最后的值。

结果如下图3所示。

图3

使用VBA自定义函数

在VBE中输入下面的代码:

Function LookupLastItem(LookupValue AsString, _

LookupRange As Range, _

ColNum As Integer)

Dim i As Long

With LookupRange

For i = .Columns(1).Cells.Count To 1 Step -1

If LookupValue = .Cells(i, 1) Then

LookupLastItem = .Cells(i, ColNum)

Exit Function

End If

Next i

End With

End Function

然后,在工作表中像Excel内置函数一样,使用公式:

=LookupLastItem($D$2,$A$2:$B$10,2)

结果如下图4所示。

图4

无论使用上述哪种方法,最终的结果如下图5所示。

图5


标签: Excel图表制作Excel常用函数excel数据透视表excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.