从Excel中的INDEX函数返回数组? [英] Return array from INDEX function in Excel?

问题描述

我正在尝试在Excel中以数组格式使用INDEX，但是遇到了问题.

I'm trying to use INDEX in array format in Excel but I'm running into problems.

从这个问题开始:从INDEX函数返回数组吗?，看来"INDEX(如VLOOKUP)不会返回值数组(在某些复杂情况下除外)"

From this question: Return array from INDEX function?, it seems that "INDEX (like VLOOKUP) doesn't return an array of values (except in some convoluted circumstances)"

所以我想知道有什么替代方案.

So I'm wondering what alternatives there are.

我正在尝试这样做:

=qlTimeSeries( ,
               INDEX({39618,39619,39638,39639},{2,3,4}),
               INDEX({18,19,38,39},{2,3,4})
               )

并且Index不返回数组.

and Index is not returning an array.

是否有其他想法如何通过INDEX({39618,39619,39638,39639}，{2,3,4})得到我想要得到的结果? 即从{39618,39619,39638,39639}退回{39619,39638,39639}?

Any alternative idea how to get the result I am trying to get with INDEX({39618,39619,39638,39639},{2,3,4})? i.e. from {39618,39619,39638,39639} get {39619,39638,39639} back?

注意:实际上，我的电子表格不是使用INDEX({39618,39619,39638,39639}，{2,3,4})，而是使用动态日期数组，目的是删除数组的第一个元素{ 39618,39619,39638,39639}，这就是为什么我使用INDEX的原因.

NOTE: Instead of INDEX({39618,39619,39638,39639},{2,3,4}) my spreadsheet in fact uses a dynamic array of dates and the objective is to drop the first element of the array {39618,39619,39638,39639} which is why I am using INDEX.

寻找避免使用VBA的解决方案

Looking for a solution that avoids using VBA

推荐答案

尝试以下数组公式:

= INDEX({39618,39619,39638,39639},N(IF({1},{2,3,4})))

这将返回您想要的内容:{39619,39638,39639}.

This will return what you desire: {39619,39638,39639}.

请注意，这是一个数组公式，因此在键入此公式后，您必须按键盘上的 Ctrl + Shift + Enter 而不是按 Enter .

Note this is an array formula, so you must press Ctrl+Shift+Enter on the keyboard after typing this formula instead of just pressing Enter.

(还要注意，它适用于动态范围，而不仅仅是硬编码数组.)

(Also note this works with dynamic ranges, not just hard-coded arrays.)

这篇关于从Excel中的INDEX函数返回数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！