未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
Excel常用功能大全(详解版)
目录
认识excel
excel格式设置
Excel查找、替换和定位
Excel排序、筛选
Excel分类汇总、数据有效性
Excel数据透视表
认识excel公式、函数
Excel中的if函数
Excel中的countif函数
Sumif函数
Vlookup函数
Match与Vlookup嵌套使用
邮件合并
Excel常用日期与时间计算
条件格式与公式
文本函数
数学函数
数组
Indirect函数
图表基础
PPT图表链接与动画
数据透视表动态区域
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
格式设置
1单元格内画斜线
①单元格内画一条斜线
单元格内填写好内容-设置单元格格式-边框斜线-alt+enter对不同内容进行分行-空格移动内容位置
②单元格内画多条斜线
直接插入形状
注意:设置单元格里的边框斜线可以随单元格变化,而插入的形状斜线是不会随单元格变化的
2设置单元格格式(数值)
设置单元格格式-数值-千位分隔符(1,000,000)
货币-货币符号(¥)
会计专用(会计专用与货币相似,只是会计专用的货币符号在单元格的最左侧)
日期(microsoft采用的是1900-1-1的日期,所有日期变换成的数字都是距离1900-1-1的天数)
特殊(直接转换中文大小写)
自定义  ;;; 隐藏
aaaa星期“几”
aaa“几”
yyyy-mm-ddxx(年)-xx(月)-xx(日)
yyyy"年"m"月"d”日”  xx年xx月xx日
@”市” 在单元格内容后面加“市”
单元格里面的数字可以按照数值的正负标注成不同的颜色
3设置单元格格式(文本)
注意:单元格格式里面的数值与文本不可以来回切换
文本数值转换为数值,单元格左上角的警惕号-转换成数字
有时txt会转换到xlsx中,此刻一般会用到分列
e.g在excel中,文本格式的2019-3-28日期转换成2019年3月28日
选中某列分列-分列结束(此处可以不进行实际分列,只是转换下格式)-设置单元格格式(转换成日期格式)
查找、替换和定位
1替换
①   颜色字体替换
替换-高级-填充-全部替换
②   精确替换
替换-高级-单元格匹配(即精确匹配,查找内容为单元格里的全部内容)-替换
③   模糊替换
替换-高级-格式(模糊替换的格式,e.g张*、李?等)-替换为
注意:?表示一个字符,*表示多个字符,??可以表示两个字符
在代码里,~后面的通配符(*/?)不生效,e.g某人叫张*替换为张经理
2添加批注
插入的是一般形状的批注:
右键-插入批注-编辑批注内容(右键可编辑、删除、显示/隐藏批注)
审阅里可显示/隐藏所有批注
插入的是特殊形状的批注:
插入-形状(随便添加一个形状)-绘图工具(格式)-编辑形状(右键)-添加到快速访问工具栏
编辑批注-更改形状
注意:批注也可以设置格式
3定位(ctrl+g)
①   批注
选中所有带批注的单元格
②   公式
选中所有带公式的单元格
③   对象
同时选中表中所有的图片
④   空值
一般用到单元格的合并与拆分
选中合并的单元格-合并后居中(即拆分为最小单元格)-定位空值(即选中了所有空的单元格)-=↑(即等于各个最小单元格相邻的上面的值)-ctrl+enter
e.g
排序、筛选
1自定义排序
主要key-依据-次序
次要key-依据-次序
注意:自定义排序里面也可以按照颜色排序
e.g在成绩等排序时经常有字段的重要性及数据的重复性,因此经常用到依次从后向前排序的方法,即依次向前直接点击排序。
(第一列数据并排,第二列大小;第二列数据并排,第三列大小……)
e.g部门自定义排序
自定义排序-依据数值-次序(新序列,自己编写)
2把第一行表头插入到每一行的数据中
e.g工资条
先做出对应数量的表头(放在数值下面)-给数值行和表头行添加一列(数值不重复且表头行的数据和数值行的数据交叉)-自定义排序新添加的列
3打印时在第二页自动添加表头
页面设置-工作表-顶端标题行(选择表头)
4筛选
e.g筛选一车间、二车间…五车间、财务部、销售部等数据中的车间数据
右键-文本筛选-  结尾是(车间)
等于(*车间)
5数据高级筛选
数据-高级筛选-方式(将筛选结果复制到其他地方)-列表区域(要筛选的区域)-条件区域(如果是去重复值的,此处不用填写)-复制到(结果存放的地方)
注意:去重要勾选选择不重复的记录
6高级筛选多个条件
先复制粘贴出这些条件到某一区域a(两个或多个条件是and关系写在同一行,两个或多个条件是or关系写在不同行)
数据-)高级筛选-方式-列表区域-条件区域(复制出来的条件区域a)-复制到
注意:此刻不用选择不重复记录
分类汇总、数据有效性
1分类汇总前一定要注意先排序
数据-分类汇总-分类字段-汇总方式-选定汇总项
¨        替换当前分类汇总
¨        汇总结果显示数据下方
2对多个字段进行分类汇总时
注意:对多个字段进行自定义排序
不要勾选下面的替换当前分类汇总
分类字段与选定汇总项不同
有时会粘贴汇总的结果,注意定位可见单元格
3使用分类汇总批量合并内容相同的单元格
排序-分类汇总-(除去表头)定位空值-合并后居中-分类汇总全部删除-格式刷刷格式至分类字段
4数据有效性
设置A列仅能输入500~1000之间的整数
设置B列仅能输入字符串长度为8位的产品编码
设置C列付款方式中仅能输入现金、转账、支票
5设置某张表的数据有效性(保护表格数据不被修改)
选中整张表格-数据有效性-允许(自定义)-公式(随便输入)
6数据有效性的出错警告
注意 :取消表中的格式(数据有效性-全部清除)
数据透视表
1创建数据透视表
插入-数据透视表-右键数据透视表选项(显示为经典数据透视表布局方便使用)-拖拉字段直接到数据透视表中的相应位置-可更改计数、求和等方式
注意双击数据区域中的某一单元格可显示该单元格的详细信息
e.g 双击数据区域的单元格338,在新的工作表中会显示出单元格338的详细信息
注意:数据透视表右侧的工具栏不小心被关掉后,点击数据表中的任何区域,右键显示字段列表
2数据透视表中创建组
3汇总多列数据
拉不同的字段至不同的列,若是排在了同一列只需拉至后一列即可
注意:数据透视表可嵌套不同的数据模板(美化图表的工具)
4创建计算字段
数据透视表-选项-域、项目和集-计算字段(名称'新列名称’、公式'双击字段写公式’)
注意:删除某一行或列,在透视表右侧工具栏,右键删除
计算结果可以更改格式
对于错误值可以选择不显示,e.g #DIV/0!,右键-数据透视表选项-布局和格式-格式(相对错误值显示'无’)
4批量一次性创建多张工作表并命好名称(前提名称在同一张工作表中的同一列)
插入-数据透视表-字段(拖至数据透视表的最上行)-数据透视表-选项-选项-显示报表筛选页-选中字段-确定
同时删除表格里的内容
Shift键选中所有工作表-复制空白行粘贴覆盖掉表中的数值
创建组
公式、函数
1选中-F4-锁定 即实现绝对应用
    F4       
