2.单元二 数据透视表及数据图表

Excel数据透视表和切片器数据透视表及数据图表Excel数据透视表和切片器01Excel数据透视表和切片器Excel数据透视表和切片器是两个强大的数据分析工具，它们可以相互配合使用来实现更全面和灵活的数据分析。1.数据透视表（PivotTable）数据透视表可以帮助你对大量数据进行汇总、分析和可视化展示。通过数据透视表，可以按照不同的维度对数据进行分组和汇总，并计算汇总值、平均值、计数等统计指标。使用数据透视表可以快速地从多个角度查看数据，并发现数据中的关联和趋势。Excel数据透视表和切片器接下来，继续以案例来进行操作说明：某金融第三方支付公司是一家专注于为商家提供硬件支付解决方案的公司，他们生产销售收款POS机、收款机等设备。Excel数据透视表和切片器为了更好地了解销售情况和优化销售策略，该公司收集了销售阶段、赢单率和金额的数据，希望通过汇总销售阶段与赢单率交叉表的金额合计值，能够了解不同销售阶段的销售额情况，并判断哪些阶段对整体业绩的贡献最大。同时，通过根据特定的“所属领域”呈现不同销售阶段的情况，他们能够针对该领域进行更精准的销售策略制定和目标设定。Excel数据透视表和切片器一、汇总销售阶段与赢单率交叉表的金额合计值具体操作如下：①单击数据列表区域中任一单元格，在【插入】选项卡中单击数据透视表图标，弹出【创建数据透视表】对话框。②【创建数据透视表】对话框默认选项不变，点击【确定】后，就会生成一个新的sheet页面并创建了一张空的数据透视表并把新的sheet页面重命名为“结果”表。Excel数据透视表和切片器一、汇总销售阶段与赢单率交叉表的金额合计值③在【数据透视表字段】对话框中，按照需求，单击“销售阶段”字段，按住鼠标左键不放将它拖到“行”区域内；同样的方法，分别把“赢单率”字段拖到“列”区域；把“金额”字段拖到“值”区域。这三个字段同时也被添加到数据透视表中，如下图：Excel数据透视表和切片器一、汇总销售阶段与赢单率交叉表的金额合计值数据透视表的结构，就是当把不同的字段拖到行、列标签，数据透视表也会按照不同的维度来进行呈现。通过以上的数据透视表，我们汇总了销售阶段与赢单率交叉表的金额合计值。  从表中可以快速地看到不同的销售阶段里各赢单率下的金额合计结果。Excel数据透视表和切片器二、制作销售阶段的饼图透视图并制作所属字段的切片器与数据透视图关联（一）按销售阶段汇总金额首先，创建数据透视表图：单击数据源区域任一单元格-【插入】-【数据透视表】。在弹出的【创建数据透视表】对话框中，这次数据透视表的位置，没有用默认的“新工作表”，因为想要把这个透视表放在上面创建的透视表里，所以，这里勾选的是“现有工作表”，位置是“结果”表里的A20单元格。这样，就又创建了一张空的透视表。Excel数据透视表和切片器二、制作销售阶段的饼图透视图并制作所属字段的切片器与数据透视图关联Excel数据透视表和切片器二、制作销售阶段的饼图透视图并制作所属字段的切片器与数据透视图关联下面进行的操作和上面的操作一样，就是用鼠标进行拖动。在【数据透视表字段】中，单击“销售阶段”字样，按住鼠标左键将它拖到“行”区域内；同样的方法，把“金额”字段拖到“值”区域内；把“所属领域”拖到“筛选”区域。Excel数据透视表和切片器二、制作销售阶段的饼图透视图并制作所属字段的切片器与数据透视图关联为什么拖到“筛选”区域而不是像前面那道题目直接拖到“列”区域呢？  因为在这道题目中，要的结果并不是要同时呈现各个领域的汇总值，而只是希望当选到某个领域时，就只看该领域的结果。所以把“所属领域”放到“筛选”区域里作为可以筛选的字段。Excel数据透视表和切片器二、制作销售阶段的饼图透视图并制作所属字段的切片器与数据透视图关联如右图，所属领域作为筛选字段，当单击下拉筛选按钮，就可以根据实际需要，选择任意一个领域。那在右图的演示中选择了“计算机领域”，自然呈现的就是该领域下不同销售阶段的金额总值了。Excel数据透视表和切片器二、制作销售阶段的饼图透视图并制作所属字段的切片器与数据透视图关联（二）制作饼图透视图数据统计好后，就可以来做饼图透视图了。  单击数据透视表里任意一单元格-【数据透视表工具】-【数据透视表分析】-【数据透视图】，在弹出的【插入图表】对话框中选择【饼图】，最终结果如右图。Excel数据透视表和切片器二、制作销售阶段的饼图透视图并制作所属字段的切片器与数据透视图关联当然，还可以对透视图表进行美化，如颜色的变换、标题的修改、添加数据标签、如隐藏图标上的字段按钮等，大家可自行探索。右图就是计算机领域在各销售阶段的金额占比情况。如果想查看其它领域在各销售阶段的占比情况，只要在数据透视表里选择对应的领域即可。Excel数据透视表和切片器二、以“所属领域”字段制作切片器，并与饼图进行关联如前述在制作饼图的时候，想要看不同领域的情况，就要对筛选框里的所属领域字段进行下拉，然后选择对应的领域。  数据透视表显示的也只是筛选后的结果，但如果想要看到对哪些数据进行了筛选，只能到该字段的下拉列表中查看，非常不直观。所以，这里就引入了“切片器”的功能。  切片器，不仅可以对数据透视表字段进行筛选，还可以很直观地在切片器内查看到该字段所有的数据信息。Excel数据透视表和切片器二、以“所属领域”字段制作切片器，并与饼图进行关联要制作“所属领域”字段的切片器，单击透视表里任一单元格，在【数据透视表工具】-【数据透视表分析】-【插入切片器】，在弹出的【插入切片器】对话列表框里，是原始数据所有字段的列表，大家勾选“所属领域”，就会生成了以“所属领域”为字段的切片器。Excel数据透视表和切片器二、以“所属领域”字段制作切片器，并与饼图进行关联操作时，可以根据排版需要，随意拖动以调整切片器的位置。同时，还可以对切片器的“列”数进行设置或样式进行设置。Excel数据可视化数据透视表及数据图表Excel数据可视化01Excel数据可视化在进行数据可视化操作之前，我们先来了解一下图表相关知识。一、图表简介图表是一系列数据的可视化结果。它能够将繁多复杂的数据转化为比较容易理解的形式传达给读者，从而使读者能够更加容易地掌握数据所表达的核心观点。Excel里内置了柱形图、条形图、折线图、饼图等一系列图表，只要有对应的数据源，可以直接插入上述图表。Excel数据可视化二、创建图表的步骤创建图表只需三个基本步骤：    首先，选择想要插入图表的数据源。如果数据源里包含列标题，需要将列标题选中。其次，单击工具栏上的【插入】按钮，然后选择相应的图表类型。最后，每个图表类型下面还有多种相关的图表，选择适合数据的图表，选择插入即可。Excel数据可视化三、对图表进行处理对图表的处理通常包括以下几种情况，由于图表处理相对简单，大部分excel用户几乎都能够掌握，这里就不展开介绍，只是提供一个简单的列表：    1.移动图表和调整图表大小；    2.复制图表和删除图表；    3.增加图表元素；    4.移动和删除图表元素；    

