办公软件:什么样整合Excel表格,你会两种

办公软件 79
办公软件

问题:有两份Excel表格,A表上记载着许多客户的姓名和地址,B表格上记载着众客户的姓名和电话。请问如何把A表上的姓名与B表上的电话,一一对应的整合在一起?

VLOOKUP函数是工作中最常用的一种查找函数,掌握好VLOOKUP函数能够极大提高工作的效率。

关于VLOOKUP函数的用法

回答:这个可以用VLOOKUP函数解决,这个函数是EXCEL在职场应用中的一个神器,一定要掌握这个函数。

VLOOKUP函数用于首列查找并返回指定列的值,字母“V”表示垂直方向。

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

假定如下两个表,一个表中存储的是姓名和地址,一个表中存储的是姓名和电话。

VLOOKUP函数的语法如下:

一、功能 
在表格的首列查找指定的数据,并返回指定的数据所在行中的指定列处的数据。

办公软件 1

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

二、语法 
标准格式: 
VLOOKUP(lookup_value,table_array,col_index_num , range_lookup)

在存储姓名和地址的工作表C2单元格输入公式:

其中,第1参数lookup_value为要搜索的值,第2参数table_array为首列可能包含查找值的单元格区域或数组,第3参数col_index_num为需要从table_array中返回的匹配值的列号,第4参数range_lookup用于指定精确匹配或近似匹配模式。

解释:

=VLOOKUP(A2,姓名电话!$A:$B,2,0)

当range_lookup为TRUE、被省略或使用非零数值时,表示近似匹配模式,要求table_array第一列中的值必须按升序排列,并返回小于等于lookup_value的最大值对应列的数据。当参数为FALSE时(常用数字0或保留参数前的逗号代替),表示只查找精确匹配值,返回table_array的第一列中第一个找到的值,精确匹配模式不必对table_array第一列中的值进行排序。

VLOOKUP(‘你要检索的内容或指定单元格’,‘你要检索的范围,检索到内容时返回你检索表的第几列中的内容’,
‘真或假参数真代表查询的表已经排序,假代表没有排序’)

鼠标放在右下角变成黑色十字后双击向下填充公式,可得出结果。

如果使用精确匹配模式且第1参数为文本,则可以在第1参数中使用通配符问号(?)和星号(*)。VLOOKUP函数不区分字母大小写。

例:VLOOKUP(A2,Sheet2!$A1:$B10,2,FALSE)

办公软件 2

案例一


明:在表SHEET2中检索当前表中A2中的内容,如果检索到,就返回表SHEET2中B2中的内容,因为B2是表SHEET2中的第二列,所以
VLOOKUP的第三个参数,使用2,表示如果满足条件,就返回查询表的第二列,最后的参数FALSE表示‘假’,意思是被查询的表,没有排序,这种情况
下,会从被查询的表中第一行开始,一直查询到结束。

回答:

A3:B7单元格区域为字母等级查询表,表示60分以下为E级、60~69分为D级、70~79分为C级、80~89分为B级、90分以上为A级。D:G列为初二年级1班语文测验成绩表,如何根据语文成绩返回其字母等级?

三、语法解释 
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。

刚看了前面几个答案,除了说用vlookup的方法基本答对之外,人气最高的答案貌似不是提问者所需要的,因为这个答案是指多个工作表的汇总,而不是提问者所需要的横向的合并。

在H3:H13单元格区域中输入=VLOOKUP(G3,$A$3:$B$7,2)

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

如前面的答案,vlookup是方法之一,但是,用vlookup有一个问题,就是只能从一个表读取数据到另一个表,而不能实现两个表的完全整合,比如如果A表上有姓名和地址而B表上没有数据的,从B表读取A表的数据就读不出来,相反也是一样。

办公软件 3

A列已排序(第四个参数缺省或用TRUE) 
VLOOKUP(2005001,A1:D7,2,TRUE) 等于“周杰伦” 
VLOOKUP(2005001,A1:D7,3,TRUE) 等于“2870” 
VLOOKUP(2005001,A1:D7,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) 等于“工 会”

