ArrayFormula 自动计算特定列 [英] ArrayFormula to automatically calculate specifics columns

问题描述

我想要一个数组公式与 Google 表单数据一起使用，以自动计算我的数据的运行指标.

在这种情况下，在 H、M、R 和 SI 列中，只要有新的响应进来，它就会自动计算.除了 S 列之外，H、M 和 R 列都可以.>

我正在尝试使用下面的这个公式计算这些特定列 H、M 和 R 中每一行的总和，结果是 S 列的 总计，但不起作用.

=ARRAYFORMULA(SE(A2:A=";; MATRIZ.MULT(H2:H+M2:M+R2:R; TRANSPOR(SINAL(COL(H2:H+M2:M)+R2:R)))))))

我做错了什么?

我的 Google 表格:

https://docs.google.com/spreadsheets/d/1c6ZLnonx8BZ_KV0elW5xiuyQZPi86LOIUzdyVbBHAXQ/edit?usp=sharing

解决方案

你在公式的开头有这个:

SE(A2:A="

但是 A2:A 对于所有行都是空白的.所以您在 S 列中得到了空白，因为这是您指示公式执行的操作.

既然您知道每个活动行的 R2:R 中都有一些内容，只需将公式的该部分更改为以下内容:

SE(R2:R="

但是，您是将公式直接添加到表单输入表中.这从来都不是一个好主意；它会导致很多问题.专业标准是单独留下表单输入表，没有额外的公式、列、条件格式等.相反，使用单独的表从表单输入表中获取结果，然后在第二张表中处理/添加.

I want an array formula to use with Google Forms data to automatically calculate running metrics on my data.

In this case, in column H, M, R and S I want it to auto-calculate whenever new responses come in. Columns H, M and R are ok except for column S.

I'm trying to use this formula below for the sum of each row in these specific columns H, M and R results in a grand total at the column S but isn't working.

=ARRAYFORMULA(SE(A2:A="";; MATRIZ.MULT(H2:H+M2:M+R2:R; TRANSPOR(SINAL(COL(H2:H+M2:M+R2:R))))))

What I'm doing wrong?

My Google Sheet:

https://docs.google.com/spreadsheets/d/1c6ZLnonx8BZ_KV0elW5xiuyQZPi86LOIUzdyVbBHAXQ/edit?usp=sharing

解决方案

You have this at the beginning of your formula:

SE(A2:A=""

But A2:A is blank for all rows. So you are getting blanks in Column S, because that is what you instructed the formula to do.

Since you know that you will have something in R2:R of every active row, just change that segment of your formula to the following:

SE(R2:R=""

However, you are adding formulas directly to the form intake sheet. This is never a good idea; it can cause many problems. The professional standard is to leave form intake sheets alone, with no additional formulas, columns, conditional formatting, etc. Instead, use a separate sheet to bring over results from the form intake sheet and then process/add in that second sheet.

这篇关于ArrayFormula 自动计算特定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！