By default, when you turn on an AutoFilter, dates are grouped in the drop down list. In the screen shot below, the dates have been rolled up to the years, 2013 and 2014. Here's how you can ungroup dates in Excel filter drop down lists, in the active workbook.
默认情况下,当您打开自动筛选时,日期会在下拉列表中分组。 在下面的屏幕快照中,日期已累计到2013年和2014年。这是在活动工作簿中的Excel过滤器下拉列表中取消日期分组的方法。
This screen shot shows how dates are automatically grouped by year, in the drop down list.
此屏幕快照显示了如何在下拉列表中按日期自动对日期进行分组。
![dategrouping01 dategrouping01](https://i-blog.csdnimg.cn/blog_migrate/d7a60f8480da3063e57d1452a111152f.png)
Some people might like the group dates feature, because it creates a short list of years, and you can drill down to find the date that you want.
某些人可能喜欢分组日期功能,因为它创建了简短的年份列表,您可以向下钻取以查找所需的日期。
![dategrouping02 dategrouping02](https://i-blog.csdnimg.cn/blog_migrate/c3c1bb55194c814192d0fabef8d017b6.png)
变更设定 (Change the Setting)
Sometimes you might prefer to see the full list of dates, instead of digging through each folder, to find the dates that you want. The good news is that you don’t have to put up with those grouped dates.
有时,您可能希望查看日期的完整列表,而不是逐个浏览每个文件夹,以查找所需的日期。 好消息是您不必忍受那些分组的日期。
You can manually change an Option setting, to ungroup them, or use programming to turn the grouping on or off.
您可以手动更改“选项”设置以取消分组,也可以使用编程打开或关闭分组。
手动关闭日期分组 (Manually Turn Date Grouping Off)
Follow these steps to turn off the Date Grouping feature in the current workbook:
请按照以下步骤关闭当前工作簿中的“日期分组”功能:
- On the Ribbon, click the File tab, then click Options 在功能区上,单击“文件”选项卡,然后单击“选项”。
- Click the Advanced category 单击高级类别
- Scroll down to the Display Options for This Workbook section 向下滚动到“此工作簿的显示选项”部分
- Remove the check mark from Group Dates in the AutoFilter menu. 从自动筛选菜单中的组日期中删除复选标记。
- Click OK to apply the setting change. 单击确定以应用设置更改。
![dategrouping03 dategrouping03](https://i-blog.csdnimg.cn/blog_migrate/929ab2bfaf5815b3aed85036bbde9e01.png)
在过滤器下拉列表中取消日期分组 (Ungroup Dates in Filter Drop Down)
When you change the setting, it only applies to the current workbook. So, if you frequently change the grouping option, you can use programming to turn the grouping on or off.
更改设置时,它仅适用于当前工作簿。 因此,如果您经常更改分组选项,则可以使用编程来打开或关闭分组。
Put the following code into a regular module in your Personal Workbook, or another file that is always open, then add a button for it on the Quick Access Toolbar.
将以下代码放入“个人工作簿”中的常规模块或始终打开的另一个文件中,然后在“快速访问”工具栏上为其添加一个按钮。
This code toggles the date grouping setting -- if the grouping is on, it turns it off, and if grouping is off, the code turns it on.
此代码切换日期分组设置-如果分组打开,则将其关闭;如果分组关闭,则将其打开。
Sub ToggleFilterDateGroup()
ActiveWindow.AutoFilterDateGrouping _
= Not ActiveWindow.AutoFilterDateGrouping
End Sub
翻译自: https://contexturesblog.com/archives/2013/12/12/ungroup-dates-in-excel-filter-drop-down/