EXCEL高级应用2.ppt

1、,第7章 图表化,图表类型,柱形图：多组数据在某段时期的变化，或各组数据比较的情形；数据在Y轴。 条形图：比较特定时间、特定范围的各组数据；数据在X轴。 折线图：某段时期内数据变化情形与趋势，强调时间性与数据变化率。或数据发展的趋势。 圆环图：适合突出比例关系的数据，显示一个以上数据系列。 饼图：适合突出比例关系的数据，尤其是某列数据与整体数据的关系比，它只能显示一个数据系列。 雷达图：独立显示数据系列之间、单个数据系列与整体之间的关系，形象地观察出数据系列的变化情形，每种类型的数轴都是由中心点发射出来的，然后用一系列的数据刻度再彼此连接。 面积图：显示一段时间内数据变动量与累积量，或各数据系

2、列与整体的比例关系。 散点图：比较在不均匀时间或测量间隔上数据变化趋势。适合做实验数据分析和函数图形。例如，0-2PI的正弦、余弦函数。 气泡图：实质上是一种XY散点图，只是气泡大小表示第三个变量值的大小。 股价图：证券交易分析。 曲面图：显示图表面的最高点和最低点。,坐标轴四种类型,时间轴 分类轴 数值轴 系列轴(右上),系列：,系列产生在“行”或“列” 何作为分类轴数据 切换操作 增加系列 系列次序上移/下移按钮 系列名称,数据区域,不同区域数据作图 CTRL 不同工作表数据作图 系列添加方法 以名称引用的数据作图 作图方法常规 优点： 名称中可以使用函数 修改函数图表随着更新,图表保存,

3、复制为图片 粘贴下菜单实现 粘贴为图片 粘贴下菜单实现,动态图表,控件 滚动条 组合框 图像 名称 使用函数,实例:,假设分析、规划求解、数据分析,第8章数据分析,假设分析,_在单元格中更改值以查看这些更改将如何影响工作表中公式结果的过程 特点: 可以在一个或多个公式中使用不同的几组值来分析所有不同的结果 例如, 生成两个预算，其中每个预算都假定一个特定水平的收入 三种假设分析工具 单变量求解 方案 数据表,区别与联系：,方案和数据表:可获取一组输入值并确定可能的结果 数据表：仅可以处理一个或两个变量，但可以接受这些变量的众多不同的值 方案：可具有多个变量，但它最多只能容纳 32 个值 单变量

4、求解：与方案和数据表的工作方式不同，它获取结果并确定生成该结果的可能的输入值,数据表,功能:数据表用于显示公式中一个或两个变量的更改对公式结果的影响。数据表提供了一种快捷手段，它可以通过一步操作计算多个结果；同时，它还是一种有效的方法，可以查看和比较由工作表中不同变化所引起的各种结果。 例:假设初期投入4万,年收益率为5%,投资20年,要求测算到期资金总额和相关收益情况. 问题: 1)年收益率低于或高于5%,到期如何? 2)投资低于或高于20年,到期又如何?10年如何? 方法1: 使用一系列格式,复制实现 方法2:假设分析/数据表（测算投资收益是常作的数据分析）,已知（或者假设）公式的一个结果

