☰

Excel 函数的工作方式是否像 SQL group by + count(distinct *)？

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 函数的工作方式是否像 SQL group by + count(distinct *)？

假设我有一张包含以下数据的 Excel 表

代码 (COL A) | 值 (COL B)
==============================
  A01 | 10
  A01 | 20
  A01 |三十
  A01 | 10
  B01 |三十
  B01 |三十

是否有类似这样的 Excel 函数？

从表 GROUP BY CODE 中选择 CODE、count（不同 *）


 CODE | 值的不同计数
===================================
  A01 | 3
  B01 | 1

或者更好的是，我可以将 Excel 公式粘贴到列中C得到如下结果：

 
 代码 (COL A) | 值 (COL B) | 具有匹配代码的不同值计数 (COL C)
===============================================================================
  A01 | 10 | 3
  A01 | 20 | 3
  A01 | 30 | 3
  A01 | 10 | 3
  B01 | 30 | 1
  B01 | 30 | 1

我知道我可以使用数据透视表轻松获得此结果。但由于报告要求，我必须附加“不同计数”列添加到 Excel 表中，因此数据透视表不是一个选项。

我的最后手段是使用 Excel 宏（很好），但在此之前，我想了解 Excel 函数是否可以完成此类任务。

答案1

假设第 2 行至第 7 行中有数据，请在单元格 C2 中输入此公式，

=SUMPRODUCT(($A$2:$A$7=A2)  /  COUNTIFS($B$2:$B$7, $B$2:$B$7, $A$2:$A$7, $A$2:$A$7))

并将其向下拖动。

怎么运行的：

当SUMPRODUCT给定一个标量参数列表时，它的工作方式类似于SUM，但它将以数组作为参数，而不需要特殊的数组条目。

CODE对于与列 中的值不匹配的记录，数组将填充零A。对于匹配的记录，数组将填充1/(the number of records that have the same A and B values as this record)。因此，例如，有两个记录具有 A=A01和 B= 10，因此对于这两个记录，1/2数组中输入 (½)。将其视为重复值的一种权重。每当将这些值相加时，每个唯一 B 值的总和为 1（在示例中，两个记录的总和为 ½+½=1）。这给出了不同记录的数量。

使用示例数据的完整示例：

对于任何带有 A= 的记录A01，该公式将返回{½,1,1,½,0,0}=的总和3。
对于任何带有 A= 的记录B01，该公式将返回{0,0,0,0,½,½}=的总和1。

答案2

这里的方法比 Excellll 的方法更容易理解，但它确实需要一个额外的列。假设您的数据在第 2 行到第 7 行（A 列和 B 列），请在 C2 中输入以下内容：

=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)=1

在 D2 中：

=COUNTIFS($C$2:$C$7, TRUE, $A$2:$A$7, $A2)

并向下拖动。

怎么运行的：

COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)计算有多少行以上，包括当前 具有与当前行相同的A和B值。第一次出现值对时（第 2、3、4 和 6 行），该值为 1，重复出现上述值对的行时，该值为 1 以上（即第 5 和 7 行中为 2）。测试TRUE第一次出现每个不同值对时以及其他地方是否为 1。然后，D 列中的公式计算当前值有FALSE多少个s 。TRUEA

您可以稍微简化一下公式：

C:    =COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)

D:    =COUNTIFS($C$2:$C$7, 1, $A$2:$A$7, $A2)

当然你也可以隐藏 C 列。

答案3

我会使用 Power Pivot Excel 插件。它把不同计数当早餐吃掉了……

首先，我将使用 Power Pivot 功能区上的“创建链接表”按钮将 Excel 表添加到 Power Pivot。

然后，我将使用 Power Pivot 功能区上的数据透视表按钮来创建数据透视表，将代码 (Col A) 列拖到行标签区域，将值 (Col B) 列拖到值区域（在 Power Pivot 字段列表中）。

默认情况下，值字段将汇总为值总和 (Col B)。我将通过单击值区域中的值总和 (Col B) 条目并选择汇总依据，然后选择不同计数来更改此设置。

以下是结果的截图

不同计数示例

相关内容