Excel数据分析：基础操作

Excel可以存储10w行，100M的数据，作为数据分析esp三件套的大哥，其中的知识点较为杂乱，因此xue微整理了一下常用的知识点，简单记录下~

1、导入数据

数据 → 新建查询 → 从文件（选择对应格式的文件即可）

保存后不会改变原有数据（若原有表格删除，Excel也会删除；若原有表格更新，可表设计刷新 Excel 表格）

2、文件加密

文件菜单 → 信息 → 保护工作簿 → 用密码加密 → 设置密码 → 确定

3、保护单元格

1）保护工作表

审阅 → 工作表保护

2）允许编辑部分

第一步：选中 → Ctrl + 1 调出单元格格式设置 → 保护 → 锁定前面的勾选去掉 → 确定

第二步：审阅 → 选择保护工作表（默认选项直接确定）

4、冻结窗格

锁定标题行：视图 → 窗口 → 冻结窗格 → 冻结首行

锁定首列：视图 → 窗口 → 冻结窗格 → 冻结首列

锁定定位单元格行列：视图 → 窗口 → 冻结窗格 → 冻结窗格

5、数据格式（单元格格式）

5.1  类型

文本格式：默认靠左

数值格式：默认靠右

日期格式：默认靠右（其本质是数值格式）

5.2  格式设置

1）保留原格式增加单位

单元格格式 → 数字选项卡 → 自定义 → 类型输入【##"单位"】

2）数值区间颜色设置

单元格格式 → 数字选项卡 → 自定义 → 类型输入【[颜色][规则]】

例：将 ≥5 设置蓝色，＜5 设置红色输入为：[蓝色][>=5];[红色][<5]

3）数值保留两位小数且负值标红

单元格格式 → 数字选项卡 → 自定义 → 类型输入【00.00;[红色]"-"00】

5.3  格式转换

1）文本批量去除引用的单引号'

使用空单元格的格式批量格式刷

2）在单元格内输入01

先输入一个英文引号'，再输入数字，如【'01】

3）长数字按科学计数法显示

① 在数字最前输入英文引号变为文本格式，如【'123123123123123】

② 录入之前将单元格格式设置为文本类型

4）以文本储存的数值转换为数值

① 输入公式：【=1*文本】

② 输入公式：【=value(文本)】

5）TRUE/FALSE转换为数值

输入公式：【=1*选中】

6）文本格式日期转换为日期

① 标准日期格式的文本：输入公式【=value(文本)】→ 单元格格式选择为日期

② 不标准的格式如230101：设置格式 → 自定义 → 类型输入【2000-00-00】即可将格式转换为2023-01-01

7）数字固定位数

在选定区域右键 → 设置单元格格式 → 数字选项卡 → 自定义 → 类型输入【n个0】，即可将选中区域的数字改为n位数字，不足位的数字左边补0

8）隐藏单元格内所有的值

选中单元格右键 → 设置单元格格式 → 数字选项卡 → 自定义 → 类型输入【;;;】→ 确定

9）公式复制时只粘贴值，不粘贴公式

复制 → 目标单元格右键 → 选择性粘贴 → 数值 → 确认

6、数据类型

错误值：

#VALUE!（文本与数值进行运算）

DIV/0!（两数相除，分母为零）

#NAME!（公式名称错误）

#N/A（查找值不存在）

#REF!（所引用单元格被删除）

#NULL!（两数组无交集）

7、数据验证

1）填写单元格时设置下拉菜单

提前在其他的空白单元格内输入选项

选中单元格范围 → 数据菜单 → 数据验证 → 设置选项卡 → 验证条件【允许选序列，来源选范围】→ 确定

2）圈示无效数据

选中 → 数据 → 数据验证 → 设置/圈示无效数据

3）防止重复录入数据

选取防止重复录入的区域 → 数据 → 数据验证 → 设置选项卡 → 验证条件【允许选择自定义】 → 输入公式【= COUNTIF(A:A,A1) = 1】，之后再输入重复值会有报错（其中公式的意思是A列中等于A1的个数为1）

4）禁止输入区域设置

选中区域 → 数据验证 → 自定义 → 公式【0】

8、条件格式

开始 → 样式 → 条件格式

1）突出显示

突出显示重复值，数值范围值，包含文本值等

