Excel函数数据有效性例题大全.docx

Excel函数数据有效性例题大全.docx

《Excel函数数据有效性例题大全.docx》由会员分享，可在线阅读，更多相关《Excel函数数据有效性例题大全.docx（19页珍藏版）》请在冰豆网上搜索。

Excel函数数据有效性例题大全

Excel函数与数据有效性配合快速填通知书

用Excel函数中的vlookup查询函数和数据有效性功能配合来填写通知书，可以免去老师们一个一个写的繁琐劳动，这下不用写到手抽筋了！

　　第一步：

处理学生成绩

　　把学生的期末考试成绩放在Sheet1表中，算出每个学生的成绩总分，为了在后面输函数公式时方便，我在前面加了一列“序号”。

把Sheet1表重命名为“考试成绩”。

如图1所示。

　　第二步：

设置“通知书”模版

　　在“考试成绩”表旁的空白表Sheet2中，设置好“通知书”的基本格式和文字内容，页面设置为B5纸，底色可以设置为默认。

如图2所示。

　　右击表“通知书”的A1单元格，选择“设置单元格格式”命令，弹出“单元格格式”对话框，选择“字体”选项卡，把字体颜色设置为“白色”，“确定”即可。

如图3所示。

　　它的作用在后面就会体现出来。

设置好后把此表表名重命名为“通知书”。

　　第三步：

插入“查询函数”

　　在“通知书”表的C3单元格输入函数“=Vlookup（A1，考试成绩!

A3：

J43，2，FALSE）”，如图4所示。

　　此公式的含义是：

使用Vlookup查询函数，根据A1单元格的内容，在“考试成绩”表的A3到J43单元格中进行查询，把查询到相同内容的这行的第2个单元格的内容显示在C3单元格中。

即根据A1单元格的内容，把考试成绩表中与之相同内容的这行的第2个单元格的姓名提取到此单元格。

由此在A10单元格中输入函数“=Vlookup（A1，考试成绩!

A3：

J43，3，FALSE）”，理解了C3、A10单元格的函数后，根据同样的原理我们分别如法设置B10、C10、D10、E10、F10、G10就可以了。

　　第四步：

设置评语的“有效性”

　　选中B11单元格，即“评语”左边的空白单元格，选择菜单栏中的“数据”-“有效性”命令，弹出“数据有效性”对话框，选择“设置”选项卡，设置“允许”条件为“序列”，在“来源”的内容框中输入你对学生的评语，注意在每个评语后面用半角逗号（,）搁开，如：

“该生在校能够尊敬师长，团结同学，努力学习。

该生平时热爱劳动，刻苦学习，能够帮助同学。

”（不带引号）。

如图5所示。

　　单击“确定”命令。

　　第五步：

打印通知书

　　在A1单元格中输入一个序号后按回车健，

在表“考试成绩”中与之相同序号的这一行的学生信息就会自动提取在表“通知书”的相应的各单元格中。

然后根据这个学生的平时情况，在“评语”栏中单击下列箭头按钮选择合适的评语就可以了。

点击“打印”命令，一张完美的学生通知书就会呈现在眼前。

如图6所示。

由于在前面对A1单元格的字体已设置为白色，与背景色一致，因此在打印时不至于显出来而影响通知书的美观。

[应用一]下拉菜单输入的实现

例1：

直接自定义序列

有时候我们在各列各行中都输入同样的几个值，比如说，输入学生的等级时我们只输入四个值：

优秀，良好，合格，不合格。

我们希望Excel2000单元格能够象下拉框一样，让输入者在下拉菜单中选择就可以实现输入。

操作步骤：

先选择要实现效果的行或列；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；在"数据来源"中输入"优秀，良好，合格，不合格"（注意要用英文输入状态下的逗号分隔！

）；选上"忽略空值"和"提供下拉菜单"两个复选框。

点击"输入信息"选项卡，选上"选定单元格显示输入信息"，在"输入信息"中输入"请在这里选择"。

例2：

利用表内数据作为序列源。

有时候序列值较多，直接在表内打印区域外把序列定义好，然后引用。

