EXCEL教程-常用函数普及(一)

5.1逻辑值、AND、OR、NOT1.逻辑值：ture，false能产生或返回逻辑值的情况：比较运算符is类信息函数and,or,not2.与（and）,或（or），非（not）and：所有条件为ture，则返回ture，，否则返回falseor：其中之一条件为ture，则返回ture，否则返回falsenot：如果条件参数结果为ture，则返回false，同理结果为false，则返回ture语文数学and解法or解法员工姓名性别98100TRUE满分刘光明男4589FALSE张小五女9099TRUE高进军男10089FALSE满分陈列男10091TRUE满分胡萝卜女9089FALSE周末女1.两科成绩=90，显示为优秀郝并男2.其中一科为100，显示有满分要求：性别=男，且年龄=30岁，补助金额100?(用两种方法)3.作业题要求：判断出=160的学生为优秀？学生姓名总分是否优秀学生姓名总分是否优秀高进军167高进军167优秀刘光明158刘光明158张小五未考效果图张小五未考郝并172郝并172优秀陈列148陈列148周末167周末167优秀胡咯波未考胡咯波未考张联盟182张联盟182优秀真（成立）假（不成立）not：如果条件参数结果为ture，则返回false，同理结果为false，则返回ture年龄补助金额3110010036004510010021001800330030100100要求：性别=男，且年龄=30岁，补助金额100?(用两种方法)5.2逻辑值与0和1的关系及运用ture：正确-成立-是false：错误-不成立-否ture相当于1false相当于0ture和false转换成1和0的各种方法示例1：工资大于2000的有多少个人逻辑值数值公式位置姓名工资TRUE1=--A13--在前肖顺琴1232FALSE0=Q14*1*前后均可莫广林4523TURE1=L15/1/在后李清香1250TURE1=Q15-0-在后秦垂娇1900FALSE0=Q16+0+前后均可黄永梅2510FALSE0=N(A18)置于函数N中谭碧菊1563刘荣梅1600张晨贵2000王冠楠3215作业题要求：求为两科成绩为优良或者良优的人数是多少？语文数学人数良优优差答题要求：不能用辅助列，一条公式完成。优优良优差优优良示例1：工资大于2000的有多少个人标准示例2：两科成绩都为优的人数2000语文数学优差人数人数良优优优差优优良运用知识：数组+逻辑值转换优优5.3AND、OR与*、+的故事现象推定：=AND(TRUE,TRUE,TRUE,TRUE,TRUE,FALSE)=AND(1,1,1,1,1,0)=OR(FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)=OR(0,0,0,0,0,0)总结规律：AND可以用*来代替OR可以用+来代替示例1：语文数学(*)解法(+)解法1.两科成绩大于等于90分以上的显示优?（*）解法981002.两科成绩有一科以上为满分显示满分?（+）解法45899099100898991100100注意：0为false，其他数字均为true作业题：语文数学1.求出两科成绩都同时大于等于90的人数？981002.两科成绩有一科以上为满分显示满分的人数？45899099第1题答题卡区第2题答题卡区100898991答题要求：不能用辅助列，一条公式完成100100=1*1*1*1*1*0=0+0+0+0+0+11.两科成绩大于等于90分以上的显示优?（*）解法2.两科成绩有一科以上为满分显示满分?（+）解法5.4IF函数IF基础学习IF函数的各种简写方式求正数有多少个？等同于=IF(表达式，0，FALSE)2等同于=IF(表达式，成立，0)2等同于=IF(表达式，2，不成立)2结论：如果参数未写，用逗号隔开则看作0如果第三个参数未写，当返回结果时看作FALSEIF函数的嵌套=IF(表达式,TRUE,FALSE)=IF(表达式,IF(表达式,,),IF(表达式,,))示例=5000=3000=20002000员工姓名工资方法1方法2方法3方法4刘光明60981级1级张小五23003级3级高进军12904级4级完整表达简写=IF(表达式,)=IF(表达式,成立，)=IF(表达式,，不成立)TRUE1级陈列78001级1级胡萝卜23403级3级周末45002级2级郝并18004级4级张联盟38002级2级条件工资级别=50001级=30002级=20003级20004级留给大家的后续作业？作业题1：求出语文，数学的总分。如果其中有一科未考则显示未考？学生姓名语文数学总分总分刘光明58100158张小五未考99未考高进军7988167陈列7375效果图148胡萝卜95未考未考周末8780167郝并9082172张联盟未考未考未考作业题2：求公司为某灾区捐款，捐款方法以工资的级别做标准，以相应级别工资对应的百分比*工资所得金额做扣除员工姓名最后工资捐款(扣除)根据工资的捐款比例刘光明1488=40000.02张小五1686=20000.015高进军2255=10000.01陈列98010000胡萝卜1910周末2560郝并4117员工姓名最后工资捐款(扣除)张联盟1283刘光明148814.88张小五168616.86效果图高进军225533.8251级陈列9800胡萝卜191019.1周末256038.4郝并411782.34张联盟128312.83示例求正数有多少个？数字214-74-68TRUE级工资=5000FALSE工资=3000级工资=3000TRUE2级FALSE工资=2000FALSE4级3级TRUETRUEFALSE工资=2000FALSE4级3级TRUE5.5IS类判断函数学习1.IS类信息函数序号函数1ISBLANK2ISERR3ISERROR4ISLOGICAL5ISNA6ISNONTEXT7ISNUMBER8ISREF9ISTEXT示例运用1示例运用2内容提取数字提取文本水果单价金额金额77苹果#VALUE!aa2.369069022梨子#VALUE!cccc4.5135013501212香蕉#VALUE!75752.5750750cscs西红柿#VALUE!23234.914701470例子分析运用2计算所有水果的平均金额（单价或数量未定的不作平均金额计算）水果单价单价数量平均金额苹果2.3234=AVERAGE(IF(ISERR(A33:A37*B33:B37),,A33:A37*B33:B37))梨子未定400香蕉2.5290西红柿4.9未定平均金额荔枝6700=AVERAGE(IF(ISERR(A33:A37*B33:B37),,A33:A37*B33:B37))值为不是文本的任意值（注意此函数在值为空白单元格时返回TRUE）值为数字值为引用值为文本功能值为空白单元格值为任意错误值（除去#N/A）值为任意错误值（#N/A、#VALUE、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!）值为逻辑值值为错误值#N/A（值不存在）与average有关系运用0与average有关系运用作业题：求出表中字母的个数？995253N34A532517326972365558CUUNT函数43R915989IS类函数355829S39ISTEXT76F999917ISERR5920D6053ISNUMBER1008432B744930395111每种水果都购买卖300斤，请计算出每种水果的金额？例子分子运用1总金额=SUM(IF(ISERR(E20:E27*300),,E20:E27*300))=AVERAGE(IF(ISERR(A33:A37*B33:B37),,A33:A37*B33:B37))=AVERAGE(IF(ISERR(A33:A37*B33:B37),,A33:A37*B33:B37))值为不是文本的任意值（注意此函数在值为空白单元格时返回TRUE）值为数字值为引用值为文本功能值为空白单元格值为任意错误值（除去#N/A）值为任意错误值（#N/A、#VALUE、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!）值为逻辑值值为错误值#N/A（值不存在）与average的有关系运用average的有关系运用5.6MIN、MAX函数学习与运用MIN（number1，number2，...）Number1，number2，...是要从中找出最大值的1到30个数字参数。返回一组值中的最小值。说明可以将参数指定为为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成如果参数是数组或引用，则函数MIN仅使用其中的数字，空白单元格，逻辑值、文本或错误值将被忽视如果参数中不含数字，则函数MIN返回0.品名单价话筒56音箱600电脑TRUE电视无单价DVD功放FALSEMAX(number1，number2，...)Number1，number2，...是要从中找出最大值的1到30个数字参数。说明可以将参数指定为为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字如果参数为数组或引用，则只有数组或引用中的数字将被计算。数组或引用中的空白单元格、逻辑值或如果参数不包含数字，函数MAX返回0（零）品名单价话筒56音箱600电脑TRUE电视无单价DVD功放FALSE示例1选手评委1评委2评委3评委4评委5最后得分牛三斤9.3399.599.389.779.43周扒皮9.639.579.629.829.49.61龙五9.319.439.739.299.579.44王小二9.669.389.4799.379.411.求和各选手的最后得分？2.一条公式做出最后得分为最高分的人？（此题暂不作要求）2题解法一：2题解法二：示例2商品名称单价计划数量实际数量金额一档齿4553104550行星齿677510传动轴7667373后桥899135100库存上限（100）、下限（10）将下列产品的进货数量上限设置为100，下限设置为10作业题年月值求同时满足2010年和5月条件的最大和最小值？2010556820114882类型年月值20114405最大2010557620115127最小2010556820114512201055762010638020106763201055732010638720116652Number1，number2，...是要从中找出最大值的1到30个数字参数。可以将参数指定为为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成如果参数是数组或引用，则函数MIN仅使用其中的数字，空白单元格，逻辑值、文本或错误值将被忽视Number1，number2，...是要从中找出最大值的1到30个数字参数。可以将参数指定为为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字如果参数为数组或引用，则只有数组或引用中的数字将被计算。数组或引用中的空白单元格、逻辑值或其他解法求同时满足2010年和5月条件的最大和最小值？2.8SUM函数SUM返回某单元格区域中所有数字之和。语法SUM（number1,number2,...）Number1,number2,...为1到30个需要求和的参数。说明直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值如果参数为错误值或不能转换成数字的文本，将会导致错误。数字25TURE#NAME?三个4示例1条件计数姓名分数求出姓名为张三，且分数大于等于25的人数是多少？张三20人数张三201陈列28张三24牛二21陈列25张三24李四30张三26示例2条件求和品名数量单价金额求出姓名为压盘，且数量大于等于25的总金额是多少？压盘233407820金额压盘23230529038800离合器255