EXCEL公式VLOOKUP函数用法详解.doc

EXCEL公式VLOOKUP函数用法详解默认分类 2010-11-09 20:42:26 阅读28690 评论2 字号：大中小订阅 VLOOKUP函数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值。这里所说的“数组”，可以理解为表格中的一个区域。数组的列序号：数组的“首列”，就是这个区域的第一纵列，此列右边依次为第2列、3列。假定某数组区域为B2:E10，那么，B2:B10为第1列、C2:C10为第2列。语法：VLOOKUP（查找值，区域，列序号，逻辑值）“查找值”：为需要在数组第一列中查找的数值，它可以是数值、引用或文字符串。“区域”：数组所在的区域，如“B2:E10”，也可以使用对区域或区域名称的引用，例如数据库或数据清单。“列序号”：即希望区域（数组）中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；若列序号小于1，函数VLOOKUP 返回错误值 #VALUE!；如果大于区域的列数，函数VLOOKUP返回错误值 #REF!。“逻辑值”：为TRUE或FALSE。它指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值；如果“逻辑值”为FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。如果“查找值”为文本时，“逻辑值”一般应为 FALSE 。另外：如果“查找值”小于“区域”第一列中的最小数值，函数 VLOOKUP 返回错误值 #N/A。如果函数 VLOOKUP 找不到“查找值” 且“逻辑值”为 FALSE，函数 VLOOKUP 返回错误值 #N/A。下面举例说明VLOOKUP函数的使用方法。假设在Sheet1中存放小麦、水稻、玉米、花生等若干农产品的销售单价： A B1 农产品名称 单价2 小麦 0.563 水稻 0.484 玉米 0.395 花生 0.51100 大豆 0.45Sheet2为销售清单，每次填写的清单内容不尽相同：要求在Sheet2中输入农产品名称、数量后，根据Sheet1的数据，自动生成单价和销售额。设下表为Sheet2： A B C D1 农产品名称 数量 单价 金额2 水稻 1000 0.48 4803 玉米 2000 0.39 780 在D2单元格里输入公式：=C2*B2 ；在C2单元格里输入公式：=VLOOKUP（A2，Sheet1!A2:B100，2，FALSE） 。如用语言来表述，就是：在Sheet1表A2:B100区域的第一列查找Sheet2表单元格A2的值，查到后，返回这一行第2列的值。这样，当Sheet2表A2单元格里输入的名称改变后，C2里的单价就会自动跟着变化。当然，如Sheet1中的单价值发生变化，Sheet2中相应的数值也会跟着变化。其他单元格的公式，可采用填充的办法写入。VLOOKUP函数使用注意事项说到VLOOKUP函数，相信大家都会使用，而且都使用得很熟练了。不过，有几个细节问题，大家在使用时还是留心一下的好。一VLOOKUP的语法VLOOKUP函数的完整语法是这样的：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)1括号里有四个参数，是必需的。最后一个参数range_lookup是个逻辑值，我们常常输入一个0字，或者False;其实也可以输入一个1字，或者true。两者有什么区别呢？前者表示的是完整寻找，找不到就传回错误值N/A；后者先是找一模一样的，找不到再去找很接近的值，还找不到也只好传回错误值N/A。这对我们其实也没有什么实际意义，只是满足好奇而已，有兴趣的朋友可以去体验体验。2Lookup_value是一个很重要的参数，它可以是数值、文字字符串、或参照地址。我们常常用的是参照地址。用这个参数时，有两点要特别提醒：A）参照地址的单元格格式类别与去搜寻的单元格格式的类别要一致，否则的话有时明明看到有资料，就是抓不过来。特别是参照地址的值是数字时，最为明显，若搜寻的单元格格式类别为文字，虽然看起来都是123，但是就是抓不出东西来的。而且格式类别在未输入数据时就要先确定好，如果数据都输入进去了，发现格式不符，已为时已晚，若还想去抓，则需重新输入。B）第二点提醒的，是使用时一个方便实用的小技巧，相信不少人早就知道了的。我们在使用参照地址时，有时需要将lookup_value的值固定在一个格子内，而又要使用下拉方式（或复制）将函数添加到新的单元格中去，这里就要用到“$”这个符号了，这是一个起固定作用的符号。比如说我始终想以D5格式来抓数据，则可以把D5弄成这样：$D$5，则不论你如何拉、复制，函数始终都会以D5的值来抓数据。3Table_array是搜寻的范围，col_index_num是范围内的栏数。Col_index_num不能小于1，其实等于1也没有什么实际用的。如果出现一个这样的错误的值#REF!，则可能是col_index_num的值超过范围的总字段数。二VLOOKUP的错误值处理。我们都知道，如果找不到数据，函数总会传回一个这样的错误值#N/A，这错误值其实也很有用的。比方说，如果我们想这样来作处理：如果找到的话，就传回相应的值，如果找不到的话，我就自动设定它的值等于0，那函数就可以写成这样：=if(iserror(vlookup(1,2,3,0)=true,0,vlookup(1,2,3,0)这句话的意思是这样的：如果VLOOKUP函数返回的值是个错误值的话（找不到数据），就等于0，否则，就等于VLOOKUP函数返回的值（即找到的相应的值）。这里面又用了两个函数。第一个是iserror函数。它的语法是iserror(value)，即判断括号内的值是否为错误值，如果是，就等于true，不是，就等于false。第二个是if函数，这也是一个常用的函数的，后面有机会再跟大家详细讲解。它的语法是if(条件判断式，结果1，结果2)。如果条件判断式是对的，就执行结果1，否则就执行结果2。举个例子：=if(D2=,空的,有东西)，意思是如D2这个格子里是空的值，就显示文字“空的”，否则，就显示“有东西”。（看起来简单吧？其实编程序，也就是这样子判断来判断去的。）三含有VLOOKUP函数的工作表档案的处理。一般来说，含有VLOOKUP函数的工作表，如果又是在别的档案里抓取数据的话，档案往往是比较大的。尤其是当你使用的档案本身就很大的时候，那每次开启和存盘都是很受伤的事情。有没有办法把文件压缩一下，加快开启和存盘的速度呢。这里提供一个小小的经验。在工作表里，点击工具选项计算，把上面的更新远程参照和储存外部连结的勾去掉，再保存档案，则会加速不少，不信你可以试试。下面详细的说一下它的原理。1含有VLOOKUP函数的工作表，每次在保存档案时，会同时保存一份其外部连结的档案。这样即使在单独打开这个工作表时，VLOOKUP函数一样可以抓取到数值。2在工作表打开时，微软会提示你，是否要更新远程参照。意思是说，你要不要连接最新的外部档案，好让你的VLOOKUP函数抓到最新的值。如果你有足够的耐心，不妨试试。3了解到这点，我们应该知道，每次单独打开含有VLOOKUP函数的工作表时，里面抓取外部档案的数值，只是上次我们存盘时保存的值。若要连结最新的值，必须要把外部档案同时打开。VLOOKUP函数我所了解的，也只是这些，大家有什么好的经验或有什么疑问，欢迎大家提出，一起探讨。excel 用vlookup函数跨表调取数据2009-03-13 14:12目标：从全校学生资料中调取本班学生资料1、下面是总表，有一千个学生的资料，我这里只举几项资料，如姓名、性别、出生年月日、学号、民族。2、插入一个工作表，命名“一（1）班”，这个是自己班学生的名字，班主任当然很快打得出来。3、现在B2单元格里输入“=VLOOKUP($A2,总表!$A$2:$E$1000,2,0)”，回车就可以显示小明的性别了。注意：输入内容必须是英文状态下输入。解释：$A2-代表本工作表要查找的单元格（意思是要到别的工作表查找与这个单元格相同的内容）总表!-代表要查找的工作表（要在“总表”工作表里查找）$A$2:$E$1000-找在哪个范围查找，即A2到E1000这个范围内2-代表要显示哪一列的数据，即查找范围是A2到B7，那A列为第1列，我想显示B列的数据，B（性别）列为第2列。4、选中B2这一格，鼠标移到右下角出现黑十字时，单击左键不放往下拉，拉到最后一名学生再放开，所有的学生的性别就出现了。5、再回来B2，像上面一步一样往右拉。一看效果怎么打横的全都是性别，别急，再看下一步。6、选中C2，看看函数栏，原来要显示的列数还没改，还是第2列，那“出生年月日”是第3列，就改为3，再回车。效果出来了，看到出生年月了，然后用上面的方法往下拉，把全班的拉出来。7、如此类推，把其他列改过来之后，再往下拉，那么就可以显示所有的资料了。8、如果一（1）班的班主任做完了，那么其他班的班主任就不用这么麻烦了，只要把自己班的学生名单复制，然后覆盖一（1）班学生的姓名，其他资料就跟着变了，呵呵！=VLOOKUP($A1,Sheet2!$A$1:$C$14864,3,0)比较文本函数函数 2007-07-19 22:40:09 阅读127 评论0 字号：大中小订阅 本题中，利用vlookup函数查找时，不区分大小写，查找的结果相同。EXACT 该函数测试两个字符串是否完全相同。如果它们完全相同，则返回 TRUE；否则，返回 FALSE。函数 EXACT 能区分大小写，但忽略格式上的差异。利用函数 EXACT 可以测试输入文档内的文本。语法EXACT(text1,text2)Text1 待比较的第一个字符串。Text2 待比较的第二个字符串。B11=LOOKUP(1,1/EXACT(A11,$A$3:$A$7),$C$3:$C$7)VLOOKUP的使用详解转载 2010-03-17 19:25:17 阅读1184 评论0 字号：大中小订阅 材料一：使用语法 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Lookup_value 为需要查找的值。Lookup_value 可以为数值、引用或文本字符串。 Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。 如果 range_lookup 为 TRUE，则 table_array 的第一列中的数值必须按升序排列：、-2、-1、0、1、2、-Z、FALSE、TRUE；否则，函数 VLOOKUP 不能返回正确的数值。如果 range_lookup 为 FALSE，table_array 不必进行排序。 通过在“数据”菜单中的“排序”中选择“升序”，可将数值按升序排列。 Table_array 的第一列中的数值可以为文本、数字或逻辑值。 文本不区分大小写。 Col_index_num 为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时，返回 table_array 第一列中的数值；col_index_num 为 2，返回 table_array 第二列中的数值，以此类推。如果 col_index_num 小于 1，函数 VLOOKUP 返回错误值值 #VALUE!；如果 col_index_num 大于 table_array 的列数，函数 VLOOKUP 返回错误值 #REF!。 Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。 说明 如果函数 VLOOKUP 找不到 lookup_value，且 range_lookup 为 TRUE，则使用小于等于 lookup_value 的最大值。 如果 lookup_value 小于 table_array 第一列中的最小数值，函数 VLOOKUP 返回错误值 #N/A。 如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE，函数 VLOOKUP 返回错误值 #N/A。 应用示例：上图中，为方便比较，我将原始数据区域放在了同一工作表中（E1:F5）,实际使用时，原始数据可以在不同的工作表，甚至不同的工作簿（即不同的Excel文件）。当被查找的内容与原始内容在不同的工作表，table_array前面需加上工作表的名称，写法为 “表名! ”区域范围，如“Sheet2!$A$1:$B$12”，而若在不同的工作簿，则还得加上文件名，如“文件名sheet1!$A$1:$B$12”。 详细解释 公式“=Vlookup(A2,$E$2:$F$5,2,FALSE)”中A2表示要查找的值为A2单元格的内容，即“Apple”, “$E$2:$F$5”告诉电脑，应该去$E$2:$F$5这个数据区域中查找，“2”表示找到后，应传回该区域第二列的值，即数量列，最后“FALSE”参数系统，查找区域内容未进行排序，需使用精确查找，找不到就算了，不返回近似匹配值。 特别要注意的是，通常我们都是使用鼠标拖动的方法来填充公式，而拖动时，Excel对公式中区域的引用，处理方法是不一样的。如果是相对参照，即栏名列号前没有“$”符号，则Excel会对该区域作相对位移，如上栏是E2:B5,拖到下栏后，即会自动成为E3:B6，这种处理方法在很多公式中是必要的，但在这个公式中却是致命的，因为它更改了查找的原始数据的区域，导致实际上包含有的数据，因己不在查找的区域中而漏网。这也是很多用户在实际应用中犯的错误，引致查找结果不真实。要解决这个问题，我们可以利用Excel对区域引用的第二种方法：绝对参照。即在栏名列号前加上“$”，这样，系统就不会作相对的位移，无论怎样拖，区域范围都不变 相对参照与绝对参照的写法，可以让电脑作自动转换。方法是，先将当前单元格定位在要修改的单元格上，然后在资料编辑列，用鼠标涂黑（英文的说法叫Highlight）要转换的部分，再按“F4”即可。见下图：通过上图可以看出，能找到的，系统己自动填入了找到的值，如Apple & cherry，对于找不到的（Plum & Pear），则显示#N/A材料二：VLOOKUP函数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值。这里所说的“数组”，可以理解为表格中的一个区域。数组的列序号：数组的“首列”，就是这个区域的第一纵列，此列右边依次为第2列、3列。假定某数组区域为B2:E10，那么，B2:B10为第1列、C2:C10为第2列。语法：VLOOKUP（查找值，区域，列序号，逻辑值）“查找值”：为需要在数组第一列中查找的数值，它可以是数值、引用或文字符串。“区域”：数组所在的区域，如“B2:E10”，也可以使用对区域或区域名称的引用，例如数据库或数据清单。“列序号”：即希望区域（数组）中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；若列序号小于1，函数VLOOKUP 返回错误值 #VALUE!；如果大于区域的列数，函数VLOOKUP返回错误值 #REF!。“逻辑值”：为TRUE或FALSE。它指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值；如果“逻辑值”为FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。如果“查找值”为文本时，“逻辑值”一般应为 FALSE 。另外：如果“查找值”小于“区域”第一列中的最小数值，函数 VLOOKUP 返回错误值 #N/A。如果函数 VLOOKUP 找不到“查找值” 且“逻辑值”为 FALSE，函数 VLOOKUP 返回错误值 #N/A。下面举例说明VLOOKUP函数的使用方法。假设在Sheet1中存放小麦、水稻、玉米、花生等若干农产品的销售单价： A B1 农产品名称 单价2 小麦 0.563 水稻 0.484 玉米 0.395 花生 0.51100 大豆 0.45Sheet2为销售清单，每次填写的清单内容不尽相同：要求在Sheet2中输入农产品名称、数量后，根据Sheet1的数据，自动生成单价和销售额。设下表为Sheet2： A B C D1 农产品名称 数量 单价 金额2 水稻 1000 0.48 4803 玉米 2000 0.39 780 在D2单元格里输入公式：=C2*B2 ；在C2单元格里输入公式：=VLOOKUP（A2，Sheet1!A2:B100，2，FALSE）。如用语言来表述，就是：在Sheet1表A2:B100区域的第一列查找Sheet2表单元格A2的值，查到后，返回这一行第2列的值。这样，当Sheet2表A2单元格里输入的名称改变后，C2里的单价就会自动跟着变化。当然，如Sheet1中的单价值发生变化，Sheet2中相应的数值也会跟着变化。其他单元格的公式，可采用填充的办法写入。VLOOKUP函数使用注意事项说到VLOOKUP函数，相信大家都会使用，而且都使用得很熟练了。不过，有几个细节问题，大家在使用时还是留心一下的好。一VLOOKUP的语法VLOOKUP函数的完整语法是这样的：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)1括号里有四个参数，是必需的。最后一个参数range_lookup是个逻辑值，我们常常输入一个0字，或者False;其实也可以输入一个1字，或者true。两者有什么区别呢？前者表示的是完整寻找，找不到就传回错误值N/A；后者先是找一模一样的，找不到再去找很接近的值，还找不到也只好传回错误值N/A。这对我们其实也没有什么实际意义，只是满足好奇而已，有兴趣的朋友可以去体验体验。2Lookup_value是一个很重要的参数，它可以是数值、文字字符串、或参照地址。我们常常用的是参照地址。用这个参数时，有两点要特别提醒：A）参照地址的单元格格式类别与去搜寻的单元格格式的类别要一致，否则的话有时明明看到有资料，就是抓不过来。特别是参照地址的值是数字时，最为明显，若搜寻的单元格格式类别为文字，虽然看起来都是123，但是就是抓不出东西来的。而且格式类别在未输入数据时就要先确定好，如果数据都输入进去了，发现格式不符，已为时已晚，若还想去抓，则需重新输入。B）第二点提醒的，是使用时一个方便实用的小技巧，相信不少人早就知道了的。我们在使用参照地址时，有时需要将lookup_value的值固定在一个格子内，而又要使用下拉方式（或复制）将函数添加到新的单元格中去，这里就要用到“$”这个符号了，这是一个起固定作用的符号。比如说我始终想以D5格式来抓数据，则可以把D5弄成这样：$D$5，则不论你如何拉、复制，函数始终都会以D5的值来抓数据。3Table_array是搜寻的范围，col_index_num是范围内的栏数。Col_index_num不能小于1，其实等于1也没有什么实际用的。如果出现一个这样的错误的值#REF!，则可能是col_index_num的值超过范围的总字段数。二VLOOKUP的错误值处理。我们都知道，如果找不到数据，函数总会传回一个这样的错误值#N/A，这错误值其实也很有用的。比方说，如果我们想这样来作处理：如果找到的话，就传回相应的值，如果找不到的话，我就自动设定它的值等于0，那函数就可以写成这样：=if(iserror(vlookup(1,2,3,0)=true,0,vlookup(1,2,3,0)这句话的意思是这样的：如果VLOOKUP函数返回的值是个错误值的话（找不到数据），就等于0，否则，就等于VLOOKUP函数返回的值（即找到的相应的值）。这里面又用了两个函数。第一个是iserror函数。它的语法是iserror(value)，即判断括号内的值是否为错误值，如果是，就等于true，不是，就等于false。第二个是if函数，这也是一个常用的函数的，后面有机会再跟大家详细讲解。它的语法是if(条件判断式，结果1，结果2)。如果条件判断式是对的，就执行结果1，否则就执行结果2。举个例子：=if(D2=”,”空的”,”有东西”)，意思是如D2这个格子里是空的值，就显示文字“空的”，否则，就显示“有东西”。（看起来简单吧？其实编程序，也就是这样子判断来判断去的。）三含有VLOOKUP函数的工作表档案的处理。一般来说，含有VLOOKUP函数的工作表，如果又是在别的档案里抓取数据的话，档案往往是比较大的。尤其是当你使用的档案本身就很大的时候，那每次开启和存盘都是很受伤的事情。有没有办法把文件压缩一下，加快开启和存盘的速度呢。这里提供一个小小的经验。在工作表里，点击工具选项计算，把上面的更新远程参照和储存外部连结的勾去掉，再保存档案，则会加速不少，不信你可以试试。下面详细的说一下它的原理。1含有VLOOKUP函数的工作表，每次在保存档案时，会同时保存一份其外部连结的档案。这样即使在单独打开这个工作表时，VLOOKUP函数一样可以抓取到数值。2在工作表打开时，微软会提示你，是否要更新远程参照。意思是说，你要不要连接最新的外部档案，好让你的VLOOKUP函数抓到最新的值。如果你有足够的耐心，不妨试试。3了解到这点，我们应该知道，每次单独打开含有VLOOKUP函数的工作表时，里面抓取外部档案的数值，只是上次我们存盘时保存的值。若要连结最新的值，必须要把外部档案同时打开。Excel 2007查询操作中的函数应用默认分类 2009-06-05 16:51:42 阅读330 评论0 字号：大中小订阅 内容摘要：我们需要把学生几次考试成绩从不同的工作表中汇总到一个新的工作表中，而这几个工作表中的参考人数及排列顺序是不完全相同的，并不能直接复制粘贴。此时，如果使用Excel的VLOOKUP、INDEX或者OFFSET函数就可以使这个问题变得非常简单。我们以Excel 2007为例。 在Excel中，我们经常会需要从某些工作表中查询有关的数据复制到另一个工作表中。比如我们需要把学生几次考试成绩从不同的工作表中汇总到一个新的工作表中，而这几个工作表中的参考人数及排列顺序是不完全相同的，并不能直接复制粘贴。此时，如果使用Excel的VLOOKUP、INDEX或者OFFSET函数就可以使这个问题变得非常简单。我们以Excel 2007为例。图1假定各成绩工作表如图1所示。B列为姓名，需要汇总的项目“总分”及“名次”位于H列和I列（即从B列开始的第7列和第8列）。而汇总表则如图2所示，A列为姓名列，C、D两列分别为要汇总过来的第一次考试成绩的总分和名次。其它各次成绩依次向后排列。 图2一、VLOOKUP函数我们可以在“综合”工作表的C3单元格输入公式“=VLOOKUP($B3,第1次!$B$1:$I$92,7,FALSE)”，回车后就可以将第一位同学第一次考试的总分汇总过来了。把C3单元格公式复制到D3单元格，并将公式中第三个参数“7”改成“8”，回车后，就可以得到该同学第一次考试名次。选中C3:D3这两个单元格，向下拖动填充句柄到最后就可以得到全部同学的总分及名次了。是不是很简单呀？如图3所示。/page VLOOKUP函数的用法是这样的：VLOOKUP(参数1,参数2,参数3,参数4)。“参数1”是“要查找谁？”本例中B3单元格，那就是要查找B3单元格中显示的人名。“参数2”是“在哪里查找？”本例中“第1次!$B$1:$I$92”就是告诉Excel在“第1次”工作表的B1:I92单元格区域进行查找。“参数3”是“找第几列的数据？”本例中的“7”就是指从“第1次”工作表的B列开始起，第7列的数据，即H列。本例中“参数4”即“FALSE”是指查询方式为只查询精确匹配值。该公式先在“第1次”工作表的B!:I92单元格区域的第一列（即B1:B92单元格区域）查找B3单元格数据，找到后，返回该数据所在行从B列起第7列（H列）的数据。所以，将参数3改成“8”以后，则可以返回I列的数据。由此可以看出，使用VLOOKUP函数时，参数1的数据必须在参数2区域的第一列中。否则是不可以查找的。二、INDEX函数某些情况下，VLOOKUP函数可能会无用武之地，如图4所示。“综合”工作表中，姓名列放到了A列，而B列要求返回该同学所在的班级。但我们看前面的工作表就知道了，“班级”列是位于“姓名”列前面的。所以，此时我们不可能使用VLOOKUP函数来查找该同学的班级。而INDEX函数就正可以一试身手。图4在B3单元格输入公式“=INDEX(第1次!$A$1:$I$92,MATCH(A3,第1次!$B$1:$B$92,0),1)”，回车并向下复制公式就可以了，如图5所示。/page 图5这里用到了两个函数，INDEX和MATCH。先说说这个MATCH(A3,第1次!$B$1:$B$92,0)。它的意思是在“第1次”工作表的B1:B92单元格区域中查找A3单元格数据，然后返回该数据在B1:B92单元格区域中的行数。以本例而言，该公式返回的结果是“2”。这一点，只要看一下图1所示工作表就清楚了。至于该公式中最后一个参数“0”是要求精确匹配，而且使用该参数时，B1:B92单元格区域不需要排序。再来看这个INDEX函数。它的用法是INDEX(参数1,参数2,参数3)。参数1是要查找的区域。参数2是行数，参数3是列数。比如公式INDEX(A2:C6,2,3)的意思就是要返回在A2:C6这个区域中第二行第三列的数据，即C3单元格的数据。所以，本例中公式的意思就是返回“第1次”工作表A1:I92单元格区域中第二行第一列的数据，呵呵，那不正是该同学所在的班级嘛！与VLOOKUP函数相比较，INDEX函数的“限制”要少一些。三、OFFSET函数其实，我们还可以使用OFFSET函数完成这个任务的。比如我们要查A3单元格同学在第二次考试中的总分。那么只需要点击E3单元格，并输入公式“=OFFSET(第2次!$A$1,MATCH($A3,第2次!$B$1:$B$92,0)-1,7,1,1)”，回车后并向下复制公式，就可以得到所需要的数据了。如图6所示。图6OFFSET函数是以指定的引用为参照系，通过给定偏移量得到新的引用，比如公式“=OFFSET(C3,2,3,1,1)”，它的意思就是返回以C3单元格为基准，向下偏移2个单元格，向右偏移3个单元格处的单元格数据，即F5单元格的数据，数一下就清楚了。所以本例公式中先由“MATCH($A3,第2次!$B$1:$B$92,0)”得到“第2次”工作表B1:B92单元格中与A3单元格相匹配的数据所在行数（本例结果为2），再减去1正好是它相对于“第2次”工作表A1单元格的向下偏移的行数，而后面的参数“7”，则是该同学的总分成绩所在单元格相对于A1单元格的向右偏移量。因此，本例中在E3单元格的公式其实就是“=OFFSET(第2次!$A$1,1,7,1,1)”，其意思就是返回“第2次”工作表中相对于A1单元格下移一行右移7行处单元格数据。好了，这三个查找引用函数的用法应该算说清楚了吧？只要我们认真研究它们的用法，相信会有很多妙用的。Excel中实现隔行删除默认分类 2009-06-03 23:28:20 阅读151 评论0 字号：大中小订阅 Excel中实现隔行删除 选一个空列做辅助列 假设是F列 在F1里填上 =MOD(ROW(F1), 2) 然后复制到本列其它单元格中 打开自动筛选 选中0或1 然后全选 删除 就搞定了 选的时候要选中整行 我试过的 完全可以 这个方法只能说是半自动的,对付几百行还能将就 如果真的有上万行,还是做个宏好一些 而且也很简单 Sub RowsDelete(Odd As Long) Dim nRows As Long Dim i As Long With Worksheets(sheet1) nRows = .UsedRange.Rows.Count For i = nRows To 2 Step -1 If i Mod 2 = Odd Then .Rows(i).Delete End If Next End With End Sub Odd为0时删除偶数行 为1时删除奇数行 这种东西为什么要用VBA或者宏? 只需要加一个辅助列即可: 在某列输入公式: =MOD(ROW(),2) 然后把公式复制到相应的数据区域即可. 如果您希望删除奇数列,那么使用筛选,选取辅助列值为1,然后选中这些行,删除即可. 如果您希望删除偶数列,那么选取辅助列值为0,然后选中筛选出来的行,删除即可. 然后选中辅助列,进行排序,这样中间就不会出现空行了。最后删除辅助列 远比使用VBA来的快捷.另外:行数即使真的几万行一样可行.如果是几万行 复制一次公式也挺烦的 不如VBA来得方便 楼主可以打开你的Excel文件 按 Alt+F11 打开VBA编辑器 在左边找到该文件的ThisWork 双击它 然后把俺的代码复制到右边的代码区 这段代码是处理名为sheet1的表单的 你要根据你的情况改一下 把光标点到这段代码里 然后按F5 哗 整个世界清静了 选定你要*作的工作表 工具-宏-VB编辑器 插入-模块 复制下面的代码行,删除前面的引号,并修改其中注释的fristline和linecount后面的数字,然后按下F5运行即可 Sub Macro1() fristline = 1 填需删除的第一行的行号 linecount = 100 填需删除的最后一行的行号 fristdelete = Int(fristline + 1) / 2) * 2 str1 = fristdelete For i = 1 To (linecount - fristdelete) / 2 str1 = str1 & , & fristdelete + i * 2 Next Range(str1).Delete End Sub EXCEL中查找函数的妙用Excel应用技巧 2009-12-03 16:16:07 阅读771 评论0 字号：大中小订阅 作为一个电子表格处理程序,EXCEL除了具有强大的计算和汇总功能外,其内嵌的各类功能函数在进行表格处理中给我们提供了极大的便利,可以极高的提高工作效率和准确性。 下面介绍几个在实际工作中可能会经常用到的查找函数:MATCH()、LOOKUP()、HLOOKUP()、VLOOKUP(),这些查找函数不仅仅具有查对的功能,同时还能根据查找的结果和参数的设定得到我们需要的数值。特别是这几个函数的配合使用,并以两个逻辑函数IF()和ISERROR()辅助,我们就可以在两个或多个有一定关联的工作簿中动态生成新的数据列。 1.MATCH() 格式:MATCH(lookup_value,lookup_array,match_type) 从英文含义我们可以看出,它是一个匹配的函数,主要功能是按照match_type参数所指定的查找方式,在lookup_array参数所代表的数据组中查找lookup_value参数代表的数值,如果查找成功则返回lookup_value在lookup_array中的位置,不成功返回错误信息#N/A。 EXCEL在“编辑”菜单中提供的“查找”功能只能进行单一数值的查找,而MATCH()函数可以实现批量数值的查找,所需要的只是在需要查找的首个单元格中定义好该函数,然后拖动该单元格直至到需要的范围,所有需要查对单元格的查找结果会自动完成。 参数解释: lookup_value需要查找的值,可以是实际的数字、字符串或逻辑值,也可以是某一单元格的引用,通常使用单元格的引用; lookup_array查找范围,可以是定义的数组,也可以是自定义的多个连续单元格的引用,这个引用我们可以通过两种方式实现: ? 直接输入连续单元格的地址,通常使用绝对引用地址,如$B$2:$B$50; ? 选定一个连续的单元格区域后,通过“插入”菜单中“定义”项的“名称”给这段单元格区域定义一个名称,如NAME1,这样在以后所有使用该单元格区域的时候都可以用定义的名称代表。 match_type,查找的方式,其值为-1、0、1,默认为1。其中-1是在lookup_array查找大于或等于lookup_value的最小数值,要求lookup_array必须按降序排列;0是在lookup_array查找等于lookup_value的第一个数值,不需要排序;1是在lookup_array查找小于或等于lookup_value的最大数值,要求lookup_array必须按升序排列。 函数举例: 例1:有两个工作簿 BOOK1和BOOK2,它们的SHEET1中都具有名为“卡片号”的列,列标为B(假设BOOK1中该列包含数据的单元格为B2:B80,BOOK2中包含数据的单元格为B2:B1000),想查对一下BOOK1中的卡片号是否能够在BOOK2中的卡片号中全部查找到。 按照以上的需求我们可以进行如下的操作: (1)定义引用的名称,拖动鼠标选中BOOK2中B2:B1000单元格,通过“插入”菜单的“定义”“名称”项,给其定义为NAME1; (2)在BOOK1中插入一空列,假定列标为H,并在H2单元格中输入如下的公式: =MATCH(B2,“BOOK2.XLS”!NAME1,0); (3)选中H2单元格,将鼠标指针移动至该单元格右下角的黑点处,此时指针变为小黑十字形状,按住左键,拖动鼠标至H80单元格,此时H2到H80单元格会自动显示出B2至B80在BOOK2中定义的NAME1所代表的单元格区域的位置(如果找到),或是#N/A(未找到); (4)通过“数据”菜单的“筛选”“自动筛选”功能项,可以查看所有匹配的项(H列的数值大于0),或是所有不匹配的项(H列的数值为#N/A)。 以上1、2步骤可以合并为直接在H2单元格中输入如下的函数: =MATCH(B2,BOOK2.XLSSHEET1!$B$2:$B$1000,0) 需要注意的是,一定要使用$B$2:$B$1000,不能使用B2:B1000,否则在拖动鼠标时,其单元格范围会随着发生变化,产生错误的结果。另外在引用外部工作簿时,如果定义了引用名称,书写格式为:“工作簿名称”!引用名称,如“BOOK2.XLS”!NAME1;如果使用表格区域引用,格式为:工作簿名称工作表名称!表格区域,如BOOK2.XLSSHEET1!$B$2:$B$1000。 2.LOOKUP()、HLOOKUP()、VLOOKUP() LOOKUP()、HLOOKUP()、VLOOKUP()函数的功能都是在数组或表格中查找指定的数值,并按照函数参数设定得值返回表格或数组当前列(行)中指定行(列)处的数值。 由于LOOKUP()函数在单行(列)区域查找数值,并返回第二个单行(列)区域中相同位置的数值,或是在数组的第一行(列)中查找数值,返回最后一行(列)相同位置处的数值,其适用范围具有比较大的局限性,在实际的应用中,通常使用更加灵活的HLOOKUP()和VLOOKUP()函数。 HLOOKUP()和VLOOKUP()的作用类似,其区别是HLOOKUP()在表格或数组的首行查找数值,返回表格或数组当前列中指定行的数值,而VLOOKUP()是在表格或数组的首列查找数值,并返回表格或数组当前行中指定列的数值。这里所说的表格是按单元格地址设定的一个表格区域,如A2:E8。 HLOOKUP()函数的格式如下: HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) 参数解释: lookup_value需要在表格或数组第一行中查找的数值,可以是数值、字符串或引用; table_array需要在其中查找数值的表格区域、数组或是表格区域的引用; row_index_num为 table_array 中待返回的匹配值的行序号; range_lookup为一逻辑值,为TRUE或省略该值时,要求table_array第一行的数据必须升序排列,否则会得到错误的结果,同时表示待查找内容与查找内容近似匹配就可以了,如果不能精确匹配的话,则函数返回小于lookup_value的最大数值;如果为FALSE,不需要table_array的数值进行排序,并要求精确匹配,如果没有找到则函数返回#N/A。 VLOOKUP()函数的格式如下: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 函数的参数中除了col_index_num表示table_array 中待返回的匹配值的列序号外,其他参数的意义和HLOOKUP()相同。 函数举例: 例2:在BOOK2中,需要根据每一行中的资产类别(列标为D),自动的计算出该资产的折旧年限。 我们可以进行如下的操作: (1)由于在现有的表格当中不存在资产类别和折旧年限的对应关系,因此首先要根据资产分类的情况构造出一个数组,数组的第一行为资产的类别名称,第二行为资产所对应的折旧年限,“类别1”,“类别2”,“类别3”,“类别4”,“类别5”;4,6,7,8,10; (2)在BOOK2的SHEET1中插入一空列,列标为K,在K2单元格中输入如下的公式: =HLOOKUP(D2, “类别1”,“类别2”,“类别3”,“类别4”,“类别5”;4,6,7,8,10,2,FALSE), (3)选中K2单元格,将鼠标指针移动至该单元格右下角的黑点处,指针变为小黑十字形状时,按住左键,拖动鼠标至K80单元格,折旧年限会自动地显示出来。 例3:按照BOOK1 中SHEET1的卡片号(列标为B)从BOOK2中SHEET1的型号列(列标为E)获取与该卡片号对应的内容,从而在BOOK1的SHEET1中自动的生成一个新列(列标为U)