☰

INDEX / MATCH - 返回最接近 TODAY() 的结果

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
INDEX / MATCH - 返回最接近 TODAY() 的结果

如何更改匹配函数以按输入的日期Column G降序返回结果？TODAY()C2

下面的函数按预期工作，但结果不符合要求的顺序，其中第一个匹配项是与日期最接近的项目C2

=IFERROR(INDEX($A$2:$A$500,SMALL(IF($B$2:$B$500>=$C$2,IF($B$2:$B$500<=$E$2,ROW($A$2:$A$500)-ROW($A$1),9^99),9^99),ROW()-ROW($A$1))),"")

可以在以下链接中找到 Google Sheet 示例：https://docs.google.com/spreadsheets/d/15adNP6hpUDh9w3Ai6C-nrkcMkYl_QfM6TwD9axLED3Q/edit?usp=sharing

在此处输入图片描述

答案1

根据我对您问题的最佳理解，我建议采用基于典型 INDEX MATCH 但使用数组公式的解决方案。数组公式在应用于大型数据集时可能会相对较慢。

如果我理解正确的话，您需要按从最接近今天（C2）到最远的顺序获取 A 中开始日期和结束日期之间的所有项目，如果有重复项（不同编号但相同到期日期），则按它们在 A 中出现的顺序获取它们。

看看这个解决方案是否适合您。

在此示例中，样本数据位于 ABCD 列和 E 列。

对于示例数据，行引用自 A2:A22。请将其扩展到所需值。如果有 1000 行，则将其设置为 A:2000 左右。

在 F2 中输入以下公式，然后按公式栏中的CTRL+ SHIFT+ENTER创建数组公式。现在将其向下拖动到所需的行。

=IFERROR(INDEX($A$2:$A$22,IF(SMALL(IF(($B$2:$B$22>=$C$2)*($B$2:$B$22<=$E$2),$B$2:$B$22-$C$2+(0.0000001*IF(($B$2:$B$22>=$C$2)*($B$2:$B$22<=$E$2),ROW($B$2:$B$22))),9^99),ROW()-ROW($F$1))=9^99,9^99,MATCH(SMALL(IF(($B$2:$B$22>=$C$2)*($B$2:$B$22<=$E$2),$B$2:$B$22-$C$2+(0.0000001*IF(($B$2:$B$22>=$C$2)*($B$2:$B$22<=$E$2),ROW($B$2:$B$22))),9^99),ROW()-ROW($F$1)),IF(($B$2:$B$22>=$C$2)*($B$2:$B$22<=$E$2),$B$2:$B$22-$C$2+(0.0000001*IF(($B$2:$B$22>=$C$2)*($B$2:$B$22<=$E$2),ROW($B$2:$B$22))),9^99),0))),"")

现在在 Col F 中它将从 A 中获取所需的数据。

在 G2 中输入以下简单的 VLOOKUP 公式并将其拖到所需的行。

=IFERROR(VLOOKUP(F2,$A$2:$B$13,2),"")

在此处输入图片描述

但是，不使用数组公式的解决方案同样可以使用辅助列。

更新

Col F 中的公式与上面已经发布的公式相同，但是现在 Col B 使用 H 中的公式获取日期。

B2 中的公式是

=IFERROR(DATE(LEFT(H2,4),MID(H2,6,2),MID(H2,9,2)),"")

将其向下拖动到目标行。这会将 H 列中表示日期的文本数据（YYYY-MM-DDT00:00:00 格式）解析为实际日期。B 列也被格式化为日期。

在此处输入图片描述

答案2

注意，如果您对此答案满意，我将删除我的其他答案

初始设置

我填充的数据与您的完全一致。请注意，由于我的“今日”单元格是=Today()，并且您几天前发布了这个问题，所以我的日期与您的不同；但是，同样的想法仍然适用于您正在做的事情。

在此处输入图片描述

我现在更好地理解了，你的Today字段和Months字段只是驱动输出的输入。看看我现在如何标记它们以使其清晰。无论哪种方式，请查看输出，因为它会找到所有符合greater than or equal to todayAND的数据less than or equal to today + number of specified months

一切奇迹发生的地方

因此，这其中最重要的部分就是我的第 I 列中发生的情况。我在单元格 I2 中的公式是：

=MIN(IF(C:C>=$E$2,IF(C:C<=DATE(YEAR($E$2),MONTH($E$2)+$F$2,DAY($E$2)),IF(COUNTIF($I$1:I1,C:C)=0,C:C))))

确保在输入此公式时按Shift+Enter而不是只按Enter

公式解释及其工作原理

从本质上讲，这个公式基本上是始终按照我们的逻辑修改数组，直到得到最终数组，然后从中找到最小值。请参阅图表以了解正在执行的操作：

在此处输入图片描述

因此，第一部分是过滤数组是否大于今天：

IF(C:C>=$E$2,C:C) 此时结果仍是一个数组