2）最前/最后规则

前N、后N的数值

3）数据条

以带颜色的数据条展示数值的大小

9、排序筛选

开始 → 编辑 → 排序与筛选

快捷筛选：Ctrl + Shift + L 进入筛选模式

高级筛选

注意：条件区域选择必须带表头，同一行表示且，不同行表示或

1）筛选出不重复值列表

数据 → 排序和筛选 → 高级 → 勾选将筛选结果复制到其他位置 → 列表区【需筛选的列】 → 条件区域【空即可】 → 复制到【复制目标区域】 → 勾选选择不重复的记录 → 确定

2）筛选多条件数据-且：筛选出部门为一车间，科目为邮寄费的数据

复制条件 → 数据 → 排序和筛选 → 高级 → 勾选将筛选结果复制到其他位置 → 列表区【全部数据区域】 → 条件区域【条件区域】 → 复制到【复制目标区域】 → 确定

3）筛选多条件数据-或：筛选出部门为一车间，或科目为邮寄费的数据

4）筛选多条件数据-或：筛选一车间或发生额大于3000的二车间或发生额大于10000的数据

5）筛选成本大于金额的数据

添加辅助，公式【=G3>F3】

注意：名称不能和表头一样

10、查找替换

开始 → 编辑 → 查找和选择

1）快速查找

Ctrl + F 弹出查找窗口，输入查找内容，再点击【选项】可以更改范围、查找范围等选项，可以查找批注、公式或者单元格本身内容

2）快速定位

Ctrl + G 弹出定位窗口，可以通过定位条件来进行定位

例：删除空白行

选择需要删除的区域 → Ctrl + G → 定位条件 → 选择【空值】 → 删除 → 删除工作表行

3）快速替换

Ctrl + H 跳出替换窗口，填写查找内容与替换内容即可

例：小数取整数（不进行四舍五入，使用替换实现）

选择需要更改的区域 → Ctrl + H → 查找内容处输入【.*】，替换为处不需要填写，然后点击全部调换，即可去掉数据小数点后面的数字

11、分列

例：有 A-001 这样一列数据，需要把数字/字母单独成一列

① 选中数据 → 数据工具 → 分列 → 分隔符号 → 分隔符号其他输入【分隔的自定义符号，这里为 - 】 → 完成

② 手动填好第一个，然后点数据菜单 → 快速填充（快捷键：Ctrl+E），即可智能填充

12、分类汇总

数据 → 分级显示 → 分类汇总

13、快速计算

1）整列乘 1.5

空白单元格输入1.5 → 复制 → 选中数值部分 → 右键 → 选择性粘贴 → 运算选择【乘】→ 确定

2）将金额转换为万元显示

在一个空白单元格内输入10000，设置为数字格式 ，复制

选则要修改的金额 → 右键 → 选择性粘贴 → 运算选择【除】→ 确定

14、快速填充

1）序列填充

填充柄【鼠标右键下拉】→ 选择填充方式【序列】

2）批量填充

Ctrl + Enter：多个单元格填充相同的数据，可以选中任意区域，先在一个单元格内输入，然后按下快捷键，即可在选中区域填入相同的数据

3）取消合并后，快速填充空单元格内容

选中区域 → 定位条件 → 空值 → 输入公式【=↑】Ctrl + 回车

4）快速输入当前日期和时间

① 输入当前日期：【ctrl + ; 】

② 输入当前时间：【ctrl + shift + ; 】

5）按月补充日期

第一个单元格是日期时，正常拖动数据，下面的单元格不会顺延，若想让日期顺延，可以

① 拖动一两格后，选右下角的以月填充后再继续拖动

② 全拖之后再选择以月填充

6）快速求和

Alt + =：选中数据区域及空白区域（用于填写求和后的数据）然后 Alt + = 即可将求和结果填入空白单元格内

15、快捷操作

1）全选表格内容

Ctrl + A：定位数据表中的任一单元格，然后 Ctrl + A 即可选中全部数据

2）快速撤销/恢复

Ctrl + Z/Y：撤销操作与恢复操作

3）快速移动

Ctrl + 方向键：当数据量比较多时，鼠标滚动不方便，按住一个单元格，然后按 Ctrl + 方向键 即可到达最后一个数字，比如按↓就会到达本列的最后一个数

