Max i Min wartość dla unikalnych wierszy — Power Query #17

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

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

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

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

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

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

rys. 6 – Wyciągnięte Min i Max dla kolumny Czas pogrupowane po kolumnach Data i Osoba

Adam Kopeć
Miłośnik Excela
Microsoft MVP

Ostatni i pierwszy wiersz po warunku — 2 formuły — porada #308

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ŻELI bę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:

=JEŻELI((tNotowania[Data]=G2)*(tNotowania[Firma]=H2);tNotowania2[L.p])

rys. 1 – Formuła sprawdzająca warunki

rys. 1 – Formuła sprawdzająca warunki

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:

{1;FAŁSZ;FAŁSZ;FAŁSZ;5;FAŁSZ;7;FAŁSZ;9;10;11;FAŁSZ;13;FAŁSZ;15;FAŁSZ;17;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}

Z takiej tablicy wystarczy odpowiednio wyciągnąć minimalną i maksymalną wartość, żeby uzyskać pierwszy i ostatni numer wiersza, który spełnia nasze warunki:

=MIN(JEŻELI((tNotowania[Data]=G2)*(tNotowania[Firma]=H2);tNotowania[L.p]))

rys. 2 – Wyciąganie numeru pierwszego wiersza za pomocą funkcji MIN

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:

=INDEKS(tNotowania[Cena];MIN(JEŻELI((tNotowania[Data]=G2)*(tNotowania[Firma]=H2);tNotowania2[L.p])))

rys. 3 – Obliczone pierwsze i ostatnie ceny po warunku

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:

=WYSZUKAJ(2;1/((tNotowania[Data]=G2)*(tNotowania[Firma]=H2));tNotowania[Cena])

rys. 4 – Wyszukiwanie ostatniego wiersza po warunku za pomocą funkcji WYSZUKAJ

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:

{1;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;1;#DZIEL/0!;1;#DZIEL/0!;1;1;1;#DZIEL/0!;1;#DZIEL/0!;1;#DZIEL/0!;1;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!}

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:

=PODAJ.POZYCJĘ(G2&H2;tNotowania[Data]&tNotowania[Firma];0)

rys. 5 – Odnalezione numery pierwszych wierszy za pomocą funkcji PODAJ.POZYCJĘ

rys. 5 – Odnalezione numery pierwszych wierszy za pomocą funkcji PODAJ.POZYCJĘ

Wystarczy jeszcze tylko dołożyć funkcję INDEKS po kolumnie z ceną i mamy wartości pierwszych cen.

=INDEKS(tNotowania[Cena];PODAJ.POZYCJĘ(G2&H2;tNotowania[Data]&tNotowania[Firma];0))

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”}

Adam Kopeć
Miłośnik Excela
Microsoft MVP

Wartość z ostatniego i pierwszego wiersza po warunkach — usuwanie duplikatów — porada #307

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

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

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

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

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

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

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

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ą.

Adam Kopeć
Miłośnik Excela
Microsoft MVP

Maksymalna i minimalna wartość po warunku za pomocą tabeli przestawnej — widzowie #120

Dostałem zapytanie jak wyciągnąć pierwsze i ostatnie wiersze (wartości z wybranej kolumny) po unikalnych wartościach w innych kolumnach.

rys. 1 – Dane i przykładowe wiersze, które chcemy wyciągnąć

rys. 1 – Dane i przykładowe wiersze, które chcemy wyciągnąć

Zadanie okazuje się prostsze niż z początku myślałem, ponieważ wartość do wyciągnięcia (w tym przykładzie czas) w ostatnim wierszu jest maksymalna dla unikalnych wartości w kolumnach z warunkami i minimalna w pierwszym. Dlatego rozwiązanie naszego problemu sprawdza się do znalezienia max i min po warunkach, a to jest dużo prostsze. Ponieważ chcemy znaleźć max i min po wszystkich unikalnych wartościach w kolumnach warunkowych najprostszym rozwiązaniem okazuje się skorzystanie z Tabeli Przestawnej.

W tym przykładzie potrzebujemy unikalnych wartości po dwóch kolumnach Data i Pracownik, dlatego oba te pola przeciągamy do obszaru etykiet wierszy

rys. 2 – Początek budowania unikalnych wierszy w tabeli przestawnej

rys. 2 – Początek budowania unikalnych wierszy w tabeli przestawnej

