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屋！