第二章-excel高级应用(李)电子教案.doc

第二章 excel高级应用(李)精品资料第2章 Excel高级应用2.1 数组的使用2.2.1 数组的意义数组是单元的集合或是一组处理的值的集合。可以写一个数组公式，即输入一个单个的公式，它执行多个输入操作并产生多个结果，每个结果显示在一个单元格区域中。数组公式可以看成有多重数值的公式，它与单值公式的不同之处在于它可以产生一个以上的结果。一个数组公式可以占用一个或多个单元区域，数组元素的个数最多为6500个。数组应用示例：将下图表中的“笔试成绩比例分”列按计算方法：（笔试成绩/3）*60% 填写。图21说明：此问题可以用单个公式编辑再拖动引用公式的方法完成，但不是数组方式。2.1.2 数组的编辑1操作步骤：（1）先选中要定义数组的全部数据区域J3:J18；（2）在编辑栏中按题目要求编写公式“=I3：I18/3*0.6”；（3）按ctrl+shift+enter键，所编辑的公式出现数组标志符号“”，同时J3：J18列各个单元中生成相应结果如下图。图222.其它练习练习示例：对上一问题中“面试成绩比例分”和“总成绩”列分别按公式“面试成绩*0.4”和“笔试成绩比例分+面试成绩比列分”进行填写。操作方法：参照上例进行。2.1.3 数组的修改数组修改示例：将上一题目中“笔试成绩比例分”按公式：（笔试成绩/2）*60%计算。1操作步骤：（1）选中数组的全部数据区域J3:J18；（2）在编辑栏中修改公式“=I3：I18/2*0.6”；（3）按ctrl+shift+enter键，所编辑的公式出现数组标志符号“”，同时J3：J18列各个单元中生成相应结果。2说明：（1）数组公式不能单个进行修改，否则系统提示错误。（2）修改数组过程中数组标记“”会消失，需重新按ctrl+shift+enter键。2.2 高级筛选2.2.1高级筛选对于筛选条件比较复杂的情况，必须使用高级筛选功能来处理。使用高级筛选功能必须先建立一个条件区域，用来指定筛选条件。条件区域的第一行是所有作为筛选条件的字段名，这些字段名与数据列表中的字段名必须一致，条件区域的其它行则输入筛选条件。需要注意的是条件区域与数据列表不能重叠，必须用空行或空列隔开。条件区域的运算关系是：同一行的条件是“与”，同一列的条件是“或”。高级筛选应用示例：将下图表中的数据进行筛选，条件为：性别-女，所在区域-西湖区图232.2.2高级筛选的编辑1操作步骤：（1）在J3单元格输入“性 别”，在K3单元格输入“所在区域”，在J4单元格输入“女”，在K4单元格输入“西湖区”；（2）将光标点中数据区的任意位置；图 2 4（3）点击菜单“数据”筛选”高级筛选”,出现数据区和条件区定义对话框;(4)在对话框中定义对应的数据区$A$1:$H$37和条件区$J$3:$K$4，见图2-4，点击“确定”，生成筛选结果，如图2-5。图 2 52说明：（1）点击筛选菜单前必须把光标点进数据区，否则不能正确筛选。（2）如果列名称中有空格，如“性 别”，在筛选区中也使用相同的样式输入，否则不能正确筛选。（3）条件的具体值如果是文本型如“女”、“西湖区”，直接书写；如果是数值型应写成相应的数学表达式，如条件“年龄大于40”，应在年龄字段名下写“40”。2.2.3撤销高级筛选操作方法：点击菜单“数据”筛选”全部显示”,恢复筛选前数据状态。2.3 透视表与透视图的使用2.3.1使用透视表一、透视表概述数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格，不仅能够改变行和列以查看源数据的不同汇总结果，也可以显示不同页面以筛选数据，还可以根据需要显示区域中的明细数据。二、透视表的创建方法透视表应用示例：对图2-1表中的数据建立一个数据透视表，具体要求（a）显示每个报考单位的人的不同学历的总人数；（b）行区域设置为“报考单位”；（c）列区域设置为“学历”；（d）数据区域设置为“学历”;（e）统计为“学历”。操作步骤：(1)点击图表中的任意位置，选择“数据”“数据透视表和数据透视图向导”如图2-6,点击“下一步”;图 2 6（2）出现图2-7，定义建立透视表的数据区，点击“下一步”；图 27（3）定义透视表建立的位置，如图2-8，点击“完成”；图28（4）在如图2-9的环境中定义透视表的具体内容，根据题目要求将数据表字段列表窗口的“报考单位”字段拖动到透视表区域中的“将行字段拖至此处”位置，将“学历”字段拖动到透视表区域中的“将列字段拖至此处”位置，最后将将“学历”字段拖动到透视表区域中的“请将数据项拖至此处”位置。图29（5）生成透视表结果如图2-10图220说明：透视表的内容也可以在步骤(3)中点击“布局”来定义。2.3.2使用透视图一、概述数据透视图是将数据透视表结果赋以更加生动、形象的表示方式。因为数据透视图需利用数据透视表的结果，因此其操作是与透视表相关联的。二、创建方法 1直接创建透视图仍以图2-1为例，创建反映各报考单位学历状况的统计透视图。操作步骤：(1)见透视表操作步骤1，在图2-6 “所需创建的报表类型”中选择“数据透视图”；(2)见透视表操作步骤2；(3)见透视表操作步骤3；(4)在图2-11中的字段列表框中将“报考单位”字段拖至图下方的“在此处放置分类字段”区域，将“学历”字段拖放至右侧的“在此处放置系列字段”处，将“学历”字段拖放至中央数据区，生成如图2-12所示的结果。图2312通过透视表创建透视图在建立透视表后可直接建立相应的透视图。操作步骤：在已建立的透视表窗口，见图2-10，点击“图表向导”图标，也可以生成图2-12的透视图结果。图242说明：创建透视图chart1的同时还会自动生成一张透视表。2.4常用函数与应用示例2.4.1日期时间函数1.year函数功能：返回某日期对应的年份，返回值为 1900 到 9999 之间的整数。格式：YEAR(serial_number)。说明：serial_number是一个日期值，也可以是格式为日期格式的单元格名称。2.today函数功能：返回当前日期。格式：TODAY()日期函数应用示例：使用日期函数自动填写图2-3中的“年龄”字段结果。操作步骤（1）点击D2单元格，在编辑栏中编辑公式“ =YEAR(TODAY()-YEAR(C2)”；（2）D2单元格产生结果42，用拖动引用公式的方法自动填充整个列年龄数据。3.minute函数功能：返回时间值中的分钟，即一个介于 0 到 59 之间的整数。格式：MINUTE(serial_number)说明：serial_number是一个时间值，也可以是格式为时间格式的单元格名称。4.hour函数功能：返回时间值的小时数。即一个介于 0到23之间的整数。格式：HOUR(serial_number)说明：serial_number是一个时间值，也可以是格式为时间格式的单元格名称。时间函数应用示例：见下一节中实战示例2问题2计算停车时间问题。2.4.2逻辑函数1.AND（与）函数功能：在其参数组中，所有参数逻辑值为 TRUE，即返回 TRUE。格式：AND(logical1,logical2,.)。说明：logical1,logical2,. 为需要进行检验的 1 到 30 个条件，分别为 TRUE 或 FALSE。2.OR（或）函数功能：在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE。格式：OR(logical1,logical2,.)。说明：logical1,logical2,. 为需要进行检验的 1 到 30 个条件，分别为 TRUE 或 FALSE。AND和OR函数应用示例：见下一节中实战示例1问题4闰年问题。3.IF函数功能：执行真假值判断，根据逻辑计算的真假值，返回不同结果。格式：IF(logical_test,value_if_true,value_if_false)。说明：Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式，Value_if_true是logical_test 为 TRUE 时返回的值，Value_if_false是logical_test 为 FALSE 时返回的值。If函数应用示例：见图2-1，使用IF函数，对Sheet1中的“学位”列进行自动填充。 要求：填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）： - 博士研究生博士 - 硕士研究生硕士 - 本科学士 - 其他无 操作步骤（1）点击H3单元格，在编辑栏中编辑公式“=IF(G3=博士研究生,博士,IF(G3=硕士研究生,硕士,IF(G3=本科,学士,无)”；（2）H3单元格产生结果博士，用拖动引用公式的方法生成整个列学位数据。操作说明（1）编辑公式中的各种符号应使用英文半角字符。（其它公式编辑也同样，不再赘述。）（2）公式中的字符信息前后必须使用定界符“ “” ”。（3）IF嵌套的层数不超过7个。2.4.3算术与统计函数1.RANK函数功能：为指定单元的数据在其所在行或列数据区所处的位置排序。格式：RANK(number,reference,order)。说明：number是被排序的值，reference是排序的数据区域，order是升序、降序选择，其中order取0值按降序排列，order取1值按升序排列。RANK函数应用示例：对图2-1中的数据，根据“笔试成绩”自动生成“排名”列的相应值。操作步骤（1）点击N3单元格，在编辑栏中编辑公式“=RANK（I3,$I$3:I18,0）”（2）N3单元格产生结果1，用拖动引用公式的方法生成整个列学位数据。操作说明（1）范围起始参数必须用”$I$3”即绝对引用方式,如使用“I3”相对引用方式则排序错误。2.MOD函数功能：返回两数相除的余数。格式：MOD(number,divisor)。说明：Number 为被除数，Divisor 为除数。MOD函数应用示例：见下一节中实战示例1问题4闰年问题。3.MAX函数功能：返回一组值中的最大值。格式：MAX(number1,number2,.)。说明：Number1, number2, . 是要从中找出最大值的 1 到 30 个数字参数。MAX函数应用示例：见下一节中实战示例2问题4统计最高停车费用问题。4.COUNTIF函数功能：计算区域中满足给定条件的单元格的个数。格式：COUNTIF(range,criteria)说明：Range 为需要计算其中满足条件的单元格数目的单元格区域。Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。COUNTIF函数应用示例：见下一节中实战示例1问题2统计图书种类问题。5.SUMIF函数功能：根据指定条件对若干单元格求和。格式：SUMIF(range,criteria,sum_range)。说明：Range 为用于条件判断的单元格区域，Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。SUMIF函数应用示例：见下一节中实战示例1问题3统计各用户总金额问题。2.4.4查找函数1.HLOOKUP函数功能：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。格式：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)。说明：Lookup_value 为需要在数据表第一行中进行查找的数值。Lookup_value 可以为数值、引用或文本字符串。Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用。Row_index_num 为 table_array 中待返回的匹配值的行序号。Range_lookup 为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值。说明：Lookup_value的值必须与table_array第一行的内容相对应。HLOOKUP函数应用示例：见下一节中实战示例2问题1统计自动填充停车费单价问题。2.VLOOKUP函数说明：VLOOKUP函数的用法与HLOOKUP基本一致，不同在于table_array数据表的数据信息是以行的形式出现，见图2-18中A2:C3区域，而VLOOKUP的table_array数据表是以列的形式出现。2.4.5文本函数1.REPLACE函数功能：使用其它文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。格式：REPLACE(old_text,start_num,num_chars,new_text)。说明：Old_text 是要替换其部分字符的文本；Start_num 是要用 new_text 替换的 old_text 中字符的位置；Num_chars 是希望 REPLACE 使用 new_text 替换 old_text 中字符的个数，如果Num_chars 为“0”，是在指定位置插入新字符；New_text 是要用于替换 old_text 中字符的文本。REPLACE函数应用示例：在图2-3所示的表中，对“原电话号码”列中的电话号码进行升级。 升级方法是在区号（0571）后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。操作步骤（1）点击G2单元格，在编辑栏中编辑公式“=replace（f2,5,0,8）”;（2）G2单元格产生修改后的电话号码，用拖动引用公式的方法生成整个列数据。2.MID函数功能：返回文本字符串中从指定位置开始的特定数目的字符。格式：MID(text,start_num,num_chars)。说明：text 是包含要提取字符的文本字符串，Start_num 是文本中要提取的第一个字符的位置，Num_chars 指定希望 MID 从文本中返回字符的个数。3.CONCATENATE函数功能：将几个文本字符串合并为一个文本字符串。格式：CONCATENATE (text1,text2,.)。说明：Text1, text2, . 为 1 到 30 个将要合并成单个文本项的文本项。MID函数与CONCATENATE函数应用示例：图2-13见图2-13，仅使用文本函数MID函数和CONCATENATE函数，对Sheet1中的“出生日期”列进行自动填充。 要求：a. 填充的内容根据“身份证号码”列的内容来确定： - 身份证号码中的第7位第10位：表示出生年份; - 身份证号码中的第11位第12位：表示出生月份; - 身份证号码中的第13位第14位：表示出生日; b. 填充结果的格式为：xxxx年xx月xx日（注意：不使用单元格格式进行设置）。操作步骤（1）点击G3单元格，在编辑栏中编辑公式“=CONCATENATE(MID(E3,7,4),年,MID(E3,11,2),月,MID(E3,13,2),日)”（2）G3单元格产生指定格式的数据，用拖动引用公式的方法生成整个列数据。2.4.6其它函数二级等级考试的EXCEL部分对一级考试涉及的常用函数如SUM、AVERAGE等仍然需要熟练掌握。另外还涉及一些函数需要掌握，限于篇幅大小不能详细介绍，此处仅仅做简单说明。1.SLN函数功能：返回某项资产在一个期间中的线性折旧值。格式：SLN(cost,salvage,life)说明：Cost 为资产原值,Salvage 为资产在折旧期末的价值（也称为资产残值）, Life 为折旧期限（有时也称作资产的使用寿命）。2.TYPE函数功能：返回数值的类型。格式：TYPE(value) .说明：Value是数字、文本以及逻辑值等类型的表格数据，如果是数字，type的返回值为1；如果是字符，type的返回值为2；如果是逻辑值，type的返回值为4。3.PMT函数功能：基于固定利率及等额分期付款方式，返回贷款的每期付款额。格式：PMT(rate,nper,pv,fv,type)。说明：Rate 贷款利率。Nper 该项贷款的付款总数。Pv 现值，或一系列未来付款的当前值的累积和，也称为本金。Fv 为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零，也就是一笔贷款的未来值为零。Type 数字 0 或 1，用以指定各期的付款时间是在期初还是期末, 0 或省略为期末，1为期初。4.IPMT函数功能：基于固定利率及等额分期付款方式，返回给定期数内对投资的利息偿还额。格式：IPMT(rate,per,nper,pv,fv,type)。说明：Per 用于计算其利息数额的期数，必须在 1 到 nper 之间。其它与上以函数相同。5.数据库函数数据库函数是一类函数，其基本格式为 函数名（database,field,criteria）,其中database是构成数据清单或数据库的单元格区域，field是函数所使用的数据列，criteria是一组包含给定条件的单元格区域。需要掌握的数据库函数有DCOUNT,DGET,DAVERAGE,DSUM等。2.5二级考试实战示例与练习实战示例1如图2-14图2-14题目：在考生文件夹的Paper子文件夹中，已有DExcel.xls文件。按下列要求操作，并将结果存盘。1. 使用数组公式，计算Sheet1中的订购金额，将结果保存到表中的 “金额”列当中。2. 使用统计函数，对Sheet1中结果按以下条件进行统计，并将结果保存在 Sheet1中的相应位置，要求： a. 统计出版社名称为“高等教育出版社”的书的种类数； b. 统计订购数量大于110且小于850的书的种类数。3. 使用函数计算，每个用户所订购图书所需支付的金额总数，将结果 保存在Sheet1中的相应位置。4. 使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”， 如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。 说明：闰年定义：年数能被4整除而不能被100整除，或者能被400整除的年份。5. 将Sheet1复制到Sheet3中，对Sheet3进行高级筛选，要求： a. 筛选条件为“订数=500，且金额总数110)- COUNTIF(G2:G51,850)”；（4） 确定公式，生成统计结果。说明：（1） COUNTIF函数中如果使用条件表达式需要用引号引起。（2） 一个COUNTIF函数不能同时使用两个表达式，如3中的两个条件关系需转化为两个COUNTIF函数计算。问题3：操作步骤（1） 在Sheet1L8单元格中编辑公式“=SUMIF(A2:A51,C1,I2:I51)”，确定公式（2） 在Sheet1L9单元格中编辑公式“=SUMIF(A2:A51,C2,I2:I51)”，确定公式（3） 在Sheet1L10单元格中编辑公式“=SUMIF(A2:A51,C3,I2:I51)”，确定公式（4） 在Sheet1L11单元格中编辑公式“=SUMIF(A2:A51,C4,I2:I51)”，确定公式问题4：操作步骤（1）选中Sheet2表格，出现图2-15；（2）在B2单元格中编辑公式“=if（or（and（mod（A2，4）=0，mod（A2，1000）,mod(A2,400)=0）,”闰年”,”平年”）”,确定公式；图 2 15（3）拖动引用B2公式至B21，生成全部结果。问题5：操作步骤（1） 选择Sheet1中表格内容，复制到Sheet3中粘贴（A1：I51部分）；（2） 在Sheet3K4：L5单元格编辑高级筛选条件，其中K4中为“订数”，K5中为“金额总数”，L4中为“=500”，L5中为“30； b. 将结果保存在Sheet2中。（注：无需考虑是否删除筛选条件）6. 根据Sheet1，创建一个数据透视图Chart1，要求： a. 显示各种车型所收费用的汇总； b. 行区域设置为“车型”； c. 计数项为“应付金额”； d. 将对应的数据透视表保存在Sheet3中。问题1：操作步骤（1）在Sheet1中选中C9单元格；（2）在编辑栏中书写公式“=HLOOKUP(B9:B39,$A$2:$C$3,2,FALSE)”；（3）确定公式，拖动引用C9公式至C39，生成全部价格。问题2：操作步骤（1）在Sheet1中选中F9单元格；（2）在编辑栏中书写公式“=E9-D9)”；（3）确定公式，拖动引用F9公式至F39，生成停车时间。问题3：操作步骤（1）在Sheet1中选中G9单元格；（2）在编辑栏中书写公式“=if(minute(f9)15,(hour(f9)+1)*c9,hour(f9)*c9)”；（3）确定公式，拖动引用G9公式至G39，生成停车应付金额。问题4：操作步骤（1）在Sheet1中选中J8单元格；（2）在编辑栏中书写公式“=countif(g9:g39,”=40”)”,确定公式；（3）选中J9单元格；（4）在编辑栏中书写公式“=max(g9:g39)”,确定公式。问题5：操作步骤（1） 选择Sheet2，见图2-19;（2） 在I1:J2区域输入筛选条件：车型，小汽车，应付金额，30;（3） 鼠标点击数据区任意位置，点击菜单“数据”筛选”高级筛选”,出现数据区和条件区定义对话框;（4） 定义数据区和条件区位置，确定生成筛选结果。问题6：操作步骤（1） 选择Sheet1，点击图表中的任意位置，选择“数据”“数据透视表和数据透视图向导”；（2） 报表类型选择数据透视图，定义数据区，完成基本定义出现图2-20；图2-20（3） 将字段列表中的“车型”拖动至下方分类字段放置处，“应付金额”字段拖放至右侧系列字段放置处，再将“应付金额”字段拖放至数据区，生成透视图如图2-21;（4） 将系统自动生成在Sheet4中的透视表复制到Sheet3中。图2-21实战练习1如图2-3与图2-22，其中图2-3为sheet1表，图2-22为sheet2表。图2-22题目：在考生文件夹的Paper子文件夹中，已有DExcel.xls文件。按下列要求操作，并将结果存盘。1. 使用时间函数，对Sheet1中用户的年龄进行计算。 要求：计算用户的年龄，并将其计算结果填充到“年龄”列当中。2. 使用REPLACE函数，对Sheet1中用户的电话号码进行升级。 要求：对“原电话号码”列中的电话号码进行升级。 升级方法是在区号（0571）后面加上“8”， 并将其计算结果保存在“升级电话号码”列的相应单元格中。3. 使用逻辑函数，判断Sheet1中的“大于等于40岁的男性”， 将结果保存在Sheet1中的“是否=40男性”。4. 对Sheet1中的数据，根据以下条件,利用函数进行统计： a. 统计性别为“男”的用户人数，将结果填入Sheet2的B1单元格中； b. 统计年龄为“40”岁的用户人数，将结果填入Sheet2的B2单元格中。5. 将Sheet1复制到Sheet3，并对Sheet3进行高级筛选。 a. 筛选条件为：“性别”女、“所在区域”西湖区； b. 将筛选结果保存在Sheet3中。（注：无需考虑是否删除筛选条件）6. 根据Sheet1的结果，创建一数据透视图Chart1，要求： a. 显示每个区域所拥有的用户数量； b. x坐标设置为“所在区域”； c. 计数项为“所在区域”； d. 将对应的数据透视表保存在Sheet4中。实战练习2如图2-13与图2-23，其中图2-13为sheet1表，图2-23为sheet2表。图2-23题目：在考生文件夹的Paper子文件夹中，已有DExcel.xls文件。按下列要求操作，并将结果存盘。1. 仅使用文本函数MID函数和CONCATENATE函数，对Sheet1中的“出生日期”列进行自动填充。 要求：a. 填充的内容根据“身份证号码”列的内容来确定： - 身份证号码中的第7位第10位：表示出生年份; - 身份证号码中的第11位第12位：表示出生月份; - 身份证号码中的第13位第14位：表示出生日; b. 填充结果的格式为：xxxx年xx月xx日（注意：不使用单元格格式进行设置）。2. 根据Sheet1中“职务补贴率表”的数据，使用VLOOKUP函数，对“员工资料表”中的“职务补贴率”列进行自动填充。3. 使用数组公式，在Sheet1中“员工资料表”的“工资总额”列进行计算。 计算方法：工资总额=基本工资*（1+职务补贴）4. 在Sheet2中，根据“固定资产情况表”，利用财务函数，对以下条件进行计算。 条件：a. 计算“每天折旧值”，并将结果填入到E2单元格中； b. 计算“每月折旧值”，并将结果填入到E3单元格中； c. 计算“每年折旧值”，并将结果填入到E4单元格中。5. 将Sheet1中的“员工资料表”复制到Sheet3，并对Sheet3进行高级筛选。 a. 筛选条件为：“性别”女、“职务”- 高级工程师； b. 将筛选结果保存在Sheet3中。（注：无需考虑是否删除筛选条件）。6. 根据Sheet1中的“员工资料表”，在Sheet4中新建一数据透视表。要求： a. 显示每种姓名的不同职务的人数汇总情况； b. 行区域设置为“性别”； c. 列区域设置为“职务”； d. 数据区域设置为“职务”； e. 计数项为职务。仅供学习与交流，如有侵权请联系网站删除 谢谢49