为什么这个数组公式不起作用? [英] Why this array formula doesn't work?

问题描述

在插图中，所有公式都是数组.每个公式所覆盖的范围都带有边框，并且每个块上的第一个公式都写在该块的顶部.

范围A4:A103是输入向量(数字)，范围C4:G23是A4:A103的行的给定(输入)排列(必须为正的非零整数，其长度不大于n的长度)输入向量).

Range A4:A103 is an input vector (which is numeric), range C4:G23 is a given (input) permutation of the rows of A4:A103 (necessarily positive non-zero integer numbers not greater then the length of the input vector).

让我们将置换矩阵解释为行集.

Let us I interpret the permutation matrix as set of rows.

如何为恒定数量的单元格中的每一行 计算输入向量中的最小数量?通过恒定数量的单元格，我的意思是解，那将是无论排列的列数是多少，每行都需要固定数量的单元格. (在生产情况下，每个维度要大得多；在排列矩阵中大约有100列.)

How to compute for each row in a constant number of cells the minimal number in the input vector? By the constant number of cells, I mean solution, that would require fixed number of cells for each row, regardless of the number of columns in permutation. (In the production case each dimension is much, much bigger; there is about 100 columns in the permutation matrix.)

我不要求VBA解决方案.如果有必要，该解决方案可以使用免费的和公开可用的Excel加载项，例如MoreFunc，但我希望将其保留为原始Excel 2007或更高版本.

I don't ask for VBA solutions. If it is necessary the solution can use a free and publicly available Excel add-on, like MoreFunc, but I'd prefer to keep it vanilla Excel 2007 or later.

我认为公式{=MIN(INDEX(INDIRECT($A$2);$C4:$G4))}将解决我的问题.出人意料的是，Excel似乎没有考虑公式的数组性质，而是像将其写为=MIN(INDEX(INDIRECT($A$2);$C4)等效于功能不正常的=INDEX(INDIRECT($A$2);$C4)一样对其进行评估.

I thought that the formula {=MIN(INDEX(INDIRECT($A$2);$C4:$G4))} would solve my problem. Surprisingly, Excel seems to not take into account the array nature of the formula, and evaluates it as if it was written as =MIN(INDEX(INDIRECT($A$2);$C4) which is equivalent to dysfunctional =INDEX(INDIRECT($A$2);$C4).

另一方面，我们可以看到MIN的参数被理解为I4:M4范围内的数组.

On the other hand, we can see the argument to the MIN is understood as array in the range I4:M4.

推荐答案

INDEX的工作方式有些奇怪！

INDEX works in some strange ways!

通常INDEX不能返回数组-尽管您似乎发现了一个例外-当它是在范围内输入的数组公式时.

Normally INDEX can't return an array - although you seem to have found the one exception to that - when it's an array formula entered in a range.

您应该能够使用OFFSET返回将在MIN中运行的必需数组，即使用此公式

You should be able to use OFFSET to return the required array that will work within MIN, i.e. with this formula

=MIN(N(OFFSET(INDIRECT($A$2);$C4:$G4-1;0)))

已通过 CTRL + SHIFT + ENTER

这篇关于为什么这个数组公式不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！