鄧高燕，黃勇奇

(黃岡師范學(xué)院，湖北 黃岡 438000)

?

Excel在地理科學(xué)專業(yè)實(shí)驗(yàn)教學(xué)中的應(yīng)用

鄧高燕，黃勇奇

(黃岡師范學(xué)院，湖北 黃岡 438000)

Excel是一款簡單易學(xué)而又功能強(qiáng)大的統(tǒng)計(jì)分析軟件，在地理實(shí)驗(yàn)數(shù)據(jù)統(tǒng)計(jì)分析中能使得費(fèi)時(shí)費(fèi)力的數(shù)據(jù)處理過程變得簡捷快便，而且實(shí)驗(yàn)結(jié)果精度高。本文以氣象觀測數(shù)據(jù)為例，介紹Excel 2007在地理實(shí)驗(yàn)數(shù)據(jù)統(tǒng)計(jì)分析、制作圖表中的應(yīng)用，利用Excel進(jìn)行地理統(tǒng)計(jì)分析的實(shí)驗(yàn)教學(xué)，可以提高課堂教學(xué)效率和教學(xué)質(zhì)量。

Excel；滑動(dòng)平均；累積距平；Mann-Kendal；回歸分析

地理科學(xué)是一門研究地球表層自然要素與人文要素的空間分布特征、相互關(guān)系及發(fā)展變化規(guī)律的學(xué)科，具有鮮明的實(shí)踐性，因此實(shí)驗(yàn)教學(xué)是地理科學(xué)專業(yè)教學(xué)的重要組成部分。通過實(shí)驗(yàn)教學(xué)可以提高學(xué)生動(dòng)手能力、分析解決問題的能力以及實(shí)踐創(chuàng)新能力，較大提升教學(xué)水平。

隨著計(jì)算機(jī)和現(xiàn)代信息技術(shù)的出現(xiàn)，讓學(xué)生掌握利用其分析綜合復(fù)雜的地理現(xiàn)象和問題，成為地理科學(xué)專業(yè)實(shí)驗(yàn)教學(xué)值得探討的重要課題。在地理科學(xué)實(shí)驗(yàn)教學(xué)中，地理要素統(tǒng)計(jì)分析及其結(jié)果制成統(tǒng)計(jì)圖表是非常重要的內(nèi)容。如SPSS、S-PLUS、MiniTab和Matlab等專業(yè)統(tǒng)計(jì)分析軟件，雖功能強(qiáng)大，但其學(xué)習(xí)和掌握的難度也大，而且軟件價(jià)格昂貴，不易配置。Excel是微軟辦公軟件的重要組成部分之一，可以完成復(fù)雜的數(shù)據(jù)處理、分析和預(yù)測，并具有強(qiáng)大的制作圖表功能[1-2]。Excel軟件廣為人知、簡單易學(xué)，而且普及率非常高，這為地理科學(xué)實(shí)驗(yàn)教學(xué)的開展提供極大方便。所以用Excel進(jìn)行地理科學(xué)專業(yè)實(shí)驗(yàn)教學(xué)不失為一個(gè)好的選擇，可以實(shí)現(xiàn)提高教學(xué)效率和教學(xué)質(zhì)量的目標(biāo)。

1 氣候?qū)嶒?yàn)數(shù)據(jù)統(tǒng)計(jì)分析

目前氣候變化與人類的生存和發(fā)展息息相關(guān)，是人們密切關(guān)注的全球變化問題之一。人們需要了解氣候變化的時(shí)空規(guī)律、變化機(jī)制，并預(yù)測未來變化趨勢和對人類社會的影響。本文以氣象觀測數(shù)據(jù)為例，介紹Excel 2007在數(shù)據(jù)統(tǒng)計(jì)分析、制作圖表中的應(yīng)用。文中所用氣象觀測數(shù)據(jù)為黃岡市英山縣和麻城市氣象觀測站1957～2011年近60年的歷史氣象資料，其中包括年平均氣溫、年降雨量等氣象資料(從中國氣象科學(xué)數(shù)據(jù)共享服務(wù)網(wǎng)下載得)。

