Excel多条件求和的三种方法.docx

Excel多条件求和的三种方法.docx

《Excel多条件求和的三种方法.docx》由会员分享，可在线阅读，更多相关《Excel多条件求和的三种方法.docx（7页珍藏版）》请在冰豆网上搜索。

Excel多条件求和的三种方法

窗体底端

Excel多条件求和的三种方法

2004-10-1916:

09作者：

陈秀峰原创出处：

天极网责任编辑：

Shiny

-

　　图1是一种典型的员工基本情况登记表，现在我们要求统计性别为“男”性、职称为“工程师”的员工的工资总和，可以用下面三种方法来实现。

文章末尾提供.xls文件供大家下载参考。

（图片较大，请拉动滚动条观看）

　　方法一、自动筛选法

　　1、打开登记表，选中数据区域任意一个单元格，执行“数据→筛选→自动筛选”命令，进入“自动筛选”状态（此时，每个列标题右侧出现一个下拉按钮，参见图1）。

　　2、选中J103（此处假定有100名员工）单元格，输入公式：

=SUBTOTAL（9,J3:

J102），用于统计基本工资数据。

　　3、先点击“性别”列右侧的下拉按钮，在随后弹出的下拉列表（如图2）中选择“男”；再点击“职称”列右侧的下拉按钮，在随后弹出的下拉列表（参见图2）中选择“工程师”。

　　符合条件的数据被筛选出来，工资之和出现的J103单元格中（如图3）。

（图片较大，请拉动滚动条观看）

　　方法二、数组公式法

　　打开登记表，选中保存统计结果数值的单元格（如J104），输入公式：

=SUM（（C3:

C102="男"）*（I3:

I102="工程师"）*（J3:

J102）），输入完成后，按下“Ctrl+Shift+Enter”组合键确认公式即可。

　　注意：

这是一个数组公式，输入完成后，不能直接用“Enter”键进行确认，需要用“Ctrl+Shift+Enter”组合键进行确认，确认完成后，公式两端出现一对数组公式标志、一对大括号（{}，如图4）。

（图片较大，请拉动滚动条观看）

方法三、条件求和向导法

　　1、打开登记表，执行“工具加载宏”命令，打开“加载宏”对话框（如图5），选中“条件求和向导”选项，按下“确定”按钮，然后按提示操作加载“条件求和向导”功能。

　　注意：

第一次使用这个功能时，需要加载的，以后就可以直接使用了；在加载这一功能时，需要用到Office的安装盘。

　　2、执行“工具→向导→条件求和”命令，打开“条件求和向导—4步骤之一”对话框（如图6），在“请输入需要进行求和计算的区域……”下面的方框中输入：

$A$2:

$J$102，按下“下一步”按钮。

　　3、在随后打开的“条件求和向导—4步骤之二”对话框（如图7）中，将“求和列”设置为“工资”；将“条件列、运算符、比较值”分别设置为“性别、=、男”，再单击一下“添加条件”按钮；再将“条件列、运算符、比较值”分别设置为“职称、=、工程师”，再按一下“添加条件”按钮。

　　4、按“下一步”按钮，打开“条件求和向导—4步骤之三”对话框（如图8）。

　　5、直接按“下一步”按钮，打开“条件求和向导—4步骤之四”对话框（如图9），在其中的方框中输入用于保存求和结果的单元格（如J105），按下“完成”按钮就完成了。

　　注意：

其实“条件求和向导”也是在相应的单元格中输入了另外一个数组公式（如图10），如果在相应的单元格中直接输入图中所示的数组公式，确认后同样可以达到多条件统计的目的。

.xls文件下载

有朋友提出这么一个要求（好像是在QQ群中提的，具体是哪位我忘了，不好意思哈）：

求一个数中各位数字的最大的一个。

比如2364，最大的数字是6；71200，最大的数字是7。

我用的是下面的数组公式：

