excel表格中的有效性设置

[原创]EXCEL有效性设置的常见场景及方法

EXCEL单元格可以限定可以输入的内容，有一方法，就是既定的规律化的输入内容作为选项，在有效性设置地方按“序列”进行设置。但是启用这种“序列”的方法也是有多种的，从序列的来源划分的话。下面，简要描述相关场景及其设置方法：

1.序列值简单，直接输入序列值

序列项目较少，并且稳定不变的，可以直接在相应的单元格设置有效性的时候，在数据来源输入具体的选项。加入，我希望A1单元格只能输入“未开始、进行中、已完成、已延期”四个选项中的一个，那么，步骤如下：

（1）选中需要设置有效性的单元格区域，本处是A1;

（2）选择菜单“数据——有效性”，对话框允许选择“序列”；

（3）在数据有效性对话框中，来源输入以下内容：未开始,进行中,已完成,已延期

注意，直接在序列输入数据值：一不必在最前加一个“=”号；二，选项值之间的逗号是英文输入状态下的逗号，而不是汉文输入状态下的逗号。

（4）确认，即可发现A1单元格的下来选项只有设定的四个选项。

（5）当然，在（3）步如果需要可以输入“输入信息”和“出错警告”信息。输入信息是，设置有效性的单元格被选中，将出现的输入提示性信息；而出错警告是，设置有效性的单元格输入错误，出现的警告信息或说明。

2.有效性设置序列在同一个Excel文件，并且与设置有效性的单元格区域在同一个电子表

假设，以上四个选项的值和A1都在一个文件的BOOK1中，并且四个选项值在I1到I4。此时，设置有效性步骤如上，但是序列的数据源设置为：=$I$1:$I$4 注意，前面有“=”号。

3.有效性设置序列在同一个Excel文件，但是与设置有效性的单元格区域不在同一个电子表

假设，需要设置有效性的单元格A1在BOOK1中，而四个选项值的清单在Bo ok2的A1到A4区域。

此时，直接按场景2进行设置，将无法进行，因为数据源，直接引用单元格数据，必须保证设置单元格有效性的区域和序列数据源在同一个电子表。

解决的方法：

（1）在BOOK2中，将单元格区域A1:A4命名为“完成状态”（这个命名可以是英文的，也可以是汉文的）。

（2）在BOOK1中，选择单元格A1，设置有效性数据源时输入：=完成状态

通过以上两步，就可设置成功。这种模式，在一些EXCLE模板中，标准数据单独放在DATA表中，业务表中，引用DATA表基本数据的情况参见。

（4）有效性设置的高级应用之一：利用库函数实现多级有效性动态控制

A1的选项清单是a1到a5，B1的选项清单，有a1到a5决定的。这种场景下，B2的选项清单是动态的，具体方法如下：

在单独的DATA表中，a1到a5作为一个源，命名为“一级选项”，每个一级选项对应的子选项清单，在另外的区域设置，并将这些区域用其对应的一级选项值进行命名。例如，B1第一个选项来源对应a1，有10个选项，这10个连续选项区域统一命名为：a1；其他类似。

在Book1中，A1按照第三种方法进行设置，在B1单元格中，数据源设置为：=INDIRECT（A1）。该函数的意思是，通过INDIRECT调用A1单元格对应的值，而A1单元格对应的值，又代表一个可以选择的多个值的清单，因此，最终的结果就是B1的可选项目，将随着A1单元格的选项内容变化而发生变化。

以上第四种场景中，综合运用了，前几种方法的设置内容，并且启用了EXC EL自身的库函数:INDIRECT()。

以上是EXCEL中，对单元格进行有效性设置的主要内容。值得用户注意的时候，设置了有效性，并不能完全保证，其他用户使用你设置的这个表不出错——实际上，只要其他人打开你设置有效性的文件，在设置有效性的区域进行粘贴操

作后，原先的有效性设置内容就被覆盖了。要防止，这种情形情形出现，只能通过其他方法，控制用户，不得改变设置有效性区域的有效性设置。

（5）利用VBA实现单元格的有效性控制

利用VBA可以调用EXCEL本身的宏命令或控件实现有效性控制，当然，也可以通过VBA实现调用其他文件，如ACCESS数据源，进行有效性控制。本处暂不详细描述了，因为一般情况下，不会弄得这般复杂。