Excel秘技36招.doc

Excel秘技36招宝典秘技一快速选取单元格区域在Excel的操作中，选取特定单元格区域可以说是最基本的步骤了。如果被选区域比较小，可以简单用鼠标来完成定位。但是如果选择的区域超过了屏幕可以显示的范围，用鼠标去选择常常会出现无法准确定位的情况。这个时候，我们可以使用键盘来操作。如图1所示，目标是选定区域A1：0466。图1 快速选定单元格区域我们可以先选中单元格A1。然后按住键，使用方向键 或使用键来扩展选择区域A1：A466。接下来继续按住键，再按右箭头键直到A1：O466区域全部选中，完成任务。同理，我们也可以先选中横列A1：O1，再按住键，用下箭头选定区域A1：O466。宝典秘技二快速填充空白单元格实际工作中，我们经常会遇到需要用空值来表示数据缺失的情况，例如，员工缺勤、学生缺考、产品更换造成某月交易额缺失等等。空值并不代表没有意义，在很多时候我们需要用特定的数值去代替空值。“编辑”“查找”“替换”是我们已经很熟悉的方法了，不过这种方法的不足之处在于它无法区别对待数据区域内的空值和数据区域外的空值。这里，我们可以使用“定位”来完成填充任务。如图2所示，客户的话费消费额中有很多的空白单元格，需要将其定义为0。图2快速填充空白单元格单击“编辑”“定位”，出现如图3所示的对话框。在“引用位置”中选择P2：AA466，单击“定位条件”，选择空值。 图3 定位 图4 定位条件完成这一步操作后，在指定区域内的所有空单元格都被选定。输入“0”，按Ctrl Enter组合键，这个键的作用事在多个单元格中同时输入数据。这时，所有的空白单元格就都被零填充了。宝典秘技三神奇的快捷键（一）Excel的许多快捷键可以非常方便的帮助我们操作数据，您是否了解它们的妙用呢？1 改变撤消步骤Ctrl+Z是Windows下统一的撤消快捷键，如果我们进行了误操作，只需轻点快捷键，就可以取消原来的操作。需要说明的是，Ctrl+Z组合可以用于撤消之前的16个步骤。2 恢复上一步在撤消完成以后，再按Ctrl+Y，就可以恢复之前的更改。但恢复操作必须紧跟在撤消操作之后，否则就无效了。3 重复操作很多时候，我们需要在Excel里面重复操作一些步骤，例如，我们要在一张数据表中隔行插入空白行，我们可以通过单击一列，然后插入行的方法解决。但是，如果我们要插入的是120个空白行呢？可以在完成一个插入的工作以后，在第二个需要插入行的地方按Ctrl+Y组合键就可以了，省时省力。此外，如果我们设置了一个比较复杂的单元格格式，也可以用Ctrl+Y把同样的操作应用到其他单元格中。与格式刷不同，重复操作只复制刚才所做的格式设置。与撤消不同，重复仅限于重复上一步的操作。宝典秘技四神奇的快捷键（二）1 查找与替换Ctrl+H组合键可以帮助我们直接打开“查找替换”对话框，而不需从菜单中选入。2 绝对引用与相对引用在单元格中输入公式的时候，我们经常会遇到对单元格绝对引用和相对引用的问题。例如，对单元格A6的绝对引用是$A$6，相对引用是A6，还可以根据需要设定A$6、$A6两种形式。对此，我们可以先将鼠标移到公式编辑区，单击选中单元格标识，然后单击F4键，就可以完成绝对引用了。连续单击F4键，就可以在绝对引用、相对引用之间进行转换了。3 在不同单元格中快速输入同一数内容选定单元格区域，输入值，然后按Ctrl Enter键，即可实现在选定的单元格区域中一次性输入相同的值。宝典秘技五“自动更正”输入统一的文本1) 执行“工具”“自动更正”命令，打开“自动更正”对话框。2) 在“替换”下面的方框中输入“m”（可以任意设定单个字母或字母组合），在“替换为”下面的方框中输入“MBI（中国）责任有限公司”，再单击“添加”和“确定”按钮，如图5所示。图5以后如果需要输入上述文本时，只要输入“m”字符，此时可以不考虑“m”的大小写，然后确认一下就可以了。如此就为我们节省了大量的录入时间。宝典秘技六建立“常用文档”新菜单我们可以在菜单栏上新建一个“常用文档”菜单，将常用的工作簿文档添加到其中，方便随时调用。1) 在工具栏空白处右击鼠标，选“自定义”选项，打开“自定义”对话框。在“命令”标签中，选中“类别”下的“新菜单”项，再将“命令”下面的“新菜单”拖到菜单栏。按“更改所选内容”按钮，在弹出菜单的“命名”框中输入一个名称（如“常用文档”）如图6所示。图62) 再在“类别”下面任选一项（如“插入”选项），在右边“命令”下面任选一项（如“超链接”选项），将它拖到新菜单（常用文档）中，并仿照上面的操作对它进行命名（如“工资表”等），建立第一个工作簿文档列表名称。3) 选中“常用文档”菜单中某个菜单项（如“工资表”等），右击鼠标，在弹出的快捷菜单中，选“分配超链接打开”选项，打开“分配超链接”对话框（图7）。通过按“查找范围”右侧的下拉按钮，定位到相应的工作簿（如“工资.xls”等）文件夹，并选中该工作簿文档。图7 重复上面的操作，将菜单项和与它对应的工作簿文档超链接起来。以后需要打开“常用文档”菜单中的某个工作簿文档时，只要展开“常用文档”菜单，单击其中的相应选项即可。在日常的工作中，使用这种方法可以极大地提高工作效率。宝典秘技七快速输入数据序列如果你需要输入诸如表格中的项目序号、日期序列等一些特殊的数据系列，千万别逐条输入，为何不让Excel自动填充呢？在第一个单元格内输入起始数据，在下一个单元格内输入第二个数据，选定这两个单元格，将光标指向单元格右下方的填充柄，沿着要填充的方向拖动填充柄，拖过的单元格中会自动按Excel内部规定的序列进行填充。如果能将自己经常要用到的某些有规律的数据（如办公室人员名单），定义成序列，以备日后自动填充，岂不是一劳永逸！选择“工具”菜单中的“选项”命令，再选择“自定义序列”标签，在输入框中输入新序列，注意在新序列各项间要输入半角符号的逗号加以分隔（例如：张三，李四，王二），单击“增加”按钮将输入的序列保存起来。 宝典秘技八让数据按需排序如果你要将员工按其所在的部门进行排序，这些部门名称不是按拼音顺序，也不是按笔画顺序，怎么办? 可采用自定义序列来排序。1) 选打开“格式”“选项”，打开“选项”对话框，进入“自定义序列”标签中，在“输入序列”下面的方框中输入部门排序的序列（如“销售部、策划部、广告部、开发部”），单击“添加”和“确定”按钮退出（图8）。图82) 选中“部门”列中任意一个单元格，执行“数据”“排序”命令，打开“排序”对话框，单击“选项”按钮，弹出“排序选项”对话框，按其中的下拉按钮，选中刚才自定义的序列，按两次“确定”按钮返回，所有数据就按要求进行了排序。 图9 图10宝典秘技九同时查看不同工作表中多个单元格内的数据公司在制定下一年的销售计划时，总是要需要参考前几年的销售业绩并且结合自身情况来最终确定。也就是说，我们在编辑新的销售计划工作表时，同时需要查看其它工作表中（上一年度的年终销售业绩等）的数据。而来回查阅几张工作表较为麻烦，能不能让Excel像一个小贴士一样自动呈现我们需要的几个数据以供参考呢？答案是肯定的，我们可以利用Excel的“监视窗口”功能来实现。执行“视图”“工具栏”“监视窗口”命令，打开“监视窗口”，单击其中的“添加监视”按钮，展开“添加监视点”对话框，用鼠标选中需要查看的单元格后，再单击“添加”按钮。重复前述操作，添加其它“监视点”（图11）。图11以后，无论在哪个工作表中，只要打开“监视窗口”，即可查看所有被监视点单元格内的数据和相关信息。宝典秘技十利用公式审核工具查看数据出处Excel 2003有一个秘密武器“公式审核”工具，它可以将任一单元格中数据的来源和计算结果的去处显示得清清楚楚、明明白白。非常方便我们查错和检查公式。单击“工具”“公式审核”选项，并点击“显示公式审核工具栏”（图12）。图12圈释无效数据新批注追踪错误取消所有追踪箭头公式求值显示监视窗口清除无效数据标识圈移去引用单元格追踪箭头追踪从属单元格移去引用单元格追踪箭头追踪引用单元格错误检查图13在“公式审核”子菜单，然后单击“显示监视窗口”按钮。右击我们想跟踪的单元格，并在快捷菜单中选择“添加监视点”。这时，“监视窗口”的列表中就出现了被Excel监视的单元格及其公式了。以后，只要我们双击“监视窗口”中的该条目，被监视的单元格就会不请自来了。如果我们表格中某个数据无效或语法不当的话，也可以点击“公式审核”工具栏上的“圈释无效数据”按钮来让Excel自动帮我们检查纰漏。提示：当包含有指向其他工作簿的单元格被监视时，只有当所有被引用的工作簿都打开时，才能在“监视窗口”的列表中显示出来。宝典秘技十一用连字符“&”来合并文本在一个Excel工作表中，A列是MBI公司名称，B列是MBI公司参与该项目的员工名单，C列是合作方公司华宇，D列是华宇参与该项目的员工名单（图14）。现在需要将MBI公司和华宇的名字合并到一个单元格中，并且将员工名单整理为一列。图141) 在D列后面插入两个空列（E、F列），然后在E2单元格中输入公式：“=A2&C2”。再次选中E2单元格，用“填充柄”将上述公式复制到E列下面的单元格中，A、C列的内容即被合并到E列对应的单元格中（图15）。图15选中E列，执行“复制”操作，然后选中F列，执行“编辑选择性粘贴”命令，打开“选择性粘贴”对话框，选中其中的“数值”选项，按下“确定”按钮，E列的内容（不是公式）即被复制到F列中（图16）。图162) 将B列和D列的员工名单复制粘贴到G列，删除A、B、C、D、E列（图17）。图17提示：完成第1、2步的操作，合并效果已经实现，但此时如果删除B、C、D列，公式会出现错误。故须进行第3步操作，将公式转换为不变的“值”。宝典秘技十二制作“专业符号”工具栏1) 执行“工具”“宏”“录制新宏”命令，打开“录制新宏”对话框，输入宏名，如“baogua”。并将宏保存在“个人宏工作簿”中，然后“确定”开始录制，还可以根据需要设置相应的快捷键（图18）。 图18 图19选中“录制宏”工具栏上的“相对引用”按钮，然后将需要的特殊符号输入到某个单元格中，再单击“录制宏”工具栏上的“停止”按钮，完成宏的录制（图19）。仿照上面的操作，录制好其它特殊符号的输入“宏”。2) 打开“工具”“自定义”对话框，在“工具栏”标签中，单击“新建”按钮，弹出“新建工具栏”对话框，输入名称“专业符号”，确定后，即在工作区中出现一个工具条（图20）。图20切换到“命令”标签中，选中“类别”下面的“宏”，将“命令”下面的“自定义按钮”项拖到“专业符号”栏上，有多少个特殊符号就拖多少个按钮，这里，按照宝瓜的要求是11个专业符号（图21）。图21选中其中一个“自定义按钮”，单击右键，在命名栏里为其命名。右击命名后的按钮，如“baogua”，在随后弹出的快捷菜单中，选“指定宏”选项，打开“指定宏”对话框，选中相应的宏，如“baogua”，确定退出（图22）。图22重复此步操作，将按钮与相应的宏链接起来。关闭“自定义”对话框，以后可以像使用普通工具栏一样，使用“专业符号”工具栏，向单元格中快速输入专业符号了。宝典秘技十三快速打印员工工资条 “如何打印员工工资表”、“如何打印学生成绩条”这样的问题，是任何一个做财务管理和学生管理工作的人最常见的问题。因为每个员工的工资或者每个学生的成绩都是需要对其他人保密的，打印出来的只能是其个人资料。虽说可以用如果有不少人采取录制宏或VBA的方法来实现，这里我们介绍一种用函数实现的简便方法。打开一张工资表（图23），在页面设置中选中工作表选项，设置打印工作表行标题（顶端标题行或顶端标题列），选好工资条的条头（图23）。图23在每一个人之间插入行分页符，再把页长设置成工资条的高度即可。打印的效果可以参见预览效果（图24）。图24宝典秘技十四把数据彻底隐藏起来工作表的部分单元格中的内容不想让浏览者查阅，最好将它隐藏起来。1) 选中需要隐藏内容的单元格区域，执行“格式”“单元格”命令，打开“单元格格式”对话框，在“数字”标签的“分类”下面选中“自定义”选项，然后在右边“类型”下面的方框中输入“;”（三个英文状态下的分号）（图25）。图252) 再切换到“保护”标签下，选中其中的“隐藏”选项，按“确定”按钮退出。执行“工具”“保护”“保护工作表”命令，打开“保护工作表”对话框，设置好密码后，“确定”返回（图26）。图26经过这样的设置以后，上述单元格中的内容不再显示出来。我们可以看到，这时候被隐藏起来的单元格区域是不允许删除和做其他修改的。解除保护的过程与上面正好相反，先撤消工作表保护，然后修改数据的显示方式即可。提示：在“保护”标签下，请不要清除“锁定”前面复选框中的“”号，这样可以防止删除隐藏起来的数据。宝典秘技十五成组填充多张表格的固定单元格我们知道每次打开Excel，软件总是默认打开多张工作表。由此就可看出Excel除了拥有强大的单张表格的处理能力，更适合在多张相互关联的表格中协调工作。要协调关联，当然首先就需要同步输入。因此，在很多情况下，都会需要同时在多张表格的相同单元格中输入同样的内容。那么如何对表格进行成组编辑呢?首先我们单击第一个工作表的标签名“Sheet1”，然后按住Shift键，单击最后一张表格的标签名“Sheet3”（如果我们想关联的表格不在一起，可以按住Ctrl键进行点选）。此时，我们看到Excel的标题栏上的名称出现了“工作组”字样，我们就可以进行对工作组的编辑工作了。在需要一次输入多张表格内容的单元格中随便写点什么，我们发现，“工作组”中所有表格的同一位置都显示出相应内容了（图27）。 图27 图28但是，仅仅同步输入是远远不够的。比如，我们需要将多张表格中相同位置的数据统一改变格式该怎么办呢?首先，我们得改变第一张表格的数据格式，如设置A2中的格式为“文本”，再单击“编辑”“填充”选项，然后在其子菜单中选择 “至同组工作表”。这时，Excel会弹出 “填充成组工作表”的对话框（图28），在这里我们选择“格式”一项，单击“确定”后，同组中所有表格该位置的数据格式都改变了。宝典秘技十六快速比较不同区域的数值比较不同区域的数据是否相同，是工作中经常会遇到的问题之一。对此，我们可以用一个EXACT函数轻松解决。如图29所示，需要比较区域AC2：AE28和区域AG2：AI28的数据是否相同。图29 比较不同区域数据选中AC2：AC28区域，单击“格式”“条件格式”，出现图30所示的对话框。图30 条件格式在条件1（1）中选择“公式”，在右边的文本框里输入公式：=OR（EXACT（AC2，AG$2:AG$28）=FALSE单击“格式”按钮，在“单元格格式”对话框中，选择“图案”底色为红色，单击确定。回到条件格式对话框，单击确定。完成设置。同理，设置AD和AE列（可以使用格式刷来快速完成）。结果如图31所示。图31宝典秘技十七在工作日历中突出显示周休日很多人都喜欢用Excel制作工作日历，如图所示，是一列日期，如何把其中的周末标注出来呢？ 图32 图33单击A2，拖动鼠标到A20，选定A2：A20，单击菜单“格式”“条件格式”，在条件格式的对话框中，在条件1（1）中选择“公式”，在右边的文本框里输入公式：=WEEKDAY（A2，2） 5单击“格式”按钮，在“单元格格式”对话框中，选择“图案”底色为绿色，单击确定。回到条件格式对话框，单击确定。完成设置。结果如图33所示。宝典秘技十八改变文本的大小写在Excel中，为表格处理和数据运算提供最强大支持的不是公式，也不是数据库，而是函数。Excel中的函数不只是针对数字，其实只要是写进表格中的内容，Excel都有对它编辑的特殊函数。例如改变文本的大小写。在Excel 2003中，至少提供了三种有关文本大小写转换的函数。它们分别是：“=UPPER（源数据格）”，将文本全部转换为大写；“=LOWER（源数据格）”，将文本全部转换成小写；“=PROPER（源数据格）”，将文本转换成适当的大小写，如让每个单词的首字母为大写等。例如，我们在一张表格的A1单元格中输入小写的“excel”，然后在目标单元格中输入“=UPPER（A1）”，回车后得到的结果将会是“EXCEL”。同样，如果我们在A3单元格中输入“mr.right”，然后我们在目标单元格中输入“=PROPER（A3）”，那么我们得到的结果就将是“Mr.Right”了。宝典秘技十九提取字符串中的特定字符除了直接输入外，从已存在的单元格内容中提取特定字符输入，绝对是一种省时又省事的方法，特别是对一些样式雷同的信息更是如此，比如员工名单、籍贯等信息。如果我们想快速从某一单元格中提取字符的话，最好使用“=RIGHT（源数据格，提取的字符数）”函数，它表示从A4单元格最右侧的字符开始提取2个字符输入到此位置。当然，也可以从左侧提取字符，则要使用“=LEFT（源数据格，提取的字符数）”函数。还有一种情况，我们不从左右两端开始，而是直接从数据中间提取几个字符。比如我们要想从内容为“湖北省武汉大学”的A5单元格中提取“武汉”两个字时，就只须在目标单元格中输入“=MID（A5,4,2）”就可以了。意思是：在A5单元格中提取第4个字符后的两个字符，也就是第4和第5两个字。宝典秘技二十随机排序有些时候，我们并不希望按照既定的规则来对数据进行排序，而是希望数据能够随机排列，特别是在决定随机抽样的情况下，更是如此。如图34所示，我们需要从所有的客户中随机抽取10人参与活动。图34这里，我们只需要在前面插入一列，命名为“抽样”。在新插入的A列中，选择A2单元格，输入公式“=RAND（）”，按住Shift键，下拉复制公式。单击A2，再单击工具栏中的“降序排列”按钮，就能够对客户进行随机排序。并且选取排在前面的10个人作为我们活动的对象。因为RAND函数是易失性函数，所以每次排序的结果都会不同，每个客户入选的可能性都是相同的。结果如图35所示。图35宝典秘技二十一用“视面管理器”保存多个打印页面对于一些工作表，我们经常需要打印其中不同的区域，可以用“视面管理器”来管理。1.打开需要打印的工作表，用鼠标在不需要打印的行（或列）标上拖拉，选中它们再右击鼠标，在随后出现的快捷菜单中，选“隐藏”选项，将不需要打印的行（或列）隐藏起来。2.执行“视图”“视面管理器”命令，打开“视面管理器”对话框，单击“添加”按钮，弹出“添加视面”对话框，输入一个名称（如“常用打印资料”）后，单击“确定”按钮。3.将隐藏的行（或列）显示出来，并重复上述操作，“添加”好其它的打印视面。4.以后需要打印某种表格时，打开“视面管理器”，选中需要打印的表格名称，单击“显示”按钮，工作表即刻按事先设定好的界面显示出来，简单设置、排版一下，按下工具栏上的“打印”按钮，一切就OK了。宝典秘技二十二使用动态引用Excel中输入公式的时候，使用动态引用会极大地提高效率。例如，图36中显示了客户的详细信息，我们需要根据用户类型和职业来计算出一个类型的用户的话费总额。图36首先，我们需要为单元格区域设定动态名称，这样就不用在每次追加记录的时候都调整引用位置了。单击“插入”“名称”“定义”（或者按Ctrl+F3组合键）在定义名称对话框中，在“在当前工作簿中的名称”文本框里输入名称“用户类型”，在“引