Excel数据分析基础入门篇（三）

※食用指南：文章内容为‘PAPAY电脑教室—Excel基础教学’视频学习笔记，旨在夯实已掌握的Excel基础

※需要表格数据请私信领取

PAPAY电脑教室—Excel基础教学https://www.youtube.com/playlist?list=PL7enJ2-v6SPm-EHMuRMCG7R7C-vXQugNMhttps://www.youtube.com/playlist?list=PL7enJ2-v6SPm-EHMuRMCG7R7C-vXQugNMhttps://www.youtube.com/playlist?list=PL7enJ2-v6SPm-EHMuRMCG7R7C-vXQugNM

目录

二十一：自定义单元格格式

1、四舍五入（#）

2、员工ID（00000）

3、统一添加

4、千分位设置

5、正负值

二十二、日期函数、年资与工时计算

1、日期数字转文字

2、周几、星期几

3、日期快捷键

4、计算入职以来的时间

二十三、使用RANK函数进行排名

1、直接排序

2、RANK.EQ

3、RANK.AVG

4、RANK.EQ（排序）

二十四、LEFT/MID/RIGHT截取单元格文字

1、LEFT

2、RIGHT

3、MID

4、FIND

5、LEN

6、简单练习

二十五、INDEX&MATCH

1、HLOOKUP

2、INDEX、MATCH

3、简单练习

二十六、表格加密保护

1、保护工作表不被查看、修改

2、保护工作簿不被查看、修改

3、开放部分权限

4、以文件加密

二十七、重复数据处理

1、标亮重复

2、删除重复

3、防止重复

二十八、RAND/RANDBETWEEN随机函数

1、RANDBETWEEN

2、CHOOSE

3、RAND

4、情景模拟

二十九、进度追踪表

1、表格美化

2、添加设置复选框

3、添加设置图标

4、工作进度

5、最终效果

三十、甘特图

1、补充并表格美化

2、插入并优化图表

3、最终效果

二十一：自定义单元格格式

1、四舍五入（#）

代表一个位数的预留位置

82.5四舍五入为83

2、员工ID（00000）

①5位数的员工ID

②如要增加文字，需要加双引号

3、统一添加

① @：文字预留位置

② *：重复指定的符号

4、千分位设置

5、正负值

“_)”:底线（可理解为空格）

❗连续输入三个；隐藏单元格的全部内容，恢复显示把格式改为”常规“就可以

综上

二十二、日期函数、年资与工时计算

1、日期数字转文字

2、周几、星期几

三个a

四个a

3、日期快捷键

已经过的时间

4、计算入职以来的时间

日、年、月、几年又几月（单元格格式要设定为：常规）

=DATEDIF(开始时间，结束时间，计算单位)

5、轮班天数

①=NETWORKDAYS(开始时间，结束时间，假日)

②=NETWORKDAYS.INTL(开始时间，结束时间，自订周末，假日)①=NETWA

二十三、使用RANK函数进行排名

1、直接排序

数据顺序会被打乱

2、RANK.EQ

EQ：Equal

RANK.EQ（主题，比较范围）

直接下拉会出现排序错误，需要绝对应用（F4）

采用美式排序：跳过第7名

3、RANK.AVG

采取平均值排名

4、RANK.EQ（排序）

RANK.EQ（主题，比较范围，排序方式）

直接使用函数，出现排序颠倒

①输入0或者不输入排序方式：默认最高位第一（递减）

②输入1：递增

c

二十四、LEFT/MID/RIGHT截取单元格文字

1、LEFT

LEF（单元格，提取的字数）

2、RIGHT

RIGHT（单元格，提取的字数）

3、MID

MID（单元格，开始位置，提取的字数）

4、FIND

（1）FIND（要搜索的文字，单元格）

找出特定字母在文本的顺位

*寻找字母有重复，FIND只能找出现的第一个字母

（2）FIND(要搜索的文字，单元格，搜索起点)

5、LEN

LEN（单元格）

计算单元格内的字数和空格

6、简单练习

（1）品名

①先用FIND截取品名字数

②LEFT函数利用FIND的结果，把品名截取出来

（2）性别

①先用FIND找出MID的起点

xi

②用MID提取性别

（3）尺寸

①用FIND函数寻找第一个横杠

+1跳到性别的位置

②再用FIND函数，从性别这个地点，向右截取第二个横杠

③用LEN函数计算单元格总字数，减去FIND得出的字数，得出尺寸的字数

④最后用RIGHT函数截取尺寸

最终结果

二十五、INDEX&MATCH

一般我们会习惯用VLOOKUP来匹配数据，但是这个公式查询数据不是在单元格最左侧；或者数据不是竖向排序的话，这个公式就无用武之地

1、HLOOKUP

如果要查询的数据刚好再第一行，可以使用HLOOKUP

HLOOKUP(被查询值，查询范围，返回行数)

2、INDEX、MATCH

这两个函数都有同一个问题，只能进行单向查询，如果想要上下左右同时查询，则可以使用INDEX&MATCH

①INDEX(列/行范围，顺序)

②INDEX(数据范围，列数，行数)

MATCH(查找值，查找范围，匹配类型)

查找范围必须是单行或者单列

