加载中…26•明修栈道暗渡陈仓---定义名称实现动态高级筛选
(2012-09-04 17:32:23)
标签：
excel技巧自动筛选名称 |
分类： 报表显示问题 |
工作中，经常需要从大量数据中筛选出符合某些条件的数据进行打印，EXCEL为我们提供了高级筛选功能。但是，当筛选条件是变量时，每次执行高级筛选的相同步骤就显得较为繁琐，而要编写VBA代码执行重复的高级筛选步骤，对普通用户又有些遥不可及。如何才能让工作即快捷又方便呢？EXCEL的一些隐藏名称为我们提供了实现“可变条件高级筛选”的方便，且看我如何“明修栈道，暗渡陈仓”。
一、明修栈道，布置条件区域
如图1，“筛选”工作表数据共有2000多条，要求每月按不同的库房打印分类数据。如，1号库房仓储01，02，21，23，24，29大类物料，那么就筛选出这几大类数据打印为“1号库房物资盘点清单”；5号库房仓储20，40，54大类物料，那么就筛选出这几大类数据打印为“5号库房物资盘点清单”。
如图2，另建立一个“库房”工作表，库房号放在第一行，第二行输入筛选条件的字段名“大类”，各库房仓储类别依次输入到下面的单元格中。这里将作为高级筛选的条件区域。
将“筛选”工作表G1：H1单元格填充为黄色，G1单元格输入“库房号：”，H1单元格输入“2”。将A1：E1区域合并单元格，设置字号为18号，加粗，输入公式“=TEXT(H1,"[DBNum1]0")&"号库房物资盘点清单"”，确定后如图3所示。
二、暗渡陈仓，定义动态名称
选择任一区域，依次选择“页面布局－打印区域－设置打印区域”，将选择的区域设置成打印区域。依次选择“页面布局－打印标题－工作表－顶端标题行”，将其设置或选择为$1:$2。依次选择“数据－高级筛选－条件区域”，选择任意一个区域，确定，退出。
依次选择“公式－名称管理器”。由于上一步的设置，在名称管理器中会出现三个名称，其中：
Print_Titles设置的是“打印标题”，可以不用管它。
选定名称“Print_Area”，在“引用位置”将其内容更改为“=OFFSET(筛选!$A$2,,,COUNTA(筛选!$A$2:$A$65536),5)”，点击左边的“对钩”确定。
选定名称“Criteria”，在“引用位置”将其内容更改为“=OFFSET(库房!$A$1,1,MATCH(筛选!$H$1,库房!$1:$1)-1,COUNTA(INDIRECT("库房!c"&MATCH(筛选!$H$1,库房!$1:$1),))-1)”，点击左边的“对钩”确定。
另新建名称“库房号”，引用位置为“=OFFSET(库房!$B$1,,,,COUNTA(库房!$B$1:$IV$1))”。
新建名称“Database”，引用位置为“=OFFSET(筛选!$A$2,,,COUNTA(筛选!$A$2:$A$65536),5)”。
以上引用位置的公式中最大行（65536）和最大列（IV）都是EXCEL2003版的极限值，在EXCEL2010版中可视具体情况更改。
设置好名称后，如图4所示
三、环环相扣，建立动态连接
选择“筛选”工作表的H1单元格，依次选择“数据－数据有效性”，将“允许”选项设置成“序列”，在“来源”选项输入公式“=库房号”，将“忽略空值”和“提供下拉箭头”钩选，确定，退出。
经过以上设置，H1单元格旁边会出现一个下拉小箭头，点击箭头，会显示“库房”工作表的所有库房号，当选择不同的库房号后，合并单元格A1的标题会相应改变，如图5所示。
当“库房”工作表的库房号有所增加或减少时，“筛选”工作表的下拉箭头内容也会动态增减，如图6所示。
依次点击“数据－高级筛选”，打开“高级筛选”对话框，会发现“列表区域”和“条件区域”也会自动设置，不需再手动填写，如图7所示。
至此在“高级筛选”对话框如果点击确定，已经可以实现动态筛选的目的，但我们如果再进一步，可以将工作变得更加快捷。
四、巧步机关，实现自动筛选
依次点击“开发工具－插入－表单控件”，选择“按钮”工具，在“筛选”工作表插入一个按钮（如找不到“开发工具”，可依次点击“文件－选项－自定义功能区”，在右侧的“主选项卡”中找到“开发工具”，将其前面的钩选中即可），在“指定宏”对话框，将“宏名”更改为“筛选”，点击右侧的“录制”，确定。依次点击“数据－高级筛选－确定”，将筛选动作录制为一段宏。记着一定要在录制完毕后，点击“开发工具－停止录制”。按ALT+F11键，打开VBE，双击“模块1”，在右侧的代码窗口会发现这样一段代码：
Sub 筛选()
'筛选 宏
Range("A2:E2057").AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:= _
Sheets(" 库房").Range("F2:F5"),
Unique:=False
End Sub
将其更改为
S ub 筛选()
Range("Database").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("Criteria")
End Sub
右键单击“按钮”－“编辑文字”，将按钮文字更改为“筛选”。完成后，如图8所示，当在H1单元格选择不同的库房号后，直接点击“筛选”按钮，会自动将符合条件的内容筛选出来。当需要打印时，由于设置了动态打印范围Print_Area，所以不用担心旁边黄色单元格区域和“筛选”按钮被打印出来。剩下的工作就是将文件保存为“EXCEL启用宏的工作簿”。
一、明修栈道，布置条件区域
如图1，“筛选”工作表数据共有2000多条，要求每月按不同的库房打印分类数据。如，1号库房仓储01，02，21，23，24，29大类物料，那么就筛选出这几大类数据打印为“1号库房物资盘点清单”；5号库房仓储20，40，54大类物料，那么就筛选出这几大类数据打印为“5号库房物资盘点清单”。
如图2，另建立一个“库房”工作表，库房号放在第一行，第二行输入筛选条件的字段名“大类”，各库房仓储类别依次输入到下面的单元格中。这里将作为高级筛选的条件区域。
将“筛选”工作表G1：H1单元格填充为黄色，G1单元格输入“库房号：”，H1单元格输入“2”。将A1：E1区域合并单元格，设置字号为18号，加粗，输入公式“=TEXT(H1,"[DBNum1]0")&"号库房物资盘点清单"”，确定后如图3所示。
二、暗渡陈仓，定义动态名称
选择任一区域，依次选择“页面布局－打印区域－设置打印区域”，将选择的区域设置成打印区域。依次选择“页面布局－打印标题－工作表－顶端标题行”，将其设置或选择为$1:$2。依次选择“数据－高级筛选－条件区域”，选择任意一个区域，确定，退出。
依次选择“公式－名称管理器”。由于上一步的设置，在名称管理器中会出现三个名称，其中：
Print_Titles设置的是“打印标题”，可以不用管它。
选定名称“Print_Area”，在“引用位置”将其内容更改为“=OFFSET(筛选!$A$2,,,COUNTA(筛选!$A$2:$A$65536),5)”，点击左边的“对钩”确定。
选定名称“Criteria”，在“引用位置”将其内容更改为“=OFFSET(库房!$A$1,1,MATCH(筛选!$H$1,库房!$1:$1)-1,COUNTA(INDIRECT("库房!c"&MATCH(筛选!$H$1,库房!$1:$1),))-1)”，点击左边的“对钩”确定。
另新建名称“库房号”，引用位置为“=OFFSET(库房!$B$1,,,,COUNTA(库房!$B$1:$IV$1))”。
新建名称“Database”，引用位置为“=OFFSET(筛选!$A$2,,,COUNTA(筛选!$A$2:$A$65536),5)”。
以上引用位置的公式中最大行（65536）和最大列（IV）都是EXCEL2003版的极限值，在EXCEL2010版中可视具体情况更改。
设置好名称后，如图4所示
三、环环相扣，建立动态连接
选择“筛选”工作表的H1单元格，依次选择“数据－数据有效性”，将“允许”选项设置成“序列”，在“来源”选项输入公式“=库房号”，将“忽略空值”和“提供下拉箭头”钩选，确定，退出。
经过以上设置，H1单元格旁边会出现一个下拉小箭头，点击箭头，会显示“库房”工作表的所有库房号，当选择不同的库房号后，合并单元格A1的标题会相应改变，如图5所示。
当“库房”工作表的库房号有所增加或减少时，“筛选”工作表的下拉箭头内容也会动态增减，如图6所示。
依次点击“数据－高级筛选”，打开“高级筛选”对话框，会发现“列表区域”和“条件区域”也会自动设置，不需再手动填写，如图7所示。
至此在“高级筛选”对话框如果点击确定，已经可以实现动态筛选的目的，但我们如果再进一步，可以将工作变得更加快捷。
四、巧步机关，实现自动筛选
依次点击“开发工具－插入－表单控件”，选择“按钮”工具，在“筛选”工作表插入一个按钮（如找不到“开发工具”，可依次点击“文件－选项－自定义功能区”，在右侧的“主选项卡”中找到“开发工具”，将其前面的钩选中即可），在“指定宏”对话框，将“宏名”更改为“筛选”，点击右侧的“录制”，确定。依次点击“数据－高级筛选－确定”，将筛选动作录制为一段宏。记着一定要在录制完毕后，点击“开发工具－停止录制”。按ALT+F11键，打开VBE，双击“模块1”，在右侧的代码窗口会发现这样一段代码：
Sub 筛选()
'筛选 宏
End Sub
将其更改为
Range("Database").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("Criteria")
End Sub
右键单击“按钮”－“编辑文字”，将按钮文字更改为“筛选”。完成后，如图8所示，当在H1单元格选择不同的库房号后，直接点击“筛选”按钮，会自动将符合条件的内容筛选出来。当需要打印时，由于设置了动态打印范围Print_Area，所以不用担心旁边黄色单元格区域和“筛选”按钮被打印出来。剩下的工作就是将文件保存为“EXCEL启用宏的工作簿”。
喜欢
0
赠金笔