Wszystko się powtarza – sposoby na dublujące się dane.
Usuwanie duplikatów w Excelu to koszmar większości stażystów w działach analitycznych. Siedzisz, wpatrujesz się jak sroka w gnat w ekran komputera, wypatrując powtarzających się wartości. Po pewnym czasie wszystko zlewa się przez oczami w jeden wielki, czarny placek podzielony na komórki.
Wyobraźcie sobie, że słyszałam o jednej osobie, która usuwa ręcznie duble przez cały miesiąc pracy. Całe szczęście Excel daje nam kilka możliwości usprawnienia takiej pracy, a w pewnym okolicznościach może wykonać ją za nas.
Jeśli nie spotkaliście się z tzw. duplikującymi się wartościami w Excelu – to są to identyczne wartości, które się powtarzają w wierszach bazy danych. Najczęściej występują, gdy eksportujemy dane z innego programu i musimy je odpowiednio opracować, aby dostosować do swoich potrzeb.
Inna sytuacja to błąd ludzki przy wprowadzaniu danych do bazy. Wyobraźmy sobie, że mamy tabelę ze zgłoszeniami incydentów od klientów, w której osoba obsługująca wpisuje:
- nazwę klienta,
- numer klienta,
- numer zgłoszenia,
- datę zgłoszenia,
- datę rozwiązania problemu.
Na powyższym przykładzie, wytłumaczę Wam, jak można wykryć lub pozbyć się dubli na pięć sposobów:
- Wykorzystując funkcję LICZ.JEŻELI, bez i z wykorzystaniem stworzonego ID,
- Za pomocą formatowania warunkowego,
- Używając funkcji „usuń duplikaty”,
- Tworząc tabelę przestawną,
- Pisząc krótkie makro, które zrobi to za nas 🙂
Wykorzystaj funkcję LICZ.JEŻELI
Funkcja LICZ.JEŻELI nie usuwa duplikatów, ale ułatwia wyłapanie dublujących się wartości. Jest bardziej narzędziem sprawdzającym, czy musimy się martwić dublami. Do naszej tabeli dodamy kolumnę „Duble licz jeżeli”, w której sprawdzimy duble po numerze zgłoszenia:
W kolumnie „Duble Licz jeżeli” widoczny jest wynik funkcji, a w kolumnie obok sama funkcja. Funkcja przeszukuje – wskazany jako pierwszy argument funkcji – zakres, w celu zliczenia liczby wystąpień wartości podanej jako drugi argument funkcji. Należy pamiętać, aby zakres był zaadresowany bezwzględnie (dodany symbol „$”), aby przy kopiowaniu formuły zakres pozostawał ten sam, a zmieniała się komórka z wartością, której występowanie jest zliczane przez funkcję.
Jeśli wynikiem funkcji jest cyfra 1 oznacza to, że dana wartość występuję w zakresie tylko jeden raz, a więc nie mamy do czynienia z dublami. Każdy wynik powyżej 1 oznacza liczbę wierszy, w której dana wartość się powtarza.
Najszybszy sposób na sprawdzenie, czy mamy duplikaty to rozwinięcie filtra w kolumnie, w której sprawdzaliśmy duble. Na filtrze powinny być widoczne wartości 1,2,3…, które są wynikiem funkcji LICZ.JEŻELI. Pamiętajcie, że jeśli wartości nie będą identyczne (np. wystąpi literówka w nazwie, dodatkowa spacja) to Excel będzie traktował je jak inne wartości.
Chcąc sprawdzić duplikaty, na podstawie więcej niż jednej kolumny, możemy stworzyć ID (identyfikator). Aby sprawdzić, czy dubluje nam się numer zgłoszenia, dotyczący tego samego klienta, które posiada zarówno taką samą datę zgłoszenia i zamknięcia, możemy stworzyć ID łączące wartości z tych kolumn w jednej kolumnie, a następnie sprawdzić duplikaty w tej kolumnie.
Użyj formatowania warunkowego
Drugim szybkim sposobem na sprawdzenie, czy w naszych danych występują duplikujące się wartości, jest skorzystanie z opcji formatowania warunkowego. Zaznaczamy
zakres, który chcemy sprawdzić, a następnie korzystamy ze ścieżki: NARZĘDZIA GŁÓWNE > FORMATOWANIE WARUNKOWE > REGUŁY WYRÓŻNIANIA KOMÓREK > DUPLIKUJĄCE SIĘ WARTOŚCI.
Jeśli jakaś wartość występuje więcej niż jeden raz w zaznaczonym zakresie to Excel wyróżni ją we wskazany przez nas sposób:
Narzędzie usuń duplikaty
Ok, czyli teraz już wiemy, że mamy duble, które są wynikiem błędu i chcemy się ich pozbyć. Po co? Przy dużej bazie danych duble mogą powodować wolniejsze działanie pliku, błędy przy sumowaniu wartości, np. jeśli będziemy zliczać liczbę wszystkich zleceń dla danego klienta to, bez usunięcia dubli, pojawi nam się suma większa niż była w rzeczywistości.
Najłatwiej zrobić to przez narzędzie USUŃ DUPLIKATY znajdujące się na wstążce DANE. Zaznaczamy dane, a następnie korzystamy ze ścieżki: DANE > USUŃ DUPLIKATY.
Wyświetli się okno, w którym zaznaczamy kolumny, które mają zostać sprawdzone pod kątem występowania duplikatów. Te wiersze, które będą się duplikować zostaną usunięte, tzn. zostanie tylko wiersz, w którym duplikująca się wartość występuje po raz pierwszy.
Jeśli w naszym przykładzie zaznaczymy wszystkie kolumny to Excel nie usunie żadnego wiersza, ponieważ w żadnym z wierszy nie występują we wszystkich kolumnach identyczne wartości (czyli w każdym z wierszy dane różnią się przynajmniej w jednej z kolumn).
Jeśli w oknie USUWANIA DUPLIAKTÓW zaznaczymy tylko kolumnę „Numer klienta” to zostaną usunięte wszystkie wiersze, w których powtarza się wcześniej występujący numer klienta. Istnieje również możliwość zaznaczenia kilku kolumn, co zadziała tak, jakbyśmy mieli stworzone ID, łączące wartości z tych kolumn – zostaną usunięte wiersze, dla których powtarzają się wartości we wszystkich zaznaczonych przez nas kolumnach. Na przykład, jeśli zaznaczymy numer klienta, datę zgłoszenia i datę rozwiązania zostanie usunięty tylko jeden wiersz:
Po każdym zastosowaniu tej funkcjonalności otrzymamy informacje, ile powtarzających się wartości zostało usuniętych:
Przed skorzystaniem z narzędzia usuwania duplikatów warto stworzyć sobie kopię zapasową danych, z których będziemy usuwać powtarzające się wartości. Excel usunie dane z zaznaczonego zakresu, a więc dane zostaną usunięte z naszej tabeli danych. Sposobem na odzyskanie tych danych jest cofnięcie wykonanej czynności poprzez przycisk COFNIJ.
Usuń duplikaty, a tabela przestawna.
A co, jeśli nie chcemy usuwać duplikujących się wartości, ponieważ informacje znajdujące się w kolejnych kolumnach są różne i w takim układzie też są nam potrzebne?
Spójrzmy na nasz przykład. Chcemy zobaczyć tylko listę wszystkich klientów (bez duplikatów) ale nie chcielibyśmy tracić pozostałych informacji. Wystarczy, że z naszej tabeli stworzymy TABELĘ PRZESTAWNĄ, a następnie do sekcji WIERSZE przeciągniemy interesującą nas kolumnę (klikamy na nazwę tej kolumny w sekcji pól, i trzymając cały czas wciśnięty prawy przycisk myszy „przeciągamy” i „upuszczamy” w sekcji wiersze).
W ten sposób otrzymamy listę wszystkich klientów, bez duplikujących się wartości:
Wykorzystanie w ten sposób tabeli przestawnej przydaje się, jeśli chcemy coś sprawdzić „na szybko”, np. ile klientów obsługujemy, bez ingerencji w bazę danych. Należy jednak pamiętać, że jeśli będziemy chcieli za pomocą takiej tabeli przestawnej sumować wartości z bazy danych, to zostaną one zsumowane ze wszystkich wierszy, co może doprowadzić do błędnych wyników.
Zautomatyzuj. Użyj makra.
A teraz pora na jazdę bez trzymanki, czyli MAKRO. Kiedy skorzystać z takiego rozwiązania? Wtedy, gdy co miesiąc usuwacie duble ze swojej bazy i już macie serdecznie dość „klikania” co miesiąc tego samego : )
W tym artykule zamieszczę Wam krótki skrypt działający jak narzędzie „usuń duplikaty”.
Sub Usuń_duble()
Range(„A1:E12”).RemoveDuplicates Columns:=Array(2, 4, 5), Header:=xlYes
End Sub
Jak dostosować to proste makro do Waszych potrzeb? Zmieniacie tylko następujące dane:
- „A1:E12” – tutaj wpiszcie zakres, w którym mają być sprawdzane i usuwane duplikujące się wiersze (tak jak zaznaczaliśmy tabelę korzystając z funkcji „Usuń duplikaty”)
- 2,4,5 – to są numery kolumn w naszym zakresie, które mają być sprawdzane pod kątem występowania duplikujących się wartości (tak jak zaznaczaliśmy „ptaszkami” nazwy kolumn korzystając z funkcjonalności „usuń duplikaty”), tutaj są to kolumny druga, czwarta i piąta (numer klienta, data zgłoszenia i data rozwiązania),
- xlYes – to jest ten „ptaszek” przy napisie „moje dane mają nagłówki”.
Jak już dostosujecie to makro do swoich potrzeb, to następnym razem tylko odpalacie plik, uruchamiacie makro i idziecie na obiad. Smacznego! : )
Agnieszka Kmieć
