mylogs.cn

Excel 查找公式越写越乱？用 Power Pivot 管住多张表

发布于

用 Excel 做报表的人大多遇到过这样的场景：交易、分类、账户等数据分散在好几张表，为了出一份汇总，只能不停地写 XLOOKUP，把需要的信息一列列拉进主表。表越堆越大，重复数据越来越多，报表还没做出来，文件先乱成一团。

Excel 查找公式越写越乱？用 Power Pivot 管住多张表
图片来源：How-To Geek

最常见的补救办法，是把主表复制到新工作表，再用 XLOOKUP 一列列拉取分类名称、预算分组、账户名称和账户类型，前后要写四条查找公式，才凑得出一张能做透视表的宽表。结果就是：在原始表、分类表、账户表之外，又多了一张重复数据的大表和一张报表，文件体积与混乱度一起翻倍。

How-To Geek 的 Excel 撰稿人分享了一种更干净的做法：用 Power Pivot 的「数据模型」直接告诉 Excel 各表之间的关系，不必先把它们合并成一张大表。

一、先确认版本并启用插件

  1. Power Pivot 仅在 Excel for Microsoft 365 和 Excel 2016 及以后版本的 Windows 桌面版中提供，网页版不支持。
  2. 依次点击「文件」→「选项」→「加载项」，在底部「管理」下拉框选「COM 加载项」，点「转到」。
  3. 在弹出窗口中勾选「Microsoft Power Pivot for Excel」，确定后功能区会出现「Power Pivot」选项卡。

二、把多张表加入数据模型

  1. 选中第一张表（如交易表），切到「Power Pivot」选项卡，点「添加到数据模型」。
  2. 对分类表、账户表重复同样操作，三张表都会进入同一个数据模型，且各自保持独立。

三、用图表视图建立表间关系

  1. 打开「Power Pivot」窗口，切到「图表视图」。
  2. 把交易表的 CategoryID 字段拖到分类表的 CategoryID 上，建立「交易表 → 分类表」的关系。
  3. 再把交易表的 AccountID 拖到账户表的 AccountID 上，建立「交易表 → 账户表」的关系。

四、直接基于数据模型出报表

建立关系后，插入数据透视表时直接以「数据模型」为数据源，行放分类名称、列放账户名称、值放金额，即可得到各部门在各账户的花费分布，全程无需新增任何查找列，也不会再复制一遍交易数据。

注意事项：Power Pivot 的「值字段设置」里自带「非重复计数」汇总方式，是普通透视表没有的功能；若所用版本不支持 Power Pivot，也可以用 Power Query 合并相关表，得到类似的干净报表。

来源：How-To Geek

相关文章

TAG：