Duplikaty i near duplicates w Excelu 📊 szybkie metody na dane z CRM

Jak odróżnić duplikat pełny od duplikatu po kluczu i jak to sprawdzić w Excelu?

Duplikat pełny to wiersz identyczny z innym wierszem we wszystkich istotnych kolumnach, na przykład te same: imię, nazwisko, e-mail, telefon, firma i adres. Duplikat po kluczu oznacza natomiast, że rekordy są uznawane za ten sam obiekt na podstawie jednego pola albo zestawu pól, mimo że pozostałe dane mogą się różnić. W danych z CRM takim kluczem bywa najczęściej e-mail, numer telefonu, ID klienta albo połączenie pól, na przykład imię + nazwisko + firma.

Różnica jest praktyczna: przy duplikacie pełnym szukasz wierszy skopiowanych 1:1, a przy duplikacie po kluczu sprawdzasz, czy ten sam klient lub kontakt występuje wielokrotnie pod tym samym identyfikatorem, nawet jeśli część informacji została dopisana, zmieniona albo wpisana inaczej.

W Excelu duplikat pełny najprościej sprawdzić przez zaznaczenie całej tabeli i użycie funkcji Usuń duplikaty, ale tylko wtedy, gdy zaznaczone są wszystkie kolumny, które mają być porównywane. Jeśli dwa wiersze różnią się choćby jedną z tych kolumn, Excel nie uzna ich za pełny duplikat. Do samego wykrycia, bez usuwania, wygodniejsze jest też formatowanie warunkowe lub kolumna pomocnicza, w której łączysz wszystkie pola w jeden tekst, na przykład formułą =A2&"|"&B2&"|"&C2&"|"&D2, a potem sprawdzasz powtórzenia tej złączonej wartości.

Duplikat po kluczu sprawdza się podobnie, ale porównujesz tylko wybrane pole albo pola. Jeśli kluczem jest e-mail, wystarczy zbadać powtórzenia w jednej kolumnie. Jeśli klucz składa się z kilku pól, tworzysz kolumnę pomocniczą z ich połączeniem, na przykład =C2&"|"&E2, i dopiero na tej podstawie wykrywasz powtórzenia. Wtedy dwa rekordy mogą mieć różne telefony czy notatki, ale nadal będą duplikatem po kluczu, jeśli mają ten sam identyfikator logiczny.

Najczytelniejsza metoda kontrolna to użycie funkcji LICZ.JEŻELI albo COUNTIF. Dla jednego klucza, na przykład e-maila w kolumnie C, możesz użyć formuły =LICZ.JEŻELI(C:C;C2)>1. Wynik PRAWDA oznacza, że wartość występuje więcej niż raz. Dla klucza złożonego najpierw tworzysz kolumnę pomocniczą, a potem liczysz wystąpienia tej złączonej wartości. Jeśli chcesz odróżnić oba typy duplikatów, utwórz osobno jedną kolumnę pomocniczą dla całego wiersza i drugą dla klucza. Powtórzenie pierwszej oznacza duplikat pełny, a powtórzenie tylko drugiej oznacza duplikat po kluczu.

W praktyce najważniejsze jest wcześniejsze ustalenie, co w Twoich danych jest kluczem. Bez tego Excel pokaże jedynie techniczne powtórzenia, ale nie rozstrzygnie, czy chodzi o kopię całego rekordu, czy o kilka wersji tego samego kontaktu.

Jak oczyścić dane z CRM, żeby duplikaty nie ukrywały się przez spacje i znaki specjalne?

Najpierw trzeba sprowadzić porównywane wartości do jednej, przewidywalnej postaci. W praktyce oznacza to usunięcie nadmiarowych spacji, niewidocznych znaków oraz znaków specjalnych, które powodują, że dwa pozornie identyczne rekordy są dla Excela różne, na przykład „Jan Kowalski”, „ Jan Kowalski ” i „Jan-Kowalski”. Bez takiego ujednolicenia wyszukiwanie duplikatów daje wyniki niepełne.

