基于Excel的道路工程计算技术
- 格式：doc
- 大小：435.50 KB
- 文档页数：10
基于Excel的道路工程计算技术作者：苏建林等来源：《价值工程》2015年第17期摘要：围绕数据组织、数据的联动检索技术、Excel高级函数、Excel自定义函数、ExcelVBA程序的开发过程，系统地建立了基于Excel的道路工程计算的框架模板。
Abstract： According to the development process of data management， data linkage retrieval technology， advanced Excel function， self-defing Excel function and ExcelVBA program，this paper establishes a frame template of road engineering calculation based on Excel systematically.关键词：道路工程；数据组织；数据检索；函数应用；程序开发Key words： road engineering；data management；data retrieval；function application；program development中图分类号：U415.6 文献标识码：A 文章编号：1006-4311（2015）17-0183-030 引言道路工程的施工现场数据（施工放样、数量计算等）采用手工计算（各种计算器）具有直观好理解的有点，缺点是易出错、效率低。
用传统的编程方法（VB、VC等）优点是计算准确、效率高，缺点是需要编制专用的程序，需要专业人员有很好的编程能力，使用者需要一定的培训。
基于此，引入Excel进行相应计算，数据的输入输出（均在Excel界面）变得十分直观、高效，计算过程可以采用插入函数计算等，在广大工程人员中有了广泛应用；但是，仔细分析尚有如下不足：①数据的输入、输出在一个界面，操作时宜相互干扰，宜造成原始数据易改动，数据量大时，操作界面数据类型多、混乱；②对自定义函数应用较少，所定义的公式过于简单、缺乏可维护性；③因为缺乏对高级函数的应用，数据引用处于较低水平，方法不易分工协作；④认为VBA就是编程，多数人退避三舍，造成批量数据计算手工操作过多，效率低、强度高。
下面以常见的已知导线点坐标、已知中桩坐标，按指定导线点为测站和后视点计算极坐标放样数据的excel过程来展示基于数据管理、检索、批量数据处理系统过程、高级函数、自定义函数和VBA程序。
1 界面设计“导线点”表单（表1）、“中桩坐标”表单（表2）向“总控界面”表单（表3）提供数据检索和关联的原始数据，总控界面计算单个放样数据、同时可以批量计算数据填写于“批量计算”表单（表4）。
总控界面可以自动检索“导线点”表单导线点个数、“中桩坐标”表单中桩个数，测站、后视点、开始桩号、结束桩号、单个中桩（桩号）均采用下拉列表选择，相应的坐标（X，Y）自动检索后填充于表格，这样可以防止数据出错、同时方便数据分类管理。
2 关键技术以下主要讨论：单个数据防止出错，需要检索；批量数据处理需要VBA对单元格调用。
2.1 高级内置函数应用：数据管理、数据联动检索使用2.1.1 VLOOKUP函数本文基于office 2013版本写作（其他版本的office可供参考）。
该函数的主要功能[1]：按着查找目标把其他表单对应值返回当前单元格—把具有相同查找目标的值从其他表单返回当前表单指定位置。
如表3中“主控界面”（B9即第2列第9行）下拉列表找到单个中桩的桩号（K115+500），则后面两列（C9、D9）中通过该函数自动检索到“中桩坐标”表单对应的X、Y值（4038008.989、531541.360）。
VLOOKUP函数的调用格式VLOOKUP函数的调用基本格式为：VLOOKUP（lookup_value，table_array，col_index_num，range_lookup），VLOOKUP各参数具体功能如表5。
VLOOKUP 函数的进一步说明，VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找），下面以表3中B11单元格为例予以介绍：查找目标：VLOOKUP（B10，中桩坐标！$A$2：$C$999999，2，FALSE）中的第一个参数—“B10”，即中桩桩号，当前为“ZK115+500”。
查找范围：VLOOKUP（B10，中桩坐标！$A$2：$C$999999，2，FALSE）中的第二个参数—“中桩坐标！$A$2：$C$999999”，即表2“中桩坐标”表单的A～C列1-999999行的区域，999999—是区域的行数，可以根据需要进行相应变化。
需要特别注意的是：查找目标（桩号列）一定要在该区域的第一列；该区域中一定要包含要返回值所在的列，本例中要返回的值是X。
返回值的列数：VLOOKUP（B10，中桩坐标！$A$2：$C$999999，2，FALSE）中的第3个参数—“2”，它是一个整数值。
它是“返回值”在第二个参数给定的区域中的列数。
本例中我们要返回的是“X”，它是第二个参数查找范围“中桩坐标！$A$2：$C$999999”的第2列。
这里一定要注意，列数不是在工作表中的列数，而是在查找范围区域的第几列。
精确OR模糊查找：VLOOKUP（B10，中桩坐标！$A$2：$C$999999，2，FALSE）中最后一个参数—“FALSE”，是决定函数精确和模糊查找的关键。
精确即完全一样，模糊即包含的意思。
第4个参数如果指定值是0或FALSE就表示精确查找，而值为1 或TRUE时则表示模糊。
2.1.2 excel单元格下拉列表的建立excel单元格下拉列表的建立流程（以excel2013为例）：数据验证→数据验证→设置→序列→来源→选定所需表单对应的列（从表3中选定）。
以设置导线点的测站为例（后视点、开始桩号、结束桩号、单个中桩也需要类似设置），需要选择导线点表单的第一列数据，即表1的第一列——编号。
2.2 自定义函数：数据格式、方位角计算函数统一的全局常量定义，所有模块均用到此常量，这样其他模块均可以用到常量Pi的值。
原代码如下：Public Const pi = 3.14159262.2.1 数据格式函数radtodms（rad）函数功能：把弧度转变为度分秒，度、分均为整数，秒为保留一位小数的数值。
rad为参数，radtodms为函数，源代码如下：Function radtodms（rad）degree = rad * 180 / pid = Int（degree）f = Int（（degree - d） * 60）s = Int（（（degree - d - f / 60） * 3600） * 10） / 10radtodms = Str$（d） + "°" + LTrim（Str$（f））+ "′" + LTrim（Str$（s））+ "″"End Function2.2.2 方位角计算函数fwj（dx， dy）函数，dx、dy分别为某点相对于测站的X、Y的坐标增量；fwj为对应于dx、dy的方位角，方位角计算结果单位为弧度。
dx=0时，需要单独讨论。
Function fwj（dx， dy）If dx = 0 ThenIf dy = 0 Then fwj = "出错！"If dy > 0 Then fwj = pi / 2If dy < 0 Then fwj = pi * 3 / 2Elsexxj = Atn（Abs（dy / dx））If dx >= 0 And dy > 0 Then fwj = xxjIf dx 0 Then fwj = pi - xxjIf dxIf dx >= 0 And dy < 0 Then fwj = 2 * pi - xxjEnd IfEnd Function2.2.3 夹角计算当放样采用极坐标夹角方式时，后视方向的坐标增量为dtx， dty，中桩方向的坐标增量为dx， dy，jiajiao为计算结果，是中桩方向方位角与后视方向方位角的差。
Function jiajiao（dx， dy， dtx， dty）'计算后视方向hs = fwj（dtx， dty）'计算单个中桩夹角jiajiao = fwj（dx， dy） - hsIf jiajiao < 0 Then jiajiao = jiajiao + 2 * piIf jiajiao > 2 * pi Then jiajiao = jiajiao - 2 * piEnd Function2.3 VBA程序开发——批量计算数据计算后视方位角，确定计算范围，表头填写，逐桩方位角计算、距离计算、夹角计算，数据回填表格。
核心技术是表格的引用[2]。
以源代码中的第五行为例，“Worksheets（"主控界面"）.Cells （4，3）”对应表3中的C4单元格，其他的引用可以参照此。
既可以从单元格提取信息（一般提取信息为文本，必要时需要通过VAL函数转变为数值才能参加计算）也可以单元格写入信息。
源代码略。
3 运行过程运行总体步骤如下：第一步：在表3中，输入原始数据表单并校核；第二步，在表3中，利用下拉列表选择测站点编号、后视点编号、单个中桩桩号，此时excel自动计算该中桩对应的极坐标放样数据—方位角和距离等；第三步：在表3中，选定开始桩号、结束桩号，批量计算放样数据按钮，程序计算的成果如表4。
4 结论本文通过创建excel模板文件解决的问题如下：①单个数据计算为了防止输入错误采用了下拉列表与Vlookup函数组合的方式；②为了完成单个数据的计算、格式化自定义了若干函数并予以引用举例；③为了完成批量数据计算编制了VBA程序。
对于道路工程施工数据数据计算过程具有高效、防错等作用。
本计算思路不但可以用于施工计算，也可以用于设计计算。
参考文献：[1]杨章伟.Office2013应用大全（精粹版）[M].北京：机械工业出版社，2013.[2]郭刚.Excel 2013 VBA入门与应用[M].北京：清华大学出版社，2014.[3]王科，孟津.MATLAB在工程计算中的几则技巧[J].成都电子机械高等专科学校学报，2007（02）.。
