《10例技巧及动态筛选.doc》由会员分享，可在线阅读，更多相关《10例技巧及动态筛选.doc（4页珍藏版）》请在金锄头文库上搜索。
1、Excel XP精致技巧10例ExcelXP是一款功能非常强大的表格处理软件，相信使用它的人一定不少。笔者在平时的操练过程中总结了一些小的技巧，现奉献给大家，如果你注意使用这些小技巧，会使你的工作方便不少哟。 1.每次选定同一单元格有时，为了测试某个公式，需要在某个单元格内反复输入多个测试值。但每次输入一个值后按下Enter键查看结果，活动单元格就会默认移到下一个单元格上，必须用鼠标或上移箭头重新选定原单元格，极不方便。如果你按Ctrl+Enter组合键，则问题会立刻迎刃而解，既能查看结果，而当前单元格仍为活动单元格。2.自动打开用户工作簿我们只要将某个需要自动打开的工作薄（如a.xls）的快
2、捷方式放到C:ProgramFilesMicrosoftOfficeOffice10XLStart文件夹中，以后每次启动时，Excel都会自动打开相应工作簿。3.在同一对应单元格内输入相同内容在按下Ctrl键的同时，单击需要输入相同内容的不同工作表名称（如Sheet1、Sheet3、Sheet4），在其中任意一个工作表中的某一单元格（如B8）中输入内容，按下Enter键后，该内容即一次性输入到上述选定的所有工作表的同一单元格（B8）中了。4.生成备份工作薄对新（老）工作薄执行“文件保存（另存为）”命令，打开“另存为”对话框，按右上角的“工具”旁的下拉按钮，选“常规”选项，在随后弹出的对话框中，
3、选中“生成备份”选项，确定保存。以后修改该工作薄（a.xls）后再保存，系统会自动生成一份名称为“a的备份.xlk”的备份工作薄，且能直接打开使用。友情提醒：如果将上述功能加到某个模板中，则使用该模板建立工作薄后，就不需要进行上述操作，而快速生成备份工作薄了。5.同时打开多个工作薄有时我们可能需要打开多个工作薄，下面的方法也许对你有所帮助：选择“文件”“打开”命令，按住Shift键或Ctrl键，并用鼠标选择彼此相邻或不相邻的多个文件，然后按“确定”按钮，就可以一次打开选中的多个工作薄。执行“开始”“运行”命令，在对话框中输入Excel，空格，后面加所要打开工作薄文件的绝对路径（每个文件之间用空
4、格分隔），“确定”后也可以打开多个工作薄。新建一个文件夹，将需要打开的多个工作薄文件放在里面，然后打开Excel，选择“工具”“选项”命令，在“常规”标签中，将替补启动目录用“浏览”的方法指向该文件夹，以后当你再次启动Excel时，就会把那个目录中的所有文件一起打开。启动ExcelXP，打开多个工作薄，选择“文件”“保存工作区”命令，取名（a.xlw）、确定保存。以后只要打开该工作区文档，即可打开上述多个工作薄（注意：工作区文件只是保存设置，并不保存文件，所以你必须对每个文件所做的更改另行保存）。6.快速选定包含公式的单元格选择“编辑”“定位”命令，在随后弹出的对话框中，按“定位条件”按钮，选
5、中“公式”选项后确定，即可一次性选中包含“公式”的所有单元格。7.快捷输入函数的参数如果你只记得函数的名称，而记不清它的所有格式，你可以先在单元格中输入一个等号及函数名，然后按下Ctrl+A键，Excel则自动打开“函数参数”对话框，引导你快速输入函数的参数，免去了刻意记忆的痛苦。8.利用IF函数避免出错信息当我们在C8单元格输入公式：=A8/B8，当B8单元格没有输入数据时，C8单元格则会出现“#DIV/0！”的错误信息。如果将公式改为：=IF（B1=0,A1/B1），就不会显示错误信息了；同样，如果我们在C8单元格中输入：=A8B8，当A8、B8没有输入数值时，C8则显示出“0”，如果将公
6、式改为：=IF（AND（A2=，B2=）,A2B2），这样，如果A8与B8单元格均没有输入数值时，C8也不会显示“0”了。9.在单元格中批量插入固定字符现在新的身份证号码将旧身份证号码的年份由2位表示改为4位表示，如果我们要将年份的前两位（19）插入旧身份证号码中，可以这样做（假定旧身份号码保存在A列中）：在B1、C1、D1单元格中分别输入公式：=LEFT(A1,6)、=RIGHT(A1,9)、=B1&19&C1，将上述公式复制到B、C、D列的其他单元格中，即可将19插入旧身份证号码中，并显示到D列中。10.让序号原地不动有时我们对数据进行排序后，序号全乱了。如果先在序号列与正文列之间插入一个
7、空列，再排序时，序号就不会乱了。注意位于序号前面列中的内容均不发生改变；为了不影响显示美观和正常打印，可将这一空列隐藏起来。 Excel 中动态筛选报表的制作 加入时间：2002年8月12日 显示次数：4430 打包邮递 推荐给朋友 投票评论 Excel中的数据筛选功能，不知道大家有没有用过，“自动筛选”能根据用户指定的条件对相应的数据区域中的数据进行筛选，非常方便。但这个功能的不足之处在于它实际上只是隐藏掉了不符合条件的记录，而非生成一张新的工作表，所以对于筛选后的结果的可操作性不强。“高级筛选”解决了这个问题，但操作起来比较麻烦。而这两个筛选功能都有一个共同的弊端，即不能对源数据区域的改变
8、作出任何响应，比如当你向原来的工作表添加了记录后，必须重新使用一次该命令才能更新原筛选结果，而无法即时地自动改变筛选结果。 那么是否可以利用其它方法来实现对数据的动态筛选呢？答案是肯定的。下面笔者将向大家介绍一种利用Excel公式实现动态筛选报表的方案。 假设现在有销售报表一份，保存在“汇总表”工作表中，如图一所示。我们要做的是，根据地区的不同，将相应的记录筛选出来，独立成表。而且以后当销售报表中的记录增加时，增加的记录也能自动添加到相应的工作表中去。 首先，我们在A列前插入一列，新的A列将用来存放用于筛选数据的索引值。在这里，我们先来筛选地区为华东的记录。在A2单元格中输入公式“=IF(B2
9、=华东,1+A1,A1)”，此公式的含义是：如果当前记录的地区值为华东，则索引值为前一条记录的索引值与1的和，否则其索引值与前一条记录的索引值相同。因为公式对单元格的引用采用的是相对引用法，所以可以通过拖曳方法轻而易举地实现公式的复制。你可以放心地往下拖曳，直到一个有足够大行号的单元格。注意，不要试图向A1单元格输入任何内容，否则下面的公式都将出错。这是因为内容为空的A1单元格决定了索引值的初始值为0，直到从A2开始的单元格公式检测到一个符合条件的记录才能将索引值变为正整数值。这就好像定义一个数值型变量，在未向其赋值以前，其初始值为零一样。不过，为了以后你可以清楚知道此列为哪个地区的索引值，可
10、以插入批注。当然，也可以通过修改A2单元格的公式来使A1单元格不去影响后面的单元格公式，这在你理解了全文的要领以后，应该可以自己办得到。 完成A列的公式后，结果如图二所示。没错，索引值有许多重复的，但是请注意，在索引值相同的记录中，地区为华东的记录总是排在最前面的。这就为后面的筛选工作做好了充分的准备。 新建一工作表，命名为“华东”，在A1中输入“项次”，然后将“汇总表”中C1:G1的内容复制到本工作表中的B1:F1。在A2单元格中输入数字1，然后向下进行拖曳你觉得足够数量的单元格（在示范工作簿中为A11），得到从1到XX的序列数。 在B2单元格中输入公式“=IF(ISNA(VLOOKUP($
11、A2,汇总表!$A$2:$G$99,3,0),VLOOKUP($A2,汇总表!$A$2:$G$99,3,0)”。这是一个多层嵌套的公式，让笔者将其用意慢慢道来：VLOOKUP($A2,汇总表!$A$2:$G$99,3,0)是指根据A列的索引值，从“汇总表”的A2:G99单元格区域（注意，这个区域最好设置得足够大，免得日后“汇总表”中的记录数超出范围而需要进行修改）中找到相应的记录，并返回该记录第三个字段的值，寻找方式为精确查找。对于索引值相同的记录，VLOOKUP（）只会响应排在第一个的记录，而不去管其他的记录，现在放心了吧！ISNA（）则是对返回的值进行判断，当VLOOKUP（）找不到相应的
12、记录时，其返回值是“#N/A”，此时ISNA（）的值就为TRUE。再来看最外层的IF（），当ISNA（）的值为TRUE时，则公式将返回空值，否则公式返回VLOOKUP（）所返回的值。将B2向右拖曳到F2以复制公式，然后进行修改，即将VLOOKUP（）返回列号改为与本列对应的数值。如C2中将3改为4，D2中将3改为5等。修改完成后，我们会看到，“汇总表”中第一条地区为“华东”的记录已经一字不差的反映在工作表“华东”中了。同时选中B1:F1单元格，向下进行拖曳以复制公式。这样，“汇总表”中所有地区为“华东”的记录就都被筛选出来在工作表“华东”中了。如图三所示。 我们可以尝试着在“汇总表”中添加一条地区为华东的记录，或者将一条地区为华东的记录进行适当的修改，来测试“华东”工作表是否能即时地更新筛选结果。结果如何？ 接下去，我们可以用同样的方法，建立“华北”“华南”等地区的筛选工作表。因为在插入行或列时，Excel会自动调整所有公式的引用而无论公式采用的是相对引用和绝对引用，所以对于已完成的公式，大可不必为其担心。 最 后要进行说明的是，对于“汇总表”中的数据，你可以添加和修改，但是最好不要直接删除掉某一条记录，否则，进行索引的公式将因为失去目标单元格而出错。 好了，现在再回过头去理解一下方案中所有的公式，应该都是非常简单了的吧！按照本文的思路，如果出现类似的问题，相信也难不倒你啦！
