excel 高级教程(高级应用)[整理版]

excel 高级 教程 人力资源管理pdf成真迷上我教程下载西门子数控教程protel99se入门教程fi6130z安装使用教程 (高级应用)[整理版] 本文由可__00贡献 doc文档可能在WAP端浏览体验不佳。建议您优先选择TXT，或下载源文件到本机查看。 1 在多个 Excel 工作簿间快速切换 按下“Ctrl+Tab”可在打开的工作簿间切换 快速删除空行 有时为了删除 Excel 工作簿中的空行，你可能会将空行一一找出然后删除，这样做非常不方便。你可以利 用自动筛选功能来实现，方法是:先在表中插入新的一行(全空)，然后选择表中所有的行，单击“数据?筛选 ?自动筛选”命令，在每一列的顶部，从下拉列表中选择“空白”。在所有数据都被选中的情况下，单击“编 辑?删除行”，然后按“确定”，所有的空行将被删去。 注意:插入一个空行是为了避免删除第一行数据。 绘制斜线单元格 绘制斜线单元格 利用 Excel“边框”选项卡的两个斜线按钮，可以在单元格中画左、右斜线。如果想在单元格中画多条斜线， 就必须利用“绘图”工具，方法是:打开 Excel 的“绘图”工具，单击“直线”按钮，待光标变成小十字后拖动光 标，即可画出需要的多条斜线。只要画法正确，斜线可随单元格自动伸长或缩短。至于斜线单元格的其他 表格线，仍然按上面介绍的方法添加。当然，斜线单元格的数据输入要麻烦一些，通常的做法是让数据在 单元格内换行(按“Alt+回车键”)，再添加空格即可将数据放到合适位置。 每次选定同一单元格 有时，为了测试某个公式，需要在某个单元格内反复输入多个测试值。但每次输入一个值后按下 Enter 键 查看结果，活动单元格就会默认移到下一个单元格上，必须用鼠标或上移箭头重新选定原单元格，极不方 便。如果你按“Ctrl+Enter”组合键，则问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 会立刻迎刃而解，既能查看结果， 当前单元格也仍为活动单元格。 禁止复制隐藏行或列中的数据 如果你复制了包含隐藏列(或行)的一个数据区域，然后把它粘贴到一个新的工作表，那么 Excel 把隐藏列 也粘贴过来了。要想避免这种情况，可以选取你要复制的数据区域，然后选择“编辑?定位”命令，单击“定 位条件”按钮，出现“定位条件”对话框，选中“可见单元格”选项，再复制和粘贴这个选定区域就会得到你所 希望的结果。 制作个性单元格 如果你的表格需要菱形、三角形之类的特殊单元格，可用以下方法实现:先在单元格内输入数据，然后打开 “绘图”工具栏，在“自选图形?基本形状”子菜单中找到需要的图形。单击后光标变成一个小十字，由单元 格左上角向右下角拖动，即可画出所需形状的单元格。如果单元格的内容被覆盖，可用鼠标右击刚刚画出 的单元格，选择快捷莱单中“设置自选图形格式”命令。将“设置自选图形格式”和“颜色与线条”选项卡打开， 选中“颜色?填充”下拉列表下“无填充颜色”， “确定”后单元格内的原有内容即会显示出来。 如果你将“属性” 选项卡中的“大小、位置均随单元格而变”选项选中，它还会随单元格自动改变大小。 2 奇特的 F4 键 Excel 中有一个快捷键的作用极其突出，那就是 F4 键。作为“重复”键，F4 键可以重复前一次操作，在很多 情况下起作用，比如在工作表内加入或删除一行，然后移动插入点并按下 F4 键以加入或删除另一行，根 本不需要使用菜单 将格式化文本导入 Excel 1. 在 Windows“记事本”中输入格式化文本，每个数据项之间会被空格隔开，当然你也可以用逗号、分号、 Tab 键作为分隔符。输入完成后，保存此文本文件并退出; 2.在 Excel 中打开刚才保存的文本文件，出 现“文本导入向导-3 步骤之 1”对话框，选择“分隔符号”，单击“下一步”; 3.在“文本导人向导-3 步骤之 2” 对话框中选择文本数据项分隔符号，Excel 提供了 Tab 键、分号、逗号以及空格等供你选择。注意，这 里的几个分隔符号选项应该单选。你在“预览分列效果”中可以看到竖线分隔的效果。单击“下一步”; 4. 在“文本导人向导-3 步骤之 3”对话框中，你可以设置数据的类型，一般不需改动，Excel 自动设置为“常 规”格式。 “常规”数据格式将数值转换为数字格式， 日期值转换为日期格式， 其余数据转换为文本格式。 单击“完成”按钮即可。 快速换行 我们在使用 Excel 制作表格时经常会遇到需要在一个单元格输入一行或几行文字的情况，如果输入一行后 敲回车键就会移到下一单元格， 而不是换行，有一个简便实用的操作方法可以实现换行:在选定单元格输入 第一行内容后，在换行处按“Alt+回车键”，即可输入第二行内容，再按“Alt+回车键”输入第三行以此类推。 在单元格中输入 0 值 一般情况下，在 Excel 表格中输入诸如“05”、“4.00”之类数字后，只要光标一移出该单元格，格中数字就会 自动变成“5”、“4”，Excel 默认的这种做法让人使用非常不便，我们可以通过下面的方法来避免出现这种情 况:先选定要输入诸如“05”、“4.00”之类数字的单元格，鼠标右键单击，在弹出的快捷菜单中单击“设置单元 格格式”，在接着出现的界面中选“数字”标签页，在列表框中选择“文本”，单击“确定”。这样，在这些单元 格中，我们就可以输入诸如“05”、“4.00”之类的数字了。 在 Excel 中,可以用以下方法实现在一个区域内的快速输入而不用鼠标来进行单元格之间的切换。 方法如下: 用鼠标圈定一定区域后，按 Tab 键可使目标单元格向后移，按“Shift+Tab”可向前移。这样就可以在键盘上 连续输入一组数据而不需用鼠标，从而提高输入速度。 提示:事实上，采用此方法最大的好处是，在一行 的最后一个单元格，继续按 Tab，则可以转到下一行开始的单元格;在选定区域最后一行的最后一个单元格 继续按 Tab 则会回到选定区域第一行第一个单元格。同样用 Enter 可以按列输入数据。 3 输入数字、文字、日期或时间 输入数字、文字、 单击需要输入数据的单元格，键入数据并按 Enter 或 Tab 键即可。如果是时间，用斜杠或减号分隔日期的 年、月、日部分，例如，可以键入 9/5/96 或 Jun-96。如果按 12 小时制输入时间，请在时间数字后空一格， 并键入字母 a(上午) 或 p(下午)，例如，9:00 p。否则，如果只输入时间数字，Excel 将按 AM(上午)处理。 将单元格区域从公式转换成数值 有时，你可能需要将某个单元格区域中的公式转换成数值，常规方法是使用“选择性粘贴”中的“数值”选项 来转换数据。其实，有更简便的方法:首先选取包含公式的单元格区域，按住鼠标右键将此区域沿任何方向 拖动一小段距离(不松开鼠标)，然后再把它拖回去，在原来单元格区域的位置松开鼠标 (此时，单元格区 域边框变花了)，从出现的快捷菜单中选择“仅复制数值” 快速输入相同文本 有时后面需要输入的文本前面已经输入过了， 可以采取快速复制 (不是通常的“Ctrl+C”、 “Ctrl+X”、 “Ctrl+V”) 的方法来完成输入: 1.如果需要在一些连续的单元 格中输入同一文本(如“有限公司”) ，我们先在第一个单 元格中输入该文本，然后用“填充柄”将其复制到后续的单元格中。 快速给数字加上单位 有时我们需要给输入的数值加上单位(如“立方米”等) ，少量的我们可以直接输入，而大量的如果一个一个 地输入就显得太慢了。我们用下面的方法来实现单位的自动输入:先将数值输入相应的单元格中(注意:仅 限于数值) 然后在按住 Ctrl 键的同时， ， 选取需要加同一单位的单元格， 单击“格式?单元格”命令， 打开“单 元格格式”对话框() ，在“数字”标签中，选中“分类”下面的“自定义”选项，再在“类型”下面的方框中输入 “#”“立”“方”“米”，按下确定键后，单位(立方米)即一次性加到相应数值的后面。 取消单元格链接 将链接单元格格式更改为数值格式就可以取消单元格链接， 方法如下:选择包含链接的单元格， 然后单击“复 制”按钮，保持该格处于选中状态，单击鼠标右键选择“选择性粘贴”命令选择“数值”后单击“确定”，单元格 内容就被单元格实际数值替代，与其它源文件的链接就删除了。 快速输入拼音 选中已输入汉字的单元格，然后单击“格式?拼音信息?显示或隐藏”命令，选中的单元格会自动变高，再 单击“格式?拼音信息?编辑”命令，即可在汉字上方输入拼音。单击“格式?拼音信息?设置”命令，可以 修改汉字与拼音的对齐关系。 4 插入“?” 插入 首先选择要插入“?”的单元格，在字体下拉列表中选择“Marlett”字体，输入 a 或 b，即在单元格中插入了“?” 输入公式 单击将要在其中输入公式的单元格， 然后键入,(等号)， 若单击了“编辑公式”按钮或“粘贴函数”按钮， Excel 将插入一个等号，接着输入公式内容，按 Enter 键。 隐藏单元格中的所有值( 技巧大赛获奖作品) 隐藏单元格中的所有值(微软 Office 技巧大赛获奖作品) 有时候，我们需要将单元格中所有值隐藏起来，这时你可以选择包含要隐藏值的单元格。单击“格式?单 元格”命令，选择“数字”选项卡，在“分类”列表中选择“自定义”，然后将“类型”框中已有的代码删除，键入 “;;;”(3 个分号)即可() 提示:你也许奇怪为什么会这样，其实单元格数字的自定义格式是由正数、负数、 。 零和文本 4 个部分组成。这 4 个部分用 3 个分号分隔，哪个部分空，相应的内容就不会在单元格中显示。 现在都空了，当然就都不显示了。 恢复隐藏列 通常，选择隐藏列两边列上的单元格，然后选择“格式?列?取消隐藏”菜单项可以恢复隐藏的列。其实恢 复隐藏列还有一种快捷方法:将鼠标指针放置在列标的分割线上，例如，若隐藏 B 列，则将鼠标指针放置 在列 A 和列 C 的分割线上， 轻轻地向右移动鼠标指针， 直到鼠标指针从两边有箭头的单竖变为两边有箭头 的双竖杠，此时拖动鼠标就可以打开隐藏的列。 快速输入自定义短语 使用该功能可以把经常使用的文字定义为一条短语，当输入该条短语时，“自动更正”便会将它更换成所定 义的文字。定义“自动更正”项目的方法如下:单击“工具?自动更正选 项”命令，在弹出的“自动更正”对话框 中的“替换”框中键入短语，如“电脑报”，在“替换为”框中键入要替换的内容，如“电脑报编辑部”，单击“添 加”按钮，将该项目添加到项目列表中，单击“确定”退出。以后只要输入“电脑报”，则“电脑报编辑部”这个 短语就会输到表格中。 快速输入日期和时间 当前日期 选取一个单元格，并按“Ctrl+;” 当前时间 选取一个单元格，并按“Ctrl+Shift+;” 当前日期和时间 选取一个单元格，并按“Ctrl+;”，然后按空格键，最后按“Ctrl+Shift+;” 注意:当你使用这个技巧插入日期和 时间时，所插入的信息是静态的。要想自动更新信息，你必须使用 TODAY 和 NOW 函数。 5 快速复制单元格内容 选中下面的单元格，按“Ctrl+' 为英文状态下的单引号)”组合键，即可将上一单元格的内容快速复制下来。 ( 使用自定义序列排序( 技巧大赛获奖作品) 使用自定义序列排序(微软 Office 技巧大赛获奖作品) 在 Excel 排序对话框中选择主要关键字后单击选项，可以选择自定义序列作为排序次序，使排序方便快捷 且更易于控制。顺便说一下，排序可以选择按列或按行，如果以前排行的顺序都是选择性粘贴转置，排完 序后再转置。 自定义排序只应用于“主要关键字”框中的特定列。 在“次要关键字”框中无法使用自定义排序。 若要用自定义排序对多个数据列排序，则可以逐列进行。例如，要根据列 A 或列 B 进行排序，可先根据列 B 排序，然后通过“排序选项”对话框确定自定义排序次序，下一步就是根据列 A 排序。 快速格式化 Excel 单元格 如果想要快速访问 Excel 中的单元格格式对话框，以更改诸如字样、对齐方式或边框等，请先选择需要格 式化的单元格然后按下“Ctrl+1”组合键，就可以打开“单元格格式对话框”。 建立文本与图表文本框的链接 在工作表的空白单元格内输入要链接的文本，单击选中图表，在编辑栏输入等号，然后单击包含要链接文 本的单元格，接着按回车键，该文本就出现在图表中的某个位置上了。这样，不管什么时候工作表单元格 内的文本发生变化时，图表内的文本也随着改变。但要注意的是，一旦你取消了选中文本框，就很难再用 鼠标选中该文本框进一步地修改，此时你可以使用我们前面介绍过的图表元素选取方法，即使用箭头键移 动选择图表元素。 给图表增加新数据系列 有时我们需要对已创建好的图表增加新的数据系列，虽然你可以重新创建包含新数据系列的图表，但对已 经存在的图表增加新数据系列显得更为简单、方便。 方法一:使用“数据源”对话框 激活图表，单击“图表 ?源数据?系列”，单击“添加”按钮，在“名称”栏中指定数据系列的名称，在“值”栏中指定新的数据系列， 单击“确定”按钮即可() 方法二:使用“选择性粘贴”对话框 选择要增加的数据系列并将其复制到剪贴板 。 上，然后激活图表，单击“编辑”菜单中的“选择性粘贴”命令，出现“选择性粘贴”对话框，选择添加单元格 为“新系列”，并选择合适的数值轴，然后单击“确定”按钮即可。 方法三:拖动鼠标法 选择要增加为新数据 系列的单元格区域，鼠标指针指向区域的边框，把它拖到图表中。当你松开鼠标按钮的时候，图表中就增 加了新的数据系列。注意:这一方法仅对内嵌式图表起作用。 方法四:使用“添加数据”对话框 激活图表， 单击“图表?添加数据”命令， 然后选择要增加为新数据系列的单元格区域，单击“确定”按钮即可。 6 快速修改图表元素的格式 通常，我们通过使用“格式”菜单或者选定图表元素后单击鼠标右键，从快捷菜单中选择“格式”命令来对图 表元素进行格式化。其实还有快速的方法:双击图表元素，将会调出此图表元素的格式对话框。根据选择的 图表元素不同，此对话框会有所不同。 创建复合图表 复合图表指的是由不同图表类型的系列组成的图表，比如，你可以让一个图表同时显示折线图和柱形图。 创建一个复合图表只需简单地将一个或一个以上的数据系列转变成其他的图表类型。 方法是:选择某个数据 系列，单击“图表?图表类型”命令，然后选择你所要应用到数据系列上的图表类型，单击“确定”按钮即可。 对度量不同的数据系列使用不同坐标轴 你需要绘制度量完全不同的数据系列， 如果你使用同 有时， 你需要绘制度量完全不同的数据系列， 样的坐标轴，那么很可能某个系列几乎是不可见的。 样的坐标轴，那么很可能某个系列几乎是不可见的。为了 使得每个系列都清晰可见，你可以使用辅助坐标轴。 使得每个系列都清晰可见，你可以使用辅助坐标轴。要为 某个数据系列指定一个辅助坐标轴， 首先要选定图表中的 某个数据系列指定一个辅助坐标轴， 这个数据系列，按右键弹出快捷莱单，单击“数据系列格 这个数据系列，按右键弹出快捷莱单，单击 数据系列格 坐标轴”命令 次坐标轴”选项 式?坐标轴 命令，选择 次坐标轴 选项。给图表增加新 坐标轴 命令，选择“次坐标轴 选项。 数据系列 有时我们需要对已创建好的图表增加新的数据系列，虽然你可以重新创建包含新数据系列的图表，但对已 经存在的图表增加新数据系列显得更为简单、方便。 方法一:使用“数据源”对话框 激活图表，单击“图表 ?源数据?系列”，单击“添加”按钮，在“名称”栏中指定数据系列的名称，在“值”栏中指定新的数据系列， 单击“确定”按钮即可() 方法二:使用“选择性粘贴”对话框 选择要增加的数据系列并将其复制到剪贴板 。 上，然后激活图表，单击“编辑”菜单中的“选择性粘贴”命令，出现“选择性粘贴”对话框，选择添加单元格 为“新系列”，并选择合适的数值轴，然后单击“确定”按钮即可。 方法三:拖动鼠标法 选择要增加为新数据 系列的单元格区域，鼠标指针指向区域的边框，把它拖到图表中。当你松开鼠标按钮的时候，图表中就增 加了新的数据系列。注意:这一方法仅对内嵌式图表起作用。 方法四:使用“添加数据”对话框 激活图表， 单击“图表?添加数据”命令，然后选择要增加为新数据系列的单元格区域，单击“确定”按钮即可。 快速修改图表元素的格式 通常，我们通过使用“格式”菜单或者选定图表元素后单击鼠标右键，从快捷菜单中选择“格式”命令来对图 表元素进行格式化。其实还有快速的方法:双击图表元素，将会调出此图表元素的格式对话框。根据选择的 图表元素不同，此对话框会有所不同。 7 创建复合图表 复合图表指的是由不同图表类型的系列组成的图表，比如，你可以让一个图表同时显示折线图和柱形图。 创建一个复合图表只需简单地将一个或一个以上的数据系列转变成其他的图表类型。 方法是:选择某个数据 系列，单击“图表?图表类型”命令，然后选择你所要应用到数据系列上的图表类型，单击“确定”按钮即可。 对度量不同的数据系列使用不同坐标轴 有时，你需要绘制度量完全不同的数据系列，如果你使用同样的坐标轴，那么很可能某个系列几乎是不 可见的。 为了使得每个系列都清晰可见， 你可以使用辅助坐标轴。 要为某个数据系列指定一个辅助坐标轴， 首先要选定图表中的这个数据系列，按右键弹出快捷莱单，单击“数据系列格式?坐标轴”命令，选择“次坐 标轴”选项。 将自己满意的图表设置为自定义图表类型 Excel 中提供了一些自定义图表类型。其实，你可以将自己创建的图表设置为自定义图表类型，以便以后 使用。具体方法为:创建要设置为自定义图表类型的图表，直到你满意为止。激活此图表，单击“图表?图 表类型?自定义类型”，选择“自定义”选项，将会显示所有用户自定义图表类型的一个列表。单击“添加” 按钮，将会出现“添加自定义图表类型”对话框，为你的图表类型输入一个名称和简短的说明，然后单击“确 定”，这样你定制的自定义图表类型就被加入到列表中了。 复制自定义图表类型 如果你希望将你定制的自定义图表类型复制到其他电脑中，只需要简单地把 C:\Windows\ApplicationData\Microsoft Excel 文件夹中的 xlusrgal.xls 文件复制到其他机器的同样文件夹中即 可。 旋转三维图表 你可以非常灵活地旋转调节三维图表的视觉角度，以获得不同的视觉效果。 方法一:使用“设置三维视图 格式”对话框 激活三维图表，单击“图表?设置三维视图格式”命令，选择合适的控制命令或数据来旋转和 进行透视改变() 方法二:使用鼠标实时拖动“角点”旋转图表 点击图表，图表边角出现黑色的控制点(称 。 为“角点”)。你可以拖动一个角点，旋转图表的三维框直到满意为止。如果一旦你的图表被完全搞乱了，不 要紧，你可以单击“图表?设置三维视图格式”命令，单击“默认值”按钮，恢复原来的标准三维视图。当你 旋转三维图表时，如果在拖动的同时按下 Ctrl 键，则可以看到全图的外廓，这样使你看得更清楚，不至于 把图表搞得奇形怪状的。 拖动图表数据点改变工作表中的数值 选择图表数据系列中的一个数据点，然后按照数值增大或减少的方向拖动数据点，你会发现工作表中的 相应数值随着图中数据点的新位置改变而改变。如果你知道一个图的外形以及你要确定能生成该图的数 值，这种技巧就显得非常有用。但要注意的是，这种方法在大多数情况下是危险的，因为你可能在不经意 间更改了不应该更改的数值。 把图片合并进你的图表 8 Excel 能很容易地把一个图案、图形文件作为组成元素合并到图表中。 方法一:使用“图案”对话框 双击某 个数据系列，选择“图案”标签，单击“填充效果”按钮，在“填充效果”对话框中选择“图片”标签，单击“选择 图片”按钮，选择一个要使用的图形文件即可。 方法二:使用剪贴板 将图像复制到剪贴板上，激活图表， 选择数据系列或数据系列中的一个数据点，再单击“编辑?粘贴”命令。这种方法适用于需要调用的图像不 在文件中的时候，只要图像可以复制到剪贴板上，则这种方法就可行。 方法三:使用链接图片 我们知道， 图表中可以使用数据表。如果你觉得图表中的数据表不是很灵活的话，你可以粘贴链接图片到图表代替数 据表。下面是具体的操作方法: 创建好图表，并将数据表使用的单元格区域按你的需要进行格式化。选定 需要的单元格区域，按住 Shift 键，单击“编辑?复制图片”命令，出现一个“复制图片”对话框，单击“确定” 接受默认选项。这样，选定的单元格区域就作为一个图片复制到剪贴板中了。激活图表，将剪贴板中的内 容粘贴到图表。此时所粘贴的是一幅图，还不是链接的表，还需要你选择粘贴的图片。在编辑栏输入链接 的单元格区域 (或直接用鼠标选择)。这样，粘贴的图片就变成与工作表数据区域链接的图片，对于工作表 单元格区域中的任何改变，都会直接反映在图表链接的图片中。 用图形美化工作表 我们在制作 Excel 工作表时，呆板的横竖显示的工作表可能没有多大的说服力。如果需要使用让人印象深 刻的图形来吸引人们的注意，首先在“绘图”工具栏上按下“自选图形”按钮，然后选择“其他自选图形”，从 中选择一个你需要的图案。插入此图形后，在工作表中选中它，单击“编辑栏”，输入你想要突出显示的链 接单元格，然后回车。这样，链接单元格的内容将会出现在所选定的图案上。如果链接单元格的内容变动 时，图案上的数据也会跟着反映出变动情形。另外，如果想要让自选图形更加醒目的话，你可以用鼠标双 击图形，打开“设置自选图形格式”对话框。在这个对话框中，你可以改变目前所使用的格式，例如调整文 字水平或垂直的位置、改变字体和字形、增加文字色彩等。 让文本框与工作表网格线合二为一 在“绘图”工具栏中单击“文本框”按钮，然后按住“Alt”键插入一文本框，就能保证文本框的边界与工作表网 格线重合。 快速创建默认图表 我们知道，创建图表一般使用“图表向导”分 4 个步骤来完成，在每个步骤中你可以根据需要调整各个选项 的设置。其实，如果你想使用默认的图表类型、图表选项和格式而不加修改直接生成图表的话，有快速的 方法:打开包含用来制作图表数据的工作表， 选取用来制作图表的数据区域， 然后按 F11 键即可快速创建图 表，图表存放在新工作表图表中，它是一个二维柱形图。 快速创建内嵌式图表 在工作表中选取用来制作图表的数据区域，然后单击“默认图表”按钮即可，不过，一般缺省时，“默认图 表”工具按钮不会显示在工具栏上，你可以通过下面的方法把“默认图表”按钮显示出来:单击“工具?自定义 ?命令”，在“类别”列表中选择“制作图表”，并在“命令”列表中找到“默认图表”，用鼠标将它拖到屏幕上工 具栏中适当的位置即可。 改变默认图表类型 Excel 的默认图表类型是二维柱形图连同一个浅灰色区域、一个在右边的图例以及水平网格线。如果你不 喜欢这种默认图表类型，可以通过以下方法来改变它:单击“图表?图表类型”命令，选择一个你想作为默认 值的图表类型(它可以是标准类型或自定义类型中的一种)，然后单击“设置为默认图表”按钮，确认即可。 9 如果你需要创建很多个同一类型的图表，你就可以通过这种改变默认图表类型的方法来提高你的效率。 快速转换内嵌式图表与新工作表图表 你可以轻易转换内嵌式图表与新工作表图表，方法是:选择你已创建的图表，可以看到 Excel 的“数据”菜 单变为“图表”菜单，单击“图表?位置”命令，出现“图表位置”对话框，你可以在“作为新工作表插入”和“作 为其中的对象插入”两者之间作出选择(图 5) ，同时选择一个工作表。这样，Excel 将删除原来的图表，以 你选择的方式移动图表到指定的工作表中。 利用图表工具栏快速设置图表 通常，我们使用“图表”菜单中的命令来对图表进行适当的设置。其实，我们可以用鼠标右键单击工具栏中 的任意位置， 在出现的快捷菜单中选择“图表”。 这样就激活了图表工具栏， 我们看到其中包含了“图表对象”、 “图表区格式”、“图表类型”、“图例”、“数据 表”、 “按行”、“按列”、“顺时针斜排’’和“逆时针斜排”等按钮(图 6) ，当然你还可以通过自定义的方法将“默认图表”等其他一些制作图表的工具按钮拖到图表工具栏中。使 用图表工具栏比使用“图表”菜单更方便，而且速度更快。 快速选取图表元素 图表创建好以后，我们还需要对某些图表元素进行编辑和格式化。图表区包括整个图表和它的全部元素， 当你选取图表区后，你就可以看到 8 个黑色小方块。要想调整单个的图表对象，首先必须选取该对象，然 后更改其属性。通过把鼠标指向某一特定图表对象来选取该对象，可能会很困难，特别是在一个带有许多 元素很拥挤的小图表中。在这种情况下，我们一般可以使用位于“图表”工具栏上左侧的“图表对象”下拉列 表，从该下拉列表中选取的任何项目，也就等于在当前图表中选取了那个项目。 其实，还有一种选取图 表元素方法，即在选取图表的任何一部分以后，你可以通过使用箭头键快速、连续地移向同一图表中的其 它部分。 使用向上或向下的箭头键可以选取主要的图表元素;使用向左或向右的箭头键可以连续地选取图表 每一个可以选取的元素，包括每一个数据系列中的单个数据点，以及图例中的彩色图例符号和文本条目。 通过一次按键创建一个 Excel 图表 这是一个非常老套的 Excel 窍门。要想使用键盘快速创建一个图表，选择你需要绘制的数据并按下 F11 键。Excel 将自动为你创建图表。另外，在选好单元格后按下“Alt+F1”，你将得到相同的结果。 绘制平直直线 绘制平直直线 在 Excel 绘制直线时是不是很难,其实，在应用直线绘制工具时，只要按下 Shift 键，则绘制出来的直线就 是平直的。另外，按下 Shift 键绘制矩形即变为正方形、绘制椭圆形即变为圆形。 一、 函数和公式编辑技巧 巧用 IF 函数清除 Excel 工作表中的 0 有时引用的单元格区域内没有数据，Excel 仍然会计算出一个结果“0”，这样使得报表非常不美观，看起来 也很别扭。怎样才能去掉这些无意义的“0”呢,利用 IF 函数可以有效地解决这个问题。 IF 函数是使用比 较广泛的一个函数，它可以对数值的公式进行条件检测，对真假值进行判断，根据逻辑测试的真假返回不 10 同的结果。它的表达式为:IF(logical_test,value_if_true,value_if_false)，logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。例如 A1>=100 就是一个逻辑表达式，如果 A1 单元格中的值大于等于 100 时， 表达式结果即为 TRUE，否则结果为 FALSE;value_if_true 表示当 logical_test 为真时返回的值，也可是公 式;value_if_false 表示当 logical_test 为假时返回的值或其他公式。所以形如公式“,IF(SUM(B1:C1)， SUM(B1:C1),“”)”所表示的含义为:如果单元格 B1 到 C1 内有数值， 且求和为真时， 区域 B1 到 C1 中的数值 将被进行求和运算。反之，单元格 B1 到 C1 内没有任何数值，求和为假，那么存放计算结果的单元格显示 为一个空白单元格。 批量求和 对数字求和是经常遇到的操作，除传统的输入求和公式并复制外，对于连续区域求和可以采取如下方法: 假定求和的连续区域为 m×n 的矩阵型， 并且此区域的右边一列和下面一行为空白， 用鼠标将此区域选中并 包含其右边一列或下面一行，也可以两者同时选中，单击“常用”工具条上的“Σ”图标，则在选中区域的右边 一列或下面一行自动生成求和公式，并且系统能自动识别选中区域中的非数值型单元格，求和公式不会产 生错误。 对相邻单元格的数据求和 如果要将单元格 B2 至 B5 的数据之和填入单元格 B6 中，操作如下:先选定单元格 B6，输入“=”， 再双击 常用工具栏中的求和符号“?”;接着用鼠标单击单元格 B2 并一直拖曳至 B5，选中整个 B2,B5 区域，这时 在编辑栏和 B6 中可以看到公“=sum(B2:B5)”， 单击编辑栏中的“?” (或按 Enter 键)确认， 公式即建立完毕。 此时如果在 B2 到 B5 的单元格中任意输入数据，它们的和立刻就会显示在单元格 B6 中。 同样的，如果 要将单元格 B2 至 D2 的数据之和填入单元格 E2 中， 也是采用类似的操作，但横向操作时要注意:对建立公 式的单元格(该例中的 E2)一定要在“单元格格式”对话框中的“水平对齐”中选择“常规”方式 , 这样在单元格 内显示的公式不会影响到旁边的单元格。 如果还要将 C2 至 C5、D2 至 D5、E2 至 E5 的数据之和分别填 入 C6、D6 和 E6 中，则可以采取简捷的方法将公式复制到 C6、D6 和 E6 中:先选取已建立了公式的单元格 B6，单击常用工具栏中的“复制”图标，再选中 C6 到 E6 这一区域，单击“粘贴”图标即可将 B6 中已建立的 公式相对复制到 C6、D6 和 E6 中。 对不相邻单元格的数据求和 假如要将单元格 B2、C5 和 D4 中的数据之和填入 E6 中，操作如下: 先选定单元格 E6，输入“=”，双击常 用工具栏中的求和符号“?”;接着单击单元格 B2，键入“，”，单击 C5，键入“，”，单击 D4，这时在编辑栏 和 E6 中可以看到公式“=sum(B2，C5，D4)”，确认后公式即建立完毕。 利用公式来设置加权平均 加权平均在财务核算和统计工作中经常用到，并不是一项很复杂的计算，关键是要理解加权平均值其实 就是总量值(如金额)除以总数量得出的单位平均值，而不是简单的将各个单位值(如单价)平均后得到 的那个单位值。在 Excel 中可设置公式解决(其实就是一个 除法 二年级余数除法练习二年级余数除法竖式二年级余数除法题算式二年级余数除法竖式题除数是一位数的除法练竖式计算 算式) ，分母是各个量值之和，分子是相应 的各个数量之和，它的结果就是这些量值的加权平均值。 自动求和 在老一些的 Excel 版本中，自动求和特性虽然使用方便，但功能有限。在 Excel 2002 中，自动求和按钮被 链接到一个更长的公式列表， 这些公式都可以添加到你的工作表中。 借助这个功能更强大的自动求和函数， 你可以快速计算所选中单元格的平均值， 在一组值中查找最小值或最大值以及更多。使用方法是:单击列号 11 下边要计算的单元格，或者单击行号右边要计算的单元格，单击常用工具栏上自动求和按钮右边的箭头， 并单击要用的公式，然后按 Enter。 用记事本编辑公式 在工作表中编辑公式时， 需要不断查看行列的坐标， 当编辑 在工作表中编辑公式时， 需要不断查看行列的坐标， 的公式很长时，编辑栏所占据的屏幕面积越来越大， 的公式很长时，编辑栏所占据的屏幕面积越来越大，正好 将列坐标遮挡，想看而看不见 非常不便 非常不便～ 将列坐标遮挡，想看而看不见,非常不便～能否用其它方 法来编辑公式呢, 打开记事本，在里面编辑公式，屏幕 法来编辑公式呢, 打开记事本，在里面编辑公式， 位置、字体大小不受限制，还有滚动条 其结果又是纯文 位置、字体大小不受限制，还有滚动条,其结果又是纯文 本格式， 可以在编辑后直接粘贴到对应的单元格中而勿需 本格式， 可以在编辑后直接粘贴到对应的单元格中而勿需 转换，既方便，又避免了以上不足。 转换，既方便，又避免了以上不足。管理加载宏 Excel 包括各种特殊作用的加载宏，它们使用自定义的函数、向导、对话框和其他工具，扩充了工作表的 基本功能。 默认情况下， 每个加载宏都配置为在第一次使用时安装， 也就是说在第一次需要某个加载宏时， 都要找 Office 光盘安装，这是非常麻烦的事。为了避 免这种情况，你可以一次性将以后可能需要的加载宏 安装， 或者全部安装它们。 单击“工具?加载宏”， 出现“加载宏”对话框， 选择可能对你有用的加载宏， 如“分 析工具库”、“规划求解”、“条件求和向导”等，单击“确定”，Excel 会提示所选加载宏尚没有安装，询问是 否现在安装，选择“是”，然后插入 Office 安装光盘完成安装。 不要在每次启动 Excel 时加载每个加载宏， 因为这样将减慢启动过程，而且每个加载宏都占用了大量的内存。建议你将“自动保存”加载，井设置适当 的“自动保存时间间隔”，这样在 Excel 使用过程中能自动创建备份文件，避免了掉电时丢失尚未保存的文 件内容。 在工作表之间使用超级连接 首先需要在被引用的其他工作表中相应的部分插入书签，然后在引用工作表中插入超级链接，注意在插 入超级链接时，可以先在“插入超级链接”对话框的“链接到文件或 URL”设置栏中输入目标工作表的路径和 名称，再在“文件中有名称的位置”设置栏中输入相应的书签名，也可以通过“浏览”方式选择。完成上述操 作之后，一旦使用鼠标左键单击工作表中带有下划线的文本的任意位置，即可实现 Excel 自动打开目标工 作表并转到相应的位置处。 快速链接网上的数据 你可以用以下方法快速建立与网上工作簿数据的链接: 1.打开 Internet 上含有需要链接数据的工作簿， 并在 工作簿选定数据，然后单击“编辑?复制”命令; 2.打开需要创建链接的工作簿，在需要显示链接数据的区域 中，单击左上角单元格; 3.单击“编辑?选择性粘贴”命令，在“选择性粘贴”对话框中，选择“粘贴链接”按钮 即可。 若你想在创建链接时不打开 Internet 工作簿，可单击需要链接处的单元格，然后键入(=)和 URL 地 址及工作簿位置，如:=[filel.xls]。 跨表操作数据 设有名称为 Sheet1、Sheet2 和 Sheet3 的 3 张工作表，现要用 Sheet1 的 D8 单元格的内容乘以 40,，再加 12 上 Sheet2 的 B8 单元格内容乘以 60,作为 Sheet3 的 A8 单元格的内容，则应该在 Sheet3 的 A8 单元格输入 以下算式:=Sheet1!D8,40,，Sheet2!B8,60,。 查看 Excel 中相距较远的两列数据 在 Excel 中， 若要将距离较远的两列数据(如 A 列与 Z 列)进行对比，只能不停地移动表格窗内的水平滚动 条来分别查看，这样的操作非常麻烦而且容易出错。利用下面这个小技巧，你可以将一个数据表“变”成两 个，让相距较远的数据同屏显示。 把鼠标指针移到工作表底部水平滚动条右侧的小块上，鼠标指针便会 变成一个双向的光标。把这个小块拖到工作表的中部，你便会发现整个工作表被一分为二，出现了两个数 据框，而其中的都是当前工作表内的内容。这样你便可以让一个数据框中显示 A 列数据，另一个数据框中 显示 Z 列数据，从而可以进行轻松的比较。 ( 技巧大赛获奖作品) 如何消除缩位后的计算误差 微软 Office 技巧大赛获奖作品) 有时我们输入的数字是小数点后两位数，但是在精度要求上只要一位，缩位后显示没问题，但其计算结 果却是有误差的。解决方法是:单击“工具?选项?重新计算”，选中“以显示值为准”() ，这样计算结果就 没有误差了。 事实上并不是计算上有误差，而是显示设置的四舍五入。采用本技巧提供的方法，可以解 决显示中的问题，但同时会改变数值的精度，在使用前 Excel 会给你一个警告。 利用选择性粘贴命令完成一些特殊的计算 如果某 Excel 工作表中有大量数字格式的数据，并且你希望将所有数字取负，请使用选择性粘贴命令，操 作方法如下: 在一个空单元格中输入“-1”，选择该单元格，并点击“编辑?复制”命令，选择目标单元格。点 击“编辑?选择性粘贴”命令，选中粘贴栏下的数值和运算栏下的乘，点击“确定”，所有数字将与-1 相乘。 你也可以使用该方法将单元格中的数值缩小 1000 或更大倍数。 Web 查询 保持 Excel 工作表总是最新 Web 页上经常包含适合在 Excel 中进行分析的信息，例如，可以在 Excel 中使 用直接从 Web 页上获取的信息分析股票报价。但如果你需要经常更新信息就必须借助 Web 页上的最新信 息。现在 Excel 2002 可以用可刷新 Web 查询简化这个任务来创建新的可刷新 Web 查询，方法如下: 在浏 览器中浏览要查询数据的 Web 页，把数据复制并粘贴到 Excel 工作表中。在粘贴的数据下方将出现一个粘 贴选项智能标记，单击粘贴选项智能标记右边的箭头，再单击创建可刷新的 Web 查询，在新建 Web 查询 对话框中，点击你想要查询的数据表前面的黄色箭头，单击导入。 在 Excel 中可以手动或自动刷新这个数 据。手动刷新方法如下:在外部数据工具栏上，单击数据区域属性按钮，在刷新控制下面选中你想要的选项 的复选框。 注意:当你从 Web 站点获取数据时，可能会丢失一些格式或内容，像脚本、gif 图像或单个单 元中的数据列表。 在 Excel 中进行快速计算 查看一系列单元格的最大值的操作方法: 选择你感兴趣的单元格， 你将看到所选单元格的总和显示在状态 栏中。状态栏就是工作表窗口下方的水平区域。如果没有出现状态栏，单击视图菜单中的状态栏，鼠标右 击状态栏，然后单击最大值，现在你就可以在状态栏中看到最大值了。该方法可以计算选定单元格的平均 值、总和、最小值。此外，你还可使用该方法计算包含数字的单元格的数量(选择计数值) ，或者计算已 填充单元格的数量(选择计数) 。 13 自动筛选前 10 个 有时你可能想对数值字段使用自动筛选来显示数据清单里的前 n 个最大值或最小值，解决的方法是使用 “前 10 个”自动筛选。当你在自动筛选的数值字段下拉列表中选择“前 10 个”选项时，将出现“自动筛选前 10 个”对话框，这里所谓“前 10 个”是一个一般术语，并不仅局限于前 10 个，你可以选择最大或最小和定 义任意的数字，比如根据需要选择 8 个、12 个等。 ( 技巧大赛获奖作品) 同时进行多个单元格的运算 微软 Office 技巧大赛获奖作品) 如果我们现在有多个单元格的数据要和一个数据进行加减乘除运算，那么一个一个运算显然比较麻烦， 其实利用“选择性粘贴”功能就可以实现同时运算。下面我们一起来看一个实例。 我们要将 C1、C4、C5、 D3、E11 单元格数据都加上 25，那么可以这样做:首先在一个空白的单元格中输入 25，选中这个单元格后 点击鼠标右键选择“复制”。然后按住 Ctrl 键依次点击 C1、C4、C5、D3、E11 单元格，将这些单元格选中。 接下来点击鼠标右键选择“选择性粘贴”，在“选择性粘贴”对话框中勾选“运算”框内的“加”选项，点击“确 定”。现在我们可以看到，这些单元格中的数据都同时被加上了 25。 让 Excel 出现错误数据提示 Excel 除了可以对单元格或单元格区域设置数据有效性条件并进行检查外，还可以在用户选择单元格或单 元格区域时显示帮助性“输入信息”，也可以在用户输入了非法数据时提示“错误警告”。 选取单元格或单元 格区域，单击“数据?有效性”命令，单击“输入信息”选项卡，选定“选定单元格时显示输入信息”复选框， 输入标题，如“注意”，输入显示信 息如“这里应输入负数～”() ，单击“确定”按钮。此后，再选定那些单元 格或单元格区域时，Excel 将自动提示上述信息。 另外，你还可以对设置了有效性条件检查的单元格或单 元格区域，再设置“出错警告”信息，方法是:选取单元格或单元格区域，单击“数据?有效性”命令，单击“出 错警告”选项卡，选定“输入无效数据时显示出错警告”复选框() ，选择警告样式，输入标题如“警告”，输 入出错信息如“不能输入正数～”，然后单击“确定”按钮即可。此后，如果你在指定的单元格中输入了正数， Excel 将警告你“不能输入正数!” 超级连接”快速 用“超级连接 快速跳转到其它文件 超级连接 快速跳转到其它文件 用超级链接在各个位置之间跳转十分方便，若你要切换到其它文件，只需用鼠标指向带有下划线的蓝色 超级链接文件， 然后单击鼠标即可跳转到超级链接所指向的子位置上去， 看完后若要返回， 只需单击“Web” 工具栏上的“返回”按钮即可。 一、 设置技巧 定制菜单命令 你可以根据自己的要求来定制选项菜单。首先单击“工具?自定义”命令，打开其中的“命令”选项卡() ， 在左侧的“类别”窗口中选择欲增删的菜单类别。如果是增加菜单命令，你只需在右侧的“命令”格内进行选 择，将其拖至对应的菜单项，菜单自动打开并出现一黑线后，将其插入黑线指示的位置，在空白处单击鼠 标左键即可。如果是删除菜单命令，只须打开菜单选中需要删除的命令，按下鼠标左键将它拖至图中的“命 令”格中即可。也可在该示意图打开的情况下，打开菜单单击右键，选中“删除”命令即可。 设置菜单分隔线 14 Excel 工具栏按钮之间有分隔线，如果你喜欢，也可以在菜单中的命令之间添加分隔线。方法是:按住 Alt 键后拖动菜单。如果拖动方向为增大菜单间距，则自动在中间添加分隔线;如果拖动方向为减小菜单间距， 则自动去掉中间的分隔线。 备份自定义工具栏 在 C:,Windows,Application Data,Microsoft,Excel 文件夹中有个 Excel10(xlb 文件，这个文件保存了 你的自定义工具栏和其他屏幕位置上每一个可见的工具栏信息。所以，建议你将工具栏设置好后，为 Excell0(xlb 文件作拷贝，起个不同的名字，以备随时载入，恢复你的工具栏。 共享自定义工具栏 如果你建立了一个自定义工具栏并希望和其他人一起分享的话，你可以将它“附加”到一个工作簿中。单击 “工具?自定义?工具栏”，选择你的自定义工具栏，单击“附加”按钮() ，出现“附加工具栏”对话框，单击 “复制”按钮，即可将工具栏添加到一个工作簿中。 使用单文档界面快速切换工作簿 Excel 2002 采用了单文档界面，每打开一个工作簿，都会在任务栏中显示出来。因此，你可以通过单击任 务栏上的名称来快速切换工作簿，而不必在“窗口”菜单中选择打开的工作簿名称。如果你的 Excel 2002 没 有此项功能，可按以下方法设置:单击“工具?选项”命令，单击“视图”选项卡，选中“任务栏中的窗口”复选 框() ，单击“确定”按钮即可。 自定义工具栏按钮 单击“工具?自定义”命令，打开“自定义”对话框使 Excel 处于自定义模式，这时你 可以用鼠标右键单击工 具栏上的按钮图标，弹出快捷菜单，利用这个快捷莱单，我们可以完成好多自定义工作。 1.使用“命名” 改变工具按钮的名称; 2.使用“复制按钮图像”可以将按钮的图标复制到剪贴板中，然后插入到文本或表格 中、或者粘贴到另一个按钮上; 3.使用“编辑按钮图像”来调用按钮编 。 目 录 第一节 EXCEL 公式及函数的高级应用 1.1 数组公式及其应用 „„„„„„„„„„„„„„„„„„„„„„.„„„3 1.1.1 数组公式的输入、 数组公式的输入、编辑及删除 „„„„„„„„„„„„„„„..„„„„3 一( 数组公式的输入 „„„„„„„„„„„„„„„„.„.„„„„„„„3 编辑数组公式„„„„„„„„„„„„„„„„„„..„..„„.„„„„4 二( 编辑数组公式 15 删除数组公式„„„„„„„„„„„„„„„„„„„„„„..„„„„5 三( 删除数组公式 1.1.2 数组公式的应 用„„„„„„„„„„„„„„„„„„„„„.„„.„„..5 数组公式的应用 用数组公式计算两个数据区域的乘积„„„„„„„„„„„„„.„„„.5 一( 用数组公式计算两个数据区域的乘积 用数组公式计算多个数据区域的 和„„„„„„„„„„.„„„„„..„„„.5 二( 用数组公式计算多个数据区域的和 用数组公式同时对多个数据区域进行相同的计算„„„„„„„„„..„„„.6 三( 用数组公式同时对多个数据区域进行相同的计算 1.2 常用函数及其应用 常用函数及其应用„„„„„„„„„„„„„„„„„„„„„„„„.„.„„6 1.2.1 SUM 函数、SUMIF 函数和 SUMPRODUCT 函数 „„„„„„„„.„.„„6 函数、 一(无条件求和 SUM 函数„„„„„„„„„„„„„„„„„„„„.„„„6 函数 函数„„„„„„„„„„„„„„„„„„„„..„„„6 二(条件求和 SUMIF 函数 函数„„„„„„„„„„„„„„„„„„„.„„.„„.7 三(SUMPRODUCT 函数 1.2.2 AVERAGE 函数 函数„„„„„„„„„„„„„„„„„„.„„.„„„„„„.7 1.2.3 MIN 函数和 MAX 函数 函数„„„„„„„„„„„„„„„.„„..„„„..„„7 函数„„„„„„„„„„„.„„„..„„„..„.7 1.2.4 COUNT 函数和 COUNTIF 函数 1.2.5 IF 函数 函数„„„„„„„„„„„„„„„„„„„„„„.„„„..„„..„„8 1.2.6 AND 函数、OR 函数和 NOT 函数 函数、 函数„„„„„„„„„„„„„„„„..„„.8 1.2.7 LOOKUP 函数、VLOOKUP 函数和 HLOOKUP 函数 函数、 函数„„„„„„„„„„„..9 函数„„„„„„„„„„„„„„„„„„„„..„„„„„„.9 一(LOOKUP 函数 二(VLOOKUP 函数„„„„„„„„„„„„„„„„„„„„„„„„„„.9 函数 三(HLOOKUP 函数 „„„„„„„„„„„„„„„„..„„„..„„.„..„.10 1.2.8 MATCH 函数 „„„„„„„„„„„„„„„„„„„„..„„„.„..„10 1.2.9 INDEX 函数 函数„„„„„„„„„„„„„„„„„„„„„„„„„„„„„.10 返回数组中指定单元格或单元格数组的数值„„„„„„„„„„..„.„..10 一( 返回数组中指定单元格或单元格数组的数值 二( 返回引用中指定单元格 „„„„„„„„„„„„„„„..„„.„„„„„11 函数„„„„„„„„„„„„„„„„„„„..„.„„„„..11 1.2.10 ADDRESS 函数 1.2.11 INDIRECT 函数 函数„„„„„„„„„„„„„„„„„..„„.„„„„„.11 1.2.12 矩阵函数 矩阵函数——TRANSPOSE 函数、MINVERSE 函数和 MMULT 函数 函数、 函 数.„.„..12 一(TRANSPOSE 函数„„„„„„„„„„„„„„„„„„.„„„„..„„12 二(MINVERSE 函数„„„„„„„„„„„„„„„„„„„„„„.„.„..12 函数 三(MMULT 函数 „„„„„„„„„„„„„„„„„„„„„„„„„.„12 1.2.13 ROUND 函数 „„„„„„„„„„„„„„„„„„„.„„„„„..„.12 EXCEL 数据分析处理 第二节 2.1 数据排 序„„„„„„„„„„„„„„„„„„„„„„„„„„„..„..13 数据排序 2.1.1 数据排序的规则„„„„„„„„„„„„„„„„„„„.„„„„„..„.13 数据排序的规则 2.1.2 数据排序步 骤„„„„„„„„„„„„„„„„„„„„.„„„„„..„.13 数据排序步骤 2.1.3 自定义排序„„„„„„„„„„„„„„„„„„„„„„„„„.„..„.14 自定义排序 2.2 数据的查找与筛选„„„„„„„„„„„„„„„„..„„„„„.„..„.14 数据的查找与筛选 2.2.1 记录单查找„„„„„„„„„„„„„„„„„„„„„„„„„..„„.15 记录单查找 一( 查找数据记录 „„„„„„„„„„„„„„„„„..„„„„„..„„.15 修改或删除记录 或删除记录„„„„„„„„„„„„„„„„„„„„„„„„„.15 二( 修改或删除记录 添加新的记录„„„„„„„„„„„„„„„„„„„„„„.„„„„15 三( 添加新的记录 2.2.2 自动筛选与自定义筛选„„„„„„„„„„„„„„„„„„„„„„.15 自动筛选与自定义筛选 自动筛选„„„„„„„„„„„„„„„„„„„„„„„.„„.„„„15 一( 自动筛选 自定义筛选方式„„„„„„„„„„„„„„„„„„„.„.„„„„„16 二( 自定义筛选方式 2.2.3 高级筛选 高级筛选„„„„„„„„„„„„„„„„„„„„„„„.„..„„„„„.16 一般情况下的高级筛选„„„„„„„„„„„„„„„„.„..„„„„„.17 一(一般情况下的高级筛选 二(计算条件情况下的高级筛选 „„„„„„„„„„„„„.„.„„„„„„17 16 2.3 2.3.1 2.3.2 2.4 2.4.1 2.4.2 数据的分类与汇总„„„„„„„„„„„„„„„„„.„..„„„„„„18 数据的分类与汇总 进行分类汇总„„„„„„„„„„„„„„„„„„„„„„„..„„.„„18 进行分类汇总 分类汇总的撤 消„„„„„„„„„„„„„„..„„..„„„„..„„.„„.19 分类汇总的撤消 数据透视表„„„„„„„„„„„„„„„„„„„„„„„„..„„„..19 数据透视表 建立数据透视表„„„„„„„„„„„„„„„„„„„„„„„„.„„19 建立数据透视表 数据的透视分析„„„„„„„„„„„„„„..„„„„„„„„..„„„.20 数据的透视分析 3.1 3.2 3.3 3.4 3.5 第三节 EXCEL 图表处理 图表类型„„„„„„„„„„„„„„„„„„„„„„„„„„.„„„„.21 图表类型 图表的建立„„„„„„„„„„„„„„„„„„„.„„„„„.„„„„„21 图表的建立 图表的编辑、修改及格式化„„„„„„„„„„„„.„„„„.„„„„„„23 图表的编辑、修改及格式化 设置坐标、标题、图例等的格式 格式„„„„„„„„„„„„„„„„„„..23 一(设置坐标、标题、图例等的格式 改变 图表大小„„„„„„„„„„„„„„„„.„„„„„.„„„„„.23 二(改变图表大小 移动或复制图表„„„„„„„„„„„„„„„„„„„„„„„.„„.23 三(移动或复制图表 添加数据标 志„„„„„„„„„„„„„„„„„„„„„„„„„.„.„.23 四(添加数据标志 改变图表颜色、图案、边框„„„„„„„„„„„„„„„„..„„„„.24 五(改变图表颜色、图案、边框 地区销售分布图表的建 立„„„„„„„„„„„„„„„„„..„„„„„..24 地区销售分布图表的建立 动态图表的建立„„„„„„„„„„„„„„„„„„„„„.„„.„„„..25 动态图表的建立 EXCEL 数据分析工具的应用 第四节 模拟运算表„„„„„„„„„„„„„„„„„„„„„„„„„„„„.26 模拟运算表 单变量模拟运算表„„„„„„„„„„„„„„„„„„„„„„„„„26 单变量模拟运算表 双变量模拟运算表„„„„„„„„„„„„„„..„„„„„„„„„„..27 双变量模拟运算表 单变量求解„„„„„„„„„„„„„„„„„.„„„„„„„„„„..27 单变量求解 规划求 解„„„„„„„„„„„„„„„„„.„„„„„„.„„„„„..27 规划求解 求解优化问题„„„„„„„„„„„„„„„.„„„..„„„„„„„„.27 求解优化问题 求解方程组 „„„„„„„„„„„„„„„..„„„„„„„„„„„„29 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 分析„„„„„„„„„„„„„„„„.„„„„„„„„„„„„„30 方案分析 建立方案 „„„„„„„„„„„„„„„„„..„„„„„„„„.„„„30 显示方案„„„„„„„„„„„„„„„„„„„„„„„.„„„„„„.31 显示方案 修改、删除或增加方案„„„„„„„„„„„„„„„„„„„„„„„.31 修改、删除或增加方案 建立方案报告„„„„„„„„„„„„„„„„„„„„„„„„„„„.31 建立方案报告 数据分析工具 库„„„„„„„„„„„„„„„„„„„„„„„„„„„„„32 数据分析工具库 4.1 4.1.1 4.1.2 4.2 4.3 4.3.1 4.3.2 4.4 4.4.1 4.4.2 4.4.3 4.4.4 4.5 第一节 EXCEL 公式及函数的高级应用 公式和函数是 Excel 最基本、最重要的应用工具，是 Excel 的核心，因此，应对公式和函数熟练掌握， 才能在实际应用中得心应手。 1.1 数组公式及其应用 数组公式就是可以同时进行多重计算并返回一种或多种结果的公式。在数组公式中使用两组或多组数 17 据称为数组参数，数组参数可以是一个数据区域，也可以是数组常量。数组公式中的每个数组参数必须有 相同数量的行和列。 1.1.1 数组公式的输入、 数组公式的输入、编辑及删除 一(数组公式的输入 数组公式的输入步骤如下: (1)选定单元格或单元格区域。如果数组公式将返回一个结果，单击需要输入数组公式的单元格;如 果数组公式将返回多个结果，则要选定需要输入数组公式的单元格区域。 (2)输入数组公式。 (3)同时按“Crtl+Shift+Enter”组合键，则 Excel 自动在公式的两边加上大括号{ } 。 特别要注意的是，第(3)步相当重要，只有输入公式 后同时按“Crtl+Shift+Enter”组合键，系统才会把 公式视为一个数组公式。否则，如果只按 Enter 键，则输入的只是一个简单的公式，也只在选中的单元格 区域的第 1 个单元格显示出一个计算结果。 在数组公式中，通常都使用单元格区域引用，但也可以直接键入数值数组，这样键入的数值数组被称 为数组常量。当不想在工作表中按单元格逐个输入数值时，可以使用这种方法。如果要生成数组常量，必 须按如下操作: (1)直接在公式中输入数值，并用大括号“{ }”括起来。 (2)不同列的数值用逗号“,”分开。 (3)不同行的数值用分号“;”分开。 ? 输入数组常量的方法: 例如，要在单元格 A1:D1 中分别输入 10，20，30 和 40 这 4 个数值，则可采用下述的步骤: (1)选取单元格区域 A1:D1，如图 2-1 所示。 图 1-1 选取单元格区域 A1:D1 (2)在公式编辑栏中输入数组公式“={10,20,30,40}”，如图 2-2 所示。 图 1-2 在编辑栏中输入数组公式 (3)同时按 Ctrl+Shift+Enter 组合键，即可在单元格 A1、B1、C1、D1 中分别输入了 10、20、30、40， 如图 2-3 所示。 假若要在单元格 A1、B1、C1、D1、A2、B2、C2、D2 中分别输入 10、20、30、40、50、60、70、80，则 可以采用下述的方法: 图 1-3 同时按 Ctrl+Shift+Enter 组合键，得到数组常量 (1)选取单元格区域 A1:D2，如图 2-4 所示。 图 1-4 选取单元格区域 A1:D2 (2)在编辑栏中输入公式“={10,20,30,40;50,60,70,80}”，如图 2-5 所示。 18 图 1-5 在编辑栏中输入数组公式 (3)按 Ctrl+Shift+Enter 组合键，就在单元格 A1、B1、C1、D1、A2、B2、C2、D2 中分别输入了 10、 20、30、40 和 50、60、70、80，如图 2-6 所示。 图 1-6 同时按 Ctrl+Shift+Enter 组合键，得到数组常量 ? 输入公式数组的方法 例如，在单元格 A3:D3 中均有相同的计算公式，它们分别为单元格 A1:D1 与单元格 A2:D2 中数据的 和，即单元格 A3 中的公式为“=A1+A2”，单元格 B3 中的公式为“=B1+B2”，„，则可以采用数组公式的方 法输入公式，方法如下: (1)选取单元格区域 A3:D3，如图 2-7 所示。 (2)在公式编辑栏中输入数组公式“=A1:D1+A2:D2”，如图 2-8 所示。 图 1-7 选取单元格区域 A3:D3 图 1-8 在编辑栏中输入数组公式 (3)同时按 Ctrl+Shift+Enter 组合键，即可在单元格 A3:D3 中得到数组公式“=A1:D1+A2:D2”，如图 2-9 所示。 图 1-9 同时按 Ctrl+Shift+Enter 组合键，得到数组公式 二(编辑数组公式 数组公式的特征之一就是不能单独编辑、清除或移动数组公式所涉及的单元格区域中的某一个单元格。若 在数组公式输入完毕后发现错误需要修改，则需要按以下步骤进行: (1)在数组区域中单击任一单元格。 (2)单击公式编辑栏，当编辑栏被激活时，大括号“{ }”在数组公式中消失。 (3)编辑数组公式内容。 (4)修改完毕后，按“Crtl+Shift+Enter”组合键。要特别注意不要忘记这一步。 三(删除数组公式 删除数组公式的步骤是:首先选定存放数组公式的所有单元格，然后按 Delete 键。 1.1.2 数组公式的应用 19 一(用数组公式计算两个数据区域的乘积 【例 2-1】如图 2-10 所示，已经知道 12 个月的销售量和产品单价，则可以利用数组公式计算每个月的销 售额，步骤如下: 图 1-10 用数组公式计算销售额 (1)选取单元格区域 B4:M4。 (2)输入公式“=B2:M2*B3:M3”。 (3)按“Crtl+Shift+Enter”组合键。 如果需要计算 12 个月的月平均销售额，可在单元格 B5 中输入公式“=AVERAGE(B2:M2*B3:M3)”，然 后按“Crtl+Shift+Enter”组合键即可，如图 2-10 所示。 在数组公式中，也可以将某一常量与数组公式进行加、减、乘、除，也可以对数组公式进行乘幂、开 方等运算。例如在图 2-10 中，每月的单价相同，故我们也可以在单元格 B4:M4 中输入公式“=B2:M2*28”， 然 后 按 “Crtl+Shift+Enter” 组 合 键 ; 在 单 元 格 B5 中 输 入 公 式 “=AVERAGE(B2:M2*28)” ， 然 后 按 “Crtl+Shift+Enter”组合键。 在使用数组公式计算时，最好将不同的单元格区域定义不同的名称，如在图 2-10 中，将单元格区域 B2:M2 定义名称为“销售量”，单元格区域 B3:M3 定义名称为“单价”，则各月的销售额计算公式为“=销售量 *单价”，月平均销售额计算公式为“=AVERAGE(销售量*单价)”，这样不容易出错。 二(用数组公式计算多个数据区域的和 如果需要把多个对应的行或列数据进行相加或相减的运算，并得出与之对应的一行或一列数据时，也 可以使用数组公式来完成。 【例 2-2】 某企业 2002 年销售的 3 种产品的有关资料如图 2-11 所示， 则可以利用数组公式计算该企业 2002 年的总销售额，方法如下: 图 1-11 某企业的月销售总额计算 (1)选取单元格区域 C8:N8。 (2)输入公式“=C2:N2*C3:N3+C4:N4*C5:N5+C6:N6*C7:N7”。 (3)按“Crtl+Shift+Enter”组合键。 三(用数组公式同时对多个数据区域进行相同的计算 【例 2-3】某公司对现有三种商品实施降价销售，产品原价如图 2-12 所示，降价幅度为 20%，则可以 利用数组公式进行计算，步骤如下: 20 图 1-12 产品降价计算 (1)选取单元格区域 G3:I8。 (2)输入公式“=B3:D8*(1-20%)”。 (3)按 Crtl+Shift+Enter 组合键。 此外，当对结构相同的不同工作表数据进行合并汇总处理时，利用上述方法也将是非常方便的。有关 不同工作表单元格的引用可参阅第 1 章的有关内容，关于数据的合并计算可参阅本章 2.3.5 节的内容。 1.2 常用函数及其应用 常用函数及其应用 在第 1 节中介绍了一些有关函数的基本知识，本节对在财务管理中常用的一般函数应用进行说明，其 他有关的专门财务函数将在以后的有关章节中分别予以介绍。 1.2.1 SUM 函数、SUMIF 函数和 SUMPRODUCT 函数 函数、 在财务管理中，应用最多的是求和函数。求和函数有三个:无条件求和 SUM 函数、条件求和 SUMIF 函数和多组数据相乘求和 SUMPRODUCT 函数。 一(无条件求和 SUM 函数 该函数是求 30 个以内参数的和。公式为 = SUM(参数 1，参数 2，„，参数 N) 当对某一行或某一列的连续数据进行求和时，还可以使用工具栏中的自动求和按钮 。 例如，在例 2-1 中， 求全年的销售量，则可以单击单元格 N2，然后再单击求和按钮 ，按回车键即可， 如图 1-13 所示。 图 1-13 自动求和 二(条件求和 SUMIF 函数 SUMIF 函数的功能是根据指定条件对若干单元格求和，公式: =SUMIF(range,criteria,sum_range) 式中 range—用于条件判断的单元格区域; criteria—确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本; sum_range—需要求和的实际单元格。 只有当 range 中的相应单元格满足条件时，才对 sum_range 中的单元格求和。若省略 sum_range,则直 接对 range 中的单元格求和。 利用这个函数进行分类汇总是很有用的。 【 例 1-4 】 某 商 场 2 月 份 销 售 的 家 电 流 水 记 录 如 图 1-14 所 示 ， 则 在 单 元 格 I3 中 输 入 公 式 “=SUMIF(C3:C10,211,F3:F10)”，单元格 I4 中输入公式“=SUMIF(C3:C10,215,F3:F10)”，在单元格 I5 中输入 公式“=SUMIF(C3:C10,212,F3:F10)”，单元格 I6 中输入公式“=SUMIF(C3:C10,220,F3:F10)”，即可得到分类 销售额汇总表。 图 1-14 商品销售额分类汇总 SUMIF 函数的对话框如图 1-15 所示。 21 图 1-15 SUMIF 函数对话框 当需要分类汇总的数据很大时，利用 SUMIF 函数是很方便的。 三(SUMPRODUCT 函数 SUMPRODUCT 函数的功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。 公式为 = SUMPRODUCT(array1,array2,array3,„) 式中，array1,array2,array3,„„为 1 至 30 个数组。 需注意的是，数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。 对于非数值型的数组元素将作为 0 处理。 例如，在例 1-2 中，要计算 2002 年产品 A 的销售总额，可在任一单元格(比如 O2)中输入公式 “=SUMPRODUCT(C2:N2,C3:N3)”即可。 1.2.2 AVERAGE 函数 AVERAGE 函数的功能是计算给定参数的算术平均值。 公式为 = AVERAGE(参数 1，参数 2，„，参数 N) 函数中的参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文 字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。 AVERAGE 函数的使用方法与 SUM 函数相同，此处不再介绍。 1.2.3 MIN 函数和 MAX 函数 MIN 函数的功能是给定参数表中的最小值，MAX 函数的功能是给定参数表中的最大值。公式为 = MIN(参数 1，参数 2，„，参数 N) = MAX(参数 1，参数 2，„，参数 N) 函数中的参数可以是数字、空白单元格、逻辑值或表示数值的文字串。 例如，MIN(3，5，12，32)=3;MAX(3，5，12，32)=32。 1.2.4 COUNT 函数和 COUNTIF 函数 COUNT 函数的功能是计算给定区域内数值型参数的数目。 公式为: = COUNT(参数 1，参数 2，„，参数 N) COUNTIF 函数的功能是计算给定区域内满足特定条件的单元格的数目。 公式为: = COUNTIF(range，criteria) 式中 range—需要计算其中满足条件的单元格数目的单元格区域; criteria—确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。 COUNT 函数和 COUNTIF 函数在数据汇总统计分析中是非常有用的函数。 1.2.5 IF 函数 IF 函数也称条件函数，它根据参数条件的真假，返回不同的结果。在实践中，经常使用函数 IF 对数 值和公式进行条件检测。 公式为 = IF(logical_test,value_if_true,value_if_false) 式中 logical_test—条件表达式，其结果要么为 TRUE，要么为 FALSE，它可使用任何比较运算符; 22 value_if_true—logical_test 为 TRUE 时返回的值; value_if_false—logical_test 为 FALSE 时返回的值。 IF 函数在财务管理中具有非常广泛的应用。 【例 2-5】例如，某企业对各个销售部门的销售业绩进行评价，评价标准及各个销售部门在 2002 年的 销售业绩汇总如图 1-16 所示，评价计算步骤如下: 图 1-16 销售部门业绩评价 (1)选定单元格区域 C3:C12。 ( 2 ) 直 接 输 入 以 下 公 式 : “=IF(B3:B12<100000," 差 ",IF(B3:B12<200000," 一 般 ",IF(B3:B12<300000," 好 ",IF(B3:B12<400000,"较好","很好"))))”。 (3)按“Crtl+Shift+Enter”组合键。 则各个销售部门的销售业绩评价结果就显示在单元格域 C3:C12 中。 也 可 以 直 接 在 单 元 格 C3 中 输 入 公 式 “=IF(B3<100000," 差 ",IF(B3<200000," 一 般 ",IF(B3<300000," 好 ",IF(B3<400000,"较好","很好"))))”后，将其向下填充复制到 C4,C12 单元格中。 1.2.6 AND 函数、OR 函数和 NOT 函数 函数、 这 3 个函数的用法如下: = AND(条件 1，条件 2，„，条件 N) = OR(条件 1，条件 2，„，条件 N) = NOT(条件) AND 函数表示逻辑与， 当所有条件都满足时 (即所有参数的逻辑值都为真时) AND 函数返回 TRUE， ， 否则，只要有一个条件不满足即返回 FALSE。 OR 函数表示逻辑或，只要有一个条件满足时，OR 函数返回 TRUE，只有当所有条件都不满足时才返 回 FALSE。 NOT 函数只有一个逻辑参数，它可以计算出 TRUE 或 FALSE 的逻辑值或逻辑表达式。如果逻辑值为 FALSE，函数 NOT 返回 TRUE;如果逻辑值为 TRUE，函数 NOT 返回 FALSE。 这 3 个函数一般与 IF 函数结合使用。 【例 2-6】某企业根据各销售部门的销售额及销售费用确定奖金提成比例及提取额，若销售额大于 300000 元且销售费用占销售额的比例不超过 1%，则奖金提取比例为 15%，否则为 10%，则计算过程如下 (如图 2-17 所示) : (1) 在单元格 D3 中输入公式“=IF(AND(B3>300000,C3/B3<1%),15%,10%)”， 将其向下填充复制到 D4,C10 单元格中。 (2)选取单元格区域 E3:E10，输入公式“=B3:B10*D3:D10”，按“Crtl+Shift+Enter”组合键。 则各销售部门的销售奖金提成比例及奖金提取额如图 1-17 所示。 23 图 1-17 奖金提成比例及提取额的计算 1.2.7 LOOKUP 函数、VLOOKUP 函数和 HLOOKUP 函数 函数、 一(LOOKUP 函数 LOOKUP 函数的功能是返回向量(单行区域或单列区域)或数组中的数值。函数 LOOKUP 有两种语 法形式:向量和数组。函数 LOOKUP 的向量形式是在单行区域或单列区域(向量)中查找数值，然后返 回第二个单行区域或单列区域中相同位置的数值;函数 LOOKUP 的数组形式在数组的第一行或第一列查 找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。 (1)向量形式:公式为 = LOOKUP(lookup_value,lookup_vector,result_vector) 式中 lookup_value—函数 LOOKUP 在第一个向量中所要查找的数值，它可以为数字、文本、逻辑值 或包含数值的名称或引用; lookup_vector—只包含一行或一列的区域 lookup_vector 的数值可以为文本、数字或逻辑值; result_vector—为只包含一行或一列的区域其大小必须与 lookup_vector 相同。 (2)数组形式:公式为 = LOOKUP(lookup_value,array) 式中 array—包含文本、数字或逻辑值的单元格区域或数组它的值用于与 lookup_value 进行比较。 例如:LOOKUP(5.2,{4.2,5,7,9,10})=5。 注意: lookup_vector 的数值必须按升序排列，否则函数 LOOKUP 不能返回正确的结果。文本不区分 大小写。如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最 大数值。如果 lookup_value 小于 lookup_vector 中的最小值，函数 LOOKUP 返回错误值#N/A。 二(VLOOKUP 函数 VLOOKUP 函数的功能是在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定 列处的数值。公式为: = VLOOKUP(lookup_value，table_array，col_index_num，range_lookup) 式中 lookup_value—需要在数据表第一列中查找的数值，lookup_value 可以为数值、引用或文字串; table_array—需要在其中查找数据的数据表，可以使用对区域或区域名称的引用，例如数据库或数据清单; 如果 range_lookup 为 TRUE，则 table_array 的第一列中的数值必须按升序排列，否则函数 VLOOKUP 不能返回正确的数值，如果 range_lookup 为 FALSE，table_array 不必进行排序。table_array 的第一列中的 数值可以为文本、数字或逻辑值，且不区分文本的大小写; col_index_num—table_array 中待返回的匹配值的列序号; col_index_num 为 1 时，返回 table_array 第一列中的数值;col_index_num 为 2 时，返回 table_array 第 二列中的数值，以此类推。如果 col_index_num 小于 1，函数 VLOOKUP 返回错误值#VALUE!;如果 col_index_num 大于 table_array 的列数，函数 VLOOKUP 返回错误值#REF!。 range_lookup—逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。 如果其为 TRUE 或省略， 则返回近似匹配值， 也就是说， 如果找不到精确匹配值， 则返回小于 lookup_value 的最大数值;如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误 值#N/A。 VLOOKUP 函数在财务管理与分析中是一个经常用到的函数，因此熟悉它将会带来很大便利。在以后的有 关章节中会经常用到它。 例如，假设单元格 A1:A4 中的数据分别为 1、30、80 和 90，单元格 B1:B4 中的数据分别为 400、500、600 24 和 700，则有:VLOOKUP(5,A1:B4,2)=400，VLOOKUP(30,A1:B4,2)=500，VLOOKUP(79,A1:B4,2)=500， VLOOKUP(92,A1:B4,2)=700。 三(HLOOKUP 函数 HLOOKUP 函数的功能是从表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中 指定行处的数值。公式为: = (lookup_value，table_array，row_index_num，range_lookup) 式中 row_index_num—table_array 中待返回的匹配值的行序号。 row_index_num 为 1 时，返回 table_array 第一行的数值，row_index_num 为 2 时，返回 table_array 第 二行的数值，以此类推。如果 row_index_num 小于 1，函数 HLOOKUP 返回错误值 #VALUE!;如果 row_index_num 大于 table_array 的行数，函数 HLOOKUP 返回错误值#REF!。 式中的其他参数含义参阅 VLOOKUP 函数。 HLOOKUP 函数与 VLOOKUP 函数的区别是: 当比较值位于数据表的首行， 并且要查找下面给定行中 的数据时，使用函数 HLOOKUP;当比较值位于要进行数据查找的左边一列时，使用函数 VLOOKUP。 VLOOKUP 函数在首列进行检索，先得到的是行号，然后根据 col_index_num 参数指定的列标返回指定的 单元格数值;而 HLOOKUP 函数在首行进行检索，先得到的是列标，然后根据 row_index_num 参数指定的 行号返回指定的单元格数值。 1.2.8 MATCH 函数 MATCH 函数的功能是返回在指定方式下与指定数值匹配的数组中元素的相应位置。公式为: = MATCH(lookup_value,lookup_array,match_type) 式中 lookup_value—需要在数据表中查找的数值，可以是数值(数字、文本或逻辑值)或对数字、文本或 逻辑值的单元格引用; lookup_array—可能包含所要查找的数值的连续单元格区域，可以是数组或数组引用; match_type—数字-1、0 或 1，它指明 Excel 如何在 lookup_array 中查找 lookup_value。 查找方式如下:当 match_type 为-1 时，lookup_array 必须按降序排列，函数 MATCH 查找大于或等于 lookup_value 的最小数值;当 match_type 为 0 时，lookup_array 可以按任何顺序排列，函数 MATCH 查找 等于 lookup_value 的第一个数值;当 match_type 为 1 时，lookup_array 必须按升序排列，函数 MATCH 查 找小于或等于 lookup_value 的最大数值。 例如，MATCH(12,{23,43,12,55},0)=3，MATCH(40,{23,43,12,55})=1。 EXCEL 公式及函数的高级应用(5) 1.2.9 INDEX 函数 INDEX 函数的功能是返回表格或区域中的数值或对数值的引用。INDEX 函数有以下两种形式: 返回数组中指定单元格或单元格数组的数值。 一. 返回数组中指定单元格或单元格数组的数值。 公式为 = INDEX(array,row_num,column_num) 式中 array—单元格区域或数组常数; row_num—数组中某行的行序号， 函数从该行返回数值。 如果省略 row_num， 则必须有 column_num; column_num—数组中某列的列序号，函数从该列返回数值。如果省略 column_num，则必须有 row_num。 需 要 注 意 的 是 : 如 果 同 时 使 用 row_num 和 column_num ， 函 数 INDEX 返 回 row_num 和 column_num 交 叉处的单元格 的数值。 如果数组只包 含一行或 一列，则相对 应的参 数 row_num 或 column_num 为可选。如果数组有多行和多列，但只使用 row_num 或 column_num，函数 INDEX 返回数组 中的整行或整列，且返回值也为数组。如果将 row_num 或 column_num 设置为 0，函数 INDEX 则分别返 回整个列或行的数组数值。如果需要使用以数组形式返回的数值时，请在一个水平单元格区域中将函数 INDEX 作为数组公式输入。此外，row_num 和 column_num 必须指向 array 中的某一单元格，否则，函数 INDEX 返回错误值#REF!。 例如:INDEX({1,2;3,4},2,2) = 4。如果作为数组公式输入，则:INDEX({1,2;3,4},0,2) = {2;4} 25 返回引用中指定单元格。 二. 返回引用中指定单元格。 公式为: INDEX(reference,row_num,column_num,area_num) 式中 reference—对一个或多个单元格区域的引用; 如果为引用输入一个不连续的选定区域，必须用括号括起来。如果引用中的每个区域只包含一行或一 列，则相应的参数 row_num 或 column_num 分别为可选项。例如，对于单行的引用，可以使用函数 INDEX(reference,column_num)。 row_num—引用中某行的行序号，函数从该行返回一个引用; column_num—引用中某列的列序号，函数从该列返回一个引用; area_num—选择引用中的一个区域，并返回该区域中 row_num 和 column_num 的交叉区域。选中或输入的 第一个区域序号为 1，第二个为 2，以此类推。如果省略 area_num，函数 INDEX 使用区域 1。 说明:row_num、column_num 和 area_num 必须指向 reference 中的单元格,否则，函数 INDEX 返回错 误值#REF!。如果省略 row_num 和 column_num，函数 INDEX 返回由 area_num 所指定的区域。 函数 INDEX 的结果为一个引用，且在其他公式中也被解释为引用。根据公式的需要，函数 INDEX 的 返 回 值 可 以 作 为 引 用 或 是 数 值 。 例 如 ， 公 式 CELL("width",INDEX(A1:B2,1,2)) 等 价 于 公 式 CELL("width",B1) 。 CELL 函 数 将 函 数 INDEX 的 返 回 值 作 为 单 元 格 引 用 。 而 在 另 一 方 面 ， 公 式 2*INDEX(A1:B2,1,2)将函数 INDEX 的返回值解释为 B1 单元格中的数字。 1.2.10 ADDRESS 函数 ADDRESS 函数的功能是按照给定的行号和列标，建立文本类型的单元格地址。公式为 = ADDRESS(row_num,column_num,abs_num,a1,sheet_text) 式中 row_num—在单元格引用中使 用的行号; column_num—在单元格引用中使用的列标; abs_num—指明返回的引用类型，其中:当为 1 或省略时为绝对引用，当为 2 时为绝对行号，相对列标， 当为 3 时为相对行号，绝对列标，当为 4 时为相对引用; a1—用以指明 A1 或 R1C1 引用样式的逻辑值。如果 A1 为 TRUE 或省略，函数 ADDRESS 返回 A1 样式的 引用，如果 A1 为 FALSE，函数 ADDRESS 返回 R1C1 样式的引用; sheet_text—一文本，指明作为外部引用的工作表的名称，如果省略 sheet_text，则不使用任何工作表名。 例如，ADDRESS(2,3)等于“$C$2”;ADDRESS(2,3,2)等于“C$2”。 1.2.11 INDIRECT 函数 INDIRECT 函数的功能是返回由文字串指定的引用。此函数立即对引用进行计算，并显示其内容。当 需要更改公式中单元格的引用，而不更改公式本身时，可使用此函数。 公式为: = INDIRECT(ref_text,a1) 式中 ref_text—对单元格的引用，此单元格可以包含 A1 样式的引用、R1C1 样式的引用、定义为引用 的名称或对文字串单元格的引用，如果 ref_text 不是合法的单元格的引用，函数 INDIRECT 返回错误值 #REF!; a1——逻辑值，指明包含在单元格 ref_text 中的引用的类型，如果 a1 为 TRUE 或省略，ref_text 被解 释为 A1 样式的引用，如果 a1 为 FALSE，ref_text 被解释为 R1C1 样式的引用。 需要注意的是，如果 ref_text 是对另一个工作簿的引用(外部引用) ，则那个工作簿必须被打开。如果 源工作簿没有打开，函数 INDIRECT 返回错误值 #REF!。 例如:如果单元格 A1 包含文本"B2"，且单元格 B2 包含数值 1.333，则:INDIRECT($A$1)=1.333。 上述介绍的几个查找函数 LOOKUP、VLOOKUP、HLOOKUP、MATCH、INDEX、ADDRESS、INDIRECT 等在财务分析与决策、预测及建立动态图表等中是非常有用的。 1.2.12 矩阵函数 矩阵函数——TRANSPOSE 函数、MINVERSE 函数和 MMULT 函数 函数、 一(TRANSPOSE 函数 TRANSPOSE 函数的功能是求矩阵的转置矩阵。 26 公式为 = TRANSPOSE(array) 式中，Array—需要进行转置的数组或工作表中的单元格区域。 函数 TRANSPOSE 必须在某个区域中以数组公式的形式输入，该区域的行数和列数分别与 array 的列数和 行数相同。 【例 1-7】假设矩阵 A 中的值如图 1-18 中单元格区域 A2:C5，求其转置矩阵的步骤如下: 图 1-18 求转置矩阵 (1)选取存放转置矩阵结果的单元格区域，如 E2:H4。 (2)单击工具栏上的【粘贴函数】按钮，在【粘贴函数】对话框中选取函数 TRANSPOSE，在该函数 对话框中输入(可用鼠标拾取)单元格 A2:C5，按“Crtl+Shift+Enter”组合键，即得转置矩阵如图 2-18 所示。 利用 TRANSPOSE 函数可以把工作表中的某些行(或列)排列的数据转换成列(或行)排列的数据。例如， 由于工作需要， 要把工作表中的某些行数据改为列数据， 若一个一个地改动数据， 将是很麻烦也很费时的， 而利用 TRANSPOSE 函数则可以很轻松地进行这项工作。但需要注意的是，利用 TRANSPOSE 函数对行 (列)数据进行转换，则无法单独修改其中转换单元格区域中的某单元格的数据。 二(MINVERSE 函数 MINVERSE 函数的功能是返回矩阵的逆矩阵。公式为 = MINVERSE(array) 式中，array—具有相等行列数的数值数组或单元格区域。 MINVERSE 函数的使用方法与 TRANSPOSE 函数是一样的。在求解线性方程组时，常常用到 MINVERSE 函数。 三(MMULT 函数 MMULT 函数的功能是返回两数组的矩阵乘积。 结果矩阵的行数与 array1 的行数相同， 列数与 array2 的列数相同。 公式为 = MMULT(array1,array2) 式中 array1, array2—要进行矩阵乘法运算的两个数组。 array1 的列数必须与 array2 的行数相同，而且两个数组中都只能包含数值。array1 和 array2 可以是单 元格区域、数组常数或引用。如果单元格是空白单元格或含有文字串，或是 array1 的行数与 array2 的列 数不相等时，则函数 MMULT 返回错误值#VALUE!。 同样地，由于返回值为数组公式，故必须以数组公式的形式输入。 以例 1-7 的原矩阵和其转置矩阵为例，它们的乘积矩阵求解方法如下: (1)选取存放乘积矩阵结果的单元格区域，如 J2:L5。 (2)单击工具栏上的【粘贴函数】按钮，在【粘贴函数】对话框中选取函数 MMULT，在该函数对话 框中的 array1 栏中输入(可用鼠标拾取)单元格区域 A2:C5，在 array2 栏中输入单元格区域 E2:H4，然后 按“Crtl+Shift+Enter”组合键，即得矩阵的乘积如图 2-18 所示。 1.2.13 ROUND 函数 ROUND 函数的功能是返回某个数字按指定位数舍入后的数字。 公式为 = ROUND(number,num_digits) 式中 number—需要进行舍入的数字; num_digits—指定的位数，按此位数进行舍入。 如果 num_digits 大于 0，则舍入到指定的小数位;如果 num_digits 等于 0，则舍入到最接近的整数; 如果 num_digits 小于 0，则在小数点左侧进行舍入。 27 利用 ROUND 函数可以防止利用格式工具栏上的【增加小数位数】或【减少小数位数】所带来的看 起来“假数据”问题的出现，使得工作表上显示的数据真实可靠。实际上，如果需要调整数据的小数位数， 最好使用 ROUND 函数，而不要使用格式工具栏上的【增加小数位数】或【减少小数位数】按钮。 例如，若单元格 A1 中的数据为 14.3772，若使用格式工具栏上的【减少小数位数】按钮将小数位数设 为两位， 则单元格 A1 中的数据显示为 14. 38， 看起来似乎单元格 A1 的数据为 14.38， 但实际上仍为 14.3772。 若在单元格 B1 中输入公式“=3*A1”，则单元格 B1 中的数据显示为 43.13，也许“不明真相”的人认为单元 格 B1 的数据算错了 (14.38 乘以 3 应该等于 43.14) 但实际上单元格的数据为 43.1316， ， 这种看起来的“假” 数 据 可 能 会 对 实 际 工 作 带 来 不 便 。 因 此 ， 正 确 的 方 法 应 是 : 单 元 格 B1 中 应 输 入 公 式“ =ROUND(3*ROUND(A1,2),2)”，结果为 43.14，即先将单元格 A1 的数据用函数 ROUND 四舍五入，然后 再对计算后的数据四舍五入。 第二节 EXCEL 数据分析处理 Excel 提供了强大的数据分析处理功能，利用它们可以实现对数据的排序、分类汇总、筛选及数据透 视等操作。 在进行数据分析处理之前，首先必须注意以下几个问题: (1)避免在数据清单中存在有空行和空列。 (2)避免在单元格的开头和末尾键入空格。 (3)避免在一张工作表中建立多个数据清单，每张工作表应仅使用一个数据清单。 (4)工作表的数据清单应与其他数据之间至少留出一个空列和一个空行，以便于检测和选定数据清单。 (5)关键数据应置于数据清单的顶部或底部。 2.1 数据排序 2.1.1 数据排序的规则 Excel 允许对字符、数字等数据按大小顺序进行升序或降序排列，要进行排序的数据称之为关键字。 不同类型的关键字的排序规则如下: 数值:按数值的大小。 字母:按字母先后顺序。 日期:按日期的先后。 汉字:按汉语拼音的顺序或按笔画顺序。 逻辑值:升序时 FALSE 排在 TRUE 前面，降序时相反。 空格:总是排在最后。 2.1.2 数据排序步骤 (1)单击数据区中要进行排序的任意单元格。 (2)单击【数据】菜单，选择【排序】项，系统将弹出【排序】对话框，如图 1-35 所示。 图 1-35 【排序】对话框 (3)在【排序】对话框中用下拉列表框选择要排序的关键字，关键字有“主要关键字”、“次要关键字” 和“第三关键字”，根据需要分别选择不同的关键字; (4)单击【确定】按钮，数据就按要求进行了排序。 28 当只有一个关键字时，可以单击工具栏上的升序按钮 或降序按钮 ，进行自动排序。 2.1.3 自定义排序 在有些情况下，对数据的排序顺序可能非常特殊，既不是按数值大小次序、也不是按汉字的拼音顺序 或笔画顺序，而是按照指定的特殊次序，如对总公司的各个分公司按照要求的顺序进行排序，按产品的种 类或规格排序等等，这时就需要自定义排序。 利用自定义排序方法进行排序，首先应建立自定义序列，其方法可参阅第 1 章的有关内容。建立好自 定义序列后，即可对数据进行排序，方法是:单击数据区中要进行排序的任意单元格，单击【数据】菜单， 选择【排序】项，在弹出的【排序】对话框中单击【选项】按钮，系统弹出【排序选项】对话框，如图 1-36 所示，在【自定义排序次序】的下拉列表中，选择前面建立的自定义序列，然后单击【确定】按钮，即可 对数据进行自定义排序。 图 1-36 【排序选项】对话框 2.2 数据的查找与筛选 企业的管理人员经常需要在数据库或数据清单众多的数据中找出需要的数据，Excel 提供了功能强大 的数据查找与筛选工具。数据查找是指从原始数据中提取满足条件的数据记录，源数据不会改变，也不会 被隐藏;数据筛选是指把数据库或数据清单中所有不满足条件的数据记录隐藏起来，只显示满足条件的数 据记录。常用的数据查找与筛选方法有:记录单查找、自动筛选和高级筛选。 下面结合实例说明各种查找方法的具体应用。 【例 2—11】图 2-37 为某公司的部分商品销售记录清单。 图 2-37 某公司的商品销售明细清单 根据图 2-37 中的有关资料， 可以分别采用记录单查找、 自动筛选或高级筛选的方式查找或选择所需要 的信息，如下所述: 2.2.1 记录单查找 记录单是查找和编辑数据的最简单的方法，利用记录单，不仅可以查找数据记录，还可以修改和删除 记录、添加新的数据记录等。 一(查找数据记录 29 利用记录单查找数据记录的步骤如下: (1)用鼠标单击数据清单或数据库中的任一非空单元格。 (2)单击【数据】菜单，选择【记录单】项，则系统弹出如图 1-38 所示的记录单。 图 1-38 记录单 (3)单击记录单中的【条件】按钮，则弹出记录单条件对话框，如图 1-39 所示。 图 1-39 记录单条件对话框 (4)输入条件，比如要查找“张三”的销售记录，则在【销售人员】栏中输入“张三”，然后单击【上一 条】按钮或【下一条】按钮，系统就逐次显示 满足条件的记录行。 还可以使用多个条件联合查找记录，此处不再叙述。 二(修改或删除记录 在图 2-38 所示的记录单中，即可对某一记录的各字段进行修改。若要删除显示的记录，只需单击记录单上 的【删除】按钮即可。 三(添加新的记录 在图 2-38 所示的记录单中，单击记录单上的【新建】按钮，则出现各字段均为空白的新建记录单，在记录 单中输入各字段的值，输入完毕后，单击【新建】按钮，即完成添加新记录。 2.2.2 自动筛选与自定义筛选 一(自动筛选 记录单检索数据每次只能显示一个数据行，当查询的数据较多，或要把查询的结果汇总成表时，就需要使 用筛选工具了。自动筛选提供了快速检索数据清单或数据库的方法，通过简单的操作，就能筛选出需要的 数据。利用自动筛选查找数据的步骤如下: (1)用鼠标单击数据清单或数据库中的任一非空单元格。 (2)单击【数据】菜单，选择【筛选】项，在【筛选】子菜单中选择【自动筛选】 ，则系统自动在数 据清单的每列数据的标题旁边添加一个下拉列标标志，如图 1-40 所示。 图 1-40 自动筛选的下拉列表标志 (3)单击需要筛选的下拉列表，系统显示出可用的筛选条件，从中选择需要的条件，即可显示出满足 30 条件的所有数据。例如，要查找所有彩电的销售记录，单击“商品”右边的下拉列表，从中选择“彩电”项， 则所有的彩电销售记录就显示出来，而其他的数据则被隐藏，如图 1-41 所示。 图 1-41 彩电销售清单的筛选结果 如果有关彩电的销售记录很多， 超过了 10 个， 当需要只显示 10 个记录时， 可单击“单价”、 “数量”、 “金 额”等右边的下拉列表中的“前 10 个”项，系统弹出【自动筛选前 10 个】对话框，如图 2-42 所示。这里， 在【显示】下拉列表中“最大”表示最大(最好)的前 10 个记录，“最小”表示最小(最差)的前 10 个记录。 中间的编辑框中的数值表示显示的记录行数，系统默认值为 10，但可以修改，根据需要输入数值即可。 图 1-42 【自动筛选前 10 个】对话框 若要恢复所有的记录，则单击“商品”右边的下拉列表中的“全部”项。若要取消【自动筛选】状态，则 单击【数据】菜单，选择【筛选】项，在【筛选】子菜单中再次选择【自动筛选】 。 二(自定义筛选方式 当在图 1-40 所示的下拉列表中选择“自定义”项时则会弹出【自定义自动筛选方式】对话框，如图 1-43 所示， 用户可根据具体条件对各栏进行设置。 如要查找销售金额大于或等于“150000”且小于或等于“200000” 的所有记录，则单击左上角的下拉箭头，选择“大于或等于”，右上角的条件值输入“150000”，单击左下角 的下拉箭头，选择“小于或等于”，右下角的条件值输入“200000”，单击【确定】按钮，并选择“与”条件， 则满足这些条件的所有记录就显示出来了，如图 1-44 所示。 图 1-43 【自定义自动筛选方式】对话框 图 1-44 【自定义自动筛选方式】筛选的结果 2.2.3 高级筛选 高级筛选可以使用较多的条件来对数据清单进行筛选，这些条件既可以是与条件，也可以是或条件，或与 条件，与或条件的组合使用，还可以使用计算条件。 一(一般情况下的高级筛选 利用高级筛选对数据清单进行筛选的步骤如下: (1)首先应建立一个条件区域。在条件区域中，同一行中的条件是与条件，也就是这些条件必须同时 满足;不同行中的条件是或条件，也就是这些条件只要满足其一即可。如需要查找张三销售彩电的所有记 录，则建立条件区域如图 1-45 所示。 31 图 1-45 建立条件区域 (2)单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【筛选】子菜单中的 【高级筛选】项，则系统弹出如图 1-46 所示的【高级筛选】对话框。 图 1-46 【高级筛选】对话框 (3)一般情况下，系统将自动给出了数据区域，用户只需在【条件区域】栏中输入条件区域(本例中 为 B19:C20，也可以用鼠标拾取单元格区域，此时在条件区域中将显示“销售明细清单!$B$19:$C$20”。 (4)高级筛选结果可以显示在数据清单的原有区域中，也可以显示在工作表的其他空白单元格区域， 系统默认的方式是在数据清单的原有区域中显示结果。若需要在工作表的其他空白单元格区域显示结果， 则在【方式】项中选中“将筛选结果复制到其他位置”，并在【复制到】栏中输入需要显示筛选结果的单元 格(开头的一个单元格即可) 。图 1-47 为在原有区域显示的高级筛选结果。 图 1-47 在原有区域显示的高级筛选结果 当需要显示原始的全部数据时，可以单击【数据】菜单，选择【筛选】子菜单中的项目，在【筛选】 子菜单中选择【全部显示】即可。 同样的方法可以进行建立或条件、与条件与或条件的组合使用情况下的高级筛选。 二(计算条件情况下的高级筛选 在有些情况下，筛选的条件不是一个常数，而是一个随数据清单中数据变化的计算结果，此时无法直 接利用高级筛选进行数据筛选。不过，我们可以通过计算条件的方法解决。以例 2-20 为例(见图 1-37) ， 这里要找出销售额大于平均销售额的所有记录。步骤如下: (1)在数据清单以外的任一空单元格内输入平均值计算公式，比如在单元格 H20 中输入公式 “=AVERAGE(E3:E16)”，这里要特别注意的是存放平均值计算公式的单元格的列标不能与数据清单的任一 列标相同，如图 1-48 所示。 图 1-48 计算条件情况下的高级筛选 (2)设置条件区域，条件区域的列表可以是除数据清单中数据标题以外的任何文本，而筛选条件可在 单元格 B20 中输入“=E3>$H$20”，这里要特别注意:必须以绝对引用的方式引用销售额平均值，以相对引 用的方式引用数据清单中的数据。 (3)按照前面介绍的步骤进行高级筛选，其中高级筛选的数据区域为$A$2:$G$16;高级筛选的条件区 32 域为$B$19:$C$20，则筛选结果如图 2-48 所示。 2.3 数据的分类与汇总 在对数据进行分析时，常常需要将相同类型的数据统计出来，这就是数据的分类与汇总。在对数据进 行汇总之前，应特别注意的是:首先必须对要汇总的关键字进行排序。 2.3.1 进行分类汇总 进行分类汇总 例如，在例 2-11 中，要按地区进行自动分类汇总，其步骤如下: (1)首先对“地区”进行排序，排序方法见前面所述。 (2)单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【分类汇总】项，系 统弹出如图 1-49 所示的【分类汇总】对话框。 图 1-49 【分类汇总】对话框 (3)在【分类汇总】对话框中， 【分类字段】选项下选择“地区”， 【汇总方式】选项下选择“求和”， 【选 定汇总项】选项下选定“数量”和“金额”，单击【确定】按钮，则分类汇总的结果如图 2-50 所示。 图 1-50 按地区分类汇总结果 在图 1-50 中，左上角有 3 个按钮，按钮 1 表示 1 级汇总，显示全部的销售数量和销售金额汇总;按钮 2 表示 2 级汇总，显示各地区的全部销售数量和销售金额汇总;按钮 3 表示 3 级汇总，显示各地区的销售 数量和销售金额的汇总明细及汇总额(即图 1-50 所示的汇总结果) 。 图 1-50 中，左边的滑动按钮 为隐藏明细按钮，单击此按钮，则将隐藏本级的明细数据，同时 变为 显示明细按钮 ，再单击 按钮，则将显示本级的全部明细数据，同时 变为 。 在上述自动分类汇总的结果上，还可以再进行分类汇总，例如再进行另一种分类汇总，两次分类汇总 的关键字可以相同，也可以不同，其分类汇总方法与前面的是一样的，此处不再介绍。 2.3.2 分类汇总的撤消 如果不再需要分类汇总结果，可在图 2-49 所示的【分类汇总】对话框中单击【全部删除】 ，即可撤消分 类汇总。 2.4 数据透视表 33 数据透视表是用于快速汇总大量数据的交互式表格，用户可以旋转其行或列以查看对源数据的不同汇 总，也可以通过显示不同的页来筛选数据，还可以显示所关心区域的数据明细。通过对源数据表的行、列 进行重新排列，使得数据表达的信息更清楚明了。 2.4.1 建立数据透视表 以例 2-11 的数据为例，建立数据透视表的步骤如下: (1)首先，要保证数据源是一个数据清单或数据库，即数据表的每列必须有列标。 (2)单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【数据透视表和图表 报告】项，则系统弹出【数据透视表和数据透视图向导—3 步骤之 1】对话框，如图 1-51 所示，根据待分 析数据来源及需要创建何种报表类型，进行相应的选择，然后单击【下一步】按钮，系统弹出【数据透视 表和数据透视图向导—3 步骤之 2】对话框，如图 1-52 所示; 图 1-51 【数据透视表和数据透视图向导—3 步骤之 1】对话框 图 1-52 【数据透视表和数据透视图向导—3 步骤之 2】对话框 (3)默认情况下，系统自动将选取整个数据清单作为数据源，如果数据源区域需要修改，则可直接输 入“选定区域”，或单击【浏览】按钮，从其他的文件中提取数据源。确定数据源后，单击【下一步】按钮， 系统弹出【数据透视表和数据透视图向导—3 步骤之 3】对话框，如图 1-53 所示。 图 1-53 【数据透视表和数据透视图向导—3 步骤之 3】对话框 (4)在【数据透视表和数据透视图向导—3 步骤之 3】对话框中，单击【版式】按钮，出现【数据透 视表和数据透视图向导—版式】对话框，如图 1-54 所示。 (5) 【数据透视表和数据透视图向导—版式】对话框中，再根据需要，将右边的字段按钮拖到左边的 图上，这里，将“销售人员”拖到“行(R)”图上，将“商品”拖到“列(C)”图上，将“数量(台)”和“金额(元)” 拖到“数据(D)”图上，如图 1-55 所示。 34 图 1-54 【数据透视表和数据透视图向导—版式】对话框 图 1-55 设置数据透视表的版式 (6)设置好版式后，单击【确定】按钮，则系统就返 回到图 2—44 所示的【数据透视表和数据透视图 向导—3 步骤之 3】对话框，然后单击【完成】按钮，数据透视表就完成了，如图 1-56 所示。 这样，通过图 2-56 的数据透视表，即可看出每个销售人员所销售商品的种类、数量、销售额及其合计数， 从而以此为基础可很方便地对每个销售人员的销售业绩进行评价。 图 1-56 各个销售人员销售商品的数据透视表 2.4.2 数据的透视分析 在图 1-56 所建立的数据透视表上， 可以很方便地进行多角度的统计与分析。 比如要了解李四所销售商品的 情况， 可在“销售人员”下拉列标中只选中“李四”， 然后单击“确定”按钮， 则李四的销售情况如图 1-57 所示。 图 1-57 李四的销售情况汇总 还可以建立透视图， 方法是: 单击数据透视表中的任一单元格， 单击鼠标右键， 在快捷菜单中选择 【数 据透视图】项，则系统自动显示出数据透视图，从而得到每个销售人员的更为直观的销售情况。 35 第三节 EXCEL 图表处理 Excel 具有完整的图表功能，它不仅可以生成诸如条形图、折线图、饼图等标准图表，还可以生成较 为复杂的三维立体图表。对各种财务数据进行图表处理，可以更直观地进行财务分析，找出工作表格不容 易发现的问题，使得财务管理工作更为有效。 3.1 图表类型 Excel 提供了约 14 种标准图表类型，如面积图、柱形图、条形图、折线图、饼图、圆环图、气泡图、 雷达图、股价图、曲面图、散点图、锥形图、圆柱图、棱锥图等，每种图表类型又都有几种不同的子类型。 此外，Excel 还提供了约 20 种自定义图表类型，用户可根据不同的需要选用适当的图表类型。 关于各种图表类型的具体情况，可单击工具栏上的【图表向导】按钮 表】项，即可查看各种图表。 ，或单击【插入】菜单，选择【图 3.2 图表的建立 建立图表的过程非常简单，只要按照【图表向导】的有关说明，一步一步地进行操作，即可完成图表 的制作。下面结合实例进行说明。 【例 2-8】某企业 2002 年 12 个月的销售量与销售费用的有关数据如图 1-19 所示，绘制各月销售额与 销售费用之间关系的图表的步骤如下: 图 1-19 销售额与销售费用有关数据 (1)选取单元格区域 A2:M3，单击工具栏上的【图表向导】按钮，或单击【插入】菜单，选择【图 表】项，出现【图表向导,4 步骤之 1,图表类型】对话框，如图 1-20 所示，在【图表类型】中选【折线 图】 ，在【子图表类型】中选【数据点折线图】 ，单击【下一步】按钮，出现【图表向导,4 步骤之 2,图 表数据源】对话框，如图 1-21 所示。 图 1-20 【图表向导,4 步骤之 1,图表类型】对话框 36 图 1-21 【图表向导,4 步骤之 2,图表源数据】对话框 (2)在【图表向导,4 步骤之 2,图表源数据】对话框中，单击【系列】 ，在【分类(X)轴标志】栏 中填入“=Sheet1!$B$1:$M$1”，单击【下一步】按钮，出现【图表向导,4 步骤之 3,图表选项】对话框， 如图 1-22 所示。 图 1-22 【图表向导-4 步骤之 3-图表选项】对话框 (3)在【图表向导,4 步骤之 3,图表选项】对话框中，对【标题】的各项进行输入，即在【图表标 题】栏中填入“销售额 和销售费用的变化图”，在【数值 X 轴】栏中填入“月份”，在【数值 Y 轴】栏中填入 “金额(千元)”，单击【下一步】按钮，出现【图表向导,4 步骤之 4,图表位置】如图 2-23 所示。 图 1-23 【图表向导-4 步骤之 4-图表位置】对话框 (4)在【图表向导,4 步骤之 4,图表位置】对话框中，若要建立嵌入式图表，即图表嵌入在本工作 表中，则选择“作为其中的对象插入”;若要建立工作表图表，则选择“作为新工作表插入”。这里选择“作为 其中的对象插入”;然后单击【完成】按钮，即得到需要的图表，如图 1-24 所示。 图 1-24 销售额与销售费用变化图 37 3.3 图表的编辑、修改及格式化 图表的编辑、 通过图表向导建立的图表可能不尽人意，如标题太大或太小、坐标系列太多、图表尺寸太小、漏掉了 数据系列、需要添加数据标志等等，这时就需要对图表进行修改和格式化。 一(设置坐标、标题、图例等的格式 设置坐标、标题、 设置坐标、标题、图例等的格式的方法非常简单，可将鼠标移到坐标、标题、图例等上，单击右键， 在快捷菜单上选择相应的项目即可。例如要改变 X 坐标大小，单击右键，出现快捷菜单，选择【坐标轴格 式】项，就会弹出【坐标轴格式】对话框，如图 1-25 所示，选择需要修改的项目，进行设置即可。 图 1-25 【坐标轴格式】对话框 二(改变图表大小 单击图表区域，将它激活，图表边框出现 8 个操作柄，用鼠标指向某个操作柄，当鼠标指针呈现双箭 头时，按住左键不放，拖动操作柄到需要的位置上，然后放开鼠标左键，即可完成。 三(移动或复制图表 移动:单击图表区域，将它激活，图表边框出现 8 个操作柄，在图表区域按住鼠标左键不放，拖动鼠 标将图表移到需要的地方。 复制:单击图表区域，将它激活，图表边框出现 8 个操作柄，在图表区域按住鼠标左键不放，拖动鼠标将 图表移到需要的地方，按 Ctrl 键，然后放开鼠标。若需要将图表复制到其他工作表或其他文件中，可选中 图表，按“Ctrl+C”键，再在需要安置图表的工作表或其他文件的适当位置，按“Ctrl+V”键 四(添加数据标志 在很多情况下，在图表上添加数据标志，可以更直观地表示因素的变化情况。添加数据标志的步骤如 下:单击需要显示数据标志的数据点，然后按鼠标右键，出现快捷菜单，如图 2-26 所示，选择【数据系列 格式】对话框，单击【数据标志】 ，选择“显示值”。 图 2-26 快捷菜单 通过【数据系列格式】对话框，还可以进行其他方面的修改或格式化，有关内容可单击【数据系列格 式】对话框的有关项。 38 图 1-27 【数据系列格式】对话框 五(改变图表颜色、图案、边框 改变图表颜色、图案、 改变图表颜色、图案、边框，可通过【图表区格式】对话框来完成，方法是:单击图表区域，单击鼠 标右键，在快捷菜单中选【图表区格式】项，弹出【图表区格式】对话框，即可进行相应的修改。 3.4 地区销售分布图表的建立 企业的产品销往全国各地及世界各地，各地的销售量是不同的，我们可以利用 Excel 的地图分析工具 建立销售数据地图，从而可以将企业产品在各地的销售情况更加直观地表示出来。 【例 2-9】某企业在某些省份的销售数据如图 1-28 所示，则建立数据地图的步骤如下: 图 1-28 销售数据 (1)选中数据区域 A2:B15。 (2)单击【插入】菜单，执行【对象】命令，弹出【对象】对话框，如图 2-29 所示，选中“Microsoft 地图”，则 Excel 就会根据所选的工作表数据建立如图 2-30 所示的数据地图;在数据地图中，数据越多的 区域(省份) ，颜色就越深。 (3)在图 2-30 中所示的地图中没有标明省份名称，但可以通过下述方法加入省份名称:双击地图，出 现地图的菜单，如图 2-31 所示，单击地图菜单上的【工具】 ，选择【标志】项，出现【地图标志】对话框， 如图 2-32 所示; (4)在【地图标志】对话框中， 【需要设置标志的地图项】中选择“中国”， 【创建标志】中选择“地图项 名称”，然后单击【确定】按钮。 图 1-29 【对象】对话框 39 图 1-30 某企业的销售地区分布 图 1-31 地图菜单项 图 1-32 【地图标志】对话框 (5)在地图上移动鼠标，在鼠标移动过程中，Excel 会显示该区域所对应的省份名称，单击左键，该 省份名称就会标注在对应的省份区域上(图表太小，此处省略) 。 (6)如果需要，还可以对各省份添加数量标志，方法是:双击地图，出现【Microsoft 地图控件】对 话框，如图 1-33 所示，根据需要选择数据类型格式，用鼠标把需要的格式拖放在对话框右边区域中的格式 上，然后把要设置这种格式的数据列(对话框中的【第 B 列】 )拖放在图中的“列”字框上即可。 【Microsoft 地图控件】对话框提供了 6 种不同的数据类型格式。图 1-20 就是第 1 种数据类型格式。 图 1-33 【Microsoft 地图控件】对话框 需要注意的是，图 1-28 的工作表数据中的各省份名称必须与 Excel 所规定的相同，否则可能会出现意 想不到的错误。Excel 所规定的各省份名称如下:黑龙江省、吉林省、辽宁省、内蒙古自治区、新疆维吾 尔自治区、北京市、天津市、河北省、山西省、陕西省、青海省、宁夏回族自治区、西藏自治区、山东省、 河南省、江苏省、浙江省、上海市、安徽省、湖北省、湖南省、福建省、广东省、广西壮族自治区、江西 省、四川省、云南省、贵州省、海南省、香港、台湾省、澳门。 3.5 动态图表的建立 在企业的经营活动中，往往需要为每个部门建立大量相似的图表，如果在一张工作表上建立太多的图 表，既费时也使得图表显得凌乱不堪。我们可以建立动态图表来解决这个问题，当需要了解某个部门的销 售情况时，只需将鼠标移到工作表中该部门的单元格上，即可立即显示出该部门的销售图表。 【例 2-10】某企业的 8 个销售部门一年内各月的销售量数据如图 1-34 所示，建立各部门的动态图表 的步骤如下: 40 图 1-34 动态销售图表 (1) 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 动态图表数据区域，如图 2-34 所示。 (2)在单元格 A13 中输入公式“=INDIRECT(ADDRESS(CELL("row"),COLUMN (A3)))”，并把该公式 向右填充复制到 M13 中，这里 COLUMN 的意思是返回参数所在的列标，CELL("row")的意思是返回当前 光标所在的行号，ADDRESS(行号，列标)的意思是返回由行号和列标确定的单元格，INDIRECT 的意 思是返回参数所确定的单元格内容; (3)选中区域 A12:M13，插入“折线图”，并进行相应的格式设置，则动态图表就建立起来了。 若鼠标单击 A3 单元格，再按 F9 键(即对工作表数据重新计算) ，就会显示部门 A 的销售图;若鼠标单击 A5 单元格，再按 F9 键(即对工作表数据重新计算) ，就会显示部门 C 的销售图。这样，就可以很方便地 对各个销售部门的销售量进行直观的观察和分析。 第四节 EXCEL 数据分析工具的应用 Excel 提供了非常实用的数据分析工具，利用这些分析工具，可解决财务管理中的许多问题，例如财 务分析工具、统计分析工具、工程分析工具、规划求解工具、方案管理器等等。下面介绍财务管理与分析 中常用的一些数据分析工具。 4.1 模拟运算表 模拟运算表就是将工作表中的一个单元格区域的数据进行模拟计算，测试使用一个或两个变量对运算 结果的影响。在 Excel 中，可以构造两种模拟运算表:单变量模拟运算表和多变量模拟运算表。 4.1.1 单变量模拟运算表 单变量模拟运算表就是基于一个输入变量，用它来测试对公式计算结果的影响。 【例 2-13】企业向银行贷款 10000 元，期限 5 年，则可以使用【模拟运算表】工具来测试不同的利率 对月还款额的影响，步骤如下: (1)设计模拟运算表结构，如图 2-62 所示。 图 1-62 单变量模拟运算表 (2)在单元格 B4 中输入公式“=PMT(A4/12,5*12,B1)(”。3 )选取包括公式和需要进行模拟运算的单元格区域 A4:B13。 (4)单击【数据】菜单，选择【模拟运算表】项，弹出【模拟运算表】对话框，如图 1-63。 41 图 1-63 【模拟运算表】对话框 (5)由于本例中引用的是列数据，故在【输入引用列的单元格】中输入“$A$4”。单击【确定】按钮， 即得到单变量的模拟运算表，如图 2-62 所示。 4.1.2 双变量模拟运算表 双变量模拟运算表就是考虑两个变量的变化对公式计算结果的影响，在财务管理中应用最多的是长期 借款双变量分析模型，有关详细内容可参阅第 3 章的有关章节。 4.2 单变量求解 单变量求解就是求解只有一个变量的方程的根，方程可以是线性方程，也可以是非线性方程。单变量 求解工具可以解决许多财务管理中涉及到一个变量的求解问题。 【例 2-14】某企业拟向银行以 7%的年利率借入期限为 5 年的长期借款，企业每年的偿还能力为 100 万元，那么企业最多总共可贷款多少, 设计如图 2-64 所示的计算表格，在单元格 B2 中输入公式“=PMT(B1,B3,B4)”，单击【工具】菜单，选 择【单变量求解】项，则弹出【单变量求解】对话框，如图 1-65 所示，在【目标单元格】中输入“B2”，在 【目标值】中输入“100”，在【可变单元格】中输入“$B$4”，然后单击【确定】按钮，则系统立即计算出 结果，如图 1-64 所示，即企业最多总共可贷款 410.02 万元。 图 1-64 贷款总额计算 图 1-65 【单变量求解】对话框 4.3 规划求解 规划求解是 Excel 的一个非常有用的工具，不仅可以解决运筹学、线性规划等问题，还可以用来求解 线性方程组及非线性方程组。 【规划求解】 加载宏是 Excel 的一个可选安装模块， 在安装 Microsoft Excel 时， 如果采用 【典型安装】 ， 则【规划求解】工具没有被安装，只有在选择【完全/定制安装】时才可选择安装这个模块。在安装完成进 入 Excel 后，单击【工具】菜单，选择【加载宏】项，在【加载宏】对话框中选定【规划求解】复选框， 然后单击【确定】按钮，则系统就安装和加载【规划求解】工具，可以使用它了。 4.3.1 求解优化问题 财务管理中涉及到很多的优化问题，如最大利润、最小成本、最优投资组合、目标规划、线性回归及 非线性回归等等。下面仅举一个简单的例子来说明利用规划求解工具解决最大利润的问题，有关规划求解 工具的更多实际应用可参阅后面的有关章节。 【例 1-15】某企业在某月份生产甲、乙两种产品，其有关资料如图 2-66 所示，则企业应如何安排两 种产品的产销组合，使企业获得最大销售利润, 利用规划求解工具求解这个问题的步骤如下: 42 图 1-66 产品有关资料及优化结果 (1)首先建立优化模型， (设 x 和 y 分别表示甲产品和乙产品的生产量) : 目标函数:max{销售利润}= (140,60)×x + (180,100)×y 约束条件:6x + 9y ? 360 7x + 4y ? 240 18x + 15y ? 850 y ? 30 x ? 0, y ? 0，且为整数 (2)单元格 B11 和 C11 为可变单元格，分别存放甲、乙产品的生产量。 (3)单元格 B12 为目标单元格(销售利润) ，计算公式为“=SUMPRODUCT(B4:C4-B5:C5,B11:C11)”; (4)在单元格 B14 中输入产品消耗工时合计计算公式“=SUMPRODUCT(B6:C6,B11:C11)”。在单元格 B15 中输入产品消耗材料合计计算公式“=SUMPRODUCT(B7:C7,B11:C11)”，在单元格 B16 中输入产品消 耗能源合计计算公式“=SUMPRODUCT(B8:C8,B11:C11)”。 (5)单击【工具】菜单，选择【规划求解】项，则系统弹出【规划求解参数】对话框，如图 1-67。 图 1-67 【规划求解参数】对话框 (6)在【规划求解参数】对话框中， 【设置目标单元格】中输入“$B$12”; 【等于】选“最大值”; 【可变 单 元 格 】 中 输 入 “$B$11:$C$11” ; 在 【 约 束 】 中 添 加 以 下 的 约 束 条 件 : “$B$11:$C$11= 整 数 ” 、 “$B$11:$C$11>=0”、“$B$14<=$E$3”、“$B$15<=$E$4”、“$B$16<=$E$5”、“$B$11<=$C$9”; 这里，添加约束条件的方法是:单击【添加】按钮，系统会弹出【添加约束】对话框，如图 1-68 所示， 输入完毕一个约束条件后，单击【添加】按钮，则又弹出空白的【添加约束】对话框，再输入第二个约束 条件。当所有约束条件都输入完毕后，单击【确定】按钮，则系统返回到【规划求解参数】对话框。 图 1-68 【添加约束】对话框 如果发现输入的约束条件有错误，还可以对其进行修改，方法是:选中要修改的约束条件，单击【更 改】按钮，则系统弹出【改变约束】对话框，如图 1-69 所示，再进行修改即可。 43 图 1-69 【改变约束】对话框 输入完毕约束条件后，若还需要添加约束条件，单击【添加】按钮，在弹出的【添加约束】对话框中 输入约束条件即可。 (7)如果需要，还可以设置有关的项目，即单击【选项】按钮，弹出【规划求解选项】对话框，如图 2-70 所示，对其中的有关项目进行设置即可; 图 1-70 【规划求解选项】对话框 (8)在建立好所有的规划求解参数后，单击【求解】 ，则系统将显示如图 2-71 所示的【规划求解结果】 对话框，选择【保存规划求解结果】项，单击【确定】 ，则求解结果显示在工作表上，如图 2-66 所示。 图 1-71 【规划求解结果】对话框 (9)如果需要，还可以单击【规划求解结果】对话框中的【保存方案】 ，以便于对运算结果做进一步 的分析。 4.3.2 求解方程组 利用规划求解工具还可以求解线性或非线性方程组，下面举例说明: 【例 2-16】有如下的非线性方程组: 则利用规划求解工具求解方程组的解步骤如下: (1)设计工作表格，如图 2-72 所示; 图 1-72 利用规划求解工具求解方程组 (2)单元格 E2:E4 为变动单元格，存放方程组的解，其初值可设为零(空单元格) ; ( 3 ) 在 单 元 格 B2 中 输 入 求 和 公 式 “=3*E2^2+2*E3^2-2*E4-8” ; 在 单 元 格 B3 中 输 入 求 和 公 式 “=E2^2+(E2+1)*E3-3*E2+E4^2-5”;在单元格 B4 中输入求和公式“=E2*E4^2+3*E2+4*E3*E4-10”; (4)可以任意选取一个方程的求和作为目标函数，而其它两个方程的求和作为约束条件，这里选取方 程 1 的求和作为目标函数， 方程 2 和方程 3 的求和作为约束条件， 故在单元格 C2 中输入目标函数公式“=B2”; 44 (5)在【规划求解参数】对话框中， 【设置目标单元格】设置为单元格“$C$2”; 【等于】设置为“值为 0”; 【可变单元格】设置为“$E$2:$E$4”; 【约束】中添加“$B$3=0”、“$B$4=0”。如有必要，还可以对“选项”的 有关参数进行设置，如“迭代次数”、“精度”等，这里精度设置为 10-11。 (7)单击【求解】 ，即可得到方程组的解，如图 2-72 所示。 利用规划求解工具还可以求解一元方程的解，此时仅有一个可变单元格，方法同上。 4.4 方案分析 在企业的生产经营活动中，由于市场的不断变化，企业的生产销售受到各种因素的影响，企业需要估 计这些因素并分析其对企业生产销售的影响。Excel 提供了称为方案的工具来解决上述问题，利用其提供 的方案管理器，可以很方便地对多种方案(即多个假设条件)进行分析。 下面结合实例来说明如何使用方案管理器进行方案分析和管理。 【例 2-17】某企业生产产品 A、 产品 B、产品 C，在 2003 年的销售额分别为 200 万元、400 万元和 300 万元，销售成本分别为 120 万元、280 万元和 160 万元。根据市场情况推测，2:80/年 产品的销售情况有好、一般和差三种情况，每种情况下的销售额及销售成本的增长率如图 1-73 所示。 图 1-73 产品销售资料及预计增长率 4.4.1 建立方案 根据以上资料，建立分析方案: (1)单击工作表的任一单元格，激活工作表，并设计方案计算分析格式，如图 2-73 所示，并在单元格 G7 中输入公式“=SUMPRODUCT(B3:B5,1+G4:G6)SUMPRODUCT(C3:C5,1+H4:H6)”。 (2)将可变单元格分别进行命名，即单元格 G4 的名字为“产品 A 销售额增长率”，单元格 H4 的名字 为“产品 A 销售成本增长率”，单元格 G5 的名字为“产品 B 销售额增长率”，单元格 H5 的名字为“产品 B 销 售成本增长率”，单元格 G6 的名字为“产品 C 销售额增长率”，单元格 H6 的名字为“产品 C 销售成本增长 率”，单元格 G7 的名字为“总销售利润”。 (3)单击【工具】菜单，选择【方案】项，系统弹出【方案管理器】对话框，如图 2-74 所示，单击【添 加】按钮，系统弹出【添加方案】对话框，如图 1-75 所示。 图 1-74 【方案管理器】对话框 图 1-75 【添加方案】对话框 45 (4)在【添加方案】对话框中， 【方案名】编辑框中输入“方案 1 销售好”， 【可变单元格】编辑框中输 入“$G$4:$H$6”，单击【确定】按钮，系统弹出【方案变量值】对话框，如图 1-76 所示; 图 1-76 【方案变量值】对话框 (5)在【方案变量值】对话框中输入每个可变单元格的值(这里要按行输入) ，完毕后单击【添加】 按钮，系统会弹出如图 1-75 所示的【添加方案】对话框，对第 2 个方案进行输入;待所有方案输入完毕后， 单击【方案变量值】对话框中的【确定】按钮，系统返回到【方案管理器】对话框，如图 1-77 所示。此时， 可 单击【关闭】按钮，回到工作表。 图 1-77 方案建立完毕后的【方案管理器】对话框 4.4.2 显示方案 方案制定好后，任何时候都可以执行方案，查看不同的执行结果，方法如下: (1)打开原工作表，并激活工作表。 (2)单击【工具】菜单，选择【方案】项，系统弹出【方案管理器】对话框，如图 2-77 所示，选择要 想查看的方案，单击【显示】按钮，则系统就自动显示出该方案的执行结果，如图 2-73 所示。 4.4.3 修改、删除或增加方案 修改、 对做好的方案进行修改， 只需在图 2-77 所示的 【方案管理器】 对话框中选中需要修改的方案， 【编 单击 辑】按钮，系统弹出如图 1-76 所示的对话框，进行相应的修改即可。 若要删除某一方案，则在图 1-77 所示的【方案管理器】对话框中选中需要删除的方案，单击【删除】 按钮。 若要增加方案，则在图 1-77 所示的【方案管理器】对话框中单击【添加】按钮，然后在图 1-75 所示 的对话框填写相关的项目。 4.4.4 建立方案报告 当需要将所有的方案执行结果都显示出来时，可建立方案报告，方法如下:( 1)在图 1-77 所示的【方案管理器】对话框中单击【总结】按钮，弹出【方案总结】对话框，如图 2-78 所示，在【结果类型】中选择“方案总结”项，在【结果单元格】中输入“G7”，然后单击【确定】按钮， 则系统在当前工作簿中自动建立一个名为“方案总结”的工作表，如图 1-79 所示。 图 1-78 【方案总结】对话框 46 图 1-79 方案报告 4.5 数据分析工具库 Excel 提供了一组数据分析工具，称为“分析工具库”它包括方差分析、相关系数分析、协方差分析、 描述统计分析、指数平滑分析、F-检验、傅里叶分析、直方图分析、移动平均分析、随机数发生器、排位 与百分比排位、回归分析、抽样分析、t-检验、z-检验等，利用这些数据分析工具，可为实际的财务管理工 作提供很大帮助，解决许多实际问题，例如财务预测问题。有关数据分析工具库在财务管理中的应用，将 在以后的有关章节中陆续予以介绍。 47