(第8讲51)Excel数据有效性.docVIP

Excel -- 数据有效性 程香宙 什么是数据有效性? 数据有效性一个包含帮助你在工作表中输入资料提示信息的工具。 它有如下功能: --给用户提供一个选择列表 --限定输入内容的类型或大小 --自定义设置 注意: 数据有效性并非十分安全。 它可以通过粘贴在单元格输入其它数据。 并且可以通过编辑|清除|清除所有取消它。 创建下拉列表选项 使用数据有效性可以为一个单元格创建一个选择输入内容的下拉列表。 列表数据项可以在工作表的行或列中输入。 也可以直接在数据有效性对话框中输入。 1。 创建列表数据项 a。 在一个半单行或单列中输入你想在下拉列表中看到的条目。 2。命名列表范围 如果你在一个工作表中输入了一个有效性列表条目，并且给它定义了名称，你就可以在同一工作簿的其它工作表的数据有效性对话框中引用这个名称。 1。 选择列表单元格范围。 2。 点击公式编辑栏左边的名称框(Name Box) 3。 定义一个名称。如： FruitList。 4。 按回车键。 注意: 要使定义的名称自动扩展包含新增加的条目，请使用 动态范围。 3。 应用数据有效性 a。 选择你想应用数据有效性的单元格 b。 “数据”→“有效性”。 c。 点击“允许”框右侧的下拉箭头，在列表中选择“序列” d。 在来源对话框中输入一个等号和列表名称。 如: =FruitList e。 点击确定。 4。 使用一个限制列表 你可以直接在来源框中输入用逗号隔开的条目替代列表。 例如: Yes,No,Maybe 注意：这个逗号是半角状态的，全角状态时输入的逗号无效。 Excel -- 数据有效性 – 有效性条件示例 程香宙 整数 设置或排除一定范围内的数值。也可以自定义最小值或最大值。 1。 在数据有效性对话框中输入值。 或者 2。 引用到工作表中的单元格。 或者 3。 使用公式设置值 小数 设置或排除一定范围内的数值。也可以自定义最小值或最大值。 1。 1。 在数据有效性对话框中输入值。 或者 2。 2。 引用到工作表中的单元格。 或者 3。 3。 使用公式设置值 序列 请参阅: 数据有效性 日期 设置或排除一定范围内的日期。也可以自定义最小值或最大值。 1。 在数据有效性对话框中输入日期。 或者 2。 引用到工作表中的单元格。 或者 3。 使用公式设置日期本例中。 当前日期为起始日期。当前日期加7天为结束日期。 时间 设置或排除一定范围内的时间。也可以自定义最小值或最大值。 1。 1。 在数据有效性对话框中输入时间。 或者 2。 2。 引用到工作表中的单元格。 或者 3。 3。 使用公式设置时间 本例公式中使用当前时间作为终止时间: =TIME(HOUR(NOW())。MINUTE(NOW())。SECOND(NOW())) Excel – 数据有效性 – 自定义条件示例 程香宙 防止输入重复值 防止在工作表一定范围输入重复值。 本例中。 在单元格 B3:B10中输入的是员工编号。 1。 选择单元格B3:B10 2。 选择数据|有效性 3。 在“允许”下拉框中选择“自定义” 4。 在“公式”框中，使用 COUNTIF函数统计B3出现次数。 在$B$3:$B$10范围内。 结果必须是1或 0: =COUNTIF($B$3:$B$10,B3)=1 限定总数 防止一个范围数据总数超过指定值。本例中。 预算不能超过$3500。预算总额统计的单元格在C3:C7范围内 1。 选择单元格C3:C7 2。 选择数据|有效性 3。 在“允许”下拉框中选择“自定义” 4。 在“公式”框中。 使用SUM函数统计$C$3:$C$7合计值。 结果必须小于或等于$3500: =SUM($C$3:$C$7)=3500 没有前置或后置间隔 防止用户在输入文本前面或后面加入空白间隔。 TRIM函数移除文本前后空白间隔。 1。 选择单元格B2 2。 选择数据|有效性 3。 在“允许”下拉框中选择“自定义” 4。 在“公式”框中。 输入: =B2=TRIM(B2) 防止输入周末日期 防止输入的日期为星期六或星期日。 WEEKDAY将输入的日期返回到星期。 并且不允许其值为1 (星期日) 和 7 (星期六)。 1。 选择单元格B2 2。 选择数据|有效性 3。 在“允许”下拉框中选择“自定义” 4。 在“公式”框中。 输入: =AND(WEEKDAY(B2)1,WEEKDAY(B2)7) Excel -- 数据有效性 – 添加提示信息 程香宙 你可以给使用电子表格的人员提示信息。在选择有数据有效性的单元格时显示输入信息。如果输入无效数