方格精選

三招移除重複資料

更新於 發佈於 閱讀時間約 9 分鐘

來出個題目給你:

raw-image

要怎麼知道售價的總和?簡單,選取 C 欄後看右下角的探索窗格、或是用 SUM 函式取得 C 欄的總和就可以了。可是如果再把這張表看得細一點,你會發現 RR003 這筆資料重複了好幾次!我們以為這次賺了很多錢,但其實這不是正確的總和。

這就是為什麼移除重複資料很重要。重複的資料有可能會讓我們做出錯誤的分析,進而做出錯誤的決定,在進行資料分析之前一定得做的一大步驟。

那麼,這次我會介紹「移除重複」功能UNIQUE條件式格式這三招,幫你甩開重複的資料。我們開始吧!

我把這次用到的試算表放這了,歡迎複製一份拿去練習唷!


「移除重複內容」功能

優缺點

👍 可快速處理大筆資料
👍 可選擇要分析的資料欄
👎 只能處理欄內重複的資料、不能分析列內重複的資料

如果你用這個功能,試算表會幫你留下第一筆資料,並把這之後重複的都移除。另外,這個功能會無視大小寫、一律去除重複的資料(例如「Apple」跟「apple」會被視為一樣的資料)。

打個比方,我想移除下面這張表裡的重複內容:

raw-image

你可以看到第 12 到第 20 列的資料跟上面的重複了。

我們就來用「移除重複內容」功能,把重複的資料拿掉。選取你的資料範圍(A 欄到 E 欄),再到工具列的「資料」>「移除重複內容」:

raw-image

會跳出來一個視窗:

raw-image

如果你的資料有標題列,建議把第一個方塊勾起來,這樣試算表就可以讀到你的標題列,可以更好搜尋:

raw-image

這邊有個「要分析的資料欄」,預設是全部勾選,這代表試算表會去分析、比對每一欄(A、B、C、D、E)內的資訊。回頭看看這張表:

raw-image

如果你在剛剛的視窗勾選了全部的方塊,試算表就會以整列為標準來分析資料。這麼一來,第 12 列(編號 A3 的藍妤苓)以後的資料會被視為重複,因為和前面的資料完全一樣。執行結果就會像這樣:

raw-image

如果在剛剛的視窗只勾選 E 欄的客戶類別,試算表就只會分析 E 欄內是否有重複的資料。這麼一來,就會是這個結果:

raw-image

你可以依照你的需求,選擇要勾選的欄位,可以多嘗試幾次,看看哪種效果最適合你。





UNIQUE 函式

優缺點

👍 函式簡單、可和其他函式組裝併用
👍 可處理動態資料
👍 可處理列裡的重複資料(需要先轉置)
👎 函式結果不能直接編輯
👎 不能指定要分析的欄位

用了 UNIQUE,試算表一樣會幫你輸出第一筆資料、但結果不能編輯。另外,UNIQUE 不會移除拼字相同、大小寫不同的重複資料,例如「Apple」跟「apple」對 UNIQUE 來說會被視為不一樣的資料。

語法相當單純:

=UNIQUE(要分析的範圍)

我用同一組表格示範:

raw-image

假設我們想在 G1 產生結果,在儲存格寫下:

=UNIQUE(A:E)
raw-image

Enter 敲下去,來看看結果!

raw-image

搞定囉,就這麼簡單!但我想提兩個小缺點,給你斟酌一下:

一:UNIQUE 的結果沒辦法編輯

如果你想要在 UNIQUE 的結果上編輯,會出現 #REF! 錯誤:

raw-image

這是因為 UNIQUE 會抓取指定範圍內的資料後輸出結果,如果有任何新資料在那,就會擋住 UNIQUE 產生資料了。

二:UNIQUE 不能分析資料欄

就如同你剛剛看到的,UNIQUE 沒有其他設定欄位的參數能輸入。如果你有這個需求,可以考慮「移除重複功能」或是等等會提到的「條件式格式」。


