每单元格公式，用于列出给定列的每日运行计数 [英] Per-cell formula for tabulating daily running counts of a given column

问题描述

跟进问题63893967 :

我如何获得...的相同结果

How can I achieve the same result of...

提供给定日期的运行中"的连续计数.换句话说:输出应枚举给定日期的每个Issue(如果Issue为空白，则返回空白)，然后在下一个日期再次为第一个Issue从1开始

giv[ing] a running count of Issues for a given day. In other words: the output should enumerate each Issue for a given date (returning blank if Issue is blank), and then start again at 1 for the first issue in a subsequent date

...但是不使用ArrayFormula?即我想在每行中都有一个公式，但是将相同的值输出为列J.原因是由于现在有了ArrayFormula，因此每次打开该电子表格时，iOS应用都会崩溃.

...but not use an ArrayFormula? i.e. I'd like to have a formula in each row, but the same values outputted as column J. The reason is that the iOS app crashes every time I open this spreadsheet on it now that I have the ArrayFormula in there.

示例电子表格，然后在行方法"标签:

Sample spreadsheet, looking on "Formula per row method" tab:

• B列包含日期
• E列包含每个Issue的T恤尺寸严重程度
• 列F包含列E的数字转换
• 列G包含该行是否存在问题的二进制输出
• 列I包含ArrayFormula方法:

• Column B contains the date
• Column E contains the T-shirt size severity of each Issue
• Column F contains a numerical translation of column E
• Column G contains a binary output of whether there was an Issue in that row
• Column I contains the ArrayFormula method:

=ArrayFormula(IF(F3:F="",,MMULT((ROW($B3:$B)>=TRANSPOSE(ROW($B3:$B))) * EXACT($B3:$B,TRANSPOSE($B3:$B))^1, ($G3:$G)^1)))

• 列L将列J中的尝试与列I中的真相进行调和

• Column L reconciles the attempt in column J against the source of truth in column I

推荐答案

最简单的方法就是:

=IF(F3="",,
    INDEX(
        MMULT(
            (ROW(B$3:B)>=TRANSPOSE(ROW(B$3:B))) * 
                EXACT(B$3:$B,TRANSPOSE(B$3:B)),
            G$3:G
        ),
        ROW(F3)-2
    )
)

...如果您对每行的MMULT都没问题的话.我可以看到，对于大型数据集，这种情况变得很慢，但前提是假设Sheets实际上每行都会对此进行计算.当然，如果您在另一张纸上记住MMULT结果并使用该范围，则可以提高效率.

...if you're ok with MMULTing at every row. I can see this getting slow for large data sets, though, assuming Sheets does in fact calculate this per every row. Of course, you can be more efficient if you memoize the MMULT result in another sheet and use that range.

我们可以创建一个新的工作表(我称其为备忘录")以进行记忆，或者

We can create a new sheet (I called it "Memo") to memoize, or cache, the MMULT() so it only has to be calculated once.

=ArrayFormula(MMULT(
    (ROW('Formula per row method'!B3:B)>=TRANSPOSE(ROW('Formula per row method'!B3:B))) * 
        EXACT('Formula per row method'!B3:$B,TRANSPOSE('Formula per row method'!B3:B)),
    'Formula per row method'!G3:G
))

那么您的原始公式将变为:

Then your original formula becomes:

=IF(F3="",,
    INDEX(
        Memo!A:A,
        ROW(F3)-2
    )
)

但是，我不确定这是否对您有用，因为您说ArrayFormula()无效.不过，它可能只是打破了您的原始用例，所以值得一试.

However, I'm not sure if this will work for you because you said ArrayFormula() wasn't working. It might just be breaking in your original use case, though, so it's worth a shot.

这篇关于每单元格公式，用于列出给定列的每日运行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！