增效方法：Excel常用功能大全（详解版）
目录
认识excel
1同一excel工作簿查看不同sheet工作表中的相关数据
视图-新建窗口-全部重排（选择重排的方式：垂直并排、水平并排）-不同窗口显示需要对比的不同sheet工作表。
注意：两个窗口实际互为镜像关系，修改一个窗口的数据，另一个窗口也会跟着变。
e.g垂直并排
2保存工作区（.xlw）
即保存表格的布局样式，再次打开仍是保存时的样式，保存为.xlw格式。
3一次插入多个工作表
点击sheet1-长按shift-点击sheetn-右键插入工作表，即实现了一次插入多个工作表。
4移动某列到同一工作表的不同位置
选中整列-长按shift-鼠标呈现十字箭头-移动到需要的地方
5快速到达工作表的边界（前提：单元格是连续的）
选中某一单元格-鼠标呈现十字箭头-四个方向都可以双击
6快速选中有效单元格（前提：单元格是连续的）
选中某一单元格-长按ctrl+shift+不同的方向键
7填充
Ctrl+；是当日日期，左键拖拽填充，右键拖拽可以选择填充的规则
8编辑自定义
选项-高级-编辑自定义-左边新序列-输入新序列规则
e.g张三-enter-李四- enter -王五- enter车-赵六- enter……
注意：每个数值后一定要enter
excel格式设置
1单元格内画斜线
①单元格内画一条斜线
单元格内填写好内容-设置单元格格式-边框斜线-alt+enter对不同内容进行分行-空格移动内容位置
②单元格内画多条斜线
直接插入形状
注意：设置单元格里的边框斜线可以随单元格变化，而插入的形状斜线是不会随单元格变化的
2设置单元格格式（数值）
设置单元格格式-数值-千位分隔符（1,000,000）
货币-货币符号（￥）
会计专用（会计专用与货币相似，只是会计专用的货币符号在单元格的最左侧）
日期（microsoft采用的是1900-1-1的日期，所有日期变换成的数字都是距离1900-1-1的天数）
特殊（直接转换中文大小写）
自定义 ;;; 隐藏
aaaa 星期几
aaa 几
yyyy-mm-dd xx（年）-xx（月）-xx（日）
yyyy"年"m"月"d日 xx年xx月xx日
@市 在单元格内容后面加市
单元格里面的数字可以按照数值的正负标注成不同的颜色
3设置单元格格式（文本）
注意：单元格格式里面的数值与文本不可以来回切换
文本数值转换为数值，单元格左上角的警惕号-转换成数字
有时txt会转换到xlsx中，此刻一般会用到分列
e.g在excel中，文本格式的2019-3-28日期转换成2019年3月28日
选中某列分列-分列结束（此处可以不进行实际分列，只是转换下格式）-设置单元格格式（转换成日期格式）
Excel查找、替换和定位
1替换
颜色字体替换
替换-高级-填充-全部替换
精确替换
替换-高级-单元格匹配（即精确匹配，查找内容为单元格里的全部内容）-替换
模糊替换
替换-高级-格式（模糊替换的格式，e.g张*、李？等）-替换为
注意：？表示一个字符，*表示多个字符，？？可以表示两个字符
在代码里，~后面的通配符（*/?）不生效，e.g某人叫张*替换为张经理
2添加批注
插入的是一般形状的批注：
右键-插入批注-编辑批注内容（右键可编辑、删除、显示/隐藏批注）
审阅里可显示/隐藏所有批注
插入的是特殊形状的批注：
插入-形状（随便添加一个形状）-绘图工具（格式）-编辑形状（右键）-添加到快速访问工具栏
编辑批注-更改形状
注意：批注也可以设置格式
3定位（ctrl+g）
批注
选中所有带批注的单元格
公式
选中所有带公式的单元格
对象
同时选中表中所有的图片
空值
一般用到单元格的合并与拆分
选中合并的单元格-合并后居中（即拆分为最小单元格）-定位空值（即选中了所有空的单元格）-=↑（即等于各个最小单元格相邻的上面的值）-ctrl+enter
e.g
Excel排序、筛选
1自定义排序
主要key-依据-次序
次要key-依据-次序
注意：自定义排序里面也可以按照颜色排序
e.g在成绩等排序时经常有字段的重要性及数据的重复性，因此经常用到依次从后向前排序的方法，即依次向前直接点击排序。
（第一列数据并排，第二列大小；第二列数据并排，第三列大小……）
e.g部门自定义排序
自定义排序-依据数值-次序（新序列，自己编写）
2把第一行表头插入到每一行的数据中
e.g工资条
先做出对应数量的表头（放在数值下面）-给数值行和表头行添加一列（数值不重复且表头行的数据和数值行的数据交叉）-自定义排序新添加的列
3打印时在第二页自动添加表头
页面设置-工作表-顶端标题行（选择表头）
4筛选
e.g筛选一车间、二车间…五车间、财务部、销售部等数据中的车间数据
右键-文本筛选- 结尾是（车间）
等于（*车间）
5数据高级筛选
数据-高级筛选-方式（将筛选结果复制到其他地方）-列表区域（要筛选的区域）-条件区域（如果是去重复值的，此处不用填写）-复制到（结果存放的地方）
注意：去重要勾选选择不重复的记录
6高级筛选多个条件
先复制粘贴出这些条件到某一区域a（两个或多个条件是and关系写在同一行，两个或多个条件是or关系写在不同行）
数据-）高级筛选-方式-列表区域-条件区域（复制出来的条件区域a）-复制到
注意：此刻不用选择不重复记录
Excel分类汇总、数据有效性
1分类汇总前一定要注意先排序
数据-分类汇总-分类字段-汇总方式-选定汇总项
替换当前分类汇总
汇总结果显示数据下方
2对多个字段进行分类汇总时
注意：对多个字段进行自定义排序
不要勾选下面的替换当前分类汇总
分类字段与选定汇总项不同
有时会粘贴汇总的结果，注意定位可见单元格
3使用分类汇总批量合并内容相同的单元格
排序-分类汇总-（除去表头）定位空值-合并后居中-分类汇总全部删除-格式刷刷格式至分类字段
4数据有效性
设置A列仅能输入500~1000之间的整数
设置B列仅能输入字符串长度为8位的产品编码
设置C列付款方式中仅能输入现金、转账、支票
5设置某张表的数据有效性（保护表格数据不被修改）
选中整张表格-数据有效性-允许（自定义）-公式（随便输入）
6数据有效性的出错警告
注意 ：取消表中的格式（数据有效性-全部清除）
Excel数据透视表
1创建数据透视表
插入-数据透视表-右键数据透视表选项（显示为经典数据透视表布局方便使用）-拖拉字段直接到数据透视表中的相应位置-可更改计数、求和等方式
注意双击数据区域中的某一单元格可显示该单元格的详细信息
e.g 双击数据区域的单元格338，在新的工作表中会显示出单元格338的详细信息
注意：数据透视表右侧的工具栏不小心被关掉后，点击数据表中的任何区域，右键显示字段列表
2数据透视表中创建组
3汇总多列数据
拉不同的字段至不同的列，若是排在了同一列只需拉至后一列即可
注意：数据透视表可嵌套不同的数据模板（美化图表的工具）
4创建计算字段
数据透视表-选项-域、项目和集-计算字段（名称‘新列名称’、公式‘双击字段写公式’）
注意：删除某一行或列，在透视表右侧工具栏，右键删除
计算结果可以更改格式
对于错误值可以选择不显示，e.g DIV/0！，右键-数据透视表选项-布局和格式-格式（相对错误值显示‘无’）
4批量一次性创建多张工作表并命好名称（前提名称在同一张工作表中的同一列）
插入-数据透视表-字段（拖至数据透视表的最上行）-数据透视表-选项-选项-显示报表筛选页-选中字段-确定
同时删除表格里的内容
Shift键选中所有工作表-复制空白行粘贴覆盖掉表中的数值
创建组
认识excel公式、函数
1选中-F4-锁定 即实现绝对应用
F4
2基本函数公式
Sum/average/count/max/min/rank
注意：rank使用时一般会用到绝对引用 rank（参数,区域）
跳跃式计算要先定位空值，再ctrl+enter
Excel中的if函数
1if（logical-test,[value-if-true],[value-if-false]）
if中可以嵌套2、3个if
2iserror判断对错经常与if连用
3and函数（and里面可以添加多个条件）
4or函数
5and与or函数
Excel中的countif函数
1countif（range,criteria）
2countifs(range1,criteria1, range2,criteria2……)
2条件格式
条件格式-新建规则-使用公式确定要设置格式的单元格-公式-格式
3设置数据有效性
e.g在A列设置不允许输入重复值
数据-数据有效性-自定义-公式（=countif(A:A,a1)<2）
4countif与countifs的区别
Countif是满足单个条件
Countifs是满足多个条件=COUNTIFS(C2:C22,">=80",D2:D22,">=80")
Sumif函数
1sumif(条件区域，条件，求和区域)
注意：sumif与countif都是之统计前15位，注意在条件上添加&’*’
2sumif(A:A,j5&k5,G:G)，针对多个条件
3sumifs(求和区域，条件区域，条件1，条件2…)
4设置sumif的数据有效性
出库量不能大于实际库存量
Vlookup函数
1vlookup中第二区域若不是整列，要绝对引用
2只有关键字的匹配(连接通配符) *代表字符或无字符
Vlookup(A2&*,数据源！B:E,4,0)
3vlookup模糊匹配
对于数据来说只匹配小于该数据的最大值，即最接近该数据的小值
注意：模糊匹配时，查找区域的数据要从小到大排列
一般用在计算提成方面
3数值格式转化成文本格式
数值只能计算，文本可以连接，若对数据进行连接，excel会自动把数值当成文本来对待，所以【数值&】可以转化成文本
4文本转换成数值【文本*1】【--文本】即负负文本得正文本
5对于格式不同的数据进行匹配
公式
=IF(ISNA(VLOOKUP(I2*1,$E$2:$G$6,3,0)),VLOOKUP(I2&"",$E$2:$G$6,3,0),VLOOKUP(I2*1,$E$2:$G$6,3,0))
Isna()函数是判断括号里的结果是否是N/A
注意：一般还是转换成统一的格式进行匹配
6横向的数据用hlookup函数
7Vlookup计算个税
Match与Vlookup嵌套使用
1vlookup只能实现左侧是id引用右侧的数据，且只能引用数值
2match与index嵌套可实现左右两侧的引用，且可引用图片
Match（lookup_value，lookup_array，match_type）即查找位置
lookup_value：需要在数据表（lookup_array）中查找的值，也可以是文本
lookup_array：可能包含有所要查找数值的连续的单元格区域，区域必须是某一行或某一列，即必须为一维数据
match_type：表示查询的指定方式，用数字-1、0或者1表示，match_type省略相当于match_type为1的情况（为1时，查找小于或等于lookup_value的最大数值在lookup_array中的位置，lookup_array必须按升序排列；为0时，查找等于lookup_value的第一个数值，lookup_array按任意顺序排列；为-1时，查找大于或等于lookup_value的最小数值在lookup_array中的位置，lookup_array必须按降序排列）
Index（array，row_num,[column_num]）即引用
Array为单元格区域，必须为一维数据
Row_num为数组中某行的行序号
Column_num是数组中某列的列序号
Index与match嵌套
Match查找，index引用
世界上本无vlookup，用的index与match的人多了，便形成了vlookup
注意：嵌套时经常用到绝对引用
3match与vlookup返回多列结果
注意：嵌套时的混合引用
Match也可查找文本
邮件合并
1excel中的数据批量填充到word文档中
邮件-邮件合并-邮件合分步向导-下一步开启-下一步选取收件人-浏览需要导入的excel表格-双击excel表（注意：如果选择错了表格，点击选择另外的表格）-下一步撰写信函-其他项目选择需要插入的字段-预览结果-再次预览结果可以返回编辑界面
-完成并合并-编辑单个文档-合并到新文档-生成一个新的文档（一页中包含一条数据）
-完成并合并-发送电子邮件-选择收件人，发邮件
-目录-完成并合并-编辑单个文档-合并到新文档-生成一个新文档（一页中包含很多数据）
2邮件合并后的资金日期格式处理
ALT+F9查看邮件中日期或者资金的源代码，再次ALT+F9是返回原界面，返回之后注意单击F9进行刷新
Excel常用日期与时间计算
1计算结束时间
excel中的整数时间是代表天,所以【90天/24小时/60分钟】
2计算时长
注意：设置单元格格式为常规
3计算结束/开始日期
注意：日期在excel中其实是一个数字，所以可以直接相加减
4计算工龄=datedif（start_serial_number,end_serial_number,return_type【y,[m],[d]】）
=datedif（开始时间，结束时间，ym,[md],[yd]）
ym指除去整年剩余的月数
md指除去整月剩余的天数
5计算间隔年月日
6计算第几周=weeknum（serial_number, return-type）
7计算周几=weekday(serial_number,return_type)
注意：写好公式后要设置成星期的格式
8第几周周几
9自定义周几=text(serial_number,aaaa)
注意：先设置自定义星期aaaa
10自定义日期=text(serial_number,0000-00-00)
注意：先设置自定义日期0000-00-00
11根据间隔月份计算结束日期=date（year,month,day）
求年份=year(serial_number)
月份=month(serial_number)
日=day(serial_number)
12计算本月最后一天=date（year,month,day）
注意：本月最后一天即为下月的前一天
day=0即为下月的前一天
day=1即为下月的第一天
13计算本月天数
14计算本月剩余天数
条件格式与公式
1为数据透视表中的数据制作数据条和切片器
注意：在插入数据透视表时要选中非空值的单元格，否则透视表中会出现空白的行和列
在数据透视表中经常对日期列进行右键-创建组-按月或者季度分组
制作数据条：选中数据-条件格式-数据条
插入切片器：选中数据-插入-切片器（切片器也可看做是筛选器，可用来添加新的维度）
添加的新维度可以切换（筛选）
2条件格式类型
突出显示单元格规则
>/
项目选取规则
选取数据中的max/min/max%/min%/>average/
数据条
即筛选器，添加新的数据维度
色阶
一般用于处理温度，表示随着数据的升降，颜色的深浅跟着改变
图标集
新建规则对应
数据条
突出显示，可以查找错误值DIV!0
Max.min
比较选定的值
重复值
公式
选中需要设置条件格式的字段（注意：在选中数据时一般不要选中表头，条件格式中的公式经常用到混合引用）
管理规则即条件格式中的公式
2利用条件格式将日期为周末的标记为红色
选中日期（除表头）-条件格式-新建规则-公式=WEEKDAY(A2,2)>5
将周末整行标记为红色
选中所有数据（除表头）-条件格式-新建规则-公式= WEEKDAY($A2,2)>5
利用条件格式标记未来15天内将要过生日的员工
=DATEDIF(DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),12,31),"d")-DATEDIF(B2,TODAY()-1,"yd")<=15
=本年的总天数-到今天为止除去到某一日期整年后剩余的天数=到今天为止本年剩余的天数
文本函数
1文本函数包含
=Left(text,num_chars)，num_chars从左至右截取的字符数，左截取
=Right(text,num_chars)，右截取
=mid(text,start_num,num_chars), start_num查找字符串文本中的起始位置，中间截取
=find(find_text,within_text,[start_num))，find_text要查找的字符，求某一字符的位置
=len(text)求字符个数
=lenb(text)求字节个数
注意：英文字母/数字/符号一个字符都代表一个字节，而汉字是一个字符代表两个字节
2文本函数结合运用
身份证倒数第二个偶数代表女性，奇数代表男性
15位的是倒数第一位，18位的是倒数第二位
截取单位len/lenb
截取特定字符前后
注意：100是取巧，因为后面要截取的字符已知不超过100个
截取地区码
注意：文本函数*1才能当做数学函数运算
计算出生年月date(year,month,day)
求身份证性别mod是求余函数
数学函数
1数学函数包含
=Round(number,num-digits)，求四舍五入，num-digits四舍五入后的小数点个数
=roundup(number,num-digits)，无条件向上进位
=rounddown (number,num-digits)，无条件向下舍去
=Int(number)，取整，结果都是小于该值的整数
=mod(number,divisor)=mod(被除数，除数)，求余
2row与column函数
=row()求行数
=column()求列数
注意：match()查找与特定字符有关，row()与column()与位置有关
3round、roundup、rounddown函数应用
4int函数应用
5row与column函数应用
注意：row与column后的数字是随单元格位置的变化而变化的
一行的转置也可选中数据-复制-移到需要粘贴的位置-选择性粘贴-转置
注意：row*（相隔的行数+1）
注意：row*（相隔的行数+1），column-与位置有关的规律数
数组
1数组即为矩阵，数组外一定要加大括号ctrl+shift+enter
注意：一个值可以与一组值相比较计算，true=1,false=0,因此计算结果的true/false*1可以与其他值进行计算
注意：sumproduct=大括号+sum
Sumproduct返回相应的数组或区域乘积的和
2lookup(lookup_value,lookup_vector,[result_vector])
lookup_value,要查找的内容
lookup_vector,要查找的区域
result_vector,返回需要的某列
注音：lookup没有第四参数，即没有精确/模糊匹配参数，一般lookup都是模糊
E4=$A$2:$A$92是true/false
(E4=$A$2:$A$92)*1是1/0
0/((E4=$A$2:$A$92)*1)是0/DIV0!(即错误值)
而lookup只能查找正确的数据
Indirect函数
1indirect(ref_text,[a1]),ref_text单元格的引用
Indirect与index的引用比较
2引用多表中的数据问题
注意：&字符的运用，因为A:G是不变的，所以要
注意：多表引用时的混合引用
3indirect与数据有效性
选中省份下面的城市-公式-定义名称（注意定义的名称要与indirect引用的内容一致）
给每列设置数据有效性（每列的标题先不要填写，在最后有限性设置完，再取消第一行的标题栏的有效性）-序列-序列内容的区域
后面列的有效性-序列-indirect(前列对应单元格)
图表基础
1图表中的元素
2了解主次坐标轴
注意：主次坐标轴的刻度与刻度的max/min
3折线图与柱形图结合图表
4制作计划于实际对比图
注意：坐标轴的刻度单位
5制作双向柱形图（旋风图）
插入-条形图-设置次坐标轴-次坐标轴的刻度固定大小-删掉上面的次坐标轴-下面的主坐标轴设置数值格式为0%;0%-分类轴（y轴）标签设置为高或者低-逆刻度值是指分类轴的上下换位置-复制背景图片前先设置背景图片的艺术效果
5利用复制粘贴更改数据系列显示样式
制作好条形图后直接插入心形-复制-粘贴到原条形区域-右键-设置数据系列格式-填充-层叠
注意：若想拉开心形间的距离可以在心形形状上在插入一个无填充的矩形
若插入的无填充矩形不好选中-开始-查找与选择-选择对象-点击矩形大概区域
6甘特图（一般用在项目进度上）
插入堆积条形图-把日期条设置成完全隐藏的格式-设置坐标轴的刻度-设置分类轴的逆刻度-日期刻度的固定值大小是按照日期的数字格式确定的
6.1动态甘特图
动态甘特图里条形分为3段（隐藏的日期段、已完成、未完成）-所以选取的是计划开始时间、已完成、未完成数据（注意if函数的应用）-c18一般是指当日的日期-插入的滚动条刻度设置为日期的刻度范围（注意是实际范围，不是数值大小）-滚动条链接一个空单元格-c18与空单元格之间建立一定的联系（c18=b2+空单元格）
7巧用图表模板
注意：上面的这些图表都可以粘贴到excel中作为模板使用
粘贴到excel中-图表工具-另存为模板
附加：图片-右键-大小和属性-属性-对象位置-大小和位置随单元格而变
坐标轴-右键-设置坐标轴格式-显示单位
PPT图表链接与动画
1双坐标柱形图
插入簇状柱形图-设置主次坐标轴-选择次坐标轴-选择数据-添加两个系列值为0的新系列-图表工具布局-选中一个系列值为0的系列-设置所选内容格式为主/次坐标轴-选中一个系列-选择数据-把一个空的新序列向上移
注意：把系列值为0的图例删掉
2饼图美化
插入三维饼图-右键三维旋转-取消自动缩放-高度调小-右键-三维格式-棱台-右键-数据标签居中
3双层饼图
双层饼图哪一个在上面先做哪一个
插入二维饼图-选择数据-添加（注意：系列值选择添加的是后饼图的数据）-新系列的水平轴标签选择的是新分类-右键设置前饼图为次坐标轴-向外同时拖动次坐标轴可以看到后面的主坐标轴-单个向内拖动次坐标轴-添加数据标签
注意：要设置边框
4图表插入到PPT中
复制图表-直接粘贴到PPT中（此时的格式是随PPT的主体系列更改的）
复制图表-PPT中保留原格式（格式不随PPT的主题格式变化）
复制图表-PPT中保留链接数据（若更改excel中的原数据，PPT中的表格工具设计里刷新数据可以直接在PPT中更改图表样式）
复制图表-保留原格式和链接数据（若更新excel中的数据，在新打开PPT时会提示是否更新数据，此时的更新是整个PPT中的数据都进行更新，不用再单个更新）
5PPT中图表的动画设置
选中图表-动画-动画进入方式-动画-动画窗格-右键-效果选项-图表动画-组合图表中选择按分类中的元素（或其他）-在动画窗格里可以看到多个动画对象-选中第二个对象shift至图表中的最后一个对象-右键-从上一项开始之后开始-播放
数据透视表动态区域
1 OFFSET(reference,rows,cols,height,width)
e.g以A1单元格为例，OFFSET（A1，1,1,2,2）即以A1单元格为参照物，下移1行，右移1列，选取两行两列，所以offset得到的是数据区域
把整个表格用offset定义为一个动态数据区域后，可以插入一个数据透视表-表/区域填写为定义的动态数据区域
选取动态数据区域，用offset（$A$1，0,0，counta($A:$A),b）
Counta($A:$A)代表A列非空单元格数，b代表选取的列数
定义名称动态数据区域，编辑公式为offset
然后在数据透视表中选中某行数据刷新即可随着表格及时更新
2offset函数经常与数据透视表、各种条形折线图连用
注意：有时一个表格会用到两个offset函数来定义名称
e.g取后10天的成交量（后10天的数据随着数据的增删而变，所以是动态图）
定义名称-日期（=offset($A$1,counta($a:$a)-10,0,10,1)）
定义名称-成交量（=offset($B$1,counta($a:$a)-10,0,10,1)）
插入空白柱形图-选择数据-添加新系列（成交量，=表名称!定义的名称）
水平轴标签编辑（=表名称!定义的名称）
豫公网安备41018102000163号
豫ICP备17025960号-3