☰

当列名中的值不等于“N/A”时，如何计算此类别出现的次数

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016 countif
当列名中的值不等于“N/A”时，如何计算此类别出现的次数

我有以下示例数据：https://docs.google.com/spreadsheets/d/1TSpuMRktIRAoACH5s1Dk9189NsNpJwjRtNaR5QROZg8/edit?usp=sharing

我想计算仅当列名下的数据不等于“N/A”时某个类别出现的次数

我目前所做的是，即使名称下的值为“N/A”，它仍然算作“1”

我目前所做的输出示例
我目前所做的输出示例

答案1

我创建了一个桌子从您的数据中进行动态引用，但如果您愿意，也可以使用没有表格的正常寻址

在 G 列中，创建类别的不同列表。在第 1 行中，创建区域的不同列表。在 Excel 2016 中，您可以使用高级筛选以及其他方法执行此操作。

然后，在相邻的单元格中输入公式：

=COUNTIFS(Categories[[Category]:[Category]],$G2,Categories[[Name]:[Name]],"<>N/A",Categories[[Regions]:[Regions]],H$1)

并向下和横向填充。

在此处输入图片描述

如果您不需要动态操作，也可以使用 Windows Excel 2010+ 和 Office 365 Excel 中的 Power Query 轻松完成

  • 选择原始表格中的某个单元格
  • Data => Get&Transform => From Table/Range或者From within sheet
  • 当 PQ UI 打开时，导航至Home => Advanced Editor
  • 记下代码第 2 行的表名称。
  • 用以下代码替换现有代码M 代码以下
  • 将粘贴代码第 2 行的表名更改为您的“真实”表名
  • 检查任何注释以及窗口Applied Steps，以更好地理解算法和步骤

M 代码

let

//Change table name in next line to your actual table name
    Source = Excel.CurrentWorkbook(){[Name="Categories"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Regions", type text}, {"Category", type text}, {"Name", type text}}),

//Group by Regions and Category
//Then Count if `Name` <> "N/A"
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Regions","Category"}, {
        {"Count", each List.Count(List.Select([Name], each _ <> "N/A"))}}),

//Pivot on the Regions to get a separate column for each Region
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Regions]), "Regions", "Count"),

//Set the data types
    typeIt = Table.TransformColumnTypes(#"Pivoted Column", 
        List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Pivoted Column")), each {_, Int64.Type}))
in
    typeIt

相关内容