1.1 氣候變化趨勢分析

氣候時(shí)間序列由隨時(shí)間變化的一系列氣候數(shù)據(jù)構(gòu)成。氣候變化的趨勢分析采用傾向率來定量描述，傾向率用一元線性回歸方程求得[3]。用xi表示樣本量為n的某一氣候要素變量，用ti表示所對應(yīng)的時(shí)刻，建立xi與ti之間的一元線性回歸：

(1)

式中a為回歸常數(shù)，b為回歸系數(shù)，即變化趨勢。當(dāng)b為正(負(fù))時(shí)，表示氣候要素值在所統(tǒng)計(jì)的時(shí)間內(nèi)有線性增加(減少)的趨勢。a和b可以用最小二乘法進(jìn)行估計(jì)：

(2)

ti與xi之間的相關(guān)系數(shù)r表示它們之間的線性相關(guān)的密切程度，要判斷變化趨勢是否顯著可對r進(jìn)行檢驗(yàn)。其計(jì)算公式為：

(3)

在Excel軟件中實(shí)現(xiàn)步驟如下：(1)選擇年份數(shù)據(jù)和對應(yīng)的氣象要素觀測數(shù)據(jù)，選擇菜單插入->折線圖或插入->直方圖；(2)在生成的圖中選擇氣象要素?cái)?shù)據(jù)點(diǎn)右擊鼠標(biāo)，在彈出菜單中選擇“添加趨勢線”，彈出“設(shè)置趨勢線格式”對話框，在對話框中勾選“顯示公式”和“顯示R平方值”，點(diǎn)擊“關(guān)閉”按鈕；(3)在“布局”菜單下分別設(shè)置圖表標(biāo)題、圖例、坐標(biāo)軸標(biāo)題等，即可完成氣候變化趨勢分析和制成一幅精美的氣候變化趨勢分析圖(如圖1所示)。 從圖1中可以看出，英山縣的年平均氣溫總體上是隨時(shí)間升高的，年降雨量總體上是隨時(shí)間降低的，但趨勢都不顯著。

圖1 英山縣年平均氣溫與年降雨量變化趨勢圖

滑動(dòng)平均相當(dāng)于低通濾波器，保留低頻部分，去掉高頻部分。用確定時(shí)間序列的平滑值來顯示變化趨勢。對樣本量為n的序列x，其滑動(dòng)平均序列表示為：

(4)

k為滑動(dòng)長度，一般取奇數(shù)，以使平均值可以加到時(shí)間序列中項(xiàng)的時(shí)間坐標(biāo)上。經(jīng)過滑動(dòng)平均后，序列中短于滑動(dòng)長度的周期大大削弱，顯示出變化趨勢。分析時(shí)主要從滑動(dòng)平均序列曲線圖來診斷其變化趨勢。

圖2 英山縣年平均氣溫移動(dòng)平均曲線

圖3 英山縣年降雨量移動(dòng)平均曲線

在Excel軟件中實(shí)現(xiàn)步驟如下：(1)點(diǎn)擊Excel左上角圖標(biāo)，在彈出菜單中點(diǎn)擊“Word選項(xiàng)”按鈕，在彈出的“Excel選項(xiàng)”對話框中選擇“加載項(xiàng)”，加載“分析工具庫”；(2)單擊“數(shù)據(jù)”選項(xiàng)卡上“分析”組中的“數(shù)據(jù)分析”。彈出“數(shù)據(jù)分析”對話框，選擇“移動(dòng)平均”，點(diǎn)擊“確定”按鈕；(3)在“移動(dòng)平均”對話框中，選擇數(shù)據(jù)“輸入?yún)^(qū)域”和“輸出區(qū)域”，間隔填寫“5”(表示取5年平均值)，勾選“圖表輸出”和“標(biāo)準(zhǔn)誤差”，點(diǎn)擊“確定”按鈕；(4)在“布局”菜單下分別設(shè)置圖表標(biāo)題、圖例、坐標(biāo)軸標(biāo)題等，即可完成(如圖2和圖3所示)。從圖2和圖3中可以看出英山縣的年平均氣溫與年降雨量總體變化情況。

在氣象氣候?qū)W上，距平值是用來確定某個(gè)時(shí)段或時(shí)次的數(shù)據(jù)，相對于該數(shù)據(jù)的某個(gè)長期平均值的高低。高于平均值為正距平 ，反之為負(fù)距平[4-5]。累計(jì)距平是系列距平值的累加。對氣候觀測數(shù)據(jù)序列x，其某一時(shí)刻t的累積距平表示為：

(5)

將n個(gè)時(shí)刻的累積距平值全部算出，就可繪出累積距平曲線進(jìn)行趨勢分析。累積距平曲線呈上升趨勢，表示有正距平值，呈下降趨勢則表示有負(fù)距平值。從曲線明顯的上下起伏，可以判斷其長期顯著的演變趨勢及持續(xù)性變化，甚至還可診斷出發(fā)生突變的大致時(shí)間。

在Excel軟件中實(shí)現(xiàn)步驟如下：(1)用每個(gè)時(shí)刻氣候觀測數(shù)據(jù)減去所有數(shù)據(jù)的平均值，得出對應(yīng)時(shí)刻的距平值，然后依次累加對應(yīng)時(shí)刻的距平值，得出累積距平值；(2)選擇距平值和累積兩列數(shù)據(jù)，選擇菜單插入->折線圖或插入->直方圖；(3)在“布局”菜單下分別設(shè)置圖表標(biāo)題、圖例、坐標(biāo)軸標(biāo)題等，即可完成(如圖4和圖5所示)。從圖4和圖5中可以看出英山縣的年平均氣溫與年降雨量總體變化情況，并在1996年均開始發(fā)生突變。

圖4 英山縣年平均氣溫累積距平曲線圖

圖5 英山縣年降雨量累積距平曲線圖

1.2 氣候變化突變點(diǎn)分析

氣候突變是指在較短時(shí)期內(nèi)由一種相對穩(wěn)定的氣候狀態(tài)，過渡到另一種氣候狀態(tài)的變化，它是氣候系統(tǒng)非線性性質(zhì)的一種表現(xiàn)。目前檢測突變的方法有多種，這里采用多數(shù)人認(rèn)為理論基礎(chǔ)和應(yīng)用效果好的Mann-kendall法，對英山縣的氣候變化進(jìn)行突變檢驗(yàn)[6]。

設(shè)氣候序列為x1，x2，…，xN，mi表示第i個(gè)樣本xi>xj(1≤j≤i)的累計(jì)數(shù)，定義統(tǒng)計(jì)量：

(6)

在時(shí)間序列隨機(jī)獨(dú)立的假定下，Sk的均值和方差分別為：

將Sk標(biāo)準(zhǔn)化：

(7)

(8)

其中UB1=0。分析繪出的 UFk和 UBk曲線圖，若 UFk或 UBk的值大于 0，則表明序列呈上升趨勢，小于 0 則表明呈下降趨勢。當(dāng)它們超過臨界直線時(shí)，表明上升或下降趨勢顯著。超過臨界線的范圍確定為出現(xiàn)突變的時(shí)間區(qū)域。如果UFk和 UBk兩條曲線出現(xiàn)交點(diǎn)，且交點(diǎn)在臨界線之間，那么交點(diǎn)對應(yīng)的時(shí)刻便是突變開始的時(shí)間。

圖6 英山縣年平均氣溫M-K統(tǒng)計(jì)量曲線圖

圖7 英山縣年降雨量M-K統(tǒng)計(jì)量曲線圖

1.3 氣候數(shù)據(jù)回歸分析

