王佩丰 Excel 基础24讲 | 学习笔记（全）

第一讲：认识Excel

1.简介

excel能做什么？

• 数据存储 → 数据处理 → 数据分析 → 数据呈现

excel界面

补充：Excel数据分析步骤
①提出问题：明确自己需要通过数据分析解决什么问题
②理解数据：理解数据各个字段的含义
③数据清洗 

这时候就需要用到Excel了
首先要对数据进行一次大清洗，将多余重复的数据筛选清除，将缺失数据补充完整，将错误数据纠正或删除
这时要用到一系列函数

• 数据重复：countif
• 缺失数据：if，and，or，嵌套函数等
• 数据抽样：left，right，concatenate，vlookup
• 数据计算：average，sum，max，min，date，if
• 数据分组：vlookup，近似匹配，search
• 数据抽样：rand

④构建模型：运用函数，数据透视表，分析工具库（描述统计分析） 

⑤数据可视化

根据数据关系选择图表

• 成分：饼图，柱形图，条形图，瀑布图
• 排序：柱形图，条形图，气泡图，帕累托图
• 时间序列：折线图，柱形图
• 相关性：散点图，柱形图，对称条形图（旋风图），散点图，气泡图
• 多重数据比较：雷达图

图表注意事项

• 信息完整：图表标题，单位，图例，脚注，来源等
• 避免无意义的图表
• 一表反映一个观点
• 只选对的不选复杂的图表
• 标题一句话阐述清楚反映观点

2.一些重要概念

几种常用文件类型

• XLS工作簿文件：2003之前的格式
• XLSX 工作簿文件：2007到2010之后新版本的文件格式
• XLW工作区文件：类似快捷方式，数据随着原文件变动，可进入【视图 - 新建窗口】熟悉该选项卡

工作簿，工作表，单元格

• 新建工作表：点击右侧小加号，双击重命名
• 更改工作表标签颜色：右键
• 交换两列数据的位置：选中目标列 → 按住shift键 → 拖动目标列至目标位置
• 插入/删除多个工作表：左键选中第一个工作表，按住shift，选中最后一个工作表，右键插入/删除
• 插入多行/列：选中n行/n列，右键插入
• 交换两列数据位置：选中目标列，鼠标放置右边线处，按住shift键拖动目标列至目标位置
• 调整行高列宽：选中多列 → 鼠标放置右边线双击 → 自动调整多列宽度；选择多列 → 手动调整列宽度（也可以同时调整多行宽度，并且都是被一致调整的）
• 框选多行：名称框中输入（起始行：目标行）

3.使用小工具，冻结窗格，填充柄，查找和替换

冻结窗格

• 定义：依据当前被选中单元格锁定表格行与列，滚屏时，被冻结的标题行总显示在上面，标题列显示在左面
• 位置：选项卡 → 视图 → 冻结窗格
• 冻结首行：视图 - 冻结窗格 - 冻结首行（即可实现滚动表格，而首行不动）
• 冻结多行：选择下一行单元格（以冻结前3行为例，选中第4行首个单元格，选择冻结拆分窗格命令）
• 同时冻结行与列：选择交界处单元格，冻结窗格。选中某一单元格，选择冻结拆分窗格命令，被选中的单元格符合以下规律：总是冻结被选中单元格上方和左侧的单元格，下方和右侧单元格是可变动范围

填充柄

• 定义：鼠标放置在单元格右下方变成黑色实心加号拖拽自动填充单元格
• 用法：选中n个单元格 → 加号拖拽。
• 拖拽规律为序列填充，按住Ctrl再拖拽可实现复制填充
• 自定义填充顺序：文件 - 选项 - 高级 - 编辑自定义列表 - 输入序列添加
• 其它格式拖拽：选中单元格，移动至右下角按住右键拖拽会弹出填充快捷菜单，可以选择想要的填充格式

小技巧

• 快速到达表格最前&最后：选中单元格，在表格的上边线双击到达表格最前，表格下边线双击到达表格最后
• 显示今天日期快捷键：Ctrl + : ，直接向下拖拽默认序列拖拽

第二讲：Excel单元格格式设置

1.使用单元格格式工具美化表格

“设置单元格格式”对话框在哪里？

• 选中单元格 - 右键 - 设置单元格格式
• 开始选项卡 - 单元格功能区

合并单元格

• 多个小单元格合并成一个大单元格，常用于标题
• 选中多个单元格 - 开始选项卡 - 合并后居中
• 合并多行：选中多个单元格 - 开始选项卡 - 跨行合并（避免用上述操作合并3次）

