一、分类汇总(使用前先排序)
1、利用分类汇总分类目统计金额
首先对类目名称进行排序,找到【数据-分类汇总】告诉表格:按什么分类,把什么汇总 ,如何汇总。
在右上方有1、2、3字样,分别为总金额、各三级类目金额、明细表,可以很方便的查看。
2、分类汇总的嵌套:分地区与产品分类求金额
关键字排序:【数据-排序】添加主要、次要关键字,顺序无所谓。
嵌套分类汇总:先做一次分类汇总,再做一次。第二次做的时候注意取消:替换当前分类汇总。
3、使用分类汇总批量合并内容相同的单元格
实现方式:把一样的值中间分开,插入某个东西,目的不是要这个数据,而是要把他们分开
操作步骤:先排序,点击【数据-分类汇总】,按所属区域分类,在按照所属区域汇总,按照计数的方式。
具体操作:A2-最合并单元格选中,【开始-查找与替换-定位条件-定位到空值-合并单元格】,这时候分类汇总没用了,全部删除。选中合并的格式,直接格式刷刷。
最终成果:这样能实现的效果就是,取消合并单元格时,所有的单元格中都有以前的数据。
二、数据有效性
目的:在Excel中规范有效数据范围,是自己设定的规则
1、利用数据有效性规定表格
(1)设置A列仅能输入500~1000的整数
选中A列,点击【数据——数据有效性】,选择允许值
(2)设置B列仅能输入字符长度为8位的产品编码
(3)设置C列付款方式中仅能输入现金、转账、支票。【数据-数据有效性-序列-输入选项是用英文半角逗号隔开】就可以设置下拉框。
2、利用数据图有效性进行单元格保护
【数据有效性-出错警告-错误信息(我昨天熬夜做的,你确认要改吗?)】
三、数据透视表
1、创建数据透视表
经典数据透视表现象设置:插入-数据透视表-右键-数据透视表选项-经典数据透视表布局-显示-勾选经典数据透视表布局
将字段拖拽到相应的区域中:这个方式非常简单,直接拖拽就可以。也可以通过双击左上角更改统计方式:求和、计数等。可以通过双击透视表,看原数据。
2、数据透视表的组合
问题1:按照所属区域、季度、商品类别金额统计,分季度看不同商品类别在不同区域,不同季度的金额。
数值拖拽:先把区域放到左侧,再把日期分到用一个区域。日期太杂的话需要按照季度显示(在日期上右键,选择“创建组——季度”);列字段区域放置产品类别,金额放在中间。
问题2:计算数值的区间(可以统计订单区间个数、员工工资区间人数、电话通信时长)
数据拖拽:金额放到行字段,依然是金额的单,放到主区域。在数值本身这一列,右键-创建组【起始于0,终止于32000(好除),步长40000(统计区间)】
3、汇总多列数据
问题1:计算不同员工四个角度统计生产数量(生产数量、平均产量、最大产量、最小产量)
拖拽数据:行字段按员工姓名分类,工号放在员工姓名左边。双击员工姓名,把分类汇总改成“无”。
更改统计方法:把生产数量拉过去,再把生产数量拉过去(这时会出现一个问题,多个数值会向下排列,所以要把数据拉倒列字段中,拉四次生产数量),第二个生产数量双击:平均值;第三个改为:最大值;第四个改为:最小值。
可以用这种方法计算销售规格、利润率(对于错误值显示为:空)
4、用数据透视表批量拆成多个sheet
将表头拖拽到数据透视表最上方,再次拖拽到主区域。【分析-选项-显示报表筛选页-确定】,用下面的表格覆盖上面的格式。
四、函数
加:Sum(区域)
平均:Average(区域)
最大值:Max(区域)
最小值:Min(区域)
Rank:(排谁,在什么区域排名),绝对引用区域用F4锁定
跳跃式求和(当数字之间有空值隔开时):选中区域——定位条件——空值——点击求和
跳跃式写公式计算平均值:选中区域-定位条件-空值-在白色框写公式-按住Alt敲回车
IF函数
1、IF函数的基本用法、嵌套、判断数据区间
函数语法:IF(如果怎样,就怎样,否则就怎样)
(1)如果性别是男,就是先生,否则就是女士
=if(A2=“男”,“先生”,“女士”)
(2)if函数的嵌套:把专业都写上专业代号,分别有理工LG,文科WK,财经CJ
=if(A2=“理工”,“LG”,if(A2=“文科”,“WK”,“CJ”))
(3)如果是本地考生,在原分数+30分,本省+20分,其他+10
=if(A2=“本地”,H2+30,if(A2=“本省”,H2+20,H2+10))
(4)if函数对数据区间的判断:600分含500,显示第一批;400-600,含400分,显示第二批;400分以下,落榜。不用写区间,用剥洋葱方式来写。
=if(A2>=600,“第一批”,if(A2>=400,“第二批”,“落榜”))
2、如何回避IF函数的嵌套
如果if嵌套超过5层,可以试试vlookup函数。
=if(A1=“A级”,“一级”,“”)&if(A1=“B级”,“二级”,“”)多层if的并列嵌套可以用连字符链接。
3、用if函数处理运算错误:iserror函数
=IF(ISERROR(A1+B1),0,A1+B1)
And函数与or函数
1、and函数:表示“且”的关系,用and把两个条件包起来
(1)60岁以上的男员工给与1000元奖金
=if(and(A1=“男”,B1>=60),1000,0)
2、or函数:表示“或”的关系
(1)对于60岁以上或40岁以下的员工给与1000元奖金
=if(or(A1>=60,B2<=40),1000,0)
3、终极挑战:如果一个人让你帮忙写函数,一定不要信他的话,给你5、6个条件,要按照自己的思路来拆解。If函数不是由多少可能性决定,结果才决定你写几个函数,写之前一定要理清楚思路。
(1)对于60岁以上的男员工或40岁以下的女员工给与100元奖金
=if(or(and(A1=“男”,B1>=60),and(A1=“女”,B1<=40)),1000,0)
Countif函数
一、count是专门帮我们数数的,一个区域当中要多少数据,表示计数的概念。
(1)数一下邮寄费、独子费、过桥费、电话费各有多少项。需要告诉函数(在哪数?数什么?)
=countif(A:A,H1) 在A列数H1有几个,下拉
(2)判断数值区间的个数:数一下每个学生参加了多少门考试,每个学生及格的科目数有几门
=count(B2:G2)
=countif(B2:G2,“>=60”)
(3)有一大组身份证号,里面有重复的,我要用这个函数数出来。数字超过15位,所以要用&“*”
=countif($A1:A100,A1&“*”)
二、countif的应用实例
1、有一个全体名单和已体检名单,通知未体检的人体检。这个表格可以在已体检名单中不断增加,会自动变化的。
=countif(G:G,A1)如果是0就是没体检
=if(countif(G:G,A1)=0,“未体检”,“已体检”)
2、用条件格式把未体检的标红,相当于把数出来等于0 的标记成红色
选中名单区域——条件格式——新建规则——使用公式确定设置格式的单元格——输入(=countif($G:$G,A1)=0)——设置颜色
3、将C列设置为禁止输入重复的值
选中C列——数据有效性——设置——自定义——公式——(=countif(C:C,C1)<2)
4、将D2:I20设置为禁止输入重复数据(绝对引用和相对引用)
选中区域——数据有效性——设置——自定义——公式(=COUNTIF($A$1:$I20,A1)<2)
三、countifs计算多个条件的计数
(1)算1车间的邮寄费,二车间的独子费,二车间的过桥费
=countifs(E:E,G5,D:D,I5)
SUMIF和SUMIFS
在某列中找出符合某个条件的值,加起来某列的数据。
(1)sumif计算数值区间:把F列中大于500的数加起来
=sumif(F: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(查找引用函数)
1、vlookup函数语法
Vlookup(用谁去找,查找区域,拿回这个区域第几列数据,匹配方式)
2、vlookup使用通配符
vlookup函数成立的前提是,查找列和区域列的第一行必须完全一致,如果有数据不一致,就会返回错误。所以当这两个表的数据不完全一致时(少几个字或多几个字),我们应该使用通配符。*号代表这个数据源后面可能还有别的东西。=vlookup(A2&“*”,查找区域,返回列,0)
3、vlookup模糊查找
精确匹配,错一点都不行。而模糊匹配,找的是近似值,并且是小于等于自己的最大值。
在工作中可以运用于奖金、绩效等数据的档次划分中,找数值档次。
4、使用isna函数处理数字格式引起的错误
文本和数字是不相等的,数值智能加减乘除,不能连接东西。所以反过来想,如果文本加减乘除数字,就变成了数值。如果数值加连字符连上空的双引号,就可以变成文本。
- 通过数值的V文本的:把要去处理的数值链接一个空的双引号。=vlookup(F2&“”,区域,列,0)
- 通过文本的V数值的:=vlookup(F2*1,区域,列,0)或者=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一样,只不过列变成行)
Match与Index函数
1、函数语法(两个函数组合起来正好是vlookup)
Match(查找值,查找区域列,0)A1在A列中的第几行?
Index(在哪拿,拿第几个)
2、Match+index与vlookup的比较
=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)