fbpx

Excel 小教室 – 用一行公式計算不重複資料數量,公式看不懂該怎麼辦?

平常我在 Excel 裡大多是在計算針對某個值的重複數量是多少,第一次有人問我:不重複的資料有幾筆要怎麼算,我思考了一下,大致上有二種做法,計算有資料的儲存格數量有多少,再去扣掉重複的資料,或者找出重複值有多少,再利用一點點邏輯運算就可以搞定,今天要教大家的就是第二個方法,順便訓練一下大家腦袋計算能力,後面再加碼一下,如果平常看別人的公式不太理解時,該怎麼拆解看每步驟的值,往下來看。

Excel 教學:計算不重複資料數量

首先,我們要做的就是像這樣,從一整排資料裡計算出不重複的資料有哪些,往下來看。

阿湯用的公式長這樣

=SUMPRODUCT(1/COUNTIF(C2:C13,C2:C13))

一行,二個函數搞定,分開來看大家應該都知道這二個函數的用途,但這樣合起來的意思是什麼呢,我們往下來拆解一下。

一般大家在拆解公式時,一定會先從最內層開始一個個看結果,比如 COUNTIF 那個函數是用來計算指定條件的數值,像這樣二個參數都放 C2:C13,代表的是每個值都會比對過一次,最後獲得的是 5,代表有 5 個值是相同的,到這裡還算簡單,就可以用計算儲存格有資料數量的函數 COUNTA 之後再減掉 COUNTIF 那個函數後 + 1(加這個 1 是因為有五個值相同,所以算成 1 個,所以我們要加回來 1 個)。

公式會變成,這是第二種做法,當然這是題外話,我們繼續往下來看第一個做法的拆解。

=COUNTA(C2:C13)-COUNTIF(C2:C13,C2:C13)+1

在第一個公式裡,有段 1/COUNTIF(C2:C13,C2:C13),為什麼要用 1 來除,直接解說大家會看不懂,我們往下來教大家,如果看不懂公式時該怎麼拆解步驟。

點選要拆解公式值的那一欄位後,進入公式 > 選擇「評估值公式」。

然後就會跳出一個框框,裡面會是你輸入的公式,按下評估值,往下來看。

當你按第一下時,會幫你把最內層的公式化成值顯示出來,裡面變成 1;5;1;1;1;1;5;5;1;1;5;5,回想一下 COUNTIF,C2:C13 就是循環代進去比對值相同有幾個,第一筆資料是 1,只有 1 個,所以是 1,第二筆相同的有 5 個所以是 5,以此類推,看懂了之後,我們往下再按一次評估值。

會看到計算了第二層,也就是用 1 去除,裡面的 5 通通變成了 0.2,最後用 SUMPRODUCT 全加起來,答案就是 8,大家有看懂了嗎?

思考一下第二個做法,是將重複的值找出來後去扣掉再加 1,而這個做法其實就是反過來,我們用 1 除了哪些重複的值數量,不論你值重複幾次,只要用 1 除了之後再全部加回來,還是會等於 1,有 5 個值重複就會出現 5 個 0.2,加起來等於 1,再加上原本的 7 個 1 不就是 8 了嗎?

當你遇上公式有點看不懂會變成什麼的時候,就用評估值公式這個功能一個個按下去,就會知道每一層的結果囉。

 

你可能對這些內容也有興趣

阿湯 作者:阿湯

對電腦 & 網路資訊充滿興趣與熱情、瘋了似的每日堅持發文,「阿湯」這個名字是由湯姆克魯斯而來的,雖然我沒有他帥氣,也不會演電影,但我會寫文章....

更多關於的文章》

0 留言

發表留言 »

姓名(*)
信箱(*)