excel实训案例与操作步骤

《excel实训案例与操作步骤》由会员分享，可在线阅读，更多相关《excel实训案例与操作步骤（38页珍藏版）》请在金锄头文库上搜索。

1、1 Excel 实训案例与操作步骤实训案例与操作步骤 2015.09 2 目目 录录 一、函数一、函数1 1. sum 函数函数1 2. max 函数函数1 3. min 函数函数.1 4. if 函数函数.2 5. sumif 函数函数3 6. sumproduct 函数函数.3 7. vlookup 函数函数4 8. countif 函数函数.6 二、基本操作与数据处理二、基本操作与数据处理 8 （一）数据有效性（一）数据有效性 8 1、输入序列数据、输入序列数据 8 2、输入指定区间的数据、输入指定区间的数据.8 （二）条件格式（二）条件格式 9 1、挑选重复数据、挑选重复数据 9 2、

2、突出显示最大值与最小值、突出显示最大值与最小值.10 3、图标集与数据条的使用、图标集与数据条的使用.10 （三）筛选（三）筛选12 1、多条件高级筛选、多条件高级筛选 12 2、利用列表（表）实现高效筛选、利用列表（表）实现高效筛选.12 三、数据透视表三、数据透视表14 （一）制作基本的数据透视表（一）制作基本的数据透视表.14 （二）利用多重数据区域制作数据透视表（二）利用多重数据区域制作数据透视表.18 （三）运用数据透视表进行表格数据对比分析（三）运用数据透视表进行表格数据对比分析.21 （四）动态数据透视表制作（四）动态数据透视表制作.23 （五）通过自定义计算字段进行统计分析（五

3、）通过自定义计算字段进行统计分析.25 四、图表制作与美化四、图表制作与美化 28 （一）柱形图（一）柱形图28 （二）折线图（二）折线图32 （三）饼图（三）饼图34 1 一、函数一、函数 主要介绍如下函数： max min sum if sumif sumproduct vlookup countif 1. sum 函数函数 功能：计算单元格区域中所有数值的和 语法：=sum(number1,number2,) “number1,number2,”为需要求和的参数。参数可以是数值、文本、逻辑值和单 元格引用。单元格引用如果是空单元格，那么该单元格引用将被忽略。 2. max 函数函数 功能

4、：返回一组值中的最大值 语法：=max(number1,number2,) number1, number2, number1 是必需的，后续数值是可选的。 3. min 函数函数 功能：返回一组值中的最小值 语法：=min(number1,number2,) number1, number2, number1 是必需的，后续数值是可选的。 例例 1.11.1 财务工作中常用函数财务工作中常用函数：化工集团含若干分工厂，2014 年各月利润如表所示，需 求出各工厂年度利润合计、各月最大利润数与各月最小利润数。 则需 在 N3 单元格中输入=SUM(B3:M3) 在 O3 单元格中输入=MAX(

5、B3:M3) 在 P3 单元格中输入=MIN(B3:M3) 再将 N3 至 P3 单元格选中，下拉填充柄，即将公式填充至下一行 可得出下表结果： 2 4. if 函数函数 功能：判断一个条件是否满足，如果满足返回一个值，如果不满足则返回另一个值。 语法：=if(logical_test,value_if_true,value_if_false) 其中第一个参数 logical_test 为任何一个可判断为 true 或 false 的数值或表达式。第二个 参数 value_if_true 为 logical_test 为 true 时函数的返回值，可以是某一个公式。如果 value_if_tr

6、ue 省略，当 logical_test 为真时，函数返回 TRUE 值。第三个参数为 logical_test 为假时的返回值，当该参数省略同时 logical_test 为假时，函数返回的值为 false。If 函数可 以嵌套，最多可以嵌套 7 层。 例例 1.11.1 企业在应收账款管理过程中，需及时对到期的应收账款进行偿还提醒，故需筛企业在应收账款管理过程中，需及时对到期的应收账款进行偿还提醒，故需筛 选出当前日期已到期的应收客户及应收金额。如下表所示选出当前日期已到期的应收客户及应收金额。如下表所示 为及时筛选出到期客户清单，需在 F 列显示出其到期状态，可通过 IF 函数来实现。

