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

1、*Excel函数与数据有效性配合快速填通知书用Excel函数中的vlookup查询函数和数据有效性功能配合来填写通知书,可以免去老师们一个一个写的繁琐劳动，这下不用写到手抽筋了！第一步：处理学生成绩把学生的期末考试成绩放在Sheetl表中，算出每个学生的成绩总分，为了在后面输函数公式时方便，我在前面加了一列序号"。把Sheetl表重命名为 考试成绩如图1所示。A.藩CDEFGOilI1f期末考试成绩2序号姓名语文数学外语物理初学生物总分31张110811788988259142张2111132863S7658653张3109127S139的7857864褊11487i:3SB8557

2、675张6124118803491771-7486隹&122134r 8s73827157097张工_115132313491655比1081201127299?可口 yes成二 hd-p119,电107116799737弋告试成绩Sheets/ Sheets /IIUJL第二步：设置通知书”模版在考试成绩”表旁的空白表Sheet2中，设置好 通知书”的基本格式和文字内容，页面设置为 B5纸，底色可以设置为默认。如图 2所示。A & C D 1 E F G通知书. I2 .3 一贾家归：,. I ,.H 般们好.根据教育局规定，我校于6月羽日开始放假,时间为8月29日 )至g月

3、3日中假期期间，里各位家长看护好孩子的安全，同时还要密切配合 卷好学校.督促募子技时定应限期作也ri sail同学的成演版一I23 到 2?评 语S长答空*.目匚琏物右击表 通知书”的A1单元格，选择 设置单元格格式”命令，弹出 单元格格式”对话框，选择 字体”选项卡，把字体颜色设置为白色“，确定” 即可。如图3所示。它的作用在后面就会体现出来。设置好后把此表表名重命名为通知书”。第三步：插入查询函数”在通知书”表的C3单元格输入函数 “=Vlookup（A1,考试成绩！A3 : J43, 2, FALSE）”,如图 4 所示。23212224第三言统申芈5TJMX 2 * ElboJnjpl

4、,考试度缴1A3: J4工 Z fal*)B i C ) '广 q f通知书贵家长您们好，根据教育局规定. 我校于6月28日开始放假，时间为6月29日 至9月3日-假期期间.望各位家长看乐好我子的妄全.同时还要密切配合 好学校，督促技子按时完成假期作业.S二 3班 |=vio心upGl,考试A3: J4X 2, ialse)|9语文数学英语物理化学）生物总分in111213P_*15 16_ ：叵20此公式的含义是：使用Vlookup查询函数，根据 A1单元格的内容，在考试成绩”表白A A3到J43单元格中进行查询，把查询到相同内容的这行 的第2个单元格的内容显示在 C3单元格中。即根

5、据A1单元格的内容，把 考试成绩表中与之相同内容的这行的第2个单元格的姓名提取到此单元格。由此在A10单元格中输入函数“=Vlookup（A1,考试成绩！A3 : J43, 3,FALSE）”，理解了 C3、A10单元格的函数后，根据同样的原理我们分别如 法设置 B10、C10、D10、E10、F10、G10 就可以了。第四步：设置评语的 有效性选中B11单元格，即 评语”左边的空白单元格，选择菜单栏中的 数据'-有效性”命令，弹出 数据有效性”对话框，选择 设置”选项卡，设置 允许' 条件为 序列“，在 来源”的内容框中输入你对学生的评语，注意在每个评 语后面用半角逗号（,）

6、搁开，如：该生在校能够尊敬师长，团结同学，努力 学习。，该生平时热爱劳动，刻苦学习，能够帮助同学。”不带引号）。如图5所示。单击确定”命令。第五步：打印通知书在A1单元格中输入一个序号后按回车健,在表 考试成绩”中与之相同序号的这一行的学生信息就会自动提取在表通知书”的相应的各单元格中。然后根据这个学生的平时情况，在 评语栏中单击下列箭头按钮选择合适的评语就可以了。点击打印”命令，一张完美的学生通知书就会呈现在眼前。如图 6所示。通知书敬堂夷唐北争生物宜分117$858985乳建厚生在栈柳叁蜃丽盘.四结网季.罂当季q._* * .a _ J同学的成单单悠们皙.嘏帼教育孰在，我枝凫月盘日月始旅科

7、,时间怎月加日 至q月之日-胃舒解间，里芯枚家K务融耙核4的史金.同时江雯窘初配合 好事按，爵促候干挨对完成药期柞业.二、3旺 和1第喘瞿!嘉心”由于在前面对 A1单元格的字体已设置为白色，与背景色一致，因此在打印时不至于显出来而影响通知书的美观。应用一下拉菜单输入的实现例1 :直接自定义序列有时候我们在各列各行中都输入同样的几个值，比如说，输入学生的等级时我们只输入四个值：优秀，良好，合格，不合格。我们希望Excel2000 单元格能够象下拉框一样，让输入者在下拉菜单中选择就可以实现输入。操作步骤：先选择要实现效果的行或列；再点击"数据 有效性 " ，打开" 数

8、据有效性"对话框；选择 "设置"选项卡，在 "允许"下拉菜单中选择"序列 " ；在 "数据来源"中输入"优秀，良好，合格，不合格 " （注意要用英文输入状态下的逗号分隔！）；选上"忽略空值"和"提供下拉菜单"两个复选框。点击"输入信息"选项卡，选上 "选定单元格显示输入信息" ， 在"输入信息 "中输入 " 请在这里选择 " 。例 2 ：利用表内数据作为序列源。有时候

9、序列值较多， 直接在表内打印区域外把序列定义好， 然后引用。操作步骤：先在同一工作表内的打印区域外要定义序列填好（假设在在Z1 ： Z8 ），如“单亲家庭，残疾家庭，残疾学生，特困，低收人，突发事件，孤儿，军烈属 ”等，然后选择要实现效果的列（资助原因）；再点击"数据 有效性 " ，打开 "数据有效性 "对话框；选择"设置"选项卡，在"允许"下拉菜单中选择 "序列 " ； “来源 ”栏点击右侧的展开按钮（有一个红箭头），用鼠标拖动滚动条，选中序列区域 Z1 ： Z8 （如果记得，可以直接输入=$

10、Z$1:$Z$8 ；选上 "忽略空值 "和"提供下拉菜单 "两个复选框。点击"输入信息 "选项卡，选上"选定单元格显示输入信息" ，在"输入信息" 中输入 "请在这里选择 " 。例 3 ：横跨两个工作表来制作下拉菜单用 INDIRECT 函数实现跨工作表在例 2 中， 选择来源一步把输入 =$Z$1:$Z$8 换成 =INDIRECT（" 表二 !$Z$1:$Z$8"）, 就可实现横跨两个工作表来制作下拉菜单。应用二 自动实现输入法中英文转换有时，我们在不

11、同行或不同列之间要分别输入中文和英文。我们希望Excel 能自动实现输入法在中英文间转换。操作步骤：假设我们在 A 列输入学生的中文名， B 列输入学生的英文名。先选定 B 列，点击进入"数据有效性 " ，打开"数据有效性"对话框；选择 " 输入法 " 对话框，在 " 模式 " 下拉菜单中选择" 关闭（英文模式） " ；然后再" 确定 " ，看看怎么样。应用三 数据唯一性检验员工的身份证号码应该是唯一的，为了防止重复输入，我们用“数据有效性”来提示大家。操作步骤：选中需要建立

12、输入身份证号码的单元格区域(如 B2 至 B14歹U),执行 数据-有效性”命令，打开 数据有效性”对话框，在 设置”标签下，按 “允许 ”右侧的下拉按钮，在随后弹出的快捷菜单中，选择 “自定义 ”选项，然后在下面 “公式 ”方框中输入公式： =COUNTIF(B:B,B2)=1 ，确定返回。以后在上述单元格中输入了重复的身份证号码时，系统会弹出提示对话框，并拒绝接受输入的号码。通过 Excel 数据有效性防止重复输入数据在 Excel 中录入数据时，有时会要求某列或某个区域的单元格数据具有唯一性，如身份证号码、发票号码之类的数据。但我们在输入时有时会出错致使数据相同，而又难以发现，这时可以通

13、过 “数据有效性 ”来防止重复输入。例如我们要在B2:B200来输入身份证号，我们可以先选定单元 格区域B2:B200,然后单击菜单栏中的“数据”一“有效性 ”命令，打开“数据有效性 ”对话框，在 “设置 ”选项下，单击 “允许 ”右侧的下拉按钮，在弹出的下拉菜单中，选 择 “自定义”选项，然后在下面“公式”文本框中输入公 式"二COUNTIF（$B$2:$B$200,$B2）=1 "（不包括引号），选“确 定”后返回（如图1） o以后再在这一单元格区域输入重复的号码时就会弹出提示对话框了（如图2）。Excel中的数据有效性在Excel中，我们可以约束某个栏位只能输入某些值

14、，这些值可以是固定的序列，也 可以是某些单元格。下面我们来看看这两种方式如何设置 （以下截图是在 Excle2007中，Excel2003类似）：1、固定的序列【步骤11通过菜单【数据】->【数据有效性】->【数据有效性】进入【数据有效性】面板:Rook：- Microsoft fxcelE_ j H I 1 J i【步骤2】【允许】选择【序列】，然后在来源中输入固定值“ New,Update,Delete以英文逗号隔开：【步骤3】效果如下:2、来源为某些单元格：【步骤u同方式i【步骤2】将鼠标点中来源，然后圈选 A1A3:【步骤3】效果同方式。注意：第一种方式不灵活，但是可以拷贝

15、到其他的Excel中直接使用；第二种方式由于引用了Excel中的单元格，不能拷贝到其他Excel中，也不难拷贝到同一个Excel文档的其他Sheet中，只能在同一个 Sheet中使用.在excel2003中定义有效性标准要定义允许输人到单元格或者范围中数据的类型，步骤如下：选择单元格或者范围，选择菜单栏的“数据”一一“有效性”，Excel显示“数据有效性”对话框 单击“数据有效性”对话框中的“设置”选项卡，从“允许”下拉框中选择个选项， 要定义公式，选择“自定义”。从“数据”下拉框中选择定义条件。所做的选择决定 可以访问的其他控制，如下图所示：数据有效件设量物入信息出错警告输入法模式有旗性条伴

