Excel基础学习（2013及以上版本）
（教学视频点这里）
文章目录
- Excel基础学习（2013及以上版本）
- (一) 快速录入
- 1) 切换单元格
- 2) 爬取网页上的表格
- 3) 清洗爬取的数据
- (二) 显示格式
- 1) 单元格格式
- 2) 表格格式
- 3) 特殊数字格式
- 4) 自定义数字格式
- (三) 快速整理
- 1) 窗口视图
- 2) 常规查找
- 3) 快速定位
- 4) 拆分重组
- 5) 原位填充
- (四) 快速计算
- 1) 公式
- 2) 所有函数
- 3) sum系列函数
- 4) average系列函数
- 5) count系列函数
- (五) 数据分析
- 1) 快速分析
- (六) 协作办公
- 1) 数据验证
- 2) 加密
- 3) 打印标题
(一) 快速录入
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) 打印标题
- 作用：对于很长的表，打印时每一页都打印出标题
- 操作：页面布局 -> 页面设置 -> 工作表 -> 打印标题
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
