可視化看板教程（低代碼）

效果圖

說明：本文以最近發(fā)布的《生產(chǎn)質(zhì)量可視化看板》為例，詳細(xì)介紹制作步驟。包含代碼及注釋。所有公式全部列出。以圖文結(jié)合的方式展現(xiàn)。

主要內(nèi)容介紹：

可視化界面的布局

數(shù)據(jù)的錄入與讀取

信息匯總分類

1、可視化界面的布局

設(shè)置工作表的大小,一般設(shè)置為橫向，頁邊距0.2，表格設(shè)置見下圖。

1.1看板底色設(shè)置。選中表格區(qū)域填充。這里填充為藍(lán)色

1.2可視化界面布局。將要展示的信息按區(qū)域劃分。方便后面數(shù)據(jù)的輸出展示。對(duì)應(yīng)區(qū)域用深藍(lán)色填充。

2、數(shù)據(jù)錄入與讀取

2.1錄入數(shù)據(jù)之前需要新建一個(gè)工作表作為數(shù)據(jù)源（也可以稱之為數(shù)據(jù)庫(kù)），方便我們數(shù)據(jù)錄入后存儲(chǔ)。點(diǎn)擊“+”創(chuàng)建圖標(biāo)后命名為“數(shù)據(jù)源”。

2.2 數(shù)據(jù)源表格的設(shè)置：將我們需要錄入數(shù)據(jù)的信息橫向輸入在數(shù)據(jù)源的表格內(nèi)并按”CTRL+T”創(chuàng)建表。紅色框選區(qū)域數(shù)據(jù)不需要錄入，可根據(jù)前面的錄入數(shù)據(jù)通過公式計(jì)算。

2.2.1 合格率計(jì)算公式：=(C3-D3)/C3

2.2.2 月份轉(zhuǎn)換公式：=MONTH(F3)

2.2.3 日轉(zhuǎn)換公式：=DAY(F3)

2.2.4 季度轉(zhuǎn)換公式：=CHOOSE([@月],1,1,1,2,2,2,3,3,3,4,4,4)

2.3 創(chuàng)建好數(shù)據(jù)庫(kù)和錄入公式后可手動(dòng)在數(shù)據(jù)庫(kù)內(nèi)錄入幾條數(shù)據(jù)測(cè)試是否有問題。無問題后進(jìn)入下一步。

2.4 從看板錄入數(shù)據(jù)：

2.4.1在看板對(duì)應(yīng)位置輸入要錄入數(shù)據(jù)的標(biāo)題和輸入框。

2.4.2 在開發(fā)工具內(nèi)插入按鈕，如下圖所示：

2.4.3 將按鈕改名為“錄入”。并將按鈕拖放到合適位置。

2.4.4 進(jìn)入VBA界面。

2.4.5 插入模塊：點(diǎn)擊右邊空白處，選擇插入-模塊。

2.4.6 在模塊內(nèi)輸入代碼

Sub 數(shù)據(jù)錄入() '創(chuàng)建數(shù)據(jù)錄入放入宏

a = Sheet2.Cells(Rows.Count, 1).End(xlUp)(2, 1).Row 數(shù)據(jù)源單元格從下往上數(shù)，讀取第一列最后一個(gè)非空單元格的值并下移一行。（a 為下圖的行號(hào)。）

If Sheet1.Cells(6, 2) = '' Or Sheet1.Cells(6, 3) = '' Or Sheet1.Cells(6, 4) = '' Then

（Sheet1.Cells(6, 2)：項(xiàng)目名輸入框的行號(hào)和列號(hào)）

MsgBox '請(qǐng)先輸入相關(guān)信息！'

判斷輸入框是否有輸入內(nèi)容，如過未輸入則彈出'請(qǐng)先輸入相關(guān)信息！'

的提示框。

Exit Sub

退出宏

End If

數(shù)據(jù)賦值，將輸入框的內(nèi)容賦值到數(shù)據(jù)庫(kù)對(duì)應(yīng)位置， a為行號(hào)

Sheet2.Cells(a, 1) = Sheet1.Cells(6, 2) '項(xiàng)目名賦值

Sheet2.Cells(a, 2) = Sheet1.Cells(6, 3) '生產(chǎn)線賦值

Sheet2.Cells(a, 3) = Sheet1.Cells(6, 4) '生產(chǎn)數(shù)量賦值

