Google表格中带有IF的数组公式未填充列。

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

Array formula with IF not populating column in Google sheets

问题

我似乎无法让我的数组公式从第一个引用的行向下填充,原因不明。

背景:我在一个活动文档中有15个公式,当我创建新行时,我希望它们能够自动填充公式。

看起来可以使用ArrayFormula,并将引用从O2更改为O2:O,使其填充工作表。

我在这里犯了一些基本错误,需要一些帮助 Google表格中带有IF的数组公式未填充列。

测试表链接 *一些标题是瑞典语(抱歉) Google表格中带有IF的数组公式未填充列。

https://docs.google.com/spreadsheets/d/1P7M73-ITRG3LNA5O7yR0grj40d9tFF8Ve6ed6hQkiyc/edit?usp=sharing

我的测试;这在G1的示例表中

原始公式,将其拖入新行中可正常工作

=IF(AND(O2=""),"",IFERROR(MAX(($V2)/$X2,0)))

该公式检查空单元格,如果不为空,将基于其他公式计算百分比。

具有原始公式和标题行的数组

={"Klar"; ArrayFormula(IF(AND(O2=""),"",IFERROR(MAX((V2)/X2,0))))}

这会导致O2按预期填充

某种方式有错误引用,我希望它们可以填充整个列

={"Klar"; ArrayFormula(IF(AND(O2:O=""),"",IFERROR(MAX((V2:V)/X2:X,0))))}

我希望该公式当前填充O2->O6,并在出现数据时进入新行。

我还希望能够为所有15列创建数组。

英文:

I cannot seem to make my array formula populate downwards from the first referenced row for some reason.

Background: I have 15 formulas in an active document that I would like the to autopopulate with the formulas when I create new rows.

The way to do this seems to be ArrayFormula and changing the references from say O2 to O2:O to make it populate the sheet.

I am making some basic error here and I need some assistance Google表格中带有IF的数组公式未填充列。

Link to test sheet *some headers are in Swedish (sorry) Google表格中带有IF的数组公式未填充列。

https://docs.google.com/spreadsheets/d/1P7M73-ITRG3LNA5O7yR0grj40d9tFF8Ve6ed6hQkiyc/edit?usp=sharing

My testing; this is in the example sheet in G1

Orig formula which works fine dragging into new rows

=IF(AND(O2=""),"",IFERROR(MAX(($V2)/$X2,0)))

The formula checks for empty cells and if not it will calculate a percentage based on other formulas.

Array with original formula + header row

={"Klar"; ArrayFormula(IF(AND(O2=""),"",IFERROR(MAX((V2)/X2,0))))}

This results in O2 populating as expected

With somehow incorrect references which I hoped would populate the whole column

={"Klar"; ArrayFormula(IF(AND(O2:O=""),"",IFERROR(MAX((V2:V)/X2:X,0))))}

I expect the formula to currently populate O2-> O6 and to go into new rows with data when they appear.

I also expect to be able to do arrays for all 15 columns.

答案1

得分: 0

=使用MAP()函数。我基于您提供的公式制作了这个公式***=IF(AND(O2=""),"",IFERROR(MAX(($V2)/$X2,0)))***。

=MAP(O2:O,V2:V,X2:X,LAMBDA(x,y,z,IF(x="", "", MAX(y/z,0))))

Google表格中带有IF的数组公式未填充列。

英文:

Use MAP() function. I have made this formula based on your provided formula =IF(AND(O2=""),"",IFERROR(MAX(($V2)/$X2,0))).

=MAP(O2:O,V2:V,X2:X,LAMBDA(x,y,z,IF(x="","",MAX(y/z,0))))

Google表格中带有IF的数组公式未填充列。

huangapple
  • 本文由 发表于 2023年8月10日 16:17:23
  • 转载请务必保留本文链接:https://go.coder-hub.com/76873841.html
匿名

发表评论

匿名网友

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

确定