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

问题描述

跟进

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

=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
列中的真实来源相协调

解决方案

最简单的事情就是这样:

=IF(F3=",,指数(MMULT((ROW(B$3:B)>=TRANSPOSE(ROW(B$3:B))) *精确(B$3:$B,TRANSPOSE(B$3:B)),G$3:G),行(F3)-2))

...如果您对每一行都使用 MMULT 没问题.但是，我可以看到对于大型数据集来说这会变慢，假设 Sheets 实际上每行都计算这个.当然，如果您将 MMULT 结果记忆在另一个工作表中并使用该范围，则效率会更高.

记忆化MMULT()

我们可以创建一个新的工作表(我称之为备忘录")来记忆，或者 缓存，MMULT() 所以它只需要计算一次.

=ArrayFormula(MMULT((ROW('Formula per row method'!B3:B)>=TRANSPOSE(ROW('Formula per row method'!B3:B)) *EXACT('每行公式方法'!B3:$B,TRANSPOSE('每行公式方法'!B3:B)),'每行公式'！G3:G))

那么你原来的公式变成:

=IF(F3=",,指数(备忘录！A:A，行(F3)-2))

但是，我不确定这是否适合您，因为您说 ArrayFormula() 不起作用.不过，它可能只是破坏了您的原始用例，因此值得一试.

Follow-up to question 63893967:

How can I achieve the same result of...

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

...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:

• 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)))

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

解决方案

The simplest thing to do would just be this:

=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
    )
)

...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.

Memoized MMULT()

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
    )
)

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