之前给大家推送了一篇用PQ完成跨表数据核对的教程，但由于版本限制，好多伙伴都无法使用，今天给大家介绍3个公式，同样可以完成数据核对。

最近在微信学习交流群中收到某位学员的问题咨询，问题是如何根据单据编号和物料长代码返回对应的含税数额。如下表：

其实这位学员的问题就是如何实现多条件查询。

下面通过一个实例跟大家分享一下常用的几种多条件查询方法。

下表是某电商公司的客户投诉表，现在需要通过A表中的客户姓名与地区两个条件来查询B表中的产品型号，返回到A表的E列中。

1.lookup函数

函数公式：=LOOKUP(1,0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19),$K$3:$K$19)

公式解析：首先通过A3单元格与B表I列数据做对比，同时用B3单元格与B表J列信息做对比。

在excel中如果两个单元格对比，相等则返回TRUE，在四则运算中用1表示。如果不相等则返回FALSE，使用0表示。

那么(A3=$I$3:$I$19)*(B3=$J$3:$J$19)这部分运算的结果就只有0或者1两种情况，因为只有0*1、1*1、1*0这三种情况。

用0来除以0和1，由于分母不能为0，所以0/0返回的是错误，0/1返回的结果为0。Lookup函数在查找的时候是忽略错误的，所以只有数据运算结果为1的公式满足条件。

那么我们就很好理解0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19)的目的就是将正确结果用0表示，其他的变成错误值，利用函数查找忽略错误这个特点完成查找。

总结：本函数由于使用了二分法原理查找，所以如果数据量较大时运算会很慢。

2.VLOOKUP函数

使用G2单元格在A列中查找，如果查找到对应单元格则返回A列向右第二列的数据。简而言之：=VLOOKUP(查找什么，在哪查找，从条件所在列算起找到后返回对应的第几列数据，精确或模糊查找)。

那vlookup如何才能完成多条件查询呢？。

还以客户投诉表为例，按照姓名&地区来匹配产品型号返回到E里中。

其实我们是可以将A、B两表中插入辅助列，将姓名和地区都合并到一个单元格中然后使用vlookup来完成。

但是插入2个辅助列后整个表列数发生变动，在工作中往往单元格中有很多公式，如果列数发生变化将直接导致表格中函数公式运算结果错误。所以添加辅助列的方式虽然简单，但不是最好的方式。

那么不用辅助列如何才能完成多条件查询呢？

首先我们查找值合并很简单，输入函数vlookup时第一个参数可以写成A3&B3，即可将A3、B3两个单元格内容合并，作为查找值。

现在问题查找区域也需要做合并。

如果把两列内容合并在一起，可输入公式=H2:H19&I2:I19，按ctrl+shift+回车生成结果，然后下拉公式，这样两个条件就变成了一个。

接下来通过IF函数提取对应的J列数据，可输入公式

=IF({0,1},H2:H19&I2:I19,J2:J19)，按ctrl+shift+回车生成结果，然后下拉公式，{0,1}表示逻辑值{FALSE,TRUE}。

下面我们详细来解析一下:

首先在excel中0表示错误，1以及其他所有数值表示正确。如下表示例：

通过上面的例子我看到如果IF判断0则返回错误，判断1则返回正确。

现在我们可以将公式拆分为以下两种情况：

IF（0, H2:H19&I2:I19,J2:J19），0表示FALSE，所以只能返回J列数据。

IF（1, H2:H19&I2:I19,J2:J19），1表示TRUE，所以只能返回H列和I列合并结果。

那么IF({0,1},H2:H19&I2:I19,J2:J19)怎么理解呢？

既然是数组公式，那么可以将它理解为同时返回两组数据，0对应的是J2:J19，1对应的H2:H19&I2:I19，构建了两列数据。

最后我们使用vlookup函数完成嵌套，

=VLOOKUP(A3&B3,IF({1,0},H3:H20&I3:I20,J3:J20),2,0)，这里我们就可以理解为用A3&B3在H3:H20&I3:I20中查找对应J3:J20中的数据。因为公式中

IF({1,0},H3:H20&I3:I20,J3:J20)返回的顺序是先返回H3:H20&I3:I20再返回J3:J20。

注意：很多人不明白为什么嵌套的时候IF第一参数又变成了{1,0}，因为这里我们需要返回的是H和I合并结果作为查找区域。PS：所有数组公式完成输入后要使用数组三键ctrl+shift+ener来返回运算结果！

这样我们不用辅助列也能通过vlookup函数完成多条件查询。

