EXCEL实训案例与操作步骤

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代表的当前日期固定，也即，将公式改为

=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月销售记录如下右表，销售记录中仅包含所销售产品编码及数量，要求要继续完成空白单元格。