使用Excel数组公式计算数据,实现小空间大计算

鉴水鱼技能说 2024-03-20 02:23:08

公式是Excel中以等号开头的可以得到一个结果的等式,公式以等号“=”开头,公式中可以包括函数、运算符、引用和常量。相对于普通公式而言,在Excel中还有另一种形式的公式:数组公式。

数组公式可以对两组或两组以上的数据执行运算,执行多项计算后返回一个或多个结果。每一组数据就是一个数组,数组可以是同一行或者同一列中的多个数据,也可以是一个多行多列的区域中的数据。数组公式可以在小空间内进行大量计算时使用,它可以替代许多重复的公式,并由此节省内存。

数组公式通常也被称作 CSE (Ctrl+Shift+Enter组合键) 公式,因为输入公式后不是只按 Enter 键,而是要按 Ctrl+Shift+Enter组合键 完成公式的输入。

与普通公式相同,数组公式同样需要以等号“=”开头,在创建数组公式时,有两种不同的方式,针对一个单元格创建的数组公式和针对单元格区域创建的数组公式。

一、在多个单元格中使用数组公式进行计算

数组公式就是指对两组或多组参数进行多重计算,并返回一个或多个结果的计算公式。使用数组公式时,要求每个数组参数必须有相同数量的和与列。在多个单元格中使用数组公式进行计算的具体操作方法如下:

图1

例如上图1所示,有一个【销售订单】工作表;(1)选择存放结果的F5:F10单元格区域,输入等号【=】;(2)移动鼠标选择要参与计算的第一个单元格区域D5:D10,如下图2所示:

图2

(3)参照上述操作方法,继续输入运算符号(乘号*),并拖动鼠标选择要参与计算的单元格区域E5:E10,如下图3示:

图3

按【Ctrl+Shift+Enter】组合键,得出数组公式计算结果,如下图4所示。

图4

在Excel中,显示的数组公式是用大括号“{}”括起来的,以区分于普通的的Excel公式。上例完整操作过程如下动图5示:

图5

二、在单个单元格中使用数组公式进行计算

在编辑工作表时,还可以在单个单元格中输入数组公式,以便完成多步计算,具体的操作方法如下:

图6

有如上图6所示的销售订单,现要计算【销售总额】显示到E11单元格中中,常规算法是:算出每一项,然后再累加。这里我们使用数组计算方式来求,具体操作步骤如下:

(1)在单元格E11中输入公式【=sum()】,再将光标定位到括号括号内,如下图7所示:

图7

(2)拖动鼠标选择要参与计算的第1个单元格区域,然后输入运算符号【乘号*】,再拖动鼠标选定要参与计算的第2个单元格区域,如下图8所示:

图8

(3)按【Ctrl+Shift+Enter】组合键确认计算,得出数组计算结果,如下图9所示:

图9

注:在单个单元格中使用数组公式计算数据时,单元格不能是合并后的,否则会弹出提示数组公式无效错误;同时:公式输入时的{}是不能手工输入的。

上例完整的操作过程如下面动图10所示:

图10

三、对数组中N个最大值求和

图11

当有多列数据时,在不排序的情况下,需要将这些数据中最大或最小的N个数据进行求和时,就要使用数组公式来实现。例如:如上图11所示,要在多列数据中,对最大的5个数据进行求和运算,具体的操作方法如下:

在C12单元格中输入公式【=SUM(LARGE(B2:C11,ROW(INDIRECT("1:5"))))】,然后按下【Ctrl+Shift+Enter】组合键,即可得出最大的的5个数据的求和结果,如下图12示:

图12

注:在上例操作中,用到的几个函数含义如下:

(1) INDIRECT:取1-5行;

(2) ROW:等到{1,2,3,4,5}数组;

(3) LARGE:求最大的5个数据并组成数组;

(4) SUM:将LARGE求得的数组进行求和。

为了便于理解,还可以将公式简化成【=SUM(LARGE(B2:C11,{1,2,3,4,5}))】。若要对最小的5个数据进行求和和运算,可输入公式:【=SUM(SMALL(B2:C11,ROW(INDIRECT("1:5"))))】或【=SUM(SMALL(B2:C11,{1,2,3,4,5}))】。

我是鉴水鱼老师,关注我,持续分享更多的Excel操作技巧。

10 阅读:930
评论列表

鉴水鱼技能说

简介:感谢大家的关注