使用EOMONTH系列将开始日期扩展到结束日期

By simon at 2018-02-28 • 0人收藏 • 56人看过

我有一个数据表,其中包含与开始日期和结束日期相关联的ID 既。

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
我需要扩大(例如,ot)这个表格允许每个eomonth有一行 每个AcNo的开始和结束日期(含)之间。行号是 ün注意。
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
我可以用这样一对公式来做每一行,
'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
但是我有成千上万的A行cNos,这对于执行而言是很难处理的 个别行。 我也使用VBA的DateDiff为单个行形成一个循环。
    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
这又一次只扩展一行。 我将如何遍历将每个系列堆叠到一个列中的行?任何对我的公式或代码进行调整的建议都会受到欢迎。

3 个回复 | 最后更新于 2018-02-28
2018-02-28   #1

只是因为你似乎在寻求多种选择,这里没有 VBA:   *用表格向右扩展你的桌子ula(在这里使用结构化引用):


=IF(EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A))<Table1[@[EndDate]:[EndDate]],EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A)),"")
[![enter image description 这里](https://i.stack.imgur.com/wF9KO.png)](https://i.stack.imgur.com/wF9KO.png)   *使用Power Query或Data Get & Transform除了前两列之外的所有部分都不旋转:(easily在GUI中完成,但我粘贴以下代码以获取兴趣)


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然后按日期排序结果: [![输入图片说明 这里](https://i.stack.imgur.com/KyF4Y.png)(https://i.stack.imgur.com/KyF4Y.png) 请注意,完成后这样,第一个日期实际上是BOM日期,但是如果 你将它们格式化为你的结果,嗯,它看起来是一样的。而且,NGS 如果这是一个问题,很容易改变。 如果将第一个月作为BOM日期不需要:   将公式更改为:


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


  *执行Data Get & Transform时,请删除查询GUI编辑器中的StartDate列,如此当时不会影响其他栏目。

2018-02-28   #2

尝试使用DateDiff作为嵌套的... Next循环来确定数量 个月。在a中收集渐进值ray将加速执行 然后再将它们转储回工作表。

Option Explicit

Sub eoms()
    Dim a As Long, m As Long, ms As Long, vals As Variant
    With Worksheets("Sheet2")
        .Range("F1:G1") = Array("AcNo", "EOMONTHs")
        For a = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
            ms = DateDiff("m", .Cells(a, "C").Value2, .Cells(a, "D").Value2)
            ReDim vals(1 To ms + 1, 1 To 2)
            For m = 1 To ms + 1
                vals(m, 1) = .Cells(a, "B").Value2
                vals(m, 2) = DateSerial(Year(.Cells(a, "C").Value2), _
                                        Month(.Cells(a, "C").Value2) + m, _
                                        0)
            Next m
            .Cells(.Rows.Count, "F").End(xlUp).Offset(1, 0).Resize(UBound(vals, 1), UBound(vals, 2)) = vals
        Next a
        .Range(.Cells(2, "G"), .Cells(.Rows.Count, "G").End(xlUp)).NumberFormat = "mmm yy"
    End With
End Sub
VBA DateSerial可以用作EOMONTH ge将日期设置为零 接下来的一个月。 请注意下图中生成的月份是EOMONTH每个 以mmm yy单元格格式设置系列中的月份。 [![enter image description 这里](https://i.stack.imgur.com/xztvR.png)](https://i.stack.imgur.com/xztvR.png)

2018-02-28   #3

只是因为你似乎在寻求多种选择,这里没有 VBA:   *用表格向右扩展你的桌子ula(在这里使用结构化引用):


=IF(EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A))<Table1[@[EndDate]:[EndDate]],EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A)),"")
[![enter image description 这里](https://i.stack.imgur.com/wF9KO.png)](https://i.stack.imgur.com/wF9KO.png)   *使用Power Query或Data Get & Transform除了前两列之外的所有部分都不旋转:(easily在GUI中完成,但我粘贴以下代码以获取兴趣)


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然后按日期排序结果: [![输入图片说明 这里](https://i.stack.imgur.com/KyF4Y.png)(https://i.stack.imgur.com/KyF4Y.png) 请注意,完成后这样,第一个日期实际上是BOM日期,但是如果 你将它们格式化为你的结果,嗯,它看起来是一样的。而且,NGS 如果这是一个问题,很容易改变。 如果将第一个月作为BOM日期不需要:   将公式更改为:


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


  *执行Data Get & Transform时,请删除查询GUI编辑器中的StartDate列,如此当时不会影响其他栏目。

登录后方可回帖

Loading...