概括
使用 Excel 的新动态/溢出数组,从 2D 溢出数组中提取单个 1D 溢出列的最佳方法是什么?
细节
假设我在 Excel 中有一个二维动态数组,并且它正在溢出,所以我不知道它有多少行(并且它甚至可能随着输入数组的数据的变化而变化)。
现在,如果我想引用整个数组,我可以使用溢出范围运算符 # 来实现。例如,假设我的数组位于 H3:L12(但请记住,虽然它目前有 10 行,但我无法知道这一点,也无法知道它不会改变)。现在,如果我在单元格 N8(例如)中输入公式 =H3#,那么我将在 N8:R17 中获得初始数组的副本。
但是假设我想要的只是原始数组 I3:I12 的第二列的副本。或者假设我想要原始数组,但要应用过滤器。例如,也许我想要一个新数组,该数组仅包含原始数组中的行,其中每行中的第二个单元格大于 5。有没有一种简洁的方法来做这些事情,也许使用溢出范围运算符?
当然,也有一些不太简洁的方法。例如,虽然我说过这是一个动态数组,这意味着我不知道行数,但实际上我可以使用 ROWS(H3#) 来计算。然后我可以像这样使用 OFFSET()。
=OFFSET(H3,0,ROWS(H3#),1)
然后对于第二种情况,我想要完整数组但经过过滤使得第二列大于 5,我可以这样做:
=FILTER(H3#,OFFSET(H3,0,ROWS(H3#),1)>5)
在这两个公式中,我都没有明确提到“硬编码”的行数,因此“动态”性质得以保留,这很好。但似乎很笨拙地必须费力提取该行数,然后求助于 OFFSET(),尤其是考虑到这是一个易挥发的函数。有没有更简单、更内置的方法来实现这个功能?
谢谢。
答案1
要返回第二列,请使用 Index 函数:
=INDEX(H3#,0,2)
并且您可以使用该FILTER
函数返回过滤后的数组。
例如:
=FILTER(H3#,INDEX(H3#,0,2)>=5)