☰

使用单个匹配单元格添加和汇总多张工作表中的所有数据

tag-icon • tag-icon microsoft-excel worksheet-function
使用单个匹配单元格添加和汇总多张工作表中的所有数据

在 Sheet1 A1 上有一个带有下拉菜单的单元格，您可以在其中选择一个图块。

有时，此标题可能会出现或不会出现在 Sheet2:Sheet7 中，其下方有 30 行数据。

我希望当我更改 A1 中 Sheet1 上的图块时，该工作表会在所有 Sheet 2-7 中找到匹配的标题，并对其他工作表下方 30 行中的所有相关数据点求和。

例如：

如果从下拉列表中选择“Sheet1 A1”中的“标题 1”，则“Sheet1 A2:A31”是 Sheets 2-7 中所有具有匹配“A1”的“A2:A31”单元格的总和。请注意，在 Sheets 2-7 中，匹配数据可能不在 A 列中，而可能位于工作表的任何位置。如果“标题 1”未找到匹配项，则值为 0

答案1

将 SUMIF 和 INDIRECT 与 SUMPRODUCT 结合起来可以解决问题：

：警告：

  • 由于 OP 没有分享任何数据集/屏幕截图，因此我假设了数据。
  • 为了正确理解和可视化，我使用了较小的数据集。
  • 我的解决方案包括标准方法，对工作表中的值进行求和，有和没有下拉菜单。
  • 也许其他人会发布更好的方法。
  • 此外，对于许多工作表和更大的数据集，VBA 应该是一个不错的选择。

在此处输入图片描述

在此处输入图片描述

在此处输入图片描述

怎么运行的：

  1. 在范围 B31:B31 中输入工作表名称。

  2. 单元格 C38 中的第一个公式：

    =SUMPRODUCT((SUMIF(INDIRECT("'"&$B$31:$B$32&"'!"&"$b$61:$b$64"),B38,INDIRECT("'"&$B$31:$B$32&"'!"&"$c$61:$c$64"))+SUMIF(INDIRECT("'"&$B$31:$B$32&"'!"&"$s$2:$s$5"),B38,INDIRECT("'"&$B$31:$B$32&"'!"&"$t$2:$t$5"))))

  1. 在单元格 E38 中创建下拉菜单：

  2. 单元格 G38 中的公式：

     =SUMPRODUCT((SUMIF(INDIRECT("'"&$B$31:$B$32&"'!"&"$b$61:$b$64"),$E$38,INDIRECT("'"&$B$31:$B$32&"'!"&"$c$61:$c$64"))+SUMIF(INDIRECT("'"&$B$31:$B$32&"'!"&"$s$2:$s$5"),$E$38,INDIRECT("'"&$B$31:$B$32&"'!"&"$t$2:$t$5"))))

注意：

  • 根据需要调整公式中的单元格引用。

相关内容