因此,个人认为最好的办法是用Excel2016的新功能Power
Query(如果是Excel2010或2013可以到微软官方下载插件)。具体实现方法如下:

案例二

若A列没有排序,要得出正确的结果,第四个参数必须用FALAE 
VLOOKUP(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) 等于“工 会”

vlookup虽好,然难承大数据之重

原创 大海 Excel到PowerBI

小勤:大海,现在公司的数据量越来越大,现在有订单表和订单明细表,经常要将订单表的一些信息读取到订单明细表里,给相关的部门去用,原来只要几列数还好,vlookup读一下就是了,但现在,经常要很多数,用vlookup要累屎了。这个订单表还算少的,还很多其他的一张表里都好几十列了。

办公软件 4

办公软件 5

大海:呵呵,大数据时代嘛。几十列算少的啦,我上次一个项目上的合同表,有差不多300列,而且这还不算真正多的。

小勤:那怎么办啊!有时候按列顺序读还好,很多时候还不是按顺序的,简直就没法整啊。而且,满篇公式的时候,工作表都要跑不起来了。

大海:这个时候用vlookup的确有点吃力了,虽然vlookup是Excel中极其重要的函数,但是,在大数据时代,已经很难承起数据关联合并的重担了,所以微软才在Excel里加了PowerQuery的功能嘛,点点点,你想要哪些列就哪些列。

小勤:这么神奇?

大海:这段时间PowerQuery的神奇你也不少见了,不用惊讶。现在就告诉你怎么弄。

Step-1:获取订单表数据并仅创建表连接上载

办公软件 6

Step-2:获取订单明细表数据并仅创建表连接上载(需要直接上传结果的可以选择表)

办公软件 7

Step-3:回到PowerQuery界面(当然,前面一个步骤如果没有关闭并上载的话,不需要这一步)

办公软件 8

Step-4:选择要接入外部数据的查询,单击-

办公软件 9

Step-5:选择要接入的外部表、选择两表之间用于匹配的列(可以是多列匹配,文末以另一个例子该步骤的附图方式说明)

办公软件 10

Step-6:展开要接入表的列信息,选择要接入的列以及列名显示方式(是否加前缀)

办公软件 11

Step-7:查看接入的结果,上载数据

办公软件 12

Step-8:改变数据的加载方式(由“仅创建连接”方式改为“表”,若前面订单明细不是以“仅创建连接”的方式创建,该步骤不需要)

办公软件 13

小勤:这样真是太方便了,只要选一下匹配要用的列,选择一下要接入哪些列就搞定了!对了,刚才你不是说可以多列匹配吗?原来用vlookup的时候可麻烦了,还得增加辅助列先将那些列连接起来,然后再用辅助列来匹配。

大海:是的。在PowerQuery里也不需要了,只要在选择匹配列时按住ctrl键就可以选择多列了。只是要注意两个表选择匹配列的顺序要一致。如下图所示:

办公软件 14

小勤:太好了,以后数据列多的时候匹配取数就太简单了。

以上是使用Power
Query代替vlookup实现的两表合并的基本用法(虽然步骤看起来很多,实际关键步骤就2个,都是鼠标点点点就瞬间完成的事情)。

那么,前面提到的,如果两个表间存在的差异数据都要显示,怎么办呢?只要对其中的表间连接类型按以下情况进行适当的选择即可:

  • 左外部:只要订单表(左表)里有的数据,结果表里都会有,但有些因为明细表(右表)里没有,所以匹配过来后会成为null(空值)

    办公软件 15

  • 右外部:和左外部相反,即明细表(右表)里有的数据,结果表里都会有,但因为订单表(左表)里有部分数据没有,所以合并后用null值表示。

    办公软件 16

  • 完全外部:不管哪个表里的数据,全都进结果表,对于双方都有一些对方没有的,合并后显示为null值。

