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

重庆邮电大学经济管理学院周玉敏
1.认识Excel
2.数据输入和表格设计
3.函数的使用
4.图表的制作
5.数据分析

1.常用函数使用范例
◦条件函数：If()
◦统计函数：countif()、countifs()sumif()sumifs()
◦查找函数：Vlookup()、Index()、Match()
◦随机函数：rand()
◦字符函数：len()、right()、left()、mid()
2.数组公式的应用

=IF(E2=0,D2,"")
【例3.1】将左图所示的表格，转换成右图所示的表格，与
户主的关系代码为0表示是户主。
IF(Logical,Value_if_true,Value_if_false)

Logical代表逻辑
判断表达式

Value_if_true表示当判断条件为Value_if_false表示当判断条件为
逻辑“真（TRUE）”时的显示逻辑“假（FALSE）”时的显示
内容，如果忽略返回“TRUE”内容，如果忽略返回“FALSE”。
【例3.2】根据库存情况填写补充提示列的内容，标准
如下：
①库存数<=30补货③50=<库存数<=60下单
②30<库存数<50准备④库存数>60充足
【例3.3】根据职工情况计算医疗付费的起付线，计算标准
如下：
①所有职工，若为“住院”，起付线则为1300
②在职职工，“急诊”或“特殊门诊”，起付线2000
③退休职工，“急诊”或“特殊门诊”，起付线2500

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>=80,"良",IF(I3>=70,"中",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】统计下表中每月出生的员工人数
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”)

地理成绩小于70
化学成绩大于65
总成绩大于200

物理成绩大于80
【例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】按下图中的要求进行条件求和。

条件区域求和区域

条件求