excel高级应用剖析

《excel高级应用剖析》由会员分享，可在线阅读，更多相关《excel高级应用剖析（48页珍藏版）》请在金锄头文库上搜索。

1、第四章 Excel高级应用,4-2,一、什么是Excel 运用计算机处理表格的软件叫电子表格 通用的电子表格有：Visicalc、 Lotus123、Excel,4.1 Excel概述,功能：1.具有超强的数据分析能力 2.根据工作表中数据生成各种图表 3.具有很强的制表能力 4.采用公式或函数自动处理数据 5.使用外部数据库 6.自动化处理(宏、VBA),4-3,三大功能：工作表、图表、数据清单 应用：财务、行政、人事、统计和金融,4.1 Excel概述,基础知识： 工作簿的基本操作(新建、保存、保护等) 工作表的基本操作(选择、插入、删除等) 单元格的基本操作(选取、编辑、注释等) 设置工

2、作表的格式(数据格式、颜色、底纹等) 工作表的打印(版面设置、页眉、页脚等),4-4,二、Excel的窗口组成 标题栏、菜单栏 工具栏(视图/工具栏) 编辑栏(视图/编辑栏，名字框，取消按钮，输入按钮，编辑公式按钮，编辑区域) 状态栏(视图/状态栏) 工作簿窗口(文档窗口) 标题栏(工作簿名称),4.1 Excel概述,4-5,工作表(256列*65536行) 列标为A,B,AA,AB,IV 行号为1,2,3,65536 行与列的交叉处称为单元格 工作表的切换(工具/选项常规) 最多有255张工作表 滚动条与滚动框 窗口边框,4.1 Excel概述,4-6,三、鼠标的操作 左指箭头：标题栏、菜

3、单栏等 形指针：编辑区域，双击单元格 空心十字指针：选择单元格 实心十字指针：移至单元格右下角 带箭头十字指针：改变行宽和列高 中间带空隙双箭头指针：水平或垂直分隔窗口,4.1 Excel概述,4-7,一、自定义下拉列表输入 输入数值(默认右对齐) 输入文字(默认左对齐) 输入日期与时间Ctrl+;/Ctrl+Shift+:,4.2 数据输入,自定义下拉列表输入数据操作过程： 选择需要的所有单元格； 数据/有效性设置选项卡 允许序列 来源直接输入逗号分隔，或单元格范围,4-8,二、自定义数据序列 输入系列数值：拖曳，编辑/填充 输入系列文字：如甲、乙、丙等 自定义系列：如经济学院、管理学院等

4、工具/选项自定义序列,4.2 数据输入,4-9,三、输入特殊字符 输入特殊字符的方法： 选择要插入字符的单元格； 插入/符号符号对话框； 选择合适的字体和子集； 选择需要插入的符号； 单击插入即可。,4.2 数据输入,4-10,四、条件格式 通过设置数据条件格式，可以让单元格中的数据满足指定条件时就以特殊的标记或颜色显示出来。,4.2 数据输入,操作方法：选择需要设置条件格式的单元格； 格式/条件格式条件格式对话框； 单元格数值完成条件选项； 单击格式按钮，选择图案选项卡； 单击确定完成设置。,4-11,4.3 公式与函数,一、公式的概念 公式是对工作表中的数值进行计算的式子，由操作数和运算符

5、组成。,1.公式操作数运算符 操作数：常量或数值、名称或文字、数组、单元格地址或单元格引用、函数等。 运算符：用于连接公式中的操作符，如加、减、乘、除等。,4-12,运算符及其优先级,4.3 公式与函数,4-13,2.输入公式 在单元格中输入公式时，先输入等号,然后再输入公式本身,如: 2016 （A2=20, A3=16） A2A3 SUM(20,16) SUM(A2,A3) SUM(A2:A3),4.3 公式与函数,4-14,二、单元格引用 在公式中使用单元格地址 1.相对地址 相对地址表示某一单元格相对于当前单元格的相对位置。当把一个含有单元格地址的公式复制到新位置时，公式中的单元格地址

