許多人對EXCEL的數據計算功能不了解，僅把它當作製作表格和圖表的辦公軟件．用它不需編程就能夠實現其他軟件需要編程才能完成的複雜計算，能夠進行各種數據統計、運算、處理和繪製統計圖形，隻要善於開發，一定能夠在數學建模中發揮出更大的作用．
8.2.1用Excel做數據處理
EXCEL擅長數據統計，用它來處理數據能夠節省大量時間，提高效率．
EXCEL的數據處理功能主要有兩大塊：
計算功能它提供了300多個內部函數供用戶使用，還允許自定義函數．當大批數據都要用同一公式計算時，隻要用鼠標拖動而不需要編程．
數據分析功能EXCEL提供了“數據分析”工具包，內含方差分析、回歸分析、協方差和相關係數、博立葉分析、t檢驗等分析工具．
8.2.1.1Excel的函數
Excel提供了12類（有常用、財務、日期與時間、數學與三角函數、統計、查找與引用、數據庫、文本、邏輯、信息、工程、用戶定義）共300多個內部函數，其中用得比較多的是常用、統計和數學與三角函數類中的函數．
函數由函數名、參數組成．不同函數對其參數要求不同，若參數為數值，則可用單元格取代，有些函數的參數是多個數據，則可用區域取代，有些函數的參數是矩陣，則可用矩形區域取代．
1. 常用函數
當插入函數對話框的選擇類別中顯示“常用函數”時，共有十多個函數供選擇，它們的功能和參數如表83所示．
表83Excel常用函數
函數名功能參數
EXP計算ex任意實數
SUM求和數組，如A2：A10
LN求自然對數lnx正實數
COUNTIF統計滿足某種條件的數據個數數據區域和條件
AVERAGE求算術平均值數組
IF由條件決定返回值一個條件，兩個結果
COUNT統計個數數組
MAX求最大值數組
SIN正弦以弧度表示的角度
SUMIF滿足某種條件的所有數據的和數據區域和條件
HYPERLINK創建一個快捷方式或鏈接路徑和文件名、標識符
2. 數學與三角函數
這些是數值計算時常用到的函數．在插入函數對話框中選擇數學與三角函數，則顯示出58種函數供選擇，其中常用的函數見表84所示．
表84Excel數學與三角函數
函數名功能參數
三角函數SIN,COS,TAN求三角函數值以弧度表示的角度
反三角函數ASIN,ACOS,ATAN求反三角函數值定義域內的數
雙曲函數SINH,COSH,TANH求雙曲函數值實數
反雙曲函數ASINH,
ACOSH,ATANH求反雙曲函數值定義域內的實數
POWERx的y次方兩個數x和y
EXPex數x或單元格
SQRTx的平方根同上
LOG給定底的對數真數和底數
LOG1010為底的對數真數或單元格
LN自然對數真數或單元格
ABSx的絕對值數x或單元格
FACT計算階乘整數n
COMBIN組合數Crnn和r兩個整數
MDETERM求行列式的值n行n列數據
MINVERSE求矩陣的逆n行n列數據
MMULT兩個矩陣相乘兩個矩陣數據
SUMSQ計算平方和數組（向量）
MOD整除求餘數兩個整數
PRODUCT連乘積若幹個數
PI圓周率無
DEGREES弧度轉換成度弧度
RADIANS度轉換成弧度度
(續表)
函數名功能參數
LCM最小公倍數若幹個數
GCD最大公約數若幹個數
RAND0～1之間均勻分布隨機數無
RANDBETWEEN兩個數之間的隨機數兩個數
SUMXMY2兩個數組對應數值的平方和兩個數組
SERIESSUM求冪級數的和滿足要求的四個數
SIGN符號函數實數
還有一些舍入或取整函數沒有一一列出，如INT，功能是向下取整．
例1計算e－2．
例2計算2+ln3的值．
例3求矩陣A=1101
1222
2－221
3－153的逆矩陣．
【作法】插入→函數→數學與三角函數→MINVERSE→A1：D4→確定
然後再在插入函數的區域僅出現一個-4，若要顯示全部逆矩陣，則以插入函數的單元格（如上例的A7）為開始，選擇一個和原矩陣A大小一樣的區域（如A7：D10），再按F2，再同時輸入Shift+Ctrl+Enter，則在選定的區域出現逆陣的計算結果．
【注】MMULT函數的用法同上，顯示時也要選區域，再按F2，再同時輸入Shift+Ctrl+Enter
3. 統計函數
Excel2003有80種統計函數供選擇使用，其中常用的如表85所示．
表85Excel中常用的統計函數
函數名功能參數
AVERAGE求算術平均值n個數
VAR樣本方差(修正)(∑ni=1x2i－nx-2)\/(n－1)n個數
VARP總體方差(∑ni=1x2i－nx-2)\/nn個數
STDEVVAR的平方根n個數
STDEVPVARP的平方根n個數
DEVSQ∑ni=1(xi－x-)2=∑ni=1x2i－nx-2n個數
AVEDV∑x－x-\/nn個數
NORMSDIST標準正態分布的分布函數值數x
NORMDIST正態分布，1：返回分布函數，0：返回概率密度x,μ,σ,1或0
NORMINV正態分布概率為時的的值α,μ,σ,
NORMSINV標準正態分布由得α
CHIDISTχ2分布P(X>x)x，自由度n
CHINVχ2分布由α查xα，n
(續表)
函數名功能參數
CHITEST兩組數據同分布的概率兩組數據
POISSON泊鬆分布，0：返回對應k的概率；1：返回累積概率k,λ，1或0
BINOMDIST二項分布，0：返回的概率值Cknpkqn－k；1：返回累積概率k,n,p，1或0
EXPONDIST指數分布，1：返回分布函數值；0：返回概率密度x,λ，1或0
TDISTt分布，1：返回分布函數值；0：返回概率密度x,n，1或0
TINVt分布滿足P(T<α)的T值α，n
FDISTF分布滿足P(F<α)的F值x,n1,n2
FINV由α查F分布臨界值α,n1,n2
CONFIDENCE總體均值的置信區間（半長度）α,μ,n（數據個數）
COVAR協方差兩組數
CORREL相關係數兩組數
FTEST兩組數方差相等的概率兩組數據
CRITBINOM二項分布的臨界值（分位數）n,p,α
SLOPE線性回歸y=a+bx中的b兩組數
INTERCEPT線性回歸y=a+bx中的a兩組數
LINEST多元線性回歸y=a+∑bixi，c=0時強製a=0，c=1時返回附加回歸統計值數組y，多維數組x，
以及邏輯值c,s
LOGEST指數回歸y=b∏ki=1mxii中的b,mi同上
GEOMEAN幾何平均數n個數
HARMEAN調和平均數（倒數平均值的倒數）n個數
MINn個數中的最小值n個數
以上概率統計函數中，有些函數名有一定規律，凡是後四個字母為DIST的函數，功能是返回某種分布的分布函數值或概率密度值（根據參數邏輯值1或0決定，為1時返回分布函數值或累積概率，為0時返回概率密度或分布律的值）；如果函數名稱後幾個字母為INV，它們是對應DIST函數的反函數，功能是給定概率反查自變量的值．
4.自定義函數
實際計算時庫函數有時不能完全滿足用戶的需要，須自己定義函數，稱為自定義函數．任何一個計算軟件，如果不具備允許用戶按自己的意願定義函數的功能，則該計算軟件的使用範圍很有限．
Excel允許用戶自己定義任意帶參數的函數，方法是：把光標放在空白處（點擊空白格子）選輸入一個等號“=”，然後輸入自定義函數的表達式．表達式可由常數、變量、內部函數和運算符組成，其中運算符包括算術運算符（-，*，\/，^，%，＋，－）、比較運算符（=，，=，<>）和連接符&．
例4當x=3,2,1,0,－1,－2,－3時，計算分段函數y=xsinx,x>0
excosx,x≤0的值．
【步驟】（1） 選一個空白列（如A列），輸入自變量x的值，在第一行（A1）輸入3，點擊第二行（A2），輸入A1-1，再點擊其他單元格，則A2顯示計算結果2，再點擊A2單元格，則它的邊框出現加粗的黑色且右下角有一黑點．用鼠標拉著該黑點向下拖動一直到A7單元格（複製公式AX-1），放開鼠標，則A3至A7單元格內的計算結果依次顯示為1,0,－1,－2,－3．
【注】用上方法複製公式時，如果公式中的自變量是單元格的名稱（編號），則隨著拖動，公式的自變量作相應的變化，其變化規律是：縱向拖動公式時，行號變而列號不變；橫向拖動公式時，列號變而行號不變．
（2） 選另外一列，如B列，點擊第一行單元格B1，輸入=IF(A1>0,A1*SIN(A1),EXP(A1)*COS(A1))．鼠標點擊其他單元格，則B1單元格顯示自定義函數的計算結果，再點擊B1單元格，拉著它的邊框右下角的黑點，向下拖動到B7放開，則B1至B7單元格依次得到自定義函數在自變量分別為A1至A7時的值．
5. 利用自定義函數完成較複雜的計算
表達式（自定義函數）可以拖動，且函數的自變量能夠自動改變，我們利用該項功能就可完成大批量數據計算以及各種複雜的計算（用其他軟件通常需要編程才能進行的計算），舉例如下：
例5用迭代法能求非線性方程x－cosx=0的數值解，迭代公式是xk=cos(xk－1)，取x0=1，試用Excel計算，要求精度達到10－12．
解：在空白列（如A列）的第一位置處輸入初始值1，點擊該單元格同行的下一行（A2）單元格，輸入=COS（A1），得到計算結果0.540302306，然後向下連續拖動黑邊框右下角的黑點，產生的效果是按迭代公式Ak=COS(Ak－1)不斷進行迭代，放開鼠標就能看到計算結果，此時單元格內顯示的數字格式為小數點後9位，A55後數字不再變化，說明迭代55次之後計算結果的精度達到10－9．為了顯示小數點後麵更多位數，先選擇該列從A2開始的單元格，然後從主菜單選擇格式→單元格→彈出對話框單元格格式→點數字欄目，選數值→把小數位數欄目內的數字改為16→確定
【注】Excel的計算精度通常最多能有16位有效數字，繼續增加小數點後的位數將無效．
由本例可見，Excel用於較複雜計算有兩大優點：
（1） 不需要編寫程序，這對於不熟悉編程，但急需計算的人員比較實用；
（2） 顯示結果比較直觀，能看見中間結果，便於數據分析．
例6利用公式π2=1+13+1325+132537+13253749+…計算π的近似值，使誤差小於10－14．
【作法】設變量的初始值為n=1;m=3;t=1;p=1，然後在循環中運算：n=n+1；m=m+2；t=t×nm；p=p+t；pi=p×2．
【步驟】第一行的前四列依次輸入：n、m、1、1，在第二行的前5列依次輸入1、3、=A2\/B2*C1、C2+D1、D2*2，在第三行的前2列依次輸入A2+1、B2+2，然後從第一列開始把每一列的公式依次向下拖動，第5列的計算結果就是π的值，設置第5列的數值顯示格式為小數點後15位．
可以看到從第46行開始，計算結果穩定在3.14159265358979，此結果精度已經達到10－14，亦是本例能達到的最高精度.
【注】如果複製公式時，不希望參數改變，即某個參數是固定在某個單元格中的數，為此在公式中代表單元格數值的列標前加$，則不管公式被複製到什麼位置上，列標固定不變,如果行號前加$，則公式被複製時，等號固定不變．
例7某公司給員工發獎金，獎金一方麵與銷售額掛鉤（按銷售額的一定比例提成），另一方麵還與其他指標掛鉤（提成比例分為三等：一等1．5%，二等1%，三等0．5%），計算銷售額為2000，3000，……，6000時三種等級的應發獎金數．
【步驟】如圖，A3A7是銷售額，B2，C2，D2是3種等級的，B3-D7是計算出來的獎金數，其中B3-B7每個數字是A3-A7對應數字乘以C2的百分比，D3-D7每個數字是A3-A7對應數字乘以D2的百分比，在B3單元格內輸入自定義計算公式=$A3*B$2，公式中$A的作用是不管公式複製到何處，均以A列為基數，$2的作用是獎金等級始終以第二行的百分比計算．B3的結果計算出來之後，隻需把B3單元格右下角的黑點向下並且向右拖動到D7，則表內所有應發獎金數都能正確計算出來．
例8連續得利問題．
設銀行活期存款年利率為r，若某儲戶存10000元活期存款，那麼滿一年後，他可以得到利息10000r，本息合計10000（1+r）元，因為銀行允許活期存款隨便什麼時候支取，如果儲戶滿半年就結算一次，此時的本息合計為10000（1+r\/2）元，把本息取出來後立即把本息一起再存活期，半年後再次結算，則全年的本息合計為10000(1+r\/2）2元．因為(1+r\/2）2=1+r+r2\/4>1+r，發現每半年結算一次的獲利經一年結算一次多．試計算每季度、每月、每半個月、每天結算一次並立即把本息再存活期情況下全年的獲利．假如活期存款的利息可以按小時計，甚至是按分鍾來結算，那麼當儲戶連續不斷地取款再存款，他能靠這種方式來發大財嗎？
8.2.1.2Excel的數據分析功能
EXCEL提供了用作“數據分析”的統計分析包，內含方差分析、回歸分析、協方差和相關係數、傅立葉分析等分析工具，使用這些分析工具，可大大提高工作效率和質量．
在默認安裝時，EXCEL並不直接提供數據分析工具包，首次使用時需要進行安裝，方法如下：
（1） 點擊office按鈕→Excel選項→加載項→轉到；
（2） 在對話框中按照需要選擇分析工具庫、規劃求解等項目，點擊確定；
（3） 如果需要，需原OFFICE安裝光盤．
安裝完成後，工具菜單中多出了數據分析子菜單，點擊它，彈出對話框，顯示各類分析工具．該工具包含有19個工具（與版本有關，大致相同），可分為5類，如下表．
表86EXCEL的數據分析工具
基礎分析檢驗分析相關，回歸方差分析其他
描述統計z檢驗協方差單因素指數平滑
直方圖F檢驗相關係數雙因素傅立葉分析
排位t檢驗回歸分析無重複雙因素隨機數發生器
抽樣分析移動平均
1. 描述統計
主要統計數據的平均值、中位數、標準差、方差等統計量．
有關對話框的說明：
(1) 輸入區域
在此輸入待分析數據區域的單元格引用．該引用必須由兩個或兩個以上按列或行排列的相鄰數據區域組成．
(2) 分組方式
若要指示輸入區域中的數據是按行還是按列排列，請單擊“逐行”或“逐列”．
(3) 標誌位於第一行\/標誌位於第一列
如果輸入區域的第一行中包含標誌項，請選中“標誌位於第一行”複選框．如果輸入區域的第一列中包含標誌項，請選中“標誌位於第一列”複選框．如果輸入區域沒有標誌項，該複選框將被清除，MicrosoftExcel將在輸出表中生成適宜的數據標誌．
(4) 平均數置信度
如果需要在輸出表的某一行中包含平均值的置信度，請選中此複選框．在右側的框中，輸入所要使用的置信度．例如，數值95%可用來計算在顯著性水平為5%時的平均值置信度．
(5) 第K大值
如果需要在輸出表的某一行中包含每個數據區域中的第K個最大值，請選中此複選框．在右側的框中，輸入K的數字．如果輸入1，則該行將包含數據集中的最大值．
(6) 第K小值
如果需要在輸出表的某一行中包含每個數據區域的第K個最小值，請選中此複選框．在右側的框中，輸入K的數字．如果輸入1，則該行將包含數據集中的最小值．
(7) 輸出區域
在此輸入對輸出表左上角單元格的引用．此工具將為每個數據集產生兩列信息．左邊一列包含統計標誌，右邊一列包含統計值．根據所選擇的“分組方式”選項，Excel將為輸入區域中的每一行或每一列生成一個兩列的統計表．
新工作表組
單擊此選項可在當前工作簿中插入新工作表，並由新工作表的A1單元格開始粘貼計算結果．若要為新工作表命名，請在右側的框中鍵入名稱．
新工作簿
單擊此選項可創建一新工作簿，並在新工作簿的新工作表中粘貼計算結果．
(8) 彙總統計
如果需要MicrosoftExcel在輸出表中為下列每個統計結果生成一個字段，請選中此複選框．這些統計結果有：平均值、標準誤差（相對於平均值）、中值、眾數、標準偏差、方差、峰值、偏斜度、極差（全距）、最小值、最大值、總和、總個數、最大值(#)、最小值(#)和置信度．
2. 直方圖
直方圖是一大批數據的頻率分布圖，由它可以觀察和分析數據的概率分布．畫直方圖的步驟如下：
(1) 輸入原始數據，進行描述分析，確定數據的最小值和最大值，把數據所在區域分成若幹個小區間，確定分段點；例如在A列輸入原始數據，在B列輸入這些分段點數數據．
(2) 點數據→數據分析→直方圖，彈出直方圖對話框
其中“輸入區域”是指原始數據所在的區域，“接收區域”是指分段點所在列，如果空白不填，則EXCEL會自動在數據的最小值之間確定一組等間隔的分段點．若第一行是表頭，則在“標誌”上打“”，輸出選項中，可選輸出區域（指定位置），也可選新工作表組，在“圖表輸出”上打“”，累計百分率欄目可選也可不選，點“確定”．得到數據統計結果和直方圖．
8.2.2用Excel繪製圖表
圖表是一種直觀有效的常用工具，通過圖表，可以把大量的數據轉換成各種格式的直觀圖形，便於用戶快速地分析數據之間的對比、關聯、變化趨勢等相互關係．Excel提供強大的圖表繪製功能，可以非常簡便地建立各種統計圖表，如直方圖、柱形圖、散點圖、餅圖、條形圖、折線圖等．對話框以向導的方式引導用戶使用，既直觀又方便．即使初次使用，也能很快掌握．
8.2.2.1創建圖表的步驟
創建一個圖表通常要4個主要步驟：
1． 準備數據
數據是圖表的依據，要創建圖表必須先準備好數據．例如，2004年數學建模競賽A題（奧運會臨時超市網點設計）中的原始數據，經過統計得到如下圖所示統計表．
2． 打開“圖表向導”
從菜單選“插入”→“圖表”，或者工具欄中的按鈕，即可啟動“圖表向導”，向導中有“標準類型”和“自定義類型”兩種類型供選擇．
（1） 標準類型．有柱形圖、條形圖、折線圖、餅圖、XY散點圖、麵積圖、圓環圖、雷達圖、曲麵圖、氣泡圖、股價圖、圓柱圖、圓錐圖和棱錐圖共14類型，每種類型又包含若幹個子類，每個子類均用圖形表示，如下圖所示．你可以選擇合適的圖表類別及子類，然後點擊“下一步”．
（2） 自定義類型．在圖表向導中選擇自定義類型，出現“內部”和“自定義”兩種選擇，若選“內部”則出現內置的20種圖形類型供挑選：彩色堆積圖、彩色折線圖、帶深度的柱形圖、對數圖、分裂的餅圖、管狀圖、黑白餅圖、黑白麵積圖、黑白折線圖、黑白柱形圖、蠟筆圖、藍色餅圖、兩軸線一柱圖、兩軸折線圖、平滑直線圖、線柱圖、懸浮的條形圖、圓錐圖、柱狀一麵積圖、自然條形圖，如下圖所示，選擇合適的類型，點擊“下一步”，出現“圖表源數據”對話框．
3． 指定數據位置
在“數據區域”欄目內輸入數據所在位置，例如輸入Al:B7，該區域的第一行和第一列是表頭（文字說明），數據按列擺放，故對“係列產生在”欄目的兩個選項“行”和“列”作出選擇“列”，點擊“下一步”，出現“圖表選項”對話框．
4． 設定圖表選項
“圖表選項”對話框用來設定圖表的標題、坐標軸、網格線、圖例、數據標誌、數據表等項目，具體功能說明如下．
（1） 標題選項，設置圖表的標題、坐標軸的文字說明．
（2） 坐標軸選項．設置是否顯示坐標軸及其刻度．
（3） 網格線選項．設定是否顯示網格線．
（4） 圖例選項．設定是否顯示圖例及其位置．
（5） 數據標誌選項．設置是否顯示數據的名稱、數據值等標誌．
（6） 數據表選項．設置是否顯示數據列表．
以上選項的設定有直觀圖形顯示在對話框的右半部分，所見即所得，立異p能看見效果，用戶可根據需要和愛好決定如何設置．
全部選項設置好以後，點擊“下一步”，出現圖表位置對話框．
5． 設定圖表位置
選擇“作為新工作表插人”或者“作為其中的對象插入”均可，點擊“完成”．
8.2.2.2編輯和修改圖表
在使用圖表向導時，可以對圖表的標題、坐標軸、網格線、圖例、數據標誌、數據表等圖表組成部分（稱為圖表的元素或對象）進行設置，但這種設置是粗略的框架，它一般不涉及字體、字型、字號、前景色、背景色、坐標刻度、線條顏色等細節．圖表向導生成的圖形通常不夠美觀，一些細節往往不中意，需要進行編輯、修改、美化和完善．
1． 圖表的組成
圖表由各種元素（部件）組成，其組成部分有圖表區、繪圖區、標題、坐標軸（分類軸和數據軸）、背景牆、網格線、數據標誌和數據係列，當鼠標在圖上移動時，會彈出相應的元素名稱．
2． 圖表的編輯
主菜單的“圖表”項目下有“圖表類型”、“源數據”、“圖表選項”、“位置”、“添加數據”、“設置三維視圖”等二級菜單，如下圖所示．先選中（點擊）某個已經創建的圖表，點擊主菜單“圖表”，在二級菜單中選擇其中任一選項，都會彈出一個對話框．二級菜單各項目的主要功能說明如下：
（1） 圖表類型．功能與圖表向導中的“圖表類型”對話框類似，用來更改已創建的當前圖表的類型．
（2） 源數據．用於重新指定源數據的位置，功能及設置方法與圖表向導中的“圖表源數據”對話框類似．
（3） 圖表選項．對話框與圖表向導中的“圖表選項”對話框相似，用來更改圖表的標題、坐標軸、網格線、圖例、數據標誌、數據表等項目，具體設置方法與圖表向導相同．
（4） 圖表位置．用來更改圖表位置，有“作為新工作表插人”或者“作為其中的對象插人”兩種設置供選擇．以上四個二級菜單項目的設置內容與圖表向導是相似的，功能是在圖表生成之後用來重新設置（更改）原來的設置，使圖表更符合自己的意願．
（5） 設置三維視圖格式．彈出對話框，用來對三維視圖上下轉動和左右旋轉，左上方的兩個箭頭用來上下轉動，中間下部的向左、向右兩個彎箭頭用於左右轉動圖形．