第三章-Excel应用 数据分析
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
步骤: 1.单击放合并计算结果的区域的左上角单元格 2.完成“合并计算”对话框中信息的填充。
3.5 数据的分类汇总与分级显示
当我们使用分类汇总后，往往希望将汇总的2级结果复 制到一个新的数据清单中。但当 我们直接进行复制粘 贴时，无法只得到汇总的2级结果，而是所有内容。此 时我们就需要使用“Alt+;”组合键选取当前屏幕中已 选定的内容（若没选定区域则默认整个工作表），然 后在进行复制和粘贴。如图2-21 复制分类汇总2级结果 。请大家仔细比较图3-20与3-21的不同之处，可知图321只是2级汇总结果的复制粘贴，不能展开3级汇总结 果。 思考：以上用合并计算可以达到同样效果吗？合并
3.3 数据的筛选
3.3.1 一般筛选 要使用Excel的一般筛选功能，在确保当前单元格处于数据
列表中的任意单元格情况下，选择“数据”选项卡，单击【筛选 】按钮即可。
一般筛选描叙的条件比较简单，一般只选择一列构成条件； 当然也可由多列构成条件，筛选结果是这些多列条件的“与”关 系构成的条件筛选的结果。但还存在如下问题： 1.在一列上最多使用2个条件； 2.多列条件之间只能是“与”的关系，不能实现“或”的关系； 3.无法实现使用涉及计算公式的筛选条件，如筛选出高于平均值 的数据行； 4.筛选结果只显示符合筛选条件的行，隐藏了不符合条件的行， 不便于对照原始数据与结果； 5.这种筛选结果显示了所有的列，而有时希望结果只是显示自定 列。
3.3 数据的筛选wk.baidu.com
3.3.2 高级筛选 使用高级筛选，可以构造更灵活的数据筛选方式,解决一般 筛选中存在的问题。使用高级筛选前应先在数据清单区域外 （既与数据清单必须以空行空列隔开）设置一个条件区域， 用来指定筛选条件。见“高级筛选案例.XLSX”
1.关于设置高级筛选条件的说明：
高级筛选的条件区域应该至少有两行，第一行是条件标签（ 如果条件列不是计算公式列，条件标签就是数据清单中的字 段名），下面的行则放置筛选条件。在条件区域的筛选条件 的设置中，同一行上的条件认为是“与”关系条件，而不同 行上的条件认为是“或”关系条件。
在“设计”选项卡的快捷按钮区“工具”选项组，有【通过数 据透视表汇总】、【删除重复项】按钮可以进行数据分析，有 【转换为区域】工具，单击【转换为区域】按钮，可以将“表 格”转换为数据清单区域。
数据管理与分析
第三章 数据分析
3.1获取外部数据
方法1：若源文件已打开，且有规则的分隔符 ，则可直接选定所需数据复制，粘贴到EXCEL 工作表某单元格开始的区域。
方法2：若源文件没打开，或者分隔的规则待 确定，则通过向导按步骤进行即可。
3.2 数据的排序
3.2.1 基本概念 数据列表（也称数据清单、数据表，一般指有表头行字 段、无空行、无空列的一个数据区域） 当用户按行进行排序时，数据列表中的列将被重新排列 ，但行次序保持不变，如果按列进行排序，行将被重新 排列而列次序保持不变。按列排序是默认值，根据某一 列的各单元格的值的大小使所在行整行移动。 3.2.2 让一些行不参与排序 如果不想让一些行参与排序，可以先将这些行隐藏起来
步骤4：单击“确定”按钮后出现数据透视表字段列表框及 数据透视表位置框中的提示。
步骤5：完成数据透视表字段列表对话框的操作。
3.7 利用表格分析数据
3.7.1 创建表格
将数据清单转换为表格的方法和步骤如下：（示例文件见： “获取外部数据--表格.xlsx” ）
步骤1：将光标放在数据清单的任意单元格，选择“插入”选 项卡，单击【表格】按钮，出现创建表对话框。
2.若条件列是计算公式列需遵循以下规则：
3.3 数据的筛选
条件列是计算公式列需遵循的规则： 公式的计算结果必须为TURE或FALSE; 不要将数据清单中的字段名用作条件标签。条件标
签可以为空，也可以为其他说明文字； 用于创建条件的公式中被筛选的数据清单（列表区
域）中的第一条记录的某相应单元格行号必须以相 对引用的形式出现，这使得在执行筛选时可以对列 表区域每行中的数据进行公式计算； 公式中的所有其他引用必须是绝对引用。 如： =COUNTIF(J$3:J$15,B2)=0
高级筛选应用案例
3.3.3 从两列数据中筛选相同（不相同）数据----筛选股票
用高级筛选比对两列数据(示例文件见:工资表.XLSX)所示，两个数据清 单：2016年中报社保机构持股一览表（左边数据清单）和2016年中报 QFII机构持股一览表（右边数据清单,注意行号大小，用了冻结窗格） 。要求分别找出: 社保持有而QFII没持有的股票的条件: =COUNTIF($L$3:$L$198,$B3)=0 社保没持有而QFII持有的股票的条件: =COUNTIF($B$3:$B$404,$L3)=0 社保持有且QFII也持有的股票的条件: =COUNTIF($L$3:$L$198,$B3)>0
步骤2：“表数据来源”框中填入要转成表格的数据清单区 域，选择复选框“表包含标题”。
步骤3：单击【确定】按钮，表格创建完成。 没有数据清单时创建表格的操作方法一样，创建完后在表格
里填入数据即可。创建了的表格在“名称管理器”中会自动 增加一个名为“表n”的名称，代表整个表格区域，n为序列 数字1,2,3，…。
将高级筛选结果放到其他工作表
一般情况下，我们将高级筛选结果放在源数 据清单的同一工作表的原数据区域或者某单 元格开始的另一区域中，但有时需要将筛选 结果直接筛选到另一工作表的某位置
这里有着关键的第一步：单击另一工作表（ 复制到的目的地）的某位置，打开高级筛选 对话框；其余操作步骤基本同上面介绍的， 完成对话框的填写即可。
3.7.2 用表格分析数据
创建好的表格在默认情况下每列会启用自动筛选功能，用户可 以快速筛选或排序数据。
用户还可以为表格添加汇总行，方法是点开表格工具的“设计 ”选项卡，在快捷按钮区的“表格样式选项”组勾选复选框“ 汇总行”即可，如图3-28 给表格添加了汇总行。单击汇总行的 任意单元格时，我们都可以从其下拉列表中选择平均值、计数 、最大值、最小值、求和、标准偏差、方差等等函数来进行数 据分析。
3.4 合并计算
Excel 2010中若要汇总和报告多个单独工作表的结果 ，可以将每个单独工作表中的数据合并计算到一个 主工作表中。这些工作表可以与主工作表在同一个 工作簿中，也可以位于其他工作簿中。对数据进行 合并计算就是组合数据，以便能够更容易地对数据 进行定期或不定期的更新和汇总，对于单表来说其 功能同按条件汇总函数SUMIF、AVERAGEIF等，但使 用合并计算，可以实现对多个表的汇总，并且不需 要写函数，更为快速。
计算与分类汇总的处理对象有何异同？
3.6 数据透视表
操作方法如下：
步骤1：选定制作数据透视表的数据源。将光标定位于数据 源的任意单元格。
步骤2：打开数据透视表对话框。进入“插入”选项卡，单 击【数据透视表】，弹出创建数据透视表对话框。
步骤3：完成对话框中的选择任务。“请选择要分析的数据 ”项目：默认“选择一个表或区域”，“表/区域”框的内 容默认为当前表的数据清单区域或表格或定义的名称；“选 择放 置数据透视表的位置”项目：默认为“新工作表”， 这里我们选“现有工作表”，填写好“位置”框的内容---某单元格名。各项目一般用默认值，也可根据情况选其他。
3.2 数据的排序
3.2.3 利用排序方法删除空行而不改变原来顺序 利用排序方法删除数据区域所有空行：选定数据区
域，然后按某列的值降序排序，空行就会移动到区 域最后面，问题是非空行的数据顺序不是原来的顺 序了。若要不改变原来的顺序，操作步骤如下： 1.在数据区域右侧增加一个辅助列，其值为序列数字 1、2、3、…； 2.单击左侧数据区域任意单元格，进行降序排序，空 行后移； 3.删除后移的空行； 4.按辅助列的值升序排序，还原原来的顺序。
3.5 数据的分类汇总与分级显示
当我们使用分类汇总后，往往希望将汇总的2级结果复 制到一个新的数据清单中。但当 我们直接进行复制粘 贴时，无法只得到汇总的2级结果，而是所有内容。此 时我们就需要使用“Alt+;”组合键选取当前屏幕中已 选定的内容（若没选定区域则默认整个工作表），然 后在进行复制和粘贴。如图2-21 复制分类汇总2级结果 。请大家仔细比较图3-20与3-21的不同之处，可知图321只是2级汇总结果的复制粘贴，不能展开3级汇总结 果。 思考：以上用合并计算可以达到同样效果吗？合并
3.3 数据的筛选
3.3.1 一般筛选 要使用Excel的一般筛选功能，在确保当前单元格处于数据
列表中的任意单元格情况下，选择“数据”选项卡，单击【筛选 】按钮即可。
一般筛选描叙的条件比较简单，一般只选择一列构成条件； 当然也可由多列构成条件，筛选结果是这些多列条件的“与”关 系构成的条件筛选的结果。但还存在如下问题： 1.在一列上最多使用2个条件； 2.多列条件之间只能是“与”的关系，不能实现“或”的关系； 3.无法实现使用涉及计算公式的筛选条件，如筛选出高于平均值 的数据行； 4.筛选结果只显示符合筛选条件的行，隐藏了不符合条件的行， 不便于对照原始数据与结果； 5.这种筛选结果显示了所有的列，而有时希望结果只是显示自定 列。
3.3 数据的筛选wk.baidu.com
3.3.2 高级筛选 使用高级筛选，可以构造更灵活的数据筛选方式,解决一般 筛选中存在的问题。使用高级筛选前应先在数据清单区域外 （既与数据清单必须以空行空列隔开）设置一个条件区域， 用来指定筛选条件。见“高级筛选案例.XLSX”
1.关于设置高级筛选条件的说明：
高级筛选的条件区域应该至少有两行，第一行是条件标签（ 如果条件列不是计算公式列，条件标签就是数据清单中的字 段名），下面的行则放置筛选条件。在条件区域的筛选条件 的设置中，同一行上的条件认为是“与”关系条件，而不同 行上的条件认为是“或”关系条件。
在“设计”选项卡的快捷按钮区“工具”选项组，有【通过数 据透视表汇总】、【删除重复项】按钮可以进行数据分析，有 【转换为区域】工具，单击【转换为区域】按钮，可以将“表 格”转换为数据清单区域。
数据管理与分析
第三章 数据分析
3.1获取外部数据
方法1：若源文件已打开，且有规则的分隔符 ，则可直接选定所需数据复制，粘贴到EXCEL 工作表某单元格开始的区域。
方法2：若源文件没打开，或者分隔的规则待 确定，则通过向导按步骤进行即可。
3.2 数据的排序
3.2.1 基本概念 数据列表（也称数据清单、数据表，一般指有表头行字 段、无空行、无空列的一个数据区域） 当用户按行进行排序时，数据列表中的列将被重新排列 ，但行次序保持不变，如果按列进行排序，行将被重新 排列而列次序保持不变。按列排序是默认值，根据某一 列的各单元格的值的大小使所在行整行移动。 3.2.2 让一些行不参与排序 如果不想让一些行参与排序，可以先将这些行隐藏起来
步骤4：单击“确定”按钮后出现数据透视表字段列表框及 数据透视表位置框中的提示。
步骤5：完成数据透视表字段列表对话框的操作。
3.7 利用表格分析数据
3.7.1 创建表格
将数据清单转换为表格的方法和步骤如下：（示例文件见： “获取外部数据--表格.xlsx” ）
步骤1：将光标放在数据清单的任意单元格，选择“插入”选 项卡，单击【表格】按钮，出现创建表对话框。
2.若条件列是计算公式列需遵循以下规则：
3.3 数据的筛选
条件列是计算公式列需遵循的规则： 公式的计算结果必须为TURE或FALSE; 不要将数据清单中的字段名用作条件标签。条件标
签可以为空，也可以为其他说明文字； 用于创建条件的公式中被筛选的数据清单（列表区
域）中的第一条记录的某相应单元格行号必须以相 对引用的形式出现，这使得在执行筛选时可以对列 表区域每行中的数据进行公式计算； 公式中的所有其他引用必须是绝对引用。 如： =COUNTIF(J$3:J$15,B2)=0
高级筛选应用案例
3.3.3 从两列数据中筛选相同（不相同）数据----筛选股票
用高级筛选比对两列数据(示例文件见:工资表.XLSX)所示，两个数据清 单：2016年中报社保机构持股一览表（左边数据清单）和2016年中报 QFII机构持股一览表（右边数据清单,注意行号大小，用了冻结窗格） 。要求分别找出: 社保持有而QFII没持有的股票的条件: =COUNTIF($L$3:$L$198,$B3)=0 社保没持有而QFII持有的股票的条件: =COUNTIF($B$3:$B$404,$L3)=0 社保持有且QFII也持有的股票的条件: =COUNTIF($L$3:$L$198,$B3)>0
步骤2：“表数据来源”框中填入要转成表格的数据清单区 域，选择复选框“表包含标题”。
步骤3：单击【确定】按钮，表格创建完成。 没有数据清单时创建表格的操作方法一样，创建完后在表格
里填入数据即可。创建了的表格在“名称管理器”中会自动 增加一个名为“表n”的名称，代表整个表格区域，n为序列 数字1,2,3，…。
将高级筛选结果放到其他工作表
一般情况下，我们将高级筛选结果放在源数 据清单的同一工作表的原数据区域或者某单 元格开始的另一区域中，但有时需要将筛选 结果直接筛选到另一工作表的某位置
这里有着关键的第一步：单击另一工作表（ 复制到的目的地）的某位置，打开高级筛选 对话框；其余操作步骤基本同上面介绍的， 完成对话框的填写即可。
3.7.2 用表格分析数据
创建好的表格在默认情况下每列会启用自动筛选功能，用户可 以快速筛选或排序数据。
用户还可以为表格添加汇总行，方法是点开表格工具的“设计 ”选项卡，在快捷按钮区的“表格样式选项”组勾选复选框“ 汇总行”即可，如图3-28 给表格添加了汇总行。单击汇总行的 任意单元格时，我们都可以从其下拉列表中选择平均值、计数 、最大值、最小值、求和、标准偏差、方差等等函数来进行数 据分析。
3.4 合并计算
Excel 2010中若要汇总和报告多个单独工作表的结果 ，可以将每个单独工作表中的数据合并计算到一个 主工作表中。这些工作表可以与主工作表在同一个 工作簿中，也可以位于其他工作簿中。对数据进行 合并计算就是组合数据，以便能够更容易地对数据 进行定期或不定期的更新和汇总，对于单表来说其 功能同按条件汇总函数SUMIF、AVERAGEIF等，但使 用合并计算，可以实现对多个表的汇总，并且不需 要写函数，更为快速。
计算与分类汇总的处理对象有何异同？
3.6 数据透视表
操作方法如下：
步骤1：选定制作数据透视表的数据源。将光标定位于数据 源的任意单元格。
步骤2：打开数据透视表对话框。进入“插入”选项卡，单 击【数据透视表】，弹出创建数据透视表对话框。
步骤3：完成对话框中的选择任务。“请选择要分析的数据 ”项目：默认“选择一个表或区域”，“表/区域”框的内 容默认为当前表的数据清单区域或表格或定义的名称；“选 择放 置数据透视表的位置”项目：默认为“新工作表”， 这里我们选“现有工作表”，填写好“位置”框的内容---某单元格名。各项目一般用默认值，也可根据情况选其他。
3.2 数据的排序
3.2.3 利用排序方法删除空行而不改变原来顺序 利用排序方法删除数据区域所有空行：选定数据区
域，然后按某列的值降序排序，空行就会移动到区 域最后面，问题是非空行的数据顺序不是原来的顺 序了。若要不改变原来的顺序，操作步骤如下： 1.在数据区域右侧增加一个辅助列，其值为序列数字 1、2、3、…； 2.单击左侧数据区域任意单元格，进行降序排序，空 行后移； 3.删除后移的空行； 4.按辅助列的值升序排序，还原原来的顺序。