电工与电子技术电子商务电子课件第5章 Excel电子表格ppt

1、电子课件第5章 Excel电子表格第5章 Excel电子表格主要内容Microsoft Excel 概述数据输入数据格式化公式与函数数据处理与分析图表5.1 Microsoft Excel 概述1.Microsoft Excel工作界面5.1 Microsoft Excel 概述2.相关概念5.1 Microsoft Excel 概述3.电子表格的操作(1)选择工作表插入工作表重命名工作表移动和复制工作表拆分工作表5.1 Microsoft Excel 概述3.电子表格的操作(2)保护工作表设置允许用户编辑区域5.2 数据输入1.基本类型输入输入文本选定要输入文本的单元格，直接在其中输入文本内

2、容，按Enter键或单击另外一个单元格即可完成输入。Excel中，对于全部由数字组成的字符串，如邮政编码、电话号码等，为避免被认为是数值型数据，Excel要求在这些输入项前添加英文下的单引号，来区分是“数字字符串”而非“数字”数据。输入数值在Excel中的数值是指能够参加算术运算的数值型数据，它不仅包括09这10个数字，还包括其它的一些特殊符号，如E、￥、$、%、小数点等。默认情况下，Excel将数值沿单元格右对齐。Excel数值输入与数值显示未必相同，如果输入的数值太长，Excel会自动以科学计数法表示。输入日期和时间Excel将日期存储为一个序列号，是一个自从1900年1月1日以来所代表的

3、天数。序列号1对应于1900年1月1日，序列号2对应于1900年1月2日。在Excel中使用小数来处理时间。每一天为1，每一小时可记为二十四分之一。输入公式公式是在工作表中对数据进行分析的等式，它可以对工作表数值进行加、减、乘、除等运算。公式可以是直接的数值运算，也可以引用同一工作表中的其他单元格、同一工作簿不同工作表中的单元格或者其他工作簿的工作表中的单元格。当引用其它单元格时，需要指明该单元格地址。5.2 数据输入2.有效性设置在Excel里处理数据时，为确保输入的数据是有效的数据，需要添加数据有效性的设置，提高数据输入的正确率。5.2 数据输入3.数据填充5.3 数据格式化1. 基本数据

4、格式化 设置数字格式设置对齐方式 设置字体格式设置列宽行高样式使用5.3 数据格式化2 .电子表格格式设置示例5.4 公式与函数1. 运算符引用运算符含义:区域运算符，对两个引用之间、包括两个引用在内的所有单元格进行引用，联合运算符，将多个引用合并为一个引用空格交叉运算符，表示几个单元格区域所重叠的那些单元格。运算符(优先级从高到低)说明：区域运算符，联合运算符空格交集运算符-负号%百分比乘幂*和/乘法和除法+和-加法和减法&文本连接符=，=，=，比较运算符5.4 公式与函数2. 单元格的引用(1)相对引用相对引用是被引用的单元格与公式所在的单元格之间的位置是相对的。如果公式所在的单元格的位置

5、发生了变化，那么引用的单元格的位置也会相应地发生变化。所以当带有相对引用的公式被复制到其他单元格时，公式内引用的单元格将变成与目标单元格相对位置上的单元格。5.4 公式与函数2. 单元格的引用(2)绝对引用在行号与列号前都加上绝对地址符号“$”，则表示绝对引用。在有绝对引用的公式中，公式填充或复制时，绝对引用的行号与列号将不随着公式位置变化而变化。5.4 公式与函数2. 单元格的引用(3)混合引用混合引用是指公式中引用的单元格地址的行号或列号前不同时加“$”符号。当公式因为复制或插入而引起行列变化时，公式中的相对地址部分会随着位置的变化，而绝对地址部分仍不变。(4)引用其他工作表或工作簿中的单

6、元格如果需要引用同一工作簿的其它工作表中的单元格地址，则在该单元格地址前加上“工作表标签名！”即可。格式为：=工作表名称！单元格地址在单元格引用中，不仅仅是引用当前工作表中的单元格，有时需要引用同一工作簿中其他工作表中的单元格。如果要引用不同工作簿中某一工作表的单元格，则需要使用如下格式：=工作簿名称 工作表名称！单元格地址5.4 公式与函数3.函数 函数名(参数1，参数2，)函数名就是函数的名称，一般代表了函数的用途，如SUM代表求和，AVERAGE代表求平均，MAX代表求最大值等。参数可以是数字、文本、逻辑值、数组、错误值或单元格引用。指定的参数都必须为有效参数值。参数也可以是常量、公式或

7、其他函数。每个函数都有返回值，返回的值就为该函数的计算结果。5.4 公式与函数4.公式与函数综合应用5.5 数据处理与分析排序筛选分类汇总合并计算模拟分析规划求解1 排序按数字简单排序按颜色简单排序1 排序多关键字排序2 筛选自定义筛选2 筛选高级筛选 3 分类汇总分类汇总 ，就是对数据按种类进行快速汇总。在分类汇总前，需要对数据按照分类字段进行排序，让同类内容有效组织在一起。4 合并计算5 模拟分析Excel的单变量求解，是在待求解问题数学模型(公式)已经被确定的前提下，根据对模型所描述目标的确定要求，利用数学模型倒推条件(自变量)指标的逆向分析过程。单变量求解过程如下：(1)建立正确的数学

