切片器加数据透视表制作可视化看板
本文以最新发布的作品《可视化看板》进行讲解。
主要内容介绍：
数据透视表
切片器及切片器设置
1.1 首先创建三个工作表。分别命名为“index”，“数据源”，“数据汇总”如下图所示。并在数据源工作表中从A列自H列输入如下列标题。
1.2 在F3单元格输入计算合格率的公式：=(C3-D3)/C3，在G3单元格输入读取月份的公式：=MONTH(E3)，在H3单元格输入读取日的公式：=DAY(E3)
1.3 填入生产数据：这里为了展示看板，手动录入了1月到12月每日的生产数据。
1.4 数据透视表：鼠标点击数据源表格任意单元格，依次点击插入—数据透视表。
1.4.1 在弹出的对话框点击确定，系统自动新建工作表。
1.4.2 将系统新建的工作表命名为“数据透视表”。
1.4.3 数据透视表字段设置：将“辅助列月”拖动到筛选区域，将“项目名”和“辅助列日”拖动到筛选区域，将“生产数量”和“不良数量”拖动到值区域。如下图所示。
2、 添加切片器：鼠标右键点击“辅助列月”，选择“添加为切片器”。
2.1切片器设置：设置切片器的列为12，调整切片器的高度，拖动切片器的外形大小到合适位置。
2.2切片器页眉设置。鼠标右键点击切片器选择“切片器设置”
2.2.1 将“显示页眉”前的勾取消。单击确定即可。
2.3将设置好的切片器剪切、粘贴至“index”工作表。
2.4切片器颜色设置：在“index”工作表选中“选项”再点击“新建切片器样式”
2.5 对每一个切片器的元素进行设置。
3、数据汇总：在数据汇总工作表中录入下图数据。在B1单元格输入公式：=数据透视表!B1,（从数据透视表获取选择的月份），在C1单元格输入公式：=IF(B1-1=0,12,B1-1)。获取选择月份上一月的月份（这里获取上一月的月份，是方便后面可视化看板相邻月份的数据对比）
3.1 用sumif函数获取每日的生产数量：在B3单元格输入公式：=SUMIF(数据透视表!$A:$A,数据汇总!B2,数据透视表!$B:$B)，并拖动填充公式至AF3单元格。
3.2用sumif函数获取每日不良数量：在B4单元格输入公式：=SUMIF(数据透视表!$A:$A,数据汇总!B2,数据透视表!$C:$C)，并拖动填充公式至AF4单元格。
3.3 在B5单元格输入直通率计算公式：=IFERROR(1-B4/B3,'')。并拖动填充公式至AF5单元格。
3.4 在B6单元格输入计算最大值的公式：=IF(B3=MAX($B$3:$AF$3),B3,'')。并拖动填充公式至AF6单元格。
3.5 在项目名下罗列出所有数据源录入的项目。
3.5.1 用SUMIF函数统计各项目的生产数量：在B11单元格输入公式：=SUMIF(数据透视表!$A:$A,[@项目名],数据透视表!$B:$B)。并拖动公式至有数据的最后一列（图为吧18）
3.5.2 用SUMIF函数统计各项目的不良数量：在C11单元格输入公式：=SUMIF(数据透视表!$A:$A,[@项目名],数据透视表!$C:$C)。并拖动公式至有数据的最后一列（图为吧18）
3.5.3 在D11单元格输入计算不良率的公式：=IFERROR([@不良数量]/[@生产数量],0)，并拖动至D18单元格
3.5.4 在E11单元格输入计算排名的公式：=RANK([@不良率],[不良率])+COUNTIF(D11:$D$18,D11)-1，并拖动至E18单元格。
3.6 各生产线生产数量统计：如下图所示，先列出数据源有的所有生产线名：
3.6.1 通过SUMIFS函数统计各生产线对应月份的生产数量。在M11单元格输入公式：=SUMIFS(表1[生产数量],表1[辅助列月],数据汇总!$B$1,表1[生产线],[@生产线])，并下拉填充至M15单元格。
3.6.2 通过SUMIFS函数统计各生产线对应月份的不良数量。在N11单元格输入公式：=SUMIFS(表1[不良数量],表1[辅助列月],数据汇总!$B$1,表1[生产线],[@生产线]),并下拉填充至N15单元格。
3.7 在G10到G12单元格分别输入1,2,3（方便后期按排名读取数据）
4、可视化界面
4.1在“index”工作表中按下图所示划分区域，
4.2 在项目不良数量统计输入公式：=数据汇总!B1&' 月生产项目不良数 TOP3'，输入公式后便可根据切片器的选择动态读取切片器选择的月份。按照同样的公式将所有图标标题输入。
4.3 生产数量排名：
4.3.1 通过VLOOKUP加if函数在“数据汇总”工作表获取不良率排名第一的项目，在B7单元格输入公式：=VLOOKUP(数据汇总!G10,IF({1,0},数据汇总!$E$11:$E$18,数据汇总!$A$11:$A$18),2,FALSE)。
4.3.2 通过VLOOKUP加if函数在“数据汇总”工作表获取不良率排名第二的项目，在B9单元格输入公式：=VLOOKUP(数据汇总!G11,IF({1,0},数据汇总!$E$11:$E$18,数据汇总!$A$11:$A$18),2,FALSE)。
4.3.3 通过VLOOKUP加if函数在“数据汇总”工作表获取不良率排名第三的项目，在B11单元格输入公式：=VLOOKUP(数据汇总!G12,IF({1,0},数据汇总!$E$11:$E$18,数据汇总!$A$11:$A$18),2,FALSE)。
4.3.4 通过Vlookup函数获取项目名对应的生产数量，在C7单元格输入公式：=VLOOKUP(B7,数据汇总!$A$11:$D$18,2,FALSE)，并下拉填充至C11单元格。
4.3.5 通过Vlookup函数获取项目名对应的不良数量，在D7单元格输入公式：=VLOOKUP(B7,数据汇总!$A$11:$D$18,3,FALSE)，并下拉填充至D11单元格。
4.3.6 通过Vlookup函数获取项目名对应的不良率，在E7单元格输入公式：=VLOOKUP(B7,数据汇总!$A$11:$D$18,4,FALSE)，并下拉填充至E11单元格。
4.3.7 插入一个矩形图形在如下图位置。
4.3.8 选择图片填充，并将图片透明度调至80%，如下图所示。
4.4 生产数量统计图表：在数据汇总工作表选中所有生产数量点击插入—条形图。（如下图所示）
4.4.1 将插入的条形图剪切至“index”工作表对应位置。并调整大小到合适位置，将图表填充选择图片填充，并调整图片透明度为80%。
4.4.3 右键图表，点击选择数据，添加系列2 的数据为“数量最大值”，并设置系列2 的图表颜色，图片为黑色到绿色的渐变色。选中绿色图标后点击“添加数据标签”。图表即可自动显示最大值的数据。
4.4.4 鼠标右键点击图表，选择“添加趋势线”。趋势线选项选择“平均”，周期设置为2即可。如下图绿色虚线为添加的“趋势线”。
4.4.5 图例中所有条形图填充颜色透明度为40%。
4.5 其他图表的添加方式与上面介绍的类似，均是插入图表类型和选择的数据源不同。有兴趣的可查看源文件，这里不再一一介绍。
联系客服
