Excel数据有效性实例

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

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

--限定输入容的类型或大小

--自定义设置

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)<=350

没有前置或后置间隔

防止用户在输入文本前面或后面加入空白间隔. TRIM函数移除文本前后空白间隔.

1. 选择单元格B2

2. 选择数据|有效性

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

4. 在“公式”框中, 输入:

=B2=TRIM(B2)

防止输入周末日期

防止输入的日期为星期六或星期日. WEEKDAY将输入的日期返回到星期, 并且不允许其值为1 (星期日) 和 7 (星期六).

1. 选择单元格B2

2. 选择数据|有效性

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

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

创建下拉列表选项

使用数据有效性可以为一个单元格创建一个选择输入容的下拉列表. 列表数据项可以在工作表的行或列中输入, 也可以直接在数据有效性对话框中输入.

1. 创建列表数据项

a. 在一个半单行或单列中输入你想在下拉列表中看到的条目.

2.命名列表围

如果你在一个工作表中输入了一个有效性列表条目，并且给它定义了名称，你就可以在同一工作簿的其它工作表的数据有效性对话框中引用这个名称.

1. 选择列表单元格围.

2. 点击公式编辑栏左边的名称框(Name Box)

3. 定义一个名称,如： FruitList.

4. 按回车键.

3. 应用数据有效性

a.选择你想应用数据有效性的单元格

b.“数据”→“有效性”.

c.点击“允许”框右侧的下拉箭头，在列表中选择“序列”

d. 在来源对话框中输入一个等号和列表名称, 如: =FruitList

e. 点击确定.

你可以使用定义一个围和INDIRECT函数在数据有效性列表中根据前一单元格容限制选择条目.

本例中, 如果你在类别中选择水果, 在名称下拉列表仅显示水果类名称.

创建名称列表

首先命元格围.

本示例中, 第一个列表定义的名称为农产品.

它包括的条目有-- 水果和蔬菜.

1. 创建第一个名称列表

a) 在工作簿的空白区域, 输入你想在下拉列表中看到的条目.它必须是一个词条，并且与所属的品名名称相匹配.

b) 选择列表包含的单元格 (不包括标题).

c) 点击公式编辑栏左侧名称框.

d) 为列表输入一个名称, 例如：农产品.

e) 按回车键.

2. 创建对应第一个名称列表的名称列表

a) 输入你想在农产品列表类别之一下拉列表中看到的词条.

b) 选择包含这些词条的单元格列表.

c) 点击公式编辑栏左侧的名称框.

d) 为这个类别所属的品名列表定义一个名称, 例如:水果. 这个名称必须与农产品列表中所属类别名称正确匹配.

e) 按回车键.

f)用同样的方法创建类别中其它条目所属的列表–本例中为蔬菜.

应用数据有效性

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

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

1. 应用数据有效性

a) 选择你想在数据有效性中应用类别列表的单元格

b) 从“数据”菜单中选择“有效性”.

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

d) 在“来源”框中, 输入一个等号和序列名称, 例如: =农产品

e) 点击“确定”.

2. 创建所属的数据有效性

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

b) 从“数据”菜单中选择“有效性”.

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

d) 在“来源”框中, 输入一个引用到类别列对应单元格的INDIRECT函数,:

=INDIRECT(A2)

e) 点击“确定”.

测试数据有效性

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

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

应用两个词条

有时你可能需要在第一个下拉列表中应用两个词条. 例如, 你可以选择'红色水果', '绿色水果'和'黄色水果'

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

2.应用一个词条创建对应的列表,例如：红色水果, 绿色水果, 黄色水果

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

=INDIRECT(SUBSTITUTE(A2," ",""))

在名称中使用非法字符

有时在第一个名称围下拉列表中名称中可能你要用到定义名称不支持的非法字符, 比如连接符(&). 例如, 你选择的条目分别是'红色水果', '绿色水果' 和 '黄色&橙色水果'

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

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

水果

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

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

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

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

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

使用动态列表

因为INDIRECT函数的作用仅为引用, 并非公式, 前面的方法不能工作于动态列表. 你可以使用下面的方法替代它:

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

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

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

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

菜Col”

在允许下拉框中选择序列,在来源框中使用一个公式推算查找围. 例如, 如果第一个下拉列表在单元格 E2。则公式为：

=OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT(E2&"Col")),1)

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

在数据有效性列表中你可以限制选择条目, 隐藏前面选择过的条目. 例如, 某公司分派员工出差，为避免重复派出，即已经派出在外的员工再次派出则会造成失误。

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

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

.

设置主表

首先设计你想要运用数据有效性的表格布局. 本例中, 使用了工作表'时间表' 中的A1:C7 单元格围.在B列使用了数据有效性.

创建列表条目

创建一个你想在数据有效性下拉列表看到条目的列表. 在这里, 在工作表“员工”的A1:A6, 单元格输入员工

创建有效列表

A) 输入一个统计已经派出的员工的公式.

1. 在“员工”工作表的B1单元格输入下列公式:

=IF(COUNTIF(时间表!$B$2:$B$7,A1)>=1,"",ROW())

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

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

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

B) 创建一个没有使用的名称列表

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

1. 在C1单元格输入数组公式 (这个公式很长，但在同一行输入完成).

=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. 在名称框中输入一个名称，例如 NameCheck.

3. 在引用框中, 输入下面公式 (在一行):

=OFFSET(员工s!$C$1,0,0,COUNTA(员工s!$C$1:$C$6)-COUNTBLANK(员工

s!$C$1:$C$6),1)

4. 点击确定

应用数据有效性

1.选择你要应用数据有效性的单元格

2. 选择数据菜单下的有效性.

3. 从允许下拉列表中, 选择序列

4. 在来源框中, 输入一个等号和序列名称,例如: =NameCheck

5. 点击确定.

测试数据有效性

B列下拉列表中仅显示没有使用过的.已经使用过的已经被移除（即已经派出的员工不会再次被派出）.

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

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

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

输入信息

a)选择你要应用数据有效性的单元格

b)在设置标签下应用需要的数据有效性 (什么是数据有效性?)

c) 点击输入信息标签

d) 选中选定单元格时显示输入作息.

e) 在标题框输入信息标题文本. 这个文本将发粗体显示在提示框的顶部.

f) 在输入信息框中输入提示信息.

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

出错警告

a) 点击出错警告标签

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

