Note: The other languages of the website are Google-translated. Back to English
English English
  • Dokumenty
  • vynikat
  • Jak dynamicky extrahovat seznam jedinečných hodnot z rozsahu sloupců v aplikaci Excel?

Jak dynamicky extrahovat seznam jedinečných hodnot z rozsahu sloupců v aplikaci Excel?

Pro rozsah sloupců, jejichž hodnoty se pravidelně mění, a vždy musíte získat všechny jedinečné hodnoty z rozsahu bez ohledu na to, jak se změnil. Jak vytvořit dynamický seznam jedinečných hodnot? Tento článek vám ukáže, jak s tím zacházet.

Dynamicky extrahujte seznam jedinečných hodnot z rozsahu sloupců pomocí vzorce
Dynamicky extrahujte seznam jedinečných hodnot z rozsahu sloupců pomocí kódu VBA


Dynamicky extrahujte seznam jedinečných hodnot z rozsahu sloupců pomocí vzorce

Jak je ukázáno níže, musíte dynamicky extrahovat seznam jedinečných hodnot z rozsahu B2: B9. Vyzkoušejte následující vzorec pole.

1. Vyberte prázdnou buňku, například D2, zadejte do ní následující vzorec a stiskněte Ctrl + směna + vstoupit současně. (B2: B9 jsou data sloupce, ze kterých chcete extrahovat jedinečné hodnoty, D1 je buňka výše, kde se nachází váš vzorec)

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

2. Pokračujte ve výběru buňky D2, potom přetáhněte rukojeť výplně dolů, abyste získali všechny jedinečné hodnoty ze zadaného rozsahu.

Nyní jsou extrahovány všechny jedinečné hodnoty v rozsahu sloupců B2: B9. Když se hodnoty v tomto rozsahu změní, seznam jedinečných hodnot se okamžitě dynamicky změní.

Snadno vyberte a zvýrazněte všechny jedinečné hodnoty v rozsahu v aplikaci Excel:

Projekt Vyberte Duplikovat a jedinečné buňky užitečnost Kutools pro Excel vám pomůže snadno vybrat a zvýraznit všechny jedinečné hodnoty (včetně prvních duplikátů) nebo jedinečné hodnoty, které se zobrazí pouze jednou, stejně jako duplicitní hodnoty podle potřeby, jak je uvedeno níže.
Stáhněte si Kutools pro Excel nyní! (

-denní stezka zdarma)


Dynamicky extrahujte seznam jedinečných hodnot z rozsahu sloupců pomocí kódu VBA

Seznam jedinečných hodnot můžete také extrahovat dynamicky z rozsahu sloupců pomocí následujícího kódu VBA.

1. lis Další + F11 současně otevřete Microsoft Visual Basic pro aplikace okno.

2. V Microsoft Visual Basic pro aplikace okno, klepněte na tlačítko Vložit > Modul. Poté zkopírujte a vložte níže uvedený kód VBA do Modul okno.

Kód VBA: Extrahuje seznam jedinečných hodnot z rozsahu

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub

Poznámka: V kódu je D2 buňka, ve které najdete seznam jedinečných hodnot. Můžete to změnit podle potřeby.

3. Vraťte se na list a klikněte na Vložit > Tvary > Obdélník. Viz snímek obrazovky:

4. Nakreslete do listu obdélník a zadejte některá slova, která na něm potřebujete zobrazit. Poté na něj klikněte pravým tlačítkem a vyberte Přiřadit makro z nabídky pravého tlačítka myši. V Přiřadit makro dialogové okno vyberte VytvořteUniqueList v Název makra a poté klikněte na OK knoflík. Viz screenshot:

5. Nyní klikněte na tlačítko obdélníku, a Kutools pro Excel Zobrazí se dialogové okno, vyberte rozsah obsahující jedinečné hodnoty, které potřebujete extrahovat, a poté klikněte na OK .

Od této chvíle můžete opakovat výše uvedený krok 5 a automaticky aktualizovat jedinečný seznam hodnot.


Související články:


Nejlepší kancelářské nástroje produktivity

Kutools pro Excel vyřeší většinu vašich problémů a zvýší vaši produktivitu
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é funkce
    . Podporuje Office/Excel
    2007-2019 a 365
    . Podporuje všechny jazyky. Snadné nasazení ve vašem podniku nebo organizaci. Plné funkce
    30
    -denní zkušební verze zdarma. 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 tím
    50%
    a každý den vám sníží stovky kliknutí myší!
