excel 筛选及列表 使用基础教程

实际工作中，对于周期性数据，我们有可能需要制作由触发器驱动的Excel图表，甚至是由多个数据的动态变化来构成一个小型图表应用系统，以此来动态展示数据。此时需要使用建立在单层次表达基础之上的多层次表达，以适应这种新需求，Excel的相关功能可以帮助我们实现这样的需求：

1. 图表可进行交互式的数据查询及呈现；

2. 图表可动态适应数据个数的变化；

3. 可动态看到数据的变化过程趋势。

通过Excel自动筛选功能及列表功能，或使用查询函数的辅助数据区域，是实现图表交互式数据查询最为简单和快捷的方法。

利用Excel定义名称封装的函数公式，在图表系列源数据中进行引用，是相对灵活和简便的数据交互查询和动态展现方法。将Excel工作表控件作为触发器来使用，交互过程更具良好的人机交互体验。

Excel数据透视表提供了强大的数据整合查询功能，利用数据透视表来制作的数据透视图在查询上可实现复杂的交互数据查询，配合Excel定义名称来使用在格式设置上更加灵活。

一个好的展示效果离不开多种Excel功能的整合使用，Excel的VBA也是其中之一，尤其是Excel图表具有多种基于VBA编程的事件响应过程，可供我们将图表变为触发器去参与交互过程。

筛选及列表

数据查询是一个“展示需要、隐藏不需要”的筛选过程。Excel中可以被使用来进行图表可视化交互的查询功能有：自动筛选、列表和Excel函数。通过这些简单的功能应用，可使查询结果变为具有动态交互效果的图表展示。

自动筛选

Excel提供的筛选功能分为：自动筛选和高级筛选两种。自动筛选功能基于下拉列表式的触发机制来完成筛选作业，该功能其实是将不符合筛选条件要求的数据行进行了隐藏。图14.1-1的案例图表正是基于此类引用的一个典型案例，整个制作过程相当简单，仅是在柱形图表系列的源数据引用区域使用了自动筛选功能而已，图14.1-1左侧的下拉列表是该方法的筛选操作。

图14.1-1利用自动筛选功能制作的交互图表

此方法需要勾选图表选项：只绘制可见单元格数据选项。自动筛选区请包含列标题，这样的好处是可以使首行不参与筛选。

列表

列表是自Excel 2003起，封装并强化了自动筛选的一个功能，该功能可动态适应数据选区。当数据选区中有新数据被加入后，列表区会自动扩充，当图表系列引用的源数据是该列表区，则图表系列的数据点个数会自动进行相应增加。这减少了反复操作图表、修改源数据引用区域的困扰。

创建一个列表区非常简单。无论Excel版本，仅仅只需选中我们需要的数据选区时，然后按下键盘Ctrl＋L组合键，根据提示勾选：表包含标题，单击“确认”按钮即可，如图14.1-2所示。

图14.1-2创建列表对话框

Excel对于列表亦提供了相应的列表工具栏来进行管理，这使得列表变得更加易于操作。如图14.1-3所示为Excel 2003列表工具栏，图14.1-4所示则为Excel 2010列表选项卡。

图14.1-3Excel 2003列表工具栏

图14.1-4Excel 2010列表选项卡

熟练掌握列表的使用后，即可将图14.1-1的案例引用源数据区直接转换为如图14.1-5所示的列表，来进行相应的管理。该方式完全基于自动筛选功能，故图表选项同样需勾选“只绘制可见单元格数据”选项。

图14.1-5利用列表功能制作的交互图表

辅助区域函数筛选

使用辅助单元格区域，同样可以利用Excel的查询函数来实现数据的检索和筛选。Excel提供了诸如OFFSET、MATCH、INDEX、CHOOSE等函数来进行相应的数据查找。

此处的触发器使用了数据有效性的序列功能，该功能可使Excel的单元格具有下拉选框。要使某个单元格具有数据有效性设置，只需按键盘Alt＋D＋L组合键即可弹出“数据有效性”对话框，在“设置”选项卡中，“允许”选序列，“来源”选择下拉列表引用单元格区域，如图14.1-6所示。

图14.1-6数据有效性对话框

图14.1-7案例图表即是基于此类引用的一个典型案例，图表数据源引用辅助单元格区域的数值，当鼠标选取筛选触发单元格时，触发辅助单元格区域函数进行重算，来响应用户的交互作业。

图14.1-7利用函数公式辅助区域制作的交互图表

提示

特别说明：

1）大部分的查找与引用函数要求查找源数据进行必要的排序。虽然使用数组公式也可完成排序，但这样的函数应用往往复杂，且投入与产出比不佳。

2）制作交互式图表的首要任务亦是将数据的排布变得有序，以方便制作图表和简化图表制作的难度。

图14.1-7案例图表每次筛选的结果均为3行，相当固定。当筛选的行数不确定时，则使用上述方法并不能有效解决这个问题。若使用辅助区域来处理不确定行数的筛选，出现的最大问题便是如图14.1-8所示的状况，图表将以“0”值方式呈现筛选结果中没有的数值，如果图表是线形类图表，在视觉中将会非常糟糕。

图14.1-8利用函数公式辅助区域制作的步长不等交互图表

图14.1-8的视觉呈现效果并不理想，要解决这个问题只需使用定义名称的方法，来限制图表系列的引用区域行数即可，如图14.1-9所示。一般而言，建议图表使用工作表级的定义名称，这样引用较为方便，不易受工作簿名称限制，且复制工作表时较易进行移植。

图14.1-9利用辅助函数公式区域与定义名称配合的交互图表

excel 筛选及列表 使用基础教程的下载地址：

• 本地下载