还剩9页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
教程:数据透视表我县有三个职业学校,每个学校有四个专业,每个专业都有五门课,同学人数千人左右这次期末统考结束后,教育局要求将全部同学的考试成果汇总到一个工作表中,并要求对三所学校的各科成果、各专业成果进行分析比较可见工作量之大啊!不仅要运用到分类、还要实现汇总计算永中的数据透视表就具有很强大的数据处理力量,不必写入简单的公式,使用向Office导创建一个交互式表箱就可以自动提取、组织和汇总数据下面就来和大家共享统计出的全县三所学校的同学成果
一、创建数据透视表在创建数据透视表前,首先对源数据进行几项重要检查,否则无法创建数据透视表*X必需包含列标题如准考证号、语文、数学等*入用于创建数据透视表的数据区域内不能有空行或空列,否则仅为空行前或空列左的数据进行创建*每列只能包含一种类型的数据九
1.选择数据-数据透视表,弹出数据透视表向导步骤1对话框
2.单击下一步,到步骤2,输入要建立数据透视表的数据源区域(留意全县三所职中统考成果行不能包含在内)方法很简洁,比如要获得周瑜的各科目的平均成果,可以将透视表调整回图所示效果,13在引用数据单元格输入,再点击周瑜最右边的总计项在编辑栏中,就会显示周瑜各科目平均值的引用公式二(成果,学号姓名,周瑜,性另『,男)GETPIVOTDATA$H$3,,20070616,当然你完全可以将公式中的“学号、等项,改为某个单元格引用但这种引用,20070616假如脱离开透视表,就会消失错误.数据透视图的生成15假如在第步,选择数据透视图(及数据透视表),则会同时生成数据透视表和数据透2视图,不过现在生成数据透视图,也为时不晚只需将光标放到透视表区域,插入一图表即可生成相应的数据透视图其他方面的应用在很多数据中筛选出不重复的数据,以本例说明,源数据中的、、列都可以看成是有B CD重复的数据,通过数据透视表,就可以生成不重复的姓名列表,性别列表和科目列表动态图表的建立在企业的经营活动中,往往需要为每个部门建立大量相像的图表,假如在一张工作表上建立太多的图表,既费时也使得图表显得凌乱不堪我们可以建立动态图表来解决这个问题,当需要了解某个部门的销售状况时,只需将鼠标移到工作表中该部门的单元格上,即可马上显示出该部门的销售图表【例2-10]某企业的8个销售部门一年内各月的销售量数据如图2-34所示,建立各部门的动态图表的步骤如下图动态销售图表2-341设计动态图表数据区域,如图2-34所示2在单元格A13中输入公式=INDIRECTADDRESSCELLrow,COLUMN A3,并把该公式向右填充复制到中,这里的意思是返回参数所在的列标,M13COLUMN CELLrow的意思是返回当前光标所在的行号,ADDRESS行号,列标的意思是返回由行号和列标确定的单元格,INDIRECT的意思是返回参数所确定的单元格内容;3选中区域A12:M13,插入“折线图,并进行相应的格式设置,则动态图表就建立起来了若鼠标单击A3单元格,再按F9键即对工作表数据重新计算,就会显示部门A的销售图;若鼠标单击A5单元格,再按F9键即对工作表数据重新计算,就会显示部门C的销售图这样,就可以很便利地对各个销售部门的销售量进行直观的观看和分析
3.依据步骤,完成后,会同时显示数据透视表工具栏和数据透视表字段列表”框
二、构建数据透视表字段.双击”数据透视表字段列表中的学校和专业”,项目会自动被填充到将行字段拖至1此处区域.选中语文,将其拖拽到请将数据字段拖至此处区域2语文拖至后效果见下图(这时可清楚得观看出各个学校及各个专业的语文成果总分).参照上面方法,分别将数学、英语等科目拖至上图中的“汇总”列区域3全部科目拖至后效果
三、转变数据透视表的汇总方式由于各个学校、各个专业的同学人数不等,所以通过总分还无法推断哪所学校此次考试的成果更胜一筹最有力的方法就是通过求平均值比较
1.选中数据透视表中的求和项:语文,单击”数据透视表工具栏上字段设置图标,弹出数据透视表字段”对话框.在汇总方式框中选择平均值2三所学校的各专业的语文科目就由〃求和改成求平均值”了与此同时,各学校的语文平均分也可以轻松得到
3.参照上面方法,得出其他学科的平均分值
四、隐蔽项目的明细数据
1.双击财会学校、第一职专、三山职中所在的单元格,即可隐蔽各专业各学科的明细数据效果见下图所示
2.这时显示每个学校每门科目的平均分及县平均分通过观看可一目了然得分析出哪个学校在此次统考中的成果更优异及较县平均分的差距贴心提示假如检查发觉数据透视表中的数据有误时,不能直接在其上进行修改,需先修改数据源,然后单击”数据透视表工具栏的数据透视表按钮,从下拉菜单中选择〃刷新数据标准数据库数据组织格式一数据透视表假如有一同学成果表,数据是按标准数据库组织格式组织的
1.即第一行为字段名,下面的每一行为一个纪录,如图所示图数据透视表源数据
1.数据一数据透视表和数据透视图2假如只生成数据透视表,则只选择数据透视表,假如同时要生成数据透视图,则最好选择下一项,即数据透视图(及数据透视表),这里选择第一项,下一步图数据透视表向导一
2.指定数据区域3在这里可以指定数据区域,假如以后,还要添加新的纪录,最好这里指定的数据区域可以大一些,如A1:F1OOO(你甚至可以设为A:F,这样做的缺点是,会产生空格项,但假如增加数据时,不必再重头制作),下一步图数据透视表向导二
3.指定数据透视表产生的位置4可以指定新建工作表,还可以指定现有工作表,只需指定产生数据透视表的第一个单元格就可以了,如H3,完成图数据透视表向导三4产生的空数据透视表如下图所示图数据透视表初表5看了这个图,你应当明白,我为什么要在步,指定在显示数据透视表了吧最上面产4H3生的哪一行的妙处,将在下面介绍.行字段的拖放5将学号拖至〃将行字段拖至此处〃框中,姓名和性别分别拖至学号相邻的右侧,将科目拖至笃等I」字段拖至此处框中将成果拖至请将数据项拖至此处框中经过以上设置,可以产生如下图所示的效果初步完成表一
6.字段的进一步设置6在左侧的行字段中,不需要对姓名和性别进行汇总,可以在任意的姓名汇总上(如刘备汇总单元格)右击,隐蔽(图12),或者字段设置,无,确定同样设置隐蔽性别的汇总注这里的汇总,实际就相当于数据分类汇总产生的结果,也就是说,数据透视表对于需要常常分类汇总的人来说,要便利了很多,不需要手工排序,排序工作自动完成本例只是选择了最简洁一种,就不需要分类汇总了字段设置对话框7(留意上面这个对话框,下面要反复用到)此时,数据透视表变为如下形式图初步完成表二
8.隐蔽空白行列7在行(空白)或列(空白)字段名上右击,隐蔽(图则透视表变为121图初步完成表三9假如你不想显示总计行和总计列,同样可以在总计行标题和列标题上右击,隐蔽.调整数据汇总方式8此时的显示,并不抱负,显示的仅为数据的个数,而我们需要显示各科成果,与几门课的平均值在图中显示为和区域任一单元格上右击,字段设置,改为求和或平均值(图17%由于每人每项目的数据仅有一个,求和与平均值在数值上是相等的,可以任选一项其实,在图7字段设置对话框中,你还可以设置其他的计算方式,包括求和、计数、平均值、最大值、最小值、乘积、数值计数、标准偏差、总体标准偏差、方差、总体方差,总计种计算11方式,你可以依据你的需要进行选择哟图初步完成表四
10.数值格式的设置9对于最右侧的总计项,由于显示的小数点位数过多,能不能通过设置小数点位数,使列宽变得窄一些呢?,这个没有问题,右击字段设置,数字(图7中与高级相同位置的按钮,在这里已经变为数字),数值,保留小数点1位令人惋惜的是,这里设置的数值格式,会影响到整个表的单元格设置,在一处设置了保留小数点位数,在其他单元格,也自动继承这种数值格式,假如要单独设置,可以按常规的单元格格式设置方法,选中要设置的单元格区域,再设置单元格格式图完成表
11.数据刷新10数据透视表还有一个致使的弱点,就是不能自动刷新数据,需要通过右击一刷新数据,进行手工刷新图右击快捷菜单12数据刷新后,你可以看出,姓名中多了周瑜,科目中添加了政治而刘备的政治缺考假如你在第步,没有多指定的数据区域,新增加的数据,将不能显示出来3图添加数据并刷新后的效果
13.数据挨次的调整11图13中的数据,怎么按性别排序呢比如把貂蝉和小乔放到周瑜的后面这个操作对于Excel2003及以前的版本,略微差了一些,只能一个一个地调整,在貂蝉对应的学号上右击,挨次,移至底端再在小乔的学号上右击,挨次,移至底端请谅解,调整后的效果图,就不要再上传了吧,这个文章已经够长了在Excel2007中,排序功能大大增加,你用了就知道了,这里也不再多叙图挨次调整子菜单14假如你并不在意表格各字段的排列,只是要统计男生与女生的平均值,只需将性别拖放到学号的左侧图按性别汇总效果
15.数据的筛选12数据透视表还自动地设置成自动筛选状态,你可以便利地进行筛选,如只显示男同学的成果,只需点击性别右侧的三角形筛选标志,仅勾选男”图透视表的自动筛选功能16同样,对科目进行筛选,可以有选择地显示某一科目的成果但这样筛选后,可能会导致数据格式的清除既然不想显示全部的科目,就不如将科目拖放到最上面的将页字段拖至此处框中,并通过筛选,只显示某一科目,如数学将科目拖至页字段的效果17假如将科目拖放到行字段区域,则会产生按学号(姓名)汇总的效果(这个就不上传效果图了吧力数据透视表是不是很自由?.数据透视表的复制13辛辛苦苦制作的数据透视表,按上所述,只显示为数学成果,要同时再制作一个只显示英语成果的透视表,是不是需要重新制作呢?答案是假如你不怕麻烦,想再练习一遍,就重新制作好了不过,我可不情愿一切从头来,选中透视表区域,复制,在单元格粘贴其它的设置,就不用再多说了吧N1你不妨试试下面的效果,复制一份透视表,让原来的透视表显示平均成果,新粘贴的透视表显示计数项.数据透视表数据的猎取14数据表是动态变化的,一旦对透视表结构进行调整,所引用的单元格的位置有可能会发生转变,如何才能获得一个动态跟随的效果呢。