EXCEL第二次课件.ppt

作业预告：一、文字录入测试（达到80字/分钟，100分）二、完成前四套（第1套、第2套、第3套、第4套）二级AOA中的EXCEL试题。完成后的作业请先保存在以自己的姓名+学号命名的文件夹下。,第三讲 EXCEL第二次课,一、相对引用、绝对引用和混合引用二、EXCEL数组公式应用三、EXCEL的数据透视表（图）四、EXCEL函数提高篇,相对引用、绝对引用和混合引用体现的是在公式中使用单元格或单元格区域的地址时，当公式向左右或上下单元格复制时，地址是如何变化的。具体情况举例说明：1、相对引用，复制公式时地址跟着发生变化；如C1单元格有公式：=A1+B1 当将公式复制到C2单元格时变为：=A2+B2 当将公式复制到D1单元格时变为：=B1+C1 2、绝对引用，复制公式时地址不会跟着发生变化；如C1单元格有公式：=$A$1+$B$1 当将公式复制到C2单元格时仍为：=$A$1+$B$1 当将公式复制到D1单元格时仍为：=$A$1+$B$1,3、混合引用，复制公式时地址的部分内容跟着发生变化；如C1单元格有公式：=$A1+B$1 当将公式复制到C2单元格时变为：=$A2+B$1 当将公式复制到D1单元格时变为：=$A1+C$1 规律：加上了绝对地址符“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上绝对地址符号的列标和行号为相对地址，在公式向旁边复制时会跟着发生变化。混合引用时部分地址发生变化。注意：工作薄和工作表都是绝对引用，没有相对引用。,Excel数组公式非常有用，尤其在不能使用工作表函数直接得到结果时，数组公式显得特别重要，它可建立产生多值或对一组值而不是单个值进行操作的公式。输入数组公式首先必须选择用来存放结果的单元格区域，在编辑栏输入公式，然后按CtrlShiftEnter组合键锁定数组公式，Excel将在公式两边自动加上花括号“”。注意：不要自己键入花括号，否则，Excel认为输入的是一个正文标签。编辑或删除数组公式编辑数组公式时，须选取数组区域并且激活编辑栏，公式两边的花括号将消失，然后编辑公式，最后按CtrlShiftEnter键。选取数组公式所占有的区域后，按Delete键即可删除数组公式。,数据透视是一种可以快速汇总大量数据的交互式方法。使用数据透视表可以深入分析数值数据，并且可以回答一些预计不到的数据问题。数据透视表是针对以下用途特别设计的：1、以多种友好方式查询大量数据；2、对数值数据进行分类汇总，按分类和子分类对数据进行汇总，创建自定义计算和公式；3、展开或折叠要关注结果的数据级别，查看感兴趣区域摘要数据的明细；4、将行移动到列或将列移动到行，以查看源数据的不同汇总；5、对最有用和最关注的数据子集进行筛选、排序、分组 和有条件地设置格式。数据透视图 是数据透视表的更深层次的应用，它可将数据以图形的形式表示出来。,数据透视表和数据透视图是microsoft excel表格提供的一个便捷的数据分析工具，能够较快的将所需数据呈现在表格或者图形中。一般操作如下：1、Execl中点击菜单栏中“数据”项选择数据透视表或数据透视图；2、弹出一个框框,上面部分选择数据来源,通常情况下是不需要改动直接点击下一步；3、选择需要处理的数据,如果在做第一步之前已经选中了其中一个包含数据的单元格的话，Execl会自动框上需要处理的数据，直接下一步；4、选择数据透视表（图）的建立位置,默认在新工作表中建立；需要指定位置的话选中第二个选项，最后点击完成。5、将你要分析的条件和数据拖到框框里就OK了。,EXCEL二级考试所涉及的全部函数：1、IF函数（题库01、02、03、04、05、06、07、08、10、11、15、18、19）共13题语法：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 时返回的值。实例1：（见题库01）=IF(G3=博士研究生,博士,IF(G3=硕士研究生,硕士,IF(G3=本科,学士,无)注意：其它题目中IF函数和“与”条件、“或”条件的书写格式。比如实例2：（见题库02）=IF(AND(B10=男,D1040),TRUE,FALSE),2、COUNTIF函数（题库02、04、05、06、07、08、11、13、18、19）共10题语法：COUNTIF(range,criteria),功能：计算区域中满足给定条件的单元格的个数。说明：Range 为需要计算其中满足条件的单元格数目的单元格区域。Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。实例：（见题库02）=COUNTIF(Sheet1!B2:B37,男),3、SUMIF函数（题库03、06、09、12、13）语法：SUMIF(range,criteria,sum_range),功能：根据指定条件对若干单元格求和。说明：Range 为用于条件判断的单元格区域。Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。Sum_range 是需要求和的实际单元格。实例：（见题库03）=SUMIF($A$11:$A$43,$I$12:$I$14,$B$11:$B$43),4、RANK函数（题库01、05、09、12）共4题语法：RANK(number,ref,order),功能：返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。说明：Number 为需要找到排位的数字。Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。Order 为一数字，指明排位的方式。如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。实例：（见题库01）=RANK(M3,$M$3:$M$18,0),5、YEAR函数（题库02、08、16）共3题语法：YEAR(serial_number)功能：返回某日期对应的年份。返回值为 1900 到 9999 之间的整数。说明：Serial_number 为一个日期值，其中包含要查找年份的日期。实例：（见题库02）=YEAR(2008-11-15)-YEAR(C2)6、REPLACE函数（题库02、08、11、15）共4题语法：REPLACE(old_text,start_num,num_chars,new_text),功能：使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。,说明：Old_text 是要替换其部分字符的文本。Start_num 是要用 new_text 替换的 old_text 中字符的位置。Num_chars 是希望 REPLACE 使用 new_text 替换 old_text 中字符的个数。New_text 是要用于替换 old_text 中字符的文本。实例：（见题库02）=REPLACE(F3,5,0,8),7、MAX函数（题库04、10）共2题语法：MAX(number1,number2,.)功能：返回一组值中的最大值。实例：（见题库04）=MAX(G9:G39)8、MIN函数（题库10、11）共2题语法：MIN(number1,number2,.)功能：返回一组值中的最小值。实例：（见题库10）=MIN(B2:B16),9、TYPE函数（题库07）共1题语法：TYPE(value)功能：返回数值的类型。说明：类型的返回值为1、2、4、16、64分别表示的数据类型是“数值”、“文本”、“逻辑值”、“误差值”、“数组”。实例：（见题库07）=IF(TYPE(B21)=2,TRUE,FALSE)10、ABS函数（题库10）共1题语法：ABS(number)功能：返回数字的绝对值。绝对值没有符号。实例：（见题库10）=ABS(C2:C16-B2:B16),11、UPPER函数（题库16）共1题功能：将文本转换成大写形式。语法：UPPER(text)说明：Text为需要转换成大写形式的文本。实例：（见题库16）=UPPER(A3)12、TODAY或NOW函数（题库16）共1题功能：返回当前日期的序列号。语法：TODAY()说明：如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。实例：（见题库16）=YEAR(TODAY()-MID(G3,7,4),13、MID函数（题库13、14、16、19）共4题语法：MID(text,start_num,num_chars),功能：返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。,说明：Text 是包含要提取字符的文本字符串。Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。Num_chars 指定希望 MID 从文本中返回字符的个数。实例：（见题库13）=MID(A2,8,1),14、VLOOKUP函数（题库03、12、14、17、20共5题）语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),功能：在表格数组的首列查找值，并由此返回表格数组当前行中其他列的值。VLOOKUP 中的 V 表示垂直方向。说明：Lookup_value为需要在表格数组（数组：用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）第一列中查找的数值。Table_array为两列或多列数据。请使用对区域的引用或区域名称。table_array 第一列中的值是由 lookup_value 搜索的值。这些值可以是文本、数字或逻辑值。不区分大小写。Col_index_num为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时，返回 table_array 第一列中的数值；col_index_num 为 2，返回 table_array 第二列中的数值，以此类推。Range_lookup为逻辑值，指定希望 VLOOKUP 查找精确的匹配值还是近似匹配值：如果为 TRUE 或省略，则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值。table_array 第一列中的值必须以升序排序；否则 VLOOKUP 可能无法返回正确的值。实例：（见题库03）=VLOOKUP(A11,$F$2:$G$4,2,FALSE),15、HLOOKUP函数（题库04）共1题语法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup),功能：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数 HLOOKUP。当比较值位于要查找的数据左边的一列时，请使用函数 VLOOKUP。HLOOKUP 中的 H 代表“行”。说明：Lookup_value 为需要在数据表第一行中进行查找的数值。Lookup_value 可以为数值、引用或文本字符串。Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用。Table_array 的第一行的数值可以为文本、数字或逻辑值。如果 range_lookup 为 TRUE，则 table_array 的第一行的数值必须按升序排列，如果 range_lookup 为 FALSE，则 table_array 不必进行排序。文本不区分大小写。,Row_index_num 为 table_array 中待返回的匹配值的行序号。Row_index_num 为 1 时，返回 table_array 第一行的数值，row_index_num 为 2 时，返回 table_array 第二行的数值，以此类推。Range_lookup 为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值。如果 range_value 为 FALSE，函数 HLOOKUP 将查找精确匹配值，如果找不到，则返回错误值#N/A!。实例：（见题库04）=HLOOKUP($B$9:$B$39,$A$2:$C$3,2,FALSE)=HLOOKUP(B9,$A$2:$C$3,2,FALSE)或=HLOOKUP($B$9:$B$39,$A$2:$C$3,2,FALSE)引用到其它单元格,16、DAVERAGE函数（题库07、15）共2题语法：DAVERAGE(database,field,criteria),功能：计算列表或数据库的列中满足指定条件的数值的平均值。,说明：database：构成列表或数据库的单元格区域。数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。列表的第一行包含每一列的标签。field：指定函数所使用的列。输入列标签，并将其包括在双引号中，例如“树龄”或“产量”；此外，也可以输入代表列表中列位置的数字（不加引号）：1 表示第一列，2 表示第二列，依此类推。criteria：为包含指定条件的单元格区域。可以为参数 criteria 指定任意区域，只要它至少包含一个列标签并且在列标签下方至少有一个用于为该列指定条件的单元格。实例：（见题库07）=DAVERAGE(A1:H17,单价,J2:L3),17、DCOUNT函数（题库07、15、20）共3题语法：DCOUNT(database,field,criteria),功能：计算列表或数据库的列中满足指定条件并且包含数字的单元格个数。,说明：database：构成列表或数据库的单元格区域。数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。列表的第一行包含每一列的标签。field：指定函数所使用的列。输入列标签，并将其包括在双引号中，例如“树龄”或“产量”；此外，也可以输入代表列表中列位置的数字（不加引号）：1 表示第一列，2 表示第二列，依此类推。criteria：为包含指定条件的单元格区域。可以为参数 criteria 指定任意区域，只要它至少包含一个列标签并且在列标签下方至少有一个用于为该列指定条件的单元格。参数 field 为可选项，如果省略，函数 DCOUNT 返回数据库中满足条件 criteria 的所有记录的个数。实例：（见题库07）=DCOUNT(A1:H17,J7:L8),18、PMT函数（题库11）共1题语法：PMT(rate,nper,pv,fv,type),功能：基于固定利率及等额分期付款方式，返回贷款的每期付款额。,说明：Rate 贷款利率。Nper 该项贷款的付款期总数。Pv 现值，或一系列未来付款的当前值的累积和，也称为本金。Fv 为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零，也就是一笔贷款的未来值为零。Type 数字 0 或 1，用以指定各期的付款时间是在期初还是期末。实例：（见题库11）=PMT(B4,B3,B2,0,0)或=PMT(B4,B3,B2,0),19、IPMT函数（题库11）共1题语法：IPMT(rate,per,nper,pv,fv,type),功能：基于固定利率及等额分期付款方式，返回给定期数内对投资的利息偿还额。,说明：Rate 为各期利率。Per 用于计算其利息数额的期数，必须在 1 到 nper 之间。Nper 为总投资期，即该项投资的付款期总数。Pv 为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。Fv 为未来值，或在最后一次付款后希望得到的现金余额。如果省略 fv，则假设其值为零（例如，一笔贷款的未来值即为零）。实例：（见题库11）=IPMT(B4/12,9,B3*12,B2,0),20、FV函数（题库13）共1题语法：FV(rate,nper,pmt,pv,type),功能：基于固定利率及等额分期付款方式，返回某项投资的未来值。,说明：Rate 为各期利率。Nper 为总投资期，即该项投资的付款期总数。Pmt 为各期所应支付的金额，其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。如果忽略 pmt，则必须包括 pv 参数。Pv 为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。如果省略 PV，则假设其值为零，并且必须包括 pmt 参数。Type 数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。实例：（见题库13）=FV(B3,B5,B4,B2,0),21、PV函数（题库13）共1题语法：PV(rate,nper,pmt,fv,type),功能：返回投资的现值。现值为一系列未来付款的当前值的累积和。,说明：Rate 为各期利率。例如，如果按 10%的年利率借入一笔贷款来购买汽车，并按月偿还贷款，则月利率为 10%/12（即 0.83%）。可以在公式中输入 10%/12、0.83%或 0.0083 作为 rate 的值。Nper 为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。例如，对于一笔 4 年期按月偿还的汽车贷款，共有 4*12（即 48）个偿款期数。可以在公式中输入 48 作为 nper 的值。Pmt 为各期所应支付的金额，其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。例如，$10,000 的年利率为 12%的四年期汽车贷款的月偿还额为$263.33。可以在公式中输入-263.33 作为 pmt 的值。如果忽略 pmt，则必须包含 fv 参数。Fv 为未来值，或在最后一次支付后希望得到的现金余额，如果省略 fv，则假设其值为零（一笔贷款的未来值即为零）。例如，如果需要在 18 年后支付$50,000，则$50,000 就是未来值。可以根据保守估计的利率来决定每月的存款额。如果忽略 fv，则必须包含 pmt 参数。Type 数字 0 或 1，用以指定各期的付款时间是在期初还是期末。实例：（见题库13）=PV(E3,E4,E2,0,0)或=PV(E3,E4,E2,0),22、CONCATENATE函数（题库14）共1题语法：CONCATENATE(text1,text2,.)功能：将几个文本字符串合并为一个文本字符串。说明：Text1,text2,.为 1 到 30 个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。实例：（题库14）=CONCATENATE(MID(E3,7,4),年,MID(E3,11,2),月,MID(E3,13,2),日),23、SLN函数（题库14）共1题语法：SLN(cost,salvage,life),功能：返回某项资产在一个期间中的线性折旧值。,说明：Cost 为资产原值。Salvage 为资产在折旧期末的价值（也称为资产残值）。Life 为折旧期限（有时也称作资产的使用寿命）。实例：（见题库14）=SLN(B2,B3,B4*365)=SLN(B2,B3,B4*12)=SLN(B2,B3,B4),24、DGET函数（题库15、16）共2题语法：DGET(database,field,criteria),功能：从列表或数据库的列中提取符合指定条件的单个值。,说明：database：构成列表或数据库的单元格区域。数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。列表的第一行包含每一列的标签。field：指定函数所使用的列。输入列标签，并将其包括在双引号中，例如“树龄”或“产量”；此外，也可以输入代表列表中列位置的数字（不加引号）：1 表示第一列，2 表示第二列，依此类推。criteria：为包含指定条件的单元格区域。可以为参数 criteria 指定任意区域，只要它至少包含一个列标签并且在列标签下方至少有一个用于为该列指定条件的单元格。实例：（见题库15）=DGET(A1:K23,3,M7:N8),25、DMAX函数（题库15）共1题语法：DMAX(database,field,criteria),功能：返回列表或数据库的列中满足指定条件的最大数值。,说明：database：构成列表或数据库的单元格区域。数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。列表的第一行包含每一列的标签。field：指定函数所使用的列。输入列标签，并将其包括在双引号中，例如“树龄”或“产量”；此外，也可以输入代表列表中列位置的数字（不加引号）：1 表示第一列，2 表示第二列，依此类推。criteria：为包含指定条件的单元格区域。可以为参数 criteria 指定任意区域，只要它至少包含一个列标签并且在列标签下方至少有一个用于为该列指定条件的单元格。实例：（见题库15）=DMAX(A1:K23,体育,M12:M13),26、EXACT函数（题库16）共1题功能：测试两个字符串是否完全相同。如果它们完全相同，则返回 TRUE；否则，返回 FALSE。函数 EXACT 能区分大小写，但忽略格式上的差异。语法：EXACT(text1,text2)说明：Text1为待比较的第一个字符串，Text2为待比较的第二个字符串。实例：（见题库16）=EXACT(L12,M12)27、INT函数（题库17）共1题功能：将数字向下（比原数小）舍入到最接近的整数。语法：INT(number)说明：Number为需要进行向下舍入取整的实数。实例：（见题库17）=INT(DAYS360(E4,F4,30)/30),28、FIND、SEARCH函数（题库18）共1题功能：FIND用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并从 within_text 的首字符开始返回 find_text 的起始位置编号。也可使用 SEARCH 查找其他文本字符串中的某个文本字符串，但是，FIND 和 SEARCH 不同，FIND 区分大小写并且不允许使用通配符。语法：FIND(find_text,within_text,start_num)说明：Find_text：是要查找的文本。Within_text：是包含要查找文本的文本。Start_num：指定开始进行查找的字符。within_text 中的首字符是编号为 1 的字符。如果忽略 start_num，则假设其为 1。实例：（见题库18）=FIND(J9,I9,1)=SEARCH(J9,I9),29、COUNTBLANK函数（题库19）共1题功能：计算指定单元格区域中空白单元格的个数。语法：COUNTBLANK(range)说明：Range为需要计算其中空白单元格个数的区域。实例：（题库19）=4-COUNTBLANK(D3:G3)30、DSUM函数（题库20）共1题功能：计算列表或数据库的列中满足指定条件的数字之和。语法：DSUM(database,field,criteria),说明：database：构成列表或数据库的单元格区域。field：指定函数所使用的列。输入列标签，并将其包括在双引号中，也可以输入代表列表中列位置的数字（不加引号）：1 表示第一列，2 表示第二列，依此类推。criteria：为包含指定条件的单元格区域。实例：（题库20）=DSUM(A2:I30,I2,K17:K18)或=DSUM(A2:I30,9,K17:K18)或=DSUM(A2:I30,护理费用(元）,K17:K18),