登陆注册
8915300000021

第21章 数据管理与分析(2)

例如,若一个单元格中含有文本“iPhone(16G)”,另一个单元格含有“iPhone(8G)”,当进行排序时,首先比较第1个字符,它们都是i,所以就比较它们的第2个字符,由于都是P,所以进行下一个字符的比较,一直到第8个字符,由于字符“1”小于“8”,就结束了比较,即“iPhone(16G)”排在“iPhone(8)”之前。

逻辑值:False 排在True 之前。

错误值:所有的错误值都是相等的。

空白(不是空格):空白单元格总是排在最后。

汉字:汉字有两种排序方式,一种是按照汉语拼音的字典顺序进行排序,如“手机”与“储存卡”按拼音升序排序时,“储存卡”排在“手机”的前面;另一种排序方式是按笔画排序,以笔画的多少作为排序的依据,如以笔画升序排序,“手机”应排在“储存卡”前面。

递减排序的顺序与递增顺序恰好相反,但空白单元格将排在最后。

日期、时间也当文字处理,是根据它们内部表示的基础值排序。

5.3数据筛选

数据筛选是一种用于查找数据的快速方法,筛选将数据列表中所有不满足条件的记录暂时隐藏起来,只显示满足条件的数据行,以供用户浏览和分析。Excel提供了自动和高级两种筛选数据的方式。在这一节中我们将使用数据筛选来完成任务2。

5.3.1自动筛选

自动筛选为用户提供了在具有大量记录的数据列表中快速查找符合某些条件的记录的功能。筛选后只显示出包含符合条件的数据行,而隐藏其他行。

在任务2中,为了及时跟踪各个类别与各个品牌的商品销售情况,需要销售清单中查询相关信息,可以通过自动筛选获取上述信息,我们以“类别”字段的筛选作为例子,具体操作步骤如下。

步骤1:销售清单中的任一单元格。

步骤2:选择“数据”→“筛选”→“自动筛选”菜单项。数据列表中第一行的各列中将分别显示出一个下拉按钮,自动筛选就将通过它们进行。

步骤3:单击需要进行筛选的列标的下拉列表,Excel会显示出该列中所有不同的数据值,这些值可用于筛选条件,如单击“类别”旁边的下拉列表,会显示出“类别”列中所有的值,其中各项的意义解释如下:

全部,显示出工作表中的所有数据,相当于不进行筛选。

前10个,该选项表示只显示数据列表中的前若干个数据行,不一定就是10个,个数可以修改。

自定义,该选项表示自己可以自定义筛选条件。

MP3、MP4、储存卡、手机、相机,这些是“类别”列中的所有数据,选择其中的某项内容,Excel就会以所选内容对数据列表进行筛选。

步骤4:如要查看“手机”的销售情况,只需在下拉列表中选择“手机”,系统就会显示。

同理,如果需要查询各个品牌的商品销售情况,则选择“品牌”旁边的下拉列表,从中选择需查看的品牌即可得到该品牌商品的销售记录。

如果要在数据列表中恢复筛选前的显示状态,只需要再次选择“数据”→“筛选”→“√自动筛选”菜单项,这时会发现该菜单项前面的“√”消失,数据列表就恢复成筛选前状态。

在任务2中,我们还需对库存清单进行筛选,找出库存最大的前5种商品,给店主小张提供进货的参考。

单击“(前10个)”显示的对话框点击“自动筛选”命令后,系统添加下拉列表标志,我们要筛选出5种库存最大的商品,应单击“期末库存”列标的下拉列表,然后选择列表中的“(前10个)”,Excel会弹出显示个数设置的对话框。

“显示”的下拉列表中选择“最大”,然后在编辑框中输入5。

如需从库存清单中筛选出库存为0的商品,则只要在下拉列表中选择“0”即可得到如。

如果要找出库存大于0并且小于等于3的手机的库存情况,需要分别对“期末库存”和“类别”进行两步筛选。首先使用“(自定义 )”,打开“自定义自动筛选方式”对话框在“期末库存”下拉列表框中选择“大于”选项,并在后面的下拉列表框中选择或直接输入“0”,选中“与”单选钮(“与”表示同时满足两个条件,“或”表示满足其中一个条件即可),然后在下面的下拉列表框中选择“小于或等于”,并在后面的下拉列表框中选择或直接输入“3”,单击“确定”按钮;第二步,在“类别”旁边的下拉列表框中选择“手机”,即可得到我们需要的结果。

5.3.2高级筛选

自定义筛选只能完成条件简单的数据筛选,如果筛选的条件比较复杂,自定义筛选就会显得比较麻烦。对于筛选条件较多的情况,可以使用高级筛选功能来处理。

