Tables Part 4: AutoFilter improvements: much more than just multi-select …
Sorting and filtering are two of the most important types of basic
analysis that you can do with data. In Excel 12, we have improved
sort and filter functionality to better expose common tasks, to make key tasks simple, and to enable scenarios that were not possible in earlier versions. We have done work in AutoFilter, our sort
functionality (Data|Sort), and in PivotTables. I will cover the first two (AutoFilter and Sort) in posts this week and PivotTables in a few weeks when I review all our PivotTable work. Today, I will focus on AutoFilter.
排序与筛选是分析数据的最基本的两种方式， 在EXEL12 中， 我们的改进使选择与筛选的功能更加强大，普通任务一目了然，关键任务更加简单，使以前版本中不可能的场景成为可能。
Several of our goals for AutoFilter were driven by a couple of our top customer requests. Specifically, in Excel 12 we have
• Enabled multi-select in AutoFilter, so you can select any number of items for your filter condition
• Added the ability to sort and filter by colour
• Increased the limit of items in the AutoFilter dropdown from 1,000 items to 10,000 items
• Additionally, we have
• Added a “quick filter” feature that enables data-type-specific filtering
• Added date grouping to date AutoFilters
• Made it possible to re-apply a set of filters with one button click
• Provided more UI to help users figure out what filters are applied to a range/table
Let’s take a closer look. The first thing we tried to do was to make it easier to turn on AutoFilter by making it part of the “Sort & Filter” commands on the Sheet tab in the Excel ribbon (the tab that is shown by default).
Sort & Filter Chunk
If you are a user of AutoFilter today, one of the first things you will notice is that we are now referring to this functionality as “Filter”. (You may also notice that “Sort Descending” should say “Sort Z to A” – that’s a bug in current builds.) We did a lot of usability work in this area, and we determined that users that had used AutoFilter before had no trouble figuring out the new name, while users that had never use AutoFilter before were much more likely to understand and try the feature when it was referred to as “Filter”. (For the duration of this post, I will refer to the feature as the Filter feature). Once you have turned on Filter, the next thing that a current user of AutoFilter will notice is that the interface has been completely revamped.
如果你现在经常使用自动筛选功能，你会发现我们把这种功能叫做筛选（你还会注意到降序排列叫做Z到A排列，这实际是一个bug。）我们在这个方面做了很多实用的工作。以前用过自动筛选功能的用户会很容易就发现名称的变化，而以前没有用过此功能的用户会更容易尝试此功能。（在本贴中，我将用FILTER FEATURE 来指代此功能）当你点击筛选按钮，你会注意到界面已完全改变。
Sort options remain at the top of the dropdown, but we have updated the text to reflect the data being filtered (“smallest to largest” for numbers, “oldest to newest” for dates, etc.). We have added the ability to sort by colour (more on that later). We’ve added a way to quickly and easily remove all filter conditions from a single column. Below that we have some filter options (more on that in a moment) and finally we have the filter items themselves.
Let’s look at filtering in more detail, starting with multi-select. In previous versions of Excel, if you wanted to multiple-select items, you needed to use the Custom dialog, and that limited you to two choices. In Excel 12, you can simply select the items you want to see included in your filter and press OK. This is much faster, easier to discover, and supports as many items as you want.
Excel 12 makes it possible to express more complex filtering conditions than just clicking individual items. It does this by providing filtering options based on the data type of your column – we are currently referring to this as “quick filters”, but we may come up with a better name by the time we ship. Say, for example, I’m looking at a record of sales for the past couple years and I want to see how much revenue I made last month and which sales brought in the most money. By clicking on the filter dropdown on my date column I’m presented with a large list of date filters, among which is the option to filter records to last month.
Excel 12能够支持更复杂的筛选情况。这项功能根据你设置的筛选选项进行操作。筛选选项的依据是的数据类型栏——也就是我们目前所称之为“快速筛选”的功能。我们会在最终版本中使用一个更好的名字。比如说，我正在看一份过去两年的销售记录，我想知道上个月有多少收入和那种销售挣的钱最多。点击我的数据列上的筛选下拉菜单， 便会呈现出一大列筛选数据， 这些数据中包含了上个月的筛选纪录选项。
As you can see from the picture above, Excel offers an array of date filters that make filtering by different date ranges a snap. What makes these filters special is that they are based on the system clock so my “last month” filter will always filter to the previous month when the filter is reapplied. Setting up these types of dynamic filters were not easily possible in previous versions.
We have also added “quick filters” for numeric data. For example, after I filter by date, I can then filter my profit column to only show me sales that were above average.