Excel 中动态筛选报表的制作

admin

Excel 中动态筛选报表的制作
  2002-3-14 21:41:32  Excel Home


Excel中的数据筛选功能，不知道大家有没有用过，“自动筛选”能根据用户指定的条件对相应的数据区域中的数据进行筛选，非常方便。但这个功能的不足之处在于它实际上只是隐藏掉了不符合条件的记录，而非生成一张新的工作表，所以对于筛选后的结果的可操作性不强。“高级筛选”解决了这个问题，但操作起来比较麻烦。而这两个筛选功能都有一个共同的弊端，即不能对源数据区域的改变作出任何响应，比如当你向原来的工作表添加了记录后，必须重新使用一次该命令才能更新原筛选结果，而无法即时地自动改变筛选结果。
那么是否可以利用其它方法来实现对数据的动态筛选呢？答案是肯定的。下面笔者将向大家介绍一种利用Excel公式实现动态筛选报表的方案。
假设现在有销售报表一份，保存在“汇总表”工作表中，如图一所示。我们要做的是，根据地区的不同，将相应的记录筛选出来，独立成表。而且以后当销售报表中的记录增加时，增加的记录也能自动添加到相应的工作表中去。
首先，我们在A列前插入一列，新的A列将用来存放用于筛选数据的索引值。在这里，我们先来筛选地区为华东的记录。在A2单元格中输入公式“=IF(B2="华东",1+A1,A1)”，此公式的含义是：如果当前记录的地区值为华东，则索引值为前一条记录的索引值与1的和，否则其索引值与前一条记录的索引值相同。因为公式对单元格的引用采用的是相对引用法，所以可以通过拖曳方法轻而易举地实现公式的复制。你可以放心地往下拖曳，直到一个有足够大行号的单元格。注意，不要试图向A1单元格输入任何内容，否则下面的公式都将出错。这是因为内容为空的A1单元格决定了索引值的初始值为0，直到从A2开始的单元格公式检测到一个符合条件的记录才能将索引值变为正整数值。这就好像定义一个数值型变量，在未向其赋值以前，其初始值为零一样。不过，为了以后你可以清楚知道此列为哪个地区的索引值，可以插入批注。当然，也可以通过修改A2单元格的公式来使A1单元格不去影响后面的单元格公式，这在你理解了全文的要领以后，应该可以自己办得到。
完成A列的公式后，结果如图二所示。没错，索引值有许多重复的，但是请注意，在索引值相同的记录中，地区为华东的记录总是排在最前面的。这就为后面的筛选工作做好了充分的准备。
新建一工作表，命名为“华东”，在A1中输入“项次”，然后将“汇总表”中C1:G1的内容复制到本工作表中的B1:F1。在A2单元格中输入数字1，然后向下进行拖曳你觉得足够数量的单元格（在示范工作簿中为A11），得到从1到XX的序列数。
在B2单元格中输入公式“=IF(ISNA(VLOOKUP($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（）找不到相应的记录时，其返回值是“#N/A”，此时ISNA（）的值就为TRUE。再来看最外层的IF（），当ISNA（）的值为TRUE时，则公式将返回空值，否则公式返回VLOOKUP（）所返回的值。将B2向右拖曳到F2以复制公式，然后进行修改，即将VLOOKUP（）返回列号改为与本列对应的数值。如C2中将3改为4，D2中将3改为5等。修改完成后，我们会看到，“汇总表”中第一条地区为“华东”的记录已经一字不差的反映在工作表“华东”中了。同时选中B1:F1单元格，向下进行拖曳以复制公式。这样，“汇总表”中所有地区为“华东”的记录就都被筛选出来在工作表“华东”中了。如图三所示。
我们可以尝试着在“汇总表”中添加一条地区为华东的记录，或者将一条地区为华东的记录进行适当的修改，来测试“华东”工作表是否能即时地更新筛选结果。结果如何？
接下去，我们可以用同样的方法，建立“华北”“华南”等地区的筛选工作表。因为在插入行或列时，Excel会自动调整所有公式的引用而无论公式采用的是相对引用和绝对引用，所以对于已完成的公式，大可不必为其担心。
最 后要进行说明的是，对于“汇总表”中的数据，你可以添加和修改，但是最好不要直接删除掉某一条记录，否则，进行索引的公式将因为失去目标单元格而出错。
好了，现在再回过头去理解一下方案中所有的公式，应该都是非常简单了的吧！按照本文的思路，如果出现类似的问题，相信也难不倒你啦！

lon91ong

能图文并茂的话就好了，可以拼个精了！

刘志文

学习一下好方法!!