移除單列的重複資料

不過 UNIQUE 還有個很方便的應用方式,它不僅能分析欄、也還能分析列的重複資料!舉例來說:

raw-image

第二列的蘋果、香蕉、西瓜重複了好幾次。

要怎麼用?因為 UNIQUE 只會回傳裡面唯一的值,而不是,我們得先把列轉置成欄(請參考怎麼欄列互換(轉置)?)。這邊我用 TRANSPOSE 做示範。

在 A5 輸入:

=UNIQUE(TRANSPOSE(A2:H2))

就會得到:

raw-image

搞定!如果你希望結果是是在列上顯示,你也可以在 UNIQUE 外面再包一層 TRANSPOSE 回去:

=TRANSPOSE(UNIQUE(TRANSPOSE(A2:H2)))

完成!

raw-image


(2024/06/28 更新)讀者也提供了一個好方法:

=UNIQUE(1:1, 1)
=UNIQUE(1:1, TRUE)

將你想要移除重複資料的列放在第一個參數,並在之後寫上 1 或是 TRUE,這樣也可以喔!


移除多列的重複資料

如果是多列資料要移除呢?

raw-image

也很簡單,我們只要把所有的值都合併到同一欄位、再丟給 UNIQUE 處理就可以了。這邊會用到 FLATTEN 函式,有了它、就可以把範圍內的值一欄一欄抓起來、再放在一起,就像是 flatten 字面上「攤平」的意思。

先把 A2 到 H10 併到同一欄:

=FLATTEN(A2:H10)
raw-image

再用 UNIQUE 包起來就好了:

=UNIQUE(FLATTEN(A2:H10))
raw-image


馬上就能用的組合技

另外,UNIQUE 也常拿來跟 QUERY 或 IMPORTRANGE 綁一起,提供兩份食譜給你參考:

移除 QUERY 的重複結果

=UNIQUE(QUERY(資料!A:D, "SELECT *"))

移除 IMPORTRANGE 的重複結果

=UNIQUE(IMPORTRANGE("https://...", "資料!A:D"))


條件式格式

優缺點

👍 可以細膩搜尋比較,斟酌哪個該刪、哪個不該刪
👍 可用視覺輔助、一有重複就會提醒
👎 面對較大筆的資料容易讓試算表卡住
👎 只能用一個欄作為分析對象

用條件式格式的功能可以讓重複的資料「亮」起來,你可以再依此決定哪筆資料要留、哪筆不要留。我在和同事分享試算表的時候,會在特定的欄上設定條件式格式,這麼一來,如果他們不小心放了重複的資料,他們就會馬上知道了。另外,使用這個功能時,試算表會無視大小寫字母,像是 Apple 跟 apple 都會被視為重複的資料。

不過,我想再提兩個小缺點:

  • 資料筆數較多的時候,條件式格式會稍稍拖慢試算表的處理速度。在執行之前可以看看資料的大小、再考慮一下要不要用這個功能。
  • 條件式格式只能用一個欄作為分析對象,也就不會像是「移除重複內容」一樣可以選定要分析的欄、UNIQUE 可以分析每個欄了。


使用步驟

選取資料範圍,工具列點選「格式」>「條件式格式設定」:

raw-image

你應該會看到右手邊會出現一個窗格:

raw-image

在「儲存格符合以下條件時套用指定格式」的下拉式選單裡,選擇「自訂公式:」。

raw-image

假設我們要找 A 欄裡重複的資料好了。在「值或公式」那邊,我們輸入:

=COUNTIF($A$1:$A1, A1) > 1

來看看效果:

raw-image

A 欄裡重複的資料「亮」起來了!你也可以繼續編輯你的格式,要變粗體、斜體、加底線、改字體顏色、儲存格顏色都可以。

如果你想要讓整列都出現條件式格式,你可以修改一下剛剛的那串:

