☰

嘗試在Excel中尋找多個輸入項目之間的日期差異

tag-icon • tag-icon microsoft-excel worksheet-function
嘗試在Excel中尋找多個輸入項目之間的日期差異

我有一張發票和修改日期的表。這會為每個人提供具有多個日期的多個發票條目。我想做的是找到第一個條目和最後一個條目之間的日期，以便給我一個從開始到結束需要多長時間的時間範圍。

見下表：

發票 輸入/修改 上一次更改 狀態
N805844 2024 年 2 月 5 日 喬 最新的
N805844 2024 年 2 月 5 日 史蒂夫 事先的
N805844 2024年12月28日 麥克風 事先的
N808961 2024 年 1 月 4 日 喬 最新的
N805961 2024 年 1 月 3 日 喬 事先的
N805961 2024 年 1 月 2 日 麥克風 事先的
N805961 2024 年 1 月 2 日 史蒂夫 事先的

我試圖使用輔助列來識別發票編號，然後減去所有日期值，這應該會產生一個數字 - 完成天數。我考慮了“最新”和“先前”，因為它可能會有所幫助。

我在另一張紙上考慮過最大/最小公式，但我不確定如何做到這一點。

然後我將透視此表以顯示完成時間。任何幫助，將不勝感激。

答案1

有很多方法可以實現這一點，我將嘗試發布一些方法，據我所知使用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-CodeDone
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具有所需輸出的文件，而後者將提示一個窗口，詢問您將結果放在哪裡。

相關內容