☰

查找从不同工作表映射的最高出现次数

tag-icon • tag-icon microsoft-excel worksheet-function
查找从不同工作表映射的最高出现次数

我有两张源表，一张有几列，里面有几条条目（确切地说是三列，有数千行，分别是源位置、目标位置和类型/服务”：单元格的示例条目：“anton 纸柜数据条目”（分隔实际上是用换行符，而不是空格）

第二张源表是手工制作的，包含位置和服务（数百）到“小、中、大、特大”值的映射，我可以有这样的布局，但效果最好，但现在我有一列“位置”，另一列是“服务”，旁边写着“小、中、大、特大”。

现在我需要实现的是在结果表上，我将有六列，行数与第一个源表相同：SRCLOC-Name、SRCLOC-#、DSTLOC-Name、DSTLOC-#、SRV-Name、SRV-# 在每一列中我都需要计算“最大”的出现次数。

这意味着如果一个单元格中的十个“对象”被映射到中等，五个被映射到大，两个被映射到特大，我需要以某种方式指示“2x 特大”（例如，SRCLOC-Name 将更改为特大，而 SRCLOC-# 将指示映射到特大的出现次数）

我尝试使用 VLOOKUP 等方法来解决这个问题，但却不知道如何将单元格中的各种字符串（我甚至不知道如何分离和测试）与变量图进行匹配。

附图片示例：

源数据

映射

结果

答案1

它通常是 Power Query 和 Language M（自 2016 年起包含在 Excel 中）的任务。

首先，您必须为使用的所有表声明表 (Insert/Table)，并将它们命名为 SrcData、LocationMapping、ServiceMapping。添加一个新表 Size，其中包含列 Size (small/medium/large/x-large) 和 N (1/2/3/4)。

表格中一半的线条都是彩色的。

然后进入每个 LocationMapping、ServiceMapping 和 Size，在数据中，选择“从表或范围”，下拉菜单“关闭并加载”、“关闭并加载...”，然后选择“无连接”菜单并按 OK。右侧将显示不同的连接，这些连接将建立 Power Query 和表之间的链接。

现在，进入主表（SrcData）。“从表或范围”，然后“高级编辑器”，并将程序替换为

let
    Source = Excel.CurrentWorkbook(){[Name="SrcData"]}[Content],
    #"Type" = Table.TransformColumnTypes(Source,{{"SRCLOC", type text}, {"DSTLOC", type text}, {"Service", type text}}),
    Src1 = Table.AddColumn(#"Type",
          "SRCLOC-N",each let sp = Text.Split([SRCLOC],"#(lf)"), 
                        l = List.Transform(sp,each  Size{[ Size= LocationMapping{[Location=_]}[Size]  ]}[N]  ),
                        max = List.Max(l),
                        filter = List.Select(l, each _ = max)
                      in
                        Size{[N=max]}[Size]),
    Src2 = Table.AddColumn(Src1,
          "SRCLOC#",each let sp = Text.Split([SRCLOC],"#(lf)"), 
                        l = List.Transform(sp,each  Size{[ Size= LocationMapping{[Location=_]}[Size]  ]}[N]  ),
                        max = List.Max(l),
                        filter = List.Select(l, each _ = max)
                      in
                        List.Count(filter)),
    Dst1 = Table.AddColumn(Src2,
          "DSTLOC-N",each let sp = Text.Split([DSTLOC],"#(lf)"), 
                        l = List.Transform(sp,each  Size{[ Size= LocationMapping{[Location=_]}[Size]  ]}[N]  ),
                        max = List.Max(l),
                        filter = List.Select(l, each _ = max)
                      in
                        Size{[N=max]}[Size]),
    Dst2 = Table.AddColumn(Dst1,
          "DSTLOC#",each let sp = Text.Split([DSTLOC],"#(lf)"), 
                        l = List.Transform(sp,each  Size{[ Size= LocationMapping{[Location=_]}[Size]  ]}[N]  ),
                        max = List.Max(l),
                        filter = List.Select(l, each _ = max)
                      in
                        List.Count(filter)),
    Svc1 = Table.AddColumn(Dst2,
          "Service-N",each let sp = Text.Split([Service],"#(lf)"), 
                        l = List.Transform(sp,each  Size{[ Size= ServiceMapping{[Service=_]}[Size]  ]}[N]  ),
                        max = List.Max(l),
                        filter = List.Select(l, each _ = max)
                      in
                        Size{[N=max]}[Size]),
    Svc2 = Table.AddColumn(Svc1,
          "Service#",each let sp = Text.Split([Service],"#(lf)"), 
                        l = List.Transform(sp,each  Size{[ Size= ServiceMapping{[Service=_]}[Size]  ]}[N]  ),
                        max = List.Max(l),
                        filter = List.Select(l, each _ = max)
                      in
                        List.Count(filter)),
    SelectColumns = Table.SelectColumns(Svc2,{"SRCLOC-N", "SRCLOC#", "DSTLOC-N", "DSTLOC#", "Service-N", "Service#"})
in
    SelectColumns

单击“确定”将预览结果。单击“关闭并加载”将创建一张包含结果的表格。

请注意，程序会对表名及其列进行挑剔，包括字符大小写。

（该程序本来可以进行优化...有些计算需要两次...但如果可以的话，我会保持原样）。

相关内容