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

※食用指南文章内容为‘PAPAY电脑教室—Excel基础教学’视频学习笔记旨在夯实已掌握的Excel基础※需要表格数据请私信领取PAPAY电脑教室—Excel基础教学https://www.youtube.com/playlist?listPL7enJ2-v6SPm-EHMuRMCG7R7C-vXQugNMhttps://www.youtube.com/playlist?listPL7enJ2-v6SPm-EHMuRMCG7R7C-vXQugNMhttps://www.youtube.com/playlist?listPL7enJ2-v6SPm-EHMuRMCG7R7C-vXQugNM目录二十一自定义单元格格式1、四舍五入#2、员工ID000003、统一添加4、千分位设置5、正负值二十二、日期函数、年资与工时计算1、日期数字转文字2、周几、星期几3、日期快捷键4、计算入职以来的时间二十三、使用RANK函数进行排名1、直接排序2、RANK.EQ3、RANK.AVG4、RANK.EQ排序二十四、LEFT/MID/RIGHT截取单元格文字1、LEFT2、RIGHT3、MID4、FIND5、LEN6、简单练习二十五、INDEXMATCH1、HLOOKUP2、INDEX、MATCH3、简单练习二十六、表格加密保护1、保护工作表不被查看、修改2、保护工作簿不被查看、修改3、开放部分权限4、以文件加密二十七、重复数据处理1、标亮重复2、删除重复3、防止重复二十八、RAND/RANDBETWEEN随机函数1、RANDBETWEEN2、CHOOSE3、RAND4、情景模拟二十九、进度追踪表1、表格美化2、添加设置复选框3、添加设置图标4、工作进度5、最终效果三十、甘特图1、补充并表格美化2、插入并优化图表3、最终效果二十一自定义单元格格式1、四舍五入#代表一个位数的预留位置82.5四舍五入为832、员工ID00000①5位数的员工ID②如要增加文字需要加双引号3、统一添加① 文字预留位置② *重复指定的符号4、千分位设置5、正负值“_)”:底线可理解为空格❗连续输入三个隐藏单元格的全部内容恢复显示把格式改为”常规“就可以综上二十二、日期函数、年资与工时计算1、日期数字转文字2、周几、星期几三个a四个a3、日期快捷键已经过的时间4、计算入职以来的时间日、年、月、几年又几月单元格格式要设定为常规DATEDIF(开始时间结束时间计算单位)5、轮班天数①NETWORKDAYS(开始时间结束时间假日)②NETWORKDAYS.INTL(开始时间结束时间自订周末假日)①NETWA二十三、使用RANK函数进行排名1、直接排序数据顺序会被打乱2、RANK.EQEQEqualRANK.EQ主题比较范围直接下拉会出现排序错误需要绝对应用F4采用美式排序跳过第7名3、RANK.AVG采取平均值排名4、RANK.EQ排序RANK.EQ主题比较范围排序方式直接使用函数出现排序颠倒①输入0或者不输入排序方式默认最高位第一递减②输入1递增c二十四、LEFT/MID/RIGHT截取单元格文字1、LEFTLEF单元格提取的字数2、RIGHTRIGHT单元格提取的字数3、MIDMID单元格开始位置提取的字数4、FIND1FIND要搜索的文字单元格找出特定字母在文本的顺位*寻找字母有重复FIND只能找出现的第一个字母2FIND(要搜索的文字单元格搜索起点)5、LENLEN单元格计算单元格内的字数和空格6、简单练习1品名①先用FIND截取品名字数②LEFT函数利用FIND的结果把品名截取出来2性别①先用FIND找出MID的起点xi②用MID提取性别3尺寸①用FIND函数寻找第一个横杠1跳到性别的位置②再用FIND函数从性别这个地点向右截取第二个横杠③用LEN函数计算单元格总字数减去FIND得出的字数得出尺寸的字数④最后用RIGHT函数截取尺寸最终结果二十五、INDEXMATCH一般我们会习惯用VLOOKUP来匹配数据但是这个公式查询数据不是在单元格最左侧或者数据不是竖向排序的话这个公式就无用武之地1、HLOOKUP如果要查询的数据刚好再第一行可以使用HLOOKUPHLOOKUP(被查询值查询范围返回行数)2、INDEX、MATCH这两个函数都有同一个问题只能进行单向查询如果想要上下左右同时查询则可以使用INDEXMATCH①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计算行列需要精确匹配获取对应数据3INDEX、MATCH套用二十六、表格加密保护1、保护工作表不被查看、修改1修改单元格格式2隐藏公式如果重要的公式不想显示可以修改单元格格式3隐藏部分数据4设置密码2、保护工作簿不被查看、修改1隐藏SHEET2设置密码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函数取整更便于区分ROUND(数值位数)位数0进位到整数位数1进位到小数第一位位数-1进位到十位数③把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(I6TRUE,1,IF($C$3G6,0,UNICHAR(128336)))图标代码查询网址❗ 更改日期测试公式4、工作进度1完成总览公式填写2隐藏状态列插入美化环形图美化环形图①更换颜色②添加刻度补充九个1分为十分依然更换颜色为灰色设置两个环形上下叠在一起把旧的上面环形图未完成部分灰色改为无填充最后添加数字引用之后修改格式即可5、最终效果三十、甘特图甘特图时程表横轴是时间纵轴是任务1、补充并表格美化2、插入并优化图表①选择数据、优化表格纵坐标修改为一致排序②设置横坐标每个日期都有对应的数字用于计算⚠如果想把甘特图的起始日改为5月1日打开单元格格式查看快捷键Ctrl1③在甘特图中增加百分比标志百分比与天数计算方式不一致需要百分比转化为天数使用误差线把计算出来的天数添加到甘特图上误差线呈现一组数字的分散情况例如A班期末考平均成绩80分误差线比较短时说明全班分数集中在80分上下误差线比较长时说明全班分数比较极端分布添加、设置误差线④将甘特图与表格放置一起隐藏天数转化、删除纵坐标和轴标题、去掉图表线条3、最终效果修改开始日期、天数、结束日期、完成百分比甘特图都会随之变化————TBC