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