Excel常用函数基础知识PPT学习教案

1、会计学1Excel常用函数基础知识常用函数基础知识第1页/共36页第2页/共36页第壹部分：excel函数及公式一、函数和公式简述 第3页/共36页第4页/共36页第5页/共36页第6页/共36页第7页/共36页第8页/共36页第9页/共36页第10页/共36页1. 1公式 在一个单元格输入等号时，EXCEL就认为你输入了一个公式。EXCEL单元格接受5种元素的输入:u运算符例如”+”,”*”u单元格引用例如”A7”u值或字符串例如7.5或”金额”u函数和参数例如SUM或IFu括号可以控制公式的运算顺序一个公式最多可容纳1024个字符，如果要创建超出范围限制的公式，必须把公式分为多个公式，或创

2、建一个自定义公式创建公式时，尽量不要使用硬编码值，例如计算7.5%的销售税，可以把销售税放到一个单元格中，使用单元格引用代替文字引用，一旦税率发生改变，只要维护税率单元格，而不需修改每个单元格 二、函数和公式基础 第11页/共36页1. 2关于运算符 运算符对公式中的元素进行特定类型的运算。Microsoft Excel 包含四种类型的运算符：算术运算符、比较运算符、文本运算符和引用运算符。 算术运算符+ +加号3+3- -减号3-1* *乘号2*5/ /除号4/3% %百分比20% 求幂32 比较运算符= =等号A2=A1大与B3B2 小于A1=大于等于A1=B1=小于等于A1=B1不等于A

3、1B1 文本运算符& &NORTH&EASTNORTH&EAST 引用运算符：区域引用(A1:B15)，联合引用(A1:B15,C1:D15)空格交叉引用(A1:D15 C1:C15)第12页/共36页1. 3引用 大多数公式都会使用单元格或范围引用一个或多个单元格，单元格的引用有4种类型，分别使用美元符号加以区别:u相对完全相对，公式被复制时，单元格引用 掉整到新的位置，如A1,A2u绝对完全绝对，公式被复制时，单元格引用不会改变，如$A$1u行绝对部分绝对引用，公式被复制时，列部分调整，行部分不会改变，如A$1u列绝对部分绝对引用，公式被复制时，行部分调整，

4、列部分不会改变，如$A1可以使用F4键在引用模式中进行循环工作表之间的引用，=SHEET2!A1+1工作薄之间的引用，=供应商表.XLSSHEET2!A1+1工作薄之间的引用，=销售表 6月.XLSSHEE2!A1+1第13页/共36页1. 4公式中的错误信息EXCEL2003在错误公式边可以显示一个智能图标，可以单击这个图标了解这个错误的进一步信息第14页/共36页1. 5定义名称 工作表中引用单元格范围计算并不直观，EXCEL可以把单元格，范围，图表，图形命名为有意义的名称，提供给公式引用。如下图即是对单元格范围G2:G13命名为单价u名称不能包含任何空格，可以使用下划线或点号代替空格u可

5、以使用字符和数字组合，但首字符必须是字母或下划线，不能以数字开头u名称长度限制在255个字符内u不能使用符号u名称不区分大小写第15页/共36页2.1工作表函数 工作表函数就是在公式中使用的一种内部工具u可以简化公式u保证公式运行，否作无法计算u提高编辑任务的速度u允许”有条件地”运行公式，使之具备基本判断能力第16页/共36页函数：是预先录制好的公式，函数集是EXCEL中的精华函数都需要使用括号，括号中的内容就是函数的参数。函数的结果决定于参数的使用方法，一个函数可以:u不带参数u只有一个参数u固定数量的参数u不确定数量的参数u可选参数第17页/共36页文本函数:&:连接连个文本字符

6、串TRIM:删除数据前后所有多余的空格，用一个空格代替多个空格。如:TRIM(“ My Home “)=My homeLEN:返回单元格中字符的数量。如:LEN(GREENHEAK VIP)=13LEFT:从左起返回确定数量的字符。如:LEFT(“Beijing”,3)=BeiRIGTH:从右起返回确定数量的字符。如:RIGHT(“Beijing”,4)=jingMID:在字符串中任意位置返回确定数量的字符。如:MID7,8)=19760811UPPER:将文本全部转化为大写。如:UPPER(“join”)=JOINLOWER:将文本全部转化为小写。如:

7、LOWER(“JOIN”)=joinPROPER:将文本首字母大写。如:PROPER(“join me”)=Join Me第18页/共36页查找函数:VLOOKUPVLOOKUP:查找表格中的第一列值，返回相应的值到指定的表格列种，查找表是垂直放置的，语法如下:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)uLookup_value:查找值在查找表的第一列uTable_array:包含查找表的范围uCol_index_num:匹配值在表中的列编号uRange_lookup:可选，为1或空，可以找到一个近似值。为0，找匹配

8、值HLOOKUPHLOOKUP:查找表格中的第一行值，返回相应的值到指定的表格行中，语法如下:HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)uLookup_value:查找值在查找表的第一行uTable_array:包含查找表的范围uCol_index_num:匹配值在表中的行编号uRange_lookup:可选，为1或空，可以找到一个近似值。为0，找匹配值第19页/共36页查找函数:MATCHMATCH:MATCH函数可以返回范围中的一个单元格相对位置，指出它可以匹配某个指定值，语法如下:MATCH(lookup_va

9、lue,lookup_array,match_type)INDEXINDEX:返回范围内的单元格，语法如下:INDEX(array,row_num,column_num)第20页/共36页信息函数:ISERRORISERROR:值为任何错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!)，语法为:ISERROR(VALUE)统计函数:MAXMAX:返回参数列表中的最大值，忽略逻辑值和文本MAX(NUMBER1,NUMBER2)MINMIN:返回参数列表中的最小值，忽略逻辑值和文本MIN(NUMBER1,NUMBER2)第21页/共36页逻辑

10、函数:IFIF:IF函数根据给定的条件，根据条件的不同返回不同的结果。语法为:IF(Logical_test,value_if_true,value_if_false)IF(A190,IF(A190,”优秀优秀”,if(A180,if(A180,”良好良好”,IF(A160,IF(A160,”及格及格”),),”不及格不及格”) )ANDAND:所有参数的逻辑值为真时返回真OROR:当有一个参数为真时即返回真COUNTIFCOUNTIF:计算区域中满足给定条件的单元格的个数，语法如下:COUNTIF(range,criteria)COUNTIF(B1:B10,COUNTIF(B1:B10,”a

11、pplesapples”) )SUMIFSUMIF:根据指定的条件对若干单元格求和，语法如下SUMIF(range,criteria,sum_range)SUMIF(A2:A20,SUMIF(A2:A20,”1000010000”,B2:B20),B2:B20)SUMIF(A2:A20,SUMIF(A2:A20,”00”) )第22页/共36页信息函数:SUMPRODUCTSUMPRODUCT:在给定的几组数组中，将数组间元素相乘，并返回乘积之和SUMPRODUCT(array1,array,)OFFSETOFFSET:以指定的引用为参照系，通过给定的偏移量来得到新的引用，OFFSET(reference,rows,cols,height,width)第23页/共36页三、数组基础知识 1.数组公式:是用于建立可以产生多个结果或对可以存放在行和列中的一组参数进行运算的单个公式。数组公式的特点就是可以执行多重计算,它返回的是一组数据结果。由于一个单元格内只能储存一个数值,所以当结果是一组数据时,单元格只返回第一个值；如果你需要用到所有的运算结果时,要么用多个单元格去分别返回（可以用index（）来返回），要