Dziś wyznaczymy, za pomocą Power Query, minimalną i maksymalną wartość dla unikalnych wierszy. W pytaniu widzów 120 robiliśmy to za pomocą tabeli przestawnej. Zadanie to jest nawet prostsze gdy korzystamy z Power Query, bo nie trzeba zmieniać tylu domyślnych ustawień co w przypadku tabel przestawnych.
W przykładowych danych (rys. 1) chcemy wyciągnąć minimalną i maksymalną wartość czasu, ale analogicznie postępujemy dla dowolnej liczby, bądź daty.
rys. 1 – Przykładowe dane
Nasze dane są tabelą Excela, więc możemy je łatwo zaczytać do Power Query (Excel 2016 – Karta Dane – polecenie Z tabeli).
Może się zdarzyć, że Power Query źle zinterpretuje dane, ale łatwo wskazać mu ich właściwy format – wystarczy wybrać odpowiednią pozycję menu po kliknięciu w ikonę typu danych w nagłówku kolumny (rys. 2).
rys. 2 – Zmiana typu danych na prawidłowy
Gdy wszystkie kolumny mają już prawidłowy typ danych możemy znaleźć nasz minimnalne i maksymalne wartości po warunkach, tylko przed kliknięciem odpowiedniego polecenia (Grupowanie według – karta Narzędzia główne – rys. 3) warto zaznaczyć kolumny, po których będziemy grupować (kolumny po których chcemy wyznaczać unikalne wartości). Są to kolumny data i osoba.
rys. 3 – polecenie Grupowanie według
Dzięki temu Power Query będzie od razu wiedział, że chcemy grupować dane po 2 kolumnach i odpowiednio wypełni nam okno grupowania (rys. 4).
rys. 4 – okno grupowania z wypełnionymi polami na podstawie zaznaczonych wcześniej kolumn
Teraz musimy dodać tylko kolejny poziom agregacji (przycisk Dodawanie agregacji), żeby znaleźć Min i Max po kolumnie Czas (rys. 5)
rys. 5 – Okno grupowania z uzupełnionymi poziomami agregacji/sposobami podsumowania
Dzięki tej operacji mamy interesujący nas wynik i możemy go zaczytać do Excela (rys. 6).
rys. 6 – Wyciągnięte Min i Max dla kolumny Czas pogrupowane po kolumnach Data i Osoba
Kontynuujemy temat wyciągania ostatnich i pierwszych wierszy po warunku z porady 307. Tym razem napiszemy formuły, które pozwolą nam wyciągnąć interesujące nas dane.
Zaczniemy od formuły, która jest dla mnie klasycznym podejściem w podobnych sytuacjach – w funkcji JEŻELIbędziemy sprawdzać dwa interesujące nas warunki – dla dnia i firmy, czyli porównujemy całe kolumny danych do konkretnych wartości. Ponieważ musimy sprawdzić 2 kolumny, dlatego wykonujemy na wynikach operacji porównań mnożenie (pamiętając o kolejności działań dokładamy nawiasy). Jeśli warunki są spełnione to chcemy mieć wartość z kolumny liczba porządkowa, jeśli nie to chcemy domyślną wartość FAŁSZ, więc nic nie wpisujemy:
W formułach korzystamy z tego, że zakres danych jest zamieniony na tabelę Excela (tNotowania) oraz, że mamy kolumnę z liczbą porządkową. Jeśli nie mielibyśmy kolumny z liczbą porządkową musielibyśmy ją stworzyć w formule – odwołanie tNotowania2[L.p] musielibyśmy zamienić na formułę:
WIERSZ(tNotowania[Data])-WIERSZ(B1)
Tak stworzona funkcja JEŻELI zwraca tablicę z numerami porządkowymi, gdzie warunki były spełnione i wartościami FAŁSZ tam, gdzie nie były one spełnione:
Z takiej tablicy wystarczy odpowiednio wyciągnąć minimalną i maksymalną wartość, żeby uzyskać pierwszy i ostatni numer wiersza, który spełnia nasze warunki:
rys. 2 – Wyciąganie numeru pierwszego wiersza za pomocą funkcji MIN
Musimy pamiętać, że nasza formuła jest formuła tablicową, więc żeby zwracała prawidłowe wyniki musimy zatwierdzać ją za pomocą kombinacji klawiszy Ctrl + Shift + Enter.
Jak wyciągnęliśmy numer wiersza, to wystarczy już tylko funkcja INDEKS, żeby pobrać wartość z kolumny Cena:
rys. 3 – Obliczone pierwsze i ostatnie ceny po warunku
Mamy już wynik, ale chce przedstawić jeszcze inny sposób na znalezienie ostatniego wiersza. Pierwszy raz poznałem tą sztuczkę od Billa Szysz(kowskiego). Wykorzystuje ona funkcję WYSZUKAJ i najważniejsze w niej jest to, że nie musimy formuły zatwierdzać jako formuły tablicowej.
Zanim zaczniemy pisać formułę, musimy sobie powiedzieć 2 ważne rzeczy na temat funkcji WYSZUKAJ – zawsze dokonuje dopasowania przybliżonego oraz nie zwraca uwagi na błędy w kolumnie w której szuka.
Dlatego będziemy szukać liczby 2 i kolumnę po której będziemy szukać stworzymy jako wynik podzielenia 1 przez wynik naszych porównań po kolumnach (pamiętając o kolejności operacji matematycznych). Wektorem (tablicą) wynikową będzie kolumna Cena:
rys. 4 – Wyszukiwanie ostatniego wiersza po warunku za pomocą funkcji WYSZUKAJ
Czemu nasza funkcja działa? Przypomnij sobie co robi funkcja WYSZUKAJ.PIONOWO, gdy szukamy na zasadzie przybliżonej wartości, która jest większa od największej liczby jaką mamy w posortowanej kolumnie – zwraca ostatnią wartość. Analogicznie działa funkcja WYSZUKAJ – szuka 2 na liście 1 i błędów dzielenia przez zero:
Tak właśnie wygląda nasz przeszukiwany wektor (kolumna). Jeśli oba warunki są spełnione to mamy dwie wartości PRAWDA. Dowolna operacja matematyczna (np.: dzielenie) zamienia je na 1, czyli 1/(1*1)=1. Ale jeśli warunek nie jest spełniony otrzymujemy wartość FAŁSZ, która przy operacjach matematycznych zamienia się na 0, czyli np.: 1/(1*0) = błąd dzielenia przez zero (#DZIEL/0!). Ponieważ funkcja WYSZUKAJ ignoruje błędy pozostają jej same 1, czyli szukając 2, przy przeszukiwaniu przybliżonym, będzie przeszukiwała po ‘posortowanej’ liście 1, czyli odnajdzie pozycję ostatniej 1, a tym samym zwróci korespondującą do niej wartość z kolumny Cena.
Przedstawimy jeszcze inny sposób na znalezienie pierwszego wiersza. Niestety funkcja WYSZUKAJ tu nie zadziała i będziemy musieli wykorzystać funkcję PODAJ.POZYCJĘ. Będziemy sprawdzać kilka warunków – muszą się zgadzać wartości w kilku kolumnach. Jednym ze sposobów na to jest scalanie szukanych wartości i scalanie kolumn. W ten sposób uzyskamy jedną scaloną wartość, której będziemy szukać w scalonej kolumnie – funkcja PODAJ.POZYCJĘ idealnie się do tego nadaje. Dokładamy jeszcze dopasowanie dokładne i udaje nam się odnaleźć pierwszy wiersz z szukaną wartością, bo dopasowanie dokładne zwraca pierwszą wartość/pierwszy wiersz jaki znajdzie od początku danych:
Pamiętaj są to formuły tablicowe i musisz je zatwierdzić za pomocą kombinacji klawiszy Ctrl + Shift + Enter.
P.S. Po podejrzeniu (klawiszem F9) przykładowa szukana scalona wartość w funkcji PODAJ.POZYCJĘ będzie wyglądać tak:
"42738Acme"
Zamiast daty zobaczyć liczbę, ale nie przejmuj się, gdyż Excel bez problemu sobie z tym radzi, poza tym scalone ze sobą kolumny Data i Firma też mają w sobie liczby, które są odpowiednikiem dat:
{"42738Acme";"42738Małe Kucyki";"42738Puchatek i Przyjaciele";"42738Małe Kucyki";"42738Acme";"42738Małe Kucyki";"42738Acme";"42738Puchatek i Przyjaciele";"42738Acme";"42738Acme";"42738Acme";"42738Małe Kucyki";"42738Acme";"42738Puchatek i Przyjaciele";"42738Acme";"42738Małe Kucyki";"42738Acme";"42739Acme";"42739Puchatek i Przyjaciele";"42739Małe Kucyki";"42739Puchatek i Przyjaciele";"42739Acme";"42739Puchatek i Przyjaciele";"42739Puchatek i Przyjaciele";"42739Małe Kucyki";"42739Puchatek i Przyjaciele";"42739Acme";"42739Acme";"42739Małe Kucyki";"42739Puchatek i Przyjaciele";"42739Acme";"42739Puchatek i Przyjaciele";"42739Acme";"42739Acme";"42739Puchatek i Przyjaciele";"42740Acme";"42740Puchatek i Przyjaciele";"42740Małe Kucyki";"42740Puchatek i Przyjaciele";"42740Puchatek i Przyjaciele";"42740Małe Kucyki";"42740Acme";"42740Małe Kucyki";"42740Acme";"42740Acme";"42740Acme";"42740Acme";"42740Małe Kucyki";"42740Acme";"42740Acme";"42740Małe Kucyki"}
W pytaniu widzów 120 rozwiązaliśmy problem jak dla unikalnych wartości znaleźć minimalne i maksymalne wartości. Te wartości występowały odpowiednio jako pierwsze i ostatnie i w tym wpisie chcemy się zająć wyciąganiem wartości na podstawie ich kolejności, a nie wielkości (czy są min czy max).
Dziś posłużymy się jedynie prostymi komendami Excela jak usuwanie duplikatów i sortowanie. Ewentualnie przyda nam się jeszcze umiejętność stworzenia w Excelu kolumny indeksu (liczby porządkowej), ale to też będzie proste zadanie.
Zacznijmy od danych (standardowo zapisałem je jako tabela Excela, ale nie jest to konieczne do dziś wykonywanych przez nas operacji). Chcemy wyciągnąć pierwszy i ostatni kurs notowań w danym dniu dla danej firmy (unikalne wartości po tych kolumnach). Jak możemy to zrobić?
Dane z zaznaczonymi przykładowymi pierwszymi i ostatnimi wierszami danych dla konkretnych firm danego dnia
Wyciągnięcie pierwszych wartości jest bardzo proste wystarczy usunąć duplikaty po interesujących nas kolumnach, ale zanim to zrobimy stwórzmy sobie dodatkową kolumnę z indeksem (liczbą porządkową), żebyśmy wiedzieli jak to zrobić, gdyby nasze dane nie miały takiej kolumny.
Wystarczy w komórce F2 (czyli w kolumnie tuż obok danych) wpiszemy 1, a w F3 wpiszemy 2. Następnie zaznaczamy te komórki i klikamy dwa razy w prawy dolny róg zaznaczenia.
Tworzenie kolumny z indeksem
Excel stworzy rosnący ciąg do końca danych (nie ważne, czy dane są w tabeli Excela, czy nie).
My już jedną kolumnę z liczbą porządkową mamy, więc nie potrzebujemy drugiej, dlatego cofniemy naszą operację.
Teraz zrobimy coś dla nas ważniejszego – skopiujemy dane, w nich zaznaczamy dowolną pojedynczą komórkę w tych skopiowanych danych. Kolejnym krokiem będzie kliknięcie w polecenie Usuń duplikaty na karcie Dane.
Polecenie usuwania duplikatów na karcie Dane
W nim musimy zaznaczyć te kolumny, po których ma identyfikować duplikaty. W naszym przykładzie są to kolumny Data i Firma (rys. 4).
Okno usuwania duplikatów
Zatwierdzamy nasz wybór przyciskiem OK i Excel usuwa duplikaty, czyli jeśli wartości w kolumnach Data i Firma powtórzyły się we wcześniejszym wierszu, to Excel usuwa ten wiersz. Zostaje tylko jego pierwsze unikalne wystąpienie, czyli dokładnie ten pierwszy wiersz, którego szukaliśmy (rys. 5).
Podsumowania usuwania duplikatów
Ponownie kopiujemy nasze dane i sortujemy je w odwrotnej kolejności (najprościej po kolumnie z liczbą porządkową) i znów usuwamy duplikaty. Excel znów zostawia tylko pierwsze unikalne wystąpienie wiersza, ale ponieważ zmieniliśmy kolejność sortowania, to są to ostatnie wiersze w danych źródłowych. Możemy porównać wyniki (rys. 6).
Dane po usuwaniu duplikatów od początku i od końca
Jeszcze nam odrobinę brakuje do ich pełnego połączenie, ponieważ wiersze nie są w takiej samej kolejności, ale to wystarczy posortować dane, po unikalnych kolumnach (Data i Firma) oba zestawy danych. Jeszcze usunięcie zbędnych kolumn (np.: kolumny Czas) i może przysunąć dane do siebie (usunąć pustą kolumnę je rozdzielającą) (rys. 7).
Połączone pierwsze i ostatnie wiersze
Na ostatnim rysunku mamy jeszcze za dużo kolumn, ale to po to, żeby pokazać, że odpowiednie wiersze do siebie pasują. Wystarczy usunąć resztę zbędnych kolumn i zostawić tylko, te których potrzebujemy.
W tym przykładzie kopiowałem całą tabelę, ale nie zawsze jest to potrzebne. Ważne jest, żeby pilnować kolejności wiersze, a ile kolumn kopiujemy jest drugorzędną sprawą.
