转换用于 ArrayFormula 的方程会产生奇怪的值

Dan 发表于 Dev

和

对于 Y 列，我有以下（非常丑陋的）方程式：

=IF(ISTEXT(Q2),"NO VIDEO DATA",IF(REGEXMATCH(VLOOKUP(I2,DEALS_RNG,COLUMN(DEALS!$G$1)-COLUMN(DEALS!$A$1)+1,FALSE), "Likes"), IF(P2>=(VLOOKUP(I2,DEALS_RNG,COLUMN(DEALS!$F$1)-COLUMN(DEALS!$A$1)+1,FALSE)), IF((R2<> W2),IF(R2,CONCATENATE("DUE: ", CONCATENATE("$",M2)),IF(W2,CONCATENATE("DUE: ", CONCATENATE("$",L2)),)),IF((R2* W2)>0,"COMPLETED",CONCATENATE("DUE: ", CONCATENATE("$",Z2)))),IF(today() >= N2+K2,IF(X2,"PAID: MUST REPOST","NO PAYMENT: MUST REPOST"),IF(today() < N2+K2,CONCATENATE((N2+K2)-today()," days remaining"),))), IF(Q2>=(VLOOKUP(I2,DEALS_RNG,COLUMN(DEALS!$F$1)-COLUMN(DEALS!$A$1)+1,FALSE)), IF((R2<> W2),IF(R2,CONCATENATE("DUE: ", CONCATENATE("$",M2)),IF(W2,CONCATENATE("DUE: ", CONCATENATE("$",L2)),)),IF((R2* W2)>0,"COMPLETED",CONCATENATE("DUE: ", CONCATENATE("$",Z2)))),IF(today() >= N2+K2,IF(X2,"PAID: MUST REPOST","NO PAYMENT: MUST REPOST"),IF(today() < N2+K2,CONCATENATE((N2+K2)-today()," days remaining"),)))))

没有深入了解这里发生的完整逻辑，基本上就像现在一样，当我将它向下拖动时它运行正常：

我将我的 AND() 更改为 * 并将 XOR() 更改为 <> 以及我的单元格从 Q2 到 Q2:Q 等。但是一旦在 ArrayFormula 中，公式不再正常工作并返回奇怪的数字（也许是内存？idk）：

谁能帮助我了解发生了什么以及如何解决这个问题？

感谢我能得到的任何帮助！

玩家0

尝试：

=ARRAYFORMULA(IFERROR(IF(ISTEXT(Q2:Q), "NO VIDEO DATA",
 IF(REGEXMATCH(VLOOKUP(I2:I, DEALS_RNG, 7, ), "Likes"), 
 IF(P2:P>=(VLOOKUP(I2:I, DEALS_RNG, 6, )), 
 IF(R2:R<>W2:W, 
 IF(R2:R, "DUE: $"&M2:M,
 IF(W2:W, "DUE: $"&L2:L, )),
 IF((R2:R*W2:W)>0, "COMPLETED", "DUE: $"&Z2:Z)),
 IF(TODAY()>=(N2:N+K2:K), 
 IF(X2:X, "PAID: MUST REPOST", "NO PAYMENT: MUST REPOST"),
 IF(TODAY()<(N2:N+K2:K), (N2:N+K2:K)-TODAY()&" days remaining", ))), 
 IF(Q2:Q>=VLOOKUP(I2:I, DEALS_RNG, 6, ), 
 IF(R2:R<>W2:W, 
 IF(R2:R, "DUE: $"&M2:M,
 IF(W2:W, "DUE: $"&L2:L, )),
 IF((R2:R*W2:W)>0, "COMPLETED", "DUE: $"&Z2:Z)),
 IF(TODAY()>=(N2:N+K2:K),
 IF(X2:X, "PAID: MUST REPOST", "NO PAYMENT: MUST REPOST"),
 IF(TODAY()<(N2:N+K2:K), (N2:N+K2:K)-TODAY()&" days remaining", )))))))

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。