回歸分析是統(tǒng)計(jì)學(xué)上分析數(shù)據(jù)的方法，基于觀測數(shù)據(jù)建立變量間適當(dāng)?shù)囊蕾囮P(guān)系，以分析數(shù)據(jù)內(nèi)在規(guī)律。回歸分析是目前氣象氣候統(tǒng)計(jì)分析中最常用的一種方法。在Excel中進(jìn)行回歸分析不需要編程，把復(fù)雜的計(jì)算過程交給計(jì)算機(jī)處理，使用起來非常簡單。

眾所周知，氣溫與海拔、經(jīng)度和緯度相關(guān)。但如何相關(guān)，相關(guān)方向與相關(guān)程度如何，這些問題只有通過回歸分析之后才能得出結(jié)論。筆者先分別將氣溫與海拔、緯度、經(jīng)度作一元回歸分析，然后將氣溫與海拔、緯度、經(jīng)度三個(gè)自變量作多元回歸分析。

在Excel軟件中實(shí)現(xiàn)一元回歸分析的步驟如下：(1)分別選擇氣溫與海拔、氣溫與緯度、氣溫與經(jīng)度兩列數(shù)據(jù)，選擇菜單插入->散點(diǎn)圖；(2)在生成的圖中選擇數(shù)據(jù)點(diǎn)右擊鼠標(biāo)，在彈出菜單中選擇“添加趨勢線”，彈出“設(shè)置趨勢線格式”對話框，在對話框中勾選“顯示公式”和“顯示R平方值”，點(diǎn)擊“關(guān)閉”按鈕；(3)在“布局”菜單下分別設(shè)置圖表標(biāo)題、圖例、坐標(biāo)軸標(biāo)題等，即可完成回歸分析(如圖8、圖9、圖10所示)。從這三幅圖中可以看出，氣溫隨海拔、緯度增加而降低，并且隨緯度增加的幅度均大于海拔；氣溫隨經(jīng)度而增加，但增加程度均小于海拔和緯度。從相關(guān)顯著性看，只有氣溫與緯度具有顯著性，與海拔、經(jīng)度均無明顯相關(guān)。

圖8 全國2011年平均氣溫和海拔回歸分析

圖9 全國2011年平均氣溫和緯度回歸分析

圖10 全國2011年平均氣溫和經(jīng)度回歸分析

在Excel軟件中實(shí)現(xiàn)多元回歸分析的步驟如下：(1)單擊“數(shù)據(jù)”選項(xiàng)卡上“分析”組中的“數(shù)據(jù)分析”；(2)彈出“數(shù)據(jù)分析”對話框，選擇“回歸”，點(diǎn)擊“確定”按鈕；(3)在“回歸”對話框中，“Y值輸入?yún)^(qū)域”選擇Excel中“年平均氣溫”列，“X值輸入?yún)^(qū)域”選擇Excel中“緯度”、“經(jīng)度”、“海拔”三列，“輸出區(qū)域”選擇新工作表中的A1單元格，勾選“標(biāo)志”、“殘差”、“置信度”，點(diǎn)擊“確定”即可完成多元回歸分析(如表1、表2所示)。

表1 回歸統(tǒng)計(jì)表

表2 方差分析表

在表1中，Multiple R即復(fù)相關(guān)系數(shù)R，用來衡量自變量x與y之間的相關(guān)程度的大小。本例R=0.98表明年平均氣溫與緯度、經(jīng)度和海拔之間的關(guān)系為高度正相關(guān)，相關(guān)程度遠(yuǎn)大于其一元回歸分析。R Square為復(fù)測定系數(shù)，復(fù)相關(guān)系數(shù)R的平方。用來說明自變量解釋因變量y變差的程度，以測定因變量y的擬合效果。此例中的復(fù)測定系數(shù)為0.96，表明用自變量可解釋因變量變差的96%。Adjusted R Square為調(diào)整后的復(fù)測定系數(shù)R2，該值為0.96，說明自變量能說明因變量y的96%，因變量y的4%要由其他因素來解釋。在表2中，Significance F對應(yīng)的是在顯著性水平下的Fα臨界值，其實(shí)等于P值，P值越小越好。本例中P<0.000 1，故置信度達(dá)到99.99%以上。從表3可以得出氣溫與海拔、緯度、經(jīng)度的回歸分析方程：

Temp=-6.61*Lat-1.4*Lon-0.04*Elev+532.45

(9)

表3 回歸參數(shù)表

從回歸方程來看，氣溫與海拔、緯度、經(jīng)度的回歸系數(shù)均為負(fù)值，即為弱遞減趨勢，這與氣溫與經(jīng)度呈弱遞增趨勢的結(jié)論相左。所以，僅僅進(jìn)行簡單一元回歸分析就草率得出結(jié)論是不科學(xué)的，還要進(jìn)行復(fù)相關(guān)分析才能得出合理結(jié)論。

地理科學(xué)專業(yè)是實(shí)驗(yàn)實(shí)踐性很強(qiáng)的專業(yè)，需要對地理事物和現(xiàn)象進(jìn)行大量和深刻的統(tǒng)計(jì)分析，才能得出其分布規(guī)律、相關(guān)聯(lián)系和發(fā)展趨勢。Excel是學(xué)生比較熟悉而且功能強(qiáng)大的統(tǒng)計(jì)分析軟件，在地理實(shí)驗(yàn)數(shù)據(jù)統(tǒng)計(jì)分析中能夠快速得出分析結(jié)果和提高實(shí)驗(yàn)結(jié)果的精確度，使龐大而復(fù)雜的統(tǒng)計(jì)分析工作變得輕而易舉，從而提升實(shí)驗(yàn)教學(xué)效率和教學(xué)質(zhì)量。而且也可以激發(fā)學(xué)生課后動(dòng)手和學(xué)習(xí)的興趣，對提高學(xué)生使用計(jì)算機(jī)進(jìn)行計(jì)算、統(tǒng)計(jì)分析問題的能力大有裨益。

[1] 左利芳,仇財(cái)興.Excel中的常用分布函數(shù)及其在氣候統(tǒng)計(jì)中的應(yīng)用[J].廣西氣象,2002,23(2):26-28.

[2] 肖玉玲,馬中元,桂保玉等. 利用Excel軟件繪制氣象要素圖表[J]. 科技廣場,2010(03):152-154.

[3] 左利芳,仇財(cái)興. Excel中的回歸分析在氣候統(tǒng)計(jì)中的應(yīng)用[J]. 貴州氣象,2005,29(1):34-37.

[4] 殷世平.淺談EXCEL中宏在處理常規(guī)氣象資料中的應(yīng)用[J]. 黑龍江氣象, 2005(1):42-46.

[5] 吳有恒,孫永順,吳世美. 利用VBA自動(dòng)計(jì)算歷年日平均氣溫月標(biāo)準(zhǔn)差[J]. 貴州氣象,2013,37(1):42-44.

[6] 李晗. EXCEL在分布擬合中的應(yīng)用[J].佳木斯大學(xué)學(xué)報(bào)(自然科學(xué)版),2012,30(4):622-624.

(王菊平)

P46

A

1003-8078(2014)03-0087-06

2014-05-22 doi 10.3969/j.issn.1003-8078.2014.03.22

鄧高燕，女，江西高安人，助理實(shí)驗(yàn)師，研究方向?yàn)橛?jì)算機(jī)應(yīng)用。

黃勇奇，男，江西高安人，博士，副教授，研究方向?yàn)?S技術(shù)理論與應(yīng)用。

湖北省教育廳2010年度人文社科項(xiàng)目(2010y099)；湖北省教育科學(xué)“十一五”規(guī)劃2009年度課題(2009B185)；2009年度黃岡師范學(xué)院教學(xué)研究重點(diǎn)項(xiàng)目(2009ce03).