使用高级筛选功能,必须先建立一个条件区域,用来指定筛选条件。条件区域的第一行是所有作为筛选条件的字段名,这些字段名与数据列表中的字段名必须一致,条件区域的其他行则输入筛选条件。需要注意的是,条件区域和数据列表不能连接,必须用空行或空列将其隔开。

条件区域的构造规则是:同一列中的条件是“或”,同一行中的条件是“与”。

前面我们使用自动筛选的自定义方式查询库存大于0并且小于等于3的手机库存情况,要进行两步筛选才能够得到结果,现在我们可以使用高级筛选进行查询,步骤如下。

步骤1:库存清单中创建一个条件区域,输入筛选条件,这里在I1、J1、K1单元格中分别输入“类别”、“期末库存”、“期末库存”,在I2、J2、K2中分别输入“手机”、“>0”、“<=3”。

步骤2:选定库存清单数据列表中的任一单元格(Excel可据此将连续的数据区域设置成数据的筛选区域,否则要在后面的操作步骤中指定筛选区域),然后选择“数据”→“筛选”→“高级筛选”菜单项,打开“高级筛选”对话框。

步骤3:指定数据列表区域和条件区域。如果第2步中未选定数据列表中的单元格,可以在“高级筛选”对话框中的“列表区域”中输入要进行筛选的数据所在的工作表区域,然后在“条件区域”中输入第1步中所创建的条件区域,可直接输入“I1:K2”,或者单击“高级筛选”对话框中“条件区域”设置按钮后,用鼠标拖动选定条件区域中的条件。

步骤4:指定保存结果的区域。若筛选后要隐藏不符合条件的数据行,并让筛选的结果显示在数据列表中,可打开“在原有区域显示筛选结果”单选按钮。若要将符合条件的数据行复制到工作表的其他位置,则需要打开“将筛选结果复制到其他位置”单选按钮,并通过“复制到”编辑框指定粘贴区域的左上角单元格位置的引用。Excel会以此单元格为起点,自动向右、向下扩展单元格区域,直到完整地存入筛选后的结果。

步骤5:最后单击“确定”按钮。

如果要将数据列表恢复到筛选前的状态,可以选择“数据”菜单中的“筛选”子菜单,从中选择“全部显示”命令即可。

提示:在“高级筛选”时,可以将某个区域命名为Criteria。此时“条件区域”框中就会自动出现对该区域的引用,也可以将要筛选的数据区域命名为Database,并将要粘贴行的区域命名为Extract,这样,Excel就会让这些区域自动出现在“数据区域”和“复制到”框中。

现在让我们来完成任务2中的最后一个要求,分析销售统计表,找出销售金额高于平均销售金额的商品。

由于平均销售金额不是一个常数条件,而是对工作表数据进行计算的结果。假如先计算出平均销售金额,再用计算结果进行筛选,这样当然可以完成任务,但是这样做比较死板,一旦数据有变化,这个筛选结果就不正确了。

那么是否可以在筛选条件中包含一个平均值计算公式呢?答案是肯定的,Excel的高级筛选允许建立计算条件。建立计算条件须满足下列3条原则:

计算条件中的标题可以是任何文本或空白,不能与数据列表中的任一列标相同,这一点与前面指定的条件区域刚好相反;必须以绝对引用的方式引用数据列表外的单元格;必须以相对引用的方式引用数据列表内的单元格。

了解了计算条件的规则之后,我们可以按照下列步骤建立计算条件。

步骤1:在单元格I9(或任一空白单元格)中输入平均值计算公式“=MEDIAN(E4∶E30)”,该公式的计算结果为1440。

步骤2:在I1中输入计算条件的列标,其值须满足上述的第1条原则,如输入“高于平均销售金额”。

步骤3:在I2中输入计算条件公式“=E4>$I$9”,输入该公式须满足上述的第2、3条规则,E4是数据列表中的单元格,因此只能使用相对引用的方式。I9包含平均值公式,是数据列表之外的单元格,只能采用绝对引用的方式。

计算条件建立好之后,按照前面介绍的步骤进行高级筛选,数据区域是A3:G30,条件区域是I1∶I2,筛选的结果。

至此,我们已经完成了任务2中的全部要求,分析结果将有助于店主改善销售、进货等经营活动,无论是从销售数量、库存积压还是销售总金额看,多普达品牌的手机都是最理想的,特别是S900。

5.4分类汇总

分类汇总是对数据列表指定的行或列中的数据进行汇总统计,统计的内容可以由用户指定,通过折叠或展开行、列数据和汇总结果,从汇总和明细两种角度显示数据,可以快捷地创建各种汇总报告。在这一节中,我们将使用分类汇总来完成任务3。

5.4.1分类汇总概述

Excel可自动计算数据列表中的分类汇总和总计值。当插入自动分类汇总时,Excel将分级显示数据列表,以便为每个分类汇总显示或隐藏明细数据行。Excel分类汇总的数据折叠层次最多可达8层。