c) 从样式下拉列表中选择一种出错警告样式.

1.中止: 其作用是防止输入无效数据.

如果点击重试按钮, 则输入的无效数据突出显示,你可以重新输入.

如果点击取消按钮, 则自动删除无效数据, 单元格恢复原始的容.

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

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

如果点击是按钮, 则接受无效数据输入, 并选择下一个单元格.

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

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

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

3.作息: 输入无效数据时给出提示信息.

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

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

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

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

f) 在出错信息框中输入要显示的信息.

g) 点击确定

注解: 如果Office 助手是打开的，则会弹出下面的提示

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

你可以使用其它工作簿中的列表作为数据有效性下拉列表条目.

要使数据有效性能够正常运行, 包含列表的工作簿必须是打开的. 你可以使这个工作簿总是打开, 但可以隐藏起来, 比如 1.xls 工作簿.

创建数据源列表

假定有一个名为1.xls的工作簿,其中有一个名称围被定义为Name.

引用到数据源

1.打开你想在其中使用数据有效性的工作簿.

2.选择插入>名称>自定义

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

4.在引用到框中, 输入名称围的引用. 首先是等号, 接着是带单引号的工作表名和一个感

叹号, 最后是定义的名称, 例如='1.xls'!name

5.点击确定

创建下拉列表

1.选择你要使用数据有效性的单元格.

2.选择数据>有效性

3.在允许框中, 选择序列

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

5.点击确定

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()))

文本长度

设置或排除一定围的长度,也可以自定义最小值或最大值.

1. 1.在数据有效性对话框中输入长度, 或者

2. 2.引用到工作表中的单元格, 或者

3. 3.使用公式设置长度

Excel数据有效性(数据验证)应用详解

Excel数据有效性（数据验证）应用详解 我们可以利用数据有效性制作表格模板，强制性要求其他人按规矩填写表格。 看课件： 1、利用数据验证为单元格的数据输入设置条件限制 在表格内输入数据时，我们可以利用数据验证来规范数据的类型，甚至限制输入数值的大小范围。我们先来利用有效性对基本工资这一列进行设置：规定只能填写整数，并且不低于3500 选中这一列，然后点击数据有效性

在【允许】下拉选项里选中整数（这里还有很多其他的项目，有兴趣的朋友可以抽空自己琢磨琢磨） 选中整数以后，下面会出现【数据】这个下拉选项，如果【允许】选择的是其他项目，下面的选项菜单也会发生相应变化。 最小值我们填入3500，点确定就好了

这时候如果输入的数据不符合我们的规定，就会弹出提示框。 接下来我们对身份证号码这一列进行设置，要求是长度必须等于18位，防止输入错误： 同样的，选择这一列，设置有效性：文本长度等于18.

当输入的号码不是18位的时候，同样会提示错误。 对于日期的输入，是不规范的情况最多的一类数据，我们同样可以使用数据有效性进行限制：只能输入2010年1月1日到2018年1月31日之间的日期，并且只能是标准的日期格式： 如图进行设置。 特别说明一点，如果在开始日期或者结束日期输入格式不对的日期时，是会报错的：

2018.1.31这种是最常见的错误格式。 日期超过范围会提示

日期格式不对也会提示 接下来对性别进行设置，只能输入男或者女： 注意，来源里的项目之间用英文的逗号分隔。 这样设置以后，就可以使用下拉菜单进行填表了。 再来对姓名进行设置，要求是不能出现重名，如果有重名的话，需要加数字进行区分。

工作表保护和数据有效性的综合案例

工作表保护和数据有效性的综合案例 彭志伟 广州市数宇软件有限公司 数据有效性是Excel当中一个非常实用的功能，它可以对单元格的输入内容进行限制，从而达到表格数据输入规范和准确的目的。工作表保护可以锁定工作表的内容，并且可以控制哪些单元格允许用户填写，哪些单元格不能填写。企业学员对这两项功能也特别感兴趣，在企业中的应用也很多，尤其是用在模板方面。 下面这个范例是一张报销单的例子，它综合了工作表保护和数据有效性两个功能来实现的。首先，请大家先新建立一个Excel文档，并仿照下图建立一个数据表。 报销单功能描述： ●整张报销单中，除了白色底纹的单元格可以录入数据以外，其他的单元格都被保护。 ●C5单元格只能输入1-4个字符，如输入错误能提示“本单元格文本长度必须为1-4 个字符。”字样。 ●E5单元格只能输入介于18到60之间的整数，如输入错误能提示“本单元格必须 输入18-60之间的整数。”字样 ●G5单元格做成下拉菜单，菜单项包括“男”和“女”。 ●C6单元格做成下拉菜单，菜单项包括“财务部”、“销售部”、“培训部”和“人事 部”。 ●F6单元格能自动出现当前日期。 ●F8:F11区域单元格只能输入数字，并且整张报销单的总金额不能超过一万元，单 元格设置为带货币的两位小数格式。 ●D12单元格能自动统计F8:F11区域的总和。

操作提示： 1.选中C5单元格，打开“数据/有效性”，在“有效性条件”作如下图设置：

4.对C6单元格作如下图的有效性设置： 5.在F6单元格中输入函数= today() 。 6.选中F8:F11区域，将此区域的有效性设置为自定义，并输入以下公式 =AND(ISNUMBER(F8),SUM($F$8:$G$11)<10000) 7.在D12单元格中输入函数= sum(F8:G11)。

(完整版)EXCEL--数据有效性

数据的有效性----设置输入条件 在很多情况下，设置好输入条件后，能增加数据的有效性，避免非法数据的录入。如年龄为负数等。那么有没有办法来避免这种情况呢？有，就是设置数据的有效性。设置好数据的有效性后，可以避免非法数据的录入。 下面我们还是以实例来说明如何设置数据的有效性。 1. 设置学生成绩介于0----100分之间。 图6-4-1 假设试卷的满分100分，因此学生的成绩应当介于0----100分之间的。我们可以通过以下几步使我们录入成绩时保证是0----100分之间，其它的数据输入不能输入。 第1步：选择成绩录入区域。 此时我们选中B3：C9 。 第2步： 点击菜单数据―>有效性，弹出数据有效性对话框。界面如图6-4-2所示。

图6-4-2 在图6-4-2中的默认的是允许任何值输入的，见图中红色区域所示。 第3步：点击允许下拉按钮，从弹出的选项中选择小数。（如果分数值是整，此处可以选择整数） 图6-4-3