6、会改变。 相对地址引用表示：直接写列字母和行号，如A1、B2、D7等。,4.3 公式与函数,4-15,2.绝对地址 绝对地址是单元格在工作表中的绝对位置。当把含有单元格地址的公式复制到新位置时，单元格地址保持不变。 绝对地址引用表示：在列字母和行号前加符号$，如$A$1、$B$2、$D$7等。,4.3 公式与函数,3.混合地址 混合地址是前两种地址的混合。 混合地址引用表示：$A1,A$1,4-16,例题4-1对于如下所示的工作表，若将B2中的公式=$B1+A$2，复制到区域B2:D4的各单元中，则在D2中的公式为 (1) ，其显示结果为 (2) 。 (1) A.$B1+C$2 B.$B1+A

7、$2 C.$B1+B$2 D.$B3+A$2 (2) A.12 B.18 C.24 D.30,4.3 公式与函数,4-17,例题4-2在Excel中，若C1有“A1+B1”,当把C1的内容复制到D3后，则D3是为 。 A.A1+B1 B.A3+B3 C.B3+C3 D.不能这样移动,4.3 公式与函数,例题4-3若A1的宽度为11个字符，且其内容为“ABC”，则在 水平对齐格式下会显示“ ABC ”。 A.常规 B.填充 C.分散对齐 D.两端对齐,4-18,3.函数 函数是Excel预先定义好的公式。 函数由函数名、一对圆括号和几个参数组成，例如:SUM(A2,A3)。 函数名输入时，字母的

8、大小写等效，各参数之间必须用逗号分隔。,4.3 公式与函数,插入/函数，插入函数按钮 常用函数：SUM、AVERAGE、IF,4-19,例题4-4若A5内公式为=AVERAGE(B$2:B$5),则复制到B200单元格后公式变为 。 A.=AVERAGE(B$2:B$5) B.=AVERAGE(C$2:C$5) C.=AVERAGE(B$197:B$200) D.=AVERAGE(C$197:C$200),例题4-5设区域a1:a8各单元中的数值均为1，a9单元中的数值为0，a10单元中为一字符串,则函数=average(a1:a10)的结果与公式 的结果相同。 A.8/8 B.8/9 C.8

