道路设计-竖曲线Excel计算表1

半径R18000切线T 182.1184.3变坡点桩号OP 4E+05第一纵坡I1-0.01第二纵坡I20.009曲线长L 364.25变坡点高程H 5.936外距E 0.9214桩号高程l 高差未输入竖曲线高程3890FALSE FALSE FALSE ######3913606.978#####0.243 6.736399670FALSE FALSE FALSE 81.692399680FALSE FALSE FALSE 81.782399690FALSEFALSE FALSE 81.872FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSEFALSE FALSE ######FALSE FALSE FALSE ######请输入蓝色框里的数据及桩号FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE######FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE###### FALSE FALSE FALSE######。

变坡点桩号变坡点高程R i1i2T L E 416.922240.64815000 2.374% 3.200%61.950123.90.128416.922240.64815000 2.374% 3.200%61.95123.90.128416.922240.64815000 2.374% 3.200%61.95123.90.128416.922240.64815000 2.374% 3.200%61.95123.90.128416.922240.64815000 2.374% 3.200%61.95123.90.128416.922240.64815000 2.374% 3.200%61.95123.90.128416.922240.64815000 2.374% 3.200%61.95123.90.128416.922240.64815000 2.374% 3.200%61.95123.90.128416.922240.64815000 2.374% 3.200%61.95123.90.128416.922240.64815000 2.374% 3.200%61.95123.90.128变坡点桩号变坡点高程R i1i2T L E 124102.532240.7915000-0.75%0.789%115.725231.450.446124102.532240.7915000-0.75%0.789%115.725231.450.446124102.532240.7915000-0.75%0.789%115.725231.450.446124102.532240.7915000-0.75%0.789%115.725231.450.446124102.532240.7915000-0.75%0.789%115.725231.450.446124102.532240.7915000-0.75%0.789%115.725231.450.446124102.532240.7915000-0.75%0.789%115.725231.450.446变坡点桩号变坡点高程R i1i2T L E 55530.442663000 2.439%-2.58%75.23150.450.943变坡点桩号变坡点高程R i1i2T L E 5200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.170凹型竖------小桩号------5200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.170变坡点桩号变坡点高程R i1i2T L E5200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.000%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.1705200134.393200000.00%-0.82%82.400164.80.170-----变坡点桩号变坡点高程R i1i2T L E55530.4426630000.000%-2.576%38.64077.280.249 55530.4426630000.00%-2.58%38.64077.280.249 55530.4426630000.00%-2.58%38.64077.280.249 55530.4426630000.00%-2.58%38.64077.280.249 55530.4426630000.00%-2.58%38.64077.280.249 55530.4426630000.00%-2.58%38.64077.280.249 55530.4426630000.00%-2.58%38.64077.280.249前坡竖曲线起点竖曲线终点米数待求桩号变坡点桩号y 改正前高程354.970478.870-79.970275.000416.9200.0002237.279354.970478.870-78.970276.000416.9200.2082237.303354.970478.870-77.970277.000416.9200.2032237.326354.970478.870-76.970278.000416.9200.1972237.350354.970478.870-75.970279.000416.9200.1922237.374354.970478.870-74.970280.000416.9200.1872237.398354.970478.870-73.970281.000416.9200.1822237.421354.970478.870-72.970282.000416.9200.1772237.445354.970478.870-71.970283.000416.9200.1732237.469354.970478.870-70.970284.000416.9200.1682237.492后坡竖曲线起点竖曲线终点_X待求桩号变坡点桩号y 改正前高程123986.805124218.255104.505124113.750124102.5300.3642240.879123986.805124218.25589.505124128.750124102.5300.2672240.997123986.805124218.25574.505124143.750124102.5300.1852241.115123986.805124218.25559.505124158.750124102.5300.1182241.234123986.805124218.25544.505124173.750124102.5300.0662241.352123986.805124218.25535.505124182.750124102.5300.0422241.423123986.805124218.25525.785124192.470124102.5300.0222241.500前坡竖曲线起点竖曲线终点米数_X待求桩号变坡点桩号y 改正前高程55455.21555605.665 5.47555460.69055530.4400.005264.299前坡竖曲线起点竖曲线终点米数_X待求桩号变坡点桩号y改正前高程5117.6005282.40057.60050605200.000134.3935117.6005282.40017.60051005200.000134.3935117.6005282.4007.60051105200.000134.3935117.6005282.400 4.0405113.565200.000134.3935117.6005282.40022.40051405200.0000.013134.3935117.6005282.40032.40051505200.0000.026134.3935117.6005282.40039.40051575200.0000.039134.3935117.6005282.40044.40051625200.0000.049134.3935117.6005282.40067.40051855200.0000.114134.3935117.6005282.40075.40051935200.0000.142134.3935117.6005282.40081.40051995200.0000.166134.3935117.6005282.40082.40052005200.0000.170134.393凹型竖曲线------小桩号（前坡）凹型竖曲线------大桩号（后坡）凸型竖曲线-----前坡凸型竖曲线-----前坡5117.6005282.40067.1005184.7005200.0000.113134.3935117.6005282.40050329.84155447.4415200.00063327.322134.3935117.6005282.40050337.10055454.7005200.00063345.591134.3935117.6005282.40050329.55055447.1505200.00063326.590134.3935117.6005282.40050322.35055439.9505200.00063308.473134.3935117.6005282.40050315.40055433.0005200.00063290.987134.3935117.6005282.40050308.80055426.4005200.00063274.384134.3935117.6005282.40050302.35055419.9505200.00063258.160134.3935117.6005282.40050296.15055413.7505200.00063242.568134.3935117.6005282.40050290.10055407.7005200.00063227.354134.3935117.6005282.40050338.90055456.5005200.00063350.121134.393后坡竖曲线起点竖曲线终点米数_X待求桩号变坡点桩号y 改正前高程5117.6005282.40082.40052005200.0000.170134.3935117.6005282.40078.0005204.45200.0000.152134.3575117.6005282.40067.9005214.55200.0000.115134.2745117.6005282.40063.9005218.55200.0000.102134.2415117.6005282.40053.40052295200.0000.071134.1545117.6005282.40032.40052505200.0000.026133.9815117.6005282.40029.40052535200.0000.022133.9565117.6005282.400 2.40052805200.0000.000133.7345117.6005282.400-0.60052835200.0000.000133.7095117.6005282.400-7.60052905200.000133.6515117.6005282.400-17.60053005200.000133.5695117.6005282.400-67.60053505200.000133.1575117.6005282.400-117.60054005200.000132.7455117.6005282.400-130.60054135200.000132.6385117.6005282.400-135.60054185200.000132.5975117.6005282.400-167.60054505200.000132.3335117.6005282.400-168.7705451.175200.000132.3235117.6005282.400-217.60055005200.000131.9215117.6005282.400-238.7705521.175200.000131.7475117.6005282.400-267.60055505200.000131.5095117.6005282.400-317.60056005200.000131.0975117.6005282.400-328.60056115200.000131.0065117.6005282.400-356.60056395200.000130.776后坡竖曲线起点竖曲线终点米数_X待求桩号变坡点桩号y改正前高程凸型竖曲线-----后坡凸型竖曲线-----后坡-----直坡上55491.80055569.08055611.73055530.4400.000263.906 55491.80055569.08055606.93055530.440264.030 55491.80055569.08055611.7355530.440263.906 55491.80055569.08055626.7355530.440263.520 55491.80055569.08055641.7355530.440263.133 55491.80055569.08055653.7355530.440262.824 55491.80055569.08055665.155530.440262.531改正后高程桩号外边缘标高2237.279275.0002237.0942237.510276.0002237.3252237.529277.0002237.3442237.548278.0002237.3632237.566279.0002237.3812237.585280.0002237.4002237.604281.0002237.4192237.622282.0002237.4372237.641283.0002237.4562237.660284.0002237.475改正后高程桩号2241.243124113.7502241.0582241.264124128.7502241.0792241.300124143.7502241.1152241.352124158.7502241.1672241.418124173.7502241.2332241.465124182.7502241.2802241.522124192.4702241.33755419.95055426.400改正后高程桩号55447.150264.29455460.69055454.700155439.95055433.00055413.750改正后高程桩号134.3935060.000134.578134.3935100.000134.578134.28850356.500 134.3935110.000134.578134.28850346.500 134.3935113.560134.578134.28850342.940 134.3805140.000134.565134.27550316.500 134.3675150.000134.552134.26250306.500 134.3545157.000134.539134.24950299.500 134.3445162.000134.529134.23950294.500 134.2795185.000134.464134.17450271.500 134.2515193.000134.436134.146134.2275199.000134.412134.122134.2235200.000134.408134.118134.2805184.700134.465134.175-63192.92955447.441#########(63193.034)-63211.19855454.700#########(63211.303) 1.800 -63192.19755447.150#########(63192.302)9.350 -63174.08055439.950#########(63174.185)16.550 -63156.59455433.000#########(63156.699)23.500 -63139.99155426.400#########(63140.096)30.100 -63123.76755419.950#########(63123.872)36.550 -63108.17555413.750#########(63108.280)42.750 -63092.96155407.700#########(63093.066)48.800 -63215.72855456.500#########(63215.833)0.000改正后高程桩号路面边沿高程134.2235200.000134.088134.2055204.400134.070134.1585214.500134.023134.1385218.500134.003134.0835229.000133.948133.9555250.000133.850133.9355253.000133.830133.7345280.000133.629133.7095283.000133.604133.6515290.000133.546133.5695300.000133.464133.1575350.000133.052132.7455400.000132.640132.6385413.000132.533132.5975418.000132.492132.3335450.000132.228132.3235451.170132.218131.9215500.000131.816131.7475521.170131.642131.5095550.000131.404131.0975600.000130.992131.0065611.000130.901130.7765639.000130.671改正后高程桩号263.90655611.730264.091 264.03055606.930264.215 263.90655611.730264.091 263.52055626.730263.705 263.13355641.730263.318 262.82455653.730263.009 262.53155665.100262.716。

利用Excel表格进行全线线路竖曲线的统一计算高速公路纵断面线型比较复杂，竖曲线数量比较多。

由于相当多的竖曲线分段造成了设计高程计算的相对困难，为了方便直接根据里程桩号计算设计高程，遂编制此计算程序。

程序原理：1、根据设计图建立竖曲线参数库；2、根据输入里程智能判断该里程位于何段竖曲线上；3、根据得到的竖曲线分段标志调取该分段的曲线参数到计算表格中；4、把各曲线参数带入公式进行竖曲线高程的计算；5、对程序进行优化和简化，去掉中间环节，进行直接计算；6、防止计算过程中的误操作，对计算表进行相应的保护。

竖曲线的高程计算原理公式：H=G+B*A+(-1)^J*X2÷(2R)H: 计算里程的设计高程K: 计算点里程D: 竖曲线交点里程G: 竖曲线交点的高程R: 竖曲线半径T: 切线长M: 前坡度I1P: 后坡度I2A: A=Abs(K-D)X: A>T => X=0; A<T => X=T-AJ: M-P<0 => J=0; M-P>=0 => J=1B: K<=D =>B=-M ; K>D => B=P程序特色：1、可以无限添加竖曲线，竖曲线数据库不限制竖曲线条数；2、直接输入里程就可以计算设计高程，不需考虑该里程所处的竖曲线分段；3、对计算公式进行保护，表格中不显示公式，不会导致公式被错误修改或恶意编辑。

程序的具体编制步骤：1、新建Excel工作薄，对第一第二工作表重新命名为“参数库”和“计算程序”，根据设计图建立本标段线路竖曲线的参数库，需要以下条目：（1）、竖曲线编号；（2）、竖曲线的前后坡度（I1、I2）不需要把坡度转换为小数；（3）、竖曲线半径、切线长（不需要考虑是凸型或凹型）；（4）、竖曲线交点里程、交点高程；（5）、竖曲线起点里程、终点里程（终点里程不是必要参数，只作为复核检测用）；如图1所示：图12、进行计算准备：（1）、根据输入里程判断该里程所处的曲线编号：需要使用lookup函数，函数公式为“LOOKUP(A2,参数库!H3:H25,参数库!A3:A25)”。