带有嵌套if语句的Google表格ARRAYFORMULA [英] Google Sheets ARRAYFORMULA with nested if statements

问题描述

我正在研究一种我认为将是相当简单的arrayformula，其中我只需要一个单元格条目，而不必将其复制到100多行.

Im working on what I thought would be a fairly straight forward arrayformula where I only needed a single cell entry instead of having to copy it down to 100+ rows.

我正在开发一个电子表格，用于Pathfinder中的野兽形状，元素身体和植物形状法术.

I am working on a spreadsheet for use with the beast shape, elemental body, and plant shape spells in Pathfinder.

我的桌子看起来像

_________ A ___________ B ________ C

_________A___________B________C

1 ___ Spell name _____ Type _____ Size

1___Spell name_____Type_____Size

其中A2将包含arrayformula. B2:B将包含动物，植物，元素，魔法野兽. C2:C将包含大小范围.

Where A2 will contain the arrayformula. B2:B will contain Animal, Plant, Elemental, Magical Beast. C2:C will contain the range of sizes.

我尝试过

ARRAYFORMULA(if(B2:B<>"，CONCATENATE(if(AND(B2:B ="Animal"，OR(C2:C ="Small"，C2:C ="Medium"))，"Beast Shape 1，" Fail BS1)，if(AND(B2:B =" Animal，OR(C2:C =" Tiny，C2:C =" Large))，" Beast Shape 2，" BS2失败)")，其他失败"))

ARRAYFORMULA(if(B2:B<>"",CONCATENATE(if(AND(B2:B="Animal", OR(C2:C="Small", C2:C="Medium")),"Beast Shape 1","Fail BS1"),if(AND(B2:B="Animal", OR(C2:C="Tiny", C2:C="Large")),"Beast Shape 2","Fail BS2")),"Fail Other"))

和

= ARRAYFORMULA(if(B2:B<>"，if(and(B2:B ="Animal"，C2:C ="Small")，"Beast Shape 1"，"not")，"if失败"))

=ARRAYFORMULA(if(B2:B<>"",if(and(B2:B="Animal",C2:C="Small"),"Beast Shape 1", "not"), "if fail"))

但是它们产生不正确的输出.我也尝试了其他几种方法，但是存在各种问题，通常不会在所有单元中传播，或者只是信息错误.即使我只尝试使用一条语句.

But they produce incorrect output. I have tried several others as well but with various issues, usually not being propagated down all the cells, or just having the wrong information. Even when I try using only a single statement.

最初，我的计划是创建一个concat语句，其中包含多个if语句，这些语句将产生拼写名称或什么都不产生.看起来像

Originally my plan was to have a concat statement which contained multiple if statements which would produce the spell name or nothing. It looked something like

arrayformula(if(b2:b<>"，concatenate(if(and(b2:b ="Animal"，OR(c2 ="small"，c2 ="medium"))，野兽形状1" ，")，if(and(b2:b ="Animal"，OR(c2 ="tiny"，c2 ="large"))，野兽形状2"，"))

arrayformula(if(b2:b<>"",concatenate(if(and(b2:b="Animal",OR(c2="small",c2="medium")), "Beast shape 1", ""), if(and(b2:b="Animal",OR(c2="tiny",c2="large")), "Beast shape 2", ""))

但是输出错误，我还无法弄清原因.我已经在某些地方看到了注释，这些注释表明AND和OR无效，因为它们具有单个输出语句，并且该连接必须用&替换，但是即使将其删除，它也不起作用.

However the output is wrong, and I havent been able to figure out why. Ive seen comments in places which suggests AND and OR are not valid as they have single output statements, and that concatenate must be replaced with &, but even when those are removed it does not work.

推荐答案

用*替换AND，并用+替换OR:

Replace AND with * and OR with +:

if(and(b2:b="Animal",OR(c2="small",c2="medium"))

成为:

if( (b2:b="Animal") * ((c2:c="small") + (c2:c="medium")) )

array-formula 必须一起工作.

这篇关于带有嵌套if语句的Google表格ARRAYFORMULA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！