为了扩展此功能以过滤掉日期小于今天的日期 + x 个月的情况，我们也添加了该子句：

IF(C:C>=$E$2,IF(C:C<=DATE(YEAR($E$2),MONTH($E$2)+$F$2,DAY($E$2)),C:C)) 此时结果仍是一个数组

**请注意，我过去常常DATE(YEAR($E$2),MONTH($E$2)+$F$2,DAY($E$2))计算today + x months日期的实际情况，因为简单地说月份数 * 30 是不准确的，因为并非所有月份都包含 30 天。此方法考虑到了这一点。

下一步很重要，也是您遗漏的部分 - 如何过滤掉已经找到的项目？因此，以类似的方法，我采用当前数组，然后简单地检查先前的结果，因为我将拖拽此公式：

PRIOR_CODE(IF(COUNTIF($I$1:I1,C:C)=0),C:C)

该$I$1:I1部分的第一部分是静态的（美元符号），这样当我们将公式向下拖动时它不会改变，而第二部分不会改变，所以它会改变。这会导致当前单元格之前的所有数据移动范围。我只是使用一个COUNTIF()函数来报告当前数组中每个项目已经报告了多少。周围的IF()状态只保留那些等于 0 的状态COUNTIF，这与声明相同not currently reported。

现在，如果你到目前为止都遵循了这些，那么你就处于一个很好的位置！最后一部分是简单地使用MIN()从我们最新的过滤数组中获取最小值，结果就是我最初展示的结果：

=MIN(IF(C:C>=$E$2,IF(C:C<=DATE(YEAR($E$2),MONTH($E$2)+$F$2,DAY($E$2)),IF(COUNTIF($I$1:I1,C:C)=0,C:C))))

现在您可以向下滚动此公式，其中唯一发生变化的值就是这部分：

$I$1:I1

它将变成$I$1:I1、$I$1:I2、$I$1:I3等等。

最简单的部分

如果你能做到上述这些，那么报告相应的字母就是小菜一碟，因为你所需要做的VLOOKUP就是INDEX(MATCH())

在我的单元格 H2 中我输入：

=INDEX(B:B,MATCH(I2,C:C,0))

并向下拖动。它将查找找到的日期的给定字母。

答案3

由于原始海报对我在其他答案中使用数组的速度不满意，因此这里有一个可以解决的 VBA 解决方案

Public Sub find_values_in_date_range()
Dim min_date As Date
Dim max_date As Date

Dim output_row As Integer

Dim number_column As String
Dim sheet_name As String
Dim min_date_cell As String
Dim number_days_forward_cell As String
Dim date_col As String

Dim output_number_col As String
Dim output_date_col As String

'~~~VVV~~~Define these variables to whatever you have in your own sheet!!~~VVV~~~~~

number_column = "B"
sheet_name = "Sheet1"
min_date_cell = "E2"
number_days_forward_cell = "F2"
date_col = "C"
output_number_col = "H"
output_date_col = "I"
output_row = 2 'initial row to output to

'~~^^^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^~~~~~~~~

'clear existing data from output columns
ThisWorkbook.Sheets(sheet_name).Range(output_number_col & "2:" & output_number_col & "10000").ClearContents
ThisWorkbook.Sheets(sheet_name).Range(output_date_col & "2:" & output_date_col & "10000").ClearContents

min_date = ThisWorkbook.Sheets(sheet_name).Range(min_date_cell).Value 'define start date
max_date = DateAdd("m", ThisWorkbook.Sheets(sheet_name).Range(number_days_forward_cell).Value, min_date) 'define end date

For Row = 2 To ThisWorkbook.Sheets(sheet_name).Range(number_column & "1").End(xlDown).Row
        If ThisWorkbook.Sheets(sheet_name).Range(date_col & Row).Value >= min_date And ThisWorkbook.Sheets(sheet_name).Range(date_col & Row).Value <= max_date Then
            ThisWorkbook.Sheets(sheet_name).Range(output_number_col & output_row).Value = ThisWorkbook.Sheets(sheet_name).Range(number_column & Row).Value
            ThisWorkbook.Sheets(sheet_name).Range(output_date_col & output_row).Value = ThisWorkbook.Sheets(sheet_name).Range(date_col & Row).Value
            output_row = output_row + 1
        End If
Next

End Sub

因此按Alt+打开 VBA，单击插入、模块。复制并粘贴此方法。更改我定义、等的F11部分，将您的 Excel 工作簿保存为扩展名。number_columnsheet_name.xlsm

为了运行 VBA 子程序，您可以：

  • 导航到开发人员功能区 > 单击Macros> 选择您的宏（在我的情况下，我将其命名为 find_values_in_date_range）> 单击Run
  • 单击“插入”>“形状”> 创建您想要的任何形状。在新形状上，右键单击Assign Macro，然后选择 find_values_in_date_range 并单击“确定”。现在，只要您单击该形状，它就会运行宏。

如果这最终满足了你的要求，我将详细说明宏的工作原理，请告诉我

相关内容