Sheet2.Cells(a, 5) = Sheet1.Cells(8, 2) '責(zé)任歸屬賦值

Sheet2.Cells(a, 4) = Sheet1.Cells(8, 3) '不良數(shù)量賦值

Sheet2.Cells(a, 6) = Sheet1.Cells(3, 17) '日期賦值

MsgBox '信息錄入成功！'

Sheet1.Range('b6:d6') = '' '單元格清空

Sheet1.Range('b8:c8') = '' '單元格清空

End Sub

2.4.7 指定宏:選擇剛剛插入的“錄入”按鈕，右鍵選擇指定宏，選擇剛剛在模塊中創(chuàng)建的宏即可。

2.5 季度數(shù)據(jù)統(tǒng)計(jì)表：在數(shù)據(jù)源表格中通過公式獲得每一個(gè)季度的數(shù)據(jù)統(tǒng)計(jì)。

2.5.1 一季度生產(chǎn)數(shù)量公式：=SUMIF(表1[季度],1,表1[生產(chǎn)數(shù)量])。其他季度只需修改公式中的數(shù)字1為對(duì)應(yīng)季度即可。

2.5.2 一季度不良數(shù)量公式：=SUMIF(表1[季度],1,表1[不良數(shù)量])。其他季度只需修改公式中的數(shù)字1為對(duì)應(yīng)季度即可。

2.5.3 合格率可直接根據(jù)生產(chǎn)數(shù)量和不良數(shù)量直接獲得：=IFERROR((Q3-R3)/Q3,'0')

2.5.4 將季度數(shù)據(jù)顯示在看板頁面：在看板對(duì)應(yīng)位置輸入“=”號(hào)后選擇季度統(tǒng)計(jì)表中對(duì)應(yīng)的值即可。將四個(gè)季度對(duì)應(yīng)的值全部用同樣方式輸入即可。

2.6 數(shù)據(jù)透視表：選擇數(shù)據(jù)源的數(shù)據(jù)表格，插入數(shù)據(jù)透視表。

2.6.1彈出的對(duì)話框點(diǎn)擊確定，并將新的表格命名為數(shù)據(jù)透視表。

2.6.2 在數(shù)據(jù)字段列表區(qū)域按下圖拖動(dòng)字段到對(duì)應(yīng)位置。

2.6.3 添加切片器：將月份字段添加為切片器（這樣便可通過選擇月份任意顯示對(duì)應(yīng)月份的數(shù)據(jù)）

2.6.4 新建“數(shù)據(jù)統(tǒng)計(jì)”工作表，下圖藍(lán)色區(qū)域手動(dòng)輸入，方便統(tǒng)計(jì)指定月份每日數(shù)據(jù)。

2.6.5 在生產(chǎn)數(shù)量位置輸入公式：=SUMIF(數(shù)據(jù)透視表!$A:$A,數(shù)據(jù)統(tǒng)計(jì)!B2,數(shù)據(jù)透視表!$B:$B) 將公式向右拉動(dòng)填充至31位置

2.6.6 在生產(chǎn)數(shù)量位置輸入公式：=SUMIF(數(shù)據(jù)透視表!$A:$A,數(shù)據(jù)統(tǒng)計(jì)!B2,數(shù)據(jù)透視表!$C:$C) 將公式向右拉動(dòng)填充值31位置

2.6.7 在合格率位置輸入公式：=IFERROR((B3-B4)/B3,'') 將公式向右拉動(dòng)填充值31位置

2.6.8 將輔助列所有位置輸入1.1

2.7 插入條形圖：選擇合格率所有數(shù)據(jù)（下圖紅框區(qū)域）點(diǎn)擊插入圖表。

2.7.1 選擇圖表。右鍵選擇數(shù)據(jù)

2.7.2 添加輔助列數(shù)據(jù)：點(diǎn)擊添加按鈕。

2.7.3 在紅色框區(qū)域選擇輔助列的值。并點(diǎn)擊排序按鈕，將系列2的值排到上面，如下圖所示。

2.7.4 圖表設(shè)置：將圖表系列重疊調(diào)為100%。然后再將系列2的圖表填充設(shè)置為無填充，將邊框顏色設(shè)置為綠色。將系列1的圖表填充為綠色，邊框設(shè)置為無填充。

2.7.5 設(shè)置好的圖表如下圖：

