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

Excel实训案例与操作步骤

2015.09

目 录
一、函数 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、突出显示最大值与最小值 10
3、图标集与数据条的使用 10
（三）筛选 12
1、多条件高级筛选 12
2、利用列表（表）实现高效筛选 12
三、数据透视表 14
（一）制作基本的数据透视表 14
（二）利用多重数据区域制作数据透视表 18
（三）运用数据透视表进行表格数据对比分析 21
（四）动态数据透视表制作 23
（五）通过自定义计算字段进行统计分析 25
四、图表制作与美化 28
（一）柱形图 28
（二）折线图 32
（三）饼图 34

一、函数
主要介绍如下函数：
max min sum if sumif sumproduct vlookup countif
1. 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(E4<B2,"到期","未到期")。表示的意思是，当E4单元格的到期日小于当前日期，则F4单元格显示“到期”，否则，则显示“未到期”。
进一步，若将函数中的B2代表的当前日期固定，也即，将公式改为
=IF(E4<$B$2,"到期","未到期")则下拉填充柄，可轻松实现公式的自动复制。

5. sumif函数
功能：对满足条件的单元格求和。
语法：=sumif(range,criteria,sum_range)
range表示要进行计算的单元格区域，criteria表示用数字、表达式或文本形式定义的条件；sum_range表示用于求和计算的实际单元格。如果省略，将使用区域中的单元格。

例1.1：根据表1-工资表得出表2-各部门工资统计
在B12单元格输入=SUMIF(C3:C8,A12,D3:D8)
即可得出管理部6月实发工资合计。

6. sumproduct函数
功能：用于计算几组数组间对应元素乘积之和
语法：=SUMPRODUCT(array1,array2,array3,……)
=SUMPRODUCT(数组1, 数组2, 数组3,……)
例1.1：对于如下左图所示数据，要计算所有产品的销售总额，一般的方法是先计算每个产品的销售额（单价乘以销售量），然后将每个产品的销售额加总在一起，得到销售总额，如下右图。

但使用sumproduct函数可以完全省略中间的计算过程，计算公式为：
在B9单元格输入=sumproduct(B2:B7,C2:C7)即可直接得出答案

7. vlookup函数
功能：根据数据区域的第一列数据，向右侧查找某列的数据
语法：
=VLOOKUP(lookup_value,table_array, col_index_num, [range_lookup])
= VLOOKUP(查找依据，查找区域，指定取数的列位置，是否精确查询的逻辑值)
0表示精确匹配，找一模一样的数据；1表示模糊匹配，找接近的数据。
注意，单独使用函数VLOOKUP无法查找指定数据区域的重复数据。
例1.1：某公司共生产12种产品，产品资料如下左表，2011年1月销售记录如下右表，销售记录中仅包含所销售产品编码及数量，要求要继续完成空白单元格。

销售记录表：
单元格D3：=VLOOKUP($B3,产品资料!$B$3:$E$14,2,0)
单元格E3: =VLOOKUP($B3,产品资料!$B$3:$E$14,3,0)
单元格F3：=VLOOKUP($B3,产品资料!$B$3:$E$14,4,0)
单元格G3： =C3*E3

例1.2： 利用VLOOKUP函数制作工资条
下表1为工资表，要想轻松高效完成每位员工工资条的制作，形成表2

操作步骤：
新建表格“工资条”，按规定格式输入第一行列标签

B2单元格：=VLOOKUP($A2,工资表!$A$2:$O$21,2,0)
即，以工资条中A2编号为查找依据，查找区域为工资表A2至O21，指定取数的列位置为第2列，也即如果遇到工资表中第一列编号也为01的情况，则B2单元格取值为被查找匹配单元格向右数第2列单元格中的数值，0表示精确查找。
但是鉴于工资条的格式与工资表的格式相同，可将2以COLUMN()代替（COLUMN()表示本单元格所在的列数），以方便公式的拖拉，而不需手动修改公式。
故，进一步将B2单元格改为：=VLOOKUP($A2,工资表!$A$2:$O$21,COLUMN(),0)，向右拖拉填充柄，即可完成第一位员工工资表的制作。

完成后，选中A1至O3单元格，向下拖动右下角的填充柄，即可实现所有员工工资条的制作。

8. countif函数
功能： 用来求满足区域内指定条件的计数函数
语法：countif（range，criteria）
range表示要计算其中非空单元格数目的区域
criteria表示统计条件

例1.1：根据工资表，需统计出实发工资各区间段的员工人数。
操作步骤：
在R6单元格中输入公式：
=COUNTIF($O$2:$O$21,">=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”即可。

Ø 由此，复合饼图便生成，其中第二绘图区的小饼形图代表的是左边饼形图中的紫色区域的构成。
Ø 最后修改图表标题，调整图例位置，添加数据标签可得如下图