Excel基本应用学习资料.pdf

《Excel基本应用学习资料.pdf》由会员分享，可在线阅读，更多相关《Excel基本应用学习资料.pdf（25页珍藏版）》请在三一文库上搜索。

1、1 Excel 基本应用讲义 (一) 1Excel 入门知识 1.1 功能及作用 Excel 称电子表格，是使用广泛的个人和单位数据管理、分析、统计、汇总 的重要工具。 其功能： 制作数据表格； 制作各种类型的图表； 数据运算（公式、函数和数组） ； 数据处理，包括外部数据的处理，处理方法灵活多样，如排序、筛选、 分类汇总、透视表、单双变量模拟运算、方案等； 数据有效性检查。 1.2 主界面认识 1.2.1菜单使用及 Word 不同处。 1.2.2工具栏。 工具栏的显示和隐藏：视图工具栏点击； 工具 栏上 除了Word具 有的命令 按钮外， Excel专用的工具栏有 自动求和、粘贴函数、升序和

2、降序排序四个按钮。 工具栏的移动和定位。用鼠标拖动移动工具栏，双击或拖动到菜单下可 以定位。 自定义工具栏： 视图工具栏自定义 /右击工具栏 快捷菜单 自定义 /工具菜单 自定义。对话框中三个标签，如图1 所示。 （图 1 自定义对话框） 2 工具栏标签的选择中打显示/取消隐藏工具栏； 点击“新建”按钮，可以按照自己的习惯和爱好，自定义一个或多 个新的工具栏。新建工具栏后，可以将命令标签中的需要的命令拖 动到新建的工具栏上。 在命令标签中，可以将菜单命令拖动到常用工具栏上； 1.2.3格式栏 格式栏除了与Word 相同的按钮外， Excel 格式按钮有专用的按钮，如： 跨列居中、货币样式、百分

3、比、千分位符号、 增加小数位、缩小小数位、减少缩进量、增加缩进量、边框、底纹颜色、字体颜 色等数字格式按钮。 使用方法：选择单元格或区域，点击需要设置格式的按钮。 1.2.4编辑栏 单元格栏 /函数栏，公式编辑栏。 1.2.5状态栏 非常有用的是自动计算功能 1.3使用帮助 方法如下 : F1 调出“搜索”对话框 /点击工具栏上的“ Microsoft Excel 帮助”按钮， ESC 键取消。 右击 OFFICE 助手，快捷菜单。 右击 OFFICE 助手选项取消“使用OFFICE 助手”选项后， F1 键， 出现常见的帮助窗口。 Shift+F1 键单击某一处 2. 文件管理 2.1 建立

4、文件 2.1.1 快速建立文件 新建按钮 /Ctrl+N/ ，建立的文件默认文件名“BOOK1 ” ，称为工作簿，每 个工作簿有 Seet1 、Seet2、Seet3 三个工作表。 2.1.2 利用已有的模板建立文件 文件新建，有 3 个标签： 常用：有一个“工作簿”通用模板，与快速建立文件的模板是同一个。 3 电子方案表格 ：常用的专业模板文件，如果没有安装，只是一个图标，需要 插入安装光盘，点击自动安装，即可使用。 其他文档 ：一般只有一个地址模板。 2.2 打开文件 打开按钮 /Ctrl+O/ 文件打开。 注意打开对话框中的左、上方的按钮和工具。 选择文件后，“打开”按钮激活，点击按钮右

5、面的三角下拉菜单： 以副本方式打开：对原来的文件将不做修改； 以只读方式打开：打开文件后不能对之进行修改。 2.3 文件保存 2.3.1 保存 保存按钮 /Ctrl+S/ 文件保存。 2.3.2 另存为 可同复制功能；文件更名；更改文件格式。 2.3.3 另存为 Web 页 用 IE 浏览器浏览。 2.3.4 保存工作区 如果打开了多个工作簿文件， 各个文件都在不同的窗口打开， 且每个文件的 设置不同，显示和计算方法不同，可以用保存工作区的方法。打开时，原来使用 的该工作区的文件按照原设置打开。工作区文件扩展名为.XLW。 2.4 搜索文件 如果找不到要打开的文件，可以在打开对话框中而不用Wi

6、ndows 中的搜索 命令来查找文件。方法： 打开文件名处输入文件名或部分文件名工具查找按要求操作。 2.5 发送电子表格文档 在 Excel 中可以将电子表格通过网上直接发送给有关人员。方法两种： （1） 作好文档后，点击工具栏上的“电子邮件” （第 4 个） 按钮出现 OFFICE 帮助对话框。 （2）文件发送。 发送邮件可以有两种方式：以正文形式/以附件方式。 4 2.6 窗口使用 2.6.1 新建窗口 其目的是对较大的表格进行多窗口操作，多个窗口显示的仍然是一个文档。 窗口新建。标题栏中显示文件名:XXX:1 、XXX:2。各窗口可以显示一个文 档的不同区域，对于编辑十分方便。 2.6

7、.2 隐藏/取消隐藏窗口 （1）用最小化按钮隐藏 /显示窗口； （2）窗口隐藏 /取消隐藏命令功能。 打开的一个或多个直到全部文档可以隐藏。取消隐藏时，在对话框中选择要 取消隐藏的文档。 2.6.3 窗口的拆分与重排 1窗口拆分 在处理大表格时， 数据的录入不方便， 可以将窗口拆分为两个或四个窗口， 可以使用任意一个窗口处理数据。 拆分 点击要拆分位置的列或行标签窗口拆分，出现竖排或横排的两个窗口。 点击要拆分位置的某个单元格窗口拆分，出现上下左右四个窗口。 使用 每个窗口都有滚动条，可以拖动； 要重新分布窗口位置， 将光标放在窗口的分隔线上，成夹子后， 左键按住拖 动。 2取消拆分 窗口撤销

8、拆分窗口。 3窗口重排 在打开多个文档时，要同时看到，可以：窗口重排窗口可以选择：平铺 /水平/垂直/层叠等方式。 4冻结窗口 与拆分窗口相似。选择行号/列号窗口冻结窗口出现黑线。被冻结的 上部/左部窗口是不能移动的，而其他窗口可以移动。给录入数据带来方便。 5 3工作表操作 3.1 工作表基本操作 3.1.1 工作表默认数量 工作簿是一个独立的文件， 是由工作表组成的。 Excel2000 默认一个工作簿 3 个工作表：在左下角有三个标签，分别为Seet1 、Seet2、Seet3 ，点击即可切 换到该工作表进行操作。如果需要增加/减少默认数量，按如下设置： 工具选项常规对 “新工作簿内的工

9、作表数” 右边的数量进行修改确 定。关闭 Excel，再启动时，按设置的数量建立工作表。 3.1.2 插入工作表 点击选择要插入新工作表的位置插入工作表，即在选择的工作表前插入 一个新的工作表，名称默认为“SeetX” 。 3.1.3 移动、复制及删除工作表 移动工作表： 用鼠标拖动要移动的工作表标签，光标处有一个纸形图， 拖动 时有一个黑三角随着拖动而移动，到新的位置放开。 复制工作表：按Ctrl+鼠标拖动要复制的工作表标签，光标处有一个纸形图 上面有一“ +”号，拖动时有一个黑三角随着拖动而移动，到新的位置放开，即 复制了一个工作表副本，名称为“XXX（2） ” 。 删除工作表：点击选择要

10、删除的工作表标签编辑删除工作表在对话框 中点“是”，即删除了工作表。注意：此删除是不可逆的。 3.1.4 工作表更名 更名是为了方便操作。双击工作表标签/右击工作表标签重命名工作表 标签出现反白，输入新的名称点击任意外/回车确认。 3.1.5 工作表隐藏 /取消隐藏 点击要隐藏的工作表标签格式工作表隐藏。 练习操作文件 ：隐藏工作表。 3.2 录入数据 工作表由单元格组成，录入数据是在工作表的单元格中进行的。 每个工作表共有 256 列，默认由 AZ 重复组合为列名称；有65536 行，由 数字组成。 用 Ctrl+四个箭头可以直接移动列行的结尾处。 单元格名称由列和行号组成，点击选择在编辑栏

11、左边第一个框中显示。 6 3.2.1 设置数据格式 录入的数据是有类型的， 所以在录入数据前， 一般要设置数据的格式。 Excel 表格中主要的数据格式有： 数值、货币、会计专用、 日期、时间、百分比、分数、 文本，另外可以根据特殊需要设置特殊和自定义类型。 设置格式的方法：选择区域/列点击格式栏上的格式按钮/格式栏格式单 元格数字标签分类：选择分类，再选择类型确定。 比如，在 Excel 中数值一般输入11 位，超过的将会用科学记数法显示或显 示为 0。身份证号是 1518 位的，就需要设置为文本格式。 3.2.2 录入和修改 单元格中可以录入数字、货币、文字、符号、日期、逻辑等类型的数据，

