数据透视表vlookup—数据透视表是干嘛的 收藏 0

跨工作表合并两个表格的数据，用vlookup半天，用数据透视表几秒

文章最后有彩蛋！好礼相送！

Excel秘籍大全，正文开始

如图所示的两个工作表sheet1和sheet2，sheet1中的表格是盘点的账面数量，sheet2中的表格是盘点的实盘数量，注意一点，账面数量的商品有的在盘点数量表里不存在，而盘点数量里的有些商品在账面数量表里也可能不存在。

因为互相有不存在的商品，所以至少要用两个vlookup函数进行匹配，这样效率就太低下了，但使用数据透视表就太简单了。

调出数据透视表向导

接着，选择【多表合并计算区域】，单击下一步。

图二

在下一步后，单击【创建单页字段】

图三

在单击下一步，如图所示的对话框中，在选定区域处，选定第一个表格，记得一定要选中标题行，点击添加，然后在选中第二个表格。

添加完成后如图所示：

图四

然后单击下一步，选择【新工作表】，单击完成。

图五

完成以后，如图所示，可以看到两个表格成了数据透视表，我们可以把它转化为普通表格，就完成了。

最后的结果

以上是图形演示，我用gif在演示一遍。

合并了两个表格

以上呢，就是使用数据透视表合并表格，并且计算出差异的全过程。这个数据透视表向导不止可以对两个表格，两个以上的也是可以的。

以下《900套高工作模板.xls 》免费下载，不收一分钱！

常用Excel

用Excel玩好报表

是必不可缺的技能

要知道一张好的图表

可以做到一图胜千言！

今天推荐的超实用干货是

《900套高工作模板.xls 》

3.2G高Excel可视化模板

制作精美 可直接套用

适合自用和内部培训使用

领取方式

关注我们

私信发送关键字：900

即可免费领取

资料来源于网络，公益分享，如有侵权，联系删除

新函数TOCOL太强大了，Vlookup彻底不行了，轻松解决6大难题

今天跟大家分享一个非常强大的Excel新函数——TOCOL，它可以快速的将多列数据转换为一列数据，可以帮助我们解决很多之前工作中的疑难杂症，快速提高工作效率，废话不多说，让我直接开始吧

TOCOL：将多列数据转换为一列数据

语法：=TOCOL(array, 要忽略的数据类型, 扫描模式)

第一参数：数据区域

第二参数：忽略类型：是否要忽略空白或者错误值

第三参数：扫描模式，FALSE按行扫描，TRUE按列扫描

它的第二、第三参数都是可选参数，没有特殊需求是可以忽略掉的

公式：=TOCOL(A3:B6)

公式：=SUM(TOCOL(A3:C10,3))

如果数据中存在错误值，直接使用SUM是不能求和的，我们可以直接TOCOL，将第二参数改成3，将错误值忽略掉，就能正常求和了

公式：=TOCOL(B2:B7/(A2:A7=A10),3)

A2:A7=D3是统计的条件，条件不成立就会返回FALSE，成立就会返回TRUE，可以将FALSE看做是0，TRUE看做是1，A2:A7=D3是在分母的位置，如果为0就会返回错误值。

这里只有40是满足的，所以就会返回40的结果，轻松秒杀Vlookup函数

公式：=TOCOL(C2:C7/((B2:B7=F2)*(A2:A7=E2)),3)

多条件查询原理也是一样的，我们只需要让两个条件相乘就可以了，这个的计算本质跟之前讲过的sumproduct函数是非常类似的

公式：=TOCOL(IF(B2:B4>=COLUMN(A:E),A2:A4,NA()),3)

下图下面展示的IF函数的结果，通过IF函数我们是可以将文具名称重复指定次数的，但是会存在错误值，之后用TOCOL将第二参数设置为3来忽略错误值即可

公式：=TOCOL(\’1月:3月\’!A2:A15,3)

这个公式的输入方法有些不一样，首先输入公式，然后点击1月的sheet，然后按照shift点击3月的sheet名字，这样就会选中1到3月3个sheet，之后选中对应的文具区域，可以多选一些，第二参数设置为3忽略空白与错误，点击回车，然后向右拖动公式即可

我们想要将2维表转换为1维表的显示格式，也可以借助TOCOL函数来实现。操作有些复杂，我们就来分布讲解，

1.公式：=IF(B2:D5<>\”\”,A2:A5,NA())

这个公式的作用获取每个数字对应的文具名称，公式会判断B2:D5这个区域是否不等于空值，如果条件成立就返回对应的文具名称，条件不成立就返回NA的错误值

使用TOCLO将多列数据设置为一列，这样就能将所有的文具名称都放在一列中的，月份的操作也是一样的，只需修改IF函数的第二参数为B1:D1就能将月份也设置为一列数据

公式：=TOCOL(IF(B2:D5<>\”\”,B1:D1,NA()),3)

就是TOCOL的常规用法，将多列数据设置为1列，至此就可以实现将二维表转换为1维表了，至此设置完毕

以上就是分享的全部内容，怎么样，你觉得TOCOL函数强大吗？可以留言讨论下

我是Excel从零到一，关注我，持续分享更多Excel技巧

如果你想要提高工作效率，不想再求同事帮你解决各种Excel问题，可以了解下我的专栏，WPS用户也能使用，讲解了函数、图表、透视表、数据看板等常用功能，带你快速成为Excel高手

用数据透视表合并两个表格数据，比VLOOKUP更简单易学

文章最后有彩蛋！好礼相送！

Excel秘籍大全，正文开始

如图所示的两个表格，行数不一致，顺序也不一致，要想合并两个表格，并且计算出差异，可以使用VLOOKUP函数，操作如下：

图例

使用VLOOKUP只需要一个公式：

使用VLOOKUP

=IFERROR(VLOOKUP(E2,$A$2:$B$18,2,FALSE),0)-F2

但是VLOOKUP函数很多人掌握的不熟练，今天我来讲解下使用数据透视表合并，并且计算出差异。

第一步，在两个表格的后面，各自增加一个辅助列，左边表格为A，右边表格为B。

构建辅助列

第二步，复制左边表格的内容，然后粘贴到右边表格的下面，如图所示：

把两个表格粘贴到一起

第三步，单击左边表格的任意一个单元格，插入数据透视表，然后把辅助列拖拽到列标签，把商品名称拖拽到行标签，求和项是账面数量

插入数据透视表

第四步，取消行列的汇总，选择对行列都禁用

取消汇总

第五步，点击 列标签，然后增加计算项

增加计算

第六步，在弹出的对话框中，输入名称“差异”，然后公式=A-B，单击确定。

输入内容

单击确定后，结果如下：

以上呢，就是使用透视表操作的全过程，如果你觉得没有VLOOKUP简单，那是因为你操作的还不熟练，如果你对函数不熟悉，用VLOOKUP是很容易出错的，而透视表就简单多了。

以下《900套高工作模板.xls 》免费下载，不收一分钱！

常用Excel

用Excel玩好报表

是必不可缺的技能

要知道一张好的图表

可以做到一图胜千言！

今天推荐的超实用干货是

《900套高工作模板.xls 》

3.2G高Excel可视化模板

制作精美 可直接套用

适合自用和内部培训使用

领取方式

关注我们

私信发送关键字：900

即可免费领取

资料来源于网络，公益分享，如有侵权，联系删除

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com