☰

根据列表的子集计算唯一值

tag-icon • tag-icon microsoft-excel worksheet-function countif conditional-statements
根据列表的子集计算唯一值

我正在尝试计算特定员工有多少个唯一主管。（请参阅下面的示例数据集。）

我能够以一种迂回的方式来做到这一点，但我想知道你们中是否有任何专家能够找出一个一步式公式来完成同样的工作。

我的方法是：

  1. 将工作人员和主管连接成单个字符串：=CONCATENATE(A2,"-",B2)

  2. 使用if带有的语句，countif对 staff-supervisor 字符串的第一个实例赋予 1，对之后的任何实例赋予 0：=IF(COUNTIF($D$2:$D2,D2)>1,0,1)

  3. 用于sumif添加此列的总和，但仅针对特定的工作人员：=SUMIF($A$2:$A$13,A2,$E$2:$E$13)

正如您所看到的，求和，如果列到达正确的值，因为手动输入独立主管人数柱子。

Data                 "Goal"                         Calculated
------------------   ----------------------------   --------------------------------------
Staff | Supervisor | Number of unique supervisors | Concat | 1 if first instance | Sum if 
------------------------------------------------------------------------------------------
Anne  | F          | 1                            | Anne-F | 1                   | 1      
Bob   | A          | 2                            | Bob-A  | 1                   | 2      
Bob   | B          | 2                            | Bob-B  | 1                   | 2      
Mary  | D          | 2                            | Mary-D | 1                   | 2      
Mary  | D          | 2                            | Mary-D | 0                   | 2      
Mary  | E          | 2                            | Mary-E | 1                   | 2      
Sue   | G          | 2                            | Sue-G  | 1                   | 2      
Sue   | H          | 2                            | Sue-H  | 1                   | 2      
Tom   | I          | 2                            | Tom-I  | 1                   | 2      
Tom   | I          | 2                            | Tom-I  | 0                   | 2      
Tom   | J          | 2                            | Tom-J  | 1                   | 2      
Tom   | J          | 2                            | Tom-J  | 0                   | 2

答案1

在 中C2，公式抄下来：

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

在此处输入图片描述

答案2

如果您拥有具有FILTER和UNIQUE功能的 Office 365，则可以使用：

=COUNTA(UNIQUE(FILTER($B$2:$B$13,A2=$A$2:$A$13)))

答案3

您需要一些数组（CSE）公式来生成摘要类型的输出，它还可以修复该问题：

在此处输入图片描述

怎么运行的：

  • 单元格中的数组（CSE）公式H161：

    {=IFERROR(INDEX($F$161:$F$172, MATCH(0,COUNTIF($H$160:H160, $F$161:$F$172), 0)),"")}

  • 在单元格中输入此数组（CSE）公式I161：

    {=IF(SUMPRODUCT((($F$161:$F$172=H161))/COUNTIFS($F$161:$F$172,$F$161:$F$172&"",$G$161:$G$172,$G$161:$G$172&""))=0,"",SUMPRODUCT((($F$161:$F$172=H161))/COUNTIFS($F$161:$F$172,$F$161:$F$172&"",$G$161:$G$172,$G$161:$G$172&"")))}

注意：

  • 完成配方Ctrl+Shift+Enter& 向下填充。

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

相关内容