使用公式创建透视表样式的报表

数据透视表会交叉汇总数据：一种类别作为行，另一种类别作为列，网格中填入总计。一个经典示例是左侧按区域排列，顶部按季度排列，每个单元格显示相应的销售额。

数据透视表非常实用，但需要手动刷新，而且位于固定区块中。公式驱动的数据透视表会在数据发生变化时自动实时重建。

在本课中，您将安排行标题、列标题，并使用一组 SUMIFS 公式自动计算每个交叉项。

我们将使用一个名为销售的工作表，其中包含以下列：A 列为区域，B 列为季度，C 列为金额，数据范围为第 2 行到第 500 行。

我们希望生成的报表如下：

• 行标签：E 列中向下列出每个不重复的区域。
• 列标签：第 1 行 F 列到 I 列横向列出 Q1、Q2、Q3、Q4。
• 主体区域：每个区域和季度组合对应的金额总计。

主体区域中的每个单元格都在回答一个问题：这个区域在这个季度卖出了多少？

行标题就是不重复的区域。将 UNIQUE 与 SORT 结合使用，让区域沿 E 列向下溢出并保持有序。

将以下公式放在 E2 中：

现在，区域会自动填充到 E2 及其下方的单元格。与汇总表一样，这份列表是整个网格所依据的锚点。

列标题是横向分布在一行中的季度。您可以手动输入 Q1、Q2、Q3、Q4，也可以将 UNIQUE 套入 TRANSPOSE，让季度水平溢出。

在 F1 中输入以下公式，即可将不重复的季度横向排列在顶部：

TRANSPOSE 会将垂直列表转换为水平列表，因此季度列会变成标题行。现在，网格的两个轴都已就位。

现在填充主体区域。每个单元格都需要计算其所在行的区域和所在列的季度对应的总计。SUMIFS 可以轻松处理两个条件。

在第一个主体单元格 F2 中输入：

这表示：当区域等于左侧的标签且季度等于上方的标题时，返回金额总计。这是数据透视表中的一个交叉项。

美元符号可以让一个公式通过复制填充整个网格。请研究以下混合引用：

• $E2 锁定 E 列，但允许行号变化，因此每一行都会读取自己的区域。
• F$1 锁定第 1 行，但允许列号变化，因此每一列都会读取自己的季度。
• $C$2:$C$500 完全锁定，因为数据区域不会发生偏移。

将 F2 复制到所有季度列以及所有区域行，每个单元格都会自动进行正确调整。

正确输入 F2 后，选中该单元格，将填充柄向右拖过所有季度列，然后向下拖过所有区域行。Excel 会为您重写相对引用部分。

• G2 单元格会变为区域 $E2、季度 G$1。
• F3 单元格会变为区域 $E3、季度 F$1。

结果就是一张完整的交叉表，每个交叉项都已计算总计。无需使用数据透视表向导，销售数据一发生变化，报表就会立即重新计算。

真正的数据透视表会显示总计。请在右侧添加一个总计列，并在底部添加一个总计行，使用普通的 SUM 对每一行或每一列进行求和。

要计算第一个区域的行总计，请将以下公式放在最后一个季度右侧的列中：

要计算列总计，请将该季度的主体单元格沿各行相加。这些边缘总计让报表更加完整，也便于读者快速检查数字是否合理。

如果您使用的工具支持此功能，就可以直接将溢出引用传递给 SUMIFS，从而无需复制公式。请将溢出的标题用作条件。

以下一个公式就能计算每个区域和季度交叉项的总计：

这里，E2# 是垂直的区域列表，F1# 是水平的季度列表。Excel 会一次性将它们配对并生成完整网格。拖动填充的方法兼容性更好，但这种方法是更简洁的现代版本。

报表不仅显示金额，还显示占比时，信息价值会更高。请添加一列，将每个区域的总计表示为总计的百分比。

如果区域行总计位于 J2，而总计位于 J10，请输入：

使用 $J$10 锁定总计后，您就可以将公式向下填充到所有区域，同时公式始终除以同一个分母。将该列设置为百分比格式，读者就能立即看出哪些区域占据主导地位。

遵循以下几个习惯，可以让公式数据透视表保持可靠：

• 引用第 2 行到第 500 行这类完整且宽裕的区域，以便包含新增行。
• 使用完整的 $ 锚点锁定数据区域；只有标题引用应当移动。
• 在下方和右侧留出空白空间，以便溢出的标题和总计有足够的扩展空间。

如果操作得当，这份报表无需维护。输入新的销售记录后，网格、总计和标签都会自动更新。

检查您对驱动公式数据透视表的混合引用的掌握程度。

您只使用公式就重新创建了一张数据透视表：

• UNIQUE 加上 SORT，在溢出列中创建了行标题。
• TRANSPOSE 将列标题横向排列在一行中。
• 带有混合引用 $E2 和 F$1 的 SUMIFS 填充了每个交叉项，既可以通过拖动完成，也可以使用 E2# 和 F1# 这样的溢出引用完成。
• SUM 添加了总计边缘。

整个网格都会实时重新计算。下一步，您将使用驱动指标的下拉菜单让仪表板实现交互。