7、在 F4 单元格输入函数: =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,“$C5。设 置格式为填充为红色，点击“确定” 。 经上一步骤后，数据透视表便将企业银行存款日记账与银行对账单中贷方不一致 的记录突出显示出来，如下图所示 24 用同样的方法，亦可将借方

8、记录中企业银行存款日记账与银行对账单中不一致的 记录突出显示。 （操作方法为可将上一步骤中的数据透视表选中，复制到 A22 单 元格，将行标签改为“借方” ，求和项改为“借方” ） （四）动态数据透视表制作（四）动态数据透视表制作 例例 3.53.5：根据原始数据（下方左图） ，运用数据透视表得出白米、白奶酪、饼干三种类 别产品在各月的销售额合计（下方右图） 。 操作步骤参考“（一）制作基本的数据透视表” （注：可通过列标签的筛选功能只选择 白米、白奶酪、饼干这三种产品） 此时，如若在原数据表格底下添加 4 月 5 月 6 月的销售数据，数据透视表又需重新选 择数据区域，重新制作才能更新数据透

9、视表中的数据，此时可以通过“创建表”的方式制 作动态，不需要重新选择数据源即可以更新的数据透视表。 操作步骤： 单击数据源表格中任一单元格，点击“插入”选项卡中“表格” 25 则原数据表格呈如下样式，此时再根据此表新建数据透视表。 在原数据表格下方添加 2015 年 4 月、5 月、6 月的数据。 在第二步中生成的数据透视表中，定位任意单元格，右键“刷新” ，即可更新数据 透视表中的数据 （五）通过自定义计算字段进行统计分析（五）通过自定义计算字段进行统计分析 当 EXCEL 提供的已有的汇总计算以及自定义显示方式不能满足需要时，EXCEL 还允许 向数据透视表添加自定义计算字段，也就是为数据

10、透视表添加新的计算指标。 计算字段是通过对表中现有的字段执行计算后得到的新的字段，一旦创建了自定义字 26 段或定义项，EXCEL 就允许在报表中使用他们，就像他们是数据源的一部分那样。 例例 3.63.6：原数据为采购部、人事部各月份预算费用额与实际费用额，需要对各费用类 别进行归集统计分析实际发生额与预算额的差异及差异率。形成右图所示数据透视表 操作步骤： 根据数据透视表制作的基本步骤，制作出如下表格： 将光标定位在“求和项-实际发生额”单元格，点击“选项”选项卡，选择“域、 项目和集”“计算字段” 。 在弹出对话框中名称中输入“差异额” ，公式= 实际发生额-预算额（实际发生额 与预算额

11、是通过分别双击字段列表中的名称得来的） ，点击确定。 27 通过同样的方法与步骤，继续添加“差异率”字段。 （差异率=差异额/预算额） 通过“条件格式”中的“图标集”设置差异额和差异率两列中，大于 0 的数字前 显示向上箭头，小于 0 的数字前显示向下箭头。 28 四、图表制作与美化四、图表制作与美化 应用 EXCEL 图表不仅可以清晰地显示数据间的差异，而且可以找到数据内的逻辑关系， 找出数据的变化趋势，据以做出合理的预测。设计完美的图表与枯燥的数据清单相比，更 能迅速有力地传递数据。 常用图表类型及用途： 柱形图：用于显示一段时期内数据的变化或者各项之间的比较关系 折线图：用于显示数据之间

