Excel动态数组重复运行计数 [英] Excel Dynamic Array Running Count of Duplicates

问题描述

我一直在使用Excel的新版

尽管尝试使用 Index()， Aggregate()， Filter()以及其他一些涉及的符号，例如 Sumproduct(-(...))，我可以找到的最简单的方法来创建辅助列A，方法似乎是通过半锚定范围创建运行计数，但不幸的是，这种方法无法很好地转换为新的动态数组公式.

有人有运气来适应在动态数组公式中使用半锚定范围和公式吗?

解决方案

要使用动态数组公式，我们需要使用易失的OFFSET.

  = COUNTIFS(OFFSET(A1,0,0，SEQUENCE(COUNTA(A1#)))，A1#) 

I've been retooling some older spreadsheet tools for filtering and formatting dynamic data outputs using Excel's newer Dynamic Array Formulas functionality. This has been helping remove some of the need for pre-allocating cells and lower amounts of helper columns (which has allowed for reduced file sizes and snappier performance).

One function type I am struggling to replace is pulling out dynamic, running duplicate counts.

For instance, say I have a column B of 20 names that can vary in length from a handful to say 200 names. There is also related data in columns C, D, etc that similarly varies in size. For use of filtering the Data in the later columns, we currently use a helper column in A consisting of the running count of the duplicates in A with a formula using semi-anchored ranges(ie. Beginning the range with an anchored cell that expands as the formula is copied down the helper column akin to the solution here with CountIf() and a semi-anchored range). The drawback here vs the new dynamic array formulas is that the helper column needs to be pre-allocated for the data.

Despite attempts with Index(), Aggregate(), Filter(), and a few more involved notations like Sumproduct(--(...)), the most straightforward method I can find to make helper column A seems to be by creating the running count via semi-anchored ranges, which unfortunately does not seem to translate well to the new dynamic array Formulas.

Has anyone had any luck adapting the use of semi-anchored ranges and formulas for use in dynamic array formulas?

解决方案

To use the dynamic array formula we need to use OFFSET which is volatile.

=COUNTIFS(OFFSET(A1,0,0,SEQUENCE(COUNTA(A1#))),A1#)

这篇关于Excel动态数组重复运行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！