3.OFFSET+MATCH函数

下面举例跟大家分享一下通过offset函数完成多条件查询。

函数公式：

{=OFFSET($J$2,MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0),)}

公式解析：

完成多条件查询第一步先要确定A表中姓名&地区合并后对应在B表中姓名&地区的顺序。这里我们通过MATCH来完成，我们用个简单的例子说明。

=MATCH(A2,E:E,0)表示使用A2单元格在E列中查找，0表示精确查找、1小于、-1大于，通常情况下都是精确查找。

MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0)表示将A3与B3合并作为查找内容，H列和I列合并作为查找区域，0表示精确查找。

确定顺序后我们通过OFFSET函数以顺序数据作为偏移行数返回对应数值。

OFFSET函数的功能是以指定的单元格引用为参照系，通过给定偏移量得到新的引用。

返回的引用可以为一个单元格或区域。并可以指定返回的行数或列数。Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值#VALUE!。

=OFFSET(J2,1,0,1,1)表示以J2单元格作为参照物向下偏移1行，向右偏移0列，返回1行1列数据区域。

=OFFSET($J$2,MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0),)表示以$J$2为参照单元格，通过MATCH查找出来顺序作为向下偏移的行数，偏移列数量省略表示不偏移，第三个、第四个参数省略表示只返回一个单元格区域。

下面我们来总结一下三种方式的利弊：LOOKUP函数使用过程中运算较慢；VLOOKUP函数使用IF（{0,1}）数组公式，理解上存在一定难度；OFFSET+MATCH函数公式简单，可以作为首选方案。

＊＊＊＊部落窝教育－excel数据核对公式应用＊＊＊＊

原创：龚春光/部落窝教育（未经同意，请勿转载）

Excel怎么设置只提取指定行中的数据？有些时候我们需要从一个excel文件中的数据库中提取指定的行或列中的数据。例如如图示，是国内所有上市公司的行业统计。但是现在我们只需要其中部分上市公司的行业统计，我们怎么办呢，下面分享一个技巧，需要的朋友可以参考下

有些时候我们需要从一个excel文件中的数据库中提取指定的行或列中的数据。例如如图示，是国内所有上市公司的行业统计。但是现在我们只需要其中部分上市公司的行业统计，我们怎么办呢，是一个个查找，然后复制吗，当然不是。我们可以做一个表格，只需要把我们需要的公司代码列复制进去就会自动显示其行业统计情况。也就是从浩如烟云的数据中，提取需要的数据对应的行中的数据。下面就以我刚刚解决的这个问题为示例详细说明一下。

1、打开源数据文件，把首行中的对应的数据，复制行到空白位置。

2、选中J2单元格，单击编辑栏中的"插入函数“，如图示。

3、弹出”插入函数“对话框，设置其类别为”逻辑“，并选择列表框中的IF选项。然后确定。

4、在弹出的”函数参数“对话框中，设置Logical_test参数为”I2=“”，将Value_if_true设置为“”，也就是空白内容。最重要的一步来了：将Value_if_false参数框中输入“VLOOKUP(I2,A2:B2846,2),然后确定。这个步骤是什么意思呢。就是用if函数判定I2单元格中的内容是不是不是空白。若是空白，那么J2单元格也是空白。若不是空白，就执行VLOOKUP函数。在A2:B2846区域中（因为源数据不改动，所以这里用的是相对引用）查找I2单元中的内容，找到后，在J2单元格中返回所找到的内容所在的行的第二列中的数据。

5、经过第4步后，我们在I2单元中输入任意证券代码就会在J2单元中显示其行业了。那么，现在我们需要的是在I列粘贴进去我们需要的证券代码后，其后会出现源数据所对应的第一行中的内容。所以要接着第6步。

6、选中J2单元格，注意是J2，不是I2哦.(I2是我们输入证券代码的地方），鼠标移动到单元格右下角，出现十字光标时，按住鼠标左键向右拖动到O2单元格。如图

7、这个时候你就会发现原来源数据中的C2-G2单元格中的内容就会自动填充到K2-O2单元格中。这个时候，如果我们需要的数据是按源数据中的数据从上到下排列的，就可以不用修改了。直接重复第6步中的方法，把J2-O2每个单元格下拉填充就完成了。如图示，先把需要的数据列复制到I列中，然后依次下拉填充。

8、但有时候，如果我们需要的不是按照源数据中的排序，比如说，回到我们现在用的这个例子，如果在I3中，输入000002就会出错。如图示。这是为什么呢，看一下J3中的公式就明白了。j3中的函数为VLOOKUP(I3,A3:B2847,2))，也就是搜索从A3到B2487区域中的内容。没有包含A2，所以我们输入000002在源数据中找不到。所以使用这个方法前，要先对需要的数据按源数据进行排序。不然，是不准确的。

