Note: The other languages of the website are Google-translated. Back to English
English English

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í maticového vzorce

Zde je také maticový vzorec, který vám pomůže extrahovat jedinečné hodnoty z více sloupců.

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:


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.


Extrahujte jedinečné hodnoty z jednoho sloupce s úžasnou funkcí

Někdy musíte extrahovat jedinečné hodnoty z jednoho sloupce, výše uvedené metody vám nepomohou, zde mohu doporučit užitečný nástroj -Kutools pro Excel, S jeho Extrahujte buňky s jedinečnými hodnotami (zahrňte první duplikát) nástroj, můžete rychle extrahovat jedinečné hodnoty.

Poznámka:Použít toto Extrahujte buňky s jedinečnými hodnotami (zahrňte první duplikát)Nejprve byste si měli stáhnout soubor Kutools pro Excela poté tuto funkci rychle a snadno aplikujte.

Po instalaci Kutools pro Excel, udělejte prosím toto:

1. Klikněte na buňku, do které chcete výsledek odeslat. (Poznámka: Neklikejte na buňku v prvním řádku.)

2. Pak klikněte na tlačítko Kutools > Pomocník vzorců > Pomocník vzorců, viz screenshot:

3. V Pomocník vzorců V dialogovém okně proveďte následující operace:

  • vybrat text možnost z nabídky Vzorec typ rozbalovací seznam;
  • Pak zvolte Extrahujte buňky s jedinečnými hodnotami (zahrňte první duplikát) z Vyberte si fromula seznam;
  • Vpravo Zadání argumentů V části vyberte seznam buněk, ze kterých chcete extrahovat jedinečné hodnoty.

4. Pak klikněte na tlačítko Ok Tlačítko a přetáhněte popisovač výplně do buněk, které chcete vypsat všechny jedinečné hodnoty, dokud se nezobrazí prázdné buňky, viz screenshot:

Stažení zdarma Kutools pro Excel nyní!


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ší kancelářské nástroje produktivity

Kutools pro Excel řeší většinu vašich problémů a zvyšuje vaši produktivitu o 80%

  • Opakované použití: Rychle vložte složité vzorce, grafy a cokoli, co jste dříve používali; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
  • Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
  • Sloučit buňky / řádky / sloupce bez ztráty dat; Rozdělit obsah buněk; Zkombinujte duplicitní řádky / sloupce... Zabraňte duplicitním buňkám; Porovnat rozsahy...
  • Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
  • Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
  • Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
  • Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
  • Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
  • Více než 300 výkonných funkcí. Podporuje Office / Excel 2007-2019 a 365. Podporuje všechny jazyky. Snadné nasazení ve vašem podniku nebo organizaci. Kompletní funkce 30denní bezplatná zkušební verze. 60denní záruka vrácení peněz.
karta kte 201905

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 omezuje stovky kliknutí myší každý den!
officetab dno
Komentáře (31)
Hodnocení 5 z 5 · 1 hodnocení:
Tento komentář byl moderátorem webu minimalizován
Is this formula complete? =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"),)&"",
Tento komentář byl moderátorem webu minimalizován
To ještě není opraveno :sad:
Tento komentář byl moderátorem webu minimalizován
jaká ztráta času..... vzorec nefunguje
Tento komentář byl moderátorem webu minimalizován
Děkuji!!! Strávil jsem hodiny tím, abych to udělal a zjistil, co se stalo s průvodcem Pivot Wizard (jiný článek).
Tento komentář byl moderátorem webu minimalizován
Používám váš kód VBA, ale nechci, aby se okno objevilo. Místo toho chci přesně definovat, jaký rozsah buněk pokaždé použít a přesně do jaké krabice vložit výstup. Vstupní rozsah a výstup by byly na dvou různých listech. jak mohu aktualizovat VBA, abych tak učinil? Děkuji!!
Tento komentář byl moderátorem webu minimalizován
Ahoj! Ví někdo, proč se zdá, že tento vzorec vede k chybě po řádku 87? Jako, funguje to perfektně a pak mi to v určité chvíli vrací chyby za každý řádek.. což je nejhorší! Protože jsem tak blízko přesně tomu, co tady potřebuji...
Tento komentář byl moderátorem webu minimalizován
=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"),)&"" It doesn't work
Tento komentář byl moderátorem webu minimalizován
ahoj chci extrahovat jedinečné buňky z prvního sloupce, když jej porovnám s jinými sloupci (mám tři nestejné sloupce), jak to mohu udělat?
Tento komentář byl moderátorem webu minimalizován
ahoj, mám tři nestejné sloupce a chci extrahovat jedinečné buňky prvního sloupce. jak to mohu udělat?? Díky předem
Tento komentář byl moderátorem webu minimalizován
miluji

Extrahujte jedinečné hodnoty z více sloupců pomocí kontingenční tabulky
Tento komentář byl moderátorem webu minimalizován
Můžete prosím odeslat správný vzorec... funkce VBA funguje dobře.
Jen pro svůj projekt raději používám správný vzorec.


