下拉求和公式但更改列引用

huangapple go评论76阅读模式
英文:

Drag down a sum formula but change column reference

问题

我试图拖动下一个求和公式,但是要更改列引用而不是行引用。

例如,单元格

E1 = 求和('Sheet2!C3:C)
E2 = 求和('Sheet2!D3:D)
E3 = 求和('Sheet2!E3:E)

有什么帮助吗?

英文:

I am trying to drag down a sum formula but change the column reference in stead of row reference.

For example cell

E1 =sum('Sheet2!C3:C)
E2 =sum('Sheet2!D3:D)
E3 =sum('Sheet2!E3:E)

Any help?

答案1

得分: 2

对于您分享的示例,请查看这是否有效(在单元格 E1 中)

=ArrayFormula(mmult(transpose(N(Sheet2!C3:E)), row(C3:C)^0))

更改范围以适应。此公式输出一个数组,因此不需要拖动下来。

英文:

For the example you shared, see if this works (in E1)

=ArrayFormula(mmult(transpose(N(Sheet2!C3:E)), row(C3:C)^0))

Change range to suit.
This formula outputs an array, so dragging down is not needed.

答案2

得分: 1

这里使用查询的公式,C和E可以更改为未来的实际范围:

= ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(C3:E + 0, "SELECT " & JOIN(",", "SUM(Col" & COLUMN(C3:E3) - COLUMN(C3) + 1 & ")"))), "SELECT Col2"))
英文:

Here the formula that use query, C & E can be changed to future actual range:

= arrayformula(query(transpose(query(C3:E +0,"select " & join(",","sum(Col" & COLUMN(C3:E3)-Column(C3)+1 & ")") )),"Select Col2"))

huangapple
  • 本文由 发表于 2020年1月6日 14:59:55
  • 转载请务必保留本文链接:https://go.coder-hub.com/59607925.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定