在数组公式中使用INDIRECT

本文介绍了在数组公式中使用INDIRECT的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我将这个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.