切片器加數(shù)據(jù)透視表制作可視化看板
本文以最新發(fā)布的作品《可視化看板》進(jìn)行講解。
主要內(nèi)容介紹：
數(shù)據(jù)透視表
切片器及切片器設(shè)置
1.1 首先創(chuàng)建三個(gè)工作表。分別命名為“index”，“數(shù)據(jù)源”，“數(shù)據(jù)匯總”如下圖所示。并在數(shù)據(jù)源工作表中從A列自H列輸入如下列標(biāo)題。
1.2 在F3單元格輸入計(jì)算合格率的公式：=(C3-D3)/C3，在G3單元格輸入讀取月份的公式：=MONTH(E3)，在H3單元格輸入讀取日的公式：=DAY(E3)
1.3 填入生產(chǎn)數(shù)據(jù)：這里為了展示看板，手動(dòng)錄入了1月到12月每日的生產(chǎn)數(shù)據(jù)。
1.4 數(shù)據(jù)透視表：鼠標(biāo)點(diǎn)擊數(shù)據(jù)源表格任意單元格，依次點(diǎn)擊插入—數(shù)據(jù)透視表。
1.4.1 在彈出的對話框點(diǎn)擊確定，系統(tǒng)自動(dòng)新建工作表。
1.4.2 將系統(tǒng)新建的工作表命名為“數(shù)據(jù)透視表”。
1.4.3 數(shù)據(jù)透視表字段設(shè)置：將“輔助列月”拖動(dòng)到篩選區(qū)域，將“項(xiàng)目名”和“輔助列日”拖動(dòng)到篩選區(qū)域，將“生產(chǎn)數(shù)量”和“不良數(shù)量”拖動(dòng)到值區(qū)域。如下圖所示。
2、 添加切片器：鼠標(biāo)右鍵點(diǎn)擊“輔助列月”，選擇“添加為切片器”。
2.1切片器設(shè)置：設(shè)置切片器的列為12，調(diào)整切片器的高度，拖動(dòng)切片器的外形大小到合適位置。
2.2切片器頁眉設(shè)置。鼠標(biāo)右鍵點(diǎn)擊切片器選擇“切片器設(shè)置”
2.2.1 將“顯示頁眉”前的勾取消。單擊確定即可。
2.3將設(shè)置好的切片器剪切、粘貼至“index”工作表。
2.4切片器顏色設(shè)置：在“index”工作表選中“選項(xiàng)”再點(diǎn)擊“新建切片器樣式”
2.5 對每一個(gè)切片器的元素進(jìn)行設(shè)置。
3、數(shù)據(jù)匯總：在數(shù)據(jù)匯總工作表中錄入下圖數(shù)據(jù)。在B1單元格輸入公式：=數(shù)據(jù)透視表!B1,（從數(shù)據(jù)透視表獲取選擇的月份），在C1單元格輸入公式：=IF(B1-1=0,12,B1-1)。獲取選擇月份上一月的月份（這里獲取上一月的月份，是方便后面可視化看板相鄰月份的數(shù)據(jù)對比）
3.1 用sumif函數(shù)獲取每日的生產(chǎn)數(shù)量：在B3單元格輸入公式：=SUMIF(數(shù)據(jù)透視表!$A:$A,數(shù)據(jù)匯總!B2,數(shù)據(jù)透視表!$B:$B)，并拖動(dòng)填充公式至AF3單元格。
3.2用sumif函數(shù)獲取每日不良數(shù)量：在B4單元格輸入公式：=SUMIF(數(shù)據(jù)透視表!$A:$A,數(shù)據(jù)匯總!B2,數(shù)據(jù)透視表!$C:$C)，并拖動(dòng)填充公式至AF4單元格。
3.3 在B5單元格輸入直通率計(jì)算公式：=IFERROR(1-B4/B3,'')。并拖動(dòng)填充公式至AF5單元格。
3.4 在B6單元格輸入計(jì)算最大值的公式：=IF(B3=MAX($B$3:$AF$3),B3,'')。并拖動(dòng)填充公式至AF6單元格。
3.5 在項(xiàng)目名下羅列出所有數(shù)據(jù)源錄入的項(xiàng)目。
3.5.1 用SUMIF函數(shù)統(tǒng)計(jì)各項(xiàng)目的生產(chǎn)數(shù)量：在B11單元格輸入公式：=SUMIF(數(shù)據(jù)透視表!$A:$A,[@項(xiàng)目名],數(shù)據(jù)透視表!$B:$B)。并拖動(dòng)公式至有數(shù)據(jù)的最后一列（圖為吧18）
3.5.2 用SUMIF函數(shù)統(tǒng)計(jì)各項(xiàng)目的不良數(shù)量：在C11單元格輸入公式：=SUMIF(數(shù)據(jù)透視表!$A:$A,[@項(xiàng)目名],數(shù)據(jù)透視表!$C:$C)。并拖動(dòng)公式至有數(shù)據(jù)的最后一列（圖為吧18）
3.5.3 在D11單元格輸入計(jì)算不良率的公式：=IFERROR([@不良數(shù)量]/[@生產(chǎn)數(shù)量],0)，并拖動(dòng)至D18單元格
3.5.4 在E11單元格輸入計(jì)算排名的公式：=RANK([@不良率],[不良率])+COUNTIF(D11:$D$18,D11)-1，并拖動(dòng)至E18單元格。
3.6 各生產(chǎn)線生產(chǎn)數(shù)量統(tǒng)計(jì)：如下圖所示，先列出數(shù)據(jù)源有的所有生產(chǎn)線名：
3.6.1 通過SUMIFS函數(shù)統(tǒng)計(jì)各生產(chǎn)線對應(yīng)月份的生產(chǎn)數(shù)量。在M11單元格輸入公式：=SUMIFS(表1[生產(chǎn)數(shù)量],表1[輔助列月],數(shù)據(jù)匯總!$B$1,表1[生產(chǎn)線],[@生產(chǎn)線])，并下拉填充至M15單元格。
3.6.2 通過SUMIFS函數(shù)統(tǒng)計(jì)各生產(chǎn)線對應(yīng)月份的不良數(shù)量。在N11單元格輸入公式：=SUMIFS(表1[不良數(shù)量],表1[輔助列月],數(shù)據(jù)匯總!$B$1,表1[生產(chǎn)線],[@生產(chǎn)線]),并下拉填充至N15單元格。
3.7 在G10到G12單元格分別輸入1,2,3（方便后期按排名讀取數(shù)據(jù)）
4、可視化界面
4.1在“index”工作表中按下圖所示劃分區(qū)域，
4.2 在項(xiàng)目不良數(shù)量統(tǒng)計(jì)輸入公式：=數(shù)據(jù)匯總!B1&' 月生產(chǎn)項(xiàng)目不良數(shù) TOP3'，輸入公式后便可根據(jù)切片器的選擇動(dòng)態(tài)讀取切片器選擇的月份。按照同樣的公式將所有圖標(biāo)標(biāo)題輸入。
4.3 生產(chǎn)數(shù)量排名：
4.3.1 通過VLOOKUP加if函數(shù)在“數(shù)據(jù)匯總”工作表獲取不良率排名第一的項(xiàng)目，在B7單元格輸入公式：=VLOOKUP(數(shù)據(jù)匯總!G10,IF({1,0},數(shù)據(jù)匯總!$E$11:$E$18,數(shù)據(jù)匯總!$A$11:$A$18),2,FALSE)。
4.3.2 通過VLOOKUP加if函數(shù)在“數(shù)據(jù)匯總”工作表獲取不良率排名第二的項(xiàng)目，在B9單元格輸入公式：=VLOOKUP(數(shù)據(jù)匯總!G11,IF({1,0},數(shù)據(jù)匯總!$E$11:$E$18,數(shù)據(jù)匯總!$A$11:$A$18),2,FALSE)。
4.3.3 通過VLOOKUP加if函數(shù)在“數(shù)據(jù)匯總”工作表獲取不良率排名第三的項(xiàng)目，在B11單元格輸入公式：=VLOOKUP(數(shù)據(jù)匯總!G12,IF({1,0},數(shù)據(jù)匯總!$E$11:$E$18,數(shù)據(jù)匯總!$A$11:$A$18),2,FALSE)。
4.3.4 通過Vlookup函數(shù)獲取項(xiàng)目名對應(yīng)的生產(chǎn)數(shù)量，在C7單元格輸入公式：=VLOOKUP(B7,數(shù)據(jù)匯總!$A$11:$D$18,2,FALSE)，并下拉填充至C11單元格。
4.3.5 通過Vlookup函數(shù)獲取項(xiàng)目名對應(yīng)的不良數(shù)量，在D7單元格輸入公式：=VLOOKUP(B7,數(shù)據(jù)匯總!$A$11:$D$18,3,FALSE)，并下拉填充至D11單元格。
4.3.6 通過Vlookup函數(shù)獲取項(xiàng)目名對應(yīng)的不良率，在E7單元格輸入公式：=VLOOKUP(B7,數(shù)據(jù)匯總!$A$11:$D$18,4,FALSE)，并下拉填充至E11單元格。
4.3.7 插入一個(gè)矩形圖形在如下圖位置。
4.3.8 選擇圖片填充，并將圖片透明度調(diào)至80%，如下圖所示。
4.4 生產(chǎn)數(shù)量統(tǒng)計(jì)圖表：在數(shù)據(jù)匯總工作表選中所有生產(chǎn)數(shù)量點(diǎn)擊插入—條形圖。（如下圖所示）
4.4.1 將插入的條形圖剪切至“index”工作表對應(yīng)位置。并調(diào)整大小到合適位置，將圖表填充選擇圖片填充，并調(diào)整圖片透明度為80%。
4.4.3 右鍵圖表，點(diǎn)擊選擇數(shù)據(jù)，添加系列2 的數(shù)據(jù)為“數(shù)量最大值”，并設(shè)置系列2 的圖表顏色，圖片為黑色到綠色的漸變色。選中綠色圖標(biāo)后點(diǎn)擊“添加數(shù)據(jù)標(biāo)簽”。圖表即可自動(dòng)顯示最大值的數(shù)據(jù)。
4.4.4 鼠標(biāo)右鍵點(diǎn)擊圖表，選擇“添加趨勢線”。趨勢線選項(xiàng)選擇“平均”，周期設(shè)置為2即可。如下圖綠色虛線為添加的“趨勢線”。
4.4.5 圖例中所有條形圖填充顏色透明度為40%。
4.5 其他圖表的添加方式與上面介紹的類似，均是插入圖表類型和選擇的數(shù)據(jù)源不同。有興趣的可查看源文件，這里不再一一介紹。
