Ecel数据有效性实例

什么就是数据有效性?

数据有效性一个包含帮助您在工作表中输入资料提示信息得工具、它有如下功能:

一给用户提供一个选择列表

一限定输入内容得类型或大小

一自定义设置

Excel-数据有效性-自定义条件示例

防止输入重复值

防止在工作表一定范围输入重复值、本例中，在单元格B3：B10中输入得就是员工

编号、

1、选择单元格B3：B10

2、选择数据|有效性

3、在“允许”下拉框中选择“自定义”

4、在“公式”框中，使用COUNTIF的数统计B3出现次数，在$8$3：$8$10范围内、结果必须就是1或0：

=C0UNTIF($B$3:$B$10,B3)<=1

限定总数

防止一个范围数

据总数超过指定

值、本例中，预

算不能超过

$3500、预算总额

统计得单元格在

C3：C7范围内

1、选择单元格

C3：C7

2、选择数据I有

效性

3、在“允许”下拉框中选择“自定义”

4、在“公式”框中，使用SUM函数统计$C$3：$C$7合计值、结果必须小于或等于$3500:

=SUM($C$3：$C$7)<=350

没有前歪或后置间隔

防止用户在输入文本前面或后面

加入空白间隔、TRIM函数移除文

本前后空白间隔、

1、选择单元格B2

2、选择数据|有效性

3、在“允许”下拉框中选择“自定义”

4、在“公式”框中，揄入：

二B2二TRIM(B2)

防止输入周末日期

防止输入得日期为星期六或星期日、WEEKDAY将输入得日期返回到星期，并且不允许其值为1(星期日)与7

(星期六)、

1、选择单元格B2

2、选择数据|有效性

3、在“允许”下拉框中选择“自定义”

在“公式”框中,输入：=AND(WEEKDAY(B2)<>1,WEEKDAY(B2)07)

创建下拉列表选项

使用数据有效性可以为一个单

元格创建一个选择揄入内容得

下拉列表、列表数据项可以

在工作表得行或列中输入，也

可以交接在数据有效性对话框

中输,K、

1、创建列表数据项

a、在一个半单行或单列中输

入您理在下拉列表中瞧到得条

目、

2、命名列表范围

如果您在一个工作表中输入了一个有效性列表条目，并且给它定义了名称，您就可以在同一工作建得其它工作表

得数据有效性对话框中引用这个名称、

1、选择列表单元格范围、

2、点击公式编辑栏左边得名称框(NameBox)

3、定义一个名称，如：FruitList、FruitList!▼

一名称框

4、接回车键、

稿纸

3、应用数据有效性1I

2I笔记本

a、选择您想应用数据有效性得单元格

b、“数据”t“有效性”、

c、点击“允许”框右侧得下拉箭头，在列

表中选择“序列”

d、在来源对话框中输入一个等号与列

表名称,如:=FruitList

e、点击确定、

您可以使用定义一个范围与INDIRECT函数在数据有效性见表中根据前一单元格内容

限制选择条目、

本例中，如果您在类别中选择水果，在名称下拉列表仅显示水果类名称、

创建名称列表

首先命名单元格范围、

本示例中，第一个列表定义得名称为农产品、

它包括得条目有一水果与蔬菜、

1、创建第一个名称列表

a）在工作簿得空白区域，输入您想在下拉列表中瞧到得条目、它必须就是一个词条,

并且与所属得品名名称相匹配、

b）选择列表包含得单元格:不包括标题）、

c）点击公式编辑栏左侧名称框、

d）为列表输入一个名称，例如：农产品、

e）按回车键、

2、创建对应第一个名称列表得名称列表

a）输入您想在农产品列表类别之一下拉列表中瞧到得词条、

b）选择包含这些词条得单元格列表、

c）点击公式编辑栏左侧得名称框、

d）为这个类别所属得品名列表定义一个名祢，例如：水果、这个名称必须与农产品

列表中所属类别名称正确匹配、

e）按回车键、

水果=苹果

A

一C一D1

农产

2列表

水果列表

水

3果

蔬

4菜

香蒸

5柠藏

6

桃子

f）用同样得方法创建类别中其它条目所属得列表-本例中为蔬菜、

水

果

歹d

n

j

苹

果

香

蕉

柠

檬

桃

子

应用数据有效性

在种类（也就就是上述得类别）列表得单元格右侧有一个下拉箭头显示可以选择输入

得类别、

在品名列表单元格数据有效性中使用了INDIRECT函数创建了一个下拉列表、、

1、应用数据有效性

a）选择您想在数据有效性中应用类别列表得单元格

b）从“数据”菜单中选择“有效性”、

回文件9编辑也）视图9插入9格式。工具豆）数据窗口®）帮助国）

DP昌E'X到100%▼外排序⑤）.

