河北路桥工程沥青混凝土配合比设计计算书excel表格计算法

用Excel计算沥青混凝土配合比的方法实例随着计算机以及计算机技术的日益普及，在生活和工作当中的发挥的作用越来越重要，因为在各个领域的运用功能不一，所以用到的应用程序也不一致，不过在90％以上的windows系统中，Office办公套件是必备的软件，其中的Excel是电子表格市场的领导者。这不仅仅是因为微软公司在市场上的巨大影响，而且还因为Excel的确是现在可以用到的最好的电子表格程序。Excel最具特色之处就是它的公式功能，在公式方面，Excel的确有它的独到之处，可以很保守的说我们常用到的Excel的函数和公式功能仅仅占所有的10％还不到，就笔者在日常试验工作中的函数应用对Excel作一个简单的介绍。Excel介绍Excel基础知识微软的Excel一直被认为是“为windows编写的最好的应用程序”，多少年来，他经历了多次修改，功能越来越强大。在没有电子表格的时代，人们只能依靠十分巨大的计算机主机或者使用计算器，花费数小时来做的事情现在使用Excel需要几分钟就可以完成工作。Execl常用名词工作薄：Excel最常用的对象之一就是工作薄，在Excel中做的任何工作都放在工作薄中，它使用.xls作为扩展名保存。工作表：最常用的表就是工作表，它总数有256列65536行，行和列的数量是固定的，可以隐藏不需要的行和列，但总数的行和列是不能增加的。函数：函数就是指在公式中可以使用的一种内部工具，它有如下的功能：（1）、可以简化公式（2）、保证公式运行，否则无法进行计算（3）、提高编辑任务的速度（4）、允许“有条件的”运行公式，使之具备基本判断能力公式：当一个空单元格中键入一个等号的时候，Excel就认为你在输入一个公式，输入单元格中的公式共有5中元素：运算符：包括一些符号，例如“+”“-”“×”“÷”等单元格应用：它们包括命名单元格和范围，甚至可以是其他工作薄工作表的单元格值或字符串工作表函数和它们的参数括号：它们控制公式中各个表达式的处理次序。Excel计算沥青混凝土配合比方法实例2.1手工与电脑联合计算法传统调整级配都是用手工方法，速度慢，计算量大且容易出错，当级配料有三种以上便很困难。本例以试验工作中的沥青混凝土生产配比的计算为实例，讲解常用的Excel函数、公式的具体应用与操作。用Excel做好后，计算速度快(每次调级配只需几秒钟便完成，一劳永逸)、结果准确、方法简单，只需输入每种矿料占总量的百分比就可以了。下面以一个四种热仓矿料组成的例子来加以说明。2.1.1、建立集料筛分记录表启动Excel，默认建有3个工作表，右键点击sheet1，从菜单中选择“插入…”在“插入”的对话框中选中工作表，点击“确定”即可建立一个空白的工作表，重复以上操作4次，建立4个工作表，现在总共为7个空白工作表分别为“Sheet1”“Sheet2”“Sheet3”“Sheet5”“Sheet5”“Sheet6”“Sheet7”，然后分别用右键点击将这几个工作表从菜单中选择“重新命”分别命名为“沥青砼级配计算”“沥青砼级配范围”“18-30集料”“12-18集料”“5-12集料”“<5集料”“矿粉”，在“18-30集料”中建立一个如下图的“集料筛分表格”粗集料筛分试验表格2-1-12.1.2、计算各个集料筛分数据在试验表格中分别输入相对应筛孔的分计筛余质量，然后在G15的表格内输入“=SUM(G16:T16)”计算各个分计筛余筛孔上集料的总质量，在H17的表格内输入“=ROUND(H16/$G15*100,1)”计算分计筛余百分率并且保留1位小数点；在H18的单元格内输入“=H17+F18”计算累计筛余百分率；在H19的单元格内输入“=100-H18”计算通过百分率。然后点击H17单元格按住鼠标不放向下拖动至H19单元格，放开鼠标把光标移向H19的单元格的右下角，当光标成黑十(“十”)字时，按住鼠标左键向右拖动，直到L19即最后一个筛孔时放开，这时从“H16:L19”单元格都已经自动计算出了相应的“分计筛余百分率”“累计筛余百分率”“通过百分率”，做平行试验，按照上述操作原理计算。分别将“第一次”和“第二次”的筛分计算完成。在H25的单元格内输入“=ROUND((H18+H23)/2,1)”计算两次平行试验的平均累计筛余百分率，并且保留1位小数；在H26的单元格内输入“=ROUND((H19+H24)/2,1)”计算两次平行试验质量通过百分率。将文件保存，并将工作表“18-30集料”中的表格依次复制并粘贴到空的“12-18集料”、“5-12集料”和“<5集料”“矿粉”工作表中，并将四个工作表中C9单元格的内容分别改为“12-18”、“5-12”、“<5”“矿粉”。在“12-18”、“5-12”、“<5”、“矿粉”工作表中分别输入各个集料的分计筛余，自动计算出各项指标，保存文件。建立级配计算表，手动输入各集料比例在“沥青砼级配计算”表格中建立如下表格混合料级配计算表格2-1-2在D6单元格内输入“='18-30集料'!H26”‘在D7单元格内输入“='12-18集料'!H26”在D8单元格内输入“='5-12集料'!H26”；在D9单元格内输入“='<5集料'!H26”;在D10单元格内输入“='矿粉'!H26”。点击D6单元格，按住左键不放拖至D10单元格，放开鼠标左键，将光标移至D10单元格的右下角，当光标成黑十(“十”)字时，按住鼠标左键向右拖动，直到O10单元格即最后一个有效筛孔时放开。这时“D5:O10”范围内的单元格均已经全部从其他工作表引入数据。点击D13单元格输入“=ROUND(D6*$C$13,1)”计算“18-30集料”在混合料相应筛孔中所占的相应百分率，并且保留1为小数。依次点击D14、D15、D16、D17，分别输入“=ROUND(D7*$C$14,1)”“=ROUND(D8*$C$15,1)”“=ROUND(D9*$C$16,1)”“=ROUND(D10*$C$17,1)”，分别计算各个集料在混合料相应筛孔中所占的相应百分率，并且保留1为小数。依照上述的方法将“D13:D17”范围选中后向后拖至O17单元格即最后一个有效筛孔时放开，这时“D13:O17”范围内的单元格均已经全部计算完成各个筛孔在相应比例下占混合料的百分率。在D19单元格内输入“=SUM(D13:D18)”计算相应筛孔下全部集料在混合料中的百分比，依照上述拖曳方法，将光标拖至O19，这样计算就全部完成了，保存文件。2.1.4制作合成级配曲线上述表格计算虽然完成，但是单单从数字上来说，级配是否合理还不是很直观，所以就用到了Excel的另一个功能，“图表”功能。在引入图表前我们先准备制作图表所用到的各种数据，其中包括“上限”、“下限”、“中值”、“通过率”、“筛孔”、“X轴距离”。入下图所示计算结果示表2-1-3“筛孔”为我们实际筛分混合料时用到的各种筛孔；“X轴距离”为各个筛孔曲线标准图至起点的距离；“通过率”为我们通过“沥青砼级配计算”表所计算出来的筛分；“上限”、“下限”为规范所规定的范围；“中值”为“上限”、“下限”的平均值。准备好了制作图表的数据后，点按菜单栏中“插入（I）”的子菜单“图表(H)”项，将显示“图表向导-4-步骤之1-图表类型”对话框，再在“标准类型”的“图表类型(C)”框中选择“xy散点图”，在“子图表类型(T)”框中选择从上数第二个，点按“下一步(N)”按扭，进入“图表向导-4-步骤之2-图数据源表”对话框，点按“系列”页面，将“系列(S)”对话框中的系列全部删除，再点按“添加(A)”按扭，添加“系列1”至，“系列13”，点击“系列1”在X值框中填入“={0.312,0.312}”，在Y值中填入“={0,100}”，其中的“={0.312,0.312}”为“X轴距离”。在Y值不变的情况下把“系列2”至“系列13”的X值全部填入相对应筛孔的“X轴距离”。添加系列并依次改名“上限”“下限”“中值”“实际值”，在“上限”的X值框中填入“=沥青砼配范围!$B$3:$B$16”即0-31.5筛孔的“X轴距离”，Y值框中填入“=沥青砼配范围!$E$3:$E$16”即级配范围的上限值，在保持X值框中数值一致的情况下，分别在“下限”“中值”“实际值”中Y值填入相应范围的数值。点按“下一步(N)”按扭，进入“图表向导-4-步骤之3-图表选项”对话框，选择“标题”栏，在“图表标题(T)”编辑栏中输入“沥青级配曲线”，在“分类(x)轴(C)”编辑栏中输入“筛孔尺寸(mm)”，在“数值(Y)轴(V)”编辑栏中输入“通过率%”，点按“下一步(N)”按扭，进入“图表向导-4-步骤之4-图表位置”对话框，选择“作为新工作表插入(O)”，并在其编辑栏中填写“级配图示”，点按“完成(F)”按扭；这时就新建“级配图示”工作表，直观显示级配的“上限”“下限”“中值”“实际值”，在“上限”。如下图筛分级配曲线图示2-1-4这样就完成了沥青混凝土生产配比计算调整的全部程序编写工作，在实际的应运中可以在“沥青砼级配计算”的工作表中任意调整各个集料的比例数，并且可以直观从“级配图示”工作表中显示曲线变化情况，判定比例数是否合适，大大简化了工作强度。2.2、电脑全自动计算法2.2.1输入已知数据并输入合成级配计算式按照下图的形式建立数据工作表数据示表2-2-1在工作表的G-K列中输入各种集料的级配，在G17、H17、I17、J17、K17中存储各种集料的配比用量。在L列中输入矿料的合成级配，在单元格L4~L15中分别计算矿料在26.5~0.075mm筛孔上的通过百分率。在工作表中输入以下的公式：在L4单元格中输入“=$G$17*G4+$H$17*H4+$I$17*I4+$J$17*J4+$K$17*K4”，然后点击单元格，鼠标指向单元格的右下角，当鼠标为一小是按住鼠标左键向下拉动一直到L15单元格。2.2.2建立目标控制条件为了获得合理的级配，要求集料的合成级配在设计范围之内，并且尽量的接近中值。因此，可以要求以合成级配与设计级配中值之差的平方和最小作为目标控制条件，即要求Q最小Q=∑[PM(i)-P设计(i)]2式中：PM(i)---------集料合成级配的通过百分率，%P设计(i)-------设计级配范围中值，%公式在表格中的输入形式为：在单元格M4中输入“=(F4-L4)^2”，然后拉动鼠标一直到M15。最后在单元格M17中输入公式“=SUM(M3:M16)”表示对M13到M16的单元格求和。2.2.3设置规划求解参数值上述步骤完成了规划求解前的准备，根据设计，集料配比比例应该在保证合成级配不超出设计级配范围的前提下，使Q最小，所以问题的求解可以描述为，寻求合适的G17、H17、I17、J17、K17的值，在保证所有筛孔的合成级配在设计的范围之内的条件下即Di≥Li≥Ei（约束条件），使得M17的的值最小（控制值）。在Excel“工具”中点击“规划求解”，如果工具栏中没有选项就在在“工具”菜单上，单击“加载宏”，然后“确定”，就可以使用了。如图规划求解对话框2-2-2a.设置目标单元格在规划求解单元格中，把目标单元格设置为M17，选中最小值选项。其意思就是控制目标单元格的最小值为规划求解的最终目的，即要求公式Q=∑[PM(i)-P设计(i)]2的计算值为最小。b.设置可变单元格选中各种集料用量的单元格G17、H17、I17、J17、K17作为可变单元格。点击上图中的“添加”来增加约束条件，但单击“添加”后，弹出“添加约束”对话框，如下图添加约束对话框2-2-3在该对话框中一次输入各个约束条件，所输入的约束条件应该满足公式P设计(i)上限≥PM(i)≥P设计(i)下限的要求，即合成级配在设计级配的范围内。除了对级配范围做出约束之外还可以设置其他的约束条件，比如在本例中事前确定了矿粉的用量为5%，则可以增加约束条件“K17=0.05”在添加为所以的约束条件后，单击“添加约束”对话框的“取消”，将回到“规划求解参数”对话框，下图为完成所以约束条件后的对话框规划求解参数对话框2-2-42.2.4规划求解计算各种集料用量单击“规划求解参数”对话框中的“求解”，规划求解开始运算，运算结束后的对话框如下规划求解结果对话框2-2-5如果有解，选中“保存规划求解结果”，点击“确定”保存计算结果，计算完成，在可变单元格中保存所求得的集料比例，如果提示没有找到解，则意味着这种原材料不能配出符合设计要求的级配，应当改换原材料。2.2.5制作合成级配曲线按照上例的曲线制作方法即可。2.3实例中应用函数介绍2.3.1SUM函数SUM是一个求和的函数，在Excel中最常用，他可以求：多个不连续的单元格的和：例如SUM(I17,K18,K16)就是指I17、K18、K16这几个单元格数值的和；单列多行单元格的和：例如SUM(D13:D18)就是指从D列13行到D列18行各个数值的和；多列单行单元格的和：例如SUM（I16:L16）就是指从I列16行到L列16行各个数值的和；多行多列单元格的和：例如SUM（I17:L19）就是指从I列17行到L列19行范围内的各个数值的和；2.3.2ROUND函数ROUND是把一个数值舍入成制定的位数。