☰

Powerquery/Formula 计算现有延迟、新延迟和超出延迟

tag-icon • tag-icon microsoft-excel
Powerquery/Formula 计算现有延迟、新延迟和超出延迟

我有每日的制造厂数据和属于特定类别（mmodel）的特定车辆（用 sno 表示），这些车辆可能会由于某些问题而延迟。

现在，我的“输入”选项卡每天都有这些数据的快照，并表示与前一天相比可能有新的延迟的车辆（sno 和 mmodel 的组合）（新的延迟意味着与当天相比，该车辆组合在紧接的前一天不存在）；现有延迟 - 相同的车辆组合在前一天存在或关闭延迟 - 前一天（或考虑中的一天）的车辆组合不存在。

当天的数字是根据当天和前一天车辆入场情况计算得出的。

第一天是参考日，所有条目都只是延迟，而不是关闭延迟或新延迟......后续几天的条目与前一天进行比较以获取延迟，新延迟和关闭延迟......输出选项卡 1 是按快照日期的摘要视图，输出 2 选项卡是按主模型和快照日期的视图。

你能帮帮我吗？

我的数据中最早的日期（升序）将被视为参考日期。因此，在这种情况下，2022 年 3 月 1 日将被视为参考日期，其中所有唯一条目（条目由 sno 和 mmodel 的组合表示）将被视为延迟。

因此，当我考虑 2022 年 3 月 2 日时，2022 年 3 月 2 日和前一天（即 2022 年 3 月 1 日）之间的共同条目将是数据 (r123,S1234)、(y123,D2123) 和 (W321,G345) 中的延迟

2022 年 3 月 1 日存在但 2022 年 3 月 2 日不存在的条目将被视为关闭延迟，在我的情况下为 (r3421,S1234)、(g21q,D2123) 和 (E231,G345)。

2022 年 3 月 2 日完全是新条目，但 2022 年 3 月 1 日不是新条目的条目是 (D891,S1234) 和 (K1231,J1231)，它们被视为新的延迟......

输入选项卡

输出选项卡 1

输出选项卡 2

以下是我尝试过的

查找最后一次出现的情况 查找第一次出现的情况

还附上了计算说明...需要将前一天条目与当前参考日进行比较和参考。

计算说明

答案1

这也可以通过 Power Query 来实现，可在 Windows Excel 2010+ 和 Excel 365（Windows 或 Mac）中使用

由于您请求两个不同的表进行输出，因此我实际上创建了三个查询：

  1. 每日报告处理每日报告并创建一个包含各种延迟状态的表格。
  2. 快照摘要获取每日报告并进行处理以创建摘要。
  3. 因型号而延迟获取每日报告并进行处理以创建该报告。

一旦创建完毕，您将来需要做的就是更改daily report并选择刷新查询。

使用 Power Query

  • 在数据表中选择一些单元格
  • Data => Get&Transform => from Table/Range或者from within sheet
  • 当 PQ 编辑器打开时：Home => Advanced Editor
  • 记下表格姓名在第 2 行
  • 将下面的 M 代码粘贴到您所看到的位置。
  • 然后再添加两个查询“从空白开始”并粘贴相关代码。
  • 将第 2 行的表名改回最初生成的表名。
  • 阅读评论并探索Applied Steps以了解算法

每日报告
在此处输入图片描述

每日报告
重命名此查询：dailyReport
加载至：仅连接

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"sno", type text}, 
        {"mmodel", type text}, 
        {"snapshot_date", type date}}),

