Excel基础学习(2013及以上版本)
(教学视频点这里)
文章目录
(一) 快速录入
1) 切换单元格
- Tab:移动到下一个(右边)单元格
- Shift+Tab:移动到上一个(左边)单元格
- Enter:跳转到下一行第一个(最左边)单元格
- Shift+Enter:移动到上一行当前列的单元格
2) 爬取网页上的表格
- 方式一:从网页上复制粘贴
- 方式二:数据选项卡 -> 获取外部数据 -> 自网站
- 让数据手动更新:表格任意位置右击 -> 刷新
- 让数据自动更新:数据选项卡 -> 查询和连接 -> 连接属性 -> 设置刷新频率或打开文件时刷新数据(或右边栏连接&查询窗口 -> 选择表右击 -> 属性 -> 设置刷新频率或打开文件时刷新数据)
- 只能爬取网页上表格类数据,不能爬取非结构化数据
3) 清洗爬取的数据
-
检查原表是否存在数据缺失、异常值、不规范等情况,并与团队确认问题
-
清洗数据(使用Power Query编辑器)
-
如果想对爬取的数据表新建一个查询的话,要先将其转化为一个超级表(对于2016专业增强版,如果直接进行下面清洗数据的步骤,则会自动提示并转换成为超级表)
-
转换为超级表:选中表 -> 插入选项卡 -> 表格(或选中表 -> Ctrl+t)
-
清洗数据:数据选项卡 -> 获取和转换 -> 从表格 -> 对数据进行清洗(这样做对源数据是无损的,可以保留更改记录便于撤回不想要的修改步骤)
- 基础操作:修改列名、筛选符合条件的行、删除不想要的列、格式转换、排序等
- 插入自定义列操作:自定义列公式举例:if [金额]>1000 then [金额]*0.95 else [金额]
-
关闭并上载至… -> 现有工作表
-
(二) 显示格式
1) 单元格格式
- 打开单元格格式设置框快捷键:Ctrl+1
- 单元格内容过多:对齐方式自动换行
- 推荐对齐方式跨列居中:不推荐合并居中,无法定位到单元格
- 自动调整列宽:选中需要调整的列 -> 开始选项卡 -> 单元格 -> 格式 -> 自动调整列宽
- 清除格式:选中区域 -> 开始选项卡 -> 编辑 -> 清除 -> 清除格式
2) 表格格式
-
条件格式
-
设置条件格式:
- 突出显示:开始 -> 样式 -> 条件格式 -> 突出显示单元格规则(或最前/最后规则)
- 数据条:开始 -> 样式 -> 条件格式 -> 数据条
- 快速找不重复:开始 -> 样式 -> 条件格式 -> 突出显示单元格规则 -> 重复值
-
编辑条件格式规则:开始 -> 样式 -> 条件格式 -> 管理规则 -> 编辑规则
-
-
超级表
-
普通区域转化为超级表:选中表 -> 插入选项卡 -> 表格(或选中表 -> Ctrl+t)
-
超级表转化为普通区域:设计选项卡 -> 工具 -> 转换为区域
-
3) 特殊数字格式
-
时间日期
- 直接输入:2022-3-16 或 2022/3/16
- 设置格式:选中 -> 开始 -> 数字 -> 格式 -> 短日期、长日期等
-
百分比
- 直接输入:直接在数字后输入百分号
- 设置格式:选中 -> 开始 -> 数字 -> 格式 -> 百分比
-
分数
- 直接输入:0 1/5(五分之一)
- 设置格式:选中 -> 开始 -> 数字 -> 格式 -> 分数
-
长数据
-
直接输入:‘123123123123(文本格式)
-
设置格式:选中 -> 开始 -> 数字 -> 格式 -> 文本
-
文本格式不能计算
-
Excel 数据精度只有15位,多于15位则后面的全都抹成零
-
4) 自定义数字格式
-
三个常见数值占位符:0、#、?(00.00、##.##、??.??)
-
文本占位符:@@(让文本重复出现)@“同学”(文本后面都加”同学“)
-
各区域内容独立定义:0;-0;0;@(正数;负数;零值;文本)
-
;-0;0;@(隐藏正数)0;-0;;@(隐藏0)
-
"正"0;"负"0;“零”;@(独立定义各部分格式)
-
[红色]0;[绿色]-0;0;@(独立定义各部分颜色)
-
按条件定义:[绿色][>=80]0;[红色][<60]0;0(三段,第三段是除了第一二段之外的情况)
-
-
自定义日期格式举例:yy.mmm 周aaa(21.Jan 周五)
(三) 快速整理
1) 窗口视图
- 多工作簿:视图 -> 窗口 -> 切换窗口、全部重排
- 同一工作簿多工作表(影子工作簿):视图 -> 窗口 -> 新建窗口、并排查看
- 冻结窗格、冻结首行、冻结首列:视图 -> 窗口 -> 冻结窗格(冻结当前定位单元格的上边所有行和左边所有列)、冻结首行、冻结首列
- 工作表组:(多个表上同一位置进行相同操作)Ctrl+连续点选选中工作表,在当前工作表输入内容即可(点击任意其他工作表就可以退出工作表组)
- 移动、隐藏工作表、工作表标签颜色:工作表名右击 -> 移动、隐藏、取消隐藏、工作表标签颜色
2) 常规查找
-
可以使用通配符
-
*:任意长度的文本
-
?:一个字长度的文本
-
~:转译字符,对*天和?进行转译
-
-
保存查找结果:设定好查找条件 -> 查找出符合条件的单元格 -> 在查找框里全部选中 -> 左上角名称框命名,如:黄色 -> 当再次点击名称框的黄色名称时,会自动选中所有刚刚查找到的单元格
-
查找结果与运算函数结合使用:使用函数时,参数传入刚命名好的名字,如:=sum(黄色),就能计算出刚刚查询到的所有单元格的和了
3) 快速定位
-
定位到可见单元格:选中区域 -> 开始 -> 查找和选择 -> 转到、定位条件 -> 可见单元格
-
定位到所有图片:
-
方式一:开始 -> 查找和选择 -> 转到、定位条件 -> 对象(矩形、线条、文本框、图片等属于对象)-> Ctrl+点击取消选择不想删除的对象
-
方式二:开始 -> 查找和选择 -> 选择对象 -> 框住所有图片
-
-
定位到所有公式、常量等:开始 -> 查找和选择 -> 转到、定位条件 -> 公式、常量等
-
定位到第一行:Ctrl+上键(双击任一单元格的上边框)
-
定位到最后一行:Ctrl+下键(双击任一单元格的下边框)
-
定位到第一列:Ctrl+左键
-
定位到最右一列:Ctrl+右键
-
Ctrl+Shift+上/下/左/右/键:全部连选
4) 拆分重组
-
提取或重组前列单元格中部分内容
-
方式一:输入多个样例 -> 快捷键Ctrl+e
-
方式二:输入多个样例 -> 单元格右下角( 或开始 -> 填充 ) -> 快速填充选项
-
方式三:输入一个样例 -> 单元格右下角 -> 拖拽或双击(到此填充的是一样的内容) -> 末尾单元格右下角 -> 自动填充选项
-
注意:只能对本列的前列单元格操作,本列左侧不能有无列名的列(无连接),有列名但是所有值都为空的可以(有连接)
-
-
拆分选中列单元格中的内容:数据 -> 数据工具 -> 分列(只能拆分一列)
5) 原位填充
-
操作方法:选中区域 -> 输入内容 -> Ctrl+Enter(选中区域全部填充为所输入的内容)
-
注意点:
-
可以选中整片区域,也可以选中零星的不整片区域
-
常结合定位到空值使用
-
输入内容可以是数据也可以是公式,如果是公式的话(如等于上一行单元格内容),那所有选中区域都依据公式规则来填充(选中区域的值都为其上一行单元格的内容)
-
(四) 快速计算
1) 公式
-
用法:=单元格1+单元格2(通过点选方式选择单元格,在fx编辑框里显示如=L4+G4的形式)
-
相对引用、相对引用:
-
相对引用:行列均不固定(如K4),公式和智能填充结合使用时,公式单元格引用的是相对值
-
绝对引用:(公式和智能填充结合使用时,公式单元格引用的是绝对值)
- 固定列(如$K4)(快捷键F4(笔记本Fn+F4)三下)
- 固定行(K$4)(快捷键F4(笔记本Fn+F4)两下)
- 固定行和列(如$K$4)(快捷键F4(笔记本Fn+F4)一下)
-
2) 所有函数
-
函数用法:
-
方式一:=函数名() -> Tab键进入函数 -> 选择要操作的单元格 -> 确定
-
方式二:=函数名() -> Tab键进入函数 -> 点击fx编辑框左边的fx按钮进入参数面板(或Ctrl+A直接调出参数面板) -> 操作(推荐不熟悉的函数用这个方式)
-
-
找到所有函数:
-
方式一:开始 -> 编辑 -> 自动求和右边下箭头 -> 一些常用函数和其他函数都可以查到
-
方式二:公式 -> 函数库
-
3) sum系列函数
-
sum(全表求和快捷键(包括行求和、列求和、总和):选中区域 -> Alt+=)
-
sum求和会自动略过文本格式的数字而不报错,删除行列时sum会自动适应更改运算结果
-
sumif(对符合条件的部分进行求和,第一个参数是用于条件判断的区域,第二个参数是判断条件,可以是一个条件,也可以是一组条件(此时条件有几个,求和结果就有几个),第三个参数是求和区域)
-
sumifs(多条件求和,第一个参数是求和区域,第二个参数是用于条件判断的区域1,第三个参数是判断条件1,第四个参数是用于条件判断的区域2,第无个参数是判断条件2,…)
-
sumproduct:
-
多数组对应元素相乘再求和,第一个参数是第一个数组,第二个参数是第二个数组,…
-
参数位置可以嵌套函数,如嵌套sumif,这时sumif条件就得是一组条件了,并且个数得与其他sumproduct参数数组的元素个数一样
-
sumproduct嵌套sumif举例:sumproduct(G19:G26,sumif(B18:B44,F19:F26,C18:C43))
-
-
可以使用通配符
-
*:任意长度的文本
-
?:一个字长度的文本
-
~:转译字符,对*和?进行转译
-
4) average系列函数
-
average
-
averageif(对符合条件的部分进行求平均,第一个参数是用于条件判断的区域,第二个参数是判断条件,可以是一个条件,也可以是一组条件(此时条件有几个,求平均结果就有几个),第三个参数是求平均区域)
-
averageifs(多条件求平均,第一个参数是求平均区域,第二个参数是用于条件判断的区域1,第三个参数是判断条件1,第四个参数是用于条件判断的区域2,第无个参数是判断条件2,…)
-
可以使用通配符
-
*:任意长度的文本
-
?:一个字长度的文本
-
~:转译字符,对*和?进行转译
-
5) count系列函数
-
count(只返回数字单元格的数量)
-
counta(返回非空单元格数量)
-
countblank(返回空单元格数量)
-
countif操作数字单元格(返回符合条件的单元格数量)(使用文本连接符&举例:"<"&G47)
-
countif操作文本单元格(返回符合条件的单元格数量)(使用通配符)
-
*:任意长度的文本
-
?:一个字长度的文本
-
~:转译字符,对*和?进行转译
-
-
countifs(多条件)
(五) 数据分析
1) 快速分析
- 选中整个表 -> 右下角快速分析 -> 格式、图表、汇总、表、迷你图
(六) 协作办公
1) 数据验证
-
设置数据验证
-
设置基础条件验证:数据 -> 数据工具 -> 数据验证 -> 设置
-
设置提示信息:数据 -> 数据工具 -> 数据验证 -> 输入信息
-
设置出错警告:数据 -> 数据工具 -> 数据验证 -> 出错警告
-
-
设置下拉列表:选中列 -> 设置 -> 允许(序列) -> 来源(如:农产品,烘焙品,肉类(可以打字,也可以选择任意工作簿中任意工作表中的区域内容))
-
诠释无效数据:对已录入好的区域,设置数据验证之后 -> 再次点击数据 -> 数据工具 -> 数据验证 -> 诠释无效数据
-
注意:数据验证只对手动输入有效,对于复制粘贴无效,复制粘贴会将源单元格的所有格式复制过来,覆盖目标单元格的所有格式和内容
2) 加密
- 保存时,弹出窗口的右下角工具 -> 设置密码
3) 打印标题
- 作用:对于很长的表,打印时每一页都打印出标题
- 操作:页面布局 -> 页面设置 -> 工作表 -> 打印标题