Excel如何返回满足一定条件的数组？

如果我的单元格范围内有数据A1:A6这是：

Apple
Banana
Cherry
Grape
Orange
Watermelon

有没有办法返回一个数组（在单个单元格中，用于较大公式的中间步骤），该数组返回上述数组，除了那些满足特定条件的条目之外？

例如，如果我想要一个公式返回仅包含包含字母的单元格的数组n，它会返回这个：

Banana
Orange
Watermelon

有办法做到这一点吗？

注意我做not想要返回一个相同大小的数组，只是包含空白条目，即我不想要：

""
Banana
""
""
Orange
Watermelon

Yes.

这是数组公式（为了可读性添加了换行符）：

= INDEX(A1:A6,N(IF({1},MODE.MULT(IF(ISNUMBER(SEARCH("n",A1:A6)),
  (ROW(A1:A6)-ROW(A1)+1)*{1,1})))))

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

这个公式有一些特别奇怪的事情，所以我想如果你感兴趣的话我会解释下面的内容。我在下面解释的一些内容可能是显而易见的，但我只是说得很彻底。

从基于列表的结果返回single索引，使用这个：

= INDEX(A1:A6,2)

这将返回Banana.

从基于列表的结果返回multiple索引，你会想到使用这样的东西：

= INDEX(A1:A6,{2;5;6})

理想情况下这会返回{Banana;Orange;Watermelon}.

但是，这不会返回数组。基于我最近问的一个问题 https://stackoverflow.com/questions/47187863/can-excels-index-function-return-array，针对这个问题给出了一个非常聪明的解决方法：

= INDEX(A1:A6,N(IF({1},{2;5;6})))

这将返回所需的结果{Banana;Orange;Watermelon}.

我认为这是解释的第 1 部分。

第 2 部分的解释是以下内容如何返回{2;5;6}:

= MODE.MULT(IF(ISNUMBER(SEARCH("n",A1:A6)),(ROW(A1:A6)-ROW(A1)+1)*{1,1}))

MODE.MULT是一个返回集合中出现最频繁的数据的函数。不过，有一些注意事项：

1. 数据必须至少出现两次才能返回MODE.MULT。如果没有重复数据，则会返回错误。例如，MODE.MULT({1;2;3})会返回错误，因为数组中没有重复数据{1;2;3}。另一个例子：MODE.MULT({1;1;2}会回来1因为1最常出现在数据中。

2. 如果出现次数最多的数据存在“平局”，MODE.MULT返回所有绑定条目的数组。例如MODE.MULT({1;1;2;2})将返回一个数组{1;2}.

3. 最重要的是，也可能是最奇特但也是最有用的行为MODE.MULT, MODE.MULT 完全忽略逻辑值（TRUE and FALSE值）确定数据模式时，even if它们比数据中的非逻辑值出现的频率更高。

我们可以利用这些属性MODE.MULT以获得所需的数组。

ISNUMBER(SEARCH("n",A1:A6))返回一个数组TRUE/FALSE数据包含的值n。像这样的事情：

FALSE
TRUE
FALSE
FALSE
TRUE
TRUE

(ROW(A1:A6)-ROW(A1)+1)返回一个从以下位置开始的数组1并增加 1 到原始数组的大小：

1
2
3
4
5
6

(ROW(A1:A6)-ROW(A1)+1)*{1,1}实际上只是复制此列：

1  1
2  2
3  3
4  4
5  5
6  6

The IFif 语句用于返回上面数组中的数字TRUE, and FALSE否则。 （自从IF语句不包含“else”子句，FALSE是给定的默认值。）

在此示例中，IF语句将返回：

FALSE  FALSE
2      2
FALSE  FALSE
FALSE  FALSE
5      5
6      6

Taking MODE.MULT上述公式将返回{2;5;6}因为如前所述，MODE.MULT方便地忽略FALSE考虑模式时上面数组中的值。

需要考虑上面的数组里面MODE.MULT而不是简单地：

FALSE
2
FALSE
FALSE
5
6

因为正如前面提到的，MODE.MULT要求at least数据中的两个条目需要匹配才能返回值。