Duplicitné hodnoty v Exceli: ako odstrániť duplikáty alebo jednoducho nájsť a zvýrazniť. Ako odstrániť duplicitné údaje v Exceli Ako odstrániť zhody v Exceli

Každý, kto pracuje s tabuľkami, sa aspoň raz musel potýkať s úlohou odstrániť duplikáty. Excel na to poskytuje svoje vlastné štandardné nástroje, ktoré zvážime v tomto článku. Hneď poviem, že sa budem venovať jednoduchým a bezproblémovým metódam, v inom článku pôjdem hlbšie do vzorcov - toto je jemnejšia práca.

Takže najprv sa rozhodnime, čo potrebujeme. Napríklad máme tabuľku s údajmi, kde sú zhody, možno vo viacerých stĺpcoch. Aké sú úlohy:

Vyhľadajte jedinečné hodnoty/vyhľadajte duplikáty.
Získanie jedinečných hodnôt.
Získanie hodnôt tam, kde sú duplicitné položky.

Prvá metóda je pre Excel 2007 a vyšší.

Vďaka Bohu, pre majiteľov Excelu 2007 a vyššieho nemusíte na nič myslieť. Počnúc touto verziou sa objavil štandardný nástroj - " Odstráňte duplikáty“ na karte Údaje.

Je to jednoduché:

Výhodou tejto metódy je rýchlosť a jednoduchosť. Potrebujete získať iba jedinečné hodnoty - riešenia na dva kliknutia, prijatie a podpísanie.

Nevýhodou je, že ak budeme pracovať hlbšie na stole, nebudeme schopní pochopiť, ktoré údaje boli duplikované (a teda nezistíme dôvod duplikácie), a to niekedy nie je o nič menej dôležité ako získanie normálny zoznam.

Druhým spôsobom je pokročilý filter.

Táto metóda je už dostupná pre verziu Excel 2003, nižšie som ju netestoval, ale pokiaľ si pamätám, táto verzia v porovnaní s verziou 2000 príliš nepokročila.

Táto metóda si bude vyžadovať trochu viac hry, ale podľa toho z nej môžete vydojiť informácie. Použité. Takže:

Výhodou tejto metódy je, že máme na výber - duplikáty vymazať alebo nevymazať, čo znamená, že môžeme s dátami pracovať a analyzovať ich.

Úlohu odstraňovania duplikátov alebo duplicitných hodnôt v Exceli možno vyriešiť rôznymi spôsobmi. V Exceli 2007 a novšom môžete odstrániť duplikáty pomocou štandardných nástrojov Excel 2003 takéto nástroje nemá, ale problém je vyriešený pomocou VBA (Visual Basic for Application).

Odstránenie duplikátov v programe Excel 2003

Ak chcete rýchlo odstrániť duplikáty v programe Microsoft Excel 2003, môžete použiť postup, ktorého programový kód je uvedený nižšie. Procedúra pracuje s vybraným rozsahom buniek, porovnáva hodnotu každej z nich s hodnotami všetkých ostatných, a ak existuje zhoda, odstraňuje duplicitné hodnoty. Pred použitím postupu musíte vybrať rozsah hodnôt.

Možnosť Explicit Sub Delete_Dublikatov_Znachenij() "makro vymaže hodnoty buniek, ak nájde duplikáty Dim iCount As Long, i As Long, j As Long, k As Long Dim Str1 As String, Str2 As String k = 1 iCount = Selection.Cells .Počet pre i = k Do iPočet Str1 = CStr(Selection.Cells(i).Value) If Str1<> <>j And Str1 = Str2 Then Selection.Cells(j).ClearContents Next j End If Next i End Sub

Procedúra, ktorej programový kód je uvedený nižšie, už neodstraňuje duplicitné hodnoty, ale celé bunky s posunom nahor, ktoré obsahujú duplicitné hodnoty.

Možnosť Explicit Sub Delete_Dublikatov_Yacheek() "makro vymaže bunky, ak nájde duplikáty Dim iCount As Long, i As Long, j As Long, k As Long Dim Str1 As String, Str2 As String Dim Group As Range k = 1 iCount = Selection. Bunky .Count For i = k To iCount Str1 = CStr(Selection.Cells(i).Value) If Str1<>"" Potom For j = i To iCount Str2 = CStr(Selection.Cells(j).Value) Ak i<>j A Str1 = Str2 Potom Ak Skupina je Nič Potom _ Nastaviť skupinu = Selection.Cells(j) Inak Nastaviť skupinu = Union(Group, Selection.Cells(j)) End If Next j End If Next i On Error Obnoviť nasledujúcu skupinu. Odstrániť Shift:=xlUp End Sub

