http://www.cfosky.com C F O 联 盟 小辰工作室整理
Excel函数应用之数学和三角函数
学习Excel函数我们还是从数学与三角函数开始毕竟这是我们非常熟悉的函数这些正弦函数余弦函数取整函数等等从中学开始就一直陪伴着我们
首先让我们一起看看Excel提供了哪些数学和三角函数笔者在这里以列表的形式列出Excel提供的所有数学和三角函数详细请看附注的表格
从表中我们不难发现Excel提供的数学和三角函数已基本囊括了我们通常所用得到的各种数学公式与三角函数这些函数的详细用法笔者不在这里一一赘述下面从应用的角度为大家演示一下这些函数的使用方法
一与求和有关的函数的应用
SUM函数是Excel中使用最多的函数利用它进行求和运算可以忽略存有文本空格等数据的单元格语法简单使用方便相信这也是大家最先学会使用的Excel函数之一但是实际上Excel所提供的求和函数不仅仅只有SUM一种还包括SUBTOTALSUMSUMIFSUMPRODUCTSUMSQSUMX2MY2SUMX2PY2SUMXMY2几种函数
SUMIF对满足某一条件这里笔者将以某单位工资表为例重点介绍SUM计算一组参数之和
的单元格区域求和的使用(说明为力求简单示例中忽略税金的计算)
图1 函数求和
SUM
1行或列求和
以最常见的工资表如上图为例它的特点是需要对行或列内的若干单元格求和
比如求该单位2001年5月的实际发放工资总额就可以在H13中输入公式
EXCEL函数应用教程
第 6／89 页
01/22/2005
http://www.cfosky.com C F O 联 盟 小辰工作室整理
=SUM(H3:H12)
2区域求和
区域求和常用于对一张工作表中的所有数据求总计此时你可以让单元格指针停留在存放结果的单元格然后在Excel编辑栏输入公式\\用鼠标在括号中间单击最后拖过需要求和的所有单元格若这些单元格是不连续的可以按住Ctrl键分别拖过它们对于需要减去的单元格则可以按住Ctrl键逐个选中它们然后用手工在公式引用的单元格前加上负号当然你也可以用公式选项板完成上述工作不过对于SUM函数来说手工还是来的快一些比如H13的公式还可以写成
=SUM(D3:D12,F3:F12)-SUM(G3:G12)
3注意
SUM函数中的参数即被求和的单元格或单元格区域不能超过30个换句话说SUM函数括号中出现的分隔符逗号不能多于29个否则Excel就会提示参数太多对需要参与求和的某个常数可用\单元格区域常数\一般不必绝对引用存放该常数的单元格
SUMIF
SUMIF函数可对满足某一条件的单元格区域求和该条件可以是数值文本或表达式可以应用在人事工资和成绩统计中
仍以上图为例在工资表中需要分别计算各个科室的工资发放情况
要计算销售部2001年5月加班费情况则在F15种输入公式为
=SUMIF($C$3:$C$12,\
其中\\$C$3:$C$12区域中部门为\$F$3:$F$12为实际求和的单元格区域
二与函数图像有关的函数应用
我想大家一定还记得我们在学中学数学时常常需要画各种函数图像那个时候是用坐标纸一点点描绘常常因为计算的疏忽描不出平滑的函数曲线现在我们已经知道Excel几乎囊括了我们需要的各种数学和三角函数那是否可以利用Excel函数与Excel图表功能描绘函数图像呢当然可以
这里笔者以正弦函数和余弦函数为例说明函数图像的描绘方法
EXCEL函数应用教程
第 7／89 页
01/22/2005
http://www.cfosky.com C F O 联 盟 小辰工作室整理
图2 函数图像绘制
1 录入数据--如图所示首先在表中录入数据自B1至N1的单元格以30度递增的方式录入从0至360的数字共13个数字
2 求函数值--在第2行和第三行分别输入SIN和COS函数这里需要注意的是由于SIN等三角函数在Excel的定义是要弧度值因此必须先将角度值转为弧度值具体公式写法为以
=SIN(D1*PI()/180) D2为例
3 选择图像类型--首先选中制作函数图像所需要的表中数据利用Excel工具栏上的图表向
在\散点图\再在右侧的\导按钮也可利用\\
型\单击[下一步]出现\不作任何操作直接单击[下一步]
4 图表选项操作--图表选项操作是制作函数曲线图的重要步骤在\如
依次进行操作的项目有 图3
标题--为图表取标题本例中取名为\为横轴和纵轴取标题
坐标轴--可以不做任何操作
网格线--可以做出类似坐标纸上网格也可以取消网格线
图例--本例选择图例放在图像右边这个可随具体情况选择
数据标志--本例未将数据标志在图像上主要原因是影响美观如果有特殊要求例外5完成图像--操作结束后单击[完成]一幅图像就插入Excel的工作区了
6 编辑图像--图像生成后字体图像大小位置都不一定合适可选择相应的选项进行修改所有这些操作可以先用鼠标选中相关部分再单击右键弹出快捷菜单通过快捷菜单中的有关项目即可进行操作
EXCEL函数应用教程
第 8／89 页
01/22/2005
http://www.cfosky.com C F O 联 盟 小辰工作室整理
至此一幅正弦和余弦函数图像制作完成用同样的方法还可以制作二次曲线对数图像等等
三常见数学函数使用技巧--四舍五入
在实际工作的数学运算中特别是财务计算中常常遇到四舍五入的问题虽然excel的单元格格式中允许你定义小数位数但是在实际操作中我们发现其实数字本身并没有真正的四舍五入只是显示结果似乎四舍五入了如果采用这种四舍五入方法的话在财务运算中常常会出现几分钱的误差而这是财务运算不允许的那是否有简单可行的方法来进行真正的四舍五入呢其实Excel已经提供这方面的函数了这就是ROUND函数它可以返回某个数字按指定位数舍入后的数字
在Excel提供的\ROUND(number,num_digits)的函数它的功能就是根据指定的位数将数字四舍五入这个函数有两个参数分别是number和
num_digits其中number就是将要进行四舍五入的数字num_digits则是希望得到的数字的小数点后的位数如图3所示
单元格B2中为初始数据0.123456B3的初始数据为0.234567将要对它们进行四舍五入在单元格C2中输入\小数点后保留两位有效数字得到0.120.23在单元格D2中输入\则小数点保留四位有效数字得到0.12350.2346
图3 对数字进行四舍五入
但由于这个函数的定义是返回实数舍入对于数字进行四舍五入,还可以使用INT取整函数
后的整数值因此用INT函数进行四舍五入还是需要一些技巧的也就是要加上0.5才能达到取整的目的仍然以图3为例如果采用INT函数则C2公式应写成\
最后需要说明的是本文所有公式均在Excel97和Excel2000中验证通过修改其中的单元格引用和逻辑条件值可用于相似的其他场合
附注Excel的数学和三角函数一览表 ABS 工作表函数 ACOS 工作表函数 ACOSH 工作表函数 ASIN 工作表函数 ASINH 工作表函数 ATAN 工作表函数 ATAN2 工作表函数
返回参数的绝对值 返回数字的反余弦值 返回参数的反双曲余弦值 返回参数的反正弦值 返回参数的反双曲正弦值 返回参数的反正切值
返回给定的 X 及 Y 坐标值的反正切值
EXCEL函数应用教程
第 9／89 页
01/22/2005
http://www.cfosky.com C F O 联 盟 小辰工作室整理
ATANH 工作表函数 返回参数的反双曲正切值 CEILING 工作表函数 将参数 Number 沿绝对值增大的方向舍入为最接近的整数或基数 COMBIN 工作表函数 计算从给定数目的对象集合中提取若干对象的组合数 COS 工作表函数 返回给定角度的余弦值 COSH 工作表函数 返回参数的双曲余弦值 COUNTIF 工作表函数 计算给定区域内满足特定条件的单元格的数目 DEGREES 工作表函数 将弧度转换为度
EVEN 工作表函数 返回沿绝对值增大方向取整后最接近的偶数 EXP 工作表函数 返回 e 的 n 次幂常数 e 等于 2.71828182845904是自然对数的底数 FACT 工作表函数 返回数的阶乘一个数的阶乘等于 1*2*3*...*该数 FACTDOUBLE 工作表
返回参数 Number 的半阶乘
函数
将参数 Number 沿绝对值减小的方向去尾舍入使其等于最接近的
FLOOR 工作表函数
significance 的倍数
GCD 工作表函数 返回两个或多个整数的最大公约数 INT 工作表函数 返回实数舍入后的整数值 LCM 工作表函数 返回整数的最小公倍数 LN 工作表函数 返回一个数的自然对数自然对数以常数项 e2.71828182845904为底 LOG 工作表函数 按所指定的底数返回一个数的对数 LOG10 工作表函数 返回以 10 为底的对数 MDETERM 工作表函数 返回一个数组的矩阵行列式的值 MINVERSE 工作表函
返回数组矩阵的逆距阵
数
MMULT 工作表函数 返回两数组的矩阵乘积结果 MOD 工作表函数 返回两数相除的余数结果的正负号与除数相同 MROUND 工作表函数 返回参数按指定基数舍入后的数值 MULTINOMIAL 工作表
返回参数和的阶乘与各参数阶乘乘积的比值
函数
ODD 工作表函数 返回对指定数值进行舍入后的奇数 PI 工作表函数 返回数字 3.14159265358979即数学常数 pi精确到小数点后 15 位 POWER 工作表函数 返回给定数字的乘幂 PRODUCT 工作表函数 将所有以参数形式给出的数字相乘并返回乘积值 QUOTIENT 工作表函
回商的整数部分该函数可用于舍掉商的小数部分
数
RADIANS 工作表函数 将角度转换为弧度
RAND 工作表函数 返回大于等于 0 小于 1 的均匀分布随机数 RANDBETWEEN 工作表
返回位于两个指定数之间的一个随机数
函数
ROMAN 工作表函数 将阿拉伯数字转换为文本形式的罗马数字 ROUND 工作表函数 返回某个数字按指定位数舍入后的数字
ROUNDDOWN 工作表函靠近零值向下绝对值减小的方向舍入数字 EXCEL函数应用教程
第 10／89 页
01/22/2005
EXCEL电子表格函数应用（全PDF版）