12、带 可以是声音、音乐或照片、图形等。 1点击单元格，输入数据。 按 TAB 键向右跳一单元格， 回车到下一单元格。 回车后光标方向控制，可以按如下设置：工具选项编辑标签在“按 Enter 键后移动方向：”右边选择“向下 /向右/向上/向左”确定。 2二次在该单元格录入为覆盖式修改，双击/F2 为插入式修改。 3录入数据时，如果没有设置单元格的数据格式，录入1/2 回车，默认为 日期 1 月 2 日。可以照此方法录入“ 0 1/2”为数据型，居右放置； “ 1/2 ”为 文字型，居左排列。 3.2.3 快速录入 1序列录入 1、2、3序号录入方法； 季度、月份、甲乙丙丁等录入； 相同内容的单元格

13、内容的录入； 自定义序列录入方法； 2填充序列录入 编辑填充序列 练习操作文件 ：序列输入。 3.3有效数据设置和检查 1设置数据录入有效性检查过滤。在输入数据时进行检查，防止无效或错 误数据的录入。方法： 选定区域数据有效性设置在“有效性条件”下，设置“允许”数据 的类型设置数据有效范围在“输入信息” 标签中，设置输入数据时自动提示 的标题和内容在“出错警告”标签中，输入录入数据出现错误时，提示的警告 图标和标题说明文字确定。 2可以利用“条件格式”方法对录入后的数据进行有效性进行检查。格式 条件格式在条件格式对话框中设置数据范围或条件格式设置字体、边 框、底纹确定。使符合条件的数据有了特殊

14、格式，以便于对数据的检查。 7 3.4 表格修改及数据格式化 3.4.1 修改表格 1插入 /删除行、列和单元格 插入行列 ：选择一行或多行 /一列或多列插入行 /列，在选择的行列前插 入一行或多行 /一列或多列。 插入单元格 ：选择单元格插入单元格选择意义如下： 选“整行” -插入的是行，如同插入行； 选“整列” -则插入的是列，如同插入列； 选择“活动单元格右移” -则插入的单元格在选择的单元格的左侧； 选择“活动单元格下移” -则插入的单元格在选择的单元格的上面。 删除行列 ：选择要删除的一行或多行/一列或多列编辑删除。 2设置行列的高度及宽度 手动拖动 ：光标对准行 /列线成为“ +”

15、开关，左键按住不放拖动； 自动调整 ：在录入数据后，光标对准行列线成为“+”形状，双击，自动按 数据内容调整； 菜单方式 ：选择行 /列格式行 /列行高 /列宽输入值确定。 最适合的行高 /最适合的列宽，是系统自动按照录入的数据进行 调整。 练习操作文件 ：表格设置。 3. 隐藏行列 选择要隐藏的行 /列格式行 /列隐藏。 取消隐藏：选择隐藏行 /列的前面行 /列格式行 /列取消隐藏。 练习操作文件 ：隐藏工作表。 4单元格对齐方式 选择一个或多个单元格点击格式栏上的“左对齐”、 “居中” 、“右对齐”、 “跨列居中”。 “跨列居中”是将多个单元格合并为一个单元格进行对齐处理。 选择一个或多个

16、单元格格式单元格/右击设置单元格格式对齐 标签设置“文本对齐”下的“水平对齐”、 “垂直对齐”的方式。 “水平对齐”下的“跨列居中” ，其功能是将行方向的多个单元格作为一个 单元格设置对齐方式， 但是单元格并不合并。 与 “合并单元格”和工具栏上的“跨 列居中”按钮不同。 “文本控制”下的选项含义如下： 自行换行 ：当内容超过单元格宽度时，自动换行。强制换行：Alt+回车。 缩小字体填充 ：当内容超过单元格宽度时，将字体调小以适应。 合并单元格 ：同工具栏上的“跨列居中”按钮。 3.4.2 表格格式化 1设置表格边框 选择表格区域 格式单元格 边框选择 “线条”下的样式、 颜色点击 “预置”下

17、的外边框、内部，选“无”则可以取消原来的边框。 8 2单独设置某单个边框线 选择一个要设置边框线的区域格式单元格边框选线条样式点击 “边框”下的边框位置。 3设置单元格的颜色 选择设置区域利用格式栏最右边的“填充颜色”、 “字体颜色”进行设 置。 选择设置区域格式单元格字体/图案：可以设置底纹颜色和图案。 4文字数字格式 数字格式化是重要的格式化。 选择区域选择格式栏上的“货币样式”、 “百分比样式”、 “千分符”、 “增加小数位”、 “缩小小数位”格式。 选择区域格式单元格数字设置“分类”下的格式。 在 Excel 中,可以同时格式化多个工作表。方法：Ctrl+点击多个工作表标签， 组成一个

