Znajdź k-ty duplikat przy nieposortowanej liście – sztuczki #29

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

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

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube – Znajdź k-ty duplikat przy nieposortowanej liście – sztuczki #29

Formatowanie warunkowe na wierszu z k-tym duplikatem dane posortowane lub nie – sztuczki #28

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

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

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)

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)

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

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)

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube – Formatowanie warunkowe na wierszu z k-tym duplikatem dane posortowane lub nie – sztuczki #28

Wyszukiwanie k-tego duplikatu gdy kolumna jest posortowana – sztuczki #27

Jak znaleźć k-ty duplikat przy posortowanej liście?

Jeśli masz posortowaną listę i szukasz k-tego wystąpienia danego wyrazu (duplikatu) to jest to bardzo proste.


Wyszukiwanie k-tego duplikatu gdy kolumna jest posortowana – sztuczki #27

Zacznijmy od tego, że potrzebujesz mieć 2 warunki wyraz którego szukasz (w tym przykładzie będzie to imię) oraz numer wystąpienia, którego szukasz.

Dane wejściowe i warunki zaznaczone na czerwono

Dane wejściowe i warunki zaznaczone na czerwono

Teraz zaczynasz od wyszukania pozycji pierwszego wystąpienia danego wyrazu. Wykorzystujesz do tego funkcję PODAJ.POZYCJĘ z ustawieniem ostatniego argumentu na dopasowanie dokładne (0).

=PODAJ.POZYCJĘ(E2;A2:A13;0)

Ponieważ jednak potrzebujesz pozycji relatywnej konkretnego wystąpienia, potrzebujesz jeszcze dodać numer duplikatu, którego szukasz i odjąć jedynkę by uzyskać prawidłowy wynik (pozycję)

=PODAJ.POZYCJĘ(E2;A2;0)+D2-1

Zanaleziona pozycja wybranego duplikatu

Zanaleziona pozycja wybranego duplikatu

Kiedy masz już dokładną pozycję możesz wykorzystać funkcję INDEKS by znaleźć konkretną wartość odpowiadającą danemu duplikatowi, szczególnie tą znajdującą się w kolumnie obok:

=INDEKS(B2:B13;PODAJ.POZYCJĘ(E2;A2:A13;0)+D2-1)

Znaleziona wartość dla zadanych warunków

Znaleziona wartość dla zadanych warunków

P.S.

Wpis na podstawie Excel Magic Trick 1003

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube – Wyszukiwanie k-tego duplikatu gdy kolumna jest posortowana – sztuczki #27

2 serie czy seria i kategoria, jak Excel wstawia dane na wykres – porada #77

Kiedy Excel ustawi 2 serie na wykresie, a kiedy serię i kategorię?


2 serie czy seria i kategoria, jak Excel wstawia dane na wykres – porada #77

Jeśli Excelowi podasz dane do wykresu w postaci 2 linii z nazwami i wartościami to potraktuje obie linie wartości jako serie i nie zobaczy powiązania między zmiennymi w nich podanych.

2 serie na wykresie przy 2 liniach danych wypełnionych w pełni

2 serie na wykresie przy 2 liniach danych wypełnionych w pełni

Jeśli chcesz, żeby jedna ze zmiennych była traktowana jako kategoria potrzebujesz usunąć lewą górną komórkę. Wtedy pierwsza zmienna zostanie potraktowana jako kategoria (oś x), a druga jako seria (oś y).

Seria i kategoria na wykresie gdy lewa górna komórka jest pusta

Seria i kategoria na wykresie gdy lewa górna komórka jest pusta

Przyspiesza to pokazywanie zależności pomiędzy zmiennymi w porównaniu do sposobu pokazanym w poradzie 76:

P.S.

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube – 2 serie czy seria i kategoria, jak Excel wstawia dane na wykres – porada #77

Wykres zależności 2 wartości plus etykiety, trend i nazwy osi – porada #76

Jak można na wykresie przedstawić zależność 2 zmiennych od siebie?


Wykres zależności 2 wartości plus etykiety, trend i nazwy osi – porada #76

Żeby pokazać na wykresie zależność 2 wartości od siebie możesz postąpić tak:

Zaznaczasz 2 linijki z nazwami wartości i reprezentacjami liczbowymi. Jednak ze względu na nazwy wykres pokazuje 2 seria, a My chcemy, żeby jedna wartość była serią a druga kategorią, dlatego usuwamy jedną serię, a następnie
wchodzimy w kartę Projektowanie -> Zaznacz dane
Edytujemy oś poziomą (kategorie) -> zaznaczając wartości dla 2 zmiennej.

Okno wybierania źródła danych dla serii i kategorii

Okno wybierania źródła danych dla serii i kategorii

Teraz na osi y masz jedną zmienną na x drugą. Warto jeszcze dodać odpowiednie tytuły osi:
karta Układ -> Tytuły osi -> dodaj poziomą i pionową,
następnie zaznacz pierwszą,
wejdź w pasek formuły i naciśnij = a następnie kliknij na komórkę zawierającą nazwę tej zmiennej
dla drugiej zmiennej postąp analogicznie.

Nadawanie nazwy tytułowi osi jako odwołanie do komórki arkusza

Nadawanie nazwy tytułowi osi jako odwołanie do komórki arkusza

Możesz dodać jeszcze trend i etykiety danych – odpowiednio:
karta Układ -> Etykiety danych
i
karta Układ -> Linia trendu

Przydatne opcje na karcie Układ

Przydatne opcje na karcie Układ

Została jeszcze jedna sztuczka, ponieważ nasze dane zostały ułożone tak, że linia trendu jest spadkowa, a chcemy widzieć trend wzrostowy, więc

  • zaznacz oś x (kategorię),
  • naciśnij Ctrl + 1
  • zaznacz opcję Kategorie w kolejności odwrotnej

kolejność danych się odwróciła, mimo, że dane wejściowe zostały takie same.

Kategorie w kolejności odwrotnej i wynik końcowy

Kategorie w kolejności odwrotnej i wynik końcowy

P.S.

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube – Wykres zależności 2 wartości plus etykiety, trend i nazwy osi – porada #76