办公软件 17

  • 内部:跟完全外部相反,只有两个表都有的数据,才进结果表。

办公软件 18

  • 左反:只有订单表(左表)有而明细表(右表)没有的数据,才进结果表。这种用法经常用于检查如哪些订单缺了明细表等。

    办公软件 19

  • 右反:和左反相反,只有明细表(右表)有而订单表(左表)没有的数据,才进结果表。

办公软件 20

欢迎关注

我是大海,微软认证Excel专家,企业签约Power BI顾问

让我们一起学习,共同进步!

回答:月末了,各部门报过来的数据,如何合并到一个文件里?

办公软件 21

过去,我们只能使用VBA或编写SQL语句。

现在,我们只需点击几次鼠标,书写一个公式。

办公软件 22

6个工作簿,数据结构都是一致的,我们需要把她们合并到一个工作簿里。

办公软件 23

,找到需要合并的文件夹。

办公软件 24

文件夹下每一个工作簿都被合并在一起。首列“内容”显示,是二进制数据的意思。

最后一列显示这些工作簿的地址。中间几列分别表示工作簿名称、后缀名、访问时间、修改时间、创建时间和文件属性。

办公软件 25

点击,进入,中间那几列无用,所以右键单击。

办公软件 26

如果此时直接点击二进制首列的”展开按钮”,会出现错误提示。

办公软件 27

这是因为,二进制数据无法直接提取。我们需要书写一条公式。

办公软件 28

在点击。

办公软件 29

在对话框,保留默认的,在列表框录入公式:

=Exel.Workbook([Content],true)

办公软件 30

注意,公式函数严格区分大小写(首字母大写)。

函数的第一个参数是需要转换的二进制字段,这个字段可以在右侧列表框双击选择,不必手工录入。

函数的第二个参数是逻辑值,如果原数据有标题行,这里应该添写true。

点击后,新增一列,数据类型显示为,右侧的列表显示了刚刚进行的步骤。

办公软件 31

随便选择数据的一个单元格,下方预览区会显示这个表的结构。

点击新增列标签右侧“展开按钮”,选择。

办公软件 32

每一个表会按列方向展开。其中Data数据类型仍然显示。

办公软件 33

我们再次点击数据列标签右侧的“展开按钮”。

办公软件 34

展开的数据已经将文件夹下所有工作簿合并在一起。

删除一些不需要的列。

办公软件 35

只留有效数据列,点击返回Excel。

办公软件 36

所有数据都已经合并到一个工作簿中。

办公软件 37

得到的合并数据实际上是一个,右键单击可以数据。

当文件夹下原工作簿内容变更,合并工作簿只要一次,即可更新数据。

展开数据时,如果选择,得到的数据会将同类项求和或计数。

怎么样,是不是比VBA要简单的多啊。

更多财税职场学习资讯,关注秀财网

回答:对于这个问题的回答,都是仁者见仁智者见智的事情。看到题主的需求,我的第一反应就是使用vlookup、index、lookup等函数。然而哪种更为简单呢,这个要根据实际情况而定。在某些情况下,我们甚至一个函数都不用也能快速地将表格整合在一起,比如使用复制粘贴或者Power
Query。

在Sheet1里面如何查找折旧明细表中对应编号下的月折旧额?(跨表查询)

五、关于TRUE和FALSE的应用 
先举个例子,假如让你在数万条记录的表格中查找给定编号的某个人,假如编号已按由小到大的顺序排序,你会很轻松地找到这个人;假如编号没有排序,你只好从上到下一条一条地查找,很费事。 
用VLOOKUP查找数据也是这样,当第一列已排序,第四个参数用TRUE(或确省),Excel会很轻松地找到数据,效率较高。当第一列没有排序,第四
个参数用FALSE,Excel会从上到下一条一条地查找,效率较低。

一、利用函数法快速解决表格整合

如下图所示,如何快速将A表的内容快速地整合到B表中去呢?办公软件 38