选择后界面如图6-4-4所示。在图6-4-4中，数据中选择介于，最小值：0 ，最大值：100 。 图6-4-4 第4步：点击确定按钮，完成数据有效性设置。 下面我们来看一下，输入数据时有何变化。见图6-4-5所示。

图6-4-5 从图中可以看出，我们输入80是可以的，因为80介于0----100之间。而输入566是不可以的，因为它不在0----100之间。 此时，点击重试按钮，重新输入，点击取消按钮，输入的数据将被清除。 第5步：设置输入信息 在数据有效性对话框中，点击输入信息页，打开输入信息界面。如图6-4-6所示。 图6-4-6 设置好输入信息后，我们再来看输入数据时，界面有何变化。如图6-4-7所示。

用ExcelXX数据有效性拒绝错误数据

用Excel XX数据有效性拒绝错误数据 Excel强大的制表功能，给我们的工作带来了方便，但是在表格数据录入过程中难免会出错，一不小心就会录入一些错误的数据，比如重复的身份证号码，超出范围的无效数据等。其实，只要合理设置数据有效性规则，就可以避免错误。下面咱们通过两个实例，体验Excel XX数据有效性的妙用实例一：拒绝录入重复数据 身份证号码、工作证编号等个人ID都是唯一的，不允许重复，如果在Excel录入重复的ID，就会给信息管理带来不便，我们可以通过设置Excel XX 的数据有效性，拒绝录入重复数据。 运行Excel XX，切换到“数据”功能区，选中需要录入数据的列(如：A列)，单击数据有效性按钮，弹出“数据有效性”窗口。 图1 数据有效性窗口切换到“设置”选项卡，打下“允许”下拉框，选择“自定义”，在“公式”栏中输入“=countif(a:a,a1)=1”（不含双引号，在英文半角状态下输入）。 图2 设置数据有效性条件切换到“出错警告”选项卡，选择出错出错警告信息的样式，填写标题

和错误信息，最后单击“确定”按钮，完成数据有效性设置。 图3 设置出错警告信息这样，在A列中输入身份证等信息，当输入的信息重复时，Excel立刻弹出错误警告，提示我们输入有误。 图4 弹出错误警告这时，只要单击“否”，关闭提示消息框，重新输入正确的数据，就可以避免录入重复的数据。 实例二：快速揪出无效数据 用Excel处理数据，有些数据是有范围限制的，比如以百分制记分的考试成绩必须是0—100之间的某个数据，录入此范围之外的数据就是无效数据，如果采用人工审核的方法，要从浩瀚的数据中找到无效数据是件麻烦事，我们可以用Excel XX的数据有效性，快速揪出表格中的无效数据。 用Excel XX打开一份需要进行审核的Excel 表格，选中需要审核的区域，切换到“数据”功能区，单击数据有效性按钮，弹出数据有效性窗口，切换到“设置”选项卡，打开“允许”下拉框，选择“小数”，打开“数据”下拉框，选择“介于”，最小值设为0，最大值设为100，单击“确定”按钮（如图5）。

excel数据有效性的应用范文

技巧1 在单元格创建下拉列表 有许多新手在EXCEL中第一次见到下图所示的下拉列表时，都以为是程序做的，当他们知道图中下拉列表只是一个普通的利用数据有效性完成的EXCEL技巧时，他们会觉得很惊奇。 那么，现在我们一起学习一下，怎么利用数据有效性来做个下拉列表吧： 第一步在一个连续的单元格区域输入列表中的项目,如图中E7:E11有个商品名称的表 第二步选中A2单元格，单击“菜单”——“数据”——“有效性”,在“数据有效性”对话框的"设置"选项卡中,在“允许”下拉列表中选择“序列” 项. 第三步在"来源"框中输入“=$E$8:$E$11”（或输入“=”号后，用鼠标选中E8:E11） 第四步勾选"忽略空值"与"提供下拉箭头"复选框,如图所示 第五步单击"确定"按钮,关闭"数据有效性"对话框. 这样,就能实现第一张图所示的效果了。 如果列表的内容较少,或者不方便在工作表中输入列表项目,也可以省略上述的第一步,然后将第

三步的操作 改为:直接在"来源"框中输入列表内容,项目之间以半角的逗号分隔.如图所示 在一般情况下,数据的有效性中的序列来源,只能引用当前工作表中的单元格区域。 如果希望能够引用其他工作表中的单元格区域,则必须先为单元格区域定义名称,然后在"来源"框中输入名称. 例如,将另一张工作表中的A2:A10区域，名称定义为“SPMC”,然后在“数据有效性”的“来源”框中输入“=SPMC”。 技巧二： 另类的批注 当我们需要对表格中的项目进行特别说明时,常常会使用EXCEL的批注功能。给单元格做批注的方法，这里不 多浪费时间。而给大家介绍一下另类批注： 使用批注多了，我们会发现EXCEL的批注也有不足之处: 一、批注框的大小尺寸会受到单元格行高变化的影响; 二、批注框的默认情况下，是只显示标识符。必须把光标悬停在单元格的上方批注内容才会显示出来，否则即使当单元格处于活动状态时，它也不会显示; 三、是在上面2种情况的共同作用下，加上拆分（冻结）窗口下的插入、拖曳等工作表操作，会导致批注的位置远离原来的单元格，而被主人遗忘，并随着主人对单元格的复制或格式刷操作而被大量复制，这也是造成文件增肥的主要原因之一。我曾经为一个会员给他的文件减肥时，从表里找出3500多个远离母单元格的批注弃儿，最终我通过删除这些个“批注弃儿”，帮那个会员给文件容量缩减了2/3之多。 言归正传，说说数据有效性 利用数据有效性功能，我们可以实现另类的批注效果，克服以上不足。 第一步：选定单元格，如C1。 第二步：单击菜单"数据"-"有效性",在"数据有效性"对话框的"输入信息"选项卡中,勾选"选定单元格时显示

EXCEL中数据有效性自定义怎么使用

