基于EXCEL的商品銷售統計分析系統的構建

摘要：在當今的信息時代，各行各業都在采用計算機及相應的信息技術進行管理和決策，這使得各企事業單位分析與處理數據的能力大大增強。EXCEL電子表格以其強大的數據處理與數據分析功能，已被廣泛應用于社會經濟生活的各個領域。本文利用EXCEL的VBA構建了一個商品銷售統計分析系統，僅供同行參考使用。

關鍵詞：EXCEL 商品銷售 統計分析 系統 構建

前言

VBA（Visual Basic for Applications）是Microsoft Office 系列的內置編程語言，是非常流行的應用程序開發語言VB(Visual Basic) 的子集。它“寄生于”Office應用程序，是Office XP套裝軟件的一個十分重要的組件。它功能強大，面向對象，利用它可以將繁瑣、機械的日常工作自動化，從而極大地提高了用戶的辦公效率。

EXCEL的商品銷售統計分析系統是由一個口令系統、一個窗體界面和十二個工作表組成。通過窗體界面進入各工作表，實現各指標的查詢與統計分析。

⒈進入系統界面的口令系統

打開EXCEL工作簿，右擊菜單的空白處→選擇visual basic命令→進入VBA編輯環境→打開【工程資源管理器】窗口→雙擊【VBAProject】中的【ThisWorkBook】→切換至代碼編輯狀態→定義一個KL()函數。

其中的代碼如下：

Sub kl()

Dim message， title， defa， value

message = \"請輸入用戶名\"

title = \"用戶名\"

defa = \"ly\"

value = InputBox(message， title， defa)

pass = InputBox(\"請輸入密碼\"， \"密碼\")

If pass = \"123\" Then

Workbooks(\"窗口界面.xls\").Windows(1).Visible = False

UserForm1.Show

Else

MsgBox (\"密碼不正確\")

ThisWorkbook.Save

ThisWorkbook.Close

End If

End Sub

代碼含義：首先進入一個“用戶名”對話框，需用戶輸入用戶名“LY”，打開“密碼”對話框，輸入密碼“123”假如輸入正確，顯示下面的窗體界面，否則顯示“密碼不正確”，退出系統。

注意：

當用戶啟動此工作簿時，定義的函數并不會自動調出，需要在啟動這個工作簿文件時顯示此菜單，在代碼窗口選擇【對象】為“WorkBook”，【方法】為“Open”。

其中代碼如下：

Private Sub Workbook_Open()

Call kl

表示在啟動工作簿時，自動調動上述設置的KL（）函數

2.商品銷售統計分析系統窗體界面

單擊【插入】菜單→選擇【用戶窗體】→進入【用戶窗體】設置界面→右擊該窗體→選擇【屬性】→打開【屬性】窗口→設置【Caption】屬性值為“歡迎進入商品銷售統計分析系統”；添加三個【文本框】→分別設置【Caption】屬性值為“商品銷售統計分析系統”、“基本數據查詢”、“統計分析內容”；添加十二個【按鈕】→分別設置【Caption】屬性值如圖1-1所示。

右擊各按鈕，選擇【查看代碼】→選擇【查看代碼】→選擇【方法】為“Click”（單擊），分別編寫按鈕的代碼如下：

代碼含義：所建的工作簿名稱為“sp.xls”，保存在E盤根目錄下，Workbooks.Open Filename:=\"E:\\sp.xls\" 為打開該工作簿， Sheets(\"1季度\").Select 為顯示1季度工作表的內容，其它同理。

⒊建立各工作表的內容

各季度的原始數據分別存放在“1季度”、“2季度”、“3季度”、“4季度”工作表中，再分別插入各工作表分別命名為：“全年”、“篩選”、“圖表顯示”、“分類匯總”、“按業務人員查詢”、“按訂貨單位查詢”、“按產品查詢”。

（1）編輯“全年”工作表的內容

復制“產品代號”、“產品名稱”、“訂貨單位”、“業務員”、“單價”各字段內容至“全年”工作表中。

合并計算全年的銷售量與銷售額。其方法是：選中F2單元格→單擊【數據】菜單→選擇【合并計算】→在打開的【合并計算】對話框中，作如圖1-3的設置，單擊確定即可求出合年的銷售量。銷售額計算同理。

（2）編輯“篩選”工作表的內容

將“全年”工作表的數據復制到“篩選”工作表中。EXCEL包含兩種篩選功能，分別是自動篩選和高級篩選。進入自動篩選的方法是：單擊【數據】菜單→選擇【篩選】→【自動篩選】在各字段右下方自動產生一個下拉按鈕，打開各按鈕可根據需要進行選擇查詢；如果要進行較復雜的查詢，可通過“高級篩選”功能完成。具體操作如下：（如查詢產品名稱為“三一牌”而且銷售額大于“20000元”的商品信息）

在“篩選”工作表的任意位置輸入如圖1-2所示的內容，單擊【數據】菜單→選擇【篩選】→【高級篩選】→在打開的【高級篩選】對話框中作如圖1-3的設置。其中“條件區域”應選擇I2:J3單元格區域。這樣產品名稱為“三一牌”而且銷售額大于“20000元”即可篩選出來。如要查詢產品名稱為“三一牌”或者銷售額大于“20000元”的商品信息，應將“條件區域”中的“銷售額>20000”填在J4單元格中，“條件區域”即為I2:J4單元格區域。

（3）編輯“分類匯總”工作表的內容

將“全年”工作表的數據復制到“分類匯總”工作表中，單擊【數據】菜單→選擇【分類匯總】→在打開的【分類匯總】對話框中作如圖1-4的設置。其中：【分類字段】可選擇“產品名稱”、“訂貨單位”、“業務員”等；【匯總方式】可選擇“求和”、“求平均值” “最大值”、“最小值”、“計數”等。

（4）編輯“按業務人員統計”工作表的內容

將“全年”工作表的數據復制到“按業務人員統計”工作表中，單擊【數據】菜單→選擇【數據透視表和數據透視圖】→在打開的【數據透視表和數據透視圖向導】對話框中單擊【下一步】和【完成】按鈕→將“業務員”字段拖至行字段，將“產品名稱”字段拖至列字段，將“銷售額”字段拖至數據項，形成結果如圖1-5所示。單擊業務員右側的下拉按鈕可按業務人員分別查詢其銷售額。

（5）圖表顯示

復制分類匯總的數據至“圖表顯示”工作表中，單擊【圖表向導】，可根據需要選擇“柱形圖”或“折線圖”進行繪制。

小結：

本文只是EXCEL在統計分析中的一個簡單應用，熟悉VBA程序的用戶可以開發出功能更加強大的EXCEL應用程序。

參考文獻：

[1] EXCEL HOME 編著 《EXCEL VBA精粹》 中國郵電出版社2008年8月第1版

注：本文中所涉及到的圖表、注解、公式等內容請以PDF格式閱讀原文