可以说方法非常多,常见的有函数法(vlookup函数、lookup函数、index函数等)请看下面的公式:

Vlookup函数法:

=VLOOKUP($G3,$A$3:$C$14,MATCH(I$2,$A$2:$C$2,),)
或者
=VLOOKUP($G3,$A$3:$C$14,Column(B1),)

以上函数的难点在单元格的引用,巧妙之处在于利用match函数或者Column函数作为vlookup函数的第3参数,使其变得非常灵活。

Lookup函数:

=LOOKUP($G3,$A$3:B14)

此公式的难点依然在于单元格区域的引用。办公软件 39

Index函数:

=INDEX($A$3:$C$14,MATCH($G3,$A$3:$A$14,),MATCH(I$2,$A$2:$C$2,))

此函数为经典的Index+match函数嵌套,难点在于引用。要写这个公式,我们不仅要掌握Index函数的用法,而且还必须熟谙match函数的技巧。

因此我认为上面的这三个函数都不是最简单的解决此问题的技巧。

在Sheet1里面的C2:C4单元格输入=VLOOKUP(A2,折旧明细表!A$2:$G$12,7,0)

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

二、最简单的复制粘贴表格整合法

其实,我们解决此问题,完全不用写任何公式,简单的排序+复制粘贴即可解决问题。办公软件 40

技巧:

1.选中A3:A14区域——按下快捷键Alt+F+T打开Excel选项对话框——单击高级——向下拖动最右侧的滚动条至底部——单击“编辑自定义列表”——在弹出的对话框中单击导入——确定——确定。

2.选中B表第一列中的任意单元格,按下快捷键Alt+H+S+U打开排序对话框,主要关键字选择学号,次序选择自定义,在打开的对话框中下拉到底部,找到第1步导入的序列,单击确定,再次单击确定。这样A、B两表的顺序都一样了。

3.复制A表中的B3:C14区域到B表中的I3:J14即可。

更多精彩内容,敬请关注我的头条号:傲看今朝。对于本篇回答有任何疑问之处,欢迎大家在评论区留言,我会抽时间给大家解答问题。

回答:这个问题比较简单,用函数就可以搞定,而且是Excel函数入门。

这里介绍2个函数抛砖引玉。

  • vlookup

办公软件 41

  • index+match组合

办公软件 42

这两个函数都可以跨表查询。

除了使用函数,透视表也可以解决该问题。

具体可以关注我的技巧文章,谢谢。

回答:这里提供两种方法来实现,想要一步到位的请使用vlookup公式,讨厌公式的请使用排序法。

首先不论是哪种方法,“姓名”是两张表共同的关键词,请先分别给两个表格按照“姓名”进行“升序”排列。办公软件 43

办公软件 44

=========================

vlookup公式法

  • 输入公式

如下图,先给C2单元格输入公式“=VLOOKUP(A2, $A$11:$B$16, 2,
0)”,然后再向下填充,将公式填充到其他单元格。办公软件 45

  • 公式解释

第一个参数(A2):表示要查找的内容。我们想在第二张表格中查找“李力”的电话,因此第一个参数自然就是A2了。

第二个参数($A$11:$B$16):表示查找的范围。我们要在第二张表格中先找到“李力”,然后再找到他的电话。因此,这个参数就是第二张表中所有包含姓名和电话的单元格。

第三个参数(2):表示找到匹配项后要得到第几列的结果。这里我们要的是电话,所以是第2列。

第四个参数(0):表示要精确匹配,也就是必须找到姓名一模一样的单元格。

  • 公式注意事项
  1. 使用前一定要给两张表格按照升序进行排序。

2.
由于查找的范围是固定的,第二个参数一定要加上$号(按F4键可以快速添加$),有$表示绝对引用,也就是向下填充公式时内容不会变。

办公软件 46

Lookup和Vlookup有哪些区别?2009-11-17
14:18Excel查询函数中,Lookup和Vlookup有哪些区别?它们在应用中应该如何把握?请看本文讲解。

