搜索
常用函数培训2009年7月18日意义：EXCEL具有强大的运算功能在日常工作中，EXCEL运用相当多。用起来，大家常感很慢，一天忙，时间不够，多数是我们对EXCEL运用不熟练。同样对一个数据处理，有的用几分钟，有的用一半天或一天，甚至几天。举例说明要想运用EXCEL对复杂的数据进行统计分析等处理。或者对EXCEL运用自如，必须学会常用的命令和函数，且要运用自如。对数据按固定表格链接需要用函数等方式处理对数据按非固定表格链接要透视等方式处理。前言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)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、SUM(A5:INDEX(A1:C10,10,1))=SUM（A5：A10）5、CHOOSE(MOD(YEAR(A3)-4,12)+1,鼠,牛,虎,兔,龙,蛇,马,羊,猴,鸡,狗,猪)1讲：行、列号函数行号函数=ROW（）=ROWS（区域）列号函数=COLUMN（）=COLUMNS（区域）ABCDEFG1234=ROW（）56=COLUMN（）78=ROWS（A1:E10）9=COLUMNS（A1:E10）101、行、列号函数行号函数=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)910HLOOKUP（）2、查找函数HLOOKUP（）按行查找引用VLOOKUP（）按列查找引用VLOOKUP（目标，包函目标在内某个范围内，在哪个范围内的第几列，匹配）ABCD1姓名语文英语2王小80903张三60804李四90585赵二45100678王小=VLOOKUP（A8，A1:C5，3，0）9LOOKUP（）查找引用2、查找函数HLOOKUP（目标，包函目标在内某个范围内，在哪个范围内的第几列，匹配）ABCD1姓名王小张三2数学80903物理60804化学90585语文45100678王小=HLOOKUP（A8，A1:C5，4，0）93、有效性有效性:EXCEL工具栏中数据有效性设置-有效性条件-序列来源哪一行或哪一列4、LEFTB()LEFTB():从字符串中最左提取指定字符数ABCD1姓名=LEFTB(A1,4)结果:姓名22日=LEFTB(A2,2)结果:2321日=LEFTB(A3,2)结果:214化学=LEFTB(A4,2)结果:化5语文5、RIGHTB()RIGHTB():从字符串中最右提取指定字符数的字符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语文MIDB():从字符串中指定字符起向右提取指定字符数长度的字符7、条件IF()IF(如此条件成立,则执行语句,否则执行本语句)=IF(23,”张三”,”李四”)结果是:张三=IF(23,”张三”,”李四”)结果是:李四IF函数可套7层=IF(L13=0,,IF(L1390,☆☆☆,IF(L13=60,☆☆,IF(L13=20,★★★,IF(L1335,★,)))))=IF(L13=0,,IF(L1390,☆☆☆,IF(L13=60,☆☆,IF(L13=50,☆,IF(L13=10,★★★,IF(L13=20,★★,IF(L1335,★,)))))))8、条件IF()IF(如此条件成立,则执行语句,否则执行本语句)=IF(23,8/4,5*2)“”,”G”,8/2A5*B7/C3结果是:2=IF(23,8/4,5*2)结果是:109、与AND()=AND(A3,A=10）=IF(AND(A33,A3=10),8/4,5*2)10、或or()=IF(OR(A33,A3=10),8/4,5*2)计税11、SUM()SUM(区域)SUM（A1：A13）SUM（A1：G1）SUM（A1：G13）SUM（A1，B2，G18，F2：F10，F20）SUM（）最多30个参数SUM(C23:INDEX($C$1:$AG$35,ROW(),COLUMN（）))12、INDEX()INDEX(A1:C10,ROW(),COLUMN（）))12、INDEX()ABCD1张王小2中国二3三我人=INDEX(A1:C10,4,2)4有的和=INDEX(A1:C10,ROW(),COLUMN（）-2))51产不62要在73些是84以发95主地106一地=SUM(A5:INDEX(A1:C10,10,1))SUM(A5:INDEX(A1:C10,10,1))=SUM（A5：A10）1-12函数小结1、运用12个函数2、读懂报表3、会设计报表13、条件求和SUMIF（）SUMIF（指定区域，条件，求和区域）ABCD1姓名数学语文三2张113三22=SUMIF（A1：A10，”张“，C1：C10）结果是：84有33=SUMIF（A1：A10，D1，C1：C10）结果是：115中446王557李668张779有8810三9914、计数COUNT（）COUNT（指定区域含数字个数）ABCD1姓名数学语文三2张113三一下=COUNT（B1：B10）结果是：74有33=COUNT（C1：C10）结果是：85中四46王557李668张779有8810三9915、计数COUNTA（）COUNTA（指定区域含数字和非空个数）ABCD1姓名数学语文三2张13三一下=COUNTA（B1：B10）结果是：94有3=COUNTA（C1：C10）结果是：85中四4=COUNT（C1：C10）结果是：66王557李668张779有810三9916、空格计数COUNTBLANK（）COUNTBLANK（指定区域空格个数）ABCD1姓名数学语文三2张13三一下=COUNTBLANK（B1：B10）结果是：14有3=COUNTBLANK（C1：C10）结果是：25中四46王557李668张779有810三9916、条件计数COUNTIF（）COUNTIF（指定区域，在指定区域满足条件）ABCD1姓名数学语文三2张13三一下=COUNTIF（A1：A10，D3）结果是：24有3=COUNTIF（A1：A10，”三“）结果是：25中四46王557李668张779有810三9916、条件计数COUNTIF（）COUNTIF（指定区域空，在指定区域满足条件）ABCD1姓名数学语文三2张13三一下=COUNTIF（A1：A10，D3）结果是：24有3=COUNTIF（A1：A10，”三“）结果是：25中四46王557李668张779有810三9917、排位RANK（）RANK（A1，A1：A10，0）A1必须具有数字值；一ref必须包含数值数据值的数组或单元格区域；order是可选。如果省略order，或为它分配为0（零）值，number的秩将是ref中number的位置，如果ref为按降序排序。如果order分配任何非零值，ref假定按升序排列.但重复数的存在将影响后续数值的排位。(10,20,50,20,60)COUNTIF（指定区域，指定区域）COUNTIF({a,b,c,a,c,d},{a,b,c,a,c,d})2,1,2,2,2,1排名次SUMPRODUCT(($B$2:$B$12B6)/COUNTIF($B$2:$B$12,$B$2:$B$12))+113-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）成本91019、绝对引用和相对引用绝对：$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:Q100050))2、在A1：A100成绩中要求查询大于80小于90的人数怎么用函数？=sumproduct((a1:a10080)*(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;