英文:
array formula for summing values down columns
问题
我有一个包含未知列数的表格（这些列是从电子表格中的其他地方的一些行的转置中读取的），我试图应用一个数组公式以包括对这些列的每一个求和。
我觉得我可以使用bycol和lambda之类的东西来做到这一点，但我在努力想出如何选择正确的范围，以便在可变列中给出给定行。任何帮助将不胜感激。谢谢！
示例表格在这里 https://docs.google.com/spreadsheets/d/1zHMN7YiXCZKnLgrcyonMmKIicLWn9MmwokRMLUdCke4/edit?usp=sharing
英文:
I have a table with an unknown number of columns (the columns are read from a transpose of some rows elsewhere in the spreadsheet) and I'm trying to apply an array formula to include a sum over each of these columns.
I feel like I could do this with a bycol and lambda or something, but I'm struggling to figure out how to choose the correct ranges to have given rows from variable columns. Any help would be appreciated. Thanks!
Example sheet here https://docs.google.com/spreadsheets/d/1zHMN7YiXCZKnLgrcyonMmKIicLWn9MmwokRMLUdCke4/edit?usp=sharing
答案1
得分: 2
=bycol(E4:99999,lambda(Σ,let(Λ,index(2:2,column(Σ)),
if(and(Λ="",counta(Σ)=0),,Λ-sum(Σ)))))
英文:
<!-- language-all: js -->
You may try:
=bycol(E4:99999,lambda(Σ,let(Λ,index(2:2,column(Σ)),
if(and(Λ="",counta(Σ)=0),,Λ-sum(Σ)))))
答案2
得分: 1
如果你想要一个范围在垂直方向扩展到表格的末尾，你可以省略最后一行。例如 A1:A。
如果你想要一个范围在水平方向扩展到表格的末尾，你可以省略最后一列。例如 A1:1。
如果你想要一个范围在垂直和水平方向都扩展到表格的末尾，你可以省略最后一行，并将最后一列输入为 ZZZ。例如 A1:ZZZ。
所以你可以使用以下公式：
=ARRAYFORMULA(IF(E2:2,E2:2-BYCOL(E4:ZZZ,LAMBDA(c,SUM(c))),))
英文:
If you want a range to expand vertically until the end of the spreadsheet, you omit the last row. For example A1:A.
If you want a range to expand horizontally until the end of the spreadsheet you omit the last column. For example A1:1.
If you want a range to expand both vertically and horizontally until the end of the spreadsheet, you omit the last row and enter ZZZ as the last column. For example A1:ZZZ.
So here's what you can use:
=ARRAYFORMULA(IF(E2:2,E2:2-BYCOL(E4:ZZZ,LAMBDA(c,SUM(c))),))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论