EXCEL函数培训

北京仁达房地产评估有限公司数据部图表与图形公式与函数数据分析宏与VBA数据操作EXCEL主要功能分类数据操作图表与图形公式与函数数据分析宏与VBA录入与导出标准图形公式基础排序录制运行宏常规编辑组合图表函数应用筛选VBA基础格式化图表美化函数嵌套列表EXCEL对象高级标记高级图表数组公式数据透视表窗体与控件自定义环境交互式图表自定义函数假设分析类模板打印高级分析1、什么是函数这里的函数是EXCEL内置函数的简称，可以理解为预先建立好的公式，通过使用一下诶称为参数的特定数值来按特定的顺序或结构执行计算，用于执行简单或复杂的数据计算；函数一般分为有参数函数和无参数函数函数的构成：函数名、“（”、函数参数和“）”组成。EL：=sum(A2:H20)函数名函数参数Unit1EXCEL函数简介函数类型功能逻辑函数用于判断真假值或复合检验的函数时间与日期函数分析并处理日期和时间数学与三角函数对现有数据进行数字取整、求和、求平均值以及复杂的函数运算查询与引用函数在现有数据中查找特定数值和单元格的引用函数信息函数用于确定存储在单元格中的数据类型的函数财务函数进行财务运算的函数统计函数对当前数据区域进行统计分析文本函数对字符串进行提取、转换的函数数据库函数按照特定条件对现有数据进行分析的函数工程函数用于工程分析的函数多维数据集函数用于联机分析处理（OLAP）数据库的函数加载宏和自动化函数用于加载宏、自定义函数等•弄清使用函数能实现的功能，返回什么值•正确填写函数名称和表达式•要会使用函数参数，不要使用匹配不对应的参数类型•1、“######”：列宽不够•2、“#DIV/0”：公式中有除数为0或空白•3、“#N/A”：引用数据源不正确或不可用•4、“#NAME?”：函数错误或引用没有定义名称数数据源或引用文本时没有加双引号•5、“#NUM！”：在公式中给函数引用了无效的参数el:sqrt(-4)•6、“#VALUE!”：将文本类型的数据参与了数值运算或输入数组公式时，忘了按ctrl+shift+enter组合键。•7、“#REF!”：公式中使用无效的单元格引用•8、“#NULL！”：公式中使用不正确的区域运算符。1、如何删除Excel不能打印的字符？1）Clean函数语法：clean（text）2）举例2、如何确定两单元格是否相同？1）用运算符“=”判断；不区分大小写返回结果：“TURE”、“FALSE”2）用EXACT函数比较；区分大小写语法：exact（text1,text2）返回的结果：“TURE”、“FALSE”3、如何连接多个字符串？1）用运算符“&”连接；2）用CONCATENATE函数连接；语法：concatenate（text1,text2……text30）4、如何转换文本大小写？1）LOWER：所有大写转换为小写；语法：lower（text）2）UPPER：所有小写转换为大写；语法：upper（text）3）PROPER：首字母及任何非字母后的首字段转换大写，其他字母转换为小写语法：proper（text）5、如何取出单元格中的部分字符？1）Left：从左边开始截取指定数目的字符；语法：left（text,num_chars）2）Right：从右边开始截取指定数目的字符；语法：right（text,num_chars）3）Mid：从文本指定位置开始截取指定数目的字符语法：mid（text,start_num,num_chars）1、了解Excel日期与时间1）日期和时间都仅仅是数字而已日期：从1900-1-1以1开始;时间：从0:00:00以0开始2）格式化日期与时间2、显示时间1）Now：显示当前系统的日期和时间;如何只显示当前时间？=now()-today()2）Time：显示指定数值的时间语法：time(hour,minute,second)3、显示日期1）Today：显示当前系统的日期;2）Date：显示指定数值的日期；3）Year：得到日期的年数4）Month：得到日期的月数5）Day：得到日期的天数3、如何确定指定日期星期几Weekday函数1）语法：weekday(serial_number,return_type)2）return_type类型1或省略：返回数字1（周日）到数字7（周六）2：返回数字1（周一）到数字7（周日）3：返回数字0（周一）到数字6（周日）1、常用的计数函数1）Count：统计时不包括空白、文本、逻辑值和错误;语法：count（value1,value2……）2）Counta：统计时仅不包括空白单元格；语法：counta（value1,value2……）3）两者区别2、指定条件的计数函数1）Countif语法：countif（range,criteria）2）举例3、常用的求和函数1）Sum：进行基本数值求和;语法：sum（number1,number2…）2）Sumif：根据条件求和；语法：sumif（range,criteria,sum_range…）4、分类汇总求和（计算）Subtotal函数1）语法：subtotal(function_num,ref1,ref2…)2）Function_num的常用类型1求平均(average)4求最大值(max)2计数(count)5求最小值(min)3计数(counta)9求和(sum)1、常用的引用函数1）Columns:统计指定区域的列数;2）Rows:统计指定区域的行数；3）Match:得到给定值在列表中的位置Match(lookup_value,lookup_array,match_type)-1查找大于或等于value的最小数值，array必须降序排列1查找小于或等于value的最大数值，array必须升序排列0查找等于value的第一个数值，array无需排序2、如何获得单元格的地址1）Column:返回指定单元格的列标号语法：column(reference)2）Row:返回指定单元格的行标号语法：row(reference)3、常用的查找函数-Vlookup函数1）用于在数据表的首列查找指定的数值，并返回数据表当前行中指定列处的值，其语法为：vlookup(lookup_value,table_array,col_index_num,range_lookup)2）range_lookup的类型True或省略返回近似值False返回精确值4、几个查找函数的比较1）Vlookup函数2）Hlookup函数3）Lookup函数4）Index函数5）Choose函数4、几个查找函数的比较（续）Hlookup函数使用方法类同于Vlookup，但两者的用法不同，Vlookup在列中垂直查找一个匹配的值，而Hlookup是在行中水平查找。4、几个查找函数的比较（续）Lookup函数使用方法类同于Vlookup，但两者的语法稍有不同:lookup(lookup_value,lookup_vector,result_vector)，或lookup(lookup_value,array)。主要区别是:1）lookup函数没有精确匹配，如果没有相等的值则取小于look_value最大的值2）lookup函数必须look_vector或array按升序排序3）vlookup或hlookup可以指定列或行取值，而lookup第二个语法总是选择行或列的最后一个数值。5、几个查找函数的比较（续）Index函数:从数组中指定单元格取值1）语法Index(array,row_num,column_num)2）举例5、几个查找函数的比较（续）Choose函数:从数字的列表中查找值1）语法Choose(index_num,value1,……)2）举例1、常用的数学函数1）Rand:生成随机数;2）Int:将数值向下取整为最接近的整数；3）Trunc:向下舍入（沿绝对值减少的方向）4）Round：按指定位数四舍五入5）Roundup：向上舍入（沿绝对值增大的方向）6）Rounddown：向下舍入（沿绝对值减小的方向）1、常用的数学函数（续）1）Abs:生成绝对值;2）Mod:求两数相除后的余数；2、其他函数1）Average:求平均数,注意不包括空白和字符2）Max：查找一组数的最大值3）Min：查找一组数的最小值4）Sln：直线法计算资产折旧公式是表格处理软件Excel的精髓及核心Excel提供了创建复杂公式的功能Excel提供了数量众多，功能强大的函数来满足运算的要求，这些函数有：数学与三角函数、日期与时间函数、统计函数、数据库函数、逻辑函数、文本函数、财务函数、查找与引用函数、信息函数、工程函数等Excel函数都由函数名，以及用一对圆括号括起来的一系列参数组成，各参数之间用逗号分隔在表格中使用公式与函数，可以自动进行计算并立即显示结果输入自定义公式格式：=后跟运算符与操作数组成的一串运算符：算术运算符：+-*(乘)/(除)%^(乘方)（计算100的2/3次方：=100^(2/3)）字符相加运算符：&（例：=Good&Morning!）比较运算符：===(不等于)括号：()(只使用圆括号)操作数：单元格或区域地址、数据、函数、表达式例：=C5*1.15+DB25=(2*(4-5)7/2)+8=C5+Sheet2!C5+sum(A6:E20，3，4-7)比较表达式输入函数格式：=后跟函数名，以及用一对圆括号括起来的一系列参数组成，各参数之间用逗号分隔例如：=sum(B2:D6,7,49,average(D2:E7))函数名区域地址常数表达式嵌套函数，不以等号开头参数表函数的组成是：=函数名(参数1，参数2，……，参数30)括号不能省略参数最多可达30个，可以是属于某种数据类型的：常量----数值常量、字符串常量(以一对半角双引号括住的若干字符)、日期常量(一对半角双引号括住的yy-mm-dd)、逻辑值常量(TRUE与FALSE，或1与0)单元格或区域地址(相对地址，绝对地址，混合地址)表达式或函数(即函数的嵌套。作为参数使用的函数或公式，不以等号开头)有些函数是不用参数的（无参函数）ROUND(number1,number2)功能：把数值number1四舍五入到指定的小数位数number2。例：=ROUND(35.78,1)的结果是35.8INT(number)功能：取小于或等于数值number的最大整数。例：=INT(15.63)的结果是15=INT(-15.63)的结果是-16★numberINT(number)●数学函数名称功能应用举例运行结果ABS(number)返回参数的绝对值=ABS(-24.65)24.65SUM(number1,number2,……)各参数相加=SUM(1,2,3,4)10PRODUCT(number1,number2,……)各参数相乘=PRODUCT(1,2,3,4)24SQRT(number)返回参数的算术平方根值=SQRT(ABS(-16))4MOD(number1,number2)返回两参数整除后的余数=MOD(26,4)2RAND()返回[0,1)范围内的随机数=RAND()0.3282798PI()返回π的值=3*3*PI()28.274333LOG10(number)返回参数以10为底的对数值=LOG10(1000)3COUNT(number1,number2,……)功能：统计参数组中数值单元格的个数。COUNTA(number1,number2,……)功能：统计参数组中非空白单元格的个数。COUNTBLANK(number1,number2,……)功能：统计参数组中空白单元格的个数。COUNTIF(range,criteria)功能：统计range区域中满足指定条件criteria的单元格个数。说明：•参数range----被统计数据所在的区域地址。•参数criteria----统计条件。其形式可以为条件所在的单元格地址，或表示条件的数字、表达式或字符。例如，优