Tento tutoriál ukáže, ako odstrániť duplikáty pomocou metódy RemoveDuplicates vo VBA.
Metóda RemoveDuplicates
Keď sa údaje importujú alebo prilepia do pracovného hárka programu Excel, často môžu obsahovať duplicitné hodnoty. Možno budete musieť vyčistiť prichádzajúce údaje a odstrániť duplikáty.
Našťastie v objekte Range objektu VBA existuje jednoduchá metóda, ktorá vám to umožňuje.
| 1 | Rozsah („A1: C8“). Odstrániť duplikáty stĺpcov: = 1, hlavička: = xlÁno |
Syntax je:
RemoveDuplicates ([stĺpce], [hlavička]
- [Stĺpce] - Zadajte, v ktorých stĺpcoch sa kontrolujú duplicitné hodnoty. Všetky stĺpce sa veľmi zhodujú, aby sa považovali za duplikáty.
- [Hlavička] - Majú údaje hlavičku? xlNo (predvolené), xlYes, xlYesNoGuess
Technicky sú oba parametre voliteľné. Ak však nezadáte argument Stĺpce, nebudú odstránené žiadne duplikáty.
Predvolená hodnota pre hlavičku je xlNo. Tento argument je samozrejme lepšie špecifikovať, ale ak máte riadok hlavičky, je nepravdepodobné, že sa bude zhodovať ako duplikát.
RemoveDuplicates Usage Notes
- Pred použitím metódy RemoveDuplicates musíte zadať rozsah, ktorý sa má použiť.
- Metóda RemoveDuplicates odstráni všetky riadky s nájdenými duplicitami, ale zachová pôvodný riadok so všetkými hodnotami.
- Metóda RemoveDuplicates funguje iba na stĺpcoch a nie na riadkoch, ale na opravu tejto situácie je možné napísať kód VBA (pozri neskôr).
Ukážkové údaje pre príklady VBA
Aby sa ukázalo, ako príkladový kód funguje, používajú sa nasledujúce ukážkové údaje:
Odstráňte duplicitné riadky
Tento kód odstráni všetky duplicitné riadky iba na základe hodnôt v stĺpci A:
| 123 | Sub RemoveDupsEx1 ()Rozsah („A1: C8“). Odstrániť duplikáty stĺpcov: = 1, hlavička: = xlÁnoKoniec pod |
Všimnite si, že sme výslovne definovali rozsah „A1: C8“. Namiesto toho môžete použiť UsedRange. Použitý rozsah určí posledný použitý riadok a stĺpec vašich údajov a použije príkaz RemoveDuplicates na celý tento rozsah:
| 123 | Sub RemoveDups_UsedRange ()ActiveSheet.UsedRange.RemoveDuplicates stĺpce: = 1, hlavička: = xlÁnoKoniec pod |
UsedRange je neuveriteľne užitočný, pretože odstraňuje potrebu explicitne definovať rozsah.
Po spustení týchto kódov bude váš pracovný hárok teraz vyzerať takto:
Všimnite si toho, pretože pretože bol zadaný iba stĺpec A (stĺpec 1), duplikát „Jablká“ predtým v riadku 5 bol odstránený. Množstvo (stĺpec 2) je však odlišné.
Ak chcete odstrániť duplikáty a porovnať viacero stĺpcov, môžeme tieto stĺpce určiť pomocou metódy Array.
Odstráňte duplikáty porovnaním viacerých stĺpcov
| 123 | Sub RemoveDups_MultColumns ()ActiveSheet.UsedRange.RemoveDuplicates stĺpce: = Array (1, 2), Header: = xlYesKoniec pod |
Pole hovorí VBA, aby porovnala údaje pomocou stĺpcov 1 a 2 (A a B).
Stĺpce v poli nemusia byť v poradí za sebou.
| 123 | Sub SimpleExample ()ActiveSheet.UsedRange.RemoveDuplicates stĺpce: = Array (3, 1), Header: = xlYesKoniec pod |
V tomto prípade sa na duplicitné porovnanie používajú stĺpce 1 a 3.
Tento príklad kódu používa na kontrolu duplikátov všetky tri stĺpce:
| 123 | Sub SimpleExample ()ActiveSheet.UsedRange.RemoveDuplicates stĺpce: = Array (1, 2, 3), Header: = xlYesKoniec pod |
Odstránenie duplicitných riadkov z tabuľky
RemoveDuplicates možno tiež použiť na tabuľku programu Excel úplne rovnakým spôsobom. Syntax je však mierne odlišná.
| 1234 | Sub SimpleExample ()ActiveSheet.ListObjects ("Table1"). DataBodyRange.RemoveDuplicates stĺpce: = pole (1, 3), _Hlavička: = xlÁnoKoniec pod |
Tým sa odstránia duplikáty v tabuľke na základe stĺpcov 1 a 3 (A a C). Neupraví to však farebné formátovanie tabuľky a v spodnej časti tabuľky uvidíte farebné prázdne riadky.
Odstráňte duplikáty z polí
Ak potrebujete z poľa odstrániť duplicitné hodnoty, svoje pole môžete samozrejme odoslať do programu Excel, použiť metódu RemoveDuplicates a pole znova importovať.
Napísali sme však aj postup VBA na odstránenie duplikátov z poľa.
Odstránenie duplikátov z riadkov údajov pomocou VBA
Metóda RemoveDuplicates funguje iba na stĺpcoch údajov, ale s trochou myslenia „vybaleného z krabice“ môžete vytvoriť postup VBA, ktorý sa bude zaoberať riadkami údajov.
Predpokladajme, že vaše údaje vo vašom hárku vyzerajú takto:
V stĺpcoch B a E máte rovnaké duplikáty ako predtým, ale nemôžete ich odstrániť pomocou metódy RemoveDuplicates.
Odpoveďou je použiť VBA na vytvorenie ďalšieho pracovného hárka, skopírovať do neho údaje, transponovať ich do stĺpcov, odstrániť duplikáty a potom skopírovať späť a transponovať späť do riadkov.
| 12345678910111213141516171819202122232425262728293031323334353637 | Sub DuplicatesInRows ()„Vypnite aktualizáciu obrazovky a upozornenia - chceme, aby kód bežal hladko bez toho, aby to používateľ videl'čo sa dejeApplication.ScreenUpdating = FalseApplication.DisplayAlerts = False„Pridajte nový pracovný listSheets.Add After: = ActiveSheet„Zavolajte nový pracovný hárok“ CopySheet ”ActiveSheet.Name = "CopySheet"„Skopírujte údaje z pôvodného pracovného hárkaHárky („DataInRows“). UsedRange.Copy'Aktivujte nový hárok, ktorý bol vytvorenýTabuľky („CopySheet“). Aktivujte„Prilepením transponujte údaje tak, aby boli teraz v stĺpcochActiveSheet.Range ("A1"). PasteSpecial Paste: = xlPasteAll, Prevádzka: = xlNone, SkipBlanks: = _Falošné, transponovať: = Pravda„Odstráňte duplikáty pre stĺpce 1 a 3ActiveSheet.UsedRange.RemoveDuplicates stĺpce: = Array (1, 3), Header _: = xlÁno„Vymažte údaje v pôvodnom pracovnom hárkuHárky („DataInRows“). UsedRange.ClearContents'Skopírujte stĺpce údajov z nového vytvoreného pracovného hárkaHárky („kópia“). UsedRange.Copy'Aktivujte pôvodný listTabuľky („DataInRows“). Aktivujte„Prilepením transponujete neduplicitné údajeActiveSheet.Range ("A1"). PasteSpecial Paste: = xlPasteAll, Prevádzka: = xlNone, SkipBlanks: = _Falošné, transponovať: = Pravda'Odstráňte hárok kópie - už nie je potrebnýHárky („Copysheet“). Odstrániť'Aktivujte pôvodný listTabuľky („DataInRows“). Aktivujte'Znova zapnite aktualizáciu obrazovky a upozorneniaApplication.ScreenUpdating = TrueApplication.DisplayAlerts = PravdaKoniec pod |
Tento kód predpokladá, že pôvodné údaje v riadkoch sú uložené v pracovnom hárku s názvom „DataInRows“
Po spustení kódu bude váš pracovný hárok vyzerať takto:
Duplikát „Jablká“ v stĺpci E bol teraz odstránený. Užívateľ je späť v čistej polohe, bez vonkajších hárkov a celý proces bol vykonaný hladko bez blikania obrazovky alebo výstražných správ.
