第2 篇 Excel 高级应用.ppt

４.财务函数 PMT 函数是基于固定利率及等额分期付款方式， 返回 贷款的每期付款额。 其完整的格式为: PMT（rate，nper， pv， fv， type） ５.闰年的计算 判断年份是否为闰年需要涉及 IF、 OR、 AND、 MOD 函数。 其中 IF 函数是比较常用的， OR 函数用来对一 组数据作或运算， AND 函数用来对一组数据作与运 算， 这两个函数都是返回逻辑值 TRUE 或者 FALSE。 MOD 函数用于返回两数相除的余数， 其完整格式为: MOD（number， divisor） 2.3 　 Excel 案例应用（三） 2.3.1 　 问题描述 国内某大型娱乐网站应广大网民的要求， 并为普及音乐和网络 知识， 丰富网络文化生活， 特举办网络歌手大赛。 本次大赛的 参赛选手通过语音聊天室进行网上演唱， 由专业评委和网友进 行打分， 评委打分占总分的 ７０％， 网友打分则占３０％。 经过一周时间的网上比赛， 网络歌手的参赛成绩已记录在网络 歌手大赛评分表中， 如图２‐３５所示。请你帮助大赛组委会 对各选手的参赛成绩进行计算和统计， 完成下列操作: （１）计算 Sheet１ 中参赛选手的“年龄”列。 （２）计算 Sheet１ 中的“评委比例分”、 “网友比例分”和“总分”等数据列。 （３）在 Sheet１ 中， 根据“总分”列对所有参赛选手计算“排名”列。 （４）将 Sheet１ 复制到 Sheet２， 并对 Sheet２进行筛选， 筛选条件为: “参赛地区”——温州市、 “性别” ——女、 “职业”——学生。 （５）根据 Sheet１， 在 Sheet３ 中新建数据透视表， 统计各赛区不同职业参赛选手的人数。 图 ２‐３５ 　网络歌手大赛评分表 2.3.2 　 解决思路 １.评分表中各数据列的计算 参赛选手的“年龄”列可以使用 Excel 公式来计算， 在公式中使用日期时间函数来计算年龄， 计算方法为今天日期与选手出生日期的年份之差再加 １。 当前日期可以使用TODAY 函数来获得， 两个日期的年份之差则可使用 DATEDIF 函数来计算。 “评委比例分”、 “网友比例分”和“总分”等数据列可以使用数组公式来计算。 “排名”列可以使用 RANK 函数来计算， 该函数可以计算出该选手的总分在所有参赛选手的总分中的排名。 ２.数据筛选 在 Sheet２ 中， 可以使用数据的自动筛选功能先找出 温州赛区的所有选手， 然后在其中找出“性别”为“女” 的选手， 最后在其中找出“职业”为“学生”的选手。 另外我们也可使用高级筛选， 在条件区域设置这三个 条件， 得到相同的结果。 ３.各赛区不同职业参赛选手的人数统计 使用数据的数据透视表和数据透视图功能， 在 Sheet３ 中新建一个数据透视表， 选择“参赛地区”作为行， 选 择“职业”作为列， 选择“选手编号”作为数据， 统计方 式设置为“计数”， 就可以在数据透视表中统计出各赛 区不同职业参赛选手的人数。 2.3.3 　 知识要点 １.Excel 公式与常用函数 运算符 单元格引用 值或字符串 函数及其参数 括号 ２.数据筛选 自动筛选 高级筛选 ３.数据透视表和数据透视图 2.3.5 　 总 　 结 利用自定义公式与常用函数自动计算部分数据列的值。 在本案例中使用了TODAY、 DATEDIF、 RANK 等常用函数和数组公式， 在学习过程中要经常归纳这些常用函数的使用方法， 总结出应用规律， 另外要深刻理解数组公式的含义， 学会在处理批量数据时使用数组公式。 利用数据的自动筛选功能快速地查找数据。 在学习过程中要熟练掌握自动筛选， 它可以帮助我们在大量数据中快速找到需要的数据。 利用数据透视表对数据进行概要统计。 在学习过程中， 可以试着改变行和列来查看数据源的不同汇总结果。 2.4 　 Excel 案例应用（四） 2.4.1 　 问题描述 某食品公司主要经营各类干货的零售和批发业务， 各 类干货均有零售指导价， 即销售单价。 批发价根据购 买数量的多少有相应的折扣。 该公司销售部使用 Excel 软件对销售情况进行管理， 每月将销售数据记录在销 售记录表中， 如图 ２‐４５ 所示。 请你帮助公司 销售部对各类干货的销售记录进行计算和统计， 完成 下列操作: （１）Sheet１ 中销售记录表的“销售单价”和“折扣”列进行自动填充。 （２）利用公式， 计算 Sheet１ 中销售记录表的“销售金额”列。 （３）统计各种商品的销售总数量和销售总金额， 将结果保存在 Sheet１ 中的“销售统计表”中。 （４）对 Sheet２ 中的销售记录表根据“销售地区”升序和“销售金额”降序排序。 （５）将 Sheet２ 复制到 Sheet３， 并对