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:
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.
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.
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.
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łę:
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)
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.
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
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
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:
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.
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
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
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.
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
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
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
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
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.
