《Excel数据处理与可视化》教案3——数据规范.docx

《Excel数据处理与可视化》课程教案

授课题目

数据规范

授课类型

理实一体

教学目标

利用数据验证（数据有效性）规范数据输入，包括规范的日期、时间、单位等

利用函数规范数据

重点

1、利用数据验证（数据有效性）规范数据输入

2、利用函数规范数据

难点

利用函数规范数据

教学过程

一利用数据验证（数据有效性）规范数据输入

1规范性别输入

利用数据验证输入性别，不仅规范而且快速。

单击“数据”→“数据验证”，在打开的“数据验证”对话框中选择“设置”选项卡，将“允许”设为“序列”，在“来源”中选择“男,女”。

设置了数据验证后，输入性别时只要选择“男”或“女”就可以了。

特别注意：

（1）在序列来源中，“男”“女”两个字之间一定是“英文状态”下的逗号，即半角逗号。

（2）只要对一个单元格设置了数据验证，就可通过鼠标拖动单元格右下角填充柄，将数据验证的设置填充到其他单元格。

2.限定输入内容

在很多时候，要求只在某些特定值内选择输入单元格的内容。例如，如图182所示的“评定等级”只有优秀、良好、合格、不合格，所有姓名对应的等级必须出自其中之一，而再无其他值，这时就可以利用数据验证来规范等级输入。

在“数据验证”对话框中选择“设置”选项卡，将“允许”设为“序列”，在“来源”中选择4个等级所在的L2:L5区域，如图182所示。

设置了数据验证后，输入等级时只要选择其中之一就可以了。

3.限定数值范围

在“数据验证”对话框中选择“设置”选项卡，将“允许”设为“整数”，“数据”设为“介于”，即可限定输入数值范围。

（1）静态限制输入数值范围。直接输入“最大值”“最小值”，如图183所示。

（2）动态限制输入数值范围。在设置了数据验证以后，可以通过修改“最小值”“最大值”单元格的数值，动态调整数据的允许输入数值范围，如图184所示。

4.限定文本长度

在“数据验证”对话框中选择“设置”选项卡，将“允许”设为“文本长度”，“数据”设为“等于”，“长度”设为“11”，如图185所示。

5.限制输入重复值

单击“数据”→“数据验证”，在弹出的“数据验证”对话框中选择“设置”选项卡，将“允许”设为“自定义”，在“公式”中输入“=COUNTIF(H:H,H1)=1”，如图1-86所示，单击“确定”按钮可以禁止输入重复值。

其中，公式的含义：H列中H1单元格的内容只出现1次。如果H列中H1单元格的内容出现次数超过1，则被禁止输入。

6.限定身份证号码

单击“数据”→“数据验证”，在打开的“数据验证”对话框中选择“设置”选项卡，将“允许”设为“自定义”，在“公式”中输入“=AND(LEN(D1)=18,COUNTIF(D:D,D1*)=1)”，如图187所示。

二、设置只能输入规范的日期

（1）设置日期格式。选中要填充日期的单元格区域，右击，在弹出的快捷菜单中选择“设置单元格格式”命令，在打开的“设置单元格格式”对话框中，将“类型”设置为要求输入的格式，这里选择常用的“*2012/3/14”格式，如图189所示。

（2）利用数据验证规范日期区间。选中要填充日期的单元格区域，在“数据验证”对话框中选择“设置”选项卡，将“允许”设为“日期”，“数据”设为“介于”，“开始日期”设为“2000/1/1”，“结束日期”设为“=today()”，如图190所示。

选中“出错警告”选项卡，将“样式”设为“停止”，“标题”设为“请重新输入：”，“错误信息”设为“请输入格式如2012/03/14的日期，并且起始日期介于2000/1/1与今天之间。”，如图191所示。

通过以上步骤的设置，单元格中就只允许输入规范格式、特定区域的日期。

三巧用数据验证规范时间格式

（1）借助一个辅助单元格，这里是E2。在E2中，输入公式“=NOW()”，显示当前时间。

（2）单击“数据”→“数据验证”，在打开的“数据验证”对话框中选择“设置”选项卡，将“允许”设为“序列”，“来源”设为“=$E$2”，如图1-93所示。在“输入信息”选项卡中，在“输入信息”中输入“请输入当前时间”，如图194所示。

四数据输入不规范，部分数据带数量单位，此时怎么计算平均值

数量单位“分”属于文本，不能参与计算。所以，在写公式时，首先要把单位去除。

去除数量单位“分”文本要用SUBSTITUTE函数，即{=SUBSTITUTE(B2:B10,分,)}。因为要进行的是数组计算，所以按Ctrl+Shift+Enter组合键执行计算。

五一键添加“能计算”的数量单位

选中数据单元格区域，右击，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框。在“分类”中选择“自定义”，直接在“类型”的“G/通用格式”后输入数量单位

六使用多级联动菜