基于数据透视表样式数据源的 xls 级联查找？
假设数据集看起来像
Country Region Product
UK North fdhlw46
UK North fdhlw47
UK North fdhlw48
UK North fdhlw49
UK North fdhlw50
UK South fdhlw51
UK South fdhlw52
UK South fdhlw53
UK South fdhlw54
UK South fdhlw55
UK South fdhlw56
UK South fdhlw57
UK West fdhlw58
UK West fdhlw59
UK West fdhlw60
UK London fdhlw61
UK London fdhlw62
USA New York fdhlw63
USA New York fdhlw64
USA New York fdhlw65
USA New York fdhlw66
USA Chicago fdhlw67
USA Chicago fdhlw68
USA Chicago fdhlw69
USA Chicago fdhlw70
USA LA fdhlw71
USA LA fdhlw72
USA LA fdhlw73
USA LA fdhlw74
USA LA fdhlw75
如何在用户选择的 Excel 中获取级联下拉列表/选择列表，然后缩小到区域并最终缩小到产品？
到目前为止，我所拥有的只是一个名为“国家”的范围，然后我可以“数据 - 验证 - 来源：=国家”，但这只给我第一个值，并且它不会删除重复项
Assuming dataset looks like
Country Region Product
UK North fdhlw46
UK North fdhlw47
UK North fdhlw48
UK North fdhlw49
UK North fdhlw50
UK South fdhlw51
UK South fdhlw52
UK South fdhlw53
UK South fdhlw54
UK South fdhlw55
UK South fdhlw56
UK South fdhlw57
UK West fdhlw58
UK West fdhlw59
UK West fdhlw60
UK London fdhlw61
UK London fdhlw62
USA New York fdhlw63
USA New York fdhlw64
USA New York fdhlw65
USA New York fdhlw66
USA Chicago fdhlw67
USA Chicago fdhlw68
USA Chicago fdhlw69
USA Chicago fdhlw70
USA LA fdhlw71
USA LA fdhlw72
USA LA fdhlw73
USA LA fdhlw74
USA LA fdhlw75
How can I get cascading dropdowns/picklists in excel where the users selects, the country, which then narrows down to region and finally product?
So far all I have is a range called 'country' which I can then "data - validation - source: = country", but this only gives me the first value, and it doesn't strip duplicates
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
http://www.contextures.com/xlDataVal13.html
这种方法确实意味着定义一个唯一的每个分组的一组值，但这很简单。
感谢您的其他想法。
http://www.contextures.com/xlDataVal13.html
This approach does mean defining a unique set of values for each grouping, but this is trivial.
Thanks for the other ideas.
选择范围，然后数据 -> 过滤器-> 自动过滤器。 这能达到您想要的效果吗？
Select the range then Data -> Filter -> Autofilter. Does that achieve what you're looking for?
我认为您想使用 INDIRECT 函数来查找单元格的值，而不是直接在数据验证中定义查找范围。 看看此页面很好地解释了这一点。
但请注意，像这样的多个级联验证有其自身的特殊问题。 主要是，如果您进行第二个（或第三个等）选择，然后返回并编辑第一个选择，则不会有自动标志表明该选择现在可能无效。 例如，如果您有以下内容：
...然后更改第一列：
...那么您现在可能遇到问题。 找到这种不一致可能非常困难，或者需要一些代码来突出显示/修复。 DDoE 博客展示了一种使用条件格式来突出显示此类错误的方法，但这需要大量计算，并且扩展性不佳。
请注意，如果大量使用这种性质的多次查找，即使不使用条件格式，也会使工作簿崩溃。
总而言之，对于数据输入受到严格控制的小型项目来说，这是一种灵活的级联下拉列表方法，但如果您对用户几乎没有控制，或者如果收集的数据将增长到超过数百行，那么您'您可能想考虑使用 VBA 来验证验证（！），或者只是全力以赴并强制通过用户表单进行输入，这是迄今为止最好的选择。
编辑： 示例工作簿
再次编辑：如果数据绝对有为了保持原样，则需要一个高级过滤器来创建验证中使用的命名范围。 在这种复杂程度下，您最好只使用表单来输入数据。
I think you want to use the INDIRECT function to lookup the value of a cell, rather than define a lookup range directly in the data validation. Have a look at this page which explains it fairly well.
Be warned though that multiple cascading validations like this have particular problems of their own. Chiefly, if you make a second (or third etc.) selection, then go back and edit the first selection, there is no automatic flag to say that the selection may now be invalid. For example, if you have the following:
...and then change the first column:
...then you may now have a problem. Finding such inconsistencies can be very hard, or requires some code to highlight/fix. The DDoE blog shows one method to highlight such errors using conditional formatting, but this is very calculation intensive and doesn't scale well.
Be aware that multiple lookups of this nature can bring a workbook to its knees if used liberally, even without using conditional formatting.
All in all, for a small project where data entry is tightly controlled this is a flexible method of cascading drop down lists, but if you have little control over the users, or if the data collected will grow past a few hundreds lines then you'll probably want to look at validating the validation (!) using VBA, or just going the whole hog and forcing entry through a userform, which is by far the best option.
Edit: Example workbook
Edit again: If the data absolutely has to stay the way it is, then an advanced filter which creates the named ranges used in the validation would be required. At this level of complexity though you'd surely be better off just using a form for data entry.