W Excelu najbezpieczniej zrobić to w kolumnie pomocniczej. Dla tekstu warto połączyć funkcje czyszczące: USUŃ.ZBĘDNE.ODSTĘPY() usuwa spacje na początku i końcu oraz redukuje wielokrotne spacje wewnątrz tekstu, a OCZYŚĆ() usuwa część znaków niedrukowalnych. Jeśli w danych z CRM występują spacje nierozdzielające, typowe po kopiowaniu z WWW lub systemów zewnętrznych, dobrze jest najpierw zamienić je funkcją PODSTAW(), na przykład USUŃ.ZBĘDNE.ODSTĘPY(PODSTAW(A2;ZNAK(160);" ")). To częsty powód, dla którego duplikaty „ukrywają się”, mimo że wizualnie wyglądają tak samo.

Jeżeli problemem są znaki specjalne, trzeba zdecydować, które z nich nie powinny mieć znaczenia przy porównaniu. Najczęściej usuwa się myślniki, kropki, przecinki, nawiasy, ukośniki i spacje wewnętrzne w polach takich jak telefon, NIP, kod klienta czy adres e-mail. Robi się to przez zagnieżdżone PODSTAW(), na przykład zamieniając kolejno „-”, „(”, „)”, „/” i spację na pusty ciąg. Dzięki temu wartości zapisane w różnych formatach trafiają do jednego wzorca porównawczego.

Dobrą praktyką jest też ujednolicenie wielkości liter, aby „abc@firma.pl” i „ABC@firma.pl” nie były traktowane jako różne tam, gdzie nie ma to znaczenia. Do tego służy MAŁE.LITERY() albo DUŻE.LITERY(). W efekcie tworzysz techniczną wersję danych, na której dopiero sprawdzasz duplikaty. Oryginalnych wartości nie należy nadpisywać, bo kolumna pomocnicza ma służyć do porównania, a nie do utraty źródłowego zapisu.

Najważniejsze jest więc nie samo usunięcie duplikatów, ale wcześniejsze zbudowanie klucza porównawczego: oczyszczonej, pozbawionej zbędnych znaków wersji danych. Dopiero rekordy o identycznym kluczu można uznać za rzeczywiście powtarzające się mimo różnic w spacjach i znakach specjalnych.

💡 Zanim szukasz duplikatów, zbuduj kolumnę z technicznym kluczem porównawczym: wyczyść spacje, zamień twarde spacje (ZNAK(160)) i usuń znaki specjalne, które nie powinny wpływać na zgodność. Duplikaty najczęściej „znikają” nie przez Excel, tylko przez niewidoczne różnice w zapisie.

Jak wykryć duplikaty po kilku kolumnach bez makr i bez Power Query?

Najprostsza metoda w samym Excelu polega na zbudowaniu kolumny pomocniczej, która łączy wartości z kilku kolumn w jeden klucz porównawczy. Jeśli duplikat ma być rozpoznawany np. po imieniu, nazwisku i e-mailu, tworzysz nową kolumnę z formułą w rodzaju . Taki zapis zamienia kilka pól w jeden identyfikator, który potem można sprawdzić standardowymi funkcjami Excela.

Aby wykryć powtórzenia, obok klucza użyj formuły zliczającej wystąpienia, np. LICZ.JEŻELI($D:$D;D2) w polskiej wersji. Jeśli wynik jest większy niż 1, dany zestaw wartości występuje więcej niż raz, czyli jest duplikatem po kilku kolumnach jednocześnie. Możesz też użyć warunkowego formatowania opartego na tej kolumnie pomocniczej albo po prostu przefiltrować wiersze z wynikiem większym od 1.

Kluczowe jest, żeby przed łączeniem danych zadbać o spójność porównania. W praktyce warto usunąć zbędne spacje i ujednolicić wielkość liter, np. przez USUŃ.ZBĘDNE.ODSTĘPY(A2) i GÓRNE(A2) lub ich odpowiedniki. Dzięki temu rekordy różniące się tylko spacją na końcu albo zapisem małymi i wielkimi literami nie zostaną błędnie potraktowane jako różne.

Warto też stosować separator między polami, np. pionową kreskę |, bo samo sklejenie wartości bez rozdzielacza może dać fałszywe trafienia. Przykładowo zestawy „AB” + „C” oraz „A” + „BC” po prostym połączeniu wyglądają tak samo, ale po użyciu separatora już nie. To podejście nie wymaga makr ani Power Query i działa w zwykłym arkuszu, o ile jasno określisz, które kolumny razem mają definiować duplikat.

