|
工作中我们会遇到这样的情况：综合部门的小伙伴会发放表格让其他部门填写，有的时候，交上来的表格会非常混乱，这个时候，如果对单元格进行一些特别的设置，就会在一定程度上避免这些问题。 今天我们就围绕这个话题，讲讲Excel中【数据验证】方面的问题（较早版本又名：数据有效性），分享一些办公“老鸟”的经验，相信你会有所收获。 下面，我们来详细讲解6种实用方法： 1、限制单元格输入重复值步骤：选中需要进行设置的单元格（本案例 B5：B6），数据选项卡，数据验证，自定义，输入公式=countif(B:B,B5)=1，确定。 关键步骤截图： 这里先说一下countif函数，这个函数的作用是：用于统计满足某个条件的单元格的数量。 语法可以理解为=COUNTIF(要检查哪些区域? 要查找哪些内容?)，具体到本案例就是countif（统计B列中，B5的值），翻译一下就是统计B5单元格在B列中出现的次数，所以countif(B:B,B5)=1就是限定B5单元格的值只能在B列出现1次，即唯一性，如果不满足唯一性就提示错误。 2、限制单元格输入非法值（黑名单） 上图演示的是，进入输入黑名单中的姓名：小张。（如果需要限定多个名字，在J列按顺序向下写） 操作方法同第一步，公式为=countif(J:J,B5)=0，注意J列是黑名单所在的列。 关键步骤截图如下： 按照前面所讲的内容，countif(J:J,B5)=0就是限定B5单元格的值只能在J列（黑名单）出现0次，即不能出现，也就是说禁止输入，如果不满足就提示错误。 限制黑名单输入的实际应用案例参考： 1分钟，从1万个人员中删除“黑名单”，这个技能只有0.01%的人会 重要概念提示： 细心的小伙伴们可能会有个疑问：为什么我们选中了2个（或多个）单元格，为啥只在公式中写入了B5，B6去哪里了？ 写入的B5是我们选中区域的活动单元格，请注意我们选择区域的时候是从B5开始框选到B6的，默认情况下B5就是活动单元格，如果先选择的B6，B6就是活动单元格，公式中写入B6。活动单元格的概念在条件格式中也会经常遇到，一定要记住哦。 为了帮助大家更好地理解这一概念，我们做个活动单元格的演示： 上图中，我们用鼠标框选了B5：B15区域，注意看图中左上角鼠标指示的名称框的位置，这里显示的就是活动单元格的位置。随着我们一次一次按Tab键（键盘左侧），活动单元格也发生了变化。默认情况下，单个连续区域中，先选择的就是活动单元格。对于多个不连续区域的默认单元格，自己尝试一下吧。 3、限制文本长度的输入上图演示的是，将学生的学籍号限定在4位或5位数，如果输入的位数不是限定范围，则提示错误。 步骤：选中需要进行设置的单元格（本案例 C5：C6），数据选项卡，数据验证，文本长度，介于，最小值输入4，最大值输入5，确定。如果不是4位或5位的文本长度就会提示错误，见上图演示。 关键步骤截图： 4、限制单元格输入不合规定的成绩上图演示的是，我们将成绩限定在0-100范围内，允许输入小数和整数。 步骤：选中需要进行设置的单元格（本案例 D5：D6），数据选项卡，数据验证，小数，介于，最小值输入0，最大值输入100，确定。 关键步骤截图： 注意：如果成绩只允许输入整数，要将小数改为整数。最小值和最大值根据实际需要进行调整，比如0至150. 5、限制日期段的输入上图演示的是，只能在【录入日期】中，输入今天及之前的日期。上图中输入了2019-5-28，超出了当前日期（2019-5-19），就会提示错误。 步骤：选中需要进行设置的单元格（本案例 F5：F6），数据选项卡，数据验证，日期，小于或等于，结束日期中输入=today()，确定。 关键步骤截图：
如果想限定2019-1-1至当天的日期，操作参考下图（today函数表示引用当前日期）。如果要限定2018年1月1日至2019年12月1日，则应在开始日期中输入2019-1-1，在结束日期中输入2019-12-1，以此类推。
6、下拉菜单制作下拉菜单的制作是我们经常遇到的问题，应用范围极广，还可以配合函数公式、图表进行操作，达到动态图表演示的效果。
上图演示的是，在姓名列，只允许输入张森、黎明、王五这三个名字了。实际工作中根据需要，设定需要经常输入的信息。 步骤：选中需要进行设置的单元格（本案例 B5：B6），数据选项卡，数据验证，小数序列，在来源中输入张森,黎明,王五，确定。 关键步骤截图：
其他制作下拉菜单的方法和实际应用案例： 只需输入姓名就能调用员工所有信息——这Excel技能越早知道越好 利用下拉菜单制作的动态演示效果示例：
特别需要强调的是：1、在Excel中，我们输入的标点符号全部应在英文半角状态下输入，否则无法达到我们演示的效果。 2、尽管数据验证的方法很强大，它也有自身的不足，如：从其他表格或区域中复制粘贴进来的数据，可破除数据验证的效果。（请自行验证，这里不再演示。） 今天你学会了吗？希望你能举一反三、灵活掌握。 你还有其他的方法吗？欢迎在留言区和我们一起讨论交流。 也欢迎写下你的疑问，我们再做进一步解答。 Excel办公精英，希望你每天都有收获。 |
|
|