EXCEL中数据有效性“自定义”怎么使用[应用一]下拉菜单输入的实现 例1：直接自定义序列 有时候我们在各列各行中都输入同样的几个值，比如说，输入学生的等级时我们只输入四个值：优秀，良好，合格，不合格。我们希望Excel2000单元格能够象下拉框一样，让输入者在下拉菜单中选择就可以实现输入。 操作步骤：先选择要实现效果的行或列；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；在"数据来源"中输入"优秀，良好，合格，不合格"（注意要用英文输入状态下的逗号分隔！）；选上"忽略空值"和"提供下拉菜单"两个复选框。点击"输入信息"选项卡，选上"选定单元格显示输入信息"，在"输入信息"中输入"请在这里选择"。 例2：利用表内数据作为序列源。 有时候序列值较多，直接在表内打印区域外把序列定义好，然后引用。 操作步骤：先在同一工作表内的打印区域外要定义序列填好(假设在在Z1：Z8），如“单亲家庭，残疾家庭，残疾学生，

特困，低收人，突发事件，孤儿，军烈属”等，然后选择要实现效果的列（资助原因）；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；“来源”栏点击右侧的展开按钮（有一个红箭头），用鼠标拖动滚动条，选中序列区域Z1：Z8（如果记得，可以直接输入=$Z$1:$Z$8；选上"忽略空值"和"提供下拉菜单"两个复选框。点击"输入信息"选项卡，选上"选定单元格显示输入信息"，在"输入信息"中输入"请在这里选择"。 例3：横跨两个工作表来制作下拉菜单 用INDIRECT函数实现跨工作表 在例2中，选择来源一步把输入=$Z$1:$Z$8换成=INDIRECT("表二!$Z$1:$Z$8"),就可实现横跨两个工作表来制作下拉菜单。 [应用二]自动实现输入法中英文转换 有时，我们在不同行或不同列之间要分别输入中文和英文。我们希望Excel能自动实现输入法在中英文间转换。

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

Excel函数与数据有效性配合快速填通知书 用Excel函数中的vlookup查询函数和数据有效性功能配合来填写通知书，可以免去老师们一个一个写的繁琐劳动，这下不用写到手抽筋了！ 第一步：处理学生成绩 把学生的期末考试成绩放在Sheet1表中，算出每个学生的成绩总分，为了在后面输函数公式时方便，我在前面加了一列“序号”。把Sheet1表重命名为“考试成绩”。如图1所示。 第二步：设置“通知书”模版 在“考试成绩”表旁的空白表Sheet2中，设置好“通知书”的基本格式和文字内容，页面设置为B5纸，底色可以设置为默认。如图2所示。

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

它的作用在后面就会体现出来。设置好后把此表表名重命名为“通知书”。 第三步：插入“查询函数” 在“通知书”表的C3单元格输入函数“=Vlookup(A1，考试成绩!A3：J4 3，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就可以了。

EXCEL如何设置数据有效性

怎样在excel中利用有效性序列建立二级下拉菜单 比如在A列中选择部门名称，B列中的选择菜单自动会变成该部门下所有员工 问题补充： 问题一样。那答案呢？ madm 的二级下拉菜单的公式如何运用。。能否发文件上来 提问者： superaoyi - 一级最佳答案 试试、看看，是否所需！ 设置“数据”表 A列 B列 部门员工 A 张三李四 B 王二郑大 C 刘一王五 D 初一赵钱 …… 命名： 选中A列，在“名称框”中输入“部门”，回车确认。 选中B列，在“名称框”中输入“员工”，回车确认。 在“菜单”表制作下拉菜单： 制作一级下拉菜单 选中A1：B1单元格区域； 执行“数据/有效性”命令，打开“数据有效性”对话框； 在“设置”选项卡下，“允许”选择“序列”、“来源”中输入“部门，员工”（不含引号，用英文逗号分隔）； 选中“忽略空值”、“提供下拉箭头”，单击“确定”按钮，完成一级下拉菜单制作。 此时在A1、B1中，单击右侧的下拉按钮进行选择输入。 制作二级下拉菜单 从A2单元格起向下选中单元格区域； 执行“数据/有效性”命令，打开“数据有效性”对话框； 在“设置”中，“允许”选择“序列”、“来源”中输入公式“=INDIRECT(A$1)”； 选中“忽略空值”、“提供下拉箭头”，单击“确定”按钮，完成“部门”的二级菜单制作。同法制作“员工”的二级菜单。此时“来源”中输入公式“=INDIRECT(B$1)”。

此时在部门、员工下面的单元格中，单击右侧的下拉按钮进行“部门”、“员工”的选择输入。 Excel二级下拉菜单下拉菜单的相关联(2009-07-22 10:43:49) 标签：杂谈 公司因业务需要，经常要向外界发送大量信函，因此查找邮政编码，就成了一件非常头痛的事，于是，我就用Excel制作了一个简单的查询表，使用起来觉得很方便，现在就推荐给大家。 1. 启动Excel 2003(其他版本请大家仿照操作)，新建一工作簿，取名保存。 2. 切换到Sheet2工作表中，仿照图1的样式，将相关数据输入到表格相应的单元格中。 提示：有关邮政编码的数据可以在网络上搜索到，然后复制粘贴到Excel中，再整理一下即可。 3.选中B1至B10单元格(即北京市所有地名所在的单元格区域)，然后将鼠标定位在右上侧“名称框”中，输入“北京市”字样，并用“Enter”键进行确认。 4. 仿照上面的操作，对其他省、市、自治区所在的单元格区域进行命名。 提示：命名的名称与E列的省、市、自治区的名称保持一致。 5. 选中E1至E30单元格区域，将其命名为“省市”(命名为其他名称也可)。 6. 切换到Sheet1工作表中，仿照图3的样式，输入“选择省市”等相关固定的字符。 图3 选择查询的省市 7.选中B5单元格，执行“数据→有效性”命令，打开“数据有效性”界面(如图4)，单击“允许”右侧的下拉按钮，在随后弹出的下拉列表中，选择“序列”项，然后在“来源”

Excel 2011数据有效性的妙用

Excel 2010数据有效性的妙用 来源: https://www.sodocs.net/doc/f63511436.html,时间: 2010-01-29 作者: apollo Excel强大的制表功能，给我们的工作带来了方便，但是在表格数据录入过程中难免会出错，一不小心就会录入一些错误的数据，比如重复的身份证号码，超出范围的无效数据等。Excel 2010中有个“数据有效性”的功能，只要合理设置数据有效性规则，就可以避免错误。下面咱们通过两个Excel 2010数据有效性应用实例，体验Excel 2010数据有效性的妙用。 实例一：拒绝录入重复数据 身份证号码、工作证编号等个人ID都是唯一的，不允许重复，如果在Excel 录入重复的ID，就会给信息管理带来不便，我们可以通过设置Excel 2010的数据有效性，拒绝录入重复数据。 运行Excel 2010，切换到“数据”功能区，选中需要录入数据的列(如：A列)，单击数据有效性按钮，弹出“数据有效性”窗口。 图1数据有效性窗口 切换到“设置”选项卡，打下“允许”下拉框，选择“自定义”，在“公式”栏中输入“=countif(a:a,a1)=1”（不含双引号，在英文半角状态下输入）。

图2设置数据有效性条件 切换到“出错警告”选项卡，选择出错出错警告信息的样式，填写标题和错误信息，最后单击“确定”按钮，完成数据有效性设置。 图3设置出错警告信息 这样，在A列中输入身份证等信息，当输入的信息重复时，Excel立刻弹出错误警告，提示我们输入有误。

图4弹出错误警告 这时，只要单击“否”，关闭提示消息框，重新输入正确的数据，就可以避免录入重复的数据。 实例二：快速揪出无效数据 用Excel处理数据，有些数据是有范围限制的，比如以百分制记分的考试成绩必须是0—100之间的某个数据，录入此范围之外的数据就是无效数据，如果采用人工审核的方法，要从浩瀚的数据中找到无效数据是件麻烦事，我们可以用Excel 2010的数据有效性，快速揪出表格中的无效数据。 用Excel 2010打开一份需要进行审核的Excel表格，选中需要审核的区域，切换到“数据”功能区，单击数据有效性按钮，弹出数据有效性窗口，切换到“设置”选项卡，打开“允许”下拉框，选择“小数”，打开“数据”下拉框，选择“介于”，最小值设为0，最大值设为100，单击“确定”按钮（如图5）。

最新excel数据有效性实例培训讲学

什么是数据有效性? 数据有效性一个包含帮助你在工作表中输入资料提示信息的工具. 它有如下功能: --给用户提供一个选择列表 --限定输入内容的类型或大小 --自定义设置 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)<=350

没有前置或后置间隔 防止用户在输入文本前面或后面加入空白间隔. TRIM函数移除文本前后空白间隔. 1. 选择单元格B2 2. 选择数据|有效性 3. 在“允许”下拉框中选择“自定义” 4. 在“公式”框中, 输入: =B2=TRIM(B2) 防止输入周末日期 防止输入的日期为星期六或星期日. WEEKDAY将输入的日期返回到星期, 并且不允许其值为1 (星期日) 和7 (星期六). 1. 选择单元格B2 2. 选择数据|有效性 3. 在“允许”下拉框中选择“自定义” 在“公式”框中, 输入: =AND(WEEKDAY(B2)<>1,WEEKDAY(B2)<>7) 创建下拉列表选项 使用数据有效性可以为一个单元格创建一个选择输入内容的下拉列表. 列表数据项可以在工作表的行或列中输入, 也可以直接在数据有效性对话框中输入. 1. 创建列表数据项 a. 在一个半单行或单列中输入你想在下拉列表中看到的条目. 2.命名列表范围 如果你在一个工作表中输入了一个有效性列表条目，并且给它定义了名称，你就可以在同一工作簿的其它工作表的数据有效性对话框中引用这个名称. 1. 选择列表单元格范围. 2. 点击公式编辑栏左边的名称框(Name Box) 3. 定义一个名称,如：FruitList. 4. 按回车键.

excel 中数据的有效性的应用

excel 中数据的有效性的应用 3、防止数据输入错误 典型应用如下： （1）防止日期错误： 只准输入日期或某个日期之后的特定日期：点击EXCEL菜单“数据-有效性-”，在“设置-允许”对话框中选择“日期”、并在相应位置输入起始日期。 （2）只准输入整数： 在“设置-允许”对话框中选择“整数”

（3）防止输入重复值： 首先要选定整行/列或单元格区域（比如选中B列），然后再点击EXCEL 菜单“数据-有效性-”，在“设置”对话框中选择“自定义”、在“公式”中输入“=COUNTIF(B:B,B1)<2”

检验一下：在B3单元格中输入“A”，看看会出现什么结果？ （4）只能输入大于上一行的数值（或日期）： 注意引用区域的最后一行行标为相对引用。A4的有效性公式为：=MAX($B$3:$B4) 4、条件输入 只准输入符合一定条件的数据： （1）只能输入大于左侧的数字

（2）按条件输入_根据左侧条件来决定右侧单元格如何输入： 下图中，如果单据类型选择了“入库单”，则只能在“入库数量”所在列即C列输入数据，而不能在“出库数量”所在列即D列输入数据；反之亦然。

C列公式为：=IF(B5="入库单",ISNUMBER(C5),FALSE) D列公式为：=IF(B5="出库单",ISNUMBER(D5),FALSE) [应用一]下拉菜单输入的实现 例1：直接自定义序列 有时候我们在各列各行中都输入同样的几个值，比如说，输入学生的等级时我们只输入四个值：优秀，良好，合格，不合格。我们希望Excel2000单元格能够象下拉框一样，让输入者在下拉菜单中选择就可以实现输入。 操作步骤：先选择要实现效果的行或列；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许

excel2007数据有效性运用实例

Excel数据有效性的运用实例 例1：数据唯一性检验 员工的身份证号码应该是唯一的，为了防止重复输入，我们用“数据有效性”来提示大家。操作步骤：选中需要建立输入身份证号码的单元格区域（如B2至B14列），执行“数据→有效性”命令，打开“数据有效性”对话框，在“设置”标签下，按“允许”右侧的下拉按钮，选择“自定义”选项，然后在下面“公式”方框中输入公式：=COUNTIF(B:B,B2)=1，确定返回。以后在上述单元格中输入了重复的身份证号码时，系统会弹出提示对话框，并拒绝接受输入的号码。 例2：利用表内数据作为序列源。 有时候序列值较多，直接在表内打印区域外把序列定义好，然后引用。 操作步骤：先在同一工作表内的打印区域外要定义序列填好(假设在在Z1：Z8），如“单亲家庭，残疾家庭，残疾学生，特困，低收人，突发事件，孤儿，军烈属”等，然后选择要实现效果的列（资助原因）；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；“来源”栏点击右侧的展开按钮（有一个红箭头），用鼠标拖动滚动条，选中序列区域Z1：Z8（如果记得，可以直接输入=$Z$1:$Z$8；选上"忽略空值"和"提供下拉菜单" 两个复选框。点击"输入信息"选项卡，选上"选定单元格显示输入信息"，在"输入信息"中输入"请在这里选择"。 例3：横跨两个工作表来制作下拉菜单 用INDIRECT函数实现跨工作表 在例2中，选择来源一步把输入=$Z$1:$Z$8换成=INDIRECT("表 二!$Z$1:$Z$8"),就可实现横跨两个工作表来制作下拉菜单。 例4：自动实现输入法中英文转换 有时，我们在不同行或不同列之间要分别输入中文和英文。我们希望Excel能自动实现输入法在中英文间转换。 操作步骤：假设我们在A列输入学生的中文名，B列输入学生的英文名。先选定B列，点击进入"数据\有效性"，打开"数据有效性"对话框；选择"输入法"对话框，在"模式"下拉菜单中选择"关闭（英文模式）"；然后再"确定"，看看怎么样。 例5： 在设置下位框时,怎么让“数据有效性”中“序列”中的“来源”从另外一个“SHEE T”中选取： 1.首先建立一个基础数据表，然后将四个科室的班级名称依次分别输入在A列至D列的单元格中，每一科室单独一列。科室的名称可放在该列的最上面一行。在E1单元格输入“科室”，并在其下方单元格中分别录入各科室名称。如图1所示。

Excel数据有效性实例

什么是数据有效性？ 数据有效性一个包含帮助你在工作表中输入资料提示信息的工具?它有如下功能--给用户提供一个选择列表--限定输入内容的类型或大小 --自定义设置 1. 选择单元格B3:B10 2. 选择数据|有效性 3. 在“允许”下拉框中选择“自定 义” 防止输入重复值 防止在工作表一定范围输入重复值本例中，在单元格B3:B10中输入的是员工编号 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)<=350

没有前置或后置间隔 防止用户在输入文本前面或后面加入空白间隔.TRIM函数移除文本前后空白间隔. 1. 选择单元格B2 2. 选择数据|有效性 3. 在“允许”下拉框中选择“自定义” 4. 在“公式”框中,输入： =B2=TRIM(B2) 防止输入周末日期 防止输入的日期为星期六或星期日.WEEKDAY 将输入的日期返回到星期，并且不允许其值为 1 (星期日)和7 (星期六). 1. 选择单元格B2 2. 选择数据|有效性 3. 在“允许”下拉框中选择“自定义” 在“公式”框中，输入：=AND(WEEKDAY(B2)<>1,WEEKDAY(B2)<>7) 创建下拉列表选项 使用数据有效性可以为一个单元格创建一个选择输入内容的下拉列表.列表数据项可以在工作表的行或列中输 入,也可以直接在数据有效性对话框中输入. 1. 创建列表数据项 a.在一个半单行或单列中输入你想在下拉列表中看到的条目 2. 命名列表范围 如果你在一个工作表中输入了一个有效性列表条目，并且给它定义了名称，你就可以在同一工作簿的其它工作表的数据有效性对话框中引用这个名称 1. 选择列表单元格范围? 2. 点击公式编辑栏左边的名称框(Name Box) 3. 定义一个名称，如：FruitList.

5个示例让你掌握公式在excel数据有效性自定义中的用法

5个示例让你重新认识excel数据有效性 数据有效性，用到最多的是制作下拉菜单，其次是限制单元格输入的数据大小、类型等。你以为掌握这些就是它的全部吗?NO!!今天本文通过5个示例让你认识一个全新的excel数据有效性。 1、借贷方只能一列填数据。 【例1】如下图所示的AB两列中，要求只能在A或B列中的一列输入数据，如果一列中已输入，另一列再输入会弹出错误提示，中止输入。 操作步骤： 选取AB列的区域，数据菜单- 数据有效性，在有效性窗口中，允许：自定义;公式中输入=COUNTA($A2:$B2)=1

公式说明：counta函数可以统一个区域有多少个非空单元格，本例中设置的条件是Ab 两列同一行中统计结果只能是一个数字。 2、判断车牌输入是否正确 【例2】如下图所示，要求A列的车牌号必须输入以汉字开头，且总长度为7位。输入错误就禁止输入。 数据有效性公式： =AND(LENB(LEFT(B2))=2,LEN(B2)=7) 注：汉字占用2个字节，数字和字母占用1个。 3、每行输入完成才能输入下一行 【例3】在excel表格的A:D输入时，只有上一行的四列都输入数据，在下一行才能输入，否则就无法输入并提示错误信息，如下图所示。 操作步骤：

选取A2:D100,数据选项卡- 有效性- 允许- 自定义，在来源框中输入以下公式： =COUNTA($A1:$D1)=4 公式说明：counta函数可以统计非空单元格个数。$A1:$D1添加$是把范围固定在A:D 列。 4、库存表中有才能出库 【例4】如下图所示，上表为库存表，要求在下表出库列中设置限制，如果为存表中数量不足，禁止输入。 当出库大于库存时 设置方法 数据有效性公式： =E3<=VLOOKUP(D3,A:B,2,0)

excel综合案例

3.5 综合案例 3.5.1案例分析 本节通过建立一个工资表的Excel电子表格，使读者进一步掌握电子表格的输入技巧，如何查看数据量大的表格，了解数据透视表的使用。 设计要求 本电子表格共有四个工作表，其中，一个工作表为9月工资表，如图3.88所示；一个工作表为9月水电读数；一个工作表为应发工资统计图，如图3.89所示；一个工作表为数据透视表，如图3.90所示。 图3.88 9月工资表 图3.89 应发工资统计图图3.90 数据透视表

3.5.2设计步骤 打开电子表格文件“工资表原始”，执行以下操作。 1．工作表管理 （1）修改工作表的名称 具体要求将sheet1工作表改名为“9月工资表” 操作步骤在“Sheet1”标签上双击，“Sheet1”处于反白状态，输入新的工作表名称：“9月工资表”，按回车键确认。 （2）删除工作表 具体要求删除Sheet2工作表 操作步骤在“Sheet2”工作表标签上单击鼠标右键，选择快捷菜单中的“删除”命令。 （3）复制工作表 具体要求将“水电表原始”工作簿的“9月水电读数”工作表复制到本工作簿文件中。 步骤1 打开“水电表原始”工作簿，切换至“9月水电读数”工作表，选择“编辑”|“移动或复制工作表”命令，打开“移动或复制工作簿”对话框。 步骤2 在“移动或复制工作簿”对话框中，如图3.91所示。在“工作簿”下拉列表中选择“工资表原始”，在“下列选定工作表之前”选择“移至最后”，选中“建立副 本”复选框。 单击“确定”按钮后，在本工作簿的最后，新增了一个“9月水电读数”工作表。 图3.91“移动或复制”工作表对话框图3.92录入数据的快捷菜 单 图3.93 从列表中选择数据 2．编辑“工资表”数据 单击“工资表”标签，切换到“工资表”工作表。 （1）插入行 具体要求在第11行前插入一行：林致远，1974-2-8，长沙，财会部，科级，1430。 操作步骤选中11行（姓名张志峰）的任一单元格作为活动单元格，选择“插入”|“行” 命令，则11行的前面插入了空行。 在空行中输入数据：林致远，1974-2-8，长沙，财会部，科级，1430。 技巧输入数据 在输入分公司、部门、职务等级这几列的数据时，选中单元格后，单击鼠标右键，在快捷菜单中选择“从下拉列表中选择”，如图3.91所示。单元格的下面出现列表，显示出上面的行中曾输入的数据，如图3.92所示。用户可直接从列表中选择需要输入的数据。

Excel 2010数据有效性的妙用实例2则

Excel 2010数据有效性的妙用实例2则2010-01-15 07:46:01 来源:网页教学网 Excel强大的制表功能，给我们的工作带来了方便，但是在表格数据录入过程中难免会出错，一不小心就会录入一些错误的数据，比如重复的身份证号码，超出范围的无效数据等。其实，只要合理设置数据有效性规则，就可以避免错误。下面咱们通过两个实例，体验Excel 2010数据有效性的妙用 实例一：拒绝录入重复数据 身份证号码、工作证编号等个人ID都是唯一的，不允许重复，如果在Excel录入重复的ID，就会给信息管理带来不便，我们可以通过设置Excel 2010的数据有效性，拒绝录入重复数据。 运行Excel 2010，切换到“数据”功能区，选中需要录入数据的列(如：A列)，单击数据有效性按钮，弹出“数据有效性”窗口。 图1 数据有效性窗口 切换到“设置”选项卡，打下“允许”下拉框，选择“自定义”，在“公式”栏中输入“=countif(a:a,a1)=1”（不含双引号，在英文半角状态下输入）。

图2 设置数据有效性条件 切换到“出错警告”选项卡，选择出错出错警告信息的样式，填写标题和错误信息，最后单击“确定”按钮，完成数据有效性设置。 图3 设置出错警告信息 这样，在A列中输入身份证等信息，当输入的信息重复时，Excel立刻弹出错误警告，提示我们输入有误。

图4 弹出错误警告 这时，只要单击“否”，关闭提示消息框，重新输入正确的数据，就可以避免录入重复的数据。 实例二：快速揪出无效数据 用Excel处理数据，有些数据是有范围限制的，比如以百分制记分的考试成绩必须是0—100之间的某个数据，录入此范围之外的数据就是无效数据，如果采用人工审核的方法，要从浩瀚的数据中找到无效数据是件麻烦事，我们可以用Excel 2010的数据有效性，快速揪出表格中的无效数据。 用Excel 2010打开一份需要进行审核的Excel表格，选中需要审核的区域，切换到“数据”功能区，单击数据有效性按钮，弹出数据有效性窗口，切换到“设置”选项卡，打开“允许”下拉框，选择“小数”，打开“数据”下拉框，选择“介于”，最小值设为0，最大值设为100，单击“确定”按钮（如图5）。

excel20XX数据有效性工具灰色怎么办

excel20XX数据有效性工具灰色怎么办 篇一：excel20XX制作下拉选项及颜色的设置 microsoftexcel20XX 制作单元格的下拉选项及颜色的设置 图1首先鼠标选定需要生成下拉选择框的单元格；然后在功能区，找到数据选项并单击，进入数据功能页； 第二步：找到数据有效性按钮并点击，选择数据有效性； 图2：在弹出的数据有效性窗口中，在有效性条件下拉列表中选择序列 图3：在来源的输入框中，将下拉选项的按钮名称键入到框中。如有多个选项则以”，” 分开。 图4：在完成下拉选项框的制作后该单元格就具有了下拉功能。但文字没有底部颜色。需要再添加底部颜色，同样选择要添加颜色的单元格如下图 图5：找到菜单开始-条件格式-新建规则（如已设置过底色可选择管理规则）--选择编 辑格式规则，选择（只为包含以下内容的单元格设置格式）。然后在设置框底部的编辑规则说明选择“单元格值”等于在文本框中输入如“同意”，然后点击预览右侧的“格式”

图6：弹出设置单元格格式的窗口，选择底部颜色并确定。 图7：注：如有多个条件，则要针对每个条件设置不同的颜色。条件格式，管理规则，在条件格式规则管理器 图8：完成多个条件格式的设置。 篇二：找回消失的excel数据有效性下拉箭头 找回消失的excel数据有效性下拉箭头 通过设置excel数据有效性可以在单元格中制作一个下拉列表以供选择不同的数据，但有时由于某种原因，选择设置了数据有效性的单元格后下拉箭头并不出现，而且在该工作表的其他单元格设置数据有效性也不会出现下拉箭头，给工作带来一些不便。例如运行包含下面代码的宏，数据有效性下拉箭头就会消失。 '删除工作表中的形状 ForeachpInworksheets("sheet1").shapes p.Delete next 上述代码的作用是删除工作表“sheet1”中的所有形状，但运行后数据有效性的下拉箭头也一并删除了。 要解决上述问题，通常可以用下面的一些方法： 方法一、检查excel选项设置 某些情况下可能是由于隐藏了工作簿中的所有对象导致数据有效性下拉箭头被隐藏了，这时通过下面的设置取消对象的隐藏。 excel20XX：单击菜单“工具→选项→视图”，在“对象”下选择“全

数据有效性概述与示例

数据有效性概述与示例 什么是数据有效性验证？ Microsoft Excel 数据有效性验证使您可以定义要在单元格中输入的数据类型。例如，您仅可以输入从A到 F 的字母。您可以设置数据有效性验证，以避免用户输入无效的数据，或者允许输入无效数据，但在用户结束输入后进行检查。您还可以提供信息，以定义您期望在单元格中输入的内容，以及帮助用户改正错误的指令。 如果输入的数据不符合您的要求，Excel 将显示一条消息，其中包含您提供的指令。 当您所设计的表单或工作表要被其他人用来输入数据（例如，预算表单或支出报表）时，数据有效性验证尤为有用。 本文介绍了如何设置数据有效性验证，包括可以进行验证的数据类型和可以显示的消息。还提供了一个工作簿，您可以下载该工作簿，以获取您可以在自己的工作表上进行修改和使用的有效性验证的示例。 可以验证的数据类型 Excel 使您可以为单元格指定以下类型的有效数据： 数值指定单元格中的条目必须是整数或小数。您可以设置最小值或最大值，将某个数值或范围排除在外，或者使用公式计算数值是否有效。 日期和时间设置最小值或最大值，将某些日期或时间排除在外，或者使用公式计算日期或时间是否有效。 长度限制单元格中可以输入的字符个数，或者要求至少输入的字符个数。 值列表为单元格创建一个选项列表（例如小、中、大），只允许在单元格中输入这些值。用户单击单元格时，将显示一个下拉箭头，从而使用户可以轻松地在列表中进行选择。 可以显示的消息类型 对于所验证的每个单元格，都可以显示两类不同的消息：一类是用户输入数据之前显示的消息，另一类是用户尝试输入不符合要求的数据时显示的消息。如果用户已打开Office 助手，则助手将显示这些消息。 输入消息一旦用户单击已经过验证的单元格，便会显示此类消息。您可以通过输入消息来提供有关要在单元格中输入的数据类型的指令。 错误消息仅当用户输入无效数据并按下Enter 时，才会显示此类消息。您可以从以下三类错误消息中进行选择： 信息消息此类消息不阻止输入无效数据。除所提供的文本外，它还包含一个消息图标、

【数据挖掘】(第8讲5-1)Excel数据有效性[1]

旗开得胜 Excel -- 数据有效性 程香宙 什么是数据有效性? 数据有效性一个包含帮助你在工作表中输入资料提示信息的工具。它有如下功能: --给用户提供一个选择列表 --限定输入内容的类型或大小 --自定义设置 注意: 数据有效性并非十分安全。它可以通过粘贴在单元格输入其它数据。并且可以通过编辑|清除|清除所有取消它。 创建下拉列表选项 使用数据有效性可以为一个单元格创建一个选择输入内容的下拉列表。列表数据项可以在工作表的行或列中输入。也可以直接在数据有效性对话框中输入。 1。创建列表数据项 a。在一个半单行或单列中输入你想在下拉列表中看到的条目。 2。命名列表范围 如果你在一个工作表中输入了一个有效性列表条目，并且给它定义了名称，你就可以在同一工作簿的其它工作表的数据有效性对话框中引用这个名称。 1。选择列表单元格范围。 2。点击公式编辑栏左边的名称框(Name Box) 3。定义一个名称。如：FruitList。 1

旗开得胜4。按回车键。 注意: 要使定义的名称自动扩展包含新增加的条目，请使用动态范围。 3。应用数据有效性 a。选择你想应用数据有效性的单元格 b。“数据”→“有效性”。 c。点击“允许”框右侧的下拉箭头，在列表中选择“序列” d。在来源对话框中输入一个等号和列表名称。如: =FruitList 1

e。点击确定。 4。使用一个限制列表 你可以直接在来源框中输入用逗号隔开的条目替代列表。例如: Yes,No,Maybe 注意：这个逗号是半角状态的，全角状态时输入的逗号无效。 Excel -- 数据有效性–有效性条件示例 程香宙 整数 设置或排除一定范围内的数值。也可以自定义最小值或最大值。 1。在数据有效性对话框中输入值。或者 2。引用到工作表中的单元格。或者 3。使用公式设置值 1

利用Excel数据有效性实现单元格下拉菜单多种分类选项

利用Excel数据有效性实现单元格下拉菜单多种分类选项 一、准备的基础知识 1、创建多个选项下拉菜单 在EXCEL单元格做下拉列表还有一个更好的方法，因为下拉列表的内容可能有30项甚至于100项以上，如在“数据-有效性-来源”中填写100项是做不到的，我记得最多只可填写30项。 创建30项以上方法(以50项为例)： 在下拉列表中选择的50项内容填在A1-A50，选择“插入-名称-定义”,定义名称可填下拉内容“一级”，定义的引用位置是A1-A50，确定后将一级下拉内容填入“数据-有效性-来源”中或者在“数据-有效性-来源”中填 “=$A$1:$A$50”。 2、选择下拉菜单中的一项，附带多项数值用方 我做的表比较复杂，要实现在一行中输入数据同时它相关的一些数据都要出来，而且要输入的数据量很大。 如：A1是一个下拉列表，我选中AA,同时一行的AA 的型号，价格都出现，而且是每行都是这样，可以实现吗？复杂吗？ 设：原数据表在sheet1表，A列为型号，B--H列为相关数据。新表建在Sheet2表，表格式同SHeet1表。选中Sheet1表的A列型号的区域（设为A2至A30)，定义名称为“型号”。 在Sheet2表的A2单元格，数据→有效性，“允许”选“序列”，“来源”中输

入“=型号”（等于应在英文状态下输入），确定退出。 在B2单元格输入公式： =IF($A2<>0,VLOOKUP($A2,Sheet1!$A$2:$H$30,COLUMN(),0),"") 再将B2单元格横向拉到H2单元格。再将A2至H2单元格向下拉若干行。A列选型号后，后面出现相关数据。 二. 下拉菜单多种分类选项快速批量输入 因工作需要，常常要将企业的单位名称输入到Excel表格中，由于要求每次输入同一个企业的名称要完全一致，我就利用“数据有效性”制作了一个下拉列表来进行输入。但由于有150多个单位名称，下拉列表太长，选择起来非常不方便，于是，我对其进行了改进，实现了“分类列表选择、快速统一输入”之目的。 使用实例界面： 1、建库 启动Excel2000(XP也可），切换到Shift2工作表（其他工作表也可）中，将建筑施工企业名称按其资质等级分别分别输入不同列的单元格中，建立一个企业名称数据库（如图1）。