按照承诺更新:
当您使用列表进行验证时,您必须输入一个范围,如下所示。
The OFFSET http://support.microsoft.com/kb/324991函数允许根据其输入条件动态设置范围。
如果你考虑这一点:
=OFFSET(C1,0,0,1,1)
- 参数 1 = 锚定单元
- 参数 2 = 要移动的行数,您可以在此处使用负数向上移动行,使用正数向下移动
- 参数 3 = 要移动的列数。左边为负,右边为正。
- 参数 4 = 范围的高度(不能为负数且可选,默认为 1)
- 参数 5 = 范围的宽度(不能为负数且可选,默认为 1)
在这种情况下,返回的范围将是C1
因为我们没有行或列偏移并且高度和宽度设置为 1
The MATCH http://office.microsoft.com/en-gb/excel-help/match-function-HP010062414.aspx函数将返回值在单元格区域中出现位置的索引(范围必须为 1 个单元格宽或 1 个单元格高)
根据上面的丝网印刷=MATCH("Group2",D1:F1,0)
将返回 2,因为“Group2”出现在第二个单元格中D1:F1
范围。 (“Group1”将返回 1,“Group3”将返回 3,“Group4”将返回 #N/A,因为它不存在)。
所以基于此我们可以把MATCH
函数作为我们的第二个参数OFFSET
函数,然后选择与第一个参数匹配的列MATCH
功能。
=OFFSET(C1,0,MATCH("Group2",D1:F1,0),1,1)
将返回范围E1
因为我们将列从C1
因为MATCH
=OFFSET(C1,1,MATCH("Group2",D1:F1,0),3,1)
现在将返回E2:E4
因为我们已将范围的高度增加到 3,并将行偏移量增加到 1。
最后我们可以更改“Group2”值MATCH
函数到单元格值,这意味着范围将动态变化。
这里我用的是CellA2
=OFFSET(C1,1,MATCH(A2,D1:F1,0),3,1)
所以无论单元格中的值是什么A2
将用于偏移范围。
最后要做的是将动态范围放入验证中(我使用B2
)
这将动态设置验证范围。
当我使用时OFFSET
函数具有多个参数,并且我不确定它是否返回正确的范围,我编写了一个小助手用户定义函数,我刚刚将其放入 VBA 模块中。
Public Function GetAddress(rng As Range) As String
GetAddress = rng.Address
End Function
这允许我输入偏移公式,它将返回范围地址。所以我可以确保它是正确的。
可能有一个内置函数可以实现此目的,但我从未找到过。