4）快速框选

Ctrl + Shift + 方向键：从当前选中单元格开始快速框选到存在数据的最后一个单元格

【Ctrl + Shift + →】 快速选中单元格右边内容

【Ctrl + Shift + ↓】 快速选中单元格下方内容

Shift + 双击边线：快速选中双击边线方向的连续单元格内容

5）插入行列

Ctrl + Shift + 加号：选择一行或者一列，按住快捷键，可以快速加入一行或者一列

6）删除行列

Ctrl + Shift + 减号：选择一行或者一列，按下快捷键，即可快速删除

7）快速插入多行空白行

鼠标选中要插入的行，鼠标放在行旁边，显示实心十字的时候，按住Shift下拉，就会插入多行空白行

8）互换两行、列的内容

选中行或者列，光标放到边线上，当光标变为十字箭头时，按住 Shift 拖拽

9）自动生成下拉列表

Alt + ↓ ：空白单元格点击【Alt + ↓】即可生成已输入过的所有数据的下拉列表

10）快速美化表格

Ctrl + T：选择任意单元格，按住Ctrl + T，弹出的窗口中可以选择美化范围，直接点击确定后便会生成一个美化后的表格，也可以通过【设计】选项卡进行进一步的美化

11）快速分析

Ctrl +Q：选中需要分析的数据区域，按下快捷键，可以根据窗口提示选择合适的功能模块对数字进行分析

12）快速复制公式

直接拖动，或者直接双击实心加号

13）快速调整最适合列宽

选择所有的列，任一列的边线双击

14）插入批注

Shift + F2：选中需要添加的单元格，按 Shift + F2 键

15）快速关闭所有Excel文件

按住Shift，选择其中一个Excel文件点击关闭

16、其他

1）仅复制可见单元格

查找和选择 → 定位条件 → 勾选可见单元格

2）单元格内换行

Alt + Enter 即可实现单元格内换行

3）隔行插入1空行

如图所示，需要将分数列的数值插入至每个姓名的下方

① 方法一

插入空列（辅助列C），第一个输入1，第二个空，以此复制全列

选中三列数据，定位条件选择常量，确定

右击，选择插入，选择活动单元格下移

选中分数列数据，复制，定位在第一个姓名下的空单元格位置，选择性粘贴，选择跳过空单元格

② 方法二

插入空列（复制列D），在D2:D4输入1-4等差数列，在D5:D8输入1.5-4.5等差数列

对D列进行升序排序

选中分数列数据，复制，定位在第一个姓名下的空单元格位置，选择性粘贴，选择跳过空单元格

4）删除重复值

选择包含重复值的单元格区域 → 选择数据按钮 → 删除重复值 → 全选，对所有字段进行删除

5）隐藏0值

选择文件选项 → 高级 → 此工作表的显示选项 → 在具有零值的单元格中显示零 → 去掉勾选

6）文字跨列居中

选取需要跨的列，右键选择设置单元格格式，选择对齐选项卡 → 水平对齐 → 跨列居中 → 确定

7）合并多个单元格的内容

选择想要合并的区域 → 开始菜单 → 填充 → 内容重排

注意把列宽改为合适的长度

8）合并相同内容的单元格

思路：使用分类汇总工具，将不同的数据行分开

① 将需合并的所属区域列按相同内容进行排序 → 数据选项卡 → 分类汇总 → 按所属区域分类，（可选）汇总方式选择计数 → 确定

② 此时同类值下都有一行分类标签

③ 从A2开始选中A列数据区域 → 开始 → 查找和替换 → 定位条件 → 空值 → 确定 → 合并单元格

④ 删除分类汇总：数据选项卡 → 分类汇总 → 全部删除

⑤ 将A列格式刷到B列：方法1-使用格式刷，方法2-复制粘贴格式

9）同时查看一个Excel文件的两个工作表

选择视图菜单 → 新建窗口，此时会出现两个窗口，然后选择【全部重排】可以将两个窗口平铺至桌面

10）同时修改两个sheet中相同位置的值

按住 Shift 选择多个工作表，在其中一个中进行修改即可修改全部列表此处的值

11）恢复未保存文件

文件菜单 → 选项 → 保存 → 保存工作簿【自动恢复文件位置】有文件保存路径，缓存设置可以调整文件缓存时间