如何使用ArrayFormula组合多个函数

Question

使用ImportRange和Query以及正确工作的Join和Split，我得到了以下公式：

=join(" / ", QUERY(IMPORTRANGE("Google-Sheet-ID","RawData!A:AC"),"select Col25 where Col1 = " & JOIN(" OR Col1 = ", split(V2:V,"+")), 0))

此外，我有下面的ArrayFormula和Split功能可以顺利工作：

=ARRAYFORMULA(if(len(V2:V)=0,,split(V2:V,"+")))

但当我尝试使用以下公式将它们组合在一起时：

=ARRAYFORMULA(if(len(V2:V)=0,,join(" / ", QUERY(IMPORTRANGE("Google-Sheet-ID","RawData!A:AC"),"select Col25 where Col1 = " & JOIN(" OR Col1 = ", split(V2:V,"+")), 0))))

它失败了，给了我以下错误：

错误函数SPLIT参数1的值应为非空。

这是我的测试表。

更新

我把它改成：

=ARRAYFORMULA(if(len(C2:C)=0,,JOIN(" OR Col1 = ", ARRAYFORMULA(if(len(C2:C)=0,,split(C2:C,"+"))))))

所以我的完整公式是：

=ARRAYFORMULA(
if(
len(C2:C)=0,,
join(" / ", 
QUERY(
IMPORTRANGE("14iNSavtvjRU0XipPWIMKyHNwXTA85P_CafFTsIPHI6c","RawData!A:AC"),"select Col25 where Col1 = " & 
ARRAYFORMULA(
if(len(C2:C)=0,,
JOIN(" OR Col1 = ", 
ARRAYFORMULA(
if(
len(C2:C)=0,,split(C2:C,"+")
)
)
)
)
),
0
))))

现在得到错误：

错误JOIN范围必须是单行或单列。

Answer 1

我相信这个名为MK.测试的选项卡上的公式会得到你想要的信息。

=QUERY(IMPORTRANGE("14iNSavtvjRU0XipPWIMKyHNwXTA85P_CafFTsIPHI6c","RawData!A:AC"),"select Col25 where Col1="&TEXTJOIN(" or Col1=",TRUE,A2:A))

我想你可能把事情搞得太复杂了？这个公式只是从发货ID中形成一个文本字符串，以便在查询中使用。有一件事可能会让你感到困惑，那就是query((对列中数据的类型非常挑剔。您的发货ID可以是数字，也可以是数字字母组合，但不能两者都是。也就是说，如果您的某些发货ID包含字母，而其他发货ID不包含字母，那么将更难获得有效的查询。(尽管并非不可能(。不过，为了帮助您，您的样本ID以这种方式尽可能准确地反映真实的ID是很重要的。

Answer 2

用Apps Script来做这件事怎么样？您可以从Sheet2、Shipment Id和MK中获取值。测试并比较它们。如果它们一致，则将ETA复制到MK的C列中。测试：

function myFunction() {
var sprsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet2 = sprsheet.getSheetByName("Sheet2");
var mkTesting = sprsheet.getSheetByName("MK.Testing");
var shipmentId = sheet2.getRange("A2:A").getValues();
var idList = mkTesting.getRange("A2:A").getValues();
for (var i = 0; i < shipmentId.length; i++){
for (var j = 0; j < idList.length; j++){
if (idList[j][0] == ""){break;} //Stops if there is an empty cell in Mk.Testing's column A
if (idList[j][0] === shipmentId[i][0]){
var eta = sheet2.getRange("E"+(i+2)).getValue();
mkTesting.getRange("C"+(j+2)).setValue(eta);
}
}
}
}

参考文献：

• 电子表格应用程序类
• 范围类别