=COUNTIF($A$1:$A1, $A1) > 1

執行結果就像這樣:

raw-image

你就可以從這些亮起來的重複資料裡,手動刪除你不要的了。

如果資料很多、不想一直滑,你可以再搭配篩選器,依顏色篩選,篩選出重複的資料,再手動刪除。

設定篩選器的步驟也很簡單。先選取資料範圍,點選工具列,「資料」>「建立篩選器」:

raw-image

你會看到第一列的每個儲存格出現了像是倒「三」的圖案。

在你剛剛條件式格式指定的欄位(以這次的例子來說,A 欄),點那個圖案後,選「依顏色篩選」>「填滿顏色」> 你剛剛指定的顏色。

raw-image

這樣就篩選好了:

raw-image


綜合比較

感謝你看到這邊!我把今天這三招的優缺點統整成一張表格:

raw-image

取決你的資料和清除的需求,你可以綜合比較這三個方法,找到一個比較適合的,加油!


如果你喜歡這次的文章,歡迎你透過這些方法支持我:
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍,即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍,加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作!

想要看更多文章,歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔!

我是喜特先生,Mr. Sheet,我們下個教學見!



留言
avatar-img
留言分享你的想法!
程騰榮-avatar-img
2024/06/27
UNIQUE 也是能回傳 "列" 裡面唯一的值,加個 1 就行。例如:UNIQUE( 1:1 ,1 )
喜特先生 Mr. Sheet -avatar-img
發文者
2024/06/28
程騰榮 這個也實測成功了!等等再加入教學,感謝分享!
程騰榮-avatar-img
2024/06/06
的確使用 條件式格式 會大大影響執行速度,在 Excel 也會。
喜特先生 Mr. Sheet -avatar-img
發文者
2023/10/23
removeDuplicates():移除重複資料提及了這篇文章,趕快過去看看吧!
avatar-img
喜特先生官方沙龍
19.3K會員
153內容數
簡潔,快速,有效, 讓你的日常生活、工作生產力大提升! ___ 快按「加入」,馬上追蹤所有喜特先生的更新,有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱,還有 Google 試算表疑難雜症的解題分享唷!💪
2025/04/20
請你試著在 Google 試算表的儲存格上打這個,按下 Enter: =WHATTHEFOXSAY() 會有神奇的事情發生喔 ✨
Thumbnail
2025/04/20
請你試著在 Google 試算表的儲存格上打這個,按下 Enter: =WHATTHEFOXSAY() 會有神奇的事情發生喔 ✨
Thumbnail
2024/06/02
上次介紹了 REPLACE 函式,可以用來取代儲存格內的特定文字。其實我們還有一個相似的函式叫 SUBSTITUTE,也有取代的功能,不過當然有一點不一樣的地方!今天會分享語法的範例,還有跟 REPLACE 的差異在哪。一起來看看!
Thumbnail
2024/06/02
上次介紹了 REPLACE 函式,可以用來取代儲存格內的特定文字。其實我們還有一個相似的函式叫 SUBSTITUTE,也有取代的功能,不過當然有一點不一樣的地方!今天會分享語法的範例,還有跟 REPLACE 的差異在哪。一起來看看!
Thumbnail
2024/05/25
這是文字處理基礎函式的第四篇文章,今天要來介紹 REPLACE 函式! REPLACE 可以取代掉儲存格內的文字,今天會分享一下它語法怎麼寫、也有兩個實際應用的案例。一起來看看! REPLACE 語法 REPLACE 的語法長了一點點,有四個參數要設定: =REPLACE(要取代
Thumbnail
2024/05/25
這是文字處理基礎函式的第四篇文章,今天要來介紹 REPLACE 函式! REPLACE 可以取代掉儲存格內的文字,今天會分享一下它語法怎麼寫、也有兩個實際應用的案例。一起來看看! REPLACE 語法 REPLACE 的語法長了一點點,有四個參數要設定: =REPLACE(要取代
Thumbnail
看更多
你可能也想看
Thumbnail
孩子寫功課時瞇眼?小心近視!這款喜光全光譜TIONE⁺光健康智慧檯燈,獲眼科院長推薦,網路好評不斷!全光譜LED、180cm大照明範圍、5段亮度及色溫調整、350度萬向旋轉,讓孩子學習更舒適、保護眼睛!
Thumbnail
孩子寫功課時瞇眼?小心近視!這款喜光全光譜TIONE⁺光健康智慧檯燈,獲眼科院長推薦,網路好評不斷!全光譜LED、180cm大照明範圍、5段亮度及色溫調整、350度萬向旋轉,讓孩子學習更舒適、保護眼睛!
Thumbnail
創作者營運專員/經理(Operations Specialist/Manager)將負責對平台成長及收入至關重要的 Partnership 夥伴創作者開發及營運。你將發揮對知識與內容變現、影響力變現的精準判斷力,找到你心中的潛力新星或有聲量的中大型創作者加入 vocus。
Thumbnail
創作者營運專員/經理(Operations Specialist/Manager)將負責對平台成長及收入至關重要的 Partnership 夥伴創作者開發及營運。你將發揮對知識與內容變現、影響力變現的精準判斷力,找到你心中的潛力新星或有聲量的中大型創作者加入 vocus。
Thumbnail
接續著上次介紹過的 removeDuplicates():移除重複資料,這次也想介紹一個在數據清理時,很基本、很簡單的一步,trimWhitespace(),功能相近於「移除多餘空白」功能以及 TRIM 函式(延伸閱讀:裁剪空格與 TRIM)。來看看怎麼操作!
Thumbnail
接續著上次介紹過的 removeDuplicates():移除重複資料,這次也想介紹一個在數據清理時,很基本、很簡單的一步,trimWhitespace(),功能相近於「移除多餘空白」功能以及 TRIM 函式(延伸閱讀:裁剪空格與 TRIM)。來看看怎麼操作!
Thumbnail
篩選是EXCEL一個非常基礎且實用的功能,但如果當資料有合併儲存格的時候,這時就會有一些小小的問題產生。 竟然篩選芭樂後,芭樂有3筆資料,卻只出現1筆,其他兩筆就這樣被篩選吃掉了😫 其實只要是合併儲存格就會有這樣的問題產生,原因是合併之後的儲存格只有第一格有資料,其他的儲存格都是空格,所以
Thumbnail
篩選是EXCEL一個非常基礎且實用的功能,但如果當資料有合併儲存格的時候,這時就會有一些小小的問題產生。 竟然篩選芭樂後,芭樂有3筆資料,卻只出現1筆,其他兩筆就這樣被篩選吃掉了😫 其實只要是合併儲存格就會有這樣的問題產生,原因是合併之後的儲存格只有第一格有資料,其他的儲存格都是空格,所以
Thumbnail
如果遇到下圖的狀況,需要再每個姓名的合併儲存格新增5月的儲存格,遇到這種狀況你會怎麼做呢? 如果點選4月-->右鍵-->插入,會變成在三月四月中間差一個空白列,這樣每次都要手動修該內容跟順序😱 如果點在下一個1月-->右鍵-->插入,則會破壞合併除儲存格😭 資料如果好幾百筆好幾千筆該怎麼做呢?其
Thumbnail
如果遇到下圖的狀況,需要再每個姓名的合併儲存格新增5月的儲存格,遇到這種狀況你會怎麼做呢? 如果點選4月-->右鍵-->插入,會變成在三月四月中間差一個空白列,這樣每次都要手動修該內容跟順序😱 如果點在下一個1月-->右鍵-->插入,則會破壞合併除儲存格😭 資料如果好幾百筆好幾千筆該怎麼做呢?其
Thumbnail
不曉得大家有沒有使用篩選工具後,想刪除特定表格,卻把隱藏的表格也給刪除的經驗呢?這篇文章想與你分享如何只刪除可見表格(保留隱藏表格),以及如何在Excel崁入隱藏版按鈕,一秒選取篩選的可見儲存格。
Thumbnail
不曉得大家有沒有使用篩選工具後,想刪除特定表格,卻把隱藏的表格也給刪除的經驗呢?這篇文章想與你分享如何只刪除可見表格(保留隱藏表格),以及如何在Excel崁入隱藏版按鈕,一秒選取篩選的可見儲存格。
Thumbnail
情境 M小姐經常會在Excel報表中,利用輔助欄位進行計算,這些輔助欄位最後都會被【隱藏】起來,M小姐常常需要將計算後的結果,填入到另外一張報表中,這時進行複製貼上後,總是會把這些隱藏的輔助欄位給貼上來,M小姐應該怎麼複製,才能略過這些隱藏的範圍呢?
Thumbnail
情境 M小姐經常會在Excel報表中,利用輔助欄位進行計算,這些輔助欄位最後都會被【隱藏】起來,M小姐常常需要將計算後的結果,填入到另外一張報表中,這時進行複製貼上後,總是會把這些隱藏的輔助欄位給貼上來,M小姐應該怎麼複製,才能略過這些隱藏的範圍呢?
Thumbnail
你知道空格有可能會搞壞你的分析嗎?來看看怎麼避免吧!
Thumbnail
你知道空格有可能會搞壞你的分析嗎?來看看怎麼避免吧!
Thumbnail
重複的資料好煩啊啊啊啊啊!怎麼把它們清掉呢?三招秀給你看看!傳送門這邊請。
Thumbnail
重複的資料好煩啊啊啊啊啊!怎麼把它們清掉呢?三招秀給你看看!傳送門這邊請。
Thumbnail
如果你是一個文字輸入量大,或是一個重視生產效率的(懶)人。懶得打重覆使用的文字,可能會使用一些其它方式來解決這種麻煩,例如: 使用剪貼簿軟體的進階複製貼上功能 這種剪貼簿工具有系統內建的,也有第三方的剪貼簿,主要是可以儲存與輸出多筆複製過的文字內容。
Thumbnail
如果你是一個文字輸入量大,或是一個重視生產效率的(懶)人。懶得打重覆使用的文字,可能會使用一些其它方式來解決這種麻煩,例如: 使用剪貼簿軟體的進階複製貼上功能 這種剪貼簿工具有系統內建的,也有第三方的剪貼簿,主要是可以儲存與輸出多筆複製過的文字內容。
Thumbnail
我有2個資料表(table database),「📕帳本」儲存支出明細、「📗書本」記錄閱讀歷。希望能在📗書本中看到同一書在📕帳本中的購買日期、金額、店家,但兩邊都輸入很麻煩,而且資料一多或事情一忙就容易出錯。
Thumbnail
我有2個資料表(table database),「📕帳本」儲存支出明細、「📗書本」記錄閱讀歷。希望能在📗書本中看到同一書在📕帳本中的購買日期、金額、店家,但兩邊都輸入很麻煩,而且資料一多或事情一忙就容易出錯。
Thumbnail
個人覺得 生活中的雜物實在太多元 太多樣 以至於每次發現的時候 都是已經很多很多 塞好塞滿的時候 那雜物要怎打掃整理呢? 步驟1.選一個櫃子或是區域開始整理(一次整理一個小區塊) 是的 要是想整
Thumbnail
個人覺得 生活中的雜物實在太多元 太多樣 以至於每次發現的時候 都是已經很多很多 塞好塞滿的時候 那雜物要怎打掃整理呢? 步驟1.選一個櫃子或是區域開始整理(一次整理一個小區塊) 是的 要是想整
追蹤感興趣的內容從 Google News 追蹤更多 vocus 的最新精選內容追蹤 Google News