excel数据分析高级应用-函数.pdf

重庆邮电大学经济管理学院 周玉敏 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】根据库存情况填写补充提示列的内容，标准 如下： ①库存数①库存数60 >60 充足充足 【例3.3】根据职工情况计算医疗付费的起付线，计算标准 如下： ①①所有职工，所有职工，若为“若为“住院住院”，”，起付线起付线则为则为13001300 ②② 在职职工，在职职工，““急诊急诊””或或““特殊门诊特殊门诊””，起付线，起付线20002000 ③退休职工，“③退休职工，“急诊急诊””或或““特殊门诊特殊门诊””，起付线，起付线25002500 IF(E3=“住院“,1300,IF(AND(D3=“在职“,OR(E3=“急诊“,E3=“特殊门诊“)),2000,2500)) 【例3.4】计算下表中的平均成绩，并给出成绩等 级，等级标准如下： ①60以下 不及格 ②[60,70),及格 ③[70,80),中 ④[80,90),良 ⑤ [90,100],优 IF(I3>=90,“IF(I3>=90,“优优“,IF(I3>=80,““,IF(I3>=80,“良良 “,IF(I3>=70,““,IF(I3>=70,“中中“,IF(I3>=60,““,IF(I3>=60,“及格及格“,““,“不及格不及格“))))“)))) 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】统计下表中每月出生的员工人数 Excel 2007还提供了一个多条件统计函数COUNTIFS，它可 以一次对多个不同区域进行不同条件的计数，其用法如下： COUNTIFS(range1， criteria1，range2， criteria2， ……，range127， criteria127) 【例3.8】有学生成绩表如下图所示统计其中总 分大于200，物理80分以下，地理70分以下，化 学65分以上的人数。

countifs(F3:F10,”>200” ,C3:C10,”>80”,D3:D10 ,”65”) countifs(F3:F10,”>200”,C3:C10,”>80”,D3:D10,”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_rangeSum_range为需要求和的单为需要求和的单 元格、区域或引用元格、区域或引用 RangeRange为用于条件判断为用于条件判断 的单元格区域的单元格区域 CriteriaCriteria是由数字、逻辑表是由数字、逻辑表 达式等组成的判定条件达式等组成的判定条件 【例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.133.13】】已知编号已知编号TZTZ- -JK003JK003，，根据A13:C18区域给出 的信息，查找该编号产品的价格 【【例例3.143.14】】利用利用vlookupvlookup的模糊查找，从表的模糊查找，从表1 1中查中查 找表找表2 2中的所得税率中的所得税率 （1）在I31单元格中输入如下的查询公式： =VLOOKUP(H31,$A$31:$B$38,2,TRUE) (2)将此公式向下角复制到将此公式向下角复制到I39，， 【【例例3.153.15】】下图中左边表格是处理成绩时的顺序，右边表 格是录入成绩时的顺序，请根据左边表格中的成绩查找右边 表格中每位同学的成绩。

=VLOOKUP(I3,$B$3:$E$52,4,FALSE) Match格式 ◦ Match ( x， r，f ) ◦ 其中x是要查找的数值，r可以是一个数组常量，或某列 （或行）连续的单元格区域，其中可能包含有要查找的 xf用于指定match的查找方式，它可以是-1，0或1 功能 ◦ Match(x，r，f)表示的意思是：在数组或连续的单元格 区域r中查找x，并返回x在r中的位置编号 ◦ 当f为0是，match进行精确查找，当f为1（或-1）时， match进行模糊查找 Match查找的方式查找的方式 取值取值 函函 数数 功功 能能 -1 r必须按降序排列，查找大于或等于必须按降序排列，查找大于或等于 x的最小数值的最小数值 0 r 不必排序，查找等于不必排序，查找等于x的第一个数值的第一个数值 1 r必须按升序排列，查找小于或等于必须按升序排列，查找小于或等于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.163.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 【【例例3.173.17】】怎样快速将七年级同学随机安排考场考试？ 【【例例3.173.17】】怎样快速将七年级同学随机安排考场考试？ 步骤一：用randbetween(1,800)函数产生一列1到800之间的随机数，复 制并选择性粘贴为数值，将随机数排序后如下图所示 【【例例3.173.17】】怎样快速将七年级同学随机安排考场考试？ 步骤二：根据随机数产生一列编号，如下图所示 【【例例3.173.17】】怎样快速将七年级同学随机安排考场考试？ 步骤三：删掉随机数一列，每个考场用不同的编号范围，再用 vlookup()函数查找编号生成考场学生数据 F4单元格填充公式：=VLOOKUP(E4,$A$3:$C$808,2,FALSE) 再向下填充公式即可。

常用字符函数 ◦ LEN(text) 计算text文本的长度 ◦ RIGHT(text,n) 该函数从text文本的右边取出n个字符 ◦ LEFT(text,n) 该函数从text文。