9、/10 D.9/10,4.3 公式与函数,4-20,例题4-6某高校职务补贴标准是：教授补贴600元副教授补贴500元，讲师补贴400元，其他人员补贴300元。在Excel工作表中，假设已输入下列数据：,现要求计算每个职工的职务补贴，应在单元D2中输入公式 。,4.3 公式与函数,4-21,利用IF()函数的嵌套语句： IF(逻辑判断, 真返回值, 假返回值),4.3 公式与函数,录取条件：成绩总分大于550（G2） 体检合格 (H2) 语句： IF(G2550,IF(H2=“合格”,“是”,“否”),“否”),4-22,A.=IF(C2=教授,600,IF(C2=副教授,500, IF(C2

10、=讲师,400,300) B.=IF(C2=教授,600,IF(C2=副教授,500, IF(C2=讲师,400,IF(C2=其他人员,300) C.=IF(C2=”教授”,600,IF(C2=”副教授”, 500,IF(C2=”讲师”,400,300) D.=IF (C2=”教授”,600,IF(C2=”副教授”, 500,IF(C2=”讲师”,400,IF(C2=”其他人员”, 300),4.3 公式与函数,4-23,4.日期与时间函数 DATE(year,month,day) DAY(serial_number) DAY(“2008-1-1”)=DAY(39448)=1 1900-1-1

11、开始算起 TODAY() TIME(hour,minute,second),4.3 公式与函数,4-24,例题4-7贷款购房方案 1.案例分析 随着银行信贷业务的广泛开展，贷款购房已成为大多数家庭购房时所选择的方案。 由于购房贷款数额大、周期长、贷款者经济能力有限等原因，部分家庭在利用抵押贷款方式购房时会因考虑不周而造成还贷困难甚至影响正常生活。,4.3 公式与函数,根据自己的还款能力制订购房贷款计划非常重要,4-25,2.设计思路 如何制订一个合理的借贷方案？,利用Excel的PMT函数及双变量模拟运算表制作一个购房贷款方案表，它能够计算还款期数和贷款本金两个参数同时变化时贷款的每期(月)偿

12、还额，从中可以选择出适合自己的一套方案。,4.3 公式与函数,4-26,3.关键技术(知识点) PMT函数-计算贷款的每期偿还额,rate：指定每期贷款利率，如有一笔贷款年利率为10%且按月付款的汽车贷款，则每期的利率为0.1/12=0.0083 nper：指定一笔贷款的还款期数，如有一笔为期4年且按月付款的汽车贷款，则这笔贷款共有412=48个付款期 pv： 指本金,4.3 公式与函数,定义：基于固定利率及等额分期付款方式，返回贷款的每期偿还额。 公式：PMT(rate,nper,pv,fv,type) PMT(贷款利率,还款期数,贷款本金),4-27, 模拟运算表,4.3 公式与函数,对

13、Excel工作表中的一个单元格区域的数据进行模拟计算，它可以显示一个计算公式中某些参数的值的变化对计算结果的影响。 还可以将所有不同的计算结果以列表的方式同时显示出来，因而便于查看、比较和分析,4-28,4.实现方法,购房者在购买住房时要考虑许多因素，如房价按揭年限等，并在众多方案中选择合适的方案。 假设某人想通过贷款购房改善自己的居住条件可供选择的房价有20万元、30万元、40万元、50万元、60万元、80万元和100万元,可供选择的按揭方案有5年、10年、15年、20年和30年。 由于收入的限制，其每月还款额最高不能超过3000元，但也不要低于2000元，已知银行贷款利率为6%。利用双变量

14、模拟运算表帮助选择贷款方案。,4.3 公式与函数,4-29, 表格建立与PMT函数(PMT函数),4.3 公式与函数,4-30,模拟运算表的计算,4.3 公式与函数,操作方法：选取B5:I10，建立模拟运算表； 选择数据/模拟运算表，打开数据表对话框； 输入引用行的单元格是$D$2 输入引用列的单元格是$C$2,4-31,例题4-8股票收益计算 1.案例分析 股票是一种重要的投资方式，虽然有很多股票投资分析软件，但多为商业软件，利用Excel也可打造的股票投资分析软件。,4.3 公式与函数,利用Excel进行股票收益管理,不仅可以做到盈亏一目了然，而且还可以确定每只股票的保本卖出价，为股票交易

15、提供科学的依据。 利用Excel管理股票除可以计算收益外,还可以作为股票交易的历史记录，为股票投资积累宝贵的资料。,4-32,2.实现方法 建立交易工作表,4.3 公式与函数,每次买卖股票，就可按数据格式顺序增加一条记录。 由于个人买卖股票的品种不会很多，可建立有效性条件 操作方法：数据/有效性数据有效性对话框,4-33,股票买入计算,买入股票支付金额的计算公式： 买入金额买入价买入数量股票交易印花税 交易佣金5(手续费) 股票交易印花税买入价买入数量印花税率 交易佣金买入价买入数量佣金率,4.3 公式与函数,4-34,股票卖出计算,卖出股票的计算公式： 卖出金额卖出价卖出数量股票交易印花税

16、交易佣金5 股票交易印花税卖出价卖出数量印花税率 交易佣金卖出价卖出数量佣金率,4.3 公式与函数,4-35,股票盈亏计算与收益率计算,股票盈亏计算：用卖出金额减去买入金额就可以 获得盈亏结果。 股票交易盈亏卖出金额买入金额 收益率：股票的卖出收入与买入投资的百分比。 股票收益率卖出收入买入投资,4.3 公式与函数,4-36,股票保本价计算公式,参考保本价买入价 (买入税费卖出税费)/买入数量,4.3 公式与函数,4-37,Excel具有强大的数据管理与分析能力能够对工作表中的数据进行排序、筛选、分类汇总等，还能够使用数据透视表对工作表的数据进行重组。 对特定的数据行或数据列进行各种概要分析，并且可以生成数据透视图