excel内存数组操作限制

luqiwe

一般地，如果不想加辅助列，很多情景下都要借助数组来完成计算工作。对VBA而言，对数组的操作可以说基本没有做不了的，说效率差的就算了。假设有一内存数组，形如3行3列内存数组myarray={1，2，3；4，5，6；7，8，9}，不用数组公式，我们可以很轻松作指定行（假设为myrow)引用并生成新的一维数组：index(myarray,1,)。但有时我们引用myarray的前2行形成内存数组{1，2，3；4，5，6}，一般地很容易根据上述操作理所当然地得出结果：{index(myarray,row(1:2),)}，实际此路不通，返回的是错误提示，有人要说不就是要引用myarray内存数组的前2行，这还不简单，试试以下公式：=IF(ROW(1:2)<3,INDEX(array1,N(IF(1,ROW(1:2))),N(IF(1,TRANSPOSE(ROW(INDIRECT("$1:$"&COLUMNS(array1))))))))，确实该公式可以引用myarray任意连续行(或指定行），但我们之所以要要做引用，是需要在引用的基础做进一步操作，比如选出每行前2名，有人又要说了，还不简单，在上述公式的基础上加large函数不就得了，实际可行吗，答案不行，大家可以试下：=IF(ROW(1:2)<3,LARGE(INDEX(array1,N(IF(1,ROW(1:2))),N(IF(1,TRANSPOSE(ROW(INDIRECT("$1:$"&COLUMNS(array1))))))),ROW(1:2))),返回结果不是我们想要地内存数组{3，2；6，5}，而是针对{1，2，3；4，5，6}生成该内存数组的前2名{6，5},当然也有人会说，既然不行，那干脆在excel表上选一列，每个单元格储存对应一维的内存数组，求出每个一维内存数组的前2名再引用就可了，事实上这些都容易导致EXCEL陷入假死状态，实用性不强。不能对数组进行整体操作的限制，不知道MS开发工程师是怎样想的，能对数组进行操作，能让我们摆脱公式数量繁冗、单元格稍有变化即重算陷入假死的尴尬困境，我们需要隐式计算，需要全盘少量公式，拖拉同样的公式随不同行列生成我们想要的对应结果。

小書生

LARGE、SMALL有降维的功能，类似sum、max、min等函数，无论几维，无视！

luqiwe

小書生 发表于 2021-8-4 17:19
LARGE、SMALL有降维的功能，类似sum、max、min等函数，无论几维，无视！

很少人会对这种话题感兴趣，除非你是EXCEL函数公式应用的极致追求者。一般地，我们这样来操作函数，比如在C1单元格写好公式，=a1+b1，简单地往下一拖拉，哇！出来一大片，则C2=a2+b2,有多少行就有多少个公式，如果我们能事先生成数组{a1+b1；a2+b2；a3+b3；......},则只需选定想要填充的单元格，然后通过行列隐式生成；这其中蕴含了丰富的信息，并非简简单单的换个写法。目前，MS能提供给我们对整体数组进行分行（列）操作并返回各行（列）的计算结果（生成新的二维数组），只有MMULT函数，但MMULT函数也只是矩阵阵运算后将同一行进行求和，将其他信息基本抹杀干净，这是我们不想看到的，没有了细节，我们实际做不了更多的后续处理工作。试比较下：=IF(ROW(1:2)<3,LARGE(INDEX(array1,N(IF(1,ROW(1:2))),N(IF(1,TRANSPOSE(ROW(INDIRECT("$1:$"&COLUMNS(array1))))))),ROW(1:2)))竟然和=LARGE(IF(ROW(1:2)<3,INDEX(array1,N(IF(1,ROW(1:2))),N(IF(1,TRANSPOSE(ROW(INDIRECT("$1:$"&COLUMNS(array1)))))))),ROW(1:2))，两种公式运算结果应该不一样，似乎才合乎数组公式运行的逻辑，但实际两种公式运算结果竟然一样，令人大跌眼镜，当然这牵扯到公式运行的底层机制，只有EXCEL开发工程师才清楚为什么。只是为什么要禁止我们对二级数组的分层（分行）同步操作，令人困惑不解！

luqiwe

很少人会对这种话题感兴趣，除非你是EXCEL函数公式应用的极致追求者。一般地，我们这样来操作函数，比如在C1单元格写好公式，=a1+b1，简单地往下一拖拉，哇！出来一大片，则C2=a2+b2,有多少行就有多少个公式，如果我们能事先生成数组{a1+b1；a2+b2；a3+b3；......},则只需选定想要填充的单元格，然后通过行列隐式生成；这其中蕴含了丰富的信息，并非简简单单的换个写法。目前，MS能提供给我们对整体数组进行分行（列）操作并返回各行（列）的计算结果（生成新的二维数组），只有MMULT函数，但MMULT函数也只是矩阵阵运算后将同一行进行求和，将其他信息基本抹杀干净，这是我们不想看到的，没有了细节，我们实际做不了更多的后续处理工作。试比较下：=IF(ROW(1:2)<3,LARGE(INDEX(array1,N(IF(1,ROW(1:2))),N(IF(1,TRANSPOSE(ROW(INDIRECT("$1:$"&COLUMNS(array1))))))),ROW(1:2)))竟然和=LARGE(IF(ROW(1:2)<3,INDEX(array1,N(IF(1,ROW(1:2))),N(IF(1,TRANSPOSE(ROW(INDIRECT("$1:$"&COLUMNS(array1)))))))),ROW(1:2))，两种公式运算结果应该不一样，似乎才合乎数组公式运行的逻辑，但实际两种公式运算结果竟然一样，令人大跌眼镜，当然这牵扯到公式运行的底层机制，只有EXCEL开发工程师才清楚为什么。只是为什么要禁止我们对二级数组的分层（分行）同步操作，令人困惑不解！