这是工作表 A：
Parent Kid Age
------ --- ---
John Scott 5
John Lucas 7
John Elisabeth 12
Victoria Jason 3
Victoria Amy 5
Jenifer Ashely 13
Jenifer Jared 17
工作表 B：
Parent Kid Team
------ --- ----
John Elisabeth Lions
Jenifer Ashely Sharks
Jenifer Jared Panters
在工作表 B 中，我希望用户能够从选择列表中选择孩子的名字（使用数据验证）。该选择列表的值应由工作表 A 中为此父母输入的孩子名字决定。例如，如果我选择单元格 A2，则选择列表应包含：
Scott
Lucas
Elisabeth
我可以使用常规公式来做到这一点吗？还是应该使用 VBA？
答案1
我不太确定这是否是最简单的方法，但如果是确定的话，那么父母就会被分组在工作表 A 中（所以就像你写的那样，而不是像例如。
John Scott
Victoria Jason
John Lucas
），您可以考虑使用这个解决方案（我假设您的工作表数据 A 在 A2 单元格中以“John”开头，WA代表工作表 A，WB代表工作表 B）：
首先，添加另一列，称为 WA 中的偏移量。因为“年龄”是C列，所以我把它放在D列中。公式是=Row(A2)-Row($A$2)并且向下传播。您应该得到0，1，2... 这些是每行相对于第一行的偏移量。
我们将使用此值作为我们的偏移量使用VLOOKUP函数。在 WB 中，假设您在单元格中输入父母的姓名A1，B1我们将获得孩子的姓名。因此，在辅助列中，C我们输入公式
=VLOOKUP(A1; WA!$A$2:$D$xxx; 4; FALSE)
参数含义：
A1是您正在搜索的单元格值 - 父母的名字WA!$A$2:$D$xxx表示搜索的范围（替换xxx为您获得的行数）4意味着我们想要检索第四列的值，WA!D在本例中，这是我们的偏移量FALSE意味着我们想要执行精确搜索，因为该WA!A列没有排序（或者可能没有排序）。
您得到的值就是范围应该开始的值。现在我们需要找到它的结尾，这意味着有多少行。最简单的方法可以是在 WA 中添加另一列，例如E，我们在其中放置一个简单的公式来计算父母有多少个孩子：
=COUNTIF($A$2:$A$xxx; A2)
在您的示例中，您将得到3，，，，，，。332222
您应该将该值复制到 WB，列D，再次使用：
=VLOOKUP(A1; WA!$A$2:$E$xxx; 5; FALSE)
现在，在 WB 中，我们在列中有A父母的名字，在列中B插入一个孩子，在列中C有父母的第一个孩子的偏移量，在列中D有父母拥有的孩子的数量。
现在转到名称管理器并添加Kids涵盖该范围的新名称：
=OFFSET(WA!$B$2:$B$xxx; WB!D1; 0; WB!E1)
其中参数的含义是：
WA$B$2:$B$xxx- 是包含孩子姓名的范围，WB!D1（注意：这是相对的！）是偏移量，因此子列表在 WA 中上述范围内开始，0意味着我们不想在列中做出任何偏移，WB!E1说我们想要获得多少行（即有多少个子项）。
有了这个Kids名字，你现在在数据验证中添加WB!B1，设置为列表，来源是=Kids。现在向下传播它。
就这样。
如果您的 WA 中的数据不包含分组的父级，您可以通过排序来获取此信息。
答案2
我最终创建了一个Kids使用以下公式调用的命名范围：
=OFFSET(WA!$B$2, MATCH(INDIRECT("A" & ROW()), WA!$A$2:$A$8, 0) - 1, 0, COUNTIF(WA!$A$2:$A$200, INDIRECT("A" & ROW())), 1)
然后将验证列表设置为=Kids。
如果有人想知道，我之所以使用，是INDIRECT("A" & ROW())因为仅使用 A1 会返回错误（我的 excel 能力不够强，无法理解原因）。执行 WB!A1 工作正常，但我不想走那条路，因为我必须在该工作簿的其他工作表中重复使用该名称范围。如果有更好的方法，我很高兴听到。