excel - 扫描工作表以查找每个 ID 的缺失项目

以下解决方案使用Excel 2010 Professional Plus 和所有更高版本的 Excel 中提供的Power Query 。我的演示是使用Excel 365。

假设您有两个表：

Table1 称为Tbl_Visitday在您的示例中是 2 列表；
Table2 称为Rng_Obligated，它是一个包含所有义务日期的 1 列表。

转到DataExcel 功能区中的选项卡，使用“从表”功能将两个表一一添加到电源查询编辑器。当您第一次访问编辑器时，请确保设置Query Options如下以避免将每个查询加载到新工作表；

Tbl_Visitday将两个表都添加到编辑器后，在Queries左侧部分中复制，如下所示：

让我们Rng_Obligated首先工作，突出显示列，使用选项卡下的Transpose功能Transform将数据从行转置到列，然后使用Merge Columns功能通过分隔符分号合并所有列;，然后您应该有如下内容：

让我们移动到Tbl_Obligated(2)，删除Visitday列，删除列中的重复项ID并按升序对其进行排序，那么您应该有：

使用选项卡下的追加查询功能将表追加到当前表，然后右键单击列标题并选择填充->使用相同的字符串快速填充合并的列，然后您应该有如下内容：HomeRng_ObligatedMerged

过滤了ID要隐藏的列null，然后使用选项卡下的Split Columns功能通过分隔符分号Transform拆分列，并在高级选项中选择将结果放入Rows中，如下图：Merged;

使用选项卡下的合并查询功能将表格与当前表格合并，按住键并在每个表格中连续选择第一列和第二列，如下所示：HomeTbl_VisitdayCtrl

展开新合并列以仅显示列，使用此公式Visitday添加自定义列=[Merged]=[Visitday]，然后过滤Custom列以仅显示FALSE结果，那么您应该有：

Merged将列的格式更改为Text，然后使用选项卡下的Group By功能对列进行分组，如下所示，结果将是预期的：TransformMergedIDerror

回到右边APPLIED STEPSList.Sum([Merged])部分的最后一步，进入公式栏，将这部分公式替换为Text.Combine([Merged],",")，点击enter，你会发现error已经变成了一个文本字符串，如下图：

如果您在第一步中修改了查询设置，您可以关闭并加载将作为连接创建的查询。您可以单击选项卡下的查询和连接Data，然后右键单击查询并选择将其加载到工作簿中的特定位置。

在您的情况下，您需要让共享工作簿的所有者解锁工作簿，以便您可以使用电源查询编辑器并加载输出。或者，您可以将数据复制并粘贴到新工作簿中，您可以在其中执行电源查询以获得结果。

Power Query 允许您在几秒钟内正常更新源表并在后端重新计算输出（一旦您选择刷新数据）。如果您不希望刷新输出，可以将输出复制并粘贴到新表中，以使结果保持不变。

下面是两张表的电源查询M代码，供大家参考。如果您有任何问题，请告诉我。干杯:)

Rng_Obligated

let
    Source = Excel.CurrentWorkbook(){[Name="Rng_Obligated"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"obligated", Int64.Type}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}, "en-AU"),{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

Tbl_Visitday(2)

let
    Source = Excel.CurrentWorkbook(){[Name="Tbl_Visitday"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Visitday", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Visitday"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"ID", Order.Ascending}}),
    #"Appended Query" = Table.Combine({#"Sorted Rows", Rng_Obligated}),
    #"Filled Up" = Table.FillUp(#"Appended Query",{"Merged"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([ID] <> null)),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Filtered Rows", {{"Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1",{"ID", "Merged"},Tbl_Visitday,{"ID", "Visitday"},"Table6",JoinKind.LeftOuter),
    #"Expanded Table6" = Table.ExpandTableColumn(#"Merged Queries", "Table6", {"Visitday"}, {"Visitday"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table6", "Custom", each [Merged]=[Visitday]),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] = false)),
    #"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Merged", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type2", {"ID"}, {{"MissedDay", each Text.Combine([Merged],","), type text}})
in
    #"Grouped Rows"

excel - 扫描工作表以查找每个 ID 的缺失项目

问题描述

解决方案

推荐阅读