Tabela przestawna jeszcze nie prezentuje się tak jakbyśmy sobie tego życzyli – musimy pozmieniać domyślne ustawienia Excela. W pierwszej kolejności potrzebujemy zamienić kompaktowy układ tabeli przestawnej na układ tabelaryczny i przy okazji zaznaczyć opcję powtarzania elementów w tabeli przestawnej.

[rys. 3 – Style układów tabel przestawnych]

[rys. 3 – Style układów tabel przestawnych]

W dalszej kolejności nie potrzebujemy sum częściowych

[rys. 4 – Wyłączanie sum częściowych]

[rys. 4 – Wyłączanie sum częściowych]

I sum końcowych.

 [rys. 5 – Wyłączanie sum końcowych]

[rys. 5 – Wyłączanie sum końcowych]

Teraz możemy przeciągnąć 2 razy pole Czas do obszaru podsumowań wartości. Excel podsumowując czas zlicza ile razy pojawiły się wiersza dla konkretnych warunków.

[rys. 6 – Tabela przestawna z 2 podsumowaniami ilościowymi czasu]

[rys. 6 – Tabela przestawna z 2 podsumowaniami ilościowymi czasu]

My chcemy mieć Max i Min czas dlatego musimy zmienić sposób podsumowania w tabeli przestawnej. Najprościej kliknąć prawym przyciskiem myszy na kolumnę z podsumowaniem, a następnie z podręcznego menu rozwinąć opcję Podsumuj wartości według i wybrać odpowiednio Maksimum i Minimum.

Niestety w tabeli przestawnej domyślnie ustawiło mi się formatowanie ogólne, które źle pokazuje czas (jako liczbę).

[rys. 7 – Czas sformatowany ogólnie w tabeli przestawnej]

[rys. 7 – Czas sformatowany ogólnie w tabeli przestawnej]

Dlatego musimy zmienić formatowanie w kolumnach z podsumowaniem tabeli przestawnej – klikamy prawym przyciskiem myszy i wybieramy z podręcznego menu pozycję Format liczy i w oknie, które się otworzy wybieramy odpowiedni sposób formatowania czasu.

[rys. 8 – Format liczby w tabeli przestawnej]

[rys. 8 – Format liczby w tabeli przestawnej]

Znaleźliśmy maksymalny i minimalny czas (liczbę) po warunkach za pomocą tabeli przestawnej. Dla lepszej estetyki możemy jeszcze wyłączyć przyciski +/- z karty Analiza.

 [rys. 9 – Przyciski +/- na karcie Analiza]


[rys. 9 – Przyciski +/- na karcie Analiza]

Dodatkowym problem w dzisiejszym zadaniu jest wyznaczenie różnicy pomiędzy tymi wartościami. Niestety w zwykłej tabeli przestawnej (nie z dodatku Power Pivot) nie jesteśmy w stanie dodać pola obliczającego tą różnicę prawidłowo, dlatego obliczenia zrobimy poza tabelą przestawną. Formuła to proste odejmowanie, tylko Excel najprawdopodobniej będzie chciał domyślnie wstawiać funkcję WEŹDANETABELI, której nie chcemy, więc najprościej wpisać odwołania do komórek ręcznie:

=L3-K3

[rys. 10 – różnica pomiędzy wartością Max, a Min]

[rys. 10 – różnica pomiędzy wartością Max, a Min]

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

VBA #23 — Jak wykorzystać funkcje makr 4.0 w VBA

W poradzie 305 wspominałem o funkcjach makr 4.0. Pomogły mi wyciągnąć listę plików z katalogu, ale był z nimi jeden problem dla Polaków. Ciężko było znaleźć ich nazwę po Polsku.

Mi przyszedł do głowy jeden pomysł na poprawne tłumaczenie ich nazw za pomocą króciutkiego kodu VBA. Zadaniem tego kodu jest po prostu dodanie nazwy, która będzie odwoływać się do przykładowej funkcji makr 4.0.

Ponieważ VBA jest angielsko języczny, więc funkcję makr 4.0 wpisujemy w nim po angielsku (listę funkcji makr 4.0 z dokładnym opisem po angielsku możesz pobrać na stronie:
https://www.myonlinetraininghub.com/excel-4-macro-functions).

Po uruchomieniu tego makra do nazw Excela (patrz Menadżer nazw na karcie Formuły) dodadzą się dwie nazwy korzystające już z polskich nazw funkcji makr 4.0, gdyż mój Excel ‘mówi’ po polsku 😉 Po prostu między VBA, a Excelem funkcje zostały przetłumaczone. Dzięki temu będziemy już mogli korzystać z potrzebnych nam funkcji makr 4.0 już bez pomocy VBA.

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP