土石方计算程序.xls

使使 用用 说说 明明 一、本工作薄含两张计算表格，断面法和方格网法，均采用传统计算公式编辑。请在使用任何一张表格前认真阅读此说明。二、表格的使用方法：（一）、断面法表的使用：断面法主要用于线路、管沟等狭长工程土石方的计算。表格设计了十一个断面、十个计算段的土石方挖填计算。采用平均断面法和圆锥台体计算法的综合计算公式，简介如下：1、断面面积的计算：每个断面设计了十五个高程点和十四小段（横断面上）间距，分别填上相对应点的设计高减去地面高的高差值和该点到下一点水平距离，有多少个点，填多少个点的数据，表格自动分成若干小断面计算其面积，如下图所示（部分）：现分解1-2点的小断面计算：S1=（1.25+2.04）*2.5/2=4.11（填M）分解2-3点小断面计算：S2（填）=D2*h2/2；S2（挖）=ABS（2.8-D2）*h3/2）式中D2=2.8/（2.04+ABS（-1.32）*2.8 D2=1.7 S2（填）=1.7*2.04/2=1.73（填M）；S2（挖）=1.1*1.32/2=0.73（挖M）S（填）=S1（填）+S2（填）+Sn（填）；S（挖）=S1（挖）+S2（挖）+Sn（挖）。2、断面间挖填方量计算：设相邻两断面挖（或、填）的断面面积为S1、S2，且S1S2,当S1/S20.6时:V=(S1+S2)/2*L(平均断面法)L为两断面间距离;当S1/S2S2,同理反除.3、将所有断面间挖填方量分别统计计算，最后得到总的挖填方量。（二）、方格网法表的使用：主要用于场地平整，设计分1010共100个方格网的计算，可连续输入和间隔输入计算。1、计算公式：分全挖全填、半挖半填、三点挖填+一点填挖、相对两点挖填或对应另两点填挖等四大类。h1 h2 h1 S h2 、全挖全填：V=（h1+h2+h3+h4）*S/4 S 、半挖半填：V1=（a+b）*S/2*（h1+h2）/4 式中a=S/（ABS（h1）+ABS（h4）*ABS（h1）；h4 h3 h4 h3 b=S/（ABS（h2）+ABS（h3）*ABS（h2）S 、三点挖填：a*b0.3*S；V1=（S-ab/2）*（h1+h2+h3）/4；h1 h2 h1 c h2 a*b0.3*S；V1=（S-ab/2）*（h1+h2+h3）/3。三点挖填 d 、直三角锥形：V=a*b/2*h/3。S 相对两点 式中：a=S/(ABS(h1）+ABS(h4)*h4；a a 挖填1500至下段距离高 差 1.25 2.04 -1.32水平距离2.5 2.8桩 号 点 号 1 2 3 4=前后桩号差L全挖全填a b=S/（ABS(h4)+ABS(h3)*h4 。h4 b h3 、直角六边棱台形（相对两点挖填）h4 b h3 V1=(S-a*b/2-c*d/2)*(h1+h3)/4。式中a、b、c、d的求法同上。三、两张表均开放代码。非VBA编程，无加载宏命令，直接用单元格公式编辑，放心使用。你可以打开表后在计算表后面的众多列中（红色框大范围内）点击有公式编辑的单元格，在公式编辑栏中具体查看公式编辑的方法和步骤，了解不用VBA，不加载宏命令同样能设计出解决测量实际问题的做法。而用于显示的计算表，你完全可以设计得更美观、漂亮、更具人性化。一、本工作薄含两张计算表格，断面法和方格网法，均采用传统计算公式编辑。请在使用任何一张表格前认真阅读此说明。（一）、断面法表的使用：断面法主要用于线路、管沟等狭长工程土石方的计算。表格设计了十一个断面、十个计算段的土石方 1、断面面积的计算：每个断面设计了十五个高程点和十四小段（横断面上）间距，分别填上相对应点的设计高减去地面高的高差值和该点到下一点水平距离，有多少个点，填多少个点的数据，表格自动分成若干小断面计算其面积，如下图所示（部分）：分解2-3点小断面计算：S2（填）=D2*h2/2；S2（挖）=ABS（2.8-D2）*h3/2）式中D2=2.8/（2.04+ABS（-1.32）*2.8S（填）=S1（填）+S2（填）+Sn（填）；S（挖）=S1（挖）+S2（挖）+Sn（挖）。（二）、方格网法表的使用：主要用于场地平整，设计分1010共100个方格网的计算，可连续输入和间隔输入计算。1、计算公式：分全挖全填、半挖半填、三点挖填+一点填挖、相对两点挖填或对应另两点填挖等四大类。h1 h2 h1 S h2 式中a=S/（ABS（h1）+ABS（h4）*ABS（h1）；h4 h3 h4 h3 b=S/（ABS（h2）+ABS（h3）*ABS（h2）S 、三点挖填：a*b0.3*S；V1=（S-ab/2）*（h1+h2+h3）/4；h1 h2 h1 c h2 a*b0.3*S；V1=（S-ab/2）*（h1+h2+h3）/3。三点挖填 d 、直三角锥形：V=a*b/2*h/3。S 相对两点 式中：a=S/(ABS(h1）+ABS(h4)*h4；a a 挖填a b=S/（ABS(h4)+ABS(h3)*h4 。h4 b h3 、直角六边棱台形（相对两点挖填）h4 b h3 三、两张表均开放代码。非VBA编程，无加载宏命令，直接用单元格公式编辑，放心使用。你可以打开表后在计算表后面的众多列中（红色框大范围内）点击有公式编辑的单元格，在公式编辑栏中具体查看公式编辑的方法和步骤，了解不用VBA，不加载宏命令同样能设计出解决测量实际问题的做法。而用于显示的计算表，你完全可以设计得更美观、漂亮、更具人性化。断断 面面 法法 土土 石石 方方 挖挖 填填 方方 量量 计计 算算 表表本本次次计计算算总总挖挖方方量量=227.4(227.4(立立方方米米)本本次次计计算算总总填填方方量量=桩 号点号123456789K001+500.0高差-1.301.501.321.861.551.060.860.530.25水平距离2.51.83.64.21.52.53.03.0至下断面距离20挖方面积M0.8填方面积M25.9至下一段挖方量M桩 号点号123456789K001+520.0高差1.001.251.321.861.551.060.860.530.25水平距离2.51.83.64.21.52.53.03.0至下断面距离20挖方面积M2.9填方面积M25.8至下一段挖方量M桩 号点号123456789K001+540.0高差-1.00-1.00-1.32-0.86-1.15-1.05-0.98-0.06-0.02水平距离2.51.83.64.21.52.53.03.0至下断面距离挖方面积M18.6填方面积M0.1至下一段挖方量M桩 号点号123456789高差水平距离至下断面距离挖方面积M填方面积M至下一段挖方量M桩 号点号123456789高差水平距离至下断面距离挖方面积M填方面积M至下一段挖方量M桩 号点号123456789高差水平距离至下断面距离挖方面积M填方面积M至下一段挖方量M桩 号点号123456789高差水平距离至下断面距离挖方面积M填方面积M至下一段挖方量M桩 号点号123456789高差水平距离至下断面距离挖方面积M填方面积M至下一段挖方量M断面号点号123456789高差水平距离至下断面距离挖方面积M填方面积M至下一段挖方量M桩 号点号123456789高差水平距离至下断面距离挖方面积M填方面积M至下一段挖方量M桩 号点号123456789高差水平距离2.5至下断面距离挖方面积M填方面积M至下一段挖方量M桩 号点号123456789高差水平距离挖方面积M填方面积M775.4(775.4(立立方方米米)1011121314150.050.050.020.462.82.82.82.62.534.5至下一段填方量M516.9101112131415-0.25-0.35-1.022.82.82.8192.9至下一段填方量M258.51011121314150.052.8至下一段填方量M101112131415至下一段填方量M101112131415至下一段填方量M101112131415至下一段填方量M101112131415至下一段填方量M101112131415至下一段填方量M101112131415至下一段填方量M101112131415至下一段填方量M101112131415断断 面面 法法 土土 石石 方方 挖挖 填填 方方 量量 计计 算算 表表本本次次计计算算总总填填方方量量=90.25至下一段挖方量M90.25至下一段挖方量M9-0.02至下一段挖方量M9至下一段挖方量M9至下一段挖方量M9至下一段挖方量M9至下一段挖方量M9至下一段挖方量M9至下一段挖方量M9至下一段挖方量M9至下一段填方量M101112131415至下一段挖方量M9计算公式编辑区1.00452.5385.7247.1611.95752.40.7545000002.81252.3135.7247.1611.95752.40000000000002.52.0883.9244.2211.652.5375000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000775.4296227.36182.0851.170.420.140.0980.6240.575025.896964 516.9496000000000.7544643 34.500180.99617850.2572332.0851.170.1750000025.798258.48000.1750.841.9180002.933 192.86160.00193810.1576161000.05000000.0501.560.120.0080000018.60850000000000000000000000011000000000000000000001100000000000000000000110000000000000000000011000000000000000000001100000000000000000000110000000000000000000011计算公式编辑区000000000000000000001100000000000000000000 本表可提供1010共100个方格网的场地土石方挖填方计算,只需在格网边长单元格中填上边长(M),在格网的每个角顶右上单元格中填上设计标高、右下单元格中填上地面标高即可得到场地的总挖方量和总填方量。可连续输入或间隔输入，当一个图形的4个角顶都有值时，这个格网将被浅绿色填充并进行计算，否则为白色格网并不参与计算。根据场地各顶点标高，程序自动按传统的场地平整计算方法和公式对参与计算的每个格网的挖填方量进行计算，最后汇总得到总量。(红色字体为设计高,黑色字体为地面高)本表按教科书上计算公式进行编辑计算,敬请同仁指正。边边长长=100100总总填填方方量量=+80135.1M+80135.1M总总挖挖方方量量=-87896.7M-87896.7M场场地地总总面面积积=370000(370000(平平方方米米)约约合合555(555(亩亩)53.45053.45053.85054.05054.23054.09054.68054.7254.80054.86048.500154.010153.920154.730154.000154.000153.960153.990154.650154.66014.950-0.560-0.070-0.6800.2300.0900.7200.7300.1500.200填方=+6658.3填方=+0.0填方=+0.0填方=+46.4填方=+266.7填方=+1188.5填方=+4148.7填方=+2870.1填方=+1508.1填方=+3700.0挖方=-3340.2挖方=-4400.0挖方=-6250.0挖方=-4955.6挖方=-1066.7挖方=-413.1挖方=-0.0挖方=-231.1挖方=-226.9挖方=-0.053.45053.45053.35053.55053.75053.61053.95054.20054.32054.88054.010154.010153.920154.730154.000154.000153.960153.990154.650154.6601-0.560-0.560-0.570-1.180-0.250-0.390-0.0100.210-0.3300.220填方=+0.0填方=+1645.5填方=+5662.0填方=+1263.0填方=+0.0填方=+0.0填方=+1002.3填方=+408.3填方=+440.0填方=+1525.0挖方=-6175.0挖方=-4716.7挖方=-1460.8挖方=-4231.1挖方=-3200.0挖方=-2000.0挖方=-2.3挖方=-1008.3挖方=-990.0挖方=-0.053.45053.45053.35053.55053.75053.61053.95054.20054.32054.88054.000154.250151.500152.000154.000154.000153.960153.990154.650154.6601-0.550-0.8001.8501.550-0.250-0.390-0.0100.210-0.3300.220填方=+2723.4填方=+1645.5填方=+5662.0填方=+1263.0填方=+0.0填方=+0.0填方=+1002.3填方=+408.3填方=+440.0填方=+1012.5挖方=-4221.3挖方=-4692.2挖方=-1460.8挖方=-4231.1挖方=-3200.0挖方=-2000.0挖方=-2.3挖方=-1008.3挖方=-990.0挖方=-742.953.45053.45053.35053.55053.75053.61053.95054.20054.32054.88051.000154.000153.920154.730154.000154.00053.96053.99054.65054.6602.450-0.550-0.570-1.180-0.250-0.390-0.0100.210-0.3300.220填方=+2723.4填方=+2019.9填方=+3424.9填方=+109.1填方=+0.0挖方=-3665.9挖方=-3871.6挖方=-1996.6挖方=-5230.7挖方=-5375.053.45053.45053.35053.55053.75053.61054.000154.000151.36053.02054.55054.320-0.550-0.5501.9900.530-0.800-0.710填方=+10004.2填方=+15363.9挖方=-504.2挖方=-36.453.45053.45053.35051.00051.00051.5202.4502.4501.83055537.054.90053.980104.95089.83710.16334-0.5600.0000-0.0700.92089.83666 6658.266-3340.2200-4400020386.76658.266 6658.266-3340.22000020883.56658.266-3340.2200-3340.220-3340.22-440000-625054.35010.163340054.21010.0-0.5600.0000-0.5600.0000-0.5700.14000-61750 1645.5182-4716.66 23.55371911946.1000 1645.51822 1645.5182-4716.66 5661.970623784.1001645.5182-4716.66-617500-4716.65760-4716.66-1460.7554.3500076.44628154.32010.0-0.5500.0000-0.80030.18969.811321.8500.03018.33333 2723.356-4221.340 1645.5182-4692.22 76.44628114156.92723.356 2723.356-4221.34 1645.51822 1645.5182-4692.22 5661.970622548.82723.356-4221.341645.5182-4692.22-4221.34 2723.356-4221.34-4692.21890-4692.22-1460.7554.35081.66667023.55371954.9805.02.45081.66718.33333-0.5500.0000-0.570-0.63081.66667 2723.356-3665.90 2019.9206-3871.56 22.2656258277.32723.356 2723.356-3665.9 2019.92059 2019.9206-3871.56 3424.948620139.72723.356-3665.92019.9206-3871.56-3665.90-3665.9-3871.55740-3871.56-1996.56318.33333077.7343752.0-0.5500.0000-0.55021.65478.346461.99018.33333 10004.17-504.167 18.3333333 15363.866-36.39025368.010004.1700 15363.8656 15363.866-36.39#VALUE!540.60000-504.16700-36.38998300081.6666781.666666700.02.4500.00002.4500.00001.830#VALUE!0#VALUE!00.0#VALUE!00#VALUE!00#VALUE!0.0000000000000.00.00000.0000#VALUE!0#VALUE!00.0#VALUE!00#VALUE!00#VALUE!0.0000000000000.00.00000.0000#VALUE!0#VALUE!00.0#VALUE!00#VALUE!00#VALUE!0.0000000000000.00.00000.0000#VALUE!0#VALUE!00.0#VALUE!00#VALUE!00#VALUE!0.0000000000000.00.00000.0000#VALUE!0#VALUE!00.0#VALUE!00#VALUE!00#VALUE!0.0000000000000000000几几何何图图形形计计算算及及方方量量计计算算区区0.0000-0.68074.72525.274730.2300.00000.0900.00000.7200.00000.7300.0000-62500 46.42476-4955.58 47.91667 266.6667-1066.6718.75 1188.5-413.099 98.63014 4148.708-0.010380 2870.08100 46.42476 46.42476-4955.58 266.666700 1188.47400 4148.708 4148.708-0.01038 2870.081 2870.0810046.42476-4955.580000002870.08100-4955.5800-1066.6700-413.09900-0.0103800-231.0760052.0833381.251.36986300.0000-1.1800.0000-0.2500.0000-0.3900.0000-0.0104.54595.454550.21038.8895661.9706-1460.75 43.22344 1263.016-4231.0500-320000-20000 1002.273-2.272730 408.333300 1263.016 1263.016-4231.05000000 1002.27300 408.33330001263.016-4231.05000000000-4231.05 1263.016-4231.05-320000-200000-2.2727300-1008.33056.7765600000.00001.55086.11113.88889-0.2500.0000-0.3900.0000-0.0104.54595.454550.21038.8895661.9706-1460.75 56.77656 1263.016-4231.0500-320000-20000 1002.273-2.272730 408.333300 1263.016 1263.016-4231.05000000 1002.27300 408.33330001263.016-4231.05000000000-4231.050-4231.05-320000-200000-2.2727300-1008.33043.2234400000.0000-1.1800.0000-0.2500.0000-0.3900.0000-0.0104.54595.454550.21038.8893424.9486-1996.56 69.00585109.101-5230.7100-53750#VALUE!#VALUE!#VALUE!#VALUE!00109.101109.101-5230.71000#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!00109.101-5230.7100#VALUE!#VALUE!#VALUE!#VALUE!00-5230.71109.1010-537500#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!30.99415000.00000.53039.85060.15038-0.8000.0000-0.710#VALUE!0.00000.000#VALUE!0#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!0#VALUE!00#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!00#VALUE!000#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!00000#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!000000.00000.00000.00000.00000.00000.000#VALUE!0#VALUE!0#VALUE!0#0#VALUE!0#VALUE!00#VALUE!00#VALUE!00#VALUE!00#VALUE!00#VALUE!00000000000000000000000000000.00000.00000.00000.00000.00000.000#VALUE!0#VALUE!0#VALUE!0#0#VALUE!0#VALUE!00#VALUE!00#VALUE!00#VALUE!00#VALUE!00#VALUE!00000000000000000000000000000.00000.00000.00000.00000.00000.000#VALUE!0#VALUE!0#VALUE!0#0#VALUE!0#VALUE!00#VALUE!00#VALUE!00#VALUE!00#VALUE!00#VALUE!00000000000000000000000000000.00000.00000.00000.00000.00000.000#VALUE!0#VALUE!0#VALUE!0#0#VALUE!0#VALUE!00#VALUE!00#VALUE!00#VALUE!00#VALUE!00#VALUE!00000000000000000000000000000.00000.00000.00000.00000.00000.000#VALUE!0#VALUE!0#VALUE!0#0#VALUE!0#VALUE!00#VALUE!00#VALUE!00#VALUE!00#VALUE!00#VALUE!00000000000000000000000000000000000000000000计计算算图图形形选选择择公公式式编编辑辑00.1500.00000.2000.00000.920000000000-231.07631.25 1508.125-226.875037000022221010-231.076 1508.125 1508.125-226.875370000000000-226.8750000068.750061.11111-0.33060.000400.2200.00000.140000000040-1008.330440-990015250010242444044000152500000000-9900000000061.11111-0.33060.000400.2200.00000.030000000020-1008.330440-9900 1012.469-742.861 4.5454552525232322044000 1012.469 1012.469-742.8610001012.46900-99000-742.861000095.4545561.11111-0.33060.000400.22025.88274.11765-0.630000000040#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!2222242444#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!00.00000.0000400077001#0#VALUE!0#VALUE!044770#0#VALUE!00#VALUE!0000000000000000.00000.00001#1#1#0#VALUE!0#VALUE!00#0#0#0#VALUE!00#VALUE!0000000000000000.00000.00001#1#1#0#VALUE!0#VALUE!00#0#0#0#VALUE!00#VALUE!0000000000000000.00000.00001#1#1#0#VALUE!0#VALUE!00#0#0#0#VALUE!00#VALUE!0000000000000000.00000.00001#1#1#0#VALUE!0#VALUE!00#0#0#0#VALUE!00#VALUE!0000000000000000.00000.00001#1#1#0#VALUE!0#VALUE!00#0#0#0#VALUE!00#VALUE!0000000000000000000000001515150200020006600131313010100010151522226613131010010000000000000030005000300010252510103355330100000000000000300050003000131322221010335533131300171717170000#171710#4#1#1#1#1#0#0#0#0#1#1#1#1#1#1#1#0#0#0#0#0#0#0#1#1#1#1#1#1#1#计计算算图图形形选选择择公公式式编编辑辑0#0#0#0#0#0#0#1#1#1#1#1#1#1#0#0#0#0#0#0#0#1#1#1#1#1#1#1#0#0#0#0#0#0#0#1#1#1#1#1#1#1#0#0#0#0#0#0#0#0000130#