Excel的基本操作及代码

1、第10周上课内容（3节）no.08 2010-11-15excel高级应用（）一、教学目标1正确使用“数学公式”；正确使用“数组运算”；搞清“单元格引用”概念2掌握“数据透视图”和“数据透视表”的生成方法 3掌握“文本函数”、“日期与时间函数”、“统计函数”、“逻辑函数”4掌握“查找与引用函数”、部分“财务函数”二、基本理论1公式概念 对工作表的数值进行计算的式子称为公式。由操作符和运算符两个部分组成。操作符可以是常量、名称、数组、单元格引用和函数等。运算符用于连接公式中的操作符，是工作表处理数据的指令。运算符有很多：“”、“”、“*”、“”、“”、“”、“”、“”、“”、“”、“&”等等。2

2、数组公式数组就是单元格的集合或者是一组处理值的集合。数组公式能够执行多个输入操作、产生多个结果，并可将结果显示在某一个单元格区域的一个公式称为“数组公式”。3单元格引用定义：一个单元格对其它单元格的名称或单元格区域的名称进行的应用，叫单元格引用。单元格引用的目的是为了获得某种结果。引用分为以下类型： 相对引用例如在e1单元格中输入：a1b1。总是以当前单元格的位置为基准。 绝对引用例如在f1单元格中输入：$c$1: $c$28$d$1: $d$28。 混合引用例如在l1单元格中输入：（j$1j$2）*（$l1:$l2）。注意：按f4键可以使“相对引用”、“绝对引用”、“混合引用”三者进行转换。

3、4数据筛选筛选是一种查找数据的快速方法，可将数据列表中不满足条件的记录暂时隐藏起来，只显示有用的数据行，以供用户浏览和分析。有两种筛选方式：1）自动筛选 2）高级筛选5数据透视表定义：数据透视表是通过对源数据表的行、列进行重新排列，提供多角度的数据汇总信息。用户可以旋转行和列以查看源数据的不同汇总，还可以根据需要显示感兴趣区域的明细数据。要学好它，应掌握数据透视表的有关术语，如表1所示。表1 数据透视表术语含义坐标轴数据透视表中的一维，例如行、列或页数据源为数据透视表提供数据的数据列表或数据库字 段数据列表中的列标题项组成字段的成员，即某列中单元格的内容概要函数用来计算表格中数据值的函数，默认

4、的概要函数是用于数字值的sum函数，用于统计文本个数的count函数透 视通过重新确定一个或多个字段的位置来重新安排数据透视表6函数概念 事先存放在函数库中用来执行计算、分析、判断等处理数据任务的特殊公式，就称为函数。例如“sum（）”、“if（）”“rank（）”等等。一般情况下，函数都有“参数”，参数放在括号内。7函数类型 excel 2003的函数共有11类：1）常用函数.10个（包含在其它函数之中，不在总数中统计）2）财务函数.16个3）日期与时间函数.14个4）数学和三角函数.50个5）统计函数.80个6）查找和引用函数.18个7）数据库函数.12个8）文本函数.34个9）逻辑函数6

5、个10）信息函数.16个11）户自定义函数总数 246个（不含常用函数） 用户自定义。三、有关函数1. 逻辑函数用来判断事物的“真”与“假”、“有”与“无”、“是”与“非”之结果或者进行复合检验的excel函数，称为逻辑函数。在excel中共有六个逻辑函数，即and、or、not、if、true、false函数。 and（logical1，logical2logical30） 结果为“ture”或“false”“与”函数。 or（logical1，logical2logical30）结果为“ture”或“false” “或”函数。 not（logical）可对数字或表达式进行“ture”或“f

6、alse”判断“非”函数。 if（logical_test，value_if_ture，value_if_false）“条件”逻辑函数。 true、false函数用来返回参数的逻辑值。可以直接在单元格或公式中键入true或者false。通常可以不用。但可以配合其它函数，作为其它函数的参数。 2. 统计函数（置于常用函数内） rank（number，ref，order）统计排名函数；有降序、升序排列之分。（rank n. 排，等级；vt. 把分等，排列）其中：number为需要进行排名数组（区域）的第一个单元格名称。ref（reference引用，参考；referee裁判）为需要进行排名数组的绝

