利用excel内部函数编制工程量计算表格试卷.doc

excel计算表中经常要对计算式进行标注，便以了解计算数据的来源、计算关系等。 GET.FORMULA函数 GET.FORMULA函数用来显示公式表达式。有时候单元格输入的公式很复杂，或者不同单元格设置了不同公式，可以用它迅速显示公式。 例： 在A1中输入 =2+9*8+54/9 确定后显示的是计算结果80 要想显示成=2+9*8+54/9，就用上GET.FORMULA了。 单击B1，插入-名称-定义（或者按快捷键Ctrl+F3），弹出来对话框，在“在当前工作簿中的名称”一栏中输入一个名称，可以是数字、字母或者汉字，比如我们输入q；在最下边的“引用位置”栏中输入 =GET.FORMULA（A1） 确定后，B1单元格显示是空白。这时，在B1中直接输入=q 确定，B1即显示成=2+9*8+54/9 EVALUATE函数 EVALUATE函数用来计算文本形式公式的数值。 例： A1中以文本形式输入 2+9*8+54/9 它就显示成2+9*8+54/9，并不会显示成结果80。因为在Excel中不输入等号，不被识别为函数，而识别为文本。 用EVALUATE函数能计算其结果。 单击B1，插入-名称-定义，在对话框的名称栏输入任意名称，如w。 在引用位置框中输入 =EVALUATE(A1) 确定后，B1无显示。这时在B1输入=w 确定，显示80。 如A1单元格 =20[長]*35[高]+5.5[長]*23[高]-0.9*1.5[扣門洞]，让人一目了然地知道计算依据，并且不容易犯漏项等错误，那么怎么得到去掉这些文字备注之后的计算结果呢？同样，在B1插入〉名称〉定义名称： X=Evaluate(SUBSTITUTE(SUBSTITUTE(A1,[,*ISTEXT([),],])))，然后在B1输入=X就可以了。 上述宏表函数支持填充复制，在办公中有较广泛的应用。 （一）列式方式计算表的制作 1、工作表制作（1）、选择一个工作表（如shellt1），命名为“计算表”，设计好表样（如图四）：图一中项目名称/部位设计为2列，C列用来输入项目名称，D列用来输入计算部位（如：1轴，A-B轴）。 图四 （2）、选择另一个工作表（如：shellt2），命名为“单位”，在A列的第1行开始逐行输入m3、m2、m等计量单位。 2、下拉列表设置：在“计算表”工作表中设置计量单位下拉菜单：在菜单栏中，点击“插入”→“名称”→“定义”，出现弹出窗口如（图五）： 图五 在当前工作薄中的的名称下面输入“dw”，按“添加”，在“引用位置”输入“=单位库!$A:$A”，按“确定”关闭窗口。选择“计算表”工作表 E3单元格，在菜单栏中，点击“数据”→“有效性”，出现弹出窗口如（图六）： 图六 在“设置”→“有效性条件”中，“允许(A)”下面选择“序列”，“来源(S)”下面输入“＝dw”，在“忽略空值(B)”和“提供下拉箭头(I)”前面打勾，按“确定”关闭窗口。我们再选择“计算稿”工作表E3单元格，这时，该单元格的右边会出现一个下拉箭头(如：图七)，点击这个箭头，就可以选择我们需要的单位了。如果需要添加单位，在“单位”工作表的A列下面添加，“计算表”工作表E3单元格也会随之自动添加的。下拉列表中如果内容太多，查找过于繁琐。如钢结构计算中，单一条件的下拉列表会显示几百种规格，实在不太方便。这时我们可以将其设置为不同的条件的分类显示（图八）。 a、E 列的数据有效性设置为——工字钢,角钢,H钢等。“,”号隔开（英文状态下）。 b、H 列的单元引用定义为：工字钢、角钢、H钢定义为 gzg、jg、hg等 ，工字钢引用位置设置为=钢材单位重量表!$A$23:$A$101 （其中：$A$23:$A$101 应根据工字钢所在的单元区域，其他同理），数据有效性“序列”中设置为=IF($E4=圆钢,yg,IF($E4=角钢,jg,IF($E4=I,gzg,IF($E4=钢板,gb,IF($E4=钢管,gg,IF($E4=C,cg,IF($E4=H钢,hg,)))))))。这时只要我们在E列中选醛I”，对应的H列即仅显示为工字钢列表。这个方法在工程量计算进行钢材分类显示和同时调用定额和清单很有帮助。 减少了下拉列表内容太多，查找不便的烦恼。 图七 图八 3、让单元格中的公式即可计算又同时显示结果在单元格中输入公式进行计算，同时又能在计算公式中进行文字说明，并得出正确的结果过，这一直是大家所期望的。首先，在菜单栏中，点击“插入”→“名称”→“定义”，在当前工作薄中的的名称下面输入“sl”，按“添加”，在“引用位置”输入“=EVALUATE(SUBSTITUTE(SUBSTITUTE(计算表!$F3,[,*ISTEXT([),],])))”，按“确定”关闭窗口。公式中“计算表!$F