EXCEL常用函数讲义.pptx

1、EXCEL常用函数讲义,讲解人：王军,EXCEL函数基础知识,工作簿、工作表及单元格 函数 公式 运算符 单元格 函数公式查看,工作簿、工作表及单元格,工作簿,工作表,单元格,相关内容,工作薄文件常用后缀名：xls (office 2003及早期版本)、xlsx（office 2007及后期版本） 双击工作表名称可以重命名工作表。一个工作薄最多可以有255张工作表。 小知识：一张工作表，EXCEL2003版本最多行是65536，列是256。EXCEL2007及能上版本最多行是1048576，列是16384。,函数,函数的定义：是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。,

2、输入内容,单击,单击,单击,常用函数,计算：SUM,COUNT,COUNTA,AVERAGE,MAX,MIN,LARGE,SMALL,ROUND,MOD,RANK,SUMPRODUCT 条件计算：SUMIF,SUMIFS,COUNTIF,COUNTIFS 查找引用：VLOOKUP,HLOOKUP,LOOKUP,INDEX,MATCH,OFFSET,INDIRECT,ROW,COLUMN 逻辑：IF,OR,AND,IFERROR 文本：LEFT,RIGHT,MID FIND,SUBSTTUTE,TRIM,TEXT 日期：YEAR,MONTH,DAY,DATE,DATEDIF,WEEKDAY,WE

3、EKNUM,EOMONTH,公式,函数是预先定义好的公式。这说明函数与公式相互联系，又有着区别。公式是由用户自行设计对工作表进行计算和处理的计算式。 例如：下表中的“=A2+B2+C2”就是公式。它以“=”开始 ，其内部包括函数、引用、运算符和常量。,运算符,算术运算符：+、 、 * 、 / 、 % 、 比较运算符：= 、 、 = 、 文本连接符：& 例如：“北京”&“欢迎您”，就会产生：“北京欢迎您” 引用运算符：,冒号：例如SUM(A2:A10)意思是计算A2到A10的和。,逗号：例如SUM(A2:A10,C2:C10)意思是计算A2到A10、C2到C10共18个单元格的和。,空格：交集运

4、算符。例如SUM(A1:C3 B2:D7)意思是计算B2、B3、C2、C3的和。,单元格,同一个工作表中的单元格引用分为：相对引用、绝对引用和混合引用。 同一个工作薄和不同工作薄中的引用方法如下：,相对引用：例如A1 绝对引用：例如$A $ 1 混合引用：例如$A1,A $1,同一个工作薄表达式为：Sheet3!B12，意思是引用Sheet3工作表中的B12单元格。 不同工作薄引用表达式为：【Book8Sheet2!$F$9，意思是引用Book8工作薄Sheet2工作表中的F9单元格,函数公式查看,单击,单击,显示函数,练习时间,打开“01函数基础知识练习题.xlsx”，完成练习。,EXCEL

