Możesz znaleźć i podświetlić zduplikowane wpisy między dwiema kolumnami za pomocą funkcji formatowania warunkowego w Arkuszach Google.
Pracując w Arkuszach Google z dużymi zestawami danych, prawdopodobnie napotkasz problem, w którym masz do czynienia z wieloma zduplikowanymi wartościami. Podczas gdy niektóre zduplikowane wpisy są umieszczane celowo, podczas gdy inne są błędami. Jest to szczególnie ważne, gdy współpracujesz z zespołem na tym samym arkuszu.
Jeśli chodzi o analizę danych w Arkuszach Google, możliwość odfiltrowania duplikatów może być niezbędna i wygodna. Chociaż Arkusze Google nie mają natywnej obsługi znajdowania duplikatów w arkuszach, oferują kilka sposobów porównywania, identyfikowania i usuwania duplikatów danych w komórkach.
Czasami chcesz porównać każdą wartość w kolumnie z inną kolumną i sprawdzić, czy są w niej jakieś duplikaty i odwrotnie. W Arkuszach Google możesz łatwo znaleźć duplikaty między dwiema kolumnami za pomocą funkcji formatowania warunkowego. W tym artykule pokażemy, jak porównać dwie kolumny w Arkuszach Google i znaleźć między nimi duplikaty.
Znajdź zduplikowane wpisy między dwiema kolumnami za pomocą formatowania warunkowego
Formatowanie warunkowe to funkcja Arkuszy Google, która pozwala użytkownikowi zastosować określone formatowanie, takie jak kolor czcionki, ikony i paski danych, do komórki lub zakresu komórek na podstawie określonych warunków.
Możesz użyć tego formatowania warunkowego, aby wyróżnić zduplikowane wpisy między dwiema kolumnami, wypełniając komórki kolorem lub zmieniając kolor tekstu. Musisz porównać każdą wartość w kolumnie z inną kolumną i sprawdzić, czy jakakolwiek wartość się powtarza. Aby to zadziałało, musisz zastosować formatowanie warunkowe do każdej kolumny osobno. Wykonaj następujące kroki, aby to zrobić:
Otwórz arkusz kalkulacyjny, który chcesz sprawdzić pod kątem duplikatów w Arkuszach Google. Najpierw wybierz pierwszą kolumnę (A), aby sprawdzić z kolumną B. Możesz podświetlić całą kolumnę, klikając literę kolumny nad nią.
Następnie kliknij menu „Format” na pasku menu i wybierz „Formatowanie warunkowe”.
Menu formatowania warunkowego otwiera się po prawej stronie arkuszy Google. Możesz potwierdzić, że zakres komórek jest wybrany w opcji „Zastosuj do zakresu”. Jeśli chcesz zmienić zakres, kliknij „ikonę zakresu” i wybierz inny zakres.
Następnie kliknij menu rozwijane w sekcji „Reguły formatu” i wybierz opcję „Formuła niestandardowa to”.
Teraz musisz wprowadzić niestandardową formułę w polu „Wartość lub formuła”.
Jeśli wybrałeś całą kolumnę (B:B), wprowadź następującą formułę LICZ.JEŻELI w polu „Wartość lub formuła” w obszarze Reguły formatu:
=liczba($B:$B,$A2)>0Lub,
Jeśli zaznaczyłeś zakres komórek w kolumnie (powiedzmy sto komórek, A2:A30), użyj tej formuły:
=LICZ.JEŻELI($B$2:$B$30,$A2)>0Podczas wprowadzania formuły pamiętaj, aby zastąpić wszystkie wystąpienia litery „B” w formule literą podświetlonej kolumny. Dodajemy znak „$” przed odwołaniami do komórek, aby uczynić je zakresem bezwzględnym, więc to się nie zmienia, stosujemy formułę.
W sekcji Styl formatowania możesz wybrać styl formatowania do wyróżniania zduplikowanych elementów. Domyślnie użyje zielonego koloru wypełnienia.
Możesz wybrać jeden z gotowych stylów formatowania, klikając „Domyślny” w opcjach „Styl formatowania”, a następnie wybierając jedno z gotowych ustawień.
Możesz też użyć dowolnego z siedmiu narzędzi formatowania (pogrubienie, kursywa, podkreślenie, przekreślenie, kolor tekstu, kolor wypełnienia) w sekcji „Styl formatowania”, aby wyróżnić duplikaty.
Tutaj wybieramy kolor wypełnienia dla zduplikowanych komórek, klikając ikonę „Kolor wypełnienia” i wybierając kolor „żółty”.
Po wybraniu formatowania kliknij „Gotowe”, aby podświetlić komórki.
Funkcja LICZ.JEŻELI zlicza, ile razy każda wartość komórki w „Kolumnie A” pojawia się w „Kolumnie B”. Jeśli więc element pojawi się chociaż raz w kolumnie B, formuła zwróci TRUE. Następnie ten element zostanie podświetlony w „Kolumnie A” na podstawie wybranego formatowania.
Nie podświetla to duplikatów, ale raczej podkreśla elementy, które mają duplikaty w kolumnie B. Oznacza to, że każdy podświetlony na żółto element ma duplikaty w kolumnie B.
Teraz musimy zastosować formatowanie warunkowe do kolumny B przy użyciu tej samej formuły. Aby to zrobić, wybierz drugą kolumnę (B2:B30), przejdź do menu „Format” i wybierz „Formatowanie warunkowe”.
Możesz też kliknąć przycisk „Dodaj kolejną regułę” w okienku „Reguły formatu warunkowego”.
Następnie potwierdź zakres (B2:B30) w polu „Zastosuj do zakresu”.
Następnie ustaw opcję „Formatuj komórki, jeśli…” na „Niestandardowa formuła to” i wprowadź poniższą formułę w polu formuły:
=LICZ.JEŻELI($A$2:$A$30,$B2)>0Tutaj używamy zakresu kolumny A ($ A 2: $ A $ 30) w pierwszym argumencie i "$ B2" w drugim argumencie. Ta formuła sprawdzi wartość komórki w „kolumnie B” z każdą komórką w kolumnie A. Jeśli zostanie znalezione dopasowanie (duplikat), formatowanie warunkowe spowoduje podwyższenie tego elementu w „kolumnie B”
Następnie określ formatowanie w opcjach „Styl formatowania” i kliknij „Gotowe”. Tutaj wybieramy kolor pomarańczowy dla kolumny B.
Spowoduje to podświetlenie elementów z kolumny B, które mają duplikaty w kolumnie A. Teraz znalazłeś i podświetliłeś zduplikowane elementy między dwiema kolumnami.
Zapewne zauważyłeś, chociaż w kolumnie A znajduje się duplikat słowa „Arcelia”, ale nie jest on wyróżniony. Dzieje się tak, ponieważ duplikat wartości znajduje się tylko w jednej kolumnie (A), a nie między kolumnami. Dlatego nie jest wyróżniony.
Wyróżnij duplikaty między dwiema kolumnami w tym samym wierszu
Możesz także wyróżnić wiersze, które mają te same wartości (duplikaty) między dwiema kolumnami, używając formatowania warunkowego. Reguła formatowania warunkowego może sprawdzić każdy wiersz i wyróżnić wiersze, które mają pasujące dane w obu kolumnach. Oto jak to zrobić:
Najpierw wybierz obie kolumny, które chcesz porównać, a następnie przejdź do menu „Format” i wybierz „Formatowanie warunkowe”.
W panelu Reguły formatu warunkowego potwierdź zakres w polu „Zastosuj do zakresu” i wybierz „Formuła niestandardowa to” z menu rozwijanego „Komórki formuł, jeśli...”.
Następnie wprowadź poniższą formułę w polu „Wartość lub formuła”:
=$A2=$B2Ta formuła porównuje wiersz po wierszu dwie kolumny i podświetla wiersze, które mają identyczne wartości (duplikaty). Jak widać, wpisana tutaj formuła dotyczy tylko pierwszego wiersza z wybranego zakresu, ale formuła zostanie automatycznie zastosowana do wszystkich wierszy w wybranym zakresie przez funkcję formatowania warunkowego.
Następnie określ formatowanie w opcjach „Styl formatowania” i kliknij „Gotowe”.
Jak widać, tylko te wiersze, które mają pasujące dane (duplikaty) między dwiema kolumnami, zostaną podświetlone, a wszystkie inne duplikaty zostaną zignorowane.
Zaznacz zduplikowane komórki w wielu kolumnach
Podczas pracy z większymi arkuszami kalkulacyjnymi z wieloma kolumnami możesz wyróżnić wszystkie duplikaty, które pojawiają się w wielu kolumnach, a nie tylko w jednej lub dwóch kolumnach. Nadal możesz użyć formatowania warunkowego, aby wyróżnić duplikat w wielu kolumnach.
Najpierw wybierz zakres wszystkich kolumn i wierszy, w których chcesz wyszukać duplikaty, a nie tylko jedną lub dwie kolumny. Możesz wybrać całe kolumny, przytrzymując klawisz Ctrl, a następnie klikając literę u góry każdej kolumny. Możesz także kliknąć pierwszą i ostatnią komórkę w swoim zakresie, przytrzymując jednocześnie klawisz Shift, aby wybrać wiele kolumn naraz.
W tym przykładzie wybieramy A2:C30.
Następnie kliknij opcję „Format” w menu i wybierz „Formatowanie warunkowe”.
W regułach formatu warunkowego ustaw reguły formatu na „Formuła niestandardowa to”, a następnie wprowadź następującą formułę w polu „Wartość lub formuła”:
=countif($A$2:$C$30,A2)>Dodajemy znak „$” przed odwołaniami do komórek, aby uczynić je kolumnami bezwzględnymi, więc to się nie zmienia, stosujemy formułę. Możesz również wpisać formułę bez znaków „$”, działa to w obie strony.
Następnie wybierz formatowanie, w którym chcesz podświetlić zduplikowane komórki, korzystając z opcji „Styl formatowania”. Tutaj wybieramy kolor wypełnienia „żółty”. Następnie kliknij „Gotowe”.
Spowoduje to podświetlenie duplikatów we wszystkich wybranych kolumnach, jak pokazano poniżej.
Po zastosowaniu formatowania warunkowego możesz w dowolnym momencie edytować lub usunąć regułę formatowania warunkowego.
Jeśli chcesz edytować obecną regułę formatowania warunkowego, wybierz dowolną komórkę z formatowaniem warunkowym, przejdź do „Format” w menu i wybierz „Formatowanie warunkowe”.
Spowoduje to otwarcie panelu „Warunkowe reguły formatu” po prawej stronie z listą reguł formatu zastosowanych do bieżącego wyboru. Po najechaniu myszą na regułę pokaże się przycisk usuwania, kliknij przycisk usuwania, aby usunąć regułę. Lub, jeśli chcesz edytować aktualnie wyświetlaną regułę, kliknij samą regułę.
Jeśli chcesz dodać kolejne formatowanie warunkowe do bieżącej reguły, kliknij przycisk „Dodaj kolejną regułę”.
Policz duplikaty między dwiema kolumnami
Czasami chcesz policzyć, ile razy wartość w jednej kolumnie powtórzy się w innej kolumnie. Można to łatwo zrobić za pomocą tej samej funkcji LICZ.JEŻELI.
Aby sprawdzić, ile razy wartość z kolumny A występuje w kolumnie B, wprowadź następującą formułę w komórce w innej kolumnie:
=LICZ.JEŻELI($B$2:$B$30,$A2)Wprowadź tę formułę w komórce C2. Ta formuła zlicza, ile razy wartość w komórce A2 istnieje w kolumnie (B2:B30) i zwraca liczbę w komórce C2.
Po wpisaniu formuły i naciśnięciu klawisza Enter pojawi się funkcja automatycznego wypełniania, kliknij „znacznik”, aby automatycznie wypełnić tę formułę pozostałymi komórkami (C3: C30).
Jeśli funkcja automatycznego wypełniania nie pojawia się, kliknij niebieski kwadrat w prawym dolnym rogu komórki C2 i przeciągnij go w dół, aby skopiować formułę z komórki C2 do komórek C3:C30.
Kolumna „Porównanie 1” (C) pokaże teraz, ile razy każda odpowiadająca jej wartość z kolumny A pojawiła się w kolumnie B. Na przykład wartość A2 lub „Franklyn” nie została znaleziona w kolumnie B, więc Funkcja LICZ.JEŻELI zwraca „0”. A wartość „Loreta” (A5) znajduje się dwukrotnie w kolumnie B, dlatego zwraca „2”.
Teraz musimy powtórzyć te same kroki, aby znaleźć zduplikowane liczby w kolumnie B. Aby to zrobić, wprowadź następującą formułę w komórce D2 w kolumnie D (porównanie 2):
=LICZ.JEŻELI($A$2:$A$30,$B2)W tej formule zamień zakres od „$ B2: $ B $ 30” na „$ A 2: $ A $ 30” i „$ B2” na „$ A2”. Funkcja zlicza, ile razy wartość w komórce B2 występuje w kolumnie A (A2:A30) i zwraca liczbę w komórce D2.
Następnie automatycznie wypełnij formułę do pozostałych komórek (D3:D30) w kolumnie D. Teraz „Porównanie 2” pokaże, ile razy każda odpowiadająca jej wartość w kolumnie B pojawiła się w kolumnie A. Na przykład , wartość B2 lub „Stark” znajduje się dwukrotnie w kolumnie A, więc funkcja LICZ.JEŻELI zwraca „2”.
Notatka: Jeśli chcesz policzyć duplikaty we wszystkich kolumnach lub wielu kolumnach, wystarczy zmienić zakres w pierwszym argumencie funkcji LICZ.JEŻELI na wiele kolumn zamiast tylko jednej kolumny. Na przykład zmień zakres z A2:A30 na A2:B30, co spowoduje zliczenie wszystkich duplikatów w dwóch kolumnach, a nie tylko w jednej.
Otóż to.