pivot returning blank instead of 0 google sheet

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

pivot returning blank instead of 0 google sheet

问题

以下是您的翻译内容:

我有一个电子表格,在其中我使用importrange和vlookup来查找另一个文件中的数据,该文件查找的是一个数据透视表。在数据透视表中有一些空白数据,但当我在公式中查找时,尽管我已经设置了iferror返回为0,但我仍然得到了空白的结果。

这是我的公式:

=iferror(VLOOKUP(A5,importrange("12PaJfEC7Q7gOcCx2zlMHG3YybQuk1TSsNjZDw26qFRg","Converted Pivot!A:E"),3,false),0)

英文:

I have a spreadsheet where I have an importrange and vlookup to another file where its looking up to a pivot table. Some data is blank in the pivot table and when I lookup in the formula, I have a result of blank even though I have set it to return to 0 by iferror.

Here's my formula:

=iferror(VLOOKUP(A5,importrange("12PaJfEC7Q7gOcCx2zlMHG3YybQuk1TSsNjZDw26qFRg","Converted Pivot!A:E"),3,false),0)

答案1

得分: 1

=let(Σ,如果错误是(vlookup(A5,importrange("12PaJfEC7Q7gOcCx2zlMHG3YybQuk1TSsNjZDw26qFRg","Converted Pivot!A:E"),3,),"no_match_found"),
如果(Σ="",0,Σ))

  • blank_value现在将显示为0,并且将出现一个no_match_found的非匹配输出错误。
英文:

You may try:

=let(Σ,ifna(vlookup(A5,importrange("12PaJfEC7Q7gOcCx2zlMHG3YybQuk1TSsNjZDw26qFRg","Converted Pivot!A:E"),3,),"no_match_found"),
       if(Σ="",0,Σ))
  • blank_value will now be shown as 0 & a non-match output error will be prompted with no_match_found

huangapple
  • 本文由 发表于 2023年6月2日 01:58:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/76384531.html
匿名

发表评论

匿名网友

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

确定