常用函数培训课程课件

书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难16 六月 2024常用函数培训课程常用函数培训课程书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难意义：EXCEL具有强大的运算功能在日常工作中，EXCEL运用相当多。用起来，大家常感很慢，一天忙，时间不够，多数是我们对EXCEL运用不熟练。同样对一个数据处理，有的用几分钟，有的用一半天或一天，甚至几天。举例说明要想运用EXCEL对复杂的数据进行统计分析等处理。或者对EXCEL运用自如，必须学会常用的命令和函数，且要运用自如。对数据按固定表格链接需要用函数等方式处理对数据按非固定表格链接要透视等方式处理。前言书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难 3 3、MAX(A1-2000)*0.05*1,2,3,4,5,6,7,8,9-25*0,1,5,15,55,135,255,415,615,0)MAX(A1-2000)*0.05*1,2,3,4,5,6,7,8,9-25*0,1,5,15,55,135,255,415,615,0)1、HLOOKUP($K$3,原料入库!$C$2:$AG$38,ROW()-6,0)2、LOOKUP（2，2，8，10，12，0.5；1；2；1；3）前言 4 4、SUM(A5SUM(A5:INDEX(A1:C10,10,:INDEX(A1:C10,10,1 1)=SUM)=SUM（A5A5：A10A10）5 5、CHOOSE(MOD(YEAR(A3)-4,12)+1,CHOOSE(MOD(YEAR(A3)-4,12)+1,鼠鼠,牛牛,虎虎,兔兔,龙龙,蛇蛇,马马,羊羊,猴猴,鸡鸡,狗狗,猪猪)书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难1讲：行、列号函数行号函数=ROW（）=ROWS（区域）列号函数=COLUMN（）=COLUMNS（区域）ABCDEFG1234=ROW（）（）56=COLUMN（）（）78=ROWS（A1:E10）9=COLUMNS（A1:E10）10书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难1、行、列号函数行号函数=ROW（）=ROWS（区域）列号函数=COLUMN（）=COLUMNS（区域）有什么用？用函数将行和列转为数字与其它函数套用才显奇效ABCD1=HLOOKUP(AI,G3:H6,1，0)=HLOOKUP(AI,G3:H6,ROW（）（），0)2=HLOOKUP(AI,G3:H6,2，0)=HLOOKUP(AI,G3:H6,ROW（）（），0)3=HLOOKUP(AI,G3:H6,3，0)=HLOOKUP(AI,G3:H6,ROW（）（），0)4=HLOOKUP(AI,G3:H6,4，0)=HLOOKUP(AI,G3:H6,ROW（）（），0)5=HLOOKUP(AI,G3:H6,5，0)=HLOOKUP(AI,G3:H6,ROW（）（），0)678=HLOOKUP(AI,G3:H6,ROW（）（）-3，0)910书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难 HLOOKUPHLOOKUP（）（）2、查找函数HLOOKUP（）按行查找引用VLOOKUP（）按列查找引用VLOOKUP（目标，包函目标在内某个范围内，在哪个范围内的第几列，匹配）ABCD1姓名姓名语语文文英英语语2王小王小80903张张三三60804李四李四90585赵赵二二45100678王小王小=VLOOKUP（A8，A1:C5，3，0）9大致匹配为0或FALSE精确匹配为1或TRUELOOKUP（）查找引用书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难2、查找函数HLOOKUP（目标，包函目标在内某个范围内，在哪个范围内的第几列，匹配）ABCD1姓名姓名王小王小张张三三2数学数学80903物理物理60804化学化学90585语语文文45100678王小王小=HLOOKUP（A8，A1:C5，4，0）9书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难3、有效性有效性:EXCEL工具栏中数据有效性设置-有效性条件-序列来源哪一行或哪一列4、LEFTB()LEFTB():LEFTB():从字符串中从字符串中最左最左提取提取指定指定字符字符数数ABCD1姓名姓名=LEFTB(A1,4)结结果果:姓名姓名22日日=LEFTB(A2,2)结结果果:2321日日=LEFTB(A3,2)结结果果:214化学化学=LEFTB(A4,2)结结果果:化化5语语文文书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难5、RIGHTB()RIGHTBRIGHTB():():从字符串中从字符串中最右最右提取提取指定指定字符字符数的字符数的字符ABCD1姓名姓名=RIGHTB(A1,2)结结果果:名名22日日=RIGHTB(A2,2)结结果果:日日321日日=RIGHTB(A3,3)结结果果:1日日4化学化学1=RIGHTB(A4,3)结结果果:学学15语语文文=RIGHTB(A5,1)结结果果:6、MIDB()ABCD1姓名姓名12MIDB(A3,2,2)结结果果:2姓名姓名12=MIDB(A1,2,3)结结果果:名名3姓名姓名12=MIDB(A1,2,4)结结果果:名名14化学化学15语语文文 MIDBMIDB():():从字符串中从字符串中指定字符起向右指定字符起向右提取提取指指定定字符数字符数长度的字符长度的字符书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难7、条件IF()IF(IF(如此条件成立如此条件成立如此条件成立如此条件成立,则执行语句则执行语句则执行语句则执行语句,否则执行本语句否则执行本语句否则执行本语句否则执行本语句)=IF(=IF(232323,”,”张三张三”,”,”李四李四”)”)结果是结果是:李四李四 IFIF函数可套函数可套7 7层层=IF(L1390,=IF(L1390,IF(L13=60,IF(L13=60,IF(L13=20,IF(L13=20,IF(L1335,IF(L1335,),)=IF(L1390,IF(L1390,IF(L13=60,IF(L13=60,IF(L13=50,IF(L13=50,IF(L13=10,IF(L13=10,IF(L13=20,IF(L13=20,IF(L1335,IF(L1335,),)书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难8、条件IF()IF(IF(如此条件成立如此条件成立如此条件成立如此条件成立,则执行语句则执行语句则执行语句则执行语句,否则执行本语句否则执行本语句否则执行本语句否则执行本语句)=IF(=IF(2323,8/4,5*2)“”,”G”,8/2 A5*B7/C3,8/4,5*2)“”,”G”,8/2 A5*B7/C3 结果是结果是:2:2=IF(=IF(2323,A3,A3,A33,A33,A33,A3B6)/COUNTIF($B$2:$B$12,$B$2:$B$12)+1书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难13-17函数小结1、运用4个计数函数2、读懂报表3、会设计报表书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难18乘积和SUMPRODUCT（）=SUMPRODUCT（区域，对应区域）ABCD1108K109K2品名品名 吨吨价格价格1002003玉米玉米1800500600=SUMPRODUCT（C2：D2，C3：D3）用量用量4豆粕豆粕30001002005麦夫麦夫15002001006次粉次粉16002001007合合计计100010008=SUMPRODUCT（B2：B6，C2：C6）成本成本910书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难19、绝对引用和相对引用绝对：$A$1相对：A1行、列都变相对：$A1行变,列不变相对：A$1行不变,列变运用好引用,设公式方便书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难19、错误值处理怪符:#NA,#VALAUE,#REF#DIV/0,#NUM,#NAME,#NULL处理错误值报表美观,计算无误书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难20、ISERROR()用ISERROR()和IF()处理ABCDE1108K109K2品名品名 吨吨价格价格1002003玉米玉米1800500600=D2/E2结结果是果是:#DIV/04豆粕豆粕3000100200=IF(E2=0,”,D2/E2)结结果无怪符果无怪符5麦夫麦夫15002001006次粉次粉1600200100其它其它?7合合计计10001000=IF（ISERROR（D2/E2，”“，D2/E2）8910IF(ISERROR(表达式),”“,表达式)=IF（A1=0，“”，B1/A1)书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难21、字符串所指定引用INDRECT()INDIRECT（”张三“）定义是5学会给字符串定义：插入/名称/定义/指定使用有效性和INDRECT（）书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难18-21、小结1、运用SUMPRODUCT（）、INDRECT（）、ISERROR（）及引用读懂报表设计报表书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难22、数组1、运用SUMPRODUCT多个条件求和 SUMPRODUCT（条件）*（条件）*（求和区）=SUMPRODUCT（周一！M28：M34=“龙腾”）*（周一！P28：P34）=SUMPRODUCT(105A!$A$3:$A$1000=月山!$A11)*(105A!$K$3:$K$1000=“月山点”)*105A!$G$3:$G$1000)数组公式=SUM(105A!$A$3:$A$1000=月山!$A11)*(105A!$K$3:$K$1000=“月山点”)*105A!$G$3:$G$1000)）书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难23、透视报表书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难函数套用了解单个函数运用后多个函数套用设计报表书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难习题1、做个表格L列是性别,Q列是年龄,COUNTIF能计算出16-50岁的女人数吗?如果COUNTIF不能可以用其他函数解决这个问题吗?=SUMPRODUCT(L1:L1000=“女”)*(Q1:Q1000=16)*(Q1:Q100080)*(a1:a10090)3、计算个人所得税公式4、=CHOOSE（4，3，5，80，90，52）书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难习题LOOKUP（2，2，8，10，12，0.5；1；2；1；3）应纳个人所得税税额=（应纳税所得额（五险）-扣除标准）*适用税率-速算扣除数=MAX(A1-2000)*0.05*1,2,3,4,5,6,7,8,9-25*0,1,5,15,55,135,255,415,615,0)=IF(A202000,0,(A20-2000)*LOOKUP(A20-2000,0;500;2000;5000;20000;40000;60000;80000;100000,0.05;0.1;0.15;0.2;0.25;0.3;0.35;0.4;0.45)-LOOKUP(A20-2000,0;500;2000;5000;20000;40000;60000;80000;100000,0;25;125;375;1375;3375;6375;10375;15375)IF(BV6=0,0,IF(BV6=500,5%,IF(BV6=2000,10%,IF(BV6=5000,15%,IF(BV6=20000,20%,IF(BV6=40000,25%,IF(BV6=90,1,IF(I3=80,2,IF(I3=70,3,IF(I3=60,4,5),优秀,良好,一般,及格,较差)25、CHOOSE书山有路勤为径，书山有路勤为径，学海无涯苦作舟学海无涯苦作舟书到用时方恨少，书到用时方恨少，事非经过不知难事非经过不知难总结1、讲了常用的函数使用，但在247个函数中常用的还多2、会学单个函数使用，部份还可多层套用，3、函数使用还需结合EXCEL工具栏中一些命令要熟练使用 分类、汇总、透视、筛选等4、由于本人掌握的也不多，只能将我所用的介绍给大家。讲得不好，占用了大家休息时间，抱歉！