Excel回归在水文分析计算中的应用

庞进 邱梦晨 解金瑞

【摘 要】回归分析在水文分析计算经常使用，但其大量的数据计算比较麻烦。Excel电子表” 中的回归分析工具使统计指标计算的工作大大简化，保证所建立的回归模型中的变量x与y之间具有显著的线性相关性，数据分析的准确性和可靠性提高。本文从判断上下游之间的洪峰流量是否存在显著的线性关系，以及依靠上游已经出现的洪水来预报下游即将出现的洪峰的方法和步骤，介绍如何应用Excel电子表回归分析工具进行水文分析计算。

【关键词】Excel；回归分析；水文计算；应用

中图分类号： P333.9 文献标识码： A 文章编号： 2095-2457（2018）13-0257-001

DOI：10.19694/j.cnki.issn2095-2457.2018.13.120

【Abstract】regression analysis is often used in hydrological analysis calculation， but its large amount of data calculation is troublesome. Excel spreadsheet "regression analysis tools of statistical indicators calculation work greatly simplified， ensure that the established regression model of the variables had a significant linear correlation between x and y， the veracity and reliability of data analysis. Peak flow between upstream and downstream from the judgment whether there was a significant linear relationship， and rely on the upstream has appeared to flood forecast methods and steps of the downstream flood peak of the upcoming， introduce how to use Excel spreadsheets regression analysis tools for hydrologic analysis and calculation.

【Key words】Excel regression analysis； Hydrological calculation application

回歸分析是一种处理变量的统计相关关系的一种数理统计方法，回归分析广泛很应用， 例如实验数据的一般处理、 经验公式的求得、因素分析、产品质量的控制、气象及地震预报、自动控制中数学模型的制定等等。

1 Excel输入相关水文分析资料

选取黄河下游高村、孙口水文站30个洪水资料，并应用“Excel电子表”中的“回归分析工具”计算上下游之间的洪峰流量之间是否具有相关关系的有关指标。在“Windows”界面中，用鼠标单台“开始”，再单击“程序”及其下拉菜单中的“ Microsoft Excel”程序，即可启动“ Excel电子表格”，出现一张标为“Book1”的空白“Excel电子表”。

其中Q下、Q上分别为下游站、上游站洪峰流量，从中找出以上游洪峰流量（x）来表示下游洪峰流量（y）的回归方程，即下游站洪峰流量的预报数学模型。

2 调用回归分析工具计算相关统计指标

在“Excel工作表”的“菜单”栏单击“工具”选项，在下拉菜单中单击“数据分析”选项，弹出“数据分析”的“分析工具”选项框，从中选中“回归”分析工具（）。单击“确定”后，弹出“分析工具”中的“回归”分析对话框。

如果在“工具”菜单中没有出现“数据分析”命令，必须在“Excel工作表”中安装“分析工具库”。即在“工具”菜单中，单击“加载宏”命令。然后在“当前加载宏”的对话框中选中“分析工具库”，单击“确定”按钮，“数据分析”工具就加载完成。（注意：此时的Microsoft office 必须为完全安装版，如果是典型安装，则无法完成此项操作。）

现在只需要在“回归”分析对话框中输入相应的数据并确定相关的选项即可。首先，单击“Y值输入区域（Y）：”右边的红色箭头，折叠对话框，然后用鼠标选中Q下（y值）。单击折叠对话框，回到“分析工具”的“回归”分析对话框。用同样的方法单击“X值输入区域（X）：”右边的红色箭头，折叠对话框，然后用鼠标选中Q上（x）值。再次单击折叠对话框，回到“分析工具”的“回归”分析对话框。在“回归分析对话框”中除了在“Y值输入区域”因变量数据区域的引用和在“X值输入区域”输入对自变量数据区域的引用外，还有一些根据分析的需要所作的选项。主要有：标志、置信度、常数为零、输出区域、新工作表组、新工作簿、残差、标准残差、残差图、正态概率图等复选框，根据分析的需要进行选择。这里我们选中“残差”、“标准残差”、“残差图”复选框。

检查“回归分析对话框”中的各个选项，然后单击“确定”按钮，系统在默认情况下，在当前工作簿中插入新工作表（按当前出现的表格数依次后续排列，如目前表格最大为sheet3，则分析结果表格自动生成sheet 4），并从新工作表的A1单元格开始粘贴回归分析工具的计算结果。这样回归分析的计算结果就计算出来了。

3 水文预报效果检验

当然，该模型是否达到水文预报的精度和标准，我们还需要采用相应的规范用以检验。由于本次指示介绍Excel回归分析工具在水文计算中的应用，本次未作预报结果的实际应用检验，只是绘制拟合效果图，作预报的效果检验。

擬合效果图可以反映预报值和实际值之间的拟合程度，从而断定预报模型的准确程度。可以看出，预报值与实际值之间的拟合程度较好，但实际应用效果未曾检验，但我们可以断定，采用Excel电子表进行水文预报，如果适当地增加参数，即采用多元线性回归分析，其效果是显而易见的（应用此方法作单站水文预报，目前在高村水文站已经试用，其各项精度指标均达到了《水文情报预报规范（SL250-2000）》中的甲级标准）。

4 结论

显而易见，Excel电子表回归分析工具的应用，为我们水文分析计算带来了极大的便利，不仅能够利用其进行分析计算，建立起用于相应的回归方程，而且能够保证所建立的回归模型中的变量x与y之间具有显著的线性相关性，杜绝出现假相关、伪相关的情况，进而使数据分析的准确性和可靠性提高。可以预言，Excel电子表回归分析工具在水文计算中的普遍应用，将会使测站在水文测报上有更加可靠的依据。该工具在探索应用中的领域将会越来越宽，也将越来越被水文行业所使用。

【参考文献】

[1]《应用回归分析》中国人民大学出版社.2007.

[2]《中长期水文预报》.河海大学函授部.2004.

[3]熊美贞.Excel在水文水利分析与计算中的应用[J].科技与创新，2015（01）：102-103.

[4]Shanghong Zhang，Bohua Zhao，E.Erdun.Watershed Characteristics Extraction and Subsequent Terrain Analysis Based on Digital Elevation Model in Flat Region[J].Journal of Hydrologic Engineering，2014.

[5]Marcelo Cerucci，Gopi K.Jaligama.Hydrologic and Water Quality Integration Tool：HydroWAMIT[J].Journal of Environmental Engineering，2008，134（8）.

[6]J.P.Wang，Duruo Huang，Su-Chin Chang，Logan Brant.On-site earthquake early warning with multiple regression analysis：Featuring two user-friendly applications for Excel[J].Computers and Geosciences，2013，58.

[7]Angus M.Brown.A step-by-step guide to non-linear regression analysis of experimental data using a Microsoft Excel spreadsheet[J].Computer Methods and Programs in Biomedicine，2001，65（3）.

[8]David J Greensmith. Ca analysis： An Excel based program for the analysis of intracellular calcium transients including multiple， simultaneous regression analysis[J]. Computer Methods and Programs in Biomedicine，2014，113（1）.