Excel数据处理与分析-数据思维+分析方法+场景应用（下篇共上中下3篇）

Excel数据处理与分析数据思维+分析方法+场景应用2024讲解人：XXX目录CONTENTS01.数据展示的利器02.静态图表第五章数据展示03.动态交互式图表入门本章知识点5.3

动态交互图表入门动态交互图表流程整个动态图表交互主要有4个流程：第一步，要有数据源；第二步是制作选择器，根据实际需求选取【开发工具】选项卡里面的组合框、复选框、列表框等控件，或数据透视表中的切片器，或数据有效性都可以作为选择器来使用；第三步从数据源中抽取随控件的选择而动态变化的数据，需要用到vlookup、index、match、offset等函数的组合应用；将第三步抽取出来的数制作静态图表，选择控件中不同的值，图表自然而然动态变化5.3.1制作选择器（1/8）选择器可以由窗体控件、数据有效性、切片器制作窗体控件：开发工具里选择5.3.1制作选择器（2/8）窗体控件：组合框、列表框、选项按钮、复选框、数值调节钮、滚动条组合框右键【设置控件格式】，选择数据源区域和单元格链接，组合框由数据源区域和单元格链接两部分组成：数据源区域必须为列区域，行区域可以转置为列再选成数据源区域；单元格链接是选择控件中的值以后结果显示在哪里。5.3.1制作选择器（3/8）窗体控件：组合框、列表框、选项按钮、复选框、数值调节钮、滚动条列表框：功能和效果和组合框一样，不同的是组合框下拉后才有选项，而列表框不用下拉选项按钮：几个选项有一个共同的结果，即它在组中的次序，一次只能勾选一个结果5.3.1制作选择器（4/8）窗体控件：组合框、列表框、选项按钮、复选框、数值调节钮、滚动条复选框：输入区域可自行输入勾选的内容，单元格链接同组合框的规则，可以进行多项选择数值调节钮：由最小值、最大值、步长和单元格链接组成5.3.1制作选择器（5/8）窗体控件：组合框、列表框、选项按钮、复选框、数值调节钮、滚动条滚动条：和数值调节钮的功能一样，同样是由最小值、最大值、步长和单元格链接组成。5.3.1制作选择器（6/8）选择器可以由窗体控件、数据有效性、切片器制作窗体控件：开发工具里选择数据有效性（数据验证）：数据有效性也可以用来当成选择器5.3.1制作选择器（7/8）选择器可以由窗体控件、数据有效性、切片器制作窗体控件：开发工具里选择数据有效性（数据验证）：数据有效性也可以用来当成选择器切片器：数据透视表切片、智能表格切片数据透视表切片5.3.1制作选择器（8/8）切片器：数据透视表切片、智能表格切片表格切片5.3.2取数（1/4）原理：在原数据的基础上建立一个取数区域，取数区域是根据取数函数动态取数的取数函数：VLOOKUP函数、INDEX函数、INDEX函数+MATCH函数、OFFSET函数、OFFSET动态定义名称5.3.2取数（2/4）取数函数：VLOOKUP函数、INDEX函数、INDEX函数+MATCH函数、OFFSET函数、OFFSET动态定义名称VLOOKUP函数INDEX函数5.3.2取数（3/4）取数函数：VLOOKUP函数、INDEX函数、INDEX函数+MATCH函数、OFFSET函数、OFFSET动态定义名称INDEX函数+MATCH函数OFFSET函数5.3.2取数（4/4）取数函数：VLOOKUP函数、INDEX函数、INDEX函数+MATCH函数、OFFSET函数、OFFSET动态定义名称OFFSET动态定义名称5.3.3静态图表变身动态图表作图选择的数据所在的区域是不变的，变的是数值，通过选择器的不同选择，得到不同的数值，图表自然而然就变化了。练一练如图5.3.44所示是2011年、2012年中国各省市区GDP前十的数据，如何用图表展示使其更直观具体？提示1：思考不同的字段可以用什么图表来展示，如GDP可以用条形图或柱状图来展示，增速如何展示？提示2：同一年的GDP和增速可以放一起展示吗？不同年份的呢？

