- 实验文件：Excel基本操作数据.zip
01. Excel保护操作
1.1 文件访问密码
1.1.1 给Excel文件添加密码
- 点击文件 >> 信息 >> 保护工作簿 >> 用密码进行加密，然后输入密码 >> 点击确定。
- 接着会需要重新输入一次密码进行密码确认，完成后就成功的给Excel文件添加了密码。
- 此时保存 >> 关闭 >> 重新打开该Excel文件，需要输入密码才可以访问文件了。
1.1.2 删除Excel文件的密码
- 与添加密码一样，还是点击文件 >> 信息 >> 保护工作簿 >> 用密码进行加密。
- 接着删除已经填写的密码，然后点击确定即可。
- 但是有时候一个工作簿已经做好了，就不想再对这个工作簿的结构做出什么改变了，此时就可以对工作簿结构进行保护。
- 实现方式：点击文件 >> 信息 >> 保护工作簿 >> 保护工作簿结构，然后勾选结构 >> 输入密码 >> 点击确定。
- 此时，发现鼠标已经无法拖拽工作表了，并且右键想要插入、删除、重命名工作簿也无法点击了。
- 注意1：这种保护仅保护工作簿的结构，对工作表的修改（如在单元格中插入删除数据，修改行高列宽、背景字字体颜色等）操作不需要输入密码。
-
1.2.2 保护单元格
工作表中有些单元格的内容是不想被人修改的，如下表中第一行和第一列的数据应该是不能被修改的。（该表在实验文件1的1.保护单元格的A15:E19）
- 因此可以选择这张表中可以修改的区域（即B16:E19） >> 右键 >> 设置单元格格式 >> 保护 >> 取消勾选锁定（默认是勾上的） >> 确定。
- 接着右键这张表所在的工作表 >> 保护工作表
- 勾选“保护工作表及锁定的单元格内容” >> 勾选“选定锁定的单元格”、“选定解锁锁定的单元格” >> 输入密码 >> 点击确定按钮即可。
- 此时，只能在表中允许的单元格（即B16:E19）中输入数据，修改别的单元格会出现以下警告。
若想取消保护则只需要右键工作表 >> 撤销工作表保护 >> 输入密码 >> 确定即可，完成后工作表中所有单元格都可以进行修改了。
02. 快速输入数据
2.1 填充柄
2.1.1 填充柄的基本用法
填充柄是Excel中一个用于快速填充数据的工具，主要有下拉、双击两种操作方式。
- 下拉：选中目标单元格，然后将光标移至单元格右下角。当光标由白色十字变成黑色十字时，按住鼠标左键开始拖动（上下左右都可以）即可实现数据填充。
- 双击：选中目标单元格，然后将光标移至单元格右下角。当光标由白色十字变成黑色十字时，双击鼠标左键，即可快速向下填充数据。（格式相同的单元格可实现填充）
2.1.2 默认填充内容与内容更换
- Excel中不同的数据类型在使用填充柄时有着不同的填充方式：
- 文字+数字类型默认以数字自增一的方式填充序列。
- 数字类型、纯文本类型等以复制单元格的方式填充。
- 填充方式可以在填充完成后更改。更改方式为：数据填充完成后，点击右下角的小按钮，选择填充方式。
- 不同的填充方式会出现不同的数据值结果。
2.1.3 填充等差数列
- 等差数列数据的生成有两种方式，其中最常见的是再手动写入一个数字确定步长，然后使用填充柄填充。
- 另一种方式就是用序列填充，方法如下：
2.1.4 填充等比序列
- 等比序列的填充与2.1.3中介绍的填充等差序列的第二种方式基本类似。
- 只需要选中首个数字与需要填充的区域，然后点击序列，接着类型选择等比序列，在步长值中填入比值，最后点击确定即可生成。
- 示例：
2.1.5 按工作日、年、月填充
- 默认情况下，Excel对于”2022-7-25”这类的日期数据默认天递增的形式填充的。
- 但是对于日期数据，Excel的填充方式还提供了“填充工作日”、“以月填充”、“以年填充”。（对于填充方式在2.1.2中有详细讲解）
- 因此，用这种方式可以快速完成下表数据的填充。
- 补充：Excel中所谓的工作室只是剔除了周六周日这两天的双休日，对于像中秋节这种法定节假日的处理Excel其实并不能自动完成。
- 对于节假日的处理，具体还是要参考国家每年在12月发布的关于来年的放假安排。
- 如2023年的放假安排如下：
2.2 利用自定义列表填充
2.2.1 Excel内置的填充列表
- 对于这样的一张表格，Excel可使用填充柄快速的填充数据。
- 填充的数据结果如下：
- 甲、星期一、一月这些数据应该都是无意义的，Excel之所以能对这些数据实现填充，是因为在文件 >> 选项 >> 高级 >> 常规 >> 编辑自定义列表中已经默认存在了这些填充规则。
2.2.2 自定义序列
- 在文件 >> 选项 >> 高级 >> 常规 >> 编辑自定义列表 >> 新序列中可以填写自定义的序列规则，填写完成后点击添加 >> 确定进行保存。
- 此时，对于数据”a”而言，也就可以使用填充柄快速填充了。
- 对于【部门一、部门二、部门三、……】、【赵、钱、孙、李、周、吴、郑、王、……】这种数据序列也可以用这种方式填充。
2.3 多个不连续单元格同数据
- 要给多个不连续的单元格填充相同的数据（效果如下图），有两种方式。
- 方式一：（推荐）
- 先按住Ctrl不放开，然后同时用鼠标左键选择需要的单元格。
- 接着松开Ctrl，然后输入数据。此时数据只会输入到最后选中的单元格中。
- 最后，使用Ctrl + Enter将数据填充到所有单元格中。
- 方式二：（不推荐）
- 先按住Shift + F8不放开，然后同时用鼠标左键选择需要的单元格。
- 后续操作与方式一相同。
方式二不推荐原因：
像性别这种一般来说只有男和女两种数据，所以其实每次都手动输入nan男、nv女挺繁琐的。
- 此时就可以先输入一对男和女，然后右键后续的单元格 >> 从下拉列表中选择 >> 点击需要的值。
- 注意：这种方式只能用来辅助输入，并不会验证数据的正确性；因此在性别这一列里依旧可以输入不知道、人妖、中性等奇奇怪怪的数据。
2.5 数据有效性
2.5.1 数据有效性验证
- 在数据 >> 数据工具 >> 数据验证中存在着数据验证选项。
- 数据验证可以对被设置的单元格进行内容和格式上的限制，主要有那么几类：
- 任何值：不设置任何限制。
- 整数、小数、日期、时间、文本长度：可进行介于、未介于、等于、不等于、大于、小于、大于或等于、小于或等于指定值的限制。
- 序列：以枚举形式罗列出可选择的数据的范围。这种类型弥补了2.4中的缺陷，除了能够快速的帮助填充数据，还可以对数据进行验证。
- 自定义：根据Excel公式对输入的数据进行限制。
2.5.2 手动设置数据范围（以序列为例）
- 性别一般来说只有男女，因此可以给性别这一列添加数据有效性验证。
- 首先，选中需要填充数据的区域。
- 接着，点击数据验证，在设置中将验证条件的：
- 允许：选择序列。
- 来源：输入
男,女，这是一种枚举的形式，多个枚举值之间用,分割。
- 此外，还可以在出错警告栏中设置数据输入错误时的警告信息。
- 设置完成后，点击确定。
- 此时，点击单元格，即可快速选择数据。
- 当然，也可以手动输入数据。但是当输入的数据不在“来源”所枚举的范围中时，会出现告警。
2.5.3 以表中先有的数据作为数据范围（以序列为例）
- “来源”中的数据除了手动指定外，还可以来自于Excel已有的数据。
- 如，可以在表格空白区域协商“财务部”、“产品部”、“营销部”、“市场部”（位于表格的E78:E81）。
- 接着，在数据验证的“来源”中写入
=数据范围，这里即为=$E$78:$E$81。（也可以通过鼠标光标划取）
- 完成后点击确定；此时就可以快速选择数据了，并且手动输入非法数据时会出现告警。
03. 数据导入
3.1 数据导入概述
- 数据分析师日常的数据来源：文件中（一般是
.txt或者.csv文件）、数据中、网络中。 - 为了方便数据处理，需要把其他数据源中的数据导入到Excel中。
本章的实验数据的data目录中存在着adults.txt和seeds.csv两份数据文件。
3.2 加载数据到新的工作表
点击数据 >> 获取和转换数据 >> 获取数据 >> 来自文件 >> 从文本/CSV，接着找到文件，点击导入。
- 可以简单预览一下数据，然后点击加载完成导入。
- 若数据存在问题，可以通过调整文件原始格式、分隔符、数据类型检查来进行简单调整。
- 此时，Excel会自动创建一个新的工作表，里面存储的就是导入进来的数据。
示例中导入的是
.txt文件的数据，用同样的方式也可以导入.csv文件的数据。3.3 加载数据到指定位置
还是点击数据 >> 获取和转换数据 >> 获取数据 >> 来自文件 >> 从文本/CSV，接着找到文件，点击导入。
- 需要注意的是，这时需要点击的是“加载到”而不是“加载”。
- 接着选择现有工作表，并指定数据起始的单元格，完成后点击确定。
- 此时数据就成功导入进来了，并且起始单元格为H14。
3.4 从网页中导入数据
- 现有一个国家统计局的数据网址：http://www.stats.gov.cn/tjsj/pcsj/rkpc/6rp/html/A0101a.htm
- 要将这个网址中的数据导入进来可以点击数据 >> 获取和转换数据 >> 获取数据 >> 自其他源 >> 自网站，接着选择基本 >> 输入网址 >> 点击确定。
- 选择需要导入的表格，完成需要后根据实际情况点击“加载”或者“加载到”完成数据的导入。
04. Excel单元格与数据类型
4.1 单元格概述
4.1.1 单元格的具体表示
- 单元格由确定的列和确定的行定位，其中列的标题是字母，行的标题是数字。
- 因此一个确定的单元格可以表示为：
字母数字- 如
B3表示的就是第2列第3行的单元格。
- 如
- 如在
'4.Excel数据类型'!J2:O13中存在以下数据：
- 此时，使用公式
=L5可以获取到L5单元格中的数据“45781”，并且在纵方向使用填充篇还可以获取L2:L13的数据，横方向上使用填充柄可以获取到J5:O5的数据。
但是若填充柄填充的数据范围超出了原表的范围，Excel也会用一些指定的值进行填充。
4.1.2 锁定单元格
4.1.1 中为什么填充柄可以填充到数据呢？这是因为
L5这个单元格是活动的，因此使用填充柄填充得到的实际上是以下内容：I5、P5、L14、L15这几个单元格存在，但是没有数据，因此使用0填充。L0、L-1单元格声明不合法，因此进行#REF!报错。
- 以
L5单元格为例，一个单元格有L5（不锁定行和列）、$L5（不锁定行但锁定列）、L$5（锁定行但不锁定列）、$L$5（行列都锁定）这四种格式。- 锁定的意思就是指在拖拽时锁定的部分不会发生改变。
- 对于不锁定行和列的情况，实际上得到的就是4.1.1中的结果。
- 对于
$L5这种不锁定行但锁定列的情况：纵方向上与L5相同，横方向上保持不变。（因为行变量可以动，列变量不能动）
- 对于
L$5这种锁定行但不锁定列的情况：纵方向上保持不变，横方向上与L5相同。（因为列变量可以动，行变量不能动）
- 对于
$L$5这种行列都锁定的情况：纵、横方向上都保持不变。（因为行变量和列变量不能动）
4.1.3 相对引用与绝对引用
=L5这样的引用称为相对引用，=$L$5这样的引用称为绝对引用。-
4.2 Excel数据类型
4.2.1 Excel四大基本数据类型
文本类型：如姓名、性别、住址、商品名称等。
- 数值类型：100、0.1、1e5等。
- 逻辑值类型：TRUE、FALSE。
错误值类型：#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NULL!。
4.2.2 错误值类型说明
VALUE!：函数或计算公式错误，主要出现原因如下：
- 公式语法不正确。
- 引用公式带有空字符单元格。
- 运算时带有文本单元格。
- 数据计算未使用正确格式。
DIV/0!：出现在除法运算中，表示分母为0的错误。
NAME?：引用无效的名称导致的表格公式错误，常见的有：
- 公式名称拼写错误。
- 公式中的文本值未添加双引号。
- 区域引用缺少冒号。
- 引用未定义的名称或已定义名称出现拼写错误。
N/A：当表格计算时找不到要求查找的内容，就会出现#N/A错误值。主要原因如下：
- 查找区域不存在查找值。
- 数据类型不匹配。
- 查找数据源引用错误。
- 引用了返回值为#N/A的函数或公式。
REF!：当表格公式中引用了无效的单元格会就会出现#REF!错误，出现这种错误的主要原因如下：
- 表格计算中误删了数据行列。
- 引用的数据中剪切粘贴了其他公式计算出来的单元格。
- 公式中引用了无效区域或参数。
NULL!：在公式中引用单元格区域时，未加正确的区域运算符，产生了空的引用区域，从而导致#NULL!错误。主要原因如下：
同样一种数据类型可以有多种表现形式。
- 如图中所表示的都是2022年11月21日这个日期数据。
4.3.2 设置数据表现形式
- 通过右键数据单元格 >> 设置单元格格式 >> 选择合适的分类 >> 选择需要的类型即可改变数据的表现形式。
- 示例：将2022-11-21这个时间用2022年11月21日这样的形式显示。
4.3.3 自定义数据格式（含参考手册）
- 当Excel中内置的数据格式无法满足实际需要时，还可以自定义数据格式。
- 实现方式：右键单元格 >> 设置单元格格式 >> 分类选择自定义 >> 在类型中输入自定义数据格式的代码 >> 从示例中验证结果 >> 点击确定完成。
- 示例：将“2000/4/5”这个日期数据显示为“00年04月5日 星期三”。
更多自定义数据格式可参考手册文件：Excel 自定义格式.pdf
4.4 自定义数据格式应用案例
4.4.1 案例一：计算总价
在
'4.Excel数据类型'!A7:C8存在以下表格，要求输入单价和数量，即可计算得到总价。
- 首先，在单价中输入20，在数量中输入2，在总价中输入公式
=A8*B8，即可得到以下结果。
- 接着调整自定义单元格格式：20 ->
0.00元；2 ->0个；40 ->0.00元。 - 注意：直接输入
20.00元和2个都是文本类型数据，因此公式=A8*B8会出现#VALUE!错误值。
4.4.2 案例二：表格调整
- 现在有一张表格已经填充完了数据，需要调整格式：
自定义格式代码：
完成如下表格的制作，具体要求：
- 每月每个商品的销售额使用
RANDBETWEEN(Bottom, Top)函数在10~1000内生成。 - 销售额数据保留两位小数，并且格式要求如
￥45.00元。 - 在总计列完成每个商品的销售额求和。
- 单月销售额不足500的用红色标注，大于等于500的用绿色标注。（总计列不用）
- 每月每个商品的销售额使用
- 填充数据：在销售额列用
=RANDBETWEEN(10,1000)生成数据。
- 选中2~5月销售额与总计数据区域，使用ALT+=快捷键求和。
- 选中2~5月销售额数据区域，设置单元格格式为自定义，代码为
[红色][<500]¥0.00"元";[绿色][>=500]¥0.00"元"。
- 选中总计列数据区域，设置单元格格式为自定义，代码为
￥0.00元。
4.5 文本类型与数值类型转换
4.4.1 数值类型转文本类型
- 一个单元格内如果数字过多（一般是长度大于等于12位）时，就会以科学计数法显示。
- 如果要显示所有数字，需要先设置单元格格式为文本，然后再输入数字。
- 也可以在数值前直接加上英文状态的单引号
'，使其变成文本格式。
4.4.2 文本类型&逻辑类型转数值类型
- 前置注意点：
- 只有像
'328791这种纯数值的文本可以转换成数值类型，像sad877yda这种非纯数字的文本不可以转换。 - 逻辑值类型（TRUE、FALSE）也可以转换成数值类型，方式与文本类型一样；其中TRUE会被转换成1，FALSE会被转换成0。
- 只有像
- 转换成数值类型有三种方式：加减零、乘除一、负负运算、函数，其中函数分为：
VALUE()函数，数值文本使用。N()函数，逻辑值使用，用法与VALUE()一致。
示例1：在
A1单元格中存在一段文本'330721199903226789，要求B1中提取处A1单元格的数值。加减零方式，在
B1中输入以下两种中的任意一个：=A1+0=A1-0
乘除一方式，在
B1中输入以下两种中的任意一个：=A1*1=A1/1
负负运算方式，在
B1中输入：=--A1。- 函数方式，在
B1中输入：=VALUE(A1)。
示例2：在
A2单元中存在一个逻辑值FALSE，用函数将其转换成数值类型可以在B2单元格中输入=N(A2)。4.4.3 批量添加/删除单引号
批量添加单引号实现起来其实很简单，只需要先给一个单元格添加单引号，再用格式刷刷掉其他单元格即可。
- 批量删除单引号也是同样的道理，只需要找个没有单引号的单元格，然后用格式刷刷掉有单引号的单元格即可。
4.5 日期数据标准化
4.5.1 常用时间格式
- 右键单元格 >> 设置单元格格式 >> 日期，在类型中可以看到一些时间格式。
- 当单元格格式为日期，并且单元格中的数据按照类型中的示例输入时，Excel会将这个数据识别为一个日期，并按照日期的格式显示，否则像
2019.1.3这样的非标准时间格式数据Excel就不会认为这是一个日期数据。 - 对于非标准时间格式数据，若要从中提取出日期，则需要经过一定的处理。
补充：以2012年3月14日为例，Excel在默认情况下会显示为
2012/3/14。4.5.2 VALUE()提取文本格式的日期
在
'7.日期数据标准化'!D1:E14表中存在一些看似是日期的文本数据。
- 可以先将目标单元格格式设置为日期，然后这些日期文本也可以用
VALUE()函数提取出来。
4.5.3 查找替换提取日期
- 像
2019.1.3这样的数据，可以使用查找替换的方式提取日期。 - 实现方式就是将这个数据中的所有
.都替换成/，因为2019/1/3对Excel来说是一个标准的日期数据。
注意：这种方式的本质实际上就是把一个非标准的时间数据凑成标准的时间数据。
4.5.4 TEXT()+自定义格式处理
100102这种时间数据也是很常见的，它代表着2010/1/2，但是Excel却并不直接识别这种数据。- 提取方式：
- 先用
=TEXT(100102, "##-00-00")转换成10-01-02（此时得到的是一个文本类型数据）。 - 然后再用负负运算
=--TEXT(100102, "##-00-00")将文本类型数据转换成数值类型数据，即得到40180。 - 最后，将单元格格式设置为日期，即可得到日期数据，即
2010/1/2。
- 先用
4.5.5 分列操作提取日期数据
- 有些文本日期数据，如
'2019/10/1它本质上是个文本，不是个日期。 - 这种数据可以用拆分的方式将日期数据提取出来：
- 选中原始数据区域，点击数据菜单中的分列功能。
- 前两步都不用调整，默认下一步即可。（因为这里的本质是提取数据，而不是拆分数据）
- 第三步有两处需要设置的，完成后点击完成即可。
- 设置列数据格式：选择日期，选择年、月、日的排列顺序。因为数据是
'2019/10/1，对应顺序为年月日，即选择YMD。 - 选定目标区域：选定输出位置的首个单元格。
- 设置列数据格式：选择日期，选择年、月、日的排列顺序。因为数据是
- 在
N2:N6区域内即可得到日期数据。
05. Excel其余操作
5.1 求和操作
5.1.1 Alt+=快速求和
- 快捷键Alt+=是用于快速求和的。
- 对于一般的求和，可以直接使用
+加法运算来实现。
- 当然，也可以使用
SUM()函数实现求和。
- 当然，也可以选中数据区域和输出结果的目标单元格，然后按下快捷键
Alt+=实现快速求和。
- 处理这种数据也很简单，只需要先选择第一块区域。接着按住Ctrl不放开，并同时选择第二块区域。最后使用Alt+=实现求和。
5.2 填充操作
5.2.1 需求描述
- 下图表格中存在三个空的单元格。
![}}2852$V3QMJL}N4GT`]LRD.png](/uploads/projects/u2200239@ng1c58/ffe5c309fca5d6a4884c77fadb4677af.png)
-
5.2.2 使用Ctrl+Enter实现填充（填充少量单元格）
快捷键Ctrl+Enter用于填充数据。
- 如上面这个场景，可以先选中所有的空白单元格，然后输入100（数据会默认输入到最后选中的那个单元格中），最后按下Ctrl+Enter实现填充。
- 这种场景可以看出的明显问题是，选择空单元格是手动选择的。
当空单元格的数量很多，且是不规则的分布在数据集中时，手动选择是一件效率、容错率都很低的事情。
5.2.3 定位条件配合Ctrl+Enter实现填充（填充大量单元格）
使用定位条件可以很好的解决5.2.2最后所描述的问题。
- 首先选中整个数据区域。
- 然后点击开始菜单 >> 查找和选择 >> 定位条件 >> 选择空值 >> 点击确定。
- 接着Excel会帮助我们自动的找出目标区域中所有的空白单元格，然后后续的操作与5.2.2中完全一致。
- 先手动填写一个数据。（此时默认会填写在第一个空白单元格内）
- 然后按下Ctrl+Enter，实现对其余单元格的填充。
![O}I98WN[`SKE]70IT@87]NK.png](/uploads/projects/u2200239@ng1c58/d1a891109bdecbfc46b5522b6ee0b1b9.png)
5.3 快速选择区域
5.3.1 Ctrl+Shift+方向键快速选择一片区域
- 使用快捷键
Ctrl + Shift + 方向键可以快速选择一片区域。（一片区域指的是指定方向上所有同类型的单元格的集合）
快速区域选择的暂停点是一组同种类型单元格的边缘，若要继续选择同方向上的下一个区域（如继续选择右边），则继续按快捷键
Ctrl + Shift + →即可。5.3.2 Shift+方向键选择相邻行/列
快捷键
Ctrl + Shift + 方向键可以快速选择一片区域，有时候可能不需要选择那么大范围的区域，那么Ctrl + Shift + 方向键其实也不那么适用。- 快捷键
Shift + 方向键可以在当前的基础上向指定方向选择一行/一列区域。
5.4 冻结单元格
若首行是标题，且数据字段特别多时，固定首行可以清楚的知道每个数据所代表的是什么。
5.4.2 取消冻结单元格
当工作表中已经有被冻结的单元格时，点击视图菜单 >> 冻结窗格 >> 取消冻结窗格可以解除所有行和列锁定，以滚动整个工作表。
5.4.3 冻结首列
点击视图菜单 >> 冻结窗格 >> 冻结首列，此时左右滑动工作表，首列的数据不会滚动。
-
5.4.4 自定义冻结窗格
选择需要冻结的区域，点击视图菜单 >> 冻结窗格 >> 冻结窗格可实现自定义冻结窗格。
- 自定义冻结窗格可分为两种情况：冻结一行窗格、冻结一列或者一块二维区域。
- 冻结一行窗格：
- 这种情况会冻结第一行到
行，列上不会冻结。 - 如选中第8行数据，点击视图菜单 >> 冻结窗格 >> 冻结窗格会冻结第1行~第7行，列不冻结。
- 这种情况会冻结第一行到
若有收获，就点个赞吧
0 人点赞
