从Excel中的筛选器中提取唯一值的集合 [英] Extracting the collection of unique values from a filter in Excel
问题描述
我有一个文件,其行数在8列中扩展到数万。一个特定的列包含周末日期。我必须计算此文件中存在的周末数。
有没有办法通过使用AutoFilter对象提取这个(该列的)唯一值集合? / p>
如果问题含糊不清:
考虑当我们对特定应用过滤器时出现的下拉列表柱。我想这个列表必须代表某种内部集合。如果我们可以提取并获取此集合的计数,那么问题就解决了。
请帮忙。
提前致谢!
假设日期在F2:F10000。
如果您只想计算唯一日期的数量,可以用佛llowing数组公式,用Ctrl + Shift + Enter确认:
= SUM(1 / COUNTIF(F1:F10000,F1:F10000))
如果日期列中有空格,则会失败。然后你可以使用(也用Ctrl + Shift + Enter确认):
= IF(F1:F10000<>"",SUM(1 / COUNTIF(F1:F10000, F1:F10000)))
如果您想要一个唯一日期列表,可以使用高级过滤器:
- 将日期列的字段名称(列标题)复制到同一工作表上的另一个单元格。此单元格下方不应有任何数据。
- 单击数据中的任意位置。
- 在功能区的"数据"选项卡上,在"排序"和"排序"中。过滤组,单击"高级"。
- Excel将选择整个数据表并显示"高级过滤器"对话框。
- 选择"操作"下的"复制到其他位置"。
- 单击"复制到:"框并指向复制字段名称的单元格。
- 勾选"仅限唯一记录"复选框。
- 单击"确定"。 / li>
I have a file which has rows extending to tens of thousands across 8 columns. One particular column contains the weekend date. I have to count the number of weekends present in this file.
Is there a way to extract this collection of unique values (of that column), by using the AutoFilter object?
In case the question is vague:
think about the drop-down list which comes up when we apply a filter on a particular column. That list must be represent a collection of some kind internally, I guess. If we can extract and get the count of this collection, then the problem is solved.
Please help.
Thanks in advance!
Let's say the dates are in F2:F10000.
If you only want to count the number of unique dates, you can use the following array formula, confirmed with Ctrl+Shift+Enter:
=SUM(1/COUNTIF(F1:F10000,F1:F10000))
This will fail if there are blanks in the date column. You can then use (also confirmed with Ctrl+Shift+Enter):
=IF(F1:F10000<>"",SUM(1/COUNTIF(F1:F10000,F1:F10000)))
If you want a list of unique dates, you can use Advanced Filter:
- Copy the field name (column heading) of the date column to another cell on the same sheet. There shouldn't be any data below this cell.
- Click anywhere in your data.
- On the Data tab of the ribbon, in the Sort & Filter group, click Advanced.
- Excel will select the entire data table and display the Advanced Filter dialog.
- Select 'Copy to another location' under Action.
- Click in the 'Copy to:' box and point to the cell where you copied the field name.
- Tick the check box 'Unique records only'.
- Click OK.
这篇关于从Excel中的筛选器中提取唯一值的集合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!