就业好的专业-大怎么读
Excel多条件求和的三种方法
2004-10-19 16:09作者：陈秀峰 原创出处：天极网责任编辑：Shiny
图1是一种典型的员工基本情况登记表，现在我们要求统计性别为“男”性、职称为“工程师”的员工的工资总和，可以用下
面三种方法来实现。文章末 尾提供.xls文件供大家下载参考。
（图片较大，请拉动滚动条观看）
方法一、自动筛选法
1、打开登记表，选中数据区域任意一个单元格，执行“数据→筛
选→自动筛选”命令，进入“自动筛选”状态（此时，每个列标题右侧出
现一个下拉按钮，参见图1）。
2、选中J103（此处假定有100名员工）单元格，输入公式：
=SUBTOTAL( 9,J3:J102)，用于统计基本工资数据。
3、先点击“性别”列右侧的下拉按钮，在随 后弹出的下拉列表（如
图2）中选择“男”；再点击“职称”列右侧的下拉按钮，在随后弹出的下
拉列表（参见图2）中选择“工程师”。
符合条件的数据被筛选出来，工资之和出现的J103单元格中（如
图3）。
（图片较大，请拉动滚动条观看）
方法二、数组公式法
打开登记表，选 中保存统计结果数值的单元格（如J104），输
入公式：=SUM((C3:C102=男工程师，输
入完成后，按下“Ctrl+Shift+Enter”组合键确认公式即可。
注意： 这是一个数组公式，输入完成后，不能直接用“Enter”键进
行确认，需要用“Ctrl+Shif t+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 有如图所示的工作表，需分别计算各商品的销售额，可利用数组公式来实
现。
单元格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）< br>＋…＋ROUND（A50，2）。
有没有更简捷的算法呢？有。因为数组ROUN D（A1：A50，2）并不在工作
表中，因此要使用数组的方式输入公式，即：{=SUM（ROUN D（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(COLUM N(1:1)*(1:1<>
3、下面出一个小题目，如果有兴趣想学数组的可以试一下，返回A列10 0行中
最后一个有数值的行号的公式是什么？
a.对正数求和条件求和(单条件求和)
{=SUM(IF(A1:A100>0,A1:A100))}
相当于 =SUMIF(A1:A100,
b.对大于0小于10的数求和(多条件求和)
{=SUM((A1:A100>0)*(A1:A100<10)*(A1:A100))}
相当
于 =SUMIF(A1:A100,
我想把每列的数字统计起来， 重复的不算，如a列1，b列2，c列2，那么在d
列得到12，不是122，怎么去掉重复的22。
公式为
{=IF(COUNTIF(A1:C1,0),0,)&SUBSTITUTE(SU M(IF(COUNTIF(A1:C1,ROW($$
1:$$10)),ROW($$1:$$10))*1 0^(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，FAL SE，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(
ROW(INDIRECT(
这个函数返回一个连续整数的数组，以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(
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)*ROW
S(B$$2:B$$31)3))),1)
=AVERAGEA(LARGE (B$$2:B$$31,ROW(A$$1:A$$3)+(ROW()-2&0)*1))
形容老师的成语-臣之壮也的之
学医的大学有哪些-123建造师网
河北传媒学院分数线-国家大事要什么心
北京211-2018世界500强排名
面试中的优缺点-励志诗
清远大学城-最好笑的冷笑话
send的过去式-计算机有哪些特点
长春高考补习学校-我的大学规划
本文更新与2020-09-14 19:35，由作者提供，不代表本网站立场，转载请注明出处：https://www.bjmy2z.cn/gaokao/394888.html
-
上一篇：《比身高》说课稿
下一篇：《Excel求和》教案