Excel在數(shù)據(jù)管理與分析中的高級(jí)應(yīng)用初探

【摘 要】Excel是日常生活中使用頻率最高的一款數(shù)據(jù)處理軟件，如果能夠根據(jù)不同需求，深入挖掘其數(shù)據(jù)管理與分析的功能，就可以輕而易舉地解決現(xiàn)實(shí)生活中的許多復(fù)雜問(wèn)題。本文介紹了Excel在“排序”、“高級(jí)篩選”、“VBA存儲(chǔ)”和“蒙特卡洛”四項(xiàng)功能上的高級(jí)應(yīng)用，對(duì)現(xiàn)實(shí)問(wèn)題的處理具有一定的啟發(fā)作用。

【關(guān)鍵詞】Excel；高級(jí)應(yīng)用；數(shù)據(jù)管理與分析

1.前言

Excel作為微軟公司出品的Office系列辦公軟件中的組件之一，近年來(lái)成為日常生活中使用頻率最高的數(shù)據(jù)處理軟件，它的魅力在于不僅可以制作外觀簡(jiǎn)潔漂亮的表格和圖片，還可以在數(shù)據(jù)的管理和分析等方面具備強(qiáng)大的功能。與像Access這樣專業(yè)的數(shù)據(jù)庫(kù)軟件相比，盡管Excel看起來(lái)簡(jiǎn)單，但是如果能夠根據(jù)用戶的不同需求，深入挖掘它的數(shù)據(jù)管理與分析功能，充分發(fā)揮其數(shù)據(jù)處理的優(yōu)勢(shì)，就可以體驗(yàn)到Excel在實(shí)際應(yīng)用中的巨大魅力。

2.幾種常用功能的高級(jí)應(yīng)用

2.1 “排序”功能的高級(jí)應(yīng)用

在Excel實(shí)際操作中，“排序”是常用的一項(xiàng)功能，最常用的排序就是使用工具欄中的“排序”選項(xiàng)進(jìn)行排序。一般地，Excel會(huì)按照當(dāng)前字段內(nèi)容進(jìn)行升序或者降序排練，但是我們通常會(huì)忽略到如果當(dāng)前字段內(nèi)容是中文漢字時(shí)，排序默認(rèn)為以字母為基準(zhǔn)，也就是按照當(dāng)前內(nèi)容拼音的首字母在英文字母中的出現(xiàn)順序進(jìn)行排列。如果我們想以筆畫(huà)作為排列基準(zhǔn)時(shí)，可以按照“數(shù)據(jù)”→“排序”操作命令打開(kāi)“排序”對(duì)話框，然后單擊對(duì)話框左下角“選項(xiàng)”按鈕，得到排序選項(xiàng)對(duì)話框如圖1所示，在該對(duì)話框中選取“方式”選項(xiàng)下的“筆畫(huà)排序”。

圖1 排序選項(xiàng)對(duì)話框

若遇到筆畫(huà)相同的情況，Excel會(huì)根據(jù)內(nèi)碼順序進(jìn)行排序操作。在此需要強(qiáng)調(diào)一點(diǎn)，在“排序選項(xiàng)”對(duì)話框中選擇了排序方式后，工具欄中的順序按鈕將會(huì)一直按照此方式進(jìn)行排序操作，若需要回到默認(rèn)時(shí)，還需要在圖1所示對(duì)話框中進(jìn)行設(shè)置選項(xiàng)。

2.2 “高級(jí)篩選”功能的高級(jí)應(yīng)用

在Ecxel實(shí)際操作中，“篩選”功能是在處理較復(fù)雜數(shù)據(jù)的情況下常用的功能。盡管普通的“自動(dòng)篩選”操作簡(jiǎn)單，但是當(dāng)面臨更具體詳細(xì)的條件來(lái)篩選數(shù)據(jù)時(shí)，就需要啟用“高級(jí)篩選”功能?！白詣?dòng)篩選”受到對(duì)一個(gè)字段最多可以設(shè)定兩個(gè)條件進(jìn)行設(shè)置等限制，但這些都可以在“高級(jí)篩選”功能中得到解決?！案呒?jí)篩選”功能是在某一個(gè)單元格內(nèi)記錄篩選條件，然后根據(jù)這些條件篩選出符合條件的記錄。如果初次使用“高級(jí)篩選”功能可能會(huì)感覺(jué)到表達(dá)式的書(shū)寫(xiě)比較麻煩，但掌握習(xí)慣之后就可以很快速地在復(fù)雜數(shù)據(jù)中進(jìn)行篩選操作。