7、对引用区域；ref中的非数值型参数将被忽略。order（顺序，命令）为一数字，0或非0（例如1、2）。若order0 或省略，rank将对ref按降序进行排名，即排名结果就是“顺数第几名”。若order0，例如1，rank将对ref按升序进行排名。即排名结果就是“倒数第几名”。注意：rank中，如“rank（f2，$f$2:$f$39）”，数据区域要用绝对引用符号“$”。 countif（range，criteria）条件统计函数。（range 范围，区域；criteria 条件，标准） average（number1，number2， number30）求平均值统计函数。注意：numberx

8、表示一个数据“区域”或一个单元格“引用（名称）”3文本函数 replace（old_text，start_num，num_chars，new_text）文本替换、插入函数。其中：old_text 要被替换字符的单元格引用（名称）；start_num 要被替换的字符串的起始位置（字符串中第一个字符的起始位置为1）；num_chars 要被替换的字符个数；【chars是characters（字符）的缩写】new_text 要替换的新字符。注意：第三个参数num_chars0（或者为“空”）时，此时该函数就是“插入函数”。可书写为“replace(b3,3, ,0)”也可写为“replace(b3,

9、3,0,0)”表示要在第3个字符后不替换，而要插入一个“0” mid（text，start_num，num_chars) 字符串提取函数 midb（text，start_num，num_bytes) 字节提取函数其中：text 要从中提取字符串的字符串地址start_num 要提取字符串的开始位置（字符串的首位为1，其余类推）num_chars 要提取的字符串的长度。num_bytes 要提取的字节长度。 concatenate（text1，text2，. text30）字符串连接函数其中：textxx 是要连接的字符串 说明：concatenate的功能可用“&”实现。方法：“text1&t

10、ext2& . &text30”。4查找与引用函数 vlookup（lookup_value，table_array，col_index_num，range_lookup）列查找函数。vvertical垂直的，所以vlookup表示“垂直查找”。其中：lookup_value 垂直方向要查找的值。table_array 垂直方向要查找的矩阵（数组）。col_index_num 要查找的内容在阵列中的列序号，例如“3”表示“第3列”。colcolumn（列），而不是指“山口”、“山坳”之类的意思。range_lookup 是一个逻辑值（“0”或“1”，或者“false”或“true”）。a）如果

11、range_lookuptrue，表示要查找的数据事前必须“升序排列”，否则不能返回正确的结果；b）如果range_lookupfalse，表示要查找的数据事前不必“升序排列”，而且可以返回精确的结果；c）根据a）点，把“range_lookup”中的“rang”理解为“级别”、“等级”较妥。 hlookup（lookup_value，table_array，row_index_num，range_lookup)行查找函数。hhorizontal水平的其中：lookup_value 水平方向要查找的值。table_array 水平方向要查找的矩阵（数组）。row_index_num 要查找的内

12、容在阵列中的行序号，例如“3”表示“第3行”。range_lookup 是一个逻辑值（“0”或“1”，或者“false”或“true”）。hlookup与vlookup完全相仿。只要把“行”和“列”加以区别即可。5财务函数 sln（cost，salvage，life）财务折旧函数。其中：cost （费用）为资产原值salvage（残值）为资产在折旧期末的价值（也称为资产残值）。life （寿命）为折旧期限（有时也称作资产的使用寿命）。四、对命题的操作步骤【例1】 给定excel文档“19 员工信息表.xls”，按要求完成各题。1在sheet4的a1单元格中设置为只能录入5位数字或文本。当录入位

13、数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本 xx张三”（xx是你学号末二位，张三指你姓名）。操作：选中sheet4的a1单元格 数据 有效性 “出错警告”选卡 “样式”下选择“警告”“出错信息”文本框中输入“只能录入5位数字或文本 xx张三”【确定】。2使用replace函数，对sheet1中“员工信息表”的员工代码进行升级。 要求： 升级方法：在pa后面加上0，例如pa125，修改后为pa0125； 将升级后的员工代码结果填入表中的“升级员工代码”列中。操作：在sheet1的c3单元格中输入“replace(b3,3, ,0)”， 或者在sheet1的c3单元

