Excel数据透视表：以自己的方式查看数据

Excel数据透视表：以自己的方式查看数据（共4篇）

1.Excel数据透视表：以自己的方式查看数据 篇一

1 提出问题

大量来自不同地区的人员参加考试, 每次组织综合科目和不同的专业科目考试, 同时通过综合科目和某一专业科目才能取得该专业执业证书, 没通过相关科目的人员可能再参加后面批次的考试, 要求汇总分析每一次考试和几次考试的人员通过情况和地区通过情况, 整理出几次考试后可取得执业注册证书的人员名单, 分析每一考试科目的总体通过情况。

2 思路分析

解决该问题的难点在于整理执业证书人员名单时, 必须通过综合和专业考试科目, 而这两个科目不一定是在一次考试通过, 而且考试的专业比较多。

在分析问题时, 也应当看到, 结果并不要求对每次考试的具体成绩进行层次分析, 这样即可简化我们解题思路, 即将通过、不通过定义为1、0即可, 而不需要分析具体的考试分数 (当然也可通过其他办法来进行考试成绩的定量分析) 。

基于以上两个主要方面的分析, 我们可以得出, 利用Excel的数据透视表来解决这一问题是一个比较适当的方法。因为数据透视表是用来从Excel数据列表、关系数据库文件或OLAP多维数据集等数据源的特定字段中总比信息的分析工具。它是一种交互性报表, 可以快速分类汇总比较大量的数据, 并可以随时选择其中页、行和列中的不同元素, 以达到快速查看数据源的不同统计结果, 同时还可以随意显示和打印出用户感兴趣的区域和明细数据。数据透视表有机地综合了数据排序、筛选和分类汇总等数据分析方法的优点, 可以方便地调整分类汇总的方式, 灵活地以多种不同方式展示数据的特征。

3 实际操作

在本节中, 以分析解决问题为主线, 穿插了Excel数据处理中的常用方法。考虑到Excel 2010在数据透视表方面显著的功能升级和强化, 本文以Excel 2010版本为平台进行操作。

3.1 整理数据源

准确、规范的数据源是高效分析、正确结果的基础, 因此, 这项基础工作应当引起足够重视。根据实际问题, 将每一次考试数据放在一个Excel文件 (工作簿) 中, 其中的多个工作表分别容纳这次考试中某一考试科目的数据, 然后将几次考试的数据 (几个Excel文件) 放在同一个文件夹内。为方便操作和表达, 这里几个文件放在“D:成绩分析”文件夹中。

以第一次考试为例, 建立一个Excel文件命名为“第1次”, 并将相应的工作表命名为“综合科目”“建筑一级”“建筑二级”……如图1所示。

需要指出的是, 每张表的结构应当一致, 即第一行标题需相同, 且不能出现多余的列, 否则, 在后面的步骤中无法正常进行。

3.2单次考试成绩分析

以第一次考试成绩分析为例。在“第1次.xlsx”工作簿中新建一个工作表命名为“第1次”。

在【数据】选项卡中单击【现有连接】按钮, 弹出【现有连接】对话框, 单击【浏览更多】按钮, 打开【选取数据源】对话框, 打开D盘根目录文件“第1次.xlsx”, 弹出【选择表格】对话框, 单击【名称】中的【第1次】, 如图2所示。

单击【选择表格】对话框中的【确定】按钮, 如图3所示。

在弹出的【导入数据】对话框中选择【数据透视表】单选纽, 【数据的放置位置】选择【现有工作表】单选钮, 如图4所示。

单击左下方的【属性】按钮, 打开【连接属性】对话框, 单击【定义】选项卡。清空【命令文本】文本框中的内容, 输入如图5所示以下SQL语句:

SELECT"综合科目"AS考试科目, *FROM[综合科目S]UNION ALL

SELECT"建筑一级"AS考试科目, *FROM[建筑一级S]UNION ALL

