Excel实战技巧62： 获取不重复的值作为数据验证列表项

“数据验证”（在Excel 2013以前称为“数据有效性”）是Excel中的一项重要功能，它能够提供下拉列表供用户选择输入项，也能限制用户输入符合设定的数据。

选择要设置数据验证的单元格或单元格区域，在功能区“数据”选项卡中，选取“数据验证——数据验证…”命令，弹出“数据验证”对话框，在“允许”下拉列表中选择相应项，在“来源”中输入数据条件，如下图1所示。

图1

设置好的数据验证如下图2所示，本例中给单元格H1设置数据验证。

图2

这样，在单元格H1中，我们可以从下拉列表中选择输入项了。

然而，细心的朋友可能注意到，在单元格H1的下拉列表中，原原本本地照搬了列A中的数据，其中有很多重复项，这显然是我们所不需要的。

如何基于已有数据在数据验证列表中填充不重复的数据项呢？下面介绍几种方法。

方法1：使用公式获取不重复值

如下图3所示，选择单元格E2，输入用于获取不重复值的数组公式，然后下拉至数据末尾，得到不重复项列表。

图3

定义一个命名公式NameByFormula：

=Offset(Sheet1!$E$2,0,0,counta(Sheet1!$E$2:$E$50),1)

如下图4所示。

图4

选择单元格H1，打开上图1所示的“数据验证”对话框，在序列来源中输入：=NameByFormula。单击“确定”按钮，数据验证设置完成。

方法2：利用数据透视表获取不重复值

选择单元格E1，插入数据透视表，数据源为数据区域A1:A14，得到结果如下图5所示。

图5

参见上图4，定义一个命名公式NameByPivot：

=Offset(Sheet1!$E$1,1,0,CountA(Sheet1!$E$2:$E$50),1)

选择单元格H1，打开上图1所示的“数据验证”对话框，在序列来源中输入：=NameByPivot。单击“确定”按钮，数据验证设置完成。

方法3：使用Office365中的新功能—动态数组

选择单元格F1，输入公式：

=SORT(UNIQUE(表1[名称]))

此时，Excel会自动将列中的不重复值分别输入到下面相邻的单元格中，如下图6所示。

图6

参见上图4，定义一个命名公式NameByDA：

=Offset(Sheet1!$F$1,0,0,CountA(Sheet1!$F$1:$F50),1)

选择单元格H1，打开上图1所示的“数据验证”对话框，在序列来源中输入：=NameByDA。单击“确定”按钮，数据验证设置完成。

实际上，对于Office 365来说，在定义命名公式时还有一种更简单的方法，如下图7所示，直接在“引用位置”输入：=F1#，告诉Excel想要获取该列完整的数据。

图7

甚至可以不定义命名公式，直接选择单元格H1，打开上图1所示的“数据验证”对话框，在序列来源中输入：=F1#，如下图8所示。

图8

Office365推出了很多方便的功能，看来确实是需要好好探究一下了！