如何使用FILTER函数返回无空白的范围。

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

How to use FILTER to return a range with no blanks

问题

I have one sheet called 'Recetas' which has as the first row a list of names. There are some blank cells between each one.

In my main sheet, I want to generate a range with those names without the blank spaces.
I have tried to use =FILTER(ARRAYFORMULA(Recetas!$1:$1), ARRAYFORMULA(NOT(ISBLANK(Recetas!$1:$1)))) but it displays an #ERROR! saying there is an issue with the formula.

英文:

I have one sheet called 'Recetas' which has as first row a list of names. There are some blank cells between each one.

如何使用FILTER函数返回无空白的范围。

In my main sheet I want to generate a range with those names without the blank spaces.
I have tried to use =FILTER(ARRAYFORMULA(Recetas!$1:$1), ARRAYFORMULA(NOT(ISBLANK(Recetas!$1:$1)))) but it displays an #ERROR! saying there is an issue with the formula.

As the documentation says, the first argument should be a range and the second one should be a column/row or in my case an ARRAYFORMULA.

I have checked both arguments in isolation and both work but when I use them in the formula, it doesn't work and the error message doesn't specify what is the problem.

如何使用FILTER函数返回无空白的范围。

答案1

得分: 2

=torow(Recetas!1:1;1)

这可能是你的工作表语言环境使用了分号分隔的约定。

英文:

Can you try:

=torow(Recetas!1:1;1)
  • could be your sheet locale uses semicolon convention

答案2

得分: 1

不需要使用ARRAYFORMULA()进行筛选。请尝试以下公式-

=FILTER(Recetas!$1:$1,Recetas!$1:$1<>"" )

英文:

You do not need ARRAYFORMULA() to filter. Try the following formula-

=FILTER(Recetas!$1:$1,Recetas!$1:$1&lt;&gt;&quot;&quot;)

huangapple
  • 本文由 发表于 2023年5月29日 18:13:23
  • 转载请务必保留本文链接:https://go.coder-hub.com/76356452.html
匿名

发表评论

匿名网友

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

确定