在数组公式中使用INDIRECT [英] Using INDIRECT in Array Formula
问题描述
我将这个arrayformula与 INDIRECT 一起使用,因为这是我知道的唯一维护引用的方法,因为引用表在脚本中具有定期向表Data添加另一行的功能!我怎样才能添加到以下公式中,以仅显示数组中在$CQ$17
中的值之上和在$CQ$16
中的值之下的值?因为它每分钟更新一次,可以在不增加我的工作表处理时间的情况下完成吗?
I use this arrayformula along with INDIRECT because it is the only way I know to maintain my references since the reference sheet has in the script to routinely add another row to sheet Data!. How can I either add to the below formula to only show values in the array that are above the value in $CQ$17
and below the value in $CQ$16
? Can this be done without increasing my sheets processing time since it updates every minute?
=ARRAYFORMULA(INDIRECT("Data!E"&13+$F$7&":E"&597+$F$7))
推荐答案
IIUC,您不需要INDIRECT
,可以使用QUERY
:
IIUC, you do not need no INDIRECT
and could use either QUERY
:
=QUERY(
OFFSET(Data!E13, $F$7, 0, 597 - 13 + 1, 1),
"
select E
where E > " & $CQ$17 & " and
E < " & $CQ$16,
-1
)
或FILTER
:
=FILTER(
OFFSET(Data!E13, $F$7, 0, 597 - 13 + 1),
OFFSET(Data!E13, $F$7, 0, 597 - 13 + 1) > $CQ$17,
OFFSET(Data!E13, $F$7, 0, 597 - 13 + 1) < $CQ$16
)
那三遍构造相同范围的效果不好,我会选择QUERY
.
Those three times constructing the same range are not good, I would go with QUERY
.
样本表确实会很有帮助.
A sample sheet would be really helpful though.
这篇关于在数组公式中使用INDIRECT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!