Excel随笔.20191025.1--按条件去重计数

例1

实现功能：

• 对excel中的透视表(pivot table)中的某列，去重计数；
• 当筛选透视表时，去重计数结果可以自动识别透视表的变化；

实现结果如下：

• 对透视表中的第一列(Personnel Number)计数，结果是6(下图1所示）
• 筛选切片器，选择AAA，结果是 (下图2所示）

图1
图2
实现所需函数：

• OFFSET
• COUNTA
• SUMPRODUCT
• COUNTIF

具体操作步骤：

• 1.公式–命名管理–新建
  • 1.1.输入名字(Personnel_Number,根据自己需要命名，不要有空格)
  • 1.2.输入公式如下：

=OFFSET('Summary Table'!$B$7,,,COUNTA('Summary Table'!$B:$B)-3,1)
#offset(需要计数区域的起始单元格,单元格向下移动几行，单元格向右移动几列，需要计数区域的行数，需要计数区域的列数）
#offset(本例中需要从Summary Table的B7单元格开始，不需要移动，不需要移动，B列行数减3，1列）

 - 具体如下图

• 2.在空单元格输入公式如下：

=SUMPRODUCT(1/COUNTIF(B:B,Personnel_Number))
#去重计数，结果是6

例2

实现功能：

• 在例1的基础上，在一定的条件下的去重计数
• 本例中需要满足日期列>=today()的条件下，对透视表中的B列，去重计数
• 本例中的today()是2019年10月25日

实现结果如下图：

实现步骤：

• 1.重复例1中的具体操作步骤1，对C列区域命名(我命的名字是First_Available_Date,在下面的公式中会引用)

=OFFSET('Summary Table'!$C$7,,,COUNTA('Summary Table'!$B:$B)-3,1)

• 2.在空单元格输入公式如下:

=SUM(IFERROR(1/COUNTIFS(Personnel_Number,Personnel_Number,First_Available_Date,">="&TODAY())*(First_Available_Date>=TODAY()),))
#>=today()条件下，有5行结果，其中有2行是重复的，所以结果为4

!!注意：公式输入之后，需要同时按下Ctrl+Shift+Enter，会看到公式最外层自动生成大括号，如下图：

此时，去切片器中进行筛选的情况下，依然是有效的，如下图：