Excel的使用.doc

公式组E x c e l的公式组可以运算一大组数据而只返回一个结果，或者在每个用户希望显示结果的单元格内显示运算结果。使用公式组的优点在于，通常需要进行一大堆重复计算的操作，使用了公式组后只需要运算一次，这样就大大节省了时间和工作表的空间。下面这个综合性的例子将为您介绍有关公式组的重要内容。在本例中，将使用公式组查找一组数字中满足特定条件的数字。在如图1所示的一组数字中，假设您希望查找最接近该组数平均值的数字。图1使用公式组决定一组数中最接近该组数平均值的数字该组数的范围考虑使用A 1单元格到A 2 0单元格之间的数字（实际上为了体现公式组的优势，可以使用更大的一组数字来完成该项运算）。首先，要查找最接近该组数平均值的数字就必须确定该组数的平均值（ AV E R A G E（A 1：A 2 0）。然后，创建公式将该组中的所有数字与平均值相比较，然后比较组中数字与平均值的差值，最后返回代表最小差值的数字。对于如此复杂的公式，最重要的是将公式分割成较小的单元，一次计算一小部分，最后将所有这些元素合并组成完整的公式。对于本例，要进行下述步骤：1) 确定该组内所有数字（我们称之为A m o u n t s）与平均值（放置于C 1单元格内）之间的差值：A m o u n t s - AV E R A G E ( A m o u n t s )若A m o u n t s代表该组内的每个数字，而且公式作为公式组对待（构造完函数后按下“ C t r lS h i f tE n t e r”键执行运算），该公式将返回一组数字，其中该组中每个数字都代表原始的数字与平均值的差值。提示创建公式组后，必须按下“ C t r lS h i f tE n t e r”键完成操作。即使在编辑任一公式组时也必须按下该组合键，否则公式就写成了通常的、非公式组。2) 收集了这一组代表原始数字与平均值的差值后就必须要确定该组数字中的最小值。另外，该组中有的数字是负数，因此需要考虑最小值时必须要使用绝对值。因此公式为：A B S ( M I N ( x ) )在此“x”代表第1步计算出来的数值。3) 使用“M AT C H”函数，需要找到差值组中代表最小的差值的数字在总的数字组中的位置。公式末尾的“0”表示“M AT C H”公式目标值的精确的匹配位置：M AT C H ( A B S ( M I N ( a m o u n t s - AV E R A G E ( a m o u n t s ) ) ) , ( A B S ( ( a m o u n t s - AV E R A G E ( a m o u n t s ) ) , 0 )4) 最后，还要返回由第2步计算出的最小差值所代表的原始数字。“I N D E X”公式返回在选定区域或组内的单元格内容。INDEX(amounts, MAT C H ( A B S ( M I N ( a m o u n t s - AV E R A G E ( a m o u n t s ) ) ) , ( A B S ( ( a m o u n t s -AV E R A G E ( a m o u n t s ) ) , 0 ) )若要将该公式创建成公式组，在完成该公式时不要忘记按下“ C t r lS h i f tE n t e r”组合键。按下该组合键后， E x c e l将在该公式的外部用大括号环绕。最后的公式如下所示：INDEX(amounts, MAT C H ( A B S ( M I N ( a m o u n t s - AV E R A G E ( a m o u n t s ) ) ) , ( A B S ( ( a m o u n t s -AV E R A G E ( a m o u n t s ) ) , 0 ) ) )有一组数字包括有正数和负数，如何才能找到该组数中最小的正数？可以使用公式组查找该组数字中最小的数字。公式的最内层部分是“ I F”函数的参数部分，设置一个只考虑正数的条件。使用“ M I N”函数查找“ I F”函数运算结果，在完成整个公式后按下“ C t r lS h i f tE n t e r”组合键使公式变成公式组（按下“ C t r lS h i f tE n t e r”组合键将在公式外添加大括号）：M I N ( I F ( A 1 : A 1 0 0 , A 1 : A 1 0 ) ) 图2显示了公式的运算结果。图2使用公式组查找一组数中最小的正数我们需要购买一批计算机设备，需要计算这些设备的总价格，每种设备价格各不相同，如何在一个公式中完成计算呢？一个公式组就可以根据所提供的单价和购买数量计算出您需要的结果。例如，您需要购买的设备见表1：设备 数量 单价打印机 1 2 $ 2 7 5C P U 2 5 $ 9 9 5M o d e m 1 5 $ 8 7显示器 2 5 $ 1 7 5我们可以创建四个独立的公式分别计算每项设备的总价，然后将这四个计算结果相加就可以得到所有设备的总价格。或者我们也可以使用如下所示的公式组（如图3）在一个简单的公式中完成计算，记住在单元格内输入完公式后按下“ C t r lS h i f tE n t e r”组合键。S U M ( B 2 : B 5*C 2 : C 5 ) 图3使用公式组计算多件不同单价项目的总价其他高级公式的应用技巧表2 公式应用中的快捷键快捷键效果C t r lA 键入函数名后打开“粘贴函数”对话框，同时：选中整个工作表C t r lS h i f tA 键入函数名后在公式中输入函数参数名F 2 编辑活动单元格F 3 从规定的名字列表中选择名字并将之放置在公式中S h i f tF 3 粘贴函数到公式中F 9 计算打开的E x c e l工作簿中的所有工作表S h i f tF 9 只计算活动工作表A l t 在活动单元格内输入自动求和公式C t r l； 在活动单元格内输入当前日期C t r lS h i f t： 在活动单元格内输入当前时间C t r lS h i f t +” 将上面单元格内的数值复制到活动单元格或公式上C t r l 键盘左上角的引号键） 在工作表的所有单元格内显示数值和显示公式之间切换C t r l （键盘中间字母行右侧的引号键） 将上面单元格内的公式复制到活动单元格或公式上C t r l” 将上面单元格内的数值复制到活动单元格或公式上1. 如何才能快速编辑已经放置在工作表内的函数？单击包含函数的单元格，然后单击“标准”工具栏上的“粘贴函数”工具。弹出“粘贴函数”对话框，显示公式的各组成部分，同时还有关于该函数的描述性介绍。按需要可以进行任意更改，完成更改后单击“确定”按钮。2. 如何能获得有关E x c e l函数库更详细的介绍资料？对于所有E x c e l内置函数的详尽分析，最好还是求助于E x c e l软件本身。选择“帮助”菜单，弹出“ O ff i c e助手”后将函数名输入到编辑框内。输入要查找的函数名。E x c e l将提供有关该函数的定义以及与特定示例的超级链接和有关该函数的提示信息。创建数据透视表数据透视表是E x c e l的简明报表，用户可对它进行轻松的交互式操作，从而可以改变数据汇总的类型。数据透视表可生成交叉报表或控制变值报表（这些术语与数据库管理系统通用）。1. 什么是交叉报表？交叉报表的一个示例就是地图上的里程表。如果您在边栏和顶栏中各指定一个城市，那么，两个城市的交叉单元格中就是这两个城市之间的距离。但数据透视表具有更强的功能，它可以对交叉单元格求和、求平均值或进行其他计算。用更专业的语言来说，数据透视表是一个表格，其元素同时与X轴及Y轴对应。表格中的交叉单元格代表y处的x值。例如：您可以创建一个按月份显示某校区内每所学校学生出勤率的表格。所有学校的名称放在第一列，当年的1 2个月份放在自左向右的1 2列。在学校与月份的交叉单元格中，可以看到该校学生在某月份内的平均出勤率。如果7月份的出勤率比其他学校放假期间的出勤率要高，那也没什么奇怪的。但是，这样一张图表却可以显示出有些学校能在某学年的某些特定月份将更多的学生留在学校，从而保持更高的出勤率。而这类信息可以帮助学校管理机构考虑这些保持较高出勤率的学校是否拥有值得其他学校效仿的有用经验。如果您要人工创建这张表格并输入每个单元格对应的计算公式，那将是一个极其冗长而乏味的过程。但如果这项工作是以一张包含了学校名称、日期以及该日期内学生出勤率的表格为基础，那么，数据透视表的相关命令将帮您在不到1分钟的时间内创建该表格。2. 什么是控制变值报表？用数据透视表也可以创建控制变值报表。所谓控制变值报表是指可以多层次生成分类汇总的报表，这些分类汇总称为变值。控制变值报表的一个经典示例就是一张不同顾客类型、不同地区内产品销售量的报表。在这样的报表中，您可能希望看到一张以地区为主变值数据区的清单，然后列出该地区内所有类型的顾客。您将看到每种顾客对应的某项产品销售总额。分类汇总就是每种顾客对应的某项产品销售额和每个地区对应的某项产品销售额。数据透视表还可以求某种产品的销售总额。此时行与列显示的都是总计与分类汇总。图1显示了一份控制变值类型的数据透视表的一部分。使用控制变值报表的主要优点之一是可以看到某地区内某种产品的销售总额，而且，只需轻轻单击几下，就可以将该表格变为按地区显示产品销售额的报表，如图2所示。图1在数据透视表中生成多层次的分类汇总图-2将数据透视表改为显示各地区内某产品销售额数据透视表作为生成图表的基础也是十分有用的。当您将一张图表链接到数据透视表（这是创建数据透视表时的初始选项）后，该图表将随着数据透视表中数据的变动而自动改变。您也可以通过将一张数据透视表链接到另一张数据透视表，来快速地建立简明数据表。3. 如何创建数据透视表？用“数据透视表和数据透视图”向导创建数据透视表。该向导在安装E x c e l时已同时安装。1) 首先应确定要用到的表格或数据区域。该表格可以在E x c e l工作表中，或位于外部数据库中。请参看下面第3步的有关选项。2) 如果该表格在E x c e l工作表中，选定表格中的任意单元格。要注意的是，为了使所有数据信息都包括在数据透视表中，整张表格必须位于连续的单元格区域中。3) 选择“数据|数据透视表和图表报告”菜单项。将看到步骤之1的对话框，它将询问用作数据源的类型。该数据源可以是Microsoft Excel数据清单或数据库、外部数据源，多重合并计算数据区域，或另一份数据透视表或数据透视图。选定相应选项后单击“下一步”按钮。4) 选择要创建的是单独的数据透视表还是带数据透视图的数据透视表。5) 第2步的对话框将询问数据源的位置。如果已经在数据源内选定了一个单元格，那么整张数据表应该被选取框包围。整个数据范围也将在数据源字段中显示出来。确认选取的数据范围无误后，单击“下一步”按钮。如果准备用外部数据库作数据源，单击“浏览”按钮并找到包含数据的对应文件名。选定文件名后单击“确定”按钮，然后单击“下一步”按钮，进行数据透视表向导的下一步操作。6) 单击“版式”按钮，设计数据透视报表，如图3所示。（如果现在不单击“版式”按钮，可在向导对话框关闭后，直接在工作表上设计数据透视表）。所有字段将以按钮形式在对话框右侧显示出来。在报表表单上，共有“页”“行”“列”“数据”等4个区域。可将任一字段按钮拖至上述任一区域。然后单击“确定”。7) “数据透视表和数据透视图”向导的最后一个对话框会询问将数据透视表放置在什么地方。可以在已有工作表上选择一个新的区域或创建一个新的工作表。最安全的做法是将数据透视表放置在新的工作表中。单击“完成”按钮，数据透视表结构被确定。图3通过拖动字段到想要的位置来设计报表版式4. 可以将字段放置在数据透视表的什么位置？可以把字段从数据表中拖至数据透视表结构画面的四个区域之一。如果把字段拖至“页”区，数据透视表将以该区域内的数据为基础创建。例如，如果您把角域拖至页面区域，那么在每个角域中只能看到一个报表页面。表格不会立即分割为多个角域。要么每次看到某个角域的汇总，要么看到所有角域的总计。注意如果在数据透视表版式中选定页区域，Excel将按页面分割打印数据透视表。但它不会为数据透视表的每个部分分别创建工作簿。如果将某字段拖至行区域，就指定了报表的每一行要汇总的数据。例如：将“地区”字段拖至“行”区域，那么报表的每一行将显示一个不同的区域。如果有7个地区，最终报表将显示7行。如您所预想的，“列”区域用来设定报表中自上向下的列标。如果将“产品”字段拖至“列”区域，那么报表从左至右各列将显示每种产品的总计。如果已经将行区域定义为地区，那么您将在报表的每一列中看到每种产品的销售情况，同时也看到每个地区的销售情况。如果要对某字段进行数学处理（或计数），可将该字段拖至“数据”区域。如果将某字段拖至“数据”区域，系统默认给出该字段的汇总。如果将一个非数字字段拖至该区域，系统默认给出其个数。例如：如果将“单价”字段拖至“数据”区域，那么您将看到每种产品和每个地区的销售总和。位于“东北地区”栏和“小件”栏交叉单元格将显示东北地区小件的销售情况。您可将经销商的姓名放置在数据区域，这样可得到每种产品的订单总数。由于每个记录只包含有一个姓名字段而且是非数字字符，系统将默认统计字段个数，从而给出订单总数。5. 我并不需要数据总计或字段数目功能。那么我可以利用数据透视表中的其他功能吗？将某字段拖至数据透视表布局对话框的数据区域，对于数字字段，系统默认给出汇总；对非数字字段，系统默认给出字段数目。把区域按钮放置到位后，可以双击该区域按钮，选择不同的计算功能。“数据透视表字段”对话框会打开并提供许多其他功能供选择，包括“平均值”、“最小值”、“最大值”、“标准偏差”等。可将某字段多次拖至数据区域,这样既可以看到销售总额，也可以看到销售额的平均值。由于计算类型不同，数据透视表将它们作为两种不同的字段对待。如果将已有字段拖至“数据”区域，那么您只能使用数据透视表字段对话框中提供的函数功能。Excel 97首次提供的一个新的功能，使用户可以同时定义被计算的字段和被计算的项目。该功能可以使用户在数据透视表中定义新的字段用于公式计算，或定义新的数据类别（如“产品”表格中没有数据的新项目）。这样，用户可以根据已有数据的计算结果对新数据进行预测。我在数据透视表向导中将一个字段拖至“页”区域，得到一张包括整张表格的总计在内的总报表。但看起来数据透视表只给我的工作簿添加了一张页面，我怎样才能找到其他页面呢？“页”在数据透视表向导中几乎是个误用的术语，因为选择该选项并不会给用户工作簿增加页面。在工作表的数据透视表顶部的“页”区域可以看到，该处有个下拉式列表，其中很可能保留着该字段内所有数值的当前值。单击该下拉式列表，它将弹出所有“页”字段的数值。选定某一数值，将立即计算数据透视表的该值所对应的汇总，使用户得以滤除其他信息，只显示当前页的数值。该功能与数据表格中用于筛选信息的“自动筛选”功能相类似。它只显示符合筛选条件的数据，其他数据仍然保存完好，选择“数据筛选全部显示”菜单项，即可显示所有数据。如果要将数据透视报表的每一页变为工作表中的单一页面，怎样才能强行将数据透视表页变为单一的工作表页面呢？用户可能需要在工作表的一页中看到数据透视报表的实际页面。假设您要打印数据透视表，您可能不愿手动选择每一页然后打印它。要往工作簿中添加工作表页面，只需双击工作表顶部的“页”区域。如果数据透视表包含过多“页面”字段的数据项，就要小心操作。因为E x c e l将整张数据透视表保留在内存中，如果您的计算机没有足够的内存，就可能会出问题。我的数据库软件包提供给了一个控制变值报表，其中列出了地区，对每个地区还列出了经销商名称。对每个经销商，还给出了每种产品的销售额汇总。我可以创建一个能提供同样数据浏览功能的数据透视表吗？在数据透视表向导的版式对话框中，可将多个字段拖至四个区域中任一区域。如果用户要得到传统版式的控制变值报表，可将多个字段拖至“行”区域。例如，仍以销售报表作例子，如果将“地区”、“经销商”和“产品”拖至“行”区域，将“单价”拖至“数据”区域，就可以得到如图4所示的数据透视表。该表按“地区”、“经销商”和“产品”分层次列表。图4控制变值数据透视表层次示例6. 控制数据透视表的显示方式如果我改变主意想让数据透视表改变次序，该怎么做？数据透视表最诱人的功能之一，就是可以很方便的改变其版式。方法有两种，一种是重新打开数据透视表向导，另一种是将字段直接拖至数据透视表中。数据透视表单元格与一般的单元格不同。由于它们与隐藏的数据之间存在关联，所以单元格不能直接从键盘输入新值或进行替换。但是，控制数据版式的标题字段却可以轻松修改。如果重新进入数据透视表向导，用户可以很方便地将字段拖至目标区域。可在数据透视表内单击右键，然后从快捷菜单中选择“向导”重新打开向导。如果您更喜欢在包含数据透视表的工作表上处理数据，可以将鼠标指向目标字段并将它拖到目标区域。一旦决定这样做，也就是说，您不愿按地区、经销商和产品来浏览销售额，而希望看到各地区内某种产品由各经销商完成的销售额，您可以将光标放置在“姓名”标题栏上，并将它拖至左侧“地区”和“产品”之间。E x c e l将立即改变数据透视表的版式。我怎样才能改变数据透视表中数据的显示方式，使数据字段左右相邻，而不是上下相邻？数据透视表中，如果一个区域内有多个字段，默认的版式是自上至下逐个放置。例如：如果将“数量”字段和“单价”字段都拖至“数据”区域，报表默认在总销售额上方显示总销售量。但大多数用户并不喜欢以这种方式显示数据。如果希望看到上述两项并排布置，而不是上下布置，可将数据字段拖至右侧（见图5）。报表将自动重新布局，宽度加大而长度变短（列数变多而行数变少）。图5将数据字段拖至右侧使报表变宽我双击一个单元格并浏览了生成表中数据的详细信息。在浏览完毕后，我怎样删除它们？没有用于撤消上述操作的专门命令（比如“编辑撤消”），但需注意详细信息将显示在一张新的工作表上。用户可以通过选择“编辑删除工作表”菜单项来删除包含该详细信息的工作表。在弹出的窗口上按下“确定”按钮，该工作表将永久删除。如果想再次浏览详细信息，可再次双击数据透视表单元格，将会出现一张新的包含详细信息的表格。我用数据透视表处理完数据后，可否删除整张数据透视表？您可以删掉包含数据透视表的工作表，也可以关闭创建数据透视表所用的文件，并选择不保存更改内容。您也可以删除整张数据透视表，这时需要首先全选数据透视表（包括标题栏和“页”标签）全选数据透视表的一个快捷方法是单击左上角单元格。注意，您不能删除数据透视表中的某一部分。要么不删，要么全删。当我单击数据透视表中的行号或列标单元格时，有一组单元格被选中，这是怎么回事？您遇到的是Excel 数据透视表的“结构化选定”功能。这是同时选中一组同类单元格的快捷方法。它为突出显示一组同类单元格或选中特定单元格进行格式编排提供了方便。您可以按下述步骤关闭或打开结构化选取功能。1) 将鼠标指向数据透视表的特定类型数据，同时单击鼠标右键打开快捷菜单。2) 从快捷菜单中选择“选定”项。3) 选择“启用选定内容”项。您也可以指定结构化选定功能是同时选取标志和数据，或只选取标志和只选取数据。如有必要，也可以指定该功能选取整张表格（这种情形较少使用）。如果“启用选定内容”打开，用户可以将鼠标指向数据标志上部或下部边缘，等待光标变成小的后向箭头。出现后向箭头后，单击“数据标志”。则所有该类数据全部选中。例如，单击“地区”字段，则选中所有地区名称。单击含有特定地区名称的单元格（比如N E），则所有包含“N E”字符的数据行（或数据列）全部被选中。如果已经定义了按产品及地区显示销售额的数据透视表，单击带N E标志的单元格将立即选中东北地区所有产品的销售额。怎样给数据透视表列标重命名？有时数据透视表的列标显得不太合适。对一张要交给别人审阅的报表来说，“单价总计”或“名称数目”就是不太理想的叫法。在数据透视报表中您可以很轻松地改变标题名称。共有3种改变标题的方法。 改变标题最快捷的方法是双击标题名称。此时将弹出“数据透视表字段”对话框，显示字段名，同时显示如何对字段进行计算的相关信息。您只需在该窗口的“字段名”栏键入新的名称即可，然后单击确定按钮。新的名称将取代原来的名称。 数据透视表向导打开时（如果数据透视表向导已关闭，可在数据透视表中单击鼠标右键，选择“向导”重新打开向导），用户可进入“版式”选项，双击字段名，然后在数据透视表字段对话框的“字段名”栏输入新名称。 用户可在任何时候单击右键并从快捷菜单中选取“字段设置”项，打开“数据透视表字段”对话框。如果您在最初定义字段名时（在创建数据透视表之前）就已改好字段名，那将省去许多格式编排的麻烦。E x c e l将自动编排数据透视表的格式，使列宽与相应的列标志匹配。由于“总计”和“数目”之类的名称过长，数据透视表将分配给它们超过实际需要的空间，而这将使您更难于同时浏览整张数据透视表。如果缩短字段名称， E x c e l自动减短字段但该功能仅在还未创建数据透视表时起作用。字段名可以任意选取。但也要遵守一条限制原则。已经用过的字段名不能重复使用。如果工作表中已有字段命名“销售额”，您将它拖至数据透视报表布局图的数据区域，而该字段的默认名称可能是“销售额汇总”。此时您不能将其命名为“销售额”，尽管这个名称是最合理的，这是因为已经有“销售额”的字段名。您可在“销售额”前面或后面加上空格，但这样做难于记住，或者您可以将其命名为“销售额￥”，这可能是最好的做法。在改变了所有的数据标签，将其名称缩短后怎样才能让表格随着新名称变窄呢？可选取“格式|自动套用格式”菜单项，使表格随字段名缩短而自动调整尺寸。数据透视表默认使用“经典1”格式，所以重新选中它， E x c e l将自动计算表格列宽。如果要改变某一组特定单元格的格式，可选定这些单元，然后单击鼠标右键，从快捷菜单中选取“设置单元格格式”。图6显示了“单元格格式”对话框。有时候最简单的方法最容易被遗忘。如果您的数据透视表列数不是非常大，改变其列宽的最简单的方法可能是这样的，将鼠标在表格的列标行（ A，B，C等等）横向拖动，当光标变为将相邻两列分开的短竖条时，双击鼠标。E x c e l将显示每一列的最佳宽度。图6从快捷菜单选取“设置单元格格式”，显示“单元格格式”对话框如果我改变数据透视表某个单元格中的文本，而该文本在数据透视表的其他地方重复出现，要改变它们有什么简便的方法吗？用户可以改变数据透视表中任何单元格的内容。单击单元格可编辑单元格内容，就像编辑工作表中的单元格一样。如果选定了标签单元格，要交互的改变其名称，并不需要将所有同类单元格全部选中。改变其中一个标签将改变所有单元格。7. 应用数据透视表如果我更改数据表格中的数据，与之相关联的数据透视表会自动更新吗？数据透视表确实与相应数据相关联，但没有自动更新的功能。如果改变相关联数据，必须选取“数据更新数据”菜单项来更新数据透视表。对外部数据源或内部数据源也是如此。按下数据透视表工具栏中的“更新数据”按钮可快速更新数据，该按钮上有类似感叹号的标记。当您创建了一张新的数据透视表之后，“更新数据”按钮可用。如果数据透视表工具栏不可见，在工作表的工具栏区域单击鼠标右键，从弹出的快捷菜单中选取“数据透视表”项。我手头有一张包含每笔销售生意相关数据的表格。有什么办法能按季度或其他时间间隔算出销售总额？您可将数据按时间段分组。例