数据透视表和数据交叉表_数据透视表中的依存数据验证

数据透视表和数据交叉表

Cascading lists and kangaroos? Today, Ed Ferrero shares his technique for creating dependent data validation from pivot tables. Ed's from Australia, and it looks like we'll learn a bit about his country too, as we go through his sample file.

级联清单和袋鼠？ 今天，埃德·费雷罗(Ed Ferrero)分享了他从数据透视表创建相关数据验证的技术。 Ed来自澳大利亚，在浏览他的样本文件时，我们似乎也会对他的国家有所了解。

相关数据验证 (Dependent Data Validation)

We've created dependent data validation drop downs before, based on named ranges, or sorted lists. Ed's technique is perfect if you have a large data source, and it isn't sorted in the order that you need.

我们已经基于命名范围或排序列表创建了从属数据验证下拉列表。 如果您有大量的数据源，并且没有按所需的顺序进行排序，那么Ed的技术非常完美。

In this example, there's a list of States and Cities, with the cities in alphabetical order.

在此示例中，有一个州和城市列表，其中城市按字母顺序排列。

创建数据透视表 (Create the Pivot Tables)

Ed created two pivot tables, one with State in the row area, and one with State and City in the row area.

Ed创建了两个数据透视表，其中一个在行区域中包含State，另一个在行区域中包含State和City。

The State labels don't repeat in the pivot table, so you can't use the sorted table dependent data validation technique.

状态标签不会在数据透视表中重复，因此您不能使用依赖于排序表的数据验证技术。

创建命名范围 (Create the Named Ranges)

Instead, Ed created a couple of named ranges, and some dynamic ranges.

相反，Ed创建了几个命名范围和一些动态范围。

• The first range is State, which is the list of state names and Grand Total in the first pivot table.
  第一个范围是州，这是州名称列表和第一个数据透视表中的总计。
• The second range is StateCity, which is the list of state names and Grand Total in the second pivot table.
  第二个范围是StateCity，它是第二个数据透视表中的州名和总计总数的列表。

Tip: If you reduce the worksheet zoom to 39%, you can see the range names.

提示：如果将工作表缩放比例减小到39％，则可以看到范围名称。

创建动态范围 (Create the Dynamic Ranges)

The first dynamic range is for the City heading in the second pivot table.

第一个动态范围用于第二个数据透视表中的城市标题。

• CityHeader:  =OFFSET(StateCity,-1,1,1,1)

  CityHeader： = OFFSET(StateCity，-1,1,1,1)

The next two dynamic ranges, StateNo and StateCityNo, use relative references to read the value of the state from the cell to the left of the active cell. For example, if the selected State is in cell A3 on Sheet1, these formulas are used:

接下来的两个动态范围StateNo和StateCityNo使用相对引用从活动单元格左侧的单元格读取状态值。 例如，如果选定的状态在Sheet1的单元格A3中，则使用以下公式：

• StateNo:  =MATCH(Sheet1!A3,State,0)

  状态编号： = MATCH(Sheet1！A3，State，0)

• StateCityNo:  =MATCH(Sheet1!A3,StateCity,0)

  StateCityNo： = MATCH(Sheet1！A3，StateCity，0)

Queensland is the selected State, so StateNo =3 and StateCityNo =5. Then, the next State is found in the StateCity range.

昆士兰州是选定的州，因此StateNo = 3和StateCityNo = 5。 然后，在StateCity范围内找到下一个州。

• StateCityNext:   =MATCH(INDEX(State,StateNo+1),StateCity,0)

  StateCityNext： = MATCH(INDEX(State，StateNo + 1)，StateCity，0)

The next State is South Australia, and it's in row 9, so StateCityNext =9.

下一个州是南澳大利亚州，在第9行，因此StateCityNext = 9。

创建城市从属清单 (Create the Dependent List of Cities)

Finally, the dynamic range for the list of cities is created.

最后，创建城市列表的动态范围。

• City:  =OFFSET(CityHeader,StateCityNo,0,StateCityNext-StateCityNo,1)

  城市： = OFFSET(CityHeader，StateCityNo，0，StateCityNext-StateCityNo，1)

The City range is offset from the CityHeader cell, 5 rows down, 0 columns right, 4 rows high (9-5), and 1 column wide.

City范围从CityHeader单元格偏移，向下5行，向右0列，高4行(9-5)，宽1列。

创建下拉列表 (Create the Drop Down List)

The final step is to create the data validation drop down lists. In cell A3, a State drop down list is created, based on the State range.

最后一步是创建数据验证下拉列表。 在单元格A3中，根据状态范围创建“状态”下拉列表。

In cell B3, a dependent City drop down list is created, based on the City range.

在单元格B3中，根据城市范围创建一个从属城市下拉列表。

下载样本文件 (Download the Sample File)

You can download Ed's sample file to see how it works: Dependent Data Validation From Pivot Tables. It's a zipped file, in Excel 2003 format.

您可以下载Ed的示例文件以查看其工作方式： 数据透视表中的相关数据验证 。 这是一个压缩文件，格式为Excel 2003。

关于埃德·费雷罗 (About Ed Ferrero)

Ed maintains an Excel techniques web site at www.edferrero.com. He is based in Australia, and has been a Microsoft Excel MVP since 2006. ____________

Ed维护着一个Excel技术网站，网址为www.edferrero.com。 他来自澳大利亚，自2006年以来一直担任Microsoft Excel MVP。____________

翻译自: https://contexturesblog.com/archives/2010/03/31/dependent-data-validation-from-pivot-tables/

数据透视表和数据交叉表