练一练答案如图5.3.44所示是2011年、2012年中国各省市区GDP前十的数据，如何用图表展示使其更直观具体？提示1：思考不同的字段可以用什么图表来展示，如GDP可以用条形图或柱状图来展示，增速如何展示？提示2：同一年的GDP和增速可以放一起展示吗？不同年份的呢？

感谢观看！THANKYOU!讲解人：图小怪Excel数据处理与分析数据思维+分析方法+场景应用2021讲解人：XXX目录CONTENTS01.正确认识数据分析报告02.用Excel写数据分析报告第六章数据分析报告本章知识点6.1

正确认识数据分析报告6.1.1写数据分析报告的步骤（1/3）4W1H的原则：来分析数据分析报告前应注意的事项。WhyWhatWhoWhenHow为什么要写数据分析报告，目的是什么？是年终总结还是给客户的竞品分析？这份报告是什么类型的，是在描述型的，还是预测型的，是在阐述一个事实，还是在预测一项指标这份报告是写给谁看的？是给上司汇报，还是用于和同事交流，是给客户展示还是用于网络公开？数据报告涉及的时间周期怎么写，步骤如何？也就是这节的重点。6.1.1写数据分析报告的步骤（2/3）标题：简洁、明了、准确简洁：简单，标题应当是对全文内容的一个高度精炼的概况明了：一目了然、开门见山，能让人一下子就能抓住重点，知道报告讲的是什么准确：没有语病、文题相符，语病是大忌，如果标题里就有语病，那文章的准确性想必都要存疑了，这不是以偏概全，而是第一印象有多重要的客观表述摘要：也可以说是前言，写在一份报告的最开端，是对报告整体内容的一个概述，为什么要做这项分析，怎么分析的，结果如何。6.1.1写数据分析报告的步骤（3/3）正文：开始罗列数据、展示图表、写出结论了，当然不是将分析的结果毫无头绪的全部叠加，要做到有理有据、逻辑清晰、层次清楚。明确正文中各部分（段落）之间的关系；数据、图表、文字的结合要恰到好处；文字部分要讲客观事实，多用数据说话，体现严谨的态度。总结：总结部分是对报告整体观点结论的一个描述性概况，和摘要不同的是，摘要主要写前期的准备工作，而总结主要将报告的主要观点罗列在一起。6.1.2报告中常见的专业术语倍数：举例来说，100/25=4，那么100就是25的4倍，这个4倍就是倍数，注意倍数是整数，没有2.5倍一说。成数：几成表示一个数是另一个数的百分之几十或十分之几，如1成表示10%（10/100或1/10），2成表示20%，这里的1成就是一个成数。“成”和平时所说的“折”是一个概念，比如商品达9折出售的意思是现价是原价的90%。番数：指原来数量的2的N次方倍，如今年的产量较去年翻一番，意思是今年是去年数量的2倍，翻两番是4倍。百分点：是表达不同百分数之间差距的值，指变动的幅度，1个百分点等于1%。如三季度用户转换率是80%，四季度是85%，那么可以说四季度较三季度提高了5个百分点。比例：比例是指总体中各部分的占比，如：男生的比例是30:50，即3/5，反映了男生的数量情况。比率：比率是指不同类别数据之间的比值，如男女比率是3:2，反映的是男女的数量情况。同比：指历史同时期的比较，如去年5月与今年5月相比。环比：指与前一个统计时期的比较，如今年5月与今年4月相比。增长率：是描述两个统计期内数据增长速度的值，用百分号表示，增长率的公式=（本期-前期）/前期，如2019年5月的用户增长率是75%，4月是66%，那么5月环比4月增长了（75%-66%）/66%=13.63%。练一练让你写一份《2019中国移动互联网行业用户画像分析报告》，你该如何着手？（仅列出目录即可）提示：按照总分总的结构，从行业发展概况出发到行业趋势预测练一练答案让你写一份《2019中国移动互联网行业用户画像分析报告》，你该如何着手？（仅列出目录即可）提示：按照总分总的结构，从行业发展概况出发到行业趋势预测标题2019中国移动互联网行业用户画像分析报告012019中国移动互联网行业发展概况022019中国移动互联网行业用户画像描绘032019中国移动互联网发展问题与预测趋势6.2

