- ?
使用EXCEL数据有效性,制作下拉列的几种方法
傅问旋
展开
利用数据有效性可以对指定的区域创建有效性下拉列表,在EXCEL 2013版本之前“数据有效性”称之为,数据验证。功能强大,可以随意创建下拉列表。
方法一、设置数据有效性数据来源,选择单元格,打开“数据有效性”在文本框中输入数据,用英文状态下的的逗号隔开。
方法二、直接引用单元格录入好的数据,选择要放置数据的空白单元格,打开“数据有效性”对话框,选择“序列" 在点击“来源”按钮,选择录入好的数据,确定。
方法三、利用名称定义,引用名称,这个方法可以跨区域,可以引用数据源不在同一行或同一列。
- ?
Excel到底有多厉害?小白才用来做数据管理…
伤逝
展开
Excel就像一把天山寒铁淬炼而成的杀猪刀,本身已经很厉害,但具体有多厉害取决于用它的人。
Excel最牛逼的地方在于它不是小李飞刀也不是轩辕剑——需要练个10年8年才能用,它只是一把菜刀,老百姓可以用来切菜,高手可以用来刮胡子,绝世高手拿着直接从南天门一直砍刀蓬莱东路。
表格是什么?表格就是数据容器,对于非IT人士来说,这辈子可能都不会用数据库,但是!Excel让每个人都可以管理数据库了!其提供的基本功能足以完成大部分数据管理统计工作。
打个比方,同事拿到全国资料开始挨个数每个省有多少个客户数了半个小时,而你只是点了两下鼠标就完成了工作。没错,在同事眼里你就是那个百年难得一见的练武奇才(以后就可以承担更多工作了,可喜可贺)!
要你命2000:数据处理(函数)
别人向你扔屎,你可以躲。客户扔给你屎,你只能接住!比如这种屎:
这种乱七八糟表格是没有任何数据意义的,如果只有三坨,动手处理一下就好了,如果有100坨,怎么办?不要紧张,我们只需要处理一行,其余99行交给excel即可。。。
首先,数据-分列:
然后,直接查找替换,将没用的天字去掉:
最后这个毛比较难以处理,动用函数:先找出具体数字,如果里面有“毛”字,直接将结果乘以0.1,一个规范的表格就诞生了:
(如无特殊需要,最后带汉字的D列可以用E列覆盖掉)
Excel也可以为自己所用,再来举一个例子:
时间管理。与一般时间管理App不同,因为我们可以自己设计研发,做出最适合自己的版本!Go!
首先写两行:
拖动一下右下角的小圆点:
同理增加横轴:
填写数据,编写公式。这样计算当天任务量:
=COUNTA(B2:P2)
这样计算完成量
这样计算完成度
=Q3/Q2
最后完成度那里设置单元格格式-数字-百分比。最后填写数据。最终效果图(点击看大图):
只要三个函数,每天的工作生活一览无遗!
P.s还可以拓展一些功能,比如当天完成度到达xx就有奖励/惩罚之类
总结:掌握了函数,只要是和数据相关的工作,就可以考虑用Excel来处理。
要你命3000:控制一切
著名篮球员赤木刚宪曾经说过,掌握代码就等于掌握了整个Excel,此言非虚。Excel自带编程功能,只有想不到,没有做不到!接下来就用解决吃饭问题做一个简单例子展示一下:
对于有选择困难症的人,让上天来决定吃啥是最好了,我们先填一点数据,如图:
然后选择开发工具-VisualBasic(为了做例子专门下了一个office365,我也是拼了= =),然后什么都不管,直接粘贴代码:
Dim a As Integer '定义公共变量Sub随机Dim x As IntegerDim y As Integera = 0Randomize '初始化reselect:x = Rnd * (3 - 1) + 1 '生成2至7的随机数,代表列数y = Rnd * (4 - 1) + 1 '生成2至6的随机数,代表行数Range("a1:d3").Interior.ColorIndex = xlNone '去掉填充色Cells(x, y).Interior.ColorIndex = 3 '填充为红色a = a + 1If a = 300 Then Exit SubGoTo reselectEnd Sub
然后保存回到excel,选择开发工具-插入-表单-按钮,画一个按钮在excel上,命名为“吃啥好”
在按钮上点右键,指定宏,选择我们刚才做的函数,然后点确定:
Excel制作的半即时战斗模拟(原文地址Excel潜能系列——Excel游戏(2v2战斗~5v5战斗模拟器)【更新V1.5】 Einsphoton_Einsphoton_新浪博客)
超牛的EXCEL版《超级玛丽》总结:掌握了代码,理论上可以用它来做任何小型项目!
用来做动画:
[Excel]Bad Apple!!
推荐书籍:
你早该这么玩Excel(数据管理、工作用)
Excel2013高级VBA编程宝典(装逼、开发用)
另外光有技巧是不够的,表格美化也很重要。(做人也一样,牛逼不够,还得帅。。。)
- ?
保证Excel数据有效性,这几招让你豁然开朗
Kennedy
展开
在我们日常数据输入过程中,数据的多样化,数据的多元化,让数据的输入人员丝毫不得懈怠,比如身份证号的输入,18位数字,怎么在输入过程中确保数据位数正确呢?比如产品只有1级,2级,3级,怎么要求输入的内容只能限定这三类?
EXCEL数据有效性,它会替我们把好这一关。
操作方法:
一、选中我们需要限制的行或者列,或者指定区域单元格
二、单击“数据”下的“数据有效性”
三、选择“允许”下的下拉箭头会出现多个可供的限制内容
四:简单介绍一下每一项的应用
1、整数
2、小数
3、序列
4、日期、时间等
5、文本长度
6、自定义(自定义公式)
五、输入信息提示、出错警告的应用
输入信息:当鼠标单击到此单元格时,会提示此单元格需注意的特殊信息
出错警告:当输入信息错误时,提示出错警告,并停止输入
实战1:对产品种类限制只允许输入A、B、C、D类,可使用上述的“序列”
实战2:对身份证号输入列进行数据有效限定,位数为18位、跟随提醒、错误提示警告。
宝宝,出错喽~~~~(>_<)~~~~
总结:数据有效性,让我们的数据更加真实可靠,提高数据输入的正确率。
- ?
Excel数据有效性使用和设置
Rayna
展开
Excel的数据有效性功能可以防止数据录入错误,节省录入时间。不需要输入,而是使用的时候,可以通过鼠标点击选择数据。那么就简单教大家怎么设置。
比如我们现在要设置供应商为数据有效性。有的供应商名字很长,每次输入就容易错。设置有效性可以防止错误。
供应商数据有效性
list表G列的数据
点击数据里面数据有效性
数据有效性设置
设置I列使用数据有效性的结果
工作注意健康
是不是要运动
- ?
6个经典实例,带你重新认识Excel数据有效性
蓝天
展开
数据有效性(也叫数据验证)是我们使用频率最多的一个功能,比如下拉菜单的制作、限制性输入等,那你真的会用数据有效性吗?本期通过6个经典案例与大家分享一下数据有效性的用法。
1、下拉菜单的制作
下拉菜单是数据有效性最经典的用法,这里与大家分享一下相关的制作方法。
一级下拉菜单的制作:
选择区域,点击数据——数据验证——设置——验证条件——序列,之后选择来源区域即可。
注意:来源若是手动输入,要注意的是文本之间的分隔符是英文状态下的逗号。
二级下拉菜单的制作:
先选中区域,点击公式——定义名称——根据所选内容创建——勾选首行,之后建立一级菜单和二级菜单,做二级菜单时,来源写公式=INDIRECT(单元格地址)。
2、在指定的区域输入数字
选中区域,打开验证对话框,在允许条件中选择整数,之后根据需要输入相应的区间。
3、只允许输入数字
这时选择的条件是自定义,之后输入公式=ISNUMBER(B1)=TRUE。
4、逐行输入内容
一样是选择自定义,在公式中输入=COUNTA(A$1:A13)=ROW(A1)
5、输入唯一值
同理,选择自定义输入公式=COUNTIF(A:A,A1)=1
6、输入当前时间
选中区域,之后按Ctrl+1打开设置单元格格式,然后选择自定义,输入数字格式H:MM:SS,点击确定。
这里需要在其他任意单元格输入公式=NOW()
完成以上操作之后,直接选中区域,打开数据验证,选择序列,来源设置=刚刚输入的公式的单元格。
这几个用法,你会了么?若有什么问题欢迎在下方留言。
- ?
这6种Excel数据验证,你用对了吗?
任老五
展开
数据验证这个功能,2013版之前的叫法是数据有效性。借助数据验证,我们可以让表格的数据信息输入更加的准确、规范和快速,大大提高我们的工作效率,妈妈再也不用担心我被扣钱啦
今天大家分享一下数据验证的6种经典应用。
1、 利用数据验证为单元格的数据输入设置条件限制
在表格内输入数据时,我们可以利用数据验证来规范数据的类型,甚至限制输入数值的大小范围。比如我们输入员工月薪资标准时,可以设置验证条件为“整数”,且这个整数需不小于3500;在设置员工身份证号码时,可以设置验证条件中的“文本长度”等于18。具体操作如下动图1和动图2:
动图1此外,我们还可以利用数据验证设置日期或时间范围,具体操作如下:
2、 利用数据验证制作下拉菜单
在表格内输入数据信息时,我们可以利用数据验证制作下拉菜单,来保证信息输入的准确性和高效性。比如我们在下面表格内输入员工性别时,利用数据验证设置一级下拉菜单。具体操作如下 :
3、 利用数据验证快速输入数据录入的日期和时间
在输入数据信息时,我们还可以结合公式:=NOW()进行数据验证设置 ,直接输入当前日期和时间。具体操作如下:
4、 利用数据验证防止重复录入相同的内容
结合公式=COUNTIF(区域,单元格)=1,数据验证还可以用来防止相同内容的重复输入。具体操作如下:
5、 利用数据验证给设置信息输入时的提醒功能,也可以设置信息输入错误时的错误警告
数据验证功能,不仅能限制、规范数据信息的录入,还可以在录入信息时进行提醒,相当于注释的作用。
当你信息录入错误时,甚至可以设置警告,提醒你重新校正输入。具体操作如下:
6、 利用数据验证制作二级下拉菜单
在前面第2点中我们已经简单讲了利用数据验证制作一级下拉菜单,这里我们再补充一下,如何利用数据验证制作二级下拉菜单。
首先我们要定义名称,选中要设置名称的区域,在【公式】选项卡下点击“根据所选内容创建名称”,勾选“首行”,确定即可。具体操作如下:
下面就是二级菜单具体操作,需要结合返回值的函数——Indirect:
以上就是今天介绍的关于数据验证的一些应用和操作,大家还可以尝试一下三级菜单制作哦,欢迎讨论和分享。
- ?
Excel利用数据验证(数据有效性)规范数据输入
邢灵珊
展开
问题来源
EXCEL输入数据时,经常会输入不规范或者无效的数据,对数据的统计工作带来很大的麻烦。
数据验证能够建立特定的规则,限制单元格可以输入的内容,从而规范数据输入,提高数据统计与分析效率。
数据验证,在EXCEL2010及以前的版本中称为“数据有效性”。
使用示例
一、规范性别输入
利用数据验证输入性别,不仅数据规范,而且快速。
方法如下动图:
特别注意:
1、序列来源中,“男”与“女”两个字之间一定是“英文状态”的逗号,即半角逗号;
2、只要对一个单元格设置了数据验证,鼠标拖动单元格右下角填充柄,进行填充,即可将“数据验证”填充到其他单元格,
二、限定输入内容
当然,输入的内容也可以用第一种输入性别的方法以序列的方式直接写入来源。
三、限定数字范围
1、静态限制输入数值范围:
2、动态限制输入数值范围:
这种动态限定数据的方法,设置了数据验证以后,可以通过修改最小值与最大值单元格的数值,动态调整数据允许输入的范围。
四、限定文本长度
限定文本长度还可以设置通过“文本长度——介于”,来设置文本位数的范围。
五、限制输入重复信息
【数据】——【数据验证】:验证条件选择自定义,输入公式:
“=COUNTIF(H:H,H1)=1”。
公式的含义是:H列中,H1单元格的内容只出现1次。如果H列中H1单元格内容出现次数超过1,则禁止输入。
如动图:
六、限定身份证号码
【数据】——【数据验证】:验证条件选择自定义,输入公式:
“=AND(LEN(H1)=18,COUNTIF(H:H,H1&"*")=1)”。
其中:
LEN(H1)=18,含义是H1单元格内容的长度为18位;
COUNTIF(H:H,H1&"*")=1,含义是在H列中H1单元格内容只出现1次,也就是不能重复出现;
(为什么查找身份证出现次数,还要在查找单元格后面添加“&"*"”,请参考Excel | 身份证号码统计易出现的错误)
AND(LEN(H1)=18,COUNTIF(H:H,H1&"*")=1),含义是同时满足内容长度为18位、不能重复出现两个条件。
七、限制输入空格
【数据】——【数据验证】:验证条件选择自定义,输入公式:
“=ISERR(FIND(" ",ASC(H1)))”。
其中:
ASC(H1),含义是H1单元格的全角空格转换为半角空格;
FIND(" ",ASC(H1)),含义是在H1单元格中查找空格,如果包含空格,则返回空格在H1单元格的位置,即一个数字;如果没有空格,则返回错误值#VALUE;
ISERR(FIND(" ",ASC(H1))),含义是通过ISERR函数,将不包含空格时返回的错误值转换为逻辑值TRUE,表示允许输入;将包含空格时返回的数值转换为逻辑值FALSE,表示禁止输入。
如下动图:
- ?
Excel如何模糊性使用高级数据有效性搜索数据?
鲍尔芙
展开
【Excel如何模糊性使用高级数据有效性搜索数据?】
数据有效性有时候能带给大家便利,有时候能带给大家烦恼,在数据少的时候,数据有效性非常便利;但是数据累计到一定量的时候,可选项就非常多,有时候并不是很方便。
看着别人在做数据的时候利用下拉菜单好像很高级,下面我带大家从不同层次进行学习这个下拉菜单的制作。
一、制作数据有效性方法一:数据→数据有效性→序列,来源,框选需要的区域。
图1
基本的数据有效性设置已经回顾了,有一些朋友会疑问,数据有效性如何能做到模糊性筛选数据,例如使用筛选功能那样,只需要输入一两个关键词,就可以匹配到相应的数据,得出简化的可视数据,带来不一样的视觉感受。现在我帮大家打破这一常规,来带领大家慢慢建立自己的高级数据有效性王国。
大家可以建一个数据源的工作表和过渡搜索数据的工作表,第三个是使用数据有效性的工作表。如图所示,每一个表格的作用会在实际使用中提及。
图2
二、对数据源工作表建立一个名称管理
第一步是对数据源工作表的公司名称建立一个名称进行管理—公司,基本操作步骤:选中所需单元格→公式→根据所选内容创建,名称为首行→确定。
图3
三、建立数组公式
第二步是在过渡工作表中建立一个数组公式,该数组公式作用是根据输入单元格内容进行搜索符合条件的内容。温馨提示:数组公式需要Ctrl + Shift + Enter三键一起输入,被提示循环运用的时候,点确定即可。
该辅助数组公式为:
=INDEX(数据源!A:A,SMALL(IF(ISNUMBER(FIND(CELL("contents"),公司)),ROW(公司),1000),ROW(A1)))&""
图4
第三步是继第二步建立第二个名称—公司名,该名称使用一个公式提取第二步搜索的数据,该公式为:
=OFFSET(提取!$A$2,,,COUNTIF(公司,"*"&CELL("contents")&"*"))
图5
四、建立第二个数据有效性
第四步是建立第二个数据有效性,该数据有效性设置与基本设置一致。设置步骤:数据→数据有效性→序列,公司名(已经在名称管理器下建立名称)。
图6
- ?
Excel基础篇之数据有效性(一级+二级动态)
Lara
展开
点击上边蓝色字体关注我们哦~~
在日常工作中,我们避免不了的需要去收集反馈一些资料,例如意向客户跟进的情况,如果我们只是发了一张表让每个人反馈,那么你收集上来的结果将会是五花八门,给自己后期整理会带来特别大的麻烦,这个时候,如果你会用数据有效性,让他们只能从你给的选项中去选择,那后期我们统计分析结果就会节省很大一部分时间。
下面我们以一个录入人员信息所在省所在市为例子简单的讲解下,大家可以根据自己的实际应用去修改即可
一、仅有一个层级:
步骤:数据→数据有效性→序列→
1、如果所需要填写的较少的话,则我们可以直接把内容填写上去即可,如下图:
此处需注意:两个数值之间用英文状态下的逗号填写即可
2、如果所选择的数值较多,则我们可以把数据源填写到其他的空白处,然后直接选择数据源即可,具体操作如下:
二、有两个层级且相关联(即第二个选项是由第一个决定的)
第一步:创建名称管理器
选中所有的内容→公式→根据所选内容创建→首行(因为此例子省份在首行,二级菜单所有的市是根据省来决定的,所以选择首行,具体可以根据不同情况进行选择)
第二步:创建一级菜单名称
选中所有的省份然后在左上角修改名称即可,此处名字可随意填写,但是一定要记着
第三步:设置一级菜单
数据→数据有效性→序列→=省份(第二步设置的名称)
第四步:设置二级菜单
数据→数据有效性→序列→=INDIRECT($G2)(($G2为二级菜单对应的前一列的单元格)
下边的直接下拉即可
xsjdsjmm
- ?
如何好好利用Excel数据有效性做数据归类?
华飞风
展开
如何好好利用Excel数据有效性做动态数据归类?】
谢谢邀请。我是汤帅,一个PPT自由设计师,承接PPT定制。
利用Excel进行数据登记是众文员需要做的工作,但是若能掌握一部分技巧,数据输入会减轻很多工作量,而且可以减少错误的发生。数据有效性有时候能带来便利,而且其他人填写数据的时候也有参考作用。
数据有效性基础操作是:选中单元格→数据→数据有效性→序列,来源,框选需要的区域。
这时候有些小伙伴会提问,那如果我需要作出动态的结果呢,有时候内容太多,但是能通过一定的条件进行区分,可以降低数据有效性的内容呢?这个如何进行操作?这个难度也不是很大,只是数据维护需要及时。
动态数据有效性操作步骤:
步骤一:对数据进行命名,选中需命名单元格→公式,名称管理器,新建。
步骤二:设置条件单元格的数据有效性,选中单元格→数据→数据有效性→序列,来源,框选需要的区域。
步骤三:利用公式INDIRECT进行编制数据有效性,选中单元格→数据→数据有效性→序列,来源,输入公式。
今日互动:你觉得还有其他与客户沟通的思路吗?P.S:我是汤帅,承接PPT定制,有需要可以留言联系我。P.P.S:觉得回答得还行,就点赞吧!
我是汤帅,知名PPT定制设计师,职场技能优秀回答者。学点新鲜有趣的职场技能,关注我准没错。
excel有效数据
-
1、只需3秒快速实现求和
-
2、如何快速填充序号
-
3、如何自动填充序号(公式法)
-
4、数据条的神奇应用
-
5、多文本快速合并
-
6、查找与替换的不同玩法
-
7、快速定位到指定区域
-
8、数据排序、工资条制作
-
9、快速筛选(模糊、精确筛选)
-
10、快速插入空行
-
11、快速删除空行
-
12.快速跳转到天涯海角
-
13、.同时查看两个Excel文件
-
14、用条件格式扮靓报表
-
15、一键插入Excel图表
-
16、批量处理行高、列宽
-
17、利用拆分功能查看数据
-
18、批量录入相同内容
-
19、工作表快速跳转
-
20、批量录入表格模板(精品课程)
-
21、Excel函数与公式的应用、公式循环引用的查找
-
22、IF函数单条件判断同比增长
-
23、用sum函数 格式相同,连续多表数据汇总
-
24、excel快捷键
-
25、VLOOKUP函数——根据销售员匹配销售额
-
26、统计各部门销售总额
-
27、统计指定条件个数
-
28、怎样输入当前日期和时间、星期数
-
29、销售业绩排名
-
30、Sumproduct函数-万能函数(销售额汇总求和)
-
31、根据销售员,地区,商品名称汇总
-
32、批量替换PPT字体
-
33、给销售额数据批量添加万元单位
-
34、一秒快速核对两列数据
-
35、快速定位到指定单元格或区域
-
36、快速制作双行标题工资条
-
37、给你的表格做个瘦身
-
38、快速打开常用的Excel文件
-
39、快速打开多个Excel文件
-
40、利用创建组—快速隐藏/展开多列数据
-
41、快速制作下拉菜单
-
42、复制粘贴表格,如何保留数据源列宽格式一致?
-
43、两列数据位置互换
-
44、1秒钟扮靓报表——如何实现表格隔行换色
-
45、快速删除重复记录——保留唯一值
-
46、快速向下填充、向右填充,文本或公式
-
47、给Excel文件添加密码
-
48、插入带图片的批注
-
49、输入公式后不计算?
-
50、如何设置单元格缩进
-
51、快速解决Excel表格总显示货币格式
-
52、批量添加万元单位
-
53、你会四舍五入么?
-
54、用RAND函数机选彩票
-
55、冻结首行你会么?
-
56、超链接的高级应用
-
57、IFERROR函数-屏蔽错误值
-
58、批量填充颜色
-
59、录入数据
-
60、快速输入工号
-
61、快速行列转置
-
62、自定义缩放界面
-
63、多个单元格同时输入
-
64、如何计算立方米?
-
65、快速制作双行标题工资条
-
66、输入带方框的√和×
-
67、快速将姓名对齐
-
68、快速输入性别
-
69、按单位职务排序
-
70、自动计算合同到期日期
-
71、计算时间间隔
-
72、日期和时间的拆分
-
73、快速处理不规范的日期格式
-
74、快速填充合并单元格
-
75、效率加倍的快捷键
-
76、快速复制表格和对象
-
77、快速创建工作表副本
-
78、快速复制序列号
-
79、快速显示公式
-
80、多个单元格同时输入
-
81、快速调整显示比例
-
82、快速自动填充
-
83、快速填充(Ctrl+E)
-
84、Ctrl与数字键结合
-
85、快速将多列数据整理为1列
-
86、快速将1列数据拆分为多列
-
87、快速定位公式
-
88、快速录入数据
-
89、快速累计求和
-
90、身份证号码显示为0怎么办?
-
91、快速制作斜线表头
-
92、文本竖向显示
-
93、神奇的监视窗口
-
94、不一样的格式刷
-
95、快速美化图表
-
96、快速生成当前日期
-
97、快速找出循环引用
-
98、快速提取信息
-
99、二维表快速转换为一维表
-
100、快速多表合并