在 Excel 中搜索多列中的字符串，该字符串存在于多行中，然后将信息合并到一行中

Question 1

这可以用公式来完成。

=TRANSPOSE(IFERROR(FILTERXML("<t><s>" & TEXTJOIN("</s><s>",TRUE,IF(COUNTIF(OFFSET(Sheet2!$B$1:$Z$1,ROW(Sheet2!$A:$A)-1,0),$B2)>0,Sheet2!$A:$A,"")) & "</s></t>", "//s"),""))

^{点击图像查看大图。}

您需要更新引用。在我的模型中，Sheet2您的问题中的工作表名称显示为“治疗表示例”。此外，Sheet2!$B$1:$Z$1如果您认为工作表可能需要变大，可以扩展范围以包含其他列。您还可以将范围设为更小使函数运行得更快一些。根据示例表的设置方式，应输入此公式C2，然后将其复制下来。

解释：

OFFSET(Sheet2!$B$1:$Z$1,ROW(Sheet2!$A:$A)-1,0)让我们一次查看治疗表中的每一行。它从范围开始B1:Z1，然后一次向下偏移一行，因为使用作为输入，所以行数与行数相同。这将返回所有行号的数组。减去 1 并偏移该数以一次ROW(Sheet2!$A:$A)引用每一行。B:Z
IF(COUNTIF(OFFSET(~),$B2)>0,Sheet2!$A:$A,""))计算所有等于工作编号的值（在我们一次查看的行中）这表。如果它找到至少一个（），则它将返回中的>0列中的治疗。否则，它将返回空白。ASheet2
TEXTJOIN("</s><s>",TRUE,IF(~))将其找到的所有治疗方式连接成一个字符串，并用分隔，</s><s>一分钟后会更有意义。(参考）
FILTERXML("<t><s>" & TEXTJOIN(~) & "</s></t>", "//s")才是真正的魔法发生的地方。该函数并非专门为此目的而设计的，但您可以使用它将分隔字符串转换为数组。这使我们能够将字符串拆分为单个单元格。所有 XML 标签（如和）<t>都是<s>为了使字符串看起来像 XML。这就是我们</s><s>之前使用的原因。（参考）
IFERROR(FILTERXML(~),"")将捕获工作表中从未找到工作值时发生的错误（FILTERXML()将输入无效的 XML 字符串）并将其转换为空白单元格。在屏幕截图中，您可以看到我在发生这种情况的底部添加了一行。
TRANSPOSE(IFERROR(FILTERXML(~)))将把给定的数据列FILTERXML()转换为一行数据。

Answer

这可以用公式来完成。

=TRANSPOSE(IFERROR(FILTERXML("<t><s>" & TEXTJOIN("</s><s>",TRUE,IF(COUNTIF(OFFSET(Sheet2!$B$1:$Z$1,ROW(Sheet2!$A:$A)-1,0),$B2)>0,Sheet2!$A:$A,"")) & "</s></t>", "//s"),""))

^{点击图像查看大图。}

您需要更新引用。在我的模型中，Sheet2您的问题中的工作表名称显示为“治疗表示例”。此外，Sheet2!$B$1:$Z$1如果您认为工作表可能需要变大，可以扩展范围以包含其他列。您还可以将范围设为更小使函数运行得更快一些。根据示例表的设置方式，应输入此公式C2，然后将其复制下来。

解释：

OFFSET(Sheet2!$B$1:$Z$1,ROW(Sheet2!$A:$A)-1,0)让我们一次查看治疗表中的每一行。它从范围开始B1:Z1，然后一次向下偏移一行，因为使用作为输入，所以行数与行数相同。这将返回所有行号的数组。减去 1 并偏移该数以一次ROW(Sheet2!$A:$A)引用每一行。B:Z
IF(COUNTIF(OFFSET(~),$B2)>0,Sheet2!$A:$A,""))计算所有等于工作编号的值（在我们一次查看的行中）这表。如果它找到至少一个（），则它将返回中的>0列中的治疗。否则，它将返回空白。ASheet2
TEXTJOIN("</s><s>",TRUE,IF(~))将其找到的所有治疗方式连接成一个字符串，并用分隔，</s><s>一分钟后会更有意义。(参考）
FILTERXML("<t><s>" & TEXTJOIN(~) & "</s></t>", "//s")才是真正的魔法发生的地方。该函数并非专门为此目的而设计的，但您可以使用它将分隔字符串转换为数组。这使我们能够将字符串拆分为单个单元格。所有 XML 标签（如和）<t>都是<s>为了使字符串看起来像 XML。这就是我们</s><s>之前使用的原因。（参考）
IFERROR(FILTERXML(~),"")将捕获工作表中从未找到工作值时发生的错误（FILTERXML()将输入无效的 XML 字符串）并将其转换为空白单元格。在屏幕截图中，您可以看到我在发生这种情况的底部添加了一行。
TRANSPOSE(IFERROR(FILTERXML(~)))将把给定的数据列FILTERXML()转换为一行数据。

Question 2

您还可以使用 Office 365 和 Windows Excel 2010+ 中提供的 Power Query 执行此操作

我把两张桌子放进表格，命名work并且treatment在这种情况下工作表是不相关的。

您应该能够向治疗表添加/删除列或行，以及向工作表添加/删除行，并且查询应该在您刷新时进行调整。

逆透视治疗表以创建包含治疗和工作编号的两列表
与工作表连接
根据“信息”和“工作编号”对连接表进行分组
- 连接，然后扩展为列处理

请阅读代码注释以获取有关该算法的更多信息

使用 PQ

在原始表格中选择某个单元格
Data => Get&Transform => From Table/Range
当 PQ UI 打开时，导航至Home => Advanced Editor
检查任何注释以及窗口Applied Steps，以更好地理解算法和步骤

let

//Read in the Work table
    SourceWrk= Excel.CurrentWorkbook(){[Name="work"]}[Content],
//add index column for later sorting
    addIdx= Table.AddIndexColumn(SourceWrk,"IDX",0,1),

//Set data type
    workTable = Table.TransformColumnTypes(addIdx, {{"Info", type text},{"Work", Int64.Type}}),

//Read in the Treatment table
    Source = Excel.CurrentWorkbook(){[Name="treatment"]}[Content],

//dynamically type the columns
//  first column will be Text; other columns will be integers
    colXforms = {{"Treatment", type text}} & 
        List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),1), each {_,Int64.Type}),
    #"Changed Type" = Table.TransformColumnTypes(Source, colXforms),

//Unpivot and remove Attribute column to create two column table
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Treatment"}, "Attribute", "Value"),
    treatmentTable = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),

//join the tables based on worknumber
    join= Table.Join(treatmentTable,"Value",workTable,"Work",JoinKind.FullOuter),
    #"Removed Columns" = Table.RemoveColumns(join,{"Value"}),

//Group by Info and Work
//Then create the new columns for the treatments and IDX for sorting
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Info", "Work"}, {
        {"sortBy" , each [IDX]{0}},
        {"Treatment", each Text.Combine([Treatment],";"),type text}
        }),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"sortBy", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"sortBy"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Treatment", 
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)),
    typeIt = Table.TransformColumnTypes(#"Split Column by Delimiter", List.Transform(
        List.RemoveFirstN(Table.ColumnNames(#"Split Column by Delimiter"),2),each {_,type text}) )
in
    typeIt

Answer