☰

根据公式创建自定义数据验证列表

tag-icon • tag-icon microsoft-excel database
根据公式创建自定义数据验证列表

我正在创建一个涉及多个国家/地区多个供应商的快速数据库。在 1 个工作表（标题为“供应商列表”）上，有一个国家/地区、供应商、供应商代码和地区代码的列表。在第二个工作表（“数据库”）上，有一个更大的数据库，其中包含更多列，但包括“供应商列表”中的所有列。我想要的是，当我在“数据库”中输入国家/地区时，我希望供应商列自动创建一个列表，以便只能选择该国家/地区的供应商。

仅使用 Excel 的内置公式可以实现这一点吗？

“供应商列表”示例 “数据库”示例

答案1

如果您希望创建一个“下拉”列表，将供应商的选择限制为与相邻单元格中已经选择的国家/地区相关的供应商，那么这里有一些 Excel 函数可以帮助您。

在“供应商列表”中，MATCH可以使用该函数查找特定国家/地区在“国家/地区”列中首次出现的位置。COUNTIF可以使用该函数查找同一特定国家/地区在“国家/地区”列中出现的次数。利用这两个数字，OFFSET可以使用该函数识别包含“供应商”列中与特定国家/地区相对应的供应商列表的范围。

例如，如果供应商列表在所说的范围内Vendors!A1:D5，而特定国家在所说的单元格中Data!A2，则包含相应供应商的范围将由以下方式提供：

=OFFSET(Vendors!$B$1,MATCH(Data!A2,Vendors!$A$1:$A$5,0)-1,0,COUNTIF(Vendors!$A$1:$A$5,Data!A2),1)

出于后面将解释的原因，该公式的更通用、更有用的版本是：

=OFFSET(Vendors!$B$1,MATCH(Data!A2,Vendors!A:A,0)-1,0,COUNTIF(Vendors!A:A,Data!A2),1)

这里有一个重要要求：供应商列表中的行必须按国家/地区排列 - 所有巴西行必须连续出现，并且供应商列表中出现的任何其他国家/地区也必须类似。如果不满足此条件，则上述公式生成的供应商范围将不正确。

确保满足要求的最简单方法是按国家/地区对供应商列表进行排序。

Data!B2可以通过选择单元格并单击数据菜单项下功能区中的“数据验证”按钮来将数据验证应用于单元格。将出现一个数据验证对话框。在允许字段中选择列表，然后OFFSET()使用数据验证对话框将上述更通用的公式粘贴到源字段中。

如果数据验证成功实施，则每当Data!B2选择作为活动单元格时，其右侧都会出现一个下拉箭头，单击箭头将生成与单元格中显示的国家/地区匹配的供应商列表Data!A2。

将单元格Data!B2向下复制到主“数据库”中的其余数据行，将使列中的所有单元格与相同的数据验证相关联 - 单元格的下拉列表Data!B3将基于 的国家/地区Data!A3，Data!B4的列表将基于Data!A4，等等。如果 中的相应单元格为空，则仍会出现下拉箭头，A但不会显示下拉列表。可以在将任何值输入单元格之前进行复制/粘贴操作Data!B2

笔记：

  1. 使用更通用的 OFFSET() 公式，可以通过在 中添加行来扩展供应商列表，而无需更改应用于 单元格的数据验证Data!B。（请注意，扩展列表可能需要按国家/地区排序，以满足供应商列表中国家/地区必须分组在一起的要求）。如果使用不太通用的版本，则每次扩展供应商列表时都需要更新数据验证公式。

  2. 在实际数据库中，内置功能可确保数据的完整性。例如，尽管下拉列表可确保列中的供应商与首次将供应商值输入工作表时Data!B的国家/地区一致，但无法阻止国家/地区随后被更改，这将破坏其与同一行中显示的供应商值之间的一致性。Data!AData!AData!B

  3. 此外，国家名称同时出现在主“数据库”和供应商列表中。真正的数据库将确保两个国家之间不会出现不匹配的情况。可以通过在 Excel 中添加额外列来标记不匹配的情况，但很难防止这种情况发生。供应商名称也是如此。

相关内容