主页 > excel专区 > excel函数 >

excel 两表数据快速对比,高手都是这样做,四种方法随你选

excel函数 2020-05-23

图1两张工作表的数据进行对比,你有几种方法?图1是在同一个工作簿下的两张表格,分别为表1和表2。

excel两表数据快速对比,高手都是这样做,四种方法随你选

图1

▍找出两表的相同数据并填充颜色

▼方法1:高级筛选——找出表1和表2数据相同的单元格,并填充上绿色。

选中表1的a1:d11区域,点“数据”菜单→点击“排序和筛选”工具组的“高级”→“列表区域”就是表1已经选中的数据区域,“条件区域”就是表2的数据区域a1:e11,点确定。表1数据自动筛选,显示的那部分数据就是两表相同的,给它们填充上绿颜色,再点“数据”→点击“排序和筛选”工具组的“清除”。动图展示:图2

excel两表数据快速对比,高手都是这样做,四种方法随你选

动图2:高级筛选找两表相同数

细节讲解:高级筛选不能跨工作簿使用,所以要比较两表,请先复制到同一个工作簿。a列和第1行的表头名称顺序可以不一样,也是能识别的。

▼方法2:条件格式——找出表1和表2数据相同的单元格,并填充上绿色。

选中表1的a1:d11区域,点“开始”菜单→点击“条件格式”→“新建规则”,输入公式=a1=表2!a1 (不能有绝对引用,因为工作原理是表1a1和表2a1比较,数据一样就变绿;就是一个一个计算比较的,加了绝对引用就不能一列一列,一行一行比较了),在点下面的“格式”,选择填充绿色,全部确定。结果为绿色的就是相同的数据,动图展示:图3

excel两表数据快速对比,高手都是这样做,四种方法随你选

动图3:条件格式找出两表相同数

细节讲解:条件格式不能跨工作簿使用,a列和第1行的表头名称顺序必须一模一样,不然会出错。

▍两表数据比大小,选出大的数据,并填充绿色

▼方法1:条件格式——比较d列的销售数量,表1>表2的数据找出来,并整行填充绿色。

选中表1的a1:d11区域,点“开始”菜单→点击“条件格式”→“新建规则”,输入公式=$d2>表2!$d2 (列方向要绝对引用,因为我们就是比较d列的销售数量,不然会出错),在点下面的“格式”,选择填充绿色,全部确定。结果为绿色的就是相同的数据,动图展示:图4

excel两表数据快速对比,高手都是这样做,四种方法随你选

动图4:条件格式比大小

细节讲解:条件格式不能跨工作簿使用,a列和第1行的表头名称顺序必须一模一样,不然会出错。

▼方法2:用vlookup函数比较大小

在表1的e列插入“辅助列”,在e2单元格输入公式=vlookup(a2,表2!$a$2:$d$11,4,0)。

公式解析:vlookup的第1参数是查找值(产品),通过产品找销售数量;第2参数是查找区域(首列a列必须是查找值产品,还要包含结果列d列销售数据),要加绝对引用,不然数据会偏移;第3参数写数字4,因为结果列销售数据是在表2的查找区域的第4列;第4参数为0表示精确查找。

这是vlookup函数的原理,不明白的朋友可以看下我发布的第1篇和第2篇文章,详细讲解vlookup的。vlookup使用范围最广,可以跨工作簿引用数据。动图展示:图5

excel两表数据快速对比,高手都是这样做,四种方法随你选

图5:vlookup引用数据比大小

▍在条件格式输入公式时错误率高达90%,注意事项必看。如图6

excel两表数据快速对比,高手都是这样做,四种方法随你选

图6

▼方法一、单列条件格式设置:将c列库存数量比 i 列库存数量大的数据用绿色标记出来,用条件格式的方法。

◆绝大部分人会犯这个错误:选中c2:d11区域,然后在条件格式的公式栏里输入=$c$2:$c$11>$i$2:$i$11,下面格式选择填充绿色,结果显示d2:d11全填充绿色,结果是错误的。如图7

excel两表数据快速对比,高手都是这样做,四种方法随你选

图7:错误案例

◆正确的写法是:选中c2:d11区域,然后在条件格式的公式栏里输入=c2>i2。如图8

excel两表数据快速对比,高手都是这样做,四种方法随你选

图8:正确案例

◆是不是很奇怪,这是为什么?这里涉及到“反白显示单元格”这个问题。解释如图9:

excel两表数据快速对比,高手都是这样做,四种方法随你选

图9:反白显示单元格的解释

当我们从c2选到c11时,c2是“反白单元格”,在条件格式的公式里只要输入反白单元格一个数据的公式就可以了,=c2>i2,然后excel系统会自动按c2>i2,c3>i3,c4>i4……以“反白单元格”开始往下依次推算,条件成立就填充绿色。

▼方法二、多列条件格式设置:将c列库存数量比 i 列库存数量大的数据找出来,然后把整行填充绿色,用条件格式的方法。

◆绝大部分人会犯这个错误:选中a2:e11区域,然后在条件格式的公式栏里输入=$a$2:$e$11>$g$2:$i$11,下面格式选择填充绿色,结果没有填充,条件格式里的公式是不需要输入区域范围的。如图10:

excel两表数据快速对比,高手都是这样做,四种方法随你选

图10:错误案例

◆正确的写法是:选中a2:e11区域,然后在条件格式的公式栏里输入=$c2>$i2。如图11:

excel两表数据快速对比,高手都是这样做,四种方法随你选

图11:正确案例

=$c2>$i2要加绝对值表示永远都是c列和i列在计算,因为条件格式的公式是从“反白单元格”开始的,这里a2是“反白单元格”,在a2、b2一直到e2都是执行$c2>$i2这个命令,成立就都会填充绿色,因为列方向有绝对引用,所以列方向的公式不会偏移。

同上原理,c3一直到e3都是执行$c3>$i3这个命令,因为只是列方向绝对引用,行方向没有绝对引用,所以行方向的数字是会变的,如果条件成立就都会填充绿色。依次类推。


标签: excel两表数据快速对比

九游会j92网 copyright © 2016-2020 sxqcrd.com. some rights reserved. 备案号:闽icp备16003269号