使用 EOMONTH 系列将开始日期扩展到结束日期 [英] Expand Start Date to End Date with Series of EOMONTHs

问题描述

我有一个数据表，其中包含与两者关联的开始日期和结束日期.

I have a data table containing ids with a start date and end date associated with both.

RowNo   AcNo     StartDate     EndDate
  1     R125     01/10/2017    30/09/2020
  2     R126     01/10/2017    30/09/2018
  3     R127     01/10/2017    30/09/2019
  4     R128     01/10/2017    30/09/2020

我需要扩展(即逆透视)此表，以便每个 AcNo 的开始日期和结束日期(含)之间的每个月都有一行.行号不重要.

I need to expand (i.e. unpivot) this table to allow one row for each eomonth between the start and end date (inclusive) for each AcNo. The row numbers are unimportant.

AcNo    EOMONTHs
R125    Oct 17
R125    Nov 17
R125    Dec 17
R125    Jan 18
R125    Feb 18
R125    Mar 18
    ...
R128    Apr 20
R128    May 20
R128    Jun 20
R128    Jul 20
R128    Aug 20
R128    Sep 20

我可以用一对这样的公式来做每一行，

I can do each row with a pair of formulas like this,

'in F2
=IF(ROW(1:1)-1<DATEDIF(C$2, D$2, "m"), B$2, TEXT(,))
'in G2
=IF(ROW(1:1)-1<DATEDIF(C$2, D$2, "m"), EOMONTH(C$2, ROW(1:1)-1), TEXT(,))
'F2:G2 filled down

但是，我有数千行 AcNo，这对于单个行执行起来很笨拙.

However I have thousands of rows of AcNos and this is unwieldy to perform for individual rows.

我还使用 VBA 的 DateDiff 为各个行形成一个循环.

I've also used VBA's DateDiff to form a loop for individual rows.

    Dim m As Long, ms As Long
    With Worksheets("Sheet2")
        .Range("F1:G1") = Array("AcNo", "EOMONTHs")
        ms = DateDiff("m", .Cells(2, "C").Value2, .Cells(2, "D").Value2)
        For m = 1 To ms + 1
            .Cells(m, "M") = .Cells(2, "B").Value2
            .Cells(m, "N").Formula = "=EOMONTH(C$2, " & m - 1 & ")"
        Next m
    End With

同样，这一次只扩展一行.

Again this only expands one row at a time.

我将如何遍历将每个系列堆叠成一列的行?欢迎任何对我的公式或代码进行调整的建议.

How would I loop through the rows stacking each series into a single column? Any suggestions for adjustments to my formula or code would be welcome.

推荐答案

只是因为你似乎在征求多个选项，这里是一个没有 VBA 的选项:

Only because you seem to be soliciting multiple options, here is one without VBA:

• 使用公式将表格向右展开(在此处使用结构化引用):

=IF(EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A))<Table1[@[EndDate]:[EndDate]],EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A)),"")

• 使用 Power Query 或 Data Get &将转换为除前两列之外的所有列:(在 GUI 中很容易完成，但我粘贴下面的代码是为了感兴趣)

• Use Power Query or Data Get & Transform to unPivot all except the first two columns: (easily done in the GUI, but I paste the code below for interest)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RowNo", Int64.Type}, {"AcNo", type text}, {"StartDate", type datetime}, {"EndDate", type datetime}, {"Column1", type datetime}, {"Column2", type datetime}, {"Column3", type datetime}, {"Column4", type datetime}, {"Column5", type datetime}, {"Column6", type datetime}, {"Column7", type datetime}, {"Column8", type datetime}, {"Column9", type datetime}, {"Column10", type datetime}, {"Column11", type datetime}, {"Column12", type datetime}, {"Column13", type datetime}, {"Column14", type datetime}, {"Column15", type datetime}, {"Column16", type datetime}, {"Column17", type datetime}, {"Column18", type datetime}, {"Column19", type datetime}, {"Column20", type datetime}, {"Column21", type datetime}, {"Column22", type datetime}, {"Column23", type datetime}, {"Column24", type datetime}, {"Column25", type datetime}, {"Column26", type datetime}, {"Column27", type datetime}, {"Column28", type datetime}, {"Column29", type datetime}, {"Column30", type datetime}, {"Column31", type datetime}, {"Column32", type datetime}, {"Column33", type datetime}, {"Column34", type datetime}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"RowNo", "AcNo"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "EOM Date"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"EOM Date", type date}})
in
    #"Changed Type1"

• 按 AcNo 然后按日期对结果进行排序:

请注意，以这种方式完成后，第一个日期实际上是 BOM 日期，但如果您将它们格式化为结果中的格式，mmm yy，它看起来会相同.如果这是一个问题，事情很容易改变.

Note that, when done this way, the first date is actually a BOM date, but if you format them as in your results, mmm yy, it'll look the same. And things are easily changed if that is an issue.

如果不需要第一个月作为 BOM 日期:

If having the first month as a BOM date is not desired:

• 将公式更改为:

=IF(EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A)-1)<=Table1[@[EndDate]:[EndDate]],EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A)-1),"")

<小时>

• 当执行Data Get &转换，删除Query GUI编辑器中的StartDate列，因为这不会影响当时的其他列.

---

• When executing the Data Get & Transform, delete the StartDate column in the Query GUI editor, as this will not affect the other columns at that time.

这篇关于使用 EOMONTH 系列将开始日期扩展到结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！