//generate list of all the dates in this report
    allDates = List.Dates(List.Min(#"Changed Type"[snapshot_date]),
                Duration.Days(List.Max(#"Changed Type"[snapshot_date])-List.Min(#"Changed Type"[snapshot_date]))+1,
                #duration(1,0,0,0)),

//group by sno and mmodel
    //If the first date in allDates is in snapshot_date then Delay=true
    //If a date in allDates is in snapshot_date and preceding date in allDates is in snapshot_date then Delay=true
    //If a date in allDates is in snapshot_date and preceding date in allDates not in snapshot_date then New Delay=true
    //if a date in allDates is NOT in snapshot_date and preceding date in allDates IS in snapshot_date then off Delay=true
    group = Table.Group(#"Changed Type",{"sno", "mmodel"},{
        {"Delay Status", (t)=>List.Generate(
            ()=>[ds=if List.Contains(t[snapshot_date],allDates{0}) then 
                [date=allDates{0}, Delay=true, off Delay=false, new Delay=false]
                else [date=allDates{0}, Delay=false, off Delay=false, new Delay=false],
                idx=0],
            each [idx] < List.Count(allDates),
            each [ds=
                if List.Contains(t[snapshot_date],allDates{[idx]+1}) 
                    and not List.Contains(t[snapshot_date],allDates{[idx]})            
                then 
                    [date=allDates{[idx]+1}, Delay=false, off Delay=false, new Delay=true]

                else if List.Contains(t[snapshot_date],allDates{[idx]+1}) 
                        and List.Contains(t[snapshot_date],allDates{[idx]})
                    then 
                        [date=allDates{[idx]+1}, Delay=true, off Delay=false, new Delay=false] 

                else if List.Contains(t[snapshot_date],allDates{[idx]}) 
                        and not List.Contains(t[snapshot_date],allDates{[idx]+1})
                    then [date=allDates{[idx]+1}, Delay=false, off Delay=true, new Delay= false]

                else [date=allDates{[idx]+1}, Delay=false, off Delay=false, new Delay=false],
                
                idx=[idx]+1],
            each [ds]), type list}
        }),
    #"Expanded Delay Status" = Table.ExpandListColumn(group, "Delay Status"),
    #"Expanded Delay Status1" = Table.ExpandRecordColumn(#"Expanded Delay Status", "Delay Status", {"date", "Delay", "off Delay", "new Delay"}, {"date", "Delay", "off Delay", "new Delay"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Delay Status1",{{"date", type date}, {"Delay", type logical}, {"off Delay", type logical}, {"new Delay", type logical}})
in
    #"Changed Type1"

快照摘要

let
    Source = dailyReport,
    #"Grouped Rows" = Table.Group(Source, {"date"}, {
        {"Delay", each List.Count(List.Select([Delay], each _ = true)), Int64.Type},
        {"off Delay", each List.Count(List.Select([off Delay], each _ = true)), Int64.Type},
        {"new Delay", each List.Count(List.Select([new Delay], each _ = true)), Int64.Type}
    })
    
in
    #"Grouped Rows"

快照摘要
在此处输入图片描述

因型号而延迟

let
    Source = dailyReport,

//don't need sno column
    #"Removed Columns" = Table.RemoveColumns(Source,{"sno"}),

//convert true and false to 1 and 0
    xForm = Table.TransformColumns(#"Removed Columns",{
        {"Delay", Number.From},
        {"off Delay", Number.From},
        {"new Delay", Number.From}
    }),

//Group by date and mmmodel
// then extract the delay statuses
    #"Grouped Rows" = Table.Group(xForm, {"date", "mmodel"}, {
        {"Delay", each List.Sum([Delay]),Int64.Type},
        {"off Delay", each List.Sum([off Delay]), Int64.Type},
        {"new Delay", each List.Sum([new Delay]), Int64.Type}}),

//remove rows with no data
    remRows = Table.SelectRows(#"Grouped Rows",
        each [Delay]<>0 or [off Delay]<>0 or [new Delay]<>0),

//Could also implement a custom sort if the mmodel sort is critical
    #"Sorted Rows" = Table.Sort(remRows,{
        {"date", Order.Ascending}, {"Delay", Order.Descending}, {"off Delay", Order.Descending}, {"new Delay", Order.Descending}}),

//replace zeros with nulls
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",0,null,Replacer.ReplaceValue,{"Delay", "off Delay", "new Delay"})
in
    #"Replaced Value"

按型号
在此处输入图片描述

如果你没有使用 Power Query 的经验，这个设置会比较复杂，因此我上传了工作簿其中包含数据和代码供您参考。

答案2

要确定特定日期的范围，例如 3/2/2022：

=INDEX(A:A, XMATCH(DATEVALUE("3/2/2022"),C:C, 0,1)):INDEX(A:A, XMATCH(DATEVALUE("3/2/2022"),C:C, 0,-1))

假设每个日期部分都是连续的。还要确保您的日期存储为日期而不是文本。一个简单的测试必须=A2+1为您提供第二天的日期，其中 A2 是日期。

要确定今天的 ID 是否在昨天的范围内，请以第 10 行为例

=COUNTIF(INDEX(A:A, XMATCH(C10 - 1,C:C, 0,1)):INDEX(A:A, XMATCH(C10 - 1,C:C, 0,-1)), A10)

如果上述值 > 0，则为先前的延迟，如果上述值 = 0，则为新的延迟。

对于关闭延迟，您必须期待“明天”的范围

=COUNTIF(INDEX(A:A, XMATCH(C10 + 1,C:C, 0,1)):INDEX(A:A, XMATCH(C10 + 1,C:C, 0,-1)), A10) = 0

对于这些公式，我假设 ID 在 A 列中，但从技术上讲，您必须将两列合并到辅助列中

=A2&B2

答案3

此实现使用 Excel 2010 及更新版本中可用的 COUNTIFS。

要确定 A 列中的今日 ID 是否在昨天的日期范围内（C 列），例如第 10 行

=COUNTIFS(A:A,A10,C:C,C10 - 1)

如果上述值 > 0，则为现有延迟，如果上述值 = 0，则为新延迟。

对于关闭延迟，您必须期待“明天”的数据

=COUNTIFS(A:A,A10,C:C,C10 + 1)=0

如果“今天”的 ID 在“明天”的数据中不存在，则返回 TRUE。

最后，此公式一次性计算关闭延迟的次数

=SUM(ISERROR(MATCH(IF(C:C=C10-1,A:A,""),IF(C:C=C10,A:A,""),0))*1)                 ...Array formula: Press CTRL+SHIFT+ENTER in version prior to Excel 365

附言：对于所有这些公式，我都假设唯一 ID 在 A 列中，但从技术上讲，您必须将两列合并到辅助列中才能形成唯一 ID

=A2&B2

相关内容