☰

在 Excel 中聚合列

tag-icon • tag-icon microsoft-excel pivot-table
在 Excel 中聚合列

我有由日期和购买清单组成的数据，如下所示（逗号代表单独的列）：

**date,product1,product2,product3** 
08/11/13,oranges,apples,chips
08/11/13,grapes,oranges
08/12/13,grapes,pineapples
08/12/13,grapes,oranges

我想知道每个日期购买了多少件每种产品（并绘制图表）。

如果我只有一个产品列，那就很简单了；我只需使用数据透视表即可。但是在我的例子中，对于 8 月 11 日，我需要一种方法来计算“橘子”的数量产品1以及来自产品2...

一种方法是复制行并将数据转换为单个列，但我想避免这样做。

有没有办法做到这一点，最好不使用 VBA？

答案1

如果您的数据在 A2:D25 范围内，那么您可以使用以下数组公式：

=SUM(($A$2:$A$25=DATEVALUE("8/11/13"))*
    (($B$2:$B$25="oranges")+($C$2:$C$25="oranges")+($D$2:$D$25="oranges")))

您需要使用Control- Shift-Enter组合键输入它。

如果您有多个日期需要计算大量产品中每种产品的总数，可以执行以下操作。

假设您的产品数据位于 Sheet 1 中的 A2:D20000 范围内，日期范围位于 A 列，如您的示例所示。假设您想将产品计数放在 Sheet2 中。

在 Sheet2 中，您需要在单元格 B1、C1 等中输入您感兴趣的日期。在单元格 A2:A2000 下方，您有产品列表。在单元格 B2 中，输入以下数组公式：

=SUM((Sheet1!$A$2:$A$20000=B$1)*((Sheet1!$B$2:$B$20000=$A2)+
     (Sheet1!$C$2:$C$20000=$A2)+(Sheet1!$D$2:$D$20000=$A2)))

并将其复制到产品列表底部。这样您就可以获得第一次约会的计数。

接下来，将整列产品数量复制到第 1 行有日期的每一列。这样你就可以获得所有日期的数量。

或者，如果您同时拥有大量产品和大量日期，那么稍微复杂一点的非数组公式将为您提供更好的性能（即，花费更少的时间来计算）。

在单元格 B2 中，您应输入：

     =COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$B$2:$B$20000,$A2)+
      COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$C$2:$C$20000,$A2)+
      COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$D$2:$D$20000,$A2)

然后，将公式复制到产品列表的底部，然后将整列公式（在范围 B2：B2000 中）复制到在第 1 行中输入日期的所有列中。

相关内容