按年份和 ID 分组然后求和

Question

我有一张包含 3 列的表格 - ID、年份和值。表格示例：

图片中的一些行：

id, year ,value
 1, 2017 ,19
 1, 2017 ,21
 1, 2017 ,22
 1, 2018 ,1
 1, 2018 ,30
 1, 2018 ,15
 1, 2018 ,17
 1, 2018 ,2
 1, 2018 ,18
 1, 2019 ,23
 1, 2019 ,3
 1, 2020 ,6
 1, 2020 ,9
 2, 2017 ,16
 2, 2017 ,14
 2, 2017 ,13
 2, 2017 ,12
 2, 2017 ,22
 2, 2017 ,20
 2, 2017 ,23
 2, 2017 ,1
 2, 2017 ,25
 2, 2018 ,17
 2, 2019 ,10
 2, 2019 ,5
 2, 2020 ,19
 2, 2020 ,16
 2, 2020 ,14
 3, 2020 ,2
 3, 2020 ,9
 3, 2020 ,11

我需要对具有相同 ID 和年份的所有值求和（例如 - ID=1，年份=2020 -> sum=15）附加的表格比原始表格小得多（原始表格有近 12000 行）因此手动检查需要大量工作。

有没有简单的方法可以做到这一点？

Answer 1

在 Excel 365 中，您可以使用动态数组执行此操作。

方法 1 — 宽格式

A1在单元格中给定一个名为的表格，Table1其标题为id、year和value，首先通过在单元格中输入以下公式来引入唯一 ID E2：

=UNIQUE(Table1[id])。

接下来，通过在单元格中输入此公式来提取唯一年份F1：

=TRANSPOSE(UNIQUE(Table1[year]))。

最后，通过在单元格中输入以下公式来计算总和F2：

=SUMIFS(Table1[value],Table1[id],E2#,Table1[year],F1#)。

结果如下：

方法 2 — 长格式

A1给定一个单元格名为 的表格，Table1其标题为id、year和，首先通过在单元格中输入以下公式引入和value的唯一组合：idyearE2

=UNIQUE(Table1[[id]:[year]])

接下来使用SUMIFS()查找单元格中的总和G2：

=SUMIFS(Table1[value],Table1[id],INDEX(E2#,,1),Table1[year],INDEX(E2#,,2))。

在这里我使用INDEX(E2#,,1)和INDEX(E2#,,2)从过滤表中获取唯一的 ID 和年份。

最后，在单元格中按E1Enter=Table1[#Headers]复制标题。您的最终分析结果如下所示：

方法 3 — 数据透视表

如果您没有使用 Excel 365，也可以通过插入数据透视表并进行如下配置轻松完成此操作：

Answer 2

感谢您以可用（文本）形式提供数据。

我认为 Power Query（Excel 2010+ 中可用）的数据透视表看起来比经典 Excel 生成的数据透视表更好（并且如果您想做其他事情，语言也更灵活）。

将光标放在表格中的某个单元格上：

• Data --> Get & Transform --> from Table/Range
• 在打开的 PQ 编辑器中，选择Year列
  • 变换 --> 枢轴
    • 选择value“值”列
    • 选择Sum聚合值函数

• Close and Load To

您已完成：