EXCEL在工程中的实际应用课件.ppt

1、主要内容1、EXCEL函数应用基础2、常用函数的应用3、数组函数的应用4、函数在工程计算中的应用5、函数在工程测量中的应用6、特殊函数在工程中的应用7、时间函数的简介8、EXCEL函数应用的建议9、结语 二、EXCEL函数分类 在工程计算统计中常用几种函数 1、条件函数 2、统计函数 3、查询函数 EXCEL函数的定义 刚才我们说过了excel函数的理解，那么在应用中excel函数的定义就是预先定义、执行计算、分析处理数据的特殊公式。通俗的说就是通过参数求得我们需要的结果，而参数就是我们在表格中预先设定的已知条件。因为电子表格中的数据库需要终端的处理，在处理日常工作中的数据采用数据库不但麻烦制

2、作过程更需要很多的VBA知识，所以合理的运用制表技术和函数组合一样可以拥有数据库功能中的快捷方式，在我们实际工作中用到的同望、以及土建和市政工程中用到的广联达也是以officee数据链为基础制作的。一、条件函数IF的应用 IF函数是EXCEL中最常用而且用途比较广泛的一种函数，IF在英文中是如果和假如，在EXCEL中IF函数：假设条件性的函数，即执行真假值的判断，根据逻辑计算的真假值，返回不同的结果。所以IF函数也常称之为逻辑函数。IF函数编写语句：=IF(测试条件,结果1,结果2)，即如果满足“测试条件”则显示“结果1”，如果不满足“测试条件”则显示“结果2”。以上是IF函数的基础应用，但I

