EXCEL系统学习
2020-03-17 18:25:35 0 举报AI智能生成
EXCEL系统学习~
作者其他创作
大纲/内容
数据输入技巧
系列数据录入和快速填充
填充自然数
输入1、2，选中，向下拖拽
输入1，黑十字右下角，右键，选择填充序列
输入1，黑十字右下角，CTRL,向下拖拽
填充奇数
输入1、3，选中，向下拖拽
填充偶数
输入2、4，选中，向下拖拽
填充数量多的时候
输入数字
输入1，开始选项卡，填充，序列，设置
输入文本
无规律，需要保存自定义序列
文件，选项，高级，进度条最下面，编辑自定义列表，回车分割序列，保存，确定。
文件，选项，高级，进度条最下面，编辑自定义列表，从单元格中导入序列，导入，保存，确定
有规律，也可像数字一样自动识别填充
日期
甲乙丙丁
星期
快速填充
OFFICE2013新版本功能
减少重复录入与规范填充
下拉菜单
ALT+⬇
只针对文本有效
必须与上面的数据紧密相连
可以打开上方不重复内容的下拉菜单
ALT+⬇
如果不和上方相连也可以
只要和周围有联系
复制单元格内容
复制上方单元格内容
CTRL+D
复制左侧单元格内容
CTRL+R
输入日期
输入今天日期
CTRL+分号
固定日期不会变
函数：=today()
跟随系统显示
输入长数字
输入前，修改单元格格式为【文本】
输入前，添加‘单引号
输入相同数字（同步录入)
选中范围，直接输入数字，CTRL+回车
拆分合并单元格与选择性粘贴
拆分单元格后同步录入
选中所有范围，定位条件orF5快捷键orCTRL+G,选择【空值】，确定，输入=上方单元格，CTRL+回车
选择性粘贴
去掉公式
选中所有范围，复制，粘贴，右键【选择性粘贴】，选择【数值】，确定
颠倒行列（转置）
选中所有范围，复制，粘贴，放在空白处，右键【选择性粘贴】，【转置】，确定。
加减乘除（批量处理数字）
空白处录入数字，复制这个数字，选中范围，右键【选择性粘贴】，选择【数值】和【加减乘除】，确定。
打印、样式设置与表格查看
打印
保留表头
页面布局【打印标题】，顶端标题行，选中表头，确定。
查看
保留表头
视图【冻结窗格】，冻结首行/首列/取消。
视图【冻结拆分窗格】，先找到冻结后能动的第一个单元格，点击后，选择【冻结拆分窗格】。
快速跳转表格末端
选中A1单元格，CTRL+方向键
慢慢连选
选中A1单元格，SHIFT+方向键
只选择有内容的
选中一行/一列
选中A1单元格，CTRL+SHIFT＋方向键
选中两行/两列
选中A1A2/A1B1，CTRL+SHIFT＋方向键
全选
选中范围内任意一个单元格，CTRL+A
套用表格样式
开始【套用表格格式】，确定，设计【有很多样式颜色】，鼠标长期停留即可预览。
去掉【镶边行】/【镶边列】即可去掉隔行/隔列变色效果。
帮助自动拓展表格（格式/公式不变）
取消套用表格样式
【转化为区域】
图表制作
图表的组成及样式设置
图表类型
柱状图
显示一段时间内数量变化情况
饼图
描述比例、构成 等信息
折线图
说明趋势
图表要素
上方：标题
下方：图例
纵轴：数据轴
横轴：分类轴
横网络线，纵网格线
全部区域：图表区。内部：绘图区
不同颜色的柱子：不同的系列
放在一起作比较的柱子：分类
柱子上方的数字：数据标签
趋势线：辅助线
修改图表要素
选中图表 ，右键，加号
在图表任意地方，双击，可以进行更详细设置
表格转化为图表
转化方法：选中表格，插入，图表
自带模板：设计选项卡，【快速布局】
配色方案：设计选项卡，【更改颜色】
单独更改一个部分的颜色也可以
重点突出：可以拖拽饼图任意部分，单独拿出
组合图表与双坐标图的制作
组合图表
柱形图+折线图
插入柱形图，二维柱形图
注意：组合图表不能使用三维柱形图
改变图例内容：图表，设计，切换行/列
柱子转换为折线
①选中柱子，右键，更改系列图表类型/设计选项卡更改系列图表类型，找到对应的系列名称，修改类型，确定。
②插入，推荐的图表
OFFICE2013新版本功能
双坐标轴
两组数据差异巨大，无法同时放在一个坐标轴内
要调整坐标轴
双击折线，设置数据系列格式，系列选项，次坐标轴
折线系列太小
①格式，当前所选内容，下拉菜单，选中，设计，更改图表类型，更改为折线图。
②格式，当前所选内容，设置所选内容格式，系列选项，次坐标轴。
③设置数据系列格式，油漆桶，标记，数据标记选项，内置，类型，大小，边框，颜色都可以调整。
④设计，选择合适的图表样式
图表美化技巧与迷你图
美化技巧
柱子
选中范围不同，美化范围也不同
选中所有柱子
选中一个柱子
使用自带美化
右键，设置系列数据格式OR双击，设置系列数据格式，填充，图片或纹理填充/图案填充/插入图片
大形状自定义美化
插入，形状，在旁边空白处画，【格式选项卡】编辑形状（颜色/边框/大小），复制做好的形状，选中柱子，粘贴。
小形状自定义美化
插入，形状，在旁边空白处画，【格式选项卡】编辑形状（颜色/边框/大小），复制做好的形状，选中柱子，粘贴，设置系列数据格式，填充效果选择层叠。
使用LOGO美化
同理大形状小形状美化。
折线
使用自带美化
设置数据系列格式，标记，内置
自定义美化
插入，形状，在旁边空白处画，【格式选项卡】进行编辑，复制做好的样子，选中折线，粘贴 。
迷你图
2010版本开始新增的
类型
折线图，柱形图，盈亏图
设置
鼠标选中希望放置迷你图的地方，【插入选项卡】，选择类型，选择数据范围，确定。
可以拖拽做一组迷你图
【设计选项卡】，可以更改颜色，可以选择最高点最低点
删除
【设计选项卡】，清除
公式与函数
相对、绝对、混合引用
相对引用
A1
慢捷键
F4四次
绝对引用
＄A＄1
快捷键
F4一次
混合引用
锁定列
＄A1
快捷键
F4三次
锁定行
A＄1
快捷键
F4两次
函数使用技巧及常用函数
函数构成
函数名
参数
不同参数之间用【英文】逗号2隔开
常用函数
求和
SUM
对数字计数
COUNT
数字
对非空单元格计数
COUNTA
数字+文本
平均数
AVERAGE
子主题 2
最大值
MAX
最小值
MIN
语法相同
函数名（范围）
保留小数
开始菜单的快捷键
只是隐藏并非保留
函数
分支主题
ROUND
四舍五入
例子：5.49=5.5
ROUNDDOWN
具体位数上的数字
例子：5.49=5.4
语法相同
函数名（小数，位数）
小技巧
用上下方向键从下拉函数表中选择函数
用TAB键选中所需要的函数
最好用小写英文字母输入函数名
快捷键
开始菜单：自动求和▼
判断函数IF
语法
=（判断条件,真值,假值）
符号
大于
＞
小于
＜
大于等于
＞=
小于等于
＜=
不等于
＜＞
多层嵌套
没有A＜B＜C这样的连等形式
函数层级不同，括号颜色也不同
外黑，中红，内蓝
三个条件需要套用两个层级（IF）
公式可简化
相同参数可以放在最外层括号外
参数设置
显示空白
双引号，中间无空格
逻辑函数AND,OR
语法
=AND/OR(条件1,条件2,条件3,.......)
并/或
用途
嵌套在其他函数中使用
条件统计函数COUNTIF,COUNTIFS,SUNIF,SUMIFS
语法
COUNTIF
=(条件范围，条件）
COUNTIFS
=（条件范围1，条件1，条件范围2，条件2，条件范围3，条件3.。。。。）
SUMIF
=（条件范围，条件，求和范围）
条件范围和求和范围，同起同止
SUMIFS
=（求和范围，条件范围1，条件1，条件范围2，条件2，条件范围3，条件 3.。。。）
tips
如果函数名或者函数内容比较长，挡住了需要选择的单元格，可以先选中周围的单元格，再用方向键调整至需要的地方。
查找函数VLOOKUP
语法
=（查询值，查询范围，返还序列 ，匹配参数）
解释
查询范围
包含查询值和返还值的连续区域
不可跳选
查询值必须位于查询范围的首列
向后拖选
数字文本均可
返还序列
表明返还值位于查询范围的第几列
不是整张表的第几列
匹配参数
0
精确匹配
1
近似匹配
tips
跨表操作时，不要来回翻页，一直写下去
#N/A
表示不存在，错误
嵌套函数消除错误
IFERROR
=(公式，替换值）
子主题 2
日期函数（1）
语法
获取年
YEAR(完整日期）
获取月
MONTH(完整日期）
获取日
DAY(完整日期）
获取完整的日期
DATE（年，月，日）
可以得到标准的时间格式
tips
在对日期进行加减运算时，会自动借位或者退位。
日期函数（2）
语法
TODAY
=（）
获取今天的日期
DATEDIF
=（起始日期，截止日期，参数）
计算日期间隔
参数
Y:整年数
M:整月数
D:整日数
YM:不足一年的整月数
YD:不足一年的整天数
MD:不足一月的整天数
TIPS
最神秘的函数
需要自己补全函数名称
直接写参数的话，要加“”
文本函数LEN,LEFT,MID,RIGHT
语法
计算文本长度
LEN（文本）
从左边截取文本
LEFT(文本，截取几位）
从右边截取文本
RIGHT(文本，截取几位）
从中间截取文本
MID(文本，从第几位开始，截取几位）
注意
截取出来的都是文本，不是数字
数据直观性与安全
条件格式
含义
根据设置的内容不同条件不同，而显示出不同的格式样子
位置
【开始】➡【条件格式】
类型
突出显示单元格的规则
简单比较就知道
项目选取规则
计算后才知道
数据条
色阶
图标集
修改格式
管理规则➡显示其格式规则（当前工作表）➡编辑规则or删除规则
新建规则
需要变色的单元格和判断值的单元格不是同一个
新建规则➡使用公式确定要设置格式的单元格➡编辑规则说明➡格式
tips
应注意需要变色的是哪个值
用公式设置更自动化的条件格式
确定两列人员是否有重叠
选中需要确定的列
条件格式—新建规则-使用公式确定要设置格式的单元格
编辑规则：=countif
设置标注颜色
数据条
长短直观表示数字大小
色阶
颜色深浅进行填充，直观表示
图标集
按照百分比统计
按照数字填充
其他规则
修改图标的值和类型
数据验证
位置
数据选项卡-数据验证/数据有效性
做法
选中需要设置的行或者列
打卡数据验证，设置验证条件
勾选【对有同样设置的所有其他单元格应用这些修改】
方便记忆是哪些单元格设置过数据验证
设置填写提示
选中需要设置的行或者列
打卡数据验证，设置输入信息
会在填写数据前显示提示
设置出错警告
选中需要设置的行或者列
打卡数据验证，设置出错警告
样式不同，后续动作也不一样
停止
错误数据完全进不去
警告
数据填写人可以决定是否可以进入
确定
数据填写人可以决定是否可以进入
会在输入信息后显示出错警告
设置数字/日期/文本长度
借助公式设置数据验证
选中需要设置的行或者列
设置-自定义-公式
下拉菜单1
数据验证-设置
允许【序列】
来源:项目之间用英文逗号分隔
下拉菜单2
需要引用的序列在其他SHEET里面的话
来源：直接框选
不能跨sheet引用序列的话
命名需要引用的序列（sheet名称+1，单元格区间）
在名称框里进行命名
公式-名称管理器：管理曾经命名过的名称
有二级下拉菜单的话
必须使用命名的方式设置二级菜单
保护工作簿、工作表
保护整个文件
文件，另存为，浏览，工具，常规选项，打开权限密码，确定，保存
目的是在打开时输入密码
保护工作表
审阅，保护工作表，设定密码，确定
撤销工作表保护
审阅，撤销工作表保护，输入密码
保护工作表时，留下部分单元格可填写
选中单元格，开始，数字，设置单元格格式，保护，取消锁定，确定。审阅，保护工作表，输入密码，确定
在保护工作表的前提下，才能进行对单元格的保护
保护单元格内的公式不被别人查看到
选中单元格，开始，数字，设置单元格格式，保护，点击隐藏，确定。审阅，保护工作表，输入密码，确定。
在保护工作表的前提下，才能进行对单元格的保护
保护工作簿
目的是为了隐藏工作表后不让别人取消隐藏
审阅，保护工作簿，输入密码，确定
数据分析与处理
单条件排序与多条件排序
单条件排序
选中需要排序的数据中的任一个，数据，排序（A→Z,Z→A）
此排序是对扩展区域排序
多条件排序
选中需要排序的数据中的任一个，数据，排序，主要关键字 ，添加条件，次要关键字 ，确定。
自定义排序
选中需要排序的数据中的任一个，数据，排序，主要关键字，次要关键字，自定义序列，确定。输入序列（项目之间用回车隔开），添加，确定。
自动筛选
文本筛选
选中需要筛选的数据中的任一个，数据，筛选，文本筛选
等于/开头/结尾/包含
数字筛选
选中需要筛选的数据中的任一个，数据，筛选，数字筛选
大于/等于/平均值/小于
日期筛选
天/周/月/季度/年
如何判断表格显示的数据是否已经被筛选？
标题上有漏斗图案是已经被筛选的，三角是未筛选
行标是蓝色是已经筛选的
如何还原被筛选的表格
漏斗×清除筛选
清除一个
数据，筛选，清除
清除整张表
高级筛选
数据，筛选，高级
列表区域
需要进行筛选的原数据
条件区域
多条件
条件之间的关系：且
复制条件名称，将筛选条件放在同一行并列两行
条件之间的关系：或
复制条件名称，将筛选条件错开放置
注意事项
将筛选结果另行放置在一个空白单元格，避免破坏原数据
框选条件区域时，不能多选也不能少选，要沿着边框选择
分类汇总制作与粘贴
分类汇总
汇总+计算
先排序，数据，分类汇总，分类字段，汇总方式，选定汇总项
隐藏不需要看到的列
数据，创建组
比单纯的隐藏和取消隐藏更方便
清除分类汇总
数据，分类汇总，全部删除
粘贴
避免复制粘贴不需要的空白单元格
选中经过筛选或分类汇总的表格，F5定位，定位条件是可见单元格，确定，复制，粘贴
ALT+分号
选中经过筛选或分类汇总的表格，复制，粘贴
数据透视
数据透视表的制作，结构与数据透视图
数据透视表
激活办法
将光标放在表格中的任意一个单元格，插入，数据透视表，将新表格放在新的工作表中
字段
列：标题
值：需要计算的
行：需要分类显示的
可以拖动字段，达到调整显示先后顺序的效果
二维表格
既需要看行标签，又需要看列标签
一维表格
只需要看列标签
更适合做数据透视表
无合并单元格
无空行空列
在选择时贴边选择，不要整张表整行或整列全选
准确
日期格式
数字格式
数据透视表做好以后
如何查看明细数据？
查看：双击数据透视表
会另外产生一个工作表
原数据透视表还在以前的工作表中
做数据透视图
分析，数据透视图
和普通图表没有区别
唯一区别：可筛选
更改数据透视表的结构与数据来源
在紧密的数据透视表数据之间插入空行
设计，空行
不能单独或者手动插入
将求和项放在底部
设计，分类汇总
此处的分类汇总和一般的分类汇总不同
将字段分开显示
设计，报表布局，以大纲形式显示
一般没有设计过的数据透视表都是压缩布局
标签行空白较多
设计，报表布局，重复所有项目标签
批量合并单元格
设计，报表布局，以表格形式显示，鼠标选择数据透视表中任意一个单元格，右键，数据透视表选项，布局和格式，合并并居中排列
是否显示合计
设计，总计
原数据有单个数据更改后
数据透视表不会随着原数据更改
分析，刷新
原数据有删除
数据透视表不会随着原数据更改
分析，刷新
原数据有一处新增
数据透视表不会随着原数据更改，因为增加项不在选中项里面
分析，更改数据源
原数据反复有新增
开始，套用表格格式，创建透视表，新增数据，分析，刷新
在创建数据透视表之前就随便套用一个表格格式即可
数据透视表字段设置
对同一字段设置不同的计算
光标放在在需要计算的列中，右键，值字段设置，值汇总方式，平均值、计数等等
可以对同一字段进行反复拖拽入值设置
对同一字段的不同显示方式设置
光标放在在需要计算的列中，右键，值字段设置，值显示方式：总计的百分比、行汇总的百分比
修改行标签
直接在编辑栏可以删除并修改行标签内容
对数字显示格式的修改
光标放在在需要计算的列中，右键，值字段设置，数字格式：货币/百分比/
对日期的分组汇总
将光标置于日期的行标签中，右键，创建组，选取需要的步长
等距分组和不等距分组
等距分组
右键，创建行，设置步长
不等距分组
手动选取需要分组的标签，右键，创建组
分组的组名可以在编辑栏进行删除修改
数据透视表的排序、筛选与拆分
排序
行标签排序
展开下拉菜单，选择升序or降序
数据排序
数据选项卡，升序or降序
筛选
行标签
展开下拉菜单，进行筛选
列标签
展开下拉菜单，进行筛选
筛选器
将字段放进筛选器，表格左上方出现筛选菜单
切片器
分析选项卡，插入切片器，选择字段，确定
点击单个数据进行筛选
也可多选或者点击×选择全部数据
更改切片器样式
切片器工具，选项
更改切片器列数
切片器工具，选项，列，调整大小
拆分
对每个数据都分别作出一个独立报表
筛选时选择全部，分析，选项，显示报表筛选页，选择字段，确定，会出现多个工作表
多余工作表可以全选，右键，删除
0 条评论
下一页
