跳到主要內容

如何從Excel中的多個列中提取唯一值?

假設您有多個具有多個值的列,則某些值在同一列或不同列中重複。 現在,您只想查找任一列中存在的值一次。 是否有任何快速技巧可以從Excel中的多個列中提取唯一值?


使用公式從多列中提取唯一值

本節將介紹兩個公式:一個使用適用於所有 Excel 版本的陣列公式,另一個使用專門針對 Excel 365 的動態陣列公式。

使用適用於所有 Excel 版本的陣列公式從多列中提取唯一值

對於使用任何版本的 Excel 的使用者來說,數組公式可以成為跨多列提取唯一值的強大工具。您可以這樣做:

1。 假設您的值在範圍內 A2:C9,請在單元格E2中輸入以下公式:

=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
備註:在以上公式中, A2:C9 表示要提取唯一值的單元格範圍, E1:E1 是您要放置結果的列的第一個單元格, $ 2:$ 9 這些行包含要使用的單元格,並且 $ A:$ C 表示列包含您要使用的單元格。 請更改為您自己的。

2。 然後按 Shift + Ctrl + 輸入 鍵,然後拖動填充手柄以提取唯一值,直到出現空白單元格。 看截圖:

這個公式的解釋:
  1. $ A $ 2:$ C $ 9:指定要檢查的資料範圍,即A2到C9的儲存格。
  2. IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C), 7^8):
    • $A$2:$C$9<>"" 檢查範圍內的單元格是否為空。
    • COUNTIF($E$1:E1,$A$2:$C$9)=0 確定這些儲存格的值是否尚未列在從 E1 到 E1 的儲存格範圍中。
    • 如果滿足兩個條件(即值不為空且尚未在 E 列中列出),IF 函數將根據其行和列計算唯一數字 (ROW($2:$9)*100+COLUMN($A: $C) )。
    • 如果不符合條件,函數將傳回一個大數字 (7^8),用作佔位符。
  3. 最小值(...):找出上面 IF 函數傳回的最小數字,對應下一個唯一值的位置。
  4. 文本(...,“R0C00”):將此最小數字轉換為 R1C1 樣式位址。格式代碼 R0C00 表示將數字轉換為 Excel 儲存格參考格式。
  5. 間接(...):使用INDIRECT函數將上一步產生的R1C1樣式位址轉換回正常的A1樣式儲存格參考。 INDIRECT 函數允許基於文字字串的內容進行儲存格參考。
  6. &"":在公式末尾加上 &"" 可確保最終輸出被視為文本,因此偶數將顯示為文字。
 
使用 Excel 365 公式從多列中擷取唯一值

Excel 365 支援動態數組,使得從多列中提取唯一值變得更加容易:

請將下列公式輸入或複製到要放置結果的空白儲存格中,然後按一下 Enter 一次取得所有唯一值的關鍵。看截圖:

=UNIQUE(TOCOL(A2:C9,1))


使用 Kutools AI Aide 從多列中提取唯一值

釋放力量 Kutools 人工智慧助手 從 Excel 中的多個欄位中無縫提取唯一值。只需點擊幾下,這個智慧工具就會篩選您的數據,識別並列出任何選定範圍內的唯一條目。忘記複雜公式或VBA程式碼的麻煩;擁抱效率 Kutools 人工智慧助手 並將您的 Excel 工作流程轉變為更有效率且無錯誤的體驗。

備註: 要使用這個 Kutools 人工智慧助手 of Excel的Kutools下載並安裝 Kutools for Excel 第一。

安裝Kutools for Excel之後,請點擊 Kutools人工智慧 > 人工智慧助手 打開 Kutools 人工智慧助手 窗格:

  1. 在聊天框中輸入您的要求,然後按一下 送出 按鈕或按下 Enter 發送問題的按鍵;
    “從範圍 A2:C9 中提取唯一值,忽略空白單元格,並將結果從 E2 開始:”
  2. 分析完後點擊 執行 按鈕運行。 Kutools AI Aide 將使用 AI 處理您的請求,並直接在 Excel 中返​​回指定儲存格中的結果。


