Pewnym sposobem na wywołanie westchnień u księgowego jest przekazanie mu listy w Excelu pełnej zduplikowanych wartości. Na szczęście istnieje kilka sposobów, aby zidentyfikować duplikaty wartości w arkuszu kalkulacyjnym.
Warunkowe formatowanie zduplikowanych wartości
Załóżmy, że chcesz określić, czy na liście numerów faktur, jak pokazano na rysunku 1, występują duplikaty:
1. Zaznacz zakres komórek, które mogą zawierać zduplikowane wartości.
2. Wybierz polecenie Formatowanie warunkowe z menu głównego programu Excel.
3. Wybierz opcję Highlight Cells Rules.
4. Wybierz Duplicate Values.
5. Funkcja Formatowanie warunkowe oferuje podgląd na żywo, co oznacza, że wszelkie zduplikowane wartości będą oznaczone kolorami. Opcjonalnie możesz użyć drugiej listy rozwijanej w oknie dialogowym Duplikaty wartości, aby dostosować formatowanie do własnych potrzeb. Kliknij OK, aby zidentyfikować zduplikowane wartości.
Możesz zidentyfikować duplikaty na liście za pomocą formatowania warunkowego.
W tym momencie możesz użyć funkcji Filtr, aby wyświetlić tylko zduplikowane wartości:
1. Zaznacz dowolną komórkę listy, do której zastosowano formatowanie warunkowe.
2. Uaktywnij menu Dane
3. Wybierz polecenie Filtr
4. Kliknij strzałkę filtra dla kolumny, która ma formatowanie warunkowe
5. Wybierz polecenie Filtr według koloru
6. Wybierz kolor z listy
7. Jak widać na rysunku 2, arkusz jest zwinięty tylko do zduplikowanych wartości
8. Kliknij strzałkę filtra dla kolumny, która ma formatowanie warunkowe
9. Wybierz polecenie Wyczyść filtr, aby wyświetlić całą listę lub kliknij polecenie Wyczyść w menu Dane.
Listy można filtrować na podstawie koloru.
Pamiętaj, że formatowanie warunkowe jest żywą warstwą formatowania, którą dodałeś do tej części arkusza. Jeśli dodasz dodatkowy znak do jednego z numerów zduplikowanych faktur, zobaczysz, że kodowanie kolorem zniknie z tej komórki i z poprzedniej komórki duplikatu.
Podczas usuwania duplikatów na liście w ten sposób lubię kliknąć polecenie Ponownie zastosuj w menu Dane, które powoduje ponowne zastosowanie filtra. Ukrywa to wszystkie unikalne wartości, dzięki czemu na liście widoczne są tylko pozostałe zduplikowane wartości.
Identyfikacja pierwszych instancji za pomocą filtra zaawansowanego
Mogą pojawić się okoliczności, w których chcesz oznaczyć kolorem pierwszą instancję danej wartości:
1. 1. Jak pokazano na rysunku 3, wybierz dowolną komórkę z listy
2. Uaktywnij menu Dane w programie Excel.
3. Kliknij przycisk Zaawansowane w grupie Sortowanie i filtrowanie
4. Użyj pola Zakres listy w oknie dialogowym Filtr zaawansowany, aby wybrać zakres komórek, w którym chcesz zidentyfikować unikalne wartości.
5. Kliknąć pole wyboru Tylko unikatowe rekordy w oknie dialogowym Filtr zaawansowany. 6.
6. Kliknij przycisk OK.
7. W tym momencie zostaną wyświetlone tylko unikatowe rekordy, a pasek stanu programu Excel będzie odzwierciedlał liczbę unikatowych elementów. Opcjonalnie można zastosować dowolne formatowanie, takie jak kolor wypełnienia.
8. Ponownie uaktywnij menu Dane.
9. Kliknij polecenie Wyczyść, aby ponownie wyświetlić całą listę. Jeśli zastosowałeś formatowanie, tylko pierwszy przypadek danej wartości będzie oznaczony kolorem, drugi i kolejne nie będą.
Filtr zaawansowany pozwala na ukrycie zduplikowanych wartości i wyświetlenie tylko unikalnych rekordów.
Usuwanie duplikatów
Teraz, gdy już wiesz jak identyfikować duplikaty, przyjrzyjmy się jak usuwać duplikaty z listy. Termin operacyjny w tej pierwszej technice to "usuń", co oznacza, że funkcja fizycznie usuwa zduplikowane wartości. W związku z tym upewnij się, że najpierw wykonasz kopię listy, którą zamierzasz przetworzyć, jeśli potrzebujesz nienaruszonego zestawu wszystkich wartości:
1. Zaznacz dowolną komórkę na liście.
2. Uaktywnij menu Dane.
3. Kliknij przycisk Usuń duplikaty.
4. Kliknij przycisk OK w oknie dialogowym Usuń duplikaty.
5. Kliknij przycisk OK na monit informujący, ile zduplikowanych wartości znaleziono i ile pozostało.
Kliknij przycisk Undo (Cofnij) lub naciśnij klawisz Ctrl-Z, jeśli zorientujesz się, że usunąłeś duplikaty i chcesz odzyskać oryginalną listę. Jak pokazano na rysunku 4, jeśli lista ma dwie lub więcej kolumn, funkcja Remove Duplicates traktuje każdy wiersz jako pojedynczy rekord.
Bądź ostrożny, ponieważ usuwanie duplikatów fizycznie usuwa nie-unikalne elementy z listy.
Funkcja UNIQUE
Jeśli używasz programu Excel 2021 lub Microsoft 365, możesz również usuwać duplikaty za pomocą funkcji arkusza. W przeciwieństwie do ręcznej natury funkcji Usuń duplikaty, funkcja UNIKAT działa w czasie rzeczywistym i dynamicznie prezentuje listę unikalnych elementów. Ponadto Twoja oryginalna lista pozostaje nienaruszona, ponieważ UNIQUE dostarcza listę unikalnych wartości w osobnym miejscu w arkuszu kalkulacyjnym. Jak pokazano na rysunku 5, funkcja UNIQUE ma trzy argumenty:
- Array - zakres komórek, z których chcesz wyświetlić unikalne wartości.
- By_col - ten argument domyślnie przyjmuje wartość TRUE, co oznacza, że zwraca unikalne wartości z kolumny. Wpisz FALSE jeśli chcesz zwrócić unikalne wartości z całego wiersza.
- Exactly_once - wpisz tutaj TRUE aby wyświetlić elementy, które pojawiają się na liście dokładnie raz, lub FALSE aby wyświetlić po jednej z każdej unikalnej wartości bez względu na to ile razy pojawia się na liście.
Należy pamiętać, że by_col i exactly_once są opcjonalnymi argumentami, które należy podać tylko wtedy, gdy są potrzebne. Ponadto UNIQUE zwróci #CALC! jeśli podasz TRUE dla argumentu exactly_once, ale żaden element nie pojawi się na liście dokładnie raz.
Funkcja UNIQUE jest dynamiczną funkcją tablicową, co oznacza, że formułę wpisujemy tylko do jednej komórki, a wyniki rozleją się do tylu dodatkowych komórek, ile potrzeba. Jeśli na drodze staną jakieś dane lub jeśli nadpiszesz wyniki funkcji UNIQUE, pojawi się błąd #SPILL!, który możesz usunąć, usuwając zbędne dane z obszaru, do którego funkcja UNIQUE chce zwrócić wyniki.
Po kliknięciu na błąd #SPILL! wokół tego obszaru pojawi się obwódka. Jak pokazano na rysunku 5, możesz owinąć funkcję SORT wokół funkcji UNIQUE, aby automatycznie posortować listę unikalnych wartości.
Funkcja UNIQUE w programach Microsoft 365 i Excel 2021 umożliwia usuwanie duplikatów za pomocą formuły.