用Excel写数据分析报告6.2.1日报案例（1/3）准备数据：原始数据的来源是某产品每日用户数的一个监控新增字段：消费用户数累计占比、潜在用户数累积占比、价值用户数累计占比、全量用户数每日、消费用户数每日、潜在用户数每日6.2.1日报案例（2/3）制作选择器：运用组合框控件抽取数据：使用vlookup和index函数来实现6.2.1日报案例（3/3）制作日报：多个单元格共同拼接生成日报：使用concatenate函数6.2.2月报案例（1/5）选择不同的日期或不同的指标，出现相应的数据，同时折线图跟随日期变化。6.2.2月报案例（2/5）组合框动态交互：制作组合框选择不同日期，四个关键指标（新关注人数、取消关注人数、净增关注人数、累计关注人数）的值随之变化的交互效果6.2.2月报案例（3/5）组合框与条件格式动态交互：随着组合框日期选择的变化，用颜色和箭头显示4个指标的、环比的结果，用到条件格式来实现。切片器动态交互：两个交互模式，一是根据指标的选择绘制随日期变化的折线图，用切片器实现；二是根据日期组合框的选择，随时间动态延展，用动态自定义名称结合offset函数来实现6.2.2月报案例（4/5）列表框动态交互：选择列表框中不同的年龄段，使用圆环图动态显示该年龄段下人数的占比情况。列表框和组合框效果是一样的，不同的是组合框不会展示下拉项，而列表框是将下拉项显示出来。6.2.2月报案例（5/5）制作小人填充的条形图练一练尝试将你目前每日通报的数据用Excel自动化处理出来感谢观看！THANKYOU!讲解人：图小怪Excel数据处理与分析数据思维+分析方法+场景应用2021讲解人：XXX目录CONTENTS01.明确目的02.获取数据第七章实例分析03.数据预处理04.数据分析05.数据展示06.数据报告7.1

明确目的7.1.1明确目的5W2H的原则WhatWhyWhoWhenHowHowmuch要分析什么？分析的是某网站关于数据分析岗位的招聘信息数据为什么要分析？分析数据分析岗位招聘信息的数据是为了对该岗位有个大致的了解，为我们从业提供良好的决策支撑给谁做的分析？目标受众是谁？为求职者做的分析怎么分析？运用结构化的思维，从求职者和企业两个角度出发，构建目标画像。同时运用分组、平均和交叉的方法对整体薪资进行分析暂用不到，不必套用分析的是什么时候的数据？采集的是2020年3月30日某招聘网站发布的深圳地区数据分析岗位招聘信息的数据7.1.2提出问题在明确目的的同时，我们也可以带着以下这几个问题来分析：数据分析岗位的薪资分布如何？薪资高的岗位对应聘者的要求是什么样的？学历越高薪资越高吗？哪个行业最吃香？没有相关工作经验也可以应聘数据分析岗吗？7.2

获取数据7.2获取数据城市：岗位所在城市页码：爬取网页的页码城市网址：招聘网站所选城市所在的网址招聘岗位：岗位名称薪资：招聘岗位的薪资所在地：招聘岗位所在的城市区域工作经验：应聘该岗位所需的工作经验学历：招聘岗位要求的学历所招人数：该岗位所需几人发布时间：该招聘信息发布的世界专业要求：该岗位所需的专业技能要求福利标签：该公司所提供的福利职位信息：招聘岗位的工作内容、职责等上班地址：公司所在地址公司：公司名称公司介绍链接：公司介绍的链接公司信息：公司简介公司类型：该公司属于国企、民企、外企等类型公司规模：公司人数规模所属行业：公司所属的行业7.3