officetab dno
Komentáře (35)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Tento komentář byl moderátorem webu minimalizován
Děkuji za tutoriál. Jak byste pomocí metody vzorce změnili vzorec, pokud byste chtěli přidat kvalifikaci kategorie? Řekněme, že ve sloupci C rozlišujete, zda se jedná o ovoce nebo zeleninu. Jak byste změnili kód tak, aby třídil pouze jedinečné ovoce a vylučoval zeleninu? Pokusil jsem se nahradit COUNTIF za COUNTIFS pomocí druhého kritéria countifs (LIST RANGE,"CATEGORY"), ale vrátí se prázdné. Potřeboval bych rozšířit své pole a začlenit VLOOKUP?
Tento komentář byl moderátorem webu minimalizován
Jsem slušný v excelu, ale opravdu se snažím zabalit hlavu kolem toho, jak a proč výše uvedený vzorec funguje (funguje pro to, k čemu ho používám, ale musím pochopit proč). Někdy jsem trochu zmatený pomocí polí, takže jakékoli vysvětlení v idiotských podmínkách by bylo velmi užitečné
Tento komentář byl moderátorem webu minimalizován
Tento vzorec je zastaralý a nefunguje. Doslova jsem nastavil tento přesný excelový list, abych zjistil, zda mohu uvést tento vzorec do práce, a ono to nefunguje.
Tento komentář byl moderátorem webu minimalizován
Hej chlape,
Jakou verzi Office používáte?
Tento komentář byl moderátorem webu minimalizován
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} – zjistil jsem, že to funguje na jiném webu...

Pomocí Ctrl+Shift+Enter získáte funkci pole (složené závorky). Tažením zkopírujte a vložte vzorce, dokud se nezobrazí #NA. Moje datová sada byla ve sloupci-Q, byla porovnána, aby se zjistilo, zda existuje v seznamu unikátů ve sloupci-V, který se neustále táhne podél stejného sloupce.
Tento komentář byl moderátorem webu minimalizován
Dobrý den.
Uveďte prosím všechny jedinečné hodnoty sloupce Q se vzorcem abobv a poté použijte jeho vzorec =IF(D2=V1,"Shoda","Žádná shoda") k porovnání, zda se jedinečné hodnoty ve sloupci Q porovnávají se sloupcem V ve stejném řádku. .
Tento komentář byl moderátorem webu minimalizován
Zdravím a děkuji za pomoc.

Potřebuji přesně tuto funkci, ale můj seznam „unikátních hodnot“ se musí rozšířit přes sloupce místo řádků, takže rozšiřující seznam po řádcích mi nebude fungovat.

Jak mohu upravit tento vzorec, aby se seznam „jedinečných hodnot“ rozšířil, když jej přetahuji přes sloupce?

Offset()?
Přemístit()?
Indirect() s řetězcem absolutních odkazů zřetězených s odkazem na sloupec namísto řádku?


Ještě jednou díky!
Tento komentář byl moderátorem webu minimalizován
milý Ryane,
Tento vzorec =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter může vám pomohou vyřešit problém.
Viz níže uvedený snímek obrazovky:
Tento komentář byl moderátorem webu minimalizován
Z jakéhokoli důvodu také původní vzorec poskytoval:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

vrátí varování "kruhová reference" a nebude počítat..
Tento komentář byl moderátorem webu minimalizován
milý Ryane,
Jakou verzi Office používáte? Vzorec funguje dobře v mém Office 2016 a 2013.
Tento komentář byl moderátorem webu minimalizován
Už se mi to stalo dříve - moje oprava byla, že jsem zadával vzorec do buňky D1 (ekvivalent v listu, který jsem používal). Bez ohledu na to, které buňce odpovídá $D:$1, musíte ji zadat do buňky níže - D2. Omlouvám se, pokud to není důvod, proč jste dostali chybu
Tento komentář byl moderátorem webu minimalizován
Máte nějaké tipy, jak získat možnost VBA pro práci s Excelem 2016 pro macOS? Sledoval jsem kroky; když však spustím makro, nestane se vůbec nic. Dík!
Tento komentář byl moderátorem webu minimalizován
Daer Jones,
Zkuste prosím níže uvedený kód VBA a dejte mi vědět, zda vám funguje. Děkuji!

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
' Při chybě Pokračovat dále
Set xRng = Application.InputBox("Vyberte rozsah:", "Kutools pro Excel", Selection.Address, , , , , 8)
Pokud xRng není nic, pak Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Sloupce:=1, Záhlaví:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
Pro I = 1 To xLastRow2
If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
End If
další
End Sub
Tento komentář byl moderátorem webu minimalizován
Ahoj Crystal,
Snažím se použít verzi VB seznamu jedinečných hodnot a narážím na problém.
Rozsah, ze kterého chci vytvořit sloupec jedinečných hodnot, jsou všechny vzorce, které odkazují na různé karty.
Jak lze získat hodnotu, která se má převést místo vzorce?
Tento komentář byl moderátorem webu minimalizován
Milý Mike,
Převeďte prosím své odkazy na vzorce na absolutní a poté použijte skript VB.
Tento komentář byl moderátorem webu minimalizován
Mám stejný problém, až na to, že můj vzorec odkazuje na názvy sloupců a nelze je převést na absolutní.
Jak změním vba tak, aby vložil hodnoty a ne vzorec?
Tento komentář byl moderátorem webu minimalizován
Jak byste přidali více kritérií, například kdybyste chtěli přidat do dynamického seznamu pouze v případě, že datum bylo právě 9. září?

