EXCEL实训案例与操作步骤
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
例企业在应收账款管理过程中，需及时对到期的应收账款进行偿还提醒，故需筛选出当前日期已到期的应收客户及应收金额。如下表所示
为及时筛选出到期客户清单，需在F列显示出其到期状态，可通过IF函数来实现。
在F4单元格输入函数:=IF(E4<B2,"到期","未到期")。表示的意思是，当E4单元格的到期日小于当前日期，则F4单元格显示“到期”，否则，则显示“未到期”。
操作步骤：
选中B2至B7单元格区域，单击“数据”选项卡中的“数据有效性”——数据有效性，
2、输入指定区间的数据
例：要求输入员工年龄时，年龄区间范围为20-60之间。一旦输入非区间内数值，将提示报错。
操作步骤：
选中E2至E7单元格，单击“数据”选项卡中的“数据有效性”——数据有效性，设置有效性条件为允许整数，介于最小值20与最大值60之间，同时出错警告输入错误提示。
可得出下表结果：
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层。
“数值”用于添加汇总计算的字段，可以用鼠标把字段列表区域窗格内的某个字段或其他小窗格内的字段拖放到此窗格内，创建值字段。
例：根据左表的销售记录数据，需统计出在不同销售渠道下，各地区不同类别商品的销售额合计，也即右表所示。
操作步骤：
选中原数据清单，插入数据透视表
将“渠道”字段拖至“报表筛选”区域，将“城市”字段拖至“行标签”区域，将“类别”字段拖至“列标签”区域，将需要汇总计算的字段“销售额”拖至“数值”区域。
（三）筛选
筛选是EXCEL的一个最常用的数据分析功能，很多人都会使用数据筛选功能。不仅可以筛选多个条件，还可对日期、时间数据进行特殊的筛选，以及按照颜色来进行筛选。
1、多条件高级筛选
例，对于下表数据，要把满足2012年7月，华北地区，销售额大于500的数据筛选出：
操作步骤：
选中数据清单的区域，单击：开始——排序和筛选——筛选
=VLOOKUP(查找依据，查找区域，指定取数的列位置，是否精确查询的逻辑值)
0表示精确匹配，找一模一样的数据；1表示模糊匹配，找接近的数据。
注意，单独使用函数VLOOKUP无法查找指定数据区域的重复数据。
例：某公司共生产12种产品，产品资料如下左表，2011年1月销售记录如下右表，销售记录中仅包含所销售产品编码及数量，要求要继续完成空白单元格。
下表1为工资表，要想轻松高效完成每位员工工资条的制作，形成表2
操作步骤：
新建表格“工资条”，按规定格式输入第一行列标签
B2单元格：=VLOOKUP($A2,工资表!$A$2:$O$21,2,0)
即，以工资条中A2编号为查找依据，查找区域为工资表A2至O21，指定取数的列位置为第2列，也即如果遇到工资表中第一列编号也为01的情况，则B2单元格取值为被查找匹配单元格向右数第2列单元格中的数值，0表示精确查找。
在R9单元格中输入公式：
=COUNTIF($O$2:$O$21,"<2000")
得到如下结果：
其他常见小函数：todayrow columntextmid left right
二、基本操作与数据处理
（一）数据有效性
数据有效性是对单元格设置的一个规则，只有满足这个规则的数据才能输入到单元格。
1、输入序列数据
完成后，选中A1至O3单元格，向下拖动右下角的填充柄，即可实现所有员工工资条的制作。
8.countif函数
功能：用来求满足区域内指定条件的计数函数
语法：countif（range，criteria）
range表示要计算其中非空单元格数目的区域
criteria表示统计条件
例：根据工资表，需统计出实发工资各区间段的员工人数。
Excel实训案例与操作步骤
一、函数
主要介绍如下函数：
max minsumifsumif sumproductvlookupcountif
1.sum函数
功能：计算单元格区域中所有数值的和
语法：=sum(number1,number2,……)
“number1,number2,……”为需要求和的参数。参数可以是数值、文本、逻辑值和单元格引用。单元格引用如果是空单元格，那么该单元格引用将被忽略。
在很多情况下，经常要输入一些重复的数据，比如要在员工信息表的某列输入该员工所属部门名称，而这些部门名称总是那么几个，此时，利用数据有效性，不仅可以实现部门名称的快速输入，也可以防止输入错误的部门名称。
例，如下左表，一共有三个部门，服装部、家电部、食品部，当单击B2单元格时，出现下拉箭头，就可以选择输入该序列的某个项目。
单击数据清单中的任一非空单元格，单击“插入”选项卡，再单击功能区最左边“数据透视表”——“数据透视表”
在默认情况下，系统自动将选取整个数据清单作为数据源，如果数据源区域需要修改，则可直接在“选择一个表或区域”输入栏中重新输入数据区域。确定数据源后，单击“确定”按钮，EXCEL将自动新建新工作表，并在此工作表上创建空白的数据透视表。
操作步骤：
在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")
依次选择订购日期的“2012年7月”，销售地区的“华北”，销售额“数字筛选”——大于——输入数字500
2、利用列表（表）实现高效筛选
尽管自动筛选非常有用，但是无法自动扩展筛选区域，如果数据区域右侧增加几列数据，这几列数据是不能已经建立的筛选区域中的，如果要把这几列数据也建立筛选，需要先取消筛选，然后再建立自动筛选。
注：1.如若不需要数据透视表最右列“总计”字段，可将单元格定位在“总计”，右键点击“删除总计”，后如欲恢复，可在鼠标定位在数据透视表任意单元格，右键“数据透视表选项”，选择“汇总和筛选”，重新勾选上“显示行总计”
2.本例中，数值汇总方式为求和，但也可以通过左键点击“数值”区域的“销售额”字段，选择“值字段设置”，可修改为以平均值方式或计数方式显示。
操作步骤：
1）选中F2-F19，条件格式——图标集——标记（第一行第二项）
2）选中F2-F19，条件格式——管理规则——编辑规则，将类型从“百分比”改为“数字”，值依次输入7000，5000，确定即可。
（2）数据条
如果不想用图标集，想更直观的看到每位员工的工资差距，可尝试使用数据条。
操作步骤：选中F2-F19单元格，点击条件格式——数据条——选择任意一种颜色即可。
如此表，如在表右侧加一列“运货商”，则需取消筛选再重新选择新区域再进行筛选。
但通过创建表的形式自动扩展筛选区域。
操作步骤：
将光标确定于数据区域内任意单元格
点击“插入”——“表格”
在“销售额”右侧增加一列“运货商”，则此列自动进入筛选区域
三、数据透视表
（一）制作基本的数据透视表
首先保证数据源是一个数据清单
进一步，若将函数中的B2代表的当前日期固定，也即，将公式改为
=IF(E4<$B$2,"到期","未到期")则下拉填充柄，可轻松实现公式的自动复制。
5.sumif函数
功能：对满足条件的单元格求和。
语法：=sumif(range,criteria,sum_range)
range表示要进行计算的单元格区域，criteria表示用数字、表达式或文本形式定义的条件；sum_range表示用于求和计算的实际单元格。如果省略，将使用区域中的单元格。
操作步骤：
（1）选中F2至F19单元格区域，依次点击条件格式——项目选取规则——值最大的10项。
（2）选中F2至F19单元格区域，依次点击条件格式——项目选取规则——值最小的10项。方法同理。
3、图标集与数据条的使用
（1）图标集
例如下表，给下表中工资数据加上图标集，其中大于7000，5000至7000之间，小于5000分别标记上不同的图标。
2. max函数
功能：返回一组值中的最大值
语法：=max(number1,number2,……)
number1, number2,……number1是必需的，后续数值是可选的。
3. min函数
功能：返回一组值中的最小值
语法：=min(number1,number2,……)
number1, number2,……number1是必需的，后续数值是可选的。
例：根据表1-工资表得出表2-各部门工资统计
6.sumproduct函数
功能：用于计算几组数组间对应元素乘积之和
语法：=SUMPRODUCT(array1,array2,array3,……)
=SUMPRODUCT(数组1,数组2,数组3,……)
例：对于如下左图所示数据，要计算所有产品的销售总额，一般的方法是先计算每个产品的销售额（单价乘以销售量），然后将每个产品的销售额加总在一起，得到销售总额，如下右图。
销售记录表：
单元格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
例： 利用VLOOKUP函数制作工资条
但是鉴于工资条的格式与工资表的格式相同，可将2以COLUMN()代替（COLUMN()表示本单元格所在的列数），以方便公式的拖拉，而不需手动修改公式。
故，进一步将B2单元格改为：=VLOOKUP($A2,工资表!$A$2:$O$21,COLUMN(),0)，向右拖拉填充柄，即可完成第一位员工工资表的制作。
“报表筛选”用于添加报表筛选字段，可以用鼠标把字段列表区域窗格内的某个字段或其他小窗格内的字段拖放到此窗格内，创建筛选字段。
“列标签”区域，用于添加列字段，可以用鼠标把字段列表区域窗格内的某个字段或其他小窗格内的字段拖放到此窗格内，创建列字段。
“行标签”用于添加行字段，可以用鼠标把字段列表区域窗格内的某个字段或者其他小窗格内的字段拖放到此窗格内，创建行字段。
例：以例1中原数据为数据源，需统计出在各时间段，各地区不同类别商品的销售额合计，也即右表所示。
操作步骤：
选中原数据清单，插入数据透视表
将“渠道”字段拖至“报表筛选”区域，将“日期”字段拖至“行标签”区域，将“类别”字段拖至“列标签”区域，将需要汇总计算的字段“销售额”拖至“数值”区域。
但使用sumproduct函数可以完全省略中间的计算过程，计算公式为：
在B9单元格输入=sumproduct(B2:B7,C2:C7)即可直接得出答案
7.vlookup函数
功能：根据数据区域的第一列数据，向右侧查找某列的数据
语法：
=VLOOKUP(lookup_value,table_array,col_index_num, [range_lookup])
当E2单元格输入66时，则会弹出对话框如下：
（二）条件格式
“开始”选项卡—“条件格式”
1、挑选重复数据
例使重复的名字突出显示
操作步骤：选中A2至A19单元格区域，依次点击条件格式——突出显示单元格规则——重复值
2、突出显示最大值与最小值
例以上工资表为例，要求突出显示“工资”列中最大工资与最小工资，以红色填充最大工资，以绿色填充最小工资。
例财务工作中常用函数：化工集团含若干分工厂，2014年各月利润如表所示，需求出各工厂年度利润合计、各月最大利润数与各月最小利润数。
则需
在N3单元格中输入=SUM(B3:M3)
在O3单元格中输入=MAX(B3:M3)
在P3单元格中输入=MIN(B3:M3)
再将N3至P3单元格选中，下拉填充柄，即将公式填充至下一行
为及时筛选出到期客户清单，需在F列显示出其到期状态，可通过IF函数来实现。
在F4单元格输入函数:=IF(E4<B2,"到期","未到期")。表示的意思是，当E4单元格的到期日小于当前日期，则F4单元格显示“到期”，否则，则显示“未到期”。
操作步骤：
选中B2至B7单元格区域，单击“数据”选项卡中的“数据有效性”——数据有效性，
2、输入指定区间的数据
例：要求输入员工年龄时，年龄区间范围为20-60之间。一旦输入非区间内数值，将提示报错。
操作步骤：
选中E2至E7单元格，单击“数据”选项卡中的“数据有效性”——数据有效性，设置有效性条件为允许整数，介于最小值20与最大值60之间，同时出错警告输入错误提示。
可得出下表结果：
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层。
“数值”用于添加汇总计算的字段，可以用鼠标把字段列表区域窗格内的某个字段或其他小窗格内的字段拖放到此窗格内，创建值字段。
例：根据左表的销售记录数据，需统计出在不同销售渠道下，各地区不同类别商品的销售额合计，也即右表所示。
操作步骤：
选中原数据清单，插入数据透视表
将“渠道”字段拖至“报表筛选”区域，将“城市”字段拖至“行标签”区域，将“类别”字段拖至“列标签”区域，将需要汇总计算的字段“销售额”拖至“数值”区域。
（三）筛选
筛选是EXCEL的一个最常用的数据分析功能，很多人都会使用数据筛选功能。不仅可以筛选多个条件，还可对日期、时间数据进行特殊的筛选，以及按照颜色来进行筛选。
1、多条件高级筛选
例，对于下表数据，要把满足2012年7月，华北地区，销售额大于500的数据筛选出：
操作步骤：
选中数据清单的区域，单击：开始——排序和筛选——筛选
=VLOOKUP(查找依据，查找区域，指定取数的列位置，是否精确查询的逻辑值)
0表示精确匹配，找一模一样的数据；1表示模糊匹配，找接近的数据。
注意，单独使用函数VLOOKUP无法查找指定数据区域的重复数据。
例：某公司共生产12种产品，产品资料如下左表，2011年1月销售记录如下右表，销售记录中仅包含所销售产品编码及数量，要求要继续完成空白单元格。
下表1为工资表，要想轻松高效完成每位员工工资条的制作，形成表2
操作步骤：
新建表格“工资条”，按规定格式输入第一行列标签
B2单元格：=VLOOKUP($A2,工资表!$A$2:$O$21,2,0)
即，以工资条中A2编号为查找依据，查找区域为工资表A2至O21，指定取数的列位置为第2列，也即如果遇到工资表中第一列编号也为01的情况，则B2单元格取值为被查找匹配单元格向右数第2列单元格中的数值，0表示精确查找。
在R9单元格中输入公式：
=COUNTIF($O$2:$O$21,"<2000")
得到如下结果：
其他常见小函数：todayrow columntextmid left right
二、基本操作与数据处理
（一）数据有效性
数据有效性是对单元格设置的一个规则，只有满足这个规则的数据才能输入到单元格。
1、输入序列数据
完成后，选中A1至O3单元格，向下拖动右下角的填充柄，即可实现所有员工工资条的制作。
8.countif函数
功能：用来求满足区域内指定条件的计数函数
语法：countif（range，criteria）
range表示要计算其中非空单元格数目的区域
criteria表示统计条件
例：根据工资表，需统计出实发工资各区间段的员工人数。
Excel实训案例与操作步骤
一、函数
主要介绍如下函数：
max minsumifsumif sumproductvlookupcountif
1.sum函数
功能：计算单元格区域中所有数值的和
语法：=sum(number1,number2,……)
“number1,number2,……”为需要求和的参数。参数可以是数值、文本、逻辑值和单元格引用。单元格引用如果是空单元格，那么该单元格引用将被忽略。
在很多情况下，经常要输入一些重复的数据，比如要在员工信息表的某列输入该员工所属部门名称，而这些部门名称总是那么几个，此时，利用数据有效性，不仅可以实现部门名称的快速输入，也可以防止输入错误的部门名称。
例，如下左表，一共有三个部门，服装部、家电部、食品部，当单击B2单元格时，出现下拉箭头，就可以选择输入该序列的某个项目。
单击数据清单中的任一非空单元格，单击“插入”选项卡，再单击功能区最左边“数据透视表”——“数据透视表”
在默认情况下，系统自动将选取整个数据清单作为数据源，如果数据源区域需要修改，则可直接在“选择一个表或区域”输入栏中重新输入数据区域。确定数据源后，单击“确定”按钮，EXCEL将自动新建新工作表，并在此工作表上创建空白的数据透视表。
操作步骤：
在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")
依次选择订购日期的“2012年7月”，销售地区的“华北”，销售额“数字筛选”——大于——输入数字500
2、利用列表（表）实现高效筛选
尽管自动筛选非常有用，但是无法自动扩展筛选区域，如果数据区域右侧增加几列数据，这几列数据是不能已经建立的筛选区域中的，如果要把这几列数据也建立筛选，需要先取消筛选，然后再建立自动筛选。
注：1.如若不需要数据透视表最右列“总计”字段，可将单元格定位在“总计”，右键点击“删除总计”，后如欲恢复，可在鼠标定位在数据透视表任意单元格，右键“数据透视表选项”，选择“汇总和筛选”，重新勾选上“显示行总计”
2.本例中，数值汇总方式为求和，但也可以通过左键点击“数值”区域的“销售额”字段，选择“值字段设置”，可修改为以平均值方式或计数方式显示。
操作步骤：
1）选中F2-F19，条件格式——图标集——标记（第一行第二项）
2）选中F2-F19，条件格式——管理规则——编辑规则，将类型从“百分比”改为“数字”，值依次输入7000，5000，确定即可。
（2）数据条
如果不想用图标集，想更直观的看到每位员工的工资差距，可尝试使用数据条。
操作步骤：选中F2-F19单元格，点击条件格式——数据条——选择任意一种颜色即可。
如此表，如在表右侧加一列“运货商”，则需取消筛选再重新选择新区域再进行筛选。
但通过创建表的形式自动扩展筛选区域。
操作步骤：
将光标确定于数据区域内任意单元格
点击“插入”——“表格”
在“销售额”右侧增加一列“运货商”，则此列自动进入筛选区域
三、数据透视表
（一）制作基本的数据透视表
首先保证数据源是一个数据清单
进一步，若将函数中的B2代表的当前日期固定，也即，将公式改为
=IF(E4<$B$2,"到期","未到期")则下拉填充柄，可轻松实现公式的自动复制。
5.sumif函数
功能：对满足条件的单元格求和。
语法：=sumif(range,criteria,sum_range)
range表示要进行计算的单元格区域，criteria表示用数字、表达式或文本形式定义的条件；sum_range表示用于求和计算的实际单元格。如果省略，将使用区域中的单元格。
操作步骤：
（1）选中F2至F19单元格区域，依次点击条件格式——项目选取规则——值最大的10项。
（2）选中F2至F19单元格区域，依次点击条件格式——项目选取规则——值最小的10项。方法同理。
3、图标集与数据条的使用
（1）图标集
例如下表，给下表中工资数据加上图标集，其中大于7000，5000至7000之间，小于5000分别标记上不同的图标。
2. max函数
功能：返回一组值中的最大值
语法：=max(number1,number2,……)
number1, number2,……number1是必需的，后续数值是可选的。
3. min函数
功能：返回一组值中的最小值
语法：=min(number1,number2,……)
number1, number2,……number1是必需的，后续数值是可选的。
例：根据表1-工资表得出表2-各部门工资统计
6.sumproduct函数
功能：用于计算几组数组间对应元素乘积之和
语法：=SUMPRODUCT(array1,array2,array3,……)
=SUMPRODUCT(数组1,数组2,数组3,……)
例：对于如下左图所示数据，要计算所有产品的销售总额，一般的方法是先计算每个产品的销售额（单价乘以销售量），然后将每个产品的销售额加总在一起，得到销售总额，如下右图。
销售记录表：
单元格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
例： 利用VLOOKUP函数制作工资条
但是鉴于工资条的格式与工资表的格式相同，可将2以COLUMN()代替（COLUMN()表示本单元格所在的列数），以方便公式的拖拉，而不需手动修改公式。
故，进一步将B2单元格改为：=VLOOKUP($A2,工资表!$A$2:$O$21,COLUMN(),0)，向右拖拉填充柄，即可完成第一位员工工资表的制作。
“报表筛选”用于添加报表筛选字段，可以用鼠标把字段列表区域窗格内的某个字段或其他小窗格内的字段拖放到此窗格内，创建筛选字段。
“列标签”区域，用于添加列字段，可以用鼠标把字段列表区域窗格内的某个字段或其他小窗格内的字段拖放到此窗格内，创建列字段。
“行标签”用于添加行字段，可以用鼠标把字段列表区域窗格内的某个字段或者其他小窗格内的字段拖放到此窗格内，创建行字段。
例：以例1中原数据为数据源，需统计出在各时间段，各地区不同类别商品的销售额合计，也即右表所示。
操作步骤：
选中原数据清单，插入数据透视表
将“渠道”字段拖至“报表筛选”区域，将“日期”字段拖至“行标签”区域，将“类别”字段拖至“列标签”区域，将需要汇总计算的字段“销售额”拖至“数值”区域。
但使用sumproduct函数可以完全省略中间的计算过程，计算公式为：
在B9单元格输入=sumproduct(B2:B7,C2:C7)即可直接得出答案
7.vlookup函数
功能：根据数据区域的第一列数据，向右侧查找某列的数据
语法：
=VLOOKUP(lookup_value,table_array,col_index_num, [range_lookup])
当E2单元格输入66时，则会弹出对话框如下：
（二）条件格式
“开始”选项卡—“条件格式”
1、挑选重复数据
例使重复的名字突出显示
操作步骤：选中A2至A19单元格区域，依次点击条件格式——突出显示单元格规则——重复值
2、突出显示最大值与最小值
例以上工资表为例，要求突出显示“工资”列中最大工资与最小工资，以红色填充最大工资，以绿色填充最小工资。
例财务工作中常用函数：化工集团含若干分工厂，2014年各月利润如表所示，需求出各工厂年度利润合计、各月最大利润数与各月最小利润数。
则需
在N3单元格中输入=SUM(B3:M3)
在O3单元格中输入=MAX(B3:M3)
在P3单元格中输入=MIN(B3:M3)
再将N3至P3单元格选中，下拉填充柄，即将公式填充至下一行