EXCEL函数篇练习单(有答案)

第一题：运用“IF”函数检查报表中“销售毛利”的公式是否正确，正确时返回“OK”，出错时返回“待查”字样；注：销售毛利=销售收入-销售成本BC7项目金额8销售收入30,000.009销售成本10,000.0010销售毛利15,000.00第二题：运用“IF”函数检查身份证号是否相符，如果相符返回空格，不相符返回“待查”字样；DEF身份证号1身份证号2公式验证194401031984020727214401031984020727212044058319871226313X4405831987122631321430406198705242023430406198705242023第三题：如果渠道字段为专柜、地铺、店中店时，返回零售，否则返回加盟B渠道渠道231专柜32地铺33店中店34加盟35专柜36外单37职能中心38管理中心39营销中心第四题：运用函数找出“年龄大于31岁的会计师”、“会计师或助理会计师”；BCDEF姓名职称年龄年龄大于31岁的会计师会计师或助理会计师47李刚智会计师3248蒋生华助理会计师2649孙展无3150韩裕文会计师4251孙国健助理会计师3352赵芳燕会计师2753褚艺德无3654蒋君芬无31第五题：请分别按“方法1”及“方法2”设定达成率公式，要求公式结果出错时返回空格；BCDE实际预算达成率64销售收入28,000.0027,000.00103.7%65销售成本8,600.008,000.00107.5%66销售毛利19,400.0019,000.00102.1%67期间费用14,000.0012,150.00115.2%68其他业务收支等225.00-#DIV/0!69利润5,175.006,850.0075.5%项目XX月第一题：运用“IF”函数检查报表中“销售毛利”的公式是否正确，正确时返回“OK”，出错时返回“待查”字样；第二题：运用“IF”函数检查身份证号是否相符，如果相符返回空格，不相符返回“待查”字样；达成率达成率ABCD函数参数要求公式LEN广州市格风服饰有限公司求字符串长度LEFT广州市格风服饰有限公司前二个字符LEFT广州市格风服饰有限公司全部字符RIGHT广州市格风服饰有限公司后4个字RIGHT广州市格风服饰有限公司全部字符MID广州市格风服饰有限公司前二个字符MID广州市格风服饰有限公司最后1个字MID广州市格风服饰有限公司6、7个字符[e-mail]“@字符串在？位置[e-mail]＝LEFT($B14,FIND(@,$B14,1)-1)[e-mail]=LEFT($B15,FIND(@,$B15,1)-1)ABCD参数1参数2要求公式广州市格风服饰有限公司合并字符串广州市格风服饰有限公司合并字符串wordword测试字符串是否相同Wordword测试字符串是否相同wordword测试字符串是否相同截取@字符串前面的字符E结果11广州广州市格风服饰有限公司有限公司广州市格风服饰有限公司广州司服饰9kyy7788wyiddghE结果运用函数广州市格风服饰有限公司CONCATENATE广州市格风服饰有限公司&TRUEEXACTFALSEEXACTFALSEEXACT数学与三角函数的应用函数名称函数解释数据1数据2公式ABS返回数字的绝对值2232Mod返回两数相除的余数8513.75813.758Rounddown按指定位数舍去数字指定位数后面的小数。13.75813.75213.758Int将数字向下舍入到最接近的整数13.75813.75813.758Round返回某个数字按指定位数进行取整以后的数字（四舍五入）（第2参数是保留小数的位数）Roundup是按指定位数向上舍入指定位数后面的小数。Trunc将数字的小数部分截去，返回整数值要求10求（数据1-数据2）差的绝对值3求（数据1/数据2）的余数13.76求数据1保留2位小数后的金额14求数据1保留0位小数后的金额13.75求数据1保留2位小数后的金额13.76求数据1保留2位小数后的金额13.76求数据1保留2位小数后的金额13求数据1保留0位小数后的金额13求数据1保留0位小数后的金额13.75求数据1保留0位小数后的金额求和求积函数①SUM函数1、简单求和水平方向求和100200300垂直方向100200300单个单元格求和100300200复合范围1004002005003000600求和4800函数100400200500300600求和CDEFG交叉区域33966082100349189819435966886987936749575781003784677688713863919839908888交叉区域：用空格2、生成序列CDEF45序列12344614724834943、文本计数（求和）数据数据之和78“--”：文本变数值12大于5的个数7求个数：*134大于5的和63乘翻求和区域567891011124、数组扩展BC月份销售额1-6月销售额且大于200的和1600181018102120选择月份区域-区域条件，选择销售额区域-条件，选择求和区域（即销售额区域），最后ctrl+shift+回车310004210奇数月的销售额512921966123如果=1，返回，否则取后者7134810009210横向序列纵向序列101291112312134店柜名称类别业绩店柜1专柜155,000总数量店柜2街铺130,000业绩15万数量店柜3店中店60,000业绩15万业绩求和店柜4专柜130,000店柜5街铺220,000店柜6店中店180,000店柜7专柜110,000店柜8街铺230,000店柜9街铺250,000店柜10专柜105,000店柜11专柜180,000店柜12店中店150,000产品数量平均单价销售额销售金额上衣10099999,900720682短裤200899179,800720682短裙150999149,850饰品30029989,700长裙1681,199201,432合计720,6825、排序店柜名称类别业绩SUM降序SUM升序店柜3店中店60,000121店柜10专柜105,000112店柜7专柜110,000103店柜2街铺130,00084店柜4专柜130,00084店柜12店中店150,00076店柜1专柜155,00067店柜6店中店180,00048店柜11专柜180,00048店柜5街铺220,000310店柜8街铺230,000211店柜9街铺250,000112选定业绩区域，可查看状态栏数据，并右键状态栏可选择其他项目HI97679969985015678910“--”：文本变数值求个数：*1乘翻求和区域选择月份区域-区域条件，选择销售额区域-条件，选择求和区域（即销售额区域），最后ctrl+shift+回车如果=1，返回，否则取后者横向序列专柜街铺店中店此处与SUMPRODUCT函数功能相同，操作上略有不同。543231335,000700,000180,000求数量时候，要*10运用数轴函数最后ctrl+shift+回车选定业绩区域，可查看状态栏数据，并右键状态栏可选择其他项目此处与SUMPRODUCT函数功能相同，操作上略有不同。求和函数合并列按部门汇总（单条件求和）ABCDEFHI序号部门员工姓名周转字段（需自行增加）工资分类应发工资序号部门1商品部甲商品部格风格风1,324.001商品部2财务部乙财务部前锦前锦8,000.002财务部3市场部丙市场部锐博锐博10,752.863市场部4管理部丁管理部格风格风6,874.524管理部5训练部戊训练部前锦前锦5,398.765训练部6电子商务部己电子商务部锐博锐博5,931.666电子商务部7陈列部庚陈列部格风格风9,608.647陈列部8设计部辛设计部前锦前锦6,631.378设计部9商品部壬商品部锐博锐博8,479.9310财务部癸财务部格风格风7,392.6511市场部甲市场部前锦前锦50.61分部门格风部分汇总（多条件求和）12管理部乙管理部锐博锐博10,967.18序号部门13训练部丙训练部格风格风8,000.001商品部14电子商务部丁电子商务部前锦前锦8,000.002财务部15陈列部戊陈列部锐博锐博5,396.373市场部16设计部己设计部格风格风3,947.904管理部17管理部庚管理部前锦前锦3,506.185训练部18训练部辛训练部锐博锐博3,914.306电子商务部19商品部壬商品部格风格风4,212.807陈列部118,389.738设计部合计合计合计按部门汇总（单条件求和）J应发工资14,016.73条件要在区域第一列，应发工资是求和，需放在范围内，15,392.6510,803.4721,347.8817,313.0613,931.6615,005.0110,579.27118,389.73分部门格风部分汇总（多条件求和）工资分类应发工资格风5,536.805536.8格风7,392.657392.65格风-0格风6,874.526874.52格风8,000.008000格风-0格风9,608.649608.64格风3,947.903947.941,360.51&:合并字符串合计求和函数SUMIFS函数语法：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)对区域中满足多个条件的单元格求和。sum_range必需。对一个或多个单元格求和，包括数字或包含数字的名称、区域或单元格引用忽略空白和文本值。criteria_range1必需。在其中计算关联条件的第一个区域。criteria1必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对criteria_range1参数中的哪些单元格求和。例如，条件可以表示为32、32、B4、苹果或32。criteria_range2,criteria2,…可选。附加的区域及其关联条件。最多允许127个区域/条件对。BCD客户月份销售额要求20甲1100客户甲的销售额21乙1150客户甲的1月份销售额22丙180客户甲的1月和3月销售额23甲280客户甲和客户丙的销售额24乙2120客户甲和客户丙的1月份销售额25丙270客户甲的1月份和客户丙的3月份销售额26甲3120客户甲和客户丙的的1月份和3月份销售额合计27乙312028丙318029甲46030乙411031丙4105要点SUMIFS和SUMIF函数的参数顺序有所不同。具体而言，sum_range参数在SUMIFS中是第一个参数，而在SUMIF中则是第三个参数。如果要复制和编辑这些相似函数，请确保按正确的顺序放置参数。语法：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)sum_range必需。对一个或多个单元格求和，包括数字或包含数字的名称、区域或单元格引用忽略空白和文本值。criteria_range1必需。在其中计算关联条件的第一个区域。criteria1必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对criteria_range1参数中的哪些单元格求和。例如，条件可以表示为32、32、B4、苹果或32。criteria_range2,criteria2,…可选。附加的区域及其关联条件。最多允许127个区域/条件对。公式值3603601001002202207957951801801=1月份280280480480上面公式月份的逗号变分号要点SUMIFS和SUMIF函数的参数顺序有所不同。具体而言，sum_range参数在SUMIFS中是第一个参数，而在SUMIF中则是第三个参数。如果要复制和编辑这些相似函数，请确保按正确的顺序放置参数。criteria1必需。条件的形式为数