操作步骤：

先在同一工作表内的打印区域外要定义序列填好（假设在在Z1：

Z8），如“单亲家庭，残疾家庭，残疾学生，特困，低收人，突发事件，孤儿，军烈属”等，然后选择要实现效果的列（资助原因）；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；“来源”栏点击右侧的展开按钮（有一个红箭头），用鼠标拖动滚动条，选中序列区域Z1：

Z8（如果记得，可以直接输入=$Z$1:

$Z$8；选上"忽略空值"和"提供下拉菜单"两个复选框。

点击"输入信息"选项卡，选上"选定单元格显示输入信息"，在"输入信息"中输入"请在这里选择"。

例3：

横跨两个工作表来制作下拉菜单

用INDIRECT函数实现跨工作表

在例2中，选择来源一步把输入=$Z$1:

$Z$8换成=INDIRECT（"表二!

$Z$1:

$Z$8"）,就可实现横跨两个工作表来制作下拉菜单。

[应用二]自动实现输入法中英文转换

有时，我们在不同行或不同列之间要分别输入中文和英文。

我们希望Excel能自动实现输入法在中英文间转换。

操作步骤：

假设我们在A列输入学生的中文名，B列输入学生的英文名。

先选定B列，点击进入"数据\有效性"，打开"数据有效性"对话框；选择"输入法"对话框，在"模式"下拉菜单中选择"关闭（英文模式）"；然后再"确定"，看看怎么样。

[应用三]数据唯一性检验

员工的身份证号码应该是唯一的，为了防止重复输入，我们用“数据有效性”来提示大家。

操作步骤：

选中需要建立输入身份证号码的单元格区域（如B2至B14列），执行“数据→有效性”命令，打开“数据有效性”对话框，在“设置”标签下，按“允许”右侧的下拉按钮，在随后弹出的快捷菜单中，选择“自定义”选项，然后在下面“公式”方框中输入公式：

=COUNTIF（B:

B,B2）=1，确定返回。

以后在上述单元格中输入了重复的身份证号码时，系统会弹出提示对话框，并拒绝接受输入的号码。

通过Excel数据有效性防止重复输入数据

在Excel中录入数据时，有时会要求某列或某个区域的单元格数据具有唯一性，如身份证号码、发票号码之类的数据。

但我们在输入时有时会出错致使数据相同，而又难以发现，这时可以通过 “数据有效性 ”来防止重复输入。

例如我们要在B2:

B200来输入身份证号，我们可以先选定单元格区域B2:

B200，然后单击菜单栏中的 “数据 ”—“有效性 ”命令，打开 “数据有效性 ”对话框，在 “设置 ”选项下，单击 “允许 ”右侧的下拉按钮，在弹出的下拉菜单中，选择 “自定义”选项，然后在下面“公式”文本框中输入公式 “=COUNTIF（$B$2:

$B$200,$B2）=1 ”（不包括引号），选 “确定 ”后返回（如图1）。

以后再在这一单元格区域输入重复的号码时就会弹出提示对话框了（如图2）。

Excel中的数据有效性

在Excel中，我们可以约束某个栏位只能输入某些值，这些值可以是固定的序列，也可以是某些单元格。

下面我们来看看这两种方式如何设置（以下截图是在Excle2007中，Excel2003类似）：

1、固定的序列

【步骤1】通过菜单【数据】->【数据有效性】->【数据有效性...】进入【数据有效性】面板：

【步骤2】【允许】选择【序列】，然后在来源中输入固定值“New,Update,Delete”，以英文逗号隔开：

【步骤3】效果如下：

2、来源为某些单元格：

【步骤1】同方式1

【步骤2】将鼠标点中来源，然后圈选A1～A3：

【步骤3】效果同方式。

注意：

第一种方式不灵活，但是可以拷贝到其他的Excel中直接使用；

第二种方式由于引用了Excel中的单元格，不能拷贝到其他Excel中，也不难拷贝到同一个Excel文档的其他Sheet中，只能在同一个Sheet中使用.