18、工作组，再设置格式。 练习操作文件 ：数据格式设置。 4条件格式数据 前面已讲过。 练习操作文件 ：条件格式化。 5自动套用表格格式 Excel 自带了 19 种表格样式，可以直接使用。 选择表格区域格式自动套用格式选择其中的需要的一种，点击 “选项” 按钮，对话框扩展出“应用格式种类” ，其选是应用表格样式中的哪些格式选 择，确定。 3.5 保护数据 工具保护保护工作表选择： 内容、对象、方案输入保护密码确定。 3.6 视面格式化 所谓的视面格式化，是对表格设置设置，包括：打印、隐藏行、列及筛选设 置。并保存起来，需要不同的方式时，直接显示、打印。如一份工资表，人事部 门要向有关领导报分类汇

19、总的数据，向财务部门送全部数据， 就可以设置不同的 视面方式。 方法如下： 1设置打印、进行有关运算或隐藏行列等处理视图视面管理器添加 输入名称为“财务处”确定。 2光标在数据区域数据分类汇总。再添加视面管理器内容为“领导” 确定。 每次要打印时， 调入视面设置： 视图视面管理器选择已经设置好的“财 务处”或“领导”确定，便可以需要的内容打印。 9 4简单数据计算 4.1 简单公式使用 4.1.1 公式的格式及元素 公式实例： =(A1+B1+C1)/SUM(D1:G1,H1) 该公式是 (A1+B1+C1)/(D1+E1+F1+G1) 1公式格式 公式是以开头的“ =” 、引用单元格和计算符

20、号、函数组成的计算方式。 注意， =及其他计算符号不能用中文符号。 公式有三个元素： “=” ，放在开头。说明是用于计算的，没有=，而有公式其他内容的只 是录入到单元格的字符或数据，不能用于计算。 引用单元格，表示方法： A1,B1 为单个引用， D1:F1 为区域引用。均为 相对引用，绝对引用在后面讲。 计算符号。分为四类，见下表。 2运算符号 算术运算符 运算符含义 + 加 - 减 * 乘 / 除 % 百分比 乘幂如：32 比较运算符 运算符含义 = 等于 大于 = 大于等于 不等于 文本运算符号& 将两个单元格中的文本连接为一个文本串。 引用运算符 “,”为联合运算符，将“ ,”前后的多

21、个单元格联合起来进行运算； “： ”为区域运算符，将“： ”前的第一个单元格与“： ”后面的最后一个单 元格的区域进行运算。 10 4.1.2 公式输入 输入公式的方法有两种。 1直接输入公式 在结果存放单元格中点击，输入“=”号后再输入计算的方法回车。 在结果单元格中显示的是结果， 在编辑栏中显示的是公式。 如果要在结果单 元格中显示公式，用快捷键Ctrl+。 2使用编辑栏 点击结果存放的单元格点击 “编辑公式”按钮在编辑公式栏中输入公式， 或者使用左边第一个常用函数栏使用函数确定/回车。 3复制公式 如果数据是规则的二维表格，可以用以下方法复制公式。 选择第一个给出公式的结果单元格CTrl

22、+C 复制选择其他一个或多 个结果单元格 Ctrl+V 粘贴。 第一个结果单元格给出公式后光标对准右下角的填充柄拖动到所有 要计算的结果单元格。 练习操作文件 ：公式计算。 4.1.3 单元格引用 在输入公式时， 要引用单元格， 以指明要计算的数据的位置。分为相对引用 和绝对引用两种。 1 按引用的单元格是否发生变化分为相对引用和绝对引用 A相对引用 是引用相对于公式位置的单元格。即公式位置变化， 引用的单元格也发生变 化。 B绝对引用 是引用相对于公式位置不发生变化的单元格。绝对引用符号为 “$” ，其格式 有： $A$1- 行列号都不发生变化； A$1- 列发生变化，而行不发生变化； $A

23、1- 列不发生变化，而发生变化。 绝对引用在数据高级操作中使用较多。 2按引用本工作簿和其他工作簿分为内部引用和外部引用 A内部引用 可以是本工作表中的单元格的引用，也可以引用其他工作表中的单元格。其 格式： 工作表名！单元格名或单元格区域。 如：=D8+Sheet2!E25 =SUM(D8:G8,Sheet2!E25:H25) B外部引用 是引用其他工作簿中的数据位置。引用的格式： 工作簿名 工作表名 ！单元 格名或区域。 =SUM(D13:G13,KS4-13.XLSSheet1!$D$8:$G$8) 11 3输入公式和引用的方法 A手工输入B. 鼠标点击 4.2 简单函数使用 函数是将计

