在本教程中,您将学习如何使用Excel 公式查找列表中最后一次出现的项目。
最近,我正在制定会议议程。
我在Excel 中有一个列表,其中有一个人员列表以及他们担任“会议主席”的日期。
由于列表中有重复(这意味着一个人曾多次担任会议主席),我还需要知道一个人最后一次担任“会议主席”是什么时候。
这是因为我必须确保不会再次分配最近担任主席的人。
所以我决定使用一些Excel 函数魔法来完成这项工作。
下面是最终结果,我可以从下拉列表中选择一个名称,它为我提供了该名称在列表中最后一次出现的日期。
如果您对Excel 函数有很好的了解,您就会知道没有一个Excel 函数可以做到这一点。在这里我们让奇迹发生。
在本教程中,我将向您展示执行此操作的三种方法。
查找最后一次出现– 使用MAX 函数
这是将返回列表中最后一个值的Excel 公式:
=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
下面是这个公式的工作原理:
- MAX 函数用于查找最后匹配名称的行号。例如,如果名称是Glen,它将返回11,因为它位于第11 行中。由于我们的列表从第二行开始,因此减去了1。因此,Glen 最后出现的位置在我们的列表中是10。
- SUMPRODUCT 用于确保您不必使用Control + Shift + Enter,因为SUMPRODUCT 可以处理数组公式。
- INDEX 函数现在用于查找最后一个匹配名称的日期。
查找最后一次出现– 使用LOOKUP 功能
这是完成相同工作的另一个公式:
=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
下面是这个公式的工作原理:
- 查找值为2(你会明白为什么.. 继续阅读)
- 查找范围是1/($A$2:$A$14=$D$3) – 当它找到匹配的名称时返回1,否则返回错误。所以你最终得到一个数组。例如,查找值為 Glen,數組將為 {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}。
- 第三个参数([result_vector]) 是它给出结果的范围,在这种情况下是日期。
这个公式起作用的原因是LOOKUP 函数使用了近似匹配技术。这意味着如果它可以找到精确匹配的值,它将返回,但如果它不能,它将扫描整个数组直到最后并返回小于查找值的下一个最大值。
在这种情况下,查找值为2,在我们的数组中,我们只会得到1 或错误。所以它扫描整个数组并返回最后一个1 的位置——这是名称的最后一个匹配值。
查找最后一次出现– 使用自定义函数(VBA)
让我也向您展示另一种方法。
我们可以使用VBA 创建自定义函数(也称为用户定义函数)。
创建自定义函数的好处是易于使用。您不必每次都担心创建复杂的公式,因为大部分工作都发生在VBA 后端。
我创建了一个简单的公式(很像VLOOKUP 公式)。
要创建自定义函数,您需要在VB 编辑器中有VBA 代码。稍后我会给你代码和将它放入VB 编辑器的步骤,但让我首先向你展示它是如何工作的:
这是将为您提供结果的公式:
=LastItemLookup($D$3,$A$2:$B$14,2)
该公式采用三个参数:
- 查找值(这将是单元格D3 中的名称)
- 查找范围(这将是具有名称和日期的范围– A2:B14)
- 列号(这是我们想要结果的列)
创建公式并将代码放入VB 编辑器后,您可以像使用任何其他常规Excel 工作表函数一样使用它。
这是公式的代码:
Function LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Columns(1).Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells(i, 1) Then LastItemLookup = LookupRange.Cells(i, ColumnNumber) Exit Function End If Next i End Function
以下是将此代码放入VB 编辑器的步骤:
- 转到开发人员选项卡。
- 单击Visual Basic 选项。这将在后端打开VB 编辑器。
- 在VB 编辑器的Project Explorer 窗格中,右键单击要在其中插入代码的工作簿的任何对象。如果您没有看到Project Explorer,请转到View 选项卡并单击Project Explorer。
- 转到插入并单击模块。这将为您的工作簿插入一个模块对象。
- 将代码复制并粘贴到模块窗口中。
现在该公式将在工作簿的所有工作表中可用。
请注意,您需要将工作簿保存为.XLSM 格式,因为其中包含宏。此外,如果您希望此公式在您使用的所有工作簿中可用,您可以将其保存到个人宏工作簿或从中创建加载项。