Jak znaleźć near-duplicates, gdy różni się literówka, skrót lub kolejność słów?

W Excelu bez Power Query nie da się wiarygodnie wykryć takich przypadków jedną prostą regułą, bo klasyczne porównanie tekstu działa znak po znaku. Żeby znaleźć near-duplicates, trzeba najpierw sprowadzić dane do bardziej porównywalnej postaci, czyli wykonać normalizację tekstu. W praktyce oznacza to usunięcie różnic technicznych, które nie zmieniają sensu rekordu: zamianę tekstu na jeden format wielkości liter, usunięcie zbędnych spacji, kropek, przecinków i innych separatorów oraz ujednolicenie typowych skrótów, jeśli występują regularnie w danych z CRM. Dzięki temu wpisy typu ABC Sp. z o.o. i abc sp z oo przestają wyglądać jak całkowicie różne wartości.

Przy literówkach najskuteczniejsze w samym Excelu jest podejście pośrednie: nie szuka się idealnej zgodności, tylko kandydatów do ręcznej weryfikacji. Można porównywać uproszczone wersje tekstu, na przykład po usunięciu znaków specjalnych i skróceniu do początku nazwy, albo zestawiać rekordy o bardzo podobnej długości i tym samym początku lub końcu. To nie daje matematycznie pełnego „fuzzy match”, ale dobrze wyłapuje przypadki takie jak pojedyncza zamiana litery, brak polskiego znaku czy pominięty znak w nazwie.

Przy skrótach kluczowe jest stworzenie jednej konsekwentnej postaci zapisu. Jeśli w danych występują równolegle formy typu ul i ulica, sp z oo i spółka z ograniczoną odpowiedzialnością, to bez wcześniejszego mapowania tych wariantów Excel będzie traktował je jako różne teksty. Najpraktyczniej zrobić osobną kolumnę pomocniczą, w której zamieniasz najczęstsze warianty na jeden standard. Dopiero tak przygotowane wartości mają sens w dalszym porównywaniu.

Najtrudniejsza jest zmieniona kolejność słów, bo Jan Kowalski i Kowalski Jan są dla Excela innymi ciągami znaków. W takim przypadku pomaga rozbicie tekstu na elementy i zbudowanie wersji uporządkowanej według stałej reguły, na przykład osobno imię i nazwisko albo posortowany zestaw słów w kolumnie pomocniczej. Jeśli nie da się logicznie rozdzielić danych na części, warto przynajmniej usuwać słowa techniczne i porównywać najistotniejsze człony nazwy. Sens tej metody polega na tym, że porównujesz nie surowy wpis z CRM, tylko jego oczyszczoną, bardziej stabilną reprezentację.

W praktyce trzeba przyjąć, że bez specjalistycznych funkcji podobieństwa tekstowego Excel pozwala raczej zawęzić listę podejrzanych rekordów niż automatycznie potwierdzić near-duplicate. Dlatego poprawna procedura to: utworzyć kolumnę z wersją znormalizowaną, wyszukać rekordy o bardzo zbliżonej postaci i dopiero wtedy ocenić je ręcznie. To podejście jest wystarczające dla większości danych z CRM, o ile różnice są niewielkie i powtarzalne.

💡 W Excelu near-duplicates wykrywa się najlepiej nie jako gotowe duplikaty, ale jako kandydatów do sprawdzenia po normalizacji tekstu. Ujednolicenie skrótów, separatorów i kolejności kluczowych członów zwykle daje więcej niż próba porównywania surowych wpisów znak po znaku.

Jak poradzić sobie z duplikatami e-maili i telefonów zapisanych w różnych formatach?

Najpierw trzeba dane ujednolicić do wspólnego formatu, bo Excel nie rozpozna jako duplikatu wartości, które wyglądają inaczej, mimo że oznaczają ten sam kontakt. Typowe przykłady to adresy e-mail różniące się wielkością liter lub spacjami oraz numery telefonów zapisane z myślnikami, spacjami, nawiasami albo prefiksem kraju.