24、算公式写在程序中的计算方式，便于使用和操作， 特别是对复杂 的操作更是有用。 Excel 共有 246 个函数。 这时介绍几个常用的函数。 1SUM() 求和 2AVERAGE() 求均值函数 3MAX() 求最大值函数 4MIN() 求最小值函数 5COUNT() 计数函数 练习操作文件 ：公式计算、函数运算、二维求和、二维三维运算。 5.打印 5.1 页面设置 文件-页面设置，有 4 个标签，其设置主要内容如下。 5.1.1 页面标签 设置纵向 /横向打印、纸张大小、缩放比例、起始页码等。可以点击“选项” 按钮进行设置。 5.1.2 页边距 设置页面的上下左右边距、页眉页脚大小、水平垂直居

25、中等。 5.1.3 页眉页脚 可选择页眉（A）/页脚（F）下的设置内容， 也可以点击 “自定义页眉”、 “自 定义页脚”按钮进行设置。 自定义页眉页脚对话框中的按钮如下： 设置字体字号和字型。 插入页码。 插入页数。 插入日期。 插入时间。 12 插入工作簿名称。 插入工作表名称。 5.1.4 工作表 1打印区域。点击右边框中的按钮，设置打印的区域，其他的区域不打印。 2打印标题。适用于大表格打印，在通常情况下，第二页以后的不打印标 题，使用不方便，可以通过这里设置，使每页都打印标题。 13 Excel 基本应用讲义 (二) 1其他函数使用 1.1 SUMIF() 条件求和函数。 格式：=SU

26、MIF （Range,Criteria,Sum_range） 功能：根据指定的条件对选定的单元格数据求和。 参数： Range 检测判断的单元格区域（条件区域） ； Criteria条件，可以以数字表达式或文本形式定义； Sum_range 求和计算的实际单元格。 如“成绩”表中左则是全部学生的各门课程成绩，要检查出某一个学生（如 李林在 H3 单元格）的总成绩，在i3 单元格中输入如下函数： =SUMIF(B2:B64,H3,E2:E64) 如果要用此函数求出每个同学的总成绩，则要在检测区域B2:B64 ，计算区 域 E2:E64 使用绝对引用，如下，然后再复制公式。 =SUMIF($B$2

27、:$B$64,H3,$E$2:$E$64) 练习操作文件 ：成绩 .XLS。 1.2 RANK() 排名次函数 格式：=RANK(Number,ref,Order) 功能： 确定某一数据在一个数据区域中的排序，可以是升序，也可以是降序。 参数： Number 确定某数据，或选择某单元格； ref 确定检测排列的区域； Order 排序方式，“0”或空时，为升序，如果为=1 为降序。 注意事项 ：被检测区域必须为绝对引用。 公式： =RANK(N3,N$3:N$23,0) 练习操作文件 ：成绩 .XLSA。 1.3 条件统计函数Countif() 格式：=Countif(Range,Criter

28、ia) 功能：统计在选定的区域中，符合条件的单元格数。 参数 ：Range 为参与统计的非空单元格区域；Criteria 为定义的条件， 条件可以是文本、数字表达式。数字直接写上，文本需要加引号。 如下列：统计学生总成绩 =250 分的人数，公式如下： =COUNTIF(N3:N23,“=250“) 练习操作文件：成绩 .XLS 14 4PMT() 求利率（利息 +本金）函数 功能：基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。 格式：=PMT(rate,nper,pv,fv,type) Rate-为各期利率，是一固定值。 Nper-总投资（或贷款）期，即该项投资（或贷款）的付款

29、期总数。 Pv-为现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一 系列未来付款当前值的累积和，也称为本金。 Fv-为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv， 则假设其值为零（例如，一笔贷款的未来值即为零）。 Type- 数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果 省略 type，则假设其值为零。 说明： PMT 返回的支付款项包括本金和利息，但不包括税款、保留支付或 某些与贷款有关的费用。 练习操作文件 ：函数运算 .XLS 2数组的使用 数组是单个公式的组合使用。 即一次要计算多组数据， 即可用数组， 多组数 据必须是矩形的，有相同数目的单

30、元格数据。 方法：选择结果单元格区域输入“=”再按组选择输入计算符号按 CTRL+Shift+ 回车。 练习操作文件 ：数组使用 .XLS。 3数据处理 以下练习操作文件均为 ：数据处理 .XLS 3.1 排序 排序分为按钮方式和菜单方式。 按钮方式为为简单排序， 菜单方式为复杂排 序。 练习操作文件 ：数据处理 .XLS 1按钮方式 把光标放在要排序的某一列的任意一个单元格点击（别选择列或区域， 否则 只排序该列点击工具栏上的“升序”/“降序”按钮。 2菜单方式 光标放在数据区域内，数据排序对话框选择“主要关键字”“次 要关键字”“第三关键字”等。 注意三个关键字之间的关系： 第二关键字是在