在实际工作中，部分资料不想被别人打开和修改，除了可以用密码之外，还可以用什么方法呢？

今天，我们要学习的是如何用Excel实现对指定日期的内容不显示。

方法：

1、设置条件格式。

目的：第5-8行的数据在12月27日之后不可查看。

方法：

1、选定不需要显示的内容。

2、【开始】-【条件格式】-【新建规则】-【使用公式确定要设置单元格的格式】，在“为符合此公式的值设置格式”中输入公式：=today()>=--"2017-12-27"。

3、【格式】-【自定义】，在编辑框中输入：;;;（三个分号），【确定】-【确定】。

2、保护工作表。

方法：

1、【审阅】-【保护工作表】-输入密码-取消【选定锁定单元格】。

2、【确定】。

在使用Excel时，经常会通过编辑公式来进行计算，但使用公式时，如果把含公式的单元格复制粘贴到其他位置，则公式的计算结果可能就会发生改变，另外更改公式中引用单元格的内容或者删除引用的单元格等都会令公式的计算结果发生改变。如果想避免这种情况，通常需要把公式转变成数字，Excel中把公式转变成数字的方法有多种，这里以Excel2007为例，介绍一种个人认为是最快速的方法，供大家参考。

●例如下图表格“合计”列中显示的内容虽然是数字，但如果选中其中的单元格，在编辑栏就会发现实际上该列中的内容都是公式，显示的数字只是公式的计算结果。

●如果想公式转变成数字，一般比较常用的是选择性粘贴的方法。即先选择公式所在的区域，进行复制操作，再在该区域中点击鼠标右键，然后点击右键菜单中的“选择性粘贴”。

●再在选择性粘贴对话框中点击“数值”选项，最后点击“确定”按钮，则所选区域中的公式就转变成数字了。

●上述选择性粘贴的转换方法虽然也不算多复杂，但实际上还有更快的转换方法。如果只是想转换一个单元格中的公式，可以在该单元格中双击鼠标，则该单元格就会处于编辑状态，显示的内容变成了实际的公式。

●此时按键盘的“F9”键，则该单元格中的内容就会立即变成数字，即公式的计算结果。

●如果想把一个区域内的单元格中的公式转变成数字，也有一种更快的方法：先选中要转换的区域。

●选中区域后，将鼠标指针指向区域的边缘，当指针形状变成四个箭头时，按住鼠标右键不放。

●按住鼠标右键不放的同时，向左侧或者右侧移动鼠标，移动一列即可。

●不要松开鼠标右键，再将鼠标移回选择区域的位置。

●鼠标移回选择区域的位置后，松开鼠标右键，此时会弹出一个菜单，点击其中的“仅复制数值”。

●点击后，选择区域内单元格中的公式就都变成数字了。

上述介绍的只是个人认为最快将Excel公式变成数字的方法。当然如果会编辑宏，则制作一个专门的宏会更快的将公式变成数字，但启用宏可能会有一定的安全问题，另外也不适合非相关专业的人员。如果读者还有其他更快的方法，欢迎在评论中提出，大家互相学习。

Excel中数据的筛选是一项非常强大的功能，其常用的数据筛选功能有【自动筛选】和【高级筛选】，其功能也是非常的强大。今天我们主要来学习一下“自动筛选”功能。对于数据量非常庞大的表格有绝对性的实用性哦！

一、通过筛选排序。

方法：

1、选定标题行。

2、【数据】-【排序】。

3、单击需要排序的列标题右下角的箭头，选择【升序】或【降序】即可。

二、按颜色筛选。

方法：

1、选定标题行，【数据】-【筛选】。

2、单击任意标题行右下角的箭头-【按颜色筛选】并选取颜色。

三、包含指定值得筛选。

目的：筛选销售地区中有“海”的所有信息。

方法：

1、选定标题行，【数据】-【筛选】。

2、单击目标列的右下角箭头，在搜索框中输入关键字“海”并【确定】。

四、筛选以指定值开头的数据。

目的：筛选出姓“小”的所有销售员信息。

方法：

1、选定标题行，【数据】-【筛选】。

2、单击目标列的右下角箭头，在搜索框中输入关键字“小*”并【确定】。