5、，反过来求出某个变量的值。(解一元一次方程） 单变量求解问题：在知道预期结果的情况下，求出这个结果需要什么样的条件。 实例1：Y = 3X 解：1）A2 X， B2 Y（输入“=3*A2”） 2）B2，单变量求解 3）目标单元格B2 目标值12 可变单元格-A2,单变量求解,实例2：C5：=SIN（B5），工具/单变量求解 目标单元格C5 目标值1 可变单元格-B5 实例3：C7：=B7*B7+2*B7+1 目标单元格C7 目标值9 可变单元格-B7 X = 2 局限性：另一个解 4 求不出,实例4：现有一家公司，目前的年销售额是1500万元，现在该公司准备拓展销售业务，计划在5年时间内销售额

6、达到2500万元。为此，该公司提出一个销售额年增长率为10，但是这个年增长率能否实现原定目标，还需要进一步检验。同时，希望制定一个适合于原定目标的年增长率，以取代初步制定的10的年增长率。 解题步骤： 第一个问题：检验原来制定的年增长率能否满足原定目标的要求。 在单元格D6中输入公式：“D4（1D5D8）5,显示数值2415765，可见，原定的年增长率不能满足预定的销售增长目标。 第二个问题：制定一个适合于原定目标的年增长率修正值，用它加上原定的年增长率，就是正确的年增长率，并以此来取代原来制定的10的年增长率。 选定公式所在的单元格D6, 从“工具”菜单中选择“单变量求解”命令， 在“单变量

7、求解”对话框中，有下列三个编辑框： 目标单元格D6 目标值预期结果，在本例中，输入2500。 可变单元格-D8,在“单变量求解状态”对话框中，显示单变量求解的过程。 如果公式比较简单,直接显示出求解结果 如果公式复杂，进行单变量求解可能会经过多次迭代计算，需要经过较长时间才能计算出结果 单击“单变量求解状态”对话框中的“确定”按钮，计算的结果就填入到工作表中的相应位置上（D8）。 在单元格D8中得到了对原定年增长率的修正值，该修正值为正数，表示应该在原定增长率的基础上加上这个修正值。因此实例最后结果应该是：要想在5年后得到2500万元的年销售额，年销售额增长率应该为10.7566。,方案,数据

8、表的局限性：只能分析一个变量的一组变化值对多个公式的影响情况，或两个变量的两组变化值对单个公式的影响情况。 方案：分析多个变量对多个公式的影响情况。 实例:要购买一辆轿车，现有两辆车可供选择，一辆价值12万元，一辆价值16万元，而且对于不同的轿车，又有不同的购买条件。如果购买12万元的车，可以提供70%的贷款，利率为4 %，期限为3年；还可以提供80%的贷款，利率为6.5%，期限为4年。如果购买16万元的车，可以提供80%的贷款，利率为5.5%，期限为4年；或者提供80%的贷款，利率为6%，期限为5年。现在要求根据这些信息与每个月所能支付的偿还额，决定购买什么样的轿车，以及选择哪一种贷款方案？

9、,解决方法：列为四个方案，如下： 方案l：购买价值12万元的车，贷款额为70%，利率为4%，期限为3年。 方案2：购买价值12万元的车，贷款额为80%，利率为6.5%，期限为4年。 方案3：购买价值16万元的车，贷款额为80%，.利率为5.5%，期限为4年。 方案4：购买价值16万元的车，贷款额为80%，利率为6 %，期限为5年。 实现: （1）在单元格F3中输入公式：=PMT(利率/12，期限*12，价格*贷款额)，并选择“方案管理器” 命令。 （2）“方案管理器”对话框中，单击“添加”按钮。 （3）在“编辑方案”对话框上的“方案名”编辑框里输“购车方案l”作为方案名，并单击“可变单元格”编

10、辑框中的按钮，然后在工作表中选择单元格区域A3：D3。 （4）单击“添加”按钮，建立其余三个方案。当建立了最后一个方案时，单击“确定”按钮， （5）建立方案汇总报表 ，单击“工具/方案”命令，在显示的“方案管理器”对话框上.单击“总结”按钮 ，分别选择“方案总结”或“方案数据透视表”,规划求解,解决运筹学、线性规划中的问题 人员调度：最小成本使职工水平达到企业指定的最满意水平 求最大与最小 目标函数和不等式给出的约束条件 问题有单一目标 例：计算线性方程组的值 X1 + X2 + X3 + 2X4 = 11 X1 + X2 + 2X3 + X4 = 12 X1 + 2X2 + X3 + X4

11、= 13 2X1 + X2 + X3 + X4 = 14 求解X1、 X2、 X3、 X4的值。,求解二元一次方程,方法 数组公式 规划求解工具 VBA编程 数据表,加载“规划求解”,单击“Office 按钮” ，然后单击“Excel 选项” 单击“加载项”，然后在“管理”框中，选择“Excel 加载宏” 单击“转到” 在“可用加载宏”框中，选中“规划求解加载项”复选框，然后单击“确定”,实现方法：,（1）为未知数预留4个单元格：B2：B5，将活动单元格定为C2：C5，并在C2、C3、C4和C5单元格分别输入公式; （2）选择“规划求解”命令，出现“规划求解参数”对话框，按所示对话框输入相关选

12、项; （3）在“规划求解参数”对话框上的“设置目标单元格”的编辑框中选择C2单元格，并选中“值为”单选钮，输入第一个方程的值11，在“可变单元格”编辑框中，选择放置方程组未知数的4个单元格：B2：B5，其他三个方程由添加约束来实现，然后单击“添加”按钮; （4）在“添加约束”对话框上，分别在三个编辑框上输入单元格地址C3、等号和数值12，两次单击“添加”按钮，输入余下的两个方程的相关数据，最后单击“确定”按钮; （5）返回，并单击“求解”按钮，B2：B5将显示出方程组的解，即：X1=4，X2=3，X3=2，X4=1,数据分析,描述统计“描述统计”分析工具用于生成数据源区域中数据的单变量统计分析

13、报表，提供有关数据趋中性和易变性的信息。 直方图分析选定区域数据在某些区间的频度及叠加百分比，结果以表格和图表的形式给出，直观、清晰。 单因素方差对两个或更多样本的数据执行简单的方差分析。此分析可提供一种假设测试，该假设的内容是：每个样本都取自相同基础概率分布，而不是对所有样本来说基础概率分布都不相同,抽样以数据源区域为总体，从而为其创建一个样本。当总体太大而不能进行处理或绘制时，可以选用具有代表性的样本。如果确认数据源区域中的数据是周期性的，还可以对一个周期中特定时间段中的数值进行采样。 “排位与百分比排位”产生一个数据表，在其中包含数据集中各个数值的顺序排位和百分比排位。该工具用来分析数据

14、集中各数值间的相对位置关系。,回归通过对一组观察值使用“最小二乘法”直线拟合来执行线性回归分析。此工具可用来分析单个因变量是如何受一个或几个自变量影响的。,第9章 图形,室内平面图,-锦上添花,第10章 VBA,一、VBA帮助,VBE窗口 方法1：选择RANGE这类代码，按F2 方法2：右上角输入“RANGE”，搜索范围“来自计算机的内容”的“开发人员参考” EXCEL对象模型参考,二、调试,相关窗口(视图菜单打开),立即窗口 输出内容：Debug.Print 输入语句: ?Activecell.Address,回车 属性计算:?5+4, 回车 监视窗口 添加监视的变量等,例如,I,K13 设

15、置中断 本地窗口 显示过程变量值,-当添加监视时,选择中断的监视类型。,-Debug.Assert I8 关系式为FALSE，程序在此句停止,三、基本语法,语句换行 当语句在1行写不了，继续在下一行写入，使用“_”开始 属性赋值格式 := 例如:RefersTo:=A1, Visible:=False 保存为启用宏的xlsm格式文件 是否操作启用宏对话框的设置 EXCEL选项/信任中心/,代码位置（3个部分） 模块（全局性；过程、函数等） 插入/模块，可以与宏、控件关联 类模块：用于窗体模块 工作簿（全局） VBE: 点击ThisWorkBook 选择事件 输入代码 工作表（局部） 双击工作表

16、，从对象列表里选择Worksheet对象,四、VBA提供代表单元格2种对象,RANGE代表某一单元格、某一行、某一列、某一选定区域等 Range(“A1”).value=Range(A3). value A1=A3单元格的值 Range(A1:A20). CELL只对单元格CELL(行数,列数) 例将单元格 A1 赋值为 24 。 Worksheets(1).Cells(1, 1).Value = 24 例：设置单元格 A2 的公式 ActiveSheet.Cells(2, 1).Formula = =Sum(B1:B5),Range.value Range.ROW/COLUMN Range.

17、COUNT计数区域的行数/列数 Range.Offset （偏移行数，偏移列数）属性 返回 Range 对象，它代表位于指定单元格区域的一定的偏移量位置上的区域。 语法 表达式.Offset(RowOffset, ColumnOffset) Range. Offset().Interior 属性 返回一个 Interior 对象，它代表指定对象的内部。 Range. Offset().Interior .ColorIndex 返回或设置一个 Variant 值，它代表内部颜色。 with 对象名_用以执行一系列语句块,Range方法：,Range.Select（选择） Range(A1:A20

18、,A1).Select选择2个区域 Range.Find（查找） Range.Find Range.copy/cut/paste/insert（复制/剪切/粘贴/插入） Range.replace/clear/delete （替换/清除/删除） Range.replace/clear/delete （替换/清除/删除）,例：通过为区域 A1:H8 中的每个单元格设置公式，用随机数字填充该区域。 Worksheets(“Sheet1”).Activate 激活表Range(A1:H8).Formula = =Rand() 例：A1的值赋给A5 Range(A5).Value = Range(A1

19、).Value,五、工作表/工作簿对象,Worksheet Worksheet.Cells/Rows/Columns Worksheet. Add/Open/Close Worksheet. Add.before/after:=sheet1,count:=2 在sheet1前/后添加了2张工作表 Worksheet.Copy/Delete Worksheet.Name Workbook Workbook.sheets W/fullname/haspassword Workbook.Add/Open/Close/Save,实例,例1：在 Sheet1 上创建行号和列标。注

20、意，当工作表激活以后，使用 Cells 属性时不必明确声明工作表（它将返回活动工作表上的单元格）。 Sub SetUpTable() Worksheets(CELL对象).Activate For TheYear = 1 To 5 Cells(1, TheYear + 1).Value = 2001 + TheYear Next TheYear For TheQuarter = 1 To 4 Cells(TheQuarter + 1, 1).Value = A & TheQuarter Next TheQuarter End Sub,例2：批量删除未选中的工作表,Sub DeleteUnSel

21、ectedSheet() Dim n As Integer, i As Integer n = ActiveWindow.SelectedSheets.Count ActiveWindow.SelectedSheets.Move Before:=Sheets(1) 移动工作表组 的第一个 Application.DisplayAlerts = False 禁止提示警告信息 For i = Sheets.Count To n + 1 Step -1 循环删除最后一个工作表 Sheets(i).Delete Next Application.DisplayAlerts = True 恢复警告信息 End Sub,例2：设置无限条件的条件格式,根据B2单元格的字母A-Z填充C2单元格的颜色。 方法： B2 ：数据有效性输入A-Z 选择第2张表(本表) 输入VBA代码 对象(worksheet_ 事件（change),Private Sub Worksheet_Change(ByVal Target