31、第一关键字数据相同的范围内 进行排序，第三关键字是在第二关键字数据相同的范围内排序。 15 3.2 数据筛选 筛选分自动筛选和高级筛选。 1. 自动筛选 需要不同的数据显示或打印时，对一列数据进行不超过两个条件进行筛选， 使用自动筛选是最方便快捷的方法。表中建立的筛选按钮是只显示不打印的。 1）将光标放置于数据区任何处（放数据区外不能进行）选择数据筛选 自动筛选。 2）按照要求选择表头名上的按扭选择筛选条件。 2高级筛选 超过两个筛选条件的要使用高级筛选。 1）建立筛选条件，如下表。 学号姓名成绩 1 成绩 2 成绩 3 成绩 4 =85 =78 =70 =82 2）数据筛选高级筛选选择“方式

32、”下“将筛选结果复制到其他位置” 选择数据区域、 条件区域、 复制到结果的区域， 如果不需要重复记录则选择 确定。 3.3 合并计算 合并计算，是指在一个EXCEL 工作表中输入了许多不同类型的数据，重复 类型比较多，需要对同一类型进行汇总，可以进行合并计算。如：不同部门的人 员工资按部门进行计算等。 1）选择合并后数据存放的区域选择“数据”“合并计算”， 出现对话 框。 2）选择“函数”为求和（或者均值等）选择引用位置（包括汉字类别）。 3）如多个合并计算内容，则使用“添加” ，再依次选择 选择左列（如果 需要标题则选择首行）确定。 3.4 分类汇总 分类汇总，比合并计算更直观， 可以显示分

33、类汇总的结果， 也可以显示各分 类的明细情况。方法如下： 1）对分类汇总的数据首先要进行排序，排序要按照“分类字段”进行。 2）选择“数据”“分类汇总”出现对话框。 3）按要求选择“分类字段”为“课程名称”， “ 汇总方式”为“求和” 选定“汇总项”为“人数” 、 “课时”确定 4）将分类汇总的数据拆叠，即并带“-”号的点击一下，成为“+”号，或 者点击 1、2、3 中的 2。 16 3.5 数据透视表 数据透视表是将排序、 筛选和分类汇总等功能集于一体，可以方便地进行选 择地查询，属于立体型的数据表。 数据透视表非常关键的组成部分是： 页字段 ：应该是查询最主要的分类， 如果以单位为主要查询

34、对象， 就将单位 作为页字段，如果要以姓名为主要查询内容，则可以将姓名作为主要查询内容。 行字段 ：隶属于页字段的内容。 列字段 ：隶属于行字段的内容，显示最具体详细的数据。 见实际操作。 透视表样表 1-以课程名称为页 ,授课班级为行，改名为列格式。 透视表样表 2-以授课班为页 ,姓名为行 ,课程名称为列格式。 透视表样表 3-以姓名为页 ,授课班级为行 ,课程名称为列格式。 4图表制作 练习操作文件 ：图表制作 .XLS 图表是数据的图形化表现形式，使数据表现的更形象、 更直观、更便于分析 和更富有吸引力和说服力。 4.1 创建图表 1 选择要创建图表的数据区域点击工具栏上的图表按钮/插

35、入图片图 表； 2标准类型标签选择“图表类型”下的某一种图表选择“子图表类型” 下的一种形式点击“按下不放可查看示例”按钮下一步。 3数据区域标签：，可更改、选择数据区域，系列产生在： “行” 、 “列”是 指分类 X 轴以行或列生成，根据表现要求选择。 4系列标签： 系列和值对应的，可以更改； 系列和名称也是对称的，可以选改； 分类 X 轴标志，可以选择。 处理好后，下一步。 5标题标签：若需要，输入标题、分类X 轴、数值 Y 轴的内容，其他标签 中的内容，根据需要选择即可。 6选择图表保存位置，“作为新工作表插入” ，即自动生成一个工作表，在 其中生成图表；选择“作为其中的对象插入”，则可

36、以选择现有的任意一个工作 表，创建图表。 7点击完成按钮。 4.2 修改图表 1修改图表大小和基本格式。 可以选择图表中的任何文字，通过格式栏进行修改； 17 拖动图表的边框，使图表放大缩小。 2通过双击 /右击图表任意处，打开该图表元素的对话框，进行修改。 3通过图表工具栏选择修改的区域元素和内容。 4通过图表菜单下的命令修改。 4.3 数据地图 也是图表的一种方式， 适用于特殊的数据要求， 如各地的绿化、 沿海的有关 数据、公司在全国各地的经济形势等。 见操作。 5Excel 与外部数据 5.1 Word 与 Excel 数据交换 选择区域复制粘贴 选择区域复制编辑选择性粘贴。 5.2 E

