基于EXCEL的商品銷(xiāo)售統(tǒng)計(jì)分析系統(tǒng)的構(gòu)建

摘要：在當(dāng)今的信息時(shí)代，各行各業(yè)都在采用計(jì)算機(jī)及相應(yīng)的信息技術(shù)進(jìn)行管理和決策，這使得各企事業(yè)單位分析與處理數(shù)據(jù)的能力大大增強(qiáng)。EXCEL電子表格以其強(qiáng)大的數(shù)據(jù)處理與數(shù)據(jù)分析功能，已被廣泛應(yīng)用于社會(huì)經(jīng)濟(jì)生活的各個(gè)領(lǐng)域。本文利用EXCEL的VBA構(gòu)建了一個(gè)商品銷(xiāo)售統(tǒng)計(jì)分析系統(tǒng)，僅供同行參考使用。

關(guān)鍵詞：EXCEL 商品銷(xiāo)售 統(tǒng)計(jì)分析 系統(tǒng) 構(gòu)建

前言

VBA（Visual Basic for Applications）是Microsoft Office 系列的內(nèi)置編程語(yǔ)言，是非常流行的應(yīng)用程序開(kāi)發(fā)語(yǔ)言VB(Visual Basic) 的子集。它“寄生于”O(jiān)ffice應(yīng)用程序，是Office XP套裝軟件的一個(gè)十分重要的組件。它功能強(qiáng)大，面向?qū)ο螅盟梢詫⒎爆崱C(jī)械的日常工作自動(dòng)化，從而極大地提高了用戶的辦公效率。

EXCEL的商品銷(xiāo)售統(tǒng)計(jì)分析系統(tǒng)是由一個(gè)口令系統(tǒng)、一個(gè)窗體界面和十二個(gè)工作表組成。通過(guò)窗體界面進(jìn)入各工作表，實(shí)現(xiàn)各指標(biāo)的查詢與統(tǒng)計(jì)分析。

⒈進(jìn)入系統(tǒng)界面的口令系統(tǒng)

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

其中的代碼如下：

Sub kl()

Dim message， title， defa， value

message = \"請(qǐng)輸入用戶名\"

title = \"用戶名\"

defa = \"ly\"

value = InputBox(message， title， defa)

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

If pass = \"123\" Then

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

UserForm1.Show

Else

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

ThisWorkbook.Save

ThisWorkbook.Close

End If

End Sub

代碼含義：首先進(jìn)入一個(gè)“用戶名”對(duì)話框，需用戶輸入用戶名“LY”，打開(kāi)“密碼”對(duì)話框，輸入密碼“123”假如輸入正確，顯示下面的窗體界面，否則顯示“密碼不正確”，退出系統(tǒng)。

注意：

當(dāng)用戶啟動(dòng)此工作簿時(shí)，定義的函數(shù)并不會(huì)自動(dòng)調(diào)出，需要在啟動(dòng)這個(gè)工作簿文件時(shí)顯示此菜單，在代碼窗口選擇【對(duì)象】為“WorkBook”，【方法】為“Open”。

其中代碼如下：

Private Sub Workbook_Open()

Call kl

表示在啟動(dòng)工作簿時(shí)，自動(dòng)調(diào)動(dòng)上述設(shè)置的KL（）函數(shù)

2.商品銷(xiāo)售統(tǒng)計(jì)分析系統(tǒng)窗體界面

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

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

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

⒊建立各工作表的內(nèi)容

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

（1）編輯“全年”工作表的內(nèi)容

復(fù)制“產(chǎn)品代號(hào)”、“產(chǎn)品名稱(chēng)”、“訂貨單位”、“業(yè)務(wù)員”、“單價(jià)”各字段內(nèi)容至“全年”工作表中。

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

（2）編輯“篩選”工作表的內(nèi)容

將“全年”工作表的數(shù)據(jù)復(fù)制到“篩選”工作表中。EXCEL包含兩種篩選功能，分別是自動(dòng)篩選和高級(jí)篩選。進(jìn)入自動(dòng)篩選的方法是：?jiǎn)螕簟緮?shù)據(jù)】菜單→選擇【篩選】→【自動(dòng)篩選】在各字段右下方自動(dòng)產(chǎn)生一個(gè)下拉按鈕，打開(kāi)各按鈕可根據(jù)需要進(jìn)行選擇查詢；如果要進(jìn)行較復(fù)雜的查詢，可通過(guò)“高級(jí)篩選”功能完成。具體操作如下：（如查詢產(chǎn)品名稱(chēng)為“三一牌”而且銷(xiāo)售額大于“20000元”的商品信息）

在“篩選”工作表的任意位置輸入如圖1-2所示的內(nèi)容，單擊【數(shù)據(jù)】菜單→選擇【篩選】→【高級(jí)篩選】→在打開(kāi)的【高級(jí)篩選】對(duì)話框中作如圖1-3的設(shè)置。其中“條件區(qū)域”應(yīng)選擇I2:J3單元格區(qū)域。這樣產(chǎn)品名稱(chēng)為“三一牌”而且銷(xiāo)售額大于“20000元”即可篩選出來(lái)。如要查詢產(chǎn)品名稱(chēng)為“三一牌”或者銷(xiāo)售額大于“20000元”的商品信息，應(yīng)將“條件區(qū)域”中的“銷(xiāo)售額>20000”填在J4單元格中，“條件區(qū)域”即為I2:J4單元格區(qū)域。

（3）編輯“分類(lèi)匯總”工作表的內(nèi)容

將“全年”工作表的數(shù)據(jù)復(fù)制到“分類(lèi)匯總”工作表中，單擊【數(shù)據(jù)】菜單→選擇【分類(lèi)匯總】→在打開(kāi)的【分類(lèi)匯總】對(duì)話框中作如圖1-4的設(shè)置。其中：【分類(lèi)字段】可選擇“產(chǎn)品名稱(chēng)”、“訂貨單位”、“業(yè)務(wù)員”等；【匯總方式】可選擇“求和”、“求平均值” “最大值”、“最小值”、“計(jì)數(shù)”等。

（4）編輯“按業(yè)務(wù)人員統(tǒng)計(jì)”工作表的內(nèi)容

將“全年”工作表的數(shù)據(jù)復(fù)制到“按業(yè)務(wù)人員統(tǒng)計(jì)”工作表中，單擊【數(shù)據(jù)】菜單→選擇【數(shù)據(jù)透視表和數(shù)據(jù)透視圖】→在打開(kāi)的【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А繉?duì)話框中單擊【下一步】和【完成】按鈕→將“業(yè)務(wù)員”字段拖至行字段，將“產(chǎn)品名稱(chēng)”字段拖至列字段，將“銷(xiāo)售額”字段拖至數(shù)據(jù)項(xiàng)，形成結(jié)果如圖1-5所示。單擊業(yè)務(wù)員右側(cè)的下拉按鈕可按業(yè)務(wù)人員分別查詢其銷(xiāo)售額。

（5）圖表顯示

復(fù)制分類(lèi)匯總的數(shù)據(jù)至“圖表顯示”工作表中，單擊【圖表向?qū)А浚筛鶕?jù)需要選擇“柱形圖”或“折線圖”進(jìn)行繪制。

小結(jié)：

本文只是EXCEL在統(tǒng)計(jì)分析中的一個(gè)簡(jiǎn)單應(yīng)用，熟悉VBA程序的用戶可以開(kāi)發(fā)出功能更加強(qiáng)大的EXCEL應(yīng)用程序。

參考文獻(xiàn)：

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

注：本文中所涉及到的圖表、注解、公式等內(nèi)容請(qǐng)以PDF格式閱讀原文