解读：

关键字中的星号（*）为通配符，*可以匹配任意长度的字符。

五、筛选以指定值结尾的数据。

目的：筛选销售地区中以“海”结束的数据。

方法：

1、选定标题行，【数据】-【筛选】。

2、单击目标列的右下角箭头，在搜索框中输入关键字“*海”并【确定】。

六、筛选指定长度的数据。

目的：筛选销售地区中地名为2位数或3位数的信息。

方法：

1、选定标题行，【数据】-【筛选】。

2、单击目标列的右下角箭头，在搜索框中输入关键字“??”或“???”并【确定】。

解读：

关键字中的问号（？）为通配符，只能匹配一个字符。??就是两个字符，???就是三个字符。在录入关键字是问号必须为英文状态。

七、精准筛选。

目的：快速筛选第一季度销售额为66或67的所有数据。

方法：

1、选定标题行，【数据】-【筛选】。

2、单击目标列的右下角箭头，在搜索框中输入关键字“66”或“67”并【确定】。

八、范围查询。

目的：筛选第一季度销售额大于等于30小于等于60的销售记录。

方法：

1、选定标题行，【数据】-【筛选】。

2、单击目标列的右下角箭头，【数字筛选】-【介于】，输入范围值，并【确定】。

结束语：

经过学习，数据筛选没有问题了，但是如果查阅原来的数据了？除了【全选】之外，有么有其它的方法呢？欢迎大家在留言区留言讨论或发表自己的看法和观点哦！

数据核对，一直是Excel中比较常见的工作之一，如果一行，一个字母，一个数字的去核对，费时费力而且还容易出错。那么，有没有办法进行高效率的数据核对呢？

一、原数据的准备。

1、账面库存。

2、盘点库存。

目的：核对上面的账面数据和库存数据。

二、将“账面数据”引用到“库存数据”当中。

方法：

1、添加辅助列。

2、选定目标单元格。

3、输入公式:：=VLOOKUP(B3,账面库存!B:C,2,0)。

4、Ctrl+Enter填充。

解读：

VLOOKUP函数不仅可以实现本表内数据的查询，还可以实现跨表查询，其语法结构为：=VLOOKUP（查询的值，查询的表名!查询范围，返回的列数，匹配模式）。

三、核对“账面数据”和“库存数据”。

方法：

1、插叙新的入住列。

2、选定目标单元格。

3、输入公式：=IFERROR(IF(C3<>D3,"错误",""),"无数据")。

4、Ctrl+Enter填充。

解读：

1、用IF(C3<>D3,"错误","")判断C3和D3的值是否不相等，如果相等，返回“错误”两个字，如果相等，返回空。

2、用IFERROR函数来判断单元格是否存在错误形式，如果存在，则返回“无数据”。

大家好，今天和大分享下Excel数据去重方面的函数公式，看完之后，请在文章末尾处留言，你会几个，会那个？

一、第1个去重复值公式

1、公式截图

2、公式

=INDEX(A:A,SMALL(IF(MATCH($A$2:$A9&"",$A$2:$A9&"",)=ROW($A$2:$A9)-1,ROW($A$2:$A9),2^20),ROW(A2)))&""

二、第2个去重复值公式

=INDEX($A$2:$A9,MATCH(,COUNTIF($C$1:C1,$A$2:$A9),))&""

三、第3个去重复值公式

=IFERROR(LOOKUP(1,0/(COUNTIF($C$1:C1,$A$2:$A$8)=0),$A$2:$A$8),"")

四、第4个去重复值公式

=LOOKUP(1,0/FREQUENCY(0,COUNTIF($C$1:C1,$A$2:$A8)),$A$2:$A8)&""

Excel是文字处理、电子表格、幻灯片制作中的电子表格，Excel有大量的公式函数应用选择，极大的方便了数据处理、统计分析和辅助决策操作，所以被广泛地应用于众多领域。如果你从事会计工作或者是跟数据打交道较多的职业，在excel中怎么引用公式是你必须掌握的，下面就跟着小编一起来认识、熟悉一下基本操作吧。

首先大家都知道的，excel数据引用分为四种：excel数据相对引用、excel数据的绝对引用、同文件内excel表间引用、不同文件间的excel引用。下面我来一一阐述。

1、excel数据相对引用，这种最简单，就不详细讲解了。

（1） 需要计算数据一和数据二的平均数与数据三的差，可以输入：=(A2+B2)/2-C2或者=sum(A2:B2)/2-C2，大家千万不要漏掉了前面的“=”哦！

