2023年2月1日 星期三

[ Excel ] 如何使用函式篩選出唯一值

 在 Google 試算表中有提供 UNIQUE 函式,於所指定範圍列中,傳回範圍中的不重複資料列,如同下圖示例。


但在 Excel 雖有可篩選的操作,可是並沒有相對應的函式,如果要用函式的方式處理,就需要繞些遠路。首先要先統計各種類的數量,在此用到的是 COUNTIF 函式,此函式可統計我們指定範圍內,符合我們所要項目的數量,在本例我們應用來取得各別項目的數量計算,語法與執行結果如下圖。


為了要統計各類的各別有的數量,利用錢的符號『 $ 』來定住 COUNTIF 函式作用區間的上緣,而下緣則為目前在的列數,這樣下面如有新增的資料時,只要在函式列下拉即可計算,COUNTIF 計算標的,為當前列的項目於我們指定的上緣到該列時的數量,例如上圖範列的第7例為黑色,在 [ J$2:J7 ] 的範圍內共有2個黑色,而用  COUNTIF (J$2:J7 , J7) 計算出來的結果也為 2 ,這樣做的結果我們會發現,我們相當於在對各個不同的項目各自排序,由上圖可以看到,紅色有3個,分別在第2、3、11列,而統計出來的結果在 K2、K3、K11 為 1、2、3 ,剛好為其排序。

至於為什麼要排序,有了排序我們就可以捉 K 欄中顯示為 1 的列,對應到同列的 J 欄就為不重複的唯一值,在本例 K 欄為 1 的,在 J 欄分別為 紅色、黑色、紫色、黃色、藍色,共有五種顏色,所以接下來的動作,我們依此統計種類數,在N欄中如上面所講的,在 K 欄值為 1時,才進行計數,從而統計出種類數為 5 種顏色。



接下來利用 VLOOKUP 函式來將上述唯一值在 Excel 篩選出,但 VLOOKUP 要求查閱值需在資料範圍的最前列,為此,需將 M 欄的結果,放到 K欄 之前,做為 VLOOKUP 的鍵值,我們直接指定 H 欄 等為 M 欄值,這樣就完成資料範圍的整備。 


接下來要用 VLOOKUP 來搜尋不重複的值,首先要建立查閱值,在前面我們已經知道有 5 種顏色,所以 O 欄的查閱值,原本可以直接輸 1、2、3、4、5,但為了擴充和通用性,我們用 「=IF(O2<MAX(M:M),O2+1,"")」,來進行查閱值的產生,查閱值產生後就可以用 VLOOKUP 函式來找出唯一的值了,如 下圖 Q 欄所示。


為什麼要這樣做


以上為用函式求找唯一值的方式,就如同前面所說,繞了很大一段路,才求得結果,但為什麼要這樣做,應用的情境為何? 就一般來說  Excel 雖有可篩選的操作,但還是需要我們點選操作,才可達成我們所要的目的,用此方法只要將資料貼到你指定的欄位,就會自動求得所以的唯一值,而後我們可以用這結果套入其他函式進行運算,從而得到自動化的結果。

另外  Excel 的篩選操作過程中都會有縮減列數的效果,如果你的欄位是在一個大資料集中,這樣做可能會將其他欄位的資料給刪除,此時要不是複制該欄貼到其他 Sheet 進行操作,就比較適合用此方式進行操作。

結語

就個人的使用習慣上,如果遇到此類操作,會將資料移到 Google 試算表操作,但有的時侯可能是只能用 Excel 操作時,且需要較自動化的操作,個人就會用前面所述的方式進行操作,看起來設定繁瑣,但設定好後,後續使用非常自動化,提供有需求的朋友進行參考。






[Excel]如何創建自定義函數

在 Excel 內建函式中找不到自已想要的函式怎麼辦?事實上我們可以自已建一個,以下是一個簡單的Excel自定義函數示例,用於將民國年轉為西元: Function 民國轉西元(x As Double) As Double     民國轉西元 = x + 1911 End Func...