excel切片器显示错误_使用切片器在Excel中设置过滤条件

最新推荐文章于 2021-06-09 11:30:02 发布

culiao2169

最新推荐文章于 2021-06-09 11:30:02 发布

阅读量1.7k

点赞数

文章标签： python java excel 数据分析大数据

原文链接：https://contexturesblog.com/archives/2016/03/31/use-slicers-to-set-filter-criteria-in-excel/

版权

excel切片器显示错误

In most cases, it's best if you keep people away from your data in Excel. It's too easy to mess things up! Instead, set up a worksheet where they can use Slicers to set filter criteria in Excel.

在大多数情况下，最好让人们远离Excel中的数据。搞砸太容易了！而是，设置一个工作表，他们可以在其中使用切片器在Excel中设置过滤条件。

Then click a button, and a macro dumps the data onto the worksheet. In the screen shot below, you can see how it works – easy, quick, and safe!

然后单击一个按钮，宏将数据转储到工作表上。在下面的屏幕截图中，您可以看到它的工作方式–简单，快速和安全！

使用切片器在Excel中设置过滤条件 (Use Slicers to Set Filter Criteria in Excel)

Here's a quick demo of the setup. Click the Slicers, then click the Get Data button. You can even change the fields if you want to.

这是设置的快速演示。单击切片器，然后单击获取数据按钮。您甚至可以根据需要更改字段。

You can download the sample file (link at the bottom of this page), to see how it's built. There are full instructions on my website, and a quick overview below.

您可以下载示例文件(此页面底部的链接)，以查看其构建方式。我的网站上有完整的说明，下面是快速概述。

这个怎么运作 (How It Works)

There are 4 key pieces in the setup to use Slicers to set filter criteria in Excel:

设置中有4个关键部分，可使用切片器在Excel中设置过滤条件：

Sales data table
销售数据表
Pivot table with filters
带有过滤器的数据透视表
Worksheet with Slicers and an output area
带切片器和输出区域的工作表
Macro that runs an Advanced Filter
运行高级筛选器的宏

销售数据表 (Sales Data Table)

On the SalesData sheet, there is a formatted Excel table, named Sales_Data. This sheet can be hidden, and kept away from accidental damage.

在SalesData工作表上，有一个格式化的Excel表，名为Sales_Data。这张纸可以隐藏起来，并避免意外损坏。

带有过滤器的数据透视表 (Pivot Table with Filters)

On another sheet that could be hidden away, there is a pivot table based on the Sales data table. There are only 3 fields in the pivot table, and they are in the filter area. Three Slicers were connected to this pivot table – one for each of the pivot table filters.

在另一个可以隐藏的工作表上，有一个基于Sales数据表的数据透视表。数据透视表中只有3个字段，它们位于过滤器区域中。三个切片机连接到该数据透视表–每个数据透视表过滤器一个。

输出表 (Output Sheet)

The main sheet has the Slicers (cut and pasted from the pivot table sheet) . There is also an output area (the Extract range for the Advanced Filter).

主工作表具有切片器(从数据透视表工作表剪切并粘贴)。还有一个输出区域(“高级过滤器”的“提取”范围)。

There are drop down lists in the output area, where you can select the fields that you want to see in the results.

在输出区域中有下拉列表，您可以在其中选择要在结果中查看的字段。

高级过滤条件范围 (Advanced Filter Criteria Range)

The macro runs an Advanced Filter, so there is a criteria range, on the Pivot Table sheet.

该宏运行“高级筛选器” ，因此“数据透视表”页面上有一个条件范围。

The criteria cells have formulas, and they show the item selected in each Slicer. If a Slicer is cleared, its criteria cells shows a wildcards character.

条件单元格具有公式，并且它们显示在每个“切片器”中选择的项目。如果清除了切片器，则其条件单元格将显示通配符。

For example, here's the result, after selections are made in the Category and Orderyr Slicers, and the Region Slicer is cleared. The Advanced Filter would return all the records for Snacks sales in 2016, for any Region.

例如，这是在“类别”和“ Orderyr切片器”中进行选择并清除“区域切片器”后的结果。 “高级过滤器”将返回任何地区2016年零食销售的所有记录。

运行高级筛选器宏 (Run the Advanced Filter Macro)

There is a Get Data button on the Output sheet, and it runs the Advanced Filter macro.

在“输出”页上有一个“获取数据”按钮，它运行“高级筛选器”宏。

To run that macro, select an item from each Slicer (or clear the Slicers), then click the Get Data button. You should see the matching records in the Output area.

要运行该宏，请从每个“切片器”中选择一个项目(或清除“切片器”)，然后单击“获取数据”按钮。您应该在“输出”区域中看到匹配的记录。

Then, change the Slicer selection, or the extract range headings, and click the Get Data button again. You'll see different results in the output area.

然后，更改“切片器”选择或提取范围标题，然后再次单击“获取数据”按钮。您将在输出区域中看到不同的结果。

为什么要使用高级过滤器？ (Why Use an Advanced Filter?)

An Advanced Filter is a great way to pull data from an Excel table, for lots of reasons. For example, they:

出于多种原因，高级过滤器是从Excel表中提取数据的好方法。例如，他们：

keep people away from source data
让人们远离源数据
are quick to run
运行快
allow you to limit the number of fields shown (and the drop down lists make it easy to change the fields)
允许您限制显示的字段数(下拉列表使更改字段变得容易)
return values, instead of formulas
返回值，而不是公式
make it easy to copy and paste to another location
使复制和粘贴到其他位置变得容易

为什么使用切片器选择标准？ (Why Use Slicers to Select the Criteria?)

Even though Advanced Filters are awesome, their criteria range setup can be confusing. Slicers take away that confusion, because:

即使“高级过滤器”很棒，它们的标准范围设置也可能令人困惑。切片器消除了这种混乱，因为：

you can set up the criteria range in the background, so other people can ignore that
您可以在后台设置条件范围，以便其他人可以忽略
after you select from one Slicer, other Slicers show related items at the top – you won't pick criteria combinations that don't exits
从一个切片器中选择后，其他切片器将在顶部显示相关项目–您不会选择不退出的条件组合