排序法

  • 思路

首先将两张表合并成一张表。接着按照姓名进行排序,这样就会看到上下两行都是同一个人的信息。然后使用条件格式下的重复项功能标记出重复的姓名,将重复项整行复制出来。最后,给重复项的奇数行自动填充序列,偶数行保留空白,再按照序列排序就提取出了所有的电话啦。看起来似乎很复杂,其实操作起来是很快的。

  • 操作步骤

1.
如图,将两张表合并成一张,其中的关键字“姓名”放在同一列下,选中整张表,点击“数据”——“排序”,按照“姓名”列升序排列。办公软件 47
2.
选中所有数据,点击“开始”——“条件格式”——“新建规则”,如图,选择为重复值设置格式,点击“格式”按钮,设置好填充颜色。办公软件 483.
条件格式标记出了所有有地址和电话的人。点击筛选按钮,选择按照颜色筛选,将这些有颜色的行全部复制出来。在F1和F3输入1和2,选中前面四个单元格,如图。办公软件 494.
向下填充,使得所有的奇数行都填充上了序列,偶数行空白。办公软件 505.
选中整张表格,按照F列升序排列,这样奇偶数行就分开了,然后复制电话到G列,删除辅助列F列即可。办公软件 51

如果不想改变原有表格的顺序,可以在操作前添加好编号。操作完毕后,再按照编号排序,就可以恢复最初的顺序了。


谢谢阅读,欢迎点赞和评论,关注或点击头像可以看更多的内容哦!

回答:首先,把A表和B表合成一个文件。

办公软件 52

两表中的内容格式示例如下。

A表

办公软件 53

B表

办公软件 54

我们在A表后面的电话一列”C2″单元格中加入函数命令,与B表中的数据进行比对“=VLOOKUP(A2,B!A:B,2,FALSE)”

办公软件 55

函数命令的解释:

  1. “VLOOKUP”是纵向查找函数;

  2. (
    )中的“A2”代表A表的单元格A2;“B”代表的是B表(也就是表的名称);“!A:B”代表的是在B表的A列到B列之间查找;“2”代表查找列中的第2列;“FALSE”是判断命令;

  3. 整句命令的含义是,在B表中的A列到B列之间查找,是否有A表中A2单元格的数据,如果有,那么就把第2列的数据显示在A表C2单元格中。也就是在B表中两列数据中查找有没有“张三”这个人,如果有,那么在A表的电话一栏显示B表中“张三”的电话,你要先算好电话一列是查找列的第几列。
  4. 输入函数后回车,即可得到结果,如下:

办公软件 56

把鼠标移至C2单元格右下角,鼠标变成黑色十字,按住下拉,即可将函数格式复制到下面的单元格,结果如下图:

办公软件 57

你可以根据表格的实际情况,修改函数中的相关字符来达到最终效果。

回答:第一反应是用VLOOKUP函数来匹配,

不过要注意的是:姓名很容易出现重复的情况

为了避免这种特殊情况,建议先用透视表,查看一下,两个表格哪些姓名有重复

然后给每位客户设置唯一的ID,

再使用ID,作为VLOOKUP函数的索引,来匹配电话号码

这样得出的结果会更加精确

回答:Index+Match函数结合也可完美解决,效果类似于Vlookup,不过后者更便捷。

思路大致如下:首先将两个工作簿放到一个表中(使用Microsoft Query)。

如果有重复项的话,要提前删除重复项。

然后用Index+Match函数或Vlookup函数就可以了。

get√

回答:第一反应,想到的就是引用和匹配函数,首选就是vlookup函数了。

因为A表和B表的共同点就是客户的姓名,通过姓名就可以互相引用对方表格里的地址或者电话了。

详细步骤就不说了,大神们已经给出答案了。

欢迎关注我的头条号,如果有excel方面的问题,可以私信交流,为你答疑解惑。