精准匹配

模糊匹配

查询分数能够达到的最高区间，

根据结果4可以使用INDEX来找出等级

或者直接把这两个和并在一起，去掉分数区间

=MATCH(AD3,Z3:Z7,1)

=INDEX(AA3:AA7,AD4)

把AD4替换成MATCH(AD3,Z3:Z7,1)，即可

后续在分数任意输入分数都能自动显示等级

3、简单练习

使用INDEX、MATCH，做成一个自动跳转数据的小板

根据业务员的姓名，自动显示所在分公司、业绩、考绩

（1）设计下拉菜单，省略输入动作

（2）使用MATCH计算行列

需要精确匹配获取对应数据

（3）INDEX、MATCH套用

二十六、表格加密保护

1、保护工作表不被查看、修改

（1）修改单元格格式

（2）隐藏公式

如果重要的公式不想显示，可以修改单元格格式

（3）隐藏部分数据

（4）设置密码

2、保护工作簿不被查看、修改

（1）隐藏SHEET

（2）设置密码

3、开放部分权限

设置成绩可以给老师修改

（1）取消密码保护

（2）设置可修改范围及密码

4、以文件加密

从文件的维度，对数据进行加密

方法一：文档加密

方法二：另存为中设置

❗ 设置好之后打开该表格就需要密码

二十七、重复数据处理

1、标亮重复

如果只想显示4项都是重复的，需要设置

方法一：使用辅助列

方法二：通过设置公式

COUNTIF(数据范围，条件)：计算数值或文字出现的次数

如果不需要标亮可以一键清除

2、删除重复

（1）数组去重

（2）单列去重

3、防止重复

⭐ 可自定义报错内容

一键去掉条件

二十八、RAND/RANDBETWEEN随机函数

1、RANDBETWEEN

使用场景：抽奖、分组

RANDBETWEEN(最小值，最大值)：随机生成1个整数

选中单元格，按F9会重新计算该函数结果

使用INDEX函数将数字转为名字

同理可以用这个函数分配考卷

2、CHOOSE

这个函数需要运用到辅助列，可以使用CHOOSE函数来代替辅助列

CHOOSE(答案，选项A，选项B)

3、RAND

这个函数分配是随机的，有时候数据不会相等

所以如果两两分组的话则需要用到另一个函数

RAND()：这个函数会直接生成0-1的小数且不会重复

①把这列数据排序

RANK(单元格，排序数组)

②把结果数分组

每一组为6个人，把每一个都除6，则会分成大于1和小于1两组

使用ROUNDUP函数取整，更便于区分

③把A组、B组代入

后续如果想分为4、3人一组，把公式中的6修改即可，在后面加上C组

当表格中有任何修改，分组都会重新刷新，所以如果确定好分组不再改变时，可以将数组贴值

4、情景模拟

想要抽5个获奖得主

使用RANDBETWEEN会有重复的值

使用RAND、RANK、INDEX

二十九、进度追踪表

通过表格设置对工作进度进行追踪

初始数据：

1、表格美化

去掉网格线、自定义绘制表格

2、添加设置复选框

（1）添加复选框

打开自定义功能区，勾选工具（开发人员）

其他路径：文件→选项→自定义功能区

WPS可以直接在插入中操作

去掉文字

（2）把复选框与状态建联

当勾选复选框后状态为TURE（默认）；取消勾线则为FALSE

重复该动作把每一个都建联

3、添加设置图标

（1）进度设置完成、正在进行

图表无法通过TURE或者FALSE来判断，我们需要设置一下

把×换成⚠

（2）设置还未开始🕐

需要用时间来界定，使用TODAY函数，再用IF函数判断

如果插入表情显示错误，可以使用代码

=IF(I6=TRUE,1,IF($C$3>=G6,0,UNICHAR(128336)))

图标代码：查询网址

❗ 更改日期测试公式

4、工作进度

（1）完成总览公式填写

（2）隐藏状态列，插入美化环形图

美化环形图

①更换颜色

②添加刻度

补充九个1，分为十分

依然更换颜色为灰色

设置两个环形上下叠在一起

把旧的（上面）环形图未完成部分灰色改为无填充

最后添加数字，引用之后修改格式即可

5、最终效果

三十、甘特图

甘特图：时程表，横轴是时间，纵轴是任务

1、补充并表格美化

2、插入并优化图表

①选择数据、优化表格

纵坐标修改为一致排序

②设置横坐标

每个日期都有对应的数字用于计算

⚠如果想把甘特图的起始日改为5月1日

打开单元格格式查看（快捷键：Ctrl+1）

③在甘特图中增加百分比标志

百分比与天数计算方式不一致，需要百分比转化为天数

使用误差线，把计算出来的天数添加到甘特图上

误差线：呈现一组数字的分散情况

例如：A班期末考平均成绩80分，误差线比较短时，说明全班分数集中在80分上下；误差线比较长时，说明全班分数比较极端分布

添加、设置误差线

④将甘特图与表格放置一起

隐藏天数转化、删除纵坐标和轴标题、去掉图表线条

3、最终效果

修改开始日期、天数、结束日期、完成百分比，甘特图都会随之变化

————TBC