16、允许的:数数列期间本定 些小序日r交目司忽喈空值5)对有同样设置的所有其触单元格厘用这些更改当部清除电)确定 取消I单击“输入消息”选项卡，并且定义当用户选择了该单元格，要显示哪个信息。可以 使用这个选项逐步告诉用户需要什么样的数据类型，如图所示单击“出错警告”选项卡，并且定义当用户进行了无效的输入，要显示哪个错误信息 风格的选择确定当输入了无效项时用户选择什么。要防止无效输入，选择停止。这一 步是可选的。单击“确定”按钮关闭“数据有效性”对话框。执行了这些步骤之后，单元格或者范 围就包吉了所定义的有效性标准。EXCEL关于数据有效性的应用使单元格区域内记录不能重复输入控制=COUNTIF(A

17、: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(

18、A:A) 同 <>"" 同 =""限定区域输入的和的最大值=SUM(A1:A10)<100有效性应用实例一： 我的 ID 你别用教师经常要用 Excel 制作表格， 录入学生信息， Excel 强大的制表功能， 给教师工作带 来了方便，但是在表格数据录入过程中难免会出错，一不小心就会录入一些错误的数据，比如重复的身份证号码，超出范围的无效数据等。其实，只要合理设置“数据有效性”规则，就可以避免错误。为了便于管理学生信息，每个学生都有属于自己独有的ID （学号），在信息录入时，学生ID不允许重复，如果在Excel录入重复的ID,就会给信息

19、管理带来不便，我们可以对Excel "数据有效性”进行设置，拒绝录入重复数据。fW的SI粕牌余式切换到“数据功UD的列（如A列）.按钮.在F拉菜单IT含井法爸噌受台，N打脚，了曲Iki，.清坤g灯阪军一i设置 输入守：出错警肯_输入法模式有效性条件 一|打下G允许”下拉框,选择 允I4:恒定声一"自定义”.在“公式”一栏中输入 =countif（a:a?a 1）=1''蜃式3）7rI 1* _»! - I I .一 I一一 » a-= coujit； £ （a,二、al ）= l|E*；对营同祥设置的鲂有复地单元格应用这些更改1

20、）确定取消0在A列中输入ID.当输入的ID出现重复 时,Excel立刻弹出错误警告.提示我们输 人有误.单击“否” .关闭提示框,重新 输入正确的ID,避免数据重复有效性应用实例二：快速揪出无效数据有些数据是有范围限制的， 比如以百分制记分的考试成绩必须是 0100之间的某个数据，录入此范围之外的数据就是无效数据，如果采用人工审核的方法，要从浩瀚的数据中找到无效数据是件麻烦事，我们可以用Excel的“数据有效性”，快速揪出表格中的无效数据。全部清解应）能石同样设置2打开“允许”下拉相.选择 , “小数”,打开“数据”下拉 框.选择“介于”.最小值设 为0,最大值设为100,单击“确定”按钮屋小

21、值圆）_0-最*值QP|ioqL_t允许也白费敷据电）：一汴十 K豆石 ，£一【I”I打开需要进行审核的Excel表格,选中需 要审核的区域.切换到数据”功能区. 单击“数据有效性” 一 “数据有效性”胃逑航i峭人页面布漏公式805'895800199199的椭圆形圈释出来.错误数 据一目了然单击“数据有效性一数据星会司女性有效性"，稍等片刻，表格 中所有无效数据被一个红色:国辉无皿教病清除无效数1S乐识因B）现在学生的身份证号已经全部都是18位的新一代身份证了，里面的数字都是有规律的。前6位数字是户籍所在地的代码，714位就是出生日期。第17位“2代表的是性别，偶

22、数为女性，奇数为男性。我们要做的就是把其中的部分数字想法提取出来STEpI ,转换身份证号码格式我们先将学生的身份证号完整地输入到Excel2003表格中，这时默认为数字”格式(单元格内显示的是科学记数法的格式)，需要更改一下数字格式。选中该列中的所有身份证号后，右击鼠标，选择 设置单元格格式在弹出对话框中 数字”标签内的 分类” 设为文本”，然后点击确定。STEP2 ,提取出”出生日期将光标指针放到 出生日期”列的单元格内，这里以 C2单元格为例。然后输入 “=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&a

23、mp;MID(B2,13,2)&"曰""(注意：外侧的双引号不用输入，函数式中的引号和逗号等符号应在英文状态下输入)。回车后，你会发现在C2单元格内已经出现了该学生的出生日期。然后，选中该单元格后拖动填充柄，其它单元格内就会出现相应的出生日期。如图 1。:起wMiMTlDC则筛 加0® *A(Xi福武如 TAG 徽变电)-口 如 帚勖咐*vrev出生日朋加8831g痢加二MIDG21 7, 白北"年"&11D(62-1I, 2)修"月"fellD电7,1& 幻也图1通过上述方法，系统自动获取

24、了出生年月日信息小提示：MID函数是EXCEL提供的一个 从字符串中提取部分字符”的函数命令，具体 使用格式在EXCEL中输入MID后会出现提示。STEP3 ,判断性别男女选中 性别'列的单元格，如 D2o 输入 “=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")"(注意如上)后回车，该生是男还是女”已经乖乖地判断出来了。拖动填充柄让其他学生的性别也自动输入。如图2r1000 HecnfIfflSU CD 特式叫 I* KJB ® ，«)用初制匕二试 ;，；1r I

25、I 一 二 QWJ .L.乐"11cg,cmWz皿q孩in笈.j男W期网合工01 flk名於翅TT微 姓氏军-海魅1宜5七票37 口银前 娥赚< -I_»G H I出生日期，性别 .L的年10月163 mHOHD醉7J“HTR1"除1小“却"#； ”男”)上外侔L?月11日 一| E%*匕口工M* ganjF.trxL 131旺,#711阴|1烈佯如身疑日1转辟08国09日图2性别被自动填入指定位置这样，通过两个简单的函数，我们就可以让EXCEL从身份证号中自动提取出生日期和性别并填充到单元格内，极大地减轻了我们的输入工作量。最后效果如图3。:唠发

26、悻也产M编辑 视图也 插入Q)格式工具9 数据 窗口阻二 . J一 . 1 - -; 0 -IB18， 启| 明名着超压船姓张李王超身份证号951211995Q32*出生日期1995年12月U日 1995年03月26日1995年08月09日图3用EXECL可以自动识别并填入学生信息如何不启用宏就不能使用Excel，不启用宏就不能用,应该怎么达到这种效果？用VBA做了个表个给其他部门用，但头一次他们使用时都要将 安全级别设置成低时才能正常使用，有的干脆不启用宏，填完数据就交上来，那些辛苦做的VBA功能根本没用上。有什么办法，让他们不启用宏就不能使用EXCEL简单的办法就是：Private Sub Workbook_BeforeClose(Cancel As Boolean)Sheetl.Visible = xlSheetHiddenEnd SubPrivate Sub Workbook_Open()Sheetl.Visible = xlSheetVisibleEnd Sub意思就是sheetl表默认是隐藏的，只要启用宏，那么 sheetl表才会自动取消隐藏。复杂一点的办法就是用老的宏表函数，也可以