excel下拉列表联动_动态数组的Excel下拉列表

Select a region name in one Excel drop down list. Then, in the next drop down list, select from a list of employees who work in that region. You've seen this dependent drop down trick before, but watch my latest video below, to see a new twist on this technique.

在一个Excel下拉列表中选择一个区域名称。 然后，在下一个下拉列表中，从该地区工作的员工列表中选择。 您之前已经看过这种依赖下拉菜单的技巧，但请在下面观看我的最新视频，以了解此技术的新变化。

相关下拉列表 (Dependent Drop Downs)

For a quick peek at the new twist, this animated screen shot shows the dependent drop down lists in action.

为了快速浏览新的变化，此动画屏幕快照显示了正在运行的相关下拉列表。

When you select a region name in cell F2, the drop down in cell G2 shows that region's employees.

当您在单元格F2中选择区域名称时，单元格G2中的下拉列表将显示该区域的员工。

动态数组 (Dynamic Arrays)

In cells D7 and F2, I used the new Excel functions, UNIQUE, SORT, and FILTER, to create dynamic arrays for the drop down lists.

在单元格D7和F2中，我使用了新的Excel函数UNIQUE，SORT和FILTER为下拉列表创建动态数组。

If you're using Excel for Office 365, with monthly updates, you should have these new functions now.

如果您使用Excel for Office 365，并且每月更新一次，则现在应该具有这些新功能。

If your version of Excel doesn't have these function, or dynamic arrays, go to my Contextures site, for other ways to create dependent drop down lists.

如果您的Excel版本不具有这些功能或动态数组，请访问我的Contextures网站， 以获取创建依赖下拉列表的其他方法 。

视频：下拉列表–动态数组 (Video: Drop Downs – Dynamic Arrays)

To see all the details for setting up these dependent drop downs, using dynamic arrays, watch this video. The timeline is below the video.

要查看使用动态数组设置这些依赖下拉菜单的所有详细信息，请观看此视频。 时间线在视频下方。

演示地址

Video Timeline

影片时间表

• 0:00 Introduction
  0:00简介
• 0:31 Create a Unique List of Regions
  0:31创建唯一的区域列表
• 2:38 Make a Region Drop Down
  2:38下拉区域
• 3:30 Create an Employee List
  3:30创建员工列表
• 5:50 Make an Employee Drop Down
  5:50使员工下落
• 7:35 Get the Workbook
  7:35获取工作簿
• Get the Sample Workbook
  获取样本工作簿

获取书面步骤 (Get the Written Steps)

Maybe you don't like watching videos. Would you rather follow the written steps for this technique, with lots of screen shots?

也许您不喜欢看视频。 您是否愿意按照大量屏幕快照的方法来执行此技术的书面步骤？

If so, then go to the dynamic array drop downs page on my Contextures site. All the steps and formulas are described there, with screen shots for most of the steps.

如果是这样，请转到Contextures网站上的动态数组下拉页面 。 此处介绍了所有步骤和公式，并提供了大多数步骤的屏幕截图。

获取工作簿 (Get the Workbook)

To get the free workbook for this video, go to my Contextures website, and download it there. The zipped workbook is in xlsx format, with no macros.

要获得该视频的免费工作簿， 请访问我的Contextures网站 ，然后在此处下载。 压缩的工作簿为xlsx格式，没有宏。

Remember though, this technique will only work if your version of Excel for Office 365 has the dynamic arrays feature.

但是请记住，仅当您的Excel for Office 365版本具有动态数组功能时，此技术才有效。

隐藏下拉菜单中的二手物品 (Hide Used Items in Drop Down)

To see another example of using dynamic arrays for drop down lists, go to the Hide Used Items page on my Contextures site.

若要查看将动态数组用于下拉列表的另一个示例， 请转到 Contextures网站上的“隐藏使用的项目”页面 。

In the Employee column, after a name has been selected, it disappears from the drop down list.

在“雇员”列中，选择名称后，该名称将从下拉列表中消失。

The list of available employees is created with a dynamic array on another sheet. Here is the formula in cell D2:

可用雇员列表是在另一个工作表上创建的，带有动态数组。 这是单元格D2中的公式：

=SORT(FILTER(tblEmp[EmpList], COUNTIF(tblSched[Employee], tblEmp[EmpList])=0))

= SORT(FILTER(tblEmp [EmpList]，COUNTIF(tblSched [Employee]，tblEmp [EmpList])= 0))