數(shù)據(jù)透視表高級應(yīng)用案例

hercules028 2022-01-31

展開全文

SIMPLE HEADLINE

利用數(shù)據(jù)透視表進行銷售綜合分析

示例 6-16 多角度的銷售分析表和銷售分析圖

圖 6-85 展示的“歷史銷售數(shù)據(jù)”工作表中記錄了某公司一定時期內(nèi)的銷售及成本明細數(shù)據(jù)。

圖6-85歷史銷售數(shù)據(jù)明細表

對這樣一個龐大而且經(jīng)常增加記錄的數(shù)據(jù)列表進行數(shù)據(jù)分析，首先需要創(chuàng)建動態(tài)的數(shù)據(jù)透視表，并通過對數(shù)據(jù)透視表的重新布局得到按“商品年份”“商店名稱”和“季節(jié)名稱”等不同角度的分類匯總分析表，再通過不同的數(shù)據(jù)透視表生成相應(yīng)的數(shù)據(jù)透視圖得到一系列的分析報表，具體請參照以下步驟。

步驟 1新建一個 Excel 工作簿，將其命名為“多角度的銷售分析表和銷售分析圖 .xlsx”，打開該工作簿，將 Sheet1 工作表改名為“銷售分析”。

步驟 2在【數(shù)據(jù)】選項卡中單擊【現(xiàn)有連接】按鈕，在彈出【現(xiàn)有連接】的對話框中單擊【瀏覽更多】按鈕，打開【選取數(shù)據(jù)源】對話框，選擇要導(dǎo)入的目標文件的所在路徑，雙擊“銷售分析數(shù)據(jù)源 .xlsx”，打開【選擇表格】對話框，如圖 6-86 所示。

圖6-86激活【選取數(shù)據(jù)源】對話框

步驟 3保持【選擇表格】對話框中對名稱的默認選擇，單擊【確定】按鈕，激活【導(dǎo)入數(shù)據(jù)】對話框，單擊【數(shù)據(jù)透視表】選項按鈕，指定【數(shù)據(jù)的放置位置】為現(xiàn)有工作表的“$A$1”，單擊【確定】按鈕生成一張空白的數(shù)據(jù)透視表，如圖 6-87 所示。

　　圖6-87生成空白的數(shù)據(jù)透視表

步驟 4向數(shù)據(jù)透視表中添加相關(guān)字段，并在數(shù)據(jù)透視表中插入計算字段“毛利”，如圖 6-88所示。

計算公式為“毛利 = 銷售金額 - 成本金額”　

　　圖6-88按商品年份匯總的數(shù)據(jù)透視表

步驟 5單擊數(shù)據(jù)透視表中的任意一個單元格（如 A2），在【數(shù)據(jù)透視表工具】的【分析】選項卡中單擊【數(shù)據(jù)透視圖】按鈕，在彈出的【插入圖表】對話框中選擇【折線圖】選項卡中的“折線圖”圖表類型，單擊【確定】按鈕創(chuàng)建數(shù)據(jù)透視圖，如圖 6-89 所示。

圖6-89按商品年份的收入及成本利潤走勢分析圖

步驟 6對數(shù)據(jù)透視圖進行格式美化后如圖 6-90 所示。

圖 6-90　美化數(shù)據(jù)透視圖

步驟 7復(fù)制圖 6-88 所示的數(shù)據(jù)透視表，對數(shù)據(jù)透視表重新布局，創(chuàng)建數(shù)據(jù)透視圖，圖表類型選擇“餅圖”，得到按不同季節(jié)的銷售金額匯總表和銷售占比圖，如圖 6-91 所示。

圖6-91不同季節(jié)銷售占比分析圖

步驟 8再次復(fù)制圖 6-88 所示的數(shù)據(jù)透視表，對數(shù)據(jù)透視表重新布局，創(chuàng)建數(shù)據(jù)透視圖，圖表類型選擇“堆積柱形圖”，得到按銷售部門反映的收入及成本利潤匯總表和不同門店的對比分析圖，如圖 6-92 所示。

圖6-92門店銷售分析圖

本例通過對同一個數(shù)據(jù)透視表的不同布局得到各種不同角度的銷售分析匯總表，并通過創(chuàng)建數(shù)據(jù)透視圖來進行銷售走勢、銷售占比和門店對比等各種圖表分析，完成圖文并茂的多角度動態(tài)銷售分析報表，如圖 6-93 所示。

