☰

如何在 Excel 中创建分类列计数的排序表摘要？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 pivot-table sorting
如何在 Excel 中创建分类列计数的排序表摘要？

我有一个如下的数据集：

Animal  fruit
cat     apple
dog     grape
cat     cherry
cat     pear
dog     cherry
horse   melon
cat     lemon

我想创建一个汇总表，计算特定列中某个项目的每次出现次数。

例如，在编程语言 R 中，我可以简单地写：

count(data, Animal, sort=T)

将会返回：

Animal  n
cat     4
dog     2
horse   1

我如何在 Excel 中重新创建此表格摘要。数据集中有很多行和许多类别。作为奖励，是否还有一种方法可以仅包含出现多次的类别？（即“马”将不包含在上面的表格中）

答案1

完成这项任务有很多方法，下面是几种方法，我喜欢使用：根据您的喜好使用：

在此处输入图片描述

• 单元格中使用的公式D1

=LET(
     _output, UNIQUE(HSTACK(A2:A8,COUNTIFS(A2:A8,A2:A8))),
     VSTACK(HSTACK(A1,"n"),FILTER(_output, DROP(_output,,1)>1)))

或者，

在此处输入图片描述

=LET(
     _output, UNIQUE(HSTACK(A2:A8,MMULT(N(A2:A8=TOROW(A2:A8)),ROW(A2:A8)^0))),
     VSTACK(HSTACK(A1,"n"),FILTER(_output, TAKE(_output,,-1)>1)))

Beta Channel中新引入的功能MS365可以使用GROUPBY()或PIVOTBY()

在此处输入图片描述

=LET(
     α, GROUPBY(A1:A8,A1:A8,COUNTA,1,0),
     VSTACK({"Animal","n"},FILTER(α,TAKE(α,,-1)>1)))

或者，

=LET(
     φ, PIVOTBY(A1:A8,,A1:A8,COUNTA,1,0),
     VSTACK({"Animal","n"}, FILTER(φ, DROP(φ,,1)>1)))

或者使用Windows 和 MAC 以上版本POWER QUERY可用的版本。Excel 2010+

在此处输入图片描述

  • 首先将源范围转换为表格并进行相应命名，在此示例中，我将其命名为Table_1
  • Data接下来，从Tab --> Get & Transform Data--> Get Data--> From Other Sources-->打开一个空白查询Blank Query
  • 上面的命令可以Power Query打开窗口，现在从HomeTab --> Advanced Editor--> 删除您看到的任何内容并粘贴以下内容M-Code，然后按Done
let
    Source = Excel.CurrentWorkbook(){[Name="Table_1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Animal"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Animal"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] > 1)
in
    #"Filtered Rows"

在此处输入图片描述

  • 最后，将其导回Excel--> 单击Close & Load或Close & Load To--> 第一个单击的将创建一个New Sheet具有所需输出的，而后者将提示一个窗口询问您将结果放在何处。

相关内容