Jak znaleźć k‑ty duplikat po nieposortowanej liście?
Żeby znaleźć k‑ty duplikat przy nieposortowanej liście możesz wykorzystać przynajmiej 2 drogi. Pierwsza wykorzystuje formuły tablicowe, a druga funkcje, które radzą sobie z tablicami.
Znajdź k‑ty duplikat przy nieposortowanej liście — sztuczki #29
1 rozwiązanie (formuły tablicowe)
W pierwszej kolejności trzeba znaleźć relatywne pozycje poszczególnych wierszy. Można to zrobić wykorzystując np taką formułę:
=WIERSZ(A2:A13)-WIERSZ(A2)+1
Czyli od numeru poszczególnych wierszy odejmujesz numer pierwszego wiersza, a następnie dodajesz jedynkę, żeby pozycje zaczynały się liczyć od 1.
Następnie przyda się sprawdzenie, gdzie znajduje się duplikat, którego aktualnie szukasz:
=A2:A13=E2
A żeby było lepiej to możesz wykorzystać do znalezienia tylko tych pozycji relatywnych wierszy, gdzie znajdują się nasze duplikaty. Przyda się nam do tego funkcja JEŻELI, którą skonstruuje się tak, żeby podawała relatywną pozycję wiersza, tylko dla szukanych duplikatów (konkretnego wyrażenia), a dla reszty zwracała fałsz:
=JEŻELI(A2:A13=E2;WIERSZ(A2:A13)-WIERSZ(A2)+1)
Co daje wynik podobny do:
{FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;5;FAŁSZ;7;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;12}
Tylko kilka liczb i dużo wyrażenia FAŁSZ. Teraz gdy wykorzystasz funkcję MIN.K podając jej tą listę i numer k‑tego duplikatu uzyskasz numer wiersza, gdzie znajduje się szukany duplikat.
=MIN.K(JEŻELI(A2:A13=E2;WIERSZ(A2:A13)-WIERSZ(A2)+1);D2)
Teraz już wystarczy tylko funkcja INDEKS to pobrania wartości, której szukasz
=INDEKS(B2:B13;MIN.K(JEŻELI(A2:A13=E2;WIERSZ(A2:A13)-WIERSZ(A2)+1);D2))
i oczywiście pamiętaj zatwierdzić ją Ctrl + Shift + Enter , bo to formuła tablicowa.
1 rozwiązanie — formuła tablicowa
2 rozwiązanie (funkcja AGREGUJ)
W tym rozwiązaniu zostanie wykorzystana funkcja AGREGUJ, z parametrami, które pozwolą jej działać na tablicach. Przydadzą nam się też pierwsze wyliczenia z wcześniejszego rozwiązania. Tym razem nie wykorzystamy dla nich funkcji JEŻELI, tylko odpowiednio je podzielimy:
=(WIERSZ(A2:A13)-WIERSZ(A2)+1)/(A2:A13=E2)
Ponieważ część druga tego równania może być zerem (FAŁSZ w działaniach matematycznych zamienia się na 0), wystąpi błąd dzielenia przez zero — #DZIEL/0! dlatego 2 parametr funkcji AGREGUJ ustawiamy na 6, by te błędy ignorował. 1 parametr to znana już wcześniej funkcja MIN.K (numer 15), która radzi sobie z tablicami.
=AGREGUJ(15;6;(WIERSZ(A2:A13)-WIERSZ(A2)+1)/(A2:A13=E2);D2)
Ostatni argument (D2) funkcji AGREGUJ to numer szukanego duplikatu.
Wynikiem jest znów numer wiersza relatywnie określony, więc wystarczy, że dołożysz funkcję INDEKS i masz swój wynik:
=INDEKS(B2:B13;AGREGUJ(15;6;(WIERSZ(A2:A13)-WIERSZ(A2)+1)/(A2:A13=E2);D2))
2 rozwiązanie — funkcja AGREGUJ
P.S.
Wpis na podstawie Excel Magic Trick 1005
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 — Znajdź k‑ty duplikat przy nieposortowanej liście — sztuczki #29