Aby sa bunky vymazali s posunom doľava, treba do predposledného riadku namiesto xlUp napísať xlToLeft.

Odstránenie duplikátov v Exceli 2007/2010/2013

Na rýchle odstránenie duplicitných hodnôt v programe Excel 2007 a vyšších je k dispozícii štandardný nástroj - tlačidlo "Odstrániť duplikáty", ktoré sa nachádza na karte "Údaje" v skupine "Nástroje údajov". Ak chcete odstrániť duplicitné hodnoty, musíte vybrať jeden alebo viacero stĺpcov, ktoré obsahujú duplicitné hodnoty.

Nevýhodou tohto nástroja je, že funguje iba s vertikálnymi rozsahmi usporiadanými do stĺpcov. V tomto zmysle sú vyššie uvedené postupy univerzálnejšie.

Ak spustíte záznam makier a zaznamenáte akciu priradenú tlačidlu „Odstrániť duplikáty“, získate makro, ktorého programový kód je uvedený nižšie. Toto makro odstraňuje duplikáty v rozsahu A1:A20.

Sub Removenie_Dublikatov() " makro odstráni duplikáty (opakované hodnoty) v rozsahu A1:A20 aktívneho pracovného hárka ActiveSheet.Range("$A$1:$A$20").RemoveDuplicates Columns:=1, Header:=xlNo End Sub

Ak chcete preniesť tento programový kód do svojho počítača, presuňte kurzor myši nad pole s programovým kódom, kliknite na jedno z dvoch tlačidielv pravom hornom rohu tohto poľa skopírujte kód programu a vložte ho do projektového modulu na vašom počítači (viac informácií o

Pri práci s dátami vzniká periodicky potreba extrahovať jedinečné záznamy. Na tento účel má používateľ programu Excel mnoho spôsobov, ako odstrániť duplicitné hodnoty a filtrovať s možnosťou skrytia alebo zobrazenia. Každá metóda môže byť použitá v každodennej práci alebo pri spracovaní zložitých štatistických údajov. V tomto prípade nie sú potrebné žiadne špeciálne znalosti, stačí minimálna znalosť tabuľkového procesora Excel.

Ako odstrániť duplikáty v Exceli

Na rýchle vyriešenie tohto problému má používateľ prístup k štandardnej funkcii „ Odstraňovanie duplikátov" V tomto prípade možno operáciu vykonať len na vybraných poliach.

Ak chcete odstrániť duplicitné riadky, musíte:

Odstránenie duplikátov v inteligentnej tabuľke

Táto metóda využíva Excel Designer (pokročilý režim s ďalšími funkciami). Návrhár sa aktivuje, keď je vybratá ľubovoľná bunka v oblasti tabuľky. Rovnako ako v prvom prípade sa po aplikácii odstránia duplicitné hodnoty.

Pre tých, ktorí nevedia: inteligentná tabuľka je typ formátovania, po ktorom všetky bunky tabuľky získajú určité vlastnosti. Zároveň Excel považuje takúto tabuľku za jeden celok a nie za súbor buniek a prvkov.

Ak chcete odstrániť duplicitné riadky v takejto tabuľke, mali by ste:

Používame filtrovanie

Pomocou filtrovania môžete skryť duplicitné údaje, ale hodnoty sa neodstránia - možno ich kedykoľvek vrátiť.

Ak chcete formátovať a nájsť duplicitné hodnoty týmto spôsobom:

Podmienené formátovanie

Túto metódu môžete použiť na vyhľadávanie identických záznamov v bunkách. Pre pohodlie si môžete prispôsobiť farbu zvýraznenia. Na jeho implementáciu musíte použiť preddefinované pravidlo výberu - “ Duplicitné hodnoty...».

Ak chcete nájsť duplikáty v Exceli, musíte:

Pomocou vzorca

Používanie vstavanej funkcie " VYHĽADÁVANIE» môžete nájsť duplicitné prvky vo vlastnom rozsahu. "MATCH" - Vráti relatívnu pozíciu v poli prvku zodpovedajúcu zadanej hodnote, berúc do úvahy zadané poradie.

Ak chcete zvýrazniť duplicitné riadky v programe Excel, musíte:

Vysvetlenie: Vo vzorci je špecifikovaná funkcia „MATCH“, ktorá vyhľadáva opakovania v stĺpci „B“ počnúc bunkou „B3“.

Skopírujte jedinečné riadky na nové miesto

Na dokončenie tohto budete potrebovať:

Používanie kontingenčných tabuliek

Na zoskupenie duplicitných prvkov môžete použiť kontingenčnú tabuľku (jednoduchší koncept je zovšeobecnený).

Aby táto metóda fungovala, budete potrebovať:

Okrem toho si môžete pozrieť video o odstraňovaní duplikátov v Exceli.

Odstraňujú sa duplikáty v Tabuľkách Google

V službe Google môžete vyhľadávať iba jedinečné záznamy v bunkách (neexistujú žiadne metódy na odstránenie duplikátov):

použitím kontingenčné tabuľky(počítanie duplicitných hodnôt);
používaním funkciejedinečný(výsledok vypíšte z poľa);
použitím rozšírenia tretích strán;

Príklad použitia funkcie " jedinečný»:

Ak chcete zobraziť jedinečné záznamy, musíte použiť vzorec «= jedinečný (skontrolovať rozsah)»:

Malý syn prišiel k otcovi a malý sa spýtal...

Nie takto nie. V skutočnosti prišiel zamestnanec a povedal – nemali by sme si nainštalovať Excel 2010? Zo skúsenosti viem, že potrebuje párkrát za deň vyplniť malú tabuľku, nič zložité. Preto okamžite vyvstala logická otázka - prečo to potrebujete? Na čo je logická odpoveď - a tam môžete odstrániť duplicitné bunky jedným príkazom. Áno. To znamená 3-4 tr. na odstránenie duplikátov. Ale musím povedať, že vo všeobecnosti mám veľmi zlý vzťah k neoprávneným výdavkom v podnikaní. Jedna vec je, keď sa niečo vyžaduje, aby priamo plnilo funkciu, ktorá sa nedá vykonávať v ničom inom. Alebo to zaberie toľko času, že je lacnejšie to optimalizovať alebo na to napísať špeciálny program – teraz napríklad píšeme jeden z nich za jeden a pol dolára. Ale je to iná vec, keď niekto chce stráviť o 10 minút dlhšie na VKontakte počas pracovnej doby a je jednoducho príliš lenivý na to, aby zistil, ako stlačiť niekoľko tlačidiel.

Dobre, teraz vám poviem, ako odstrániť duplikáty v Exceli 2003, a môžete pokračovať a zistiť, prečo by ste ešte mohli potrebovať 2010 (nie prečo to potrebujem ja - viem to veľmi dobre :-)).

