Excel中按类别统计最近10000条数据里‘Good’响应的高效方法？

针对你的大数据量Excel统计需求，以下是两种比现有方法更高效的解决方案，避免易失性函数带来的卡顿问题：

方法1：动态数组公式（Excel 365/2021适用） #

利用非易失性的动态数组函数组合，替代易失性的OFFSET，大幅提升计算效率：

单个类别统计 #

如果要统计特定零件编号（比如PartA）的最近10000条数据中Good的数量，使用公式：

=COUNT(TAKE(FILTER(SORTBY(Table1[response], Table1[Day], -1, Table1[Time], -1), Table1[category]="PartA"), 10000)="Good")

公式逻辑：

1. SORTBY：按Day降序、Time降序对整个表的response列排序
2. FILTER：筛选出对应类别的数据
3. TAKE：提取前10000条（若该类别数据不足10000条，自动取全部）
4. = "Good"：将符合条件的条目转为TRUE，否则为FALSE
5. COUNT：统计TRUE的数量（即Good条目数）

批量统计所有类别 #

如果需要一次性统计所有零件编号的结果，用LET和BYROW简化公式：

=LET(
    AllCats, UNIQUE(Table1[category]),
    GoodCounts, BYROW(AllCats, LAMBDA(cat,
        COUNT(TAKE(FILTER(SORTBY(Table1[response], Table1[Day], -1, Table1[Time], -1), Table1[category]=cat), 10000)="Good")
    )),
    HSTACK(AllCats, GoodCounts)
)

执行后会自动生成包含所有类别及其对应Good计数的结果区域，无需手动下拉填充。

方法2：Power Query（推荐用于30万+行大数据） #

Power Query是Excel处理大数据的利器，计算效率远高于工作表公式，且支持一键刷新新增数据：

1. 选中数据表，点击「数据」选项卡 → 「从表格/区域」，导入Power Query编辑器（确保表有表头）
2. 点击「转换」选项卡 → 「分组依据」：
   • 分组列选择category
   • 新列名设为所有行，操作选择「所有行」
3. 添加自定义列（点击「添加列」→ 「自定义列」），公式：

   =Table.Sort([所有行], {{"Day", Order.Descending}, {"Time", Order.Descending}})
   

   作用是对每个类别的数据按日期时间降序排序
4. 再次添加自定义列，提取前10000行：

   =Table.FirstN([自定义], 10000)
   

5. 添加自定义列统计Good数量：

   =Table.RowCount(Table.SelectRows([自定义.1], each [response]="Good"))
   

6. 删除多余的列（保留category和最后一列统计结果），点击「关闭并上载」将结果导入工作表
7. 后续新增数据后，只需右键结果表 → 「刷新」即可更新统计结果

关键注意事项 #

• 确保Day和Time字段是Excel可识别的日期/时间格式，否则排序逻辑会失效
• 动态数组公式仅支持Excel 365/2021及以上版本；Power Query支持Excel 2016及以上版本（2016需单独安装插件）

内容的提问来源于stack exchange，提问作者ZachE