《excel在工程中的应用技巧.doc》由会员分享，可在线阅读，更多相关《excel在工程中的应用技巧.doc（15页珍藏版）》请在金锄头文库上搜索。
1、EXCEL在工程中的应用技巧前言：微软的OFFICE是目前最为流行的办公软件，广泛的应用于生产建设的各个行业和众多领域，而其中的EXCEL套装除了能进行简单的表格处理以外，更重要的应用领域就是具有繁重计算任务的统计、财务、预算、数据分析等。在工程建设中，利用EXCEL中相关的数据分析和函数应用，能极大的提高工作效率和保证结果的正确性。本文以EXCEL2003版为基础，通过一些深入简出的EXCEL应用技巧，结合工程建设中的实例，来揭开EXCEL的冰山一角，让大家体会到EXCEL的强大和神奇。关键词：EXCEL 技巧 工程建设 工作效率1、轻松操作工作表1.1使用键选择大区域如果需要选择一个较小的
2、单元格区域，可以简单是使用鼠标来完成，一旦单元格超出了电脑屏幕显示的范围，用鼠标去选择常常会出现无法准确定位的情况，要么选多了，要么选少了，这个时候，使用键盘的键和鼠标配合操作就简单多了。比如，我们需要选择A1至Q345的单元格区域，可是先用鼠标定位到A1单元格，然后按住键，在用鼠标定位到Q345单元格，然后松开鼠标和键，就可以把A1至Q345的单元格区域全部选择了。1.2选择当前数据区域有时候我们需要对矩形数据区录入完毕的工作表加上边框，最简单的办法就是先选中数据区域的任一单元格，然后按组合键，就能选择当前矩形数据区域，然后既可以直接添加边框了。1.3轻松选择“特殊”区域比如我们通过数据透视
3、表（后有介绍）得出了如下图的一个汇总表格，为了便于筛选和统计需要向下填充单元格，我们可以使用EXCEL的“定位”功能选择所有空白单元格，然后向下填充就可以了。具体操作步骤如下：在当前工作表上，单击“编辑”“定位（也可以使用快捷键F5）”“定位条件”“空值”“确定”“键盘=” “键盘”“ctrl+enter组合键”就能完成向下填充空白单元格，填充完毕后效果如下：1.4快速切换工作表在一个EXCEL工作簿中，如果存在很多工作表，可以使用右键单击工作表导航栏，这是会显示一个工作表标签列表，单击其中任何一项，就能达到相应的工作表。如果工作表实在太多，还可以单击工作表标签列表底部的“其他工作表”选项，这
4、时会显示“活动文档”对话框，累出全部工作表的标签，双击其中的任何一项，就可以到达指定的工作表。如下图所示：1.5多窗口协同作业利用EXCEL菜单里面的“拆分窗口”和“并排比较”可以再一个电脑屏幕上同时显示多个工作表或工作簿窗口，以便于进行数据的比较和编辑。具体效果见下图：拆分：并排比较1.6神奇的右键和双击填充当输入一个数据到一个单元格，然后用右键拖拽此单元格，当松开右键时，会出现一个快捷菜单，菜单上显示了本次填充可以选用的类型，甚至可以链接到“序列”对话框，进行更复杂的设置，如下图所示：当选中一个或者多个单元格以后，使用鼠标右键移动所选单元格，到达移动位置以后，松开右键，也会出现一个快捷菜单
5、，菜单上显示了本次移动可选用的类型，该技巧特别是在处理含有函数和特殊格式的单元格时，能代替“选择性粘贴”的功能，从而有效的提高了工作效率。如下图所示：当需要在数据区域中对某一列的内容进行填充时，可以不必从第一个单元格一直拖拽到最后一个单元格，而只需双击第一个单元格右下角的填充柄即可，该技巧在单元格内含有函数时，双击填充柄，即是对该列所有同一个函数的自动计算。但是双击填充有一个局限性，即填充到最后一个单元格的位置取决于左边一列中第一个空单元格的位置，如果填充的是第一列，则参考右边列中的单元格。如下图所示：双击填充的局限性1.7使用语音校验输入准确性在EXCEL2003版本中，有一项非常有用但却不
6、被人注意的功能文本的语音，这就意味着可以让EXCEL开口说话了。对于需要大量数据录入的工作，比如单线材料录入，语音功能可以帮助录入者检验所录入数据的准确性，让耳朵加入工作，会比只使用眼睛轻松的多。在使用该功能时，可以指定一个单元格区域让EXCEL朗读，也可以设置每录入一个单元格就能听到结果。单击菜单“工具”“语音”“显示文本到语音工具栏”就可以启用该功能。在该工具栏上可以对朗读的方向和范围及每录入一个数据就马上听到语音结果进行设置。如下图所示：当然，该功能在很多情况下没有和EXCEL被一同安装，对于这种情况，可以根据提示使用安装光盘进行手动添加。1.8快速录入部分相同数据在录入比较长的数据，且
7、前半部分为相同的字符时，比如录入“TCC-201-3100-PT-001”、 “TCC-201-3100-PT-002”，可以使用单元格格式设置来完成。在需要录入数据的单元格里面只录入001、002就能实现单元格数据为“TCC-201-3100-PT-001”、 “TCC-201-3100-PT-002”。具体操作见下图：2数据有效性技巧2.1在单元格中创建下拉列表利用数据有效性可以在一个单元格中创建一个下拉列表，选定该单元格，就会在单元格的右侧出现一个倒三角的箭头，单击箭头，就会出现一个下拉列表，单击其中任何一项，该项内容就会被输入到该单元格中。如下表：利用下面步骤可以实现上述效果：在一个连
8、续单元格区域中输入列表中的项目，如下图所示：单击菜单“数据”“有效性”，在“数据有效性”对话框的“设置”选项卡中，在“允许”下拉列表框中选择“序列”项，在“来源”框选定输入列表的单元格，勾选“忽略空值”和“提供下拉箭头”复选框，单击“确定”按钮，关闭“数据有效性”对话框，这样就能实现在单元格中创建下拉列表了，如下图：如果列表的内容较少，可以在“来源”框中直接输入相关数据。使用该功能，可以极大地提高对需要经常录入固定数据的单元格的录入速度，比如探伤委托单上一些工号、探伤比例、焊接形式等。2.2限制重复值的录入在大量录入数据时，避免录入重复的数值，是平常工作中经常遇见的事情，比如在制作管线目录时，
9、为确保管线的唯一性，可以把每条管线号作为一个关键字来限制录入。方法如下：加入需要在A2：B11区域内输入管线号，单击A2单元格并拖动光标到B11以选定区域A2：B11，单击菜单“数据”“有效性”，在“数据有效性”对话框的“设置”选项卡中，找到“允许”下拉列表框中选择“自定义”项，在公式框中输入“=COUNTIF($A$2:$B$11,A2)=1”，然后勾选“忽略空值”复选框，单击“确定”按钮，关闭“数据有效性”对话框，现在任何重复的管线号都不能在A2：B11区域内被重复录入，如下图：上述情况是EXCEL针对同一个关键字来限制重复录入，如果在录入焊接记录的时候，需要在每条管线中不得出现两个同样的
10、焊口号，就需要使用EXCEL的针对“双关键字限制重复值”的功能了，假如需要在A2：B20区域内完成此操作，具体方法如下：单击A2单元格并拖动光标到B20以选定区域A2：B20，单击菜单“数据”“有效性”，在“数据有效性”对话框的“设置”选项卡中，找到“允许”下拉列表框中选择“自定义”项，在公式框中输入“=SUM(N($A2&|&$B2=$A$2:$A$11&|&$B$2:$B$11)=1” 然后勾选“忽略空值”复选框，单击“确定”按钮，关闭“数据有效性”对话框，现在任管线号的重复焊口都不能在B20区域内被重复录入，如下图：上述公式实际是一个数组公式，数组公式如果没有被应用于工作表中，是不用带花
11、括号的（数组公式后续有介绍），此公式的含义是将活动单元格所在行的A列单元格与B列单元格组合，然后与其他行的A、B列单元格组合相比较，判断内容是否一致，最后合计内容一致的次数，如果次数为1，则表示没有重复的组合，反之则提示错误。2.3多级选择录入在许多信息系统的数据录入中，都存在多级选择的功能，即先选择某录入大项后，再选择录入此属于此大项的小项，这是一个非常有用的功能，比如在制作无损检测委托单的时候，首先选择录入了一种管道的材质，然后录入焊工的焊工号，往往会因为统计和录入的错误，将不能焊接该种材质的焊工录入，为了避免这种错误，可以使用EXCEL的多级选择录入功能确保录入焊工的准却，方法如下：首先
12、需要在委托单的工作簿中建立一个焊工库工作表，焊工库里面记录了能够焊接某种材质的焊工号，如下图：然后定位到无损检测委托单工作表中需要录入焊工号的单元格，按组合键，调出：“定义名称”对话框，添加“caizhi”和“hangong”两个名称，分别对应的引用位置为“=焊工库!$A$1:$E$1”和“=焊工库!$A$2”如下图所示：单击工作表标签，切换到“无损检测委托单工作表”，单击需要录入管道材质的单元格，单击菜单“数据”“有效性”，在“数据有效性”对话框的“设置”选项卡中，在“允许”下拉列表中选择“序列”项，在“来源”中输入=caizhi，然后勾选“忽略空值”复选框，单击“确定”按钮，关闭“数据有效
13、性”对话框，再单击需要录入焊工号的单元格，单击菜单“数据”“有效性”，在“数据有效性”对话框的“设置”选项卡中，在“允许”下拉列表中选择“序列”项，在“来源”中输入=OFFSET(hangong,MATCH($D9,caizhi,)-1,COUNTA(OFFSET(hangong,MATCH($D9,caizhi,)-1,65535) ，勾选“忽略空值”复选框，单击“确定”按钮，关闭“数据有效性”对话框如此设置完成后，就能实现多记录入了，输入一种材质，在需要输入焊工号的单元格里面只能输入能够焊接该种材质的焊工号，从而有效的避免了不能焊接该种材质的焊工号的输入，保证了资料的正确性。如下图：3数据
14、分析技巧EXCEL的数据分析功能在工程中的主要应用就是利用“数据透视”功能实现快速的分类汇总，虽然EXCEL菜单中也带有“分类的汇总”功能，但经过笔者多次使用发现，“分类汇总”功能往往不能够直接得到需要将单线材料汇总报出材料计划的直观表格样式，在对“数据透视”研究使用后发现，使用“数据透视表”能够快速而且直观的得到汇总材料计划的表格样式。具体操作如下：打开录入完毕的单线材料表，如下图：单击菜单“数据”“数据透视表和数据透视图”，在弹出的对话框中单击“完成”按钮，RXCEL会建立一个新的工作表，如下图所示：接下来就可以根据工作表提示将需要汇总的项目及数据拖入规定的区域内，如下图：双击左上角的灰色
15、单元格“计数项：数量”，在弹出的对话框中选择“汇总方式”“求和”单击“确定“按钮，关闭对话框，如下图：然后单击选择行“汇总”单元格，单击右键，选择“隐藏”隐藏后结果如下图：然后选择包括“材料描述”“规格”“材料代码”“汇总”行及其以下的数据单元格，按键盘组合键复制，然后新建一个工作表，使用“选择性粘贴”“数值”得到如下图所示的表格：然后采用本文1.3技巧，填充所有空白单元格，就得到如下的材料计划基本表格样式：至此，通过“数据透视表”功能将一份完整而直观的材料计划表格制作完毕，整个过程将达上万行的单线材料表汇总完毕仅需要20秒，极大的提高了在材料汇总方面的工作效率。同时，“数据透视表”功能还能完成添加计算项、快速统计重复项、二维表格转换一维表格等等功能，因在工程中较少用到，本文不再详述。4函数技巧函数就是进入EXCEL世界大门的钥匙，只有了解和掌握了相当数量的行数以后，才能真正体会到EXCEL世界的神奇和伟大之处。根据笔者的使用经验，以下函数在工程建设中使用较多且能够有效的提高数据统计效率和日常工作效率。4.1TEXT函数该函数就是将指定的数值格式转换成文本，顾名思义，该函数能够将数值转换成文本，以便使用字符串对相关的数值进行其他的函数操作，该函数参数简单，
