我们在工作中有时会接到设计制作某些功能的表，在表格的使用过程中往往需要别人来录入数据，而他人的不小心或误操作有可能导致整个表格的功能崩溃。为了最大限度的确保我们的表格能正常工作，除了我们在设计表格功能的时候尽量的做到最大限度的考虑的数据的兼容性，还需在数据规范录入和表格的结构保护上下功夫。哪我们就先从数据录入上着手，怎么样让别人乖乖的按照你的要求输入数据？

规范输入

什么？只要和你的同事搞好关系并告诉他们如何输入不就行了，还用这么费劲啊！十来年的工作经验告诉我们，这并没有什么乱用，因为各种原因他们会忘，有些操作还是误操作，你咋整，总不能天天盯着他们录入吧！如果你做的表格能教他如何输入是不是问题就解决了呢？聪明的你是不是已经想到了【数据验证】？没错，我开始也是这么解决的但效果却不是很理想，想象和现实总是有一定差距，其实在录入的过程中，需要规范输入的大部分都是数值，因为它是各个公式运算的核心，哪我们设计需求就很明确了：

必须输入数值：解决方案【数据验证】（技巧等级：初级）

看好需求是数值并非数字，因为数字有可能是文本格式的数字；具体的操作步骤：【数据】->【数据验证】按钮，这里需要注意的是，【数据验证】按钮是分上下两部分的，上部分点击直接弹出【数据验证对话框】，下半部分点击会弹出数据验证的下拉菜单，选择数据验证命令也会弹出数据验证对话框，在【设置】选项卡中为我们提供了涉及数值的所有情况，比如整数，小数，日期，时间等；

数据验证的允许选项

看到这四个选项，哪么问题来了，我们知道日期和时间本身就是整数和小数的数值，为什么还要增加的日期和时间，是不是可以推测出验证选项不光区分数据，还区分单元格的格式呢？我们会在之后的操作中验证这个问题！

回看需求，必须为数值就只有整数和小数两个选项了，整数通常用于物品的数量，以个，匹，科，棵，头，只等为单位你就选”整数”，而小数的应用就广泛多了：比如以重量，金额，%，功率，温度，长度等单位类型的物品或属性上，一般就要选”小数”了，这里要说明2个问题，

选择”整数”我们照样可以输入1.00这样数值，这就说明验证的检测的单元格最后计算的结果；

选择任意一项，在【数据(D)】选择任意逻辑关系后，且”小数”的范围值可为整数；

整数选项对应的逻辑关系有哪些

哪如果都选”小数”会有什么不好影响吗？

在日常的使用中，我们不难发现，Excel在数据类型上采用了弱类型的设计，也就是在Excel的世界里，根本就没有整数这个理念，所有的数值都是采用了浮点型(始终带有小数部分)存储，为了看起来整数，是经过了取整操作，而如果验证方式选”小数”后，就有可能会忽略部分的取整操作，会导致运算中有误差，虽然很小很小，但一旦带入多层级运算，就会导致类似天气预报中下雨和晴天这样两种完全不同的结果并且这个结果并可以预测。（详解Excel基础知识-微软都无法修复MOD中的BUG，我们该怎么做）。

自带的选项确实能帮我们省去设计的时间，单它的功能也相对单一并不能满足我们日常的需求，接下来就需要我们自己设计验证规则了，【允许(A)】选【自定义】选项，接下来我们就来盘盘它。

需求：支持输入为数值，不管是不是数值格式（技巧等级：中级）

当我们选择【自定义】介于选项为灰色，输入项变为【公式】，在录入框支持常量和公式的录入，而验证判断的标准是你录入内容最终返回结果是真还是假，结果为真的常量有数值≠0，逻辑值的TURE,其它的的文本，0，false结果都为假;这个知识点会在之后用到，公式的录入知识有两点：

公式通用引导符号“＝”函数附加的引导符号“@”；
公式也分常量，函数，表达式，当前的需求需要用到表达式作为函数参数使用，使用数学运算符负号（-）、加减0和乘除1来讲文本数值转化成数值，再用数值判断函数来判断；

具体操作如下：

选择要使用验证单元格的范围或整列，点击【数据】选项卡下的数据验证按钮，弹出的【数据验证】弹窗，【允许(A)】选择“自定义”选项，然后在“公式(F)”输入=isnumber(--c1)、=isnumber(c1*1)、=isnumber(c1/1)、=isnumber(c1 0)或@isnumber(c1-0)；

说明：至于在输入框中的表达式或函数输入哪个单元格需要看选择范围后，当前激活的单元格为哪个（白色单元格）！

配置数据验证公式的步骤

如果你的录入的单元格设置了这样的验证，必须在编写函数运算公式的时候引用这些单元格计算的时候，必须先做文本数值转数值的操作，否则会导致运算的最后的结果不正确，因为函数求和或其他运算时，文本数值会被看做0 带入计算的！

需求：最大限度的保证手机号的正确性（技巧等级：高级）

要做好这个功能，必须先思考一个问题，什么样的数字才能称为手机号呢？也可以说手机号有什么独有的特征呢？你总结的特征越多，这个功能完成就越高，然后将特征转为表达式就是验证输入的内容啦。

及格特征：

1.长度为11，2.第1个数字为1，3.第2位数不可能为0，1，2；（注：三个特征来自手机号段信息汇总）

如果用数据验证实现这三个方面的限制，在手机号码的验证上就算合格了；三个特征转为公式为：特征1：len(d1)=11,特征2：left(d1,1)*1=1; 特征3；mid(d1,2,1)*1 > 2，三者的逻辑是且关系，也就是三个特征都必须满足的情况才算是手机号；要实现3条件的且的逻辑，我们通常会想到用if函数来完成，公式=if(len(d1)=11,if(len(d1)*1=1,if(mid(d1,2,1)*1>2,ture,false),false),false),你也可以通过数学的方式来，比较表达式返回TURE和FALSE,再数学表达式中转为1和0，而数值0可以转化为逻辑值FALSE,非零的数值则为TURE;用数学表达式编写公式为：=(len(d1)=11)*( left(d1,1)*1=1)*( mid(d1,2,1)*1 > 2)；这两种的方式你更喜欢哪种呢？

具体操作步骤：d列为手机号输入列，选择整列，然后调出【数据验证】窗口，选【自定义】并在公式录入框输入上面编写的If或数学表达式公式中的任意一个，点确定。

为什么这样做才只能算及格呢？

上述的方法从对应的位数上是否出现的数字作为检测标椎，确实能达到过滤部分的输错的可能，但相当一部分的数据通过这种方法是无法避免输错的，而实际的可以确认的号段也就40多个，而且上述方法组合70种组合，每一种会增加上亿个错误可能性，而且一旦号段有所增加，需要重新汇总数据规律，重理逻辑，再编写公式，相当繁琐；接下来我们就一起玩一下满分的做法！（设计思想：重配置，轻逻辑，易维护，可扩展性高）

满分特征：1.长度为11，2.前三位号码段，3.4-7位为地区编号；

实现步骤：新建配置工作表，名称配置信息；然后在网上找到号段信息，填入到配置信息表第一行表格中，通过统计截取录入手机号的前三位出现的次数，为0则输入错误，为1则通过特征2的判断，公式为=COUNTIF(配置信息!$1:$1,LEFT(Sheet1!D1,3))再乘上(len(d1)=11) *(isnumber(d1*1))就是填入【数据验证】窗口的完整公式了，一旦有新增号段，只需填入新的号码段就行，再用同样的方法增加地区编码验证就能满分了！虽然这样做能满分，但它的实际体验并不好，于是我就做了改良，将部分的验证公式改用条件格式来做，同事反馈体验良好。

操作步骤：选中d列，点击【开始】菜单中的【条件格式】按钮，在弹出的下拉菜单中，选【新建规则】，在【新建规则窗口】中选择【使用公式确定要设置的单元格】，输入=COUNTIF(配置信息!$1:$1,LEFT(Sheet1!D1,3))=0，点击格式按钮，设置红色字体并加粗，点确定；

条件格式实现操作步骤

然后将数据验证修改的公式修改成=(len(d1)=11)*(isnumber(d1*1)),手机为11位数字这个必须的，至于号段或手机号录入错触发的概率太高了，这样只需标注出来，提到提醒的作用就可以了！

注意：【数据验证】当公式返回结果为false的时候弹出错误提示，而【条件格式】则是公式的结果为TRUE时使用自定义的格式，所以两者的公式不能通用，是不是眼尖你的早就发现！

接着一起盘一下【数据验证】弹窗的其它三个选项卡：

【输入信息】作用：提示说明，当鼠标单击有数据验证的单元格，会弹出提示信息；实用性：确实在输入有一定的提示作用而大量的同事抱怨在录入时，非常碍眼，后去掉，补充做法：表格右侧空白地方增加提示信息区域或直接将输入录入说明制作成单独文件下发给同事；

输入信息的操作界面以及对应的显示位置

【出错警告】作用：当录入者输入错误时，会弹出提示错误，实用性：由于输入错误不一定是人为，误操作导致的，不论怎么输入一直提示错误，后来让我过去看看，原来是设置了文本格式，这种方法也并是太好用！

出错警告操作界面以及显示位置

【输入法模式】作用：让输入法自动切换为英文输入或中文输入，我的环境：windows8.1系统，office 2016,只有QQ输入法，设置完成后，不能起到任何作用！

输入法模式的操作界面

解决方法：需要在控制面板-语言-添加语言-英语，因为只有一个输入法，是不能满足【输入法模式】开启的条件的！，还需要修改被QQ输入法中英文切换占用的【ctrl 空格】快捷键为其它，经过这些操作之后才能开启功能！

输入法模式无效的解决方法示意图

实用性：如果一个功能依赖的条件太多，肯定不发普及和推广，因为大部分对系统配置操作不是太了解，虽然它的功能上有益录入中英文的数据效率，不过我觉的还是用输入法自带shift切换来的更直接有效！你觉得呢？

在文章的最后需要说明一点：任何的强大的功能都有缺点，数据验证的缺点就是它会阻断录入过程，但有些阻断是必要的，有些阻断了就会给录入造成很大麻烦，所以我们在使用【数据验证】的时候，需要注意有些对于数据准确性要求不是很严格的数据，使用【条件格式】这种有识别的格式就好，不是所有的场景都适合使用，比如数据验证并不能作用于设置之前已有的数据；文末彩蛋：我们可以用来限制他人修改已有的数据，只需在自定义的公式中输入0或FALSE，不论别人输入什么都会弹出错误提示！通过这个例子想告诉你，只要我们平时多思考，多用发现的眼光观察，缺点也是有它的用武之地的，好了今天就先写到这了，如果对你有所帮助记得关注我啊，我是爱极客的小胖子！

怎么设置输入内容自动添加表格（怎么设置表格输入的指定内容）

规范输入

相关推荐