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

Excel 函数与数据有效性配合快速填通知书 用 Excel 函数中的 vlookup 查询函数和数据有效性功能配合来填写通知书 ，可以免去老师们一个一个写的繁琐劳动，这下不用写到手抽筋了！ 第一步：处理学生成绩 把学生的期末考试成绩放在 Sheet1 表中，算出每个学生的成绩总分 ，为了在后面输函数公式时方便，我在前面加了一列“序号”。把 Sheet1 表重命名为“考试成绩” 。如图 1 所示。 第二步：设置“通知书” 模版 在“考试成绩” 表旁的空白表 Sheet2 中，设置好“通知书 ”的基本格式和 文字内容，页面设置为 B5 纸，底色可以设置为默认。如图 2 所示。 右击表“通知书” 的 A1 单元格，选择“设置单元格格式”命令，弹出“单 元格格式”对话框，选择“ 字体” 选项卡，把字体颜色设置为 “白色” ，“确定 ”即可。如图 3 所示。 它的作用在后面就会体现出来。设置好后把此表表名重命名为“通知 书”。 第三步：插入“查询函数” 在“通知书” 表的 C3 单元格输入函数“=Vlookup(A1，考试成绩!A3 ：J 43，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：直接自定义序列 有时候我们在各列各行中都输入同样的几个值，比如说，输入学生的 等级时我们只输入四个值：优秀，良好，合格，不合格。我们希望 Excel 2000 单元格能够象下拉框一样，让输入者在下拉菜单中选择就可以实现 输入。 操作步骤：先选择要实现效果的行或列；再点击“数据有效性“，打 开“ 数据有效性 “对话框；选择 “设置“选项卡，在“允许“ 下拉菜单中选择“序 列“ ；在 “数据来源“中输入“ 优秀，良好，合格，不合格“（注意要用英文 输入状态下的逗号分隔！）；选上“忽略空值“ 和“提供下拉菜单“两个复选 框。点击“输入信息 “选项卡，选上 “选定单元格显示输入信息“，在“ 输入 信息“ 中输入“请在这里选择“。 例 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 至 B 14 列），执行“ 数据有效性”命令，打开“ 数据有效性”对话框，在“ 设置” 标签下，按“允许 ”右侧的下拉按钮，在随后弹出的快捷菜单中，选择“自 定义”选项，然后在下面“ 公式 ”方框中输入公式：=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】将鼠标点中来源，然后圈选 A1A3： 【步骤 3】效果同方式。 注意：第一种方式不灵活，但是可以拷贝到其他的 Excel 中直接使用； 第二种方式由于引用了 Excel 中的单元格，不能拷贝到其他 Excel 中，也不难拷贝到 同一个 Excel 文档的其他 Sheet 中，只能在同一个 Sheet 中使用. 在 excel2003 中定义有效性标准 要定义允许输人到单元格或者范围中数据的类型，步骤如下： 选择单元格或者范围，选择菜单栏的“数据”“有效性”，Excel 显示“数据有 效性”对话框。 单击“数据有效性”对话框中的“设置”选项卡，从“允许”下拉框中选择个选项， 要定义公式，选择“自定义”。从“数据”下拉框中选择定义条件。所做的选择决定 可以访问的其他控制，如下图所示： 单击“输入消息”选项卡，并且定义当用户选择了该单元格，要显示哪个信息。可以 使用这个选项逐步告诉用户需要什么样的数据类型，如图所示 单击“出错警告”选项卡，并且定义当用户进行了无效的输入，要显示哪个错误信息。 风格的选择确定当输入了无效项时用户选择什么。要防止无效输入，选择停止。这一 步是可选的。 单击“确定”按钮关闭“数据有效性”对话框。执行了这些步骤之后，单元格或者范 围就包吉了所定义的有效性标准。 EXCEL 关于数据有效性的应用 使单元格区域内记录不能重复输入控制 =COUNTIF(A:A,A2)=1 (直接复制此公式进去即可) 禁止单元格输入数字控制 =ISNUMBER(A1)1,WEEKDAY(A1)“ 同=“ 限定区域输入的和的最大值 =SUM(A1:A10)100 有效性应用实例一：我的 ID 你别 用 教师经常要用 Excel 制作表格，录入学生信息，Excel 强大的制表功能，给教师工作 带来了方便，但是在表格数据录入过程中难免会出错，一不小心就会录入一些错误的 数据，比如重复的身份证号码，超出范围的无效数据等。其实，只要合理设置“数据 有效性”规则，就可以避免错误。 为了便于管理学生信息，每个学生都有属于自己独有的 ID（学号），在信息录入时， 学生 ID 不允许重复，如果在 Excel 录入重复的 ID，就会给信息管理带来不便，我们 可以对 Excel“数据有效性”进行设置，拒绝录入重复数据。 有效性应用实例二：快速揪出无效 数据 有些数据是有范围限制的，比如以百分制记分的考试成绩必须是 0100 之间的某个 数据，录入此范围之外的数据就是无效数据，如果采用人工审核的方法，要从浩瀚的 数据中找到无效数据是件麻烦事，我们可以用 Excel 的“数据有效性”，快速揪出表 格中的无效数据。 现在学生的身份证号已经全部都是 18 位的新一代身份证了，里面的数字都是有规律 的。前 6 位数字是户籍所在地的代码，7 14 位就是出生日期。第 17 位“2”代表的是 性别，偶数为女性，奇数为男性。我们要做的就是把其中的部分数字想法“提取出来” 。 STEp1，转换身份证号码格式 我们先将学生的身份证号完整地输入到 Excel2003 表格中，这时默认为“数字”格式（ 单元格内显示的是科学记数法的格式），需要更改一下数字格式。选中该列中的所有 身份证号后，右击鼠标，选择“设置单元格格式 ”。在弹出对话框中 “数字”标签内的“ 分 类”设为“文本”，然后点击确定。 STEP2，“提取出”出生日期 将光标指针放到“出生日期”列的单元格内，这里以 C2 单元格为例。然后输入“=MID(B 2,7,4) 2 、在 “工具”菜单上，单击“选项”，再单击“ 编辑”选项卡; 3 、选中 “自动设置小数点”复选框; 4 、在 “位数”框中，输入小数位数，本例中输入“3”; 5 、单击 “确定”按钮，开始输入数据。 编辑提示：“位数”框中可输入正数，也可以输入负数。例如，如果在 “位数”框 中输入“3”，然后在单元格中键入“1”，则其值为“0.001”。如果在“位数”框中输入“-3” ， 然后在单元格中键入“1”，则其值为 “1000”;在选择“自动设置小数点” 选项之前输入的 数字不受小数位数的影响。 方法二：自定义数据格式 1 、选定需要输入数