excel - Excel - 如何计算包含列表的两列中的对

Question

我的数据库中注册了许多农民。每个农民都种一些水果，然后销往几个县。

对于每个水果/县对（例如苹果，沃里克郡），我如何计算可以提供该组合的农民数量？

我的数据库中注册了 100 多个农民。

所以我的数据库中每个农民都有一行，水果一列，他们覆盖的县有一列。每个农民覆盖的水果和县在该农民行的两个单元格中记录为逗号分隔的列表。

我想创建一个矩阵，其中水平为水果，垂直为县，以计算有多少农民覆盖了该特定组合。

对于屏幕截图中的示例，我尝试过：

=COUNTIF(A2:B4,AND(ISNUMBER(SEARCH(G11,A2,1)),ISNUMBER(SEARCH(A13,B2,1)))="TRUE")

但没有运气。

标签： excelexcel-formula

Answer 1

如果您有 Excel 2010+，则可以使用Power Query（也称为Get & TransformExcel 2016+）来执行此操作。

使用 Power Query 可让您在添加任何新产品（或县）时轻松更新表。将行添加到数据表（或将产品或县添加到给定行）后，您只需重新运行查询。

除了删除多余的空格（拆分列后修剪），所有都可以通过 GUI 完成。但是您可以将 M 代码粘贴到高级编辑器中，然后浏览 GUI 以研究各个步骤。

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Products", type text}, {"Counties", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Counties", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Counties"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Counties", type text}}),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Products", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Products"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Products", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Prod", each Text.Trim([Products])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "County", each Text.Trim([Counties])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Products", "Counties"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"County", "Prod"}, {{"grouped", each _, type table [Prod=text, County=text]}, {"counts", each Table.RowCount(_), type number}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"grouped"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Prod]), "Prod", "counts", List.Sum)
in
    #"Pivoted Column"

原始数据

结果