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
如果我在第一个单元格中选择 One，则第二个单元格中的列表应包含以下选项:
If I select One in the first cell, the list in the second cell should contain these choices:
One:
1.1
1.2
1.3
如果我在第一个单元格中选择两个，则第二个单元格中的列表应包含以下选项:
If I select Two in the first cell, the list in the second cell should contain these choices:
Two:
2.1
2.2
2.3
等等.周围有很多教程，但我在弄清楚其中哪些解决了这个确切的问题时遇到了一些麻烦.
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 时组标题 (col A) 下的组 1，右侧 (col D) 组 1 下列出的条目应出现在项目标题 (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.
推荐答案
按承诺更新:
当您使用 List 进行验证时，您必须输入如下所示的范围.
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)
在这种情况下，返回的范围将是 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 函数中的第二个参数，并选择与 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由于 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.
最后，我们可以将 MATCH 函数中的Group2"值更改为一个单元格值，这意味着范围将动态变化.
And finally we can change the "Group2" value in the MATCH function to a cell value that will mean the range will dynamically change.
这里我使用了 Cell 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屋！
