• Zapisz się do newslettera, aby otrzymywać powiadomienia o nowościach na blogu
    Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Jak wykryć duplikaty na podstawie 2 kolumn?

Czyli formuła w formatowaniu warunkowym

Wyobraźmy sobie sytuację, w której prowadzimy spis projektów, przykładowo obiektów budowlanych, na budowę których sprzedajemy towary. Mamy więc listę, w której odnotowujemy projekty i uczestniczących w nich klientów. Zależy nam na tym, aby na tej liście każda para Projekt-Klient wystąpiła tylko raz. Nie chcemy powiem dublować danych. Chodzi o coś takiego:

Czyli jak dopisujemy do listy nowe dane: projekt i klienta, to Excel ma nam wykrywać, czy ich kombinacja już wcześniej nie wystąpiła. O tym jak to zrobić jest ten wpis.

Zapewne się już domyślasz, że do rozwiązania tego zadania użyję formatowania warunkowego. Gdyby w zadaniu chodziło o to, aby zaznaczyć tylko jedną kolumnę (my chcemy 2), w której są duplikaty – nie ma problemu, mamy od tego wbudowaną funkcjonalność. Jednak tutaj zadanie jest bardziej złożone: chcemy zaznaczyć 2 kolumny, których para definiuje wartość, która nie może się powtórzyć. Wracając do naszego przykładu: możemy mieć sytuację, że na jeden projekt kupuje kilku klientów, ale jeden klient w jednym projekcie może wystąpić tylko raz. To już nie będzie takie oczywiste i, aby to osiągnąć, napiszę formułę w formatowaniu warunkowym. Zacznijmy więc od tej formuły.

Formuła

Załóżmy, że jesteśmy w wierszu 10. arkusza i chcemy sprawdzić, czy wcześniej w tabeli wystąpiła już kombinacja projektu i klienta. Najpierw sprawdzimy, czy wystąpił już dany projekt, a potem klient. Przekładając to na język Excela: sprawdzimy, czy w zakresie B3:B9 wystąpił już projekt (i analogicznie dla klienta). Coś takiego umie zrobić funkcja LICZ.JEŻELI. Natomiast my tutaj potrzebujemy sprawdzić jednocześnie klienta, więc mamy de facto 2 warunki do sprawdzenia. To z kolei umie zrobić LICZ.WARUNKI, którą się tutaj posłużę. Sprawdzę, czy w dotychczasowym zakresie projektów wystąpił nasz wpisywany projekt i czy w dotychczasowym zakresie klientów – klient.

Formułę tworzę tradycyjnie dla pierwszego wiersza tabeli (gdzieś w komórce obok, np. I4). Pierwszym wierszem jest więc wiersz 4. (dla ułatwienia formuły). Dla niego więc dotychczasowym zakresem jest B3:B3. Natomiast dla kolejnego wiersza – 5., dotychczasowym zakresem jest zakres B3:B4 itd. Zauważ, że ten dotychczasowy zakres będzie nam się powiększał sukcesywnie. Samo z siebie się to nie zrobi, więc musimy odpowiednio zablokować komórki. Dodatkowo musimy pamiętać, że formuła ta ma funkcjonować w formatowaniu warunkowym na więcej niż jedną kolumnę (konkretnie na 2). To również należy uwzględnić w adresowaniu.

Sama funkcja jest taka:

=LICZ.WARUNKI(B3:B3;B4;C3:C3;C4)

Natomiast adresowanie powinno być następujące:

=LICZ.WARUNKI($B$3:$B3;$B4;$C$3:$C3;$C4)

Zauważ, że dla każdej pary zakresów jest identyczne. Dlaczego takie? $B$3:$B3 dlatego, że zawsze zaczynamy dotychczasowy zakres od pierwszej komórki, B3, więc musi ona być całkowicie zablokowana. Natomiast :$B3 jest dlatego, żeby formatowanie warunkowe działało prawidłowo: niezależnie od tego, która komórka ma zostać sformatowana – projektu zawsze będziemy szukać w kolumnie B. Dlatego właśnie ją blokujemy. Nie blokujemy zaś wiersza 3., ponieważ on ma się zmieniać, aby dotychczasowy zakres był dynamiczny. Jak widać cała magia tkwi w adresowaniu 🙂 (adresowania komórek możesz nauczyć się tutaj).

Dokładnie taką samą logikę stosujemy dla klienta – zauważ, że adresowanie jest analogiczne, tylko dla kolumny C.

Ok. Teraz robimy magię, czyli połączymy tę formułę z formatowaniem warunkowym. Do dzieła!

Formatowanie warunkowe

Teraz już łatwiejsza część: wklejenie formuły i wybór kolorków. Zanim jednak do tego przejdziemy, należy zaznaczyć zakres, którego ma dotyczyć formatowanie warunkowe. W naszym przypadku jest to zakres dwóch pierwszych kolumn tabeli, czyli B4:C9. Ważne jest tutaj, aby zakres ten zaznaczyć od początku, czyli od komórki B4, ponieważ za chwilę napiszemy regułę formatowania warunkowego, która musi być spójna z zaznaczeniem!