W przypadku e-maili zwykle wystarczy utworzyć kolumnę pomocniczą, w której adres zostanie oczyszczony przez usunięcie zbędnych spacji i zamianę liter na małe. Praktycznie oznacza to użycie formuły w rodzaju =LOWER(TRIM(A2)). Dzięki temu Jan.Kowalski@Firma.pl i jan.kowalski@firma.pl staną się identyczne i będzie można je poprawnie porównać.

Przy telefonach trzeba usunąć znaki nienumeryczne i zdecydować, jaki format ma być docelowy. Najczęściej sprowadza się numer do samego ciągu cyfr, np. przez wielokrotne podstawienie znaków funkcją SUBSTITUTE, np. usunięcie spacji, myślników, nawiasów i znaku +. Jeśli w danych raz występuje numer z prefiksem kraju, a raz bez niego, trzeba przyjąć jedną regułę, na przykład zachować zawsze pełny numer albo zawsze porównywać tylko ostatnie 9 cyfr. Bez takiej reguły część duplikatów pozostanie niewidoczna.

Po normalizacji porównuje się już nie oryginalne dane, tylko wartości z kolumn pomocniczych. Wtedy można użyć COUNTIF, formatowania warunkowego albo funkcji usuwania duplikatów, ale na oczyszczonej wersji e-maila lub telefonu. To kluczowe, bo sam mechanizm wykrywania duplikatów działa poprawnie dopiero wtedy, gdy zapis został sprowadzony do jednej, spójnej postaci.

Jeżeli w CRM występują jednocześnie braki, błędy lub różne długości numerów, warto najpierw odfiltrować rekordy niespełniające przyjętego wzorca. Inaczej można błędnie połączyć różne osoby albo pominąć faktyczne duplikaty. W praktyce najważniejsze są więc dwa kroki: normalizacja zapisu i dopiero potem wykrywanie powtórzeń.

Jak użyć UNIQUE, FILTER i XLOOKUP do szybkiej listy rekordów do weryfikacji?

Najprostszy schemat to zbudowanie pomocniczego klucza porównawczego, wyciągnąć tylko te klucze, które występują więcej niż raz, a potem pokazać pełne rekordy odpowiadające tym kluczom. Dzięki temu dostajesz krótką listę przypadków do ręcznej kontroli zamiast przeglądać cały CRM.

Załóżmy, że dane są w tabeli z kolumnami ID, Imię, Nazwisko, Email, a w kolumnie pomocniczej Klucz tworzysz ujednoliconą wartość, np. z e-maila albo z połączenia pól. Następnie lista kluczy do weryfikacji może wyglądać tak: =UNIQUE(FILTER(Tabela1[Klucz];COUNTIF(Tabela1[Klucz];Tabela1[Klucz])>1)). COUNTIF wskazuje klucze występujące wielokrotnie, FILTER odrzuca unikalne, a UNIQUE usuwa powtórzenia z samej listy kluczy. Wynik to jeden wpis na każdy podejrzany duplikat.

