Excel函数的高级应用

单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Excel,函数的高级应用,第一章 高级应用基础,工作簿和工作表的区别,工作表的重命名,分数及文本输入，不同单元格输入同一内容,单元格的三种引用方式,文本和数据合并方法,数据筛选,数据有效性,打印区域设置,第二章 数据的链接及应用,1,、跨工作表引用数据,数据表名！单元格名，也可用复制,粘贴，然后单元格旁边小图标，选择“链接单元格”,2,、在实例中的应用,第一节 工作薄及工作表相关,工作薄是由工作表构成，工作表是由行和列构成。,工作表的插入方法,工作表重命名、复制、移动,回到章节,第二节 一些数据的输入,1,、分数的录入,0+,空格,+,分数 如：,0 1/5,2,、数字文本录入,+,数字，如,340222199909091112,3,、不同的单元格输入同一内容,用,CTRL,键选择不同的单元格，然后在编辑框录入数据，录入完成按,CTRL+,对号,回到章节,第三节 单元格的三种引用方式,1,、相对引用,如单元格,A1,2,、绝对引用,如单元格,$A$1,，读着绝对的,A,列，绝对的,1,行,3,、混合引用,如单元格,A$1,读着相对的,A,列，绝对的,1,行,回到章节,第四节 文本和数据合并方法,1,、两列文本的合并,如,A1,列数据为“张”，,B1,列数据为,”,三”，两列数据在,C1,列，在,C1,列输入：,=A1,空格,&,空格,B1,2,、数据合并,如,A1,列数据为：,2,B1,数据为,4,要把,B1,列数据加入到,A1,列，复制,B1,列，点,A1,列，点编辑,-,选择性粘贴，选择相关选项。,回到章节,第五节 数据筛选,1,、自动筛选,2,、高级筛选,二个条件以上的筛选，有“和”与“或”两种情况。,回到章节,第六节 数据有效性,1,、点“数据”,“,有效性”,点“设置,”,，“输入信息”，“出错警告”等，进行相应的设定,回到章节,第七节 打印区域设置,点“文件”,“,页面设置”,“,工作表”,“,顶端标题行”，然后选择要在每一页中要出现的标题行，如,1,和,2,两行。,回到章节,第二章 常用函数及应用,1,、,ABS,函数,2,、,AND,函数,3,、,AVERAGE,函数,4,、,COLUMN,函数,5,、,CONCATENATE,函数,6,、,COUNTIF,函数,7,、,DATE,函数,8,、,函数名称：,DATEDIF,9,、,DAY,函数,10,、,DCOUNT,函数,11,、,FREQUENCY,函数,12,、,IF,函数,13,、,INT,函数,14,、,LEFT,函数,15,、,LEN,函数,16,、,MATCH,函数,17,、,MAX,函数,18,、,MID,函数,19,、,MIN,函数,20,、,MOD,函数,21,、,MONTH,函数,22,、,NOW,函数,23,、,RANK,函数,24,、,RIGHT,函数,25,、,SUMIF,函数,26,、,VLOOKUP,函数,一,ABS,函数,函数名称：,ABS,主要功能：求出相应数字的绝对值。 使用格式：,ABS(number,),参数说明：,number,代表需要求绝对值的数值或引用的单元格。 应用举例：如果在,B2,单元格中输入公式：,=ABS(A2),，则在,A2,单元格中无论输入正数（如,100,）还是负数（如,-100,），,B2,中均显示出正数（如,100,）。 特别提醒：如果,number,参数不是数值，而是一些字符（如,A,等），则,B2,中返回错误值“,#VALUE,！”。,回到首页,二,AND,函数,函数名称：,AND,主要功能：返回逻辑值：如果所有参数值均为逻辑“真（,TRUE,）”，则返回逻辑“真（,TRUE,）”，反之返回逻辑“假（,FALSE,）”。 使用格式：,AND(logical1,logical2, .),参数说明：,Logical1,Logical2,Logical3,：表示待测试的条件值或表达式，最多这,30,个。 应用举例：在,C5,单元格输入公式：,=AND(A5=60,B5=60),，确认。如果,C5,中返回,TRUE,，说明,A5,和,B5,中的数值均大于等于,60,，如果返回,FALSE,，说明,A5,和,B5,中的数值至少有一个小于,60,。 特别提醒：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“,#VALUE!”,或“,#NAME”,。,回到首页,三,AVERAGE,函数,函数名称：,AVERAGE,主要功能：求出所有参数的算术平均值。 使用格式：,AVERAGE(number1,number2,),参数说明：,number1,number2,：需要求平均值的数值或引用单元格（区域），参数不超过,30,个。 应用举例：在,B8,单元格中输入公式：,=AVERAGE(B7:D7,F7:H7,7,8),，确认后，即可求出,B7,至,D7,区域、,F7,至,H7,区域中的数值和,7,、,8,的平均值。 特别提醒：如果引用区域中包含“,0”,值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。,回到首页,四,COLUMN,函数,函数名称：,COLUMN,主要功能：显示所引用单元格的列标号值。 使用格式：,COLUMN(reference,),参数说明：,reference,为引用的单元格。 应用举例：在,C11,单元格中输入公式：,=COLUMN(B11),，确认后显示为,2,（即,B,列）。,回到首页,五,CONCATENATE,函数,函数名称：,CONCATENATE,主要功能：将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。 使用格式：,CONCATENATE(Text1,，,Text),参数说明：,Text1,、,Text2,为需要连接的字符文本或引用的单元格。 应用举例：在,C14,单元格中输入公式：,=CONCATENATE(A14,“”,B14,“.com”),，确认后，即可将,A14,单元格中字符、,、,B14,单元格中的字符和,.com,连接成一个整体，显示在,C14,单元格中。 特别提醒：如果参数不是引用的单元格，且为文本格式的，请给参数加上英文状态下的双引号，如果将上述公式改为：,=A14&“”&B14&“.com”,，也能达到相同的目的。,回到首页,六,COUNTIF,函数,函数名称：,COUNTIF,主要功能：统计某个单元格区域中符合指定条件的单元格数目。 使用格式：,COUNTIF(Range,Criteria,),参数说明：,Range,代表要统计的单元格区域；,Criteria,表示指定的条件表达式。 应用举例：在,C17,单元格中输入公式：,=COUNTIF(B1:B13,“=80”),，确认后，即可统计出,B1,至,B13,单元格区域中，数值大于等于,80,的单元格数目。 特别提醒：允许引用的单元格区域中有空白单元格出现。,回到首页,七,DATE,函数,函数名称：,DATE,主要功能：给出指定数值的日期。 使用格式：,DATE(year,month,day,),参数说明：,year,为指定的年份数值（小于,9999,）；,month,为指定的月份数值（可以大于,12,）；,day,为指定的天数。 应用举例：在,C20,单元格中输入公式：,=DATE(2003,13,35),，确认后，显示出,2004-2-4,。 特别提醒：由于上述公式中，月份为,13,，多了一个月，顺延至,2004,年,1,月；天数为,35,，比,2004,年,1,月的实际天数又多了,4,天，故又顺延至,2004,年,2,月,4,日。,回到首页,八,DATEDIF,主要功能：计算返回两个日期参数的差值。使用格式：,=DATEDIF(date1,date2,“y”),、,=DATEDIF(date1,date2,“m”),、,=DATEDIF(date1,date2,“d”),参数说明：,date1,代表前面一个日期，,date2,代表后面一个日期；,y,（,m,、,d,）要求返回两个日期相差的年（月、天）数。应用举例：在,C23,单元格中输入公式：,=DATEDIF(A23,TODAY(),“y”),，确认后返回系统当前日期,用,TODAY(),表示）与,A23,单元格中日期的差值，并返回相差的年数。特别提醒：这是,Excel,中的一个隐藏函数，在函数向导中是找不到的，可以直接输入使用，对于计算年龄、工龄等非常有效。,回到首页,九,DAY,函数,函数名称：,DAY,主要功能：求出指定日期或引用单元格中的日期的天数。使用格式：,DAY(serial_number,),参数说明：,serial_number,代表指定的日期或引用的单元格。应用举例：输入公式：,=DAY(“2003-12-18”),，确认后，显示出,18,。特别提醒：如果是给定的日期，请包含在英文双引号中。,回到首页,十,DCOUNT,函数,函数名称：,DCOUNT,主要功能：返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。使用格式：,DCOUNT(database,field,criteria,),参数说明：,Database,表示需要统计的单元格区域；,Field,表示函数所使用的数据列（在第一行必须要有标志项）；,Criteria,包含条件的单元格区域。应用举例：如图,1,所示，在,F4,单元格中输入公式：,=DCOUNT(A1:D11,“,语文,”,F1:G2),，确认后即可求出“语文”列中，成绩大于等于,70,，而小于,80,的数值单元格数目（相当于分数段人数）。特别提醒：如果将上述公式修改为：,=DCOUNT(A1:D11,F1:G2),，也可以达到相同目的。,回到首页,十一,FREQUENCY,函数,函数名称：,FREQUENCY,主要功能：以一列垂直数组返回某个区域中数据的频率分布。使用格式：,FREQUENCY(data_array,bins_array,),参数说明：,Data_array,表示用来计算频率的一组数据或单元格区域；,Bins_array,表示为前面数组进行分隔一列数值。应用举例：如图,2,所示，同时选中,B32,至,B36,单元格区域，输入公式：,=FREQUENCY(B2:B31,D2:D36),，输入完成后按下“,Ctrl+Shift+Enter,”,组合键进行确认，即可求出,B2,至,B31,区域中，按,D2,至,D36,区域进行分隔的各段数值的出现频率数目（相当于统计各分数段人数）。特别提醒：上述输入的是一个数组公式，输入完成后，需要通过按“,Ctrl+Shift+Enter,”,组合键进行确认，确认后公式两端出现一对大括号（,），此大括号不能直接输入。,回到首页,十二,IF,函数,函数名称：,IF,主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。使用格式：,=,IF(Logical,Value_if_true,Value_if_false,),参数说明：,Logical,代表逻辑判断表达式；,Value_if_true,表示当判断条件为逻辑“真（,TRUE,）”时的显示内容，如果忽略返回“,TRUE”,；,Value_if_false,表示当判断条件为逻辑“假（,FALSE,）”时的显示内容，如果忽略返回“,FALSE”,。应用举例：在,C29,单元格中输入公式：,=IF(C26=18,“,符合要求,”,“,不符合要求,”,),，确信以后，如果,C26,单元格中的数值大于或等于,18,，则,C29,单元格显示“符合要求”字样，反之显示“不符合要求”字样。特别提醒：本文中类似“在,C29,单元格中输入公式”中指定的单元格，读者在使用时，并不需要受其约束，此处只是配合本文所附的实例需要而给出的相应单元格，具体请大家参考所附的实例文件。,回到首页,十三,INT,函数,函数名称：,INT,主要功能：将数值向下取整为最接近的整数。使用格式：,INT(number,),参数说明：,number,表示需要取整的数值或包含数值的引用单元格。应用举例：输入公式：,=INT(18.89),，确认后显示出,18,。特别提醒：在取整时，不进行四舍五入；如果输入的公式为,=INT(-18.89),，则返回结果为,-19,。,回到首页,十四,LEFT,函数,函数名称：,LEFT,主要功能：从一个文本字符串的第一个字符开始，截取指定数目的字符。使用格式：,LEFT(text,num_chars,),参数说明：,text,代表要截字符的字符串；,num_chars,代表给定的截取数目。应用举例：假定,A38,单元格中保存了“我喜欢天极网”的字符串，我们在,C38,单元格中输入公式：,=LEFT(A38,3),，确认后即显示出“我喜欢”的字符。特别提醒：此函数名的英文意思为“左”，即从左边截取，,Excel,很多函数都取其英文的意思。,回到首页,十五,LEN,函数,函数名称：,LEN,主要功能：统计文本字符串中字符数目。使用格式：,LEN(text,),参数说明：,text,表示要统计的文本字符串。应用举例：假定,A41,单元格中保存了“我今年,28,岁”的字符串，我们在,C40,单元格中输入公式：,=LEN(A40),，确认后即显示出统计结果“,6”,。特别提醒：,LEN,要统计时，无论中全角字符，还是半角字符，每个字符均计为“,1”,；与之相对应的一个函数,LENB,，在统计时半角字符计为“,1”,，全角字符计为“,2”,。,回到首页,十六,MATCH,函数,函数名称：,MATCH,主要功能：返回在指定方式下与指定数值匹配的数组中元素的相应位置。使用格式：,MATCH(lookup_value,lookup_array,match_type,),参数说明：,Lookup_value,代表需要在数据表中查找的数值；,Lookup_array,表示可能包含所要查找的数值的连续单元格区域；,Match_type,表示查找方式的值（,-1,、,0,或,1,）。如果,match_type,为,-1,，查找大于或等于,lookup_value,的最小数值，,Lookup_array,必须按降序排列；如果,match_type,为,1,，查找小于或等于,lookup_value,的最大数值，,Lookup_array,必须按升序排列；如果,match_type,为,0,，查找等于,lookup_value,的第一个数值，,Lookup_array,可以按任何顺序排列；如果省略,match_type,，则默认为,1,。应用举例：如图,4,所示，在,F2,单元格中输入公式：,=MATCH(E2,B1:B11,0),，确认后则返回查找的结果“,9”,。特别提醒：,Lookup_array,只能为一列或一行,回到首页,十七,MAX,函数,函数名称：,MAX,主要功能：求出一组数中的最大值。使用格式：,MAX(number1,number2),参数说明：,number1,number2,代表需要求最大值的数值或引用单元格（区域），参数不超过,30,个。应用举例：输入公式：,=MAX(E44:J44,7,8,9,10),，确认后即可显示出,E44,至,J44,单元和区域和数值,7,，,8,，,9,，,10,中的最大值。特别提醒：如果参数中有文本或逻辑值，则忽略。,回到首页,十八,MID,函数,函数名称：,MID,主要功能：从一个文本字符串的指定位置开始，截取指定数目的字符。使用格式：,MID(text,start_num,num_chars,),参数说明：,text,代表一个文本字符串；,start_num,表示指定的起始位置；,num_chars,表示要截取的数目。应用举例：假定,A47,单元格中保存了“我喜欢天极网”的字符串，我们在,C47,单元格中输入公式：,=MID(A47,4,3),，确认后即显示出“天极网”的字符。特别提醒：公式中各参数间，要用英文状态下的逗号“,”,隔开。,回到首页,十九,MIN,函数,函数名称：,MIN,主要功能：求出一组数中的最小值。使用格式：,MIN(number1,number2),参数说明：,number1,number2,代表需要求最小值的数值或引用单元格（区域），参数不超过,30,个。应用举例：输入公式：,=MIN(E44:J44,7,8,9,10),，确认后即可显示出,E44,至,J44,单元和区域和数值,7,，,8,，,9,，,10,中的最小值。特别提醒：如果参数中有文本或逻辑值，则忽略。,回到首页,二十,MOD,函数,函数名称：,MOD,主要功能：求出两数相除的余数。使用格式：,MOD(number,divisor,),参数说明：,number,代表被除数；,divisor,代表除数。应用举例：输入公式：,=MOD(13,4),，确认后显示出结果“,1”,。特别提醒：如果,divisor,参数为零，则显示错误值“,#DIV/0!”,；,MOD,函数可以借用函数,INT,来表示：上述公式可以修改为：,=13-4*INT(13/4),。,回到首页,二十一,MONTH,函数,函数名称：,MONTH,主要功能：求出指定日期或引用单元格中的日期的月份。使用格式：,MONTH(serial_number,),参数说明：,serial_number,代表指定的日期或引用的单元格。应用举例：输入公式：,=MONTH(“2003-12-18”),，确认后，显示出,11,。特别提醒：如果是给定的日期，请包含在英文双引号中；如果将上述公式修改为：,=YEAR(“2003-12-18”),，则返回年份对应的值“,2003”,。,回到首页,二十二,NOW,函数,函数名称：,NOW,主要功能：给出当前系统日期和时间。使用格式：,NOW(),参数说明：该函数不需要参数。应用举例：输入公式：,=NOW(),，确认后即刻显示出当前系统日期和时间。如果系统日期和时间发生了改变，只要按一下,F9,功能键，即可让其随之改变。特别提醒：显示出来的日期和时间格式，可以通过单元格格式进行重新设置。,回到首页,二十三,RANK,函数,函数名称：,RANK,主要功能：返回某一数值在一列数值中的相对于其他数值的排位。使用格式：,RANK,（,Number,ref,order,）参数说明：,Number,代表需要排序的数值；,ref,代表排序数值所处的单元格区域；,order,代表排序方式参数（如果为“,0”,或者忽略，则按降序排名，即数值越大，排名结果数值越小；如果为非“,0”,值，则按升序排名，即数值越大，排名结果数值越大；）。应用举例：如在,C2,单元格中输入公式：,=RANK(B2,$B$2:$B$31,0),，确认后即可得出丁,1,同学的语文成绩在全班成绩中的排名结果。特别提醒：在上述公式中，我们让,Number,参数采取了相对引用形式，而让,ref,参数采取了绝对引用形式（增加了一个“,$”,符号），这样设置后，选中,C2,单元格，将鼠标移至该单元格右下角，成细十字线状时（通常称之为“填充柄”），按住左键向下拖拉，即可将上述公式快速复制到,C,列下面的单元格中，完成其他同学语文成绩的排名统计。,回到首页,二十四,RIGHT,函数,函数名称：,RIGHT,主要功能：从一个文本字符串的最后一个字符开始，截取指定数目的字符。使用格式：,RIGHT(text,num_chars,),参数说明：,text,代表要截字符的字符串；,num_chars,代表给定的截取数目。应用举例：假定,A65,单元格中保存了“我喜欢天极网”的字符串，我们在,C65,单元格中输入公式：,=RIGHT(A65,3),，确认后即显示出“天极网”的字符。特别提醒：,Num_chars,参数必须大于或等于,0,，如果忽略，则默认其为,1,；如果,num_chars,参数大于文本长度，则函数返回整个文本。,二十五,SUMIF,函数,SUMIF,函数函数名称：,SUMIF,主要功能：计算符合指定条件的单元格区域内的数值和。使用格式：,SUMIF,（,Range,Criteria,Sum_Range,）参数说明：,Range,代表条件判断的单元格区域；,Criteria,为指定条件表达式；,Sum_Range,代表需要计算的数值所在的单元格区域。应用举例：在,D64,单元格中输入公式：,=SUMIF(C2:C63,男,D2:D63),，确认后即可求出“男”生的语文成绩和。特别提醒：如果把上述公式修改为：,=SUMIF(C2:C63,女,D2:D63),，即可求出“女”生的语文成绩和；其中“男”和“女”由于是文本型的，需要放在英文状态下的双引号（,男,、,女,）中。,二十六,VLOOKUP,函数,函数名称：,VLOOKUP,主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。使用格式：,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup,),参数说明：,Lookup_value,代表需要查找的数值；,Table_array,代表需要在其中查找数据的单元格区域；,Col_index_num,为在,table_array,区域中待返回的匹配值的列序号（当,Col_index_num,为,2,时,返回,table_array,第,2,列中的数值，为,3,时，返回第,3,列的值,）；,Range_lookup,为一逻辑值，如果为,TRUE,或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于,lookup_value,的最大数值；如果为,FALSE,，则返回精确匹配值，如果找不到，则返回错误值,#N/A,。应用举例：参见图,7,，我们在,D65,单元格中输入公式：,=VLOOKUP(B65,B2:D63,3,FALSE),，确认后，只要在,B65,单元格中输入一个学生的姓名（如丁,48,），,D65,单元格中即刻显示出该学生的语言成绩。特别提醒：,Lookup_value,参见必须在,Table_array,区域的首列中；如果忽略,Range_lookup,参数，则,Table_array,的首列必须进行排序；在此函数的向导中，有关,Range_lookup,参数的用法是错误的。,第三章 数据透视表,什么是数据透视表,为什么应该使用数据透视表,何时应该使用数据透视表,数据透视表的结构,对数据透视表报表的限制,为数据透视表报表准备数据,什么是数据透视表,可以把,Excel,想象成一个很大的工具箱，它包含我们要使用的各种工具。数据透视表本质上是,Excel,工具箱中的一个工具。,通过数据透视表查看数据集时，有可能看到之前没有注意到的数据细节，此外还可以旋转数据透视表，以便从不同的透视角度查看数据。,使用数据透视表能够建立数据集的交互视图。我们称这个视图为数据透视报表，从而我们可以方便地将数据分组，汇总大量的数据形成有用的信息，在很很短的时间内进行各种计算。,为什么应该使用数据透视表,通常，,Excel,的行为可分为两大类：计算数据和整理（格式化）数据。虽然很多内置工具和公式可以使这些任务很容易完成，但是数据透视表通常是计算、整理数据最有效的方法。它可以帮助我们以比传统的函数和公式更快更好的方式完成许多任务，有助于极大地提高必须使用,EXCEL,完成的大量的任务的效率，并减少错误，数据透视表不能做所有的事情，但哪怕只知道数据透视表的基本功能的用法，就可以让自己的数据分析能力和效率提高到的新水平。,何时应该使用数据透视表,有大量交易数据，已很难通过有意义的方式分析和汇总,需要找出数据内部的关系并分组,需要找出数据中某一字段的一系列特定值,需要利用各种时间周期找出数据趋势,预期要频繁地查询数据分析的变化,需要创建常常包含新增加部分数据的分类汇总,需要将数据组织成易于制成图表的格式,数据透视表的结构,数据透视表的结构,值区域,行区域,列区域,报表筛选区域,对数据透视表报表的限制,类别,2000,2002/2003,2007,行字段数（受存储空间限制）,受可用存储空间限制,受可用存储空间限制,1048576,列字段数,256,256,16384,页面字段,256,256,数据字段,256,256,单个数据透视表字段中特定项数（可能受存储空间限制）,8000,32500,1048576,计算项数,受可用存储空间限制,受可用存储空间限制,受可用存储空间限制,一张工作表上的数据透视报表数,受可用存储空间限制,受可用存储空间限制,受可用存储空间限制,为数据透视表报表准备数据,数据源的第一行由字段标签或者各列信息的标题组成,数据源中的每一列表示惟一一类数据,数据源中的每一行表示各列中的单独一项,