若要插入自动分类汇总,我们必须先对数据列表进行排序,将要进行分类汇总的行组合在一起,然后为包含数字的数据列计算分类汇总。

分类汇总为分析汇总数据提供了非常灵活有用的方式,它可以完成以下工作:

显示一组数据的分类汇总及总和;

显示多组数据的分类汇总及总和;

在分组数据上完成不同的计算,如求和、统计个数、求平均值(或最大值、最小值)、求总体方差等。

5.4.2创建分类汇总

在创建分类汇总之前,首先要保证要进行分类汇总的数据区域必须是一个连续的数据区域,而且每个数据列都有列标题;然后必须对要进行分类汇总的列进行排序。这个排序的列标题称为分类汇总关键字,分类汇总时只能指定排序后的列标题为汇总关键字。

例如,如果要统计各个类别的商品销售数量,应该先以“类别”字段为主要关键字进行自定义排序,并以“品牌”字段为次要关键字按升序排序,参见5.2.2节。

在对分类字段排序后,就可以插入Excel的自动分类汇总了,操作步骤如下。

步骤1:单击数据区域中的任一单元格,然后选择“数据”→“分类汇总”菜单项,打开“分类汇总”对话框。

步骤2:从“分类字段”下拉列表中选择要进行分类的字段,分类字段必须已经排序好,在本例中,我们选择“类别”作为分类字段。

步骤3:“汇总方式”下拉列表中列出了所有汇总方式(统计个数、计算平均值、求最大值或最小值及计算总和等)。在本例中,我们选择“求和”作为汇总方式。

步骤4:“选定汇总项”的列表中列出了所有列标题,从中选择需要汇总的列,列的数据类型必须和汇总方式相符合。在本例中我们选择“数量”作为汇总项。

步骤5:选择汇总数据的保存方式,有3种方式可以选择,可同时选中,默认选择是第1和第3项。

替换当前分类汇总:选中时,最后一次的汇总会取代前面的分类汇总。

每组数据分页:选中时,各种不同的分类数据分页显示。

汇总结果显示在数据下方:选中时,在原数据的下方显示汇总计算的结果。

图中左边是分级显示视图,各分级按钮的功能解释如下:

隐藏明细按钮:单击按钮隐藏本级别的明细数据。

显示明细按钮:单击按钮显示本级别的明细数据。

行分级按钮:指定显示明细数据的级别。例如,单击1就只显示1级明细数据,只有一个总计和,单击3则显示汇总表的所有数据。

在Excel中我们也可以对多项指标进行汇总,并且可以进行嵌套分类汇总。现在让我们来完成任务3,我们需要对销售统计表中的销售金额和利润金额两项指标进行汇总,并且需要对各个类别与各个品牌的商品进行分类汇总,由于每个类别都有多个品牌,因此我们可以先对类别进行分类汇总,然后在此基础上再对品牌进行分类汇总。在5.2.2节中我们对销售统计表按类别进行了自定义排序,此处只需将排序的次要关键字修改为“品牌”升序排序即可,排序后的结果。分类汇总的操作过程如下。

步骤1:单击销售统计表中的任一单元格,然后选择“数据”→“分类汇总”菜单项,打开“分类汇总”对话框。

步骤2:分类字段选择“类别”,汇总方式选择“求和”,在“选定汇总项”下拉列表框中选择“销售金额”和“利润金额”两个字段,按“确定”按钮即可得到的结果。

步骤3:再次选择“数据”→“分类汇总”菜单项。分类字段选择“品牌”,汇总方式和汇总项与第2步相同,清除“替换当前分类汇总”复选框,按下“确定”按钮,我们就可以得到的结果。

同类推荐
  • 悟道:一位IT高管20年的职场心经

    悟道:一位IT高管20年的职场心经

    本书是一位有20多年职场经验的IT企业高管撰写的一系列有关职场悟道的短文集成,讲述的是在企业里如何修炼自己,如何摆平自己的心态,怎样做到“世事洞明”和“人情练达”,如何“搞定老板”,怎样做到工作和生活平衡等诸多话题,涉及到跳槽、转行、升迁、环境、沟通、老板、下属、老外等等。每一篇都以作者的亲身经历或者身边的故事说明道理,语言简洁流畅,妙趣横生,更有不少经典片段和发人深省的职场警句,读起来就像是一个睿智幽默的老朋友坐在你面前娓娓道来。
  • 音视频合成制作

    音视频合成制作

    本书共分4篇6章,其中第1篇简介音视频合成的编导基础,第2篇主要介绍Sony Cinescore 1电影音乐自动合成,第3篇主要介绍Vegas 7.0音视频剪辑合成,第4篇主要介绍Adobe Audition 2.0、Cakewalk SONAR 6和Steinberg Nuendo 3的音视频合成。本书可以作为音乐、美术、动画、舞蹈、影视、戏剧等艺术院校视听艺术合成制作课程的教材,也可以作为相关人员的自学用书。
  • 现代企业财务软件应用教程

    现代企业财务软件应用教程

    本书内容包括财务软件概论、系统管理、企业应用平台、总账管理、报表管理、薪资管理、固定资产管理、应收款管理、应付款管理等。
  • 学校计算机实用技术

    学校计算机实用技术

    本书由杭州市上城区教育学院编著,他们邀请了众多信息技术教育教学的一线专家,认真细致地筛选了学校计算机应用方面的常见问题,提出了解决问题的具体操作方案,旨在帮助一线教育工作者解决可能面临的种种问题,为广大教师提供便捷服务。