SELECT"建筑二级"AS考试科目, *FROM[建筑二级S]UNION ALL

SELECT"结构一级"AS考试科目, *FROM[结构一级S]UNION ALL

SELECT"结构二级"AS考试科目, *FROM[结构二级S]

单击【确定】按钮返回【导入数据】对话框, 再次单击【确定】按钮选择创建一张空白的数据透视表, 如图6所示。

在【数据透视表字段列表】对话框中, 将“考试科目”字段拖动到【列标签】区域, 将“身份证号”“姓名”“省市” (因为可能会有重名情况, 所有应当以身份证号作为第一索引字段) 依次拖动到【行标签】区划, 将“通过情况”拖动到【∑数值】区划, 出现如图7所示结果。

虽然得到了结果, 但这种形式不方便阅读, 可以调整格式使表格更易于阅读。单击透视表中任意一单元格, 单击标题栏的【设计】选项卡, 单击【报表布局】按钮, 选择【以表格形式显示】, 如图8所示。单击【分类汇总】按钮, 选择【不显示分类汇总】, 如图9所示。得到结果, 如图10所示。

在这张报表中, 我们还可以单击【考试科目】字段标题单元格中的下拉列表, 筛选考试科目, 如图11所示。

再分析第1次考试中各省市通过情况。在【数据透视表字段列表】中将【行标签】中的“省市”拖动到“身份证号”上面, 出现结果。单击“省市”列任一单元格, 单击【选项】选项卡, 单击【折叠整个字段】, 如图13所示, 再隐藏B列和C列, 得到如图14所示结果。

3.3 多次 (整体) 考试成绩分析

在“D:成绩分析”文件夹中新建一个Excel文档 (工作簿) , 命名为“汇总.xlsx”, 在该文档的sheet1中执行选取数据源的步骤, 同单次考试成绩分析步骤一样, 不同之处在于两处:一是打开【选取数据源】对话框, 打开D盘根目录文件“汇总.xlsx”, 弹出【选择表格】对话框, 单击【名称】中的【sheet1】。二是在【命令文本】文本框中的内容, 输入以下SQL语句:

SELECT"综合科目"AS考试科目, *FROM[D:成绩分析第1次.xlsx].[综合科目S]UNION ALL

SELECT"建筑一级"AS考试科目, *FROM[D:成绩分析第1次.xlsx].[建筑一级S]UNION ALL

……

SELECT"综合科目"AS考试科目, *FROM[D:成绩分析第2次.xlsx].[综合科目S]UNION ALL

SELECT"暖通一级"AS考试科目, *FROM[D:成绩分析第2次.xlsx].[暖通一级S]UNION ALL

……

SELECT"监理安装"AS考试科目, *FROM[D:成绩分析第5次.xlsx].[监理安装S]

重复其他步骤, 得到如图15所示报表。

再汇总分析取得执业注册证书人员, 在报表的最后一列键入“注册”, 在下方的V3单元格键入公式“=IF (AND (T3>=1, U3>=2) , "注册", "") ”, 将公式复制到这一列, 得到如图16所示结果。

单击“注册”单元格, 单击标题栏上的【数据】选项卡, 单击【筛选】按钮, 再单击“注册”单元格的下拉箭头, 可进行筛选, 同时也可对“省市”进行筛选, 查看各省市通过人员的详细情况。

同时筛选任一专业科目和综合科目, 可得到某一专业取得注册执业证书人员情况。

还需要提出的是, 在报名中的数据区域, 如果想知道该数据的具体来源, 如表中想知道“孙建川”的“电气一级”没过具体情况 (单元格显示“0”) , 可以双击单元格, Excel会自动生成该数据的具体情况。

参考文献

[1]Excel Home.Excel 2010数据透视表应用大全[M].北京:人民邮电出版社, 2013.

2.Excel数据透视表：以自己的方式查看数据 篇二

计算性能

