Excel在工作中的使用技巧总结

Excel在日常工作中的使用技巧总结第一部分 前言：也许你已经在Excel中完成过上百张报表，也许你已利用Excel函数实现过很多复杂运算，也许你认为Excel也不过如此，甚至了无新意。但我们平日里无数次重复的得心应手的使用方法只不过是Excel全部技巧的百分之一。本人通过在工作中对Excel2003版和2007版的使用，总结出一些使用技巧，希望能让大家领略一下Excel的别样风情，让繁琐的工作变得轻松愉快。第二部分 Excel2003版和2007版主要区别：对我们的日常工作中常用功能来说，Excel2003版和2007版的主要区别从下表中可见一斑。项目Excel2003Excel2007数据容量256列 65536行。16384列 1048576行。重复项的删除可以通过编写宏来实现，对操作者要求较高。有快捷按钮，并可以选择多个包含重复项的列进行删除，操作非常简便。自动筛选选项只能单项选择。可进行多项选择。颜色的筛选可以通过编写宏来实现，对操作者要求较高。可在自动筛选下拉列表中进行操作，可对填充颜色及字体颜色进行筛选。表格或单元格区域中排序层级只有3个层级。有64个层级，并可进行颜色排序。公式中的嵌套层级只有7个层级。有64个层级。除上面表格中所列之外，同一文件用2007版存储大约可以减少50%65%的存储空间；并且Excel2007彻底抛弃了以往的下拉式菜单，做成了更加直观的标签式菜单，大大方便了用户的操作。尤其是对新手来说，完全可以在“零”时间内上手操作，所以本人建议大家换版使用Excel2007。第三部分 以Excel2007版本为例的常用使用技巧：一、 利用粘贴链接，实现不同工作薄中相同项目的统一更改1. 工作薄数据链接的功用：常用于数据统计汇总，将各分表的数据链接到总表指定单元格，当分表数据发生变化时，总表的统计汇总数据将自动更改。合理地利用该功能，可大大提高工作效率。2. 工作薄数据链接的创建： 打开要链接数据的工作薄（分表工作薄），选定要链接的数据（如图1中的E26:K26），单击“开始”选项卡中的复制按钮（或按+快捷键），将选定数据复制到剪贴板中。图1 打开需要创建链接的工作薄（即总表工作薄），选择要插入数据的单元格或区域。地址 单击“开始”选项卡中粘贴按钮的“”，在弹出的命令列表中单击“粘贴链接”选项。（如图2）图2此时图2中的B3:H3就引用了图1中的E26:K26数据。当分表中E26:K26数据发生变化时，总表中B3:H3中的显示数据也将随之变化。3. 功能扩展及注意事项：粘贴链接既可以在Excel工作薄之间建立链接，也可以在Excel工作薄与其他应用程序对象（如Word）之间建立链接。建立链接的工作薄或对象，既可是本机的，也可以是网络中其它计算机。如果链接对象不是本机上的，需要在工作薄名称前加上计算机或Web地址（如图2 ）。二、 通过建立工作组，成组填充多张表格的固定单元格1. 工作组的功用：在工作中，很多时候需要同时在多张表格的相同单元格中输入同样的内容，或需要将多张表格中相同位置的单元格统一设置（或改变）格式。通过在同一工作薄中选取多个工作表建立工作组，可以轻松实现多表相同单元格或单元格区域同时操作。2. 工作组的建立和取消： 打开包含多个工作表并要对其相同单元格进行统一操作的工作薄，选取多张工作表，建立工作组，之后便可对工作组内的所有工作表进行统一操作了。方法如下：图3 要选取两个或多个相邻的工作表时，首先单击要选取的第一个工作表的标签，然后在按住键的同时单击要选取的最后一个工作表的标签。 要选取不相邻的两个可多个工作表时，单击要选取的第一个工作表的标签，然后在按住键的同时单击其它待选取的工作表的标签。 如果要选取整个工作薄的所有工作表，可在任意一工作表中将鼠标指针移动到的工作表标签位置，然后单击鼠标右键，在弹出的右键菜单中单击“选定全部工作表”命令。在工作组建立成功之后，将在工作表顶部的标题栏中显示“工作组”字样（如图3）。 取消工作组比建立工作组更加容易，只要单击任意一个非工作组的工作表标签或在任意一个工作组的工作表标签处单击鼠标右键，然后单击右键菜单中的“取消组合工作表”选项即可（如图3）。三、 条件格式的使用1. 条件格式的功用：使用条件格式可以突显所关注的单元格或单元格区域；强调异常值；使用数据条、颜色刻度和图标集来直观地显示数据。2. 什么是条件格式：条件格式是基于条件更改单元格区域的外观。如果条件为True，则基于该条件设置单元格区域的格式；如果条件为False，则不基于该条件设置单元格区域的格式。3. Excel2007中的条件格式：在Excel2007中，条件格式最多可包含64个条件（早期版本只支持3个条件），且单元格中的条件格式区域可以重叠。此外，Excel2007还提供了许多新的条件格式类型，主要有色阶、数据条、图标集、排名靠前或后的值、高于或低于平均值、唯一或重复值等。4. 条件格式的使用实例：将高于平均分的总分设置为红字加粗格式。 选择要更改格式的单元格区域（如图4）。 单击“开始”选项卡条件格式按钮的“”，在弹出的命令列表中单击“项目选取规则”选项（如图5）。 图4 图5四、 巧用数据有效性1. 数据有效性的功用：在很多情况下，设置单元格数据的有效范围非常重要，通过设置数据的有效范围，可以限制用户只能输入有效数据，或可以让用户用鼠标进行选择性输入，从而达到数据的规范统一，极大地减少了数据处理操作的复杂程度。2. 数据有效性的类型及含义设置单元格区域的数据有效性，首先要设置单元格区域的有效性类型。单击“数据”选项卡的“数据有效性”按钮打开设置选项卡，然后单击“允许”选项下方的列表框，就可看到各种类型（如图6）。 任何值默认选项，对输入数据不作任何限制，表示不使用数据有效性。 整数指定输入的数值必须是整数。 小数指定输入的数值必须是数字或小数。 序列为有效性数据指定一个序列。 日期指定输入的数值必须是日期。图6 时间指定输入的数值必须是时间。 文本长度指定有效性数据的字符数。 自定义允许用户定义公式、使用表达式或引用其它单元格的数据来判定输入数据的正确性。3. 数据有效性序列类型的应用实例为所选单元格区域建立一个下拉列表，从下拉列表中选择数据进行输入，达到规范输入及可以鼠标输入的目的。 选择要进行数据有效性设置的单元格区域（如E2:E4），打开设置选项卡，单击“允许”选项下方的列表框中的“序列”，然后在“来源”文本框中键入列表值（如图7），也可单击“来源”文本框右侧的按钮，之后选择含有列表值的单元格区域（实例中为J2:J4），再单击“来源”文本框右侧的按钮（如图8），最后单击“确定”按钮，完成设置。图7 图8 完成序列设置后，当焦点移动到该单元格时，单元格右侧将会出现一个下拉三角形，单击后可打开下拉列表，之后可用鼠标进行选择输入（如图9）。注意：在 “来源”文本框中键入列表值时，各项之间用Windows列表分隔符（即英文逗号）分隔。图9 4. 数据有效性文本长度类型的应用实例在一些时候，我们在某单元格区域输入的数据字符数是固定的，如手机号码、身份证号码、邮政编码等。为了防止输入时出现位数错误，就用数据有效性文本长度类型来进行设置。 选择要进行数据有效性设置的单元格区域（如C2:C4），打开设置选项卡，单击“文本长度”选项下方的列表框中的“序列”，然后在“来源”文本框中键入“00000000000”（如图10），后单击“确定”按钮，完成设置。文本框中键入的“0”个数，即为所设置的单元格输入数据的位数。 设置完成后，在该单元格输入数据时，如果输入位数不等于11位，则会弹出提示，并停止输入（如图11）。 图10 图11五、 简单快捷地去除超级链接如果Excel中某列单元格均含有超链接，用手工删除超链接的话，就必须一个个进行。其方法是右击该列中任何一个含有超链接的单元格，在弹出的右键菜单中选择“取消超链接”。如果数量巨大，这种方法的效率就太低了，因此只能考虑采用批量删除法。下面介绍一种操作简便的批量删除超链接的方法。 如图12中所示的表格中，C列单元格大部分含有超链接，而要批量除法的最简单的方法就是选择C列进行复制，再选择一个空列（如F列），然后单击“开始”选项卡中粘贴按钮的“”，在弹出的命令列表中单击“粘贴值”选项。图12 选择F列，再把F列复制粘贴回C列即可（如图13）。 图13第四部分 几个常用函数的使用介绍一、 最常用的查找函数VLOOKUP1. 用途：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数VLOOKUP代替函数HLOOKUP。2. 语法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)。3. 参数：Lookup_value为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。Table_array为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。Col_index_num为table_array中待返回的匹配值的列序号。Col_index_num为1时，返回table_array第一列中的数值；col_index_num为2，返回table_array第二列中的数值，以此类推。Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。4. 建立一个供方信息查询表要进行操作的是根据供应商信息表如图12，建立一个查询表，格式如图13。 图12 图13 以B4单元格作为参数lookup_value，以图12信息表中的A:K单元格区域作为参数table_array，根据所需内容确定参数col_index_num，参数range_lookup取FALSE（精确匹配）。如在B6输入“=VLOOKUP($B$4,信息表!$A:$K,2,FALSE)” （如图13）。含义是在信息表A:K单元格区域查找与B4单元格完全相同的内容，并在该区域中取与其对应的第2列单元格的内容到B6单元格。 为了防止公司名称输入不规范，造成无法查询，建议有前面讲到的数据有效性中的序列，对B4单元格进行设置。 在应用函数时要注意相对引用和绝对引用的使用。$B$4是绝对引用，在公式所在单元格位置变化或公式复制时，此引用不会改变；如果用B4，则为相对引用，在公式所在单元格位置变化或公式复制时，会产生变化。如公式由B6复制（移动）到H6，B4也会变成H4；如公式由B6复制（移动）到B12，B4也会变成B10。二、 非常好用的逻辑函数IF1. 用途：执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测任务。2. 语法：IF(logical_test，value_if_true，value_if_false)。3. 参数：Logical_test计算结果为TRUE或FALSE的任何数值或表达式；value_if_true是Logical_test为TRUE时函数的返回值，如果logical_test为TRUE并且省略了value_if_true，则返回TRUE。而且value_if_true可以是一个表达式；value_if_false是Logical_test为FALSE时函数的返回值。如果logical_test为FALSE并且省略value_if_false，则返回FALSE。value_if_false也可以是一个表达式。4. 应用实例：上面简单的介绍了函数IF，下面通过一个实例来具体理解它到底能为我们做些什么？ 下面这个表是一个我即将要进行整理的评级表。我要做的是把F列和G列的数据进行分类比较，之后在H列进行标注（如图14）。 图14 在H2中输入“=IF(F2=G2,F2,IF(F2=取消,G2&(昌铃取消),IF(G2=未评级,F2&(合肥未评级),级别不同)”。a) 说明如下：=IF(F2=G2,F2,IF(F2=取消,G2&(昌铃取消),IF(G2=未评级,F2&(合肥未评级),级别不同)第1层第1层的参数logical_test第1层的参数value_if_true第2层，同时作为第1层的value_if_false参数第3层，同时作为第2层的value_if_false参数b) 可以实现的结果是：当F2=G2时，H2取F2的数据，如果F2G2，则对F2是否为“取消”进行区分；如果F2=取消，H2取G2并加上(昌铃取消)，F2取消，则对G2是否为“未评级”进行区分；如果G2=未评级 ，H2取F2并加上(合肥未评级)，G2未评级，H2取级别不同。 H3及之下的单元格，只要将H2进行复制粘贴即可。三、 Excel2007新增的多重条件函数AVERAGEIFS、COUNTIFS和 SUMIFS这几个多重条件函数都可以在一定范围内根据条件自行计算，给我们的工作带来了极大的方便。下面以AVERAGEIFS为例，具体介绍如何应用。1. AVERAGEIFS用途：计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。2. AVERAGEIFS语法：AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2)。3. AVERAGEIFS参数：Average_range 是要计算平均值的单元格区域，其中包括数字或包含数字的名称、数组或引用；Criteria_range1, criteria_range2, 是指在哪些单元格区域中指定条件（区域可为1 至 127 个区域）；Criteria1, criteria2, 可以是数字、表达式、单元格引用或文本形式的 1 至 127 个条件，用于定义要对哪些单元格求平均值。【注：在条件中使用通配符，即问号 (?) 和星号 (*)。问号匹配任一单个字符；星号匹配任一字符序列。如果要查找实际的问号或星号，请在字符前键入波形符 ()。】4. AVERAGEIFS应用实例：要进行的操作是计算图15所示表中的各班各科的平均分、及格率和优秀率。 图15 先在图15中表的下方建立一个如图16所示的汇总统计表。图16 然后在E91中输入“=AVERAGEIFS(E2:E88,$D$2:$D$88,$A91)”。其目的是计算初一1班数学平均分；含义是计算区域$D$2:$D$88中所有与$A91相同（即为:初一1班）的E2:E88区域中的平均数。5. 及格率及优秀率的计算：要计算及格率和优秀率，只要在E93、E93中分别输入“=COUNTIFS($D$2:$D$88,$A91,E$2:E$88,=60)/COUNTIFS($D$2:$D$88,$A91)”和“=COUNTIFS($D$2:$D$88,$A91,E$2:E$88,=90)/COUNTIFS($D$2:$D$88,$A91)”。6. 其它班及科目均参照输入即可。四、 可以随意从文本串中提取字符的MID或MIDB函数1. 用途：MID返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。MIDB返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。MIDB函数可以用于双字节字符。2. 语法：MID(text，start_num，num_chars)或MIDB(text，start_num，num_bytes)。3. 参数：Text是包含要提取字符的文本串。Start_num是文本中要提取的第一个字符的位置，文本中第一个字符的start_num为1，以此类推；Num_chars指定希望MID从文本中返回字符的个数；Num_bytes指定希望MIDB从文本中按字节返回字符的个数。4. 应用实例：如下图17所示。要做的工作是在单元格D2输入A2中的中显示从A2中前5个数字，如果数据行很多，一个个地输入不但效率低且易出错。现在我们用MID这个函数来完成这项工作。图16第五部分 实用的自编宏：在日常工作中，我们在对数据进行处理时，往往要对某个单元格的公式进行向下的复制。如果要处理的数据行数很多，操作起来就有些麻烦。而下面的方法可以轻松完成这个操作。首先，我们先录制一个宏。1. 在任何一个打开的Excel文件中，单击“视图”选项卡中“取消隐藏”按钮，在弹出的“取消隐藏”对话框中选择“PERSONAL.XLSB”，再单击“确定”按钮退出对话框。如下图17所示。此时会打开一个名为“PERSONAL.XLSB”的表作为当前表。 图172. 单击“开发工具”选项卡中“录制宏”按钮，弹出“录制新宏”对话框。3. 在“录制新宏”对话框中“宏名”文本框中输入宏的名称自动填充；在“快捷键”文本框中输入一个字母，作为该宏的快捷键（+），方便以后的调用；在“保存在”文本框中输入“个人宏工作薄”，这样今后每次启动Excel时，该宏可以自动加载；之后单击“确定”按钮退出对话框。此时“开发工具”选项卡中“录制宏”按钮变为了“停止录制”按钮；单击“停止录制”按钮。如下图18所示。 图184. 单击“开发工具”选项卡中“宏”按钮，弹出“宏”对话框。选择刚才录制的宏“自动填充”，再单击“编辑”按钮。如下图19所示。 图195. 在随后打开的Visual Basic编辑器窗口的“Sub 自动填充()”到“End Sub”之间输入以下代码（注意：在输入前先将绿色字体部分删除）：Application.ScreenUpdating = FalseOn Error Resume NextDim Myx As Long删除Dim XC As StringDim XR As StringDim XCL As StringMyx = a65536.End(xlUp).RowXC = ActiveCellXR = ActiveCell.RowXCL = ActiveCell.ColumnIf Cells(XR, 1) ThenActiveCell.Select 图20Selection.CopyRange(Cells(XR, 1).Offset(1, XCL - 1), Cells(XR, 1).Offset(Myx - XR, XCL - 1).SelectActiveSheet.PasteEnd IfApplication.ScreenUpdating = True6. “自动填充”宏的功用是：将当前单元格复制到其下第1个单元格至当前单元格所在列最后一个同行A列相应单元格不为空的区域。7. 保存后关闭Visual Basic编辑器窗口；退出Excel。8. 下面介绍使用已经完成的“自动填充”宏。 打开要进行数据处理的文件，选择要进行复制的单元格作为当前单元格。如下图21所示（实例中选择A2作为当前单元格）。 图21 图22 按下快捷键+。效果如上图22所示。工作薄或工作表中如果有函数的单元格较多，会降低其运行速度，所以建议在数据处理完成之后，选中有函数的单元格区域，复制后在原区域（单元格区域不变）直接粘贴值。第五部分 结束语：以上是本人在Excel使用中的一些技巧总结，算是抛砖引玉吧！希望有兴趣的朋友也能将自己的一些使用技巧发布出来供大家学习参考。