在数组公式中使用 INDIRECT [英] Using INDIRECT in Array Formula

问题描述

我将这个数组公式与 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(
  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.

样品表会很有帮助.

这篇关于在数组公式中使用 INDIRECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！