Excel基础课件

nullnullExcel的使用技巧主讲：新航线培训中心 旗下项目：新航线计算机二级培训 计算机主管：18283583693 中公公务员培训 公务员主管：18283596010 文登考研培训 考研主管：15008311864null新航线培训中心（雅安站）     诚聘校园代理 新航线旗下项目：新航线计算机 中公公务员              文登考研 待遇：提成+ 奖金+ 暑期社会实践证明+各 种计算机，公务员，考研以及营销类学习资料共享 有意者，发送你的“姓名+学院+寝室号+电话号码+QQ号码”到 15008311864 . （此招聘长期有效）电子表格软件Excel电子表格软件Excel6.1 概述 6.5 数据 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 6.2 工作表的编辑 6.6 宏 6.3 图表 6.7 数据交换 6.4 数据管理 6.8 插入各种对象 6.9 打印 主讲：新航线培训中心 电话联系方式:15008311864 旗下项目：新航线计算机二级培训 中公公务员培训 文登考研培训6.1 概述6.1 概述6.1.1 功能简介 Excel 2003是微软公司办公集成软件Office 2003的组成部分。其主要功能是： 1. 电子表格一个Excel文档由若干个表（或称页，Sheet）组成，而每一张表（或页）由若干个单元格组成。单元格是Excel中数据处理的基本组成单位。用户可以方便地在单元格中进行各类数据（字符、数值、日期等）和公式（四则运算、函数等）的输入和修改，还可以方便地对单元格的格式（字符的字体、字型、字号和颜色等，数值的格式、边框和背景等）进行设置。null2. 由表作图利用图表向导功能，可方便地由 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 好的数据表作出各种类型的统计图，并可进一步修改图的格式。图会随着数据的变化而变化。 3. 数据管理可将一个数据表视作一个数据库，不用编程或输入命令，而通过工具按钮或菜单选项，就方便地实现数据的排序、筛选和分类汇总等操作。 4. 数据分析利用其提供的函数和宏，可进行从简单到复杂的数据分析。如财务函数、统计函数、单变量求解、模拟运算、规划求解、数据透视、方差分析、回归和统计中的各种检验等等。 5. 与其它程序交换数据可和xBASE、Lotus等表格和数据处理软件进行数据交换，并可从网上下载数据。null6. 插入各种对象可插入图片、剪贴画、艺术字、声音等对象，使得电子表格更加有声有色，丰富多采。 综上所述，Excel 集成了Word、Lotus、xBASE、SPSS等软件的一些优点，是一个功能强大的办公软件。若无特别说明,本章提到的Excel 均指Excel 2000。 6.1.2 安装、启动和退出 1. 安装Office 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 版和专业版都包括Excel，所以只要按照缺省或最小方式安装了Office，就安装了Excel。当然，也可以选择单独安装Excel。 2. 启动可以使用以下方式中的任何一种进入Excel。null选择Windows任务栏的“开始|程序|Microsoft Excel”，单击之，即进入Excel窗口并打开一个电子表格空文档。 选择Windows任务栏的“开始|新建Office文档”，在“常用”中选择“空工作薄”，双击之，即进入Excel窗口并打开一个电子表格空文档。 选择Windows任务栏的“开始|打开Office文档”，找到一个已经编辑过的Excel文档，双击之，即进入Excel窗口并打开该电子表格文档。 选择Windows任务栏的“开始|文档”， 找到一个已经编辑过的Excel文档，单击之，即进入Excel窗口并打开该电子表格文档。null3. 退出双击Excel窗口左上角的控制按钮，或单击右上角的“×”，或用快捷键“Alt+F4”均可退出Excel。若当时编辑的文档没有保存，系统会提示用户保存；此时，用户须根据情况选择“是”或“否”方能退出。 6.1.3 窗口界面 与Word等其它Office程序相似，Excel的工作窗口包括：标 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 栏、菜单栏、工具栏、状态栏和文档窗口。另外，还有一个特殊的编辑栏，如图6.1所示。null图6.1null1. 标题栏位于窗口上部第一行。显示正在编辑的文档名称（空文档的默认名为Book1.xls等）。标题栏最左边为控制菜单按钮，右侧为最小化、恢复（或最大化）和退出三个按钮，其作用和一般Windows窗口的相应按钮相同，这里不再赘述。 2. 菜单栏位于窗口上部第二行。一般有文件(F)、编辑(E)、视图(V)、插入(I)、格式(O)、工具(T)、数据(D)、窗口(W)、帮助(H)9个菜单项；当光标在图标上时，还会多出一个图表(G)项。单击某个菜单项或按快捷键“Alt+X（X为F、E等）”，就会出现一个下拉菜单；每个下拉菜单又有若干个可选项目，单击某项就执行相应操作。null若某项右边有“…”，表示下面有对话框；若某项右边有“>”，表示还有下级菜单选项。 Excel 的基本操作都可通过菜单方式实现。 3. 工具栏为了使操作更加直观简捷，Excel 将一些常用的菜单项做成工具按钮的形式，排列在菜单栏下面或其它位置（用户可以用拖动等办法改变该位置）。改变菜单项“视图”中的“工具栏”中的某些选项，可以控制相应的工具栏出现与否。一般情况下，在菜单栏下面出现“常用”和“格式”两栏工具按钮，在最下面的状态栏的上方出现“绘图”工具按钮，其它工具栏还有图表、图片、艺术字、窗体等。null将鼠标指针指向某一工具按钮并停留一会，按钮下面将浮现出对该按钮功能的简单解释框。有些工具按钮的右边带着倒三角符号，表示该按钮有许多可选参数，单击之即弹出参数菜单。有些按钮可以同时选用（已被选用者底色变浅），如字体的倾斜、加粗等；有些则互相排斥，如位置的居中、左右对齐等。 常用工具栏的按钮从左到右依次是：新工作薄、打开文件、保存文件、打印、打印预览、拼写检查、剪切、复制、粘贴、格式刷、恢复、重复、超级链接、Web工具栏、自动求和、粘贴函数、升序排列、降序排列、图表向导、地图 、Office助手。null格式工具栏的按钮从左到右依次是：字体、字号、加粗、倾斜、下划线、左对齐、居中、右对齐、合并及居中、货币式样、百分比式样、千分位式样、增加小数位数、减少小数位数、边框、填充色、字体颜色。 4. 编辑栏位于工具栏和文档窗口之间。左边是单元格的名称框，右边是单元格编辑区，显示单元格输入内容；中间出现“×”、“”和“=”三个按钮，分别表示“取消”、“输入”和“公式”。 5. 状态栏在窗口的最下面，说明Excel 的工作状态。左边是消息区，显示Excel 在做什么，如“输入”、“就绪”等。右边显示大写或小写、插入或覆盖等状态信息。null注意：状态栏和编辑栏的出现前提是菜单项“视图”中的相应选项被选中。 6. 文档窗口每打开一个Excel文档，就出现一个文档窗口。一个Excel 文档称作一个“工作薄（Book）”，一个工作薄由若干个工作表（Sheet）组成。窗口最下面一行显示当前在哪个表上工作，如图6.1所示，Book1中可以看到的工作表为Sheet1，Sheet2和Sheet3。当前在Sheet1上工作，称作“当前工作表”，其名称带有下划线，且为白色填充。要使用其它工作表，可以单击向右（或向左）的黑三角（切换按钮）。null工作表由垂直和水平方向交叉形成的单元格组成，垂直方向最多可以由256列，分别称作A列，B列，…，AA列，AB列，…，IA列，IB列，…，IV列；水平方向最多可以有65536行，分别称作 1行，2行，3行…。行和列交叉形成单元格。每个单元格都有名称，默认为列名+行名。例如：第一列的单元格分别为：A1，A2，A3…；第一行的单元格分别为：A1，B1，C1，…。拖动右边的垂直滚动条可以改变窗口中内容的上下位置，拖动下边的水平滚动条可以改变窗口中内容的左右位置。 每个文档窗口的大小和在Excel 窗口中的位置都可改变。若要关闭一个文档，单击该文档窗口右上角的“×”即可。null6.1.4 文档的新建、打开和保存 采用上述打开Excel的方法，可以新建一空Excel文档，或打开一个已有的文档。 在进入Excel窗口后，也可使用菜单项“文件|新建或打开”（或常用工具栏中的“新建”或“打开”按钮），实现新建和打开文档的操作。 若要保存正在编辑的文档（工作文档窗口），可以使用菜单项“文件|保存”（或常用工具栏中的“保存”按钮），也可以选择菜单项“文件|另存为”将当前文档以另外一个名字保存，或保存到另外一个路径中，或保存为另外一种格式。6.2 工作表的编辑6.2 工作表的编辑单元格是工作表的基本组成元素。工作表的编辑就是单元格中数据的输入和修改，以及单元格格式的设置。 6.2.1数据输入和修改 要在某个单元格输入内容，首先用鼠标单击该单元格，称为“激活单元格”，此时该单元格四周被黑色边框包围，同时编辑栏左边出现该单元格名称，状态栏左边显示“就绪”状态。可用英文或某种汉字输入法输入内容，输入过程中状态栏显示为“输入”。按回车键，或者将光标指向别的单元格，都表示输入结束。null也可以在选中某个单元格后，用鼠标单击编辑栏的输入区域，输入内容后按回车键即可。 要修改一个单元格中的内容，可以双击该单元格，使鼠标指针变为“I”形状（可以理解为“Insert”的首字母），就可在插入状态下修改原有内容；也可在选中该单元格后，单击编辑栏的输入区域，修改其中内容。当然，如果原有内容完全不要，可以像前面输入内容的方法一样，在单击单元格后直接输入新内容。 Excel单元格中数据的类型区分虽然没有xBASE等那样严格，且不同类型之间可以方便灵活地相互转换，但输入数据时也要注意不同的类型，有不同的输入方法。null1. 数值直接输入即可。在英文输入状态下，可以输入任意的正数、负数，如3，3.14, -3, -3.14 等；可以输入百分数，如10%, 12.34% ；可以输入以科学计数法表示的数字，如1e7, -5e-7等。 2. 字符串若字符串不全是数字，也不是科学计数法表示的数字，则可以直接输入，如：姓名，部门，张三，Smith，等。若该字符串全由数字组成，如邮政编码（如100871）和电话号码（如01390106678）等，则须先输入单引号“′”，后输入数字，表示这是一个字符串，而非一般可计算的数字(若要在开头输入一单引号，则需输入两个单引号)。 3. 日期可按“年月日”，“月日”， “年/月/日”，“月/日”，等形式输入。null例如：二零零零年十月一日，可以输入“2000/10/1”,或“2000101”；十月一日，可以输入“10/1”，或“101”。 时间：可按“时∶分∶秒”或“时∶分”的形式输入。 例如：十三点三十分二十五秒，可输入13∶30∶25; 一分三十秒，可输入1∶30。 【例6.1】输入如图6.2所示的工作表。第一行为整个表的名称说明，第二行为每列的属性说明，第三行到第八行为实际数据。该表有字符串、数字、日期等类型的数据。注意，销售额和利润两列暂时不填入数据。将该工作薄（活动文档）保存为sale.xls。 null图6.2null6.2.2 公式输入和修改 在单元格中不仅可以输入上述的数据常量，还可输入类似于编程语言中的变量，即公式。其输入过程是：选中欲输入公式的单元格，输入“=”，然后输入和公式有关的单元格名称、常量、四则运算符号（+，-，*，/）、括号和函数等，按回车键。和公式有关的单元格的数值被代入公式，计算结果显示在公式单元格中。所以在单元格中看到的是数字，但在编辑栏的输入区域看到的是公式。输入公式过程中，碰到单元格名称，也可直接单击相应单元格，则单元格名称自动填入公式，用户不必自己输入。null【例6.2】［例6.1］中的销售额=销售量*单价，要计算第三行的销售额的数值，并将结果放在单元格中，具体做法是： 单击单元格 I3，输入“=”，单击G3，输入“*”，单击H3，按回车键。计算结果立刻出现在单元格 I3中，但是在编辑栏的输入区域中看到的是公式:=G3*H3。 销售额列其它单元格可类似输入，也可利用公式复制功能。单击I3，将光标置于单元格右下角，光标指针由中空的大十字架变为细小的十字架；按住鼠标左键，向下面的单元格拖动，则与第一个单元格类似的公式被复制到下面的单元格中，单元格中出现的是计算结果。null假设利润列可以用公式计算：利润=销售额*利润率，而各个部门的利润率不同。例如：网络部的利润率最高，为30%，软件部为20%，微机部为10%，则以下几个单元格可分别填入公式：J3=I3*10%, J4=I4*30%, J5=I5*20% 利润列的其它单元格可类似输入，或利用公式复制功能来实现。 若在最后一行增加合计一栏，则可计算本月整个公司销售额和利润的总和。销售额的总和放在单元格 I9 中，利润的总和放在 J9中。 选中单元格I9，单击常用工具栏的“自动求和”按钮（即“∑”按钮），则在等号后面出现一个函数 SUM(I3∶I8)，并将上面同列中几个单元格用虚线框起来，表示要求出这几个单元格的数值之和。null按回车键，系统将这些单元格的数值的和求出，其值显示在单元格I9中，而在编辑栏的输入区域显示的是求和公式。 利润的求和公式的输入类似，J9= SUM(I3∶I8)。当然也可通过复制I9的方式实现。 6.2.3 单元格格式 选中某个单元格，选择菜单“格式|单元格格式”，或者在该单元格上单击右键，在弹出的菜单中选择“设置单元格格式”，就会弹出图6.3的对话框。其中选项的含义是:null图6.3null1. 数字将单元格的数值按照指定格式显示出来。实际上也可以完成数据类型的转换。有常规、数值、货币、会计专用、日期、时间、百分比、分数、科学记数、文本、特殊和自定义等。例如：数字选项设置为货币，则其界面如图6.4所示。 可以选择小数点后保留几位数字（一般情况为两位），货币符号（如人民币用“￥”，美元用“＄”等），负数的表示方法（一般用“-”）。通过示例可以看到实际例子。null图6.4null图6.5null2. 对齐设置文本的对齐方式。文本方向可以设置为水平、垂直或任意角度。水平对齐可以选择常规、左（缩进）、居中、靠右、填充、两端对齐、跨列居中；垂直对齐可以选择靠上、居中、靠下、两端对齐；文本控制可以选择自动换行、缩小字体填充、合并单元格。 3. 字体可选择字体、字形、字号、下划线、颜色、特殊效果。设置改变，“预览”窗口就可看到实际效果。当然，这些设置也可以通过格式栏上相应的按钮和选项来实现。 4. 边框可以选择单元格的边框有无、边框的形状、线形、颜色。null5. 图案设置单元格底纹的颜色和图案。 6. 保护有锁定和隐藏两种方式。但只有在保护工作表的情况下，锁定单元格或隐藏公式才会生效。 6.2.4 剪切、复制和粘贴 与Office其它套件类似，Excel 也可以方便地通过菜单、工具按钮和鼠标拖动三种方式实现文本、图形和其它对象的剪切、复制与粘贴。不管采用什么方式，前提都是先要选中某些单元格。选中的方法可以用按住鼠标左键移动的方法，也可以用按住Shift加上、下、左、右等移动键的方式。null选中的单元格会反相显示（即黑底白字），四周边界有边框。但和Word不同的是，第一个单元格仍然是正常显示（白底黑字）。 1. 菜单方式通过菜单“编辑|剪切”，可将选中区域从原来位置剪切掉，并保存在剪贴板中。然后，用鼠标单击其它单元格，选择菜单“编辑|粘贴”，可以将剪贴板中的内容复制到以该单元格为左上角的区域中。通过菜单“编辑|复制”，可以将选中区域的内容复制到剪贴板中，原来选中区域的内容仍然保留。 2. 工具按钮方式通过常用工具栏上的剪切、复制和粘贴按钮，也可实现菜单方式相应的操作。null3. 鼠标拖拉方式选定某个区域后，将光标移动到区域边界上，鼠标指针变为斜向的箭头()，按住鼠标左键拖动，移动到目标单元格后松手，即将选定区域内容移到新位置；若在按住Ctrl键的前提下拖动鼠标，则实现对选中区域的复制。 注意：粘贴是将剪贴板中的内容（包括格式、公式、数值等）全部复制到目标区域。若仅复制公式或数值，则使用“选择性粘贴”， 如图6.6所示。一般情况的粘贴指全部的粘贴。 若仅粘贴数值，则选中“数值”，“确定”即可，通过这种方式也可将单元格中的公式转化为数值。null图6.6null“运算”是选择目标区域内容与待粘贴内容的运算关系，运算结果填入目标区域。“转置”是将剪贴板中内容按照矩阵的方法进行转置，结果填入目标区域。6.3 图表6.3 图表6.3.1由表作图 若要由图6.2的销售报表作出一个如图6.7所示的销售图，可以按照以下步骤：单击常用工具栏的“”图表向导按钮（作图），或者选择菜单“插入|图表”，出现选择对话框：图表向导\|4步骤之1\|图表类型。如图6.8所示。 标准类型可选择柱形图、条形图、折线图等14种图表，而且每一种类型又分为若干子图表类型。null图6.7null图6.8null图6.8中的柱形图的子图表类型有簇状柱形图、堆积柱形图、百分比堆积柱形图等7种。用户还可选择“自定义类型”。自定义类型分为内部和自定义两种。内部的类型包括彩色堆积图、彩色折线图等20种。 选择标准类型中的条形图的簇状条形图，单击“下一步”，出现对话框：图表向导|4步骤之2|图表数据源(图6.9)。 在数据区选项中，可以选择数据区域和系列。系统会自动选择表中的一部分为数据区域，并将该区域用闪烁的虚线框起来。null图6.9null用户可以根据情况重新选择，如图6.9所示，用户选择的是包括标题行和数据行在内的区域：＄C＄2∶＄J＄8。“系列产生在”可以选择“行”或“列”。一般情况，输入数据时喜欢一行行地输入，最上面是标题行。每一列成为一个系列，所以一般“系列产生在”选择“列”。当然，如果数据是一列列地输入的，则“系列产生在”选择“行”。 在系列选项中，可以选择图中出现哪些系列。系统会将所有的数值型系列都选中，如图中的单价、数量、销售额和利润。在此例中，单价和数量的图形比较毫无意义，用户可将它们从系列中删除掉，只留下销售额和利润。每一系列都会出现相应的名称和数值区域。null“分类（X）轴标志”确定哪些系列数据出现在X轴标志上。图中标志过多，用户可重新选择，例如只选中姓名列中的数据，则只有姓名出现在X轴上（图6.10）。 单击“下一步”， 出现对话框：图表向导\|4步骤之3\|图表选项（图6.11）。 可以进行标题、坐标轴、网格线、图例、数据标志、数据表的选项设置。最常用的是标题的设置，如图6.11所示，用户可以在图表标题、分类（X）轴、数值（Y）轴中输入相应的标题说明。 单击“下一步”， 出现对话框：图表向导\|4步骤之4\|图表位置（图6.12）。null图6.10null图6.11null图6.12null图表可以放在“新工作表”中，也可以通过“嵌入工作表”插入当前工作表。一般选择“嵌入工作表”，以便和数据进行比照分析。 四步骤完成后，单击“完成”，则将生成的图表插入当前工作表。在作图过程中，可通过单击“上一步”、“下一步”去选择要改变的选项。 6.3.2 图的编辑和修改 图表和Office套件中的其它对象一样，可以在选中后（鼠标标志为斜向的箭头“”）用鼠标拖动移动其位置；还可以通过拖动位于四个角或四条边的中心位置的黑色小方块来改变其大小。null如图6.13所示，一个图表中包括下列组成元素：绘图区、图例、分类轴、分类轴标题、数值轴、数值轴标题。若要得到某个元素（如分类轴）的特性，可以将光标指向该元素，单击右键，将弹出一个菜单选项，包括“分类轴格式”（其它元素类似）和“清除”两项。单击“坐标轴格式”，将弹出一个类似“单元格格式”的对话框，其中包括字体、对齐、图案等选项。设置合适，可以使图表更加漂亮。 也可在选中图表后，选择菜单“图表”（只有这时才出现）中的“图表选项”，来改变设置。选中“图表”中的“图表类型”和“数据源”，可以改变图表类型和数据源（系列等）。null图6.13null在“图表”中还有两项：一是“添加数据”，可以用来增加新的数据；二是“添加趋势线”，可以用来进行趋势预测。如图6.14所示。 类型：包括线性、对数、多项式、乘幂、指数、移动平均。 选择数据系列：可以选择要作趋势分析的系列数据。 选项：可以设置趋势线名称；可以设置推测周期、截距、显示公式和R值等。 趋势线分析在社会学、财务、经济、管理、心理等领域应用很广。 注意：图表是“由表作图”，当表中的数据改变时，图形也立即作出相应的调整。null图6.146.4 数 据 管 理6.4 数 据 管 理6.4.1 记录单 使用菜单栏的“数据”中的选项“记录单”，可以对数据一条一条地进行管理。 【例6.3】 仍然使用［例6.1］的销售报表，将光标置于该表的任意单元格上，选择菜单栏的“数据”中的选项“记录单”，则出现如图6.15所示的界面。 该对话框将销售报表的一行数据显示出来，用按钮“上一条”或“下一条”进行上下翻页（直接拉动滚动块也可），用“新建”按钮可以输入新的一行数据，用“删除”按钮可以删除当前的一行数据，用“关闭”按钮可以关闭对话框，回到数据表上。null图6.15null如要修改已有数据，直接单击某个字段（如姓名，单价等）后面的输入栏，输入新值即可。但是，因为销售额和利润中输入的是公式，其值是由其它相关单元格决定的，所以，用户只能看到它们的当前值，不能在“记录单”中直接修改。 6.4.2 排序 使用常用工具栏上的排序按钮，可以实现简单排序；使用菜单栏的“数据”中的“排序”选项，可以实现多达三个关键字的复杂排序。 不管使用什么排序方法，宜将原始数据区复制到另外一个空白区域或另外一个新工作表上，在新的数据区上排序，以保护原来的数据不被破坏。null1. 简单排序将鼠标选中数据区某列的标题或任一单元格上，单击常用工具栏上的升序按钮，则数据区按照该列的数据升序重新排列。如果该列为数值，如销售额或利润，就按照数值从小到大的顺序排列；如果该列为日期或时间，就按照由先到后（即由远到近）的顺序排列；如果该列为字符串，就按照字符的ASCII码的顺序由小到大排列。 例如，用鼠标选中销售额列，单击升序按钮，排列结果如图6.16所示。 用鼠标选中日期列，单击升序按钮，排列结果如图6.17所示。 用鼠标分别选中部门和姓名列，单击升序按钮，排列结果分别如图6.18和6.19所示。null图6.16null图6.17null图6.18null图6.19null从上面的排序结果可以看出，常用汉字的排列顺序是按照其拼音的先后顺序排列的。 如果单击常用工具栏的降序按钮，排序结果则刚好和上面相反。 2. 复杂排序将光标选中数据区的任意单元格上，选择菜单“数据”中的排序，则出现如图6.20所示对话框。 这种排序可以设置多达三个关键字，每个都可以选择递增或递减。当前数据清单如果包括标题行，则选中“有标题行”。 若选中“选项”按钮，则出现图6.21中的对话框。“自定义排序次序”一般按照“普通”方式；英文字母若要区分大小写，则选中“区分大小写”；null图6.20null图6.21null“方向”要根据数据区的输入方向确定，一般为“按列排序”；“方法”是针对汉字而言的，可以选择“字母排序”（按汉字内码排列）或“笔画排序”，一般为“字母排序”。 例如：“主要关键字”选“部门”，顺序按“递增”，“次要关键字”选“姓名”，顺序按“递减”，“选项”中“方法”选“笔画排序”，则排列结果如图6.22所示。 当只选择一个关键字和默认选项时，复杂排序就和简单排序的结果完全一致。null图6.22null6.4.3 筛选 “数据”菜单中的“筛选”功能可以方便地选择出符合某些条件的数据。筛选分为“自动筛选”和“高级筛选”。与排序类似，不管哪种方式，在筛选以前，也宜将数据复制一份，粘贴到空白区域或另外的工作表上，以免破坏原始数据。 1. 自动筛选单击待筛选数据区（如销售报表）的任意单元格，选择菜单“筛选|自动筛选”，每个标题单元格的右边出现向下的三角。单击该三角，出现下拉列表，前面三个为：（全部），（前10个..），（自定义...）。接下来是该列中出现的值（注意：无重复）。例如，图6.23中下拉部门列，除了前面的三项外，依次出现的是该列中出现的三个值，即三个部门的名字。null图6.23null这时若要筛选出该列中等于某个值的数据，选中该值即可。例如：要筛选出部门为“微机部”的数据，单击“微机部”，则数据区中看到的只有符合这个条件的两行数据。此时其它不符合该条件的数据并未被清除，仅仅是被暂时隐藏而已；若要再次看到所有数据，单击“部门”的下三角，从列表中选择“全部”，即可看到全部数据。 在筛选出“部门”=“微机部”的前提下，也就是仅看到两条记录的情况下，若要进一步筛选，例如，要找出姓名为某个值的数据，只要单击“姓名”右边的下三角，进一步选定某个值即可。 注意：这时“姓名”列中可选的值比未按“部门”筛选前要少，这时实际是双重筛选，如图6.24所示。null图6.24null为了取消筛选，可以再次选择菜单“数据”中的“筛选”中的“自动筛选”，可以看到“自动筛选”前面的“”由有到无，各个标题右边的下三角消失，数据区恢复到原来没有筛选时的状态。 再看一下“自定义”的使用方法。选择菜单“数据|筛选|自动筛选”，下拉“销售额”右边的下三角，从列表中选择“（自定义...）”，弹出“自定义自动筛选方式”对话框，要求选择条件表达式。左边的下拉列表包括“等于”、“不等于”等多种条件关系，右边的下拉列表为该标题列（这里是“销售额”）可选值，也可输入一确定值。还可选择两个条件，它们之间是“与”或“非”的关系。null例如：如果需要筛选出20000<=销售额<=40000的数据，在上面的条件中左栏选择“大于或等于”，右栏输入20000，在下面的条件中左栏选择“小于或等于”，右栏输入40000，中间的关系选定“与”，单击“确定”，则筛选出符合这个条件的数据，如图6.25所示。 又如：如需在全部数据中筛选出姓黄和姓张的数据，则首先选择“销售额”下拉列表中的“全部”，恢复到全部显示的状态，再选择“姓名”下拉列表中的“（自定义...）”，在弹出的“自定义自动筛选方式”对话框中，上面条件的左栏选择“等于”，右栏输入“张*”， 下面条件的左栏选择“等于”，右栏输入“黄*”，中间的条件关系选“或”。null图6.25null注意：如对话框下部所提示的，这里用“*”代表任意多个字符；两个条件之所以选“或”的关系，是因为二者不是必须同时成立，而是有一个成立就行。单击“确定”，就筛选出符合这个复合条件的数据来。 若筛选后标题栏下面没有一行数据，则表示无符合条件的数据（记录）。 2. 高级筛选“自动筛选”的特点是在原来的数据区上筛选，结果在原来区域上显示，不能明显地看到条件。若需要筛选出来的数据和原来数据区分开，并且能看到条件的表达，可使用“高级筛选”。在使用“高级筛选”前，需在一个空白区域写出条件表达式。它可包括一列中的多个条件、多列中的多个条件。null使用某一列中的三个或多个条件如果对于数据清单的某一列有三个或多个筛选条件，可直接在相邻的行中从上到下依次键入各个条件。例如，下面的条件区域将显示在“部门”列中包含“微机部”或“网络部”的数据行。部门微机部网络部 (2) 使用两列或多列中的条件如要查找同时匹配两列或多列中条件的数据，在条件区域的同一行中输入所有的条件。例如，下面的条件区域将显示所有“部门”列包含“微机部”、“姓名”列包含“张*”的数据行，也即“微机部”中姓“张”的数据行。部门[]姓名微机部[]张*当然，也可以通过菜单“数据|自动筛选”，分别指定不同列中的多个条件，显示匹配所有条件的数据行。null(3) 若要查找或者匹配一列中的一个条件，或者匹配另一列中的一个条件的数据行，则在条件区域的不同行中输入条件。例如，下面的条件区域显示将所有或者销售额小于20000、或者销售额大于 40000 的数据行。销售额[]销售额<20000[][]>40000(4) 若要查找交叉匹配一列中的两条件之一与另一列中的两条件之一的数据行，则在不同的行中键入条件。例如，下面的条件区域将显示在“部门”列中包含“微机部”，且销售额大于30000 ，或者“部门”为“网络部”，且销售额大于 40000 的数据行。部门[]销售额微机部[]>30000网络部[]>40000条件区域写好后，选择菜单“数据|筛选|高级筛选”，弹出图6.26的区域选择对话框。null图6.26null方式：一般选择“将筛选结果复制到其他位置”，以免覆盖原有数据。 数据区域：系统回自动选定光标位置所在的数据区；如果不对，用户可以在单击“数据区域”输入栏右边的选择按钮后，重新选定某个数据区域（要包括标题行）。 条件区域：需要用户选定。用户可以在单击“条件区域”输入栏右边的选择按钮后，选定某个数据区域（要包括标题行）作为条件区域。 复制到：需要用户选定。用户可以在单击“复制到”输入栏右边的选择按钮后，单击某个空白单元格，作为目标区域的起始单元格，系统将自动向右、向下扩充。null选择不重复的记录：若不选择重复的记录，可选中该项。 所有区域选好，单击“确定”按钮，筛选结果出现在“复制到”区域，如图6.27所示。 6.4.4 分类汇总 使用菜单“数据|分类汇总”，可以对数据表按照某类（列）进行数值列的汇总计算。为了保证分类汇总的正确性，在分类汇总前先要按照某列排序。 【例6.4】欲对上述的销售报表按照“部门”进行分类汇总，先将该表“复制”到一空白区域“选择性粘贴”，只粘贴数值，按照“部门”的升序或降序进行排列，然后选择菜单“数据”中的“分类汇总”，弹出图6.28所示对话框。null图6.27null图6.28null从“分类字段”的下拉列表中选择“部门”，在“汇总方式”的下拉列表中选择“求和”（在其它情况下还可选择“计数”、“均值”、“最大值”、“最小值”、“乘积”等），在“选定汇总额（可多个）”选项中，选中欲汇总的字段（列），这里选择“销售额”和“利润”。单击“确定”，出现图6.29的“分类汇总”结果。 这里只关心“部门”、“销售额”和“利润”三列，故可将其它列删除（注意：前面的选择性粘贴只粘贴了数值，所有列中都无公式，所以删除单价和数量列不会造成销售额和利润的计算错误）。null图6.29null在分类汇总表中，可看到各部门的销售额和利润的汇总结果和总计结果，以及每行数据的值。单击左边的“-”，“-”变为“+”，则仅显示对应的分类汇总结果，看不到其中各行明细数据（仅暂时隐藏起来）；反之单击“+”，则展开明细数据。 若要取消分类汇总，只需再次选择菜单“数据|分类汇总”，从弹出的对话框中选择“全部删除”即可。 6.4.5 数据透视表 菜单“数据”中的“数据透视表”可以帮助用户对数据进行多角度的分析。选择菜单“数据|数据透视表”，则弹出“数据透视表向导”，如图6.30所示。null图6.30null“4步骤之1”是“创建数据透视表的数据源”，选择“Microsoft Excel 数据清单或数据库”。单击“下一步”。 出现“4步骤之2”——“请键入或选定要建立数据透视表的数据源区域”(图6.31)。用户可以选定区域（注意包括标题行）。 单击“下一步”，出现“4步骤之3”(图6.32)。根据提示，可将右部的字段按钮拖到左部的草图上。例如，将“部门”拖到行，“品名”拖到列，将“销售额”和“利润”拖到“数据”区，表示将在“部门”和“品名”的交叉情况下，对这两列数据进行求和计算。还可将某字段拖到“页”上，表示将按照该字段的值分页。null图6.31null图6.32null单击“下一步”，出现“4步骤之4”(图略), 以确定数据透视表显示位置。在“新建工作表”和“现有工作表”任选一个，这里选择“新建工作表”，单击“完成”，在新的工作表上出现“数据透视表”，如图6.33所示。 和分类汇总相比，数据透视表对数据的分析更加透彻、细致，在行和列两个方向都作了汇总计算。如果单击某一求和项，如“软件部”的“求和项：销售额”，则所有该数据的求和项，如销售额的求和项，就都被选中，便于用户分析。null图6.33null在作出一个数据透视表后，如需修改，可以再次选择菜单“数据”中的“数据透视表”，则弹出的是“数据透视表向导4步骤之3”(图6.32)，让用户再次选择行、列和数据等各区域的内容。6.5 数 据 分 析6.5 数 据 分 析6.5.1 函数 和数学中的函数概念一样，Excel的函数也需要若干各自变量。要在某个单元格输入函数表达式，直接单击常用工具栏上的函数按钮“fx”，或者在菜单“插入”中选择“函数”，则出现图6.34所示的函数选择对话框。 Excel 将函数分为财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑、信息、工程、用户定义等类别。null图6.34null用户选择一个函数，就会弹出与之对应的一个函数对话框，提示用户输入什么自变量及各个自变量的含义。下面以一个财务函数PMT为例来讲解函数的用法。 假设要到银行贷款买房，贷款总额为30万，分30年偿清，年利率为5%，那么每年连本带息要还多少钱？函数PMT就可以解决这个问题。 选中一个空白单元格，单击常用工具栏上的函数按钮“fx”，从“粘贴函数”对话框中选择“财务”或“常用”中的PMT，则Excel的界面如图6.35所示。这个函数的解释是：返回在固定利率下，投资或贷款的等额分期偿还额。null图6.35null该函数需要输入五个自变量：Rate，Nper，Pv，Fv和Type。它们各代表什么呢？ 首先将光标点在Rate后面的输入栏中，注意到下面对该自变量的注释是：各期利率，是一固定值。设每年为一期，贷款利率输入5%。 用类似的方法可以看到其它自变量的解释，并输入其值。 Nper 的解释是：总投资期或贷款期，即该项投资或贷款的付款期总数。输入30。 Pv 的解释是：从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累计和。获得银行贷款，就有30万款项入账，故输入30000。nullFv 的解释是：未来值，或在最后一次付款后可以获得的先进余额。如果忽略，则认为此值为0。银行要求在30年后必须偿请贷款，故未来值为0，不必再输入。 Type 的解释是：数值0或1，用以指定付款时间是在期初还是在期末；如果为0或忽略，付款在期末。一般是在每年的年末偿还贷款，所以可以忽略该项。 这几项自变量已经填好，注意下面已经有了计算结果：-19515.43。因为要向银行付出款项，所以为负值。当然，如果贷款总额为负值，则每期付款为正值。null单击“确定”，该函数填入单元格。单元格中看到的是计算数值，输入栏中看到的是公式：=PMT(5%, 30,300000,0) 如要修改自变量的值，可以在输入栏中直接输入新值。例如，考虑一种特殊情况，贷款利率若为0，则计算结果=PMT(0, 30, 300000, 0)=-10000。这个值有助于验证这个函数的正确性。 以上将函数的自变量直接输入得到了函数值。也可以将自变量的值写在某些单元格中，在“粘贴函数”对话框中直接选中单元格即可，如图6.36所示。null图6.36null6.5.2 单变量求解 前面介绍了公式的输入。当与公式有关的单元格的数值改变时，公式的值也会改变。那么反过来，当公式的值改变时，与之相关的某个单元格会怎么变化呢？使用菜单“工具”中的“单变量求解”，可以解决这个问题。这实际上是求解一元方程。但这和数学上的求解方程不同。数学上的求解方程是严格推导出来的，而Excel 的“单变量求解”则是通过一定的方法算出自变量的值。以该值代入公式时，公式的相应值和给定值的误差在某个范围内。下面通过两个例子讲解“单变量求解”的过程。null【例6.5】使用“单变量求解”方程：y=2x。若y为8，x等于几? 将两个相临的单元格，如B2和C2，分别命名为x和y（直接在编辑栏左边的单元格名字中输入即可）。在 y中输入公式 =2*x。 用鼠标选中单元格C2，使用菜单“工具”中的“单变量求解”，出现图6.37“单变量求解”的对话框。 目标单元格：即公式所在单元格，选中C2。 目标值：输入目标数值，如8。 可变单元格：即欲求的自变量所在的单元格，这里选中B2。null图6.37null单击“确定”，求解结果如图6.38。 所得可变单元格的值使得给定的“目标值”和模拟求出的“当前解”刚好一致。 【例6.6】在单元格C4中输入=SIN(B4)。用与［例6.5］类似的方法进行单变量求解，在给定目标值为1的条件下，求解的结果如图6.39所示。 可以看出，给定的目标值和当前解有一定的误差(0.0741%)。 【例6.7】在单元格C6中输入=B6*B6+2*B6+1，即(B6+1)2。用与上面类似的方法进行单变量求解，在给定目标值为9的条件下，求解结果null图6.38null图6.39null如图6.40所示。 解为1.999987≈2。实际上，方程还有一个解-4。可见单变量求解有着局限性。 6.5.3 模拟运算表 和单变量求解相反，模拟运算表是在给定公式的前提下，观察一个或两个自变量变化对公式结果的影响。这里以前述的函数PMT为例，讲解模拟运算表的使用方法。 1. 创建单变量模拟运算表如图6.40所示，单元格 C6=PMT(C3,C4,C5)。如果希望看到年利率C3变化所导致的年度偿还数额C6的变化，就要用到单变量的模拟运算表。null图6.40null单变量模拟运算表的结构特点是：其输入单元格数值（自变量）被排列在一列中（称作列引用）或一行中（称作行引用）。单变量模拟运算表中使用的公式必须引用输入单元格（自变量）。下面介绍怎样创建单变量模拟运算表。 在一列或一行中，键入要替换工作表上的输入单元格的数值序列。 如果输入数值被排成一列，则在第一个数值的上一行且处于数值列右侧的单元格中，键入所需的公式。在同一行中，在第一个公式的右边分别键入其它公式。null图6.41的例子就是这样的。B9～B15中是输入单元格得数值序列，C8=C6。 如果输入数值被排成一行，则在第一个数值左边一列且处于数值行下方的单元格内键入所需的公式。在同一列中，在第一个公式的下方分别键入其它公式。 选定包含公式和需要被替换的数值的单元格区域，图6.41中是B8～C15。 选择菜单“数据|模拟运算表”，弹出“模拟运算表”对话框，如图6.41所示。 如果模拟运算表是列方向的，则在“输入引用列的单元格”编辑框中为输入单元格键入引用。图中输入的是C3。null图6.41null如果模拟运算表是行方向的，则在“输入引用行的单元格”编辑框中为输入单元格键入引用。 单击“确定”，模拟运算结果填入输入引用列右边对应的一列，如图6.42所示。 2． 双变量的模拟运算表如果希望看到年利率和贷款期限的双重变化所导致的年度偿还额的变化，就要使用双变量模拟运算表。双变量模拟运算表中的两组输入数值使用同一个公式，它必须引用两个不同的输入单元格。具体做法是（以图6.43为例）：null图6.42null图6.43null在工作表的某个单元格内，输入所需的引用两个输入单元格的公式，图中E6=C6。 在公式下面同一列中键入一组输入数值，在公式右边同一行中键入第二组输入数值，在图6.43中，E7～E13 列中为年利率的变化，F6～K6行中为贷款期限的变化。 选定包含公式以及数值行和列的单元格区域，图例中为E6～K13。 选择菜单“数据|模拟运算表”命令，弹出如图6.43所示对话框。 在“输入引用行的单元格”编辑框中，输入要由行数值替换的输入单元格的引用，这里为C4。null在“输入引用列的单元格”编辑框中，输入要由列数值替换的输入单元格的引用，这里为C3。 单击“确定”，将运算结果填入所选单元格中，如图6.44所示。 不管是单变量的模拟运算表还是双变量的模拟运算表，当公式改变或与公式有关的其它单元格的数值改变时，模拟运算表的结果都会有相应的变化。null图6.446.6 宏6.6 宏在Excel 中若常重复某些任务，可用宏将其变为可自动执行的任务存储起来。宏就是一系列命令和函数，在需要执行该任务时可随时运行。记录宏就如同用磁带录音机录制音乐，或用摄像机拍摄电视；运行宏就如同用收录机播放音乐，或用放像机播放录像。 在记录或编写宏之前，应当先制订 计划 项目进度计划表范例计划下载计划下载计划下载课程教学计划下载 ，确定宏所要执行的步骤和命令。因为如果在记录宏时出现失误，对失误的纠正也将记录在宏中。每次记录的宏都将存放在附属于某个工作簿的新模块中。null用“Visual Basic 编辑器”，可以编辑宏，并且可以将宏从一个模块复制到另一个模块、从一个工作簿复制到另一个工作簿、给存放宏的模块重新命名或重新命名宏。 选择菜单“工具|宏|录制新宏”，弹出如图6.45的对话框。 “宏名”的默认值为“宏1”，用户可定义，但首字符必须是字母或汉字，其它字符可以是字母、数字或下划线。宏名称中不能有空格；下划线也可作为分词符。null图6.45null“快捷键”为Ctrl+字母键，字母可以是任意大小写，但不能是数字或特殊字符。在包含宏的工作表打开时，宏快捷键将使任何Excel 默认快捷键无效。运行宏时可以用 Ctrl+字母（小写）或 Ctrl+Shift+字母（大写）的快捷键方式。这里定义为Ctrl+g。 “保存在”默认为“当前工作簿”，也可以选择“个人宏工作簿”或“新工作簿”。在“说明”输入栏里，用户可以输入有关的解释说明。 这些选项填好以后，单击“确定”，就开始录制新宏，同时弹出图6.46的停止录制对话框。以后的所有操作结果都将保存在这个宏里，直到按下停止录制对话框上的停止录制按钮()。null图6.46null如果在录制宏时选中了某些单元格，则该宏在每次运行时都将选中原来的这些单元格而不管事先是否选中了其它单元格，因为宏这里记录的是单元格绝对引用。如果要让宏在选择单元格时不考虑活动单元格的位置，则需要将宏设置成为记录单元格相对引用。单击“停止录制”工具栏的相对引用()，按钮Excel 将按照相对引用格式继续记录宏，直到退出 Excel 或再次单击相对引用按钮，转为绝对引用为止。 如果要使宏选中指定的单元格，执行某个操作，再选中另一个偏离该活动单元格一定位置的单元格，可以在记录该宏时混合使用相对和绝对引用。注意：确认相对引用按钮是在已按下还是未按下（绝对引用）的状态。null【例6.8】按照“录制新宏”的方法，录制一个宏，宏名为“宏1”，记录下选中某个单元格区域，然后复制、粘贴到另外一个空白区域的操作。录制结束后，再次选择菜单“工具|宏|宏”，就会弹出如图6.47所示对话框。 在“宏名”下为已经录制好的宏的列表。如刚才录制好的“宏1”。选择某个宏，如默认的“宏1”，就可以去执行了。执行的方式有“执行”和“单步执行”两种。 “执行”即调用宏，显示操作结果。也可以通过快捷键方式来实现，如上面的例子中，不必选择菜单“工具”中的“宏”的“宏”，按Ctrl+g即可执行“宏1”。null图6.47null“单步执行”则是进入Microsoft Visual Basic的编辑界面和调试界面，如图6.48所示。 这里可以看到“宏1”的源程序，可以编辑修改和调试。调试完毕，关闭该窗口，终止调试器，又回到Excel的编辑界面。 “宏”对话框中的“编辑”按钮和上面的“单步执行”异曲同工，也是进入Microsoft Visual Basic的编辑界面和调试界面（如图6.48所示）。“删除”按钮可以删除当前选中的宏；“选项”按钮可以用来重新定义当前选中宏的快捷键和说明。 Excel系统本身提供很多宏，供用户使用。这些宏必须由系统加载后才能使用。选择“工具”中的“加载宏”，弹出图6.49中“加载宏”的对话框。null图6.48null图6.49null系统有以下这些宏可以加载：Internet Assistant向导、Web窗体向导、查阅向导、分析工具库、分析工具库VBA函数、更新加载宏链接、模板应用、条件求和向导、自动保存等。选中的宏表示已经被加载了，可以使用；未选中的宏需经加载后才能使用。 加载后的宏一般成为菜单栏的“工具”中的一个选项。例如“分析工具库”加载后，菜单栏的“工具”中多了一项“数据分析”，选中该项后，出现图6.50“数据分析”的对话框。null图6.50null分析工具包括：方差分析、相关系数等19种。这些工具在统计学、心理学、社会学、经济学、管理学、教育学、数学、物理学等领域都有广泛的应用。 其它加载的宏的使用方法也类似。有些宏（如条件求和、Web窗体等）还以向导的方式出现，帮助用户一步步地完成任务。6.7 数 据 交 换6.7 数 据 交 换6.7.1 多工作表之间的数据交换 一个工作簿可以包括多个工作表，这些工作表以默认的Sheet1、Sheet2、Sheet3等名字出现。用户可以在表名上单击右键，通过“重命名”重新定义名字，也可以通过“删除”删除当前的工作表，通过“插入”插入新的工作表。 在不同的工作表之间交换信息的方法和在一个工作表上交换信息的方法相同，通过菜单里的“复制”、“粘贴”或“选择性粘贴”即可。null6.7.2 合并计算 如果某几个表的结构类似，可以使用“数据”中的“合并计算”功能，将它们按某种计算方法（如求和）合并一起。 例如，假设前面的销售报表例子中，每个月都按照部门作了分类汇总，且只保留部门、销售额和利润三列数据；现有4、5、6三个月的数据，分别位于三张表上；希望对这三张表的数据进行汇总，求出这三个月的销售情况报表。怎样用“合并计算”完成这个操作？图6.51表示其中4月份的分类汇总报表。null图6.51null在一个空白区域（注意：插入点不能在将被合并计算的数据区域内！）选择菜单“数据|合并计算”，弹出如图6.52所示的对话框。 函数：有求和、均值、计数11种。常用的为求和、求平均等。 引用位置：需要求和的数据源的位置。用户可以直接在输入栏输入引用的数据区域，也可以单击输入栏中右边的“”按钮，到某一张表的合适位置选择数据。输入或选择好数据区域后，单击右边的“添加”按钮，数据区域出现在“所有引用位置”列表中。甚至可以单击右边的“浏览”按钮，到其它文件中选择数据区域。null图6.52null所有引用位置：在“引用位置”中被输入或被选定的数据区域以列表形式出现。若选择错误或不当，可以在选中某个区域的前提下，单击“删除”按钮，将其从区域列表中删除。 标志位置：即标题行的位置。一般情况下，在首行输入标题，在最左列输入说明，所以标志位置选“首行”和“最左列”。 图6.53就是将三张表中的数据区域作为引用位置被添加后对话框的内容。 单击“确定”按钮，合并计算后的新表出现在空白区域内。null图6.53null6.7.3 和其它程序的数据交换 在Windows操作系统的操作中，不同的程序之间可以通过剪贴板交换数据，Excel也不例外。如果要将Excel的数据和图形等对象拿到其它程序（如Word）上使用，可以在将Excel 的数据区域和图形等对象选中后，选择菜单“编辑”中的“复制”选项，或者直接单击常用工具栏上的“复制”按钮，或者在选中的对象上单击右键，选择“复制”。这样就将选中内容复制一份到剪贴板上