篩選條件的編寫(xiě)需要遵循最基本的規(guī)則：編寫(xiě)作為字段的對(duì)象名稱，并且需要將篩選條件編寫(xiě)在其下方的單元格中。篩選條件的表達(dá)式可以使用運(yùn)算符與字符串、萬(wàn)用字符等的組合。如下面1所示篩選條件的示例。

2.3 “VBA存儲(chǔ)”功能的高級(jí)應(yīng)用

在利用Excel進(jìn)行數(shù)據(jù)管理與分析的應(yīng)用中常常要用VBA存儲(chǔ)功能來(lái)轉(zhuǎn)存篩選出的數(shù)據(jù)。通常我們需要將篩選出來(lái)的數(shù)據(jù)復(fù)制到其他的位置，而未被篩選出來(lái)的數(shù)據(jù)將會(huì)自動(dòng)排除在被復(fù)制的范圍之外。一般地我們會(huì)將篩選出的范圍認(rèn)定為“可見(jiàn)數(shù)據(jù)庫(kù)”來(lái)進(jìn)行復(fù)制，例如：

Set myNeed=Range（“B1”）.CurrentRegion

myNeed.AutoFilter Field：=7，Criterial：=”一班”

以第7個(gè)字段為基礎(chǔ)進(jìn)行篩選操作

myNeed.SpecialCells（xlCellType Visible）.Copy

僅僅復(fù)制可見(jiàn)單元格

按照剛剛提到的特性，也可以將代碼改為下列形式，所得結(jié)果都是相同的：

Set myRng=Range（“B1”）.CurrentRegion

myNeed.AutoFilter Field：=7，Criterial：=”一班”

myNeed.Copy

這樣我們就可以只復(fù)制篩選出來(lái)的顯示范圍

在此需要強(qiáng)調(diào)，在Excel中使用“隱藏”選項(xiàng)而隱藏的列和因?yàn)楹Y選而成為隱藏的列其操作方式是不相同的。如果使用了“隱藏”選項(xiàng)的單元格范圍，復(fù)制操作就會(huì)連同處于隱藏狀態(tài)的單元格一起復(fù)制。但是如果篩選過(guò)程中出現(xiàn)了有隱藏的行列，并且在其中輸入了函數(shù)的情況，在復(fù)制操作時(shí)就必須要選擇“看得見(jiàn)的單元格”來(lái)進(jìn)行處理。若在實(shí)際操作中沒(méi)有辦法根據(jù)需要處理數(shù)據(jù)時(shí)，就要留意是否由兩種不同隱藏狀態(tài)造成的。

2.4 “蒙特卡洛”功能的高級(jí)應(yīng)用

蒙特卡洛作為模擬隨機(jī)現(xiàn)象的一種數(shù)學(xué)方法，它的基本思路是用一個(gè)隨機(jī)過(guò)程去模擬另外一個(gè)相同概率的隨機(jī)過(guò)程，也就是用這一事件是否發(fā)生來(lái)判斷另一事件是否發(fā)生的過(guò)程?，F(xiàn)實(shí)生活中的隨機(jī)過(guò)程，很多問(wèn)題采用解析法進(jìn)行求解非常困難，但采用蒙特卡洛方法進(jìn)行模擬則較為簡(jiǎn)單，首先建立隨機(jī)過(guò)程概率模型，該模型的參數(shù)等于問(wèn)題的解，然后對(duì)該隨機(jī)過(guò)程概率模型進(jìn)行觀察抽樣試驗(yàn)，計(jì)算這些參數(shù)的某一統(tǒng)計(jì)特性，最后根據(jù)這些統(tǒng)計(jì)特征求出參數(shù)的近似解。

基本操作主要有以下5步：

1）依據(jù)實(shí)際問(wèn)題，建立相應(yīng)的隨機(jī)過(guò)程概率模型，要求所求參數(shù)的解應(yīng)該是所建立模型的數(shù)學(xué)期望；

2）依據(jù)問(wèn)題特點(diǎn)和計(jì)算的需要，采取直接模擬的方法或者降低方差的方法，來(lái)提高計(jì)算效率，降低運(yùn)算時(shí)間；

3）確定偽隨機(jī)數(shù)的產(chǎn)生方法，調(diào)用隨機(jī)數(shù)發(fā)生器產(chǎn)生滿足要求的隨機(jī)數(shù)；

4）使用Excel軟件中的數(shù)據(jù)分析功能，根據(jù)所求參數(shù)的統(tǒng)計(jì)特征，計(jì)算其統(tǒng)計(jì)近似值及方差；

5）將數(shù)據(jù)輸入到Excel軟件中，采用數(shù)據(jù)分析模塊來(lái)模擬計(jì)算。

