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

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

1.前言

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

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

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

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

圖1 排序選項對話框

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

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

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

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

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

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

Set myNeed=Range（“B1”）.CurrentRegion

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

以第7個字段為基礎(chǔ)進行篩選操作

myNeed.SpecialCells（xlCellType Visible）.Copy

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

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

Set myRng=Range（“B1”）.CurrentRegion

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

myNeed.Copy

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

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

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

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

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

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

2）依據(jù)問題特點和計算的需要，采取直接模擬的方法或者降低方差的方法，來提高計算效率，降低運算時間；

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

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

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

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

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

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

3.小結(jié)

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

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

參考文獻：

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

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

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

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

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