数据预处理7.3.1缺失值处理筛选“招聘岗位”字段为空的数据，发现有13个缺失值，且所有的字段都缺失，直接删除整行。对于“薪资”、“工作经验”、“学历”均缺失的数据筛选出来，对分析不能提供很大的帮助，因此我们也将它删除.对于“工作经验”一列缺失的521个数据，我们不知道是发布信息的HR忘了填写还是这份工作根本就无须经验，虽然这一列缺失，但其他列还是有数据的，因此就先保留这种缺失值，对后续分析不会造成太大的影响。同样对于“薪资”和“学历”中的缺失值也先做保留处理。7.3.2重复值处理原始数据中没有一列用于标记一条记录是否唯一的序列，因此我们可以这么判断该招聘信息是否重复出现：如果岗位、薪资、所在地、经验这4个字段的值均一致，说明这条数据重复出现了。7.3.3异常值处理观察数据发现“招聘岗位”字段里居然有“运营”、“客服”、“文员”、“UI设计师”、“销售”、“财务”等岗位，这和我们要分析的“数据分析”岗位差别有点大，因此也需要剔除。“所在地”这个字段里有除了深圳以外其他地区的，也需要进行剔除。7.3.4字段拆分对于“薪资”一列，我们需要的是工资上下限两个数值型的数据，以得到薪资的平均值，而非合并在一起的一个区间值，因此要将这列数据进行拆分处理。“所属行业”这个字段里有些单元格中的值被分成了两行，也是需要拆分的。7.3.5字段计算对于薪资来说，有的是以万为单位计，有的则是以千为单位，单位不统一是没法进行分析的，因此需要将薪资的单位全部统一为“千”。7.3.6薪资缺失值处理对于有工作经验有学历的数据来说，其实完全可以用均值来填充。7.4

数据分析7.4.1企业画像(1/2)地域维度：数据透视表+SQL语句公司类型维度：数据透视表7.4.1企业画像(2/2)公司规模维度：数据透视表+SQL语句行业维度：数据透视表7.4.2求职者画像(1/3)工作经验维度：数据透视表+SQL语句学历维度：数据透视表+SQL语句7.4.2求职者画像(2/3)整体薪资情况：数据分析（描述性统计分析+直方图）公司所在地和薪资的关系：数据透视表+SQL语句工作经验和薪资的关系：数据透视表+SQL语句学历和薪资的关系：数据透视表+SQL语句7.4.2求职者画像(3/3)整体薪资情况：数据分析（描述性统计分析+直方图）公司规模和薪资的关系：数据透视表+SQL语句行业和薪资的关系：数据透视表7.5

数据展示7.5招聘岗位词云图对招聘岗位的名称做词云图7.5.1企业画像地域、公司类型、公司规模、行业7.5.2求职者画像工作经验、学历7.5.3整体薪资情况(1/2)整体薪资情况、公司规模和薪资、行业和薪资7.5.3整体薪资情况(2/2)工作经验和薪资、学历和薪资感谢观看！THANKYOU!讲解人：图小怪近年来，数据分析师的岗位逐渐火起来，甚至校招也开设了该岗位。数据分析岗位的行情究竟如何？为了对该岗位有更好地了解，为从业者提供良好的决策支撑，这里选取了2020年3月30日某招聘网站发布的深圳地区数据分析岗位招聘信息的数据作为本次分析的数据源，并从求职者和企业两个角度出发，构建目标画像，对数据分析岗位做一个深入的分析。

图7.53招聘公司所在地与薪资关系的条形图数据分析岗位招聘分析南山/福田区拥有更多的就业机会。对招聘数据分析岗位的地域做一个划分，南山、福田属于第一梯队，龙岗、龙华属于第二梯队，宝安、罗湖属于第三梯队。第一梯队能够提供的岗位远远超过第二和第三梯队；南山/福田区薪资高。从公司所在地域平均薪资的关系来看，第一梯队（南山区/福田区）公司提供的薪资也最高；

图7.53招聘公司所在地与薪资关系的条形图1.1公司画像——地域分布民营公司岗位最多。从招聘公司类型来看，民营公司提供的数据分析岗位最多。中小型公司最多。从公司规模来看，人数为150~500人和50~150人的公司较多。大公司/初创企业薪资最高。从公司规模和薪资的关系来看，大公司和创业公司给的薪资最高，说明想拿高薪，要么大公司，要么创业。

图7.53招聘公司所在地与薪资关系的条形图1.2公司画像——类型规模互联网/计算机最多。从所属行业来看，互联网/电子商务行业提供的数据分析岗位最多，占比都超过了10%，其他行业都在10%以下。机械/设备行业最高。从行业与薪资的关系来看，互联网/计算机行业招聘人数虽多，薪资却不高。

图7.53招聘公司所在地与薪资关系的条形图1.3公司画像——行业分布2~4年经验最优。公司对所招聘人才的需求主要集中在3~4年和2年工作经验上，说明数据分析这个岗位对工作经验是有依赖的，且工作年限越久的分析师可能越吃香。工作经验越长薪资越高。工作经验对薪资的提升是很大的，工作年限越长平