3、F函数在EXCEL的使用中有一定局限性，就是一次最多只能判定不超过7次的条件选择，那么如果我有10个甚至更多怎么办？这里就需要IF函数的套用。三、IF函数的套嵌 IF函数的套嵌当超过七层后就需要重复用IF将之前的公式看成数值的形式带入新的IF函数中，这里最为重要的是IF函数的括号输入，IF函数有几层IF条件，后面就有几个反括号。中间除了IF(外后面不加括号，直到输入到最后一层IF函数后在按套嵌数量补齐括号。实例：路基工程量计算提示：重复套嵌IF函数需要07以上版本的office，并且IF套嵌一定要分组进行，否则一个括号错误就会导致公式无法正确计算。四、IF函数多条件应用 在日常的台账整理和工程

4、量计算中，我们常常会遇到多个条件的条件判定，那么这里就会用到AND和OR这两个和IF函数组合用到最多的。AND返回逻辑值（即返还的信息是“正确”、“错误”）：如果所有参数值均为逻辑“真（TRUE）”，则返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”,AND的输入参数最多为30个。该函数返回值条件是必须满足全部参数。OR用于判断多个条件中是否至少有一个条件成立。如果任何一个参数为TRUE，则返回TRUE；如果所有参数都为FALSE，则返回FALSE。该函数返回值条件是满足任意一个即可。当IF多条件的编写为：=IF(AND（A1=5,B10),”上”,IF(OR(A1=5,B1=10

5、),”下”,0)以上函数的意思为:当A1单元格大于等于5，B1单元格不等于0的时候返回上，当A1单元单元格等于5或B1单元格等于10，满足任意一个值返回下。以上是IF函数的应用 一、查询函数LOOKUP LOOKUP函数是一种精确查找函数，单纯的LOOKUP可以返回精确值也可返回最小误差的近似值，那么单纯的LOOKUP在查找较大的数据范围的时候就会出现重复计算，但如果我们在日常计算中采用VLOOKUP或HLOOKUP的话就可以精确到小数点后的20几位去查找。V和H分别代表的是横向查找和竖向查找，由于我们平时制表过程中多采用横向制表，所以这里主要讲解VLOOKUP的使用，HLOOKUP的使用与V

6、LOOKUP相同。二、查询函数VLOOKUP VLOOKUP是在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值。语法：VLOOKUP（查找值，区域，列序号，逻辑值）查找值即为条件值，区域是我们要查找值的区域，序列号为我们要查找的值从查找区域的第一排算起，在第几排就输入几，逻辑值若要精确查找就输入0，相似值就输入小数点后的位数，最后返回我们要查找的数字，若要查找的区域内没有则返回TRUE或FALSE，若返回错误值或公式有误则返回#VALUE!和#REF!。这里是关于VLOOKUP函数的计算实力 三、IFERROR函数在VLOOKUP中的应用 那么我们在日常

7、应用中常常会遇到返回TRUE、FALSE、#VALUE的时候，这时说明单元格内显示的值为数值，那么这里就用到IFERROR函数。当公式=VLOOKUP(A1,F1:H20,2,0)时候，返回值为#VALUE，这是我们在公式的前面加上=IFERROR(VLOOKUP(A1,F1:H20,2,0),0)这样的组合函数最后的结果就为0。下面是在实例计算中的公式计算方式 以上函数是LOOKUP的函数应用 一、统计函数概念 EXCEL中统计函数非常的多，在本次讲解中主要以工程常用的统计函数为主 1、SUM函数 SUM函数属于求和函数很多人都会用这里不过多讲解。2、SUMIF函数 根据指定条件对若干单元格

8、求和。Excel中sumif函数的用法是根据指定条件对若干单元格、区域或引用求和。=SUMIF(条件区域,条件,求和)但该函求和只能对应行或列，不能区域性。3、COUNT函数 COUNT与SUM相同都是汇总函数，但COUNT是个数统计。语法：=COUNT(计数范围）那么COUNT有个局限性就是不能对EXCEL中出现的符号进行统计，在有字符、符号的范围内的计算结果不包含字符。4、COUNTIF函数 COUNTIF函数是条件取个数函数，这个函数可以对字符和符号的个数进行区域范围性的查找。语法：=COUNTIF(计算区域，条件）5、SUMPRODUCT函数 这个函数在我们日常报形象、日报中是作用非常

9、大的。该函数属于多条件求取1的乘积返回数值。语法：=SUMPRODUCT(条件1）*（条件2）（条件30）*计算区域）SUMPRODUCT还有一种用法是连续乘积，=SUMPRODUCT（1，2，3，4），这个计算式的意思是1*2*3*4=24以上为大家介绍的是我们日常需要用到的统计函数，这些函数除了独立应用外还常常以数组的形式出现，下文将会为大家说明在复杂工程量统计以及台账查找中常用的数组公式。这里选取个在日常统计工作中的表格给大家看下实际应用 一、什么是数组函数 网络及书本中给EXCEL数组函数的定义很深，由于我们并非计算机专业，所以数组我个人通俗的理解是区域及范围内数据的组合计算及反算，由

10、于EXCEL函数多以单行或单列基础去计算，所以区域性计算、较多条件计算、模式复杂条件计算普通函数就无法完成，数组的形式种类繁多，很多需要自己对函数的理解自己去组合。当我们正常输入公式或者函数时候，语句的结尾通过Enter键结束，数组函数则需要通过Ctrl+Shift+Enter组合键结束，好的数组公式可以解决很多函数无法解决的计算。在本文中主要介绍我们日常经常用到的和通过我个人组合出比较适用的数组函数于大家分享。一、VLOOKUP与IF的组合 1、IF(1,0在数组中的使用 IF(1,0在数组公式中与IF的判定类型相同1,0表示2个元素,表示组合数据后得出结果。VLOOKUP在前文中说过主要是

11、根据条件在组合数据内选出与之相同的值。那么二者之间组合后便成为：=VLOOKUP(A1&A2,IF(1,0,D1:D10&F1:F10,H1:H10),2,0 这个公式最外侧的大括号是通过Ctrl+Shift+Enter组合键完成的。这里通过一工区工程量计算给大家看一下具体计算时候的应用。二、SUM多条件求和数组函数 这个函数在日常工作中经常会遇到，比如说上级领导需要现场完成的工程量，按工区按桥区分，如果通过筛选或者累加公式，经常会遇到出错的现象，那么通过SUM数组函数就可以解决这个问题。编写方法=SUM(A1:A100=“白水滩1#桥”)*(B1:B100=“完成”),C1:D100)C1:

12、D100比如是孔桩的钢筋，那么通过这个公式我们就可以统计出白水滩1#桥已完成的孔桩钢筋量。这里有实例计算：EXCEL是我们平时计算工程量及对上对下计量中不可缺少的软件，那么以上为大家介绍的函数我们应用到我们的计算表格中后可以极大的提高计算效率和计算准确率。那么我们常常也会遇到很多函数和数组都不能一次解决的问题，同时我们也不能熟练的应用VBA宏去解决的时候，可是我们改为手动输入在重复、反复、及数据量较大的时候我们就可能因为疲劳出现很多错误，这时我们就需要做一些辅助单元格去应对这些计算。有时我们会采用函数辅助函数的方法，有时也会采用添加特殊字符去区分的方法。1、函数辅助函数 在隧道计算中尤其是白水

13、滩隧道各种不同的衬砌及围岩同时要按每延米去计算工程量的话会遇到各种变化，那么我们人工去判别里程的位置以及前后两次里程的对接经常会出现做的越多越迷糊。但是由于复杂的段落和反复的选取条件有时让IF函数和LOOKUP函数包括它们的数组都无法解决，我曾尝试过INDEX,MDETERM和IF之间的数组去解决可是最终还是需要人工去判别大量的数据，这时我想到了采用辅助单元计算，结果通过简单的IF函数和VLOOKUP函数就解决了隧道里程判别正确错误的问题，这里有计算实例，而且该实例在做11月隧道计量的时候我已经验证过。二、插入辅助单元格 在我们常常计算工程量时候，我们遇到条件重复但结果却不一样的数值，这里我在

14、曾经的工作中请教过一些高手，这种问题不是函数可以解决的，只有VBA宏应用的复杂程序可以计算，但是我们遇到这种情况的量并不大，所以并没有必要去采用复杂的VBA宏。所以我们这时可以在表格以内或以外的任意位置插入单元格输入字符，这时我们将这字符代入数组公式中，那么我们就可以很好的区分这些特殊的项目。在三工区二道岭各桥的计算过程中桥台的工程量计算就碰到了这种问题，由于设计给非常的乱所以通过字符代号区分就很重要。EXCEL在工程测量中也是常常用到的，卡西欧4800或卡西欧5800计算器比较适用于现场，毕竟笔记本并不适合随身携带，那么工程测量中涉及到的很多内业资料有时可以通过EXCEL很好的完成。在鹤大这

15、种线路较长纵断面线形较多的工程上，计算竖曲线我们往往要查找很多要素，翻很多次图纸，那么如果我们采用函数去计算我们翻一次图纸把曲线要素输入到EXCEL中通过条件和逻辑函数去计算每一个点的高程。这里有一份以前做过的全线高程计算表 在平面放样中一样可以通过函数，根据方位角的角度以及曲线的线形去计算全线任意一点坐标。这样做的效果可以达到只输入里程就可以计算任一点 一、EVALUATE的用法 EVALUATE属于引用函数计算，这个函数在工程中常用于计量计算单和收方计算单使用。用法：比如我们现场收方一处路基边坡铺砌，我们知道底宽是3m，高度是4m，长度10m，铺砌厚度是0.4m，我们在写计算单时要求A1-

16、A20单元格写出计算公式B1-B20单元格为结果，为了方便计算这里就用到了EVALUATE，以EXCEL2010版本为例，公式菜单自定义名称定义名称引用位置(在这里输入=EVALUATE(A1:A20)定义名称(定义名称输入一个自己熟悉的字符或编号这里假定A)范围(范围一定要选择自己所在工作簿的名字)，之后我们回到工作簿在A1单元格输入的公式为：SQRT(32+42)*10*0.4（SQRT是根号，代表多少次方),这时我们在B1输入=A，那么A1单元格的公式，在B1单元格自动计算出结果，这里我们一定要注意A1单元格里输入的公式一定要按EXCEL的公式输入格式去写，否则无法计算。下边有一个实例计

17、算 二、折线图绘制简单路基断面 在EXCEL中我们可以通过数据插入折线图的方式绘制简单的断面，在插入菜单中选择折线图(这里要选择带标记的那一种在图标类型中选择点位为X,Y的那一种后，A1:Q1就可以理解为默认的距离A2:Q2就可以理解为标高 那么输入填方段时先输入顶面高程和距离在输入底面高程和距离，0为分界线左侧为-，右侧为+，挖方段先输入底面高程和距离在输入顶面高程和距离，点位输入按顺时针一圈最后和第一点闭合。这里有计算实例1 1、DATEDATE函数函数 函数名称：函数名称：DATE 主要功能：主要功能：给出指定数值的日期。使用格式：使用格式：DATE(year,month,day)参数说

18、明：参数说明：year为指定的年份数值（小于9999）；month为指定的月份数值（可以大于12）；day为指定的天数。应用举例：应用举例：在C20单元格中输入公式：=DATE(2003,13,35)，确认后，显示出2004-2-4。特别提醒：特别提醒：由于上述公式中，月份为13，多了一个月，顺延至2004年1月；天数为35，比2004年1月的实际天数又多了4天，故又顺延至2004年2月4日。2 2、函数名称：、函数名称：DATEDIFDATEDIF主要功能：主要功能：计算返回两个日期参数的差值。使用格式使用格式：=DATEDIF(date1,date2,y)、=DATEDIF(date1,d

19、ate2,m)、=DATEDIF(date1,date2,d)参数说明：参数说明：date1代表前面一个日期，date2代表后面一个日期；y（m、d）要求返回两个日期相差的年（月、天）数。应用举例：应用举例：在C23单元格中输入公式：=DATEDIF(A23,TODAY(),y)，确认后返回系统当前日期用TODAY()表示）与A23单元格中日期的差值，并返回相差的年数。特别提醒：特别提醒：这是Excel中的一个隐藏函数，在函数向导中是找不到的，可以直接输入使用，对于计算年龄、工龄等非常有效。3 3、DAYDAY函数函数函数名称：函数名称：DAY主要功能：主要功能：求出指定日期或引用单元格中的日

20、期的天数。使用格式：使用格式：DAY(serial_number)参数说明：参数说明：serial_number代表指定的日期或引用的单元格。应用举例：应用举例：输入公式：=DAY(2003-12-18)，确认后，显示出18。特别提醒：特别提醒：如果是给定的日期，请包含在英文双引号中。4 4、DCOUNTDCOUNT函数函数函数名称：函数名称：DCOUNT主要功能：主要功能：返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。使用格式：使用格式：DCOUNT(database,field,criteria)参数说明：参数说明：Database表示需要统计的单元格区域；Field表示函数

21、所使用的数据列（在第一行必须要有标志项）；Criteria包含条件的单元格区域。应用举例：应用举例：如图1所示，在F4单元格中输入公式：=DCOUNT(A1:D11,语文,F1:G2)，确认后即可求出“语文”列中，成绩大于等于70，而小于80的数值单元格数目（相当于分数段人数）。EXCEL是目前工程内业计算中的主要应用软件，尤其在工程量计算，计量计算，内业台账整理上报过程中起到了增加准确率，提高效率的目的，因此现在很多大型项目的计算工作都采用EXCEL函数智能计算。EXCEL在计算过程中最重要的便是是制表的过程，合理的制表才能合理的运用EXCEL函数，尤其是在台账编制整理，需要统一格式，关键字

22、符按设计图纸上的书写方式编辑，这样才能起到快速汇总快速查询。EXCEL函数合理的运用好也同样可以避免计量中的工程量计算错误和重复计算，只要我们详细的统计出特征性数据这样我们即便出现错误我们只需要修改特征数据本表，而其他的表格可以通过特征本表的数据链接自动更新。建议我们在日后的工作中可以将各工区的施工台账汇总数据统一格式，确定好常用计算项目，尤其是形象统计，物资量计算，根据日期函数以及汇总函数的综合运用，我们可以毫不费力的在几分钟能完成数据汇总。当然以上结语都是在合理组织、统一格式的前提下进行，如果计算数据需要5个小时时间，那么我们利用EXCEL函数智能计算我们可以利用1小时思考1小时制表而公式

23、编辑和计算仅仅需要不超过15分钟的时间我们便可以完成，那么我们可以最少节省一半的时间，如果熟悉函数的编写可以节省更多的时间，那么余下的时间我们可以做更多的核算工作。EXCEL的应用目前已经是工程计算、统计、汇总的必备软件，如果运用的好、运用的合理、思路正确，那么我们在今后的工作可以事半功倍。在网络发展的今天，很多关于软件的知识我们都可以在网络中找到，很多EXCEL论坛QQ群组的数量不断增加，这就给了我们一个更多的学习机会，项目管理应与时俱进，好的软件好的功能我们就应该学习，当我们有多的人懂得去运用EXCEL进行数据处理的话，那么我们的工作和办事效率就可以无限提高。最后希望我们有更多的人可以去学习EXCEL的应用，用EXCEL强大的数据处理能力和执行力，来指导项目更好的运作。