我在excel中有3个时间段-我需要知道最长连续时间段的持续时间 [英] I have 3 time periods in excel - I need to know the duration of the longest continuous period

问题描述

请帮忙！

理想情况下，我真的只想使用公式来解决这个问题 - 而不是 VBA 或任何我认为花哨"的东西.

Ideally, I would really like to solve this using formulas only - not VBA or anything I consider 'fancy'.

我为一个为持续参与提供奖金的计划工作.我们有三个(有时更多)参与时间段，它们可能重叠和/或可能有不参与的空间.神奇的数字是连续订婚 84 天.我们一直在手动检查每条线路(数百条线路)，以查看这些时间段加起来是否为 84 天的连续参与天数，没有不活动的时间段.

I work for a program that awards bonuses for continuous engagement. We have three (sometimes more) engagement time periods that could overlap and/or could have spaces of no engagement. The magic figure is 84 days of continuous engagement. We have been manually reviewing each line (hundreds of lines) to see if the time periods add up to 84 days of continuous engagement, with no periods of inactivity.

在链接中有一张我们工作内容摘要的图片.例如，第 3 行在 3 个时间段中的任何一个中都没有 84 天，但前 2 个时间段组合包括连续 120 天.日期不会按日期顺序出现 - 例如早期参与可能会列在第 3 期.

In the link there is a pic of a summary of what we work with. Row 3 for example, doesn't have 84 days in any of the 3 time periods, but the first 2 time periods combined includes 120 consecutive days. The dates will not appear in date order - e.g. early engagements may be listed in period 3.

非常期待您的建议.

安妮

推荐答案

@TomSharpe 向您展示了一种使用公式解决此问题的方法.如果您有三个以上的时间段，则必须对其进行修改.

@TomSharpe has shown you a method of solving this with formulas. You would have to modify it if you had more than three time periods.

不确定您是否认为 Power Query 解决方案太花哨"，但它确实允许无限数量的时间段，如您在示例中所示.

Not sure if you would consider a Power Query solution to be "too fancy", but it does allow for an unlimited number of time periods, laid out as you show in the sample.

有了 PQ，我们

• 为每对开始/结束构建所有连续日期的列表
• 合并每一行的列表，删除重复项
• 对每一行的结果日期列表应用间隙和孤岛技术
• 计算每个岛"的条目数；并返回最大值

请注意:我计算了开始日期和结束日期.在您的日子专栏中，您没有(除了一个实例).如果您想同时计算两者，请保持代码不变；如果你不这样做，我们可以做一个小的修改

使用 Power Query

To use Power Query

• 创建一个表格，排除合并单元格的第一行
• 按照我在屏幕截图中显示的格式重命名表格列，因为表格中的每个列标题必须具有不同的名称.
• 选择该数据表中的某个单元格
• 数据 =>获取&转换=>从表/范围
• 当 PQ 编辑器打开时:Home =>高级编辑器
• 记下第 2 行中的表名称
• 将下面的 M 代码粘贴到您看到的位置
• 将第 2 行中的表名称改回最初生成的名称.
• 阅读评论并探索应用步骤以更好地理解算法

• Create a table which excludes that first row of merged cells
• Rename the table columns in the format I show in the screenshot, since each column header in a table must have a different name.
• Select some cell in that Data Table
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to better understand the algorithm

M 代码
编辑代码以对日期列表进行排序以处理某些情况

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start P1", type datetime}, {"Comment1", type text}, {"End P1", type datetime}, {"Days 1", Int64.Type}, {"Start P2", type datetime}, {"Comment2", type text}, {"End P2", type datetime}, {"Days 2", Int64.Type}, {"Start P3", type datetime}, {"Comment3", type text}, {"End P3", type datetime}, {"Days 3", Int64.Type}}),

//set data types for columns 1/5/9... and 3/7/11/... as date
dtTypes = List.Transform(List.Alternate(Table.ColumnNames(#"Changed Type"),1,1,1), each {_,Date.Type}),
typed = Table.TransformColumnTypes(#"Changed Type",dtTypes),

//add Index column to define row numbers
rowNums = Table.AddIndexColumn(typed,"rowNum",0,1),

//Unpivot except for rowNum column
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(rowNums, {"rowNum"}, "Attribute", "Value"),

//split the attribute column to filter on Start/End => just the dates
//then filter and remove the attributes columns
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Attribute.1] = "End" or [Attribute.1] = "Start")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.1"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Value", type date}, {"rowNum", Int64.Type}}),

//group by row number
//generate date list from each pair of dates
//combine into a single list of dates with no overlapped date ranges for each row
    #"Grouped Rows" = Table.Group(#"Changed Type2", {"rowNum"}, {
        {"dateList", (t)=> List.Sort(
            List.Distinct(
                List.Combine(
                    List.Generate(
                        ()=>[dtList=List.Dates(
                                t[Value]{0},
                                Duration.TotalDays(t[Value]{1}-t[Value]{0})+1 ,
                                #duration(1,0,0,0)),idx=0],
                        each [idx] < Table.RowCount(t),
                        each [dtList=List.Dates(
                                    t[Value]{[idx]+2},
                                    Duration.TotalDays(t[Value]{[idx]+3}-t[Value]{[idx]+2})+1,
                                    #duration(1,0,0,0)),
                                idx=[idx]+2],
                        each [dtList]))))}
            }),

//determine Islands and Gaps
    #"Expanded dateList" = Table.ExpandListColumn(#"Grouped Rows", "dateList"),

//Duplicate the date column and turn it into integers
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded dateList", "dateList", "dateList - Copy"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Duplicated Column",{{"dateList - Copy", Int64.Type}}),

//add an Index column
//Then subtract the index from the integer date
// if the dates are consecutive the resultant ID column will => the same value, else it will jump
    #"Added Index" = Table.AddIndexColumn(#"Changed Type3", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "ID", each [#"dateList - Copy"]-[Index]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"dateList - Copy", "Index"}),

//Group by the date ID column and a Count will => the consecutive days
    #"Grouped Rows1" = Table.Group(#"Removed Columns2", {"rowNum", "ID"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Grouped Rows1",{"ID"}),

//Group by the Row number and return the Maximum Consecutive days
    #"Grouped Rows2" = Table.Group(#"Removed Columns3", {"rowNum"}, {{"Max Consecutive Days", each List.Max([Count]), type number}}),

//combine the Consecutive Days column with original table
    result = Table.Join(rowNums,"rowNum",#"Grouped Rows2","rowNum"),
    #"Removed Columns4" = Table.RemoveColumns(result,{"rowNum"})
in
    #"Removed Columns4"

这篇关于我在excel中有3个时间段-我需要知道最长连续时间段的持续时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！