Jeżeli chcesz od razu zobaczyć pierwszy odpowiadający rekord dla każdego klucza, użyj XLOOKUP, np. =XLOOKUP(H2#;Tabela1[Klucz];Tabela1[ID]) albo analogicznie dla nazwiska czy e-maila, gdzie H2# to rozlana lista kluczy z poprzedniej formuły. To daje skrócony podgląd: jeden reprezentatywny rekord na każdy podejrzany klucz.

Jeśli celem jest pokazanie wszystkich rekordów wymagających sprawdzenia, a nie tylko pierwszego trafienia, najwygodniejsze jest użycie FILTER na całej tabeli: =FILTER(Tabela1;COUNTIF(H2#;Tabela1[Klucz])>0). Wtedy Excel zwróci wszystkie wiersze, których klucz znajduje się na liście podejrzanych przypadków. To jest praktycznie najlepszy wariant do weryfikacji operacyjnej, bo od razu widzisz komplet rekordów należących do grup zduplikowanych.

XLOOKUP jest przydatny głównie wtedy, gdy chcesz szybko dociągnąć wybrane pola do listy kluczy, np. pierwszy ID, właściciela rekordu albo datę utworzenia. Trzeba jednak pamiętać, że standardowo zwraca tylko jedno dopasowanie, więc samodzielnie nie zastąpi listy wszystkich duplikatów. Do kompletnej listy rekordów do weryfikacji kluczowe są UNIQUE i FILTER, a XLOOKUP pełni rolę pomocniczą.

W praktyce ważne jest też, aby klucz był wcześniej ujednolicony, np. bez zbędnych spacji i różnic wielkości liter. W przeciwnym razie lista do weryfikacji będzie niepełna albo zaszumiona. Dopiero na takim kluczu połączenie UNIQUE + FILTER + XLOOKUP daje szybki i użyteczny widok rekordów wymagających sprawdzenia.

💡 Najpraktyczniejszy układ to: pomocniczy klucz → UNIQUE z listą podejrzanych kluczy → FILTER z pełnymi rekordami do kontroli. XLOOKUP traktuj jako dodatek do podglądu jednego reprezentatywnego pola, a nie narzędzie do wyciągania wszystkich duplikatów.

Jak ustawić formatowanie warunkowe, żeby zespół widział problem od razu?

Najczytelniejsze rozwiązanie to oznaczać całe wiersze lub kluczowe kolumny jednym, mocnym kolorem wtedy, gdy rekord jest duplikatem albo wygląda na near-duplicate według ustalonej reguły. W praktyce nie warto podświetlać wszystkiego naraz, bo arkusz staje się nieczytelny. Lepiej wyróżnić tylko te pola, na których zespół podejmuje decyzję, na przykład e-mail, telefon, nazwę firmy albo kolumnę pomocniczą z wynikiem kontroli.

Dla dokładnych duplikatów najprościej zaznaczyć zakres danych i dodać regułę formatowania warunkowego opartą na formule, np. dla kolumny z e-mailami: =LICZ.JEŻELI($B:$B;B2)>1. Taka reguła oznaczy każdą wartość, która występuje więcej niż raz. Jeśli chcesz, żeby zespół widział problem na poziomie całego rekordu, zastosuj tę samą logikę do całego zakresu tabeli, ale formułę oprzyj na jednej kolumnie identyfikującej konflikt, np. =LICZ.JEŻELI($B:$B;$B2)>1, a regułę przypisz do wszystkich kolumn w danym wierszu.

W przypadku near-duplicates najlepiej najpierw przygotować kolumnę pomocniczą z ujednoliconą wartością, bo samo formatowanie warunkowe nie służy do skomplikowanego porównywania tekstów. Przykładowo można porównywać wersję nazwy bez spacji, kropek i wielkości liter. Jeśli w kolumnie pomocniczej masz już taki klucz, reguła działa tak samo jak dla zwykłych duplikatów: formatowanie reaguje na powtórzenia klucza, a nie surowego tekstu.

Żeby problem był widoczny od razu dla zespołu, ustaw jeden poziom ostrzegawczy i jeden kolor o wysokim kontraście, np. jasnoczerwone tło z ciemnym tekstem. Nie mieszaj wielu podobnych kolorów dla różnych typów błędów, jeśli użytkownicy mają tylko szybko wychwycić rekord do sprawdzenia. Dobrą praktyką jest też dodanie osobnej kolumny statusowej z prostą etykietą typu SPRAWDŹ i objęcie jej filtrowaniem, bo sam kolor pomaga wizualnie, ale filtr pozwala od razu wyciągnąć wszystkie problematyczne rekordy.

Najważniejsze jest to, aby reguła była oparta na stabilnym zakresie danych i jednej, zrozumiałej definicji konfliktu. Jeśli zespół nie wie, czy czerwony wiersz oznacza identyczny duplikat, podobną nazwę czy brakujący identyfikator, formatowanie przestaje być użyteczne. Dlatego kolor powinien sygnalizować tylko jeden konkretny problem, który da się od razu zweryfikować.

Majczęściej zadawane pytania i odpowiedzi odnośnie Duplikaty i near-duplicates w Excelu bez Power Query: szybkie metody na dane z CRM

Od czego zacząć szukanie duplikatów w danych z CRM w Excelu?

Najlepiej zacząć od ustalenia, co w Twoich danych ma być kluczem porównawczym. Dopiero wtedy wiadomo, czy szukasz pełnych kopii wierszy, czy kilku wersji tego samego kontaktu. W praktyce pierwszy krok to wybór pola lub zestawu pól, potem oczyszczenie zapisu w kolumnie pomocniczej i dopiero na końcu użycie formuł, filtrów albo formatowania warunkowego.

Kiedy lepiej szukać duplikatów po e-mailu lub telefonie niż po całym wierszu?

Duplikaty po e-mailu lub telefonie warto sprawdzać wtedy, gdy rekordy opisują ten sam kontakt, ale różnią się innymi polami. W CRM to częsta sytuacja, bo jeden klient może mieć kilka wersji danych. Porównanie całego wiersza wykryje tylko identyczne kopie, a porównanie po kluczu pokaże powtórzenia logiczne, nawet gdy zmienił się telefon, notatka albo nazwa firmy.

Jakie pola w CRM najczęściej nadają się na klucz do wykrywania duplikatów?

Najczęściej jako klucz sprawdzają się pola, które identyfikują kontakt możliwie jednoznacznie. W praktyce są to pojedyncze pola albo ich połączenia, zależnie od jakości danych.

adres e-mail,
numer telefonu,
ID klienta,
zestaw pól, np. imię + nazwisko + firma.

Najważniejsze, aby ta definicja była spójna przed rozpoczęciem analizy.

Dlaczego Excel nie pokazuje części duplikatów, mimo że rekordy wyglądają tak samo?

Najczęściej problemem są niewidoczne różnice w zapisie, a nie sam mechanizm Excela. Rekordy mogą różnić się twardą spacją, spacją na końcu, wielkością liter albo znakiem specjalnym. Dla użytkownika wyglądają identycznie, ale Excel widzi inny ciąg znaków. Dlatego przed porównaniem trzeba utworzyć oczyszczoną wersję danych w osobnej kolumnie pomocniczej.

Czy funkcja Usuń duplikaty w Excelu wystarczy do pracy na danych z CRM?

Funkcja Usuń duplikaty wystarcza tylko do prostych przypadków i nie zastępuje wcześniejszego przygotowania danych. Działa dobrze przy pełnych duplikatach albo jasno zdefiniowanym kluczu, ale nie rozwiązuje problemu spacji, różnych formatów zapisu ani near-duplicates. W danych CRM bezpieczniej najpierw wykryć powtórzenia, ocenić je ręcznie i usuwać dopiero po weryfikacji.

Jak odróżnić rekord do usunięcia od rekordu tylko do ręcznego sprawdzenia?

Rekord do usunięcia to zwykle pełny duplikat, a rekord do sprawdzenia to najczęściej duplikat po kluczu lub near-duplicate. Jeśli powtarza się cały wiersz, decyzja jest zazwyczaj prosta. Gdy zgadza się tylko e-mail, telefon albo znormalizowana nazwa, trzeba porównać pozostałe pola i ustalić, czy to kopia, aktualizacja danych czy inna osoba o podobnym zapisie.

Jak przygotować listę podejrzanych rekordów do weryfikacji zamiast przeglądać cały arkusz?

Najwygodniej zbudować pomocniczy klucz i odfiltrować tylko te wartości, które występują więcej niż raz. Taki sposób ogranicza liczbę rekordów do ręcznej kontroli i porządkuje pracę zespołu.

utwórz kolumnę z kluczem porównawczym,
wybierz tylko klucze z powtórzeniami,
pokaż pełne rekordy przypisane do tych kluczy.

W tym układzie najlepiej działają funkcje UNIQUE, FILTER i pomocniczo XLOOKUP.

Czy bez Power Query da się sensownie wykrywać near-duplicates w Excelu?

Tak, ale w praktyce wykryjesz głównie kandydatów do sprawdzenia, a nie gotowe, pewne dopasowania. Excel bez dodatkowych narzędzi nie porównuje podobieństwa tekstu w zaawansowany sposób. Najlepszy efekt daje normalizacja: ujednolicenie liter, spacji, separatorów i skrótów, a potem porównywanie uproszczonych wersji nazw, e-maili lub innych pól z CRM.