Excel -按重复和总和或平均值分组

Question

我有一张表格(来自Google Analytics的数据)：

 A        |B      |C        |D
 URI      |Views  |ViewsUn. |Time
 -----------------------------------------------------
 /uri-1   |123    |100      |10
 /uri-2   |200    |180      |5.2
 /uri-1   |50     |35       |5

我想要按URI分组，并添加视图和唯一视图的数量，并计算平均时间，我希望得到以下输出：

 A        |B      |C        |D
 URI      |Views  |ViewsUn. |Time
 -----------------------------------------------------
 /uri-1   |173    |135      |7.5
 /uri-2   |200    |180      |5.2

我正在尝试使用SUMPRODRUCT，但是看起来我必须已经有了所有的uri-1而没有重复，然后循环这个新的数组。

有没有更快的方法呢？还是通过接口/功能？

Answer 1

有多种方法可以做到这一点。一个是透视表；另一个是用公式创建你自己的透视表。我个人更喜欢后者，因为透视表不会在数据添加到源中时自动刷新，而且仅仅通过查看透视表来判断它是否已经刷新并不容易。

要创建数据透视表的...

选择数据，然后在插入选项卡中选择透视表：

​

​

设置透视表字段，如下所示：

​

​

使用对公式执行相同的操作，以使内容始终保持最新...

如果您使用的是Microsoft 365，则可以使用独特的功能。假设您的数据在A到D列中，输入以下函数：

Cell G1 =UNIQUE(A:A)
Cell G1 ="URI"
Cell G2 =IF($F2="","",SUMIF($A:$A,$F2,$B:$B))     [Drag this down]
Cell H1 ="Views"
Cell H2 =IF($F2="","",SUMIF($A:$A,$F2,$C:$C))     [Drag this down]
Cell I1 ="ViewsUn"
Cell I2 =IF($F2="","",AVERAGEIF($A:$A,$F2,$B:$B)) [Drag this down]
Cell J1 ="Time"
Cell J2 =IF($G2="","",AVERAGEIF($A:$A,$G2,$B:$B)) [Drag this down]

在上面的策略中，当您继续添加其他URI时，该表将保持活动或最新状态，只要您将公式拖下以覆盖可能填充的所有可能的URI行，但IF语句确保在未检测到URI时它们为空。

如果你使用的不是Microsoft 365，你将无法访问该独特功能。在这种情况下，还有其他几种获取唯一列表的方法。在您的情况下，我可能会执行以下操作：

Cell F1 ="Index"
Cell F2 =1
Cell F3 =$F2+1           [Drag this down]
Cell G1 ="URI"
Cell G2 =" /uri-"&F2     [Drag this down]
Columns H:J as above