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 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(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项。

（2）选中F2至F19单元格区域，依次点击 条件格式——项目选取规则——值最小的10项。

方法同理。

3、图标集与数据条的使用将左边的10改为1，右边的设置中点击自定义格式，选择填充，红色，即可。

（1）图标集例2.2 如下表，给下表中工资数据加上图标集，其于7000，5000至7000之间，小于5000分别标记上不同的图标。

操作步骤：1）选中F2-F19，条件格式——图标集——标记（第一行第二项）2）选中F2-F19，条件格式——管理规则——编辑规则，将类型从“百分比”改为“数字”，值依次输入7000，5000，确定即可。

（2）数据条如果不想用图标集，想更直观的看到每位员工的工资差距，可尝试使用数据条。

操作步骤：选中F2-F19单元格，点击条件格式——数据条——选择任意一种颜色即可。