您好,欢迎访问三七文档
当前位置:首页 > 财经/贸易 > 资产评估/会计 > EXCEL数据分析与主要函数
EXCEL函数与数据分析Excel函数与数据处理EXCEL函数与数据分析3数据分析是业务发展的推动力随着公司的快速发展,对管理人员的数据分析能力提出了更高的要求,提高数据分析能力是提高管理能力和水平的重要内容。当前企业数据当中的大部分都属于非结构化数据,比如独立报表、零散数据、自由文本等,致使企业不能充分利用。另一方面,企业数据量非常大,而其中真正有价值的信息却很少,因此管理人员就要从大量的数据中经过深层分析,获得有利于企业运营的信息,供领导决策。为贯彻落实“科学发展观”思想,深入开展“管理提升月”活动,提高管理人员的数据挖掘和数据分析能力,特进行本次交流,共同探讨利用最常用的办公软件来管理和分析数据,提升管理水平,增强企业核心竞争力。4总目录一.公式与函数二.常用函数用法三.应用举例5一、公式与函数公式的特性公式的输入公式中的运算符公式中的数据类型公式的复制和移动公式的调整函数格式内置函数6公式的特性公式的基本特性:•公式的输入是以“=”开始,公式的计算结果显示在单元格中,公式本身显示在编辑栏中。([工具]→[选项]菜单)•如:=1+2+6(数值计算)=A1+B2(引用单元格地址)7公式的输入•=IF(and(A1A2,B1B2),100*A1/A2,100*B1/B2)(函数计算)•=“ABC”&”XYZ”(字符计算,结果:ABCXYZ)•=25+count(A1:C4)(混和计算)公式中的自变量变化,则计算结果会自动调整8公式中的运算符算术运算符:+-*/^%字符运算符:&比较运算符:===逻辑运算符:andornot以函数形式出现优先级顺序:算术运算符→字符运算符→比较运算符→逻辑函数符(使用括号可确定运算顺序)9公式中的数据类型输入公式要注意公式中可以包括:数值和字符、单元格地址、区域、区域名字、函数等不要随意包含空格公式中的字符要用半角引号括起来公式中运算符两边的数据类型要相同如:=“ab”+25出错#VALUE10公式的复制和移动复制移动或公式时,公式会作相对调整公式的复制:使用填充柄菜单[编辑]→[复制]/[粘贴]或→[复制]/[选择性粘贴]11公式的调整相对地址在公式复制时将自动调整绝对地址在公式复制时不变。例如:C3单元的公式=$B$1+$B$2复制到D5中,D5单元的公式=$B$1+$B$2混合地址在公式复制时绝对地址不变,相对地址按规则调整。12函数格式函数是Excel附带的预定义或内置公式函数的格式:函数名(参数1,参数2,...)函数中的参数可以是:数值、字符、逻辑值、表达式、单元格地址、区域、区域名字等没有参数的函数,括号不能省略例如:PI(),RAND(),NOW()13Excel内置函数数学和三角函数统计函数文本函数日期与时间函数逻辑函数财务函数数据库工作表函数工程函数信息函数查找与引用函数14二、常用函数用法重点介绍50个常用函数的功能、格式、参数和用法,包括:数学函数(ABS、MOD、INT、ROUND、ROUNDDOWN、ROUDUP、RAND、SQRT、SUBTOTAL)三角函数(SIN、COS、PI)统计函数(AVERAGE、COUNT、MAX、MIN、SUM、RANK、LARGE、FREQUENCY)文本函数(TEXT、MID、LEFT、RIGHT、TRIM、VALUE、LEN、CONTAENATE)日期函数(NOW、DATE、DAY、MONTH、TODAY、WEEKDAY、DATEIF)条件函数(IF、SUMIF、COUNTIF)逻辑函数(OR、AND)查找函数(COLUMN、INDEX、MATCH、VLOOKUP)财务函数(PMT、PV、NPV、IRR)数据库函数(DCOUND)其他函数(ISBLANK、ISERROR)15数学函数-ABS主要功能:求出相应数字的绝对值。使用格式:ABS(number)参数说明:number代表需要求绝对值的数值或引用的单元格。应用举例:如果在B2单元格中输入公式:=ABS(A2),则在A2单元格中无论输入正数(如100)还是负数(如-100),B2中均显示出正数(如100)。特别提醒:如果number参数不是数值,而是一些字符(如A等),则B2中返回错误值“#VALUE!”。16数学函数-MOD主要功能:求出两数相除的余数。使用格式:MOD(number,divisor)参数说明:number代表被除数;divisor代表除数。应用举例:输入公式:=MOD(13,4),确认后显示出结果“1”。特别提醒:如果divisor参数为零,则显示错误值“#DIV/0!”;MOD函数可以借用函数INT来表示:上述公式可以修改为:=13-4*INT(13/4)。17数学函数-INT主要功能:将数值向下取整为最接近的整数。使用格式:INT(number)参数说明:number表示需要取整的数值或包含数值的引用单元格。应用举例:输入公式:=INT(18.89),确认后显示出18。特别提醒:在取整时,不进行四舍五入;如果输入的公式为=INT(-18.89),则返回结果为-19。18数学函数-ROUND将数字“12.3456”按照指定的位数进行四舍五入,可以在D3单元格中输入以下公式:“=ROUND(B3,C3)“19数学函数-ROUNDDOWN向下舍入函数。例如:出租车的计费标准是:起步价为5元,前10公里每一公里跳表一次,以后每半公里就跳表一次,每跳一次表要加收2元。输入不同的公里数,然后计算其费用。可以在C3单元格中输入以下公式:=IF(B3=10,5+ROUNDDOWN(B3,0)*2,20+ROUNDDOWN((B3-10)*2,0)*2)Roundup函数(向上舍入,用法同上)20数学函数-RAND、SQRTRAND():返回大于等于0及小于1的均匀分布随机(Random)数,每次计算工作表时都将返回一个新的数值。若要生成a与b之间的随机实数,请使用:RAND()*(b-a)+a。如:产生40-100之间的整数:=INT(RAND()*60)+40SQRT(number):返回number的平方根例:=SQRT(4)=221数学函数-SUBTOTAL主要功能:返回列表或数据库中的分类汇总。使用格式:SUBTOTAL(function_num,ref1,ref2,...)参数说明:Function_num为1到11(包含隐藏值)或101到111(忽略隐藏值)之间的数字,用来指定使用什么函数在列表中进行分类汇总计算(如图6);ref1,ref2,……代表要进行分类汇总区域或引用,不超过29个。应用举例:如图7所示,在B64和C64单元格中分别输入公式:=SUBTOTAL(3,C2:C63)和=SUBTOTAL(103,C2:C63),并且将61行隐藏起来,确认后,前者显示为62(包括隐藏的行),后者显示为61,不包括隐藏的行。特别提醒:如果采取自动筛选,无论function_num参数选用什么类型,SUBTOTAL函数忽略任何不包括在筛选结果中的行;SUBTOTAL函数适用于数据列或垂直区域,不适用于数据行或水平区域。222324三角函数-SIN、COSSIN(x):返回x的正弦(Sine)值,其中x用弧度表示。=SIN(PI()/2)COS(x):返回x的余弦(Cosine)值,其中x用弧度表示。=COS(PI()/2)25统计函数-AVERAGE主要功能:求出所有参数的算术平均值。使用格式:AVERAGE(number1,number2,……)参数说明:number1,number2,……:需要求平均值的数值或引用单元格(区域),参数不超过30个。应用举例:在B8单元格中输入公式:=AVERAGE(B7:D7,F7:H7,7,8),确认后,即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。特别提醒:如果引用区域中包含“0”值单元格,则计算在内;如果引用区域中包含空白或字符单元格,则不计算在内。26统计函数-COUNT主要功能:返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数COUNT可以计算单元格区域或数字数组中数字字段的输入项个数。使用格式:COUNT(value1,value2,...)参数说明:Value1,value2,...为包含或引用各种类型数据的参数(1到30个),但只有数字类型的数据才被计算。应用举例:在单元格中输入公式:=COUNT(f3:f22),确认后,即可统计出f3至f22单元格区域中的单元格数目。特别提醒:允许引用的单元格区域中有空白单元格出现。COUNTA函数返回参数列表中非空值的单元格个数,包括非数值型的单元格。27统计函数-MAX主要功能:求出一组数中的最大值。使用格式:MAX(number1,number2……)参数说明:number1,number2……代表需要求最大值的数值或引用单元格(区域),参数不超过30个。应用举例:输入公式:=MAX(E44:J44,7,8,9,10),确认后即可显示出E44至J44单元和区域和数值7,8,9,10中的最大值。特别提醒:如果参数中有文本或逻辑值,则忽略28统计函数-MIN主要功能:求出一组数中的最小值。使用格式:MIN(number1,number2……)参数说明:number1,number2……代表需要求最小值的数值或引用单元格(区域),参数不超过30个。应用举例:输入公式:=MIN(E44:J44,7,8,9,10),确认后即可显示出E44至J44单元和区域和数值7,8,9,10中的最小值。特别提醒:如果参数中有文本或逻辑值,则忽略。29统计函数-SUM主要功能:计算所有参数数值的和。使用格式:SUM(Number1,Number2……)参数说明:Number1、Number2……代表需要计算的值,可以是具体的数值、引用的单元格(区域)、逻辑值等。应用举例:在D64单元格中输入公式:=SUM(D2:D63),确认后即可求出语文的总分。特别提醒:如果参数为数组或引用,只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略;如果将上述公式修改为:=SUM(LARGE(D2:D63,{1,2,3,4,5})),则可以求出前5名成绩的和。30统计函数-RANK主要功能:返回某一数值在一列数值中的相对于其他数值的排位。使用格式:RANK(Number,ref,order)参数说明:Number代表需要排序的数值;ref代表排序数值所处的单元格区域;order代表排序方式参数(如果为“0”或者忽略,则按降序排名,即数值越大,排名结果数值越小;如果为非“0”值,则按升序排名,即数值越大,排名结果数值越大;)。31应用举例:如在C2单元格中输入公式:=RANK(F3,$F$3:$F$22,0),确认后即可得出孙建刚同学的语文成绩在全班成绩中的排名结果。假如E2、E3、E4单元格存放一季度的总产值,在F2单元格内输入公式”=RANK(E2,$E$2:$E$4)”,敲回车即可计算出铸造车间的产值排名是2。特别提醒:在上述公式中,我们让Number参数采取了相对引用形式,而让ref参数采取了绝对引用形式(增加了一个“$”符号),这样设置后,选中C2单元格,将鼠标移至该单元格右下角,成细十字线状时(通常称之为“填充柄”),按住左键向下拖拉,即可将上述公式快速复制到C列下面的单元格中,完成其他同学语文成绩的排名统计。统计函数-RANK(续)32统计函数-FREQUENCY主要功能:以一列垂直数组返回某个区域中数据的频率分布。使用格式:FREQUENCY(data_array,bins_array)参数说明:Data_array表示用来计算频率的一组数据或单元格区域;Bins_array表示为前面数组
本文标题:EXCEL数据分析与主要函数
链接地址:https://www.777doc.com/doc-1788456 .html