Zkouším "&" ve vzorci MATCH, ale nefunguje to.

Například na základě vašeho příkladu:
=IFERROR(INDEX($B$2:$B$9, MATCH(0 & B4,COUNTIF($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"" )
To způsobí chybu nebo vytvoří duplikáty.

Případně jsem četl, že "+" by mohlo fungovat, i když se mi to nedaří zprovoznit. Nebo pomocí SMALL.

Nápady?
Tento komentář byl moderátorem webu minimalizován
milý Zacu,
Je nám líto, s tím vám nemohu pomoci, svůj dotaz můžete zveřejnit v našem fóru: https://www.extendoffice.com/forum.html získat další podporu Excelu od našeho profesionála.
Tento komentář byl moderátorem webu minimalizován
Jak byste přidali druhou proměnnou? Například chci všechny jedinečné prvky v jednom sloupci, které také sdílejí podobnou hodnotu v jiném sloupci. Ve vašem příkladu si představte 3. sloupec nazvaný „Oddělení“, který by obsahoval hodnoty jako produkt, maso atd. Uvědomuji si, že to všechno jsou Produkce, ale doufám, že jste pochopili, co říkám. Upravili byste vzorec CountIF na COUNTIFS nebo jej upravili jiným způsobem?
Tento komentář byl moderátorem webu minimalizován
Ahoj Matt
Zkuste prosím tento vzorec =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"Ano","").
Předpokládejme, že dva porovnávané seznamy jsou sloupec A a sloupec C. Pokud jedinečné hodnoty zůstanou pouze ve sloupci A, ale nikoli ve sloupci C, zobrazí se ve sloupci B Ano; zatímco pokud nevrátí nic ve sloupci B, znamená to, že odpovídající hodnota zůstane ve sloupci A i ve sloupci C.
Tento komentář byl moderátorem webu minimalizován
Díky za odpověď .. ale horko vytáhnout tuto jedinečnou hodnotu, pokud se zobrazí ANO .. můžete mi prosím poradit vzorec pro vytažení jedinečné hodnoty v jiném sloupci.
Tento komentář byl moderátorem webu minimalizován
Pokud to udělám pro tisíc řádků excelového listu v nejnovější verzi Excelu na Macu, nikdy se to nevrátí. První řádek funguje, ale když duplikuji dolů, Excel přejde do výpočetního režimu, který již více než dvě hodiny nevrací hodnoty.

Máte nějaké nápady, jak to udělat pro velké seznamy (až 2 50 řádků), které vrátí 60 nebo XNUMX jedinečných hodnot?

Vysmíval jsem se to v aplikaci „Čísla“ a funguje to tam perfektně, výpočet zabere jen pár minut. Jen to v Excelu trvá tak dlouho, že jsem zvědavý, jestli se to někdy dokončí. Mám v plánu to nechat "běžet" přes noc, abych viděl, co se bude dít.
Tento komentář byl moderátorem webu minimalizován
Zkontrolujte možnosti výpočtu. Je potřeba to nastavit na automatické. Soubor > Možnosti > Vzorce > Možnosti výpočtu > Výpočet sešitu (automatický výběr)
Tento komentář byl moderátorem webu minimalizován
Snažím se přetáhnout vzorec dolů za moje skutečná data, abych mohl vkládat datové sady různých velikostí a nemusel nic upravovat. Poslední řádek po skončení mých skutečných dat však vždy vrátí "0". Používám jedinečné hodnoty pro něco jiného v sousedním sloupci a 0 způsobí opakování poslední hodnoty (Když vymažu 0, hodnota se již neopakuje). Nějaký nápad, jak to opravit? Také používám Office 365 Business
Tento komentář byl moderátorem webu minimalizován
Ahoj, děkuji za pomoc.
Jak mohu mít své hodnoty také seřazeny podle abecedy? (Nechci použít filtr na mé hlavní tabulce)
Mám místo COUNTIF použít COUNTIFS?
Prosím pomozte
Tento komentář byl moderátorem webu minimalizován
ahoj Alexis,
Lituji, nemohu seřadit extrahovanou hodnotu abecedně současně se vzorcem. Děkuji za váš komentář.
Tento komentář byl moderátorem webu minimalizován
Používám vzorec =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),""), který je skvělý pro jeden sloupec, ale moje data jsou rozložena do řady sloupců a řádků. Mohu upravit vzorec tak, aby zahrnoval celou oblast? Moje data žijí od AC4 do AR60...
Tento komentář byl moderátorem webu minimalizován
Zkouším kód VBA a vzorec. Kód VBA funguje velmi dobře, ale nemohu udržet soubor s makrem. Ale problém je v tom, že nedokážu zprovoznit vzorec. Měl někdo nápad? Děkuji
Tento komentář byl moderátorem webu minimalizován
Ahoj Charlotte,
Děkuji za váš komentář. Soubor s makrem si můžete ponechat pro budoucí použití uložením sešitu jako sešit Excel s podporou maker.
Pokud jde o problém se vzorcem, poskytli byste prosím snímek obrazovky vašich dat? Děkuji za váš komentář.
Tento komentář byl moderátorem webu minimalizován
děkuju mnohokrát
Tento komentář byl moderátorem webu minimalizován
jak zajistit, aby kód vba fungoval pro rozsah, kde byl použit jiný vzorec? ve sloupci BI má vzorec odkazující na sloupce D a E.
Pokud použiji použít kód na sloupec L (řekněme), (samozřejmě správně upravím buňky v kódu), makro vrátí vzorec aplikovaný na sloupce M a N... Funguje to tedy, ale ne tak, jak chci! Jak zachovat hodnoty ve sloupci B? díky
Tento komentář byl moderátorem webu minimalizován
Ik heb gemerkt dat de 'formule'-methode erg traag werkt bij grote datasets. Een goed alternatief is het gebruik van een draaitabel. Kies dan alleen de rijlabels, je krijgt dan een lijstje met unieke waarden. Het kan zijn dat je wat extra's krijgt "(leeg)" bijvoorbeeld. Je kunt deze er vervolgens uitfilteren. Helaas kun je maar op 1 criterium filteren. Dobře, pokud je to oplossingen voor, je to mnohem složitější.
Tento komentář byl moderátorem webu minimalizován
Chtěl bych být schopen udělat přesně to samé, kromě použití dvou samostatných rozsahů sloupců (B2:B9) a také (D2:D9), je to možné?
Tento komentář byl moderátorem webu minimalizován
Ahoj Anthony,
Výsledky můžete umístit do stejného sloupce jako původní data. Jako například sloupec B v tomto případě.
Ale musíte odkazovat na horní buňku výsledné buňky ve vzorci následovně.
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($B$11:B11, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter
Tento komentář byl moderátorem webu minimalizován
con este procedimiento de filtro se hace de forma muy rapida

1.CZ ESTE EJEMPLO los datos a remover los duplicados estan en la col A de la fila 59 a la 239
2. definujte kritérium filtrace en este caso en la fila d56 el mismo titulo de la lista a remover duplicados y la d57 la dejo en blanco
3. una vez ejecutado se muestran los datos en la fila destino, que en mi caso fue la d59

Range("A59:A239").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("D56:D57"), CopyToRange:=Range("D59"), Unique:=True
Zatím zde nejsou žádné komentáře
Zanechat své připomínky
Odesílání jako host
×
Ohodnoťte tento příspěvek:
0   Postavy
Doporučená umístění

Sociální sítě

Copyright © 2009 - www.extendoffice.com. | Všechna práva vyhrazena. Poháněno ExtendOffice. | |. | Sitemap
Microsoft a logo Office jsou ochranné známky nebo registrované ochranné známky společnosti Microsoft Corporation ve Spojených státech a / nebo jiných zemích.
Chráněno Sectigo SSL