Excel：如何根据数据表创建动态数据验证列表 [英] Excel: How to create dynamic data validation list based on data table

问题描述

想想我正在为我的孩子在Excel中编写一个菜单计划器（比我的实际问题更容易描述）...

我有一个可用的列表食物：苹果，香蕉，胡萝卜，枣，鸡蛋，鱼等。

我有一个孩子列表：John，Karen，Lional，Mike等。 p>

然后，我有一个简单的表，列出每个孩子喜欢的食物：在约翰的专栏中，将有一个x反对苹果，香蕉和鱼，但对其他人的空白。

  JKLM 
 --------------- 
axxx 
bxx 
cxxx 
dx 
exx 
fx 
  

在我的菜单计划的主要部分，我想要一些细胞与数据验证，允许我为每个孩子选择食物，使用上述'喜欢'表：

 名称食物
 A2 B2 
  

单元格A2将包含数据验证，给出一个单元内的drop-d拥有所有的孩子名字（J，K，L，M）（很容易 - 我可以做那个位！）

单元格B2需要包含动态生成的列表被选中的孩子喜欢的食物。所以，如果我在A2中选择John，那么B2列表将是{a，b，f}。如果我选择莱昂内尔，B2列表将是{a，b，c，e}。显然，随着我孩子的口味变化，我可以通过添加/删除x来更新我的喜欢表，B2中的下拉菜单将自动更新。

如何为单元格B2创建下拉确认列表？ （我宁愿这样做，而不用诉诸于VBA）

解决方案

我假设你的数据表范围是A1： E7。

步骤1.创建每个孩子的选择列表

对于每个孩子，创建列出所有喜好的列表（在列表末尾，我添加 - 作为占位符）。在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 ; $ A $ 1; IFERROR（MATCH）; $ A $ 2; $ A $ 7; 0）; 0）; 0; 7; 1）; 0）; 0; 1; 1））; 
 （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）。

步骤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））; - ））
  

此公式不包括列表中的所有空选项（ - ），并根据孩子的姓名给出正确的列表。

---

<强>更新。使用INDEX / MATCH的替代解决方案

OFFSET是一个易失性公式（即，当您的工作簿中有任何更改时，Excel会重新计算它），因此您可能想要这与INDEX代替。以下是我上述步骤1的公式：

  = IF（G1 = - ; - ; IFERROR （$ A $ 2：$ A $ 7; IFERROR（MATCH（G1; $ A $ 2：$ A $ 7; 0）; 0）+ 
 MATCH（x; INDEX（B $ 2：B $ 7; IFERROR （$; $ A $ 2：$ A $ 7; 0）+1; 1）; 1）：B $ 7; 0）; 1）; - ））
  

对于第二步，似乎只有当您选择单元格时，才能重新计算数据验证公式，因此OFFSET在数据验证列表中不具有波动性。由于INDEX无法返回范围，Excel不允许用于数据验证的INDEX（..）：INDEX（..）范围，OFFSET对于数据验证列表更好。

Imagine I'm writing a menu-planner in Excel for my kids (easier to describe than my actual problem) ...

I have a list of available foods: apples, bananas, carrots, dates, eggs, fish, etc.

I have a list of kids: John, Karen, Lional, Mike, etc.

Then, I have a simple table that lists the food each kid likes: Under John's column there will be an 'x' against apples, bananas and fish, but blanks against the others.

     J  K  L  M
---------------
a    x  x  x
b    x     x
c       x  x  x
d       x
e          x  x
f    x

Now, in the main part of my menu-planner, I want to have some cells with data validation that allow me to select food for each kid, using the above 'likes' table:

Name  Food
A2    B2

Cell A2 will contain data validation that gives an in-cell drop-down with all kids names (J, K, L, M) (that's easy - I can do that bit!)

Cell B2 needs to contain a dynamically generated list of foods that are liked by the selected kid. So, if I select John in A2, then B2 list would be {a, b, f}. If I select Lionel, B2 list would be {a, b, c, e}. Clearly, as my kid's tastes change, I can just update my 'likes' table by adding/removing 'x', and the drop-downs in B2 will auto-update.

How do I create the drop-down validation list for cell B2? (I'd prefer to do this without resorting to VBA please)

解决方案

I assumed that your data table is in range A1:E7.

Step 1. Create a list of choices for each kid

For each kid create a list with all their preferences listed (at the end of the list I added "-" as placeholders). Enter this formula in G2 and drag to range 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)))

Also put kids names above data columns (G1:J1).

Step 2. Create conditional data validation

Given that your first data validation list (name) is in cell L2 and you've followed step 1, use this formula for data validation for food:

=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));"-"))

This formula both excludes all empty choices ("-") in the list and gives the right list based on kid's name.

---

UPDATE. Alternative solution with INDEX/MATCH

OFFSET is a volatile formula (i.e. Excel recalculates it whenever there is any change in your workbook) so you might want to do this with INDEX instead. Here is the formula for my step 1 above:

=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);"-"))

As for the step two, it seems that formula for data validation gets recalculated only when you select the cell so OFFSET doesn't have volatility in data validation lists. As INDEX cannot return a range and Excel doesn't allow INDEX(..):INDEX(..) ranges for data validation, OFFSET is better for data validation lists.

这篇关于Excel：如何根据数据表创建动态数据验证列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！