excel - 删除excel中前缀相同但后缀不同的条目

Question

我需要电子表格方面的帮助

电子表格看起来像这样：

主机名	一个标题	另一个标题
主机 1-dns	文字 11	文本 12
主机1-ftp	文字 11	文本 12
主机1-ntp	文字 11	文本 12
主机1-vip	文字 11	文本 12
host2-dns	文本 21	文本 22
主机2-ftp	文本 21	文本 22
主机2-ntp	文本 21	文本 22
host3-dns	文本 31	文本 32
主机3-ftp	文本 31	文本 32
主机4	正文 41	文字 42
host5-sans	正文 51	正文 52

我需要创建一个表来消除重复的主机。在我的桌子上：

• host1-dns, host1-ftp, host1-ntp, host1-vip 只是一台主机，应该算一次
• 主机名（前缀）没有统一的字符数
• 后缀也没有统一的字符数
• 前缀和后缀用连字符分隔

最后，表格应如下所示：

主机名	一个标题	另一个标题
主机1	文字 11	文本 12
主机2	文本 21	文本 22
主机3	文本 31	文本 32
主机4	正文 41	文字 42
主机5	正文 51	正文 52

我对任何实现、公式或宏都持开放态度。

提前致谢 ：-）

标签： excelexcel-formulaoffice-2016

Answer 1

Power Query您可以使用（在 Excel 2010+ 中可用）相当容易地做到这一点

粘贴M-Code到高级编辑器中

在第 2 行，确保引用的表名称与原始数据表的名称相同。

请注意，标头应根据每个主机名的唯一标头数拆分为多个列。

检查Applied Steps窗口和嵌入的注释以了解发生了什么。

M代码

let
    Source = Excel.CurrentWorkbook(){[Name="tblHosts"]}[Content],

    //split off the part after the hyphen
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Hostname", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Hostname"}),

    //Group by hostnames
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Hostname"}, 
           {{"Grouped", each _, type table [Hostname=nullable text, A header=nullable text, Another header=nullable text]}}),

    //combine headers into a unique, sorted list
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Header", each 
        List.Sort(
            List.Distinct(
                List.Combine(
                    {Table.Column([Grouped],"A header"),
                     Table.Column([Grouped],"Another header")
                    })
        ))),

    //split the list into a single item per column
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Header", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Extracted Values", "Header", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv))
    
in
    #"Split Column by Delimiter1"