EXCEL在工程中的应用.doc

EXCEL在工程中的应用开讲主导思想：大家好！很高兴与大家分享我实践应用EXCEL的一些心得和成果。在开讲之前，我想问大家两个问题：1、龟兔赛跑，兔子为什么跑不过乌龟？很多人想到了我们小学的那篇课文龟兔赛跑，兔子打瞌睡了。然而，我的解释是：两个出发点不一样，乌龟在跑的同时，兔子也在跑，它们的目的地是一样的，兔子虽然跑得快，然而每次跑得只是乌龟跑过的路程，所以最后乌龟胜利。2、两条平行直线相交于一点？我们都知道，在初中的几何里，两条平行直线是不可能相交的。这是欧几里德站在二维平面的角度说出的，大家不必诧异，这不是个弱智的问题，大家只是没有思考过而已。爱因斯坦认为光线在送到质量大的物体时，由于引力作用，无法直线行进会相交于一点。这两个问题是我讲解本课的主导思想：股票市场流传着这样一句话,思维有多远，财富就有多近。因循守旧，固步自封是制约我们前进的羁绊，思维的革新，创造性的工作带来却是我们的自信人生。下面我带领大家一起步入EXCEL殿堂，“闻道有先后”，在这里，我只能说有些东西我比大家学的早，但是不一定比大家认识的深刻，在讲得过程中，若有错误希望大家不吝赐教。前言：EXCEL发展历程EXCEL是1985年JENSEN HARRIS带领MS OFFICE UI开发团队开发的，在数年的应用中，与时俱进，不断跟进社会的工作效率要求，持续的改进。在EXCEL 5.0版本里出现了VISUAL BASIC FOR APPLICATION，也就是VBA，VBA程序的绑定是EXCEL发展历程中的里程碑。它的出现把EXCEL带入了一个自由的国度。EXCEL是WINDOWS XP九种软件中，第一个绑定VBA的软件，后来，又在WORD、POWERPOINT、ACCESS、PUBLISHER等八种软件中绑定了VBA。CAD的开发者，后来吸取了这些经验，也绑定VBA、VLISP、VSCRIPT三种编程语言。EXCEL的发展历程我就做这些简要的介绍，下面我将分基础部分、中级部分、高级部分三个部分予以介绍：一、基础部分：1.1、单元格文本间距调整单元格的长度（文本） 32767字符，只有1024个字符会显示在单元格内，所有的字符会显示在编辑栏，这是因为行高0409，列高0255之间限制。实例：略1、单击右键-设置单元格格式-对齐-水平对齐、垂直对齐在下拉菜单中均选择两端对齐-确定。2、强制换行ALT+ENTER。在VBA和VB中，换行是CHR(13)+CHR(10),其中CHR(13)回车，CHR(10)为换行。1.2、符号的快速录入实例：我们经常会录入平方、立方、人民币符号等，最快捷的方法就是： ： ALT+0178 ： ALT+0179 ： ALT+0165： ALT+0215： ALT+0247% ： ALT+0037： ALT+0137 等等，后面的数字是怎么来的，打开插入-符号-来自-ASCII(十进制) -字符代码。1.3、照相机功能实例：略工具-自定义-工具-照相机。点击要照相的区域，按住CTRL键，鼠标变为十字点击任意区域，可以看到这个功能相当于我们粘贴图片给表格。1.4、单元格输入中语言切换我们在EXCEL单元格输入中经常会在英文模式和其他文字录入模式切换，我首先带领大家把自己经常使用的文字录入模式至首位：方法一、开始-运行-REGEDIT-HKEY_CURRENT_USERKEYBOARD LAYOUTPRELOAD在这个里面更改次序，这个顺序对应的是你的电脑目前输入法的顺序，更改后不要忘了重新启动电脑。方法二、用超级兔子-魔法设置-个性化设置-上移、下移或删除输入法顺序。实例：略数据-数据有效性-输入法-模式-打开。1.5、记忆键入法的妙用文本录入中，经常会出现单元格文本内容，很多文字性的东西输入会影响工作的效率，可以运用记忆键入法（ALT+）。实例：略1.6、常量定义这个财务上经常使用，不同税率，如何在快速的在表格中使用。实例：略插入-名称-在当前工作表中的名称-引用位置输入数字。1.7、保护工作表工作表的保护大家都会操作：工具-保护-保护工作表-两次录入密码。我这里要说的是如果密码忘记了怎么办，数据又无法编辑。有两种方法：方法一、重新打开一张新的工作表，把保护的进行复制，粘贴到新的工作表中；方法二、可以下载PASSWORD RECOVERY进行密码破解，这个软件可以破解EXCEL的VBA密码、表格的保护密码。在我写程序的过程中，我经常会及时的录入一段反破解的密码，这样用这个软件就不会破解掉。无论怎么说“道高一尺魔高一丈”，需要的还是不断的学习。1.8、去除0值在我们工具-选项-视图-零值去勾,这个在计价中经常使用，比如有些单价里设备单价是等于0，我们可以清除该部分。1.9、条件格式的应用格式-条件格式-设置，可以凸显数据的重点。1.10、数据自定义功能我们都知道，文本格式是不能进行数学运算的。我们在实际的操作中，这个功能是个伟大的功能。实例：平均断面法计算方量对该单元格应用自定义功能，设置单元格格式-自定义-改为：“30+”0。1.11、超级链接表格之间切换，便于操作。实例：物资管理程序.xls1.12、输入相同的数据这里有两个快捷键一个是应用区域的，一个是应用单元格的。应用区域的是CTRL+SHIFT+ENTER;应用单元格的是CTRL+D。实例：略1.13、分列功能这个功能用的最多的是在测量中，我们知道测量的数据一般是SCS数据：点号，Y坐标，X坐标,Z坐标。我们一般需要对数据进行一定的处理，便于实际报验资料，这个时候我们用的最多的就是分列功能，下面，我通过实例讲解这个功能。实例：略这个功能，我曾经实现了SET210 TOTAL STATION数据的转换。又用C+处理过全站仪数据写成了论文浅谈全站仪数据转换论文。1.14、快速删除表格中空行删除Excel XP工作表中的空行，一般的方法是需要将空行都找出来，然后逐行删除，但这样做操作量非常大，很不方便。实例：略插入-列-插入一新的列X-顺序填入整数-行排序-删去所有空行中X列的数据-以X列重新排序-然后删去X列。1.15、快速进行页面设置一个工作簿中有多个工作表应该是常有的事儿。各个工作表需要打印时，怎样进行页面设置最快？当然是将这些工作表同时选中同时设置最快了。实例：略方法是在工作表标签处右击鼠标，在弹出的快捷菜单中选择“选定全部工作表”。然后再点击菜单命令“文件页面设置”就可以对它们同时进行页面设置了.如果要选中的只是部分工作表，那么可以按住“Ctrl”键依次单击各工作表标签。1.16、自动更正功能我们经常为输入某些固定的文本而烦恼，到底有没有一种简便的方法呢？回答是有的。实例：兰州商贸有限公司(LZSM)执行“工具自动更正”命令，打开“自动更正”对话框。在“替换”下面的方框中输入“LZSM”（也可以是其他字符，“lzsm”用小写），在“替换为”下面的方框中输入“兰州商贸有限公司，再单击“添加”和“确定”按钮。以后如果需要输入上述文本时，只要输入“LZSM”字符此时可以不考虑“lzsm”的大小写，然后回车就可以了。1.17、绘制双斜线表头实例：略首先，我们在Word 2003中“插入表格”工具插入一个二行二列的表格。选中表格中左上角的单元格，单击“表格”菜单，在弹出的下拉菜单中单击“绘制斜线表头”命令，弹出“绘制斜线表头”对话框，该对话框共提供了五种斜线表头的样式，根据需要选择一种样式，并设置适当的字号，然后在行标题栏中分别填入各栏的标题，完成后单击“确定”按钮，则“插入斜线表头”对话被关闭，在被选中的单元格中插入了一个斜线表头。 其实在Word 2003中制作的斜线表头是预先制作好的一个斜线表头框架，用鼠标点击后会在四周出现控点。用鼠标选中这个斜线表头框架，将它复制到“剪贴板”上。 关闭Word 2003回到Excel 2003中，将斜线表头框架粘贴到工作表中并移动到要放置斜线表头的位置。调整斜线表头框架及它要覆盖的单元格的大小，选择所有被斜线表头覆盖的单元格，单击工具栏上的“合并单元格”按钮，将它们合并起来，则一个带有斜线表头的Excel工作表制作完毕。1.18、快速拆分窗口实例：略为方便查看表格的不同部分，我们经常要对工作表进行垂直或水平拆分。快速拆分的方法是：先在需要拆分的位置单击鼠标，激活活动单元格，然后将鼠标指向垂直滚动条顶端的拆分框，待鼠标指针变成拆分指针时，双击鼠标，那么窗口就会在活动单元格上方将窗口水平拆分，再次双击拆分框，则拆分取消。1.19、隔行粘贴实例：略按Ctrl键后同时选中A1、E1、A5、E5四个单元格，复制后粘贴会发现复制的内容连接到了一起。这样可以达到快速复制不连接单元格的作用。当然如果中间有空白单元格则是无法选中的。1.20、查找替换实例：略1、如果A1单元格中的公式是：=152,值为30。要直接查找30，你肯定查不到！你肯定很奇怪，明明有30，却查不到！ 原来，excel默认按公式进行查询，点击“选项（T)”按钮你就明白了，选择按值查找，就可以找到。2、在我们替换的过程中，比如：我有1000个数据要更改，用系统的查找替换命令一个个的改太要时间，当然懂VBA的人肯定不在话下，通过程序很快也就改掉。其实，只需将范围切换到“工作簿”，就一切OK了，根本不需要额外的工具！公式运算的结果则必须按“值”进行查询。3、如果想查询: Abbbc abc abbbbbc,那么在查找内容中使用*通配符，即: a*c。 *代表一串字符串?代表一个字符初级部分就讲到这里，我总共讲了20个被忽略的功能。大家经常用的那些功能，我在此不在赘述！下面我们进入中级阶段！二、中级部分 函数的熟练操作，是你步入中级殿堂的标志。学过C、C+、VB、VLISP等编程软件的都知道，函数是执行计算、分析等处理数据任务的特殊公式。而中级部分，我们主要讲函数。EXCEL的函数分为内置函数和扩展函数。内置函数是预定义好的，扩展函数是运用程序二次开发的。EXCEL的内置函数共分十一类：1、数据库函数：如DAVERAGE、DMAX等，共13个；2、日期与时间函数：如DATE、NOW等，共20个；3、外部函数：如EUROCONVERT等，共2个；4、工程函数：BESSELI等，共39个；5、财务函数： FV等，共52个；6、信息函数:CELL等，共9个；7、逻辑运算符：AND、NOT等，共6个；8、查找和引用函数：ROW、TRANSPOSE等，共17个；9、数学和三角函数：ABS、ATAN等，共28个；10、统计函数：COUNT、COUNTA等，共80个；11、文本和数据函数：TEXT、TRIM等，共28个。以上11类函数，共294个。内容：1、数据库函数：（）这类函数有3个共同点：1、均有三个参数：DATABASE（构成列或数据库的单元格区域）,FIELD（指定函数所使用的数据列）和CRITERIA（包含给定条件的单元格区域）；2、都以字母D开头，因此又称DFUNCTIONS函数；3、去掉D后我们发现很多数据库函数在其他类型函数中出现过了。下面通过一个实例进行说明：树 高度 使用年限 产量 利润苹果树 18 20 14 105梨树樱桃树树苹果树=DAVERAGE($A$1:$E$7,B1,C9:C10)2、日期与时间函数：（）通过日期和时间函数，可以在公式中分析处理日期值和时间值。1、用于取出当前系统时间/日期信息的函数主要有NOW、TODAY；2、如果需要单独的取年份、月份、日期或小时的数据，可以使用HOUR,DAY,MONTH,YEAR。实例：1、如果“=NOW()”=2009-12-2 19:30:00 则：“=year()”=2009“=month()”=12“=day()”=2“=hour()”=19“=minute”=30“=second()”=02、再学一个日期与时间函数DATEDIF(START_TIME,END_TIME,UNNIT)如果张三进入公司的时间为2006-7-21，那么他的工龄可以这样输入：=DATEDIF(“2006-7-21”,now(),“y”)=3年3、财务函数：由于外部函数和工程函数不经常使用，这里不在多说。财务函数大体包括四类：投资计算函数、折旧计算函数、偿还计算函数、债券。投资计算函数PV（计算投资现值）、FV（计算投资未来值）、NPV（计算净现值）、PMT（计算贷款的每期付款额）经常使用，掌握一个其他触类旁通，这里主要介绍PV函数。实例：假设要购买一项保险年金，该保险可以在今后20年内于每月末回报500元。此项年金的购买成本为60000，假定投资回报率为8%。那么该项年金的现值多少？=PV(RATE,NPER,PMT,FV,TYPE)RATE:各期利率。如果年利率为12%，则月利率为1%；NPER：总投资期。如果4年期按月偿还，共有48个偿还期数；PMT：各期应支付的金额。FV:未来值，如果18年后支付5万元，那么5万元就是未来值；TYPE:数字0或者1，用以指定各期的付款时间是在期初还是期末。=PV(0.08/12,12*20,500,0)=-59777.15备注：500指月底年金的支出。负值指的是支出的现金流，年金59777.15的现值小于实际支付60000。因此，不是可以看出这不是一项合适的投资。4、信息函数：专门返回某些指定单元格或区域等的信息。1、CELL函数用于返回单元格的格式、位置或内容的信息函数。语法形式：CELL(INFO_TYPE,REFERENCE)INFO_TYPE:一个文本值，所需要单元格信息的类型；REFERENCE:要获取其有关信息的单元格。=CELL(“ROW”，A20)=CELL(“CONTENTS”，A20) 2、COUNTBLANK用于计算指定区域中空白单元格的个数。5、逻辑运算符：1、AND、OR、NOT2、IF函数可以7重嵌套，如=IF（H860,“通过”,“没有通过”）3、COUNTIF(B2:G2,“=90”)6、查找和引用函数： OFFSET函数用于以指定的引用为参照系，通过给定偏移量得到新的引用。OFFSET(A1,2,3,4,5)标示比单元格A1靠下2行并靠右3列的4行5列的区域。 TRANSPOSE7、数学和三角函数： 1、SUMIF函数可对满足某一条件的单元格区域求和。 实例：=SUMIF(B2:B9,“职工”,C2:C9)发表论文的数量8、统计函数：AVERAGE函数、TRIMMEAN函数AVERAGE():计算算术平均值TRIMMEAN():比如，我们在计算选手分数中常用去掉一个最高分，去掉一个最低分，最后得分。实例：略9、文本函数：LOWER:将字符串的所有大写字母转换为小写字母UPPER：将文本转换为大写形式PROPER:将字符串的首字母及任何非字母字符之后的首字母转换为大写。LEFT(“I LOVE YOU”,1)=IMID(“I LOVE YOU”,3,4)=LOVERIGHT(“I LOVE YOU”,3)=YOUTRIM去除空格函数实例：略三、高级部分