運(yùn)用EXCEL圖表進(jìn)行海森格紙繪制

1、【關(guān)鍵詞】Excel 函數(shù) 工程水文 頻率曲線Microsoft Excel （簡稱Excel）是Microsoft Office中的一個(gè)重要組件，具有強(qiáng)大的計(jì)算功能，為工程水文及水利計(jì)算中的有關(guān)計(jì)算提供廣闊的平臺。利用Excel可以有效提高工程水文及水利計(jì)算的速度和精度，具有快速、簡便、靈活的特點(diǎn)。特別是使用Excel的“拖動充填”、“排序”、“插入函數(shù)x”、“圖表”等功能，充分體現(xiàn)其優(yōu)越性，可以充分解決工程水文及水力計(jì)算的有關(guān)問題。本文可以通過其內(nèi)置功能介紹如何比較輕松準(zhǔn)確的繪制水文頻率曲線。其具體繪制步驟如下：1.制作海森機(jī)率格紙主要運(yùn)用EXCEL電子表格中的NORMSINV函數(shù)進(jìn)行計(jì)算

2、。（詳見圖1）在表中B2單元格中輸入公式：=NORMSINV(A2%) （1.1）拖動單元格右下角的填充柄，將公式復(fù)制到本列其他單元格中。在表中C6單元格中輸入公式：=-$B$4-(-B6) （1.2） 拖動單元格右下角的填充柄，將公式復(fù)制到本列其他單元格中。在表中D2單元格中輸入最小值，如“0”；在D3單元格中輸入最大值，如“1600”；在D4單元格輸入“=D2”； 在D5單元格輸入“=D3”。 拖動單元格右下角的填充柄，將公式復(fù)制到本列其他單元格中。在表中E2單元格中輸入公式：=1/A2*100 （1.3）拖動單元格右下角的填充柄，將公式復(fù)制至本列E25單元格止。在表中E26單元格中輸入公

3、式：=1/(1-A26/100) （5）拖動單元格右下角的填充柄，將公式復(fù)制到本列其他單元格中。 圖1 海森機(jī)率格紙2.繪制海森機(jī)率格紙?jiān)趫D1表格中點(diǎn)擊“插入”菜單中的“圖表”命令，在“標(biāo)準(zhǔn)類型”的“圖表類型”表框中選擇“XY散點(diǎn)圖”，在“子圖表類型” 表框中選擇“散點(diǎn)圖”。單擊“下一步”按鈕。（詳見圖2） 圖2單擊“系列”標(biāo)簽，在“系列”欄下點(diǎn)擊“添加”按鈕，在“X值”文本框內(nèi)選取數(shù)據(jù)區(qū)域“海森機(jī)率格紙！$C$2:$C49”， 在“Y值”文本框內(nèi)選取數(shù)據(jù)區(qū)域“海森機(jī)率格紙！$D$2:$D49”。之后點(diǎn)擊“完成”按鈕。（詳見圖3）圖3在圖中將鼠標(biāo)置于X軸數(shù)字“4”上單擊右鍵，按“清除”按鈕，清

4、除水平軸顯示的“0 2 4 6 8”數(shù)字。將鼠標(biāo)置于X軸圓點(diǎn)上單擊右鍵，點(diǎn)擊“數(shù)據(jù)系列格式”，在出現(xiàn)的對話框內(nèi)點(diǎn)擊“數(shù)據(jù)標(biāo)志”選項(xiàng)卡，在“數(shù)據(jù)標(biāo)簽包括”欄內(nèi)選擇“X值”后，按下“確定”按鈕。（詳見圖4）圖4然后挨個(gè)點(diǎn)擊在圖內(nèi)X軸上下出現(xiàn)的數(shù)字，將X軸下側(cè)數(shù)字逐一修改為表內(nèi)“頻率”列相對應(yīng)的數(shù)字，將X軸上側(cè)數(shù)字逐一修改為表內(nèi)“重現(xiàn)期”列相對應(yīng)的數(shù)字。如將“0.62874179”改為“0.1”。 （詳見圖5）圖5全部修改完后，右鍵單擊圖表空白處，在“圖表選項(xiàng)”對話框點(diǎn)擊“標(biāo)題”選項(xiàng)卡，在“圖表標(biāo)題”文本框內(nèi)輸入圖表標(biāo)題。將鼠標(biāo)置于圖內(nèi)X軸圓點(diǎn)上單擊右鍵選擇“數(shù)據(jù)系列格式”，在出現(xiàn)的對話框內(nèi)點(diǎn)擊“誤

