EXCEL数据分析与主要函数ppt课件

.,1,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出错#VALUE,.,10,公式的复制和移动,复制移动或公式时，公式会作相对调整公式的复制：使用填充柄菜单编辑复制/粘贴或复制/选择性粘贴,.,11,公式的调整,相对地址在公式复制时将自动调整绝对地址在公式复制时不变。例如：C3单元的公式=$B$1+$B$2复制到D5中，D5单元的公式=$B$1+$B$2混合地址在公式复制时绝对地址不变，相对地址按规则调整。,.,12,函数格式,函数是Excel附带的预定义或内置公式函数的格式：函数名(参数1，参数2,.)函数中的参数可以是：数值、字符、逻辑值、表达式、单元格地址、区域、区域名字等没有参数的函数，括号不能省略例如:PI()，RAND(),NOW(),.,13,Excel内置函数,数学和三角函数统计函数文本函数日期与时间函数逻辑函数财务函数数据库工作表函数工程函数信息函数查找与引用函数,.,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=18,符合要求,不符合要求)，确信以后，如果C26单元格中的数值大于或等于18，则C29单元格显示“符合要求”字样，反之显示“不符合要求”字样。特别提醒：本文中类似“在C29单元格中输入公式”中指定的单元格，在使用时，并不需要受其约束。,.,49,条件函数SUMIF,主要功能：计算符合指定条件的单元格区域内的数值和。使用格式：SUMIF（Range,Criteria,Sum_Range）参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式；Sum_Range代表需要计算的数值所在的单元格区域。应用举例：在c40单元格中输入公式：=SUMIF(D3:D22,男,F3:F22)，确认后即可求出“男”生的语文成绩和。d3性别计算：=IF(LEN(C3)=18,IF(MOD(MID(C3,17,1),2)=0,女,男),IF(MOD(MID(C3,15,1),2)=0,女,男)特别提醒：如果把上述公式修改为：=SUMIF(D3:D22,”女,F3:F22)，即可求出“女”生的语文成绩和；“男”和“女”是文本型。,.,50,条件函数COUNTIF,主要功能：统计某个单元格区域中符合指定条件的单元格数目。使用格式：COUNTIF(Range,Criteria)参数说明：Range代表要统计的单元格区域；Criteria表示指定的条件表达式。应用举例：在C17单元格中输入公式：=COUNTIF(f3:f22,”=80”)，确认后，即可统计出f3至f22单元格区域中，数值大于等于80的单元格数目。假如d3:d22区域内存放着员工的性别，则公式“=COUNTIF(d3:d22，”女”)”统计其中的女职工数量特别提醒：允许引用的单元格区域中有空白单元格出现,.,51,逻辑函数OR,主要功能：返回逻辑值，仅当所有参数值均为逻辑“假（FALSE）”时返回函数结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”。使用格式：OR(logical1,logical2,.)参数说明：Logical1,Logical2,Logical3：表示待测试的条件值或表达式，最多这30个。应用举例：在C40单元格输入公式：=OR(f3=60,f4=60)，确认。如果C40中返回TRUE，说明A62和B62中的数值至少有一个大于或等于60，如果返回FALSE，说明A62和B62中的数值都小于60。特别提醒：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!”或“#NAME”。,.,52,逻辑函数AND,主要功能：返回逻辑值：如果所有参数值均为逻辑“真（TRUE）”，则返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。使用格式：AND(logical1,logical2,.)参数说明：Logical1,Logical2,Logical3：表示待测试的条件值或表达式，最多这30个。应用举例：在C38单元格输入公式：=AND(f5=60,f6=60)，确认。如果C38中返回TRUE，说明f5和f6中的数值均大于等于60，如果返回FALSE，说明f5和f6中的数值至少有一个小于60。特别提醒：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!”或“#NAME”。,.,53,财务函数PMT,主要功能：求贷款分期偿还额语法形式：PMT(rate,nper,pv,fv,type)其中，rate为各期利率，是一固定值，nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pv为现值，或一系列未来付款当前值的累积和，也称为本金，fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零），type为0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。应用举例：假如你为购房贷款十万元，如果年利率为7%，每月末还款。采用十年还清方式时，月还款额计算公式为“=PMT(7%/12,120,-100000)”。其结果为￥-1,161.08，就是你每月须偿还贷款1161.08元。,.,54,财务函数PV,主要功能：零存整取收益函数语法形式：PV（rate，nper，pmt，fv，type）。rate为存款利率；nper为总的存款时间，对于三年期零存整取存款来说共有3*12=36个月；pmt为每月存款金额，如果忽略pmt则公式必须包含参数fv；fv为最后一次存款后希望得到的现金总额，如果省略了fv则公式中必须包含pmt参数；type为数字0或1，它指定存款时间是月初还是月末。应用举例：假如你每月初向银行存入现金500元，如果年利2.15%（按月计息，即月息2.15%/12）。如果你想知道5年后的存款总额是多少，可以使用公式“=FV(2.15%/12,60,-500,0,1)”计算，其结果为￥31，698.67。,.,55,财务函数NPV,主要功能：求投资的净现值：语法形式：NPV(rate,value1,value2,.)其中，rate为各期贴现率，是一固定值；value1,value2,.代表1到29笔支出及收入的参数值，value1,value2,.所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。应用举例：=NPV(8,A3:A8),.,56,财务函数IRR,主要功能：返回内部收益率语法形式：IRR(values,guess)其中values为数组或单元格的引用，包含用来计算内部收益率的数字，values必须包含至少一个正值和一个负值，以计算内部收益率，函数IRR根据数值的顺序来解释现金流的顺序，故应确定按需要的顺序输入了支付和收入的数值，如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略；guess为对函数IRR计算结果的估计值，excel使用迭代法计算函数IRR从guess开始，函数IRR不断修正收益率，直至结果的精度达到0.00001%，如果函数IRR经过20次迭代，仍未找到结果，则返回错误值#NUM！，在大多数情况下，并不需要为函数IRR的计算提供guess值，如果省略guess，假设它为0.1（10%）。如果函数IRR返回错误值#NUM！，或结果没有靠近期望值，可以给guess换一个值再试一下。应用举例：如果要开办一家服装商店，预计投资为￥110,000，并预期为今后五年的净收益为：￥15,000、￥21,000、￥28,000、￥36,000和￥45,000。分别求出投资两年、四年以及五年后的内部收益率。,.,57,数据库函数DCOUNT,主要功能：返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。使用格式：DCOUNT(database,field,criteria)参数说明：Database表示需要统计的单元格区域；Field表示函数所使用的数据列（在第一行必须要有标志项）；Criteria包含条件的单元格区域。应用举例：如图1所示，在F4单元格中输入公式：=DCOUNT(A1:D11,“语文”,F1:G2)，确认后即可求出“语文”列中，成绩大于等于70，而小于80的数值单元格数目（相当于分数段人数）。特别提醒：如果将上述公式修改为：=DCOUNT(A1:D11,F1:G2)，也可以达到相同目的。,.,58,数据库函数DCOUNT（续）,.,59,查找函数COLUMN,主要功能：显示所引用单元格的列标号值。使用格式：COLUMN(reference)参数说明：reference为引用的单元格。应用举例：在C11单元格中输入公式：=COLUMN(B11)，确认后显示为2（即B列）。特别提醒：如果在B11单元格中输入公式：=COLUMN()，也显示出2；与之相对应的还有一个返回行标号值的函数ROW(reference)。,.,60,查找函数INDEX,主要功能：返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。使用格式：INDEX(array,row_num,column_num)参数说明：Array代表单元格区域或数组常量；Row_num表示指定的行序号（如果省略row_num，则必须有column_num）；Column_num表示指定的列序号（如果省略column_num，则必须有row_num）。应用举例：如图3所示，在F8单元格中输入公式：=INDEX(A1:D11,4,3)，确认后则显示出A1至D11单元格区域中，第4行和第3列交叉处的单元格（即C4）中的内容。特别提醒：此处的行序号参数（row_num）和列序号参数（column_num）是相对于所引用的单元格区域而言的，不是Excel工作表中的行或列序号。,.,61,查找函数INDEX（续）,.,62,查找函数MATCH,主要功能：返回在指定方式下与指定数值匹配的数组中元素的相应位置。使用格式：MATCH(lookup_value,lookup_array,match_type)参数说明：Lookup_value代表需要在数据表中查找的数值；Lookup_array表示可能包含所要查找的数值的连续单元格区域；Match_type表示查找方式的值（-1、0或1）。如果match_type为-1，查找大于或等于lookup_value的最小数值，Lookup_array必须按降序排列；如果match_type为1，查找小于或等于lookup_value的最大数值，Lookup_array必须按升序排列；如果match_type为0，查找等于lookup_value的第一个数值，Lookup_array可以按任何顺序排列；如果省略match_type，则默认为1。应用举例：如图4所示，在F2单元格中输入公式：=MATCH(E2,B1:B11,0)，确认后则返回查找的结果“9”。特别提醒：Lookup_array只能为一列或一行。,.,63,查找函数MATCH2,.,64,查找函数VLOOKUP,主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。使用格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)参数说明：Lookup_value代表需要查找的数值；Table_array代表需要在其中查找数据的单元格区域；Col_index_num为在table_array区域中待返回的匹配值的列序号（当Col_index_num为2时,返回table_array第2列中的数值，为3时，返回第3列的值）；Range_lookup为一逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，则返回精确匹配值，如果找不到，则返回错误值#N/A。,.,65,应用举例：成绩表2中，我们在G27单元格中输入公式：=VLOOKUP(孙丹,B2:C21,2,FALSE)，确认后，单元格中即刻显示出该学生的语文成绩。特别提醒：Lookup_value参见必须在Table_array区域的首列中；如果忽略Range_lookup参数，则Table_array的首列必须进行排序；在此函数的向导中，有关Range_lookup参数的用法是错误的。,VLOOKUP（续）,.,66,其他函数ISBLANK,此函数可以判断单元格是否为空。例如判断员工是否到岗：1）输入姓名和上班时间，如图75所示；2）判断其是否到岗，在单元格E3中输入以下公式：“=IF(ISBLANK(D3),请假,到岗)”。,.,67,其他函数ISERROR,主要功能：用于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE，反之返回FALSE。使用格式：ISERROR(value)参数说明：Value表示需要测试的值或表达式。应用举例：输入公式：=ISERROR(A35/B35)，确认以后，如果B35单元格为空或“0”，则A35/B35出现错误，此时前述函数返回TRUE结果，反之返回FALSE。特别提醒：此函数通常与IF函数配套使用，如果将上述公式修改为：=IF(ISERROR(A35/B35),A35/B35)，如果B35为空或“0”，则相应的单元格显示为空，反之显示A35/B35的结果。,.,68,三、应用举例,利用函数进行等级评定统计学生考试成绩自动录入性别根据身份证号提取出生日期年龄统计位次阈值统计让Excel按人打出工资条Word表格计算,.,69,利用函数进行等级评定,在F2单元格中输入：=CONCATENATE(IF(C2=80,A,IF(C2=60,B,C),IF(D2=80,A,IF(D2=60,B,C),IF(E2=80,A,IF(E2=60,B,C)，然后把鼠标指针指向F2单元格的右下角，等鼠标指针变成黑色十字加号时，按住左键向右拖动到这列单元格的最后放手。,.,70,.,71,利用函数进行等级评定(续),也可以在F2单元格中输入：=IF(C2=80,A,IF(C2=60,B,C)&IF(D2=80,A,IF(D2=60,B,C)&IF(E2=80,A,IF(E2=60,B,C)，然后把鼠标指针指向F2单元格的右下角，等鼠标指针变成黑色十字加号时，按住左键向右拖动到这列单元格的最后放手。,.,72,.,73,统计学生考试成绩,先点击f23单元格，输入如下公式：=AVERAGE(f3:f22)，回车后即可得到语文平均分。点击f24单元格，输入公式：=MAX(f$3:f$22)，回车即可得到语文成绩中的最高分。优秀率是计算分数高于或等于85分的学生的比率。点击f25单元格，输入公式：=COUNTIF(C$2:C$95,=85)/COUNT(C$2:C$95)，回车所得即为语文学科的优秀率。点击f26单元格，输入公式：=COUNTIF(C$2:C$95,=60)/COUNT(C$2:C$95)，回车所得即为及格率。,.,74,选中f23:f26单元格，拖动填充句柄向右填充公式至h26单元格，松开鼠标，各学科的统计数据就出来了。至于各科分数段人数的统计，那得先选中f28:f35单元格，在编辑栏中输入公式：=FREQUENCY(F$3:F$22,$C$28:$C$35)。然后按下“Ctrl+Shift+Enter”快捷键，可以看到在公式的最外层加上了一对大括号。现在，我们就已经得到了语文学科各分数段人数了。在K列中的那些数字，就是我们统计各分数段时的分数分界点。现在再选中f28:f35单元格，拖动其填充句柄向右至h列，那么，其它学科的分数段人数也立即显示在我们眼前了。,.,75,自动录入性别,在d3单元格中输入“IF(LEN(C3)=18,IF(MOD(MID(C3,17,1),2)=0,女,男),IF(MOD(MID(C3,15,1),2)=0,女,男)”。回车后即可在单元格获得该职工的性别，而后只要把公式复制到D3、D4等单元格，即可得到其他职工的性别。,.,76,根据身份证号提取出生日期,在单元格中输入公式“=IF(LEN(C3)=15,CONCATENATE(19,MID(C3,7,2),年,MID(C3,9,2),月,MID(C3,11,2),日),CONCATENATE(MID(C3,7,4),年,MID(C3,11,2),月,MID(C3,13,2),日)”。,.,77,年龄统计,工作表的E2:E600单元格存放职工的工龄，我们要以5年为一段分别统计年龄小于20岁、20至25岁之间，一直到55至60岁之间的年龄段人数，可以采用下面的操作方法。首先在工作表中找到空白的K列(或其他列)，自K2单元格开始依次输入20、25、30、35、40.60，分别表示统计年龄小于20、20至25之间、25至30之间等的人数。然后在该列旁边选中相同个数的单元格，例如J2:J10准备存放各年龄段的统计结果。然后在编辑栏输入公式“=FREQUENCY(YEAR(TODAY()-YEAR(E2:E600),K2:K10)”，按下Ctrl+Shift+Enter组合键即可在选中单元格中看到计算结果。其中位于J2单元格中的结果表示年龄小于20岁的职工人数，J3单元格中的数值表示年龄在20至25之间的职工人数等。,.,78,位次阈值统计,假设C2:C21区域存放着学生的考试成绩，首先在D列选取空白单元格D3，在其中输入公式“=PERCENTILE(C2:C21,0.67)”。其中D2作为输入百分点变量的单元格，如果你在其中输入0.33，公式就可以返回名次达到前1/3所需要的成绩。,.,79,让Excel按人打出工资条,新建一Excel文件，在sheet1中存放工资表的原始数据，假设有N列。第一行是工资项目，从第二行开始是每个人的工资。在sheet2中我们来设置工资条。根据实际情况，工资条由三行构成，一行对应工资项目，一行对应一个人的工资数据，然后是一个空行用来方便切割。这样三行构成一个工资条。工资项目处在行号除以3余数为1的行上；空行处在行号能整除3的行上。以上两行不难设置，关键是工资数据行，牵扯到sheet1与sheet2中数据的对应，经分析不难看出“sheet2中的数据