excel高级教程高级应用.docVIP

Excel高级应用教程 目 录 第一节 EXCEL公式及函数的高级应用 1 1.1 数组公式及其应用 1 1.1.1 数组公式的输入、编辑及删除 1 一．数组公式的输入 1 二．编辑数组公式 2 三．删除数组公式 3 1.1.2 数组公式的应用 3 一．用数组公式计算两个数据区域的乘积 3 二．用数组公式计算多个数据区域的和 3 三．用数组公式同时对多个数据区域进行相同的计算 3 1.2 常用函数及其应用 4 1.2.1 SUM函数、SUMIF函数和SUMPRODUCT函数 4 一．无条件求和SUM函数 4 二．条件求和SUMIF函数 4 三．SUMPRODUCT函数 5 1.2.2 AVERAGE函数 5 1.2.3 MIN函数和MAX函数 5 1.2.4? COUNT函数和COUNTIF函数 5 1.2.5? IF函数 6 1.2.6 AND函数、OR函数和NOT函数 6 1.2.7 LOOKUP函数、VLOOKUP函数和HLOOKUP函数 7 一．LOOKUP函数 7 二．VLOOKUP函数 7 三．HLOOKUP函数 8 1.2.8? MATCH函数 8 1.2.9 INDEX函数 8 一、返回数组中指定单元格或单元格数组的数值。 8 二、返回引用中指定单元格。 9 1.2.10? ADDRESS函数 9 1.2.11? INDIRECT函数 9 1.2.12 矩阵函数——TRANSPOSE函数、MINVERSE函数和MMULT函数 10 一．TRANSPOSE函数 10 二．MINVERSE函数 10 三．MMULT函数 10 1.2.13? ROUND函数 10 第二节 EXCEL数据分析处理 11 2.1 数据排序 11 2.1.1 数据排序的规则 11 2.1.2 数据排序步骤 11 2.1.3 自定义排序 12 2.2 数据的查找与筛选 12 2.2.1 记录单查找 13 一．查找数据记录 13 二．修改或删除记录 13 三．添加新的记录 14 2.2.2 自动筛选与自定义筛选 14 一．自动筛选 14 二．自定义筛选方式 14 2.2.3 高级筛选 15 一．一般情况下的高级筛选 15 二．计算条件情况下的高级筛选 16 2.3 数据的分类与汇总 16 2.3.1 进行分类汇总 16 2.3.2 分类汇总的撤消 17 2.4? 数据透视表 17 2.4.1 建立数据透视表 17 2.4.2? 数据的透视分析 19 第三节 EXCEL图表处理 19 3.1 图表类型 19 3.2? 图表的建立 19 3.3 图表的编辑、修改及格式化 21 一．设置坐标、标题、图例等的格式 21 二．改变图表大小 21 三．移动或复制图表 21 四．添加数据标志 21 五．改变图表颜色、图案、边框 22 3.4? 地区销售分布图表的建立 22 3.5? 动态图表的建立 24 第四节 EXCEL数据分析工具的应用 24 4.1 模拟运算表 24 4.1.1 单变量模拟运算表 24 4.1.2 双变量模拟运算表 25 4.2 单变量求解 25 4.3? 规划求解 25 4.3.1? 求解优化问题 26 4.3.2? 求解方程组 27 4.4 方案分析 28 4.4.1? 建立方案 28 4.4.2? 显示方案 29 4.4.3? 修改、删除或增加方案 29 4.4.4? 建立方案报告 30 4.5? 数据分析工具库 30 第一节 EXCEL公式及函数的高级应用公式和函数是Excel最基本、最重要的应用工具，是Excel的核心，因此，应对公式和函数熟练掌握，才能在实际应用中得心应手。 数组公式及其应用 数组公式就是可以同时进行多重计算并返回一种或多种结果的公式。在数组公式中使用两组或多组数据称为数组参数，数组参数可以是一个数据区域，也可以是数组常量。数组公式中的每个数组参数必须有相同数量的行和列。 数组公式的输入、编辑及删除．数组公式的输入 数组公式的输入步骤如下： 1）选定单元格或单元格区域。如果数组公式将返回一个结果，单击需要输入数组公式的单元格；如果数组公式将返回多个结果，则要选定需要输入数组公式的单元格区域。 2）输入数组公式。 3）同时按“Crtl+Shift+Enter”组合键，则Excel 自动在公式的两边加上大括号{ } 。 特别要注意的是，第（3）步相当重要，只有输入公式后同时按“Crtl+Shift+Enter”组合键，系统才会把公式视为一个数组公式。否则，如果只按Enter键，则输入的只是一个简单的公式，也只在选中的单元格区域的第1个单元格显示出一个计算结果。 在数组公式中，通常都使用单元格区域引用，但也可以直接键入数值