12、的变化趋势 饼图：用于显示数据系列中各项占总体的比例关系，注意饼图只显示一个数据系列 （一）柱形图（一）柱形图 1.基本柱形图基本柱形图 例例 4.14.1 某公司 2013-2014 年四个季度的净利润数据如表所示，请据此表绘制柱形图 操作步骤： 选取数据范围。利用鼠标连续选中工作表中该数据所在的区域。选中 A2 至 E4 区 域。 选择图表类型。选择“插入”选项卡， “柱形图” 柱形图的子类别分别有二维柱形图，三维柱形图等，本例选择二维柱形图中的第 一个。 在原电子表格右下角生成了该数据的柱形图表格 29 运用“图表工具” 。选中表格，显示图表工具，分别是“设计” 、 “布局” 、 “格式

13、” 三个选项卡。 通过“设计”选项卡，切换行/列数据，改变图表样式，重新选择数据等 通过“布局”选项卡，可以设置图表标题、图例、数据标签、坐标轴、网格线等。 增加例中图表标题、调整图例位置，及不显示网络线，可得下图： 2.动态柱形图动态柱形图 当用户需要查看多个地区不同月份销售额时，如果采用柱形图，则需绘制多个柱形图， 不便查看，通过创建下拉菜单式的动态图表，可显示不同地区的销售数量。 操作步骤（例（例 4.14.1）： 因为动态图表中涉及到下拉组合框，因此需在 EXCEL 中添加“开发工具”选项卡， 30 具体做法为：文件选项自定义功能区，将开发工具勾选上之后，即可 在 A11 单元格中输入

14、数值“1” ，在 B11 单元格中输入=INDEX(B4:B9,$A$11)，按回 车键后向右复制公式至 G11 单元格中，得到上海地区各月的销量。如图所示 选择需创建图表的区域，这里选择 B11 至 G11 单元格区域，在“插入”选项卡下 单击“柱形图”第一种类型。 31 出现柱形图后，选中右键点击“选择数据” ，单击水平分类轴标签，编辑，选择轴 标签区域，用鼠标选中 B3 至 G3 单元格，则柱形图水平轴即变成月份。 删除图例和网络线，添加图表标题，并修改为“各部门销售业绩表” 。 在“开发工具”选项卡中选择“插入” ，选择表单控件中的第二项“组合框” ，并 在柱形图空白位置画出。 32

15、右键点击组合框，选择“设置控件格式” ，在数据源区域中选择 A4 至 A9 单元格 区域，单元格链接选择 A11 单元格（注，这些操作不需要通过键盘输入，而是通 过方框右边的选项按钮与鼠标的配合完成） ，勾选三维阴影，即可完成。 为柱形图添加数据标签，可任意选择组合框中的各地区选项，即可出现对应地区 上半年销售数量柱形图。 （二）折线图（二）折线图 EXCEL 电子表格可以对图表中的图表项进行修改。例如，调整各图表项字体的大小和 背景色等，用户还可以将某一系列的数据由柱形图改为折线图。 33 将例 4.1 中已完成的柱线图选中，右键选择“更改图表类型” ，选择“折线图” ，第四 种类型。 分别

16、选中两条折线，添加数据标签并手动调整标签位置后，图表变为如下所示： 注：在某些情况下，可以对折线图添加“趋势线” ，方法：图表工具布局趋势 线。并可根据趋势线对未来进行预测。 选择“趋势线”“线性趋势线” ，为 2014 年净利润添加趋势线 34 选中趋势线，右键点击“设置趋势线格式” ，调整趋势线颜色为红色，并前推一周 期。 35 （三）饼图（三）饼图 1.基本饼形图基本饼形图 例例 4.24.2：A 公司的股本构成情况如下：中国大洋食品开发集团占总股本 49%，长岛食 品研究所占总股本 21%，企业高管层人员赵正华占总股本 15%，钱小红占总股本 49%，孙 伟占总股本 5%，请绘制饼形图，描述该公司的股本结构。 操作步骤： 连续选中 B2 至 G3 单元格区域，选择“插入”选项卡，图表组，单击“饼图”按 钮，弹出“饼图”的子菜单，从中选择“三维饼图”的第二个，即“分离型饼图” ， 如图所示。 修改图表标题，并添加数据标签 36 2.复合饼形图复合饼形图 例例 4.2