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进行加密保护了。
联系客服