下载
加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 [指导]excel表格与函数的结合运用

[指导]excel表格与函数的结合运用.doc

[指导]excel表格与函数的结合运用

守着沵到永远_
2017-10-19 0人阅读 举报 0 0 暂无简介

简介:本文档为《[指导]excel表格与函数的结合运用doc》,可适用于娱乐时尚领域

指导excel表格与函数的结合运用公式是单个或多个函数的结合运用。AND“与”运算返回逻辑值仅当有参数的结果均为逻辑“真(TRUE)”时返回逻辑“真(TRUE)”反之返回逻辑“假(FALSE)”。条件判断AVERAGE求出所有参数的算术平均值。数据计算COLUMN显示所引用单元格的列标号值。显示位置CONCATENATE将多个字符文本或单元格中的数据连接在一起显示在一个单元格中。字符合并COUNTIF统计某个单元格区域中符合指定条件的单元格数目。条件统计DATE给出指定数值的日期。显示日期DATEDIF计算返回两个日期参数的差值。计算天数DAY计算参数中指定日期或引用单元格中的日期天数。计算天数DCOUNT返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。条件统计FREQUENCY以一列垂直数组返回某个区域中数据的频率分布。概率计算IF根据对指定条件的逻辑判断的真假结果返回相对应条件触发的计算结果。条件计算INDEX返回列表或数组中的元素值此元素由行序号和列序号的索引值进行确定。数据定位INT将数值向下取整为最接近的整数。数据计算ISERROR用于测试函数式返回的数值是否有错。如果有错该函数返回TRUE反之返回FALSE。逻辑判断LEFT从一个文本字符串的第一个字符开始截取指定数目的字符。截取数据LEN统计文本字符串中字符数目。字符统计MATCH返回在指定方式下与指定数值匹配的数组中元素的相应位置。匹配位置MAX求出一组数中的最大值。数据计算MID从一个文本字符串的指定位置开始截取指定数目的字符。字符截取MIN求出一组数中的最小值。数据计算MOD求出两数相除的余数。数据计算MONTH求出指定日期或引用单元格中的日期的月份。日期计算NOW给出当前系统日期和时间。显示日期时间OR仅当所有参数值均为逻辑“假(FALSE)”时返回结果逻辑“假(FALSE)”否则都返回逻辑“真(TRUE)”。逻辑判断RANK返回某一数值在一列数值中的相对于其他数值的排位。数据排序RIGHT从一个文本字符串的最后一个字符开始截取指定数目的字符。字符截取SUBTOTAL返回列表或数据库中的分类汇总。分类汇总SUM求出一组数值的和。数据计算SUMIF计算符合指定条件的单元格区域内的数值和。条件数据计算TEXT根据指定的数值格式将相应的数字转换为文本形式数值文本转换TODAY给出系统日期显示日期VALUE将一个代表数值的文本型字符串转换为数值型。文本数值转换VLOOKUP在数据表的首列查找指定的数值并由此返回数据表当前行中指定列处的数值条件定位WEEKDAY给出指定日期的对应的星期数。星期计算Excel部分函数列表函数的步骤:选中存放结果的单元格单击“=”(编辑公式)找函数(单击“三角形”形状按钮。或者直接输入函数名选范围CTRL回车键求和函数SUM()平均函数AVERAGE()排位函数RANK()例:Rank(A:$A$:$A$)行号和列号前面加上“$“符号A叫行号。或者叫列号表示单元格所在的位置数据单元格在A列号或者是A列号最大值函数MAX()最小值函数MIN()统计函数COUNTIF()计算满足给定单元格的数目例:Countif(A:B”>”)统计分数大于分的人数注意条件要加双引号在英文状态下输入。单元格内容合并CONCTENATE()将若干文字串合并到一个字符串中RIGHT(A)提取字符串最右边的若干字符长度为位LEFT()返回字符串最左边的若干字符MIDB()自文字的指定位置开始向右提取指定长度的字符串、重复函数REPT()单元格重量出现的次数。、NOW()返回电脑内部的系统日期与时间、MONTH()将序列数转换为对应的月份数编者语:Excel是办公室自动化中非常重要的一款软件很多巨型国际企业都是依靠Excel进行数据管理。它不仅仅能够方便的处理表格和进行图形分析其更强大的功能体现在对数据的自动处理和计算然而很多缺少理工科背景或是对Excel强大数据处理功能不了解的人却难以进一步深入。编者以为对Excel函数应用的不了解正是阻挡普通用户完全掌握Excel的拦路虎然而目前这一部份内容的教学文章却又很少见所以特别组织了这一个《Excel函数应用》系列希望能够对Excel进阶者有所帮助。《Excel函数应用》系列将每周更新逐步系统的介绍Excel各类函数及其应用敬请关注~术语说明什么是参数,参数可以是数字、文本、形如TRUE或FALSE的逻辑值、数组、形如#NA的错误值或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。参数不仅仅是常量、公式或函数还可以是数组、单元格引用等:数组用于建立可产生多个结果或可对存放在行和列中的一组参数进行运算的单个公式。在MicrosoftExcel有两类数组:区域数组和常量数组。区域数组是一个矩形的单元格区域该区域中的单元格共用一个公式常量数组将一组给定的常量用作某个公式中的参数。单元格引用用于表示单元格在工作表所处位置的坐标值。例如显示在第B列和第行交叉处的单元格其引用形式为"B"。常量常量是直接键入到单元格或公式中的数字或文本值或由名称所代表的数字或文本值。例如日期、数字和文本"QuarterlyEarnings"都是常量。公式或由公式得出的数值都不是常量。Excel的数据处理功能在现有的文字处理软件中可以说是独占鳌头几乎没有什么软件能够与它匹敌。在您学会了Excel的基本操作后是不是觉得自己一直局限在Excel的操作界面中而对于Excel的函数功能却始终停留在求和、求平均值等简单的函数应用上呢,难道Excel只能做这些简单的工作吗,其实不然函数作为Excel处理数据的一个最重要手段功能是十分强大的在生活和工作实践中可以有多种应用您甚至可以用Excel来设计复杂的统计管理表格或者小型的数据库系统。请跟随笔者开始Excel的函数之旅。这里笔者先假设您已经对于Excel的基本操作有了一定的认识。首先我们先来了解一些与函数有关的知识。一、什么是函数Excel中所提的函数其实是一些预定义的公式它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。例如SUM函数对单元格或单元格区域进行加法运算。函数是否可以是多重的呢,也就是说一个函数是否可以是另一个函数的参数呢,当然可以这就是嵌套函数的含义。所谓嵌套函数就是指在某些情况下您可能需要将某函数作为另一函数的参数使用。例如图中所示的公式使用了嵌套的AVERAGE函数并将结果与相比较。这个公式的含义是:如果单元格F到F的平均值大于则求F到F的和否则显示数值。在学习Excel函数之前我们需要对于函数的结构做以必要的了解。如图所示函数的结构以函数名称开始后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现请在函数名称前面键入等号(=)。在创建包含函数的公式时公式选项板将提供相关的帮助。公式选项板帮助创建或编辑公式的工具还可提供有关函数及其参数的信息。单击编辑栏中的"编辑公式"按钮或是单击"常用"工具栏中的"粘贴函数"按钮之后就会在编辑栏下面出现公式选项板。整个过程如图所示。二、使用函数的步骤在Excel中如何使用函数呢,单击需要输入函数的单元格如图所示单击单元格C出现编辑栏图单元格编辑单击编辑栏中"编辑公式"按钮将会在编辑栏下面出现一个"公式选项板"此时"名称"框将变成"函数"按钮如图所示。单击"函数"按钮右端的箭头打开函数列表框从中选择所需的函数图函数列表框当选中所需的函数后Excel将打开"公式选项板"。用户可以在这个选项板中输入函数的参数当输入完参数后在"公式选项板"中还将显示函数计算的结果单击"确定"按钮即可完成函数的输入如果列表中没有所需的函数可以单击"其它函数"选项打开"粘贴函数"对话框用户可以从中选择所需的函数然后单击"确定"按钮返回到"公式选项板"对话框。在了解了函数的基本知识及使用方法后请跟随笔者一起寻找Excel提供的各种函数。您可以通过单击插入栏中的"函数"看到所有的函数。三、函数的种类Excel函数一共有类分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。数据库函数当需要分析数据清单中的数值是否符合特定条件时可以使用数据库工作表函数。例如在一个包含销售信息的数据清单中可以计算出所有销售数值大于,且小于,的行或记录的总数。MicrosoftExcel共有个工作表函数用于对存储在数据清单或数据库中的数据进行分析这些函数的统一名称为Dfunctions也称为D函数每个函数均有三个相同的参数:database、field和criteria。这些参数指向数据库函数所使用的工作表区域。其中参数database为工作表上包含数据清单的区域。参数field为需要汇总的列的标志。参数criteria为工作表上包含指定条件的区域。日期与时间函数通过日期与时间函数可以在公式中分析和处理日期值和时间值。工程函数工程工作表函数用于工程分析。这类函数中的大多数可分为三种类型:对复数进行处理的函数、在不同的数字系统(如十进制系统、十六进制系统、八进制系统和二进制系统)间进行数值转换的函数、在不同的度量系统中进行数值转换的函数。财务函数财务函数可以进行一般的财务计算如确定贷款的支付额、投资的未来值或净现值以及债券或息票的价值。财务函数中常见的参数:未来值(fv)在所有付款发生后的投资或贷款的价值。期间数(nper)投资的总支付期间数。付款(pmt)对于一项投资或贷款的定期支付数额。现值(pv)在投资期初的投资或贷款的价值。例如贷款的现值为所借入的本金数额。利率(rate)投资或贷款的利率或贴现率。类型(type)付款期间内进行支付的间隔如在月初或月末。信息函数可以使用信息工作表函数确定存储在单元格中的数据的类型。信息函数包含一组称为IS的工作表函数在单元格满足条件时返回TRUE。例如如果单元格包含一个偶数值ISEVEN工作表函数返回TRUE。如果需要确定某个单元格区域中是否存在空白单元格可以使用COUNTBLANK工作表函数对单元格区域中的空白单元格进行计数或者使用ISBLANK工作表函数确定区域中的某个单元格是否为空。逻辑函数使用逻辑函数可以进行真假值判断或者进行复合检验。例如可以使用IF函数确定条件为真还是假并由此返回不同的数值。查询和引用函数当需要在数据清单或表格中查找特定数值或者需要查找某一单元格的引用时可以使用查询和引用工作表函数。例如如果需要在表格中查找与第一列中的值相匹配的数值可以使用VLOOKUP工作表函数。如果需要确定数据清单中数值的位置可以使用MATCH工作表函数。数学和三角函数通过数学和三角函数可以处理简单的计算例如对数字取整、计算单元格区域中的数值总和或复杂计算。统计函数统计工作表函数用于对数据区域进行统计分析。例如统计工作表函数可以提供由一组给定值绘制出的直线的相关信息如直线的斜率和y轴截距或构成直线的实际点数值。文本函数通过文本函数可以在公式中处理文字串。例如可以改变大小写或确定文字串的长度。可以将日期插入文字串或连接在文字串上。下面的公式为一个示例借以说明如何使用函数TODAY和函数TEXT来创建一条信息该信息包含着当前日期并将日期以"ddmmyy"的格式表示。用户自定义函数如果要在公式或计算中使用特别复杂的计算而工作表函数又无法满足需要则需要创建用户自定义函数。这些函数称为用户自定义函数可以通过使用VisualBasicforApplications来创建。以上对Excel函数及有关知识做了简要的介绍在以后的文章中笔者将逐一介绍每一类函数的使用方法及应用技巧。但是由于Excel的函数相当多因此也可能仅介绍几种比较常用的函数使用方法其他更多的函数您可以从Excel的在线帮助功能中了解更详细的资讯。Excel是办公应用中的常用软件它不光有统计功能在进行查找、计算时Excel也有诸多的函数可以简化我们的操作。需要注意的是对中英文的处理是不大相同的中文的一个字是按两个字节计算的稍不注意就可能忽略这一点造成错误。其实Excel函数中有专门针对双字节字符的函数。让我们先来看看函数FIND与函数FINDB的区别。语法:FIND(findtext,withintext,startnum)FINDB(findtext,withintext,startnum)两个函数的参数相同。作用:FIND函数用于查找文本(withintext)中的字符串(findtext)并从withintext的首字符开始返回findtext的起始位置编号。也可使用SEARCH查找字符串但是FIND和SEARCH不同FIND区分大小写并且不允许使用通配符。FINDB函数用于查找文本(withintext)中的字符串(findtext)并基于字节数从withintext的首字符开始返回findtext的起始位置编号。此函数用于双字节字符。示例:在图中单元B中的公式为“=FIND("雨",A)”单元格B中的公式为“=FINDB("雨",A)”两个函数公式得出的结果不一样这是因为在FIND函数中“雨”位于“吴雨峰”串中的第二个位置故返回“”而在FINDB函数中每个汉字按个字节算所以“雨”是从第三个字节开始的返回“”。同理:LEFT与LEFTB、RIGHT与RIGHTB、LEN与LENB、MID与MIDB、REPLACE与REPLACEB、SEARCH与SEARCHB的关系也如是。即不带字母B的函数是按字符操作的而带字母B的函数是按字节进行操作的。我们在编辑、修改、计算工作簿数据时经常会用到许多汉字字符如果使用以上带字母B的函数对汉字字符进行操作就会很方便。学习Excel函数我们还是从“数学与三角函数”开始。毕竟这是我们非常熟悉的函数这些正弦函数、余弦函数、取整函数等等从中学开始就一直陪伴着我们。首先让我们一起看看Excel提供了哪些数学和三角函数。笔者在这里以列表的形式列出Excel提供的所有数学和三角函数详细请看附注的表格。从表中我们不难发现Excel提供的数学和三角函数已基本囊括了我们通常所用得到的各种数学公式与三角函数。这些函数的详细用法笔者不在这里一一赘述下面从应用的角度为大家演示一下这些函数的使用方法。一、与求和有关的函数的应用SUM函数是Excel中使用最多的函数利用它进行求和运算可以忽略存有文本、空格等数据的单元格语法简单、使用方便。相信这也是大家最先学会使用的Excel函数之一。但是实际上Excel所提供的求和函数不仅仅只有SUM一种还包括SUBTOTAL、SUM、SUMIF、SUMPRODUCT、SUMSQ、SUMXMY、SUMXPY、SUMXMY几种函数。这里笔者将以某单位工资表为例重点介绍SUM(计算一组参数之和)、SUMIF(对满足某一条件的单元格区域求和)的使用。(说明:为力求简单示例中忽略税金的计算。)SUM、行或列求和以最常见的工资表(如上图)为例它的特点是需要对行或列内的若干单元格求和。比如求该单位年月的实际发放工资总额就可以在H中输入公式:=SUM(H:H)、区域求和区域求和常用于对一张工作表中的所有数据求总计。此时你可以让单元格指针停留在存放结果的单元格然后在Excel编辑栏输入公式"=SUM()"用鼠标在括号中间单击最后拖过需要求和的所有单元格。若这些单元格是不连续的可以按住Ctrl键分别拖过它们。对于需要减去的单元格则可以按住Ctrl键逐个选中它们然后用手工在公式引用的单元格前加上负号。当然你也可以用公式选项板完成上述工作不过对于SUM函数来说手工还是来的快一些。比如H的公式还可以写成:=SUM(D:D,F:F)SUM(G:G)、注意SUM函数中的参数即被求和的单元格或单元格区域不能超过个。换句话说SUM函数括号中出现的分隔符(逗号)不能多于个否则Excel就会提示参数太多。对需要参与求和的某个常数可用"=SUM(单元格区域常数)"的形式直接引用一般不必绝对引用存放该常数的单元格。SUMIFSUMIF函数可对满足某一条件的单元格区域求和该条件可以是数值、文本或表达式可以应用在人事、工资和成绩统计中。仍以上图为例在工资表中需要分别计算各个科室的工资发放情况。要计算销售部年月加班费情况。则在F种输入公式为=SUMIF($C$:$C$,"销售部",$F$:$F$)其中"$C$:$C$"为提供逻辑判断依据的单元格区域"销售部"为判断条件即只统计$C$:$C$区域中部门为"销售部"的单元格$F$:$F$为实际求和的单元格区域。二、与函数图像有关的函数应用我想大家一定还记得我们在学中学数学时常常需要画各种函数图像。那个时候是用坐标纸一点点描绘常常因为计算的疏忽描不出平滑的函数曲线。现在我们已经知道Excel几乎囊括了我们需要的各种数学和三角函数那是否可以利用Excel函数与Excel图表功能描绘函数图像呢,当然可以。三、常见数学函数使用技巧四舍五入在实际工作的数学运算中特别是财务计算中常常遇到四舍五入的问题。虽然excel的单元格格式中允许你定义小数位数但是在实际操作中我们发现其实数字本身并没有真正的四舍五入只是显示结果似乎四舍五入了。如果采用这种四舍五入方法的话在财务运算中常常会出现几分钱的误差而这是财务运算不允许的。那是否有简单可行的方法来进行真正的四舍五入呢,其实Excel已经提供这方面的函数了这就是ROUND函数它可以返回某个数字按指定位数舍入后的数字。在Excel提供的"数学与三角函数"中提供了一个名为ROUND(number,numdigits)的函数它的功能就是根据指定的位数将数字四舍五入。这个函数有两个参数分别是number和numdigits。其中number就是将要进行四舍五入的数字numdigits则是希望得到的数字的小数点后的位数。如图所示:单元格B中为初始数据B的初始数据为将要对它们进行四舍五入。在单元格C中输入"=ROUND(B,)"小数点后保留两位有效数字得到、。在单元格D中输入"=ROUND(B,)"则小数点保留四位有效数字得到、。附注:Excel的数学和三角函数一览表ABS工作表函数返回参数的绝对值ACOS工作表函数返回数字的反余弦值ACOSH工作表函数返回参数的反双曲余弦值ASIN工作表函数返回参数的反正弦值ASINH工作表函数返回参数的反双曲正弦值ATAN工作表函数返回参数的反正切值ATAN工作表函数返回给定的X及Y坐标值的反正切值ATANH工作表函数返回参数的反双曲正切值CEILING工作表函数将参数Number沿绝对值增大的方向舍入为最接近的整数或基数COMBIN工作表函数计算从给定数目的对象集合中提取若干对象的组合数COS工作表函数返回给定角度的余弦值COSH工作表函数返回参数的双曲余弦值COUNTIF工作表函数计算给定区域内满足特定条件的单元格的数目DEGREES工作表函数将弧度转换为度EVEN工作表函数返回沿绝对值增大方向取整后最接近的偶数EXP工作表函数返回e的n次幂常数e等于是自然对数的底数FACT工作表函数返回数的阶乘一个数的阶乘等于****该数FACTDOUBLE工作表函数返回参数Number的半阶乘FLOOR工作表函数将参数Number沿绝对值减小的方向去尾舍入使其等于最接近的significance的倍数GCD工作表函数返回两个或多个整数的最大公约数INT工作表函数返回实数舍入后的整数值LCM工作表函数返回整数的最小公倍数LN工作表函数返回一个数的自然对数自然对数以常数项e()为底LOG工作表函数按所指定的底数返回一个数的对数LOG工作表函数返回以为底的对数MDETERM工作表函数返回一个数组的矩阵行列式的值MINVERSE工作表函数返回数组矩阵的逆距阵MMULT工作表函数返回两数组的矩阵乘积结果MOD工作表函数返回两数相除的余数结果的正负号与除数相同MROUND工作表函数返回参数按指定基数舍入后的数值MULTINOMIAL工作表函数返回参数和的阶乘与各参数阶乘乘积的比值ODD工作表函数返回对指定数值进行舍入后的奇数PI工作表函数返回数字即数学常数pi精确到小数点后位POWER工作表函数返回给定数字的乘幂PRODUCT工作表函数将所有以参数形式给出的数字相乘并返回乘积值QUOTIENT工作表函数回商的整数部分该函数可用于舍掉商的小数部分RADIANS工作表函数将角度转换为弧度RAND工作表函数返回大于等于小于的均匀分布随机数RANDBETWEEN工作表函数返回位于两个指定数之间的一个随机数ROMAN工作表函数将阿拉伯数字转换为文本形式的罗马数字ROUND工作表函数返回某个数字按指定位数舍入后的数字ROUNDDOWN工作表函数靠近零值向下(绝对值减小的方向)舍入数字ROUNDUP工作表函数远离零值向上(绝对值增大的方向)舍入数字SERIESSUM工作表函数返回基于以下公式的幂级数之和:SIGN工作表函数返回数字的符号当数字为正数时返回为零时返回为负数时返回

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

文档小程序码

使用微信“扫一扫”扫码寻找文档

1

打开微信

2

扫描小程序码

3

发布寻找信息

4

等待寻找结果

我知道了
评分:

/15

[指导]excel表格与函数的结合运用

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利