案例三

  ★Lookup——数与行列比

如何实现通配符查找?

  Lookup的工作职责是什么呢?用一个数与一行或一列数据依次进行比较,发现匹配的数值后,将另一组数据中对应的数值提取出来。

在B2:B7区域中输入公式=VLOOKUP(A2&”*”,折旧明细表!$B$2:$G$12,6,0)

  ·工资税率表:用数值比较

办公软件 58

  根据不同的工资进行不同的税率计算是一个常见的应用。我们来看这张“工资税率查询”表(见图1)。现在要在右侧根据“收入”(F列),直接得到
对应的“税率”(G列)。在计算第1个“税率”时,输入函数公式“=LOOKUP(F4,$B$3:$B$8,$D$3:$D$8)”,回车,便可得到
“36.00%”。

办公软件 59

办公软件 60

案例四

  这个结果是怎么来的?用F4中的第1个收入数“$123,409”,与左侧表的“收入最低”各档数据(“$B$3:$B$8”)进行对比,虽然
“$123,409”在“收入最低”各档数中没有完全一致的数据与之匹配,但是会与其中小于它的最大数“$58,501”相匹配。这样,同一行对应的
“36.00%”就提取出来了。

如何实现模糊查找?

  ·图书销售表:用文本比较

在F1:F9区域中输入公式=VLOOKUP(E2,$A$2:$B$7,2,1)

  Lookup函数的对比数还可以是文本。在这张图书销售查询表中(见图2),用下表输入的“编号”(A15单元格)文本当作查询数,与上表的
“编号”一列($A$3:$A$11)进行对比,查询到了匹配的文本后,将“教材名称”一列($B$3:$B$11)对应的数据提取出来。公式是
“=LOOKUP(A15,$A$3:$A$11,$B$3:$B$11)”。

办公软件 61

办公软件 62

案例五

  ★Vlookup——数与表格比

如何通过数值查找文本数据、通过文本查找数值数据、同时实现数值与文本数据混合查找?

  Lookup有一个大哥——Vlookup函数。两兄弟有很多相似之处,但大哥本领更大。Vlookup用对比数与一个“表”进行对比,而不是Lookup函数的某1列或1行,并且Vlookup可以选择采用精确查询或是模糊查询方式,而Lookup只有模糊查询。

通过数值查找文本数据:在F3:F6区域中输入公式=VLOOKUP(E3&””,$A$2:$C$6,3,0)

  ·模糊匹配

通过文本查找数值数据:在F11:F13区域中输入公式=VLOOKUP(–E11,$A$10:$C$14,3,0)

  用Vlookup函数进行模糊查询时,几乎与Lookup的作用完全一致。我们用Vlookup函数来提取第1个例子中的工资税率结果。函数公式为“=VLOOKUP(F4,$B$3:$D$8,3,TRUE)”。

同时实现数值与文本数据混合查找:在F19:F21区域中输入公式=IF(ISNA(VLOOKUP(E19*1,$A$18:$C$22,3,0)),VLOOKUP(E19&””,$A$18:$C$22,3,0),VLOOKUP(E19*1,$A$18:$C$22,3,0))

  在这个函数中,用第1个收入“$123,409”(F4单元格)当作对比数,用它与左侧表(“$B$3:$D$8”)的第1列数进行对比,虽然
“$123,409”在“收入最低”各档数中没有完全一致的数据与之匹配,但是函数的最后一个参数是“TURE”(“TURE”就是模糊查询),所以它会
与其中小于它的最大数“$58,501”相匹配。并将表中第3列(函数的第3个参数为“3”)对应的数据提取出来,所以结果同样是“36.00%”。

办公软件 63

  ·订单明细表:精确匹配

案例六

  有时候,我们需要精益求精。在下面这个“订单明细表”(见图3)中,最后一列“货运费用”中的数据要通过“交货方式”从左侧“配送公司表”中进行匹配查询。这是一个典型的精确查询的例子,计算第1个数据的函数公式是“=VLOOKUP(H3,$B$2:$D$6,3,FALSE)”。

