企业官网建设流程全解析

如果有两个表格（表1 和表2），它们的第一列是项目名称，第二列是要核对的数字，要制作出一个两个表数字差异的核对表你能想到几种方法？

一、EXCEL查找函数-VLOOKUP

1、合并二表中的项目名称并提取唯一值

方法1：将表1和表2中的项目名称复制到一列，使用数据选项卡中的删除重复项命令

方法2：使用Excel新函数提取不重复值

=UNIQUE(VSTACK(B8:B20,E8:E20))

首先使用VSTACK纵向合并二表中的项目名称，再用UNIQUE函数提取不重复值

2、根据项目名称在表1查找

在J8单元格输入公式：

=IFERROR(VLOOKUP(I8,$B$8:$C$20,2,0),"")

3、根据项目名称在表2查找

=IFERROR(VLOOKUP(I8,$E$8:$F$20,2,0),"")

以上查找不到的都显示空文本。

4、计算表1与表2数据差值

在L8单元格输入公式：

=N(J8)-N(K8)

如果直接相减会出现错误，因为VLOOKUP查找不到时返回的是空文本，用N函数处理空文本为0值。

5、筛选掉结果为0的结果

二、EXCEL新函数-LET

如何设计一个综合公式，一键完整这两个表的核对，并且制作出一个两个表数字差异的核对表？

使用LET函数通过定义名称来简化公式，此时，参考公式如下：

=LET(

项目1,B8:B20,

数值1,C8:C20,

项目2,E8:E20,

数值2,F8:F20,

项目, UNIQUE(VSTACK(项目1,项目2)),

表1, SUMIF(项目1,项目,数值1),

表2, SUMIF(项目2,项目,数值2),

差值,表1-表2,

合并, HSTACK(项目,表1,表2,差值),

筛选, FILTER(合并,差值<>0,"全部一致"),

IFERROR(VSTACK({"项目","表1","表2","表1-表2"},筛选),""))

显然，这个公式就非常简练和易读了，公式中按照计算步骤顺序，定义了以下几个名称：

1. 名称“项目1”：表示单元格区域，B8:B20
2. 名称“数值1”：表示单元格区域，C8:C20
3. 名称“项目2”：表示单元格区域，E8:E20
4. 名称“数值2”：表示单元格区域，F8:F20
5. 名称“项目”：表示不重复项目，UNIQUE(VSTACK(项目1,项目2))
6. 名称“表1”：表示每个项目在表1中的值，SUMIF(项目1,项目, 数值1)
7. 名称“表2”：表示每个项目在表2中的值，SUMIF(项目2,项目, 数值2),
8. 名称“差值”：表示每个项目在两个表的数值的差值，表1-表2
9. 名称“合并”：表示将不重复项目名称、表1的值、表2的值和计算出来的差值，合并为一个新数组，HSTACK(项目,表1,表2,差值)
10. 名称“筛选”：表示对合并数组的筛选结果，筛选条件是差值不为0，FILTER(合并,差值<>0,"全部一致")

LET函数的最后一行是使用VSTACK函数将一行标题文字与筛选结果合并为一个完整核对表，并使用IFERROR函数判断处理错误值，因为如果两个表格数据完全一致，那么FILTER函数的返回结果只有1列数据，但标题是4列数据，列不一致就导致VSTACK函数出返回错误值。

三、EXCEL自定义函数-LAMBDA+LET

为了能够在任何一个表格中对任意位置的两个表进行核对，我们可以联合使用LAMBDA函数和LET函数设计自定义函数公式，此时参考公式如下：

=LAMBDA(表1项目,表1值,表2项目,表2值,

LET(项目, UNIQUE(VSTACK(表1项目,表2项目)),

表1, SUMIF(表1项目,项目,表1值),

表2, SUMIF(表2项目,项目,表2值),

差值,表1-表2,

合并, HSTACK(项目,表1,表2,差值),

筛选, FILTER(合并,差值<>0,"全部一致"),

IFERROR(VSTACK({"项目","表1","表2","表1-表2"},筛选),"")

)

)

将这个公式定义一个名称“核对”，就可以在当前工作簿的任何一个工作表调用了。

在I8单元格输入公式：

=核对(B8:B20,C8:C20,E8:E20,F8:F20)

四、Power Query

1、分别将表1 和表2加载至PowerQuery中

2、将二表合并为新查询

进行如下图所示设置：

3、展开“表2”列

4、合并项目名称列-添加条件列

5、删除无关列，调整列顺序并重命名

6、添加差值列

注意不能直接将二列相减，在PowerQuery中，null与数字的计算结果是null

7、筛选掉差值中的0值，上载至EXCEL

五、合并计算

1、将表1 和表2中的数据列的名称重命名为不同的名称

2、使用数据选项卡中的合并计算功能将二表中的数据合并

3、添加差值计算列

以上5种方法各有特色，你喜欢哪一种？