Excel数据有效性设置灵活应用（二）

来源：部落窝教育
网址：http://www.ittribalwo.com/article/3638.html
标签：Excel，数据有效性、设置
收藏：株野
作者：IT部落窝
日期：2016/10/15

---

内容提要：本文的Excel数据有效性设置教程分为上下篇，比较实用的行业运用案例分享给大家，希望对你了解excel数据有效性设置带来些帮助。

3  级联数据有效性

　　其实在上一节里，数据有效性已经基本解说完毕了，不过在具体工作中对于数据有效性在根据序列设置有效性时，可以和公式及定义区域结合形成更高级的有效性：类似于网页那样根据前边（或某一特定单元格）的内容允许不同区域的内容，例如，前边选择陕西，后边就只能选择输入陕西的地级市；如果前边选择的是四川，后边就只能选择输入四川的地级市……我们权且将其称为级联数据有效性，一起来试试。

　　例2：在工作需要根据不同区域公司名称输入不同的销售分店，要求分店必须准确（以方便转为分店代码进行其他操作），不能有错字或空格，所以最理想的方法便是提供下拉箭头选择输入。鉴于可以理解的原因，这个例子改为建立一个如下图所示的资料输入表，要求省可以下拉选择、市根据省下拉选择、区根据市下拉选择（象网页里输入那样）。

3.1  建立辅助资料表

　　因为要求提供下拉箭头选择输入，故只能使用序列设置数据有效性。我们以陕西、四川两个省的资料为例进行设置。

　　新建一个资料表，到网上搜索两省的行政区划资料，整理为如下图的格式：

　　同一行第1格为市名，同行其他格子为市下辖区/县，所有市的最顶端为省。现以陕西为例进行设置。（本文配套的Excel数据源文件，请到QQ群：488925627下载）

3.2  批量定义名称

　　先拖选红线单元格区域，然后点公式菜单中的名称管理器中的根据所选内容创建命令，在弹出的对话框“以下列选定区域的值创建名称”中选择最左列并确定，即将红线中的最左列设定为右侧对应内容的名称；再拖选蓝线单元格区域，同样操作，不过要勾选首行创建名称。这样市既是区/县的名称（名称框输入市名即选定该市下辖所有区/县的单元格），又是省所包含的内容（名称框输入省即选定省下辖的所有市的单元格）。

　　对四川资料表同样操作。

3.3  设置级联数据有效性

　　回到需要设置数据有效性的表，先设定省的数据有效性。在省字段下的第一个空格（图5的C2单元格，下边用单元格名进行说明），点数据-数据有效性-设置：允许序列，来源里直接输入“陕西,四川”（不含双引号，以英文逗号间隔），确定。

　　再设定市的数据有效性，选择D2，点数据-数据有效性-设置：允许序列，来源里输入：=indirect(C2)，确定，提示错误不要理会，因为当前C2里还没有输入值，所以报错。使用序列设置数据有效性要么手工输入序列内容，要么引用单元格，除此外不接受别的内容，这里我们引用C2的值（省名），根据C2的值来变，但我们已将各省名定义为所辖市的单元格区域名，但如果直接输入省名，就只能选输入的内容；而indirect函数可以将定义的名称转为区域，符合Excel的规范。

　　设定区/县的数据有效性，选择E2，点数据-数据有效性-设置：允许序列，来源里输入：=indirect(D2)，确定，提示错误不要理会。

　　最后选择C2:E2向下拖选填充（选中单元格后，鼠标移至最右单元格的右下角，直至鼠标变成黑实心十字状，摁住左键向下拖动鼠标）至想要到达的行即完成设定（或直接选定C2:Ei<i为想要到达的行>，按Ctrl+d完成填充）。

　　完成后的效果：

 3.4  辅助资料的隐藏

　　如果你不想让输入者看到资料表，可将其深度隐藏（有别于普通隐藏，普通隐藏右击工作表名可隐藏，也可以取消隐藏）：按Alt+F11组合键调出Visual Basic界面，在VBA窗口的资源管理器中选定资料工作表，在属性窗口中将Visible值选为2，就深度隐藏了，右击工作表名也无法取消隐藏。

　　你又要说了，那别人也可以在VBA中改回去（查看），好吧，那你可以给VBA再加个密码：按Alt+F11调出VBA编辑器后点VBA窗口中的工具菜单-VBAproject属性，选保护标签，输入密码并确认，就对这个工作簿的VBA进行加密保护了。

1