EXCEL 数据处理与应用
一、练习：
1、打开“浙江省国税培训名单”工作表，在‘原始名单’工作表中插入新列“地区姓名”，通过公式产生“姓名”连接“地区”的信息。

按“单位”进行“排序”，按“部门”进行“排序”。

问题：查看有无重复人员信息录入？（同名同姓，同一单位、同一部门内有无同名同姓，确实为同名同姓不同人员，作标记。

删除重复录入人员。

）
2、选择性粘贴：对于通过公式产生的信息，选用“选择性粘贴”将结果粘贴回去。

二、打开“练习1”，在“相对引用与绝对引用”中做绝对引用与相对引用练习。

条件格式：将下图中销售净收入大于4000元的用红字加粗显示。

Excel2007的条件格式功能要大得多，可对文本内容加条件格式。

二、利用数组公式进行操作
数组是一组单元格或一组值，如常量数组：{1，2，3}、{1；2；3}。

数组公式可以同时完成多个计算，并且根据需要得到多个结果或一个结果。

数据公式输入结果后，用Ctrl+Shift+Enter得到结果。

练习：
1、用数组公式统计下表中各班级各类成绩的总人数，以及各类人数占各所在班（或全校）的百分比。

设置工作表保护，锁定公式所在的单元格并隐藏公式内容，并允许对各班人数进行编辑。

2、假3%、4%、5%、
5%的营业税，试用数组及求和公式完成下表。

在空白行处对公式进行验算，并对验算的各行进行隐藏。

三、函数运用
练习：1、运用lookup函数，根据下面折扣表单提供的数据，统计下表中每人的折扣率和折扣额。

● 对折扣表单中的数据，根据
‘等级’进行升序排序。

● 在‘李强’右边的折扣率单
元格内输入公式：
=LOOKUP(B2,$A$10:$A$13,$B$10:$B$13) ● 因为需要对公式进行复制，
所以函数中的第二、第三个参数使用绝对引用。

2、运用查找引用函数，完成下面功能，在输入姓名后系统可自动显示相应的电话号码。

● 方法1：对‘电话号码簿’数据按‘姓名’升序排序；
在‘电话号码为：’后面输入公式： =LOOKUP(B1,A6:A10,B6:B10)
查找的是小于等于‘输入姓名’的最大值。

● 方法2：无需对‘电话号码簿’数据排序，
在‘电话号码为：’后面输入公式： =INDEX(A6:B10,MATCH(B1,A6:A10,0),2)
参考1：Lookup()函数
LOOKUP 函数可从向量或者从一个数组返回值。

向量：单行或单列区域
数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。

数组区域共用一个公式；数组常量是用作参数的一组常量。

折扣表单
9000，
9000， 2%；
15000,折扣4%； 25000,折扣6%。

输入姓名： 电话号码为：
电话号码簿：
LOOKUP函数具有两种语法形式：向量形式和数组形式
向量形式LOOKUP(lookup_value, lookup_vector, result_vector)
lookup_value 是函数在第一个向量中要搜索的值。

它可以是数字、文本、逻辑值、名称或对值的引用。

lookup_vector 是函数指定的搜索区域，只包含一行或一列。

其中的值可以是文本、数字或逻辑值。

lookup_vector 中的值必须以升序排列。

否则，LOOKUP 可能无法返回正确的值。

大写文本和小写文本是等同的。

result_vector 是函数返回值所在区域。

result_vector 参数必须与lookup_vector 大小相同。

数组形式LOOKUP(lookup_value, array)
lookup_value 意思同上，是函数在数组中要搜索的值。

array 是函数指定的搜索区域，包含需要进行比较的文本、数字或逻辑值的单元格区域。

如果数组包含宽度比高度大的区域（列数多于行数），LOOKUP 会在第一行中搜索lookup_value 的值，找出所要的列。

如果数组是正方的或高度大于宽度（行数多于列数），则会在第一列中进行搜索，找出所要的行。

返回：选择行或列中的最后一个值。

用Index与match代替lookup函数
由于当LOOKUP 找不到指定值时，它会找到小于或等于指定值的最大值，作为查找结果，因而可能出错。

组合使用Index与match函数，可以解决上述问题。

Index()函数INDEX(array, row_num, [column_num])
array：数组区域row_num：行column_num：列
返回指定数组区域中指定单元格的值，区域为单行或单列时，参数行或列可以有一个省略。

行或列可以是match函数的返回值。

Match()比较函数MATCH(lookup_value, lookup_array, [match_type])
lookup_value 是要查找的值。

它可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。

lookup_array 是要搜索的数组区域（行或列）。

match_type 可选，为-1、0 或1(默认为1), 选择0，函数会查找等于要搜索值的单元格。

返回找到的值在数组中的位置。

Excel函数中含有丰富的函数，需要时，可以按F1请求系统帮助。

四、邮件合并操作
利用Excel现有的数据列表，可以方便地合并到Word 文档中，产生含数据库信息的新文档。

练习：利用“2012年执法资格考试人员准考证号排序考.xls”中的数据信息，完成准考证的填写。

五、Excel文档中的数据保护
六、数据处理
Excel具有强大的数据处理功能，对于大量的数据，可以通过排序、筛选、汇总、数据透视表等功能进行有效管理。

排序、筛选、汇总、数据透视表等功能操作前，应先选取数据区域或单元格。

练习：首先将文本文件LX4.TXT中的数据导入到练习4中。

菜单：数据/导入外部数据/导入数据…，选择LX4.TXT文件，按向导操作，进行导入。

1、按‘工号’升序排序。

2、添加两个自定义序列：
临时工、合同工、正式工、干部；
工人、助工、工程师、高工。

按‘编制’升序、‘职称’降序排序。

3、筛选出表格中所有正式工人员。

筛选出所有正式工的工人。

4、筛选出表格中所有的合同工或工人的人员
5、汇总表格中各类编制人员的平均基本工资。

6、用数据透视表的方法汇总表格中各类编制人员的平均基本工资。

操作提示：
1、单击表格工号列中任一单元格，单击‘升序’按钮。

2、‘自定义序列’通过菜单：工具/选项/自定义序列进行导入或添加；排序时，使
用菜单：数据/排序，并单击其中的‘高级’按钮，从对话框中选择‘自定义序列’。

按‘编制’升序确定返回到工作表窗口后，再使用菜单对‘职称’按降序排序。

3、按自动筛选操作。

4、按高级筛选操作。

多个条件的高级筛选，“并且关系”条件，写入一行；“或者关
系”条件，不能写入一行。

5、按‘编制’排序，并汇总。

6、数据透视表操作前无需排序，操作结果可以放入一张新的工作表。