EXCEL数据分析与主要函数.ppt

* 查找函数－MATCH2 * 查找函数－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。 * 应用举例：成绩表2中，我们在G27单元格中输入公式：=VLOOKUP(孙丹,B2:C21,2,FALSE)，确认后，单元格中即刻显示出该学生的语文成绩。 特别提醒：Lookup_value参见必须在Table_array区域的首列中；如果忽略Range_lookup参数，则Table_array的首列必须进行排序；在此函数的向导中，有关Range_lookup参数的用法是错误的。 VLOOKUP （续） * 其他函数－ISBLANK 此函数可以判断单元格是否为空。例如判断员工是否到岗：1）输入姓名和上班时间，如图75所示；2）判断其是否到岗，在单元格E3中输入以下公式：“=IF(ISBLANK(D3),请假,到岗)”。 * 其他函数－ 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的结果。 * 三、应用举例 利用函数进行等级评定 统计学生考试成绩 自动录入性别 根据身份证号提取出生日期 年龄统计 位次阈值统计 让Excel按人打出工资条 Word表格计算 * 利用函数进行等级评定 在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单元格的右下角，等鼠标指针变成黑色十字加号时，按住左键向右拖动到这列单元格的最后放手。 * * 利用函数进行等级评定(续) 也可以在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单元格的右下角，等鼠标指针变成黑色十字加号时，按住左键向右拖动到这列单元格的最后放手。 * * 统计学生考试成绩 先点击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)，回车所得即为及格率。 * 选中f23:f26单元格，拖动填充句柄向右填充公式至h26单元格，松开鼠标，各学科的统计数据就出来了。 至于各科分数段人数的统计，那得先选中f28:f35单元格，在编辑栏中输入公式：=FREQUENCY(F$3:F$22,$C$28:$C$35)。然后按下“Ctrl+Shift+Enter”快捷键，可以看到在公式的最外层加上了一对大括号。现在，我们就已经得到了语文学科各分数段人数了。在K列中的那些数字，就是我们统计各分数段时的分数分界点。 现在再选中f28:f35单元格，拖动其填充句柄向右至h列，那么，其它学科的分数段人数也立即显示在我们眼前了。 * 自动录入性别 在d3