筛选国）

一❹团曲9收藏夹,前往⑥“阳

♦I|EY有效性&）.•.

A2二|=水果

A|BCiDE分列⑥y

由数据透视表和图表报告也）.•・

获取外部数据也）►

，更爵数据电）

C）在“允许”下拉列表中选择“序列”

d）在“来源”框中，输入一个等号与序列名称，例如：=农产品

e）点击“确定”、

数据有效性

111

2、创建所属得数据有效性

a）选择依附类别单元格中已经输入条目（水果或蔬菜）并与这些条目匹配输入得应用

数据有效性得单元格

b）从“数据”菜单中选择“有效性”、

c）在“允许”下拉列表中选择“序列”

d）在“来源”框中，输入一个引用到类别列对应单元格得INDIRECT函数，：

=INDIRECT（A2）

e）点击“确定”、

测试数据有效性

种类列单元格将显示农产品列表、

品名列将根据种类列已经输入得类别显示水果或蔬菜列表

应用两个词条

有时您可能需要在第一个下拉列表中应用两个词条、例如，您可以选择‘红色水果'，

'绿色水果'与‘黄色水果’

1.用上述方法创建第第一个名称范围与下拉列表、

2.应用一个词条创建对应得列表，例如:红色水果，绿色水果，黄色水果

3.在允许下拉框中选择序列，在来源框中使用一个公式移除名称中间隔、例如:

=INDIRECT(SUBSTITUTE(A2,"

DEFGHI

40

在名称中使用非法字符

有时在第一个名称范围下拉列表中名称中可能您要用到定义名称不支持得非法字符，比如连接符

(&)、例如，您选择得条目分别就是‘红色水果绿色水果‘与‘黄色&橙色水果’

1.用上述方法创建第第一个名称范围与下拉列表、

2.使用一个词条名称创建一个支持得名称列表，例如：红色水果，绿色水果，黄色或橙色

水果

3.创建一个包含第一个下拉列表名称得查询表格、

4.在毗邻单元格输入正确得名称

5.命名这个表格，如：NameLookup

6.在允许下拉框中选择序列，在来源框中使用一个公式查找正确得名称、例如：

=INDIRECT(VLOOKUP(A2,NameLookup,2,0))

GK

©富由效性HD

2|麻色水果林里卜1

全部清除0)]崎定

使用动态列表

因为INDIRECT函数得作用仅为引用，并非公式，前面得方法不能工作于动态列表、

您可以使用下面得方法替代它：

1.用上述方法创建第第一个名称范围与下拉列表、

2.创建支持得名称列表，并且命名第个范围得第一个单元格，例如：单元格B1

命名为“水果”且单元格C1命名为“蔬菜”、

3.用每个找到得列表命名列,例如：B列命名为“水果Col”，C列命名为“蔬菜

Col”

在允许下拉框中选择序列,在来源框中使用一个公式推算查找范围、例如I,如果

第一个下拉列表在单元格E2o则公式

为:=0FFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT(E2&*CoD),1)

Excel-数据有效性一在下拉列表中隐藏前面使用过得条目

在数据有效性列表中您可以限制选择条目，隐藏前面选择过得条目、例如，某公司

分派员工出差，为避免重复派出，即已经派出在外得员工再次派出则会造成失误。

这时,您就可以设计在有效性下拉列表中，移除已经使用过得条目（即已经派得员工

不会再出现在侯选列表中）

设置主表

首先设计您想要运用数据有效性得表格布局、本例中，使用了工作表‘时间表’中得

A1:C7单元格范围、在B列使用了数据有效性、

创建列表条目

创建一个您想在数据有效性下拉列表瞧到条目得列表、在这里，在工作表“员工”

得A1:A6,单元格输入员工姓名

创建有效列表

A）输入一个统计已经派出得员工名单得公式、

1、在“员工”工作表得B1单元格输入下列公式：

=IF（COUNTIP（时间表!$B$2:$B$7,Al）>=1,ROW（））

"ABFDEF

程香

宙

1亩

冰

2刘

使用数组公式将空白

龙

3程

单元格移动到底部。

凤

张

玲

4玲

建

杨

新

6新

强

6王

7

8

9在数据有用公式判断名称

效性下拉是否在时间表中

10列表中使出现过

2用的条目

列表

4

2、将公式向下复制到B6单元格、

这个公式统计“程香宙〃在工作表“时间表”中B2:B7区域出现次数、如果返回

结果大于或等于1,单元格将显示为空白、否则则返回行数、

B）创建一个没有使用得名称列表

下一步将使用一个数组公式将空白得单元格移动到序列得最后边、

I、在Cl单元格输入数组公式（这个公式很长，但在同一行输入完成）、