5、差線Y”，在“顯示方式”欄選擇“負(fù)偏差”，在“百分比”文本框輸入100%，然后按“確定”按鈕，圖表內(nèi)將顯示X軸刻度線。在圖中將鼠標(biāo)置于Y軸數(shù)字“1600”上單擊右鍵，選擇“坐標(biāo)軸格式”，在對話框內(nèi)選擇“刻度”選項(xiàng)卡，選擇“最大值”并在文本框內(nèi)輸入“1600”。然后按“確定”按鈕。將鼠標(biāo)置于X軸圓點(diǎn)上單擊右鍵，點(diǎn)擊“數(shù)據(jù)系列格式”，點(diǎn)擊“圖案”選項(xiàng)卡，在“數(shù)據(jù)標(biāo)記”欄選擇“無”，按“確定”按鈕。至此，一張完整的海森機(jī)率格紙圖繪制完畢。（詳見圖6） 圖63.系列經(jīng)驗(yàn)頻率的計(jì)算公式中華人民共和國行業(yè)標(biāo)準(zhǔn)水利水電工程設(shè)計(jì)洪水計(jì)算規(guī)范(SL44-93)中對頻率曲線的線型要求一般應(yīng)采用Pearson型，頻

6、率曲線的統(tǒng)計(jì)參數(shù)采用均值，變差系數(shù)Cv，偏態(tài)系數(shù)Cs，計(jì)算連續(xù)和不連續(xù)洪水系列，Cv，Cs值。3.1 P曲線數(shù)學(xué)上常稱分布，其概率密度函數(shù)為：f(x)= EXCEL中概率密度函數(shù)的計(jì)算公式如下： Xp=GAMMAINV（1-P%，）+a0 式中=4/（Cs2） =2/(CvCs) a0=(1-2Cv/Cs)3.2 系列經(jīng)驗(yàn)頻率的計(jì)算1.在n項(xiàng)連續(xù)洪水系列中，按大小順序排位的第m項(xiàng)洪水的經(jīng)驗(yàn)頻率公式如下：Pm； 其中： m=1,2,3,.，n；2.在調(diào)查考證期N年內(nèi)有特大洪水a(chǎn)個(gè)，其中有l(wèi)個(gè)發(fā)生在n項(xiàng)連續(xù)洪水系列內(nèi)，l可以等于0、1、a中的任一個(gè)值。不連續(xù)系列經(jīng)驗(yàn)頻率計(jì)算公式如下：a個(gè)特大洪水的

7、經(jīng)驗(yàn)頻率為：Pm = 其中： M=1,2,3,.，a；實(shí)測系列中剩余的（n-l）項(xiàng)的經(jīng)驗(yàn)頻率公式為：Pm=+(1-)； 式中：M特大洪水排位序號，M=1、2、a； N調(diào)查考證期，指調(diào)查或考證的最遠(yuǎn)的年份至樣本資料最末一年的年數(shù)； n實(shí)測洪水項(xiàng)數(shù)； l實(shí)測洪水中提出作特大值處理的洪水個(gè)數(shù)； m實(shí)測一般洪水排位的序號，m=l+1, l+2, l+3、n； Pm實(shí)測洪水第項(xiàng)的經(jīng)驗(yàn)頻率。3.3 應(yīng)用矩法進(jìn)行統(tǒng)計(jì)參數(shù)初估3.3.1對n年連續(xù)系列，其計(jì)算公式如下：=S=Cv=3.3.2對n年不連續(xù)系列，其計(jì)算公式如下：=（+）Cv=特大洪水的洪峰流量或者洪量，j=1，2，a，n，m3/s或m3；實(shí)測一般洪

8、水的洪峰流量或洪量，i=+1，+2，n，m3/s或m3；其他符號含義同前。3.4 制作經(jīng)驗(yàn)頻率計(jì)算表格，計(jì)算出均值，變差系數(shù)Cv。（詳見圖7） 圖7 經(jīng)驗(yàn)頻率計(jì)算4. 繪制頻率曲線4.1 建立頻率曲線表格將 “經(jīng)驗(yàn)頻率計(jì)算”表中計(jì)算出的與實(shí)測洪水相對應(yīng)經(jīng)驗(yàn)頻率逐一填至“頻率曲線”表中的A列（詳見圖7中A列紅色數(shù)字），將實(shí)測洪水值相應(yīng)填至表中D列；另外應(yīng)在A列中按順序適當(dāng)添加一些建立頻率曲線所必須的頻率數(shù)值，如0.01%、1%、99%、99.9%等（詳見圖7中A列紅色數(shù)字）。在表中E2單元格中輸入公式：=$F$2*(1-2*$H$2/$I$2)+GAMMAINV(1-A2%,4/POWER($I

9、$2,2),$F$2*$H$2*$I$2/2)拖動單元格右下角的填充柄，將公式復(fù)制到本列其他單元格中。4.2 繪制頻率曲線將海森機(jī)率格紙圖復(fù)制粘貼至“頻率曲線”表，右鍵單擊圖中空白區(qū)域，點(diǎn)擊“原數(shù)據(jù)”，在對話框內(nèi)點(diǎn)擊“系列”按鈕。在“系列”欄下點(diǎn)擊“添加”按鈕，在“系列2”欄“x值” 文本框內(nèi)選取數(shù)據(jù)區(qū)域“頻率曲線！$C$2:$C72”, 在“Y值” 文本框內(nèi)選取數(shù)據(jù)區(qū)域“頻率曲線！$D$2:$D72”。 繼續(xù)點(diǎn)擊“添加”按鈕，在“系列3”欄“x值” 文本框內(nèi)選取數(shù)據(jù)區(qū)域“頻率曲線！$C$2:$C72”, 在“Y值” 文本框內(nèi)選取數(shù)據(jù)區(qū)域“頻率曲線！$E$2:$E72”，點(diǎn)擊“確定”按鈕后，右

10、鍵單擊在圖形內(nèi)出現(xiàn)的比較圓滑的散點(diǎn)，在出現(xiàn)的對話框內(nèi)點(diǎn)擊“圖表類型”，在“子圖標(biāo)類型”欄內(nèi)選擇“無數(shù)據(jù)點(diǎn)平滑線散點(diǎn)圖”，按“確定”按鈕后，光滑散點(diǎn)就變成一條光滑曲線。自此一幅完整的頻率曲線圖就基本完成了。之后讀者可根據(jù)自己的喜好，對圖形進(jìn)行必要的修改后完善。（詳見圖8） 圖8 頻率曲線圖4.3 線型適線調(diào)整“頻率曲線”表格中Cv值(H2單元格)、Cs值（I2單元格），直至圖內(nèi)光滑曲線與散點(diǎn)較好配合。5為圖表設(shè)置動態(tài)源數(shù)據(jù)在Excel 中創(chuàng)建圖表后，有時(shí)需要不斷地向源數(shù)據(jù)中添加數(shù)據(jù)，如果每次都用手工更改源數(shù)據(jù)的方法來更新圖表顯得有些繁瑣。我們可以使用“表”和公式的方法來為Excel圖表設(shè)置一個(gè)動

11、態(tài)源數(shù)據(jù)，當(dāng)向圖表源數(shù)據(jù)中添加或刪除數(shù)據(jù)后，圖表會自動更新。5.1 定義源數(shù)據(jù)區(qū)域名稱以“頻率曲線”表為例，目前可用的源數(shù)據(jù)為A1:E72，以后不斷添加，但不超出區(qū)域A1:E150。A列中數(shù)據(jù)定義的名稱為“頻率%”。單擊菜單“插入名稱定義”，在“定義名稱”對話框中，在“在當(dāng)前工作簿中的名稱”下輸入 “頻率%”，然后在“引用位置”下輸入公式：    =OFFSET($A$2,0,0,COUNTA($A$2:$A$150),1)然后按“添加”按鈕，用同樣的方法將其他列定義名稱并輸入公式。例如：B例名稱為“至P=50%的水平距離”，其公式為：    =OFFSET($B$2,0,0,COUNTA($B$2:$B$150),1)完成后，按“確定”按鈕。5.2 設(shè)置動態(tài)源數(shù)據(jù)。直接編輯系列公式：直接在圖表中單擊“計(jì)算值XP”系列，在編輯欄中看到該系列的公式為： =SERIES(,頻率曲線!$C$2:$C$72,頻率曲線!$E$2:$E$72,4)   將上述公式中$C$2:$C$72改為“距0.01%的水平距離”，$E$2:$E$72改為“計(jì)算值XP”，即=SERIES(,頻率曲線! 距0.01%的水平距離,頻率曲線! 計(jì)算值XP,4)然后按回車鍵。&