EXCEL教程.doc

vlookup的作用简单来说就是按照某个条件在一个给定的区域里找和这个条件相同的值的同一行中的其他对应值.这样说可能不清楚,我举个例子:看图,现在有个小型数据库,第一列是名字,第二列是他们的成绩,我现在想查李四的成绩到底是多少.公式为:=vlookup(李四,A1:B4,2,false)第一个位置放的是你要查找的条件,这里放的是李四,注意,这里引号不能少,表示对文字的引用.也可以改成对某个单元格的引用.比如 A5单元格的内容也是李四的话,则vlookup(A5,A1:B4,2,false)第二个位置放的是你要在什么范围内查找.这里要注意的是,在这个范围内和第一个位置中的条件匹配的那列必须放在第一列!这里我第一个位置放的是李四,而李四在A1:B4范围中对应的是A列(名字),所以这第二个位置的选择一定要从A列开始.第三个位置是说你要返回按照你的条件,在第二个位置列出的范围内相对应的那一行中的第几列。上述公式的意思就是: 我按照李四这个条件,在A1:B4范围内,找A列内容是李四的那一行,即第三行,然后返回A1:B4的范围内的第二列,即第三行第二列的数值,那就是40.第四个位置是说按照精确还是模糊方法查询.一般常用false或者0,代表精确查找,即一定按照李四这个条件在A列查找,如果有任何不同,比如李4,或者李四后面多了空格,都将返回#N/A,代表没有找到匹配的值.所以以上例子的整个意思是:按照李四这个条件,在A1:B4范围内返回李四相对应的成绩,如果在A列中找不到和李四完全相同的名字,就返回#N/A 上面写的就是vlookup最基本的作用,即按照某个条件,在一个范围内查找符合该条件的单元格所在的那一行,并返回该行中的某一列的值.需要注意的是如果A列符合条件的不只一个,那么会默认返回第一个找到的那行(由上而下)所对应的相应列.但我们还漏了个尾巴,第四个位置我说最常用的是false或0,那相对应的,一定也可以有true或者其他写法吧? 答案是肯定的.但要换一个例子讲解.这个不常用,大家听过算数.我们把上面例子中的AB两列换个个,A列是成绩,B列是名字,首先要做的是,按A列的成绩由小到大排序.然后大家在空白格子里打入:=vlookup(34,A1:B4,2,true)返回的结果是张三,大家把34改成44看看,返回的结果就会变成李四，如果改成29看看，返回的 就是#N/A，提示找不到。所以true的意思是: 在一个首列为升序排列的范围内,按照一个条件,找到首列中小于等于且最接近这个条件所在的那一行。这里的注意点是：1）必须首列按照“升序”排列，不然返回的值没有意义。 2）优先按照绝对匹配查找，绝对匹配找不到的，再按照比条件小的那个最接近的值。 3）首列必须是数字，不然没有意义。 1.vlookup, iserror, if的混用有时候我想完成以下公式: 用vlookup查找,但是如果查找范围内没有我给的条件,我不想它返回#N/A,而是想返回没找到这样的字,那该怎么做呢?if不说了,iserror指的是判断后面括号里的公式返回值是不是错误提示,比如#N/A=if(iserrror(vlookup(.),没找到,vlookup(.)看图 2. 用left, right, mid, find公式和vlookup搭配使用。有时候我们会碰到这样的情况，我拿到的条件是类似于：D2单元格： 初三2班张三同学D3单元格： 初二10班李四同学直接用以上内容做vlookup是肯定不会出结果的我们我只想取其中的名字部分，其他内容舍弃。那这个时候就可以巧用find和mid了。=vlookup(mid(D2,find(班,D2,1)+1,2),A1:B4,2,false)find(班,D2,1)是指：在D2单元格的内容的第一个字开始找“班”这个字，是在第几个字出现？ 这里的结果是4，班字在初，三，2，之后，排第4个字，那么find(班,D2,1)+1的结果就是5了，即张三的“张”字在第五个字出现那么mid()里的内容就相当于： mid( D2,5,2)，是指：在D2单元格内的第5个字开始，连取2个字，那么就取到了张三,所以整个vlookup公式其实跟vlookup(张三,A1:B4,2,false)的结果是一样的。同样D3单元格的“初二10班李四同学”也可以用类似的方法处理。给喜欢动脑筋的同学的回家作业：如果这里的名字不是张三，李四这种都是2个字的，而是有2个字，3个字名字混合的。那该怎么做呢？3.绝对引用，column()和vlookup的组合达到单条件整行匹配查询有时候我们需要匹配的不止一列，而是好多列。如图：现在需要根据给定的查询条件在绿色区域返回每个月的销量情况，如果用vlookup公式，则需要：B11: =vlookup(A11,A1:M6,2,false)C11: =vlookup(A11,A1:M6,3,false)D11: =vlookup(A11,A1:M6,4,false).M11: =vlookup(A11,A1:M6,13,false)有没有办法做一个公式，然后复制到整个区域就可以搞定呢？ 先讲下什么是绝对引用和相对引用excel里表示一个单元格一般用字母+数字表示列+行的组合比如B1B1这样的表示方法就叫相对引用,含有相对引用的公式在复制到其他列时,会自动根据相对位置的变换改变.比如把含有=B1这个公式的单元格往右复制一格,那么公式就变为=C1,往下复制一格就变成=B2如果在B1的字母和数字前加上$号,那么就叫相对引用,加了$的部分不会因为公式的移动而变化.比如=$B1,向左或者向右复制,B都不会变成C或者A,比如=B$1,向下复制,1都不会变成2或者3,比如=$B$1,向任何方向复制，都会保持$B$1简单的说就是$在哪个前面，哪个就不会变。再讲下column()公式column就是返回括号中的单元格所在的列对应的数字column(B1)返回2，因为B列是第二列。column(G44)返回7，因为G列是第七列。我们可以利用column这个公式得到往右列逐渐增大的数列，1，2，3，4，5。所以我们将vlookup公式中第三部分改写成B11： =vlookup(A11,A1:M6,column(B2),false)这样我们会发现，将B11公式复制到C11，column(B2)会变成Column(C2)=3,即返回数据库中第三列的数字复制到M11,column(B2)会变成column(M2)=13,这样就达到了我们取得列代表的数字随着公式往右而逐渐增大的目的.但是当复制公式后会发现整个公式不能返回正确的值,那是因为我们没有调整好绝对引用和相对引用.当B11复制到C11时,公式变成:C11: =vlookup(B11,B1:N6,column(C2),false)这样代表根据vlookup函数的规则,意思是按照B11的值为条件,在B1:B6范围内找和B11值相同的那行的第3列.而B11根本不是我们想要查询的条件.因此找不到,返回错误信息.我们这时候就需要用到绝对引用了.把公式进一步改称:B11: =vlookup($A11,$A$1:$M$6,column(B2),false)$A11固定了A列,所以再往右复制查找的列也不会变,而11前面没有$,这样往下复制后会根据查找条件的变化而返回不同的数.将以上公式复制到绿色区域,就能一次完成整个区域的vlookup查找匹配. 4.用通配符配合vlookup做模糊查询通配符的意思是说这个符号可以代替任何字或者数字一共有2种:?可以代表1个字或者数字*可以代表任意多个字或者数字我们看下以下例子:根据A11为条件得到该学生的成绩 利用通配符可以将出了名字的其他信息都替代掉:B11: =VLOOKUP(?&A11&*,$A$1:$B$4,2,FALSE)以上公式的第一部分?&A11&*解释为:查找条件是: 任意1个字符+任意1个字符+王五+任意多字符显然在数据库区域内的初一王五0103112124是符合这个查找条件的注意的是: 1.通配符要用号括起来2.用&号连接引号部分和单元格引用部分 5.打破vlookup公式的局限性,反向查找前面提到vlookup有个特点,就是要查找的那列必须在所选区域的左数第一列,而返回的值所在的列必须在它右边,也就是从左往右的关系.那有没有办法让它从右往左找呢?通过一点小技巧我们可以办到.看下面的例子:数据库区中名字列在成绩列右边,没办法做vlookup,一般我们会把A列再复制一遍到C列然后再vlookup,但其实不用这么麻烦. 利用以下公式可以做到反向查找:B9: =VLOOKUP(A9,IF(1,0,B2:B4,A2:A4),2,FALSE)其他都好理解,当中一块不好理解IF(1,0,B2:B4,A2:A4)是什么意思呢?我们先来补充2个概念:第一个,if函数:if函数的公式: if(判断式,判断式为正确时返回的值,判断式为错误时返回的值)在判断式部分,有如下规则: 1代表true(正确); 0代表false(错误)所以 if(1,A,B将返回Aif(0,A,B将返回B第二个,数组的概念大学数学好的同学应该知道这个概念,数组就是一组数1,0代表1和0组成的一组数a1,a2.a10表示a1,a2.a10这10个量组成的一组数我们可以解释IF(1,0,B2:B4,A2:A4)了这个公式返回的也是一个2个项构成的数组,1对应的是true,对应的结果是B2:B40对应的是false,对应的是A2:A4.所以新生成的数组也就是 B2:B4,A2:A4我把它理解为和A2:B4一样的一个范围,只是A,B列顺序是反的(也不知道这样理解对伐)这样原本在第二列的B列又变成第一列了,原本在第一列的A列变成第二列了所以=VLOOKUP(A9,IF(1,0,B2:B4,A2:A4),2,FALSE)中取得第二列九等于原本A列的值.这个感觉太深奥了,有人听懂了伐? sumif公式的表达式为:=sumif(条件所对应的列,条件判断式,求和的列)举个例子:有2条手套的纪录,分别对应数量是22和41,和是63所以用sumif求和公式为: 以上例子sumif的条件判断式部分是一个单元格,意思是条件是A列中等于手套的条件判断式也可以是不等式,例如=SUMIF(A2:A10,&A14,B2:B10)代表不等于手套的项目数量求和以上就是sumif的基本用法了下面讲点实际应用.1.sumif与通配符结合,模糊条件求和如图,我们想知道所有姓张的人的收入通配符前面说过了,道理大家自己想 sumif就说到这里,因为它的作用可以被sumproduct完全替代,没多说的必要下面是countif,按照单条件计数countif(查找的范围,查找的条件)这样返回的是在查找范围内,符合条件的一共有多少个应用如下: sumproduct公式的本意是指几个数列的乘积和比如:A5=SUMPRODUCT(A1:A2,B1:B2)=A1*B1+A2*B2=2*4+3*5=8+15=23 以上的用法常常被用在计算总营业收入上,销量*单价=营业收入,当有很多种产品,我们各有他们的单价和销量的时候: sumproduct这个公式之所以如此重要不是因为以上的功能,而是另外一个功能,请看:这是一个典型的双条件求和,既要求A列是甲,又要求B列是2010利用sumproduct可以写成:=SUMPRODUCT(A2:A9=甲)*(B2:B9=2010)*(C2:C9) 是右括号阿. 我们必须要解释下以上公式,不然很多同学想到天边也想不出是什么意思.=SUMPRODUCT(A2:A9=甲*(B2:B9=2010)*(C2:C9)(A2:A9=甲和(B2:B9=2010)这两部分其实是2个等式,等式按照成立和不成立2种可能会返回2种值:成立-返回1不成立-返回0那么这个公式就可以被分解成以下8个结果的和:(A2=甲是不是成立)*(B2=2010是不是成立)*C2 -这里A2的值是甲所以第一个括号结果等于1,B2的值是2010所以第二括号结果也是1,所以整个等式就相当于1*1*C2=C2=305(A3=甲是不是成立)*(B3=2010是不是成立)*C3-这里A3的值是乙所以第一个括号结果等于0,B3的值是2011所以第二括号结果也是0,所以整个等式就相当于0*0*C3=0.一直到(A8=甲是不是成立)*(B8=2010是不是成立)*C8我们不难发现一个规律,只要某个条件不符合,那就会返回0,导致整个一条的结果都为0换成人类能听懂的语言就是: 找到条件A和条件B都符合的C列的和,既双条件求和有人懂了吗?其实如果大家要用的话不用记住以上的解释,只要记住以下公式,非常实用的公式sumproduct(条件式1)*(条件式2)*.(条件式N)*(求和的列)这里注意的是所有条件式和求和的列包含的单元格一定要一样多,不然返回错误.sumproduct的运用1，自动完成矩阵统计表。 sumproduct运用之2，利用不等式自动分段计算统计表 下面我们讲一个很好用但不太常用的函数，indirect=indirect(引用的范围描述)这个公式的含义是在括号里用文字写出要引用的范围，公式的结果就是这个范围的引用。比如=indirect(B8)出来的结果就是B8单元格的内容=sum(indirect(B1:B2)计算的结果就是sum(B1:B2)=B1+B2有些同学会觉得很无聊，我不会直接写成sum(B1:B2)吗？为什么要去用这个indirect呢？我们来看看它的妙用。如图，这个文件一共包含5个学生的个人成绩单，每个人的成绩单在一个工作表中且格式相同，且以该学生名字命名该工作表。现在要在第一张表“班级成绩统计表”中把所有学生的成绩都列在一起，怎样完成呢？ 统计表的样子是： 要求根据A列的名字把相应工作表的C4单元格内容贴到B列相应位置。 继续说indirect，上面的例子用indirect公式就非常方便了：入图中的公式,B3: =INDIRECT(A3&!C4括号中A3的值就是张三所以括号中的内容等同于张三!C4这样的写法就等于引用了以张三为名字的工作表中的C4单元格,即张三的成绩所在的单元格.我们巧妙利用了A列的名字和后面成绩单所在工作表的名字一致的原理完成了这一看似繁琐的问题. 接着我们讲index函数，大家怎么理解这个函数呢？其实非常简单，和match函数正好相反。index是在给定的范围内根据你给的行数，列数确定是哪个单元格并返回那个单元格的内容。=index(查找的范围，行号码，列号码，区域号码（仅在选择了多个区域的情况下需要）)为什么说index也可以代替vlookup的普通功能呢，我们看个简单的例子： index和match组合用法的原理就是用match获得所查条件匹配的行列数，然后用相同的行数在需要返回的那列找到相同行的单元格并返回其内容。 现在讲下index和match为什么可以发挥比vlookup更强大的功能.简单的来说,vlookup只能查询一维的.而index&match可以做三维的查询.同样举个例子.我们会发