《表格处理及数据分析高级应用课课件.ppt》由会员分享，可在线阅读，更多相关《表格处理及数据分析高级应用课课件.ppt（94页珍藏版）》请在三一办公上搜索。
1、Office 2010教程（进阶篇）,本项目旨在帮助用户认识Excel 2010在日常办公领域中广泛应用，以及Excel独具特色公式与函数的实际应用，掌握表格设计的基本理念，为设计“好表格”做好充分准备。,2,项目二 数据管理及分析高级应用,Excel 2010公式与函数的高级应用,使用Excel 2010高级功能,课堂任务一,课堂任务二,操作一 函数应用基础操作二 函数应用案例操作三 收益表的设计制作,操作一 Excel与其他程序共享数据操作二 Excel 2010的数据安全,3,项目二 数据管理及分析高级应用,Excel报表设计理念,课堂任务三,操作一 设计思路不可缺少操作二 表格元素一个
2、都不能少操作三 表格设计案例导论中公司年度销售报表,本章共6课时主讲人：,4,项目二 数据管理及分析高级应用,Excel 2010公式与函数的高级应用,课堂任务一,操作一 函数应用基础,1、认识函数2、函数参数,3、常用函数学习,5,1、算账理财,2、人事资料分析3、信息统计4、日常办公管理计算,操作二 函数应用案例,Excel 2010公式与函数的高级应用,课堂任务一,6,1、认识收益表,2、收益表设计制作,操作三 收益表的制作,什么是函数？,1、认识函数,7,函数就是预先定义，执行计算、分析等处理数据任务的特殊公式。,1、认识函数,8,其实很简单！,不同函数参数不同！有常量、引用、逻辑值、
3、数组以及单元格名称。,2、函数参数,9,种类多样，但目标一致：高效地分析和管理数据。,2、函数种类,10,条件求和运算,2、常用函数介绍,11,四舍五入运算,2、常用函数介绍,12,IF 函数检查所指定的条件为“真”还是“假”。如果为“真”，则出现一种情况；如果为“假”，则出现另一种情况。例如，如果您使用 IF 函数查看支出金额超出预算还是在预算内，则结果为“真”表示“预算内”，而结果为“假”则表示“预算外”。,2、常用函数介绍,13,什么是IF？有何功能？,IF函数（图中“总计”部分，换成=IF(SUM(C6:F6),SUM(C6:F6),“”)可以避免0值单元格。,2、常用函数介绍,14,
4、这样操作，会出现0值单元格,Vlookup查找函数,2、常用函数介绍,15,查找ID为38的员工姓名！如何操作？,假定学校订购多种教材，现需统计Office教程的数量，你知道哪个函数能解决吗？Countif函数可以解决，该函数为条件计数功能，可以设置条件为“教材=Office”。=Countif（A12:A34,Office”,A12:A34）,IF函数最多可以嵌套多少层？最多可以嵌套六十四层。,16,日常生活理财可能在困扰着很多人，别急，Excel来帮你！,1、算账理财,17,“零存整取”是工薪阶层常用的投资方式，这就需要计算该项投资的未来值，从而决定是否选择某种储蓄方式。,1、算账理财零存
5、整取,18,假设：妻子新建一个账户每月底存入300元，年利2.1%(即月息0.00175)，连续存款5年。如果丈夫也有“零存整取”账户，每月初存入200元，年利1.28%(即月息0.001667)，连续存款3年。问：在妻子、丈夫分别在5年、3年后，投资的未来值是多少？,1、算账理财零存整取,19,1、算账理财零存整取,20,1、算账理财购房还贷,21,买房一定要“心知肚明”！,1、算账理财购房还贷,22,如今贷款购买住房进行消费的家庭越来越多，计算贷款的月偿还金额是决策的重要依据！,1、算账理财购房还贷,23,单位小王即将结婚，正筹划购置新房。但面积太大，经济承受不起（月供压力），太小，又担心
6、不合适，现正不知如何是好假设银行贷款年利为4.1%(即月息0.00342)，预计的还贷时间为10年，贷款金额为10万元，且每月底还贷。,1、算账理财购房还贷,24,1、算账理财保险投资,25,我投资的险种不知收益率有多大？,1、算账理财保险投资,26,中国平安保险公司于2011年新开办了一种平安保险，具体办法是一次性缴费12000元，保险期限为20年。如果保险期限内没有出险，每年返还1000元。请问：在没有出险的情况下，它与现在的银行利率相比，这种保险的收益率如何？,1、算账理财保险投资,27,中国平安保险公司于2011年新开办了一种平安保险，具体办法是一次性缴费12000元，保险期限为20年
7、。如果保险期限内没有出险，每年返还1000元。请问：在没有出险的情况下，它与现在的银行利率相比，这种保险的收益率如何？,2、人事资料分析,28,人事资料管理中少不了员工信息的管理，在其中需要对员工的全面信息进行管理，包括编号、员工姓名、身份证号码、参加工作时间，出生年月、性别，工龄信息等。,手动输入信息,员工信息,函数提取信息,2、人事资料分析,29,手动输入信息,员工信息,函数提取信息,自动从身份证号码中提取出生年月、性别信息。自动从参加工作时间中提取工龄信息。,2、人事资料分析,30,自动从身份证号码中提取“性别”信息。,2、人事资料分析,31,自动从身份证号码中提取“出生年月”信息。,2
8、、人事资料分析,32,自动从参加工作时间中计算“工作年限”,3、信息统计,33,使用Excel管理人事信息，具有无须编程、简便易行的特点,尝试使用函数填充后续信息,D5单元格中的输入公式：=IF(LEN(C5)=15,IF(MOD(MID(C5,15,1),2)=1,男,女),IF(MOD(MID(C5,17,1),2)=1,男,女),34,性别输入,3、信息统计,3、信息统计,35,出生日期输入,E5单元格中的输入公式：=IF(LEN(C5)=15,CONCATENATE(19,MID(C5,7,2),年,MID(C5,9,2),月,MID(C5,11,2),日),CONCCTENCTE(M
9、ID(C5,7,4),年,MID(C5,11,2),月,MID(C5,13,2)”日),3、信息统计,36,J5单元格中的输入公式：=FREQUENCY(YEAR(TODAY()-YEAR(E5:E14),I5:I9),年龄统计,3、信息统计,37,名次值统计,4、日常办公管理计算,38,企业、学校等单位均存在许多管理计算问题，例如计算一个学期有几个授课日、企业在多少个工作日之后交货等等。下面介绍有关该问题的几种解决方法。,4、日常办公管理计算授课天数统计,39,假设：新学期从2011年9月1日开始到2012年1月15日结束，希望知道本学期有多少个授课日，也就是排除双休日和国家法定假日外的授课
10、工作日。,4、日常办公管理计算折旧值计算,40,假设：某单位有一批2008年购进原价5600元/每台的电脑，预计使用寿命6年，寿命期结束时的资产残值约为1000元，求使用第二年内的折旧值。,假定你于2003年4月24日加入公司，现需计算你的工龄，应该如何计算，采用什么函数可以解决？可以采用datedif函数计算两个日期之间的差,若需要求出班级课程测试成绩中的最大值和最小值的函数？large函数和small函数,41,1、认识收益表,42,收益表又称损益表，也是公司最主要的综合财务报表之一。损益表的格式有单步式和多步式两种。多步式损益表是通过四个步骤计算出公司的净收益：1.认销售收入中减去销售成
11、本，得出销售毛利；2.认销售毛利中减去营业费用，得出营业纯利润；3.营业纯利润营业收入，减去营业外支出，得出税前盈利；4.从税前盈利中减去应付所得税，得出税后盈利，即公司净收益。,2、收益表的设计制作,43,你知道收益表中的“利润总额”与“净利润”之间有何关系吗？净利润=利润总额-所得税，即为税后利润。,收益表在企业报表中的作用是什么？利润表的作用表现为以下几个方面：(1)评价和预测企业的经营成果和获利能力,为投资决策提供依据。经营成果是一个绝对值指标，可以反映企业财富增长的规模。获利能力是一个相对值指标，它指企业运用一定经济资源获取经营成果的能力，经济资源可以是资产总额、净资产，可以是资产的
12、耗费，还可以是投入的人力。因而衡量获利能力的指标包括资产收益率、净资产（税后）收益率、成本收益率以及人均实现收益等指标。经营成果的信息直接由利润表反映，而获利能力的信息除利润表外，还要借助与其它会计报表和注释附表才能得到。根据利润表所提供的经营成果信息，股东和管理部门可评价和预测企业的获利能力，对是否投资或追加投资、投向何处、投资多少等做出决策。(2)评价和预测企业的偿债能力，为筹资决策提供依据。偿债能力指企业以资产清偿债务的能力。企业的偿债能力不仅取决于资产的流动性和资产结构，也取决于获利能力。获利能力不强甚至亏损的企业，通常其偿债能力不会很强。债权人通过分析和比较利润表的有关信息，可以评价
13、和预测企业的偿债能力，尤其是长期偿债能力，对是否继续向企业提供信贷做出决策。财务部门通过分析和比较利润表的有关信息和偿债能力可以对筹资的方案和资本结构以及财务杠杆的运用做出决策。(3)企业管理人员可根据利润表披露的经营成果做出经营决策。企业管理人员比较和分析利润表中各种构成因素，可知悉各项收入、成本费用与收益之间的消长趋势，发现各方面工作中存在的问题，做出合理的经营决策。(4)评价和考核管理人员的绩效董事会和股东从利润表所反映的收入、成本费用与收益的信息可以评价管理层的业绩，为考核和奖励管理人员做出合理的决策。,44,认识函数的参数及语法规则,认识函数的种类,45,掌握常用函数的应用及其案例,
14、掌握收益表的设计和计算,本节内容,Excel与其他程序共享数据,Excel 2010的数据安全策略,46,下节内容,使用Excel 2010高级功能,课堂任务二,操作一 Excel与其他程序共享数据,1、了解数据共享2、复制和粘贴3、将数据从Excel复制到Word文档中4、在Word文档中嵌入Excel工作簿,操作二 Excel 2010的数据安全,1、保护类型2、工作表保护3、解锁单元格4、工作簿保护,47,1、了解数据共享,21世纪是计算机网络信息时代，在信息时代中信息都是可以共享的。Excel也不例外，可以与其他应用程序间进行方便友好的共享数据信息。,1、了解数据共享,共享,使用对象嵌
15、入,利用链接,使用剪贴板,2、复制和粘贴,直接复制和粘贴创建一个静态的副本,3、将数据从Excel复制到Word文档中,粘贴静态信息,粘贴链接,嵌入对象,操作方式,3、将数据从Excel复制到Word文档中,52,粘贴静态信息,3、将数据从Excel复制到Word文档中,53,什么情况下出现该对话框？,3、将数据从Excel复制到Word文档中,54,什么时候使用“粘贴链接”呢？,3、将数据从Excel复制到Word文档中,55,在需要保持文档间的联系时，当Excel表格被修改后，Word中粘贴的信息也能相应更新，此时会使用到“粘贴链接”操作。,3、将数据从Excel复制到Word文档中,56
16、,创建好的链接能编辑或断开吗？,3、将数据从Excel复制到Word文档中,57,右击链接对象弹出该快捷菜单,3、将数据从Excel复制到Word文档中,58,所谓嵌入：是指从另一个程序中插入一个对象，并使用该程序的编辑命令来操作该对象,3、将数据从Excel复制到Word文档中,59,3、将数据从Excel复制到Word文档中,60,4、在Word文档中嵌入Excel工作簿,61,4、在Word文档中嵌入Excel工作簿,62,4、在Word文档中嵌入Excel工作簿,63,单击“新建”嵌入新建Excel对象,单击“由文件创建”嵌入已存在Excel对象,链接和嵌入有何区别？链接能保证与原文件
17、间的更新关系，当原文件修改后，链接文档也能更新变化；嵌入对象不会与原文件间的链接更新，但双击后，可以用嵌入对象原程序来编辑嵌入文件。,在Word文档中，嵌入Excel工作簿有哪些操作方式？以下三种方式在Word文档中嵌入Excel工作簿：复制区域并使用Word的“选择性粘贴”功能使用Word的“对象”功能打开已有的Excel文件使用Word的“对象”功能创建新的Excel工作簿,64,1、保护类型,65,Excel中与保护相关的功能可分为三类：工作表保护：保护工作表不被修改，或者对某些用户限制修改。工作簿保护：防止在工作簿中插入、删除、移动或复制工作表，同时需要密码才能打开工作簿。VB保护：使
18、用密码防止其他用户查看或者修改VBA代码。,2、工作表保护,66,什么时候需要保护工作表呢？,2、工作表保护,67,防止自己或者其他人意外地删除表格中公式或其他关键数据,密码可选！,3、解锁单元格,68,保护后的工作表能再编辑吗？若想修改工作表中某部分的数据，该怎么办呢？,单元格默认状态为“锁定”,3、解锁单元格,69,保护后的工作表能再编辑吗？若想修改工作表中某部分的数据，该怎么办呢？,清除“锁定”复选项,3、解锁单元格,70,Excel不仅可以设置编辑区域，还可以为受保护的工作表的不同区域指定操作权限。,怎样才能打开该对话框？,单击“权限”按钮设置权限,3、工作簿保护,71,3、工作簿保护
19、,72,加密保护,3、工作簿保护,73,工作表保护和工作簿有何不同？工作表的保护主要保护工作表内的数据信息不被破坏和修改，以及工作表行列的保护；而工作簿的保护主要保护工作结构和窗口。,保护工作簿结构主要对工作簿起到哪些约束？工作簿的结构受到保护，用户将不能进行以下操作：添加表删除表隐藏表或显示隐藏的表重命名表移动或复制表,74,Excel与其他程序数据共享的途径和方法,Excel数据安全性,75,本节内容,76,表格设计思想,表格元素的设计,下节内容,Excel报表设计理念,课堂任务三,操作一 设计思想不可缺少,1、如何才能设计出“好表格”,操作二 表格元素一个也不能少,1、表格一般应具备的元
20、素,77,操作三 表格设计案例公司年度销售报表,1、各种设计方案对比2、公司年度销售报表设计过程,1、如何才能设计出“好表格”,问：“听说过平面设计、网页设计表格也需要设计吗？”答：“表格当然需要设计！”问：“表格能反映数据信息不就行了吗？”答：“并非如此！表格设计是为了解决一个实际问题：让数据的载体表格能更清晰易懂”,1、如何才能设计出“好表格”,你认为这张表在设计上有何缺陷？,1、如何才能设计出“好表格”,修改后的表格！,1、如何才能设计出“好表格”,让表格使用者能很清晰、自然地从中获取所需的信息，这是表格设计者所要把握的基本准绳,表格设计的目的是什么？让数据的载体表格能更清晰易懂表格设计
21、应该遵循什么准绳？让表格使用者能很清晰、自然地从中获取所需的信息,82,1、表格一般应具备的元素,表格标题,相关责任人签章,制表单位,1、表格一般应具备的元素,表格一般应具备哪些附加信息？表格标题，单位或部门名称，表格创建或填表日期，制表人，审核人，汇总人等信息。这些附属元素依据不同的表格，根据实际需求予以添加。,85,1、公司年度销售报表各种设计方案对比,86,设计方案一,1、公司年度销售报表各种设计方案对比,87,设计方案二,1、公司年度销售报表各种设计方案对比,88,设计方案三,2、公司年度销售报表的设计过程,89,清楚报表所要反映的主要数据核心数据：单件商品销售额、季度销售额、年度总销
22、售额；比较数据：上年同期、增幅（有可能是负值）；特殊数据：平均折扣率、季度和年度销售排名等企业领导所关心的信息,2、公司年度销售报表的设计过程,90,销售报表一般由哪些部分数据构成？核心数据：单件商品销售额、季度销售额、年度总销售额；比较数据：上年同期、增幅（有可能是负值）；特殊数据：平均折扣率、季度和年度销售排名等企业领导所关心的信息阐述销售报表的制作过程。确定报表的总体框架确定各部门子表的结构确定主表与子表的数据关系向表中输入数据完善表格元素,91,92,掌握Excel函数语法规则掌握Excel函数的使用掌握Excel数据共享与安全,掌握表格的一般设计理念掌握表格应具备的一般元素了解公司销售报表的设计制作过程,本章内容,实践Excel函数的使用,实践Excel与Word 间数据共享的几种方式,创建公司年度销售报表,93,作业,享受学习 发现快乐,非常感谢您的聆听与参与！,94,
