☰

Excel 公式根据另一列为一列添加前缀？

tag-icon • tag-icon microsoft-excel
Excel 公式根据另一列为一列添加前缀？

有没有办法在 Excel 中做到这一点？

我的 Excel：

在此处输入图片描述

我的期望输出：

在此处输入图片描述

答案1

=TEXTJOIN(";",TRUE,"AVS"&LET(list,FILTERXML("<a><z>"&SUBSTITUTE(B1,CHAR(10),"</z><z>")&"</z></a>","//z"),TRIM(LEFT(list,IFERROR(FIND(" ",list),LEN(list))))))

这将获取 中的文本B1，用换行符将其分隔，提取每行第一个空格之前的所有文本，添加AVS到开头，并用分号将它们全部连接起来。将此公式添加到A1并复制/粘贴下来。

  • FILTERXML("<a><z>"&SUBSTITUTE(B1,CHAR(10),"</z><z>")&"</z></a>","//z")是大多数魔法发生的地方。该函数并非专门为此目的而设计的，但您可以使用它将分隔字符串转换为数组。这使我们能够将字符串拆分为单个单元格。所有 XML 标签（如和）<a>都是<z>为了使字符串看起来像 XML。这就是我们</z><z>之前使用的原因。（参考）
  • LET(list,FILTERXML(~)定义list为等于我们从上面的函数中得到的文本数组。它允许我们稍后通过这个名称引用它，而不是每次都粘贴整个函数。
  • IFERROR(FIND(" ",list),LEN(list))查找列表中的每个项目的第一个空格。如果根本找不到空格，这将出错，因此在这种情况下我们返回整个字符串。如果您只想要前 5 个字符，您可以用数字 5 替换此部分。
  • TRIM(LEFT(list,IFERROR(~)))提取前几个字符，然后修剪掉所有空格。您可以IFERROR(~)通过减一来解释这个修剪过程，但在没有找到空格的极端情况下，这将使函数返回字符串中除最后一个字符之外的所有字符。
  • LET(list,FILTERXML(~),TRIM(~))然后，返回原始文本每行的前几个字符的整洁列表。
  • TEXTJOIN(";",TRUE,"AVS"&LET(~))在该整洁列表中每个项目的开头添加“AVS”，然后将整个列表组合成一个字符串，每个项目之间用分号分隔。

截屏

答案2

这是使用 PowerQuery 执行此操作的一种方法。这看起来像是一篇很长的文章，但我喜欢添加很多图像。它主要是点击操作，不涉及公式，因此它将更轻松地处理大型数据集。随着源数据的增长，您需要做的就是右键单击并从工作簿中刷新查询。您无需重复这些步骤，也无需扩展公式中的任何范围。

在数据中添加标题，然后将光标放在数据中并使用数据>获取和转换数据>从表/范围。

当 PowerQuery 编辑器打开时，您将看到以下内容：

在此处输入图片描述

使用添加列>索引列>从 1 开始，这样你就会看到：

在此处输入图片描述

现在展开 Power Query 编辑器左侧的“查询”窗格，右键单击该查询并选择“复制”，这样您就有了同一查询的两个版本。

在第一个查询中，执行以下操作：

现在右键单击列标题并使用拆分列>按分隔符，配置如下：

在此处输入图片描述

你会看到这个：

在此处输入图片描述

现在使用添加列>自定义列，配置如下：

在此处输入图片描述

你会看到这个：

在此处输入图片描述

现在使用 Home>Group By，配置如下：

在此处输入图片描述

你会看到这个：

在此处输入图片描述

这是因为我们尝试对一些文本进行总结。

转到主页>高级编辑器。更改此项：

#"Grouped Rows" = Table.Group(#"Added Custom", {"Index"}, {{"unique_ids", each List.Sum([unique_id]), type text}})

对此：

#"Grouped Rows" = Table.Group(#"Added Custom", {"Index"}, {{"unique_ids", each Text.Combine( [unique_id] , ";"), type text}})

请注意，List.Sum已更改为Text.Combine。

你会看到这个：

在此处输入图片描述

现在使用主页>合并查询，配置如下：

在此处输入图片描述

单击“表格”列顶部的双箭头并进行如下配置：

在此处输入图片描述

你会看到这个：

在此处输入图片描述

现在您可以删除或不删除索引，然后使用“主页”>“关闭并加载”将结果返回到工作簿中。

以下是高级编辑器中第一个查询的完整查询：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"orig", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"orig", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "orig"),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "unique_id", each "AVS" & Text.Start([orig],5)),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Index"}, {{"unique_ids", each Text.Combine( [unique_id] , ";"), type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Index"}, #"Table1 (2)", {"Index"}, "Table1 (2)", JoinKind.Inner),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table1 (2)", {"orig"}, {"orig"})
in
    #"Expanded Table1 (2)"

对于第二个查询：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"orig", type text}})
in
    #"Changed Type"

相关内容