Następnie wybieramy Narzędzia główne/Formatowanie warunkowe/Nowa reguła…/Użyj formuły do określenia komórek, które należy sformatować (pkt. 1) i do okienka, które się pokaże (obrazek poniżej) wklejamy napisaną wcześniej formułę (pkt. 2).

Okienko formatowania warunkowego

Okienko formatowania warunkowego

Koniecznie pamiętaj też o ustawieniu kolorków, czyli formatowania, jakie ma zostać zastosowane, gdy warunek będzie spełniony, czyli gdy na liście będą duplikaty (pkt. 3).

Po naciśnięciu OK, jeśli na liście nie ma duplikatów, a tak jest w moim przykładzie), nie zobaczymy jeszcze żadnej różnicy. Zobaczymy ją dopiero, gdy dopiszemy projekt i klienta do listy. Natomiast, żeby też to zadziałało (na razie zdefiniowaliśmy formatowanie warunkowe tylko dla istniejącego zakresu, a nie do powiększonego o nowe wpisy), musimy stworzyć na naszym zakresie danych tabelę (obiekt tabela). Obiekt ten ma bowiem tę właściwość, że gdy dopiszemy pod nim dane – powiększa swój rozmiar i kopiuje istniejące formatowanie. I o to właśnie nam chodzi.

Tabela

Stworzenie tabeli to też pikuś – zanzaczamy dowolną komórkę naszego zakresu i naciskamy skrót klawiszowy Ctrl + t. Zatwierdzamy OK w okienku Tworzenie tabeli (koniecznie sprawdź, czy Excel prawidłowo rozpoznał, że Twoja tabela ma nagłówki!).

Tworzenie tabeli

Tworzenie tabeli

Teraz możemy dopisać nowe wartości i przetestować, czy się udało. Magia, prawda?

Wynik

Wynik

Polecam Ci zajrzeć tutaj:

 

A na koniec, jak zwykle, mam dla Ciebie plik z gotowcem do pobrania oraz film, na którym rozwiązuję opisane wyżej zagadnienie:

I wersja wideo:

 

Spodobał Ci się ten artykuł?

Jeśli TAK, zapisz się na newsletter, aby otrzymywać powiadomienia
o o nowościach na blogu.

Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Tagi , , , , , , , , , .Dodaj do zakładek Link.

9 odpowiedzi na „Jak wykryć duplikaty na podstawie 2 kolumn?

  1. Magda mówi:

    Bardzo dziękuję za ten wpis! 🙂 <3
    Jak wprowadzę w swoim rejestrze dam znać 🙂

    • Malina mówi:

      Magda,
      super, że o to chodziło. Powodzenia we wdrażaniu do swojego pliku:)
      Niezły zbieg okoliczności,prawda? Odpowiadałam na pytanie zupełnie innej osoby, a przy okazji odpowiedziałam na Twoje, które zadałaś mi w mailu! Ale super 🙂 I to w tym samym czasie:)

  2. Magda mówi:

    Zadziałało! cud-Malina ;P
    I znowu w pracy będą mnie hołubić 😉
    A mogę być ciut bezczelna? – przydałoby się żeby jeszcze formuła zwracała informację w którym “Lp” lub w którym wierszu pojawił się taki sam zestaw klienta z projektem 🙂 – bo moja tabela ma 5 tys.wierszy, a nie wszyscy współpracownicy ogarniają filtrowanie 😀

    • Malina mówi:

      Magda, Super, bardzo się cieszę 🙂
      Odnośnie numeru wiersza (arkusza), to przychodzi mi do głowy taka formuła tablicowa: =PODAJ.POZYCJĘ(B4&C4;tbProjekty[Projekt]&tbProjekty[Klient];0)+3
      Zakładając, że tabela nazywa się tbProjekty i zaczyna się w wierszu jak na formatce (stąd jest +3). Jeśli Zaczyna się od początku arkusza – trzeba będzie dodać tylko 1, dla nagłówka.
      🙂

  3. Zbyszek mówi:

    Witaj, Malina.
    Świetny materiał, ale zastanawia mnie co się stało na filmiku z formułą w kolumnie pomocniczej “I”?
    Po wpisaniu duplikatu pojawiła się wartość 2 zamiast 1, a z kolei po zmianie klienta w I10 wartość zmieniła się na 1, choć wyżej duplikatów nie było.
    Zauważyłaś?

    Pozdrawiam 🙂

    • Malina mówi:

      Zbyszek, dziękuję. Też to zauważyłam i mnie dziwi. Myślę, że to bug tego samego pokroju to stosowanie powiększające sie zakresu w tabelach. Tam przedostatnia formuła głupieje z kolei… Jaką masz wersję Excela? U Ciebie dzieje się to samo?

  4. Leszek mówi:

    Witam
    Jestem na etapie tworzenia w exelu książki przychodów i rozchodów, chciałem zastosować to formatowanie warunkowe i wszystko działa, ale w chwili kiedy przechodzę na kolejną stronę i celowo wprowadzam tego samego klienta z tą samą FV i nie podświetla mi powtórzonej FV, jest na to jakiś sposób?

    • Malina mówi:

      Hej Leszek 🙂
      A jak jest skonstruowane formatowanie warunkowe? Cyz odwołuje się np. do innego arkusza, tego, gdzie wprowadzasz duplikaty?

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *