小伙伴们好啊，今天老祝和大家分享一组常用的函数公式知识，一起来学习一下Excel中的求和计算。

SUM函数

这个不用我多说了吧，最常用的求和函数。

如下图所示，是某单位食堂的采购记录表，使用以下公式，即可计算所有物品的数量总和。

=SUM(D2:D14)

SUMIF函数

如果要按指定条件求和，那就要请出SUMIF函数了。

这个函数的用法是：

=SUMIF(条件区域,指定的条件,求和区域)

如下图所示，要计算职工食堂的物资采购总量，公式为：

=SUMIF(B2:B14,H3,D2:D14)

公式的意思是，如果B2:B14单元格区域中等于H3指定的部门“职工食堂”，就对D2:D14单元格区域对应的数值进行求和。

SUMIFS函数

SUMIFS函数的作用是多条件求和。这个函数的用法是：

=SUMIFS(求和区域,条件区域1,指定的条件1,条件区域2,指定的条件2,……)

之一参数指定要求和的区域，后面是一一对应的条件区域和指定条件，多个条件之间是同时符合的意思，可以根据需要，最多写127对区域/条件。

如下图所示，要计算部门是职工食堂，单价在1元以下的物质采购总量。

公式为：

=SUMIFS(D2:D14,B2:B14,H3,E2:E14,I3)

公式的意思是，如果B2:B14单元格区域中等于H3指定的部门“职工食堂”，并且E2:E14单元格区域中等于指定的条件"<1",就对D列对应的数值求和。

SUMIF或是SUMIFS的判断条件除了引用单元格中的内容，也可以直接写在公式中：

=SUMIFS(D2:D14,B2:B14,"职工食堂",E2:E14,"<1")

SUMPRODUCT函数

该函数作用是将数组间对应的元素相乘，并返回乘积之和。

如下图所示，要计算采购所有物资的总金额，公式为：

=SUMPRODUCT(D2:D14,E2:E14)

公式中，将D2:D14的数量和E2:E14的单价分别对应相乘，然后将乘积求和，得到最终所有物资的采购总金额。

使用SUMPRODUCT函数，还可以计算指定条件的乘积。

如下图所示，要分别计算职工食堂和领导餐厅的物资采购金额。公式为：

=SUMPRODUCT((B$2:B$14=G2)*1,D$2:D$14,E$2:E$14)

公式先使用B$2:B$14=G2，依次判断B列的部门是不是等于G2单元格指定的部门，得到一组由逻辑值TRUE和FALSE构成的内存数组，然后将这一组逻辑值乘以1，逻辑值TRUE乘1，结果是1，逻辑值FALSE乘1，结果是0。

最后，将三个数组的元素对应相乘后，再计算出乘积之和。

SU *** OTAL函数

仅对可见单元格汇总计算，能够计算在筛选状态下的求和。

如下图，对B列的部门进行了筛选，使用以下公式可以计算出筛选后的数量之和。

=SU *** OTAL(9,D2:D14)

SU *** OTAL之一参数用于指定汇总方式，可以是1~11的数值，通过指定不同的之一参数，可以实现平均值、求和、更大、最小、计数等多种计算方式。

如果之一参数使用101~111，还可以忽略手工隐藏行的数据，小伙伴们有空可以试试。

AGGREGATE函数

和SU *** OTAL函数功能类似，功能比SU *** OTAL更多，之一参数可以使用1到19的数值，来指定19种不同的汇总方式。第二参数使用1到7，来指定忽略哪些内容。

如下图所示，已经对B列的部门进行了筛选，而且F列的金额计算结果有错误值，使用以下公式，可以对F列的金额进行汇总。

=AGGREGATE(9,7,F2:F14)

AGGREGATE函数之一参数使用9，表示汇总方式为求和，第二参数使用7，表示忽略隐藏行和错误值。

以上咱们学习了Excel中的求和计算，今天的内容就是这些吧，祝各位小伙伴一天好心情！

图文 *** ：祝洪忠

转自EXCEL不加班

今天没有废话，直接进入主题。

收入登记表：手工录入。

支出登记表：手工录入。

利润表：根据收入、支出录入表动态统计，并生成图表。

01 利润表公式设置。

B4输入公式下拉。

=SUMPRODUCT((TEXT(收入录入表!$B$3:$B$5000,"m月")=A4)*收入录入表!$E$3:$E$5000)

C4输入公式下拉。

=SUMPRODUCT((TEXT(支出录入表!$B$3:$B$5000,"m月")=A4)*支出录入表!$D$3:$D$5000)

D4输入公式下拉。

=B4-C4

录入表是具体日期需要用TEXT函数转换成月份，"m月"就是转换成月份的意思。SUMPRODUCT函数条件求和语法说明：

=SUMPRODUCT((条件区域=条件)*求和区域)

02 生成利润图。

Step 01 选择区域，插入推荐的图表（新版本功能）→所有图表→组合图，确定。

低版本没有这个功能，操作比较繁琐，有条件的话还是建议换成Excel2016。

Step 02 直接生成的图表，有部分月份利润为负数，这样挡住月份的坐标轴，需要设置坐标轴标签位置为低。

Step 03 对图表进行美化处理。

小伙伴们好啊，今天老祝和大家一起来认识函数中的大哥大——OFFSET。

从复杂的数据汇总到高级动态图表，都离不开OFFSET函数。但是这个函数的参数多，变化性强，要想灵活驾驭可不是件容易的事。

1、函数作用：

用于生成数据区域的引用，再将这个引用作为半成品，作为动态图表的数据源、或是作为其他函数的参数，进行二次加工。

2、函数用法：

=OFFSET(基点,偏移的行数,偏移的列数,<新引用的行数>,<新引用的列数>)

第二参数使用正数时，表示从基地向下偏移，负数表示向上偏移。

第三参数使用正数时，表示向右偏移，使用负数时表示向左偏移。

第四和第五参数是可选的，如果省略这两个参数，新引用的区域就是和基点一样的大小。

咱们用下面这个公式，来理解一下OFFSET函数的计算过程：

=OFFSET(C3,4,2,4,3)

以C3为基点，向下偏移4行，向右偏移2列，新引用的行数是4行，新引用的列数是3列，最终得到对E7:G10单元格区域的引用。

咱们换一种更形象的说法：

OFFSET函数就像是一个敌人的小分队，从据点董家庄（C3）出动，顺着大路向南走4里（C7）

拐弯儿再向东2里，这时候就到马家河子（E7）了

敌人队长说了，我要以马家河子（E7）这个地方开始，再占领一片地盘。有多大呢？向南4里，向东3里。

吆西，结果就是E7:G10单元格区域了。

3、常用姿势解锁：

1）行列转置

如下图，要将A2:D7单元格中多行多列的姓名，转换到一列中。

F2单元格公式为：

=OFFSET($A$2,(ROW(A1)-1)/4,MOD(ROW(A1)-1,4))&""

OFFSET函数的基点为A2。

向下偏移的行数为(ROW(A1)-1)/4，这部分公式下拉时，可以得到从0开始，按0.25递增的序号，即0，0.25，0.5，0.75，1，1.25，1.5……OFFSET函数对带有小数的参数自动向下取整，向下偏移的行数依次为0 0 0 0 1 1 1 1 2 2 2 2……也就是公式每下拉四行，就从数据源中向下偏移一行。

向右偏移的列数为MOD(ROW(A1)-1,4)，这部分公式下拉时，可以得到0 1 2 3 0 1 2 3……的循环序列序列，也就是公式每下拉一个行，就从数据源向右偏移一列，下拉到第五行时，偏移的列数又会从0开始。

偏移行数和偏移列数二者结合，最终形成1 2 3 4 2 2 3 4 3 2 3 4 ……这样的偏移方式。

2）计算指定区间的销售额

如下图所示，要计算从1月份到指定月份的累计销售额。

F4单元格公式为：

=SUM(OFFSET(B2,0,0,MATCH(F2,A2:A13,0)))

MATCH(F2,A2:A13,0)部分，先使用MATCH函数计算出F2单元格中的月份在A2:A13中的位置，结果为9.

OFFSET函数以B2单元格为基点，向下偏移0行，向右偏移0列，以MATCH函数的计算结果作为新引用的行数，最终得到B2:B10单元格区域的引用，再使用SUM函数计算这个区域中的总和，得到从1月份到指定月份的销售总额。

3）计算最近7天的平均销量

如下图所示，A列和B列是销售流水记录，要计算出最近7天的平均销量。

F4单元格公式为：

=AVERAGE(OFFSET(B1,COUNT(B:B),0,-7))

先使用COUNT函数，统计出B列的数值个数。

OFFSET函数以B1为基点，以COUNT的结果作为向下偏移的行数，也就是B列有多少个数值，就向下偏移多少行。

这时候就相当于到了B列数值的最后一行，给定的偏移列数是0，新引用的行数是-7，得到从B列数值的最后一行开始，再向上7行这样一个动态的区域。

如果B列的数值增加了，COUNT函数的计数结果就增加了，OFFSET函数的行偏移参数也就随之变化。

就相当于一竿子捅到底，然后来个烧鸡大窝脖儿，向上引用7行，所以得到的始终是最后7行的引用。

最后使用AVERAGE函数计算出这个引用区域中的平均值。

4）计算筛选后的商品总价

如下图，是各食堂的采购记录，需要计算筛选后的商品总价。

G1单元格公式为：

=SUMPRODUCT(SU *** OTAL(3,OFFSET(A1,ROW(1:9),0))*C2:C10*D2:D10)

要计算筛选后的内容，首先需要判断单元格是不是处于显示状态。

先来看OFFSET(A1,ROW(1:9),0)部分，OFFSET函数以A1单元格为基点，向下偏移的行数是ROW(1:9)的计算结果，表示依次向下偏移1~9行，最终得到9个引用区域，每个单元格区域由一个单元格构成。

这里涉及到多维引用的知识点了，小伙伴们如果犯迷糊，可以先收藏一下。

接下来使用SU *** OTAL函数对OFFSET函数得到的多个引用区域进行处理，之一参数使用3，表示使用COUNTA函数的计算规则，即依次统计A2~A9这九个单元格区域中的不为空的单元格个数。

如果单元格处于显示状态，则对这个单元格的统计结果为1，否则统计结果为0。

这部分公式得到类似下面的效果：

{0;0;0;0;0;1;1;1;1}

再用SU *** OTAL函数的结果乘以C列的单价和D列的数量，如果单元格处于显示状态，则相当于1*数量*单价，否则相当于0*数量*单价。

最后使用SUMPRODUCT函数对乘积进行求和，这样就得到筛选后的商品总价了。

除了以上常规的用法，OFFSET函数还经常用于动态图表的 *** ，这些内容咱们以后继续分享。

好了，今天的内容就是这些吧，后半部分对于大多数小伙伴可能有点难了，不过不用担心，咱们可以先收藏，然后慢慢消化。

祝各位小伙伴一周好心情！

图文 *** ：祝洪忠

对于管理费用你的脑海里之一反应是什么？招待、餐饮、差旅、办公、折旧，再多就想不起来什么了吧！管理费用的核算内容多而且杂，会计应结合自己所在企业的实际业务需求去设置相关科目。

1、管理费用—工资—基本工资/加班费/临时工工资

说明：本二级科目包含三个三级科目，其中“工资”是二级科目，在这个二级科目之下又设有三个三级科目“基本工资”，“加班费”，“临时工工资”。这里的“基本工资”是指公司管理部门的正式职工应得的工资扣除“加班费”之外的全部工资。

2、管理费用—职工福利费—福利费/医疗补助

说明：本二级科目包含两个三级科目，“福利费”包括管理部员工工作餐，医疗用品，公司组织职工体检费，工伤医疗费，注射疫苗费，医疗室药品费，工作人员租房费，液化气，餐厅用厨具，司机保安餐费补助及夜班补助，厨师工资，职工慰问金，体育用品等。另外，它还包括每月公司按一定比例计职工福利基金，?“医疗补助”指公司和个人按一定工资比例交纳的一种医疗基金。

3、管理费用—折旧费

说明：本二级科目是指管理部使用的固定资产每月所计提的折旧。

4、管理费用—修理费

说明：本二级科目包含电脑，空调，打印机，复印机，传真机等的修理安装费，硬件升级费，办公楼和宿舍装修费，其他管理部办公用品移动和安装费等。

5、管理费用—中介费/ *** 费

说明：本二级科目包含人事档案 *** 费， *** 中介费。

6、管理费用—办公费—书报费/印刷费/日常办公用品费/消耗用品费/年检/审计费/其他

说明：本二级科目包含六个三级科目，其中“书报费”指管理部门购书，订报刊杂志的费用，“印刷费/复印费”指印名片、劳动合同、公司内部报纸等，“日常办公用品”指管理部门每月按预算标准购买的办公用品及为新员工购买的小件办公品，以及传真机、打印机、复印机用色带、墨盒、墨粉、复印纸等，“消耗用品费”主要指人事总务部购咖啡，茶叶，纸杯，纯净水，矿泉水，纸巾以及洗手间用的洗手液，消毒液，手纸等，“年检/审计费”指企业参加工商联合年费，企业变更费，企业验资审计费等。“其他”包括财务部购发票费，财务报表，财务帐本和封皮，以及复印费等。

7、管理费用—物料消耗

说明：本二级科目包括购买硬盘、光盘、软盘等电脑用品，以及插座等维修零件，其他扣除“办公费—日常办公用品费”和“低值易耗品”外的办公用品。

8、管理费用—低值易耗品摊销

说明：本二级科目是指月底时将用于管理部的“低值易耗品”结转费用

9、管理费用—无形资产摊销

说明：本二级科目指月底对公司所拥有的无形资产分期摊销，结转费用。

10、管理费用—开办费摊销

说明：本二级科目是对公司在筹建期间所发生的开办费的摊销。

11、管理费用—租赁费

说明：本二级科目包括食堂房租，会议室租赁费，职工宿舍房租，其他的管理部门使用场地时发生的场地费用。

12、管理费用—运输费

说明：本二级科目不是很常用，仅指管理部门偶尔发生的通过运输公司的运费。其他的如EMS费应计入“邮电费”。

13、管理费用—邮电费—快递费/上网费

说明：本二级科目又分两个三级科目，其中“快递费”主要指管理部门日常发快件的费用(例如EMS)，”上网费“指Internet使用费，LGE-NET使用费。

14、管理费用— *** 费—固定 *** 费/手机费

说明：本二级科目含两个三级科目，其中“固定 *** 费”指管理部门办公室有线 *** 使用费，“手机费”指管理部门移动 *** 使用费。

15、管理费用—研究开发费—工资/加班费/办公费/教育培训费/修理费/招待费/活动经费/市内交通费/差旅费/其他

说明：本二级科目设置的目的是对研究所发生费用的统计，下面对这些科目作一个说明：“工资”含研究所中外职工的日常工资，年终双薪等;“加班费”即研究所职工的加班费;“办公费”指研究所发生的邮寄费，复印费，传真费，冲扩费，购书费及其他杂费;“教育培训费”是研究所职工教育培训所发生的费用;“修理费”指研究所发生的修理电脑费，购硬盘，数据线等费用。“招待费”即研究所招待客户的餐费;“活动经费”是研究所组织活动所发生的费用;“市内交通费”是研究所职工市内办公所发生的费用;差旅费指研究所职工国内外出差所发生的长途机、车、船票费和出差补助;“其他”即研究所发生的除以上各项外的费用。

16、管理费用—技术 *** 费

说明：本二级科目是财务部根据公司与其他公司 *** 技术合同所计提的一种费用。

17、管理费用—技术研究开发费

说明：本二级科目阐述公司依据一定的比例为新产品研究开发计提的经费。

18、管理费用—技术提成

说明：本二级科目用于公司与其他公司技术合作所发生的按一定比例计提的基金。

19、管理费用—待业保险费

说明：本二级科目阐述的是公司和个人按一定比例交纳的社会保险的一种。

20、管理费用—劳动保险费

说明：本二级科目阐述的是公司和个人依据一定比例交纳的社会保险的一种,包括退休人员工资。

21、管理费用—工会经费

说明：本二级科目指的是公司和个人按一定工资比例交纳的工会费。

22、管理费用—住房公积金

说明：本二级科目指公司和个人按一定工资比例交纳的购房基金。

23、管理费用—会议费

说明：本二级科目指企业加入 *** 或社会某协会的会费和活动费。

24、管理费用—职工教育经费

说明：本二级科目指公司按照一定工资标准计提的公司职工教育基金。

25、管理费用—劳动保护费

说明：本二级科目指公司为职工购买劳保用品所发生的费用。

26、管理费用—董事会费—活动经费/会议费/差旅费

说明：本二级科目包括三个三级科目，“活动经费”指董事会成员组织活动所发生的费用(主要指董事长、总经理、常务副总)，如招待餐费等，“会议费”指公司召开董事会发生的会议费，“差旅费”指董事会成员召开会议期间的住宿费、打车费和补助等。

27、管理费用—培训费—讲师费/资料费/餐费/其他

说明：本二级科目包括四个三级科目，其中“讲师费”指聘请讲师的讲课费，“资料费”指购买培训材料的费用，“餐费”指培训期间公司员工发生的餐费，购食品费用，?“其他”包含培训期间的场地使用费及其他杂费。

28、管理费用—咨询费

说明：本二级科目包含公司聘请律师顾问费、会计税务咨询费及其他信息咨询费用。

29、管理费用—差旅费—国内/国外/培训差旅费/市内交通费

说明：本二级科目含五个三级科目，“国内”指管理部职工国内出差发生的费用，“国外”指管理部职工国外出差发生的费用?“培训差旅费”指管理部员工培训期间发生的住宿费等，“市内交通费”指管理部门市内办公发生的交通费，培训期间发生的市内交通费等。

30、管理费用—水电费

说明：本二级科目指管理部门消耗水电的费用。

31、管理费用—保险费

说明：本二级科目包含公司为手机运输所投的保险费以及车辆保险费等。

32、管理费用—招待费—招待费/活动经费

说明：本二级科目包含两个三级科目，“招待费”指公司管理部对外招待客户发生的餐费，“活动经费”指公司管理部职工内部聚餐发生的费用。

33、管理费用—车辆费—油费/养路费/租车费/修理费/车辆维护用品费/车辆租金/其他

说明：本二级科目包括七个三级科目。“油费”指管理部车辆使用汽油、机油的费用，“养路费”指管理部使用的车辆按国家规定所交的公路维护费，“修理费”指管理部车辆的修理维护费，“车辆维护用品费”指管理部为车辆购买的日常维修用具及轮胎等，“车辆租金”指公司租用车辆的费用，“其他”指管理部车辆过路过桥费、停车费、交通违章罚款以及车辆年审和驾驶员证件审查费用等。

推荐学习：

中小企业，小规模纳税人会计实务真账实操！新手必看！

新成立的公司如何建账？

神奇的电子表格（自动生成资产负债表｜利润表｜现金流量表）

建筑施工企业财务流程及账务处理

马上就有到月底了，需要进行各部门费用的汇总进行报销，像餐补呀、话补呀、固定 *** 什么的都要报销，公司的员工数比较多的话，有什么高效的汇总 *** 来完成这个工作吗？ *** 当然是有的，还不止这一种呢，下面就按效率从低到高来进行实例操作，下图是原始数据，隐藏了姓名列，按部门汇总也用不到姓名列。

一、插入空白行求和

这是大家用的最多的也最普通的 *** 了，在每个部门的最后面插入一个空白行，然后点击求和公式，好处是方便，但部门很多的话就比较慢了，也不方便把部门的求和汇总在一起，需要一直向下翻动才能查看。

二、sumif条件求和

使用sumif函数可以按条件进行求和，但是条件列不能有空白的，也不能有合并单元格的情况，否则无法求和，另外就是需要先输入条件，把条件列出来，像这个表，就要先把各部门名称在边上列出来供sumif函数使用，好处是不论数据量大小，都可以把汇总的结果放在一起，稍微整理下就可以使用了

A列是条件区域，G列是求和区域

公式=SUMIF(A3:A130,K3,G3:G130)

在使用公式的过程中选择求和区域和条件区域时有个小技巧，前面也讲过，就是通过ctrl+shift+向下箭头来实现对区域的快速选择。

最后拖动公式，各个部门的餐补汇总就出来了，并且是在一起显示的。

三、合并计算

数据菜单下面的合并计算工具在解决这类问题时可能是最快速和完美的 *** 了，不用输入任何条件，只需要简单的点击就能汇总出各部门的合计数据出来，但在选择数据时一定要选中标题行，并且同样的不能有合并单元格存在，空白的条件存在，我们来仔细看动图演示。

四、分类汇总

数据菜单下的分类汇总功能也能实现按部门进行求和，不需要输入条件，只需要选中全部的数据，点击分类汇总就可以了，然后再点击隐藏明细数据后，就只显示汇总的值了，但这个不足的地方是在原有的数据上实现的，会影响原来的数据，也不美观。

五、透视表

使用透视表来汇总餐补明细也非常简单，灵活性好，可以根据需要在字段列表中选择需要的字段就可以了，如我们选择部门和金额，也可以选别的字段。

六、从表格的查询

这种 *** 灵活性好，能实现的功能也很强大，但是估计用的人很少，它没有SQL语句灵活，也没有透视表简单直观，仅作为一种 *** 来使用，当然了，还是我们对这个功能用的不熟练。

最后这个动图没法录完，就差个数据上载显示的过程，操作步骤就是这样子。

对于数据的分类汇总， *** 有很多，选择适合自己的 *** 就好