Duplicity v Exceli odstránite cez Údaje → Odstrániť duplicity. Pred potvrdením si však pripravte kópiu údajov a určte, podľa ktorých stĺpcov sa majú záznamy porovnávať. Excel ponechá prvý výskyt a ďalšie zhodné záznamy odstráni.
Ak si chcete opakovania najprv pozrieť, použite podmienené formátovanie. Ak potrebujete samostatný zoznam bez duplicít a pôvodné údaje chcete zachovať, pomôže funkcia UNIQUE alebo rozšírený filter. Rozhodujúce je, či čistíte pôvodnú tabuľku, alebo z nej iba vytvárate nový výstup.
Najprv určite, čo je vo vašej tabuľke duplicita
Rovnaké meno zákazníka ešte neznamená zbytočný riadok. Jeden človek môže mať viac objednávok a dvaja ľudia môžu mať rovnaké meno. Pri čistení preto uprednostnite spoľahlivý identifikátor, napríklad číslo objednávky, alebo vhodnú kombináciu stĺpcov.
Rozdiel ukazuje tento ilustračný príklad:
| ID zákazníka | ID objednávky | Suma |
|---|---|---|
| Z001 | O101 | 30 € |
| Z001 | O102 | 45 € |
| Z001 | O101 | 30 € |
| Z002 | O103 | 20 € |
Ak pri odstraňovaní porovnáte všetky tri stĺpce, zmizne iba druhá kópia objednávky O101. Zostanú tri záznamy. Ak porovnáte iba ID zákazníka, zostanú dva záznamy a prídete aj o odlišnú objednávku O102.
Odstránenie duplicít údaje nesčíta ani nezlúči. Ak chcete napríklad spočítať hodnotu všetkých objednávok zákazníka, potrebujete súhrn údajov, napríklad kontingenčnú tabuľku.
Ako nájsť a zvýrazniť duplicity v Exceli
Na rýchlu kontrolu jedného stĺpca použite pravidlo Duplicitné hodnoty. Údaje zostanú na svojom mieste a opakované hodnoty dostanú zvolenú farbu.
- Označte bunky, ktoré chcete preveriť, napríklad
A2:A100. Hlavičku stĺpca vynechajte. - Otvorte Domov → Podmienené formátovanie → Pravidlá zvýrazňovania buniek → Duplicitné hodnoty.
- Vyberte zvýraznenie duplicitných hodnôt, nastavte farbu a potvrďte OK.
Takýto postup na zvýraznenie duplicít uvádza aj Microsoft. Pravidlo označí všetky výskyty opakovanej hodnoty vrátane prvého. Dve zafarbené bunky teda neznamenajú, že máte obe odstrániť.
Pozor pri označení viacerých stĺpcov naraz: pravidlo hľadá opakované hodnoty buniek v celom výbere. Neoveruje, či sa zhoduje celý záznam. Na kontrolu dvojice stĺpcov, napríklad zákazníka a objednávky, použite vzorec COUNTIFS uvedený nižšie.
Ako odstrániť duplicitné riadky krok za krokom
Pred mazaním si skopírujte celú tabuľku do iného hárka alebo uložte kópiu zošita. Nasledujúci postup mení zdrojové údaje. Názvy ponúk vychádzajú zo slovenského Excelu pre Windows; v anglickom rozhraní hľadajte príkaz Remove Duplicates.
- Označte celý súvisiaci rozsah vrátane všetkých stĺpcov a hlavičiek, napríklad
A1:C100. Ak sú v údajoch prázdne riadky alebo stĺpce, skontrolujte, že výber obsahuje aj záznamy za nimi. - Na karte Údaje vyberte Odstrániť duplicity v skupine Nástroje pre údaje.
- V dialógovom okne skontrolujte nastavenie hlavičiek. Názvy stĺpcov sa nemajú porovnávať ako bežný záznam.
- Začiarknite stĺpce, podľa ktorých má Excel posudzovať zhodu. Pre úplne rovnaké riadky vyberte všetky dátové stĺpce. Pre zhodu konkrétneho identifikátora vyberte iba príslušný stĺpec alebo potrebnú kombináciu.
- Potvrďte OK. Prečítajte si počet odstránených duplicít a skontrolujte zostávajúce záznamy oproti kópii.
Začiarknuté stĺpce určujú podmienku zhody. Pri jej splnení sa odstráni záznam zo všetkých stĺpcov vybraného rozsahu, aj z nezačiarknutých. Microsoft zároveň spresňuje rozsah zásahu: bunky mimo spracúvaného rozsahu alebo tabuľky sa nezmenia ani neposunú.
Preto nečistite samostatne iba stĺpec s menami, ak k nemu vedľa patria adresy, sumy či objednávky. Mohli by sa rozísť údaje, ktoré pôvodne tvorili jeden záznam. Celý súvisiaci rozsah vyberte pred otvorením nástroja; porovnávacie stĺpce obmedzte až v jeho okne.
Ako ponechať najnovší alebo posledný záznam
Keďže nástroj ponecháva prvý výskyt, požadovaný záznam musí byť pred odstraňovaním navrchu. Z toho vyplýva praktický postup pre tabuľku, v ktorej chcete nechať najnovšie údaje o každom zákazníkovi:
- Označte celú tabuľku a cez Údaje → Zoradiť ju zoraďte podľa dátumu aktualizácie od najnovšieho. Stĺpec musí obsahovať skutočné dátumy, aby poradie nebolo iba textové.
- Spustite Odstrániť duplicity a ako podmienku zhody vyberte ID zákazníka. Dátum aktualizácie do porovnávania nezahrňte.
- Skontrolujte, že pri každom zákazníkovi zostal požadovaný záznam. Ak majú dva záznamy rovnaký dátum, určte ďalšie kritérium poradia alebo ich skontrolujte ručne.
Ak chcete ponechať posledný výskyt podľa pôvodného poradia, pred zoradením pridajte pomocný stĺpec s pevnými poradovými číslami. Zoraďte podľa neho zostupne a až potom odstráňte duplicity. Pomocný stĺpec nezačiarkujte ako podmienku zhody.
Ako vytvoriť zoznam bez duplicít bez mazania pôvodných údajov
Funkcia UNIQUE: samostatný zoznam, ktorý sa prepočítava
Funkcia UNIQUE je dostupná v Exceli pre Microsoft 365, Exceli 2024 a 2021 vrátane verzií pre Mac a vo webovom Exceli. V Exceli 2019 a 2016 ju nenájdete.
Vzorec zadajte do prázdnej bunky mimo zdrojovej tabuľky. Vyberte skutočne použitý rozsah údajov bez hlavičky; číslo posledného riadka v ukážkach prispôsobte svojim dátam.
Vzorce v tomto návode používajú bodkočiarku medzi argumentmi. Ak ju váš Excel neprijme, môže vyžadovať čiarku. Rozhodujú regionálne nastavenia a nastavenia Excelu, nielen jazyk ponúk.
| Čo potrebujete | Vzorec |
|---|---|
| Z každého odlišného údaja v stĺpci jednu položku | =UNIQUE(A2:A100) |
| Odlišné riadky podľa kombinácie stĺpcov A až C | =UNIQUE(A2:C100) |
| Iba hodnoty, ktoré sa v stĺpci vyskytujú presne raz | =UNIQUE(A2:A100;0;1) |
Posledná možnosť má iný význam než bežný zoznam bez opakovaní. Zo vstupu Ján, Ján, Eva vráti prvý vzorec položky Ján, Eva, posledný iba Eva. Hodnota 0 v druhom argumente znamená porovnávanie riadkov a 1 v treťom zapína výber položiek s jediným výskytom.
Výsledok sa automaticky rozšíri do susedných buniek. Tie musia byť voľné; vzorec s takýmto výstupom zároveň nepatrí dovnútra excelovej tabuľky vytvorenej cez Vložiť → Tabuľka. Pri chybe #SPILL! skontrolujte, či výstupu niečo neprekáža, alebo vzorec presuňte na voľné miesto. Podrobnosti vysvetľuje Microsoft v návode na dynamické polia a rozšírenie výsledku vzorca.
Zmeny v zadanom rozsahu sa premietnu do výsledku. Ak však nové údaje pridáte pod riadok 100, rozsah A2:A100 ich nezahrnie. Upravte ho alebo použite odkaz na stĺpec excelovej tabuľky, ktorý sa prispôsobuje pribúdajúcim riadkom.
Rozšírený filter: jednorazový výstup aj bez UNIQUE
V desktopovom Exceli môžete použiť rozšírený filter s výberom jedinečných záznamov. Hodí sa aj vtedy, keď vaša verzia funkciu UNIQUE nepozná.
- Označte stĺpec alebo súvisiaci rozsah vrátane hlavičiek.
- Vyberte Údaje → Rozšírené v skupine Zoradiť a filtrovať.
- Zvoľte Kopírovať do iného umiestnenia a do poľa Kopírovať do zadajte začiatok dostatočne veľkej voľnej oblasti na tom istom hárku.
- Začiarknite Iba jedinečné záznamy a potvrďte OK.
Pri viacerých stĺpcoch sa posudzuje ich spoločná kombinácia. Skopírovaný výsledok sa po zmene zdroja sám neprepočíta; filter musíte spustiť znova. Možnosť Filtrovať zoznam na mieste opakované záznamy iba skryje. Bežné zapnutie filtra cez šípky v hlavičke samo osebe duplicity neodstráni.
Ako spočítať opakovania a označiť zhodu vo viacerých stĺpcoch
COUNTIF ukáže počet výskytov jednej hodnoty
Ak máte v stĺpci A vyplnené identifikátory, do voľného pomocného stĺpca, napríklad do bunky D2, zadajte:
=COUNTIF($A$2:$A$100;A2)Vzorec skopírujte nadol po posledný záznam. Výsledok 1 znamená jediný výskyt, vyššie číslo opakovanie. Znamienka $ držia prehľadávaný rozsah na mieste, zatiaľ čo odkaz A2 sa pri kopírovaní mení. Prázdne identifikátory posudzujte osobitne.
COUNTIF nerozlišuje veľké a malé písmená: ABC a abc započíta spolu. Funkciu môžete použiť priamo v bunke aj ako súčasť pravidla podmieneného formátovania.
COUNTIFS preverí kombináciu dvoch stĺpcov
Predpokladajme, že stĺpec A obsahuje ID zákazníka, B číslo objednávky a C sumu. Ak chcete zvýrazniť celé záznamy so zhodnou dvojicou A a B, označte A2:C100 tak, aby aktívnou bunkou bola A2. Otvorte Domov → Podmienené formátovanie → Nové pravidlo a vyberte možnosť použiť vzorec na určenie formátovaných buniek. Zadajte:
=AND($A2<>"";$B2<>"";COUNTIFS($A$2:$A$100;$A2;$B$2:$B$100;$B2)>1)Vyberte farbu výplne a potvrďte pravidlo. COUNTIFS započíta riadok pri splnení všetkých zadaných kritérií. Tento vzorec zvýrazní zhodné dvojice zákazníka a objednávky vrátane ich prvého výskytu; riadky s prázdnym A alebo B vynechá. Sumy v stĺpci C môžu byť odlišné, preto si ich pred mazaním prezrite.
Pri oboch funkciách majú znaky * a ? v kritériách význam zástupných znakov. Ak sú doslovnou súčasťou identifikátora, jednoduché vzorce vyššie nepoužívajte bez úpravy kritérií: doslovná hviezdička sa zapisuje ako ~* a otáznik ako ~?.
Prečo Excel niektoré zdanlivé duplicity nenájde
Medzery a neviditeľné znaky po importe
Pri údajoch z webu, exportov alebo po prevode PDF do Excelu skontrolujte medzery na začiatku a na konci textu. Problémom môže byť aj pevná medzera, ktorú samotná funkcia TRIM neodstráni.
Pre bežné textové údaje môžete v pomocnom stĺpci použiť tento čistiaci vzorec a skopírovať ho nadol:
=TRIM(CLEAN(SUBSTITUTE(A2;UNICHAR(160);" ")))SUBSTITUTE nahradí pevnú medzeru bežnou, CLEAN odstráni riadiace znaky ASCII s hodnotami 0 až 31 a TRIM upraví nadbytočné bežné medzery. CLEAN však neodstraňuje všetky neviditeľné znaky Unicode, takže nejde o univerzálnu opravu každého importu.
Najprv porovnajte výsledok s originálom. Vzorec mení aj viacnásobné medzery medzi slovami a odstraňuje tabulátory či konce riadkov. Použite ho tam, kde tým nemeníte význam údajov. Duplicity potom hľadajte podľa vyčisteného pomocného stĺpca; pri mazaní naďalej vyberte celý súvisiaci rozsah.
Typ údajov a rozdielne formáty dátumov
Skontrolujte, či sa v jednom stĺpci nemiešajú čísla, text a dátumy. Zhodný vzhľad ešte nemusí znamenať zhodný obsah. Pri identifikátoroch zároveň neprevádzajte všetko automaticky na čísla: napríklad nuly na začiatku kódu môžu byť dôležité.
Pri filtrovaní jedinečných hodnôt a odstraňovaní duplicít Microsoft upozorňuje aj na číselný formát. V dokumentácii k porovnávaniu hodnôt uvádza, že rovnaký dátum zobrazený raz ako 8.3.2006 a druhýkrát ako 8. marec 2006 môže byť vyhodnotený ako dve odlišné položky. Pred čistením preto zjednoťte typ údajov aj ich formát a výsledok skontrolujte na malej vzorke. Toto upozornenie sa týka uvedených nástrojov, nie všeobecne všetkých excelových funkcií.
Medzisúčty, prehľady a kontingenčné tabuľky
Ak nástroj odmieta pracovať s údajmi obsahujúcimi prehľad alebo medzisúčty, najprv ich odstráňte z pracovnej kópie. Duplicity je vhodnejšie riešiť v zdrojových záznamoch ešte pred vytváraním súhrnov. Pravidlo Duplicitné hodnoty navyše nemožno použiť v oblasti hodnôt kontingenčnej tabuľky.
Čo robiť, keď ste odstránili nesprávne riadky
Hneď použite Späť alebo Ctrl + Z. Samotné uloženie zošita túto možnosť automaticky neruší: podľa dokumentácie k vráteniu akcií možno zmeny vrátiť aj po uložení, pokiaľ sú ešte dostupné v histórii. Ďalšie užitočné kombinácie nájdete v prehľade klávesových skratiek pre Excel.
Ak už Späť nepomôže, siahnite po pôvodnej kópii. Pri súbore uloženom v OneDrive alebo SharePointe môžete skúsiť históriu verzií súboru: vyberte súbor, otvorte jeho kontextovú ponuku, zvoľte História verzií a vyhľadajte stav pred mazaním.
Obnovenie staršej verzie vráti celý zošit do skoršieho stavu. Ak ste medzitým doplnili potrebné údaje, najprv si uložte aj aktuálnu kópiu. Dostupnosť starších verzií si overte pri konkrétnom súbore; pri pracovnom alebo školskom účte závisí aj od nastavení organizácie.
