时间戳

首页 > 解决方案 > Google 表格匹配公式仅适用于某些公式，但会在其他公式中引发错误

google-sheets - Google 表格匹配公式仅适用于某些公式，但会在其他公式中引发错误

问题描述

我有一个大的工作表集，我将数据从中提取到摘要选项卡中。我有一些公式，其中许多有效，但有些无效，我不知道为什么。这个公式很好用：

=index(importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$Aj:$aj"),
match($A9&$B9&$C9&$D9&$I9&$N9,importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$A:$a")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$t:$t")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$Ab:$ab")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$b:$b")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$h:$h")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$n:$n"),0),1)

奇怪的是，“匹配”部分本身会引发错误（未找到匹配）：

=match($A9&$B9&$C9&$D9&$I9&$N9,importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$A:$a")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$t:$t")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$Ab:$ab")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$b:$b")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$h:$h")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$n:$n"),0)

最大的问题是这个公式由于“匹配”部分而引发错误：

=iferror(if(hlookup(D9&" ROYALTY FINAL",importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$A:$DB"),
match($A9&$B9&$C9&$D9&$I9&$N9,importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$A:$a")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$t:$t")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$Ab:$ab")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$b:$b")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$h:$h")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$n:$n"),0),FALSE)=0,,
hlookup(D9&" ROYALTY FINAL",importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$A:$DB"),
match($A9&$B9&$C9&$D9&$I9&$N9,importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$A:$a")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$t:$t")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$Ab:$ab")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$b:$b")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$h:$h")&
importrange("1HprNdu3RgOimFFkyqp5k3pC79m-YubH0jtT3jKgDxzU","TEAM SALES!$n:$n"),0),FALSE)),)

这是一个私人工作表集（并且包含数十个互连工作表），所以我无法分享它，但我创建了一个重现错误的示例（如下），但实际上，核心问题是“匹配”在index 公式，但不是单独的，也不在 hlookup 公式中。我刚刚复制了比赛部分，所以我看不出它在某些情况下不起作用的任何原因。任何人都可以看到公式中的任何错误吗？或者怀疑为什么它不起作用？

顺便说一句，我还尝试先在帮助选项卡中导入数据，然后使用此公式（也使用连接进行测试）：

=hlookup(D9&" ROYALTY FINAL",TEMP!$A:$DB,match(concatenate($A9,$B9,$C9,$D9,$I9,$N9),TEMP!$A:$A&TEMP!$T:$T&TEMP!$AB:$AB&TEMP!$B:$B&TEMP!$H:$H&TEMP!$N:$N,0),FALSE)

但这仍然会引发同样的错误......

我已经重新创建了必要的部分，错误就在这里。有问题的表格是这样的：https ://docs.google.com/spreadsheets/d/1nH144yPYyQZ0r1KYCjiNM8uRjPlS2Ziv6uyWfQrFrcQ/edit?usp=sharing

错误/样本位于 Y、Z 和 AB 列的“COMMISSIONS”选项卡上。

它从中导入数据的工作表在这里：https ://docs.google.com/spreadsheets/d/1kNPtrBhRZxaboIddTM0hGnwJva02hMaKO-67F1FAtUw/edit?usp=sharing

标签： google-sheetsgoogle-sheets-formula

解决方案

我找到了一个可能的答案，但我不确定你期待什么结果，所以我不能确定。如果您选择当前显示错误的 Commissions 中的单元格 AB8，然后执行 Ctrl-Shift-Enter 使其成为数组公式，这似乎有效。我认为（不是肯定的）这是因为使用 & 符号的连接仅作用于多个 IMPORTRANGE 语句的第一行。通过删除公式的匹配部分来测试这一点，只留下：

={ 
importrange("1kNPtrBhRZxaboIddTM0hGnwJva02hMaKO-67F1FAtUw","TEAM SALES!$A:$a")&
importrange("1kNPtrBhRZxaboIddTM0hGnwJva02hMaKO-67F1FAtUw","TEAM SALES!$t:$t")&
importrange("1kNPtrBhRZxaboIddTM0hGnwJva02hMaKO-67F1FAtUw","TEAM SALES!$Ab:$ab")&
importrange("1kNPtrBhRZxaboIddTM0hGnwJva02hMaKO-67F1FAtUw","TEAM SALES!$b:$b")&
importrange("1kNPtrBhRZxaboIddTM0hGnwJva02hMaKO-67F1FAtUw","TEAM SALES!$h:$h")&
importrange("1kNPtrBhRZxaboIddTM0hGnwJva02hMaKO-67F1FAtUw","TEAM SALES!$n:$n")}

在我的测试中，这只返回一行。

但是添加 ARRAYFORMULA 带回了整个属性列表。

让我知道这是否有帮助。我会看看你问的其他问题...

更新：

如果这对您有用，那么可以按如下方式对其进行增强，以填充整个列。在单元格 AB8 中试试这个：

= ArrayFormula(match($A8:A&$B8:B&$C8:C&$D8:D&$I8:I&$N8:N,
importrange("1kNPtrBhRZxaboIddTM0hGnwJva02hMaKO-67F1FAtUw","TEAM SALES!$A:$A")&
importrange("1kNPtrBhRZxaboIddTM0hGnwJva02hMaKO-67F1FAtUw","TEAM SALES!$t:$t")&
importrange("1kNPtrBhRZxaboIddTM0hGnwJva02hMaKO-67F1FAtUw","TEAM SALES!$Ab:$ab")&
importrange("1kNPtrBhRZxaboIddTM0hGnwJva02hMaKO-67F1FAtUw","TEAM SALES!$b:$b")&
importrange("1kNPtrBhRZxaboIddTM0hGnwJva02hMaKO-67F1FAtUw","TEAM SALES!$h:$h")&
importrange("1kNPtrBhRZxaboIddTM0hGnwJva02hMaKO-67F1FAtUw","TEAM SALES!$n:$n"),0))

一旦取消合并该单元格，就可以将其复制到 AB3。

推荐阅读