37、XCEL 与 ACCESS 交换数据 将 ACCESS 表转换为 EXCEL 表：文件导出。 将 EXCEL 表转换为 ACCESS 表：文件导入。 5.3 Excel 表与 Visual Foxpro表交换数据 将 Excel 表转换为 VF 表：不要标题，设置好表头，输入数据文件另存 为：格式选 DBF 4 ； 将 VF 文件转换为 Excel 数据表：用 Excel 直接可以打开。 18 Excel 基本应用讲义 (三) 1Excel 的模板的调用、及数据的链接 模板，是一个含有特定内容和格式的工作薄，可以作为模型来建立与之类似 的其它工作薄。模板可以包括标准文本，如页标题、行标和列标、

38、自定义工具栏 等。 正常启动 Excel 时，默认工作薄模板是空白工作薄模板。Excel 有一整套各 种模板，可以根据需要调用。 练习操作文件 ：模板应用 .XLS 1.1 模板的调用 模板调用 .Xls 文件中没有资产负债表，建立非常复杂，可以直接使用Excel 带的模板。方法： 文件新建电子表格模板双击需要的模板名称（“运输企业财务报 表” ） 。 将新建的模板复制到模板应用.XLS 工作薄中去。 打开文档“模板应用 .XLS”切换到新建的 ” 运输企业财务报表 ” 文档点 击” 资产负债表 ” 编辑移动或复制工作表将选定工作表移动到工作薄:选择” 模板应用 .XLS” 下列选定工作表之前

39、 :选择” 损益表 ”确定。 复制后可以改名： 双击工作表标签输入新的工作表名称回车或点击其 他处。 1.2 复制公式到“模板应用”的“损益表”中 模板应用 .XLS 中的“损益表”中没有公式只有数据，可以使用复制公式的 方式解决。 点击用“运输企业财务报表”建立的文档点击“损益表”选择全部表 或者数据区域 Ctrl+C切换到“模板应用 .XLS”点击“损益表”选择相同 的区域编辑选择性粘贴粘贴：选择“公式”，运算“无” ，选择“跳过空单 元”确定。 1.3 工作薄之间数据链接 练习操作文件：数据链接目标.XLS 、数据链接源_A.XLS 、数据链接源 _B.XLS 。 1什么是数据链接 是指

40、源数据区域与目标数据区域链接在一起，源数据区域的数据发生变化， 目标数据区域的数据也随之发生变化。 19 2数据链接的几种情况 在同一个工作簿中的不同工作表之间，在不同工作簿的工作表之间的数据 复制，是复制了单元格的地址，而不仅仅是数据。 输入公式时的单元格引用也是数据链接。 制作图表所引用的数据也是链接的。 3工作薄间的数据复制 打开 “数据链接目标 .XLS” 、“数据链接源 _A.XLS ” 和 “数据链接源 _B.XLS ” 文件分别选择 A、B 两个文档的一、二、三、四、五、六月份的数据Ctrl+C 切换到“数据链接目标” 文件选择不同位置编辑选择性粘贴“粘贴链 接”确定。 “数据链

41、接目标”文件中的第一季度、第二季度、上半年、东部、西部、 北部数据计算使用数组方式解决。 4计算结果链接 打开“合并计算数据链接目标.XLS” 、 “合并计算数据链接源 _A.XLS ”和 “合 并计算数据链接源 _B.XLS ”三个文件，进行合并计算，选择“创建连至数据源 的文件” 。 2数据预测 2.1 单变量 /双变量模拟运算 练习操作文件 ：模拟运算表 .XLS 模拟运算是数据预测的重要方法，在给定一定模型（计算公式）情况下，可 以预测各种变量的结果。 1单变量模拟运算 单变量即在一组模拟运算的元素中，有一个是变化的量， 如贷款额、 贷款利 率和贷款期限，贷款利率是变化的，其他是固定的

42、。 模拟运算结构有两种： 竖式结构 如下表 1 所示。 偿还贷款试算表 定值利率变动月偿还额 贷款额100,000.00 8.50% 贷款年利率8% 9.50% 贷款期限（月）120 10.50% 11.50% 12.50% 13.50% （表 1 单变量求解竖式结构） 20 操作方法 ： A在月偿还额下方用PMT() 计算出结果（数据模型） ，如表 2 所示。 偿还贷款试算表 定值利率变动月偿还额 ￥-1,213.28 贷款额100,000.00 8.50% 贷款年利率8% 9.50% 贷款期限（月）120 10.50% 11.50% 12.50 % 13.50 % （表 2 用 PMT()

43、 函数计算出数据模型） B选择“利率变动”和“月偿还额”两列数据（从第一格选起）数据 模拟运算点击图1 对话框中的“输入引用列的单元格” （因为竖式结构）再 点击贷款年利率 8%单元格确定。结果如表3 所示。 （图 1 模拟运算表对话框） 偿还贷款试算表 定值利率变动月偿还额 ￥-1,213.28 贷款额100,000.00 8.50% ￥-1,239.86 贷款年利率8% 9.50% ￥-1,293.98 贷款期限（月）120 10.50% ￥-1,349.35 11.50% ￥-1,405.95 12.50% ￥-1,463.76 13.50% ￥-1,522.74 （表 3 单变量模拟运

44、算竖式结构计算结果） 是横式结构如下表4。 与竖式结构相比，操作方法区别： A在月偿还额右边用PMT() 函数计算出数据模型； B选择“利率变动”和“月偿还额”两行右的区域； C在模拟运算对话框中，点击“输入引用行的单元格” 21 贷款额 贷款 年利率 贷款期限（月） 定值100,000.00 8% 120 利 率 变动 8.50% 9.50% 10.50% 11.50% 12.50% 13.50% 月 偿 还额 ￥-1,213.28 ￥-1239.86 ￥-1293.98 ￥-1349.35 ￥-1405.95 ￥-1463.76 ￥-1522.74 （表 4 单变量求解横式结构） 2双变量

45、模拟运算 双变量即有两个变化的量， 使用一个公式进行模拟运算。仍以贷款为例。 在 贷款期限不变的情况下，贷款额与贷款利率都在变动，模拟出不同的结果。 双变量模拟运算的方法 是： 在公式单元格下方输入一组数组， 如不同的利率； 在公式单元格右输入一组数组，如贷款金额。 运算的前提是，必须有另一个要素，即贷款期限是不变的（在贷款总额、利 率和贷款期限三个要素中，只能有两个是变化的量，一个必须是固定的值）。 本金 ￥0.00 ￥10,000.00 ￥11,000.00 ￥12,000.00 ￥13,000.00 ￥14,000.00 利率 （年） 10.0% 10.5% 11.0% 11.5% 12

46、.0% （表 5 双变量模拟运算结构） 操作方法： A在公式单元格，用PMT() 函数计算出结果（数据模型） ，选择利率单元 格为利率表下的空单元格，选择贷款额为贷款额表右的空单元格。 B选择公式单元格向右向下的全部区域数据模拟运算表“输入引用 列的单元格”选择利率的空单元格， “输入引用行的单元格”选择贷款额的空单 元格。结果如图 6 所示。 本金 ￥0.00 ￥10,000.00 ￥11,000.00 ￥12,000.00 ￥13,000.00 ￥14,000.00 利率 （年） 10.0% (￥132.15) (￥145.37) (￥158.58) (￥171.80) (￥185.01)

47、 10.5% (￥134.93) (￥148.43) (￥161.92) (￥175.42) (￥188.91) 11.0% (￥137.75) (￥151.53) (￥165.30) (￥179.08) (￥192.85) 11.5% (￥140.60) (￥154.65) (￥168.71) (￥182.77) (￥196.83) 12.0% (￥143.47) (￥157.82) (￥172.17) (￥186.51) (￥200.86) （表 6 双变量模拟运算结果） 公式单元格 22 2.2 单变量求解 练习操作文件 ：单变量求解 .XLS 单变量求解是经营管理中进行预测的重要手段。

48、单变量求解是在一组计算数 据是，有一个数据是不确定的，要推导出来。 举两个最简单的例子。 计算学生成绩 某学生期未目标是总评成绩85 分。 计算公式：总评成绩 =期初成绩 *30%+ 期中成绩 *30%+期未成绩 *40%。现在有了两次的成绩，即：期初成绩为89 分， 期中成绩 67 分，期未成绩应该达到多少分才能达到目标。 第 一 步 ： 给 定 计 算 模 型 。 在 总 评 目 标 结 果 单 元 格 中 输 入 “=B7*30%+B8*30%+B9*40%” ，计算出结果为 46.8，如下图 2 所示。 （图 2 输入公式） 第二步：工具单变量求解输入如下图3 所示的求解条件：目标单元格： B7；目标值： 85；可变单元格 B6确定。结果如图4 所示。期未应该为95.5 分，才能达到目标。 （图 3 设求解条件） （图 4 求解结果） 计算在偿还能力一定条件下可贷款额 在模拟运算中， 我们解决了需要月还贷款额的问题。那么，现在提出一个问 题，比如，一个企业每月只能还贷款100000 万，若贷款利率为9.5