數據透視表高級應用案例

SIMPLE HEADLINE

利用數據透視表進行銷售綜合分析

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

圖 6-85 展示的“曆史銷售數據”工作表中記錄了某公司一定時期内的銷售及成本明細數據。

圖6-85曆史銷售數據明細表

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

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

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

圖6-86激活【選取數據源】對話框

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

　　圖6-87生成空白的數據透視表

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

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

　　圖6-88按商品年份彙總的數據透視表

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

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

步 驟 6對數據透視圖進行格式美化後如圖 6-90 所示。

圖 6-90　美化數據透視圖

步 驟 7複制圖 6-88 所示的數據透視表，對數據透視表重新布局，創建數據透視圖，圖表類型選擇“餅圖”，得到按不同季節的銷售金額彙總表和銷售占比圖，如圖 6-91 所示。

圖6-91不同季節銷售占比分析圖

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

圖6-92門店銷售分析圖

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

圖6-93圖文并茂的多角度動态銷售分析報表

制作物料動态進銷存模闆
ENJOY THE SUMMER

示例 6-17 根據多工作表數據統計進銷存且支持自适應路徑及文件名更改

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

圖6-94根據多工作表數據統計進銷存且支持自适應路徑及文件名更改

本案例的關鍵解決思路：

™ 利用 SQL 語句創建數據透視表實現從多工作表提取數據。

™ 創建計算字段統計期末結存的數量和金額。

™ 利用 VBA 使數據透視表支持自适應路徑及文件名更改後的統計。

具體操作步驟如下。

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

圖6-95選擇創建進銷存彙總表的數據

步 驟 2在【選擇表格】對話框中單擊【确定】按鈕，在彈出的【導入數據】對話框中選中【數據透視表】單選按鈕，單擊【屬性】按鈕，在彈出的【連接屬性】對話框中單擊【定義】選項卡，清空命令文本中的内容并輸入 SQL 代碼，單擊【确定】按鈕，最後返回【導入數據】對話框，再次單擊【确定】按鈕，如圖 6-96 所示。

圖6-96輸入 SQL 語句

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

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

先使用子查詢語句 UNION ALL 将所有工作表的數據列表記錄彙總。由于不同工作表下相同字段名代表的含義不同，如字段名“數量”在期初、入庫和出庫表中的數量分别代表期初數量、入庫數量和出庫數量，所以用 as 别名标識符對字段重命名為易于識别的名稱。Excel 工作表在引用時需要将其包含在方括号内“[]”，同時需要在其工作表名稱後面加上“$”符号，如 select * from [ 期初 $]

步 驟 3在創建的空白數據透視表中進行字段布局，如圖 6-97 所示。

圖6-97設置數據透視表的字段布局

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

期末數量 = 期初數量 + 入庫數量 - 出庫數量

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

圖6-98美化後的進銷存彙總表

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

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

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

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

Sub SQL 自适應路徑和文件名更改 ()

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 ' 防錯語句，當執行代碼遇到錯誤時繼續運行後面的代碼

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

iT = sht.PivotTables.Count ' 統計數據透視表的個數

If iT ＞ 0 Then

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

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

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

圖6-101編輯 ThisWorkbook 的 VBA 代碼

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

至此，實現了數據透視表根據多工作表數據統計進銷存且支持自适應路徑及文件名更改的需求。為了使 VBA 代碼能夠順利執行，當開啟文件時遇到“安全警告 部分活動内容已被禁用。單擊此處了解詳細信息”時，需要單擊【啟用内容】按鈕。

END