资源描述
EXCEL使用交流,市场销售部：岑惠梅2012.09.05,目录,一、快捷键、菜单功能等二、统计数据的透视分析三、函数四、EXCEL图表制作,第一讲快捷健、菜单功能等,1.1、快捷键,Ctrl+“”光标移动到数据区域最右边的单元格、数据区域最下面的单元格Ctrl+“”Shift+Ctrl+“”选中从光标所在位置至数据区域最右边单元格之间的区域Shift+Ctrl+“”选中从光标所在位置至数据区域最下面边单元格之间的区域,很多，大多数记不住也用不着！常用的通常适合于所有的Windows操作：Ctrl+C（复制）、Ctrl+V（粘贴）、Ctrl+A（全选）、Ctrl+B（应用或取消加粗格式设置）等,下面是几个快速定位光标：,操作练习：用户明细1,1.2、导入文本格式的文件,要将文本格式的数据表文件导入到Excel表中，只需先启动Excel软件，然后执行“打开”文件的操作，设置打开文件的类型为“文本文件”格式，然后从文本格式的数据表文件的所在位置找到打开即可。具体操作步骤如下：单击“Office按钮”一“打开”命令按钮，弹出“打开”对话框从“查找范围”下拉列表框找到需要打开文本文件所在的文件夹，从“文件类型”下拉列表中选择“文本文件”，从中可以看到需要打开的“文本数据表”文件，然后用鼠标单击该文本文件的图标启动了“文本导入向导”，该向导共包括三个步骤,操作练习：用户明细2.txt、固定资产数据表.txt,1.3、F4的妙用,F4,可以重复上一次你进行的操作,比如你插入一列,按F4,可以重复你的插入列动作.在编辑公式时,引用其他单元格的时候,按F4,可以在4种引用方式下切换.例如A1,$A$1,A$1,$A1,表示该引用是相对,绝对还是混合引用.A1：行列均不绝对；$A$1：行列均绝对；A$1：列不绝对，行绝对；$A1：列绝对，行不绝对；,注意：要把鼠标点到公式中相应的单元格位置再按才行,操作练习：各营销中心产能情况,4、查找、替换,可在菜单列选择“”，或使用快捷键Ctrl+F替换还可应用在公式中。,操作练习：各营销中心产能情况,5、选择性粘贴,粘贴时一并进行运算,操作练习：各营销中心产能情况,6、定位,操作练习：各营销中心产能情况,在数据表选中区域，点击选择“定位条件”，或按快捷键Ctrl+G，弹出对话框（如上图），若要定位空值，则点“空值”，然后在第一空白的单元格内填写“=单元格”，然后同时按Ctrl+回车键。,7、条件格式的运用,使用Excel条件格式可以帮助您直观地查看和分析数据、发现关键问题以及识别模式和趋势。,在分析数据时，您经常会问自己一些问题，如：在市级代理商中，月产能低于10户的有哪些？这个月哪个营销中心的降幅大于平均值?条件格式有助于解答以上问题，因为采用这种格式易于达到以下效果：突出显示所关注的单元格或单元格区域;强调异常值;使用数据条、颜色刻度和图标集来直观地显示数据。条件格式基于条件更改单元格区域的外观。如果条件为True，则基于该条件设置单元格区域的格式;如果条件为False，则不基于该条件设置单元格区域的格式。,操作练习：各营销中心产能情况,8、分列,Excel中分列是对某一数据按一定的规则分成两列以上。分列时，选择要进行分列的数据列或区域，再从数据菜单中选择分列，分列有向导，按照向导进行即可。关键是分列的规则，有固定列宽，但一般应视情况选择某些特定的符号，如空格、逗号、分号等。值得注意的是，分列向导中可以对分列后的各列设置格式，这对于文本、时间等，先行设置格式能减少分列后的很多操作。,操作练习：各营销中心产能情况,9、数据的排序,Excel中数据排序的基本规则排序有升序或降序两种方式。以按升序排序为例。实现具有多个关键字的复杂排序,操作练习：各营销中心产能情况,10、数据的筛选,根据需要，有时要按指定条件从数据库中筛选特定记录。Excel提供三种筛选方法：自动筛选、自定义筛选和高级筛选。（1)自动筛选（2）自定义筛选对某一个字段筛选出符合特定值的记录此时只要单击需要筛选字段的筛选箭头，然后从下拉菜单中直接单击选中特定值即可。对同一个字段进行逻辑“与”和逻辑“或”运算此时只要单击需要筛选字段的筛选箭头，然后从下拉菜单中选择不同的筛选条件，或者“自定义筛选”命令菜单，在弹出的“自定义自动筛选方式”对话框中进行设置即可。在不同字段间进行逻辑“与”运算进行多次自动筛选或者自定义筛选即可。对于数值型字段，可以筛选出最大(小)的若干个(若干百分比)记录要单击需要筛选数值字段的筛选箭头，从下拉菜单中单击“数字筛选”命令菜单，然后再从出现的子菜单中选择“10个最大的值”，最后在弹出的“自动筛选前10个”对话框中进行设置即可。,（3）高级筛选,自动筛选无法实现多个字段间的“或”运算，并且它将筛选出来的记录，仍然显示在原来的表格中，而不能使其显示到一个新的地方。要解决上述问题，就需要高级筛选。如果要进行高级筛选，首先必须在该数据库表格上方设置筛选条件区域。,操作练习：数据的筛选,第二讲统计数据的透视分析,1、数据透视表,所谓对统计数据进行透视分析，就是从统计数据库表格的特定字段中概括信息，从而可以方便地从多个角度来查看、分析、汇总、统计数据，并帮助统计人员快速做出决策。在Excel中，实现数据透视分析的工具是数据透视表和数据透视图。其中，数据透视表用来对大量的统计数据进行快速汇总和建立交叉列表，本次概括性地介绍数据透视表的基本操作。,操作练习：用户明细1,1.1.1、数据透视表的创建1,操作步骤如下：(1)选取表格中的任一个单元格，或将插入点放在该表的任一单元格中。(2)单击“插入”“表”数据透视表”按钮右侧的箭头，从弹出的下拉列表中，单击“数据透视表”选项，出现“创建数据透视表”对话框。在本对话框中，可以设置创建数据透视表的数据源和需要将创建的数据透视表放置的位置。(3)单击“确定”按钮，系统将自动插入“Sheet2”工作表。(4)“数据透视表字段列表”任务窗格中的“选择要添加到报表的字段”区域，选择需要添加到数据透视表的字段，可选中“开户时间”、“发展渠道”、“号码”复选框，并通过拖动分别将它们放置到“报表筛选”、“行标签”、“列标签”和“数值”区域中,(5)添加了相应字段后，创建的数据透视表。在表格的各个项目下面有项目汇总信息，在最右边和最下边还给出了汇总的数据(6)在透视表中先后选择了“年”和“月”两个字段作为列标签，所以它们进行了分层次的显示，当单击对应“年”前面的“-”符号时，可以将其后面的具体客户名称折叠隐藏。通过单击所有业务员前面的“”符号，将所有月份折叠隐藏如果再单击“+”符号，还可以再将每年各月的数据详细地显示出来,1.1.2、数据透视表的创建2,1.2、创建数据透视表的注意事项,1注意数据表格的格式制作数据透视表的数据表格，需要是数据库格式的表格，否则无法得到相应结果2创建之前定好光标的位置在制作数据透视表之前，将光标置于数据区域中间某一单元格中，这样系统就能够智能化地选取数据源所在的整个区域，省去选取操作步骤,1.2、创建数据透视表的注意事项,3注意数据透视表的组成部分一个完整的数据透视表，是由以下几个不同的区域组成的，要学会合理地进行设置页字段：数据透视表中被指定为页方向的源数据库中的字段，供筛选用。行字段：数据透视表中被指定为行方向的源数据库中的字段，可以有多个。列字段：数据透视表中被指定为列方向的源数据库中的字段，可以有多个。数据字段：含数据的源数据库中的字段项。,1.2、创建数据透视表的注意事项,4拖动字段的过程是可逆的可根据需要将相应的字段拖动到不同的位置。这个操作过程是可逆的，可以将拖动错误的内容撤销掉5合理拖动字段的位置各个字段的拖动位置，与数据分析的目的关系密切，这直接决定了将来制作出来的数据透视表样式。,1.2、创建数据透视表的注意事项,6可以对数据作一定的筛选根据数据透视表，可以进行一定的数据筛选。操作时，单击相关字段右侧箭头，从下拉列表选择需要筛选的内容即可。7可以对分析的结果进行排序在数据透视表中，可以对相关字段进行排序。例如对按照用户数，进行升序排列后的显示效果,1.2、创建数据透视表的注意事项,8可以修改数据的汇总方式在数据透视表中，可以修改数据的汇总方式。操作时，在单元格A3(其中包含了“求和项：合计金额”内容)上单击鼠标右键，从弹出的快捷菜单中选择“数据汇总依据”菜单项，然后从“数据汇总依据”的子菜单中，将当前的方式由“求和”修改为“计数”即可。,1.2、创建数据透视表的注意事项,9可以修改数据的显示形式在数据透视表中，可以修改数据的显示形式。操作方法为：在单元格上单击鼠标右键，从弹出的快捷菜单中单击“值字段设置”菜单项，弹出“值字段设置”对话框，选择其中的“占总和的百分比”，然后单击“确定”按钮，即可得到按“占总和百分比”方式显示数据的数据透视表效果。,1.3、在数据透视表中进行自定义计算,用户明细创建的一个数据透视表，请按照“话费分成佣金=本月出帐*8”的规则，在该数据透视表中添加“话费分成佣金”字段并求和。问题分析：此处题目已经给定了话费分成佣金的计算公式，可以根据该公式在数据透视表中增加一个由自定义公式创建的计算字段。具体操作步骤如下：,1.3、在数据透视表中进行自定义计算,(1)单击图数据透视表的任一单元格。(2)单击“数据透视表工具”“公式”按钮下面的箭头，从下拉菜单中选择“计算字段”项，弹出“插入计算字段”对话框。(3)在“插入计算字段”对话框中的“名称”框内输入“话费分成佣金”；然后将光标定位到“公式”框中，清除掉原有的“=0”，双击“字段”列表框中的“本月出帐”，最后在“公式”框中的“=本月出帐”后面输入“*0.08”。(4)单击“添加”按钮，则“提成金额”计算字段被添加到“字段”列表框中，此时，单击“字段”列表框中的“提成金额”，利用上面的“修改”和“删除”命令按钮，还可以对该计算字段进行修改，或者删除操作。(5)单击“确定”按钮，包含“提成金额”字段的透视表则制作完毕。,2、数据透视图的创建与编辑,数据透视图以图形形式表示数据透视表中的数据，它有一个使用相应布局的关联数据透视表，二者中的字段相互对应。在数据透视表中调整数据，可以引起数据透视图的相应变化，用户也可以直接更改数据透视图的布局，编辑其数据，或者设置其格式。如果更改了数据透视图的字段位置，数据透视表也将改变。,2.1、数据透视图的两种创建方法,创建数据透视图有两种方法，一种是根据已经作好的数据透视表来创建数据透视图；第二种方法是直接根据数据表来创建数据透视图利用的销售情况数据表，制作好的数据透视表，请根据该数据透视表创建一个相应的数据透视图。问题分析：这里是利用已有的数据透视表来创建数据透视图。操作步骤如下：(1)单击已有的数据透视表。(2)单击“插入”“图表柱形图”(或者其他需要的图表类型)按钮右侧的箭头，从弹出的子图表类型列表中，单击选择一种图表类型。,2.1、数据透视图的两种创建方法,(3)工作表中则生成一个数据透视图，并在工作表右侧弹出一个“数据透视表筛选窗格”；同时，在原功能选项卡的上面增加了一个“数据透视图工具”选项卡，其下面包括“设计”、“布局”、“格式”和“分析”4个标签。(4)利用“数据透视表筛选窗格”，可以调整数据透视图的布局，利用“数据透视图工具”选项卡中对应各个标签中的命令，可以对数据透视图进行优化设置，其操作方法与一般数据图表的操作方法一样。,第三讲函数,1、公式、函数及名称,常用的基本函数：Sum逻辑函数：IF统计函数：Sumif和Countif、Average、MAX、MIN查找函数：Vlookup文本函数：Value、Text、Concatenate（可用“&”快捷健）、Left、Right、MIDB；排序函数：RANK,2.1、利用SUMIF函数实现单条件汇总,SUMIF函数的功能是根据指定条件对若干单元格求和，其语法格式如下：SUMIF(条件判断区域，条件，求和区域)该函数需要三个参数，其中的“条件”在形式上可以是数字、表达式或者文本，例如条件可以表达为：100，“8000”，“笔记本电脑”(注意其中的引号，需要是英文引号)等。,操作练习：指定条件统计,2.2、利用SUMIF函数实现单条件汇总举例,某家电销售公司2009年2月份前20天的产品销售记录流水账。而H1单元格中的数值，是根据SUMIF函数汇总出的洗衣机总销售额，H6：H10单元格区域是根据SUMIF函数汇总出的每个业务员的个人总销售额。问题分析：本例的汇总属于单条件汇总，用SUMIF函数可完成任务。,2.3、利用SUMIF函数实现单条件汇总举例,操作步骤如下：(1)按照设计表格，并输入相关数据和文字(2)在H2单元格中输入如下公式，求洗衣机总销售额=SUMIF(D2：D21,洗衣机,E2：E21)(3)在H6单元格中输入如下公式，求出“张姗姗”的总销售额。=SUMIF($C$2：$C$21,G6,$E$2：$E$21)(4)选定H6单元格，向下一直拖动到H10单元格，求出所有人员的总销售额。,2.4、利用SUMIF函数实现单条件汇总举例,3.1、利用SUM和IF函数联合实现多条件汇总,某电脑公司的销售记录清单，现在假如需要统计汇总：(1)2009年1月中旬的销售额；(2)2009年1月上旬或2月份以后的销售额。问题分析：对于以上两种数据的统计汇总，都包含两个条件，所以单纯应用SUMIF函数已经无法解决，下面通过使用SUM和IF函数的组合来解决这个问题。,3.2、利用SUM和IF函数联合实现多条件汇总,操作步骤如下：(1)设计表格，并输入相关数据和文字。(2)将光标定位到E20单元格，输入如下公式：=SUM(IF(B2：B17=DATEVALUE(2009-1-11)*(B2：B17=DATEVALUE(2009-2-1),E2：E17)(5)按下(Ctrl+Shift+Enter)组合键，构造数组公式，求出2009年1月上旬，或2月份以后的销售额。,3.3、利用SUM和IF函数联合实现多条件汇总,说明：（1）这里的两个公式中，IF函数都包括了两个参数，他们之间用“*”连接的表示二者之间是“与”的关系，也就是两个条件都必须同时满足才能进行求和；用“+”连接的表示二者之间是“或”的关系，也就是两个条件只要满足其中的一个就可以进行求和运算，“”和“+”的选取要根据实际需要而定。(2)在上述公式中，要注意日期的使用方法：其中DATEVALUE函数的功能是将以文本表示的日期转换为一个序列号数字。(3)使用SUM和IF函数组合进行多条件求和时，必须按照数组公式输入。所以，在公式输完后，不要忘记按下(Ctrl+Shift+Enter组合键，否则将返回错误值“#VALUE。,3.4、利用SUM和IF函数联合实现多条件汇总,4、利用SUMIFS函数实现多重条件的数据汇总,SUMIFS函数是Excel2007中新增的一个特色的统计汇总函数，它解决了SUNIF函数只能设置一个条件的局限性，而是可以设置多个不同的条件。其语法格式如下：SUMIFS(求和区域，条件区域1，条件1，条件区域2，条件2)，其中：求和区域进行求和的单元格区域，包括数字或包含数字的名称、数组或引用。条件区域1，条件区域2，是计算关联条件的区域，最多可以设置127个。条件1，条件2，是数字、表达式、单元格引用或文本形式的条件，要与前面的条件区域相对应，最多可以设置127个，格式要求与SUMIF函数的一样。,4.1、利用SUMIFS函数实现多重条件的数据汇总,某公司工程项目部人员的薪资基本数据。现在要求在J28单元格中汇总出“第1项目组中，工龄在20年以上的男工程师基本工资总额”。问题分析：这里要汇总“第1项目组中工龄在20年以上的男工程师基本工资总额”，其中包括了4个不同的条件(项目组、性别、职称、工龄)，用SUMIF显示无法解决，SUM与IF函数组合，公式都较复杂。对于这种有多个条件，并且多个条件之间是逻辑“与”关系的情况，利用SUMIFS函数可方便地解决。,4.2、利用SUMIFS函数实现多重条件的数据汇总,本例中，其实只要在J28单元格中输入如下公式即可：=SUMIFS(H2：H26,C2：C26,第1项目组，D2：D26，男,E2：E26,工程师，F2：F26，20),4.3、利用SUMIFS函数实现多重条件的数据汇总,SUMIFS函数使用时，需要注意以下几个事项：(1)只有当求和区域中的每一单元格满足为其指定的所有关联条件时，才对这些单元格进行求和，也就是说各个条件之间是严格的逻辑“与”关系。(2)求和区域中包含TRUE的单元格计算为1，包含FALSE的计算为0。(3)与SUMIF函数中的条件区域和条件参数不同，SUMIFS中每个条件区域的大小和形状必须与求和区完全相同。(4)SUMIFS和SUMIF的参数顺序不同。具体而讲，“求和区域”参数在SUMIFS中是第一个参数，而在SUMIF中则是第三个参数,第四讲EXCEL图表制作,1、常用统计图表类型的样式与作用,在各种统计分析活动中，使用较多的统计图表类型有柱形图、条形图、折线图、饼图、面积图、圆环图、XY散点图、雷达图、气泡图等几种，下面分别举例说明各自作用。,11、柱形图,柱形图用于显示某段时间内数据的变化，或比较各数据项之问的差异。其分类在水平方向组织(一般为日期或时间)，而数值在垂直方向组织，以便强调相对于时间的变化。另外，在实际管理应用中，柱形图还有一些变形，例如：堆积柱形图显示了单个数据项与整体的关系，三维透视的柱形图可比较两个坐标轴上的数据点等。,1.2、条形图,条形图也用于显示各数据之问的比较。与柱形图不同的是，其分类在垂直方向，而数值在水平方向，以使观察者的注意力集中在数值的比较上，而不在时间上。另外，堆积条形图显示了单个数据与整体的关系，可以把不同项目之间的关系描述得更清楚。,1.3、折线图,折线图主要用于显示各数据随时间而发生变化的趋势情况。折线图的横坐标几乎总是表现为时间，比如：年份、季度、月份、日期等。折线图中的子图表类型也很多，有带(或者不带)数据标记的折线图表、堆积数据点折线图，以及三维折线图表等不同类型。,1.4、饼图,饼图用于显示组成数据系列的各数据项与数据项总和的比例。当只有一个数据系列，并且用于强调整体中的各个组成部分占整体的百分比时，使用饼图就最为合适。在饼图中，要使小扇区更容易查看，还可将它们组织为饼状图中的一个数据项，然后将该数据项在主图表旁边的小饼状图或小条形图中拆分显示，这就是复合饼图。,1.5、面积图,面积图用于显示不同数据系列之间的对比关系，它可以强调数量随时间而变化的程度，也可用于引起人们对总值趋势的注意；同时，通过显示所绘制的值的总和，它也能显示数据与整体的关系。,1.6、XY散点图,XY散点图可用于显示若干数据系列中各数值之间的关系。散点图有两个数值轴，沿水平轴方向显示一组数值数据，沿垂直轴方向显示另一组数值数据。散点图将这些数值合并到单一数据点上显示。它通常用于科学实验、相关分析、回归分析等。,1.7、圆环图,圆环图用于显示若干数据系列中各单个数据与数据汇总之间的关系，而前面介绍的饼图只能显示一个系列。如图所示，就用来显示男女不同职称教师比例的圆环图。,1.8、气泡图,气泡图与XY散点图功能类似，但是XY散点图只能表示两个变量之间(x值和y值)的关系，而气泡图可以表示三个变量之间(x值、y值和气泡大小)的关系。制作气泡图的时候，需要在数据表上第一列中列出x值，在相邻列中列出相应的y值和气泡大小的值。,2、复杂统计图表的制作-组合图表的制作,在实际统计分析中，Excel2007中的标准图表有时无法满足要求，需要根据实际情况，来自己制作一些格式要求比较复杂的统计图表。本次介绍几种复杂统计图表的制作。组合图表就是在一个统计图表中表示两个甚至多个数据系列，而且不同的数据系列用不同的图表类型来表示。利用组合图能够很方便地比较多个数据系列之间的异同。例如：根据数据表数据制作的一个“柱形一折线”组合数据图表，其中：数据系列“预计”显示的为一个柱形图，而另一个数据系列“实际”显示的则为一个折线图；从中可清晰地看出“预计”与“实际”数值的差别。,操作练习：复杂统计图表组合图示例,2.2、复杂统计图表的制作-组合图表的制作,请根据表格中的三行数据，制作一个“柱形一折线一面积”组合图表，三行数据分别采用对应的不同图表类型。问题分析：上面的组合图表中包含了三种图表类型，而利用标准图表类型显然无法一次做好(虽然以前的Excel2003“自定义图表类型”提供了组合图，但是也只有“柱形一折线”双组合图表)。为此，考虑先将三种数据系列全部做成一种图表类型，然后再修改数据系列对应的图表类型，最终得到需要的“柱形一折线一面积”组合图表。,操作练习：复杂统计图表改造组合图,2.3、复杂统计图表的制作-双轴图表的制作,所谓双轴图表。是指在一个统计图表中，在主、次坐标轴上分别绘制一个或者多个数据系列，它们可以采用两种不同的坐标值来度量数据。当一个数据图表涉及多个数据系列，并且不同数据系列的数值相差悬殊时，比较合理的图表方案就是建立双轴图表。,操作练习：复杂统计图表双轴图,2.4、复杂统计图表的制作-复合条饼图的制作,利用复合条饼图，可以对数据做更深层次的分析。下面通过实例来讲解其制作步骤。数据表为“净爽牌”洗衣机的某一批产品，在售出一年后对客户维修的情况反馈，其中包括了换货、无修理以及修理(包括次数的统计)。现在，请根据该数据表制作一个饼图，以便对比换货、无修理与修理三种情况之间的比例关系，同时对修理次数之间的对比关系也能够清晰地看到。,2.4、复杂统计图表的制作-复合条饼图的制作,问题分析：根据上面的数据表，可以快速将其制作成一般的饼图五种情况列在一个表格中，数据对比不很清晰：没有按照题目的要求显示出已经更换过、没有修理过、已经修理过三种情况之间的比例关系。另外，修理次数之间的比例关系在图中查看也不方便。为此，需要根据原数据表，来制作复合条饼图。,2.4、复杂统计图表的制作-复合条饼图的制作,根据以上分析，下面将根据数据表制作复合条饼图。具体操作步骤如下：(1)选中数据表的A2：B7单元格区域，然后单击“插入”“图表”“饼图”按钮下边的箭头，找寻其中的“复合条饼图”按钮(2)单击“复合条饼图”按钮后，很快就创建好一个复合条饼图(3)建立的默认图表并未达到目的。下面先增加数据标签。操作时，在主饼图上单击鼠标右键，从快捷菜单上运行“添加数据标签”，为图表加上数据标签,2.4、复杂统计图表的制作-复合条饼图的制作,(4)上面添加的数据标签是“值”，并不是题目所需要的“百分比”。为此，需要设置数据标签的格式，操作时，在主饼图上单击鼠标右键，从快捷菜单上运行“设置数据标签格式”选项，在随后出现的“设置数据标签格式”对话框上，选择“系列选项”选项卡，将其中的“百分比”和“显示引导线”复选框选上，取消选中“值”复选框，其余保持默认的设置。本步骤结束后，得到复合条饼图效果。(5)本例中需要把表格中的最后3行，也就是对应修理次数的3行设置到堆积条形图中，但是默认的图表只将最后2行放到其中，为此需要重新设置。操作方法为：在主饼图上单击鼠标右键，从快捷菜单上运行“设置数据系列格式”选项，在随后出现的“设置数据系列格式”对话框上，选择“系列选项”选项卡，将“第二绘图区包含最后一个”的值在微调按钮中，由默认的2调整为3。,结束！,
展开阅读全文