Excel创建数据验证唯一项的动态下拉列表，无需新列

Question

我的表格是一份随数据刷新而不断增长的列表，如下所示：

Balham Halfords - P83690
Balham Halfords - P83690
Gloucester & Durham St - P83680
Gloucester & Durham St - P83680

在另一个表格中，我希望下拉列表的数据验证仅显示以下选项：

Balham Halfords - P83690
Gloucester & Durham St - P83680

问题在于，我不想在书中任何地方创建一个独立的Table A清单，并且我希望数据验证列表能够智能地适应进入Table A的新记录。

- Dasal Kalubowila

---

我不确定是否有任何方法可以在没有辅助列的情况下完成此操作。数据验证（无论是下拉列表）本质上都是非智能的。它只允许您输入一系列单元格-不允许公式。 - ImaginaryHuman072889

谢谢。我认为我的最佳选择是在新记录到来时通过宏创建一个独立的单列表。然后让数据验证列表范围成为该表。虽然不是理想的，因为用户必须在添加到表A时点击按钮，而且人们无法处理这种类型的指令。 - Dasal Kalubowila

我同意需要一个辅助列。如果你想避免使用VBA，可能可以用公式来完成。 - ImaginaryHuman072889

我发布了一个新答案，修改了@KresimirL.的答案，不使用OFFSET，这可能会对你有所帮助。 - TotsieMae

Answer 1

如果您想使用公式的帮助列方法，请尝试此解决方案。 如果您的数据在A列中，请在单元格D2中输入以下公式：=IFERROR(INDEX($A$2:$A$900, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$900), 0)),"")，并将其向下拖动直到您估计有不同值为止。由于这是一个数组公式，所以必须使用CTRL+SHIFT+ENTER进行输入。

然后使用数据验证，选择列表，在源中输入以下公式=OFFSET(D2,0,0,198-COUNTBLANK(D2:D200),1)

现在，每当向表中添加新值时，它们也会自动添加到数据验证列表中。

- Kresimir L.

---

很棒的解决方案。不知道数据验证可以使用公式。 - ImaginaryHuman072889

谢谢，现在我很好奇是否可以将整个数组存储在DataValidation列表中，从而消除辅助列的需要...？ - Kresimir L.

谢谢Kresimir。新的数组公式可以工作，但是使得工作簿变得非常缓慢。每次有新条目输入时都会重新计算，并且您可以在右下角看到计算百分比。VBA选项只是为了快速创建数据验证的唯一列表，还是更复杂的选项？ - Dasal Kalubowila

是的，这个数组公式往往会使工作簿变得非常缓慢，因此它只应该用于小数据集。创建VBA解决方案并不难，可以尝试查看这里获取更多信息。您还可以创建一个数据透视表，并在每次添加新行时刷新它...这将消除对VBA的需求... - Kresimir L.

数据验证允许公式，但由于某些原因，UNIQUE无法正常工作，这将使其更加容易，并且无需创建辅助列。 - David Leal

Answer 2

@DasalKalubowila，这里是对@KresimirL答案的修改，可能是您正在寻找的。

首先，为您的输入数据创建一个定义名称。方法是在功能区中选择公式，然后在定义名称组下单击名称管理器。

在我的示例中，我将输入数据范围称为InputData。我使用的公式是

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$501,MATCH("Ω",Sheet1!$A$2:$A$501))

其中

• Sheet1 是输入数据所在的工作表名称，

• $A$2 是输入范围中包含数据的第一个单元格（我称之为锚点），

• $A$2:$A$501 是您的数据可能存在的列的最大区域，

• "Ω" 是希腊字母Omega。您可以通过按住ALT键并在10键数字键盘上依次按下2、3、4来获取它（也可以在Windows的字符映射应用程序中找到它）。

这个公式根据存在的条目数量有效地扩展或缩小了您的数据范围。

接下来，您需要创建一个辅助列。我知道这不是您想要的，但这将是其中唯一/更好的方法之一。我把我的放在与我的输入数据相同的工作表上，但您不必这样做。我在E2中使用的公式是

=IFERROR(INDEX(InputData,MATCH(0,COUNTIF($E$1:$E1,InputData),0)),"")

你需要使用 Ctrl+Shift+Enter 来提交这个数组公式。然后将该公式向下拖动到你需要的行数。你基本上需要向下拖动与你认为会有唯一条目相同的行数。

然后我需要创建一个更多的定义名称，这将在下一步中在我的数据验证下使用。我称这个新定义的名称为ValidationList（这需要作用于工作簿）。我用于ValidationList的公式是

=Sheet1!$E$2:INDEX(Sheet1!$E$2:$E$501,COUNTIF(Sheet1!$E$2:$E$501,">*"))

查看上面有关InputData的注释以更好地理解这个公式。唯一的区别是，该公式使用COUNTIF而不是MATCH。这是因为如果你的唯一值尚未填充你拖动到E列 (在之前的步骤中)，则与以前相同的方式使用MATCH会抓取我们不需要的许多空白。因此，COUNTIF仅计算那些包含大于“*”的值的单元格（星号是任意字符的通配符，“”不包含任何字符，因此它排除了这些项目）。

现在，创建您的数据验证并将其设置如下：

现在，你应该只剩下这个了：

当您向InputData区域添加信息时，您的ValidationList范围应扩展以包括最新的唯一值，这将反过来填充到Data Validation区域中，如下所示： 我发现这似乎不会显著减慢我的工作簿速度，但我很想听听它在您的电脑上的表现如何。

- TotsieMae

---

totsiemae - 非常感激您的乐意帮助。最终我选择了轻量级VBA而非较重的索引和匹配公式。现在用户将数据添加到表A并运行宏，以创建帮助列。数据验证源现在是该列或=INDIRECT("Table5[Description + Code]")。 - Dasal Kalubowila

Answer 3

如其他人所说，很难避免使用辅助列。但是如果您使用的是MS 365，您可以在数据验证框中简化公式，只需使用简单的单元格引用，使用#后缀。

根据原帖所说，数据在Excel表格A中，这是一个好的开始（如果不在表格中，请将其放入表格中！）。

如果表格列为（假设为）Name，那么在单元格F2中创建辅助列公式，如=SORT(UNIQUE(A[Name]))。

然后在单元格 D1 的数据验证中：

注意：如果计算设置为“自动”，则此设置效果最佳。

- DS_London