在excel2003中定义有效性标准

要定义允许输人到单元格或者范围中数据的类型，步骤如下：

选择单元格或者范围，选择菜单栏的“数据”——“有效性”，Excel显示“数据有效性”对话框。

单击“数据有效性”对话框中的“设置”选项卡，从“允许”下拉框中选择个选项，要定义公式，选择“自定义”。

从“数据”下拉框中选择定义条件。

所做的选择决定可以访问的其他控制，如下图所示：

单击“输入消息”选项卡，并且定义当用户选择了该单元格，要显示哪个信息。

可以使用这个选项逐步告诉用户需要什么样的数据类型，如图所示

单击“出错警告”选项卡，并且定义当用户进行了无效的输入，要显示哪个错误信息。

风格的选择确定当输入了无效项时用户选择什么。

要防止无效输入，选择停止。

这一步是可选的。

单击“确定”按钮关闭“数据有效性”对话框。

执行了这些步骤之后，单元格或者范围就包吉了所定义的有效性标准。

EXCEL关于数据有效性的应用

使单元格区域内记录不能重复输入控制

=COUNTIF（A:

A,A2）=1（直接复制此公式进去即可）

禁止单元格输入数字控制

=ISNUMBER（A1）<>TRUE

允许单元格只能输入数字控制

=ISNUMBER（A1）=TRUE

禁止单元格输入字母和数字

=LENB（A1）=2

禁止输入周末日期

=AND（WEEKDAY（A1）<>1,WEEKDAY（A1）<>7）

特定前缀输入:

应该含某个字开头

=OR（LEFT（A1）="张",LEFT（A1）="李"）

禁止单元格前后输入多余空格

=A1=TRIM（A1）

禁止输入数字大于某某值

=A1<=100

禁止输入限定的值

>=MAX（A:

A）同<>""同=""

限定区域输入的和的最大值

=SUM（A1:

A10）<100

有效性应用实例一：

我的ID你别用

教师经常要用Excel制作表格，录入学生信息，Excel强大的制表功能，给教师工作带来了方便，但是在表格数据录入过程中难免会出错，一不小心就会录入一些错误的数据，比如重复的身份证号码，超出范围的无效数据等。

其实，只要合理设置“数据有效性”规则，就可以避免错误。

为了便于管理学生信息，每个学生都有属于自己独有的ID（学号），在信息录入时，学生ID不允许重复，如果在Excel录入重复的ID，就会给信息管理带来不便，我们可以对Excel“数据有效性”进行设置，拒绝录入重复数据。

有效性应用实例二：

快速揪出无效数据

有些数据是有范围限制的，比如以百分制记分的考试成绩必须是0—100之间的某个数据，录入此范围之外的数据就是无效数据，如果采用人工审核的方法，要从浩瀚的数据中找到无效数据是件麻烦事，我们可以用Excel的“数据有效性”，快速揪出表格中的无效数据。

现在学生的身份证号已经全部都是18位的新一代身份证了，里面的数字都是有规律的。

前6位数字是户籍所在地的代码，7－14位就是出生日期。

第17位“2”代表的是性别，偶数为女性，奇数为男性。

我们要做的就是把其中的部分数字想法“提取出来”。

STEp1，转换身份证号码格式

我们先将学生的身份证号完整地输入到Excel2003表格中，这时默认为“数字”格式（单元格内显示的是科学记数法的格式），需要更改一下数字格式。

选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。

在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。

STEP2，“提取出”出生日期

将光标指针放到“出生日期”列的单元格内，这里以C2单元格为例。

然后输入“=MID（B2,7,4）&"年"&MID（B2,11,2）&"月"&MID（B2,13,2）&"日"”（注意：

外侧的双引号不用输入，函数式中的引号和逗号等符号应在英文状态下输入）。

回车后，你会发现在C2单元格内已经出现了该学生的出生日期。

然后，选中该单元格后拖动填充柄，其它单元格内就会出现相应的出生日期。

如图1。

图1通过上述方法，系统自动获取了出生年月日信息

小提示：

