fbpx

Jak wykryć duplikaty na podstawie 2 kolumn?

25.09.2018 | ECP2, Formatowanie warunkowe

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

Powiązane produkty:

 

A na koniec, jak zwykle, mam dla Ciebie plik z gotowcem do pobrania oraz film, na którym rozwiązuję opisane wyżej zagadnienie:
Plik do pobrania: MalinowyExcel Duplikaty na podstawie 2 kolumn dw.xlsx

 

I wersja wideo:

 

Jeżeli chcesz lepiej poznać Excela na poziomie średniozaawansowanym zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 2! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

16 komentarzy

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

    Reply
    • 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:)

      Reply
  2. 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 😀

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

      Reply
      • Zatwierdzasz oczywiście CSE 🙂

        Reply
  3. 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 🙂

    Reply
    • 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?

      Reply
  4. 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?

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

      Reply
      • Mam podobny problem. Wypełniam formularz w jednym arkuszu (zawsze te same komórki) następnie z użyciem makra kopiuje dane do innego arkusza tworząc listę. Chcę aby kontrola wystąpienia duplikatu odbywała się na etapie wypełniania formularza. Chcę wykorzystać funkcję poprawność danych aby w przypadku wystąpienia duplikatu wyskakiwał msgbox.

        Reply
  5. Witam,
    a czy dało by się tak zrobić, żeby zaznaczało tylko duplikaty powyżej, a ostatnie wystąpienie zostawało bez formatowania?

    Reply
    • Tak! Ale cudowny pomysł! Napiszę o tym wpis, bo to mega-super pytanie!

      Reply

Submit a Comment

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *