一、分类汇总（使用前先排序）

1、利用分类汇总分类目统计金额

首先对类目名称进行排序，找到【数据-分类汇总】告诉表格：按什么分类，把什么汇总 ，如何汇总。

在右上方有1、2、3字样，分别为总金额、各三级类目金额、明细表，可以很方便的查看。

2、分类汇总的嵌套：分地区与产品分类求金额

关键字排序：【数据-排序】添加主要、次要关键字，顺序无所谓。

嵌套分类汇总：先做一次分类汇总，再做一次。第二次做的时候注意取消：替换当前分类汇总。

3、使用分类汇总批量合并内容相同的单元格

实现方式：把一样的值中间分开，插入某个东西，目的不是要这个数据，而是要把他们分开

操作步骤：先排序，点击【数据-分类汇总】，按所属区域分类，在按照所属区域汇总，按照计数的方式。

具体操作：A2-最合并单元格选中，【开始-查找与替换-定位条件-定位到空值-合并单元格】，这时候分类汇总没用了，全部删除。选中合并的格式，直接格式刷刷。

最终成果：这样能实现的效果就是，取消合并单元格时，所有的单元格中都有以前的数据。

目的：在Excel中规范有效数据范围，是自己设定的规则

1、利用数据有效性规定表格

（1）设置A列仅能输入500~1000的整数

选中A列，点击【数据——数据有效性】,选择允许值

（2）设置B列仅能输入字符长度为8位的产品编码

（3）设置C列付款方式中仅能输入现金、转账、支票。【数据-数据有效性-序列-输入选项是用英文半角逗号隔开】就可以设置下拉框。

2、利用数据图有效性进行单元格保护

【数据有效性-出错警告-错误信息（我昨天熬夜做的，你确认要改吗？）】

经典数据透视表现象设置：插入-数据透视表-右键-数据透视表选项-经典数据透视表布局-显示-勾选经典数据透视表布局

将字段拖拽到相应的区域中：这个方式非常简单，直接拖拽就可以。也可以通过双击左上角更改统计方式：求和、计数等。可以通过双击透视表，看原数据。

问题1：按照所属区域、季度、商品类别金额统计，分季度看不同商品类别在不同区域，不同季度的金额。

数值拖拽：先把区域放到左侧，再把日期分到用一个区域。日期太杂的话需要按照季度显示（在日期上右键，选择“创建组——季度”）;列字段区域放置产品类别，金额放在中间。

问题2：计算数值的区间（可以统计订单区间个数、员工工资区间人数、电话通信时长）

数据拖拽：金额放到行字段，依然是金额的单，放到主区域。在数值本身这一列，右键-创建组【起始于0，终止于32000（好除），步长40000（统计区间）】

问题1：计算不同员工四个角度统计生产数量（生产数量、平均产量、最大产量、最小产量）

拖拽数据：行字段按员工姓名分类，工号放在员工姓名左边。双击员工姓名，把分类汇总改成“无”。

更改统计方法：把生产数量拉过去，再把生产数量拉过去（这时会出现一个问题，多个数值会向下排列，所以要把数据拉倒列字段中，拉四次生产数量），第二个生产数量双击：平均值；第三个改为：最大值；第四个改为：最小值。

可以用这种方法计算销售规格、利润率（对于错误值显示为：空）

4、用数据透视表批量拆成多个sheet

将表头拖拽到数据透视表最上方，再次拖拽到主区域。【分析-选项-显示报表筛选页-确定】，用下面的表格覆盖上面的格式。

最大值：Max（区域）

最小值：Min（区域）

Rank：（排谁，在什么区域排名），绝对引用区域用F4锁定

跳跃式求和（当数字之间有空值隔开时）：选中区域——定位条件——空值——点击求和

跳跃式写公式计算平均值：选中区域-定位条件-空值-在白色框写公式-按住Alt敲回车

1、IF函数的基本用法、嵌套、判断数据区间

函数语法：IF（如果怎样，就怎样，否则就怎样）

（1）如果性别是男，就是先生，否则就是女士

=if（A2=“男”，“先生”，“女士”）

（2）if函数的嵌套：把专业都写上专业代号，分别有理工LG，文科WK，财经CJ

=if（A2=“理工”，“LG”，if（A2=“文科”，“WK”，“CJ”））

（3）如果是本地考生，在原分数+30分，本省+20分，其他+10

（4）if函数对数据区间的判断：600分含500，显示第一批；400-600，含400分，显示第二批；400分以下，落榜。不用写区间，用剥洋葱方式来写。

2、如何回避IF函数的嵌套

如果if嵌套超过5层，可以试试vlookup函数。

=if（A1=“A级”，“一级”，“”）&if（A1=“B级”，“二级”，“”）多层if的并列嵌套可以用连字符链接。

3、用if函数处理运算错误：iserror函数

1、and函数：表示“且”的关系，用and把两个条件包起来

（1）60岁以上的男员工给与1000元奖金

2、or函数：表示“或”的关系

（1）对于60岁以上或40岁以下的员工给与1000元奖金

3、终极挑战：如果一个人让你帮忙写函数，一定不要信他的话，给你5、6个条件，要按照自己的思路来拆解。If函数不是由多少可能性决定，结果才决定你写几个函数，写之前一定要理清楚思路。

（1）对于60岁以上的男员工或40岁以下的女员工给与100元奖金

一、count是专门帮我们数数的，一个区域当中要多少数据，表示计数的概念。

（1）数一下邮寄费、独子费、过桥费、电话费各有多少项。需要告诉函数（在哪数？数什么？）

（2）判断数值区间的个数：数一下每个学生参加了多少门考试，每个学生及格的科目数有几门

（3）有一大组身份证号，里面有重复的，我要用这个函数数出来。数字超过15位，所以要用&“*”

1、有一个全体名单和已体检名单，通知未体检的人体检。这个表格可以在已体检名单中不断增加，会自动变化的。

=if（countif（G：G，A1）=0，“未体检”，“已体检”）

2、用条件格式把未体检的标红，相当于把数出来等于0 的标记成红色

选中名单区域——条件格式——新建规则——使用公式确定设置格式的单元格——输入（=countif（$G:$G，A1）=0）——设置颜色

3、将C列设置为禁止输入重复的值

选中C列——数据有效性——设置——自定义——公式——（=countif（C：C，C1）<2）

4、将D2：I20设置为禁止输入重复数据（绝对引用和相对引用）

三、countifs计算多个条件的计数

（1）算1车间的邮寄费，二车间的独子费，二车间的过桥费

在某列中找出符合某个条件的值，加起来某列的数据。

（1）sumif计算数值区间：把F列中大于500的数加起来

（2）sumif超过15位字符的错误：找一下F3中的银行卡号的金额，A列所有银行卡号、B列所有银行卡号中的金额（涉及到银行卡只能统计前15位，所以一定要链接星号）

=sumif（A:A，F3&“*”，B:B）在某列找某个值，并且把某列对应的值加起来。要求第一参数和第三参数区域一样大。

（3）再多列中使用sumif：数据不规范，有多列，怎么使用sumif？

=sumif（A:I，L3，$B$1）B列、D列、F列都是发生额，但是只把相加字段的名称绝对引用就可以了。B1框的字段为“发生额”。第一和第三个值不要错位。

（4）使用辅助列处理多条件的sumif：求一车间的邮寄费、二车间的独子费、三车间的过桥费。。。。

插入一列——（链接两列 =A1&B1）——【去要算的框中写公式=sumif（A:A辅助列，J1&H1计算列，G：G发生额）】

（5）用sumifs处理多列条件

=sumifs（求和区域F:F，条件区域一D:D，条件一I5，条件区域二H：H，条件二G5）

（6）用sumif做查找引用（数字版的vlookup）

根据姓名查工资=sumif（性名列A:A，李明M4，工资列J:J）

（7）挑战与回顾：用数据有效性和sumif做产品出库单，出库表上的产品数量总计加起来不能大于库存表。数据为：日期、产品、数量。

先设置数据有效性：产品序列建立下拉列表，数量列【设置-自定义-=sumif（产品列F:F，某个产品类别F3，数量列G:G）<sumif(库存产品列A:A,产品类别F3,库存数量列B:B)】

Vlookup（查找引用函数）

Vlookup（用谁去找，查找区域，拿回这个区域第几列数据，匹配方式）

vlookup函数成立的前提是，查找列和区域列的第一行必须完全一致，如果有数据不一致，就会返回错误。所以当这两个表的数据不完全一致时（少几个字或多几个字），我们应该使用通配符。*号代表这个数据源后面可能还有别的东西。=vlookup（A2&“*”，查找区域，返回列，0）

精确匹配，错一点都不行。而模糊匹配，找的是近似值，并且是小于等于自己的最大值。

在工作中可以运用于奖金、绩效等数据的档次划分中，找数值档次。

4、使用isna函数处理数字格式引起的错误

文本和数字是不相等的，数值智能加减乘除，不能连接东西。所以反过来想，如果文本加减乘除数字，就变成了数值。如果数值加连字符连上空的双引号，就可以变成文本。

• 通过数值的V文本的：把要去处理的数值链接一个空的双引号。=vlookup（F2&“”，区域，列，0）
• 如果数据源又有数值，又有文本：=vlookup（F1*1，$精确引用区域，列，0），凡通过数值是没找到的，请你帮我转成文本再找一次。=if（isna（vlookup（F1*1，$精确引用区域，列，0）），vlookup（F1&“”，区域，列，0），vlookup（F1*1，$精确引用区域，列，0））：如果XXXisna，那么XX，否则XX。

5、Hlookup函数（就是当数据是行的时候，返回第几行，和vlookup一样，只不过列变成行）

1、函数语法（两个函数组合起来正好是vlookup）

Match（查找值，查找区域列，0）A1在A列中的第几行？

Index（在哪拿，拿第几个）

=index（引用区域列，match（查找值，查找区域列，0））

3、使用Match与vlookup函数嵌套返回多列结果

首先，考虑一下引用的值，在哪里，未来怎么变？列不要变化，被乘数行不要

4、认识column函数（列）

=column返回当前单元格的列号。

返回多列，要求两张表的顺序是一致的。那么：=vlookup（$D4，绝对引用区域，column（）-3,0）

如果两个表的顺序不一样，怎么返回多列？那么我这样想：表头是第几列就是第几列。

=vlookup（A3，绝对引用区域，match（表头B2，绝对引用表头区域，0），0）

未来要向右向下拖拽：==vlookup（$A3，绝对引用区域，match（B$2，绝对引用表头区域，0），0）