教程基于Excel 2016

在Excel中做统计求和，相信大家会想到很多办法，函数公式？数据透视表？

今天，院长给大家介绍一种常常让人忽视，但却非常实用的功能：分类汇总。

分类汇总，各位小伙伴基本都能做出第一级，但二级、三级甚至是多级分类汇总怎么做？相信很多小伙伴都会一脸懵逼。

大家好，我是案例：

案例中，我们需要实现先按日期，再按组别对服务人数进行汇总。

1. 先对汇总条件进行排序

使用自定义排序功能，添加排序条件，主要关键字选择【日期】，次序选择【升序】；次要关键字选择【组别】，次序选择【升序】。

2. 按日期为分类做一级汇总

使用菜单【数据】中的【分类汇总】功能，分类字段选择【日期】，选定汇总项勾选【服务人数】。

如下图所示，按日期分类，计算出服务人数的汇总数。

3. 按组别为分类做二级汇总

使用菜单【数据】中的【分类汇总】功能，分类字段选择【组别】，选定汇总项勾选【服务人数】，注意：必须要把【替换当前分类汇总】勾选去掉。

如下图所示，按组别和日期作为分类条件，计算出服务人数的汇总数。

好了，今天的教程就到这里了。从现在开始，你也能通过分类汇总功能，快速做出多级统计求和了。

来源：Excel成长学院， 作者：院长

每日收看会计网（ID：wwwkuaijicom）推送，可学习更多EXCEL技巧！

小K推荐课程：

EXCEL财务技巧零基础到精通【直播班】

从零基础→精通，只需8课时！专属的师生交流群，老师在线解疑，电子版学习资料，还可以在一年内任意回放视频，只要忘记就可以重学，直到你掌握为止！

原价：299

今日推广价：188

点击【阅读原文】直接进入购买

▼

今天给大家传授一项Excel常用快捷功能：一键求和！

说起Excel数据求和，大家一定不陌生，但是Excel里有一项快捷键能够帮你高效求和，并且可以多行多列一键求和，大大提高你的工作效率，下面我们就来看一下吧。

大家看下面的数据表，需对各月和每名员工季度业绩进行求和汇总，此数据表是典型的多行多列求和，你看到的第一反应是怎样的。

你还在手动输入sum公式进行求和吗？在行末尾输入sum公式，然后下拉；在列的末尾输入sum公式，然后向右拖拽。

这样求和方式简直太低效了，那就看一下如何多行多列一键求和吧！

首先选中需要求和的行和列，然后按住“Alt”和“=”，如此行和列的求和结果就一键展示出来了，是不是很神奇呀！

一键求和是不是非常的快速高效并且很简单呢？大家学会了吗？

小编在这里要提醒大家，学会之后一定要在日常工作学习中应用哦，这样才会熟练于心。

好了，今天的Excel小教程就这样了，下次再回！如果对你有帮助，记得收藏转发哦。

我们肯定知道按单条件、多条件，多表求和，唯独有一种求和我们很少听说过，那就是：按—颜—色—求—和。是的没看错，就是an颜色求和，

【例】如下图所示，要求按颜色进行求和。

Excel中还没提供按颜色求和的函数，但我们可以借用宏表函数（老版本遗留的一种函数，只能在定义名称中使用）获取单元格的颜色值，然后再用sumif函数求和来解决我们的问题。

一、定义名称，为提取颜色值做准备

公式 - 定义名称 ，在定义名称窗口中输入名称 mycolor（可以自定义），然后在下面引用来源中输入公式：=GET.CELL(63,Sheet2!B2)&T(NOW())

公式说明：

Get.cell(63,单元格) 可以获取单元格填充颜色值&T(NOW()) 实现表格在更新时定义名称取值也可以更新

二、获取颜色值

在C列和F列分别输入公式 =mycolor，即可获取B列和D列的单元格填充色。

三、剩下的交给sumif函数吧

单元格内输入 =SUMIF(C:C,E2,B:B)

按颜色求和对高手并不难，但对于新手可能还要用多了才能记住，所以建议大家先收藏起来备用吧。

Hi，大家好，我是胖斯基

周末闲暇，整理了下那铺满灰尘的书架。

透过尘埃，在故纸堆中发现这么一本连环画图册，脑海中瞬间回忆起那真假美猴王的剧幕，那一幕幕，完美的演绎了真亦假时假亦真，无为有处有还无……

其实，在Excel中，这种真真假假也同样存在，也一样带来了求和困扰！

求和，在Excel中很简单，相信小白都可以脱口而出，直接函数SUM即可，然后有时候，事实并非如此：

当采用最傻瓜的方式直接逐个相加时，发现结果正确；

但是当采用SUM求和时，结果却错误。

不明其中缘由的你，可能会非常困扰，明明就这几个数相加，怎么就不对了呢？

其源头就是“真假数据”，在Excel中的专业叫法是文本型数据（不参与直接运算）。

So，当这类“假数据”参与SUM时，自然不进行正确的求和运算。

1

那如何判定文本型的数值参与了运算？

技巧1：观察法

如果是文本型的数据，则在其单元格的左上角，会有一个三角符号提示

相信只要视力没问题，基本可以快速排查出所谓的“假数据”

当然，更BT的一种就是其单元格上没有小三角符合，那如何快速判定这一系列数据是否有问题呢？

依旧采用观察法：

选中这一列数据，然后观察Excel的右下方，会有文字提示：计数：6，数值计数：5，很明显，这其中有1个数字为“假数据”，So，找到Ta，解决Ta即可！

那如何从茫茫人海中找到这个“假数据”呢？

技巧2 ： 函数判断法

在Excel中，判断一个数字是否是真正的数值，有一个函数ISNUMBER，当其结果为TRUE的时候，其为真数字，当其结果为FALES时，其为假数字（文本型数字）

从函数检测结果来看，很容易就分辨出了那些数据有问题

发现问题很重要，解决问题更重要

2

如何解决文本型数值参与的计算？

技巧3 ： 文本转数值法

如果单元格里面的数值已经明确判定为文本型的“假数据”了，那可以采用文本转数值的模式，如下：

选中数据列，在右侧的提示框中选择转化为数值即可

技巧4 ： 公式计算法

当然，如果单元格里面的数据是从其他表，通过公式引入过来的，又该如何处理呢？

无论单元格里面公式是什么，在其后乘以1（*1）即可，通过数据运算让文本型转化为数值型数据

当然，也许发懒的你，什么都不想改，想直接在求和中自动计算，又该如何处理呢？

公式1：=SUM(1*(A2:A7)) ， 输入公式后，三键一起按（数组公式）

公式2：=SUMPRODUCT(1*(A2:A7))

其核心内涵，还是采用数据乘以1的方式，将文本转化为数值。

胖斯基|说

无论文本型数据也好，还是数值型数据也好，当掌握了上面4个小技巧时，真真假假，假假真真，便不那么重要了。

这种起止期，数据透视表即使通过步长的日区间分析也很难完成，除非用SQL进行数据查询，但是又局限于太难，很多人都完成不了，这样如何去实现呢，可以通过SUMPRODUCT函数多条件实现指定日期区间求和，话不多说，直接上函数：

=SUMPRODUCT((源数据日期>=起止期A)*1,(源数据日期<=起止期B)*1,(源数据单位=指定的单位)*1,(金额))

根据我的表格写的公式如下：

=SUMPRODUCT((入库!A:A>=B3)*1,(入库!A:A<=C3)*1,(入库!C:C=Sheet1!$A$3)*1,(入库!I:I))

SUM这个函数您一定经常用，有没有遇到一种情况，当我们插入新的行或列进去后，公式求得和没有变化，这种稍不注意，所有的数据就都算错了

今天我们分享下Excel高大上的自动汇总求和，插入新的数值数据会自动更改

1、选中区域→插入→表格，选定对应我们要建的区域后点击确定即可

2、点击我们的新表→表格工具→设计→汇总行，直接就求出了汇总和

接下来，我们新增数据，数值自动变化，同时，可以看到此处按钮处有个三角按钮，可以看到还可以求最大值、最小值、平均值、计数等等，具体操作如下：

正常我们平时筛选就是直接选择筛选，但是插入切片器瞬间让我们的表格高大上

点击我们的新表→表格工具→设计→插入切片器→选择筛选条件，可以单选也可多选→调整列数和宽高，操作步骤如下

当然，如果我们在此表的基础上插入图形，就实现了动态数据图

欢迎收藏和分享给更多人，如若要原始数据源，可留言

比如下面这个表格，有2016年和2017年的进货日期，只有数量和单价，没有各项目的小计。如果要筛选出2017年来项目形成一张报表来进行求和，直接使用SUMPRODUCT是不能成功的。有人说可以选择区域啊，那么如果筛选出2016的不是又要更改求和区域吗？其实EXCEL提供了一个处理筛选的函数。请看分析。

数据源示例

首先：利用SUBTOTAL参数102，对隐藏单元格进行计数。SUBTOTAL(102,OFFSET(A1,ROW(1:13),))，得到一个{0;0;0;0;0;0;1;1;1;1;1;1;0}的内存数组，从这里就可以看到，当所在行筛选隐藏后，函数返回结果为0，而显示的行则会返回1，然后就可以利用SUMPRODUCT一一对应乘积求和了。

完整公式：SUMPRODUCT(SUBTOTAL(102,OFFSET(A1,ROW(1:13),)),C2:C14,D2:D14)

效果示例

（辅助列是本例效果示例，并没有采用辅助列求和）

问题情景

很多电商、工资等数据统计的时候都会遇到这样的问题：数据以不同的部门，或者不同的月份，也或不同的年份等情况分布在不同的工作表里，经常要求对分表根据相同数据项对数据进行汇总统计。

示例数据如下图：

共12月的数据，无需合并，直接汇总到“汇总”工作表“年销售业绩”列中。

注意：各分表的结构一致，姓名都在B列，求和的数据都在C列。

关键操作第一种情况：所有表“姓名”排序一致

这种求和方法很简单，只要在“汇总”表C2单元格输入公式“=SUM('*'!C2)”就可以了，回车以后，公式自动变成：=SUM('1月:12月'!C2)。

第二种情况：所有表“姓名”排序不一致

这种情况下求和稍有麻烦，我们一步一步分析：

第一步：

如果是仅对一个表，只需要Sumif函数直接求和即可，比如：

=SUMIF('1月'!$B$2:$B$37,汇总!B2,'1月'!$C$2:$C$37)；

第二步：

现在是对12个表汇总，需借助INDIRECT函数生成对多个表区域的引用，即是：

INDIRECT(ROW($1:$12)&"月!$B$2:$B$37")--引用每个表的B列姓名数据区域

以及

INDIRECT(ROW($1:$12)&"月!$C$2:$C$37")--引用每个表的C列销量数据区域。

第三步：

写入Sumif函数，即是：

SUMIF(INDIRECT(ROW($1:$12)&"月!$B$2:$B$37"),汇总!B2,INDIRECT(ROW($1:$12)&"月!$c$2:$c$37"))

第四步：

INDIRECT函数引用的是一组数，数组求和，最后最外层加Sumproduct函数，即是：

=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$12)&"月!$B$2:$B$37"),汇总!B2,INDIRECT(ROW($1:$12)&"月!$c$2:$c$37")))。

结果如下：

另：

本例使用INDIRECT函数产生多维引用，其中：

使用ROW($1:$12)产生月份序号数组：

{1;2;3;4;5;6;7;8;9;10;11;12}

公式中的INDIRECT(ROW($1:$12)&"月!$B$2:$B$37")作为SUMIF函数的range参数，表示每个月销售记录表的$B$2:$B$37区域，共12个区域：

{"1月!$B$2:$B$37";"2月!$B$2:$B$37";"3月!$B$2:$B$37";"4月!$B$2:$B$37";"5月!$B$2:$B$37";"6月!$B$2:$B$37";"7月!$B$2:$B$37";"8月!$B$2:$B$37";"9月!$B$2:$B$37";"10月!$B$2:$B$37";"11月!$B$2:$B$37";"12月!$B$2:$B$37"}

同理，公式中的INDIRECT(ROW($1:$12)&"月!$c$2:$c$37")表示每个月销售记录表的$c$2:$c$37区域，作为SUMIF函数的sum_range参数。

SUM函数自动求和是Excel中最基础的技能。但是对于不规则的多区域进行自动求和汇总还是有很多朋友无法解决的。

下面的这个案例各位朋友是怎么完成的呢？之前有朋友跟我说是一个个做的，那我想说如果数据多了怎么办呢？

今天通过这个案例就跟各位朋友分享下Excel数据表格中自动求和的高级用法，其实很简单！希望对各位朋友在工作中有做帮助！

上图是某单位每月的项目销售情况统计表表，由于每个项目的所属类别不同所以数据区域也不同。现在我们需要统计下每个项目的小计及总计数据汇总情况。

Excel数据表中自动求和的高级用法——操作步骤

1、小计求和：选中数据区域B3：E14进行条件定位；

2、定位条件弹窗中选择空值；

3、定位条件空值后，进行求和；

4、点击自动求和后，小计部分求和答案马上完成；

5、小计求和完成后，选择总计求和答案的单元格区域，点击自动求和，完成。

Excel数据表格自动求和小计、总计（动态图操作）

Excel自动求和小计、总计动态视频操作

Excel数据表自动求和的这种用法，你学会了吗？上面的操作步骤如果学会了，下面的数据操作也就不难了。数据量大的情况下操作原来也是如此简单（附动态视频操作）

Excel表中数据汇总的这种简单而又高级的自动求和用法，你学会了吗？希望今天的分享对各位朋友在工作中有所帮助！

更多关于Office——Excel操作技巧可加入我们群一起交流学习！159534659

在工作中，有时候excel表格需要每天统计数据，表格的行数会随着每天记录的数据而不断增加。像下表，每天都要记录出库数量，并且每天都要统计出库合计数量。当每天新增记录时，出库合计数量不需要再重新计算，根据新增的记录数据自动求和。

今天的小技巧运用到OFFSET函数和COUNTA函数。

OFFSET函数：以指定的引用为参照系，通过给定的偏移量返回新的引用。

函数语法：OFFSET(reference, rows, cols, [height], [width])

这个函数有5个参数：

第一个参数是基点。

第二个参数是要偏移几行，正数向下，负数向上。

第三个参数是要偏移几列，正数向右，负数向左。

第四个参数是新引用几行。

第五个参数是新引用几列。

第一至第三个参数为必需，第四个和第五个参数为可选。

例如公式：=OFFSET(B2,3,2,3,2)

就是以B2为基点，向下偏移3行，向右偏移2列，新引用的行数是3行，新引用的列数是2列，最终得到对D5:E8区域的引用。和SUM函数组合=SUM(OFFSET(B2,3,2,3,2))就是对D5:E8区域求和。

COUNTA函数：计算区域中不为空的单元格的个数。

接下里求出库数量合计，在B1单元格输入公式：=SUM(OFFSET(C3,0,0,COUNTA(C:C)-1,1))。当我们再次想数据表格中增加数据时，就会自动统计出库数量。