电子表格Excel44数据管理与分析 办公自动EXCEL电子表格课件

4.4数据管理与分析4.4.1使用公式或函数进行数据计算, 4.4.2数据的排序 4.4.3数据的筛选 4.4.4数据的分类汇总 4.4.5数据的合并计算 4.4.6数据透视表Excel的主要功能不在于能创建表格、显示和打印表格，而在于 对工作表中数据的管理、分析、查询与统计，如可对工作表进行 数据运算、数据排序、统计筛选、分类汇总、合并计算、建立数 据透视表等。4.4.1使用公式或函数进行数据计算利用公式和函数，既可完成像加、减、乘、除这样的简单 运算，也可以完成像统计、比较等较复杂的运算公式就是一个等式，包括运算的数据、函数、单元格地址 、运算符等要素。EXCEL规定，公式必须是用等号（=） 开始，其计算结果自动显示在对应的单元格中。函数则是已经建立好的公式， EXCEL将一些常用的公式（ 如求和、求平均、计数统计等）内置EXCEL系统内，并以 函数的形式提供给用户，当遇到使用这些公式时，可以直 接给出对应的函数名，再添加上相应的自变量即可使用， 无须再输入公式的各种要素。1、单元格地址及表示 1）单个单元格地址的表示 列号加行号表示单个单元格的地址。如：A6 2）连续单元格地址的表示 用区域左上角的单元格地址加冒号“：”，再加上区 域右下角的单元格地址来表示连续单元格区域的地址 。 如：C3：E8 3）不连续单元格的地址表示 用逗号号分隔开各个单元格地址或者说单元格区域 地址。如：A3，B2：C6，D5：F92、单元格的引用在公式或函数中，要涉及到引用单元格的数据，其 引用方法不是直接给出单元格内容，而是给出单元 格地址。 这种引用办法可以保证当单元格内容改变时，公式 或函数的计算结果也能及时自动更新，而不需要更 改公式或函数。 单元格的引用有相对引用、绝对引用和混合引用三 种方法。（另外还有引用其他工作表或工作簿中的 单元格（多维引用）1）相对引用在输入公式的过程中。除非我们特别指明，一般 是使用相对地址来引用单元格的位置。 所谓相对引用是指：当把一个含有单元格地址的 公式复制到一个新的位置的时候，公式中引用的 单元格地址会随着改变。如 ：A1，B4等，表示相对址址的引用。 例如：在A4 单元格中输入“=A1+A2+A3”公式， 现将该公式复制到B4单元格中，则B4单元格中 的公式变成了“=B1+B2+B3”，即公式中引用的单 元格地址发生了变化，公式内容也随之改变，因 而计算结果也就不同了，这就是相对引用。2）绝对引用所谓绝对地址引用是指：当把公式复制到新的位置 时，公式中的单元格地址保持不变。 也就是被引用的单元格与引用单元格的位置关系是 绝对的。在Excel中，是通过地单元格地址的“冻结” 来达到此目的的，也就是在列号和行号的前面添加 美元符号“$”。 如：$A、$B、$C等，表示绝对引用。 例如：在A4单元格中输入“=$A$1+$A$2+$A$3”公式 ，现将该公式复制到B4单元格中国，则B4单元格中 的公式仍然是“=$A$1+$A$2+$A$3”，即公式内容不 变，计算结果也不变，就是绝对引用.3）混合引用单元格地址的引用除了上述两种方法外，还有混合引用 。 混合引用 是指引用的单元地址，行用相对地址列用绝对地 址，或行用绝对地址，列用相对地址。如$A2。D$4这就是 混合引用 。 在混合引用 中。相对地址随公式所以地址的变化而变化， 绝对地址不随公式地址的改变而改变。例如：在A5单元格 中输入“=A$1+$A2+$A2+A$3+A4”公式，现将该公式复制到 B5单元格中，则B5单元格中的公式变成了 “=B$1+$A2+$A2+$A$3+B4”即公式中有$的绝对引用地址没 有改变，而相对引用 地址发生了变化，因而计算结果也就 不同了，这就是混合引用 。4）引用其他工作表中的单元格（多维引用）在当前工作表中可以引用其他工作表中的单元格内容， 其方法有两种： 方法一：直接键入 非当前工作表的单元格在引用时必须加上工作表名称。 再加上“！”，这就是二维引用。如sheet3!B2表示引用 sheet3工作表的B2单元格。如果引用的工作表不是当前的 工作簿中的表格，引用时还须加上工作簿的路径、名称 等信息。这就是三维引用，如：“D：My documentsbook2.xlssheet2!$a$1+a1”表示引用book2工作 簿中的sheet2表的A1单元格和当前工作表的A1单元 格， 并且求该两个单元格内容之和。 方法二：用鼠标选择要引用的单元格 直接打开被引用的工作簿，选中要引用的工作表，单击 要引用的单元格或单元格区域即可。建立公式1） 键入公式 用键盘创建公式的步骤如下 （1）选定要输入公式的单元格 （2）先键入等号“=”。然后输入计算公式 （3）按回车键确认，计算结果自动显示在选定的单元格中 2）使用公式选项板建立公式 使用公式选项板来输入公式可以预览计算结果，还可以创建包 含函数的复杂公式。公式选项板如下： 要显示公式选项板，可单击编辑栏中的“=”，要在公式中输入 函数，可在选项板左边的函数下拉列表中选取（默认为求和函 数SUM），公式的计算结果显示在选项板下部，输入完毕按“ 确定”按钮。 要使公式选项板输入公式，也需要先选中存放计算结果的单元 格，然后单击编辑栏中的“=”，接着输入所需要的计算公式， 最后按“确定”按钮。公式的修改、移动与复制1）修改已有的公式单击包含要修改公式的单元格，则该单元格中的公式会显示在编 辑栏中，在编辑栏中进行公式的修改，最后按回车键结束 2）公式的移动与复制公式的移动与复制，其方法类似于单元格内容的移动与复制使用函数1）函数的分类及组成 Excel2000提供了200多个函数，按 功能可分为如下几大类，即数学和 三角函数、文本函数、逻辑函数、 数据库函数、统计函数、查找和引 用函数、日期和时间函数、信息函 数和财务函数。 函数包括函数名、参数（常数、单 元格地址）及函数值（结果）三部 分。一个典型函数的示例如图所示 。2）输入函数输入函数同输入公式的操作类似，如果能记住函数的名称、参数，可直接在单元 格中键入函数及参数，否则可以使用函数向导来粘贴函数。 使用函数向导来粘贴函数的操作方法如下： （1）选定要输入函数的单元格（即存放函数值的单元格） （2）单击“插入”菜单，选“函数”命令，或者单击“常用”工具栏上的“粘贴函数”按 钮，系统弹出如图所示的“粘贴函数”对话框。 （3）在“函数分类”列表框中选择所需要的函数类型，则该函数类型所对应的所有 函数出现在“函数名”列表框中，在该列表框中选定需要的函数.在两个列表框的下 部有选定函数的文字说明，通过这些说明可以了解所选函数的作用。 (4)单击“确定”按扭，系统又弹出了一个与所选函数对应的对话框，如图所示： SUM函数的对话框，在对话框的下部，有该函数所用的参数说明和函数的计算结 果，通过参数说明可以了解参数的数量、意义及用法。 （5）在该对话框中，用户可以输入函数的参数（常数、单元格引用地址等），也 可以使用鼠标选定函数的单元格引用 参数，通过单击对话框的折叠按钮，可以将 该对话框折叠起来，只显示一行参数输入区，以便完整地显示出当前工作表，然 后使用鼠标选定单元格或单元格区域，被选定的单元格区域有一个闪烁的虚线框 ，系统自动将所选定的单元格地址添加到参数输入区中。单元格选定结束，再单 击展开按钮，则对话框又展开在屏幕上，如上图，函数参数为A2：D2，即计算A2 ，B2，C2，D2四个单元格的数值之和。4.4.2数据的排序（一）在数据的管理工作中，经常遇到对某些数据进行排序处理，以便直观地对比各个记录。 1、数据排序原则 在对工作表进行数据排序时，根据单元格不同的数据类型，Excel有其默认的排序原则。 在按升序 排序时， Excel使用如下次序（降序除了空格在最后，其它排序次序反转） 1）数值 从最小的负数到最大的正数进行排序 2）字母 按字母先后顺序排序。在按字母先后顺序对文本项进行排序时， Excel从左到右一个字符一个 字符地进行排序。如：A100。将排在A1后面A11前面。 3）文本以及包含数字的文本，按下列顺序排序：0123456789（空格）！“#￥%（）*，。/ ：；？·-|+|ABCDEFGHIJKLMNOPQRSTUVWXYZ 4)逻辑值 在逻辑值中，FALSE（假或不成立）排在TRUE（真或成立）之前。 5）错误值 所有的错误值的优先级相同 6）空格 空格始终排在最后。4.4.2数据的排序（二）2、使用工具栏按钮对单列数据进行排序 根据工作表中某一列数据进行排序，可以使用菜 单命令，但使用工具按钮是一种最快捷的方法。 步骤如下： （1）在待排序数据列中单击任一单元格。 （2）单击“常用”工具栏中的排序按钮 若要按“升序”排序，单击“升序”按钮，反之按“降 序”按钮 3、使用排序对话框对多列数据组合排序 按照单列数据排序时，往往会出现某些数据完全 相同的（即并列）的情况,这时可以选择按多列数 据进行组合排序，以进一步区分并列的数据。使 用菜单命令对多列数据进行组合排序的操作步骤 如下： （1）在需要排序的工作表中，单击任一单元格。 （2）单击“数据”菜单，选“排序”命令，出现如图 所示的“排序”对话框： （3）在“排序”对话框中，从“主关键字”下拉列 表框中选择排序的主关键（主数据列），并从对 应的排序顺序单选框中，确定该关键字是升序还 是降序。再依次从“次关键字”和“第三关键字”下 拉列表中选定排序依据的第二关键字或第三关键 字，并确定出它们4.4.3数据的筛选4.4.4数据的分类汇总（一） 分类汇总就是按类别对数据进行求和、 计数、求平均、求最大值、求最小值、 求方差等的统计运算。在Excel2000中， 使用专用的分类汇总命令，不需要人工 创建公式，Excel自动创建公式、插入分 类汇总行与总和行，并且可以对汇总结 果或明细数据分级进行显示。数据的分类汇总（二）1、创建分类汇总要创建分类汇总，可按如下步骤进行。 （1）选定需要分类汇总的数据列，并对该列数据进行排序（升序或降序均可） （2）在要分类的工作表中单击任一单元格。 （3）单击“数据”菜单，选“分类汇总”命令，弹出“分类汇总”对话框。 （4）在对话框事，从“分类字段”列表框中选择要进行分类汇总的分类字段列。如 ： （5）在“汇总方式”列表框中选择用来计算分类汇总的函数 （6）在“选定汇总项”列表框中，选定需要分类汇总的数据列 （7）选择“分类汇总”对话框中的其他选项 若想替换现存的分类汇总，选中“替换当前分类汇总”复选框。 若想在每组之前插入分页，选中“每组数据分页”复选框。 若想在数据组末显示分类汇总结果，选中“汇总结果显示在数据下方”复选框 （8）单击“确定”按钮。完成分类汇总操作。如图：2、汇总结果的显示与调整 分类汇总表具有分级显示分类汇总结果的功能。 共分三级：第一级只显示全部数据的汇总结果（总的汇总结果，只有一个；第二 级 只显示分类汇总结果和总的汇总结果；第三级显示全部原始数据、分类汇总结果 和总的汇总结果。 如果要分级查看分类汇总表中的明细数据或汇总结果，只需要单击对应的级别符 号按钮之一即可。 并通过分级下方的显示明细数据符号按钮和隐藏显示数据符号按钮，决定是否显 示明细数据。3、删除分类汇总 要清除工作表中的分类汇总，可以按照以下步骤操作： （1）在要清除分类汇总的工作表中，单击任一单元格 （2）单击“数据”菜单，选“分类汇总”命令 （3）在“分类汇总”对话框中，单击“全部删除”按钮。则现有的分类汇总即被删除 。4.4.5数据的合并计算一个公司内可能有很多的销售地区或者分公司，各个分公 司具有各自的销售