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

重庆邮电大学经济管理学院 周玉敏 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】统计下表中每月出生的员工人数 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 ,”70”,E3:E10,”65”) countifs(F