5.对图表元素进行格式修饰；6.复制图表格式；7.给图表重命名；    8.把内置图表转化为单元格图表。Excel数据可视化四、常见图表类型常见的图表类型有：柱形图、条形图、折线图、饼图、漏斗图、瀑布图、散点图、直方图、面积图等等。 接下来，将根据人事档案excel，对常见的图表进行操作的讲解。   Excel数据可视化一、柱形图Step1：汇总数据。根据员工信息表汇总不同部门的招聘人数，鼠标选中人数那一列的单元格，点击【公式】-【插入函数】-【搜索函数countif】-【转到】-【选择函数countif】-【确定】Excel数据可视化一、柱形图输入条件，点击确定，得出的数据下拉，最后的结果如右图所示。Excel数据可视化一、柱形图Step2：插入图表。选中部门、人数这两列单元格，点击【插入】-【图表】-【簇状柱形图】，这样一个简单的柱形图就做好了，如下图所示。Excel数据可视化一、柱形图Step3：美化图表。将网格线去掉，为柱形图加上数据标签，修改标题，结果如下图所示。Excel数据可视化二、折线图Step1：汇总数据。根据员工信息表每个员工的工资情况制作折线图。在员工信息表中复制姓名列和工资列到新的表格中，为了更清晰的展示，这里就只复制部分员工的信息，如右图所示。Excel数据可视化二、折线图Step2：插入图表。选中姓名、工资这两列单元格，点击【插入】-【图表】-【折线图】，这样一个简单的折线图就做好了，如下图所示。Excel数据可视化二、折线图Step3：美化图表。将网格线去掉，修改标题，结果如下图所示。Excel数据可视化三、条形图Step1：汇总数据。根据员工信息表汇总不同年龄段的招聘人数，鼠标选中人数那一列的单元格，点击【公式】-【插入函数】-【搜索函数countifs】-【转到】-【选择函数countifs】-【确定】，输入条件，点击确定，这里每个段都需要改一下上限和下限的数据。汇总的数据如右图所示。Excel数据可视化三、条形图Step2：插入图表。选中年龄段、人数这两列单元格，点击【插入】-【图表】-【簇状条形图】，这样一个简单的条形图就做好了，如下图所示。Excel数据可视化三、条形图Step3：美化图表。将网格线去掉，为条形图加上数据标签，修改标题，结果如下图所示。Excel数据可视化四、饼图Step1：汇总数据。根据员工信息表汇总不同性别的招聘人数，鼠标选中人数那一列的单元格，点击【公式】-【插入函数】-【搜索函数countif】-【转到】-【选择函数countif】-【确定】，输入条件，点击确定，汇总的数据如右图所示。Excel数据可视化四、饼图Step2：插入图表。选中性别、人数这两列单元格，点击【插入】-【图表】-【饼图】，这样一个简单的饼图就做好了，如下图所示。Excel数据可视化四、饼图Step3：美化图表。将网格线去掉，为饼图加上数据标签，修改标题，结果如下图所示。Excel数据可视化五、瀑布图Step1：汇总数据。根据员工信息表汇总不同招聘渠道的招聘人数，鼠标选中人数那一列的单元格，点击【公式】-【插入函数】-【搜索函数countif】-【转到】-【选择函数countif】-【确定】，输入条件，点击确定，汇总的数据如右图所示。Excel数据可视化五、瀑布图汇总出来的数据还是进行个排序的操作，操作步骤和前面的一样，就不重复叙述了。Excel数据可视化五、瀑布图Step2：插入图表。选中招聘渠道、人数这两列单元格，点击【插入】-【图表】-【瀑布图】，这样一个瀑布图就做好了，如下图所示。Excel数据可视化五、瀑布图Step3：美化图表。修改标题，结果下图所示。Excel数据可视化六、漏斗图Step1：汇总数据。根据员工信息表汇总不同工作年限的招聘人数，鼠标选中人数那一列的单元格，点击【公式】-【插入函数】-【搜索函数countif】-【转到】-【选择函数countif】-【确定】，输入条件，点击确定，汇总的数据如右图所示。Excel数据可视化六、漏斗图这里还要再多一个排序步骤。选中这两列，点击【数据】-【排序】。Excel数据可视化六、漏斗图跳出如下对话框，排序依据选择人数，次序选择降序。最后的结果如右图所示。Excel数据可视化六、漏斗图Step2：插入图表。选中工作年限要求、人数这两列单元格，点击【插入】-【图表】-【漏斗图】，这样一个漏斗图就做好了，如下图所示。Excel数据可视化六、漏斗图Step3：美化图表。修改标题，结果下图所示。Excel数据可视化七、仪表盘仪表盘是用于展示各种可视化数据的一个图表报告，其组成部分包括各种可视化图表、图标、数据等，具备动态交互效果。它能够将繁琐复杂的数据通过可视化的信息图表表达出来，让人一目了然。那接下来我们就一起来制作一个仪表盘。Excel数据可视化七、仪表盘Step1：创建数据。我们汇总各个学历对应人数，依旧是用到countif这个函数进行汇总，汇总结果如下。Excel数据可视化七、仪表盘但是需要展现的是学历占比的仪表盘，那数据就不能以这样的形式展示，得换个形式。如下图，这样就可以继续后续的制作。这里的学历对应人数里的数据是可以进行修改的，不同的数据会影响指针的位置，那要做到这样，指针这里需要设一个公式“=学历对应人数/总人数”，结果值设置为百分比，不要小数位。Excel数据可视化七、仪表盘那接下来需要对仪表盘的构成进行了解。仪表盘通常可以分为3个部分：表盘、刻度盘和指针。Excel数据可视化七、仪表盘仪表盘的制作方法并不复杂，它其实是由一个饼图和两个圆环图组成的组合图表。Excel数据可视化七、仪表盘（1）表盘表盘实际上是一个被分成了11个部分的圆环图，其中前10个部分各占圆环的3/4，最后一个部分占圆环的1/4。一个完整的圆环是360°，那么表盘的数据源就应该是10个27，1个90，如右图所示。根据这个数据源即可创建出表盘圆环图。Excel数据可视化七、仪表盘（2）刻度盘刻度盘与表盘是一一对应的，因此刻度盘也应该被分为11个部分，即10个27，1个90，对应的刻度是0%~100%，刻度盘和刻度显示对应的数据源如右图所示。Excel数据可视化七、仪表盘但是如果这样设置刻度盘和刻度显示的数字，会使最终得到的仪表盘中刻度显示的数字显示在刻度盘各部分的中间位置，如右图所示。Excel数据可视化七、仪表盘而实际上，仪表盘中的刻度显示的数字应该显示在刻度盘各部分的起始位置，因此，我们需要在刻度盘数据源中的每个数字前面增加一个0来占位，使刻度显示的数字都与占位的0相对应。根据带有占位的0的数据源就可以创建出理想的刻度盘。Excel数据可视化七、仪表盘（3）指针指针实际上就是饼图中的一个极小的扇区。此处学历占比率为第1扇区，指针为第2扇区，其余区域为第3扇区，如右图所示。Excel数据可视化七、仪表盘接下来，确定饼图中3个扇区的角度，首先确定指针所占角度。这里需要注意：在设置指针大小，即设置第2扇区的角度时，数值不宜太大，若太大，指针太粗，会导致读数不准；但也不能太小，若太小，指针太细，会导致显示不清。具体数值根据图表的大小进行调整即可。此处指定指针所占角度为1.5°。接下来计算第1扇区的角度，第1扇区的角度应该对离职率进行换算得到。  由于我们要做的仪表盘角度为270°，因此第1扇区的角度应该是“学历占比率×270”。最后计算第3扇区的角度，一个完整饼图是360°，那么第3扇区的角度为“360°-第1扇区角度-第2扇区角度”。Excel数据可视化七、仪表盘这样就可以计算出指针饼图的数据源了，如下图所示。数据源确定好之后，接下来就可以绘制仪表盘图了。Excel数据可视化七、仪表盘Step2：绘制图表。选中刻度盘、表盘、指针这三列数据，点击【插入】-【图表】-【圆环图】，如下图所示。Excel数据可视化七、仪表盘选中图表的一个数据系列，右击【更改图表类型】。弹出【更该图表类型】对话框，将“指针”的【图表类型】设置为【饼图】，勾选指针右侧的【次坐标轴】复选框。Excel数据可视化七、仪表盘现在饼图的起始位置不对。这是因为在制作圆环图和饼图时，默认数据都是从钟表的12点位置开始排列的。此处需要饼图的起始位置在7点和8点中间的位置，因此，我们需要将饼图第1扇区的起始角度设置为225°。点击红框中的饼图，出现右边的设置界面，如果点击后没有出现右边的界面可按ctrl+1。Excel数据可视化七、仪表盘我们设置第一扇区起始角度为225°，饼图分离为55%，效果如下图所示。Excel数据可视化七、仪表盘放大图表，选择图中红框框出来的地方，把它设置成无线条，拖动它把它往里移动下位置。Excel数据可视化七、仪表盘选中图中这个圆环，设置它的第一扇区起始角度为225°，圆环图圆环大小为80%，效果如右图所示。Excel数据可视化七、仪表盘选中图中区域，设置成无填充、无线条。效果如右侧两图所示。Excel数据可视化七、仪表盘选中图中红框区域，点击右边的【+】号，勾选数据标签，效果如下图所示。Excel数据可视化七、仪表盘选中任一一个数据标签，在【设置数据标签格式】任务窗格中单击【标签选项】按钮，在【标签选项】组中取消勾选【值】复选框，勾选【单元格中的值】复选框。Excel数据可视化七、仪表盘选中内圈的圆环，设置成无填充、无线条，效果如下图所示。Excel数据可视化七、仪表盘选中最下面这一段，设置成无填充，无线条，效果如下图所示。到这，仪表盘的图表已经出来了，接下来就是对它进行美化。Excel数据可视化七、仪表盘Step3：美化图表。圆环的颜色可以在右边的界面中进行设置，比较简单，这里就不再详细介绍了。下图就是设置完颜色后的仪表盘。Excel数据可视化七、仪表盘接着修改标题、修改数据标签的字体大小、把图例去掉。看看最后得到的效果。Excel数据可视化八、创建图表组合首先，我们先筛选需要用到的数据，员工名称、工资，然后再添加一列“公司平均工资“作为辅助列，这是用于后期绘制平均薪水，所以这一列的值都是平均值。Excel数据可视化八、创建图表组合使用所有列的数据（包括辅助列）来绘制柱形图，得到下面的图形。此时，辅助列也成为了柱形图的一部分。Excel数据可视化八、创建图表