Díky
Tento komentář byl moderátorem webu minimalizován
neví někdo pro výstup, jak to udělat do několika řádků, ale ne do jednoho řádku? (v současné době je výsledek jednoho řádku dosažen pomocí worksheetfunction.transpose, ale chci dosáhnout (výsledkem) toho, že při výběru pro 3 sloupce jsou vráceným výsledkem také 3 sloupce, místo toho jeden
Tento komentář byl moderátorem webu minimalizován
Tento maticový vzorec je SPRÁVNÝ. Data ve sloupcích A až C, první výsledný vzorec v buňce D2... Tento se liší od ostatních maticových vzorců tím, že později zkopírujete vzorec dolů a Ctrl+Shift+Enter all vzorec. Tento maticový vzorec by však měl být proveden pomocí Ctrl+Shift+Enter v první buňce a zkopírován dolů.
Tento komentář byl moderátorem webu minimalizován
Muchas gracias por la macro!!! mě fue muy util
Tento komentář byl moderátorem webu minimalizován
Přizpůsobil jsem se svému listu, ale vracím pouze první hodnotu v definovaném poli... co mi chybí?
Tento komentář byl moderátorem webu minimalizován
Dobrý den, Cody,
Výše uvedený vzorec funguje dobře v mém pracovním listu, mohl byste sem dát snímek obrazovky vašeho problému s daty?
Děkuji!
Tento komentář byl moderátorem webu minimalizován
Pokud jde o verzi vzorce, mohl byste podrobněji vysvětlit, co tato část dělá? *100+COLUMN($A:$C),7^8)),"R0C00") Konkrétně, jaké jsou * 100, 7 8 ^, a "R0C000" dělat? Všemu ostatnímu rozumím, ale nemůžu přijít na to, k čemu to je.
Tento komentář byl moderátorem webu minimalizován
Trochu pozdě na moji reakci, ale...
ROW($2:$9)*100 – toto je vynásobení čísla řádku *100, takže pokud je na řádku 5, nyní je číslo 500
COLUMN($A:$C) – toto se přidá k číslu řádku*100, takže pokud je to řádek 5, sloupec 2, pak je číslo 502.
7^8)), - toto (myslím) má mít maximální hodnotu pro příkaz min z dříve.
"R0C00") - toto formátuje text na základě čísla. V příkladu jsme měli 502, takže to dává R5C02 (řádek 5, sloupec 02).

Pokud máte hodně sloupců, ale málo řádků, můžete to změnit na ROW($2:$9)*1000+COLUMN($A:$C);7^8)),“R0C000")
Tento komentář byl moderátorem webu minimalizován
Díky za kód. Používám kód VBA této stránky. Existuje způsob, jak přidat třídicí kód po extrahování jedinečných hodnot, aby se automaticky třídil?
Tento komentář byl moderátorem webu minimalizován
můžeme místo makra vytvořit funkci uniqdata?
Tento komentář byl moderátorem webu minimalizován
Ahoj, İlhan, pokud máte rádi uživatelsky definovanou funkci k vytvoření vzorce pro řešení tohoto problému, níže uvedený kód vám může pomoci: Po vložení kódu vyberte seznam buněk, kam chcete umístit výsledky. Poté zadejte tento vzorec:=Unikátní(A1:C4)  v řádku vzorců.Stiskněte Ctrl + Shift + Enter klíče dohromady. 


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
Tento komentář byl moderátorem webu minimalizován
Czy to żart?
Tento komentář byl moderátorem webu minimalizován
Pole vzorec v horní části funguje skvěle, když je použit s daty ve stejném listu, ale když se jej pokusím použít k odkazování na stejná přesná data z jiného listu, vzorec nevrací nic. Nejsem schopen přijít na to proč. Existuje omezení s funkcemi pole, které vám brání odkazovat na rozsahy v jiném listu?

Děkujeme za jakékoli informace, které můžete poskytnout.
Tento komentář byl moderátorem webu minimalizován
ahoj Erin,

Rád jsem pomohl. Použití funkce NEPŘÍMÉ v tomto vzorci je složitější při odkazování na data v jiných listech. Nedoporučuje se používat tuto funkci při odkazování na rozsahy v různých listech.

Například: Nyní jsou data v Listu1, chci odkazovat na obsah buňky C2 List1 v Listu2. Nejprve do libovolných dvou buněk v Listu2, například D1 a D2, zadejte List1 a C2. V tomto okamžiku zadejte vzorec do prázdné buňky List2:
=NEPŘÍMÝ("'"&D1&"'!"&D2), pak lze vrátit obsah buňky C2 v Listu1.

Jak vidíte, dělá věci mnohem složitějšími. Doufám, že moje vysvětlení pomůže. Hezký den.

S pozdravem,
Mandy
Tento komentář byl moderátorem webu minimalizován
Dobrý den pane! VBA dokázala zázraky, moc za to děkujeme! Zajímalo by mě, jestli změním původní data, je možné automaticky obnovit sloupec s jedinečnými hodnotami?
Hodnocení 5 z 5
Tento komentář byl moderátorem webu minimalizován
Ahoj Ioannisi,

Rád jsem pomohl. Po změně původních dat nemůže VBA automaticky aktualizovat výsledek. A nejjednodušší způsob, který mě napadá, je stisknutím Ctrl + Alt + F9 obnovit všechny výsledky v listech ve všech otevřených sešitech. Hezký den.

S pozdravem,
Mandy
Tento komentář byl moderátorem webu minimalizován
Děkujeme za tento skvělý článek.

Pro lidi, kteří používají array formular v neanglickém Excelu je třeba věnovat zvláštní péči řetězci textového formátu: ve vašem příkladu: "R0C00".
Pro němčinu by to znamenalo „Z0S00“. "S" je však speciální znak odkazující na sekundy pro formátování času. Tento znak musí být escapován, a proto je správný formátovací řetězec pro německý Excel "Z0\S00".

Doufám, že to někomu v budoucnu pomůže :-)
Zatím zde nejsou žádné komentáře
Načíst další
Zanechat své připomínky
Odesílání jako host
×
Ohodnoťte tento příspěvek:
0   Postavy
Doporučená umístění