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

1、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单元

2、格显示是空白。这时，在B1中直接输入=q确定，B1即显示成=2+9*8+54/9EVALUATE函数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扣門洞，让人一目了然地知道计算依

3、据，并且不容易犯漏项等错误，那么怎么得到去掉这些文字备注之后的计算结果呢？同样，在B1插入名称定义名称： X=Evaluate(SUBSTITUTE(SUBSTITUTE(A1,*ISTEXT(),)，然后在B1输入=X就可以了。上述宏表函数支持填充复制，在办公中有较广泛的应用。 （一）列式方式计算表的制作 1、工作表制作（1）、选择一个工作表（如shellt1），命名为“计算表”，设计好表样（如图四）：图一中项目名称/部位设计为2列，C列用来输入项目名称，D列用来输入计算部位（如：1轴，A-B轴）。 图四 （2）、选择另一个工作表（如：shellt2），命名为“单位”，在A列的第1行开始逐行

4、输入m3、m2、m等计量单位。 2、下拉列表设置：在“计算表”工作表中设置计量单位下拉菜单：在菜单栏中，点击“插入”“名称”“定义”，出现弹出窗口如（图五）： 图五 在当前工作薄中的的名称下面输入“dw”，按“添加”，在“引用位置”输入“=单位库!$A:$A”，按“确定”关闭窗口。选择“计算表”工作表 E3单元格，在菜单栏中，点击“数据”“有效性”，出现弹出窗口如（图六）： 图六 在“设置”“有效性条件”中，“允许(A)”下面选择“序列”，“来源(S)”下面输入“dw”，在“忽略空值(B)”和“提供下拉箭头(I)”前面打勾，按“确定”关闭窗口。我们再选择“计算稿”工作表E3单元格，这时，该单元

5、格的右边会出现一个下拉箭头(如：图七)，点击这个箭头，就可以选择我们需要的单位了。如果需要添加单位，在“单位”工作表的A列下面添加，“计算表”工作表E3单元格也会随之自动添加的。下拉列表中如果内容太多，查找过于繁琐。如钢结构计算中，单一条件的下拉列表会显示几百种规格，实在不太方便。这时我们可以将其设置为不同的条件的分类显示（图八）。 a、E 列的数据有效性设置为工字钢,角钢,H钢等。“,”号隔开（英文状态下）。 b、H 列的单元引用定义为：工字钢、角钢、H钢定义为 gzg、jg、hg等 ，工字钢引用位置设置为=钢材单位重量表!$A$23:$A$101 （其中：$A$23:$A$101 应根据工

6、字钢所在的单元区域，其他同理），数据有效性“序列”中设置为=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、让单元格中的公式即可计算又同时显示结果在单元格中输入公式进行计算，同时又能在计算公式中进行文字说明，并得出正确的结果过，这一直是大家所期望的。首先，在菜单栏中，点击“插

7、入”“名称”“定义”，在当前工作薄中的的名称下面输入“sl”，按“添加”，在“引用位置”输入“=EVALUATE(SUBSTITUTE(SUBSTITUTE(计算表!$F3,*ISTEXT(),)”，按“确定”关闭窗口。公式中“计算表!$F3”表示“计算表”工作表需要列式计算的“F3”单元格。在G3单元格中输入公式“=IF(F3=,ROUND(SL,2)”，公式中的“SL”就是我们刚刚定义的名称，“ROUND(SL,2)”表示为“SL”计算结果保留两位小数。这样在F3单元格中输入计算公式时，G3单元格便会自动显示出运算结果来。如：在F3输入计算公式“0.24墙厚*（3.5墙长*(2.9-0.4

8、扣梁高)-1.2*1.8扣窗洞C1022）”，回车，G3就自动出来计算结果“1.58”。计算公式中的文字说明，如“墙厚、墙长”等，必须用“”括起，而且只能紧跟在数字后面，否则将不能计算。 4、格式条件的运用在“计算表”中，如果希望B列中输入数据时，自动添加底色或改变字体颜色。点击菜单栏的“格式”“条件格式”，出现设置“条件格式”窗口如（图九）： 图九 在“条件1（1）”下面选择“公式”，在条件栏中输入“=B1”，按右边的“格式”出现设置“单元格格式”窗口如（图十）： 图十 选择“图案”选项，选一个自己喜欢的颜色，按“确定”关闭“单元格格式”窗口，再按“确定”关闭“条件格式”窗口。这样就可以用颜

9、色来区分项目名称、汇总行与计算行了。用条件格式自动添加表线，方法和添加颜色相同，这里不再赘述了。完成以上内容，一个列式方式的计算表就基本形成了。为了可以在以下各行都可以进行计算，并得出结果。在“计算表”工作表中“B3：H3”单元格，用填充柄下拉复制到需要计算的所在行即可。（二）竖向计算形式计算表的制作列式方式计算表的缺点是输入数据多，列式过长时复核较为困难。竖向计算方式与传统的列式最大的区别是：数据的输入和计算是竖向的，只有“初步计算式”栏中有少量的列式，缩短了数据的输入时间。本文作者根据审计局的基本要求，特制作了竖向计算方式的excel计算表（如：图十一）。 图十一 竖向excel计算表的特

10、点是当“初步计算式”和“倍数”列输入数据时，“数量”列自动识别以下三个条件计算关系存在时，计算结果为其中的那一种。（1）当倍数与三个数据相乘时；（2）当倍数与两个数据相乘时；（3）当倍数于一个数据相乘时如：当输入在F12单元格输入97，在G12单元格输入1.5，G13单元格输入0.62时，为倍数与两个数相乘，H12单元格得出数值为90.21。而当在F16单元格输入97，在G16单元格输入1.87，在G17单元格输入6，G18单元格输入2.98时，为倍数与三个数相乘，H16单元格得出的数值为3243.25，以此类推。在excel中如何实现上述的计算过程呢? 我们用excel的逻辑函数IF、AND

11、和OR来实现,在H3单元格输入公式“=IF(OR(G3=,F3=),IF(AND(G5,F5=,F4=),ROUND(G3*G4*G5*F3,2),IF(AND(G4,F4=),ROUND(G3*G4*F3,2),IF(AND(G4,F4,G3,F3),ROUND(G3*F3,2), ) ”，再用填充柄下拉进行复制。表格中“尺寸”数据是根据“初步计算式”的简单列式或输入的数据计算所得。具体的设置方式和上面讲的列式计算表相同，这里就不再赘述。（三）汇总设置无论是列式表格还是竖向表格计算，最终的结果都需要数量合计、汇总，这里以列式表格为例，说一下汇总设置的方法。在列式“计算表”中H列为汇总列，即G

12、3单元格“多孔砖”下有多条列式时，H3可以自动合计。要实现这一功能，我们在I列增加一项“代码”。 在I3单元格输入公式“=IF(F3=,COUNTA($C$3:$C3)”，“代码”的功能是当G3单元格“多孔砖”下有多条列式时，I列数值相同。然后在H3单元格输入公式“=IF(C3,SUMIF(I:I,$I3,G:G),)”对同一“代码”的“数量”进行汇总合计。（如：图十二） 图十二公式设置完成后，当在C3输入“多孔砖”，F3、F4、F5输入计算式时，H3即可自动汇总出结果7.89。通过以上的方法进行对excel的设置，一个简单的工程量计算表格就直做好了。在表格中只要我们输入项目的名称、单位、计算

13、公式，就可得到想要的工程量结果和汇总结果。如果想对计算表格进行多功能设置，例如想进行定额的鼠标填充，计算表和定额库的切换等等，需要vba代码编程来支持。总之，通过人们的不断实践和努力，excel已经起到了提高工程量计算的效率作用，并作为软件算量的补充。 函数名称：COUNTIF 主要功能：统计某个单元格区域中符合指定条件的单元格数目。 使用格式：COUNTIF(Range,Criteria) 参数说明：Range代表要统计的单元格区域；Criteria表示指定的条件表达式。 应用举例：在C17单元格中输入公式：=COUNTIF(B1:B13,=80)，确认后，即可统计出B1至B13单元格区域中

14、，数值大于等于80的单元格数目。 特别提醒：允许引用的单元格区域中有空白单元格出现。一、先说不可不用。 if最善于解决非此即彼、非男即女、非阴即阳、非前即后、非有即无的问题。如果问题的答案是二选其一，则除了if，没有更好的办法。比如学龄，以7岁为条件，if(年龄=7,已到学龄,未到学龄)，做这样的判断，任何函数方法都不会更简明于此了。 如果我们的问题都是这么简单就好了。 有一个著名的数组公式，其内核公式为：if(match(列起点:列终点,列起点:列终点,0)=row(列起点:列终点),row(列起点:列终点),)，作用是在一列中查找重复值各单项的所在行号，这个if就是不可或缺，不可不用的，因

15、为到目前为止还没有其他更简明的办法来达到用公式筛选重复值的目的。但说穿了，if在这里所解决的，仍然还是一个非此即彼的问题。 再看一例：设A列为姓名，B列为数值，求姓名甲的数值合计。=SUM(IF(A1:A15=甲,B1:B15)，其实也是一类问题，是=SUM(IF(A1:A15=甲,B1:B15,0)的一种简写，叫做非甲即0。而在数组公式中，*号可以用来替代AND，+号则可以替代OR，因此也可以进一步简写作=SUM(A1:A15=F1)*B1:B15)，而且条件越多，越可以体现这种写法的优点，比如再加上一列月份，求甲在3月份的数值合计，你可以省下两个if，多用一个*号就可以了（自己试试？）二、

16、再来说不可多用。 为什么不可多用？大致是因为：一、会增加公式写入的强度；二、降低公式的可读性；三、降低运算速率；四、不利于脑力的发挥和开掘，使人懒惰。 例一：A1为一个数值，其范围为1-7，B1设置公式，按A1数值变化分别等于A-G。 先来看看纯粹使用if的解法：=IF(A1=1,a,IF(A1=2,b,IF(A1=3,c,IF(A1=4,d,IF(A1=5,e,IF(A1=6,f,IF(A1=7,g,) 是不是很麻烦？何止是麻烦，假如再增加两个条件，A1的数值范围为1-26，B1相应取值为A-Z，你又当如何？ if的嵌套最大可以为7层，上面的公式已经用到了极限。虽然说可以用一些旁门左道来“突

17、破”这个限制，但也只是一种堆沙式的游戏，如上例，可以采用以下方式：=IF(A1=1,a,IF(A1=2,b,IF(A1=3,c,IF(A1=4,d,IF(A1=5,e,IF(A1=6,f,IF(A1=7,g,)&IF(A1=8,h,IF(A1=9,I,) 这样的用法，真是叫人兴味荡然，昏昏欲睡，EXCEL何必还要学下去，还不如去跟儿子摆积木更好玩呢！ 所以说，if最好不要多用。不是说不能用，而是说用多了会叫人伤心。 其实EXCEL里准备了许多办法来替代上面的愚蠢的做法。 比如CHOOSE函数。=CHOOSE(A1,a,b,c,d,e,f,g,h,i)，这是不是方便多了？CHOOSE的参数清单可

18、以有29项之多，一般足够你使用了。如果还不够，那么请看下面： =LOOKUP(A1,1,2,3,4,5,6,7,8,9;a,b,c,d,e,f,g,h,i)，你可以尽情地输入参数，只要公式内容长度允许（规定公式内容长度为1024个字符）。 如果真的如例中所举，只是生成A-Z等字母的话，则只需=CHAR(A1+64)就可以了。当然，实际使用中这样的巧合实在是太少了，但作为一种方法还是有提及的必要。 一个if只能处理一个有无或是否的问题，即使这个问题可能是由诸多小的方面组合而成的。我们可以利用这一点，来达到替代if使用的目的。 例二：公司结算日期为每月24日，帐目的月份一栏，如果超过24日，就要记

19、为下月。 如果按照普通思路，公式应该是这样的：=IF(DAY(A1)24,IF(MONTH(A1)=12,1,MONTH(A1)+1),MONTH(A1) 要用到两个if判断，外层的是判断日期是否大于24，内层的是判断月份是否在12月，因为12月的下月是1月而非13月。现在对比一下下面的公式： =MONTH(DATE(YEAR(A1),MONTH(A1)+1,0)+(DAY(A1)24) 后者用了A1日期当月最后一天的序列值，最重要的是后面加了一个由判断是否大于24而生成的逻辑值，相当于=if(day(a1)24,1,0)。逻辑值在公式设置中是一个很重要的概念，是对问题本身的逻辑关系的判断，其

20、中TRUE=1，FALSE=0，生成的同样是有无或是否的结果，用得恰当，会使你的公式格外生动有趣。类似的还有根据年龄计算性别、年龄的公式，也是使用逻辑值做判断，具体见我以前的相关帖子，此处不在赘述。 是不是一定要少用if，以至于该用的也想办法不用？我曾经说，最少用到if的公式往往是最好的公式。之所以用“往往”来做限制，就是因为我没有根据来做一定如此的定论。凡事都要实事求是，具体情况具体分析。 例三：A1为性别，B1为年龄，C1标注是否退休。条件是男60岁，女55岁。 对这个问题，=IF(OR(AND(A1=男,B1=60),AND(A1=女,B1=55),退,未退)只用到一个if，但未必就比=

21、IF(B1-IF(A1=男,5)=55,退,未退)更简洁，尽管后者用到两个if判断。当然我还是反对=IF(AND(A1=男,B1=60),退,IF(AND(A1=女,B1=55),退,未退)这种用法的。 单元格公式的备注参考可用多种方式表示，如单元格格本身的备注，在公式旁的单元格格用文字表示等等。 现在为大家介绍一个小技巧，就是用 N 函数在公式内加插批注，N 函数的详细请参考电子表格的帮助。 这个小技巧主要利用 N(其它)，传回 0 的特色。文字属于其它，例子如下： B6 ，C6 及 D6 是三个区域的个别统计数字，H20 则是他们的总和，平常公式是 =SUM(B6:D6) 在公式内加插批注

22、可以这样表达： =SUM(B6:D6) + N(区域 1 ， 区域 2 及 区域 3 的总数) 这样，后边的注释能在编辑栏内看到，而返回的值还是正确的，在某一列的单元里设置下拉菜单，然后能在下拉菜单里选择自已输入的信息。如在 H 列输入：石子，砂子，水泥等，然后在 B 列的每个单元里设置下拉菜单，并能在下拉菜单里选择输入 H 列处的石子，砂子，水泥等。你可以设置一个分类下拉菜单和明细菜单，通过分类选项就少多了。这样行不行？1、H 列的数据有效性设置为石子,砂子,水泥 石子、砂子、水泥要用“,”号隔开（英文状态下）。2、H 列的单元引用定义假设是：石子为 a ，砂子为 b ，水泥为 c ，引用位

23、置设置定义为=工作表名称!$H:$H （其中：$H 应根据石子、沙子、水泥明细表所在的单元区域确定，如果石子为$E$1:$E$4，$E$5:$E$12，$E$23:$E$33），数据有效性“序列”中采用=if($H1=石子,a,if($H1=砂子,b,c) 这个方法对于工程量计算中同时调用定额和清单以及钢材规格的分类显示很有帮助。例如我们定义de=dek!$b:$b”为定额名称区域，qd=qdk!$b:$b为清单名称区域，数据有效性“序列”中设置“=if($H1=q,qd,de) ”,这是只要我们在H列输入“q”，即可下来列表中显示清单，反之为定额。 如果在D单元格中的结果是通过A单元格的不同

24、条件选择，进行B、C单元格的数据计算。这一方法用于计算异型梁、柱、独立基穿条形基础等工程量上非常简洁。 =IF(A1=A,B1,)&IF(A1=B,B1*C1,)&IF(A1=C,B1-C1,)&IF(A1=D,B1/C1,)&IF(A1=E,C1+B1,)&IF(A1=F,C1,)&IF(A1=G,B12,)&IF(A1=H,C12,)一个数值和文字混排的单元格，如何计提数值参与计算？ 数值在文字中间，如“收入562.00元”；最后，如“计提标准0.5”（此处的0.5）；还有出现在首端的，如895人/月。 不管出现在那个位置，出现数值的地方只有一个。 公式作者：gvntw。 =LOOKUP(

25、9E+307,-MID(A1,MIN(FIND(0;1;2;3;4;5;6;7;8;9,A1&1234567890),ROW(INDIRECT(1:&LEN(A1) 解释作者：山菊花。 与看不懂的朋友共同学习一下： 以“收入562.00元”为例 1、FIND()告诉我，0-9在文本中第一次出现的位置。 用Find()在指定的文本中查找0-9，会返回第一次出现的位置，如果没有会返回一个错误值，聪明的gvntw，在文本后面连接上1234567890，让Find()不再有借口返回错误值，请放心，它不会影响后面的结果。在编辑栏中，像下面一样，用鼠标将这一段文本抹黑，然后按F9。 =LOOKUP(9E+

26、307,-MID(A1,MIN(FIND(0;1;2;3;4;5;6;7;8;9,A1&1234567890),ROW(INDIRECT(1:&LEN(A1) 2、对着 Min()，王小丫说：恭喜你，答对了，第一个数字在第3位。 如果你还怀疑，可以这再次抹黑，并按F9。 =LOOKUP(9E+307,-MID(A1,MIN(7;8;5;12;13;3;4;16;17;18),ROW(INDIRECT(1:&LEN(A1) 3、神奇魔术师 Mid() ，一个文本变出许许多多。 让事实说话，继续抹抹黑，按F9。开始位置不变，长度依次增1，形成一个数组，数组的个数等于文本长度(Row()的结果)。后

27、面的会出现相同的情况，没关系，还是不影响结果的。 =LOOKUP(9E+307,-MID(A1,3,ROW(INDIRECT(1:&LEN(A1) 4、如果忽略了-，还是功亏一篑。 看看，它的结果会是什么呢？ =LOOKUP(9E+307,-5;56;562;562.;562.0;562.00;562.00元;562.00元;562.00元) 5、Lookup() 总是那么沉着，他闭着眼睛说，我就要最后面那个数字。 =LOOKUP(9E+307,5;56;562;562;562;562.00;#VALUE!;#VALUE!;#VALUE!) 9E+307 是一个非常大的数，在任何时候都适用，如

28、果你知道你的文本中最大数的范围，比如1000，文本中的数字不会超过1000的，那么，这个数字你用1000，也是行的，即： Lookup(1000,) 一、角度的度分秒与小数点格式互相转换 EXCEL能将角度转换成度分秒格式吗? 如将:120.999722222222转换成1205959. 32.55转换成32330 我知道可以用函数RADIANS将角度转换为弧度，但我们工程测量上用到的角度计算是以度分秒（比如：1261236）来记录并进行计算的，在Excel中怎样用这些数据进行计算？ 1.设定公式“=A1/24”,并将单元格的格式设定为自定义格式:hmmss 2.直接设定公式=TEXT(A1/

29、24,hmmss) 第1种方法，方便于在计算时将度分秒转为度，这时只需乘24即可第2种方法，度分秒转为度时，需要用公式=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,:),:),)*24 (度分秒符号用单位软键盘上的符号) 二、度/分/秒的输入问题在工程计算中经常要用到度、分、秒，通常的做法就是插入特殊字符来输入。但是在EXCEL中显示的结果却不近人意，三者之间的字符间距太大，看起来就象刚学会写字的人写的东西。其实这是由字体引起来的，一般情况下系统默认的字体是宋体，在这种情况下输入度分秒的时候，它们之间的间距就会比较大。下面几种字体的效果就比较好： Arial Uni

30、code Ms , Batang , Dotum , Gungsuh , New Gulim , Gulim 。当然也不只是这几种，大家也可以自己测试一下，找一下其它合适的字体。=SUM(*!A1)*! -? 呵呵，用公式时无意弄出这么个东东，*! 代表什么？输输看？ 如果工作表中有三个工作表Sheet1、Sheet2、Sheet3，你在 Sheet1表中输入 这个公式 ： =SUM(*!A1)，回车，看看公式自动变成了什么？ =SUM(Sheet2:sheet3!A1) 如果你在 Sheet2表中输入 这个公式 ： =SUM(*!A1)，回车，公式自动变成了 =SUM(Sheet1!A1,sheet3!A1) 呵呵，明白了吗？ *!-代表工作簿中