想象一下，我正在用Excel为我的 child 编写一个菜单计划器(比我的实际问题更容易描述)...
我有可用食物的 list :苹果，香蕉，胡萝卜，枣，鸡蛋，鱼等。
我有一个 child list :John，Karen，Lional，Mike等。
然后，我有一个简单的表格，列出了每个 child 喜欢的食物:在约翰的专栏下，苹果，香蕉和鱼对着一个“x”，其他对着空白。
J K L M
---------------
a x x x
b x x
c x x x
d x
e x x
f x
现在，在我的菜单计划器的主要部分中，我希望有一些具有数据验证功能的单元格，这些单元格允许我使用上面的“喜欢”表为每个 child 选择食物:
Name Food
A2 B2
单元格A2将包含数据验证，该数据验证将为所有 child 的姓名(J，K，L，M)提供单元内下拉菜单(这很简单-我可以做到这一点!)
单元格B2需要包含所选 child 动态喜欢的食物的动态生成列表。因此，如果我在A2中选择John，则B2列表将为{a，b，f}。如果我选择Lionel，则B2列表将为{a，b，c，e}。显然，随着 child 口味的变化，我可以通过添加/删除“x”来更新“喜欢”表，并且B2中的下拉列表会自动更新。
如何为单元格B2创建下拉验证列表？ (我宁愿不诉诸VBA来做到这一点)
最佳答案
我假设您的数据表在A1:E7范围内。
步骤1.为每个 child 创建一个选项列表
为每个 child 创建一个列表，列出所有偏好设置(在列表的末尾添加“-”作为占位符)。在G2中输入此公式，然后拖动到G2:J7范围:
=IF(G1="-";"-";IF(ISNA(OFFSET($A$1;IFERROR(MATCH(G1;$A$2:$A$7;0);0)+
MATCH("x";OFFSET(B$2;IFERROR(MATCH(G1;$A$2:$A$7;0);0);0;7;1);0);0;1;1));
"-";OFFSET($A$1;IFERROR(MATCH(G1;$A$2:$A$7;0);0)+MATCH("x";OFFSET(B$2;
IFERROR(MATCH(G1;$A$2:$A$7;0);0);0;7;1);0);0;1;1)))
还要在数据列(G1:J1)上方放置 child 的名字。
步骤2。创建条件数据验证
鉴于您的第一个数据验证列表(名称)在单元格L2中，并且您已按照步骤1进行操作，请使用以下公式对食品进行数据验证:
=OFFSET(F$2;0;MATCH(L2;$G$1:$J$1;0);6-COUNTIF(OFFSET(F$2:F$7;0;
MATCH(L2;$G$1:$J$1;0));"-"))
此公式既排除了列表中的所有空白选项(“-”)，又根据 child 的名字给出了正确的列表。
更新。 INDEX/MATCH 的替代解决方案
OFFSET是一个易变的公式(例如，只要您的工作簿中有任何更改，Excel都会重新计算它)，因此您可能想使用INDEX进行此操作。这是我上面第1步的公式:
=IF(G1="-";"-";IFERROR(INDEX($A$2:$A$7;IFERROR(MATCH(G1;$A$2:$A$7;0);0)+
MATCH("x";INDEX(B$2:B$7;IFERROR(MATCH(G1;$A$2:$A$7;0)+1;1);1):B$7;0);1);"-"))
至于步骤二，似乎仅在选择单元格时才会重新计算数据验证的公式，因此偏移量在数据验证列表中没有波动率。由于INDEX无法返回范围，并且Excel不允许INDEX(..):INDEX(..)范围进行数据验证，因此OFFSET对于数据验证列表更好。
关于Excel:如何基于数据表创建动态数据验证列表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19730067/