Najjednoduchší spôsob a) je odstrániť duplicitné hodnoty programu Excel:

1. Vezmite, vyberte rozsah buniek s duplikátmi, kliknite na Údaje -> Filter -> Rozšírený filter...

3. Dostaneme výsledok, ktorý sa dá urobiť pomocou Ctrl+C - Ctrl+V na požadované miesto/list.

Teraz možnosť B), pre tých, ktorí sa neboja ťažkostí :)

1. Naľavo od ľavého stĺpca našej tabuľky vložte ďalší stĺpec (povedzme, že sme mali A - vložte ďalšie A, aby sa z nášho stalo B), a zadajte doň sériové čísla (jednoduchým zadaním čísel 1 a 2 do bunky, zvýraznením týchto dvoch buniek a dvojitým kliknutím na čiernu bodku v pravom dolnom rohu všetko rozšírite na koniec rozsahu). Budeme to potrebovať neskôr, ak chceme obnoviť poradie záznamov, ak to nie je dôležité, nemusíme to robiť. Bude to vyzerať asi takto:

3. Zoraďte zoznam podľa stĺpca B, povedzte vzostupne.

4. Do bunky C2 vložte vzorec =IF(B2=B1;0;1), ktorý porovnáva každú hodnotu s predchádzajúcou. Ak je riadok duplikát, potom mu bude priradená hodnota 0, ak nie, potom 1. Samozrejme, hodnoty B2 a B1 sú v mojom príklade, všetko závisí od toho, koľko stĺpcov je v tabuľky.

5. Kliknutím na bod zakrúžkovaný červenou farbou v pravom dolnom rohu bunky rozšírite vzorec na celý stĺpec (podobne ako sme vkladali sériové čísla):

6. So získaným výsledkom stlačte Ctrl+C, prejdite na Upraviť -> Prilepiť špeciálne

7. V dialógovom okne, ktoré sa otvorí, vyberte - Vložiť hodnoty

8. Teraz vyberte prvé tri bunky v riadku 2, so stlačeným Shiftom kliknite na spodný okraj výberu, čím vyberiete všetko od A2 po C12. Kliknite na Údaje -> Triedenie, zoraďte podľa stĺpca C, zostupne (to je dôležité – zoradiť zostupne! Ak by sme duplikátom priradili 1, nie 0, museli by sme zoradiť opačne, vzostupne). Nebudem poskytovať snímku obrazovky, pretože je úplne rovnaká ako kroky 2 a 3.

9. Vyberte stĺpec C, stlačte Ctrl-F, do vyhľadávacieho formulára zadajte 0 a vyhľadajte prvú bunku v tomto stĺpci s nulou.