在Excel2010中，多线程计算有助于加快数据透视表中的计算速度，从而提高数据透视表的整体性能。这意味着，当您处理大量数据时(例如对数据透视表中的数据进行排序和筛选时)，可以更快地获得结果。

数据透视表标签

在Excel2010中，您可以在数据透视表中向下填充标签，因而能够更加轻松地使用数据透视表。还可以在数据透视表中重复标签，在所有的行和列中显示嵌套字段的项目标题。您可以为各个字段重复标签，但同时也可以打开或关闭数据透视表中所有字段的重复标签选项。

例如，如果数据透视表中的各个列中包含值字段，行上的所有字段的总计和小计关闭，则可以使用重复标签功能。

命名集

在Excel2010中，可以创建多层次结构的命名集。命名集可用于提取特定的信息集。如果您不熟悉多维表达式(MDX)语言，可以使用对话框来定义简单的命名集。如果您熟悉MDX，可以使用MDX编辑器来创建更加高级的命名集。数据透视表和OLAP公式都支持命名集。利用命名集，您可以生成显示不同业务领域的不同衡量标准的OLAP数据透视表。

项目搜索

Excel2010提供数据透视表中的项目搜索，使用户能够处理包含大量项目的字段和列。使用项目搜索，用户可以在数据透视表中的数千个甚至数百万个行中查找相关项目。可以在打开或关闭自动筛选的情况下，使用项目搜索，在单个列中查找透视字段或OLAP多维数据集字段项目标题。

排序

Excel2010提供多线程排序，从而可以更快地对数据透视表和Excel表中的大量数据进行排序。可以打开或关闭多线程排序。

筛选

筛选功能经过了改进，可以对大量数据进行分析。在Excel2010中，对多个项目的筛选速度会显著加快，并且，在对OLAP数据透视表和非OLAP数据透视表应用筛选器时，还会将不可见的数据(例如总计中的隐藏项目)包括在筛选范围内，

在Excel2010中，还可以选择使用切片器来筛选数据。单击切片器提供的按钮可以筛选数据透视表数据。切片器除了可以提供快速筛选之外，还可以指示当前筛选状态，使用户可以轻松地了解筛选出的数据透视表中显示的确切内容。

“值显示方式”功能

在Excel2010中，“值显示方式”功能的查找和使用更加简单，而且其在OLAP和非OLAP数据透视表中的功能均得到了改进。

大型数据透视表的撤消支持

有些撤消操作(例如刷新操作)会将多个项目添加到撤消堆栈中，在大型数据透视表中，这会显著降低性能。为了增强在大型数据透视表中的性能，现在实现的“撤消”命令可以支持更大的撤消堆栈。此外，如果在更新或刷新数据透视表的同时应用“自动调整”和“样式”，性能也会降低。为了解决Excel2010中的这个问题，可以按Esc取消“自动调整”和“样式”。当您在大型数据透视表中刷新和更新数据时，关闭这些选项可以增强性能。

OLAP数据透视表中的新增和改进功能：计算字段

在Excel2010中，可以通过创建计算字段，在OLAP数据透视表中添加某些计算。如果您不熟悉多维表达式(MDX)语言，可以使用对话框来定义简单的计算字段。如果您熟悉MDX，可以使用MDX编辑器来创建更加高级的计算字段。通过使用可自行创建的计算字段，您可以对数据进行深入分析，而无需依赖于IT部门的人员或其他具有编程知识的人员。

OLAP数据透视表中的新增和改进功能：回写支持

回写是一项用于处理Analysis Services多维数据集中的数据的重要功能。从高端的规划和预算到简单的多用户数据收集，在各种应用场景中经常会使用到回写。

3.Excel数据透视表：以自己的方式查看数据 篇三

判断题：

1、在数据筛选时要先选定数据区域然后再做自动筛选，这样可以避免因为空白数据行的存在所导致的出错。（√）

题目解析：如果不选定数据区域而直接自动筛选，将会出现空白行以下的数据漏选的错误。

