英文:
Array formula with IF not populating column in Google sheets
问题
我似乎无法让我的数组公式从第一个引用的行向下填充,原因不明。
背景:我在一个活动文档中有15个公式,当我创建新行时,我希望它们能够自动填充公式。
看起来可以使用ArrayFormula,并将引用从O2更改为O2:O,使其填充工作表。
我在这里犯了一些基本错误,需要一些帮助
测试表链接 *一些标题是瑞典语(抱歉)
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
Link to test sheet *some headers are in Swedish (sorry)
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))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论