由Skill成长学院原创出品
作者:解题宝宝
编辑:乌妹妹
数据 · Excel基础·细节控 ·函数
⏱4mins 📝有作业
你试过录入大量数据没有?
工作量大,纠正起来还麻烦。
当数据重复,计算会报错,而查重工作繁琐又麻烦。
文本或数字填错行列,肉眼难以发现,常常到了用公式时发现。
尤其面对这种,密密麻麻的数字,录入时候更要极度细心,一旦错了直接影响决策判断。
今天,解题宝宝帮财务姐姐处理了一个仓库清单,
动用了三个公式,
使表格根据我设定的规则,
自动发现有误数据,然后报错。
由于内部文件不能公开,我用微软自带的一张表格,给大家演示怎么做哒。
这张表格设计超好看哒,也分享给你,下载方式将在文末附上,你先耐心学完教程先喔。
防止数据重复录入
这个表里,在库数量的数字是一模一样的,很明显是输错对照量了。
为了避免发生这种惨剧,就要让系统监视我的录入动作。
当它发现我输入了一个重复数据,就马上出现错误警告,别让我继续错下去。
◎ 演示效果
怎么做哒?
这里我用到 Countif 函数,它的语法规则是:
=COUNTIF(range,criteria)
其中的意思,分别如下:
range -指定区域
criteria -以数字、表达式或文本形式表示你所定义的条件
那么,它的作用是对指定区域中「符合指定条件」的单元格计数。
也就是说,你设定一个条件,
通过 Countif 函数,能算出来有多少个单元格符合这个条件。
然后,我们要用另一个手段——
把所有符合这条件的单元格,控制为只能留存一个,这就实现了「防止数据重复录入」功能。
而这个手段,就是数据验证 。
Step 1
框选你要操作的所有行,点击 数据 - 数据验证 ,允许 自定义 。
Step 2
输入公式 =COUNTIF($G:$G,G1)=1
($G:$G 代表你选取了G列作为计算范围,加了 $ 是表示绝对引用。
G1 代表用G1这个单元格,作为你设立的特定条件。
=1 代表你的特定条件,只能=1,即G1里面的值(25)只能存在1个。
公式输入完毕后,点击确认,规则就生成啦。
这时候,你试试在第二个单元格里敲25?系统马上报错啦!
◎ 效果演示
这条公式一旦录入,是对你选取的所有行生效。
它能继续拾取「你输入的其他数额」作为唯一值来参照,然后帮你识别出重复值。
当你A2输入的是30,假如你想在A3也输入30,系统一样会报错。
◎ 效果演示
格式特定
录入时如果弄错行列,把名字那一行一小心全部填成了数字,这个错误就犯得有点低级呐。
那么,我们可以用 IS 类判断函数,把值的格式固定住。
让这行只能填文本,那行只能填数字。
IS 类判断函数通常用于,可以检验数值的类型,并根据参数取值,返回 TRUE 或 FALSE。
在这里,我想要让C列只能填数字,D列只能填文字。
如果它们不这样做,IS 要给我报错,返回FALSE。
Step 1
跟刚刚步骤一样,打开数据验证 。
框选你要操作的所有行,点击 数据 - 数据验证 ,允许 自定义 。
Step 2
C列要填的是库存ID,所以我要填的就是数字,英文是NUMBER。
那么,输入公式 =ISNUMBER(C4)=TRUE
意思是,如果C4这个单元格的值是数字,那就是对的,否则就要报错。
虽然刚刚,只是设定了C4为判断范围,它只能填数字;
但同样,这条公式一旦录入,是对你选取的所有行生效。
如果给C5、C6、C7……输入文字,系统同样会报错,它们也只能填数字。
◎ 效果演示
Step 3
D列要填的是名称,那么我要填的是文字,英文是TEXT。
那么,这次我输入公式 =ISTEXT(D4)=TRUE
这时,如果我填了数字,系统就会报错。
◎ 效果演示
内容特定
识别错别字大法来了!
有一个方法,能让我对单元格,设定一系列特定文本。
这样,我只能输入一模一样的内容,才会被显示出来。
否则,要是我输入了别的东西,或者有错别字,系统就会报错!
在这里,我设定D列只能填「苹果」或「华为」。
要是我填的内容,不是这两个其中一个,系统就会报错!
◎ 效果演示
这里用的是OR 函数,它是一个逻辑函数。
它的语法是:=OR(Logical1,logical2……)
Logical - 你要检验的逻辑值或条件,最多可填30个。
在这里,我要用 OR 函数帮我检验——特定区域内,是否包含我设定的逻辑值。
如果不包含,就给我报错。
Step 1
框选你要操作的所有行,点击 数据 - 数据验证 ,允许 自定义 。
Step 2
现在,我要把「苹果」与「华为」变成逻辑值,
它的公式写法很简单,以“”开头,对一个单元格,生成你想要的文本生成为逻辑值。
输入公式 =OR(D4="苹果", D4="华为")
这样,单元格D4,就只能填「苹果」或「华为」,否则就会报错。
同样,这条公式一旦录入,是对你选取的所有行生效。
只要没有被填进去「苹果」或「华为」,系统就会报错。
◎ 效果演示
今天学了三个公式,听起来有点繁琐?
但其实,你只要操作一张表,就能一次性把它们全弄懂。
所以,这揭示了我们平常学习的一个规律:
与其逐个逐个看语法解释,还不如全部实践一遍。
记得多还不如用得上,毕竟实践出真知。
最后,
今天的作业,是一个Excel文件,含两张工作表,一份作业,一份答案。
做完自行对答案喔。
宝宝们加油,有不懂的本文章下方留言问呐。
后台敲「516」领取本期作业。
联系客服