表格处理及数据分析高级应用课课件

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