非宏解决方案，用于从单元格内数据验证列表中排除非空白空字符串

Question 1

数据验证下拉菜单不会配合你。它只想查看静态列表，或者你告诉它的单元格，而不考虑其中的内容。

既然我们无法让它做我们想做的事，那就让我们妥协吧——我们将给它一个连续的范围，只显示我们想要看到的值（加上最后的空白，人们大多不会看）。

在我的示例中，包含数据验证列表的值（和空白）的单元格是 A1:A15。我将转到另一列并输入以下公式：

=IFERROR(INDEX($A$1:$A$15,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15),999),ROW(1:1))),"")

我需要将其输入为数组公式，因此在输入完公式后，我将按 CTRL + SHIFT + ENTER。接下来，我将复制/粘贴或填充 15 行。结果将是 A1:A15 中的所有非空白值，然后是最后的所有空白。

现在，我将打开“数据验证”对话框，并将列表指向包含公式的单元格（不是 A1:A15）。空白仍在那里，但在末尾，因此除非向下滚动到那里看不到任何东西，否则没人需要看到它们。

它不是完全干净的，但它是动态的并且避免了宏。

那么这个公式起什么作用呢？

=INDEX($A$1:$A$15,

查看包含您的值和空白的原始范围，并让您通过数字告诉它您想要查看哪个单元格。

SMALL(

看一组数字，从最小的数开始数，然后返回一个，

IF($A$1:$A$15<>"",

检查范围内的每个单元格是否匹配除空白字符串之外的任何内容

ROW($A$1:$A$15)

如果有则返回行号，

,999),

如果没有，则返回 999。如果行数超过 1000 行，则需要更大的数字，但这（希望）足以满足您的数据验证选择。SMALL 函数现在有一个行号列表，其中 999 代替任何空白的实际行号。

ROW(1:1)

是一种动态方式，从您输入此公式的任何单元格开始计数。当您向下复制公式时，数字会增加。这告诉小数，您想要包含公式的第一行中的第一个最小数字，第二行中的第二个最小数字，等等。

将SMALL行号返回给INDEX，后者为您提供该行的值。最后，这些 999 都变成了 #REF 错误，但我们使用悄悄地将它们变回空白IFERROR。

该公式可能难以理解。如果您遇到问题，请尝试在新工作表的 A1:A15 中设置示例数据，选择另一列中的单元格，然后将我的公式精确复制到公式栏中（不要忘记 CTRL+SHIFT+ENTER）。尝试一下，您应该会掌握窍门。

祝你好运！

Answer