《Excel培训教程-Excel公式与函数应用》由会员分享，可在线阅读，更多相关《Excel培训教程-Excel公式与函数应用（104页珍藏版）》请在金锄头文库上搜索。
1、编写：刘勇2004年10月16日,Excel培训教程 Excel公式与函数应用,1.对象模型概念 对象：即指可以被程序员操纵的软件元素,一、函数基础,2.数据录入技巧,3.公式基础,公式元素,在公式中使用运算符,支持下列运算符：,引用运算符：,计算模式,自动（默认）模式：,人工模式：,单元格和范围引用外部引用-不同工作簿中的单元格的引用称为外部引用。 远程引用-引用其它程序中的数据称为远程引用。 相对引用-在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。 绝对引用-如果在复制公式时不希望 Excel 调整引用，那么请使用绝对引用。即加入美元符号，如$C$1。,实例
2、： =Sheet2!A1+1 =Budget.xlssheet1!A1+1 =Budget Analysis.xlssheet1!A1+1 =C:MSOfficeExcel =Budget Analysis.xlssheet1!A1+1,引用样式 A1样式 R1C1样式 三维引用(Firstsheet:Lastsheet!RangeReference)例如：=M(SHEET1:SHEET13!B3) R1C1样式示例：,准确复制公式 将公式转换成值,隐藏公式 处理循环引用,循环引用A1引用A1；A1引用B1，B1引用A1；A1引用B1，B1引用C1，C1引用A1 有目的的利用循环引用 示例 产
3、生唯一随机整数A1=IF(M($B$1:$B$15)15,INT(RAND()*30+1),A1)B1=COUNTIF($A$1:$A$15,A1) 解递归方程X=1/(1+X) 解联立方程3X+4Y=83X+8Y=20,公式中的错误,4.名称,名称定义及其优点 创建单元格和范围名称1）使用“定义名称”对话框2）使用“名称框”3）自动创建 渗透到多个工作表的名称 工作薄级名称和工作表级名称 创建名称列表 使用带名称的交叉运算符 使用带名称的范围运算符 命名常数 命名公式,使用带名称的交叉运算符例如：NORTH引用B2:B13 JANUARY引用B2:E2=JANUARY NORTH,使用带名称
4、的范围运算符例如：=M(NORTH JANUARY):(WEST MARCH) 使用命名常数,5.数组公式 多单元格数组公式1.选择保存结果的范围:D2:D62.输入公式：=B2:B6*C2:C63.按“Ctrl+Shift+Enter”组合键使用单一的数组公式的优越性单个单元格数组公式1.选择保存结果的单元格:B82.输入公式：=M(B2:B6*C2:C6)3.按“Ctrl+Shift+Enter”组合键,数组常量=M(1,2,3,4*5,6,7,8)命名数组常量 数组维数1.一维横向数组：1,2,3,42.一维纵向数组：1;2;3;43.二维数组：1,2,3;4,5,6;7,8,9 数组转
5、置通过函数TRANSPOSE( )TRANSPOSE(1,2,3,4; 5,6,7,8;9,10,11,12)=1,5,9;2,6,10;3,7,11;4,8,12,6.函数概述,函数的功能1.简化公式2.实现其它方法无法实现的计算功能3.提高编辑速度4.实现判断的功能5.其它函数功能 函数的结构函数参数可以使用名字、整个行（列）、文字值、表达式、其它函数、数组 在公式中输入函数的方法1.手工输入函数2.使用“插入函数”对话框的方法,函数种类1.文本函数：是用来处理文本串的。例如改变大小写或确定文本串的长度。 2.统计函数：对数据区进行统计和分析。例如可统计根据提供值绘制出直线的斜率、截距等。
6、 3.逻辑函数：进行真假值的判断或复合检查，根据判断的结果返回不同的数值。 4.信息函数：确定存储在单元格中数据的类型。 5.财务函数：有关财务方面相关计算的函数。 6.工程函数：这部分函数主要用于进行工程分析。 7.数学和三角函数：这部分的函数比较常用，例如求和、求平均值等，利用这些函数可以处理简单和复杂的数学 计算。 8.查阅和引用函数：可以利用这些函数在表格中查找特定的数值或某一单元格的引用。 9.日期和时间函数：分析和处理日期或时间值。10.DDE/外部函数：主要是关于与外部数据库的动态连接以及相关的数据查询。11.数据库函数：对存储在数据库或数据清单中的数据进行分析。12.用户自定义
7、函数,二、数学和三角函数ABS 工作表函数返回参数的绝对值 ACOS 工作表函数返回数字的反余弦值 ACOSH 工作表函数返回参数的反双曲余弦值 ASIN 工作表函数返回参数的反正弦值 ASINH 工作表函数返回参数的反双曲正弦值 ATAN 工作表函数返回参数的反正切值 ATAN2 工作表函数返回给定的 X 及 Y 坐标值的反正切值 ATANH 工作表函数返回参数的反双曲正切值 CEILING 工作表函数将参数Number 沿绝对值增大的方向，舍入为最接近的整数或基数 COMBIN 工作表函数计算从给定数目的对象集合中提取若干对象的组合数 COS 工作表函数返回给定角度的余弦值 COSH 工作
8、表函数返回参数的双曲余弦值 COUNTIF 工作表函数计算给定区域内满足特定条件的单元格的数目DEGREES 工作表函数将弧度转换为度,EVEN 工作表函数返回沿绝对值增大方向取整后最接近的偶数 EXP 工作表函数返回 e 的 n 次幂常数 e 等于 2.71828182845904，是自然对数的底数 FACT 工作表函数返回数的阶乘，一个数的阶乘等于 1*2*3*.*该 FACTDOUBLE 工作表函数返回参数 Number 的半阶乘 FLOOR 工作表函数将参数 Number 沿绝对值减小的方向去尾舍入，使其等于最接近的 significance 的倍数 GCD 工作表函数返回两个或多个整
9、数的最大公约数 INT 工作表函数返回实数舍入后的整数值 LCM 工作表函数返回整数的最小公倍数 LN 工作表函数返回一个数的自然对数自然对数以常数项 e（2.71828182845904）为底 LOG 工作表函数按所指定的底数，返回一个数的对数LOG10 工作表函数返回以 10 为底的对数 MDETERM 工作表函数返回一个数组的矩阵行列式的值 MINVERSE 工作表函数返回数组矩阵的逆距阵 MMULT 工作表函数返回两数组的矩阵乘积结果 MOD 工作表函数返回两数相除的余数结果的正负号与除数相同,MROUND 工作表函数返回参数按指定基数舍入后的数值 MULTINOMIAL 工作表函数返
10、回参数和的阶乘与各参数阶乘乘积的比值 ODD 工作表函数返回对指定数值进行舍入后的奇数 PI 工作表函数返回数字 3.14159265358979，即数学常数 pi，精确到小数点后 15 位 POWER 工作表函数返回给定数字的乘幂 PRODUCT 工作表函数将所有以参数形式给出的数字相乘，并返回乘积值 QUOTIENT 工作表函数回商的整数部分，该函数可用于舍掉商的小数部分 RADIANS 工作表函数将角度转换为弧度 RAND 工作表函数返回大于等于 0 小于 1 的均匀分布随机数 RANDBETWEEN 工作表函数返回位于两个指定数之间的一个随机数 ROMAN 工作表函数将阿拉伯数字转换为
11、文本形式的罗马数字 ROUND 工作表函数返回某个数字按指定位数舍入后的数字 ROUNDDOWN 工作表函数靠近零值，向下（绝对值减小的方向）舍入数字 ROUNDUP 工作表函数远离零值，向上（绝对值增大的方向）舍入数字 SERIESM 工作表函数返回基于以下公式的幂级数之和：,SIGN 工作表函数返回数字的符号当数字为正数时返回 1，为零时返回 0，为负数时返回 -1 SIN 工作表函数返回给定角度的正弦值 SINH 工作表函数返回某一数字的双曲正弦值 SQRT 工作表函数返回正平方根 SQRTPI 工作表函数返回某数与 pi 的乘积的平方根 BTOTAL 工作表函数返回数据清单或数据库中的
12、分类汇 M 工作表函数返回某一单元格区域中所有数字之和 MIF 工作表函数根据指定条件对若干单元格求和 MPRODUCT 工作表函数在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和 MSQ 工作表函数返回所有参数的平方和 MX2MY2 工作表函数返回两数组中对应数值的平方差之和 MX2PY2 工作表函数返回两数组中对应数值的平方和之和，平方和加在统计计算中经常使用 MXMY2 工作表函数返回两数组中对应数值之差的平方和 TAN 工作表函数返回给定角度的正切值 TANH 工作表函数返回某一数字的双曲正切值 TRUNC 工作表函数将数字的小数部分截去，返回整数,示例计算空单元格：=COU
13、NTBLANK(DATA) 非空单元格计数：=COUNTA(DATA) 数字单元格计数：=COUNT(DATA) 非文本单元格计数：=M(IF(ISNONTEXT(DATA),1) COUNTIF(Range,Search_condition),出现频率最高项目的计数：（数字值）=COUNTIF(DATA,MODE(DATA)（文本值）=MAX(COUNTIF(DATA,DATA) MIF根据范围：=MIF(Difference,”=“&Today(),Amount) 搜索条件AND：* =M(Differenc0)*(Office=“Oregon”)*Amount)OR：+ =M(IF(Di
14、fferenc=60“)/count(b2:b6)”，然后单击编辑栏中的对号，及格率就会显示出来，这个及格率看起来是不是有些别扭-“0.6”？我们可以单击鼠标右键，在弹出的菜单中选“单元格格式”，将其中的数字设为百分比即可。,三、逻辑函数AND函数：所有参数的逻辑值为真时返回 TRUE；只要一个参数的逻辑值为假即返回 FALSE。简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE OR函数：OR函数指在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE。它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。而OR函数仅需其中任何一个
15、为真即可为真。 NOT函数：NOT函数用于对参数值求反。当要确保一个值不等于某一特定值时，可以使用 NOT 函数。简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSE。 TRUE、FALSE函数：TRUE、FALSE函数用来返回参数的逻辑值，由于可以直接在单元格或公式中键入值TRUE或者FALSE。因此这两个函数通常可以不使用。 IF函数：IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。它的应用很广泛，可以使用函数 IF 对数值和公式进行条件检测。它的语法为IF(logical_test,value_if_true,
16、value_if_false)。其中Logical_test表示计算结果为 TRUE 或 FALSE 的任意值或表达式。本参数可使用任何比较运算符。Value_if_true显示在logical_test 为 TRUE 时返回的值，Value_if_true 也可以是其他公式。Value_if_false logical_test 为 FALSE 时返回的值。Value_if_false 也可以是其他公式。,示例 B2=50，C2=AND(B230,B260)=TRUE B1-B3 单元格中的值为 TRUE、FALSE、TRUE ，B4=AND(B1:B3)=FALSE B5=OR(B1:B3)=TRUENOT(2+2=4)=FALSE 应用实例销售业绩表,
