EXCEL常用函数讲义

EXCEL常用函数讲义讲解人：王军EXCEL函数基础知识工作簿、工作表及单元格函数公式运算符单元格函数公式查看工作簿、工作表及单元格工作簿工作表单元格相关内容工作薄文件常用后缀名：xls(office2003及早期版本)、xlsx（office2007及后期版本）双击工作表名称可以重命名工作表。一个工作薄最多可以有255张工作表。小知识：一张工作表，EXCEL2003版本最多行是65536，列是256。EXCEL2007及能上版本最多行是1048576，列是16384。函数函数的定义：是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。输入内容单击单击单击常用函数计算：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,MIDFIND,SUBSTTUTE,TRIM,TEXT日期：YEAR,MONTH,DAY,DATE,DATEDIF,WEEKDAY,WEEKNUM,EOMONTH公式函数是预先定义好的公式。这说明函数与公式相互联系，又有着区别。公式是由用户自行设计对工作表进行计算和处理的计算式。例如：下表中的“=A2+B2+C2”就是公式。它以“=”开始，其内部包括函数、引用、运算符和常量。运算符算术运算符：+、—、*、/、%、^比较运算符：=、、、=、=、文本连接符：&例如：“北京”&“欢迎您”，就会产生：“北京欢迎您”引用运算符：冒号：例如SUM(A2:A10)意思是计算A2到A10的和。逗号：例如SUM(A2:A10,C2:C10)意思是计算A2到A10、C2到C10共18个单元格的和。空格：交集运算符。例如SUM(A1:C3B2:D7)意思是计算B2、B3、C2、C3的和。单元格同一个工作表中的单元格引用分为：相对引用、绝对引用和混合引用。同一个工作薄和不同工作薄中的引用方法如下：相对引用：例如A1绝对引用：例如$A$1混合引用：例如$A1,A$1同一个工作薄表达式为：Sheet3!B12，意思是引用Sheet3工作表中的B12单元格。不同工作薄引用表达式为：【Book8]Sheet2!$F$9，意思是引用Book8工作薄Sheet2工作表中的F9单元格函数公式查看单击单击显示函数练习时间打开“01函数基础知识练习题.xlsx”，完成练习。EXCEL函数分类统计函数数据库函数日期与时间函数工程函数信息函数财务函数查找和引用函数逻辑函数数学和三角函数文本函数如何查看所有函数及功能单击单击单击单击常用统计函数AVERAGECOUNTMAXMINLARGESMALLCOUNTIFCOUNTIFSSUBTOTALFREQUENCYAVERAGE:计算参数的平均值使用格式：=AVERAGE(number1,number2……）COUNT：计算数字参数的个数使用格式：=COUNT(value1,value2……）MAX：求所有数值最大值主要功能：用于求所有数值的最大值。使用格式：=MAX(number1,mumber2……）MIN：求所有数值最小值主要功能：用于求所有数值的最小值。使用格式：=MIN(number1,number2……）LARGE：返回数据集中第K个最大值主要功能：返回数据集中第K个最大值。（查询第几名次）使用格式：=LARGE(array,k)SMALL：返回数据集中第K个最小值主要功能：返回数据集中第K个最小值。使用格式：=SMALL(array,k)COUNTIF：单条件计数主要功能：主要用于统计某个单元格区域中符合指定条件的单元格数目。使用格式：=COUNTIF(Range,Criteria)参数说明：Range代表要统计的单元格区域；Criteria表示要指定的条件表达式。COUNTIFS：多条件计数主要功能：用于对某一区域内满足多重条件的单元格进行计数。使用格式：=COUNTIF(Criteria_range1,Criteria1,Criteria_range2,Criteria2……)SUBTOTAL:分类汇总主工功能：返回数据清单或数据库中的分类汇总。使用格式：SUBTOTAL(function_num,ref1,ref2……）参数说明：function_num为1—11之间的自然数，分别指1是AVERAGE,2是COUNT,3是COUNTA,4是MAX,5是MIN,6是PRODUCT,7是STDEV,8是STDEVP,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DSUMDAVERAGE:数据库求平均数主要功能：返回选定数据库项的平均值。使用格式：=DAVERAGE(database,field,criteria)DCOUNT：数据库统计次数主要功能：计算数据库中包含数字的单元格个数。使用格式：=DCOUNT(database,field,criteria)DMAX:数据库求最大值主要功能：返回选定数据库项中的最大值。使用格式：=DMAX(database,field,criteria)DMIN：数据库求最小值主要功能：返回选定数据库中的最小值。使用格式：=DMIN(database,field,criteria)DSUM:数据库求和主要功能：返回数据清单或数据库的指定列中，满足给定条件单元格中的数字之和。使用格式：=DSUM(database,field,criteria)练习时间打开“03常用数据库练习题.xlsx”，完成练习。常用日期与时间函数DATEDATEDIFNOWTODAYYEARDATE：返回特定日期数值主要功能：用于返回特定日期数值。使用格式：=DATE(year,month,day)DATEDIF:计算两个日期的减法主要功能：用于计算两个日期之间的天数、月数或年数。使用格式：=DATEDIF(start_date,end_date,unit)NOW主要功能：返回当前日期和时间对应的序列数。使用格式：=NOW()TODAY主要功能：返回当前日期的系列数。使用格式：=TODAY()YEAR主要功能：返回某日期的年份。使用格式：=YEAR(serial_number)练习时间打开“04常用日期与时间函数练习题.xlsx”，完成练习。工程函数工程函数是指用工程工作表函数对工程进行分析。通常分为三类：对复数进行处理的函数在不同的数字系统间进行数值转换的函数在不同的度量系统中进行数值转换的函数信息函数PHONETICPHONETIC主要功能：用于单元格字符合并。使用格式：PHONETIC(reference)常用财务函数RATECUMPRINCRATE:计算年金的各期利率主要功能：返回年金的各期利率。使用格式：=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”，完成练习。查找和引用函数INDEXROWVLOOKUPINDEX:索引函数主要功能：使用索引从引用或数组中选择值。使用格式：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FALSEAND和IF函数应用练习时间打开“07逻辑函数练习题.xlsx”，完成练习。常用数学和三角函数SUMROUNDROUND:四舍五入主要功能：用于按指定位数四舍五入。使用格式：=ROUND(number,num_digits)练习时间打开“08练习题.xlsx”，完成练习。常用文本函数综合案例：身份证提取出生日期MID主要功能：返回文本串中从指定位置开始的特定数目的字符。使用格式：=MID(text,start_num,num_chars)练习时间打开“09常用文本函数练习题.xlsx”，完成练习。综合练习打开“09综合练习题.xlsx”，按下图效果完成练习。