如果项目 x 位于 B 至 X 之间的任意一列，则筛选 A 列中的项目列表

Question

我正在为一家连锁餐厅开发库存系统，该连锁餐厅有大量的菜谱及其相应的配料。我有一张 Excel 表，A 列包含所有菜谱名称，B 列及之后的列包含每种菜谱的配料（A1 =“鸡肉面条汤”，A2 =“鸡汤”，A3 =“面条”等）。有些菜谱只有一种配料，有些则有十多种。

我正在尝试获取使用特定成分的所有食谱的列表，以便清理成分列表以删除那些不再使用的成分。我有一张单独的工作表，其中 A 列包含所有成分，并且一直尝试使用 FILTER 函数以及转置来填充 B 列及以后的食谱表中的食谱，但我想出的唯一方法似乎非常笨拙：

=TRANSPOSE(FILTER(‘Recipes’!A1:A8,(‘Recipes’!B1:B8='Inventory Items'!A1)+‘Recipes’!C1:C8='Inventory Items'!A1)+‘Recipes’!D1:D8='Inventory Items'!A1))

我发现我必须独立检查每一列，你可以想象，对于包含超过 10 列的某些食谱的工作表（并且将来可能会添加更多成分的新食谱），这种解决方案使用和更新起来非常笨重，尽管它确实产生了预期的结果。

还有其他方法可以做到这一点吗？我很想将 INCLUDE 变量设置为 'Recipes'!B2:M8，但这会引发 #VALUE 错误。

Answer 1

这可以使用 Windows Excel 2010+ 和 Excel 365（Windows 或 Mac）中的 Power Query 来实现

使用 Power Query

• 在数据表中选择一些单元格
• Data => Get&Transform => from Table/Range或者from within sheet
• 当 PQ 编辑器打开时：Home => Advanced Editor
• 记下表格姓名在第 2 行
• 将下面的 M 代码粘贴到您所看到的位置
• 将第 2 行的表名改回最初生成的表名。
• 阅读评论并探索Applied Steps以了解算法

let

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table25"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each {_, type text})),
    
    col1 = Table.ColumnNames(#"Changed Type"){0},
    
//unpivot to produce a two column display
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", 
        {col1}, "Attribute", "Ingredients"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),

//Group by ingredients and output a list of applicable recipes
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Ingredients"}, {
        {"Recipes", each Text.Combine(Table.Column(_,col1),"; "), type text}})
in
    #"Grouped Rows"

样本数据

以上数据结果