边框线

• 若不设置，打印不会有边框
• 选中整个表格区域的单元格 → 开始选项卡 → 边框 → 选择所有框线
• 如需设置边框格式，选中单元格区域后，右击选择设置单元格格式

斜线

• 设置斜线：在单元格内右键 - 设置单元格格式 - 边框 - 选择斜线
• 斜线双表头（通过双行文字实现）：表头1单元格设置斜线 - 双击单元格 - 写表头2 - alt + enter - 左右对齐，空格设置格式 - 回车
• 为什么应用空格调整而不直接设置右对齐和左对齐？因为对齐是针对整个单元格内容，不能同时设置一行左对齐，另一行右对齐
• 如果需要三个斜线：插入 - 形状 - 直线 - 鼠标绘制斜线
• 填充文字：通过双航文字实现，回车（alt + enter）分行，文字左对齐，第一行输入空格将文字右对齐

格式刷

• 先在已经完成格式设置的表格里应用格式刷，可以刷到另一个新表格，就会应用一样的格式
• 双击格式刷按钮，可以保持格式刷状态，刷新多个单元格的格式，直到按下esc退出

2.单元格数据格式

定义：在Excel当中，会去掉无意义的数字格式。比如，输入2400.00，会直接省略成2400，输入007，会变成7。所以需要设置单元格数字格式

路径：选中单元格 - 右键设置单元格格式 - 可以设置不同的数字类别（但数值永远是数值，只是显示格式改变了）

如果一组数字是文本格式，那么这组数字不能运算，但仍然可以把它改成数字：选中文本格式的数字，然后这些文本格式数字的左上角会出现黄色棱形感叹号，点击这个感叹号，选择“转换成数字”，就可以转换成数字了

自定义数字格式：可以实现在所有数值前面或后面加内容，如在数值后加“元”字，并且这些数值还可以做相加等运算（因为设置格式不改变值）

3.使用“分列”工具

将一组txt内容复制黏贴到Excel表格，数据会全部在A列，此时找到数据选项卡 - 分列，根据实际情况选择通过【分隔符号】或者【固定宽度】分列

一般是通过【分隔符号】来分列，找到数据里可以用来分隔的符号

如果一次分列没有完成，可以再次找到能够分隔的符号，进行二次分列

日期是以文本格式输入的，无法通过“设置单元格格式”改为其他格式，可以通过分列的第3步修改列数据格式，修改成常规或者日期，修改成功后就可以修改格式了

第三讲：查找，替换与定位

1.查找与替换

快捷键：查找Ctrl + F ，替换Ctrl + H

补充：替换选项卡里有 “选项” 功能，可选择 “单元格匹配”，避免一些不必要的替换

按值查找

• 开始选项卡 - 查找和选择
• 替换选项卡里有“选项”功能，选择“单元格匹配”，避免一些不必要的替换

按格式查找

• 如按颜色查找替换：格式按钮 - 填充 - 选择颜色

模糊查找

• 认识通配符：* 和 ?都是通配符
• * 指的是任意一个或多个值
• ?（英文的半角问号）指的是一个模糊字
• 注意：像 “张?” 替换成 “经理的亲戚” ，这样 “张三” 也会被替换成 “经理的亲戚三” 。此时需要开启单元格匹配，张?表示一个模糊字，这样单元格内 “张三三” 就不会被替换了。用 ? 时，常常需要勾选单元格匹配，为了限制字符
• 规避通配符的作用，用波浪线 ~

2.定位工具

通过名称框定位单元格及区域位置

• 如在名称框输入A900：B1000即可选中该单元格区域
• 在名称框输入100:200即可选中100到200整行

定义名称

• 为某个单元格区域自定义名称，以后可以直接使用这个名称查找，通过输入名称，或运用 “查找和替换” - “转到”
• 选择某个区域，然后在名称框中输入一个名字，然后回车，这个名字就被保留了下来，下一次就可以比较容易找到

定位条件

• 查找和替换 - 转到 - 定位条件
• 查找和替换 - 定位条件

使用定位条件解决以下问题

• 为有批注的单元格设置红色填充色：查找和替换 - 定位条件里选择批注 - 选中 - 可以自行修改颜色
• 为有公式的单元格设置红色填充色：查找和替换 - 定位条件里选择公式 - 选中 - 可以自行修改颜色
• 填充解除单元格合并后遗留的空白单元格：查找和替换 - 定位条件里面选择空值 - 选中 - 输入 - 并按下上方向键 - 全部填充快捷键（Ctrl + enter）
• 批量删除图片：查找和替换 - 定位条件里选择对象 - 选中 - 按删除键删除，如果没有图片，执行上述操作后Excel会警告 “找不到对象”，除此之外，在查找和替换里找到 “选择对象”，也可以只框选图片

