excel里如何用A表里的数据查找B表内相同的数据?操作步骤是什么?
方法一:
1、假设你的数据A、B两列都从第一行开始,在C1输入:=IF(COUNTIF1(B:B,A1)>0,"B列有","B列没有"),敲回车键确定。
2、复制C1单元格,选中C列并粘贴,这样C列所有的单元格都有这个公式代码了。
3、相反如果要找B列里有而A列里没有的话则输入:=IF(COUNTIF(A:A,B1)>0,"A列有","A列没有")下拉公式。方法见1-2两步。
4、还有一种情况,如果两个同时都需要找,这两个公式同时使用就OK了。在C1输入公式=IF(COUNTIF(B:B,A1)>0,"B列有","B列没有")。
然后在D1输入公式代码=IF(COUNTIF(A:A,B1)>0,"A列有","A列没有")
5、复制C1、D1单元格,分别粘贴到C列和D列即可。
方法二:
1、简单的举个例子,例子简单但很形象,能够了解函数的用法就可以了。
2、那么我们在C列中输入函数vlookup匹配函数,找出存在的项目并显示出来;第一列是A1然后用逗号隔开,就可以点击提示的第二个参数;第二个参数我们直接点击第二列即B列。
3、然后逗号隔开,选择下个参数,第三个参数是1;第四个参数是精确与模糊匹配的概念,我们这里选择0或false,精确匹配。
4、我们可以选择往下拉,也可以选中第一个单元格右下角出现+字后双击,数目应于第一列相同。
5、我们可以看到C列中都存在数据,说明A列在B列中都存在,如果不存在那么现实#value,就是无法显示的意思;
6、在模糊匹配的时候要用到*号,B列是12345678,A列是345,那么我们可以写成“*”&A&“*”,这个通配符来表示任意字符,只要包含即可。
1、首先将关键数据输入到单元格中,需要根据“公司现状”数据提取到对应店名称的“公司标准”列中。
2、点击“fx”插入函数,选择“VLOOKUP”函数。
3、然后在打开的函数参数设置中根据下图设置参数,切换为公式为:=VLOOKUP(A2,$D$2:$E$5,2,0)。
4、点击回车生成计算结果并向下填充公式,即可得到所有的店名称对应的公司标准数据了。
1、假设这两个表没有在一个excel里面,把它们移动到同一个表中,移动方法图所示,
2、Vlookup函数的语法:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
lookup_value:要查找的值,数值、引用或文本字符串
table_array:要查找的区域,数据表区域
col_index_num:返回数据在区域的第几列数,正整数
range_lookup:模糊匹配,TRUE(或不填) /FALSE
3、找出两列相同的数据:用的公式是=VLOOKUP(A2,Sheet2!A:B,1,0)
公式的含义是在sheet2工作表的A:B区域的首列中查找等于a2的值,找到后,返回该区域第1列(即E列)的同行的值。最后的参数0表示精确查找。
4、找出两列所对应的数据:用的公式是=VLOOKUP(A2,Sheet2!$A$2:B150,2,0)
公式的含义是在sheet2工作表的A2:B150区域中查找符合条件显示B列的值,找到后,返回该区域第2列(即F列)的同行的值。最后的参数0表示精确查找。
5、完成上面四步以后,最后一步比较简单,直接拉填充柄填充下面的空白处即可,查找到对应的数据会直接显示,如果没有找到刚显示#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 B
1 农产品名称 单价
2 小麦 0.56
3 水稻 0.48
4 玉米 0.39
5 花生 0.51
…………………………………
100 大豆 0.45
Sheet2为销售清单,每次填写的清单内容不尽相同:要求在Sheet2中输入农产品名称、数量后,根据Sheet1的数据,自动生成单价和销售额。设下表为Sheet2:
A B C D
1 农产品名称 数量 单价 金额
2 水稻 1000 0.48 480
3 玉米 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。这对我们其实也没有什么实际意义,只是满足好奇而已,有兴趣的朋友可以去体验体验。
2.Lookup_value是一个很重要的参数,它可以是数值、文字字符串、或参照地址。我们常常用的是参照地址。用这个参数时,有两点要特别提醒:
A)参照地址的单元格格式类别与去搜寻的单元格格式的类别要一致,否则的话有时明明看到有资料,就是抓不过来。特别是参照地址的值是数字时,最为明显,若搜寻的单元格格式类别为文字,虽然看起来都是123,但是就是抓不出东西来的。
而且格式类别在未输入数据时就要先确定好,如果数据都输入进去了,发现格式不符,已为时已晚,若还想去抓,则需重新输入。
B)第二点提醒的,是使用时一个方便实用的小技巧,相信不少人早就知道了的。我们在使用参照地址时,有时需要将lookup_value的值固定在一个格子内,而又要使用下拉方式(或复制)将函数添加到新的单元格中去,这里就要用到“$”这个符号了,这是一个起固定作用的符号。比如说我始终想以D5格式来抓数据,则可以把D5弄成这样:$D$5,则不论你如何拉、复制,函数始终都会以D5的值来抓数据。
3.Table_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函数的工作表时,里面抓取外部档案的数值,只是上次我们存盘时保存的值。若要连结最新的值,必须要把外部档案同时打开。
这个工作中常常用到,比较实用!
如何让xcel 每隔10行抽取一行数据?
如果你想抽取 A列的 A10 ,A20,A30...那么就输入公式如下 =OFFSET($A$1,ROW(1:1)*10-1,,,)向下填充就行了。如果你想抽取整行的数据,就输入 =OFFSET(A$1,ROW()*10-1,,,)先向右拉,然后再整 行向下拉就行了。
xcel 如何根据单元格末位数据的不同分别做不同处理
--RIGHT(A1)=2,--RIGHT(A1)=3),MID(A1,1,LEN(A1)-1),IF(--RIGHT(A1)=4,MID(A1,1,LEN(A1)-1)&"DM",IF(OR(--RIGHT(A1)=5,--RIGHT(A1)=6,--RIGHT(A1)=7),MID(A1,1,LEN(A1)-1)&"D",IF(--RIGHT(A1)=0,MID(A1,1,LEN(A1)-1)&"GM","")))用条件格式 ...
excel里,按照A列的某一个数据为分割点,将B列分成两列数据。如题:_百度...
C1=INDEX(A:A,SMALL(IF(A$1:A$4<2.5,ROW($1:$4),65536),ROW(1:1)))&""D1=INDEX(A:A,SMALL(IF(A$1:A$4>2.5,ROW($1:$4),65536),ROW(1:1)))&""公式均以CTRL+SHIFT+回车结束.将两个公式向下复制.
xcel函数公式 有很多表每张表都有不同的人名,人数、顺序不一样,但都...
用VBA来做吧 把所有表都放在一个目录下 在一主表中遍历统计 做好模板后,要用时运算一两分钟吧就可以得结果了 当然用sumif、透视表也行,但得一张表一张表地去统计 如若此工作内容是长期的 每次的统计工作将占很多时间
怎么把年月写成点的格式
1. 如何在单元格中将年月写成1983.02格式 xcel表格公式大全,这个太全了 (2014-02-14 06:37:30)转载▼标签: xcel表格公式大全这个杂谈 分类: 转摘 xcel表格公式大全,这个太全了! 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。2、用出生年月来计算年龄公式:=TRUNC((...
XCEL中当用VLOOKUP函数匹配结果#N\/A或者0时,如何让他显示空白二不显示#...
=if(or(vlookup()=#N\/A,vlookup()=0)," ",vlookup())
在EXCEL表格里,怎么提取某一单元格中的一部分文字
xcel表格中文字和数字如何分离提取?这样做再也不用一个个敲了
excel图表中,怎么显示出0值
点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。用格式刷将A2单元格的条件格式向下复制。B列可参照此方法设置。按以上方法设置后,AB列均有的数据不着色,A列有B列无或者B列有A列无的数据标记为红色字体。四、EXCEL中怎样批量地处理按行排序假定有大量的数据(数值),需要将每一行按从大到小排序,...
可以单鎒xcel里的某一个sheet吗
sheet 英[ʃi:t] 美[ʃit]n. 纸; 被单; 一张(通常指标准尺寸的纸); 一大片(覆盖物); 表格;vt. 用(床单等)包裹; 用(床单等)掩盖; 用缭绳调节(或固定); 给…铺床单;vi. 成片展开,成片流动;[例句]Once a week, a maid changes the sheets.女仆每...
怎么用EXCEL算积分?
Excel中暂时没有现成的求积分的方法。1、网上有很多软件都可以求积分的。譬如:Wolfram ,Mathematica 8等等。2、积分是微分的逆运算,即知道了函数的导函数,反求原函数。3、在应用上,积分作用不仅如此,它被大量应用于求和,通俗的说是求曲边三角形的面积,这巧妙的求解方法是积分特殊的性质决定的。
曲致流感: 请说得具体一些,以便公式的选择,因为EXCEL中数据查找的函数很多,如match、countif、lookup、vlookup等等
高明区15013546313: EXCEL如何从表A中找到表B要寻找的名单. - ?
曲致流感: 用VLOOKUP可以简单实现 假定名字都在两个表的A列从第2行开始,其他信息至K列 在表B的B2单元格输入公式:=vlookup($a2,a!$a:$k,column(b1),)右拉填充,再一起下拉填充
高明区15013546313: EXCEL中用一个表中的一列数据去查询另一个表中的数据应怎么做 - ?
曲致流感: 单条件的使用 Vlookup() 函数查询 多条件的使用 SUMPRODUCT() 函数查询
高明区15013546313: EXCEL表格中如何在表A中找出表B中所对应的详细信息 - ?
曲致流感: 方法和详细的操作步骤如下:1、第一步,将关键数据输入到单元格中,然后需要基于“公司现状”数据将其提取到相应商店名称的“公司标准”列中,见下图,转到下面的步骤.2、第二步,完成上述步骤后,单击“ fx”以插入函数,然后选择“ VLOOKUP”函数,见下图,转到下面的步骤.3、第三步,完成上述步骤后,在打开的功能参数设置中根据下图设置参数,见下图,转到下面的步骤.4、第四步,完成上述步骤后,单击Enter键以生成计算结果并填充公式,可以获得与所有商店名称相对应的上市公司的标准数据,见下图.这样,就解决了这个问题了.
高明区15013546313: EXCEL 表格A中输入一个数据如何自动查找另一个表格B中和表格A相同的数据并调用B中同一行数据 - ?
曲致流感: A表 a1=输入位置 b1=OFFSET(B!A2,,MATCH(A1,B!1:1,)) B表 a1:f1=1,2,3,4,5
高明区15013546313: EXCEL中如何查找A表A列与B表A列相同的所对应B表中B列的数据? - ?
曲致流感: 方法一: 1、假设你的数据A、B两列都从第一行开始,在C1输入:=IF(COUNTIF1(B:B,A1)>0,"B列有","B列没有"),敲回车键确定.2、复制C1单元格,选中C列并粘贴,这样C列所有的单元格都有这个公式代码了.3、相反如果...
高明区15013546313: EXCEL中如何查找A表A列,B列,C列与B表A列,B列,C列相同的所对应B表中D列的数据 - ?
曲致流感: 建议简单化处理:1. 复制表1数据到表2,更改颜色为红色(为了便于观察)2. 按ABC列排序(重复数据中,原数据肯定在上方)3. 在E列输入公式,以E2为例,输入数组公式:=IF(A2:C2=A3:C3,"目标",""),三键确认后下拉填充4. 之后的事情你自己就会处理了吧.也可以一次性选择E列空格,删除所在行.
高明区15013546313: 在Excel中:在A表中有一个值,如何利用公式快速找到B表中对应的值,并且将值填到当前A表中 - ?
曲致流感: =vlookup(A1,B!A:B,2,0) 在B列的A列查找当前表的A1,返回对应B列值
高明区15013546313: EXCEL如何查找同一列中相同数据 - ?
曲致流感: 材料/工具:excel2010 1、打开excel2010表格,例如要找到这一列表数据中的所有的重复值 . 2、接着点击表格左上角的开始菜单按钮 3、然后找到“条件格式”这个菜单,这个菜单在开始菜单下,靠中间的位置. 4、设置让重复值突出显示,这样就很方便的看到重复数据了. 5、给重复值设置一个特定的格式,比如浅黄色的背景,或者红色的背景等. 6、最后重复值就很明显的被标明出来了.
高明区15013546313: 在excel表格里怎么查找同一列之中的某个数据.比如在A列全是名字有几百行,我要找一个叫张三的那行 - ?
曲致流感: 以excel2003为例:在左上角点击“编辑”,会有下拉框,在选择“查找”(或者打开excel后直接按“Ctrl+F”),然后在...