私信回複關鍵詞【工具】，獲取Excel高效小工具合集！

讓你的Excel效率開挂~(◦˙▽˙◦)

前段時間遇到這樣一個問題，讓我很頭疼。

頭疼的原因有 3 點：

❶ 問題描述不清晰，理解起來困難；

❷ 去重複計算數量，函數公式實現難度大；

❸ 提問的是個男生。

我嘗試着把問題精簡了一下，是這樣的，你就湊活着看吧。

一列「用戶 ID」，一列「活動日期」，現在想統計，每個用戶參加活動的天數。

因為用戶可能在 1 天中參加多次活動，所以要根據「用戶 ID」對「活動日期」去除重複，然後再計數。

明白了嗎？

明白了，咱們就開始幹！

關于去除重複計數，也就是統計唯一值，Excel 中有一個經典的用法。

使用 SUMRODUCT 和 COUNTIF/COUNTIFS 函數完成。

= SUMPRODUCT(1/COUNTIF(統計區域,統計區域))

現在看不懂沒關系，我們通過這個案例，一起走一遍這個過程。

❶ COUNTIFS 統計數量。

首先是統計數量，因為這裡有「用戶 ID」「活動日期」兩列數據，所以我們用 COUNTIFS 函數。

完成公式如下：

=COUNTIFS(C2:C16,C2:C16,B2:B16,H2)

計算結果：

= {0;0;0;0;0;0;0;0;0;0;0;1;1;1;1}

注意：這裡有一個數組的用法，在判斷條件的參數中使用數組，那麼計算的結果，也是對應數量的數組。

❷ 數量求倒數。

接下來，用 1 除以計數結果，獲取對應的倒數。

完成公式如下：

=1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2)

計算結果：

={#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;1;1;1}

▲左右滑動查看

因為「1/0」會出現「#DIV/0!」的錯誤，所以公式外面，再加一個 IFERROR 容錯：

=IFERROR(1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2),0)

計算結果：

={0;0;0;0;0;0;0;0;0;0;0;1;1;1;1}

這一步是非重複計數的關鍵操作，結合下一步倒數求和，會更容易理解。

❸ SUMPRODUCT 倒數求和。

因為 SUM 函數不支持數組操作，所以這裡使用 SUMPROUDCT 進行求和。

完成公式如下：

=SUMPRODUCT(IFERROR(1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2),0))

計算結果：

= 4

到這一步，你可能就明白求倒數的意義了。

如果相同數據出現了 2 次，那麼計數過程就是「1/2 1/2 =2」；

如果出現了 3 次，就是 3 個「1/3」相加「=3」；

其他次數以此類推，即實現了非重複計數。

❹ 增加「用戶 ID」判斷。

但是上一步計算結果，顯然是錯的，QY1 的去重計數，應該是 1 才對啊。

這是因為計數的過程，沒有對用戶進行限制。

因為 QY1 有「10/4」的記錄，所有的「10/4」都被統計到 QY1 用戶上了。

所以需要再增加一個用戶條件的判斷，這裡使用 EXACT 函數實現。

完整公式如下：

=EXACT(B2:B16,H2)

計算結果：

={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

▲左右滑動查看

EXACT 的作用，是判斷兩個數值是否相等。

因為 EXACT 中也是引用了區域，所以計算結果是一個 TRUE 和 FALSE 的數組。

接下來，是把 EXACT 的計算結果，作為條件添加到前面的公式中。

方法很簡單，和第 1 步的計數過程相乘就可以了。

完成公式如下：

=SUMPRODUCT(IFERROR(1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2)*EXACT(B2:B16,H2),0))

▲左右滑動查看

計算結果：

=1

這樣就把非當前用戶的統計給去掉了，也就得到了最終的結果。

方法 1 是傳統的方法，經過一番折騰，最終算是圓滿完成了需求。

但是正如你所感受到的，傳統函數公式的思路太古怪，一般人很難想到用 1/次數的方法，來做去重計數。

這也是函數公式難學的主要原因。

正常用戶的思路，不應該是這樣的嘛？

❶ 篩選用戶 ID

❷ 去除重複值

❸ 統計數量

這個正常的思路，用傳統公式是很難實現的。

但是 Office 365 中新增的 FILTER 和 UNIQUE 函數，讓這個過程變的簡單，變的正常了。

❶ 篩選用戶 ID。

使用新增的 FILTER 函數，可以輕松的根據「用戶 ID」篩選對應的記錄。

完整公式如下：

=FILTER($C$2:$C$16,$B$2:$B$16=H4)

計算結果：

={43739;43739;43739;43740;43741;43742}

FILTER 的作用就是篩選符合條件的記錄。

（日期返回的是數字格式，所以變成了 43739 的樣子。）

❷ 去除重複值。

Office 365 中新增的 UNIQUE 函數，就是用來去除重複值的。

完整公式如下：

=UNIQUE(FILTER($C$2:$C$16,$B$2:$B$16=H4))

計算結果：

={43739;43740;43741;43742}

注意到了嗎？FILTER 篩選出來的重複值，被 UNIQUE 函數一下子去除掉了。

❸ 統計數量。

有了去重後的篩選結果，統計數量太簡單，就是普通的 COUNTA 函數嘛。

完整公式如下：

=COUNTA(UNIQUE(FILTER($C$2:$C$16,$B$2:$B$16=H4)))

計算結果：

= 4

簡單的 3 個步驟，符合常規思路，你肯定一下子就學會了，不是嗎？

03總結

溫馨提示：

FILTER 和 UNIQUE 函數，目前隻有 Office 365 的版本才有，而且需要參加「預覽版體驗計劃」。

今天的非重複計數學會了吧，别忘了點個贊！

私信回複關鍵詞【工具】，獲取Excel高效小工具合集！

讓你的Excel效率開挂~(◦˙▽˙◦)

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部