尝试在 Excel 中查找多个条目之间的日期差异

Question

实现此目的的方法有很多，我将尝试发布一些方法，就我所知，使用Excel Formulas。还有一个灵活的选项，那就是使用POWER QUERY。

使用 Excel 公式：

• 单元格中使用的公式F2返回唯一发票

=UNIQUE(A2:A8)

• 单元格中使用的公式G2返回开始日期和结束日期

=AGGREGATE({15,14},6,B$2:B$8/($F2=A$2:A$8),1)

•#在单元格中输入以下内容I2

=H2-G2+1

以上所有公式均需填写，除发票公式外。

替代方法 2

以下公式是MS365独有的。

=LET(
     _UniqInv, UNIQUE(A2:A8),
     _LastestDate, MAXIFS(B2:B8,A2:A8,_UniqInv),
     _PriorDate, MINIFS(B2:B8,A2:A8,_UniqInv),
     _DataBody, HSTACK(_UniqInv, _PriorDate, _LastestDate, _LastestDate-_PriorDate+1),
     VSTACK({"Unique Invoice","Prior Date","Latest Date","# Of Days"},_DataBody))

使用UNIQUE()函数返回唯一的发票。
用于MAXIFS()返回最新日期。
用于MINIFS()返回开始日期。
用于HSTACK()合并以上所有三个，并与第二和第三个变量进行减法以返回#天数。（笔记：天数是包括所有之前的日期，如果您不想要相同的，则排除+1）。
最后，使用VSTACK()将整体与标题一起附加。

另一种方法是使用新的测试版启用功能MS365-GROUPBY()

=LET(
     _Data, DROP(GROUPBY(A2:A8,B2:B8,HSTACK(MIN,MAX),0,0),1),
     _NoOfDays, INDEX(_Data,,3)-INDEX(_Data,,2)+1,
     VSTACK({"Unique Invoice","Prior Date","Latest Date","# Of Days"},
     HSTACK(_Data,_NoOfDays)))

这也可以使用和Power Query来实现Windows Excel 2010+Excel 365 (Windows or Mac)

要使用 Power Query，请按照以下步骤操作：

首先将源范围转换为表格并进行相应命名，在此示例中，我将其命名为Table1

Data接下来，从Tab --> Get & Transform Data--> Get Data--> From Other Sources-->打开一个空白查询Blank Query

上面的命令可以Power Query打开窗口，现在从HomeTab --> Advanced Editor--> 删除您看到的任何内容并粘贴以下内容M-Code，然后按Done

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Invoice"}, {{"Start_Date", each List.Min([#"Entered/Modified"]), type datetime}, {"End_Date", each List.Max([#"Entered/Modified"]), type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "No_Of_Days", each Duration.Days([End_Date]-[Start_Date])+1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Invoice", type text}, {"Start_Date", type date}, {"End_Date", type date}, {"No_Of_Days", Int64.Type}})
in
    #"Changed Type"

最后，将其导回Excel--> 单击Close & Load或Close & Load To--> 第一个单击的将创建一个New Sheet具有所需输出的，而后者将提示一个窗口询问您将结果放在何处。

Answer 1