L罗乐 / 数据有效性 / 用数据有效性彻底告别'表格疯'

分享

   

用数据有效性彻底告别'表格疯'

2017-08-29  L罗乐

来来来,科普一下搜狗新词【表格疯】。


从上学到工作,我们常常都得填表格,对填表这件事大家早已驾轻就熟~


然而!总是有大神不按常理出牌,在填表的时候脑回路不知歪到了哪里!



只想说,填个表戏都能这么多,在下佩服!!







如何不让您的表格被“张红梅”S给整疯了?

答:数据有效性。

为你的单元格(填报区域)设置数据有效性,下拉选择:

再也不用害怕那些奇葩的脑洞了~


表姐说

在企业里,每个部门(编表的人)下发的表单,都是为了收集数据,并最终进行统计分析的。

那么就不希望,填报后的表单,数据参差不齐、千差万别。

除了写上两三页的填表说明以外,最好的办法是,在单元格中,设置【数据有效性】!!!控制填报人员的填写规范。

数据有效性的设置,有2种方法

①固定的数据选项设置

数据验证--设置--选择序列--在来源那里,直接手动输入选项


动态的数据选项设置

a.用引用数据源的方式

b.用名称管理器设置数据源

c.用indirect的方式引用


   法①    固定的数据选项设置   

适用于:

在选项不多的情况下使用

或者是,下拉设置的选项参数,无需经常更改和变动的情况

如:设置性别,学历等


在数据验证(数据有效性)下

【设置】--【验证条件】下的允许值为【序列】--在来源处

直接输入文本文字

注意,各选项之间用英文状态下的,逗号隔开。


    法②    动态的数据选项设置  

适用于:

在下拉选项变化情况较多,且需要交互式维护的情况

如:员工姓名、产品名称等


a.用引用数据源的方式

在数据验证(office2016版本,office2010及以下版本,叫做:数据有效性)中

设置,【允许】为【序列】

在【来源】中,直接点击excel表格中的单元格区域,如:

=$A$2:$A$22

【确定】,效果如下:


b.用名称管理器设置数据源

选中数据源以后,在【名称框】中,输入一个名称

如:为以下城市区域,设置名称为“湖南省的城市”

也就是说,我们给A2:A15单元格,起了一个名字为“湖南省的城市”

下一步就是把这个名称,赋值到单元格的有效性当中:


c.用indirect的方式引用

上述的方法,对于数据源来说,都是固定大小的区域

只能在这些区域内,进行数据的修订

但不能够实现,数据源区域自动增减的功能

用【indirect】的方式,除了可以修订已有的数据信息外

还可以自定义的增减数据选项


①建立一张超级表,即给表格套用【表格格式】

将该表的名称改为,浙江


②设置数据有效性中序列的来源=INDIRECT('浙江[city]')


其中:

浙江,为该表格的表名

city,为改下拉选项所在的字段名称

字段名需要用英文状态下的[]大括号括起来

indirect函数内的参数,用英文 状态下的''双引号括起来

语法如下:

=INDIRECT('表名[字段名]')

设置以后,可以在数据源city字段下,增加新的内容,下拉选项,随之变动:



    【高级】设置双重数据有效性   

数据有效性

在很多表格的填报中,希望点选了一级选项后,二级的下拉选择是有筛选性的与之变动,如:

选择了北京市,对应的二级下拉只有:朝阳区、通州区等

选择了水果,对应的二级下拉只有:樱桃、火龙果、猕猴桃等


设置的步骤共三步:

双重数据有效性

1.设置省份的数据有效性

选中I2:I23单元格,设置其数据有效性为:

序列,来源=$A$1:$G$1

设置后效果如下


2.给省份下的城市,定义名称

选中A1:G22单元格区域

点击【公式】选项卡下的【根据所选内容创建】

勾选【首行】

点击【确定】



此时,点击名称框可见:


3.用indirect函数,将城市关联到二级有效性当中

选中J2:J23单元格区域,设置其数据有效性为:

序列,来源=INDIRECT($I2)

注意:引用时,只锁定列号,不锁定行号。

效果如下:


    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多