利用Excel函数进行多条件求和
我们在实际工作中,可能经常要制作各种各样的Excel统计分析报表,但是这些报表中又有很多是需要根据多个条件进行计数和求和的,这样的问题就是多条件计数与多条件求和。在Excel中,利用相关的函数和公式进行多条件计数与多条件求和有3种方法,利用Excel函数进行多条件求和的方法如下:
·使用SUM函数构建数组公式;
·使用SUMPRODUCT函数构建普通公式;
·使用Excel 2007的新增函数COUNTIFS和SUMIFS。
如果要采用SUM函数或者SUMPRODUCT函数进行多条件计数与多条件求和。都需要在公式中使用条件表达式,这些条件表达式既可以是“与”条件(也就是几个条件必须同时满足)。也可以是“或”条件(也就是几个条件中只要有一个满足即可)。
如果要使用Excel 2007的新增函数COUNTIFS和SUMIFS,那么所有的条件都必须是“与”条件。
以前面几节案例的数据为例。要计算各个大区各个性质店铺的个数及其本月销售数据汇总。其汇总报表结构如图1所示。
图1
下面再介绍几个多条件计数与多条件求和的实际案例。
1、使用SUM函数构建数组公式
首先对原始数据定义名称。
在单元格C2中输入数组公式“=SUM((性质=$A$2)*(大区=$B2))”,并向下复制到单元格C8,得到各个地区的自营店铺数。
在单元格D2中输入数组公式“=SUM((性质=$A$2)*(大区=$B2)*本月指标)”,并向下复制到单元格D8.得到各个地区自营店的本月指标总额。
在单元格E2中输入数组公式“=SUM((性质=$A$2)*(大区=$B2)*实际销售金额)”,并向下复制到单元格E8.得到各个地区自营店的实际销售总额。
在单元格F2中输入数组公式“=SUM((性质=$A$2)*(大区=$B2)*销售成本)”,并向下复制到单元格F8.得到各个地区自营店的销售总成本。
在单元格C9中输入数组公式。=SUM((性质=$A$9)*(大区=$B9))”,并向下复制到单元格C15.得到各个地区的加盟店铺数。
在单元格D9中输入数组公式“=SUM((性质=$A$9)*(大区=$B9)*本月指标)”。并向下复制到单元格D15.得到各个地区加盟店的本月指标总额。
在单元格E9中输入数组公式“=SUM((性质=$A$9)*(大区=$B9)*实际销售金额)”。并向下复制到单元格E15.得到各个地区加盟店的实际销售总额。
在单元格F9中输入数组公式“=SUM((性质=$A$9)*(大区=$B9)*销售成本)”。并向下复制到单元格F15.得到各个地区加盟店的销售总成本。
最终结果如图2所示。
图2
2、使用SUMPRODUCT函数构建普通公式
前面介绍的是利用SUM函数构建数组公式,因此在输入每个公式后必须按【Ctrl+Shift+Enter】组合键。很多初次使用数组公式的用户往往会忘记按这3个键。导致得不到正确的结果。
其实。还可以使用SUMPRODUCT函数构建普通的计算公式,因为SUMPRODUCT函数就是针对数组进行求和运算的。
此时。相关单元格的计算公式如下:
单元格C2:=SUMPRODUCT((性质=$A$2)*(大区=$B2));
单元格D2:=SUMPRODUCT((性质=$A$2)*(大区=$B2)*本月指标);
单元格E2:=SUMPRODUCT((性质=$A$2)*(大区=$B2)*实际销售金额);
单元格F2:=SUMPRODUCT((性质=$A$2)*(大区=$B2)*销售成本);
单元格C9:=SUMPRODUCT((性质=$A$9)*(大区=$B9));
单元格D9:=SUMPRODUCT((性质=$A$9)*(大区=$B9)*本月指标):
单元格E9:=SUMPRODUCT((性质=$A$9)*(大区=$B9)*实际销售金额):
单元格F9:=SUMPRODUCT((性质=$A$9)*(大区=$B9)*销售成本)。
3、使用Excel 2007的新增函数COUNTIFS和SUMIFS
由于本案例的多条件计数与多条件求和的条件是“与”条件。因此在Excel 2007中也可直接
使用新增函数COUNTIFS和SUMIFS。此时,有关单元格的计算公式如下:
单元格C2:=COUNTIFS(性质,$A$2,大区,$B2);
单元格D2:=SUMIFS(本月指标,性质,$A$2,大区,$B2);
单元格E2:=SUMIFS(实际销售金额,性质,$A$2,大区,$B2);
单元格F2:=SUMIFS(销售成本,性质,$A$2.大区。$B2);
单元格C9:=COUNTIFS(性质。$A$9.大区。$B9);
单元格D9:=SUMIFS(本月指标,性质。$A$9,大区。$B9):
单元格E9:=SUMIFS(实际销售金额,性质。$A$9.大区。$B9);
单元格F9:=SUMIFS(销售成本。性质。$A$9.大区,$B9)。
今天我们先学习了一些Excel简单的函数求和运算,包括Excel2007增加的几个函数运算方法,利用Excel函数进行多条件求和的方法我们一共学习了3种,也给大家列举了全部的求和公式。
数据分析咨询请扫描二维码
CDA数据分析师在中国航信高科技产业园进行了面向测试度量的数据分析培训课程,培训人数近2 ...
2024-05-01CDA数据分析师走进深圳迈瑞生物医疗电子股份有限公司,在迈瑞总部展开了为期两天的培训,本次课程参训人员线上及线下近百人, ...
2024-05-01CDA数据分析师在合肥市对合肥阳光新能源科技有限公司开展了为期8天的企业内训。 合肥阳光新能源科技 ...
2024-05-01CDA数据分析师走进海尔大学,进行了《数据治理与数据中台建设的道与术》专题培训,培训现场爆满,近百人参加了此次培训。 ...
2024-05-01在中国银行苏州分行培训中心开始数据分析师培训,此次培训课程共10天内容,包括Excel、MySQL、概率论与数理统计、SPSS等内容, ...
2024-05-01从实际的业务需求出发,结合行业的典型应用特点,围绕实际的商业问题,探讨数据挖掘、机器学习模型在金融领域的应用,包括获客、信用评分、细分画像、交叉销售、反欺诈、违规识别、时序预测、运筹优化、流程挖掘九个方面,形成 ...
2024-05-01本次培训课程为线上+线下的模式,由于学员编程能力不一、部分学员没有编程基础,故提供统计学、python基 ...
2024-05-01华夏银行信用卡中心-机器学习培训 1、课程亮点 取材于业界一流企业和顶级咨询公司的行业实践;已经被证明是人人 ...
2024-05-01主 题:数据中台建设及数据分析应用主题分享 1. 数据中台市场洞察 2. 主流数据中台产品比较 3. 某企业数据中 ...
2024-05-01围绕“数据驱动”战略,全力打造我行 300 人数字化人才梯队,着力培养数字化管理人才、大数据专业团队 ...
2024-05-01在当今数据驱动的商业环境中,数据分析成为了企业决策的重要依据。通过对大量数据的收集、处理和分析,企业能够更好地理解市场 ...
2024-04-29在人工智能(AI)的世界里,提示词(Prompt)是一种强大的工具,它能够引导AI按照用户的需求产生特定的输出。本文将深入探讨AI ...
2024-04-29CDA立足未来职场,拓展前沿视野——对外经贸大学保险学院举办“三全育人大讲堂”分享行业最新动态。 ...
2024-04-294月2日,CDA数据分析师创始发起人兼协会理事长赵坚毅博士受邀在浙江万里学院举办了一场以“数字化能力在职场中的作用” ...
2024-04-29随机森林(Random Forests)现在机器学习中比较火的一个算法,是一种基于Bagging的集成学习方法,能够很好地处理分类和回归的问 ...
2022-12-23方差分析是数据分析中常用的一种统计分析方法,接下来让我们简单了解一下方差分析的基本思想和原理吧。 方差分析(Analysis ...
2022-12-23来源:关于数据分析与可视化 关于streamlit-aggrid 数据排序 表格样式的调整 数据 ...
2022-08-03作者:麦叔 定义 「把上面晦涩的概念汇成一句话就是:」 ❝ 回调函数就是一个被作为参 ...
2022-08-03现今,高学历人群日益增多,物以稀为贵的高学历光环淡去。无论本科生还是研究生,甚至博士生,求职竞争力都大不如前,就业压力越来越大。
2022-06-01某家企业10个人面试,有9个本科生……如何脱颖而出,除得体的举止和良好的沟通力外,证书成重要筹码,这也是很多人考证的关键所在。
2022-04-14