在Excel中录入数据信息时,为了提高工作效率,用户希望通过输入数据的关键字后,自动显示该记录的其余信息,例如,输入员工工号自动显示该员工的信命,输入物料号就能自动显示该物料的品名、单价等。如图所示为某单位所有员工基本信息的数据源表,在“2010年3月员工请假统计表”工作表中,当在A列输入员工工号时,如何实现对应员工的姓名、身份证号、部门、职务、入职日期等信息的自动录入?

办公软件 64

解决方案1:使用VLOOKUP+MATCH函数

  小提示:

在“2010年3月员工请假统计表”工作表中选择B3:F8单元格区域,输入下列公式,按【Ctrl+Enter】组合键结束。

  把最后一个参数从“TRUE”变更成“FLASE”,就是精确匹配。而精确查询,就是查询数要与查询表第1列中的数据完全一致才能匹配提取,否则结果返回错误值“#N/A”。

=IF($A3=””,””,VLOOKUP($A3,员工基本信息!$A:$H,MATCH(B$2,员工基本信息!$2:$2,0),0))

  点评:

解决方案2:HLOOKUP+MATCH函数。

  Excel为我们提供了近20个有关“查找和引用”的函数,除了最常用的Lookup、Vlookup,还有Choos、Row、Colum、
Index和Match等,大家可以通过函数的帮助查看具体的功能。这些函数往往不是单独使用,可以与其他函数和Excel中的一些功能进行配合。

在“2010年3月员工请假统计表”工作表中选择B3:F8单元格区域,输入下列公式,按【Ctrl+Enter】组合键结束

出处:

=IF($A3=””,””,HLOOKUP(B$2,员工基本信息!$A$2:$H$20,MATCH($A3,员工基本信息!$A$2:$A$20,0),0))

=========================================================================

办公软件 65

今天在百度知道的时候,看到旁边有人问excel中条件查找vlookup的问题,有几位高手都知道使用vlookup作答,可惜都是没有经过测试,直接复制别人的答案,让所有的读者都无法实施,一头雾水。今天我们详细解答一下vlookup函数的实际应用问题:

办公软件 66

问题:如下图,已知表sheet1中的数据如下,如何在数据表二 sheet2
中如下引用:当A列学号随机出现的时候,如何在B列显示其对应的物理成绩?

案例七

办公软件 67

在使用Excel查询和引用数据时,经常需要将文本形式的单元格地址转换成对应应用,。如下图所示为某超市的商品采购清单,其中又两个供货商提供了报价表(如供货商A、供货商B工作表),如何根据品名和供货商自动查询对应的商品单价?

首先我们知道需要用到vlookup函数,那么先介绍一下使用
vlookup函数的几个参数,vlookup是判断引用数据的函数,它总共有四个参数,依次是:

选择D3:D13单元格区域,输入下列公式,按【Ctrl+Enter】组合键结束。

1、判断的条件
2、跟踪数据的区域
3、返回第几列的数据
4、是否精确匹配

=VLOOKUP(B3,INDIRECT(C3&”!a:b”),2,0)

根据以上参考,和上述在sheet2表的B列显示问题的实际需求,在sheet2表的B2单元格输入这个公式是:

办公软件 68

=vlookup(a2,sheet1!$a$2:$f$100,6,true)

详细说明一下,在此vlookup函数例子中各个参数的使用说明:

办公软件 69