14、格中输入“replace(b3,3,0,0)”【确定】 填充至c66单元格。（注意：replace（）在此处应理解为“插入”函数）3使用时间函数，计算sheet1中“员工信息表”的“年龄”列和“工龄”列。要求： 假设当前时间是“2009-11-15”，结合表中的“出生年月”、“参加工作时间”列，对员工“年龄”和“工龄”进行计算； 计算方法为两年份之差。并将结果保存到表中的“年龄”列和“工龄”列中。操作：在sheet1“年龄”列下的f3单元格中输入“2009year（e3）”即可。在sheet1“工龄”列下的h3单元格中输入“2009year（g3）”即可。（注意：如果出现异常，必须使“年龄”及

15、“工龄”列的数据区域单元格的“分类”设置为“数值”、“小数位数”设置为“0”）4使用统计函数，根据sheet1中“员工信息表”的数据，对以下条件进行统计。 统计男性员工的人数，结果填入n3单元格中； 统计高级工程师人数，结果填入到n4单元格中； 统计工龄大于等于10的人数，结果填入n5单元格中。操作：在sheet1的n3中输入“countif（d3:d66，男）”即可。在sheet1的n4中输入“countif（i3:i66，高级工程师）”即可。在sheet1的n5中输入“countif（h3:h66，10）”即可。5使用逻辑函数，判断员工是否有资格评“高级工程师”。要求： 评选条件为：工龄大

16、于20，且为工程师的员工； 并将结果保存在“是否有资格评选高级工程师”列中； 如果有资格，保存结果为true，否则为false。操作：在sheet1的k3中输入“and（h320,i3工程师）”【确定】 填充区域。6将sheet1的“员工信息表”复制到sheet2中，并对sheet2进行高级筛选。 要求（a）筛选条件为：“性别”男，“年龄”30，“工龄”10，“职称”助工（b）将结果保存在sheet2中。 注意 （a）无需考虑是否删除或移动筛选条件； （b）复制过程中，将标题项“员工信息表”连同数据一同复制； （c）数据表必须顶格放置。操作：首先将sheet1的“员工信息表”复制到sheet2

17、中。 在sheet2的适合区域建立筛选条件，例如在b68开始的区域建立筛选条件如下： 性别 年龄 工龄 职称 . 采用“复制”“粘贴”的方法 男 30 10 助工 . 从键盘键入（注意符号、数字输入问题） 点击数据区域任意单元格（作用是选中整个表格区域） 数据 筛选 高级筛选 弹出“高级筛选”对话框，在“列表区域”自动显示“$a$2:$k$66” 光标置于”对话框“条件区域” 选择“条件区域”即“b68:e69” 在对话框“条件区域”自动形成“sheet2!$b$68:$e$69”【确定】，即告完成。注意：如果出现错误则：数据 筛选 全部显示。可恢复筛选前状态，后可纠错。7根据sheet1中的

18、“员工信息表”数据，创建一个数据透视图chart1。要求： 显示工厂中各种职称人数的汇总情况； x坐标设置为“职称”； 计数项为职称； 数据区域为“职称”； 将对应的数据透视表保存在sheet3中。操作： sheet1中，点击“员工信息表”内任一单元格（其作用就是选中该区域） 数据 “数据透视表和数据透视图” 弹出“数据透视表和数据透视图向导 步骤之1”对话框 选中“ 据透视图”【下一步】 弹出“数据透视表和数据透视图向导 步骤之2”对话框 “选定区域”自动形成 “$a$2:$k$66”【下一步】 弹出“数据透视表和数据透视图向导 步骤之3”对话框 在数据透视表显示位置下方选中“ 现有工作表”

19、 光标置于“ 现有工作表”下方文本框内 点击“sheet3” 再点击sheet3的a1单元格 在文本框内自动形成“sheet3!$a$1”【完成】 此时就自动创建了一个数据透视图chart1。在chart1中，将自动生成“数据透视表”工具栏、“数据透视表字段列表”工具栏。 将“数据透视表字段列表”中的“职称”拖至x坐标即“在此处放置分类字段”处 以职称为x坐标单位的x坐标就形成了。 将“职称”拖至数据区域 此时透视图就形成了，y坐标为各类职称的数量。 至此全部完成。在sheet3中是“数据透视图”对应的“数据透视表”。【例2】 给定excel文档“20 员工资料表.xls”，按要求完成各题。1

