Excel函数数据有效性例题大全
- 格式:doc
- 大小:867.00 KB
- 文档页数:23
Excel 考试题(一)、学号(数组公式if).xls1. 使用数组公式,对Sheet1计算总分和平均分,将其计算结果保存到表中的“总分”列和“平均分”列当中。
{=C2:C39+D2:D39+E2:E39} {=F2:F39/3}2. 使用RANK函数,对Sheet1中的每个同学排名情况进行统计,并将排名结果保存到表中的“排名”列当中。
RANK(G2,$G$2:$G$39,0)3. 使用逻辑函数判断Sheet1中每个同学的每门功课是否均高于平均分,如果是,保存结果为TRUE,否则,保存结果为FALSE将结果保存在表中的“三科成绩是否均超过平均”列当中。
IF(AND(C2>AVERAGE($G$2:$G$39),D2>AVERAGE($G$2:$G$39),E2>AVERAGE($G$ 2:$G$39)),"TRUE","FALSE")4. 根据Sheet1中的结果,使用统计函数,统计“数学”考试成绩各个分数段的同学人数,将统计结果保存到Sheet2中的相应位置。
DCOUNT(Sheet1!A1:I39,Sheet1!D1,B10:C11)5. 将Sheet1复制到Sheet3中,并对Sheet3进行高级筛选,要求:a. 筛选条件:“语文”>=75,“数学”>=75,“英语”>=75,“总分”>=250;b. 将结果保存在Sheet3中。
6. 根据Sheet1中的结果,在Sheet4中创建一张数据透视表,要求:a. 显示是否三科均超过平均分的学生人数;b. 行区域设置为:“三科成绩是否均超过平均”;c. 计数项为三科成绩是否均超过平均。
(二)、折扣表(采购表)(VLOOKUP函数).xls1. 使用VLOOKUP函数,对Sheet1中的商品单价进行自动填充。
要求:根据“价格表”中的商品单价,利用VLOOKUP函数,将其单价自动填充到采购表中的“单价”列中。
数据的有效性----设置输入条件在不少情况下,设置好输入条件后,能增加数据的有效性,避免非法数据的录入。
如年龄为负数等。
那末有没有办法来避免这种情况呢?有,就是设置数据的有效性。
设置好数据的有效性后,可以避免非法数据的录入。
下面我们还是以实例来说明如何设置数据的有效性。
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 所示。
图 6-4-7从图中可见,输入时多了人性化的提示,而这个提示信息就是我们刚刚设置的内容。
第 6 步:设置出错警告在数据有效性对话框中,点击出错警告页,在出错警告页我们可以设置警告信息。
员工档案表
员工编号员工姓名性别所属部门参加工作时间工作年限备注1宋凯男销售部2008年9月1日2主管
2段峰123财务部2008年6月5日2
3赵莹莹女行政部2008年10月26日1
4高欣女人事部2008年9月1日1主管
5张琳琳男人事部2008年6月5日1★
6苏妍女行政部2008年10月26日1主管
1、在表中的“所属部门”字段下的单元格中设置数据的有效性(序列),若输入错误则提示:对不
2、在参加工作时间字段下的单元格设置数据为介于“2008”与“2009”之间。
3、在性别项中将"男、女"设为数据有效性。
:对不起,您输入了错误的信息!。
物业名称/地址推荐标题间隔面积荔湾区-东风西路嘉和苑二期,三房豪华装修84㎡荔湾区-周门园中园&大房大厅&周边配套完善&60㎡100㎡荔湾区-周门北路(电梯)周门北电梯楼笋租再现&三房二厅&家电全齐荔湾区-富力广场富力广场※高层3房带主套※家电齐110㎡荔湾区-富力广场富力广场小区低层3房精选笋盘78㎡荔湾区-司法大楼龙津西路*司法大厦*家电齐60㎡荔湾区-富力广场窗明几靓‖简洁明了‖家私全新68㎡荔湾区-富力广场富力广场※高层3房带主套※家电齐110㎡荔湾区-富力广场富力广场小区低层3房精选笋盘78㎡荔湾区-司法大楼龙津西路*司法大厦*家电齐60㎡荔湾区-富力广场窗明几靓‖简洁明了‖家私全新68㎡使用数据有效性工具完成以下的操作。
(没有要求操作的项目请不要更改)A.当用户选中“间隔”列的第2行至第12行(含)中的某一行时,在其右侧显示一个下拉列表框箭头,并供“2房1厅”、“3房1厅”和“3房2厅”的选择项供用户选择。
B.当用户选中“租价”列的第2行至第12行(含)中的某一行时,在其右侧显示一个输入信息“介于1000 5000之间的整数”,标题为“请输入租价”,如果输入的值不是介于1000与5000之间的整数,会有出错告,错误信息为“不是介于1000与5000之间的整数”,标题为“请重新输入”。
(选择项必须按题述的顺序列出,A操作的有效性条件为序列,B操作的有效性条件为介于整数之间)C.保存文件。
租价时间12月18日12月18日12月18日12月18日12月18日12月18日12月18日12月18日12月18日12月18日12月18日显示一个下拉列表框箭头,并提显示一个输入信息“介于1000与与5000之间的整数,会有出错警入”。
效性条件为介于整数之间)。
excel设置数据有效性-使用其他工作表的序列
excel设置数据有效性时,当你使用序列,试图引用其他表时,系统会发出错误提示,并且无法选择其他表。
我们可以使用下述两种办法解决:
1、使用公式
如我要在sheet2的单元格中设置有效性,序列的来源为sheet1的A2:A50区域,则在sheet2的有效性设置中选择序列,然后输入公式:
=INDIRECT("sheet1!$A$2:$A$50"),则可达到目的。
INDIRECT函数,请参阅EXCEL帮助文件。
2、使用名称
名称可以理解为自定义变量。
如上述例题,则先要点击“插入”——“名称”——“定义……”,弹出“定义名称”对话框,在里面定义AAA,引用位置为=sheet1!$A$2:$A$50。
然后在sheet2的有效性设置中选择序列,然后输入公式:=AAA。
1,对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7):2,对生产表中大于100的产量进行求和:{=SUM((B2:B11〉100)*B2:B11)}:3,对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}:4,对一车间男性职工的工资求和:{=SUM((B2:B10="一车间”)*(C2:C10=”男”)*D2:D10)}: 5,对姓赵的女职工工资求和:{=SUM((LEFT(A2:A10)=”赵")*(C2:C10="女”)*D2:D10)}6,求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3})):7,求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9)8,求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)}9,求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2),10,用SUM函数计数:{=SUM((B2:B9=”男”)*1)}11,求1累加到100之和:{=SUM(ROW(1:100))}12,多个工作表不同区域求前三名产量和:{=SUM(LARGE(CHOOSE({1,2,3,4,5},A组!B2:B9,B组!B2:B9,C组!B2:B9,D组!B2:B9,E组!B2:B9),ROW(1:3)))}13,计算仓库进库数量之和:=SUMIF(B2:B10,”=进库”,C2:C10)14,计算仓库大额进库数量之和:=SUMIF(B2:B8,"〉1000"):15,对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,”〈="&{1400,1600})*{—1,1})} 16,求前三名和后三名的数据之和:=SUMIF(B2:B10,">”&LARGE(B2:B10,4))+SUMIF(B2:B10,”<"&SMALL(B2:B10,4)),17,对所有车间人员的工资求和:=SUMIF(A2:A10,"?车间",C2)18,对多个车间人员的工资求和:=SUMIF(A2:A10,"??车间*”,C2):19,汇总姓赵、刘、李的业务员提成金额:=SUM(SUMIF(A2:A10,{”赵",”刘”,”李"}&”*",C2:C10))20,汇总鼠标所在列中大于600的数据:=SUMIF(INDIRECT("R2C”&CELL("col”)&”:R8C”&CELL("col”),FALSE),”〉600")21,只汇总60~80分的成绩:=SUMIFS(B2:B10,B2:B10,">=60",B2:B10,”<=80”)22,汇总三年级二班人员迟到次数:=SUMIFS(D2:D10,B2:B10,"三年级",C2:C10,"二班”)23,汇总车间女性人数:=SUMIFS(C2:C11,A2:A11,"*车间”,B2:B11,"女”)24,计算车间男性与女性人员的差:=SUM(SUMIFS(C2:C11,B2:B11,{"女”,”男”},A2:A11,”*车间”)*{—1,1})25,计算参保人数:=SUMPRODUCT((C2:C11="是”)*1)26,求25岁以上男性人数:=SUMPRODUCT((B2:B10=”男")*1,(C2:C10〉25)*1)27,汇总一班人员获奖次数:=SUMPRODUCT((B2:B11="一班”)*C2:C11)28,汇总一车间男性参保人数:=SUMPRODUCT((A2:A10&B2:B10&C2:C10="一车间男是")*1) 29,汇总所有车间人员工资:=SUMPRODUCT(-—NOT(ISERROR(FIND("车间",A2:A10))),C2:C10)30,汇总业务员业绩:=SUMPRODUCT((B2:B11={"江西","广东"})*(C2:C11=”男")*D2:D11) 31,根据直角三角形之勾、股求其弦长:=POWER(SUMSQ(B1,B2),1/2)32,计算A1:A10区域正数的平方和:{=SUMSQ(IF(A1:A10>0,A1:A10))}33,根据二边长判断三角形是否为直角三角形:=CHOOSE((SUMSQ(MAX(B1:B3))=SUMSQ (LARGE(B1:B3,{2,3})))+1,"非直角",”直角")34,计算1到10的自然数的积:=FACT(10)35,计算50到60之间的整数相乘的结果:=FACT(60)/FACT(49)36,计算1到15之间奇数相乘的结果:=FACTDOUBLE(15)37,计算每小时生产产值:=PRODUCT(C2:E2)38,根据三边求普通三角形面积:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B1:B3,{1,2,3})))^0。
Excel常用函数公式大全实用例题及解析在日常的工作中,Excel作为一款功能强大的电子表格软件,常常被广泛应用于数据处理、统计分析等方面。
熟练掌握Excel中的常用函数和公式是提高工作效率的关键之一。
本文将介绍一些Excel中常用的函数和公式,并结合实际例题进行解析,帮助读者更好地理解和运用。
1. SUM函数例题:某公司A部门员工的销售业绩如下: - 员工A:销售额5000元 - 员工B:销售额6000元 - 员工C:销售额7000元请使用SUM函数计算A部门员工的总销售额。
解析:在Excel中,SUM函数的作用是对一组数值进行求和。
在本例中,我们可以使用如下公式来计算A部门员工的总销售额:计算结果为18000元。
2. AVERAGE函数例题:某学生小明在期末考试中的数学成绩如下: - 数学:80分 - 英语:75分 - 物理:85分 - 化学:70分请使用AVERAGE函数计算小明的平均成绩。
解析:AVERAGE函数用于计算一组数值的平均值。
在这个例子中,我们可以通过以下公式来计算小明的平均成绩:计算结果为77.5分。
3. VLOOKUP函数例题:某公司人员花名册如下: | 姓名 | 工号 | 部门 | |——–|——–|——–| | 张三 | 001 | 销售部| | 李四 | 002 | 财务部 | | 王五 | 003 | 技术部 |现在根据工号查找对应员工的部门,请使用VLOOKUP函数完成该任务。
解析:VLOOKUP函数用于在垂直区域中查找某个值,并返回该值所在行的指定列的值。
在这个例子中,我们可以通过以下公式来实现工号和部门的对应关系:这里,“002”是要查找的工号,A2:C4是查找的区域范围,3表示返回部门这一列的值,FALSE表示精确匹配。
运行后将返回“财务部”。
通过掌握以上常用函数和公式,可以更高效地进行数据处理和分析工作。
希朐读者通过实际操作和练习,进一步熟练运用Excel中的相关功能,提升工作效率。
Excel教程:数据有效性应用常见五大案例➤案例1:只能输入不重复的值。
之前我们分享的条件格式大家还记得吗?把表格里重复的值突显出来。
今天我们再分享一招,重复值禁止第二次输入。
搭档函数:条件统计函数Countif步骤:先选中A列,然后点击数据->数据验证(2013版之前的叫作数据有效性),然后在自定义输入框中输入=Countif(A:A,A1)=1,意思是判断A列中A1的个数是否为1,我们只允许出现1个。
当在A列中的数据第二次出现时,会自动弹出对话框进行提醒,这样,我们就不会重复录入数据了。
➤案例2:强制序时录入就是强制按从小到大或从大到小的顺序录入数据。
搭档函数:最大值函数Max要强制序时录入,首先我们就要先判断目前已记录的数据最大值是哪一个,这样录入时才能自动比较大小。
步骤:先选中A列,然后点击数据->数据验证(2013版之前的叫作数据有效性),然后在允许中选择“日期”,然后数据中选择“大于或等于”,输入框中输入=max($A1:A$2)。
然后按确定,这样A列中输入的日期必须大于或等于已有的日期才能通过。
➤案例3:只允许输入数字搭档函数:数字函数ISNumber,检测一个值是否是数值。
步骤:先选中A列,然后点击数据->数据验证(2013版之前的叫作数据有效性),然后在自定义输入框中输入=ISNumber(A1)=true,按确定。
➤案例4:只允许输入某个特征的数据搭档函数:OR或函数,Left左截取函数。
以下案例是说单元格内只允许输入“李”开头或“王”开头的数据,问你怎么办?步骤:先选中A列,然后点击数据->数据验证(2013版之前的叫作数据有效性),然后在自定义输入框中输入=OR(LEFT(A2,1)="李",LEFT(A2,1)="王"),按确定。
➤案例5:身份证号长度验证设置身份证号长度的验证,你觉得要怎么下手比较好?有经验的人会发现我们的身份证号有两个特点:1、身份证位数(是否为15位或18位)•编码:ABCDEFYYYYMMDDXXXR•地址码(ABCDEF):表示编码对象常住户口所在县(市、旗、区)的行政区划代码。
公式在数据有效性中应用单元格不让输入数据,可以在有效性公式输入:=type(a1)<>1例1、限制B15:B21的单元格只能输入数字A列只许输入逻辑值,那么可设为:=NOT(A1)只能数字=isnumber()只能文本=istext()如果限制A列只能输入文字则用数据有效性--公式--=type(a1)=2=count(B5)=type(B5)=1=sign(B5)RADIANS(B5)=FACT(a1)isnumber()用来判断输入的是否为数字如果是数字,返回True,如果返回false则判断为错误信息(不是有效的)例2、限制B31:B39只能输入E31:E36中的数据电信电信移动联通铁通网通卫通例3、限制单元格只能输入以E开头的文本或者是=find("E",B47)=1e2本例中find函数返回查找"E"的位置如果位置为1,公式成立,如果不为1,不成立不成立的就返回false,则被判断为不有效.例4、限制单元格里面只能输入7位的数字2222222len检查输入数据的长度例5、限制单元格不能输入小于今天的日期例6:限制重复值的录入2123例7:保护公式商品价格数量总价栗子 2.738102.6衣服 5.352275.6车8.724208.8棋子25.15125.5花生66.7352334.5瘦肉12.366811.8薯仔 2.82775.6选择区域<<编辑<<定位<<定位条件<<公式<<例8:保证身份证的唯一性和位数=COUNTIF($B$43:$B$53,$B43)=1这个公式保证了没有重复输入=OR(LEN($B43)=15,LEN($B43)=18)保证15位或是18位用and则同时符合这两个条件时才可以输入=AND(countif($B$43:$B$53,$B43)=1,OR(len($B43)=15,len($B43)=18))例9:只允许汉字输入汉字,6汉字,3=len(B65)*2=lenb(B65)例10:创建动态下拉菜单运营商城市费用姓名A1B1C1D1A2B2C2D2A3B3C3D3A4B4C4D4A5B5C5D5A6B6C6D6A7B7C7D7A8B8C8D8一级科目二级科目城市B2城市B2。
什么是数据有效性?数据有效性一个包含帮助你在工作表中输入资料提示信息的工具. 它有如下功能:--给用户提供一个选择列表--限定输入内容的类型或大小--自定义设置Excel –数据有效性–自定义条件示例防止输入重复值防止在工作表一定范围输入重复值. 本例中, 在单元格B3:B10中输入的是员工编号.1. 选择单元格B3:B102. 选择数据|有效性3. 在“允许”下拉框中选择“自定义”4. 在“公式”框中, 使用COUNTIF函数统计B3出现次数, 在$B$3:$B$10范围内. 结果必须是1或0:=COUNTIF($B$3:$B$10,B3)<=1限定总数防止一个范围数据总数超过指定值.本例中, 预算不能超过$3500.预算总额统计的单元格在C3:C7范围内1. 选择单元格C3:C72. 选择数据|有效性3. 在“允许”下拉框中选择“自定义”4. 在“公式”框中, 使用SUM函数统计$C$3:$C$7合计值. 结果必须小于或等于$3500:=SUM($C$3:$C$7)<=350没有前置或后置间隔防止用户在输入文本前面或后面加入空白间隔. TRIM函数移除文本前后空白间隔.1. 选择单元格B22. 选择数据|有效性3. 在“允许”下拉框中选择“自定义”4. 在“公式”框中, 输入:=B2=TRIM(B2)防止输入周末日期防止输入的日期为星期六或星期日. WEEKDAY将输入的日期返回到星期, 并且不允许其值为1 (星期日) 和7 (星期六).1. 选择单元格B22. 选择数据|有效性3. 在“允许”下拉框中选择“自定义”在“公式”框中, 输入: =AND(WEEKDAY(B2)<>1,WEEKDAY(B2)<>7)创建下拉列表选项使用数据有效性可以为一个单元格创建一个选择输入内容的下拉列表. 列表数据项可以在工作表的行或列中输入, 也可以直接在数据有效性对话框中输入.1. 创建列表数据项a. 在一个半单行或单列中输入你想在下拉列表中看到的条目.2.命名列表范围如果你在一个工作表中输入了一个有效性列表条目,并且给它定义了名称,你就可以在同一工作簿的其它工作表的数据有效性对话框中引用这个名称.1. 选择列表单元格范围.2. 点击公式编辑栏左边的名称框(Name Box)3. 定义一个名称,如:FruitList.4. 按回车键.3. 应用数据有效性a.选择你想应用数据有效性的单元格b.“数据”→“有效性”.c.点击“允许”框右侧的下拉箭头,在列表中选择“序列”d. 在来源对话框中输入一个等号和列表名称, 如: =FruitListe. 点击确定.你可以使用定义一个范围和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.命名这个表格, 如:NameLookup6.在允许下拉框中选择序列,在来源框中使用一个公式查找正确的名称. 例如:=INDIRECT(VLOOKUP(A2,NameLookup,2,0))使用动态列表因为INDIRECT函数的作用仅为引用, 并非公式, 前面的方法不能工作于动态列表. 你可以使用下面的方法替代它:1.用上述方法创建第第一个名称范围和下拉列表.2.创建支持的名称列表,并且命名第个范围的第一个单元格, 例如:单元格 B1命名为“水果”且单元格 C1命名为“蔬菜”.3.用每个找到的列表命名列, 例如: B列命名为“水果Col”,C列命名为“蔬菜Col”在允许下拉框中选择序列,在来源框中使用一个公式推算查找范围. 例如, 如果第一个下拉列表在单元格 E2。
巧用Excel数据有效性,输入“入库时间”
小虎在Excel输入数据时,经常要输入当时的日期时间(如下图中的入库时间),一个字一个字敲键盘,太慢了吧,就算用快捷键ctrl+;和ctrl+:,还不是要按两三下键,不小心还会按错。
Today()、now()之类的函数就别想了,它会随着电脑的时间的变化而变化,根本不符合要求。
最好还是设置数据有效性吧,想输错都不能错了。
(这话怎么那么耳熟啊,哦,想起来了,是“就算司机想撞车都撞不上”。
)
第一步,在另一个工作表(如sheet2)的任意单元格(如b2)输入公式=TEXT(NOW(),"e-mm-dd hh:mm:ss") 。
千万注意e 要小写,其实也可以写yyyy。
写成e,就更像高手了。
然后定义名称rksj 为本工作表的$B$2单元格。
第二步,在入库单表中,选中需要输入库时间的所有单元格,点数据-有效性,“允许”下面选“序列”,来源里输入=rksj,“出错警告”选项卡的样式下面选“停止”(默认就是“停止”)。
现在好了,选中单元格,点后面的下拉按钮,就只有一个选项,点一下,就输入了。
就算三想输错,都错不了了。
其实,设置那个名称是因为数据有效性不能跨工作表使用,如果嫌麻烦,就在同一个工作表中的其他单元格(如H2)输入公式,有效性里的来源写=$h$2,就行了。
只不过这个工作表不太美观而已!可以输入完公式后,把H列隐藏啊,显得好看点。
联系方式
姓名:唐绍军身份证号:370727************ 通信地址:山东省高密市地方税务局(261500)QQ号:272619898。
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就可以了。
第四步:设置评语的“有效性”选中B11单元格,即“评语”左边的空白单元格,选择菜单栏中的“数据”-“有效性”命令,弹出“数据有效性”对话框,选择“设置”选项卡,设置“允许”条件为“序列”,在“来源”的内容框中输入你对学生的评语,注意在每个评语后面用半角逗号(,)搁开,如:“该生在校能够尊敬师长,团结同学,努力学习。
一级一级二级三级一级A一级D二级D2三级D26一级B一级C一级D一级A一级B一级C一级D二级A1二级B1二级C1二级D1二级A2二级B2二级C2二级D2二级A3二级B3二级C3二级D3二级A4二级B4二级C4二级D4二级A5二级B5二级C5二级A6二级C6二级A7二级C7二级A8二级C8二级A1二级A2二级A3二级A4二级A5二级A6二级A7二级A8三级A11三级A21三级A31三级A41三级A51三级A61三级A71三级A81三级A12三级A22三级A32三级A42三级A52三级A62三级A72三级A82三级A13三级A23三级A33三级A43三级A53三级A63三级A73三级A83三级A14三级A24三级A34三级A44三级A54三级A64三级A74三级A84三级A15三级A25三级A35三级A45三级A55三级A65三级A75三级A85三级A26三级A36三级A46三级A56三级A66三级A76三级A86三级A27三级A57三级A77三级A28三级A58三级A78三级A59三级A79二级B1二级B2二级B3二级B4二级B5二级C1二级C2二级C3三级B11三级B21三级B31三级B41三级B51三级C11三级C21三级C31三级B12三级B22三级B32三级B42三级B52三级C12三级C22三级C32三级B13三级B23三级B33三级B43三级B53三级C13三级C23三级C33三级B14三级B34三级B54三级C14三级C24三级C34三级B15三级B35三级B55三级C15三级C25三级C35三级B16三级B36三级B56三级C26三级C36三级C37三级C38三级C39二级C4二级C5二级C6二级C7二级C8二级D1二级D2二级D3三级C41三级C51三级C61三级C71三级C81三级D11三级D21三级D31三级C42三级C52三级C62三级C72三级C82三级D12三级D22三级D32三级C43三级C53三级C63三级C73三级C83三级D13三级D23三级D33三级C44三级C54三级C64三级C74三级C84三级D14三级D24三级D313三级C55三级C65三级C75三级C85三级D15三级D25三级C56三级C66三级C86三级D16三级D26三级C57三级C67三级C87三级D27三级C58三级C88三级D28三级C59三级C89二级D4三级D41三级D42三级D43。
姓名性别出生日期学历身份证号码
〖数据有效性〗练习
-——用于限制输入文字,防止输入错误。
步骤:选中区域:数据菜单有效性设置:允许:还可设置:警告语句。
1.【姓名】列:请限制姓名输入的最大长度为4位。
设置标签:允许文本长度小于等于 4 ,再设出错警告:小心哦,姓名最长只有数。
2.【性别】列:性别只能是“男”或“女”
设置标签:允许序列再输入“来源”:男,女,再设出错警告:小心哦,性别只是“男”或“女”。
3.【出生日期】列:公司招人要求年龄20岁以上(含20),即出生日期为:
出生的人。
设置标签:日期小于等于输入“来源”限制日期:再设出错警告:小心哦,年龄超范围。
4.【学历】列:学历可以:硕士/本科/大专/高中/中专/初中/小学
设置标签:允许序列再“来源”直接选择表格右边的学历区域,再设出错警告:小哦,学历超出范围。
学历硕士大专高中中专
本科
小学
初中
数据有效性:
用于限制一列可以输入的数值。
步骤:
1.选中区域→数据→有效性:
2.设置 标签: 设允许值:
●常用的有:序列/数值/文本长度/日期…… 3.设置警告语句: → 确定 ●序列是指一列值,常用于文字:如“男,女”,
“学历:本科、大专”操作笔记
设置:只有4位性别只能 以前年龄超出告:小心。
excel函数公式练习题Excel是一款功能强大的电子表格软件,广泛应用于各行各业。
掌握Excel函数公式的使用是提高工作效率和数据分析能力的关键。
本文将为大家提供一些Excel函数公式练习题,帮助大家熟悉函数公式的运用。
练习题一:求和函数请使用Excel的求和函数,计算以下数列的和:1、2、3、4、5、6答案:使用SUM函数,选择数列范围A1:A6,得出结果21。
练习题二:平均值函数请使用Excel的平均值函数,计算以下数列的平均值:10、15、20、25、30答案:使用AVERAGE函数,选择数列范围A1:A5,得出结果20。
练习题三:最大值函数请使用Excel的最大值函数,找出以下数列中的最大值:18、12、25、20、15答案:使用MAX函数,选择数列范围A1:A5,得出结果25。
练习题四:最小值函数请使用Excel的最小值函数,找出以下数列中的最小值:22、14、8、12、17答案:使用MIN函数,选择数列范围A1:A5,得出结果8。
练习题五:计数函数请使用Excel的计数函数,统计以下数列中出现的数字个数:5、2、5、9、7、5答案:使用COUNT函数,选择数列范围A1:A6,得出结果6。
练习题六:求百分比请使用Excel的百分比函数,计算以下数列中每个数字占总数的百分比:12、8、10、15、25答案:使用DIVIDE函数,选择每个数字与总数的直接相除,然后选择将结果设置为百分比格式,得出结果为:12/70 = 17.14%8/70 = 11.43%10/70 = 14.29%15/70 = 21.43%25/70 = 35.71%练习题七:日期函数请使用Excel的日期函数,计算以下日期之间的天数差:起始日期:2021年1月1日结束日期:2021年12月31日答案:使用DATEDIF函数,选择起始日期和结束日期,计算结果为365天。
练习题八:文本函数请使用Excel的文本函数,将以下英文句子进行大写转换:"hello world!"答案:使用UPPER函数,选择句子范围A1,得出结果"HELLO WORLD!"。
Excel数据有效性
Excel数据有效性-先生,你的工号好像输入有误?
燃爆数据猿⋅ 1天前
对Excel控制输入数值的条件有效性
可以说时职场人员必备的小技巧了。
当你在统计身份证号或者工号这种唯一性的信息时,如果有人重复输入,却没有发现;
是不是会造成很大的误解?
简单的数据有效性设置,完全规避这一问题。
当我们要统计如上图工号信息;
要学会设置数据有效性,防止工号重复输入;
选中单元格,找到数据有效性设置;
=COUNTIF($B$2:$B$10,B2)=1
我们分析一下:
公式的意思时用 $B$2:$B$10 区域内单元格和当前单元格比较;
数据与当前单元格相同的有几个,有一个则满足条件;否则为不满足情况;
如图当输入了相同的数值,会弹出报错;
当然我们同样能更改报错的信息;
每天进步一点点,这里是燃爆数据猿。
谢谢。
《Excel高级应用》范例题目描述案例1的题目描述一、EXCEL高级应用的函数1、使用数组公式,对Sheet1中的“总分”列和“平均分”列计算。
计算方法:总分=语文+数学+体育;平均分=总分/3,计算结果保留两位小数。
2、使用IF函数,根据以下条件,对Sheet1 的“考评”数据列计算。
条件:如果总分>=210,填充为“合格”;否则,填充为“不合格”。
3、使用RANK函数,根据总分对Sheet1中的每个同学排名情况进行统计,并将排名结果保存到表中的“排名”列当中。
4、根据Sheet1中的结果,使用统计函数countif,统计“数学”考试成绩各个分数段的同学人数、平均分;使用统计函数计算最高分和最低分,将统计结果保存到Sheet1中的相应位置。
5、将语文、数学和体育成绩在60分以下的数据设置为红色加粗显示。
6、工作表sheet2中的数据来源于某公司对各个部门员工吸烟情况进行统计,作为人力资源搭配的一个数据依据.对于调查对象,只能回答Y(吸烟)或者N(不吸烟)。
请使用函数,统计符合以下条件的数值。
a.统计未登记的部门个数;填入到B14单元格b.统计在登记的部门中,吸烟的部门个数;将结果填入到B15单元格7、使用数组公式,根据Sheet3中的数据,计算考试总分,并将结果填入到“(总分100)”列中。
计算方法:总分=单选题+判断题+windows操作题+EXCEL操作题+PowerPoint操作题+IE 操作题8、利用统计函数(countif函数和sumif函数),根据以下要求对Sheet3中的数据进行计算:要求:a.统计“考1级的考生人数”,并将计算结果填入到N2单元格中;b.统计“考试通过人数(> = 60 ) ”并将计算结果填入到N3单元格中;c.统计,“1级考生考试的平均分”,并将计算结果填入到N4 单元格中。
(其中,计算时候的分母直接使用“N2”,计算结果保留两位小数)二、高级筛选将Sheet1中的数据清单复制到Sheet4当中,并对Sheet4 进行高级筛选,要求:a.筛选条件为:“性别”—男、“数学”—>85、“体育”—>85;b.将筛选结果保存在Sheet4 中。
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就可以了。
第四步:设置评语的“有效性”选中B11单元格,即“评语”左边的空白单元格,选择菜单栏中的“数据”-“有效性”命令,弹出“数据有效性”对话框,选择“设置”选项卡,设置“允许”条件为“序列”,在“来源”的内容框中输入你对学生的评语,注意在每个评语后面用半角逗号(,)搁开,如:“该生在校能够尊敬师长,团结同学,努力学习。
,该生平时热爱劳动,刻苦学习,能够帮助同学。
”(不带引号)。
如图5所示。
单击“确定”命令。
第五步:打印通知书在A1单元格中输入一个序号后按回车健,在表“考试成绩”中与之相同序号的这一行的学生信息就会自动提取在表“通知书”的相应的各单元格中。
然后根据这个学生的平时情况,在“评语”栏中单击下列箭头按钮选择合适的评语就可以了。
点击“打印”命令,一张完美的学生通知书就会呈现在眼前。
如图6所示。
由于在前面对A1单元格的字体已设置为白色,与背景色一致,因此在打印时不至于显出来而影响通知书的美观。
[应用一]下拉菜单输入的实现例1:直接自定义序列有时候我们在各列各行中都输入同样的几个值,比如说,输入学生的等级时我们只输入四个值:优秀,良好,合格,不合格。
我们希望Excel2 000单元格能够象下拉框一样,让输入者在下拉菜单中选择就可以实现输入。
操作步骤:先选择要实现效果的行或列;再点击"数据\有效性",打开"数据有效性"对话框;选择"设置"选项卡,在"允许"下拉菜单中选择"序列";在"数据来源"中输入"优秀,良好,合格,不合格"(注意要用英文输入状态下的逗号分隔!);选上"忽略空值"和"提供下拉菜单"两个复选框。
点击"输入信息"选项卡,选上"选定单元格显示输入信息",在"输入信息"中输入"请在这里选择"。
例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至B1 4列),执行“数据→有效性”命令,打开“数据有效性”对话框,在“设置”标签下,按“允许”右侧的下拉按钮,在随后弹出的快捷菜单中,选择“自定义”选项,然后在下面“公式”方框中输入公式:=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 的“数据有效性”,快速揪出表格中的无效数据。