Přejít k hlavnímu obsahu

Jak extrahovat jedinečné hodnoty z více sloupců v aplikaci Excel?

Předpokládejme, že máte několik sloupců s více hodnotami, některé hodnoty se opakují ve stejném sloupci nebo jiném sloupci. A nyní chcete najít hodnoty, které jsou v kterémkoli sloupci přítomny pouze jednou. Existují nějaké rychlé triky, jak extrahovat jedinečné hodnoty z více sloupců v aplikaci Excel?


Extrahujte jedinečné hodnoty z více sloupců pomocí vzorců

Tato část pokryje dva vzorce: jeden používá maticový vzorec vhodný pro všechny verze Excelu a druhý používá dynamický maticový vzorec speciálně pro Excel 365.

Extrahujte jedinečné hodnoty z více sloupců pomocí vzorce Array pro všechny verze aplikace Excel

Pro uživatele s jakoukoli verzí Excelu mohou být maticové vzorce výkonným nástrojem pro extrahování jedinečných hodnot z více sloupců. Můžete to udělat takto:

1. Předpokládejme své hodnoty v rozsahu A2: C9, do buňky E2 zadejte následující vzorec:

=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"),)&""
Poznámka: Ve výše uvedeném vzorci, A2: C9 označuje rozsah buněk, ze kterých chcete extrahovat jedinečné hodnoty, E1: E1 je první buňka sloupce, do kterého chcete umístit výsledek, $ 2: $ 9 stojany od řádků obsahují buňky, které chcete použít, a $ A: $ C označuje, že sloupce obsahují buňky, které chcete použít. Změňte je prosím za své.

2. Pak stiskněte tlačítko Shift + Ctrl + Enter klávesy dohromady a poté přetažením úchytu výplně extrahujte jedinečné hodnoty, dokud se neobjeví prázdné buňky. Viz screenshot:

Vysvětlení tohoto vzorce:
  1. $ A $ 2: $ C $ 9: Toto určuje rozsah dat, který se má zkontrolovat, což jsou buňky od A2 do 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<>"" kontroluje, zda buňky v rozsahu nejsou prázdné.
    • COUNTIF($E$1:E1,$A$2:$C$9)=0 určuje, zda hodnoty těchto buněk ještě nebyly uvedeny v rozsahu buněk od E1 do E1.
    • Pokud jsou splněny obě podmínky (tj. hodnota není prázdná a ještě není uvedena ve sloupci E), funkce IF vypočítá jedinečné číslo na základě jeho řádku a sloupce (ROW($2:$9)*100+COLUMN($A: $C)).
    • Pokud podmínky nejsou splněny, funkce vrátí velké číslo (7^8), které slouží jako zástupný symbol.
  3. MIN(...): Najde nejmenší číslo vrácené výše uvedenou funkcí KDYŽ, odpovídající umístění další jedinečné hodnoty.
  4. TEXT(..."R0C00"): Převede toto minimální číslo na adresu ve stylu R1C1. Kód formátu R0C00 označuje převod čísla do referenčního formátu buňky Excel.
  5. NEPŘÍMÝ(...): Používá funkci NEPŘÍMÉ pro převod adresy stylu R1C1 vygenerované v předchozím kroku zpět na normální odkaz na buňku stylu A1. Funkce NEPŘÍMÉ umožňuje odkazování na buňky na základě obsahu textového řetězce.
  6. &"": Přidání &"" na konec vzorce zajistí, že konečný výstup bude zpracován jako text, takže sudá čísla budou zobrazena jako text.
 
Extrahujte jedinečné hodnoty z více sloupců pomocí vzorce pro Excel 365

Excel 365 podporuje dynamická pole, takže je mnohem snazší extrahovat jedinečné hodnoty z více sloupců:

Zadejte nebo zkopírujte následující vzorec do prázdné buňky, kam chcete umístit výsledek, a poté klikněte vstoupit klíč k získání všech jedinečných hodnot najednou. Viz snímek obrazovky:

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


Extrahujte jedinečné hodnoty z více sloupců pomocí Kutools AI Aide

Poznejte sílu Kutools AI asistent pro bezproblémové extrahování jedinečných hodnot z více sloupců v Excelu. Pouhými několika kliknutími tento inteligentní nástroj prozkoumá vaše data, identifikuje a vypisuje jedinečné položky v libovolném vybraném rozsahu. Zapomeňte na potíže se složitými vzorci nebo kódem vba; Využijte efektivitu Kutools AI asistent a přeměňte svůj pracovní postup v Excelu na produktivnější a bez chyb.

Poznámka: Chcete-li použít toto Kutools AI asistent of Kutools pro Excel, Prosím stáhnout a nainstalovat Kutools pro Excel jako první.

