Duplikációk kiemelése 500.000 sornál

Üdvözletem,

Az ismétlődések (duplikációk) azonosítása/eltávolítása gyakori probléma, írtam is már róla egy bejegyzést, mely itt olvasható.

De mi van akkor, ha:
– a duplikációkat tartalmazó cellákat ki akarjuk színezni (vizuális ellenőrzés végett) és
– mindezt egy olyan oszlopban, mely 500.000 sort tartalmaz?

Aki próbált már Excelben több százezer vagy akár egy millió sort hozzáadni, az tudja, hogy könnyen megnövekszik a fájlméret, a munkafüzet lomha és lassú lesz, esetleg le is fagy.

Teszteljük:
– üres munkalapon az A1-es legyen az aktív cella
– jelöljük ki az „A” oszlopot
– írjunk be mondjuk 1-et, majd CTRL + ENTER

Most az „A” oszlop minden cellájában 1-es szerepel. Elmentjük, bezárjuk a fájlt és a mérete máris 5,4 MB. Képzeljük el ugyanezt több tucat oszloppal.
Vagyis az Excelben (2007-től) az 1.048.576 sor és a 16.384 oszlop erősen elméleti maximum.

Az alábbiakban olyan lehetőségeket ismertetek, mely az 500.000 adatsoros oszlopban kijelöli a duplikációkat. Kiinduló állapot: véletlenszámokkal töltöttem fel a C2:C5000001-es tartományt.

A következő metódusokat mindenképpen úgy próbáld ki, hogy csak az adott munkafüzet van megnyitva, mivel – az utolsó két módszer kivételével – nagy valószínűséggel szanaszét fog fagyni a munkafüzet és vele együtt az egész Excel is!

Módszerek:

I. a legkézenfekvőbbnek tűnő Feltételes formázás:

C2:C500001-es tartomány kijelölése -> Kezdőlap menü -> Feltételes formázás -> Cellakiemelési szabályok -> Ismétlődő értékek:

Majd itt OK:

Pár másodperc gondolkodás után pár cella piros háttérszínt kap, minden jónak tűnik. Azonban ha görgetjük a képernyőt, szaggat. Ennek oka, hogy a feltételes formázás folyamatosan kiértékelődik. Görgetés helyett tegyünk rá egy szűrőt és próbáljuk meg leszűrni a piros színű cellákra. A szűrés ikonra kattintva lefagy az Excel, a processzor felpörög, a régebbi gépeknek olyan hangja lesz a ventilátor miatt, mintha fel akarnának szállni 🙂

Ez tehát nem nyert, nézzük a következő opciókat.

II. Power Query-vel ezen link alapján (angolul)

Ezen most nem megyünk végig lépésről lépésre. A lelke az egésznek a csoportosítás PQ-ben, jelen esetben a számokat tartalmazó – itt „Véletlenszámok” nevű – oszlop soraié, mely az ismétlődéseket írja ki a „Duplicates” oszlopban:

Majd az oda-vissza, ún. kibontás nyílra kattintva (előző képen piros kerettel jelölve) visszarakjuk az „Index” oszlopot, hogy növekvő rendezéssel visszakapjuk a „Véletlenszám” oszlop sorainak eredeti sorrendjét:

Majd az „Index” oszlopot töröljük és a „Duplicates” oszlopban kivesszük a 0 értékeket, mivel ennél nincsenek ismétlődések:

Végül a PQ betölti a kimeneti adatokat egy táblázatba, az eredmény így néz ki:

Ezután már csak a cellák színezése marad.
Persze a „Duplicates” oszlopra nincs szükségünk – vagyis ezt is törölhetnénk PQ-ben – csupán az ellenőrzés végett maradt bent.
A Power Query képes kezelni több millió, vagy akár több tízmillió sort is. Gondolhatnánk, ez az 500.000 sor meg sem kottyan neki. Azonban ha frissítjük a PQ lekérdezést Excelben, meglepő módon 25-30 másodpercig homokórázik, mire frissíti a lekérdezést és ezzel együtt a munkalapon lévő táblázatot.

III. Irányított szűrés: itt csak pár értékkel dolgozom

– E1-es cellába írjunk be egy nevet, pl.: Feltétel
– E2-es cellába képlet a duplikációk azonosítására -> =DARABTELI(C:C;C2)<>1
Most itt járunk, a sárga színű cellák tartalmazzák a duplikációkat:

Ezután az aktív cella legyen a „C” oszlopban, majd:
Adatok menü -> Irányított: majd az alábbi négy opciót beállítani és OK.

Eredmény a „G” oszlopban:

Ha a fenti lépéseket 500.000 sorra engedjük rá, akkor lefagy az Excel, én legalábbis fél óra után lőttem le az Excelt a Feladatkezelőből.

IV. VBA megoldás #1

A dictionary objektum segítségével a futási idő ez esetben nálam 30-35 másodperc. Itt a „Adatok_Original” lapról a kód átmásolja az adatokat az „Adatok” lapra, majd ez utóbbin végzi el a színezést a „C” oszlopban. Eredmény:

Kód:

V. VBA megoldás #2

Érezzük, hogy az előző pontnál elért 30-35 másodpercen még lehetne javítani. Vannak üdítő kivételek, mikor a barkácsmegoldás („quick and dirty) – vagy legalábbis annak látszó – metódus bizonyul a leghatékonyabbnak.
Lényege:
– új oszlop („Index”) hozzáadása
– „Értékek” oszlop sorba rendezése (pl. növekvő sorrend)
– új oszlopban („Dupl”) képlet hozzáadása a duplikációk azonosítása végett: ha a cella tartalma egyenlő az alatta vagy a felette lévő cella tartalmával, akkor az eredmény „Igen”
– előző pontban lévő oszlopot leszűrni az „Igen”-ekre, majd beszínezni a leszűrt cellákat
– szűrő kikapcsolása, majd a segédoszlopok törlése
Az eredmény ugyanaz, mint az előző VBA-s megoldásnál, viszont a futási idő mindössze 6-8 másodperc lett!
Kód:

Összegezve tehát:
– az Excelnek is megvannak a maga korlátai, az elméleti maximuma nem mindig használható ki (itt a sorok/oszlopok tekintetében)
– nagyobb adatmennyiségnél a megfelelően megírt VBA kód olykor gyorsabb és hatékonyabb, mint az Excel beépített funkciói (Feltételes formázás, Irányított szűrő, Power Query)
– ha több millió/több tízmillió adatsorunk van, ráadásul nem csak egy oszlopban, akkor több opciót is meg kell vizsgálni. A teljesség igénye nélkül:

– van-e lehetőségünk átalakítani az adatbázisunkat (adatbázis szétszedése több kisebb fájlra, adatok átstrukturálása stb.)?
– az is fontos, hogy az adatokat rendszerezni, tisztítani kell-e vagy a már megfelelő adatbázison analízist végezni?
– milyen Excel verziót használunk, hány biteset (32 vagy 64)?
– az is számíthat, milyen géppel dolgozunk. Oktatások/fejlesztések során többször látom, hogy a tanítvány/ügyfél 6-10 éves laptopon dolgozik, ahol a megfelelő processzor, memória stb. már egy lassan tovatűnő foszlány.
– persze az is előfordulhat, hogy az Excel helyett más megoldás után kell néznünk, mint pl. az MS Access vagy az SQL vagy éppen egy direkt „big data”-ra specializálódott program (pl. SAS, Hadoop).