Excel数据分析高级应用-(3)函数

重庆邮电大学经济管理学院周玉敏1.Excel基础知识2.数据输入和表格设计3.函数的使用4.图表的制作5.数据分析1.常用函数使用范例◦条件函数：If()◦统计函数：countif()、countifs()sumif()sumifs()◦查找函数：Vlookup()、Index()、Match()◦随机函数：rand()◦字符函数：len()、right()、left()、mid()2.数组公式的应用【例3.1】将左图所示的表格，转换成右图所示的表格，与户主的关系代码为0表示是户主。=IF(E2=0,D2,)IF(Logical,Value_if_true,Value_if_false)Logical代表逻辑判断表达式Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。【例3.2】根据库存情况填写补充提示列的内容，标准如下：①库存数=30补货③50=库存数=60下单②30库存数50准备④库存数60充足【例3.3】根据职工情况计算医疗付费的起付线，计算标准如下：①所有职工，若为“住院”，起付线则为1300②在职职工，“急诊”或“特殊门诊”，起付线2000【例3.4】计算下表中的总成绩，并给出成绩等级，等级标准如下：①60以下不及格②[60,70),及格③[70,80),中④[80,90),良⑤[90,100],优IF函数查找转换案例【例3.5】某学校年度奖金如下图所示，奖金根据职称确定。教授：2000元，副教授：1500，讲师：1000，助教：是500元。◦◦在F3输入公式：=IF(E3=教授,2000,IF(E3=副教授,1500,IF(E3=讲师,1000,IF(E3=助教,500))))◦公式含义分析COUNTIF(range,criteria)用来搜索指定条件的单元格个数RANGE=指定作为搜索对象的单元格区域.即找是否有与条件相配的单元格区域CRITERIA=指定搜索RANGE单元格的条件,即说明条件【例3.6】按如下要求进行统计：①销售30件以上的店的个数；②男员工的个数；③查找公司员工表中是否已经输入“张三”。【例3.7】统计下表中每月出生的员工人数Excel2007还提供了一个多条件统计函数COUNTIFS，它可以一次对多个不同区域进行不同条件的计数，其用法如下：COUNTIFS(range1，criteria1，range2，criteria2，……，range127，criteria127)【例3.8】有学生成绩表如下图所示。统计其中总分大于200，物理80分以下，地理70分以下，化学65分以上的人数。countifs(F3:F10,”200”,C3:C10,”80”,D3:D10,”70”,E3:E10,”65”)countifs(F3:F10,”200”,C3:C10,”80”,D3:D10,”70”,E3:E10,”65”)总成绩大于200物理成绩大于80地理成绩小于70化学成绩大于65【例3.9】统计下表中一班男性学生的个数。COUNTIFS(B2:B9,一班,D2:D9,男)【例3.10】按如下要求统计：①统计“王伟”超出苹果汁、牛奶和酱油销售配额的次数。②计算有多少销售人员同时超出其苹果汁和牛奶配额。③统计“李芳”和“赵军”超出苹果汁、牛奶和酱油销售配额的次数。①COUNTIFS(B2:D2,=是)②COUNTIFS(B2:B5,=是,C2:C5,=是)③COUNTIFS(B5:D5,=是,B3:D3,=是)SUMIF（Range‚Criteria‚Sum_Range）Sum_range为需要求和的单元格、区域或引用Range为用于条件判断的单元格区域Criteria是由数字、逻辑表达式等组成的判定条件【例3.11】按下图中的要求进行条件求和。条件求和函数SUMIFS用法SUMIFS(sum_range,range1,criteria1,range2,criteria2…)其中range1,range2,…是计算关联条件的1至127个区域；Criteria1,criteria2,…是数字、表达式、单元格引用或文本形式的1至127个条件，用于定义要对哪些单元格求和。这些区域与条件是对应的，即Criteria1是用于range1区域的条件，criteria2是用于range2的条件，以此类推。Sum_range是求和区域。◦功能SUMIFS函数对某一区域内满足多重条件的单元格求和。【例3.12】某家电商场的销售记录如下图所示的A1:G13所示。现要统计出统计每位职工销售各种产品的总数量，并将它统计在I8:L13区域中。（1）在J10单元格中输入统计李本成销售的彩电总数量的计算公式：=SUMIFS($E$3:$E$13,$D$3:$D$13,$I10,$A$3:$A$13,J$9)(2)将此公式向下角复制到L13，求和范围条件1条件2功能：Vlookup按列查找的方式从指定数据表区域的最左列查找特定数据，它能够返回查找区域中与找到单元格位于相同行不同列的单元格内容。语法：VLOOKUP(x‚table‚n‚f)x是要查找的值table是一个单元格区域n是table区域中要返回的数据所在列的序号。n=1时，返回table第1列中的数值；n=2时，返回table第2列中的数值；以此类推。f是一个逻辑值，表示查找的方式。当其为true（或1）时，表示模糊查找；当它为false（或0）时，表示精确查找。【例3.13】已知编号TZ-JK003，根据A13:C18区域给出的信息，查找该编号产品的价格。【例3.14】利用vlookup的模糊查找，从表1中查找表2中的所得税率（1）在I31单元格中输入如下的查询公式：=LOOKUP(H31,$A$31:$B$38,2,TRUE)(2)将此公式向下角复制到I39，【例3.15】下图中左边表格是处理成绩时的顺序，右边表格是录入成绩时的顺序，请根据左边表格中的成绩查找右边表格中每位同学的成绩。=VLOOKUP(I3,$B$3:$E$52,4,FALSE)Match格式◦Match(x，r，f)◦其中x是要查找的数值，r可以是一个数组常量，或某列（或行）连续的单元格区域，其中可能包含有要查找的x。f用于指定match的查找方式，它可以是-1，0或1。功能◦Match(x，r，f)表示的意思是：在数组或连续的单元格区域r中查找x，并返回x在r中的位置编号。◦当f为0是，match进行精确查找，当f为1（或-1）时，match进行模糊查找。Match查找的方式取值函数功能-1r必须按降序排列，查找大于或等于x的最小数值0r不必排序，查找等于x的第一个数值1r必须按升序排列，查找小于或等于x的最大数值Index(Area，r，c，n)其中，Area是1个或多个单元格区域；r是某行的行序号，c是某列的列序号，该函数返回指定的行与列交叉处的单元格引用。如果r等于0，则返回整行单元格引用，如果c等于0，则返回整列单元格引用。当Area包括多个单元格区域时，n=1就表示结果来自于Area中的第1个区域，n=2表示结果来源于第2个单元格区域……。如果省略n表示结果来源于第1个单元格区域。功能Index（Area，r，c，n）的功能是返回Area中第n个单元格区域中的r行，c列交叉处的单元格引用。【例3.16】已有“员工参保资料”表和“按人月汇总”表,制作如下表格，选择姓名、月份后查阅该职工工号和当月社保缴纳情况（1）姓名、月份用“数据有效性”进行设定，姓名数据来源与“员工参保资料”表，月份可直接在“数据有效性”对话框中输入。（2）“工号”查询公式：=VLOOKUP($C$4,按人月汇总!$B:$D,2,0)（3）查询“单位缴费_养老保险”的公式：=INDEX(按人月汇总!$E$5:$P$31,MATCH(按人分月调阅表!$C$4,按人月汇总!$B$5:$B$31,),MATCH(按人分月调阅表!$E$4,按人月汇总!$E$1:$P$1,))功能：返回大于等于0及小于1的均匀分布随机实数另外，Excel2007增加函数randbetween(bottom,top)，可生成两个数之间的随机数。1、Rand函数◦又称为随机函数，它产生一个介于0~1之间小数。◦对Rand函数的结果进行放大，能产生任意范围内的数据。如：RAND()*100：可以产生0~100之间的数据RAND()*50+50：可以产生50~100之间的数据。2、Int函数◦又称取整函数，将数字向下舍入到最接近的整数。比如：INT(8.9)的结果是8INT(-8.9)的结果是-9怎样快速将七年级同学随机安排考场考试？常用字符函数◦LEN(text)计算text文本的长度◦RIGHT(text,n)该函数从text文本的右边取出n个字符。◦LEFT(text,n)该函数从text文本的左边取出n个字符◦MID(text,n,m)该函数从text文本的第n个字符起，取出m个字符。=--TEXT(MID(B2,7,6+(LEN(B2)=18)*2),#-00-00)概述数组公式：能够同时对一组或两组以上的数据进行计算，计算的结果可能是一个，也可能是多个。普通公式只执行一个简单计算，并且返回一个运算结果。数组公式的建立方法（1）选中需要保存数组公式结果的单元格或单元格区域。（2）输入公式的内容。（3）按Ctrl+Shift+Enter键。问题1：在D2:D4求出商品的销售金额。问题1：在D2:D4求出商品的销售金额。一般解决方法：三个普通公式问题1：在D2:D4求出商品的销售金额。数组公式解决：数组公式的优点：（1）批量计算，节省计算时间；（2）不能修改其中任一部分，保持公式集合的完整性。问题2：在F1求出商品的销售总金额普通公式解决办法：（1）插入辅助列，先求出各商品的销售额，然后再求总和。（2）直接在F1输入公式“=SUM(B2*C2,B3*C3,B4*C4)”，这样看上去不错，可是，如果有100行数据，一千行号数据呢？先不考虑单元格能容纳多少字符的问题，就光输入公式，累也得把你累趴下，显然是行不通的。数组公式：选中F1单元格，输入公式“=SUM(B2:B4*C2:C4)”，三键确认输入即可。数组公式解决：选中F1单元格，输入公式“=SUM(B2:B4*C2:C4)”，三键确认输入即可。（1）三键输入数组公式。（2）数组公式同时进行多个计算，可返回一个或多个结果。（3）多单元格数组公式需选中多个单元格进行输入，多单元格数组公式具有保护公式的作用。（4）数组公式可以完成复杂的中间运算得到最终想要的运算结果。1、行列数相同数组的运算2、数组与单一的数据的运算3、单列数组与单行数组的计算4、行数（或列数）相同的单行（或单列）数组与多行多列数组的计算4、行数（或列数）相同的单行（或单列）数组与多行多列数组的计算5、行、列数不相等的数组计算5、行、列数不相等的数组计算5、行、列数不相等的数组计算