Po instalaci Kutools pro Excel klikněte prosím Kutools AI > AI asistent k otevření Kutools AI asistent podokno:

  1. Zadejte svůj požadavek do pole chatu a klikněte Poslat nebo stiskněte tlačítko vstoupit klíč k odeslání otázky;
    "Extrahujte jedinečné hodnoty z rozsahu A2:C9, ignorujte prázdné buňky a umístěte výsledky počínaje E2:"
  2. Po analýze klikněte Provést tlačítko pro spuštění. Kutools AI Aide zpracuje váš požadavek pomocí AI a vrátí výsledky v zadané buňce přímo v Excelu.


Extrahujte jedinečné hodnoty z více sloupců pomocí kontingenční tabulky

Pokud jste obeznámeni s kontingenční tabulkou, můžete snadno extrahovat jedinečné hodnoty z více sloupců pomocí následujících kroků:

1. Nejprve vložte jeden nový prázdný sloupec nalevo od dat, v tomto příkladu vložím sloupec A vedle původních dat.

2. Klikněte na jednu buňku ve svých datech a stiskněte Alt + D a stiskněte P klíč okamžitě otevřete Průvodce kontingenční tabulkou a kontingenčním grafemzvolte Několik rozsahů konsolidace v kroku průvodce 1, viz screenshot:

3. Pak klikněte na tlačítko další tlačítko, zkontrolujte Vytvořte pro mě jednostránkové pole možnost v kroku 2 průvodce, viz screenshot:

4. Pokračujte kliknutím další Klepnutím vyberte rozsah dat, který zahrnuje nový nový sloupec buněk, a poté klepněte na přidat tlačítko pro přidání rozsahu dat do Všechny rozsahy seznam, viz screenshot:

5. Po výběru rozsahu dat pokračujte kliknutím další, v kroku průvodce 3 vyberte, kam chcete umístit sestavu kontingenční tabulky, jak chcete.

6. Konečně klikněte na tlačítko úprava k dokončení průvodce a v aktuálním listu byla vytvořena kontingenční tabulka, potom zrušte zaškrtnutí všech polí v Vyberte pole, která chcete přidat do sestavy sekce, viz screenshot:

7. Poté zkontrolujte pole Hodnota nebo přetáhněte hodnotu na Řádky štítek, nyní získáte jedinečné hodnoty z více sloupců takto:


Extrahujte jedinečné hodnoty z více sloupců pomocí kódu VBA

S následujícím kódem VBA můžete také extrahovat jedinečné hodnoty z více sloupců.

1. Podržte ALT + F11 klíče a otevře se Okno Microsoft Visual Basic pro aplikace.

2, klikněte Vložit > Modula vložte následující kód do okna modulu.

VBA: Extrahujte jedinečné hodnoty z více sloupců

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. Pak stiskněte tlačítko F5 ke spuštění tohoto kódu a vyskočí okno s výzvou, aby vám připomnělo vybrat rozsah dat, který chcete použít. Viz snímek obrazovky:

4. A pak klikněte na tlačítko OK, zobrazí se další pole s výzvou, které vám umožní vybrat místo pro vložení výsledku, viz screenshot:

5. klikněte OK zavřete toto dialogové okno a všechny jedinečné hodnoty byly extrahovány najednou.


Více relativních článků:

  • Spočítejte počet jedinečných a odlišných hodnot ze seznamu
  • Předpokládejme, že máte dlouhý seznam hodnot s některými duplicitními položkami, nyní chcete spočítat počet jedinečných hodnot (hodnoty, které se v seznamu objeví pouze jednou) nebo odlišné hodnoty (všechny různé hodnoty v seznamu, to znamená jedinečné hodnoty + 1. duplicitní hodnoty) ve sloupci, jak je zobrazen snímek obrazovky vlevo. V tomto článku budu hovořit o tom, jak řešit tuto práci v aplikaci Excel.
  • Extrahujte jedinečné hodnoty na základě kritérií v aplikaci Excel
  • Předpokládejme, že máte následující rozsah dat, který chcete vypsat pouze jedinečné názvy sloupce B na základě konkrétního kritéria sloupce A, abyste získali výsledek, jak je znázorněno níže. Jak jste mohli s tímto úkolem v aplikaci Excel jednat rychle a snadno?
  • Součet jedinečných hodnot na základě kritérií v aplikaci Excel
  • Například mám řadu dat, která obsahuje sloupce Název a Objednávka, nyní, abych shrnul pouze jedinečné hodnoty ve sloupci Objednávka na základě sloupce Název, jak ukazuje následující snímek obrazovky. Jak rychle a snadno vyřešit tento úkol v aplikaci Excel?

Nejlepší nástroje pro produktivitu v kanceláři

🤖 Kutools AI asistent: Revoluční analýza dat založená na: Inteligentní provedení   |  Generovat kód  |  Vytvořte vlastní vzorce  |  Analyzujte data a generujte grafy  |  Vyvolejte funkce Kutools...
Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup    VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar    Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky    Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50+ Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

Popis


Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
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