={=IFaSNUMBER(SMALLOff($B$1ROW($B$1:$B$6)),ROW(1:1))),INDIRECT("A"aSMALLClF($B$1

=IF(ISNUMBER(SMALL(IF($B$1：$B$6="",ROW($B$1：$B$6)),ROW(1:1))),

INDIRECT("A"&SMALL(IF($B$1：$B$6="",ROW($B$1：$B$6)),ROW(1:1))),"")

2、按组合键Ctrl+Shift+Enter输入数组公式

3、向下复制公式直到C6单元格、

定义有效列表名称

1、选举插名称＞自定义

2、在名称框中输入一个名称，例如NameChecks

3、在引用框中，输入下面公式（在一行内）：

=OFFSET（员工s!$C$LO,O,COUNTA（员工s!$C$l:$C$6）-COUNTBLANK（员工

s!$C$l:$C$6）,1）

4、点击确定

应用数据有效性

1、选择您要应用数据有效性得单元格

2、选择数翦菜单下得有箔性、

3、从无计吓拉列表中，选择序列

4、在来碰中，输入一个等号与序列名称,例如：:NameCheck

5、点击确定、

测试数据有效性

B列下拉列表中仅显示没有使用过得姓名、已经使用过得姓名已经被移除（即已经派出得员工

不会再次被派出）、

Excel一数据有效性-添加提示信息

您可以给使用电子表格得人员提示信息、在选择有数据有效性得单元格时显示输入信

息、如果输入无效数据则显示出错警告、

输入信息

a）选择您要应用数据有效性得单元格

b）在设置标签下应用需要得数据有效性（什么就是数据有效性?）

c）点击筋人信息标签

d）选中选定单元格时显示输入作息、

数据有效性

设置13511JI出错警告|输入法模式|

9步学单元格时显示输入信息⑤）

选定端格时显示下列输入信息：

e）在标题框内输入信息标题文本、这个文本将发粗体显示在提示框得顶部、

f）在獭入信息框中输入提示信息、

g）点击确定或进行下一项对出错警告进行设置、

出错警告

a）点击出偌警告标签

b）选中输入无效数据时显示出错警告、

数需有效

设置］输入信息出错警告|输入法模式|

金输入无效数据时显示出错警告9

飞入无效数据时显示下列出错警告：

c）从样式下拉列表中选择一种出错警告施C、

1.中止:其作用就是优止输入无效数据、

如果点击量出按钮，则输入得无效数据突出显示，您可以重新输入、

如果点击效消按钮，则自动删除无效数据，单元格恢复原始得内容、

这样可禁止使用者在单元格中输入无效数据

2.警告:为输入无效数据设置一个障碍、

如果点击威涯按钮，则接受无效数据输入，并选择下一个单元格、

如果点击否按钮，无效数据突出显示，可以重新输入、

如果点击依游按钮，无效数据被自动删除，单元格恢复原始内容、

这样可使使用者选择就是否在单元格中输入无效数据、

3.俏会：输入无效数据时给出提示信息、

如果点击破定按钮，则接受无效数据输入，并选择下一个单元格、

如果点击砍消按钮,无效数据被自动删除,单元格恢复原始内容、

这样可使使用者选择就是否在单元格中输入无效数据、

d）在标遨框中输入标题文本、这个文本将以粗体形式显示在弹出得信息窗口上部、

f）在傍错管息框中输入要显示得信息、

g）点击确定

注解：如果Office助手就是打开得，则会弹出下面得提示

Excel一数据有效性-使用源于其它工作簿得列表

您可以使用其它工作簿中得列表作为数据有效性下拉列表条目、

要使数据有效性能够正常运行，包含列表得工作簿必须就是打开得、您可以使这个工作簿总就

是打开，但可以隐藏起来，比如1、X1S工作簿、

创建数据源列表

假定有一个名为1、xls得工作簿，其中有一个名称范围被定义为Name、

引用到数据源

1.打开您想在其中使用数据有效性得工作簿、

2.选我插入＞名称》自定义

3.输入列表名称，如顾客名单

4.在引俄砸中，输入名称范围得引用、首先就是等号，接着就是带单引号得工作表名与

一个感叹号，最后就是定义得名称，例如='1、xls'!name

5点击确定

创建下拉列表

1.选择您要使用数据有效性得单元格、

2.选辑.数据＞有效性

3.在无力框中，选择存冽

4.在来源框中输入列表名称，前面有一个等号，例如:=顾客名单

5.点击确定

数据有效性

Excel一数据有效性-有效性条件示例

整数

设置或排除一定范围内得数值，也可以自定义最小值或最大值、

i.在数据有效性对话框中输入值，或者

2.引用到工作表中得单元格，或者

3.使用公式设置值

小数

设置或排除一定范围内得数值，也可以自定义最小值或最大值、