MID函数是EXCEL提供的一个“从字符串中提取部分字符”的函数命令，具体使用格式在EXCEL中输入MID后会出现提示。

STEP3，判断性别“男女”

选中“性别”列的单元格，如D2。

输入“=IF（MID（B2,17,1）/2=TRUNC（MID（B2,17,1）/2）,"女","男"）”（注意如上）后回车，该生“是男还是女”已经乖乖地判断出来了。

拖动填充柄让其他学生的性别也自动输入。

如图2。

图2性别被自动填入指定位置

这样，通过两个简单的函数，我们就可以让EXCEL从身份证号中自动提取出生日期和性别并填充到单元格内，极大地减轻了我们的输入工作量。

最后效果如图3。

图3用EXECL可以自动识别并填入学生信息

如何不启用宏就不能使用Excel,不启用宏就不能用，应该怎么达到这种效果？

用VBA做了个表个给其他部门用，但头一次他们使用时都要将安全级别设置成低时才能正常使用，有的干脆不启用宏，填完数据就交上来，那些辛苦做的VBA功能根本没用上。

有什么办法，让他们不启用宏就不能使用EXCEL

 简单的办法就是：

PrivateSubWorkbook_BeforeClose（CancelAsBoolean）

Sheet1.Visible=xlSheetHidden

EndSub

PrivateSubWorkbook_Open（）

Sheet1.Visible=xlSheetVisible

EndSub

意思就是sheet1表默认是隐藏的，只要启用宏，那么sheet1表才会自动取消隐藏。

复杂一点的办法就是用老的宏表函数，也可以达到禁用宏就退出，具体的搜索一下本坛，很多的，就搜索禁用宏就退出。

不过这个方法在未来的版本可能会不兼容了吧。

还有个办法，就是干脆在工作表设置一个按钮，只有启用宏按钮才有效果，点击按钮，需要的表格才能出来。

-

如何在Excel2010中快速录入小数

使用以上两种方法虽然可以实现同样的功能，但仍存在一定的区别：

使用方法一更改的设置将对数据表中的所有单元格有效，方法二则只对选中单元格有效，使用方法二可以针对不同单元格的数据类型设置不同的数据格式。

   在工作中笔者要经常录入大批保留三位小数的数据表，数据范围为0.001～100.000，由于大部分数据集中0.001～0.010之间，这样输入一个数据就需要击键5次，录入速度比较慢。

能不能提高输入速度呢？

经过研究，笔者发现通过对数据格式进行重新定义可大大提高录入效率，如输入“0.001”只需输入“1”即可，下面是具体的实现方法和步骤：

　　方法一：

自动设置小数点　　

　　1、选定需要输入数据的单元格;　　

　　2、在“工具”菜单上，单击“选项”，再单击“编辑”选项卡;　　

　　3、选中“自动设置小数点”复选框;　　

　　4、在“位数”框中，输入小数位数，本例中输入“3”;　

　　5、单击“确定”按钮，开始输入数据。

　　编辑提示：

“位数”框中可输入正数，也可以输入负数。

例如，如果在“位数”框中输入“3”，然后在单元格中键入“1”，则其值为“0.001”。

如果在“位数”框中输入“-3”，然后在单元格中键入“1”，则其值为“1000”;在选择“自动设置小数点”选项之前输入的数字不受小数位数的影响。

　　方法二：

自定义数据格式　

　　1、选定需要输入数据的单元格;　

　　2、在“格式”菜单上，单击“单元格”，选中“数字”选项卡;　　 

　　3、在“分类”下拉框中选中“自定义”;　　

　　4、单击“类型”输入框，输入新的格式类型“0.000,”，注意逗号“,”为半角英文字符，而非中文全角标点;　　　　5.单击“确定”按钮，开始输入数据。

　　编辑提示：

可以通过自定义“类型”来定义数据小数位数，在数字格式中包含逗号，可使逗号显示为千位分隔符，或将数字缩小一千倍。

如对于数字“1000”，定义为类型“#　###”时将显示为“1　000”，定义为“#　”时显示为“1”。

-