批注

• 标志：右上角红色小三角
• 插入批注：选中单元格 - 右键 - 插入批注 - 编辑文字
• 显示/隐藏单个批注：选中单元格 - 右键 - 显示隐藏批注
• 显示所有批注：审阅选项卡 - 显示所有批注
• 删除所有批注：选中区域，右键删除批注
• 更改批注框形状：插入 - 形状 - 选择目标形状
• 在批注中插入图片：设置批注格式 - 颜色与线条 - 填充 - 颜色 - 填充效果 - 图片

取消合并单元格后空白内容处理

• 选中区域 - 定位条件：空值 → 在一个空单元格内输入 “=” 
• 若填充与上/下方相同的内容，则按下上方向键，然后软回车（Ctrl + enter）
• 若填充其它内容，则手动输入再软回车
• 选中一个区域，在一个单元格输入数据，按下软回车，则该区域所有单元格都被填上相同的数据

第四讲：排序与筛选

1.排序

注意：执行排序操作的时候避免选中某列，如果以当前选中的区域排序会导致数据错乱，可以选择全部数据或者这一列中的某个单元格

简单排序

• 开始 - 排序和筛选 - 升序/降序

多条件排序

• 如将语数外成绩排序，当数学成绩一样时按照语文成绩排，语文成绩一样时按英语成绩排：排序和筛选 - 自定义排序 - 添加排序依据
• 也可以分别进行单列排序：先依次排次要关键字，再排主要关键字（先排英文，再排语文）

按颜色排序

• 排序和筛选 - 自定义排序 - 添加排序依据 - 单元格颜色

自定义排序次序

• 如果要排序的值是文字，默认按照拼音首字母排序
• 如果要改变，那么选择次序为自定义序列

利用排序插入行制作工资条（格式：表头 + 个人信息）

• 假设第一行是工资表表头，下方11行是员工个人信息
• 再复制10行工资表表头
• 在第一行表头旁的空白列输入0，在员工个人信息输入1-11
• 其它10行表头输入1.5-10.5
• 最后将数字按照升序排序即可

打印要求每页都有表头

• 页面布局 - 页面设置 - 工作表 - 设置顶端标题行 - 选中第一行 - 打印
• 设置后，第一行名称栏会显示print titles

2.筛选

使用筛选

• 点击筛选区域的任意单元格 - 排序和筛选 - 筛选 - 第一行出现下拉箭头
• 筛选完了，让原本所有数据都出现，在下拉箭头勾选 “全选”
• 想要把筛选出来的数据复制到另一个表中去，却发现粘贴了原来的整个表，解决方法：筛选完成后 - 查找和选择 - 定位条件 - 可见单元格 - 复制

在筛选中使用多个条件

• 数字筛选：根据大于/小于/等于等条件来筛选
• 同时筛选多列：分别进行筛选，在上一列的筛选结果中再筛选
• 文本筛选：输入开头/结尾能匹配的关键词

高级筛选

• 筛选不重复值：数据选项卡 - 高级筛选 - 可选择在原区域/其它位置显示筛选结果 - 选中列表区域 - 选中条件区域（没有就不动）- 选中复制到哪个单元格 - 勾选不重复的记录。应用场景为找出不重复的数量
• 使用常量条件区域：条件区域指的是在数据中找到符合条件的需要另外输出条件，且（与）的条件写在同一行，或的条件错开位置，条件也可以输入大于小于

第五讲：分类汇总与数据有效性

1.分类汇总工具

认识分类汇总

• 数据选项卡 - 分类汇总
• 按什么分类，汇总什么，怎么汇总

使用分类汇总前先排序

• 选中要分类所属列的某个单元格 - 点击【开始 - 排序和筛选】或【数据】里的【升序】或【降序】- 完成排序操作
• 点击【数据】里的【分类汇总】，选择分类字段（即根据哪一项进行分类），汇总项（即对哪一项进行汇总）汇总方式（可对汇总项进行求和，计数，求平均值等操作）

分类汇总的嵌套

• 根据多个字段进行分类汇总，即依次根据不同的字段进行重复操作
• 选择主要关键字，次要关键字进行排序
• 依次对主要关键字为分类字段先进行分类汇总，第二次起注意取消 “替换当前分类” 的勾选
• 可以通过点击工作表左上角的【1,2,3,4】来查看结果

复制分类