比如，設(shè)某個(gè)航空導(dǎo)彈射擊飛機(jī)的命中概率為0.78，現(xiàn)在對(duì)來(lái)襲飛機(jī)發(fā)射3顆導(dǎo)彈，請(qǐng)判斷射擊效果。針對(duì)這個(gè)問(wèn)題，我們都知道每次發(fā)射一顆導(dǎo)彈能否命中來(lái)襲飛機(jī)是一個(gè)隨機(jī)事件，并且已知命中概率為0.78，那么我們就可以模擬這個(gè)作戰(zhàn)過(guò)程，可以通過(guò)調(diào)用隨機(jī)數(shù)來(lái)確定作戰(zhàn)的結(jié)果。在Excel實(shí)際操作中，可以直接調(diào)用“RAND”函數(shù)來(lái)模擬產(chǎn)生0到1之間均勻分布的隨機(jī)數(shù)，若產(chǎn)生的隨機(jī)生小于或者等于0.78，我們就認(rèn)為導(dǎo)彈命中來(lái)襲飛機(jī)，若隨機(jī)數(shù)大于0.78則認(rèn)為沒(méi)有命中來(lái)襲飛機(jī)。如果我們?cè)贓xcel軟件中進(jìn)行多次重復(fù)模擬，統(tǒng)計(jì)意義上的平均結(jié)果肯定接近于0.78。

發(fā)射三顆導(dǎo)彈，可以產(chǎn)生3個(gè)隨機(jī)數(shù)，調(diào)用“RAND”函數(shù)得出的模擬結(jié)果如表2所示。

根據(jù)模擬結(jié)果可知，第2、3發(fā)命中來(lái)襲飛機(jī)，第1發(fā)沒(méi)有命中，因此這一組3發(fā)的命中概率約為0.667。在此需要說(shuō)明每一次試驗(yàn)都是獨(dú)立的和隨機(jī)的，并且還要避免重復(fù)。選擇模擬次數(shù)N也是一個(gè)非常重要的問(wèn)題，一般情況認(rèn)為模擬次數(shù)N應(yīng)該足夠大，只有這樣才可以得出一條更加光滑的概率分布圖。

3.小結(jié)

只有不斷深入挖掘Excel軟件的功能，探索其在數(shù)據(jù)管理和分析方面的高級(jí)應(yīng)用，其實(shí)在像Access這樣專業(yè)數(shù)據(jù)庫(kù)軟件中的常用功能都可以由Excel來(lái)實(shí)現(xiàn)，尤其是如果能夠繼續(xù)深入探究采用VBA和Excel結(jié)合起來(lái)模仿數(shù)據(jù)庫(kù)應(yīng)用程序。在運(yùn)用Excel軟件進(jìn)行蒙特卡洛模擬時(shí)既可以免去繁復(fù)的公式推導(dǎo)過(guò)程，又可以避免復(fù)雜程序的編寫(xiě)，同時(shí)在產(chǎn)生偽隨機(jī)數(shù)和計(jì)算結(jié)果分析方面都非常方面。

作為一款卓越的數(shù)據(jù)處理軟件，Excel軟件擁有強(qiáng)大的數(shù)據(jù)管理和分析工具，不僅僅表現(xiàn)在“排序”、“高級(jí)篩選”、“VBA存儲(chǔ)”和“蒙特卡洛”等功能上，還有如單變量求解工具、模擬運(yùn)算表和規(guī)劃求解工具等。但是，大部分用戶對(duì)于這些管理和分析工具了解的太少，不能熟練運(yùn)用一些具體操作。如果掌握了這些方法和技巧，并在實(shí)際情況下靈活運(yùn)用這些數(shù)據(jù)管理和分析工具就能夠輕松解決很多復(fù)雜的問(wèn)題。

參考文獻(xiàn)：

[1]陳鄧安，叢中旖，王子明.在效能分析中應(yīng)用Excel數(shù)據(jù)分析模塊進(jìn)行蒙特卡羅模擬[J].科學(xué)技術(shù)與工程，2012（2）.

[2]劉平.Excel在辦公中的高級(jí)應(yīng)用初探[J].電腦知識(shí)與技術(shù)，2010（27）.

[3]周紅曉.Excel數(shù)據(jù)有效性高級(jí)應(yīng)用探討[J].福建電腦，2009（10）.

[4]周洪林.Excel函數(shù)高級(jí)應(yīng)用[J].福建電腦，2009（2）.

作者簡(jiǎn)介：唐學(xué)軍（1969—），女，新疆工程學(xué)院實(shí)驗(yàn)師，研究方向：計(jì)算機(jī)基礎(chǔ)、多媒體制作。