使用數據透視表從多個列中提取唯一值

如果您熟悉數據透視表,則可以按照以下步驟輕鬆地從多個列中提取唯一值:

1。 首先,請在數據左側插入一個新的空白列,在本例中,我將在原始數據旁邊插入A列。

2。 單擊數據中的一個單元格,然後按 Alt + D 鍵,然後按 P 立即打開鑰匙 數據透視表和數據透視圖嚮導選擇 多種合併範圍 在嚮導step1中,查看屏幕截圖:

3。 然後點擊 下一頁 按鈕,檢查 為我創建一個頁面字段 嚮導步驟2中的選項,請參見屏幕截圖:

4。 繼續點擊 下一頁 按鈕,單擊以選擇包括左側新的單元格列的數據范圍,然後單擊 加入 按鈕將數據范圍添加到 所有範圍 列錶框,請參見屏幕截圖:

5。 選擇數據范圍後,繼續單擊 下一頁,在嚮導的第3步中,根據需要選擇要放置數據透視表報表的位置。

6. 最後點擊 以完成嚮導,並在當前工作表中創建了一個數據透視表,然後取消選中 選擇要添加到報告中的字段 部分,請參見屏幕截圖:

7. 然後檢查現場 或將值拖到 標籤,現在您將從多個列中獲得唯一值,如下所示:


使用VBA代碼從多個列中提取唯一值

使用以下VBA代碼,您還可以從多個列中提取唯一值。

1。 按住 ALT + F11 鍵,然後打開 Microsoft Visual Basic for Applications窗口.

2。 點擊 插入 > 模塊,然後將以下代碼粘貼到“模塊窗口”中。

VBA:從多個列中提取唯一值

Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
    If rng.Value <> "" Then
        dt(rng.Value) = ""
    End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub

3。 然後按 F5 運行此代碼,將彈出一個提示框,提醒您選擇要使用的數據范圍。 看截圖:

4。 然後點擊 OK,將出現另一個提示框,讓您選擇放置結果的位置,請參見屏幕截圖:

5. 點擊 OK 關閉此對話框,並立即提取所有唯一值。


更多相關文章:

  • 計算列表中唯一值和不同值的數量
  • 假設您的值列表很長,其中包含一些重複項,現在,您想要計算唯一值(僅一次出現在列表中的值)或不同值(列表中所有不同值的數量)的數量值+第一個重複值),如左圖所示。 本文,我將討論如何在Excel中處理此工作。
  • 根據Excel中的條件提取唯一值
  • 假設您具有以下數據范圍,您希望根據列A的特定標準僅列出列B的唯一名稱,以得到結果,如下面的屏幕快照所示。 您如何在Excel中快速輕鬆地處理此任務?
  • 僅在Excel中允許唯一值
  • 如果您只想在工作表的列中輸入唯一值並防止重複,則本文將介紹一些快速技巧來處理此任務。
  • 根據Excel中的條件求和唯一值
  • 例如,我現在有一系列數據,其中包含“名稱”和“訂單”列,以便根據“名稱”列僅對“訂單”列中的唯一值求和,如下圖所示。 如何在Excel中快速輕鬆地解決此任務?

最佳辦公生產力工具

🤖 Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (31)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Thank you for this solution, however what if the columns are in separate excel sheet? or is in separate columns instead of a table?
This comment was minimized by the moderator on the site
Hello, Jon,
The methods in this article ar only works well for a range of data, if your data in separate columns, you should copy and paste them into one range first, and then apply the formula or VBA code.
Thank you!
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
This comment was minimized by the moderator on the site
Hello, Michael H.
Thanks for your kindly explanation.
Hope this can help others in the future.😄
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
This comment was minimized by the moderator on the site
Hello Sir! The VBA worked wonders, thank you very much for that! I was wondering, If I change the original data, is it possible to refresh the column with the unique values automatically?
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hello Ioannis,