{=MAX（MID（A1,ROW（INDIRECT（"1:

"&LEN（A1）））,1）*1）}

数组常量的使用数组公式中还可使用数组常量，但必须自己键入花括号“{}”将数组常量括起来，并且用“，”和“；”分离元素。

其中“，”分离不同列的值，“；”分离不同行的值.

下面介绍两个使用数组公式的例子。

    1有如图所示的工作表，需分别计算各商品的销售额，可利用数组公式来实现。

      单元格F2中的公式为：

{=SUM（IF（A2：

A11=″商品1″，B2：

B11＊C2：

C11，0））}.这个数组公式创建了一个条件求和，若在A2：

A11中出现值“商品1”，则数组公式将B2：

B11和C2：

C11中与其相对应的值相乘并累加，若是其他值则加零。

同时，虽然数组B2：

B11和C2：

C11均在工作表中，但其相乘的数组B2：

B11＊C2：

C11不在工作表中，因此必须使用数组公式。

   2假设要将A1：

A50区域中的所有数值舍入到2位小数位，然后对舍入的数值求和。

很自然地就会想到使用公式：

=ROUND（A1，2）＋ROUND（A2，2）＋…＋ROUND（A50，2）。

    有没有更简捷的算法呢？

有。

因为数组ROUND（A1：

A50，2）并不在工作表中，因此要使用数组的方式输入公式，即：

{=SUM（ROUND（A1：

A50，2））}.

“逻辑函数的非逻辑表现”

例如，求取范围Data中小于0或大于5的数值之和:

正确用法：

{=SUM（IF（（Data<0）+（Data>5）,Data））}

错误用法：

{=SUM（IF（OR（Data<0,Data>5）,Data））}

看了楼上的关于逻辑函数的非逻辑表现,我要注明一句,逻辑函数AND和OR它的意思就是返回一个结果,而不是一个数组,就是利用数组常数并用CRTL+SHIFT+ENTER,也不行,它们只能返回一个值.但是在数组中必须要解决这个问题,如果看贴细心的话,应该发现我在7楼贴子中最后一句话的意思,即用*代替AND+代替OR

在EXCEL的数组公式中ROW函数是一个非常有用的函数现在举个例子来讲一下。

1、返回一列中最后一个数值

{=INDEX（A:

A,MAX（ROW（A1:

A100）*（A1:

A100<>""）））}

在这个公式中用ROW函数返回A1:

A100<>""即A1格到A100中不为空的单元格，它是一组数据，然后用MAX确定最大的一个行号，即最后一格不为空的单元格，然后用INDEX，来返回A1到A100中A列最大行号的那个数据。

2、同理如果要返回一行中最后一个数值则为

{=INDEX（1:

1,MAX（COLUMN（1:

1）*（1:

1<>""）））}

3、下面出一个小题目，如果有兴趣想学数组的可以试一下，返回A列100行中最后一个有数值的行号的公式是什么？

a.对正数求和条件求和（单条件求和）

{=SUM（IF（A1:

A100>0,A1:

A100））}

  相当于  =SUMIF（A1:

A100,">0",A1:

A100）

b.对大于0小于10的数求和（多条件求和）

{=SUM（（A1:

A100>0）*（A1:

A100<10）*（A1:

A100））}

  相当于  =SUMIF（A1:

A100,">0",A1:

A100）-SUMIF（A1:

A100,">=10",A1:

A100）

我想把每列的数字统计起来，重复的不算，如a列1，b列2，c列2，那么在d列得到12，不是122，怎么去掉重复的22。

公式为{=IF（COUNTIF（A1:

C1,0）,0,）&SUBSTITUTE（SUM（IF（COUNTIF（A1:

C1,ROW（$1:

$10））,ROW（$1:

$10））*10^（9-ROW（$1:

$10）））,0,）}

这个公式的意思先从说文本连接符前讲起

IF（COUNTIF（A1:

C1,0）,0,），这段公式的意思是检查A-C中是否有0，如无为空如有则为0

SUBSTITUTE（SUM（IF（COUNTIF（A1:

C1,ROW（$1:

$10））,ROW（$1:

$10））*10^（9-ROW（$1:

$10）））,0,）

这段的意思先从IF（COUNTIF（A1:

C1,ROW（$1:

$10））讲起

它的意思是，从A1-C1中对应1-10中找出是否有相符的数值，如122，对应相符的为12，其余为0。

IF（COUNTIF（A1:

C1,ROW（$1:

$10））,ROW（$1:

$10））的意思是上面求出的数值用IF求出对应从1-10的数组是否为真，以122为例，对应为1，2，FALSE，FALSE后面都为FALSE（假）。

*10^（9-ROW（$1:

$10）），的意思就是上面求出为真的数值对应乘以10的8次方，7次方......

即100000000,20000000,0,0.....

然后用SUM合计得出120000000

最后用SUBSTITUTE,装用SUM求出的数值中的O替换为空.

做出这个公式的大侠非常利害,这种公式可以算是个艺术品了。

^_^

如何将单元格中数字的各位数字相加?

..例....123------1+2+3.....=6

........57-------5+7.......=12

........159------1+5+9.....=15

........60-------6+0.......=6

{=SUM（MID（A1,ROW（INDIRECT（"1:

"&LEN（A1）））,1）*1）}

ROW（INDIRECT（"1:

"&LEN（A1）））

这个函数返回一个连续整数的数组，以1开始，以单元格A1中数值的数字数结束。

例如A1为123，则LEN函数返回3，并且由ROW函数产生数组为：

{1,2,3}

该数组用作MID函数的第二个参数。

这样公式的MID部分就简化为：

=MID（123,{1,2,3},1）*1这个函数生成一个拥有3个元素的数组{1,2,3}

公式就简化为：

SUM（{1,2,3}）  结果为6

由MID函数创建的数组中的值乘以1，是因为MID函数返回一个字串，乘以1转为数值。

如果不乘以1转为数值，也可以VALUE函数将字串转为数值。

即公式也可以改为：

{=SUM（VALUE（MID（A1,ROW（INDIRECT（"1:

"&LEN（A1）））,1）））}

A1是标题：

“姓名”，B1是标题：

“分数”。

A2：

A31是30个不同的姓名

B2：

B31是对应的分数

分数前十名为A类，中十名为B类，后十名为C类

要求在D2：

D4中，分别计算出A，B，C各类前三名的平均分数。

=AVERAGE（LARGE（B$1:

B$31,（ROW（A1）-1&（ROW（A$1:

A$3）））））

=ROUND（AVERAGEA（（LARGE（B$2:

B$31,ROW（A$1:

A$3）+（ROW（）-2）*ROWS（B$2:

B$31）/3）））,1）

=AVERAGEA（LARGE（B$2:

B$31,ROW（A$1:

A$3）+（ROW（）-2&0）*1））

（注：

可编辑下载，若有不当之处，请指正，谢谢!

）