• 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.

5 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.
      🙂

Dodaj komentarz

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