(完整版)EXCEL中函数的使用详解

1、excel函数应用17th dec 2015jeff zheng提高电脑操作速度的几个小心得 尽量不要将所有文件都放在桌面 建立分等级的临时文件夹 任务栏工作组的应用 alt+tab和win+tab组合键的应用 win7搜索文件方法 单击与双击的区别我们为什么要学excel函数？如何去重复值打印体检表幸存者偏差学生打分关于excel的几个基本概念 工作簿与工作表 行，列，单元格 路径 相对引用与绝对引用 函数与公式 excel2003版与2007版数据引用时的问题excel函数应用 excel函数的种类 函数的基本语法 函数的调用 嵌套函数的使用 使用excel的帮助来理解函数 常用函数介绍e

2、xcel函数的种类 财务函数 日期函数 时间函数 数学与三角函数 统计函数 数据库管理函数 文本函数 信息类函数 函数的基本语法 函数的基本语法为： = 函数名(参数1,参数2,参数n)。 注意问题： 函数名与其后的括号“（”之间不能有空格。 当有多个参数时，参数之间要用逗号“，”分隔。 参数部分总长度不能超过1024个字符。 参数可以是数值、文本、逻辑值、单元格引用，也可以是各种表达式或函数。 函数中的逗号“，”、引号“”等都是半角字符，而不是全角字符。函数的调用 直接在单元格中输入函数 利用插入函数按钮或命令插入函数嵌套函数的使用 当一个函数中的参数为另外一个函数时，就是在使用嵌套函数 。

3、 利用插入函数按钮或命令插入各个函数。使用excel的帮助来理解函数 在excel帮助里搜索函数名 在单元格中输入函数名后点击下方提示中的函数名。常用函数介绍求和函数: sum/sumif/sumproduct函数数学函数 avergae、 max、min、 abs、 sqrt函数 计数函数: count、counta、countif函数条件函数和逻辑函数: if函数、and函数、or函数日期和时间函数: day、date、days360、today函数等分析工具库里的日期函数查找和引用函数: vlookup、 hlookup、 match、 index、choose、offset函数四舍五入

4、函数: round、floor和ceiling函数 取整函数: int函数信息函数: isblank、istext、isnumber、iserror函数 文本函数：len、left、right、trim、find、text等函数排序函数: rank、large、small函数数据库函数：dget、dmax、dmin、dsum、davgeage函数其他函数求和函数: sum/sumif/sumproduct函数 sum函数 :无条件求和 = sum (参数1,参数2,参数n) sumif函数 :条件求和 =sumif(range,criteria,sum_range) sumproduct函数

5、:在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和 = sumproduct(array1,array2,array3, .) 求和函数: 应用举例 sum函数 sumif函数 sumproduct函数求和函数: 应用举例 隔栏加总计算 某预算表如图所示，每个月分为“预算数”和“实际数”，现要求计算年度“预算数”和“实际数”。这实际上就是隔栏加总问题。如果一个一个单元格相加是很繁琐的。 解决方法：巧妙应用第3行的标题。求和函数: 应用举例 往下（或往右）累加汇总 往下（或往右）累加汇总是常见的实务问题。 解决方法：使用sum函数，但单元格引用的第一个地址应为绝对引用，而第二个地址为相

6、对引用。求和函数: 应用举例 动态汇总当天数据 工作表中存在每天的数据，要求动态汇总计算当前的数据。 可使用sumif函数。但要注意条件的写法。数学函数 avergae、 max、min、 abs、 sqrt函数 常用的数学函数有： avergae函数：求平均值 max：求最大值 min：求最小值 abs：求绝对值 sqrt：计算平方根平均值函数: avergae函数 avergae函数 :求n个数的平均值 = avergae(参数1,参数2,参数n) 最大值与最小值函数: max、min函数 max函数 : 求n个数的最大值 = max(参数1,参数2,参数n) min函数: 求n个数的最小

7、值 = min(参数1,参数2,参数n) 绝对值函数: abs函数 abs函数: 求某数的绝对值 = abs(参数) 例如，abs(-100)=100开平方函数: sqrt函数 sqrt函数: 求某数的平方根 = sqrt(参数) 例如，sqrt(2)=1.4142135623731计数函数: count、counta、countif函数 count函数: 计算给定区域内数值型参数的数目 = count(参数1,参数2,参数n) counta 函数: 返回参数列表中非空值的单元格个数 = counta(参数1，参数2，参数n ) countif函数: 计算给定区域内满足特定条件的单元格的数目

8、= countif(range,criteria) 计数函数:应用举例 count函数: counta 函数: countif函数:计数函数应用计算销售业绩计数函数:计算高于平均业绩的人数条件函数和逻辑函数: if函数、and函数、or函数 if函数: if函数也称条件函数，它根据参数条件的真假，返回不同的结果。 = if(条件表达式，条件值为真时返回的值，条件值为假时返回的值) and函数：and函数表示逻辑与，当所有条件都满足时（即所有参数的逻辑值都为真时），and函数返回true，否则，只要有一个条件不满足即返回false。 = and(条件1，条件2，条件n) or函数：只要有一个条件