2基本函数公式
Sum/average/count/max/min/rank
注意:rank使用时一般会用到绝对引用 rank(参数,区域)
跳跃式计算要先定位空值,再ctrl+enter
中的if函数
1if(logical-test,[value-if-true],[value-if-false])
if中可以嵌套2、3个if
2iserror判断对错经常与if连用
3and函数(and里面可以添加多个条件)
4or函数
5and与or函数
中的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")
函数
1sumif(条件区域,条件,求和区域)
注意:sumif与countif都是之统计前15位,注意在条件上添加&’*’
2sumif(A:A,j5&k5,G:G),针对多个条件
3sumifs(求和区域,条件区域,条件1,条件2…)
4设置sumif的数据有效性
出库量不能大于实际库存量
函数
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计算个税
与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进行刷新
常用日期与时间计算
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条件格式类型
①   突出显示单元格规则
>/</<<(注意:min<介于=<max)/=/⊆/发生日期/重复值
②   项目选取规则
选取数据中的max/min/max%/min%/>average/<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只能查找正确的数据
函数
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中-图表工具-另存为模板
附加:图片-右键-大小和属性-属性-对象位置-大小和位置随单元格而变
坐标轴-右键-设置坐标轴格式-显示单位
图表链接与动画
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))
插入空白柱形图-选择数据-添加新系列(成交量,=表名称!定义的名称)
水平轴标签编辑(=表名称!定义的名称)
本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报
从APP上打开文章,阅读全文并永久保存 查看更多类似文章
来自:知更鸟的360  > 待分类
举报
[荐]  原创奖励计划来了,万元大奖等你拿!
猜你喜欢
类似文章
Excel电子表格基本知识点
Excel操作技巧(3)
EXCEL电子表格的基本操作
《Excel 2010应用大全》目录
职场小白,Excel这些操作要知道
OFFICE2007课堂笔记
更多类似文章 >>
生活服务
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!