Excel动态下拉列表按筛选表 [英] Excel dynamic drop down List by filtered table

问题描述

我对Excel中的数据验证列表有疑问.

Hi I have a problem with the data validation list in Excel.

首先，我在一个受保护的选项卡中有一个表(名称:Article)，该表具有四个属性(名称，性别，颜色，价格).两种或两种以上的文章可能具有相同的名称，但性别(男性或女性)或肤色不同.

First of all I have one table(named: Article) in a protected Tab with four attributes (Name, Sex, Colour, Price). It is possible that two or more articles have the same name and differ by Sex (male or female) or by colour.

Name         | Sex       | Colour     | Price
-------------|-----------|------------|---------
Product1     | Male      | Blue       | 10
Product1     | Male      | Green      | 10
Product1     | Female    | Pink       | 5
Product2     | Male      | Grey       | 8
Product2     | Female    | Orange     | 8

在主选项卡中，我将订单组织在另一个表中(称为:订单).

In the main Tab I organized the order in an different table (called: Order).

Product      | Sex       | Colour     | Quantity   | Price
-------------|-----------|------------|------------|---------
<List>       | <List>    | <List>     |            | <Calculation>

产品的下拉列表(数据验证|列表|来源-> = INDIREKT("Article [Name]"))

The dropdown List for Product (Data Validation | List | Source -> =INDIREKT("Article[Name]"))

和性别(数据验证|列表|来源->男性；女性)很容易.

and Sex (Data Validation | List | Source -> Male; Female) is easy.

但是我如何获得颜色列表-取决于产品名称和性别?

But how I get a list for Colour - depended on Productname and Sex?

例如，产品=产品1"和性别=男性"，则颜色下拉列表应包含蓝色和绿色.

For example Product = Product1 and Sex = Male the drop down for colour should contain Blue and Green.

推荐答案

首先，您需要更改表的结构.复制并使用数组公式将其转置，并输入为 Ctrl + Shift + Enter :

First you need to change the structure of your table. Either copy and transpose this with an array formula, entered as Ctrl+Shift+Enter:

=TRANSPOSE(TABLE)

您需要使用以下格式的表:

You need your table in the following form:

Product 1 | Product2 | Product 3

Green     | Grey     | Pink

Blue      | Orange   | White

然后，将这些列中的每一个定义为一个命名范围.您可以选择整个表格，然后转到公式">从选择中创建"(在定义的名称"部分中).仅标记顶行"，然后单击确定".现在，您已经命名了可以在从属或级联下拉列表中使用的范围.在这一篇文章中，您的来源将是:

Then, you define each of those columns as a named range. You can select the entire table and go to Formulas > Create from selection (in the Defined Names section). Mark only "Top row" and click ok. Now you have named ranges you can use in your dependent or cascading dropdown list. In this one, your source will be:

=INDIRECT(A1) 'Replace A1 with the location of your first-level dropdownlist

当然，如果您需要在辅助下拉菜单中添加额外的条件，它将变得更加复杂.首先，您的表必须具有以下结构:

Of course, it becomes considerably more complicated if you need to add an extra condition to your secondary dropdown. Firstly, your table needs to be in the following structure:

Product 1Male | Product1Female | Product 2Male | Product 2Female

Green         | Grey           | Pink          | Red

Blue          | Orange         | White         | Pink

与您相关的下拉菜单的公式为:

And the formula for your dependent dropdown would be:

=INDIRECT(A1&A2) 'Where A1 and A2 are the locations of your other 2 dropdown lists.

这应该可以根据您的需要工作.让我知道是否需要进一步澄清.

That should work as you need it. Let me know if further clarification is needed.

这篇关于Excel动态下拉列表按筛选表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！