热门推荐
  • 福妻驾到

    福妻驾到

    现代饭店彪悍老板娘魂穿古代。不分是非的极品婆婆?三年未归生死不明的丈夫?心狠手辣的阴毒亲戚?贪婪而好色的地主老财?吃上顿没下顿的贫困宭境?不怕不怕,神仙相助,一技在手,天下我有!且看现代张悦娘,如何身带福气玩转古代,开面馆、收小弟、左纳财富,右傍美男,共绘幸福生活大好蓝图!!!!快本新书《天媒地聘》已经上架开始销售,只要3.99元即可将整本书抱回家,你还等什么哪,赶紧点击下面的直通车,享受乐乐精心为您准备的美食盛宴吧!)
  • 南镜

    南镜

    新的世界,新的历史,不变的热血,在春花起舞的季节里,开启新的征程。
  • 岸边的猫

    岸边的猫

    本文章再说,一只青蛙讲诉蚯蚓爱上猫的故事。
  • 大BOSS:求放手

    大BOSS:求放手

    第一次写写的不好的话,请大家多多包涵。也希望大家喜欢我这个作品,谢谢!
  • 一舞倾城傲娇王妃不为后

    一舞倾城傲娇王妃不为后

    四年前,因为误会她悄然离他而去他发疯般寻找。四年内,他带着他们的儿子走遍整个江山,只为寻找到她的影子。四年后,整个皇城只有她一人可跳出那倾国倾城的倾城绝,一年一舞。一次只为一人而舞。而他带着他们的儿子默默跟随在她身后。“不知爱妃可否玩够?’‘回王爷,还没。’。。。。。。‘那敢问爱妃打算何时回家?’‘我堂堂倾城阁阁主是不可能跟你回去成亲的’。。。。。。‘哦?是么?来人呐,将王妃给本王绑回去’‘妄想!'.......‘主子不好了,女主子带着小女主子跑了!’‘哎,带上小主子,我们追。’
  • 吉尔尕朗河两岸

    吉尔尕朗河两岸

    本书以一条静静流淌在天山腹地伊犁大草原的吉尔尕朗河为背景,通过作者十年住居生活的亲身体验,以细腻浪漫的笔调和田园牧歌式的行吟,全景式的描绘了吉尔尕朗河两岸广阔的牧场、田园、林区、山脉等四季变幻的迷人风景,并对生活在此的游牧民族的独特文化、风俗、节庆、民歌等做了深入详实的了解与记录。全书充溢着作者对这片远离都市喧嚣的原生态土地上山川风物的热爱与眷恋,对现代工业文明弊端的清醒与重返健康田园生活的提倡,以及对生态文明的现状和现代人精神生活的关注与反思。
  • 虚拟世界之呜喵王

    虚拟世界之呜喵王

    在无限轮回的恐怖动漫世界,你能打破盒子理论吗?好奇的喵咪讨论组569017731
  • 神皇之界

    神皇之界

    一场天灾,少年不甘命运如此,凭着一颗不甘的心,逆天而行!!
  • 换种方式说爱你之二 雪满寒石

    换种方式说爱你之二 雪满寒石

    一千年的距离是多少?是短到她回眸瞬间,是长到他分秒如年?
  • 守护甜心之最后的时代

    守护甜心之最后的时代

    一次意外穿越到守护甜心时代,拥有了系统,通过一次又一次任务,来提升自身能力,甚至出现异能,然而,在所谓的系统背后,竟是一个惊天的阴谋。另一所城市的守护者,新一轮的坏蛋突袭,新的复活社成立,最后竟然与这系统脱不了干系,这一切阴谋的背后究竟隐藏着什么。这个时代,是否就是最后的时代?背叛与欺骗,信任与守护,信任危机与能力危机,当所有真相都付出水面的时候,又会怎样。欢迎来到最后的时代【永不弃文,放弃食用,不喜勿进,周更模式,假期日更】