20、在sheet5的a1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因。样式为“警告”，错误信息为“只能录入5位数字或文本”。操作：选中sheet5的a1单元格 数据 有效性 “出错警告”选卡 “样式”下选择“警告”“出错信息”文本框中输入“只能录入5位数字或文本”【确定】。2仅使用mid函数和concatenate函数，对sheet1中“员工资料表”的“出生日期”列进行填充。要求： 填充的内容根据“身份证号码”列的内容来确定： 身份证号码中的第 710位：表示出生年份； 身份证号码中的第1112位：表示出生月份； 身份证号码中的第1314位：表示出生日。 填充结果的格式为：

21、xxxx年xx月xx日。操作：方法 在sheet1的g2单元格输入：“concatenate（mid（e3,7,4），年，mid（e3,11,2），月，mid（e3,13,2)，日）”方法 在sheet1的g2单元格输入：“mid（e3,7,4）&年&mid（e3,11,2）&月&mid（e3,13,2）&日”3根据sheet1中“职务补贴率表”的数据，使用vlookup函数，对“员工资料表”中的“职务补贴率”列进行自动填充。操作：在sheet1的j3单元格输入：“vlookup（h3,$a$2:$b$6,2,0）” 填充数据区 将j3:j38数据区的内容化为百分数。注意：参数中的“2”表示是

22、所选择区域的“第2列”。4使用数组公式，在sheet1中对“员工资料表”的“工资总额”列进行计算，并将结果保存在“工资总额”列。 计算方法：工资总额 基本工资 *（1职务补贴）操作：在sheet1中，先选中“工资总额”下方的数据区“k3:k38” 然后在编辑栏中键入“i3:i38 *（1j3:j38）” 按“ctrlshiftenter”组合键就一次性完成了。注意： 正确结果在编辑栏中的形式如“ i3:i38 *（1j3:j38）”。 不能更改局部“结果”，否则死机！需要更改时，必须首先删除整个区域的内容。5在sheet2中，根据“固定资产情况表”，使用财务函数，对以下条件进行计算。 计算“每

23、天折旧值”，并将结果填入到e2单元格中； 计算“每月折旧值”，并将结果填入到e3单元格中； 计算“每年折旧值”，并将结果填入到e4单元格中。操作：每天折旧值：在sheet2的e2中输入“sln（b2，s3，b4*365）”每月折旧值：在sheet2的e3中输入“sln（b2，s3，b4*12）”每年折旧值：在sheet2的e4中输入“sln（b2，s3，b4）”6将sheet1中的“员工资料表”复制到sheet3，并对sheet3进行高级筛选。 要求 筛选条件为：“性别”女、“职务”高级工程师； 将筛选结果保存在sheet3中。 注意 无需考虑是否删除或移动筛选条件； 复制过程中，将标题项“员

24、工资料表”连同数据一同复制； 数据表必须顶格放置。操作：将sheet1的“员工资料表”复制到sheet3 处理sheet3中的数据错误 在表格下方合适的区域创建筛选条件如下： 性别 职务 女 高级工程师点击表格中的任意单元格 数据 筛选 高级筛选 弹出“高级筛选”对话框（“列表区域”自动形成表格区域地址） 将光标置于“条件区域” 选中条件区域 【确定】。全部完成。7根据sheet1中的“员工资料表”，在sheet4中新建一张数据透视表。要求： 显示每种性别的不同职务的人数汇总情况； 行区域设置为“性别”； 列区域设置为“职务”； 数据区域设置为“职务”； 计数项为职务。操作： 在sheet1的“员工资料表”有效区域点击（作用是选中该区域） 数据 “数据透视表和数据透视图” 弹出“数据透视表和数据透视图向导 步骤之1”对话框 选中“ 据透视表”【下一步】 弹出“数据透视表和数据透视图向导 步骤之2”对话框 “选定区域”自动形成 “$d$2:$k$38”【下一步】 弹出“数据透视表和数据透视图向导 步骤之3”对话框 在数据透视表显示位置下方选中“ 现有工作表” 光标置于“ 现有