2、各种创建数据透视表的方式都不是错误的。（√）

题目解析：各种创建透视表的方式都是将信息移来移去，看看各信息如何组合的过程，以及不同组合信息的结果。它是根据操作者信息需求，而对源数据进行的整理，所以各种创建数据透视表的方式都不是错误的。

3、一般情形下，数据透视表的结果随源数据的变化而即时更新。（×）

题目解析：一般情形下，数据透视表的结果不随源数据的变化即时更新，如果源数据发生变化，需要重新手动刷新，生成新的数据透视表结果。

4、在office2007中通过使用报表筛选，可以集中关注报表中数据的子集，通常是产品线、时间范围或地理区域。office2003数据透视表中的页字段，按页显示数据，并允许一次查看一项数据（例如，一个国家或地区），或者一次查看所有项。报表筛选和页字段二者实质上是一样的。（√）

题目解析：报表筛选和页字段操作界面不同，但两者实质上是一样的。

5、如果要删除某数据清单中的小计栏，可以先选定数据区域，查找“计”，将查找出的全部结果全选（Ctrl+A），关闭查找栏，通过编辑操作删除所在行即可。（√）

题目解析：按照上述办法操作，可以删除数据清单中的小计栏。

6、汇总字段与源字段名相同时，可以使用给汇总字段加一个半角空格的方法来消除错误。（√）

题目解析：汇总字段与源字段名相同时，可以使用给汇总字段加一个半角空格的方法来加以区分，以免出现数据错误。

7、随数据显示方式的不同，基本字段和基本项也不同。（×）

题目解析：采用某些数据显示方式，比如普通、占同行数据总和的百分比时就没有基本字段和基本项供选择。

8、一般情况下，数据透视表的源数据变化后其也随之更新。（×）

题目解析：一般情况下，数据透视表源数据变化后，不能自动更新，需要单击“数据透视表”工具栏上的“刷新数据”按钮更新透视表才能反映变化后的数据。如果对数据选项进行设置时选择打开后刷新按钮，才会在数据透视表打开时更新数据。

9、要对某数据清单中的时间按月汇总显示，需要在数据透视表中“组及显示明细数据”项 下的“组合”栏选“月”项目，起始日和终止日均按自动汇总。（×）

题目解析：如果要对某数据清单中的时间按月汇总显示，需要设置汇总的条件，即首先需要在数据透视表中“组及显示明细数据”项下的“组合”栏选“月”项目，其次起始日和终止日均需要手工操作，操作者可根据自身需要，输入起始日和终止日，如果需要2010年全年的数据按月汇总显示，则在起始日要输入2010-1-1，终止日要输入2010-12-31。

10、计算字段的内容必须是现有的字段或添加的计算字段来完成，而不能使用单元格引用的方式来完成。（√）

题目解析：数据透视表中不能使用单元格引用的方式来操作计算字段的内容，如果使用单元格引用的方式来完成计算字段将会出错。

11、对于计算项，可以按单元格逐个输入不同的公式。（√）

题目解析：如果名称为 OrangeCounty 的计算项有一公式“=Oranges *.25”作用于所有月份，也可以按单元格逐个输入不同的公式，比如将六月、七月和八月的公式更改为“=Oranges *.5”。

12、计算项的添加，可以在数据区进行操作。（×）

题目解析：在数据区不能添加计算项，如果要给某个字段添加计算项，鼠标必须放到那个字段进行添加。

13、在向字段中添加计算项时，如果字段中的项已经分组，则需要先取消分组。（√）题目解析：要向字段中添加计算项，如果字段中的项已经分组，则必须先取消分组，然后才能进行此操作。

14、在数据透视表中，分组的情形下也可以添加自定义字段或计算项。（×）

题目解析：如果进行了分组，在某些分组的情形下不可添加自定义字段或计算项。

单选题：

1、删除数据量非常大的数据清单中的空白行时，效率最低的操作是（A）。

A、逐行删除

B、使用辅助列，先对主要关键字排序，将空行集中删除，然后对辅助列排序，还原数据清单的最初顺序，最后删除辅助列

C、使用自动筛选方法：先选定数据区域，筛选出非空白项，然后定位可见单元格，复制粘贴到目的区域

D、使用辅助列，输入公式1/count（数值区域），定位公式中的错误值，编辑→删除→所在行

题目解析：在上述四种操作中，逐行删除完全是手工逐一进行定位和删除，如果数据量大，则操作繁琐，费时费力，仅适用于数据量比较小的数据清单。

2、数据透视表字段是指（B）。

A、源数据中的行标题

B、源数据中的列标题

C、源数据中的数据值

D、源数据中的表名称

题目解析：数据透视表字段取自源数据中的列标题，列标题生成数据透视表的字段的名称。每个字段都汇总了源数据中的多行信息。

3、要将数据清单中的合并单元格还原到未合并前的状态，某人先后进行了如下操作①选定所有的合并单元格；②对齐方式中撤销合并单元格③定位选定区域中的空值④输入公式“=A1”（A1为合并单元格复原后的第一个单元格位置）⑤输入回车⑥输入Ctrl加回车键⑦将还原后的数据区域复制⑧选择性粘值⑨回车。正确的顺序是（B）。

A、①②③④⑤⑥⑦⑧⑨

B、①②③④⑥⑦⑧⑨

C、①②③④⑤⑥

D、①②③④⑥

题目解析：前①到④及⑥到⑨的操作是将合并单元格撤销合并，还原到未合并前的状态，并将还原后的数据区域复制，其中第⑧是保证还原后的清单中不含有公式，这样才不会影响最终的数据透视表数值。

4、下列关于数据透视表中字段的说法错误的是（C）。

A、数据透视表中的字段有行字段、页字段、列字段和数据字段四类

B、数据透视表中源字段只能处于行字段、页字段和列字段三个中的一个位置

C、某个源字段在行字段中出现了，也可以同时在页字段中出现

D、数据字段可以重复多次使用

题目解析：数据透视表中源字段是一个标志值，只能处于行字段、页字段和列字段三个中的一个位置，而不能重复使用。

5、要创建数据透视表，第一步应该做（D）。

A、整理数据清单

B、打开数据透视表制作向导

C、将数据从工作表拖放到数据透视表视图中

D、确定想知道什么

题目解析：在制作数据透视表之前首先确定自己需要了解什么样的信息，解决的问题是什么，确定要选择哪些关键数据、按照什么标准组合数据等，然后再创建需要的数据透视表。

6、在数据透视表中对每行数据或每列数据进行汇总，可通过（D）操作。

A、字段设置

B、数据源选项

C、表格选项

D、格式选项

题目解析：打开表选项，在格式选项下将行总计或列总计按钮打勾选取，可以对每行数据或每列数据进行汇总。

7、要在数据透视表中分类汇总某个日期的累计值，应该选用的数据显示方式是（B）。

A、计数

B、按某一字段汇总

C、累计值

D、求和

题目解析：本题中要求是按某个日期进行分类汇总，需要指定汇总的条件，应该选用的数据显示方式是按某一字段汇总，在基本字段中选指定的日期选项，才能正确地予以分类汇总。

8、在一份销售数据清单中，要计算每位销售人员所售货物的笔数，应单击透视表工具栏中的按钮（A）。

A、字段设置

B、显示数据明细

C、表格选项

D、格式选项

题目解析：第一步是单击数据区域中的任意单元格，然后再选择字段设置按钮，在“汇总方式”列表中根据需要选择：计数、平均值、数值计数等运算。

9、在数据透视表中要将同一项目进行合并居中显示，需要使用如下功能（C）。

A、跨行合并

B、格式选项

C、合并标志

D、自动套用格式

题目解析：要在数据透视表中将同一项目进行合并居中显示，不能使用设置单元格格式的合并功能，需勾选表格选项中的合并标志功能。

10、当源数据表和数据透视表不在同一个工作表内时，要想实现源数据变动后数据透视表的内容随着更新，可以设置（D）。

A、数据透视表选项

B、格式选项

C、数据选项

D、打开时刷新

题目解析：一般情况下，当源数据变动时，数据透视表不能自动更新，只有当勾选数据透视表选项中的数据选项下的“打开时刷新”选项，才能实现源数据变动后数据透视表的内容随之更新。

11、我们在定义名称中编辑公式时，为了使光标在公式栏内移动需要按（B）。

A、F1

B、F2

C、F3

D、F4

题目解析：在定义名称中编辑公式时，F2键是编辑公式的开关键，打开后才能使光标 4 在公式栏内移动。

12、要将所设定好的公式复制到计算字段的公式栏内，采取的操作是（D）。

A、添加

B、插入字段

C、手工输入

D、Ctrl+V键

题目解析：由于计算字段的对话框中没有复制功能，使用Ctrl+V键是最快捷的方式，将所设定好的公式复制到计算字段。

13、在数据透视表中自定义计算字段是通过对（A）执行运算得到自定义字段的。

A、数据透视表中的现有字段

B、数据清单中的现有字段

C、数据透视表中的现有字段项

D、数据清单中的现有字段项

题目解析：自定义字段是根据操作者需要通过对数据透视表中的现有字段执行计算所得到的字段。

14、在数据透视表中，若想在公式中使用其他字段的数据时，应使用（C）。

A、计算项

B、函数

C、计算字段

D、名称

题目解析：若想在公式中使用其他字段的数据时应使用计算字段。A、B、D选项都无法实现此功能

15、使用定义名称的方法创建一个动态数据透视表时，关键就是（C）。

A、定义数据区域

B、定义名称

C、使用OFFSET函数和COUNTA函数定义动态数据区域

D、使用OFFSET函数和COUNT函数定义动态数据区域

4.Excel数据透视表：以自己的方式查看数据 篇四

1、Windows NT4.0+ORACLE 8.0.4

2、ORACLE安装路径为：C:ORANT

SQL语句：

/*     中文环境    */

col 表空间名 format a20;

select

b.file_id  文件ID号,

b.tablespace_name  表空间名,

b.bytes  字节数,

(b.bytes-sum(nvl(a.bytes,0)))  已使用,

sum(nvl(a.bytes,0))  剩余空间,

sum(nvl(a.bytes,0))/(b.bytes)*100  剩余百分比

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id

group by b.tablespace_name,b.file_id,b.bytes

order by b.file_id;

/*    英文环境    */

col tablespace_name format a20;

select

b.file_id  file_ID,

b.tablespace_name  tablespace_name,

b.bytes  Bytes,

(b.bytes-sum(nvl(a.bytes,0)))  used,

sum(nvl(a.bytes,0))  free,

sum(nvl(a.bytes,0))/(b.bytes)*100         Percent

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id

group by b.tablespace_name,b.file_id,b.bytes

order by b.file_id;

文件ID号 表空间名                          字节数    已使用  剩余空间 剩余百分比

--------- ------------------------------ --------- --------- --------- ----------

1 SYSTEM                          20971520   9971712  10999808  52.451172

2 USER_DATA                        3145728    432128   2713600  86.263021

3 ROLLBACK_DATA                    5242880   1640448   3602432  68.710938

4 TEMPORARY_DATA                   2097152      2048   2095104  99.902344

★ 两表连接的SQL语句数据库教程

★ 将excel数据导入到表的sql语句

★ 怎样将Excel中的数据导入到SQL Server 数据库中数据库教程

★ 一个批量删除临时表的sh用于informix数据库

★ 在Excel工作表区域内快速移动或滚动EXCEL基本教程