Pracując w Excelu, zwłaszcza na dużych zbiorach danych, bardzo ważne jest dbanie o ich odpowiednią jakość. Nawet najlepiej przygotowane raporty lub prezentacje nie będą miały dużej wartości, jeśli zostały przygotowane na podstawie nieprawidłowych danych wejściowych. Eksportując dane z innego programu, łącząc ze sobą dane z wielu źródeł lub bazując na danych wprowadzanych ręcznie, istnieje duża szansa na to, że prędzej czy później przyjdzie Ci zmierzyć się problemem powtarzających się wartości — duplikatami. Czym są duplikaty i dlaczego podczas procesu analizy danych powinniśmy się nimi przejmować? Oraz przede wszystkim, jak Excel może nam pomóc, gdy w pliku pojawią się duplikaty? Odpowiedź na te pytania wraz z praktycznymi przykładami czekają na Ciebie w dalszej części artykułu.
Tradycyjnie tutaj możesz ściągnąć plik, na którym pracowałem podczas przygotowywania tego artykułu.
Czym są duplikaty w MS Excel i czy powinniśmy się nimi przejmować?
Duplikat to drugi (lub kolejny), identyczny egzemplarz już występującego obiektu. Duplikatem będzie przedmiot, który został dokładnie odtworzony na wzór innego (np. dorobiony klucz do mieszkania lub drugi egzemplarz dokumentu). W przypadku analizowania danych w Excelu będą to po prostu powtarzające się, identyczne wartości.
Wykonując obliczenia na zduplikowanych wartościach możemy otrzymać niepoprawne wyniki. Na ich podstawie rzeczywistość, którą obrazują te dane, może zostać w błędny sposób opisana lub zilustrowana. W końcowym etapie, bazując na niepoprawnych danych, mogą zostać podjęte błędne decyzje. Poniższy przykład pozwoli zobrazować, jak duplikaty mogą wpłynąć na wykonywane obliczenia.
W poniższej tabeli znajduje się podsumowanie sprzedaży w pewnej firmie:
Bazując na danych z kolumny C w łatwy sposób możemy policzyć łączną wartość sprzedaży korzystając z formuły:
=SUMA(C3:C22)
Czy otrzymane wyniki są poprawne? Cóż… zakładając, że każde zamówienie ma swój niepowtarzalny numer zamówienia, to posiadanie w tabeli więcej niż jednej pozycji z takim samym numerem zamówienia powinniśmy uznać za błąd i coś niepożądanego. Po dokładniejszym zweryfikowaniu danych możesz zauważyć, że w tabeli powtarza się numer zamówienia 200843. Zatem wyliczona przez nas suma nie jest poprawna, gdyż zawiera zduplikowaną wartość jednego zamówienia.
Ten prosty przykład na ograniczonym zestawie danych powinien pozwolić Ci zrozumieć jaki wpływ na pracę w Excelu mogą mieć duplikaty. Teraz wyobraź sobie, że tych danych jest o wiele więcej i ręcznie nie jesteś w stanie ich wszystkich zweryfikować. Kiepska perspektywa, prawda? Na szczęście jest parę sposobów, które pozwalają zapobiegać, kontrolować i odpowiednio reagować na duplikaty pojawiające się w Twoich danych.
Oczywiście warto pamiętać, że nie zawsze duplikaty są czymś niepożądanym lub błędnym. Może się zdarzyć, że występują one w sposób celowy i jak najbardziej poprawny.
Co, gdy pojawią się duplikaty w Twoim pliku Excel?
Postanowiłem w tym artykule przedstawić dwa podejścia, które pozwalają wyeliminować problemy związane z powtarzającymi się wartościami. W pierwszym z nich pokażę Ci sposoby, dzięki którym zapobiegniesz wprowadzeniu duplikatów do Twojego pliku. W drugim podejściu zajmiemy się sposobami pozwalającymi na usunięcie powtarzających się wartości w Twoich danych.
Zapobieganie
Opcja POPRAWNOŚĆ DANYCH
Na głównej wstążce Excela w zakładce Dane, w obszarze Narzędzia danych znajduje się opcja o nazwie POPRAWNOŚĆ DANYCH. Przy pomocy której stworzymy prosty mechanizm uniemożliwiający użytkownikowi wprowadzenie do tabeli zduplikowanej wartości. Zaznacz zakres komórek, w którym chcesz wprowadzić sprawdzanie danych (w moim wypadku są to komórki od B3 do B2). W nowym oknie, które pojawi się na ekranie, wybierz następujące opcje oraz w miejscu na formułę wpisz:
=LICZ.JEŻELI($B$3:$B$22;B3)=1, gdzie:
$B$3:$B$22 – to zakres komórek zawierający wartości, w których chcemy weryfikować występowanie duplikatów. Jeśli nie wiesz, co oznacza symbol dolara w tej formule, zachęcam do zapoznania się z tym artykułem.
B3 – to pierwsza komórka z powyższego zakresu. Zwróć uwagę, że w tym wypadku adres komórki B3 nie zawiera symbolu dolara. Jest to o tyle istotne, że dzięki temu odwołanie do komórki zostanie zaktualizowane na następne wiersze.
Korzystając z pozostałych zakładek okna ze sprawdzaniem danych, możesz ustalić, czy po kliknięciu komórki ma pojawić się jakiś komunikat wejściowy lub jaki komunikat ma się pojawić przy próbie wprowadzenia zduplikowanej wartości. Zdecydowałem się wprowadzić komunikat, który przy próbie wprowadzenia duplikatu poinformuje użytkownika, że numer zamówienia już występuje:
I gotowe! Teraz wprowadzenie już występującego numeru zamówienia nie będzie możliwe, a dodatkowo użytkownik otrzyma odpowiedni komunikat.
Opcja FORMATOWANIE WARUNKOWE
Druga opcja nie będzie blokowała możliwości wprowadzenia niepożądanej wartości, a jedynie wyróżni nam kolorem komórki, w których znajdują się duplikaty. Skorzystamy z opcji FORMATOWANIE WARUNKOWE, którą możesz znaleźć na karcie Narzędzia główne w obszarze Style. Analogicznie jak w poprzednim przypadku, zaznacz interesujący Cię zakres danych, wybierz opcję Formatowanie warunkowe -> Reguły wyróżniania komórek -> Duplikujące się wartości. W nowym oknie możesz zmienić lub zostawić zaproponowany przez Excela sposób formatowania zduplikowanych komórek, zatwierdzasz klikając OK i gotowe. Jeśli w Twoich danych znajdują się duplikaty, to Excel zaznaczy je w wybrany przez Ciebie sposób. W moim przypadku sformatuje komórki na czerwono:
Jak znaleźć duplikat na podstawie wartości z dwóch lub więcej kolumn?
Możemy rozbudować działanie tej opcji, aby Excel za duplikaty uznał połączenie wartości z dwóch lub więcej kolumn. W poniższej tabeli znajdują się informacje na temat zleceń realizowanych przez pewną firmę. Firma prowadzi ewidencję zleceń w taki sposób, że każde zarejestrowane zlecenie otrzymuje swój indywidualny i niepowtarzający się numer. Ten sam klient może oczywiście zlecić firmie parę zleceń, więc ten sam klient występujący w spisie zleceń nie będzie błędem. Sytuacja, w której na liście występuje zlecenie, w którym imię i nazwisko klienta, a także data zlecenia są identyczne, musi być uznana za błąd.
Wykorzystam do tego funkcję LICZ.WARUNKI, której sposób działania jest bardzo podobny do funkcji LICZ.JEŻELI, o której pisałem więcej tutaj. Różnica pomiędzy tymi funkcjami jest taka, że LICZ.JEŻELI zwraca liczbę komórek w zakresie spełniających jedno kryterium (np. komórki z wartością większą niż 10). LICZ.WARUNKI działa bardzo analogicznie. Pozwala obliczyć liczbę komórek spełniających więcej niż jedno kryterium (np. komórki z wartością większą niż 10 oraz zawierające „A” w tekście komórki).
W moim przykładzie funkcja będzie miała następującą postać:
=LICZ.WARUNKI($I$2:$I2;$I3;$J$2:$J2;$J3), gdzie:
$I$2:$I2 – to pierwszy zakres komórek, w którym mogą pojawić się duplikaty (imię i nazwisko klienta).$I3 – to następna komórka występująca po powyższym zakresie. Dzięki temu stworzymy mechanizm sprawdzający, czy wartość wpisana w obecną komórkę nie wystąpiła już we wcześniejszych komórkach tego zakresu.
Kolumna I dotyczy pierwszej zmiennej (imię i nazwisko klienta). Musimy jeszcze przygotować analogiczny zakres dla drugiej zmiennej (data zlecenia). Wartości dla tej zmiennej znajdują się w kolumnie J, więc zakresy będą wyglądały następująco:
$J$2:$J2 – tu mogą pojawić się duplikaty (data zlecenia).$J3 – następna komórka po powyższym zakresie.
Uważaj na adresowanie komórek
Zwróć uwagę na adresowanie komórek (symbol dolara), to dzięki niemu cały mechanizm wykrywania duplikatów będzie działał poprawnie. Pierwszy z zakresów $I$2:$I2 to obszar, w którym będziemy poszukiwali duplikatów. Chcemy zawsze szukać ich w całym zakresie, od jego początku do końca. Stąd pierwsza część zakresu $I$2 jest w całości zablokowana, nie chcemy, żeby początek tego zakresu mógł się przesuwać. Szukanie duplikatów Excel zawsze ma rozpocząć od komórki I2. Druga część zakresu $I2 jest zablokowana tylko częściowo. Taki sposób adresowania umożliwia zmianę (rozszerzanie) przeszukiwanego zakresu. Dzięki temu formuła będzie dynamiczna, duplikaty będą wyszukiwane także po dodaniu nowych wierszy.
Oczywiście możemy rozszerzyć sprawdzanie pod kątem występowania duplikatów na więcej kolumn. Wystarczy do powyższej formuły dodać w analogiczny sposób kolejny zakres, który będzie sprawdzany. Poniższa formuła sprawdza naszą tabelę pod kątem duplikatów. Rekord będzie duplikatem, jeśli w tabeli występuje już pozycja z takim samym imieniem i nazwiskiem, datą zlecenia oraz pracownikiem odpowiedzialnym za to zlecenie.
=LICZ.WARUNKI($P$2:$P2;$P3;$Q$2:$Q2;$Q3;$R$2:$R2;$R3)
Naprawa
Nie zawsze udaje się zapobiec pojawieniu się duplikatów w Excelu. W tej części artykułu przedstawię Ci moje ulubione sposoby eliminowania duplikatów z danych.
Opcja USUŃ DUPLIKATY
Jedna z najczęściej używanych opcji pozwalająca usunąć z danego zakresu komórek powtarzające się wartości. Wystarczy zaznaczyć zakres komórek, z którego Excel ma usunąć duplikaty, wybrać ze wstążki głównej z obszaru Dane opcję USUŃ DUPLIKATY i zatwierdzić klikając OK. Sprawdzany obszar może dotyczyć zarówno wartości z jednej kolumny (np. powtarzające się imię i nazwisko), jak i z więcej niż jednej kolumny (np. duplikatem na liście pracowników nie będzie takie samo imię i nazwisko, ale takie samo imię i nazwisko i powtarzający się PESEL).
W przykładzie sprawdzającym duplikaty na podstawie jednej kolumny sprawa jest prosta — jeśli Excel napotka w danym zakresie wartość, która wystąpiła już wcześniej, usunie ją. Na początkowej liście Daniel Andrzejewski występował 3 razy, a Oliwia Laskowska 2 razy. Po użyciu opcji USUŃ DUPLIKATY obie osoby występują już tylko raz.
Szukając powtarzających się wartości w więcej niż jednej kolumnie, Excel również doskonale sobie poradzi. Zauważ, że pracownik Daniel Andrzejewski początkowo występuje na liście 3 razy, a po użyciu funkcji na liście pozostały 2 rekordy. Z jakiego powodu? Ponieważ są to 2 różne osoby. Pomimo posiadania takiego samego imienia i nazwiska, mają różny PESEL, więc nie kwalifikowały się do usunięcia. Excel zidentyfikował jako duplikaty pozycję z wiersza 11, gdyż takie połączenia imienia, nazwiska i nr PESEL wystąpiło już w wierszu 6.
Opcja FILTR ZAAWANSOWANY
Kolejną z popularniejszych opcji jest korzystanie z tzw. filtru zaawansowanego. Umożliwia on wyfiltrowanie z zaznaczonego zakresu tylko unikatowych wartości i skopiowanie ich w inne miejsce. Dzięki temu w kilku prostych krokach jesteśmy w stanie sprawdzić nasze dane pod kątem występowania duplikatów oraz odpowiednio na nie zareagować (zostawić je lub usunąć).
Na początku wystarczy zaznaczyć obszar, który będzie sprawdzany. Następnie ze wstążki głównej, z karty Dane wybrać opcję Zaawansowane. W nowym okienku, które pojawi się na ekranie, należy wybrać opcję Kopiuj w inne miejsce, w polu Kopiuj do wskazać, gdzie Excel ma wkleić przefiltrowaną listę. Zwróć uwagę, czy checkbox Tylko unikatowe rekordy jest zaznaczony. Jeśli nie będzie zaznaczony, to Excel po prostu skopiuje zaznaczoną listę do wskazanego zakresu.
Oczywiście w przypadku tej opcji także można jej używać na więcej niż jednej kolumnie. Zadziała podobnie, jak opisywana wcześniej funkcja USUŃ DUPLIKATY.
MAKRO
W przypadku, gdy regularnie sprawdzanie danych pod kątem duplikatów odbywa się regularnie, dobrym pomysłem jest zautomatyzowanie tego procesu. Aby nie musieć co miesiąc korzystać ze wcześniej opisanych opcji, usuwanie powtarzających się wartości może zostać przypisane za pomocą języka VBA (Visual Basic for Applications) do przycisku stworzonego w Excelu. Cały proces sprawdzenia i usunięcia duplikatów będzie więc sprowadzał się tylko do kliknięcia tego przycisku. Zanim przygotujemy to rozwiązanie, opiszę krótko, czym są makra i język VBA.
Czym są makra?
Makra umożliwiają rozszerzenie domyślnych możliwości Excela poprzez tworzenie w nim własnych rozwiązań lub pozwalających wymusić określoną reakcję programu. Korzystanie z makr pozwala użytkownikom na pisanie własnych poleceń, które mogą wykorzystywać w codziennej pracy. Dzięki VBA z łatwością można oprogramować powtarzające się czynności w Excelu i oszczędzić sobie trochę pracy.
Język VBA pozwala przetłumaczyć czynności wykonywane przez użytkowników (np. formatowanie, kopiowanie itp.) na język rozumiany przez Excela. Za każdym razem, gdy wykonujesz w Excelu jakieś działanie, przykładowo tworząc i formatując tabelę, każda Twoja czynność musi zostać odpowiednio przetłumaczona tak, aby program wiedział, jak zareagować. Język, który pozwala przetłumaczyć te komendy to właśnie VBA.
Jak przygotować makro?
Kod, który usunie duplikaty wygląda następująco:
Sub Przycisk1_Click()
Range("B2:B22").RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub, gdzie:
Range("B2:B22") – to zakres komórek, z którym Excel ma sprawdzić występowanie duplikatów i w przypadku ich stwierdzenia je usunąć.RemoveDuplicates – to po prostu nazwa funkcji dostępna w języku VBA służąca do usuwania duplikatów.Columns:=Array(1) – to numer kolumny, która ma być sprawdzona pod kątem występowania powtarzających się wartości.Header:=xlYes – służy do oznaczenia, czy dane w podanym zakresie mają nagłówek.
Kod składa się ze słów kluczowych Sub oraz End Sub. One wyznaczają nam początek i koniec procedury, w której środku zawieramy czynność lub czynności, które ma wykonać Excel. Procedury mogą być uruchamiane na wiele sposobów, w naszym przykładzie jest wykonywana po kliknięciu przycisku. Zdarzenia wywołujące działanie kodu mogą być inne, np. zmiana wartości w komórce, uaktywnienie komórki lub arkusza, lub samo otwarcie pliku Excela. Pomiędzy tymi dwoma słowami kluczowymi zawarte jest polecenie wywołujące funkcję usuwającą duplikaty w podanym przez nas zakresie.
Teraz wystarczy powyższy kod przypisać do przycisku w Excelu. Aby to zrobić, na karcie głównej należy wejść w kartę Deweloper i w obszarze Formanty kliknąć Wstaw i wybrać pierwszą opcję Przycisk (formant formularza). Następnie możesz określić, jak duży ma być Twój przycisk. Po tym, gdy skończysz „rysować” przycisk, na ekranie pojawi Ci się nowe okno, w którym Excel poprosi Cię o przypisanie makra do utworzonego przycisku. Wybierz opcję Nowe i w nowym okienku, które pojawi się na ekranie, wklej powyższy kod. Zwróć uwagę, aby treść kodu była wklejona pomiędzy słowa kluczowe Sub i End Sub.
Jak odkryć kartę Deweloper?
Jeśli nie widzisz u siebie karty deweloper, kliknij prawym przyciskiem myszy na dowolną zakładkę dostępną na wstążce głównej i wybierz opcję Dostosuj wstążkę. W okienku, które pojawi się na ekranie, możesz odnaleźć na liście pozycję Deweloper i zaznaczyć checkbox, co spowoduje pojawienie się karty na wstążce głównej.
Jeśli chcesz korzystać z makr, koniecznie sprawdź, czy zapisałeś swój plik Excel jako plik z obsługą makr (rozszerzenie pliku xlsm, zamiast standardowego xlsx). Aby zmienić rozszerzenie w otwartym pliku, możesz wybrać opcję Zapisz jako (lub użyć skrótu klawiszowego klikając F12) i w nowym oknie, w pozycji Zapisz jako typ kliknąć Skoroszyt programu Excel z obsługą makr.
Podsumowanie
Mam nadzieję, że po przeczytaniu tego artykułu wiesz już odpowiednio dużo na temat duplikatów w Excelu, aby nie sprawiały Ci one problemów w codziennej pracy. Myślę, że dzięki zastosowaniu przedstawionym tutaj rozwiązaniom będziesz w stanie odpowiednio zabezpieczyć swoje pliki przed pojawieniem się zduplikowanych wartości lub odpowiednio zareagować, jeśli jakieś się pojawią.