Glad to help. After you change the original data, the VBA can not refresh the result automatically. And the easiest way I can think of is to press Ctrl + Alt + F9 to refresh all results in worksheets in all open workbooks. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
The array formula at the top is working great when used with data in the same sheet, however when I try to use it to reference the same exact data from another sheet the formula returns nothing. I'm unable to figure out why. Is there a limitation with array functions that prevents you from referencing ranges in a different sheet?

Thanks for any insight you can provide.
This comment was minimized by the moderator on the site
Hello Erin,

Glad to help. The INDIRECT function in this formula is more complicated to use when referencing data in other worksheets. It is not recommended to use this feature when referencing ranges in different worksheets.

For example: Now the data is in Sheet1, I want to reference the content of cell C2 of Sheet1 in Sheet2. First, in any two cells in Sheet2, such as D1 and D2, enter Sheet1 and C2, respectively. At this point, enter the formula in the empty cell of Sheet2:
=INDIRECT("'"&D1&"'!"&D2), then the content of cell C2 in Sheet1 can be returned.

As you can see, it make things way more complex. Hope my explanation can help. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Czy to żart?
This comment was minimized by the moderator on the site
can we create uniqdata function instead of macro?
This comment was minimized by the moderator on the site
Hi, İlhan,If you like a User Defined Function to create a formula for solving this problem, the below code may help you:After inserting the code, select a list of cells where you want to put the results. Then type this formula:=Uniques(A1:C4)  in the formula bar.Press Ctrl+Shift+Enter keys together. 


Function Uniques(ByVal inputRange As Range)
Dim inputArray As Variant
Dim myColl As New Collection
Dim xVal As Variant
Dim outArray() As Variant
On Error Resume Next
With inputRange
inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
End With
On Error GoTo 0
On Error Resume Next
For Each xVal In inputArray
myColl.*** Item:=xVal, Key:=(CStr(xVal) & TypeName(xVal))
Next xVal
myColl.Remove "String"
On Error GoTo 0
ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
For xVal = 1 To UBound(outArray)
outArray(xVal) = vbNullString
Next xVal
For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
outArray(xVal) = myColl(xVal)
Next xVal
If Application.Caller.Columns.Count = 1 Then
Uniques = Application.Transpose(outArray)
Else
Uniques = outArray
End If
End Function
This comment was minimized by the moderator on the site
Thanks for the code. I'm using the VBA code of this page. Is there a way to add a sorting code after the unique values are extracted so it sorts it automatically?
This comment was minimized by the moderator on the site
Regarding the formula version, could you explain in more detail what this portion is doing? *100+COLUMN($A:$C),7^8)),"R0C00") Specifically, what are the *100, 7^8, and "R0C000" doing? I'm understanding everything else, but I can't figure out what these are for.
This comment was minimized by the moderator on the site
Little late for my response here but...
ROW($2:$9)*100 - this is multiplying the row number *100, so if it's in row 5, now the number is 500
COLUMN($A:$C) - this gets added to the row*100 number, so if it's row 5 col 2, then the number is 502.
7^8)), - this (I think) is to have a max value for the min statement from earlier.
"R0C00") - this formats the text based on the number. In the example, we had 502 so this gives R5C02 (row 5, col 02).

If you have a lot of columns but not many rows, then you could change it to ROW($2:$9)*1000+COLUMN($A:$C),7^8)),"R0C000")
This comment was minimized by the moderator on the site
i've adjusted to my sheet but am only returning the first value in the defined array... what am i missing?
This comment was minimized by the moderator on the site
Hello, Cody,
The above formula works well in my worksheet, could you give a screenshot of your data problem here?
Thank you!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations