EXCEL查找与引用函数VLOOKUP的应用法及实例 详解

第一篇：EXCEL查找与引用函数VLOOKUP的应用法及实例 详解

EXCEL查找与引用函数VLOOKUP的应用法及实例详解

在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数 VLOOKUP 代替函数 HLOOKUP。

在 VLOOKUP 中的 V 代表垂直。语法

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP（查找XX，在YY区域中，与查找值相对的第ZZ列的值，方式）lookup_value

为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。

如下图所示，C3单元格即为lookup_value

名称的引用，例如数据库或列表。

如下图所示：C6：F10即为

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!。

如下图所示，4表示数据表的第4列，col_index_num

range_lookup

如果 range_lookup 为 TRUE，则 table_array 的第一列中的数值必须按升序排列：„、-

2、-1、0、1、2、„、-Z、FALSE、TRUE；否则，函数 VLOOKUP 不能返回正确的数值。如果 range_lookup 为 FALSE，table_array 不必进行排序。

（通过在“数据”菜单中的“排序”中选择“升序”，可将数值按升序排列。）为了方便起见，一般选用 FALSE，即表格不必要再按升序又排列一次。

table_array 的第一列中的数值可以为文本、数字或逻辑值。文本不区分大小写。

如下图所示，FALSE 即为range_lookup表示第一列的数值不必进行排序

range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。

说明

如果函数 VLOOKUP 找不到 lookup_value，且 range_lookup 为 TRUE，则使用小于等于 lookup_value 的最大值。

VLOOKUP 返回错误值 #N/A。

如果 lookup_value 小于 table_array 第一列中的最小数值，函数

如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE，函数 VLOOKUP 返回错误值 #N/A。

----------------------------对于可能产生错误值的防范 将公式进行以下修改

=IF(C3=“",”查询值为空，请填写查询值“,IF(ISERROR(VLOOKUP(C3,C6:F10,4,FALSE))=TRUE,”请检查参数是否正确",VLOOKUP(C3,C6:F10,4,FALSE)))如下图所示，如果查找时未指定名称，则公式的计算结果为“查询值为空，请填写查询值”

如下图所示，因使用的是FALSE，精确查找，当查找值为“西瓜”时，因该值不在列表中，会产生#N/A 错误，但使用公式用，可提示有参数错误

上述公式，使用了函数ISERROR检查是否有错误值，如果有兴趣你也可以使用函数ERROR.TYPE返回错误值对应的数字，从而明确显示是哪一类型的错误，错误值对应的数字如下表所示

呵呵，关于VLOOKUP就介绍到这里，祝大家工作愉快！

第二篇：关于VLOOKUP函数的用法

关于VLOOKUP函数的用法

“Lookup”的汉语意思是“查找”，在Excel中与“Lookup”相关的函数有三个：VLOOKUP、HLOOKUO和LOOKUP。下面介绍VLOOKUP函数的用法。

一、功能

在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的数据。

二、语法

标准格式：

VLOOKUP(lookup_value,table_array,col_index_num , range_lookup)

三、语法解释

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)可以写为：

VLOOKUP(需在第一列中查找的数据,需要在其中查找数据的数据表,需返回某列值的列号,逻辑值True或False)

1.Lookup_value为“需在数据表第一列中查找的数据”，可以是数值、文本字符串或引用。

2.Table_array 为“需要在其中查找数据的数据表”，可以使用单元格区域或区域名称等。

⑴如果 range_lookup 为 TRUE或省略，则 table_array 的第一列中的数值必须按升序排列，否则，函数 VLOOKUP 不能返回正确的数值。

如果 range_lookup 为 FALSE，table_array 不必进行排序。

⑵Table_array 的第一列中的数值可以为文本、数字或逻辑值。若为文本时，不区分文本的大小写。

3.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!。

4.Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。

四、应用例子

A B C D编号 姓名 工资 科室2005001 周杰伦 2870 办公室2005002 萧亚轩 2750 人事科2005006 郑智化 2680 供应科2005010 屠洪刚 2980 销售科2005019 孙楠 2530 财务科2005036 孟庭苇 2200 工 会

A列已排序（第四个参数缺省或用TRUE）

VLOOKUP(2005001,A17,2,TRUE)等于“周杰伦”

VLOOKUP(2005001,A17,3,TRUE)等于“2870”

VLOOKUP(2005001,A17,4,TRUE)等于“办公室”

VLOOKUP(2005019,A1:D7,2,TRUE)等于“孙楠”

VLOOKUP(2005036,A1:D7,3,TRUE)等于“2200”

VLOOKUP(2005036,A1:D7,4,TRUE)等于“工 会”

VLOOKUP(2005036,A1:D7,4)等于“工 会”

若A列没有排序，要得出正确的结果，第四个参数必须用FALAEVLOOKUP(2005001,A1:D7,2,FALSE)等于“周杰伦”VLOOKUP(2005001,A1:D7,3,FALSE)等于“2870”

VLOOKUP(2005001,A1:D7,4,FALSE)等于“办公室”VLOOKUP(2005019,A1:D7,2,FALSE)等于“孙楠”VLOOKUP(2005036,A1:D7,3,FALSE)等于“2200”

VLOOKUP(2005036,A1:D7,4,FALSE)等于“工 会”

五、关于TRUE和FALSE的应用

先举个例子，假如让你在数万条记录的表格中查找给定编号的某个人，假如编号已按由小到大的顺序排序，你会很轻松地找到这个人；假如编号没有排序，你只好从上到下一条一条地查找，很费事。

用VLOOKUP查找数据也是这样，当第一列已排序，第四个参数用TRUE（或确省），Excel会很轻松地找到数据，效率较高。当第一列没有排序，第四个参数用FALSE，Excel会从上到下一条一条地查找，效率较低。

笔者觉得，若要精确查找数据，由于计算机运算速度很快，可省略排序操作，直接用第四个参数用FALSE即可。

最近爱上了VLOOKUP，有人还对它进行了更新。因为它的漏洞就是只能返回重复值得第一个值。下面就详细来叙述一下吧！

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value 为需要在Table_array第一列中查找的数值。

可以为数值、引用或文本字符串。需要注意的是类型必须与table_array第一列的类型一致。查找文本时，文本不区分大小写；可以使用通配符“*”、“?”。

Table_array 为需要在其中查找数据的数据表。

可以使用对区域或区域名称的引用、常数数组、计算后的内存数组。对区域引用时，可以引用整列,excel会自动判断使用区域。

该参数的第一列必须包含查找的内容，其它列包含需返回的内容；返回内容的列序号由下个参数指定。

Col_index_num 为table_array中待返回的匹配值的列序号。

如为1时，返回table_array第一列中的数值；为2，返回table_array第二列中的数值，以此类推。

如果col_index_num小于1，函数 VLOOKUP 返回错误值值 #VALUE!；

如果col_index_num大于table_array的列数，函数 VLOOKUP 返回错误值 #REF!。

Range_lookup 为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。

如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；

近似匹配查询一般用于数值的查询，table_array的第一列必须按升序排列；否则不能返回正确的结果。

如果range_value为FALSE（或0），函数VLOOKUP将返回精确匹配值。

此时，table_array不必进行排序。如果找不到，则返回错误值#N/A；可isna检测错误后使用if判断去除错误信息。

VLOOKUP 经常会出现错误的#N/A,下面是几种可能性： 数据有空格或者数据类型不一致。

可以在lookup_value 前用TRIM（）将空格去除。

如果格式不一致，可以将数值强制转换成文本，lookup_value之后用&跟""表示的空字符串。将文本转换成数值，lookup_value*1进行运算。假设有以下两个表格：表1：

姓名 身份证 学科 成绩

张三 310111 物理 A

李斯 310112 化学 B王五 310113 政治 A赵六 310114 物理 B

张三 310111 政治 C

赵六 310114 语文 B刘备 310115 英语 D

表2：

姓名 身份证 住址 学科 成绩

曹操 319723 陕西马超 310197 甘肃赵云 319784 黑龙江

李斯 310112 浙江刘备 310115 江苏张三 310111 上海

请问：如何快速的将表2的学科和成绩自动填入？直接使用VLOOKUP即可。示例如下：

=VLOOKUP($B1,Sheet1!$B$1C$5,2,FALSE)

涵义：在Sheet1的单元格区间B1:C5，查找符合条件为：第一列与当前工作表单元格B1的值相等的第二列的值。

$B1 条件值

Sheet1!$B$1C$5 要查找区间（注意：请使用绝对引用）2 查找的结果为区域中的第几列FALSE 表示精确查找

*具体应用中大家注意各个参数的意义！调整参数来满足个人需要。

第三篇：EXCEL中如何使用VLOOKUP函数查找引用其他工作表数据和自动填充数据

EXCEL中如何使用VLOOKUP函数查找引用其他工作表数据和自动填充数据

如何在EXCEL中对比两张表（不是对比两列）？

两张都是人员在职信息表，A表长，B表短，A表中的记录比较多，有的人A表中有而B表中没有，有的人AB两表都有但是在A表中的行数比B表中多（举例说明，就是这个人在A表中可能有三行，分别是7.8.9三月的在职信息，同样的人在B表中可能只有7月一个月的在职信息），如何把A表中有而B表中没有的行挑选出来单列成一张表？

假设姓名在A列,在职月份在B列,两个表的第一行都是表头.在B表插入一个新A列,这样B表的姓名就在B列，月份在C列,在A2单元格输入 =B2&C2

在A表表头的最后一个空白列(假设为H1)写上“与B表的关系”

在H2输入公式 =IF(ISERROR(VLOOKUP(A2&B2,Sheet2!A:A,1,FALSE)),“B表没有此记录”,“B表有此记录”)如何在EXCEL中筛选出相同的名字？

我现在有2张表：一张有1000个用户，另一张有800个用户；如何快速的找出两张表中相同的名字啊。方法

一、sheet!b1輸入 =IF(COUNTIF(Sheet2!$A$1:$A$1000,A1)>=1,“重複”,“")

方法

二、在1000个用户的sheet1!B1輸入（假设你的记录在A1而且是竖列扩展）

=if(isna(vlookup(a1, sheet2$a$1:$a$800,2,0)), ” “, ”重复“)

两列数据查找相同值对应的位置

=MATCH(B1,A:A,0)

EXCEL中如何使用VLOOKUP函数查找引用其他工作表数据和自动填充数据

VLOOKUP函数，在表格或数值数组（数据表）的首列查找指定的数值（查找值），并由此返回表格或数组当前行中指定列（列序号）处的数值。

VLOOKUP(查找值，数据表，列序号，[匹配条件]）

例如在SHEET2表中有全部100个学生的资料，B列为学号、C列为姓名、D列为班级，现在在SHEET1表的A列有学号，我们需要使用该函数，将SHEET2表中对应学号的姓名引用到SHEET1表的B列。我们只需在SHEET1的B2输入以下公式 =VLOOKUP(A2,SHEET2!$B:$D,2,FALSE)（或者=VLOOKUP(A2,SHEET2!$B$2:$D$101,2,0)，就得到了A2单元格学号对应的学生姓名。同理, 在SHEET1表的C2输入公式 =VLOOKUP(A2,SHEET2!$B:$D,3,FALSE),即可得到对应的班级.VLOOKUP(A2,SHEET2!$B:$D,2,FALSE)四个参数解释

1、“A2”是查找值，就是要查找A2单元格的某个学号。

2、“SHEET2!$B:$D”是数据表，就是要在其中查找学号的表格，这个区域的首列必须是学号。

3、“2”表示我们最后的结果是要“SHEET2!$B:$D”中的第“2”列数据，从B列开始算第2列。

4、“FALSE”（可以用0代替FALSE）是匹配条件，表示要精确查找，如果是TRUE表示模糊查找。如果我们需要在输入A列学号以后,B列与C列自动填充对应的姓名与班级,那么只需要在B列,C列预先输入公式就可以了。为了避免在A列学号输入之前,B列与C列出现“#N/A”这样错误值,可以增加一个IF函数判断A列是否为空,非空则进行VLOOKUP查找.这样B2与C2的公式分别调整为

B2=IF(A2=“",”“,VLOOKUP(A2,SHEET2!$B:$D,2,0))

Model=IF(G311=”“,”“,VLOOKUP(G311,数据表!$B:$I,4,FALSE))

C2=IF(A2=”“,”“,VLOOKUP(A2,SHEET2!$B:$D,3,0))

Excel课表生成中应用的两种方法

课表是学校最基本的教学管理依据，课表形成的传统方法是先安排好原始数据，再设计好表格的固定格式，一项项往表里填内容。上百张课表的形成都要人工录入或人工粘贴复制，既繁琐又容易出差错，而且不利于检索查询。笔者介绍一种方法，在原始数据录入后利用“数据透视表”，可以实现课表生成的自动化。

一、功能

1.一张“数据透视表”仅靠鼠标移动字段位置，即可变换出各种类型的课表，例如：班级课表。每班一张一周课程表。可选框内选择不同的学院和班号，即可得到不同班的课表。按教师索引。即每位教师一周所有的信息。按时间索引，即每天每节课有哪些教师来、上什么课。按课程索引。课程带头人可能只关心和自己有关的内容。按学院索引。可能只需要两三项数据，了解概况。按本专科索引。按楼层索引。专家组听课时顺序走过每个教室，需要随时随地查看信息。按教室或机房索引。安排房间时要随时查看。

2.字段数量的选择是任意的，即表格内容可多可少，随时调整。

3.任何类型的表都能够实现连续打印或分页打印。如班级课表可以连续显示，也可快速、自动生成每班一张；某部门所有教师的课表可以汇总在一张表上，也可每个老师一页纸，分别打印。

4.遇到调课，只要更改原始表，再重新透视一次，可在瞬间完成，就意味着所有表的数据都已更新。而传统的方法必须分别去改班级表、教室表、机房表、教师表……稍有疏忽就可能遗漏。

5.所有的表都不用设计格式，能够自动形成表格，自动调整表格大小，自动合并相同数据单元格。

二、建立数据库

规范数据库的建立是满足查询、检索、统计功能的基本要求。

1.基本字段：班级、星期、节次、课程、地点、教师。

2.可选字段：学院、班级人数、学生类别、金工实习周次、教师单位、地点属性、备注字段名横向排列形成了“表头”，每个字段名下是纵向排列的数据。

3.库中的数据必须规范。如“地点”中不能出现除楼号、房间号以外的任何文字(包括空格)；“课程”中必须是规范的课程名，不允许有“单、双”等字样。建议上机课增加一个字段“上机”，而不是在课程名中增添“上机”说明，后者不利于课程检索。

4.库中的每条数据清单的每个格只要存在数据就必须填满。不允许因为与上一行数据相同就省略了，更不能合并单元格。

5.增加的整条记录在库中的位置可以任意。如规律课表的课程只有8节，某班增加“9～10节”或双休日上课，新增记录则可插在该班其他课的末尾，也可附在库的最底端。无论在什么位置，都不影响透视后的效果。

三、做数据透视表

在选择透视范围时要包含全部原始数据库，如果录制“宏”，最好比原始表多增加若干行，以备增加记录用。但字段的数量可根据需要选择。把选中的字段分别放置在表的“行字段”中，在每个字段名上双击，弹出“字段设置”框，选择“无”，即形成了显示美观的透视表。

1.用鼠标拖动各字段，重新安排左右顺序、上下位置(指行字段与页字段之间的转换)，或在可选框内选中所需，即可形成各种各样的新表。

2.常用的班级课表可排好纸张版面、页眉页脚，专门供原始打印。“班级”字段最好放在“页字段”中，以便于每班打印1张。在“班级”字段的可选框内选择各班，即可显示出所有的班级课表。每班课表的大小是自动调整的，如 “节次”中的数据项只有8节，遇到增添“9～10节”课程的情况，表格会在7～8节后自动增加1行，把9～10节的内容填进去，下一个班则可自动恢复正常。既可以设置为无课显示空格，也可以设成无课不显示，即有哪节显示哪节。

Excel 2003查找重复姓名方法两则

每次统计年级学生基本情况时都会因为学生姓名相同而导致张冠李戴的错误。以往为避免类似错误都要将Excel表格按姓名进行排序，然后依次检查是否重名，非常麻烦还容易出问题。如果您也遇到过类似情况，那么在Excel中，我们可以采用以下的方法来区分那些有重复的姓名，以避免出错。

一、利用条件格式进行彩色填充

选中图1所示表格中数据所在单元格区域A2:I11，点击功能区“开始”选项卡“样式”功能组中的“条件格式”按钮，在弹出的菜单中点击“新建规则”命令，打开“新建格式规则”对话框，在“选择规则类型”列表中点击“使用公式确定要设置格式的单元格”，然后在“为符合此公式的值设置格式”下方的输入框中输入如下公式“=COUNTIF($B$2:$B$11,$B2)>=2”，然后点击下方的“格式”按钮，在打开的“设置单元格格式”对话框的“填充”选项卡中指定一种填充颜色，确定后如图2所示。

确定后关闭此对话框，则可以将重名同学所在行的全部数据都填充此颜色，如图3所示。有了此醒目的标志，那么我们在以后的操作中就不太容易出错了。

查找数据公式两个(基本查找函数为VLOOKUP,MATCH)

（1）、根据符合行列两个条件查找对应结果

=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE)

（2）、根据符合两列数据查找对应结果(为数组公式)

=INDEX(C1：C7，MATCH(H1&I1,A1:A7&B1:B7,0)

使用 INDEX 函数和 MATCH 函数查找数据

假设您在单元格 A1:C5 中创建了以下信息表，且此表包含单元格 C1:C5 中的年龄(Age)信息：

假设您希望根据某人的姓名(Name)查找此人的年龄(Age)。为此，请按如下公式示例，配合使用 INDEX 函

数和 MATCH 函数：

=INDEX($A$1:$C$5, MATCH(”Mary“,$A$1:$A$5,),3)

此公式示例使用单元格 A1:C5 作为信息表，并在第三列中查找 Mary 的年龄(Age)。公式返回 22 一些Excel公式的实用运用例子 =COUNTIF(D2:D10,”>400“)

统计D2:D10的值大于400的个数

=COUNTIF(B2:B10,”东北部“)

统计B2:B10的内容为”东北部“的个数

=TODAY()

显示当前系统日期

=NOW()

显示当前系统日期和具体时间

=YEAR(B2)

获得B2单元格内(当前系统日期和具体时间)的年

=MONTH(B2)

获得B2单元格内(当前系统日期和具体时间)的月

=DAY(B2)

获得B2单元格内(当前系统日期和具体时间)的日

=HOUR(B2)

获得B2单元格内(当前系统日期和具体时间)的时

=RANK(D2,$D$2:$D$10)

取D2的值在D2-D10范围内的排名是多少

=MATCH(99,C2:C10,0)

统计出C2-C10范围内值为99的个数

=EXACT(A4,B4)

比较A4,B4两个单元格内的字符串内容是否相等，返回布尔值TRUE/FALSE

=IF(C2>=60,IF(C2>=90,”优秀“,”及格“),”不及格“)

如果C2>=60(如果C2>=90则显示”优秀“否则显示”及格“)否则显示”不及格“

=IF(AND(B2>=60,C2>=60),IF(OR(B2>=90,C2>=90),”优秀“,”及格“),”不及格“)

与上例相似，只不过是2个单元格都要进行条件判断

=VLOOKUP(B3,D2:G14,4,0)

VLOOKUP（需在第一列中查找的数值,需要在其中查找数据的数据表,需返回某列值的列号,逻辑值True或False）

经常用Excel建立一些表格，有时我们需要给一些表格建立很多个副表，那么如何使这些复制表格中的数据随原表的修改而修改呢？VLOOKUP函数可以帮我们做到这一点

=HLOOKUP(B7,B1:F3,2,0)

HLOOKUP与VLOOKUP

HLOOKUP用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。VLOOKUP用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数 HLOOKUP。

当比较值位于要进行数据查找的左边一列时，请使用函数 VLOOKUP。

语法形式为：

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

其中，Lookup_value表示要查找的值，它必须位于自定义查找区域的最左列。Lookup_value 可以为数值、引用或文字串。

Table_array查找的区域，用于查找数据的区域，上面的查找值必须位于这个区域的最左列。可以使用对区域或区域名称的引用。

Row_index_num为 table_array 中待返回的匹配值的行序号。Row_index_num 为 1 时，返回 table_array 第一行的数值，row_index_num 为 2 时，返回 table_array 第二行的数值，以此类推。

Col_index_num为相对列号。最左列为1，其右边一列为2，依此类推.Range_lookup为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配。

检查单元格 A2 是否为空白(FALSE)=ISBLANK(A2)

检查 #REF!是否为错误值(TRUE)=ISERROR(A4)

检查 #REF!是否为错误值 #N/A(FALSE)=ISNA(A4)

检查 #N/A 是否为错误值 #N/A(TRUE)=ISNA(A6)

检查 #N/A 是否为错误值(FALSE)=ISERR(A6)

检查 10.72 是否为数值(TRUE)=ISNUMBER(A5)

检查 COUNTRY 是否为文本(TRUE)=ISTEXT(A3)

检查 5 是否为偶数ISEVEN(5)FALSE

检查-1 是否为奇数ISODD(-1)TRUE

2.如何去掉execl单元格中文字前面的数字?

自己写个函数放在模块里，然后在单元格调用函数=delnum(A1)

Public Function delnum(zifu As String)As String

Dim l As Integer, m As Integer, n As String, a As String

l = Len(zifu)

For m = 1 To l

n = Mid(zifu, m, 1)

If Asc(n)< 48 Or Asc(n)> 57 Then

a = a & n

End If

Next m

delnum = a

End Function

3.excel中,列很多,行很少,怎么能让打印在一页上?

使用公式先进行一下转换就是了。

以下为示例：

源数据为数据区域A1:O2，即一个2行15列的数据，如下：

ABCDEFGHIJKLMNO

***131415

先使用公式转变为6行5列的数据，公式如下：

[假设我们在A6单元格开始输入公式，转变后的数据区域为A6:E11]

在单元格A6输入以下公式：

=INDIRECT(ADDRESS(IF(MOD(ROW(),2)=0,1,2),IF(MOD(COLUMN(),5)=0,5,MOD(COLUMN(),5))+INT((ROW()-6)/2)*5))

并将该公式复制到数据区域A6:E11，我们可以看到，现在数据已经进行了转换。

结果为：

ABCDE

12345

FGHIJ

678910

KLMNO

1112131415

公式说明：

1.由于假定从单元格A6开始，因此IF(MOD(ROW(),2)=0,1,2)的结果为若为偶数行则指向第一行，否则指向第二行。

2.MOD(COLUMN(),5)由于示例中指定了为5列。

3.INT((ROW()-6)/2)*5)，示例中是从A6单元格开始的，因此减6行，5为列数。

附加：如果不是正好满列数，那么应该进行一次判断，如下：

=If(Indirect(...)=”“,”“,Indirect(...))

[Indirect(...)即上面示例中的公式]

5.excel里A列为身份证号码，要求在B列得出其出身日期？

A列为个人的身份证号或企业代码，身份证包括2类：15位的身份证，18位身份证。15位（***）的身份证的生日为1956-10-20；18位（***053）的身份证生日为1974-03-15。企业代码不满足15位或18位。

现在要求在B列得到A列身份证号人的出生日期；若是企业代码的不需要。

=if(len(A1)=15,”19“ & mid(A1,7,2)& ”-“ & mid(A1,9,2)& ”-“ & mid(A1,11,2),mid(A1,7,4)& ”-“ & mid(A1,11,2)& ”-“ & mid(A1,13,2))

为15位时,应该没2000年后出生的吧所以,以上应该行得通,试试看

当A列是企业代码时,公式有问题.如:A1=2***10,得到的是3123-32-10

公式上做了点修改.=IF(OR(LEN(A1)={15,18}),IF(LEN(A1)=15,”19“ & MID(A1,7,2)& ”-“ & MID(A1,9,2)& ”-“ & MID(A1,11,2),MID(A1,7,4)& ”-“ & MID(A1,11,2)& ”-“ & MID(A1,13,2)),”“)

=IF(LEN(A1)=15,”19“ & MID(A1,7,2)& ”-“ & MID(A1,9,2)& ”-“ & MID(A1,11,2),IF(LEN(A1)=18,MID(A1,7,4)& ”-“ & MID(A1,11,2)& ”-" & MID(A1,13,2),A1))

当A列是企业代码时,返回原企业代码

第四篇：EXCEL中如何使用VLOOKUP函数查找引用其他工作表数据和自动填充数据

EXCEL中如何使用VLOOKUP函数查找引用其他工作表数据和自动填

充数据

2010-06-15 10:01:38|分类： 标签： |字号大中小 订阅

VLOOKUP函数，在表格或数值数组（数据表）的首列查找指定的数值（查找值），并由此返回表格或数组当前行中指定列（列序号）处的数值。

VLOOKUP(查找值，数据表，列序号，[匹配条件]）

例如在SHEET2表中有全部100个学生的资料，B列为学号、C列为姓名、D列为班级，现在在SHEET1表的A列有学号，我们需要使用该函数，将SHEET2表中对应学号的姓名引用到SHEET1表的B列。我们只需在SHEET1的B2输入以下公式 =VLOOKUP(A2,SHEET2!$B:$D,2,FALSE)（或者=VLOOKUP(A2,SHEET2!$B$2:$D$101,2,0)，就得到了A2单元格学号对应的学生姓名。同理, 在SHEET1表的C2输入公式 =VLOOKUP(A2,SHEET2!$B:$D,3,FALSE),即可得到对应的班级.VLOOKUP(A2,SHEET2!$B:$D,2,FALSE)四个参数解释

1、“A2”是查找值，就是要查找A2单元格的某个学号。

2、“SHEET2!$B:$D”是数据表，就是要在其中查找学号的表格，这个区域的首列必须是学号。

3、“2”表示我们最后的结果是要“SHEET2!$B:$D”中的第“2”列数据，从B列开始算第2列。

4、“FALSE”（可以用0代替FALSE）是匹配条件，表示要精确查找，如果是TRUE表示模糊

查找。

如果我们需要在输入A列学号以后,B列与C列自动填充对应的姓名与班级,那么只需要在B列,C列预先输入公式就可以了。为了避免在A列学号输入之前,B列与C列出现“#N/A”这样错误值,可以增加一

个IF函数判断A列是否为空,非空则进行VLOOKUP查找.这样B2与C2的公式分别调整为

B2=IF(A2=“",”“,VLOOKUP(A2,SHEET2!$B:$D,2,0))

C2=IF(A2=”“,”",VLOOKUP(A2,SHEET2!$B:$D,3,0))

点击下面标签查看演示动画:

利用VLOOKUP函数查找引用其他工作表数据

利用VLOOKUP函数自动填充数据

第五篇：COUNTIF函数的用法及实例

Excel统计函数COUNTIF的用法及实例

一：首先介绍COUNTIF函数的用法

英文通用格式是：COUNTIF（range,criteria)

中文通用格式是：COUNTIF（数据区域,条件表达式)=COUNTIF（A$2：A$55，A2）(注释：A2单元格在A2：A55区域中出现的次数（0：没有；1：出现1次；2：出现2次。）)

1、返加包含值12的单元格数量

=COUNTIF（range,12）

2、返回包含负值的单元格数量

20-屯昌电视台MSTP（总表处理后重复303条

=COUNTIF（range，“<0”）

3、返回不等于0的单元格数量

=COUNTIF（range，“<>0”）

4、返回大于5的单元格数量

=COUNTIF（range，“>5”）

5、返回等于单元格A1中内容的单元格数量

=COUNTIF（range，A1）

6、返回大于单元格A1中内容的单元格数量

=COUNTIF（range，“>”&A1）

7、返回包含文本内容的单元格数量

=COUNTIF（range，“*”）

8、返回包含三个字符内容的单元格数量

=COUNITF（range，“???”）

9、返回包含单词“GOOD”(不分大小写)内容的单元格数量

=COUNTIF（range，“GOOD”）

10、返回在文本中任何位置包含单词“GOOD”字符内容的单元格数量

=COUNTIF（range，“*GOOD*”）

11、返回包含以单词“AB”(不分大小写)开头内容的单元格数量

=COUNTIF（range，“AB*”）

12、返回包含当前日期的单元格数量

=COUNTIF（range，TODAY()）

13、返回大于平均值的单元格数量

=COUNTIF（range，“>”&AVERAGE(DATA))

14、返回平均值上面超过三个标准误差的值的单元格数量

=COUNTIF（range，“>"&AVERAGE(DATA)+STDEV(DATA)*3)

15、返回包含值为或-3的单元格数量

=COUNTIF（range，3）+COUNIF（DATA，-3）

16、返回包含值;逻辑值为TRUE的单元格数量

=COUNTIF（range，TRUE）

二：COUNTIF函数应用举例

假设存在如下图的学生成绩表，现在，让我们一起来使用COUNTIF函数进行统计，下面统计的是不及格的人数！

如下图，首先，选择F列中的F3单元格，之后，在其上方fx函数输入区域，直接输入=COUNTIF(E3:E55,“<60“)，之后按回车键确认，这样，就得到不及格的人数了！

=COUNTIF(E3:E55,“<60“)分析：

E3:E55代表统计的数据区域是E3到E55，“<60“统计的条件是成绩小于60；

知识扩展：

“<60“改为：“<80“是什么意思呢？

表示的是，统计分数小于80的人数；

“<60“改为“>60“是什么意思呢？

表示的是：统计及格的人数！

英文通用格式是：COUNTIF（range,criteria)

中文通用格式是：COUNTIF（数据区域,条件表达式)上述格式中，数据区域和条件表达式，可根据需要自行构造，灵活性非常大；