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

问题描述

我有一个包含ID的数据表，其ID分别与开始日期和结束日期相关。

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在开始和结束日期（含）之间的每个eomonth排一行。行号并不重要。

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

同样，这只会扩展一行

我将如何遍历将每个系列堆叠成一列的行？欢迎提供有关调整公式或代码的任何建议。

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日期，但是如果按照结果中的格式对它们进行格式化，则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&转换，删除查询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屋！