2.8 將設(shè)置好的圖表剪切到看板主界面對(duì)應(yīng)位置，并拖動(dòng)大小。如下圖所示。

2.9 切片器設(shè)置：將數(shù)據(jù)透視表的切片器剪切至看板主界面對(duì)應(yīng)位置：在設(shè)計(jì)位置輸入12（對(duì)應(yīng)12個(gè)月份），并調(diào)節(jié)寬度與高度到合適位置。

3、下拉信息設(shè)置：

3.1 新建下拉信息工作表，并在工作表中輸入對(duì)應(yīng)內(nèi)容，按Ctrl+t創(chuàng)建超級(jí)表。

3.2 名稱管理器：選中對(duì)應(yīng)表格依次點(diǎn)擊公式—根據(jù)所選內(nèi)容創(chuàng)建定義名稱—勾選首行—確定即可。按同樣的方式將三個(gè)表格設(shè)置完成。

3.3 下拉信息設(shè)置：選中看板界面對(duì)應(yīng)的輸入框，依次選擇數(shù)據(jù)--數(shù)據(jù)驗(yàn)證—序列，設(shè)置需要下拉選擇的輸入框。

4.信息匯總分類

4.1 根據(jù)下拉信息表格進(jìn)行數(shù)據(jù)的匯總統(tǒng)計(jì)：過sumif函數(shù)對(duì)數(shù)據(jù)透視表中不良數(shù)量求和統(tǒng)計(jì)再通過rank函數(shù)進(jìn)行排名統(tǒng)計(jì)：

4.1.1 不良數(shù)量計(jì)算公式：=SUMIF(數(shù)據(jù)透視表!$A:$A,[@生產(chǎn)線],數(shù)據(jù)透視表!$C:$C)

4.1.2 排名計(jì)算公式：=RANK([@不良數(shù)量],[不良數(shù)量])+COUNTIF(K4:$K$8,K4)-1

4.2 根據(jù)下拉信息表格良品責(zé)任歸屬統(tǒng)計(jì)：通過sumif函數(shù)對(duì)數(shù)據(jù)透視表中不同責(zé)任歸屬求和統(tǒng)計(jì)：

4.2.1 月度不良數(shù)量公式：=SUMIF(數(shù)據(jù)透視表!$A:$A,[@責(zé)任歸屬],數(shù)據(jù)透視表!$C:$C)

4.2.2 占比公式：=[@月度不良數(shù)量]/$P$2 （P2為月度不良總數(shù)）

4.3 年度數(shù)據(jù)統(tǒng)計(jì)匯總：通過通過sumif函數(shù)對(duì)數(shù)據(jù)源數(shù)據(jù)匯總。再通過rank函數(shù)進(jìn)行排名。

4.3.1 生產(chǎn)數(shù)量公式：=SUMIF(數(shù)據(jù)源!$A:$A,[@項(xiàng)目名],數(shù)據(jù)源!$C:$C)

4.3.2 不良數(shù)量公式：=SUMIF(數(shù)據(jù)源!$A:$A,[@項(xiàng)目名],數(shù)據(jù)源!$D:$D)

4.3.3 排名公式：=RANK([@合格率],[合格率])+COUNTIF(E4:$E$11,E4)-1

4.4 可視化數(shù)據(jù)呈現(xiàn)：

4.4.1 不良排名表格呈現(xiàn)：通過VLOOKUP+if函數(shù)反向查找對(duì)應(yīng)排名的生產(chǎn)線和不良數(shù)量。公式如下

生產(chǎn)線獲取公式：=IFERROR(VLOOKUP(B13,IF({1,0},表3[[#全部],[排名]],表3[[#全部],[生產(chǎn)線]]),2,FALSE),'')

數(shù)量獲取公式：=IFERROR(VLOOKUP(C13,下拉信息!$J:$K,2,FALSE),'')

4.4.2 不良品責(zé)任歸屬：選擇責(zé)任歸屬和占比數(shù)據(jù)插入旭日?qǐng)D或環(huán)形圖即可。將插入的圖表剪切至看板對(duì)應(yīng)位置，調(diào)整大小和背景顏色即可（這里不詳細(xì)說明）

4.5全年信息匯總：全年信息匯總也是通過VLOOKUP+IF函數(shù)，通過排名反向查找項(xiàng)目名，生產(chǎn)數(shù)量、不良數(shù)量等信息（4.4.1節(jié)可查看詳細(xì)公式）