Excel电子表格函数课件完整版.xls

函函数数的的应应用用 函数是预先定义好的表达式 它必须包含在公式中 每个函数都由函数名和参数组成 其中函数名表示将执行的操作 如求平均值函数AVERAGE 参数表示函数将作用的值的单元格地址 通常是一个单元格区域 如A2 B7单元格区域 也可以是更为复杂的内容 在公式中合理地使用函数 可以完成诸如求和 逻辑判断和财务分析等众多数据处理功能 1 必须以 号开头 2 函数名后一定要有圆括号 3 函数中的参数多于一个时要用 隔开 每个函数都由函数名和参数组成 其中函数名表示将执行的操作 如求平均值函数AVERAGE 参数表示函数将作用的值的单元格地址 通常是一个单元格区域 如A2 B7单元格区域 也可以是更为复杂的内容 在公式中合理地使用函数 可以完成诸如求和 逻辑判断和财务分析等众多数据处理功能 常常用用函函数数 数学函数 所得的值为数值型 文本无效 1 SUM 返回所有数的和 2 AVERAGE 返回所有数的平均值 3 COUNT 统计数字的个数 注 该函数无法统计文本的个数 4 COUNTIF 对指定范围按指定条件统计个数 5 MAX 返回所有数中值最大的数 6 MIN 返回所有数中值最小的数 6 Rank 返回指定数值在数列中的排名 8 Product 返回所有数的积 9 round 返回按指定位数进行四舍五入的数值 10 ABS 返回数字的绝对值 11 IF 根据测试条件是否成立 结果为TRUE或FALSE 输出相应的结果 格式 IF 测试条件 满足条件时的输出结果 不满足条件时的输出结果 天南师大外语系 2007 级成绩表 制表日期01 Dec 07 学号姓名专业语文英语军事理论 20063101001邓家星材料物理758571 20063101002胡文晓材料物理829982 20063101003郭力材料物理787771 20063101014黄亮科学教育636558 20063101020胡建龙通信工程706472 20063101021陈焕恒通信工程779245 20063101022陈伟奇通信工程707192 20063101023林彩华通信工程939084 20063101024林稚超通信工程467357 20063101025蔡红莹通信工程576198 统计右侧各项 各科最高分 各科最低分 总人数 各科及格人数 材料物理 专业人数 科学教育 专业人数 通信工程 专业人数 任务 1 用函数SUM Average Round Rank计算 一年级成绩表 中的 总分 平均分 平均分四舍五入 以及 名次 2 用函数Max Min各科 最高分 最低分 3 用函数Count计算一班学生 总人数 4 用函数Countif计算各科及格人数 5 用函数计算 材料物理 科学教育 以及 通信工程 专业人数 员员工工工工资资表表 工工号号姓姓名名出出生生年年月月部部门门工工作作年年限限 基基本本工工资资 001何军1986年8月7日生产部5 2 000 00 002赵宝玉1968年6月4日行政部2525 2 300 00 003郑淑贤1972年10月9日生产部20 3 200 00 004孙娜1988年9月12日销售部3 2 800 00 005曾丝华1979年3月2日销售部14 2 500 00 006罗远方1977年7月10日生产部18 3 000 00 007何湘萍1982年9月1日行政部20 2 700 00 008黄莉1965年1月8日行政部3030 2 200 00 009刘伟良1987年5月6日生产部6 2 500 00 010张翠华1961年6月18日销售部3232 3 500 00 011刘雅诗1975年10月5日行政部19 2 500 00 012林晓旋1985年5月13日行政部6 3 000 00 013刘泽标1983年7月5日销售部8 2 600 00 014廖玉嫦1975年5月7日生产部2121 3 200 00 015李立聪1980年6月3日销售部9 3 500 00 员员工工人人数数各各项项平平均均数数 奖奖金金最最多多 津津贴贴最最少少 任务 1 在 员工工资表 中计算 津贴 列的数据 津贴 工作年限 津贴标准 2 用函数计算每位员工的总工资 总工资 基本工资 奖金 津贴 3 用函数计算基本工资 奖金 津贴的平均值 四舍五入到一位小数 4 用函数计算奖金最多 津贴最少以及津贴最大差值 5 用函数计算津贴最大差值 6 用函数计算员工工资的排名 7 用函数根据员工的总工资评定 工资等级 分两个等级 总工资 大于等于5000为高 总工资 小于5000为低 8 用函数计算 员工人数 以及 工资等级 为高的员工人数 计算机平均分 平均分 四舍五入总分名次 70 92 70 85 88 79 87 76 74 93 常常用用函函数数 IF 测试条件 满足条件时的输出结果 不满足条件时的输出结果 津贴标准50 奖奖金金津津贴贴总总工工资资排排名名工工资资等等级级 1 500 00 3 505 00 1 200 00 3 525 00 1 300 00 4 520 00 800 00 3 603 00 1 800 00 4 314 00 1 500 00 4 518 00 1 300 00 4 020 00 1 200 00 3 430 00 800 00 3 306 00 2 000 00 5 532 00 1 000 00 3 519 00 1 200 00 4 206 00 1 200 00 3 808 00 1 500 00 4 721 00 1 000 00 4 509 00 等级高的人数 津津贴贴最最大大差差值值 1 用函数SUM Average Round Rank计算 一年级成绩表 中的 总分 平均分 平均分四舍五入 以及 名次 员员工工工工资资表表 7 用函数根据员工的总工资评定 工资等级 分两个等级 总工资 大于等于5000为高 总工资 小于5000为低 文文本本函函数数 所得的值是文本型 1 LEFT 文字串 长度 截取文字串左边指定长度的文字 2 RIGHT 文字串 长度 截取文字串右边指定长度的文字 3 MID 文字串 开始位置 长度 从开始位置截取文字串指定长度的文字 4 LEN 文字串 求出文字串的长度 5 CONCATENATE 文字串1 文字串2 将所有文字串连接成一个文字串 6 Text 文字串 数值格式 将指定单元格文字串转换为特定格式 text value format text dbNum1 0 text value format text 0000 00 00 注 1 以上函数中的文字串为常数时 必须加上英文状态下的引号 2 以上函数名后若加上字母 B 表示对文字串按字节进行处理 学号姓名学业成绩入学年份专业代号专业学号专业年级 20100811吴春旺553 20051101普希金471 20000102赵本山364 19950601张壹壹689 19971532小沈阳467 20121212苗粟587 20032501钟无艳693 姓名身份证号出生日期年龄大写出生日期 杨19860817壹玖捌陆零捌壹柒 柴梦邵云徐梦黄蔡潭周立华阿陈伟范张连5 CONCATENATE 文字串1 文字串2 将所有文字串连接成一个文字串 日日期期函函数数 1 year 返回指定数值的年份 2 Now 返回当前日期时间 3 Today 返回当前日期 4 Datedif 返回两个日期间的年 月 日间隔数 DATEDIF start date end date unit 姓名身份证号出生年份年龄出生天数 杨星3607221986081727111986 08 172810307 柴梦邵云徐梦黄蔡潭周立华阿陈伟范张连当前年月日 2014 11 5 IF函数 根据测试条件是否成立 结果为TRUE或FALSE 输出相应的结果 格式 IF 测试条件 满足条件时的输出结果 不满足条件时的输出结果 1 用IF 函数从性别代号中获取每个学生的性别 1 男 2 女 2 用IF 函数从专业代号中获取每个学生的专业 1 计算机 2 英语 3 电子 3 用IF 函数从学业成绩中获取每个学生的成绩等级 学业成绩在85分以上的为 优秀 在85分以下 75分以上的为 良好 75分以下 60分以上为 及格 60分以下为 不及格 4 用IF 函数从成绩等级中获取每个学生的奖学金 优秀的为500 良好的为300 其余为空 学号姓名性别代号学业成绩专业代号性别专业成绩等级 952034王小凡2342 961053张国有1891 962073李经1952 951017王国兵1761 952067顾为民1902 963112张采丽2893 961092王学友1811 973167张在中1673 971095李以为1721 要求 用IF 函数求出职称等级 高工 的等级为 高级职称 工程师 的等级为 中级职称 助工 的等级为 初级职称 编号姓名性别年龄职称基本工资职称等级 100001李小玲女35工程师469 100002王民男43工程师460 100003张强男28助工380 100004张可安男54工程师512 100005武艺男52高工858 100006黄河男25助工320 100007黄莲花女27助工368 100008黄山男43高工690 100009王永恒男36工程师578 100010白杨柳女50高工750 100011杨阳女34工程师520 要求 将第一列的宽调整为12 根据 部门代码 用IF函数填入部门名称 其中部门代码 A02 表示 技术部 部门代码 B01 表示 生产部 部门代码 B03 表示 销售部 完成后另存为AC XLS 姓名部门代码基本工资部门名称奖金扣款 罗子明B011300 50750 00635 00 张广萍A021435 00823 00710 00 胡小亮B011280 50652 00653 00 李金B011535 70805 00732 00 何利A021556 70711 00580 00 朱广强A021800 50664 00658 00 宋小燕B031250 30580 00465 20 刘黎明B031630 50660 00488 00 马明君A021260 80725 00550 60 周军军B031700 40700 00650 70 罗芝B011560 00750 00635 00 张罗A021200 00823 00710 20 胡子B011345 00652 00653 00 李进B011535 70805 00732 50 何其A021556 70711 00580 30 朱起A021650 00664 00465 30 宋盐B031250 00580 00488 00 得名B031630 50660 00550 60 马达A021260 80725 00650 70 周密B031654 00700 00558 60 奖学金 2 用IF 函数从专业代号中获取每个学生的专业 1 计算机 2 英语 3 电子 3 用IF 函数从学业成绩中获取每个学生的成绩等级 学业成绩在85分以上的为 优秀 在85分以下 75分以上的为 良好 75分以下 60分以上为 及格 60分以下为 不及格 4 用IF 函数从成绩等级中获取每个学生的奖学金 优秀的为500 良好的为300 其余为空 要求 将第一列的宽调整为12 根据 部门代码 用IF函数填入部门名称 其中部门代码 A02 表示 技术部 部门代码 B01 表示 生产部 部门代码 B03 表示 销售部 完成后另存为AC XLS 日日期期函函数数 办办公公用用品品价价格格表表 序号品名单位 进价售价Vlookup函数 1 胶圈卷0 50 8Excel中的一个纵向查找函数 是按列查找 最终返回该列所需查询列序所对应的值 2 文件夹只4 55 2 3 档案夹只1 82 5共4个参数 4 笔记本本6 87lookup value 需查找的值 5 复印纸包3254table array 查找范围 6 传真纸包15 518 8col index num 查找数据在范围中的第几列 7 电脑打印纸 包2629range lookup0或false为精确查找 一模一样 8 回形针盒22 21或true为模糊查找 返回小于 lookup value 的最大数值 9 订书机个1517 10 起钉器个5 57 2注意 需查找的值必须在查找范围的首列 11 打孔机个3 54 12 美工刀把3 65 3 例 要求 参照办公用品价格表在下面的表格中填入相应数值 销销售售记记录录表表 日日期期分分店店品品名名数数量量单单位位进进价价售售价价销销售售额额毛毛利利润润 2008 12 2 东山分店文件夹300 只 2008 12 2 东山分店传真纸25 2008 12 2 东山分店打孔机30 2008 12 2 东山分店美工刀42 2008 12 2 东山分店复印纸20 2008 12 2 东山分店档案夹120 2008 12 2 天河分店传真纸32 2008 12 2 天河分店起钉器45 2008 12 2 天河分店档案夹101 2008 12 2 天河分店复印纸56 2008 12 2 天河分店美工刀12 2008 12 2 天河分店笔记本65 练练习习题题 奖学金发放标准 总分参照分比例 400以下00 400 5004000 5 500 6005001 600以上6002 要求 1 利用VOOLUP函数查找各个学生奖学金发放比例 2 求奖学金 奖学金 总分 发放比例 姓名总分发放比例 奖学金 吴春旺5531553 普希金471 赵本山364 张壹壹689 小沈阳467 苗粟587 钟无艳693 周全534 郭大侠498 黄蓉428 林志玲449 李玖哲504 Excel中的一个纵向查找函数 是按列查找 最终返回该列所需查询列序所对应的值 需查找的值 查找数据在范围中的第几列 0或false为精确查找 一模一样 1或true为模糊查找 返回小于 lookup value 的最大数值 需查找的值必须在查找范围的首列 大连xx公司员工年龄分布情况统计表 员工姓名出生日期年龄频率分布FREQUENCY 计算数据落在各个区间段的个数 Annie1972年10月25日28FREQUENCY data array bins array Jacky1974年12月25日26Data array 是一个数组或对一组数值的引用 即你要计算个数的那列数据 Micheal1970年5月25日31Bins array 是一个区间数组或对区间的引用 该区间用于对 data array 中的数值进行分组 即条件分组表达式 Henry1980年3月12日21 David1956年12月31日44统计小小于于数数组组区区间间的出现个数 Rebecca1968年7月8日33计算结果时也应选中区间 输出结果时要批量输出 按住 Ctrl Shift Enter Luxia1979年8月25日22 Brade1964年1月5日37 25岁以下225 26 30岁230 31 35岁235 36 40岁140 40岁以上1 班别学号姓名类别 计算 机应 用基 础 解剖 组胚 学 英语 职业 生涯 规划 大学 生心 理健 康教 育 体育 思想 道德 修养 与法 律基 础 12助专1班12020101高美凤9093 6 85 68 87 48690 4 74 16 12助专1班12020102罗洋萍92 178 876 888 482 482 4 70 76 12助专1班12020103罗嘉怡92 891 4 88 44 84 886 87681 36 12助专1班12020104吴 静89 390 4 83 44 85 286 883 2 81 28 12助专1班12020105蔡 姗9095 2 89 32 87 484 482 4 81 88 12助专1班12020106黄贵玉88 689 4 73 16 86 68589 6 76 68 12助专1班12020107胡海燕83 4 71 728583 879 6 71 28 12助专1班12020108蔡燕玲86 59390 92 86 893 292 8 80 16 12助专1班12020109刘玉妹86 591 8 90 48 85 486 28076 2 12助专1班12020110李小清92 194 892 686 885 482 8 77 88 12助专1班12020111陈佩珊9091 89286 286 292 482 4 12助专1班12020112曹朝吉909692 284 682 48880 88 12助专1班12020113袁慧敏7689 4 94 56 85 486 279 2 81 48 12助专1班12020114方 婉95 687 679 886 285 871 2 76 44 12助专1班12020115曾静娴89 387 2 67 44 85 686 271 2 87 36 12助专1班12020116郑罗琼59 989 8 89 36 86 6777861 2 12助专1班12020117陈碧宁95 690 8 86 32 84 886 874 8 82 96 12助专1班12020118黄 冬86 578 6 79 568584 880 8 76 68 12助专1班12020119曾洁茹93 59069 248581 28877 64 12助专1班12020120吴康佳9089 685 487 686 891 2 83 88 12助专1班12020121徐小灵59 970 6 78 68 88 884 289 270 2 12助专1班12020122邹柳丹93 594 2 69 92 85 283 889 6 74 52 12助专1班12020123练文婷95 68070 72 86 686 289 6 75 76 12助专1班12020124林晓瑞95 686 288 887 288 276 4 69 76 12助专1班12020125许娇媚95 691 4 71 64 85 686 27274 88 12助专1班12020126罗露男93 593 2 90 64858389 2 76 76 12助专1班12020127张 媛79 590 273 885 8837776 72 12助专1班12020128叶晓婷93 589 8 87 728586 27873 8 12助专1班12020129潘荣云66 982 2 88 44 85 685 673 676 6 12助专1班12020130黄 薇76838685 48383 672 6 12助专1班12020131冯菀霞67 675 2 87 52 84 884 277 6 66 48 12助专1班12020132陈晓琳88 68985 728883 67572 48 12助专1班12020133李珏盈86 591 2 93 96 86 2867483 8 12助专1班12020134林依媛93 589 2 77 36 87 283 677 8 77 76 12助专1班12020135邓 菁78 891 4 78 08 83 886 876 8 72 68 12助专1班12020136潘丽媛79 585 6 76 52858675 2 75 68 12助专1班12020137田莉琼838688 284 483 683 876 2 12助专1班12020138陈佩珊93 5949386 886 677 4 78 36 12助专1班12020139陈冰纯79 595 2 92 88 84 883 276 8 76 24 12助专1班12020140吴康佩86 59277 12 85 685 493 6 79 32 12助专1班12020141陈洁汝9093 8 86 128581 886 4 77 84 12助专1班12020142袁艳萍91 486 6 91 44 86 283 877 4 77 16 12助专1班12020143刘嘉雯91 486 8 77 64 86 685 489 2 78 44 12助专1班12020144谢晓芳79 585 2 83 72 86 485 672 2 76 52 12助专1班12020145李楚英92 18986 32 85 285 688 482 8 12助专1班12020146钟美诚93 568 2 86 128883 680 6 79 92 要求 用频率分布FREQUENCY求出平均分各个分数段的学生人数 条件区间 符合人数 不及格60分以下59 990 及格60 70分69 990 一般70 80分79 9918 良好80 90分89 9928 优秀90分以上0 护理 礼仪 与人 际沟 通 生物 化学 生理 学 病理 学基 础 药理 学 医护 心理 学 英语 健康 评估 基础 护理 学 体育 病原 生物 与免 疫学 毛泽 东概 论 平均分 92 490 2 92 36 79 8 80 12 81 688 283 2 90 649382 4 90 44 86 92632 8784 8836553 280 87071 2 79 768476 48 80 64 78 29158 88 891 6 80 64 69 2 72 72 81 8 85 567883 488682 4 89 08 83 73053 9090 2 78 087068 68 84 2 81 24 77 6 80 689680 88 84 52 83 24737 92 28192 92 80 4 80 68 83 277 475 2 87 368682 32 80 72 84 73684 9383 27768 6 68 28 82 4 70 12 75 2 80 769584 685 2 81 70526 8578 4 75 88 61 6 54 04 83 265 864 8 80 488471 16 79 92 75 50444 91 486 4 89 32 94 2 76 44 84 29084 892 29278 68 87 76 87 93579 86 487 2 84 32 61 66384 4 83 128280 129278 68482 28105 92 287 29180 2 74 52 84 6 85 56 79 2 87 968874 88 85 48 85 43053 92 286 6 79 24 64 652 684 4 88 56 82 4 89 927476 48 88 76 83 19789 91 285 4 92 28 86 6 91 04 75 2 90 56 81 6 89 689678 6 89 1287 44 8883 462 66660 44 82 4 93 886872 048576 47979 44211 88 68477 68 74 4 61 448380 84 82 8 84 447979 44 85 68 81 26105 87 28568 92695482 8 80 44 78 8 82 248480 88 87 08 79 71895 86 685 6 73 68 68 2 52 84858070 4 81 088174 96 82 1677 02 91 886 265 668 6 61 76848076 8 72 288780 66 82 48 81 01368 89 88275 286665 12 85 2 80 92 73 2 82 048074 88 89 4 79 77789 86 471 665 464 25683 2 78 32 78 4 81 848465 92 79 52 77 86211 9286 6 81 086784 6 87 72 85 2 86 649582 32 89 24 86 21556 89 290 2 64 64 60 2 56 24 90 2 83 16 65 279 49871 248877 75053 89 286 892 473 8 84 56 83 467 884 8 85 929581 64 81 92 84 10421 88 486 2 84 04736083 6 79 64 77 686 28575 64 87 68 81 65684 90 287 2 92 64 75 2 79 84 81 6 79 76 80 483 68276 48 78 12 83 11579 84 89382 88 64 465 481 6 74 287481 367571 24 84 28 79 45053 85 489 486 881 6 73 88 80 276 286 485 28877 92 85 88 84 64105 计算数据落在各个区间段的个数 FREQUENCY data array bins array Data array 是一个数组或对一组数值的引用 即你要计算个数的那列数据 Bins array 是一个区间数组或对区间的引用 该区间用于对 data array 中的数值进行分组 即条件分组表达式 计算结果时也应选中区间 输出结果时要批量输出 按住 Ctrl Shift Enter 86 48785 768282 480 8 80 56 83 6 83 848166 68 86 08 81 69158 89 683 8 84 08 78 8728682 282 4 89 648179 36 89 4 83 80526 87 886 6 75 48 61 6 68 56808071 6 66 289268 96 85 8 78 08526 87 480 2 67 48 60 8 52 12 83 8806481 248574 2 83 52 77 33474 87 880 4 69 92 62 8 63 04828061 2 86 568471 92 78 88 76 41684 8681 4 83 447272 68 78 2 73 08 81 2 81 367574 2 75 96 79 83789 86 493 4 83 48 67 8 79 52 84 2807884 148072 76 82 96 82 85895 9284 86066 8 78 32 83 4 78 16 87 6 79 029069 72 82 44 80 98316 92 684 4 72 64 68 4 66 76 83 6 71 24 79 2 83 368064 482 6 78 81895 8883 6 73 04 63 2 62 96 78 2 70 08 70 885 28672 68 80 24 77 76316 85 487 2 82 64 84 4 74 848077 873 2 81 92827279 36 81 36632 92 286 2 93 24 72 6 91 16 84 290 486 4 87 727574 12 82 96 85 57158 88 281 6 86 28 71 4 72 88 82 479 86878 647279 36 75 88 80 26632 91 881 673 462 885 884 478 8 85 728881 64 85 6 83 28333 88 667 8 74 92 62 8 63 56 83 4 76 44 67 6 85 489480 96 86 68 80 69474 9287 2 71 32 63 46577 4 84 967679 748267 52 80 52 80 05579 87 887 2 74 04 70 4 61 08 84 67169 2 73 528372 68 82 32 79 59579 91 890 2 76 64 62 6 71 92 84 6 83 36 73 6 89 648574 96 76 2 80 50842 87 883 2 70 24 60 4 61 08 84 6746678 348573 22 80 6 79 67895 88 480 6 72 12 81 46080 872 275 6 77 568566 6 80 92 79 00737 20132013年年8 8月月百百城城新新建建住住宅宅价价格格指指数数 广广东东十十城城 P221 城城市市环环比比涨涨跌跌样样本本平平均均价价格格样样本本价价格格中中位位数数 深圳1 34 2825622000 广州1 68 1714915000 珠海0 43 1257111535 东莞1 17 92708500 佛山1 94 92697780 汕头 0 72 85128500 湛江1 55 71237313 惠州1 99 65015800 中山0 27 63385500 江门 1 14 61706175 PMT函数 即年金函数 基于固定利率及等额分期付款方式 返回贷款的每期付款额 PMT rate nper pv fv type 一套房100平方 每平方6000 首期3成 货款20年 rate各期利率 nper总期数 pv现值房子总价 600000 fv未来值 余值 首期 180000 type付款时间 1代表期初 0代表期末 贷款总额 420000 商业贷款月供 3 143 78 公积金贷款月供 2 657 13 题目 假设以深圳中位数价格购买一套120平方的住宅 首期4成 贷款15年 分别计算每期商业贷款月供额和每期公积金贷款月供额 房价中位数平方数首付比例贷款年限 商业贷款月供 公积金贷款月供 贷款利率存款利率 利率项目年利率 利率项目 六个月以内 含6个月 贷款5 60 活期存款 六个月至一年 含1年 贷款6 三个月定期存款 一至三年 含3年 贷款6 15 半年定期存款 三至五年 含5年 贷款6 40 一年定期存款 五年以上贷款6 55 二年定期存款 利率项目年利率 个人住房公积金贷款五年以下 含五年 4 个人住房公积金贷款五年以上4 50 共支付利息 共支付利息 一套房100平方 每平方6000 首期3成 货款20年 题目 假设以深圳中位数价格购买一套120平方的住宅 首期4成 贷款15年 分别计算每期商业贷款月供额和每期公积金贷款月供额 年利率 0 35 2 6 2 8 3 3 75 存款利率 学号姓名性别出生日期籍贯操行分学业成绩 成绩等级 2 张丽华女1971 2 24 广东75 089 0良好 4 李小建男1973 5 13 湖南90 090 3优秀 5 王文兵男1972 9 16 湖南85 084 8良好 6 王小霞女1971 7 16 广西95 078 5良好 7 刘冬梅女1972 4 19 湖南65 086 3良好 8 李新理男1973 1 23 湖北75 079 5良好 10 王社景男1972 5 12 湖北85 082 3良好 11 叶秀兰女1974 10 3 广东90 078 0良好 13 洪文友男1972 7 19 湖南95 090 0优秀 14 施参杰男1971 3 28 广东80 054 0一般 1