关闭

关闭

封号提示

内容

首页 Excel常用函数大全.doc

Excel常用函数大全.doc

Excel常用函数大全.doc

上传者: 豆蔻造梦乡 2017-10-19 评分 3 0 11 2 52 暂无简介 简介 举报

简介:本文档为《Excel常用函数大全doc》,可适用于IT/计算机领域,主题内容包含Excel常用函数大全作者:佚名文章来源:天极网点击数:更新时间:我们在使用Excel制作表格整理数据的时候常常要用到它的函数功能来自动统计处理表格符等。

Excel常用函数大全作者:佚名文章来源:天极网点击数:更新时间:我们在使用Excel制作表格整理数据的时候常常要用到它的函数功能来自动统计处理表格中的数据。这里整理了Excel中使用频率最高的函数的功能、使用方法以及这些函数在实际应用中的实例剖析并配有详细的介绍。、ABS函数函数名称:ABS主要功能:求出相应数字的绝对值。使用格式:ABS(number)参数说明:number代表需要求绝对值的数值或引用的单元格。应用举例:如果在B单元格中输入公式:=ABS(A)则在A单元格中无论输入正数(如)还是负数(如)B中均显示出正数(如)。特别提醒:如果number参数不是数值而是一些字符(如A等)则B中返回错误值“#VALUE~”。、AND函数函数名称:AND主要功能:返回逻辑值:如果所有参数值均为逻辑“真(TRUE)”则返回逻辑“真(TRUE)”反之返回逻辑“假(FALSE)”。使用格式:AND(logical,logical,)参数说明:Logical,Logical,Logical……:表示待测试的条件值或表达式最多这个。应用举例:在C单元格输入公式:=AND(A>=,B>=)确认。如果C中返回TRUE说明A和B中的数值均大于等于如果返回FALSE说明A和B中的数值至少有一个小于。特别提醒:如果指定的逻辑条件参数中包含非逻辑值时则函数返回错误值“#VALUE!”或“#NAME”。、AVERAGE函数函数名称:AVERAGE主要功能:求出所有参数的算术平均值。使用格式:AVERAGE(number,number,……)参数说明:number,number,……:需要求平均值的数值或引用单元格(区域)参数不超过个。应用举例:在B单元格中输入公式:=AVERAGE(B:D,F:H,,)确认后即可求出B至D区域、F至H区域中的数值和、的平均值。特别提醒:如果引用区域中包含“”值单元格则计算在内如果引用区域中包含空白或字符单元格则不计算在内。、COLUMN函数函数名称:COLUMN主要功能:显示所引用单元格的列标号值。使用格式:COLUMN(reference)参数说明:reference为引用的单元格。应用举例:在C单元格中输入公式:=COLUMN(B)确认后显示为(即B列)。特别提醒:如果在B单元格中输入公式:=COLUMN()也显示出与之相对应的还有一个返回行标号值的函数ROW(reference)。、CONCATENATE函数函数名称:CONCATENATE主要功能:将多个字符文本或单元格中的数据连接在一起显示在一个单元格中。使用格式:CONCATENATE(TextText……)参数说明:Text、Text……为需要连接的字符文本或引用的单元格。应用举例:在C单元格中输入公式:=CONCATENATE(A,"",B,"com")确认后即可将A单元格中字符、、B单元格中的字符和com连接成一个整体显示在C单元格中。特别提醒:如果参数不是引用的单元格且为文本格式的请给参数加上英文状态下的双引号如果将上述公式改为:=A""B"com"也能达到相同的目的。、COUNTIF函数函数名称:COUNTIF主要功能:统计某个单元格区域中符合指定条件的单元格数目。使用格式:COUNTIF(Range,Criteria)参数说明:Range代表要统计的单元格区域Criteria表示指定的条件表达式。应用举例:在C单元格中输入公式:=COUNTIF(B:B,">=")确认后即可统计出B至B单元格区域中数值大于等于的单元格数目。特别提醒:允许引用的单元格区域中有空白单元格出现。、DATE函数函数名称:DATE主要功能:给出指定数值的日期。使用格式:DATE(year,month,day)参数说明:year为指定的年份数值(小于)month为指定的月份数值(可以大于)day为指定的天数。应用举例:在C单元格中输入公式:=DATE(,,)确认后显示出。特别提醒:由于上述公式中月份为多了一个月顺延至年月天数为比年月的实际天数又多了天故又顺延至年月日。、函数名称:DATEDIF主要功能:计算返回两个日期参数的差值。使用格式:=DATEDIF(date,date,"y")、=DATEDIF(date,date,"m")、=DATEDIF(date,date,"d")参数说明:date代表前面一个日期date代表后面一个日期y(m、d)要求返回两个日期相差的年(月、天)数。应用举例:在C单元格中输入公式:=DATEDIF(A,TODAY(),"y")确认后返回系统当前日期用TODAY()表示)与A单元格中日期的差值并返回相差的年数。特别提醒:这是Excel中的一个隐藏函数在函数向导中是找不到的可以直接输入使用对于计算年龄、工龄等非常有效。、DAY函数函数名称:DAY主要功能:求出指定日期或引用单元格中的日期的天数。使用格式:DAY(serialnumber)参数说明:serialnumber代表指定的日期或引用的单元格。应用举例:输入公式:=DAY("")确认后显示出。特别提醒:如果是给定的日期请包含在英文双引号中。、DCOUNT函数函数名称:DCOUNT主要功能:返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。使用格式:DCOUNT(database,field,criteria)参数说明:Database表示需要统计的单元格区域Field表示函数所使用的数据列(在第一行必须要有标志项)Criteria包含条件的单元格区域。应用举例:如图所示在F单元格中输入公式:=DCOUNT(A:D,"语文",F:G)确认后即可求出“语文”列中成绩大于等于而小于的数值单元格数目(相当于分数段人数)。特别提醒:如果将上述公式修改为:=DCOUNT(A:D,,F:G)也可以达到相同目的。、FREQUENCY函数函数名称:FREQUENCY主要功能:以一列垂直数组返回某个区域中数据的频率分布。使用格式:FREQUENCY(dataarray,binsarray)参数说明:Dataarray表示用来计算频率的一组数据或单元格区域Binsarray表示为前面数组进行分隔一列数值。应用举例:如图所示同时选中B至B单元格区域输入公式:=FREQUENCY(B:B,D:D)输入完成后按下“CtrlShiftEnter”组合键进行确认即可求出B至B区域中按D至D区域进行分隔的各段数值的出现频率数目(相当于统计各分数段人数)。特别提醒:上述输入的是一个数组公式输入完成后需要通过按“CtrlShiftEnter”组合键进行确认确认后公式两端出现一对大括号({})此大括号不能直接输入。、IF函数函数名称:IF主要功能:根据对指定条件的逻辑判断的真假结果返回相对应的内容。使用格式:=IF(Logical,Valueiftrue,Valueiffalse)参数说明:Logical代表逻辑判断表达式Valueiftrue表示当判断条件为逻辑“真(TRUE)”时的显示内容如果忽略返回“TRUE”Valueiffalse表示当判断条件为逻辑“假(FALSE)”时的显示内容如果忽略返回“FALSE”。应用举例:在C单元格中输入公式:=IF(C>=,"符合要求","不符合要求")确信以后如果C单元格中的数值大于或等于则C单元格显示“符合要求”字样反之显示“不符合要求”字样。特别提醒:本文中类似“在C单元格中输入公式”中指定的单元格读者在使用时并不需要受其约束此处只是配合本文所附的实例需要而给出的相应单元格具体请大家参考所附的实例文件。、INDEX函数函数名称:INDEX主要功能:返回列表或数组中的元素值此元素由行序号和列序号的索引值进行确定。使用格式:INDEX(array,rownum,columnnum)参数说明:Array代表单元格区域或数组常量Rownum表示指定的行序号(如果省略rownum则必须有columnnum)Columnnum表示指定的列序号(如果省略columnnum则必须有rownum)。应用举例:如图所示在F单元格中输入公式:=INDEX(A:D,,)确认后则显示出A至D单元格区域中第行和第列交叉处的单元格(即C)中的内容。特别提醒:此处的行序号参数(rownum)和列序号参数(columnnum)是相对于所引用的单元格区域而言的不是Excel工作表中的行或列序号。、INT函数函数名称:INT主要功能:将数值向下取整为最接近的整数。使用格式:INT(number)参数说明:number表示需要取整的数值或包含数值的引用单元格。应用举例:输入公式:=INT()确认后显示出。特别提醒:在取整时不进行四舍五入如果输入的公式为=INT()则返回结果为。、ISERROR函数函数名称:ISERROR主要功能:用于测试函数式返回的数值是否有错。如果有错该函数返回TRUE反之返回FALSE。使用格式:ISERROR(value)参数说明:Value表示需要测试的值或表达式。应用举例:输入公式:=ISERROR(AB)确认以后如果B单元格为空或“”则AB出现错误此时前述函数返回TRUE结果反之返回FALSE。特别提醒:此函数通常与IF函数配套使用如果将上述公式修改为:=IF(ISERROR(AB),"",AB)如果B为空或“”则相应的单元格显示为空反之显示AB的结果。、LEFT函数函数名称:LEFT主要功能:从一个文本字符串的第一个字符开始截取指定数目的字符。使用格式:LEFT(text,numchars)参数说明:text代表要截字符的字符串numchars代表给定的截取数目。应用举例:假定A单元格中保存了“我喜欢天极网”的字符串我们在C单元格中输入公式:=LEFT(A,)确认后即显示出“我喜欢”的字符。特别提醒:此函数名的英文意思为“左”即从左边截取Excel很多函数都取其英文的意思。、LEN函数函数名称:LEN主要功能:统计文本字符串中字符数目。使用格式:LEN(text)参数说明:text表示要统计的文本字符串。应用举例:假定A单元格中保存了“我今年岁”的字符串我们在C单元格中输入公式:=LEN(A)确认后即显示出统计结果“”。特别提醒:LEN要统计时无论中全角字符还是半角字符每个字符均计为“”与之相对应的一个函数LENB在统计时半角字符计为“”全角字符计为“”。、MATCH函数函数名称:MATCH主要功能:返回在指定方式下与指定数值匹配的数组中元素的相应位置。使用格式:MATCH(lookupvalue,lookuparray,matchtype)参数说明:Lookupvalue代表需要在数据表中查找的数值Lookuparray表示可能包含所要查找的数值的连续单元格区域Matchtype表示查找方式的值(、或)。如果matchtype为查找大于或等于lookupvalue的最小数值Lookuparray必须按降序排列如果matchtype为查找小于或等于lookupvalue的最大数值Lookuparray必须按升序排列如果matchtype为查找等于lookupvalue的第一个数值Lookuparray可以按任何顺序排列如果省略matchtype则默认为。应用举例:如图所示在F单元格中输入公式:=MATCH(E,B:B,)确认后则返回查找的结果“”。特别提醒:Lookuparray只能为一列或一行。、MAX函数函数名称:MAX主要功能:求出一组数中的最大值。使用格式:MAX(number,number……)参数说明:number,number……代表需要求最大值的数值或引用单元格(区域)参数不超过个。应用举例:输入公式:=MAX(E:J,,,,)确认后即可显示出E至J单元和区域和数值中的最大值。特别提醒:如果参数中有文本或逻辑值则忽略。、MID函数函数名称:MID主要功能:从一个文本字符串的指定位置开始截取指定数目的字符。使用格式:MID(text,startnum,numchars)参数说明:text代表一个文本字符串startnum表示指定的起始位置numchars表示要截取的数目。应用举例:假定A单元格中保存了“我喜欢天极网”的字符串我们在C单元格中输入公式:=MID(A,,)确认后即显示出“天极网”的字符。特别提醒:公式中各参数间要用英文状态下的逗号“,”隔开。、MIN函数函数名称:MIN主要功能:求出一组数中的最小值。使用格式:MIN(number,number……)参数说明:number,number……代表需要求最小值的数值或引用单元格(区域)参数不超过个。应用举例:输入公式:=MIN(E:J,,,,)确认后即可显示出E至J单元和区域和数值中的最小值。特别提醒:如果参数中有文本或逻辑值则忽略。、MOD函数函数名称:MOD主要功能:求出两数相除的余数。使用格式:MOD(number,divisor)参数说明:number代表被除数divisor代表除数。应用举例:输入公式:=MOD(,)确认后显示出结果“”。特别提醒:如果divisor参数为零则显示错误值“#DIV!”MOD函数可以借用函数INT来表示:上述公式可以修改为:=*INT()。、MONTH函数函数名称:MONTH主要功能:求出指定日期或引用单元格中的日期的月份。使用格式:MONTH(serialnumber)参数说明:serialnumber代表指定的日期或引用的单元格。应用举例:输入公式:=MONTH("")确认后显示出。特别提醒:如果是给定的日期请包含在英文双引号中如果将上述公式修改为:=YEAR("")则返回年份对应的值“”。、NOW函数函数名称:NOW主要功能:给出当前系统日期和时间。使用格式:NOW()参数说明:该函数不需要参数。应用举例:输入公式:=NOW()确认后即刻显示出当前系统日期和时间。如果系统日期和时间发生了改变只要按一下F功能键即可让其随之改变。特别提醒:显示出来的日期和时间格式可以通过单元格格式进行重新设置。、OR函数函数名称:OR主要功能:返回逻辑值仅当所有参数值均为逻辑“假(FALSE)”时返回函数结果逻辑“假(FALSE)”否则都返回逻辑“真(TRUE)”。使用格式:OR(logical,logical,)参数说明:Logical,Logical,Logical……:表示待测试的条件值或表达式最多这个。应用举例:在C单元格输入公式:=OR(A>=,B>=)确认。如果C中返回TRUE说明A和B中的数值至少有一个大于或等于如果返回FALSE说明A和B中的数值都小于。特别提醒:如果指定的逻辑条件参数中包含非逻辑值时则函数返回错误值“#VALUE!”或“#NAME”。、RANK函数函数名称:RANK主要功能:返回某一数值在一列数值中的相对于其他数值的排位。使用格式:RANK(Number,ref,order)参数说明:Number代表需要排序的数值ref代表排序数值所处的单元格区域order代表排序方式参数(如果为“”或者忽略则按降序排名即数值越大排名结果数值越小如果为非“”值则按升序排名即数值越大排名结果数值越大)。应用举例:如在C单元格中输入公式:=RANK(B,$B$:$B$,)确认后即可得出丁同学的语文成绩在全班成绩中的排名结果。特别提醒:在上述公式中我们让Number参数采取了相对引用形式而让ref参数采取了绝对引用形式(增加了一个“$”符号)这样设置后选中C单元格将鼠标移至该单元格右下角成细十字线状时(通常称之为“填充柄”)按住左键向下拖拉即可将上述公式快速复制到C列下面的单元格中完成其他同学语文成绩的排名统计。、RIGHT函数函数名称:RIGHT主要功能:从一个文本字符串的最后一个字符开始截取指定数目的字符。使用格式:RIGHT(text,numchars)参数说明:text代表要截字符的字符串numchars代表给定的截取数目。应用举例:假定A单元格中保存了“我喜欢天极网”的字符串我们在C单元格中输入公式:=RIGHT(A,)确认后即显示出“天极网”的字符。特别提醒:Numchars参数必须大于或等于如果忽略则默认其为如果numchars参数大于文本长度则函数返回整个文本。、SUBTOTAL函数函数名称:SUBTOTAL主要功能:返回列表或数据库中的分类汇总。使用格式:SUBTOTAL(functionnum,ref,ref,)参数说明:Functionnum为到(包含隐藏值)或到(忽略隐藏值)之间的数字用来指定使用什么函数在列表中进行分类汇总计算(如图)ref,ref,……代表要进行分类汇总区域或引用不超过个。应用举例:如图所示在B和C单元格中分别输入公式:=SUBTOTAL(,C:C)和=SUBTOTAL,C:C)并且将行隐藏起来确认后前者显示为(包括隐藏的行)后者显示为不包括隐藏的行。特别提醒:如果采取自动筛选无论functionnum参数选用什么类型SUBTOTAL函数忽略任何不包括在筛选结果中的行SUBTOTAL函数适用于数据列或垂直区域不适用于数据行或水平区域。、SUM函数函数名称:SUM主要功能:计算所有参数数值的和。使用格式:SUM(Number,Number……)参数说明:Number、Number……代表需要计算的值可以是具体的数值、引用的单元格(区域)、逻辑值等。应用举例:如图所示在D单元格中输入公式:=SUM(D:D)确认后即可求出语文的总分。特别提醒:如果参数为数组或引用只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略如果将上述公式修改为:=SUM(LARGE(D:D,{,,,,}))则可以求出前名成绩的和。、SUMIF函数函数名称:SUMIF主要功能:计算符合指定条件的单元格区域内的数值和。使用格式:SUMIF(Range,Criteria,SumRange)参数说明:Range代表条件判断的单元格区域Criteria为指定条件表达式SumRange代表需要计算的数值所在的单元格区域。应用举例:如图所示在D单元格中输入公式:=SUMIF(C:C,"男",D:D)确认后即可求出“男”生的语文成绩和。特别提醒:如果把上述公式修改为:=SUMIF(C:C,"女",D:D)即可求出“女”生的语文成绩和其中“男”和“女”由于是文本型的需要放在英文状态下的双引号("男"、"女")中。、TEXT函数函数名称:TEXT主要功能:根据指定的数值格式将相应的数字转换为文本形式。使用格式:TEXT(value,formattext)参数说明:value代表需要转换的数值或引用的单元格formattext为指定文字形式的数字格式。应用举例:如果B单元格中保存有数值我们在C单元格中输入公式:=TEXT(B,"$")确认后显示为“$”。特别提醒:formattext参数可以根据“单元格格式”对话框“数字”标签中的类型进行确定。、TODAY函数函数名称:TODAY主要功能:给出系统日期。使用格式:TODAY()参数说明:该函数不需要参数。应用举例:输入公式:=TODAY()确认后即刻显示出系统日期和时间。如果系统日期和时间发生了改变只要按一下F功能键即可让其随之改变。特别提醒:显示出来的日期格式可以通过单元格格式进行重新设置(参见附件)。、VALUE函数函数名称:VALUE主要功能:将一个代表数值的文本型字符串转换为数值型。使用格式:VALUE(text)参数说明:text代表需要转换文本型字符串数值。应用举例:如果B单元格中是通过LEFT等函数截取的文本型字符串我们在C单元格中输入公式:=VALUE(B)确认后即可将其转换为数值型。特别提醒:如果文本型数值不经过上述转换在用函数处理这些数值时常常返回错误。、VLOOKUP函数函数名称:VLOOKUP主要功能:在数据表的首列查找指定的数值并由此返回数据表当前行中指定列处的数值。使用格式:VLOOKUP(lookupvalue,tablearray,colindexnum,rangelookup)参数说明:Lookupvalue代表需要查找的数值Tablearray代表需要在其中查找数据的单元格区域Colindexnum为在tablearray区域中待返回的匹配值的列序号(当Colindexnum为时,返回tablearray第列中的数值为时返回第列的值……)Rangelookup为一逻辑值如果为TRUE或省略则返回近似匹配值也就是说如果找不到精确匹配值则返回小于lookupvalue的最大数值如果为FALSE则返回精确匹配值如果找不到则返回错误值#NA。应用举例:参见图我们在D单元格中输入公式:=VLOOKUP(B,B:D,,FALSE)确认后只要在B单元格中输入一个学生的姓名(如丁)D单元格中即刻显示出该学生的语言成绩。特别提醒:Lookupvalue参见必须在Tablearray区域的首列中如果忽略Rangelookup参数则Tablearray的首列必须进行排序在此函数的向导中有关Rangelookup参数的用法是错误的。、WEEKDAY函数函数名称:WEEKDAY主要功能:给出指定日期的对应的星期数。使用格式:WEEKDAY(serialnumber,returntype)参数说明:serialnumber代表指定的日期或引用含有日期的单元格returntype代表星期的表示方式当Sunday(星期日)为、Saturday(星期六)为时该参数为当Monday(星期一)为、Sunday(星期日)为时该参数为(这种情况符合中国人的习惯)当Monday(星期一)为、Sunday(星期日)为时该参数为。应用举例:输入公式:=WEEKDAY(TODAY(),)确认后即给出系统日期的星期数。特别提醒:如果是指定的日期请放在英文状态下的双引号中如=WEEKDAY("",)。VLOOKUP函数的使用方法(入门级)VLOOKUP函数是Excel中几个最重函数之一为了方便大家学习兰色幻想特针对VLOOKUP函数的使用和扩展应用进行一次全面综合的说明。本文为入门部分一、入门级VLOOKUP是一个查找函数给定一个查找的目标它就能从指定的查找区域中查找返回想要查找到的值。它的基本语法为:VLOOKUP(查找目标查找范围返回值的列数精确OR模糊查找)下面以一个实例来介绍一下这四个参数的使用例:如下图所示要求根据表二中的姓名查找姓名所对应的年龄。公式:B=VLOOKUP(A,$B$:$D$,,)参数说明:查找目标:就是你指定的查找的内容或单元格引用。本例中表二A列的姓名就是查找目标。我们要根据表二的“姓名”在表一中A列进行查找。公式:B=VLOOKUP(A,$B$:$D$,,)查找范围(VLOOKUP(A,$B$:$D$,,)):指定了查找目标如果没有说从哪里查找EXCEL肯定会很为难。所以下一步我们就要指定从哪个范围中进行查找。VLOOKUP的这第二个参数可以从一个单元格区域中查找也可以从一个常量数组或内存数组中查找。本例中要从表一中进行查找那么范围我们要怎么指定呢,这里也是极易出错的地方。大家一定要注意给定的第二个参数查找范围要符合以下条件才不会出错:A查找目标一定要在该区域的第一列。本例中查找表二的姓名那么姓名所对应的表一的姓名列那么表一的姓名列(列)一定要是查找区域的第一列。象本例中给定的区域要从第二列开始即$B$:$D$而不能是$A$:$D$。因为查找的“姓名”不在$A$:$D$区域的第一列。B该区域中一定要包含要返回值所在的列本例中要返回的值是年龄。年龄列(表一的D列)一定要包括在这个范围内即:$B$:$D$如果写成$B$:$C$就是错的。返回值的列数(B=VLOOKUP(A,$B$:$D$,,))。这是VLOOKUP第个参数。它是一个整数值。它怎么得来的呢。它是“返回值”在第二个参数给定的区域中的列数。本例中我们要返回的是“年龄”它是第二个参数查找B$:$D$的第列。这里一定要注意列数不是在工作表中的列数(不是范围$第列)而是在查找范围区域的第几列。如果本例中要是查找姓名所对应的性别第个参数的值应该设置为多少呢。答案是。因为性别在$B$:$D$的第列中。精确OR模糊查找(VLOOKUP(A,$B$:$D$,,))最后一个参数是决定函数精确和模糊查找的关键。精确即完全一样模糊即包含的意思。第个参数如果指定值是或FALSE就表示精确查找而值为或TRUE时则表示模糊。这里兰色提醒大家切记切记在使用VLOOKUP时千万不要把这个参数给漏掉了如果缺少这个参数默为值为模糊查找我们就无法精确查找到结果了。好了关于VLOOKUP函数的入门级应用就说到这里VLOOKUP函数可不只是这么简单的查找我们讲的还只是的用法。其他的没法在一篇文章中说明。敬请期待“VLOOKUP的使用方法进阶篇”吧。VLOOKUP函数的使用方法(初级篇)上一讲咱们学习了VLOOKUP的基本用法和示例本讲将介绍VLOOKUP在使用中的一些小技巧。一、VLOOKUP多行查找时复制公式的问题VLOOKUP函数的第三个参数是查找返回值所在的列数如果我们需要查找返回多列时这个列数值需要一个个的更改比如返回第列的参数设置为如果需要返回第列的就需要把值改为。。。如果有十几列会很麻烦的。那么能不能让第个参数自动变呢,向后复制时自动变为。。。在EXCEL中有一个函数COLUMN它可以返回指定单元格的列数比如=COLUMNS(A)返回值=COLUMNS(B)返回值而单元格引用复制时会自动发生变化即A随公式向右复制时会变成BCD。。这样我们用COLUMN函数就可以转换成数字。。。例:下例中需要同时查找性别年龄身高体重。公式:=VLOOKUP($A,$B$:$F$,COLUMN(B),)公式说明:这里就是使用COLUMN(B)转化成可以自动递增的数字。二、VLOOKUP查找出现错误值的问题。、如何避免出现错误值。EXCEL在VLOOKUP查找不到就#NA的错误值我们可以利用错误处理函数把错误值转换成或空值。即:=IF(ISERROR(VLOOKUP(参数略)),"",VLOOKUP(参数略)EXCELEXCEL中提供了一个新函数IFERROR处理起来比EXCEL简单多了。IFERROR(VLOOKUP()"")、VLOOKUP函数查找时出现错误值的几个原因A、实在是没有所要查找到的值B、查找的字符串或被查找的字符中含有空格或看不见的空字符验证方法是用=号对比一下如果结果是FALSE就表示两个单元格看上去相同其实结果不同。C、参数设置错误。VLOOKUP的最后一个参数没有设置成或者是没有设置掉。第二个参数数据源区域查找的值不是区域的第一列或者需要反回的字段不在区域里参数设置在入门讲里已注明请参阅。D、数值格式不同如果查找值是文本被查找的是数字类型就会查找不到。解决方法是把查找的转换成文本或数值转换方法如下:文本转换成数值:*或或数值转抱成文本:""VLOOKUP函数的初级篇就说到这里了咱们下一讲将介绍VLOOKUP的模糊查找有、反向查找等。敬请大家关注:VLOOKUP函数的使用方法(进阶篇)。本系列为兰色幻想原创。转载请注明作者和转自“EXCEL精英培训”原创:VLOOKUP函数的使用方法(高级篇)前言:前面我们分别学习了VLOOKUP函数的入门、初级和进阶篇。今天我们学习VLOOKUP函数的高级应用部分VLOOKUP函数的数组应用。(本文由兰色幻想原创转载请注明转自excel精英培训)一、VLOOKUP的反向查找。一般情况下VLOOKUP函数只能从左向右查找。但如果需要从右向右查找则需要把区域进行“乾坤大挪移”把列的位置用数组互换一下。例:要求在如下图所示表中的姓名反查工号。公式:=VLOOKUP(A,IF({,},B:B,A:A),,)公式剖析:、这里其实不是VLOOKUP可以实现从右至右的查找而是利用IF函数的数组效应把两列换位重新组合后再按正常的从左至右查找。、IF({,},B:B,A:A)这是本公式中最重要的组成部分。在EXCEL函数中使用数组时(前提时该函数的参数支持数组)返回的结果也会是一个数组。这里和不是实际意义上的数字而是相关于TRUE相当于FALSE当为时它会返回IF的第二个参数(B列)为时返回第二个参数(A列)。根据数组运算返回数组所以使用IF后的结果返回一个数组(非单元格区域):{"张一","A""赵三","A""杨五","A""孙二","A"}二、VLOOKUP函数的多条件查找。VLOOKUP函数需要借用数组才能实现多条件查找。例:要求根据部门和姓名查找C列的加班时间。分析:我们可以延用例的思路我们的努力方向不是让VLOOKUP本身实现多条件查找而是想办法重构一个数组。多个条件我们可以用连接在一起同样两列我们也可以连接成一列数据然后用IF函数进行组合。公式:{=VLOOKUP(AB,IF({,},A:AB:B,C:C),,)}公式剖析:、AB把两个条件连接在一起。把他们做为一个整体进行查找。、A:AB:B和条件连接相对应把部分和姓名列也连接在一起作为一个待查找的整体。、IF({,},A:AB:B,C:C)用IF({}把连接后的两列与C列数据合并成一个两列的内存数组。按F后可以查看的结果为:{"销售张一","销售赵三","人事杨五","销售赵三",}、完成了数组的重构后接下来就是VLOOKUP的基本查找功能了另外公式中含有多个数据与多个数据运算(A:AB:B),所以必须以数组形式输入即按ctrlshift后按ENTER结束输入。三、VLOOKUP函数的批量查找。VLOOKUP一般情况下只能查找一个那么多项该怎么查找呢,例要求把如图表中所有张一的消费金额全列出来分析:经过前面的学习我们也有这样一个思路我们在实现复杂的查找时努力的方向是怎么重构一个查找内容和查找的区域。要想实现多项查找我们可以对查找的内容进行编号第一个出现的是后面连接第二个出现的连接。。。公式:{=VLOOKUP(B$ROW(A),IF({,},$B$:$B$COUNTIF(INDIRECT("b:b"ROW($:$)),B$),$C$:$C$),,)}公式剖析:、B$ROW(A)连接序号公式向下复制时会变成B$连接、给所有的张一进行编号。要想生成编号就需要生成一个不断扩充的区域(INDIRECT("b:b"ROW($:$))然后在这个逐行扩充的区域内统计“张一”的个数在连接上$B$:$B$后就可以对所有的张一进行编号了。、IF({}把编号后的B列和C组重构成一个两列数组通过以上的讲解我们需要知道VLOOKUP函数的基本用法是固定的要实现高级查找就需要借助其他函数来重构查找内容和查找数组。至此VLOOKUP函数从入门到高级的四篇VLOOKUP函数使用教程全部结束了VLOOKUP函数在数组运算中还有着其他应用但只是配角了所以本系列不再介绍。由于笔者水平有限不免有错漏之处请大家多多指点。Countif函数一、countif函数作用:根据条件统计个数countif函数是使用频率最高的几个函数之一,下面针对这个函数做一个小小的专题以方便大家学习此文是本站原创。转载请注明转自“excel精英培训”一、countif函数的用途countif函数是根据条件在另一个区域进行个数的统计一方面它可以完成符合条件的统计计算。另一方面由此扩展出它可以进行重复值的查找我表格的核对。二、countif的基本语法:COUNTIF(单元格引用,条件)参数说明:第一个参数只能是单元格引用方式不能使用内存数组第二个参数是条件条件可以是值可以是字符串构成的复合条件可以使用通配符进行模糊统计可以使用内存数组。应用示例:例:统计在A列是“公司A”的个数公式=Countif(A:A,"公司A")例:统计A列包含“公司A”的个数公式=Countif(A:A,"*公司A*")注:这里使用通配*表示字符前后有任意个字符。例:统计C列成绩大于的学生个数公式=Countif(C:C,">")注:这里是用运算对比符号和数字构成的条件例:统计大于C单元格数值的C列个数。公式:=Countif(c:c,">"c)注:这里是用插入了一个变量即C的值。例:统计C列大于平均数的个数公式:=Countif(c:c,">"average(c:c))注:这里是使用了平均值函数average先计算平均值然后作为条件。例:统计A列为“公司A”或“公司B”的个数公式:{=Sum(Countif(A:A,{"公司A","公司B"}))}注:这里在第二个参数里加入了常量数组使用countif的结果是分别按两个公司名称统计的结果也是一个数组假如是{,}得到数组后用sum函数对两个数进行求和得到总的个数这个公式是数组公式所以一定要输入公式后把光标放在公式最后按ctrlshift然后按enter键结束输入。另:也许也还会问如果设置更多条件该怎么弄兰色幻想建议使用另一个可以多条件求和与计数的函数:sumproduct例:统计大于小于的数字个数=sumprodcut((a:a>)*(a:a<))indirect函数INDIRECT函数的使用uploadrarIndirect函数故事:小A与小相爱了,上帝用一条丝带把他俩连接到了一起(=A),可有一天,小A发现,他和小虽然走到了一起(=A的结果为“A”),却无法组成一个家庭并得到大家的承认(公式的结果只是字符串A而不能取出A单元格的值)经人指点后小A带着小去了民政局婚姻登记处的indirect函数indirect说这个好办我可能帮助你们(=indirect(A))。于是小A和小幸福的生活到了一起(=indirect(A)可以取出单元格A的值OFFSET函数第集:我原本不是花心的GG俺就是人见人爱也是见一个爱一个的花心大帅哥OFFSET函数哥因为太花心了所以现在一直没有老婆杯具啊~~不过呢今天俺又要去参加一个选美大会哈哈有位美女哦俺都流口水了啊~~走和我一起去看看吧。虽然俺很花心但我现在还要装作感情很专一的样子嘿嘿~于是我先挑了第行第列的“美女”。哦你问我是怎么挑的。这个嘛很简单嘛。我向下走步再右走步就找到她了。公式:=OFFSET(B,,)结果:美女美女找到了俺领着她回家了。可可。。这么多美女只让我领走一个实在是不甘心啊。第集:一夫多妻的梦想夜深了累了一天我很快就进入了梦乡。迷迷糊糊中我突然听到一个好消息意思是为了解决“男少女多”的社会问题国家规定从即即日起实行“一夫多妻制”。哇~~~真是天大的好消息啊。天天可以有好多MM陪伴左右哈哈。又逢一届选美大会还是有位美女在等着我挑选。经过认真对比我发现第列第、行的美女特别漂亮那就选她们吧怎么选呢,我向下走了两步(走到第行)然后向右走了四步(走到第列)我先拉住号美女的手然后又对号下面的号美女说这次我要选两个老婆你也被挑中了跟我走吧~~~于是乎两个美女跟着我回家了~~~~看仔细了我是怎么选美女的:OFFSET(本帅哥向下走步向右两步选两个美女在同一列中)即:=OFFSET(B,,,,)也许你会迷惑,用上面的公式查找返回的结果是错误值,为什么会这样呢真笨~~这都不理解,我带回两个美女,她们都争着抛头露面的,到底让谁显示啊,干脆两个都不显示,金屋藏矫,免得让人眼馋如果真的想看,你选中公式,按F就发现我藏了两个美女,嘿嘿~~,两个美女领回家后,因为国家允许一夫多妻的,我都和她们办了结婚手续,属合法夫妻所以EXCEL王国里的函数都可以引用她们进行计算:我可以算算他们的编号合计是多少:=SUM(OFFSET(B,,,,))结果是=还可以算算她们的编号谁最大:=MAX(OFFSET(B,,,,))结果是美女多了也不好,这不,她们为了争风吃醋又在打架了,我赶忙去劝架,哎呀~~~一个MM拎起的平底锅正砸在我头上原来是梦一场啊~~~~美梦已醒,我得总结了一下经验,看来一夫多妻并不好玩啊,她们在一起天天打架,我我成了受气包,可一个人去选美总控制不住自已,看顺眼的MM都想要,算了,还是下次一次邀请几个G们一起选美吧一人挑一个嗯嗯这个注意不错就这样~~下集还跟我一起选美吧。。。第集:GG集体选美今天又是一个好天气哥邀了另外三个哥们一起去参加选美大会。大会上依旧婷婷玉立着位美女他们每排个共排。我们四个GG商定一起选MM而且她们要在同一列中。经常仔细对比发现第列的MM个个都很漂亮。于是我们齐步向右走、、、、我们到达第列时一起各自拉住了MM的手。注:如果你想动态返回一个数据列某一列或某一行的数据你可以把一个区域(如一列或多列一行或多行)作为OFFSET函数的第一个参数作为位移参照这样你得到的是一个同样大小的区域。如:=OFFSET(A:A,,)表示A:A整体向右偏移列即F:F区域=OFFSET(A:I,,)表示A:I区域整体向下偏移行返回的是A:I区域。数偏移后得到的区域还是一个平面区域(每个GG找一个MM,符合法律规定:))所以EXCEL函数都可以拿它当参数进行处理如:=SUM(OFFSET(A:A,,))=LARGE(OFFSET(A:A,,),)第集:神秘的小三虽然国家规定一夫一妻但是~~~我还是没有抵挡美女的诱惑看着别人都找了小三我也决定背着老婆偷偷试一下。于是我又遛到了选美大会。这次我选美女不再是一次选一个了而是一次选两个。我向下走两步然后利用新学的分身术同时向右走步找到了“美女”和向右走步找到了“美女”。看来分身述没有白学啊。我一下找了两个情人。公式:OFFSET(B,,{,})注:如果OFFSET的参数是一组数,它返回的结果也是在不同平面的多个数或数组象上面返回的虽然只是两个单元格D和F,但这两个单元格不在同一个平面上就好比找了两个小三肯定不能让他们住在一起而要各自为她们买套房子单独安家。因为找小三是非法的所以我和他们约会都要偷偷的进行好多函数都不能用了。比如我想统计一下他们的序号和是多少而结果却是让我很失望:统计无效。如:=SUM(OFFSET(B,,{,}))结果是不是==MaX(B,,{,}))结果不是不是第集:给小三、小四们发生活费因为我的贪心先后找了个情人(小三、小四、小五、小六)而且还各和他们生了个孩子。本来应该是开心的事但因为是地下情所以情人和她们的孩子都不能公开(三维的数组有些函数无法计算也看不到结果按F显示错误值)。如下图所示:公式:=OFFSET(B,,{,,,},,)上面公式返回的是四个平面构成的三维空间。即:C:C平面D:D平面E:E平面F:F平面。因为是三维的每个平面都有多个数所以无法显示出来。显示错误值。最近哥很累这么多情人和孩子们都要养活所以我每月都要给他们发生活费。我的私房钱啊~~又到了发生活费的时间了我的情人孩子们一下全过来了这么多人太乱了我正在作难之际我的朋友小T和小N正巧来了他们说可以有办法说服一个家庭只派一个人过来。这个人是在最家里地位最高的(每个平面的左上角位置)。注:(N函数的作用可以针对数字类型的提取而T针对文本类型的数组提取)公式:=T(OFFSET(B,,{,,,},,))经过T函数的帮助(如果要提取的是数字可以找N帮忙)就可以把四个平面的各自各上角顶点单元格内容提取出来了。即结果是:{"小三","小四","小五","小六"}我心里暗自庆幸多亏T和N的帮忙要不我还真不知咋分才好呢,MMULT函数在数组中有着不可替代的作用但它的矩阵运算原理却让新手颇为头痛。这里兰色拍了一个MM函数相亲的故事希望能帮助大家认识MM函数。第一集:单身MM约会单身GG。MM今天要约会一个据说很帅的GGMM想我选男朋友一定要谨慎我要看看他的相貌、人品和兴趣是否和自已的一致是否能擦出(*)火花。于是MM便逐个拿自已的相貌和GG的相貌(MM相貌*GG相貌)、MM人品*GG人品MM兴趣*GG兴趣测试火花值最终三项加起来即是MM对GG的总评分。即:总评分=MM相貌*GG相貌MM人品*GG人品MM爱好*GG爱好最后经过评测MM拿着对GG的评估分回家交差了。第二集:姊妹花相亲。MM今天又要去相亲妈说你妹妹也该找婆家了要不你姐妹两一起去相亲吧。MM想正好拉上妹妹做个伴谁看对眼了就归谁。于是姐妹俩就一起找GG相亲去了。这次是两个MM和一个GG相亲MM需要拿自已的相貌、人品和兴趣和GG进行对比、擦火花MM呢也非要和姐姐一样也要对比一下这三样看是否合适最后评一个综合印象分。结果呢姐妹俩每人给GG评了一个印象分高高兴兴的回家交差了。导演注:因为有两个MM一起相亲(mmlut第个参数有两行)所以每个MM都要对GG有总评分即返回两个合计数组成的数组(也就是说MMULT函数结果有几行是和第一个参数的行数保持一致的)。(选取G:G在G输入公式mmult(c:e,a:a)并按CTRLSHIFTENTER键结束就可以在G和G看到结果或者直接选取公式按F也可以看到两个数)第三集:相亲大会。话说第二集中两姐妹去和同一个GG约会结果是评分差强人意。又是一个星期天老妈说:MM和MM听说今天有一个相亲大会你们今天去参加吧。这次我约好的不是一个GG而是两个GG(GG和GG)你们都可以和两个GG进行接触并进行印象评分。两个MM一听这次约会的是两个GG马上来了精神就欣然一起赴约了。在相亲大会上MM和MM见到了GG和GG并根据“相貌”、“人品”和“兴趣”三个标准分别对两GG进行了印象评分一个MM评了两次两MM带个四个评分回家汇报结了。导演注:、因为是两个MM都要分别给两个GG分别评分所以最终MMULT(两)返回的是四个评分结果MM放在第一行MM两个评分放在行数据两列数据第二行。对GG的评分放在第一列对GG的评分放在第二列。、多行多行应该返回怎么样的区域大家记住下面的规则:返回的数组它的行数和第一个参数的行数保持一致(有几个MM就是几行)它的列数和第二个参数的列数保持一致有几个GG这个最终返回的数组就有几列。、还有一个需要注意的MMULT的第一个参数的列数要和第二个参数的行数保持一致(MM的三个标准要对应GG的三个条件)

类似资料

该用户的其他资料

孔孟后人的行辈查考.doc

功能模块图.doc

400话术.doc

客运安全宣传片.doc

甘肃秦州:做强药材产业协调发展.doc

职业精品

精彩专题

用户评论

0/200
    暂无评论
上传我的资料

精选资料

热门资料排行换一换

  • 永恒的日记——每一天的音乐.pdf

  • 门萨的娼妓 伍迪·艾伦幽默文集…

  • _持志如心痛_与谢有顺对话.pdf

  • 从1万到100万的短线操盘绝招.…

  • 卡巴拉 - 犹太的智慧, 西方魔…

  • 枪炮钢铁与细菌.pdf

  • 课堂观察:走向专业的听评课.pdf

  • 《简明中国历史地图集》.扫描版.…

  • 郑保卫:《中国共产党新闻思想史》…

  • 资料评价:

    / 36
    所需积分:0 立即下载

    意见
    反馈

    返回
    顶部