首先,我想澄清一点,我正在寻找技术解释,说明为什么会出现以下问题,而不是寻求达到预期结果的替代设置(我将在任何情况下提出几个这样的替代方案作为这个问题) .
另外,我没有在 Excel 2010
以外的Excel版本中测试过这个,所以我不知道这个问题是否是版本特定的 .
Row/ B C
Col
1 X 91
2 X 96
3 X 21
4 X 64
5 3
6 X 60
7 52
8 X 37
9 79
10 91
假设所有条目都在 Sheet1
中进行,那么例如,在 C1:C10
范围内给出一系列数字,并且 B1:B10
范围内的每个条目都是"X"或不是(上面给出的示例),以下结构:
=INDEX($C:$C,N(IF(1,MODE.MULT(IF($B$1:$B$10="X",{1,1}*ROW($B$1:$B$10))))))
正确强制,将生成n个条目的垂直数组,其中n等于以下结果:
=COUNTIF($B$1:$B$10,"X")
该数组将包含来自C列的n个条目,其中B列中的对应条目是"X" . 对于上面的示例,此构造将返回:
{91;96;21;64;60;37}
然后在 Name Manager
中输入该公式作为(工作簿范围) Defined Name
,例如,名称 Series_MODE_MULT
. 请注意,作为 Defined Name
,默认情况下,构造被评估为 array formula
,因此满足了我之前提到的"properly coerced"的条件 .
尝试使用单个系列创建简单图表(例如,列)时,其 Series Values
输入为:
=Sheet1!Series_MODE_MULT
Excel立即崩溃 .
但为什么?
我现在已经搜索了几个小时的解释 . 我能提出的唯一类似案例涉及在建筑物中使用 INDIRECT
作为图表的 Series Values
;但是,虽然这也是无效的(我们实际上可以使用 EVALUATE
开发变通方法),但用户会遇到错误消息“此工作表中的公式包含一个或多个无效引用 . ”,当然,这不是完全相同的是平坦的崩溃 .
我的 Headers 表明该问题可能与函数 MODE.MULT
特别相关 . 我的理由是我们可以使用替代(虽然更长,在某些情况下,易失性)构造,这些构造生成所需的数组但在作为Chart的 Series Values
传递时完全有效:
=INDEX($C:$C,N(IF(1,SMALL(IF($B$1:$B$10="X",ROW($B$1:$B$10)),ROW(INDEX($A:$A,1):INDEX($A:$A,COUNTIF($B$1:$B$10,"X")))))))
要么:
=N(OFFSET($C$1,SMALL(IF($B$1:$B$10="X",ROW($B$1:$B$10)-MIN(ROW($B$1:$B$10))),ROW($A$1:OFFSET($A$1,COUNTIF($B$1:$B$10,"X")-1,))),))
仅仅是两个这样的例子 .
MODE.MULT
在某种程度上负责吗?是什么让这种结构在工作表中有效,尽管不在Chart的 Series Values
中?是否有任何其他构造/功能,以这种方式使用,同样导致Excel崩溃?如果是这样,这种行为的解释是什么?
任何帮助将不胜感激 .
问候