8、模型(表达式)。(2)确定模型中的确定性前提条件与待求解变量(只能有一个)。(3)建立包含已知前提条件和与其相关的数学模型的数据表。(4)应用“数据数据工具组假设分析按钮单变量求解选项”进行问题求解。5 模拟分析5 模拟分析【例5-2】假设购买设备需要一定数量的贷款，已知贷款的还款期限、每月能够提供的还款数额上限，需要确定所能够承受的贷款利率。目标模型为：=PMT(B3/12，B2，B1)，计算月还款金额5 模拟分析模拟运算数据表，为用户提供了对指定数学模型中变量变化的观察手段，支持用户观察当模型限制条件在指定范围内变化时，即模型中1至2个变量发生变化时的模型最终结果的变化情况。即观察变量在指

9、定范围内变化对模型最终结果所产生的影响。假设分析中模拟数据表由三个部分构成：(1)问题(模型)描述中的数据量(常量和变量)。(2)模型涉及的变量及其取值范围(只能包括1-2个变量，只涉及一个变量称为单变量数据表，涉及两个变量的称为双变量数据表)。(3)问题的数学模型(公式，数量彼此之间关系表达)。5 模拟分析1)单变量数据表【例5-3】假设预选项目可以获得初始投资200，000元，经过分析论证，估计项目的投资建设可以获得年收益X%左右，预计可投资时间延续10年，需要了解投资各个时期投资总额和相关收益情况。年收益总额的数学模型为： 初始投资总额 *(1+年收益百分比)投资年5 模拟分析1)单变量

10、数据表【例5-4】单变量多公式数据表求解，设计三个待观察计算目标：到其总额，总收益，总收益率。5 模拟分析2)双变量数据表【例5-5】考虑的两个变量为年收益率和投资时间，计算公式选择为总收益率，年收益率变量的输入值组织与单变量数据表的组织形式相同，投资时间的变量输入值组织为列格式列表，并且位置必须设置在行输入值列表的左侧，设置的输入值为可参考的投资年限范围：6，7，15。5 模拟分析Excel 方案管理器提供了层次性的数据管理方案与计算功能。每个方案在变量与公式计算定义的基础上，能够通过定义一系列可变单元格和对应各变量(单元格)的取值，构成一个方案。在方案管理器中可以同时管理多个方案，从而达到

11、对于多变量、多数据系列、以及多方案的计算和管理。6 规划求解在计划管理中，经常会遇到各种规划问题，例如：人力资源的调度、产品生产的安排、运输线路的规划、生产材料的搭配、采购批次的确定等。这类问题有一个共同要求，那就是：如何合理利用各种约束资源实现最佳的经济效益，也就是达到利润最大、成本最低、费用最省等目标。这就是本章要解决的在约束条件下寻求目标函数最优的规划问题。一般来讲，这类规划问题都具有如下三个特点：1)所求问题都有单一的目标，如求生产的最低成本，求运输的最佳路线，求产品的最大盈利，求产品周期的最短时间以及求其他目标函数的最佳值等。2)总是有明确的不等式约束条件。比如库存不能低于一定的数量

12、，否则造成原料短缺或产品缺货；生产产品不能超过一定额度，否则会造成商品积压等。3)问题都有直接或间接影响约束条件的一组输入值。6 规划求解 “规划求解”是Excel中的一个加载宏，要使用“规划求解”分析工具，利用“规划求解”分析工具求解规划问题的大致步骤如下：1)建立问题的数学模型2)在Excel中建立Exce工作表规划模型3)设置 “规划求解参数”4)设置可变单元格：确定决策变量5)设置目标单元格：建立目标函数6)设置约束条件：约束条件可以用线性等式或不等式表示，还有非负约束()和整数约束(Int)6 规划求解【例5-6】某饲养场饲养动物，设每头动物每头至少需690g蛋白质、28g矿物质、8

13、0mg维生素。现有五种饲料可供选择，各种饲料每Kg营养成分含量及单价如表X所示：求解：饲养场要求确定即满足动物生长的营养需求，又使费用最少的选择饲料的方案。数学模型：根据题意，用X1 ， X2 ， X3 ， X4 ， X5分别表示A、B、C、D 、E 5种饲料的数量(Kg)，用COST表示总费用，则得如下线性规划数学模型：约束条件目标函数6 规划求解5.6 图表迷你图常见图表数据透视图5.6 图表迷你图主要包括折线图、柱形图、盈亏图3种.5.6 图表针对不同类型的图表，Excel系统提供大量的可选择样式，用户可以通过：图表工具设计标签图表样式工具栏获得不同的可选择标准样式。5.6 图表可在已创建图表添加或图表元素，如：坐标轴、坐标轴标题、图表标题和数据标签等。5.6 图表图表中营业额和利润若都有簇柱状图表示，则营业额和利润因为数据相差较大，对利润的变化情况不能很好表达。可利用组合图，把利润用折线图分别表示，步骤如下：选中图中任意红色利润簇柱状图右击鼠标更改系列图表类型，利润图表类型改为折线图，设置为次坐标，即可完成。5.6 图表如某商品销售渠