excel就绪筛选模式_Excel自动筛选器显示筛选器模式

最新推荐文章于 2022-09-21 16:16:16 发布

culiao2169

最新推荐文章于 2022-09-21 16:16:16 发布

阅读量1.4k

点赞数

文章标签：过滤器 java python excel 大数据

原文链接：https://contexturesblog.com/archives/2010/01/27/excel-autofilter-shows-filter-mode/

版权

excel就绪筛选模式

It's Price Book publishing week for one of my clients, and we've been making lists, and checking them twice. Or 3 or 4 times, or more!

这是我的一位客户的价格手册发布周，我们一直在列出清单，并对其进行两次检查。或3或4次，或更多！

When comparing the new prices to the previous prices, an Excel AutoFilter comes in handy. You can select the same product or model in each workbook, and easily compare item details. Yes, the widget prices went up a bit this year, so that's why the assembled parts cost a bit more.

将新价格与之前的价格进行比较时，可以使用Excel自动筛选器。您可以在每个工作簿中选择相同的产品或型号，并轻松比较项目详细信息。是的，小部件价格今年上涨了一点，所以这就是组装零件成本更高的原因。

Note: Creating price lists would be easier with a big fancy monitor, so add your Excel question in the comments for last Friday's Excelerators Quiz post, and you'll have the chance to win a shiny new monitor.

注意：使用大型花哨的显示器可以更容易地创建价目表，因此，在上周五的Excelerators测验帖子的注释中添加Excel问题，您将有机会赢得崭新的显示器。

状态栏中的记录计数 (Record Count in the Status Bar)

Sometimes when you select records with an AutoFilter, the record count appears in the Status Bar, at the bottom left. In this example, I was working with a small table, with 50 records, and only one column had a formula.

有时，当您使用“自动筛选”选择记录时，记录计数会显示在左下方的状态栏中。在此示例中，我正在使用一个有50条记录的小表，并且只有一列具有公式。

I selected File Folder in the Product column, and the Status Bar showed that 3 of the 50 records contained that product. So far, so good.

我在“产品”列中选择“文件夹”，状态栏显示50条记录中的3条包含该产品。到目前为止，一切都很好。

状态栏显示过滤器模式 (Status Bar Shows Filter Mode)

Then I added another record to the table, and selected a different product from the AutoFilter drop down list. This time the Status Bar showed the rather unhelpful message, "Filter Mode", instead of the record count.

然后，我在表中添加了另一条记录，并从“自动筛选”下拉列表中选择了另一种产品。这次状态栏显示了非常无用的消息“过滤器模式”，而不是记录数。

Excel 2007 seems to handle this better, but in Excel 2003, and earlier versions, you might see "Filter Mode" if there are more than 50 formulas in the list.

Excel 2007似乎可以更好地解决此问题，但是在Excel 2003和更早版本中，如果列表中包含50个以上的公式，则可能会看到“过滤器模式”。

When you apply an AutoFilter, the formula recalculate. If there are lots of formulas to calculate, Excel shows a "Calculating %" message in the Status Bar, so you'll have something to entertain you while you wait.

当您应用自动筛选时，公式将重新计算。如果要计算的公式很多，Excel会在状态栏中显示“正在计算％”消息，因此在等待时您会感到很开心。

Unfortunately, the "Calculating %" message interferes with the record count message in the Status Bar. If the record count message is interrupted, it shows the "Filter Mode" message instead.

不幸的是，“正在计算％”消息会干扰状态栏中的记录计数消息。如果记录计数消息被中断，它将显示“过滤器模式”消息。

You can't change this behaviour, but there are a couple of workarounds that you can use to find the record count.

您无法更改此行为，但是可以使用一些变通办法来查找记录数。

改用AutoCalc (Use AutoCalc Instead)

If the Status Bar shows "Filter Mode", you can get the record count from the AutoCalc feature instead.

如果状态栏显示“过滤器模式”，则可以从自动计算功能获取记录计数。

Right-click on the Status Bar
右键单击状态栏
In the pop-up menu, click Count Nums
在弹出菜单中，单击“计数数量”
Click on the column heading for a column that contains numbers (and no blank cells within the list)
单击列标题以查找包含数字的列(列表中没有空白单元格)

You'll see the count of visible numbers in the AutoCalc area of the Status Bar.

您将在状态栏的“自动计算”区域中看到可见数的计数。

使用SUBTOTAL函数 (Use the SUBTOTAL Function)

If you'd rather have the record count show up automatically, you can use the SUBTOTAL function. It ignores the filtered rows, and calculates based on the visible rows only.

如果您希望自动显示记录计数，可以使用SUBTOTAL函数。它忽略过滤的行，并且仅基于可见行进行计算。

For example, with numbers in column D, this formula, with 2 as the first argument, will calculate the COUNT of visible numbers:

例如，对于D列中的数字，该公式(第一个参数为2)将计算COUNT个可见数：

=SUBTOTAL(2,D:D)

=小计(2，D：D)

If you want to count items in a column that contains text, use 3 as the first argument, and subtract 1 from the result, to account for the heading cell.

如果要计算包含文本的列中的项目，请使用3作为第一个参数，并从结果中减去1，以说明标题单元格。

=SUBTOTAL(3,B:B)-1

=小计(3，B：B)-1