9、满足时，or函数返回true，只有当所有条件都不满足时才返回false。 = or(条件1,条件2,条件n)说明：这3个函数常常联合使用。if函数应用举例 之一计算奖金if函数应用举例 之二计算增长率 计算增长率当被比较年份没有数据时，会出现被除数为0的错误。可以利用if函数进行处理。 如图，去年没有数据，而今年有数据，就显示“新增项目”。 去年有数据，而今年没有数据，就显示“已经停产”。if函数应用举例 之三 自动归类问题 为了有效管理零用金，希望在输入现金开支数据后，该金额数据自动依部门归类到适当的列，从而更加醒目地标示出来。 解决办法：使用if函数 如图，先在单元格f2输入公式“=if(

10、$d2=f$1,$c2, )”。 选定单元格区域f2:j2，按【f2】，使处于编辑状态。 按【ctrl+enter】组合键注意单元格的引用方式。and函数应用举例or函数应用举例日期函数: day、date、days360、today函数 day函数: 返回以序列号表示的某日期的天数，用整数 131 表示。 = day(日期序列号) 例如 day(2006-12-22)=22 date函数：返回代表特定日期的序列号。 = date(年,月,日) 例如 date(2006,12,23)= 2006-12-23 days360函数：按照一年 360 天计（每个月以 30 天，一年共计 12 个月）

11、，返回两个日期间相差的天数。 = days360(开始日期,截止日期,逻辑值) 例如 days360(2000-1-15,2005-12-16)=2131天 today函数：返回系统当前的日期。日期函数应用(举例） 设置动态标题：=今天是 &text(today(),yyyy年m月d日)=今天是 &text(today(),yyyy年m月d日)& &text(weekday(today(),aaaa) 设置上月标题：=if(month(today()=1,12,month(today()-1)&“月份收支情况” 设置本月标题=month(today()&

12、amp;月份收支情况“ 将日期转换为星期 中文星期：= text(weekday(today(),aaaa) 英文星期：= text(weekday(today(),dddd) 计算2年5个月20天后的日期 =date(year(today()+2,month(today()+5,day(today()+20)日期函数应用举例确定具体日期日期函数应用举例确定两个日期间的天数日期函数应用举例设置日期显示格式分析工具库里的日期函数 edate函数 eomonth函数 weeknum函数 workday函数 networkdays函数 一个特殊的日期函数：datedif 注意：在使用这些函数之前，必

13、须加载分析工具库，即：单击【工具】【加载宏】命令，打开【加载宏】对话框，选择“分析工具库”，如上图。edate函数 edate函数：返回指定日期往前或往后几个月的日期。 例： 2007年4月12日之后3个月的日期：=edate(“2007-4-12”,3)，为2007-7-12 2007年4月12日之前3个月的日期：=edate(“2007-4-12”,-3)，为2007-1-12 计算应付账款的到期日：如果一笔应付款的到期日为自交易日起3个月的那一天，比如交易日为2006年11月30日，满3个月后为2007-2-28：=edate(“2006-11-30”,3)，为2007-2-28eomo

14、nth函数 eomonth函数：返回指定日期往前或往后几个月的特定月份的月底日期。 例： 2007年4月12日之后3个月的月末日期：= eomonth(“2007-4-12”,3)，为2007-7-31 2007年4月12日之前5个月的月末日期：=edate(“2007-4-12”,-5)，为2006-11-30 计算应付账款的到期日：如果一笔应付款的到期日为自交易日起满3个月后的下一个月的5号，比如交易日为2006年11月20日，满3个月后下个月5号就是2007-3-5：=eomonth(2006-11-20,3-(day(a1)0.5)表示只有在完成率超过50% 时才利用公式(b2-0.4

15、9999)*10进行计算。信息函数: isblank、istext、isnumber、iserror函数 isblank函数：判断单元格是否为空白单元格 istext函数：判断单元格数据是否为文本 isnumber函数：判断单元格数据是否为数字 iserror函数：判断单元格是否出现错误文本函数len、left、right、trim、find、text等函数 len函数：获取字符串的长度 left函数：获取字符串左边指定个数的文本 right函数：获取字符串右边指定个数的文本 mid函数：获取字符串从指定位置起的指定个数的文本 trim函数：取消字符串两侧的空格 find函数：查找某字符在字符串中第一次出现的位置 text函数：将数值转换为按指定数字格式表示的文本 其他的文本函数文本函数应用举例 例1：len(abcd)=4left(abcd,2)= abright(abcd,2)= cdtrim( abcd )= abcdfind(.,12345.65)=6text(12345.6687,￥0.00)= ￥12345.67 例2：从身份证号码获取出生日期和性别文本函数应用举例例3：将科目编码与科目名称分离（假设科目编码与科目名称之间有一个空格）问题：如果科目编码与科目名称之间没有空格，该怎么办呢？可使用len函数和le