Excel是许多职场人士常用的烦恼之源,学习相关技巧需耗费大量时间。简道云作为一款办公神器,能很好地替代Excel。它是一个在线表单和数据管理工具,支持PC端和手机微信浏览器操作。除此之外,简道云还能辅助企业进行流程审批、财务报销、人事管理等业务管理,满足不同需求。
excel 如何利用公式设置数据验证条件
Excel程序内置的验证条件可以解决一些常见的数据输入限制问题,但若想更加灵活地控制数据输入,则需要用公式设置验证条件。只要你对函数有足够的了解,则可以设计出非常灵活的验证条件,如“限制输入空格”“限制输入重复值”等。本文将用以下示例说明如何应用公式设置数据验证条件。
一、避免输入重复值
用户在进行数据录入时,由于录入数据量较大,可能会发生重复录入的情况。比如在录入学生姓名制作成绩报表时,为了防止数据的录入重复,可以设置当数据录入重复时给出提示或者禁止录入。
具体步骤为:选中A2:A9单元格区域,在“数据”选项卡的“数据工具”组中单击“数据验证”按钮,打开“数据验证”对话框 - 选择“设置”标签,在“验证条件”栏下设置“允许”条件为“自定义”,在“公式”栏下的文本框内输入公式“=COUNTIF(A:A,A2)<2” - 单击“确定”按钮完成设置。当输入的姓名重复时就会自动弹出提示框,重新输入不重复的姓名即可。
注意:公式“=COUNTIF(A:A,A2)<2”用来统计A列中A2单元格中数据的个数,如果小于2则允许输入,否则不允许输入。因为当其个数大于1就表示出现了重复值了。
二、限制单元格输入的产品价格必需包含两位小数
本例记录了蔬菜的单价信息,为了规范单价的录入(需要保留两位小数),可以使用自定义公式,当录入不是两位小数的数据时自动弹出提示框。
具体步骤为:选中B2:B20单元格区域,在“数据”选项卡的“数据工具”组中单击“数据验证”按钮,打开“数据验证”对话框 - 选择“设置”标签,在“验证条件”栏下设置“允许”条件为“自定义”,在“公式”栏下的文本框内输入公式“=LEFT(RIGHT(B2,3),1)="."” - 单击“确定”按钮完成设置。当输入的小数位数不是两位数时就会自动弹出提示框,重新输入正确的小数位数(两位小数)即可。
注意:公式“=LEFT(RIGHT(B2,3),1)="."”,首先使用RIGHT函数从B2单元格中数据的右侧提取3个字符,接着使用LEFT函数从上步结果的左侧提取1个字符,判断其是否是小数点“.”,如果是则满足条件,不是则不满足条件。
三、限制输入空格
手工输入数据时,经常会有意或无意地输入一些空格,这些数据如果只是用于查看,有无空格并无大碍,但如果要用于统计查找,如“北京”和“北 京”,则会被当作两个完全不同的对象,这时的空格则为数据分析带来困扰。为了规范数据的录入,则可以使用数据验证限制空格的录入,一旦有空格录入就会弹出提示框。
具体步骤为:选中B3:B13单元格区域,在“数据”选项卡的“数据工具”组中单击“数据验证”按钮,打开“数据验证”对话框 - 选择“设置”标签,在“验证条件”栏下设置“允许”条件为“自定义”,在“公式”栏下的文本框内输入公式“=ISERROR(FIND(" ",B3))” - 单击“确定”按钮完成设置。当输入的文本中间有空格时就会自动弹出提示框,重新输入正确的文本(不包含空格)即可。
注意:公式“=ISERROR(FIND(" ",B3))”,首先用FIND函数在B3单元格中查找空格的位置,如果找到返回位置值,如果未找到则返回的是一个错误值,然后用ISERROR函数判断值是否为错误值,如果是则返回TRUE,不是则返回FALSE。本例中当结果为“TRUE”时则说明未找到空格,此时允许输入,否则则不允许输入。
四、限定单元格内必需包括指定内容
例如某产品规格都是以“OMD”开头的,要求在输入产品规格时,只要不是以“OMD”开头的就自动弹出错误提示框,并提示如何才能正确输入数据。
具体步骤为:选中C3:C13单元格区域,在“数据”选项卡的“数据工具”组中单击“数据验证”按钮,打开“数据验证”对话框 - 选择“设置”标签,在“验证条件”栏下设置“允许”条件为“自定义”,在“公式”栏下的文本框内输入公式“=ISNUMBER(SEARCH("OMD?",C3))” - 切换至“出错警告”标签,在“输入无效数据时显示下列出错警告”栏下的“样式”下拉列表中选择“警告”,再分别设置“标题”和“错误信息”内容即可 - 单击“确定”按钮完成设置。当输入错误的产品规格时,会弹出错误警告提示框。
注意:ISNUMBER函数用于判断引用的参数或指定单元格中的值是否为数字。SEARCH函数用来返回指定的字符串在原始字符串中首次出现的位置。公式“=ISNUMBER(SEARCH("OMD?",C3))”用于在C3单元格中查找“OMD”,找到后返回其位置,位置值是数字,所以外层的ISNUMBER函数的判断结果即为真(允许输入);如果找不到,SEARCH函数返回错误值,外层的ISNUMBER函数的判断结果即为假(不允许输入)。
五、禁止出库数量大于库存数
月末要编辑产品库存表,其中已记录了上月的结余量和本月的入库量。当产品要出库时,显然出库量应该小于库存量。为了保证可以及时发现错误,需要设置数据验证,禁止输入的出库量大于库存量。
具体步骤为:选中 E2:E12单元格区域,在“数据”选项卡的“数据工具”组中单击“数据验证”按钮,打开“数据验证”对话框 - 选择“设置”标签,在“验证条件”栏下设置“允许”条件为“自定义”,在“公式”栏下的文本框内输入公式“=C2+D2>E2” - 切换至“出错警告”标签,在“输入无效数据时显示下列出错警告”栏下的“样式”下拉列表中选择“停止”,再设置“错误信息”内容 - 单击“确定”按钮完成设置。当在E2:E12中输入的出库数量小于库存数时允许输入,当在E2:E12中输入的出库数量大于库存数时(上月结余与本月入库之和),系统则会弹出错误警告提示框。
六、用有效性设置保护公式
我们知道通过设置工作表保护的操作可以实现对表格中公式的保护,同样,为了保护公式结构不被修改,也可以使用数据验证功能巧妙实现。
具体步骤为:打开包含公式的表格后,在“开始”选项卡的“编辑”组中单击“查找和选择”下拉按钮,在打开的下拉列表中选择“公式”,即可选中表格中所有包含公式的单元格 - 保持单元格的选中状态,在“数据”选项卡的“数据工具”组中单击“数据验证”按钮,打开数据验证对话框 - 选择“设置”标签,在“验证条件”栏下设置“允许”条件为“自定义”,在“公式”栏下的文本框内输入0 - 切换至“出错警告”标签,在“输入无效数据时显示下列出错警告”栏下的“样式”下拉列表中选择“警告”,再分别设置“标题”和“错误信息”内容即可 - 单击“确定”按钮完成设置。当对含有公式的单元格进行编辑修改时,就会弹出警告提示框。
excel 如何利用公式设置数据验证条件的***:
***1