☰

在 Excel 2016 中查找两个日期之间的所有缺失日期

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
在 Excel 2016 中查找两个日期之间的所有缺失日期

我的表格中有超过 500,000 行数据。在该表格的 D 列中，每行都有一个介于 2016 年 9 月 18 日至 2021 年 5 月 31 日之间的日期。我想使用数组公式列出我刚刚给出的这些日期之间未在 D 列中显示的所有日期。 编辑：我想使用一个公式，因为我的结束日期 2021 年 5 月 31 日每个月都会更改为更晚的日期。

在 Excel 中，使用预定义函数可以实现这一点吗？还是我需要编写宏？如果可以的话，我会发布我开始的内容，但我不知道从哪里开始。

答案1

这是我的答案，但需要您在日期列后插入一列（除非日期是最后一列）。最终结果是：

在此处输入图片描述

图像显示 1) 插入到工作表中的 B 列，2) C 列是所需范围的开始和结束日期，3) E 列中的溢出公式可为您提供缺失的日期。公式（对于此示例）为：

=FILTER(IFNA(VLOOKUP(SEQUENCE(+C2-C1+1,1,C1,1),A1:B31,2,FALSE),SEQUENCE(+C2-C1+1,1,C1,1)),IFNA(VLOOKUP(SEQUENCE(+C2-C1+1,1,C1,1),A1:B31,2,FALSE),SEQUENCE(+C2-C1+1,1,C1,1))<>0,"no missing dates")

解释：

SEQUENCE()根据 C 列中的日期范围创建一个日期数组

VLOOKUP()如果匹配，则使用 SEQUENCE 返回 B 列的空白（零）值

IFNA()如果 VLOOKUP 失败（即缺少日期），它将返回缺少的日期（再次是 SEQUENCE）

FILTER()仅返回缺失的日期

答案2

使用 Excel 2016 中的数组公式执行此操作将非常复杂。

为什么不使用一种不涉及复杂公式或 VBA 的更简单的方法呢？

创建从开始日期到结束日期的日期列表。在下一列中，使用 Match 公式查找数据中的日期。您可以使用返回 TRUE/FALSE 结果的公式，例如

=isnumber(Match(A1,$Z:$Z,0))

如果匹配的结果是一个数字，即找到日期，则公式返回 TRUE，否则返回 FALSE。

现在您只需要过滤日期列表以仅显示公式列中带有“FALSE”的行。

这比编写 VBA 或复杂数组要快得多。

笔记：这仅在您的日期仅为日期而非日期/时间值时才有效。如果您有日期/时间值（如果格式化为常规，则显示小数），则您需要在数据源中有一个仅提取日期的辅助列。然后在该日期列上运行 Match()。

答案3

我最终为此编写了一个宏。它不是最好的，因为它使用直接单元格引用，但我在下面添加了注释来解释所有内容，所以也许有一天它会对某人有用。

Private Sub CommandButton1_Click()

'on a button click, loop through list of dates on one worksheet to 
'find missing ones between two dates specified on another worksheet

Dim lr As Long
Dim r As Range, dateslist As Range
Dim startD As Date, endD As Date, da As Date
Dim wb As Workbook
Dim frontSht As Worksheet, rawSht As Worksheet

Set wb = ThisWorkbook
'the sheet where we want missing dates displayed:
Set frontSht = wb.Sheets("frontPage") 
'the sheet where our dated data rows live:
Set rawSht = wb.Sheets("rawData") 'the sheet where our dated data rows live

'Cell B1 on the front sheet has the start date of the range we care about:
startD = frontSht.Range("B1").Value 
'Cell B2 has the end date of that range:                             
endD = frontSht.Range("B2").Value

'Get the last row with data's number:
lr = rawSht.Cells(Rows.Count, 4).End(xlUp).Row 

'define the date list to look through and include the blank cell below our last date: 
Set dateslist = rawSht.Range("D2:D" & (lr + 1)) 

'no screen updating during the following loops:
Application.ScreenUpdating = False 

'The list of missing dates we are about to generate will start in this cell:
frontSht.Range("A7").Activate 

'For each date in our desired range, loop thru dates in the data looking for a match
'If we find one, exit the inner loop and move to the next date in our range.
'If we don't find one and reach the blank box at the end of the data range,
'write that date for which no data exists into our 'missing dates' list.
For da = startD To endD
    For Each r In dateslist
        If da = r.Value Then Exit For
        If IsEmpty(r) Then
            ActiveCell.Value = da
            ActiveCell.Offset(1, 0).Activate
        End If
    Next r
Next da

End Sub

如果您在像我这样的大型数据集上运行此宏（4.5 年范围，并且每天最多要查看 500,000 行），则需要花费一些时间。我相信有更好的方法可以做到这一点。如果您知道更好的方法，请随时告诉我们。

答案4

=FILTER((SEQUENCE(A13-A2+1,1,MIN(A:A))),NOT(ISNUMBER(MATCH((SEQUENCE(A13-A2+1,1,A2)),A2:A13,0))))

带有标题的 A 列日期列表，用最后一个包含数据的单元格替换 A13 的三个实例。

这将创建一个包含所有日期的新日期列表，然后根据 A 列中未包含的日期进行筛选。

相关内容