10. Označte celý riadok od A do C, v ktorom bola prvýkrát nájdená nula, so stlačeným klávesom Shift kliknite myšou na spodný okraj výberu, čím vyberiete všetky hodnoty nižšie. Potom si s nimi robíme, čo chceme: môžeme ich vymazať do pekla, alebo môžeme tieto duplikáty niekam skopírovať. Predpokladajme, že bol odstránený.

11. Odstránime hodnoty zo stĺpca C - to tiež zohralo úlohu.

12. Označte celé stĺpce A a B, kliknite na Údaje -> Zoradenie a zoraďte podľa stĺpca A (v mojom prípade podľa čísel) vo vzostupnom poradí.

Keď v Exceli pracujete s obrovským množstvom údajov, veľmi ľahko sa môže stať, že sa omylom pomýlite a znova zadáte rovnaké údaje. Takto vznikajú duplikáty. Tým sa samozrejme zväčšuje objem všetkých súhrnných informácií a v niektorých prípadoch dochádza k zámene vzorcov, ktoré majú zhrnúť a vypočítať rôzne premenné. Duplikáty môžete nájsť manuálne, ale prečo, ak existuje niekoľko spôsobov, ako automaticky odstrániť duplicitné riadky v Exceli.

Metóda 1: Štandardné odstránenie duplikátov

Najjednoduchším spôsobom, ako je čiara, je použiť príslušný nástroj umiestnený na páse s nástrojmi.

Potrebujete teda:

Podržte ľavé tlačidlo myši a vyberte požadovaný rozsah buniek, v rámci ktorých sa majú vyhľadať a automaticky odstrániť duplicitné riadky.
Prejdite na kartu Údaje.
Kliknite na nástroj „Odstrániť duplikáty“, ktorý sa nachádza v skupine „Nástroje údajov“.
V zobrazenom okne začiarknite políčko vedľa položky Moje údaje obsahujú hlavičky.
Začiarknite políčka vedľa názvov stĺpcov, v ktorých sa bude vyhľadávanie vykonávať. Upozorňujeme, že ak sú začiarknuté všetky políčka, riadky, ktoré sa úplne opakujú vo všetkých stĺpcoch, sa budú považovať za duplikáty. Ak teda chcete odstrániť duplikáty z jedného stĺpca, musíte ponechať iba jedno začiarkavacie políčko vedľa jeho názvu.
Kliknite na „OK“.

Hneď po kliknutí na tlačidlo sa spustí vyhľadávanie duplikátov vybranej oblasti a následne ich vymazanie. V dôsledku toho sa zobrazí okno, v ktorom sa zobrazí správa. Teraz viete, ako odstrániť duplicitné riadky v programe Excel, ale iba prvá metóda, druhá je ďalšia.

Metóda 2: Použitie inteligentnej tabuľky

Duplikáty je možné odstrániť podobným spôsobom, ako je popísané v tomto článku. Tentokrát sa v príbehu o odstránení duplicitných riadkov v Exceli použije „inteligentná tabuľka“.

Ak chcete odstrániť duplikáty v programe Excel, musíte urobiť nasledovné:

Rovnako ako v predchádzajúcej metóde najprv vyberte rozsah buniek, z ktorých je potrebné odstrániť duplikáty.
Kliknite na tlačidlo "Formátovať ako tabuľku", ktoré sa nachádza na karte "Domov" v skupine nástrojov "Štýly".
Z rozbaľovacej ponuky vyberte štýl, ktorý sa vám páči (akýkoľvek).
V zobrazenom okne musíte potvrdiť predtým zadaný rozsah buniek a ak sa nezhoduje, znova ho priradiť. Začiarknite tiež políčko vedľa položky „Tabuľka s hlavičkami“, ak áno, kliknite na tlačidlo „OK“.
Inteligentný stôl bol vytvorený, ale to nie je všetko. Teraz musíte vybrať ľubovoľnú bunku tabuľky, aby sa na kartách objavila položka „Návrhár“ a prejdite priamo na túto kartu.
Na páse s nástrojmi kliknite na tlačidlo „Odstrániť duplikáty“.

Potom sa zobrazí okno na odstránenie duplicitných riadkov. Bude to podobné tomu, čo bolo uvedené v prvej metóde, takže všetky nasledujúce akcie vykonajte v súlade s prvými pokynmi.

Záver

Pozreli sme sa teda na dva spôsoby, ako odstrániť riadky s duplicitnými hodnotami v Exceli. Ako vidíte, nie je v tom nič zložité a pomocou pokynov môžete túto operáciu dokončiť za pár sekúnd. Príklad bol uvedený vo verzii programu 2016, ale rovnakým spôsobom môžete odstrániť duplicitné riadky v Exceli 2010 a iných verziách.