Excel数组公式

我身边的伴侣有许多人利用Excel，可是都不相识Excel数组公式，就不能认为已经把握了Excel。数组公式是Excel成果量强大的计较公式。可以利用表达式和各类函救来建设数组公式。以办理用普通公式所无法办理的巨大问题。

所谓数组公式。就是对数组举办计较的公式。前面先容的公式根基上都是执行一个简朴计较。而且返回一个计较功效。当需要对两组或两组以上的数据举办计较并返回一个或多个计较功效时。就需要利用数组公式了。

数组公式具有以下特征：

·单击教组公式地址的任意单位格。就可以在公式编辑栏中看到公式前后呈现的大括号“{}”。在公式编辑栏中单击。大括号就会消失。

·输入数组公式的每个单位格中的公式是完全沟通的。

·必需按【Ctrl+Shift+Enter】组合键才气获得数组公式。不然。假如只按【Enter】键。那样获得的是普通公式。

·公式中肯定有单位格区域的引用。可能肯定有数组常量。

·不能单独对数组公式所涉及的单位格区域中的某一个单位格举办编辑、删除或移动等操纵。

·数组公式可以存在于多个单位格中。也可以仅输入到一个单位格。纵然是输入到一个单位格的数组公式。也必需按【Ctrl+Shift+Enter】组合键来建设。

下面团结实际案例来说明数组公式的利用方式。

图1所示的A列至C列数据是从数据库导出的数据。个中C列的批次是数量和单元的殽杂字符串。此刻要求把批次分成数量和单元两部门。别离生存在D列和E列。以便于今后举办统计阐明。

图1

显然。这样的问题不是一个简朴的公式所能办理的，需要利用数组公式。

要将批次中的数量和单元分隔。需要相识批次数据中从左边开始哪些字符是数字。到哪个字符就由数字变为了文本（汉字或字母），这样才气操作文本函数LEFT把数量取出来。操作文本函数MID把单元取出来。下面以单位格C3的批次数据为例。说明办理问题的思路和公式的建设方式及步调。

要想判定批次数据中从左边开始哪些字符是数字。到哪个字符就由数字变为了文本。需要知道批次数据有几多个字符。然后操作MID函数把每个字符取出来。因此。首先利用LEN函数获得批次数据的长度。再操作INDIRECT函数和ROW函数获得一个从1开始到批次数据长度竣事的自然数序列。选择单位格区域G2：G15（可能G列从第2行开始到某行为止。行数要大于批次数据字符长度），输人数组公式“=ROW（INDIRECT（“1：”＆LEN（C3）））”，如图2所示。

图2

有了这个自然数序列。就可以操作MID函数把批次数据的各个字符别离取出来了。选择单位格区域H2：H15.输人数组公式“=MID（c3，G2：G15，1）”。如图3所示。

图3

由于操作MID函数从字符串中取出的岂论是数字照旧文本。都被处理惩罚成文本。因此需要把取出来的批次数据中代表数量的数字转换成真正的数字。选择单位格区域12：|15.输入数组公式“=1*H2：H15”。如图4所示。

图4

显然，从批次数据左侧开始取出的各个数据。只要开始呈现错误值。就表白从该字符开始就是单元了。因此需要判定从哪个位置开始呈现错误值。选择单位格区域J2：J15.输入数组公武“=ISERROR（12：|15）”。如图5所示。

图5

这样，就知道了批次数据各个字符串是否为数字（FALSE暗示是数字，TRUE暗示是文本，第一个呈现TRUE的位置就是单元开始呈现的位置）。因此可以操作MATCH函数确定这个位置。选择单位格K2.输入公式“=MATCH（TRUE.J2:J15.0）”。即可获得这个位置的数宇。如图6所示。

图6

知道了批次数据中从哪个字符开始就是单元。可操作LEFT函数很容易地把数量数字取出。操作MID函数把单元取出。选择单位格L2.输入公式“=1*LEFT（C3.K2—1）”。获得批次数据中的数量数字。如图7所示。

图7

最后。凭据与上面相反的顺序。将中间的计较进程逐一消除，把中间计较进程的计较公式综合成一个公式。步调如下：