（2）计算数据一、数据二、数据三的最大值与最小值的差，可以输入：=max(a2:d2)-min(a2:d2)，max是求最大值函数，min是求最小值函数。

2、excel数据的绝对引用：计算数据中每个数据与最大值的差

在d2单元格输入=MAX(A2:A6)-A2，我们无法通过拖动，生成d3、d4、d5等下面单元格的数据，我们在d3单元格想要的是=MAX(A2:A6)-A3，我们用“$”来固定那些需要不变的数据，如上面a2单元格的公式就可以写成=MAX(A$2:A$6)-A2。

3、同文件内excel表间引用：同文件内不同表之间的数据引用和计算通常是使用英文状态的引号表名结合而来的。

引用a列数据，可以在a2单元格输入='1'!a2 英文单引号中为表的名字，表和单元格名称间用英文的!分割开来。

4、不同文件间的excel引用：用英文的中括号 [ ]来引用文件，我们将正在操作的文件保存到d盘，默认名字为book1.xls 新建一个excel文件，引用book1.xls文件中第一列的数据：=[book1.xls]1!a2输入完公式，打回车键之后，excel会自动加上文件地址。图例会变为：='d:\[book1.xls]1'!a2。

大家是不是发现其实也并不会太难，关注“智虎科技”头条号，带你掌握更多的知识。

如果在几百行的Excel表格中，找出不同列中数据不同的哪一行；或者在两列数据中个，找出相同的数值；核对两个工作表中的数据是否相同等等。你会怎么做？一行行的对比……那你就OUT了……

一、同行对比（相同/不同）。

目的：对比库存数和账面数是否相同。

方法：

选定目标单元格。快捷键：Ctrl+\（反斜杠）。标识。

二、两列数据对比。

目的：对比两列数据中是否有重复值。

方法1：

方法：

选定目标单元格。【条件格式】--【突出显示单元格规则】-【重复值】……

备注：

此方法主要用于对比两列中的数据是否有重复值，数据可以不在同一行。

方法2：

方法：

选定一列数据，包括标题。【数据】-【高级】。在【条件区域】中选择第二列单元格（包含标题），并【确定】。【开始】-填充单元格颜色。【数据】-【清除】。

三、不同工作表的两列核对。

目的：对比“表1”和“表2”的数据。

方法：

选定目标单元格。输入公式：=COUNTIF(表1!B:B,B3)。Ctrl+Enter填充。选定【公式】单元格，【数据】-【筛选】，筛选出非0的值。【开始】-填充颜色。【数据】-【筛选】，取消筛选选项。观察数据，填充了颜色的为重复的值，对于的数值为重复的次数。

备注：

公式计算的结果为0则为不重复，非0则为重复，对于的数值为重复的次数。公式的计算结果可以理解为：表2对应列中的值在表1对应列出现的次数。

四、数值类表格比较。

目的：对比库存数据和账面数据是否一致。

方法：

复制其中一个表格的数据。选择目标表格的第一个单元格，【右键】-【选择性粘贴】。选择【运算】中的【减】，并【确定】。非0值即为不一致的数据。

备注：

前提条件数据必需时数值类型。

五、不限类型表格对比。

目的：对比表格的差异。

方法：

选定目标单元格。【条件格式】-【新建规则】-【使用公式确定要使用单元格的格式】。输入公式：=A2<>A13（其中A2为第一个表格的左上角第一个单元格，A13为第二个表格的左上角第一个单元格）。【格式】-【填充】-选择填充色-【确定】-【确定】。查看数据，可以看到不用的数据已被用填充色填充。

六、“差异化”比较。

目的：比较基础数据有差异的表格数据。

从上图中我们可以发现“账面数据表”和“盘点数据表”的商品名称并没有按照特定的顺序来排列，那么我们如何来比较这两个表格的差异呢？

方法：

选定目标单元格。【条件格式】-【新建规则】-【使用公式确定要使用单元格的格式】。输入公式：=VLOOKUP($B14,$B$3:$I$9,COLUMN()-1,0)<>C14。【格式】-【填充】-选择填充色-【确定】-【确定】。

假设A列有500个数据，如何选出这一列里的第5、10、15、20、25个数据，以此类推。

=INDEX($A:$A,ROW(1:1)*5+1)

INDEX函数有2种语法结构。这里介绍她的第一种结构：

INDEX(要取数的目标区域，行号，列号)，其中列号可以省略。如果列号省略，则目标区域必须为单列。