5、函数分类,统计函数 数据库函数 日期与时间函数 工程函数 信息函数 财务函数 查找和引用函数 逻辑函数 数学和三角函数 文本函数,如何查看所有函数及功能,单击,单击,单击,单击,常用统计函数,AVERAGE COUNT MAX MIN LARGE,SMALL COUNTIF COUNTIFS SUBTOTAL FREQUENCY,AVERAGE:计算参数的平均值,使用格式：=AVERAGE(number1,number2）,COUNT：计算数字参数的个数,使用格式：=COUNT(value1,value2）,MAX：求所有数值最大值,主要功能：用于求所有数值的最大值。 使用格式：=MAX(nu

6、mber1,mumber2）,MIN：求所有数值最小值,主要功能：用于求所有数值的最小值。 使用格式：=MIN(number1,number2）,LARGE：返回数据集中第K个最大值,主要功能：返回数据集中第K个最大值。（查询第几名次） 使用格式：=LARGE(array,k),SMALL：返回数据集中第K个最小值,主要功能：返回数据集中第K个最小值。 使用格式：=SMALL(array,k),COUNTIF：单条件计数,主要功能：主要用于统计某个单元格区域中符合指定条件的单元格数目。 使用格式：=COUNTIF(Range,Criteria) 参数说明：Range代表要统计的单元格区域；Cr

7、iteria表示要指定的条件表达式。,COUNTIFS：多条件计数,主要功能：用于对某一区域内满足多重条件的单元格进行计数。 使用格式：=COUNTIF(Criteria _range1,Criteria1, Criteria _range2,Criteria2),SUBTOTAL:分类汇总,主工功能：返回数据清单或数据库中的分类汇总。 使用格式：SUBTOTAL(function_num,ref1,ref2） 参数说明：function_num为111之间的自然数，分别指1是AVERAGE,2是COUNT,3是COUNTA,4是MAX,5是MIN,6是PRODUCT,7是STDEV,8是ST

8、DEVP,9是SUM,10是VAR,11是VARP。ref1、ref2数据区域。,FREQUENCY :返回作为矢量数组的频率分布,主工功能：计算出在给定的值域和接收区间内，每个区间包含的数据个数。 使用格式：FREQUENCY(data_array,bins_array) 参数说明：data_array是用来计算频率的一个数组。bins)_array是一个区间数组。,注意：选定G2单元格，先按F2键，再按shift+ctrl+enter组合键。最后拖动复制,练习时间,打开“02常用统计函数练习题.xlsx”，完成练习。,常用数据库函数,DAVERAGE DCOUNT DMAX DMIN DS

9、UM,DAVERAGE:数据库求平均数,主要功能：返回选定数据库项的平均值。 使用格式：=DAVERAGE(database,field,criteria),DCOUNT：数据库统计次数,主要功能：计算数据库中包含数字的单元格个数。 使用格式：=DCOUNT(database,field,criteria),DMAX:数据库求最大值,主要功能：返回选定数据库项中的最大值。 使用格式：=DMAX(database,field,criteria),DMIN：数据库求最小值,主要功能：返回选定数据库中的最小值。 使用格式：=DMIN(database,field,criteria),DSUM:数据库

10、求和,主要功能：返回数据清单或数据库的指定列中，满足给定条件单元格中的数字之和。 使用格式：=DSUM(database,field,criteria),练习时间,打开“03常用数据库练习题.xlsx”，完成练习。,常用日期与时间函数,DATE DATEDIF NOW TODAY YEAR,DATE：返回特定日期数值,主要功能：用于返回特定日期数值。 使用格式：=DATE(year,month,day),DATEDIF:计算两个日期的减法,主要功能：用于计算两个日期之间的天数、月数或年数。 使用格式：=DATEDIF(start_date,end_date,unit),NOW,主要功能：返回当

11、前日期和时间对应的序列数。 使用格式：=NOW(),TODAY,主要功能：返回当前日期的系列数。 使用格式：=TODAY(),YEAR,主要功能：返回某日期的年份。 使用格式：=YEAR(serial_number),练习时间,打开“04常用日期与时间函数练习题.xlsx”，完成练习。,工程函数,工程函数是指用工程工作表函数对工程进行分析。 通常分为三类：,对复数进行处理的函数 在不同的数字系统间进行数值转换的函数 在不同的度量系统中进行数值转换的函数,信息函数,PHONETIC,PHONETIC,主要功能：用于单元格字符合并。 使用格式：PHONETIC(reference),常用财务函数,

12、RATE CUMPRINC,RATE:计算年金的各期利率,主要功能：返回年金的各期利率。 使用格式：=RATE(nper,pmt,pv,fv,type,guess),案例：张三建议你贷给他30000元，同意每年付你8100元，共付5年，计算年回报率。,CUMPRINC：计算本金数额,主要功能：计算一笔贷款两期之间累计偿还的本金数额。 使用格式：=CUMPRINC(rate,nper,pv,start_period,end_period,type),案例：一笔住房贷款，年利率为6.00%；期限为20年；现值为100000。求第一个月偿还的本金。,练习时间,打开“05常用财务函数练习题.xlsx”

13、，完成练习。,查找和引用函数,INDEX ROW VLOOKUP,INDEX:索引函数,主要功能：使用索引从引用或数组中选择值。 使用格式：INDEX(array,row_num,column_num),ROW：返回给定引用行号,主要功能：返回给定引用的行号。 使用格式：=ROW(reference),VLOOKUP:纵向查找,主要功能：指定的区域的首列查找指定的数值。 使用格式：=VLOOKUP(Lookup_value,table_array,col_index_num,range_lookup) 四个参数：判断条件；跟踪数据的区域；返回第几列的数据；是否精确匹配。,练习时间,打开“06查找和引用函数练习题.xlsx”，完成练习。,逻辑函数,AND OR IF NOT TRUE FALSE,AND和IF函数应用,练习时间,打开“07逻辑函数练习题.xlsx”，完成练习。,常用数学和三角函数,SUM ROUND,ROUND:四舍五入,主要功能：用于按指定位数四舍五入。