Jak sformatować warunkowo wiersz z k‑tym wystąpieniem szukanego duplikatu?
Szukamy sposobu na stworzenie formuły, który zapewni nam formatowanie wiersza, gdzie został znaleziony k‑ty duplikat. Wypróbujemy 2 rozwiązania do posortowanych danych i dla danych niesortowanych.
Formatowanie warunkowe na wierszu z k‑tym duplikatem dane posortowane lub nie — sztuczki #28
Budowanie formuły dla formatowania warunkowego często wygodniej na początek stworzyć obok obszaru, który chcemy poddać formatowaniu, a następnie skopiować formułę z lewej górnej komórki (od niej najczęściej się zaznacza dane) do formatowania warunkowego.
1 podejście
Najpierw chcemy sprawdzić czy numer wystąpienia zgadza się z numer wystąpienia duplikatu, które szukamy. Możemy rozpocząć od funkcji
=LICZ.JEŻELI($A$4:$A4;$A4)
Zliczanie numeru wystąpień poszczególnych duplikatów
Bardzo istotne są odpowiednie odwołania względne:
a) nasz obszar ma 2 kolumny, więc w większości sytuacji będziemy blokować kolumnę ($A4), ale nie wiersz.
b) 1 argument funkcji LICZ.JEŻELI jest obszarem rosnącym dynamicznie od komórki $A$4 (stała zablokowana bezwględnie w obu kierunkach) do $A4, która zapewnia rozrost obszaru w dół
Dzięki temu mamy kolejne numery wystąpień kolejnych duplikatów. Teraz potrzebujemy je przyrównać do numeru szukanego duplikatu:
=LICZ.JEŻELI($A$4:$A4;$A4)=$E$2
PRAWDA gdy zgadza się numer dupikatu, czyli brakuje jeszcze 2 warunku
Powoduje nam to wystąpienie wartości PRAWDA w aż 4 wierszach ponieważ sprawdzamy tylko 1 warunek, czyli numer wystąpienia duplikatu, ale nie sprawdzamy wartości samego duplikatu, żeby połączyć te warunki najprościej skorzystać z funkcji ORAZ i wstawić do niej też 2 warunek:
=ORAZ(LICZ.JEŻELI($A$4:$A4;$A4)=$E$2;$A4=$F$2)
Dodany 2 warunek PRAWDA tylko dla 1 wiersza (1 formuła)
2 warunek $A4=$F$2, czyli przyrównujemy wartość (imię) z odpowiedniego wiersza do wartości, której szukamy. Ta formuła zwraca nam poprawną wartość (PRAWDA jest tylko w 1 wierszu).
2 podejście
Uwzględnia ono to co zrobiliśmy w sztuczce 27, czyli wykorzystujemy funkcję PODAJ.POZYCJĘ do której dodajemy numer szukanego duplikatu i odejmujemy jedynkę, by uzyskać właściwą pozycję
=PODAJ.POZYCJĘ($F$2;$A$4:$A$15;0)+$E$2–1
Wyszukanie relatywnej pozycji szukanego duplikatu (2 formuła)
Potrzebujemy ją teraz porównać do relatywnego numeru wiersza. Możemy to uzyskać wykorzystując formułę:
=PODAJ.POZYCJĘ($F$2;$A$4:$A$15;0)+$E$2–1=ILE.WIERSZY(F$4:F4)
Funkcja ILE.WIERSZY zlicza wiersze od początku obszaru i tym samym daje nam informację o relatywnej pozycji wiersza, w którym aktualnie jesteśmy.
Wynik dla 2 formuły przy posortowanej liście
Teraz gdy mamy 2 formuły możemy je przetestować pod kątem sortowania danych, czyli która formuła się sprawdza lepiej, gdy dane są nie posortowane. Szybko okazuje się że 2 sposób nie radzi sobie z nieposortowanymi danymi (było to wspomniane, w sztuczce 27).
Wynik obu formuł dla nieposortowanej liście (2 formuła nie daje rady)
P.S.
Wpis na podstawie Excel Magic Trick 1004
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.
Bezpośredni odnośnik do filmu na youtube — Formatowanie warunkowe na wierszu z k‑tym duplikatem dane posortowane lub nie — sztuczki #28
