EXCEL实训案例与操作步骤.doc

Excel实训案例与操作步骤2015.09目 录一、函数11. sum函数12. max函数13. min函数14. if函数25. sumif函数36. sumproduct函数37. vlookup函数48. countif函数6二、基本操作与数据处理8（一）数据有效性81、输入序列数据82、输入指定区间的数据8（二）条件格式91、挑选重复数据92、突出显示最大值与最小值103、图标集与数据条的使用10（三）筛选121、多条件高级筛选122、利用列表（表）实现高效筛选12三、数据透视表14（一）制作基本的数据透视表14（二）利用多重数据区域制作数据透视表18（三）运用数据透视表进行表格数据对比分析21（四）动态数据透视表制作23（五）通过自定义计算字段进行统计分析25四、图表制作与美化28（一）柱形图28（二）折线图32（三）饼图34一、函数主要介绍如下函数：max min sum if sumif sumproduct vlookup countif1. sum函数功能：计算单元格区域中所有数值的和语法：=sum(number1,number2,)“number1,number2,”为需要求和的参数。参数可以是数值、文本、逻辑值和单元格引用。单元格引用如果是空单元格，那么该单元格引用将被忽略。2. max函数功能：返回一组值中的最大值语法：=max(number1,number2,)number1, number2, number1 是必需的，后续数值是可选的。3. min函数功能：返回一组值中的最小值语法：=min(number1,number2,)number1, number2, number1 是必需的，后续数值是可选的。例1.1财务工作中常用函数：化工集团含若干分工厂，2014年各月利润如表所示，需求出各工厂年度利润合计、各月最大利润数与各月最小利润数。则需在N3单元格中输入=SUM(B3:M3)在O3单元格中输入=MAX(B3:M3)在P3单元格中输入=MIN(B3:M3)再将N3至P3单元格选中，下拉填充柄，即将公式填充至下一行可得出下表结果：4. if函数功能：判断一个条件是否满足，如果满足返回一个值，如果不满足则返回另一个值。语法：=if(logical_test,value_if_true,value_if_false)其中第一个参数logical_test为任何一个可判断为true或false的数值或表达式。第二个参数value_if_true为logical_test为true时函数的返回值，可以是某一个公式。如果value_if_true省略，当logical_test为真时，函数返回TRUE值。第三个参数为logical_test为假时的返回值，当该参数省略同时logical_test为假时，函数返回的值为false。If函数可以嵌套，最多可以嵌套7层。例1.1企业在应收账款管理过程中，需及时对到期的应收账款进行偿还提醒，故需筛选出当前日期已到期的应收客户及应收金额。如下表所示为及时筛选出到期客户清单，需在F列显示出其到期状态，可通过IF函数来实现。在F4单元格输入函数: =IF(E4B2,到期,未到期)。表示的意思是，当E4单元格的到期日小于当前日期，则F4单元格显示“到期”，否则，则显示“未到期”。进一步，若将函数中的B2代表的当前日期固定，也即，将公式改为=IF(E4=6000)在R7单元格中输入公式：=COUNTIF($O$2:$O$21,=4000)-COUNTIF($O$2:$O$21,=6000)在R8单元格中输入公式：=COUNTIF($O$2:$O$21,=2000)-COUNTIF($O$2:$O$21,=4000)在R9单元格中输入公式： =COUNTIF($O$2:$O$21,2000)得到如下结果：其他常见小函数：today row column text mid left right二、基本操作与数据处理（一）数据有效性数据有效性是对单元格设置的一个规则，只有满足这个规则的数据才能输入到单元格。1、输入序列数据在很多情况下，经常要输入一些重复的数据，比如要在员工信息表的某列输入该员工所属部门名称，而这些部门名称总是那么几个，此时，利用数据有效性，不仅可以实现部门名称的快速输入，也可以防止输入错误的部门名称。例2.1，如下左表，一共有三个部门，服装部、家电部、食品部，当单击B2单元格时，出现下拉箭头，就可以选择输入该序列的某个项目。 操作步骤：选中B2至B7单元格区域，单击“数据”选项卡中的“数据有效性”数据有效性， 2、输入指定区间的数据例2.1：要求输入员工年龄时，年龄区间范围为20-60之间。一旦输入非区间内数值，将提示报错。操作步骤：选中E2至E7单元格，单击“数据”选项卡中的“数据有效性”数据有效性，设置有效性条件为允许整数，介于最小值20与最大值60之间，同时出错警告输入错误提示。 当E2单元格输入66时，则会弹出对话框如下：（二）条件格式“开始”选项卡“条件格式”1、挑选重复数据例2.2 使重复的名字突出显示 操作步骤：选中A2至A19单元格区域，依次点击 条件格式突出显示单元格规则重复值2、突出显示最大值与最小值例2.2 以上工资表为例，要求突出显示“工资”列中最大工资与最小工资，以红色填充最大工资，以绿色填充最小工资。操作步骤：（1）选中F2至F19单元格区域，依次点击 条件格式项目选取规则值最大的10项。将左边的10改为1，右边的设置中点击自定义格式，选择填充，红色，即可。（2）选中F2至F19单元格区域，依次点击 条件格式项目选取规则值最小的10项。方法同理。3、图标集与数据条的使用（1）图标集例2.2 如下表，给下表中工资数据加上图标集，其中大于7000，5000至7000之间，小于5000分别标记上不同的图标。操作步骤：1）选中F2-F19，条件格式图标集标记（第一行第二项）2）选中F2-F19，条件格式管理规则编辑规则，将类型从“百分比”改为“数字”，值依次输入7000，5000，确定即可。 （2）数据条如果不想用图标集，想更直观的看到每位员工的工资差距，可尝试使用数据条。操作步骤：选中F2-F19单元格，点击条件格式数据条选择任意一种颜色即可。 （三）筛选筛选是EXCEL的一个最常用的数据分析功能，很多人都会使用数据筛选功能。不仅可以筛选多个条件，还可对日期、时间数据进行特殊的筛选，以及按照颜色来进行筛选。1、多条件高级筛选例2.3，对于下表数据，要把满足2012年7月，华北地区，销售额大于500的数据筛选出：操作步骤： 选中数据清单的区域，单击：开始排序和筛选筛选 依次选择订购日期的“2012年7月”，销售地区的“华北”，销售额“数字筛选”大于输入数字500 2、利用列表（表）实现高效筛选尽管自动筛选非常有用，但是无法自动扩展筛选区域，如果数据区域右侧增加几列数据，这几列数据是不能已经建立的筛选区域中的，如果要把这几列数据也建立筛选，需要先取消筛选，然后再建立自动筛选。如此表，如在表右侧加一列“运货商”，则需取消筛选再重新选择新区域再进行筛选。但通过创建表的形式自动扩展筛选区域。操作步骤： 将光标确定于数据区域内任意单元格 点击“插入”“表格” 在“销售额”右侧增加一列“运货商”，则此列自动进入筛选区域三、数据透视表（一）制作基本的数据透视表首先保证数据源是一个数据清单单击数据清单中的任一非空单元格，单击“插入”选项卡，再单击功能区最左边“数据透视表”“数据透视表” 在默认情况下，系统自动将选取整个数据清单作为数据源，如果数据源区域需要修改，则可直接在“选择一个表或区域”输入栏中重新输入数据区域。确定数据源后，单击“确定”按钮，EXCEL将自动新建新工作表，并在此工作表上创建空白的数据透视表。 “报表筛选”用于添加报表筛选字段，可以用鼠标把字段列表区域窗格内的某个字段或其他小窗格内的字段拖放到此窗格内，创建筛选字段。“列标签”区域，用于添加列字段，可以用鼠标把字段列表区域窗格内的某个字段或其他小窗格内的字段拖放到此窗格内，创建列字段。“行标签”用于添加行字段，可以用鼠标把字段列表区域窗格内的某个字段或者其他小窗格内的字段拖放到此窗格内，创建行字段。“数值”用于添加汇总计算的字段，可以用鼠标把字段列表区域窗格内的某个字段或其他小窗格内的字段拖放到此窗格内，创建值字段。例3.1：根据左表的销售记录数据，需统计出在不同销售渠道下，各地区不同类别商品的销售额合计，也即右表所示。 操作步骤： 选中原数据清单，插入数据透视表 将“渠道”字段拖至“报表筛选”区域，将“城市”字段拖至“行标签”区域，将“类别”字段拖至“列标签”区域，将需要汇总计算的字段“销售额”拖至“数值”区域。注：1. 如若不需要数据透视表最右列“总计”字段，可将单元格定位在“总计”，右键点击“删除总计”，后如欲恢复，可在鼠标定位在数据透视表任意单元格，右键“数据透视表选项”，选择“汇总和筛选”，重新勾选上“显示行总计”2. 本例中，数值汇总方式为求和，但也可以通过左键点击“数值”区域的“销售额”字段，选择“值字段设置”，可修改为以平均值方式或计数方式显示。例3.1：以例1中原数据为数据源，需统计出在各时间段，各地区不同类别商品的销售额合计，也即右表所示。操作步骤： 选中原数据清单，插入数据透视表 将“渠道”字段拖至“报表筛选”区域，将“日期”字段拖至“行标签”区域，将“类别”字段拖至“列标签”区域，将需要汇总计算的字段“销售额”拖至“数值”区域。 鼠标单击行标签区域内任一单元格，右键选择“创建组”，选中“月”和“年” 鼠标定位在数据透视表表格区域，“设计”选项卡，“报表布局”“以表格形式显示”，进一步点击“报表布局”“重复所有项目标签”可得。例3.2：应收账款账龄分析：要求根据提供的应收账款清单，分析富华食品、尚展食品、天宝食品、裕庆食品这几家客户各不同逾期天数段中，应收账款逾期的金额合计。操作步骤： 选中A3至K43单元格区域，插入“数据透视表”，设置行标签为“公司名称”字段，列标签为“逾期天数”字段，求和项为“未收金额”字段，得到如下数据透视表。 但因这样的统计仍然不够直观清晰，故需进一步将逾期天数进行分类。鼠标定位在工作表第四行数据透视表内上除了A4之外，比如“46”、“49”、“54”中任意一单元格，右键选择“创建组”，使得逾期天数以起始于“1”，终止于“270”，步长也即间隔为“60”的形式显示。 进一步修改行标签和列标签名称可得到：（二）利用多重数据区域制作数据透视表例3.3：某公司有三个公司，三个公司2014年各税种缴纳情况如下表：（单位：万元）， 操作步骤： 依次按下ALT与D，同时松开后再按键盘上的P字母，出现如下对话框，调出数据透视表向导，选择“多重合并计算数据区域”，下一步，选择“自定义页字段”， 选定区域，点击图示红色圆圈内按钮，选择“广州分公司”数据清单区域，设页字段数目为1，命名为“广州分公司”，后点击“添加” 继续重复同一动作，将上海分公司和北京分公司分别添加进区域，并分别设页字段数目为1，分别命名为“上海分公司”和“北京分公司”。 单击“下一步”，继续点击“完成”。 新工作表中即形成汇总后的数据透视表，并且可以通过页1字段的选择，分别显示广州分公司、上海分公司和北京分公司的数据。可将“页1”直接编辑为“分公司” 通过对字段在“报表筛选”区域、“列标签”区域、“行标签”区域的拖动，可实现各种形式表格的变换，如下图所示： （三）运用数据透视表进行表格数据对比分析例3.4：运用数据透视表进行银行对账如下左表为企业内部的银行存款日记账，右表为银行对账单记录。现在要进行银行对账，找出两个工作表中不一致的数据。 操作步骤： 新建工作表，将两个工作表进行合并（手工合并，将银行对账单的记录按照对应的字段粘贴于企业银行存款日记账记录下方），合并的时候注意，银行对账单记录中的贷方金额应粘贴在新工作表借方，银行对账单记录中的借方金额应粘贴在新工作表贷方，另外，在最右方加一列“单位”字段，注明是企业记录与银行记录以便区分。 根据新工作表创建数据透视表，选择“贷方”为行标签，“单位”为列标签，求和项为“贷方”。 连续选中数据透视表中B5至C16单元格，设置条件格式，使得左右不一致的记录填充为红色，具体操作为选择“开始选项卡”条件格式新建规则使用公式确定要设置格式的单元格，并在空白方框中输入公式：=$B5$C5。设置格式为填充为红色，点击“确定”。 经上一步骤后，数据透视表便将企业银行存款日记账与银行对账单中贷方不一致的记录突出显示出来，如下图所示 用同样的方法，亦可将借方记录中企业银行存款日记账与银行对账单中不一致的记录突出显示。（操作方法为可将上一步骤中的数据透视表选中，复制到A22单元格，将行标签改为“借方”，求和项改为“借方”） （四）动态数据透视表制作例3.5：根据原始数据（下方左图），运用数据透视表得出白米、白奶酪、饼干三种类别产品在各月的销售额合计（下方右图）。 操作步骤参考“（一）制作基本的数据透视表”（注：可通过列标签的筛选功能只选择白米、白奶酪、饼干这三种产品）此时，如若在原数据表格底下添加4月5月6月的销售数据，数据透视表又需重新选择数据区域，重新制作才能更新数据透视表中的数据，此时可以通过“创建表”的方式制作动态，不需要重新选择数据源即可以更新的数据透视表。操作步骤： 单击数据源表格中任一单元格，点击“插入”选项卡中“表格” 则原数据表格呈如下样式，此时再根据此表新建数据透视表。 在原数据表格下方添加2015年4月、5月、6月的数据。 在第二步中生成的数据透视表中，定位任意单元格，右键“刷新”，即可更新数据透视表中的数据（五）通过自定义计算字段进行统计分析当EXCEL提供的已有的汇总计算以及自定义显示方式不能满足需要时，EXCEL还允许向数据透视表添加自定义计算字段，也就是为数据透视表添加新的计算指标。计算字段是通过对表中现有的字段执行计算后得到的新的字段，一旦创建了自定义字段或定义项，EXCEL就允许在报表中使用他们，就像他们是数据源的一部分那样。例3.6：原数据为采购部、人事部各月份预算费用额与实际费用额，需要对各费用类别进行归集统计分析实际发生额与预算额的差异及差异率。形成右图所示数据透视表操作步骤： 根据数据透视表制作的基本步骤，制作出如下表格： 将光标定位在“求和项-实际发生额”单元格，点击“选项”选项卡，选择“域、项目和集”“计算字段”。 在弹出对话框中名称中输入“差异额”，公式= 实际发生额-预算额（实际发生额与预算额是通过分别双击字段列表中的名称得来的），点击确定。 通过同样的方法与步骤，继续添加“差异率”字段。（差异率=差异额/预算额） 通过“条件格式”中的“图标集”设置差异额和差异率两列中，大于0的数字前显示向上箭头，小于0的数字前显示向下箭头。四、图表制作与美化应用EXCEL图表不仅可以清晰地显示数据间的差异，而且可以找到数据内的逻辑关系，找出数据的变化趋势，据以做出合理的预测。设计完美的图表与枯燥的数据清单相比，更能迅速有力地传递数据。常用图表类型及用途：柱形图：用于显示一段时期内数据的变化或者各项之间的比较关系折线图：用于显示数据之间的变化趋势饼图：用于显示数据系列中各项占总体的比例关系，注意饼图只显示一个数据系列（一）柱形图1.基本柱形图例4.1某公司2013-2014年四个季度的净利润数据如表所示，请据此表绘制柱形图操作步骤： 选取数据范围。利用鼠标连续选中工作表中该数据所在的区域。选中A2至E4区域。 选择图表类型。选择“插入”选项卡，“柱形图” 柱形图的子类别分别有二维柱形图，三维柱形图等，本例选择二维柱形图中的第一个。 在原电子表格右下角生成了该数据的柱形图表格 运用“图表工具”。选中表格，显示图表工具，分别是“设计”、“布局”、“格式”三个选项卡。通过“设计”选项卡，切换行/列数据，改变图表样式，重新选择数据等通过“布局”选项卡，可以设置图表标题、图例、数据标签、坐标轴、网格线等。 增加例中图表标题、调整图例位置，及不显示网络线，可得下图：2.动态柱形图当用户需要查看多个地区不同月份销售额时，如果采用柱形图，则需绘制多个柱形图，不便查看，通过创建下拉菜单式的动态图表，可显示不同地区的销售数量。操作步骤（例4.1）： 因为动态图表中涉及到下拉组合框，因此需在EXCEL中添加“开发工具”选项卡，具体做法为：文件选项自定义功能区，将开发工具勾选上之后，即可 在A11单元格中输入数值“1”，在B11单元格中输入=INDEX(B4:B9,$A$11)，按回车键后向右复制公式至G11单元格中，得到上海地区各月的销量。如图所示 选择需创建图表的区域，这里选择B11至G11单元格区域，在“插入”选项卡下单击“柱形图”第一种类型。 出现柱形图后，选中右键点击“选择数据”，单击水平分类轴标签，编辑，选择轴标签区域，用鼠标选中B3至G3单元格，则柱形图水平轴即变成月份。 删除图例和网络线，添加图表标题，并修改为“各部门销售业绩表”。 在“开发工具”选项卡中选择“插入”，选择表单控件中的第二项“组合框”，并在柱形图空白位置画出。 右键点击组合框，选择“设置控件格式”，在数据源区域中选择A4至A9单元格区域，单元格链接选择A11单元格（注，这些操作不需要通过键盘输入，而是通过方框右边的选项按钮与鼠标的配合完成），勾选三维阴影，即可完成。 为柱形图添加数据标签，可任意选择组合框中的各地区选项，即可出现对应地区上半年销售数量柱形图。（二）折线图EXCEL电子表格可以对图表中的图表项进行修改。例如，调整各图表项字体的大小和背景色等，用户还可以将某一系列的数据由柱形图改为折线图。将例4.1中已完成的柱线图选中，右键选择“更改图表类型”，选择“折线图”，第四种类型。 分别选中两条折线，添加数据标签并手动调整标签位置后，图表变为如下所示：注：在某些情况下，可以对折线图添加“趋势线”，方法：图表工具布局趋势线。并可根据趋势线对未来进行预测。 选择“趋势线”“线性趋势线”，为2014年净利润添加趋势线 选中趋势线，右键点击“设置趋势线格式”，调整趋势线颜色为红色，并前推一周期。（三）饼图1.基本饼形图例4.2：A公司的股本构成情况如下：中国大洋食品开发集团占总股本49%，长岛食品研究所占总股本21%，企业高管层人员赵正华占总股本15%，钱小红占总股本49%，孙伟占总股本5%，请绘制饼形图，描述该公司的股本结构。操作步骤： 连续选中B2至G3单元格区域，选择“插入”选项卡，图表组，单击“饼图”按钮，弹出“饼图”的子菜单，从中选择“三维饼图”的第二个，即“分离型饼图”，如图所示。 修改图表标题，并添加数据标签 2.复合饼形图例4.2：复合饼图制作企业资金构成。要求根据下表数据制作复合饼图操作步骤： 选中A2至C5单元格，“插入”选项卡，选择二维饼图，第三种“复合饼图”。出现如下右图样式。 选中所生成复合饼图中左边稍大型的饼图，右键点击“设置数据系列格式”，将第二绘图区包含最后一个值，从“1”改为“2”即可。 由此，复合饼图便生成，其中第二绘图区的小饼形图代表的是左边饼形图中的紫色区域的构成。 最后修改图表标题，调整图例位置，添加数据标签可得如下图