一个单元格中多个条件的 Excel 匹配索引 [英] Excel Match Index for Mutliple Criteria in one cell

问题描述

我有一个电子表格，它是根据 Microsoft Sharepoint 中的数据自动填充的.不幸的是，此数据按如下方式填充了我的位置列:IND;# ENG ;# USA 如果例如该特定输入有 3 个位置.

I have a spreadsheet which is filled automatically from data in Microsoft Sharepoint. Unfortunately this data fills my location column as follows: IND;# ENG ;# USA if for example that particular input has 3 locations.

然后我有下拉框(通过数据验证制作)来过滤结果并给出最佳结果.但是，在选择位置时，我可以选择 ALL、IND、ENG、USA 或 IND；# ENG；# USA.例如，如果我选择了 IND，则不会返回位于多个位置的选项(只有位于仅 IND 中的选项才会返回).

I then have drop down boxes (made via data validation) to filter the results down and give an optimum result. However when it comes to choosing location i can either choose ALL, IND, ENG, USA OR IND;# ENG ;# USA. If I chose, for instance, IND, then the options that are in multiple locations will not be returned (only the options that are in only IND will).

我只是想知道是否有任何方法可以避免使用 VBA，它可以选择单个位置以返回该位置的所有选项，包括在多个位置可用的选项.

I was just wondering if there is any way, avoiding VBA, which will enable a choice of the individual location to return all options in this location, including the options that are availible in multiple locations.

** 请注意，这些多个位置包含在同一个单元格中.

** Please note that these multiple locations are contained within the SAME cell.

推荐答案

这是不可能的，因为你已经写好了问题.数据验证创建的下拉列表只能显示单个结果.如果您选择了IND"并想显示IND"和IND、ENG"的结果，则您需要使用下拉列表以外的其他内容，或者您​​需要根据在您的下游选择的值进行额外过滤代码/逻辑.

It's not possible as you have the question written. A drop down list created by data validation can only display a single result. If you chose 'IND' and wanted to show results for say 'IND' and 'IND, ENG' you would need to use something other than a drop down list or you need to do additional filtering based on the value selected down stream in your code/logic.

我认为最好的办法是使用 VBA.这对用户来说是透明的.当用户在下拉列表中选择一个值时，vba 可以根据选择的内容(又名相关下拉列表)对剩余的下拉列表进行排序和过滤

I'd say your best bet is to use VBA. This can be transparent from the user. When a user selects a value in the drop down list, vba can sort and filter the remaining drop down list based on what was selected (aka dependent drop downs)

这篇关于一个单元格中多个条件的 Excel 匹配索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！