我有一个Excel公式来查找行中最后一个负值单元格的列号。问题是它是一个数组公式,我需要用来创建电子表格的工具(Apache POI SXSSF(不支持数组公式。
行中的值不以任何方式排序。
手工制作电子表格时有效的数组公式是
{=MATCH(2,1/(B18:M18<0))}
有没有办法用普通公式达到相同的结果?
感谢您的任何提示!
=MATCH(2,1/MMULT(1,-(B18:M18<0)))
解释
如果正常输入原始公式,则使用隐式交集计算范围B18:M18
(例如,如果在C5
中输入,则计算结果为返回#N/A
的=MATCH(2,1/(C18<0))
(。使用 CTRL+SHIFT+Enter 输入公式意味着将范围评估为数组而不是单个单元格区域。
MMULT
是少数可以从一系列值返回数组的函数之一。要在这里使用它,我们首先需要使用 -(B1:B18<0)
将布尔值数组转换为数字(或用 N
代替 -
(。接下来,我们预先乘以 1
(即 1x1 矩阵(以返回相同的数组,就好像它经过数组评估一样。这适用于水平数组,对于垂直数组,我们需要后乘以 1,这意味着切换参数。
这种方法非常普遍。作为另一个示例,请考虑使用公式 =MAX(IF(B1:B18<0,B1:B18))
查找范围内的最大负值。再次将B1:B18<0
替换为 MMULT(1,-(B1:B18<0))
消除了对数组输入的需要。如果不尝试提出一个完全不同的公式,我不知道任何其他方法可以做到这一点。