圖6-93圖文并茂的多角度動態(tài)銷售分析報表

制作物料動態(tài)進銷存模板

ENJOY THE SUMMER

示例 6-17 根據(jù)多工作表數(shù)據(jù)統(tǒng)計進銷存且支持自適應(yīng)路徑及文件名更改

圖 6-94 展示了某企業(yè)的物料進銷存模板，模板中包含了期初、入庫、出庫和進銷存四張工作表，分別放置了期初、入庫和出庫數(shù)據(jù)，進銷存表中則是按物料編碼統(tǒng)計進銷存信息的數(shù)據(jù)透視表。為了便于不同部門的人員查看數(shù)據(jù)，還需要具有文件所在路徑及文件名更改時，不影響數(shù)據(jù)透視表的跨表提取數(shù)據(jù)的功能。

圖6-94根據(jù)多工作表數(shù)據(jù)統(tǒng)計進銷存且支持自適應(yīng)路徑及文件名更改

本案例的關(guān)鍵解決思路：

? 利用 SQL 語句創(chuàng)建數(shù)據(jù)透視表實現(xiàn)從多工作表提取數(shù)據(jù)。

? 創(chuàng)建計算字段統(tǒng)計期末結(jié)存的數(shù)量和金額。

? 利用 VBA 使數(shù)據(jù)透視表支持自適應(yīng)路徑及文件名更改后的統(tǒng)計。

具體操作步驟如下。

步驟 1打開“根據(jù)多工作表數(shù)據(jù)統(tǒng)計進銷存且支持自適應(yīng)路徑及文件名更改”工作表，選中“進銷存”工作表的 A1 單元格，在【數(shù)據(jù)】選項卡中單擊【現(xiàn)有連接】按鈕，在彈出的【現(xiàn)有連接】對話框中單擊【瀏覽更多】按鈕，在彈出的【選取數(shù)據(jù)源】對話框中選擇文件所在位置（如桌面），選擇目標文件，單擊【打開】按鈕，在彈出的【選擇表格】對話框中選中“入庫 $”數(shù)據(jù)源表，如圖 6-95 所示。

圖6-95選擇創(chuàng)建進銷存匯總表的數(shù)據(jù)

步驟 2在【選擇表格】對話框中單擊【確定】按鈕，在彈出的【導(dǎo)入數(shù)據(jù)】對話框中選中【數(shù)據(jù)透視表】單選按鈕，單擊【屬性】按鈕，在彈出的【連接屬性】對話框中單擊【定義】選項卡，清空命令文本中的內(nèi)容并輸入 SQL 代碼，單擊【確定】按鈕，最后返回【導(dǎo)入數(shù)據(jù)】對話框，再次單擊【確定】按鈕，如圖 6-96 所示。

圖6-96輸入 SQL 語句

SQL 代碼如下：select *, 數(shù)量 as 入庫數(shù)量 ,0 as 出庫數(shù)量 ,0 as 期初數(shù)量 , 金額 as 入庫金額 ,0 as 出庫金額 ,0 as 期初金額 from [ 入庫 $] UNION ALL select *,0 as 入庫數(shù)量 , 數(shù)量 as 出庫數(shù)量 ,0 as 期初數(shù)量 ,0 as 入庫金額 ,金額 as 出庫金額 ,0 as 期初金額 from [ 出庫 $] UNION ALLselect *,0 AS 入庫數(shù)量 ,0 as 出庫數(shù)量 , 數(shù)量 as 期初數(shù)量 ,0 as 入庫金額 ,0 as 出庫金額 , 金額 as 期初金額 from [ 期初 $]

提示：此 SQL 語句的含義如下。

先使用子查詢語句 UNION ALL 將所有工作表的數(shù)據(jù)列表記錄匯總。由于不同工作表下相同字段名代表的含義不同，如字段名“數(shù)量”在期初、入庫和出庫表中的數(shù)量分別代表期初數(shù)量、入庫數(shù)量和出庫數(shù)量，所以用 as 別名標識符對字段重命名為易于識別的名稱。Excel 工作表在引用時需要將其包含在方括號內(nèi)“[]”，同時需要在其工作表名稱后面加上“$”符號，如 select * from [ 期初 $]

步驟 3在創(chuàng)建的空白數(shù)據(jù)透視表中進行字段布局，如圖 6-97 所示。

圖6-97設(shè)置數(shù)據(jù)透視表的字段布局