1、a2
是判断的条件,也就是说sheet1表和sheet2表中学号相同者,即sheet2表a列对应的数据和sheet1表中学号列a列的数据相同方能引用;
2、sheet1!$a$2:$f$100
是数据跟踪的区域,因为需要引用的数据在f列,所以跟踪的区域至少在f列,sheet1!是不同表间引用所用的表名称,和标志是表间引用的!符号,$是绝对引用(关于绝对引用可以参考这里),$a$2:$f$100
表明从A2到F100单元格的数据区域,如果数据区域不止100,那么可以直接使用A:F,这样虽然方便但是有风险,因为如果sheet1表的下方还有其它数据,就有可能出现问题;
3、6
这是返回什么数的列数,如上图的物理是第6列,所以应该是6,如果要求英语的数值,那么此处应该是5
4、是否绝对引用,如果是就输入 true 如果是近似即可满足条件 那么输入false
(近似值主要用于带小数点的财务、运算等)
5、vlookup是垂直方向的判断,如果是水平方向的判断可使用Hlookup函数

案例八

结果如下图:

用VLOOKUP函数实现反向查找,如下图,如何实现通过工号来查找姓名?

办公软件 70

有三种实现方法:

不知道你是否已经会使用vlookup这个条件查找函数,如果你有兴趣可以试试本例。与本例结合紧密的是excel数据引用,更多的excel可以参考这里。

方法一:在B8单元格输入=VLOOKUP(A8,CHOOSE({1,2},B1:B5,A1:A5),2,0),按ENTER键结束。

原地址  

方法二:在B8单元格输入=VLOOKUP(A8,IF({1,0},B1:B5,A1:A5),2,0),按ENTER键结束。

==============================================================

方法三:在B8单元格输入=INDEX(A1:A5,MATCH(A8,B1:B5,)),按ENTER键结束。

使用HLOOKUP函数进行水平查找

在一些企业,会根据员工的销售奖金是根据业绩而定的,业绩越高奖金比例也就越高。根据这种情况,往往需要对业绩数据根据奖金比例标准经行查询并定位奖金比例,当员工数量较多时,人为查询会比较困难,用HLOOKUP函数水平查找就可以实现。
HLOOKUP函数主要用于在表格或数值组的首行查找指定的数值(即在水平查找),并返回表格显示当前列中指定行处的值。

方法/步骤
1
将两个工作表放在同意工作薄内。
办公软件 71

办公软件 72

2
在业绩表的选择C2,输入“=HLOOKUP(B2,奖金标准!$B$3:$E$4,2)”,按回车。
公式中B2表示要查找的值;
“奖金标准!$B$3:$E$4”表示在“奖金标准”工作表中的B3:E4区域中查找,加$是防止在向下填充公式时照成错误;如果搜索区域是在同一工作表中,直接在公式中输入“$B$3:$E$4”即可;
“2”表示要显示的数据为B3:E4区域中查找的值所在列的第2行的值。
整个公式表示:在“奖金标准”工作表中B3:E4区域的首行查找与B2近似匹配的值所在的列,并显示该列第2行的值。
办公软件 73

3
选择C2单元格,点击并拖动光标向下填充公式。填充后,C2列单元格区域都会根据公式显示对应结果。
办公软件 74

办公软件 75

4
在业绩表的选择D2,输入“=B2*C2”,按回车,计算出销售奖金;
选择D2单元格,点击并拖动光标向下填充公式。
办公软件 76

注意事项
使用HLOOKUP函数时,如果是查找近似匹配值,被搜索区域(例子中的B3:E4)的首行值以升序的顺序排列,可以防止搜索错误

出处:

办公软件 77

案例九

用VLOOKUP函数实现多条件查找,如下图,如何实现通过姓名和工号来查找员工籍贯?

在C16单元格里面输入=VLOOKUP(A16&B16,IF({1,0},A2:A5&B2:B5,D2:D5),2,0),按SHIFT+CTRL+ENTER键结束。

办公软件 78

案例十

用VLOOKUP函数实现批量查找,VLOOKUP函数一般情况下只能查找一个,那么多项应该怎么查找呢?如下图,如何把张一的消费额全部列出?

在C9:C11单元格里面输入公式=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT(“b2:b”&ROW($2:$6)),B$9),$C$2:$C$6),2,),按SHIFT+CTRL+ENTER键结束。

办公软件 79

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图