Excel：下拉列表取决于其他下拉列表 [英] Excel: Dropdown list dependant on other dropdown list

问题描述

我想在Excel中找到以下内容：

I want the following in Excel:

相邻单元格中的两个下拉列表：

Two dropdown lists in adjacent cells:

下拉列表＃ 1 |下拉列表＃1

Dropdown list #1 | Dropdown list #1

Dropdown list 1:
One
Two
Three

如果我在第一个单元格中选择了一个，则第二个单元格中的列表应包含以下选项：

If I select One in the first cell, the list in the second cell should contain these choices:

One:<br/>
1.1<br/>
1.2<br/>
1.3

如果我在第一个单元格中选择了两个，则第二个单元格中的列表应该包含这些选项：

If I select Two in the first cell, the list in the second cell should contain these choices:

Two:<br/>
2.1<br/>
2.2

等等。有很多教程，但我有一些麻烦，弄清楚哪些解决这个确切的问题。

And so on. There are a lot of tutorials around, but I'm having some hassle figuring out which of them addresses this exact question.

更新：一个例子。选择f.ex.组1标题（col A）下的组1，右侧（col D）下列出的条目应显示在项目标题（col B）下。对于其他群组，相同。

Update: An example. When selecting f.ex. Group 1 under the Group heading (col A), the entries listed under Group 1 to the right (col D) should appear under the Item heading (col B). And the same for the other Groups.

推荐答案

更新为承诺：

重新使用列表进行验证，您必须输入如下所示的范围。

When you're using a List for validation, you have to input a range as shown below.

OFFSET 功能允许根据其输入条件动态设置范围。

The OFFSET function allows to to dynamically set a range based on its input criteria.

如果你考虑这个：

=OFFSET(C1,0,0,1,1)

• 参数1 =锚点单元格


• 参数2 =要移动的行数您可以使用减号来移动行数和正数以向下移动


• 参数3 =要移动的列数。


• 参数4 =范围的高度（不能为负，是可选的，默认为1）

<参数5 =范围的宽度（不能为负，可选，默认为1）

• Argument 1 = Anchor cell
• Argument 2 = Number of rows to move, you can use minus number here to move rows up and positive numbers to move down
• Argument 3 = Number of columns to move. Negative is left, positive to the right.
• Argument 4 = Height of the range (can't be negative and is optional, default is 1)
• Argument 5 = Width of the range (can't be negative and is optional, default is 1)

在这种情况下，返回的范围将为 C1 ，因为我们没有行或列偏移量，高度和宽度设置为1

In this instance, the range returned would be C1 as we have no row or column offset and height and width is set to 1

MATCH 函数将返回一个值在单元格范围内出现的索引（范围必须是1个单元格宽或1个单元格高）

The MATCH function will return an index of where a value appears in a range of cells (range must be either 1 cell wide or 1 cell high)

基于上述屏幕打印 = MATCH（Group2，D1：F1,0）将返回2，因为Group2出现在 D1中的第二个单元格中：F1 范围。 （Group1将返回1，Group3将返回3，Group4将返回＃N / A，因为它不存在）。

Based on the above screen print =MATCH("Group2",D1:F1,0) will return 2, as "Group2" appears in the second cell in the D1:F1 range. ("Group1" would return 1, "Group3" would return 3, and "Group4" would return #N/A as it doesn't exist).

因此，我们可以将 MATCH 函数作为 OFFSET 函数中的第二个参数，并选择匹配的列 MATCH 函数中的第一个参数。

So based on that we can put the MATCH function in as our 2nd argument in the OFFSET function, and pick the column that matches the first argument in the MATCH function.

= OFFSET（C1,0，MATCH （Group2，D1：F1,0），1,1）将返回范围 E1 ，因为我们已将列移动2因为 C1 因为 MATCH

=OFFSET(C1,0,MATCH("Group2",D1:F1,0),1,1) will return back range E1 as we've shifted the columns by 2 from C1 because of the MATCH

= OFFSET（C1,1，MATCH（Group2，D1：F1,0），3,1）现在将返回 E2：E4 因为我们将范围的高度增加到3，行偏移量增加到1。

=OFFSET(C1,1,MATCH("Group2",D1:F1,0),3,1) will now return back E2:E4 as we've increased the height of the range to 3 and the row offset to 1.

最后我们可以更改Group2 c> MATCH 函数到单元格值将意味着范围将动态更改。

And finally we can change the "Group2" value in the MATCH function to a cell value that will mean the range will dynamically change.

这里我已经使用单元格 A2 = OFFSET（C1,1，MATCH（A2，D1：F1,0），3,1）所以有价值在单元格中，A2 将用于抵消范围。

Here I've used Cell A2 =OFFSET(C1,1,MATCH(A2,D1:F1,0),3,1) so whatever value is in cell A2 will be used to offset the range.

最后一件事是将动态范围放入验证中（我使用 B2 ）

And the last thing to do is to put the dynamic range into the validation (I used B2)

这将动态设置验证范围。

This will dynamically set the validation range.

当我正在使用具有多个参数的 OFFSET 函数，我不知道它返回正确的范围，我写了一个小帮手用户定义的函数，我刚刚放在VBA模块。

When I'm using OFFSET function with multiple arguments and I'm not sure that it's returning back the right range, I wrote a small helper User Defined Function that I just put in a VBA module.

Public Function GetAddress(rng As Range) As String
GetAddress = rng.Address
End Function

这允许我将偏移公式放入，并返回范围地址。所以我可以确定它是正确的。

This allows me to put the offset formula in and it will return back the range address. So I can make sure it's right.

可能有一个内置的功能，但我从来没有找到它。

There may be a built in function for this, but I've never found it.

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