步驟 4在數(shù)據(jù)透視表中插入【期末數(shù)量】和【期末金額】計算字段，按照期初、入庫、出庫和結(jié)存的顯示順序調(diào)整數(shù)據(jù)透視表的字段，并美化數(shù)據(jù)透視表，如圖 6-98 所示。

期末數(shù)量 = 期初數(shù)量 + 入庫數(shù)量 - 出庫數(shù)量

期末金額 = 期初金額 + 入庫金額 - 出庫金額

圖6-98美化后的進銷存匯總表

步驟 5為了使數(shù)據(jù)透視表支持自適應(yīng)路徑及文件名更改，添加 VBA 代碼。單擊【開發(fā)工具】選項卡下的【Visual Basic】按鈕，在彈出的【Microsoft Visual Basic for Applications】對話框中，單擊【插入】→【模塊】命令，如圖 6-99 所示。

圖6-99在 VBE 界面添加模塊

步驟 6雙擊【模塊 1】，在代碼框中輸入以下代碼，如圖 6-100 所示。

圖6-100編輯模塊中的 VBA 代碼

Sub SQL 自適應(yīng)路徑和文件名更改 ()

Dim strCon As String, iPath As String ' 定義變量

Dim iT As Integer, jT As Integer, iFlag As String, iStr As String Dim sht As Worksheet

iPath = ThisWorkbook.FullName ' 獲取本工作簿的完全路徑

On Error Resume Next ' 防錯語句，當執(zhí)行代碼遇到錯誤時繼續(xù)運行后面的代碼

For Each sht In ThisWorkbook.Worksheets ' 遍歷工作簿中的每張工作表

iT = sht.PivotTables.Count ' 統(tǒng)計數(shù)據(jù)透視表的個數(shù)

If iT ＞ 0 Then

For jT = 1 To iT ' 遍歷工作簿中的每張工作表

strCon = sht.PivotTables(jT).PivotCache.Connection ' 將數(shù)據(jù)透視表中緩存連接信息賦值給變量

strCon Select Case Left(strCon, 5) ' 利用 select case語句判斷緩存連接信息中的數(shù)據(jù)連接方式是 ODBC 還是 OLEDB, 判斷方法為從 strCon 變量左側(cè)截取 5 個字符 Case 'ODBC;' ' 判斷緩存連接信息中的數(shù)據(jù)連接方式 , 如果是 ODBC 方式 iFlag = 'DBQ=' ' 將 'DBQ=' 賦值給變量 iFlag Case 'OLEDB' ' 判斷緩存連接信息中的數(shù)據(jù)連接方式 , 如果是 OLEDB 方式 iFlag = 'Source=' ' 將 'Source=' 賦值給變量 iFlag Case Else ' 沒有引入外部數(shù)據(jù)或其他方式，不予處理 Exit Sub End Select iStr = Split(Split(strCon, iFlag)(1), ';')(0) ' 利用 split 函數(shù) , 分隔符分別取 iFlag 變量和 ';' 為分隔符取得數(shù)據(jù)源和路徑在變量strCon 中截取文件路徑信息 With sht.PivotTables(jT).PivotCache ' 替換據(jù)透視表緩存信息中的文件完全路徑 .Connection = VBA.Replace(strCon, iStr, iPath) ' 利用 Connection 屬性把連接屬性里前面的文件夾路徑設(shè)置成當前工作簿的路徑 .CommandText = VBA.Replace(.CommandText, iStr, iPath) ' 利用 CommandText 屬性修改 SQL 語句的文件路徑為當前工作簿的文件路徑 End With Next End If NextEnd Sub

步驟 7雙擊【ThisWorkbook】，輸入以下代碼，如圖 6-101 所示。

Private Sub Workbook_Open() Call SQL 自適應(yīng)路徑和文件名更改End Sub

圖6-101編輯 ThisWorkbook 的 VBA 代碼

提示：如果用戶發(fā)現(xiàn)當輸入 VBA 代碼后，Excel 文件無法保存，請將文件另存為“Excel 啟用宏的工作簿 (*.xlsm)”類型。

至此，實現(xiàn)了數(shù)據(jù)透視表根據(jù)多工作表數(shù)據(jù)統(tǒng)計進銷存且支持自適應(yīng)路徑及文件名更改的需求。為了使 VBA 代碼能夠順利執(zhí)行，當開啟文件時遇到“安全警告部分活動內(nèi)容已被禁用。單擊此處了解詳細信息”時，需要單擊【啟用內(nèi)容】按鈕。

END