Excel数据处理和应用.docVIP

Excel 数据处理与应用 一、练习： 1、打开“浙江省国税培训名单”工作表，在‘原始名单’工作表中插入新列“地区姓名”，通过公式产生“姓名”连接“地区”的信息。按“单位”进行“排序”，按“部门”进行“排序”。 问题：查看有无重复人员信息录入？（同名同姓，同一单位、同一部门内有无同名同姓，确实为同名同姓不同人员，作标记。删除重复录入人员。） 2、选择性粘贴：对于通过公式产生的信息，选用“选择性粘贴”将结果粘贴回去。 二、打开“练习1”，在“相对引用与绝对引用”中做绝对引用与相对引用练习。 条件格式：将下图中销售净收入大于4000元的用红字加粗显示。 Excel2007的条件格式功能要大得多，可对文本内容加条件格式。 二、利用数组公式进行操作 数组是一组单元格或一组值，如常量数组：{1，2，3}、{1；2；3}。数组公式可以同时完成多个计算，并且根据需要得到多个结果或一个结果。数据公式输入结果后，用Ctrl+Shift+Enter得到结果。 练习： 用数组公式统计下表中各班级各类成绩的总人数，以及各类人数占各所在班（或全校）的百分比。设置工作表保护，锁定公式所在的单元格并隐藏公式内容，并允许对各班人数进行编辑。 统计表： 人数 百分比 A班 优秀 15 良好 29 一般 6 B班 优秀 19 良好 24 一般 7 C班 优秀 12 良好 32 一般 6 各班总计： 优秀 良好 一般 假设，对于“蔬菜”、“水果”、“肉类”、“海鲜”，分别纳税3%、4%、5%、5%的营业税，试用数组及求和公式完成下表。在空白行处对公式进行验算，并对验算的各行进行隐藏。 蔬菜 水果 肉类 海鲜 税额 一月 ￥2,231 ￥1,456 ￥5,643 ￥1,233 二月 ￥2,543 ￥1,674 ￥5,733 ￥1,532 三月 ￥2,866 ￥1,943 ￥6,022 ￥1,509 四月 ￥2,908 ￥1,844 ￥5,904 ￥1,364 总计 三、函数运用 练习：1、运用lookup函数，根据下面折扣表单提供的数据，统计下表中每人的折扣率和折扣额。 销售商 销售额 折扣率 折扣额 李强 24500 周浩 14000 陈黎明 32000 张杰 22000 王亚鹏 8500 折扣表单 等级（￥） 折扣率 销售额小于9000，不享受折扣；销售额大于等于9000，折扣率2%；大于等于15000,折扣4%；大于等于25000,折扣6%。 0.00% 9000 2.00% 15000 4.00% 25000 6.00% 对折扣表单中的数据，根据‘等级’进行升序排序。 在‘李强’右边的折扣率单元格内输入公式：=LOOKUP(B2,$A$10:$A$13,$B$10:$B$13) 因为需要对公式进行复制，所以函数中的第二、第三个参数使用绝对引用。  2、运用查找引用函数，完成下面功能，在输入姓名后系统可自动显示相应的电话号码。 输入姓名： 电话号码为： 电话号码簿： 姓名 电话 王 123455 李 534655 张 766543 赵 434344 钱 242424 方法1：对‘电话号码簿’数据按‘姓名’升序排序；在‘电话号码为：’后面输入公式：=LOOKUP(B1,A6:A10,B6:B10) 查找的是小于等于‘输入姓名’的最大值。 方法2：无需对‘电话号码簿’数据排序，在‘电话号码为：’后面输入公式：=INDEX(A6:B10,MATCH(B1,A6:A10,0),2) 参考1：Lookup()函数 LOOKUP 函数可从向量或者从一个数组返回值。 向量：单行或单列区域 数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。 LOOKUP 函数具有两种语法形式：向量形式和数组形式 向量形式 LOOKUP(lookup_value, lookup_vector, result_vector) lookup_value 是函数在第一个向量中要搜索的值。它可以是数字、文本、逻辑值、名称或对值的引用。 lookup_vector 是函数指定的搜索区域，只包含一行或一列。其中的值可以是文本、数字或逻辑值。 lookup_vector 中的值必须以升序排列。否则，LOOKUP 可能无法返回正确的值。大写文本和小写文本是等同的。 result_vector 是函数返回值所在区域。result