☰

根据另一个下拉列表中的选择进行过滤的下拉列表

tag-icon • tag-icon microsoft-excel worksheet-function
根据另一个下拉列表中的选择进行过滤的下拉列表

我在 Excel 工作表中有一个简单的人事数据库。它有一个查找表，在 A 列中显示经理，在 B 列中显示其各自的员工。我想在单元格 E1 和 E2 中创建两个下拉菜单。E1 会让我选择其中一位经理（姓名来自 A 列）。根据我在 E1 中选择的经理，我应该只会在 E2 中获得其员工的下拉列表。

因此，例如，如果我在 E1 中选择 Gary，则 E2 中的下拉菜单将仅显示 Sam 和 Nick。

A 乙 C 德 埃
1 经理 员工 经理： 加里
2 标记 查尔斯 员工： 缺口
3 标记 德里克
4 标记 琳达
5 加里 山姆
6 加里 缺口
7 弗雷德 布赖恩
8 弗雷德 鲍勃
9 弗雷德 朱丽叶
10 弗雷德 卡尔

我能想到的唯一解决方案是为每位经理创建单独的列，并在每列中列出他们下属的员工。然后我为每列创建命名范围。我将第一个下拉列表设为唯一经理姓名列表。然后我使用数据验证中的 INDIRECT 函数引用第一个下拉列表。

虽然这有效，但我想知道是否有一种解决方案不需要我创建单独的列，因为您可能会理解如果在查找表中添加了新经理或员工，我将不得不再次执行这些步骤。

答案1

您是否愿意拥有两个辅助列（与管理人员数量无关）？

（此解决方案已通过 Excel 2013 测试。）

我假设您有 16 行数据，在 中A2:B17。相应地调整我答案中的文字。我假设您使用 ColumnsG和 H作为辅助列。

进入：

  • 0在G1，
  • =MATCH($E$1,INDEX($A$2:$A$17,G1+1):$A$17,0)+G1在G2，
  • =IFERROR(INDEX($B$2:$B$17,G2), "*Zaphod*")在H2，和
  • ="H2:H" & MATCH("*Zaphod*",H2:H17,0)在H1。

（*Zaphod*用任何永远不会出现在您的数据中的字符串替换。）选择G2:H2并向下拖动/填充足够深以包含任何经理的员工列表。然后将验证设置为E2列表=INDIRECT(H1)。

解释：

  • G2、、G3等将包含中经理姓名 G4内的索引号。（这将是行号减一。）A2:A17E1
  • INDEX($A$2:$A$17,G1+1)标识上一个匹配项下方的单元格。例如，对于问题中的数据，
    • 在第 2 行中，这指向A2（因为G1为零）。
    • 在第 3 行中，它指向A6（因为A5包含“Gary”），并且
    • 在第 4 行中，这指向A7（因为A6包含“Gary”）。
  • INDEX($A$2:$A$17,G1+1):$A$17标识列的其余部分 A （位于上一个匹配项下方）。
  • =MATCH($E$1,(the above),0)查找该范围内第一次出现的E1；即下一个#N/A发生。如果没有下一次发生，这将是错误代码。
  • 添加G1以将其转换回相对于的索引A2。
  • INDEX($B$2:$B$17,G2)获取对应的员工姓名n中第 1 次出现的经理姓名E1。如果没有n第 一次发生时，#N/A错误将传播到这里。
  • IFERROR让我们在真实值后面放置一个虚拟值。
  • MATCH("*Zaphod*",H2:H17,0)返回虚拟值内的索引号H2:H17。这将是行号减一，因此它是最后一个真实值的行号。
  • 将以上内容附加到H2:H以获取有效值的范围。

电子表格中包含问题数据（E1 中除“Fred”之外）

我假设您知道如何验证经理姓名E1。如果不知道如何验证，请参阅动态列表和实例计数。

答案2

对于 Excel 365

要过滤“经理”列以获取唯一值，您需要将以下公式放在一个单元格中，该单元格下方有足够的可用行，以容纳数据集中经理的总数，因为它将向下填充。此单元格可以位于工作簿中的任何位置。

假设：您在同一工作表上为公式选择了单元格 D5，并且您的表名为“employeeList”

In cell D5 place the formula:            =UNIQUE(employeeList[Manager])  
Alternatively, to order alphabetically:  =SORT(UNIQUE(employeeList[Manager]))

这将动态生成无重复的经理列表。当源数据更新时，列表也会反映这些变化。

接下来，从该过滤列表而不是包含重复项的 employeeList 表创建下拉列表验证。

In cell E1 set the following Data Validation:

Settings > Validation criteria
                         Ignore blank:      checked
                         In-cell dropdown:  checked
                         Allow:             List
                         Source:            =D5#

磅号 (=D5#) 表示包含单元格下方的 SPILL。

相关内容