Liczenie wartości pod rząd (np. wzrostów i spadków cen akcji)

Z lekcji dowiesz się:

• Jak sprawdzić, ile razy dana wartość wystąpiła pod rząd?

• Co to jest „grupowanie danych”?

• W jakich sytuacjach wykorzystać grupowanie?

Zadanie grupowania danych wykonamy w Power Query na podstawie przykładowych danych przedstawionych na rys. 1.

(Rys. 1) Przykładowe dane kursu akcji

Dane zawierają zestawienie cen akcji, ich wzrosty i spadki. Zadanie polega na pogrupowaniu tych danych tak, aby połączyć występujące pod rząd te same sytuacje, czyli np. pogrupować dni kiedy miał miejsce wzrost cen akcji w jeden wiersz i zliczyć ilość tych zliczonych elementów w tym wierszu (rys. 2).

(Rys. 2) Grupa powtarzających się sytuacji, którą połączymy w jeden wiersz

Podsumowując - celem będzie podliczenie ilości elementów w poszczególnych grupach (grupy wzrostu cen akcji lub spadku), inaczej podliczenie ile razy dana wartość wystąpiła pod rząd. Możemy to w łatwy sposób wykonać za pomocą Power Query. Ustawiamy aktywną komórkę w obrębie tabeli z danymi, a następnie wybieramy polecenie Z tabeli/ zakresu z karty Dane (rys. 3).

(Rys. 3) Polecenie Z tabeli/ zakresu z karty Dane

Po kliknięciu otwiera się Edytor zapytań Power Query z zaczytaną tabelą danych z Excela przedstawioną na rys. 4.

(Rys. 4) Dane z tabeli zaczytane do Power Query

Power Query źle importuje datę, dlatego klikamy na ikonkę kalendarza z zegarkiem przy nazwie kolumny Data (zaznaczone na rys. 5 strzałką) i z podręcznego menu wybieramy typ danych Data.

(Rys. 5) Zmiana typu danych w kolumnie Data

Pojawi się okienko Zmiany typu danych, gdzie musimy potwierdzić czy na pewno chcemy tę zmianę wykonać za pomocą przycisku Zamień bieżącą (rys. 6).

(Rys. 6) Potwierdzenie zmiany typu danych w kolumnie Data

Otrzymujemy zmienione dane, które teraz należy pogrupować według kolumny Wzrost/spadek. Po podliczeniu tych powtarzających się stanów naszym zadaniem będzie odpowiedź na pytanie, ile maksymalnie było spadków i wzrostów pod rząd. W celu ułatwienia obliczeń zduplikujemy zapytanie. Rozwijamy listę zapytań w panelu bocznym, a następnie klikamy prawym przyciskiem myszy na nazwę zapytania tAkcje i z podręcznego menu wybieramy polecenie Duplikuj (rys. 7).

(Rys. 7) Polecenie Duplikuj (duplikowanie zapytania)

W pierwszej kolejności chcemy otrzymać pogrupowane dane (grupowanie wzrostów i spadków występujących pod rząd). Zaznaczamy kolumnę Wzrost/spadek i wybieramy polecenie Grupowanie według z karty Strona główna (rys. 8).

(Rys. 8) Polecenie Grupowanie według z karty Strona główna

Otwiera się okno Grupowania według, gdzie musimy ustawić parametry grupowania. W pierwszej kolejności wybieramy grupowanie zaawansowane, ponieważ przy grupowaniu podstawowym otrzymalibyśmy tylko ilość pogrupowanych danych. Jednak oprócz wartości (ilości spadków i wzrostów) warto otrzymać również daty w systemie od – do, czyli od czasu w którym dana sytuacja miała miejsce i jak długo się powtarzała. W tym celu musimy wcisnąć przycisk Dodawanie agregacji.

W pierwszej agregacji jest już automatycznie wpisana nazwa nowej kolumny jako Liczność oraz Operacja, czyli Zlicz wiersze. Drugą agregację (dodaną przez nas) musimy wypełnić samodzielnie. Wpisujemy nazwę nowej kolumny jako Wszystkie, natomiast w polu Operacja wybieramy z listy rozwijanej Wszystkie wiersze. Tak ustawione parametry grupowania zatwierdzamy przyciskiem OK (rys. 9).

(Rys. 9) Parametry Grupowania według

Otrzymamy pogrupowane dane przedstawione na rys. 10.

(Rys. 10) Pogrupowane dane

Otrzymaliśmy pogrupowane dane, ale nie do końca w sposób o jaki nam chodziło. Chcieliśmy otrzymać pogrupowane dane przy każdej zmianie spadku na wzrost i odwrotnie, a nie pogrupowane wszystkie dane z podziałem ogólnym na dwie grupy. W kolumnie Wszystkie są ukryte tabelki z danymi spadku i wzrostu co widać na rys. 11.

(Rys. 11) Tabela ze wszystkimi wystąpieniami wzrostu cen akcji ukryta pod Table

Naszym celem jest uzyskanie wielu grup z takimi samymi wartościami pod rząd, więc musimy zmodyfikować trochę te dane. Niestety nie możemy tego zrobić z interfejsu użytkownika, potrzebujemy do tego formuły. Jeśli nie widzimy paska formuły to można go łatwo włączyć na karcie Widok, za pomocą checkboxa przy poleceniu Pasek formuły (rys. 12).

(Rys. 12) Uruchomienie Paska formuły na karcie Widok

Od czasu, kiedy Power Query koloruje formułę, modyfikacja takiego zapisu jest dużo łatwiejsza. Do funkcji Table.Group musimy dołożyć dodatkowy argument GroupKind.Local, który grupuje lokalnie, czyli przy każdej zmianie danych. Domyślnym argumentem jest tutaj GroupKind.Global, czyli grupowanie po wszystkich elementach. Zapis formuły dla Grupowania według powinien wyglądać następująco:

=Table.Group(#"Zmieniono typ", {"Wzrost/Spadek"},{{"Liczność" each Table.RowCount(_), type numer}, {"Wszystkie", each _, type table [Data=date, Cena akcji=numer, #"Wzrost/Spadek"=text]}}, GroupKind.Local)

(Rys. 13) Zapis formuły Grupowania według z opcjonalnym parametrem grupowania lokalnego

Otrzymamy dane pogrupowane lokalnie, czyli przy każdej zmianie spadku na wzrost i odwrotnie przedstawione na rys. 14.

(Rys. 14) Dane pogrupowane lokalnie

Pod każdym Table są ukryte elementy z poszczególnych grup, czyli wszystkie daty i wartości cen dla np. wzrostu pod rząd. Teraz z każdej takiej tabeli należy wyciągnąć datę początkową i końcową, aby uzyskać okres, w którym występował wzrost lub spadek. W tym celu trzeba odpowiednio rozwinąć kolumnę Wszystkie, czyli zaznaczyć kolumnę i kliknąć ikonkę ze strzałkami przy jej nazwie. Rozwinie się okienko w którym wybieramy rodzaj operacji Agreguj, a następnie z listy wybieramy odpowiednie dane. Na liście nie ma interesującej nas opcji, ale jest parametr #Liczba elementów Data, który za pomocą trójkąta można rozwinąć i wybrać oczekiwane wartości (Minimum i Maksimum) jak na rys. 15.

(Rys. 15) Ustawienie elementów kolumny Data, jakie chcemy wyciągnąć z danych

Odznaczamy checkbox przy parametrze Użyj oryginalnej nazwy kolumny jako prefiksu. Tak ustawione parametry agregacji danych zatwierdzamy przyciskiem OK (rys. 16).

(Rys. 16) Parametry agregacji danych z kolumny Wszystkie

Otrzymamy dane po agregacji przedstawione na rys. 17.

(Rys. 17) Dane po agregacji kolumny Wszystkie

Otrzymaliśmy dane z datą minimalną i maksymalną dla poszczególnych grup wartości (spadków i wzrostów). Podsumowując - dane zostały pogrupowane lokalnie z dodatkową informacją w jakim okresie dana wartość się powtarzała.

W kolejnym etapie odpowiemy na pytanie ile razy maksymalnie pod rząd wystąpił wzrost lub spadek cen akcji. Zadanie to wykonamy na zduplikowanym zapytaniu tAkcje (2).

Rozwijamy panel boczny z listą zapytań i przechodzimy na zapytanie tAkcje (2) – klikamy na jego nazwę. Analogicznie jak w pierwszym zapytaniu musimy pogrupować dane po kolumnie Wzrost/Spadek, czyli zaznaczamy w/w kolumnę i wybieramy polecenie Grupowanie według z karty Strona główna (analogicznie jak na rys. 8).

Otwiera się okno Grupowania według, gdzie pozostajemy na domyślnym podstawowym sposobie grupowania. W polu Nazwa nowej kolumny zostawiamy domyślną nazwę Liczność, a w polu Operacja zostawiamy Zlicz wiersze. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. 18).

(Rys. 18) Domyślne parametry Grupowania według

Otrzymujemy dane pogrupowane globalnie, czyli podsumowane wszystkie wystąpienia wzrostu i spadku razem przedstawione na rys. 19.

(Rys. 19) Dane pogrupowane globalnie

Analogicznie jak w poprzednim przykładzie należy zmienić typ grupowania na lokalne, czyli zastosować opcjonalny parametr GroupKind.Local. Zapis formuły dla grupowania lokalnego powinien wyglądać następująco:

=Table.Group (#"Zmieniono typ", {"Wzrost/Spadek"}, {{"Liczność", each Table.RowCount (_), type numer}}, GroupKind.Local)

Po dopisaniu parametru formułę zatwierdzamy przyciskiem Enter i otrzymamy dane pogrupowane lokalnie przedstawione na rys. 20.

(Rys. 20) Dane pogrupowane lokalnie

Na tym etapie należy wyciągnąć wartość maksymalną z kolumny Liczność dla wzrostu i dla spadku cen akcji. Wykorzystamy do tego scalanie zapytań, ale najpierw w kroku Pogrupowano wiersze, zaznaczamy kolumnę Wzrost/Spadek, następnie rozwijamy polecenie Usuń wiersze (punkt 23 na rys. 21) z karty Strona główna (punkt 1) i wybieramy polecenie Usuń duplikaty (punkt 3).

(Rys. 21) Ścieżka dostępu do polecenie Usuń duplikaty

Otrzymujemy dane po usunięciu duplikatów przedstawione na rys. 22.

(Rys. 22) Dane po usunięciu duplikatów

W tym kroku nie będzie potrzebna kolumna Liczność, więc zaznaczamy ją i usuwamy za pomocą przycisku Delete. Otrzymamy dane przedstawione na rys. 23.

(Rys. 23) Dane po usunięciu kolumny Liczność

Te działania mają na celu znalezienie wartości, które ulegają zmianie, czyli w naszym przykładzie wzrost i spadek. Na tym etapie należy scalić te dane (z kroku Usunięto kolumny) z danymi z kroku Pogrupowano dane, aby móc wyciągnąć wartość maksymalną. Wybieramy polecenie Scal zapytania z karty Strona główna (rys. 24).

(Rys. 24) Polecenie Scal zapytania z karty Strona główna

Otwiera się okno Scalania, gdzie w drugim polu wybieramy zapytanie z jakim chcemy scalić dane – wybieramy to samo zapytanie, czyli tAkcje (2) bieżący. Następnie wybieramy tabele i kolumny, które chcemy scalić (jest tylko jedna kolumna). Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. 25).

(Rys. 25) Parametry Scalania

Otrzymamy scalone dane przedstawione na rys. 26, ale niewiele to dało ponieważ, Power Query połączył dane z ostatniego kroku (usunięto kolumny).

(Rys. 26) Scalone dane

Wykonaliśmy takie scalanie po to, aby otrzymać formułę scalania. Teraz możemy w formule zmienić drugie wystąpienie nazwy kroku Usunięto kolumny na krok Pogrupowano wiersze. Zaznaczamy krok Pogrupowano wiersze, następnie za pomocą przycisku F2 przechodzimy do jego edycji i za pomocą skrótu klawiszowego Ctrl+C kopiujemy jego nazwę. W kroku Scalone zapytania zastępujemy drugie wystąpienie kroku Usunięto kolumny na skopiowaną nazwę, czyli zaznaczamy ten krok i za pomocą skrótu klawiszowego Ctrl+V wklejamy wcześniej skopiowaną nazwę (miejsce wklejenia nazwy kroku Pogrupowano wiersze zaznaczono na rys. 27).

(Rys. 27) Miejsce zastąpienia nazwy kroku Usunięto kolumny

Zapis formuły powinien wyglądać następująco:

=Table.NestedJoin(#"Usunięto kolumny", {"Wzrost/Spadek", #"Pogrupowano wiersze", {"Wzrost/Spadek"}, "Usunięto kolumny", JoinKind.LeftOuther)

Po zatwierdzeniu zmian w kolumnie otrzymamy dane przedstawione na rys. 28, gdzie pod Table kryją się wszystkie wartości związane ze spadkiem lub ze wzrostem cen akcji z kroku Pogrupowano wiersze.

(Rys. 28) Scalone dane z dwóch kroków w zapytaniu tAkcje (2)

Wystarczy teraz analogicznie jak w poprzednim przykładzie odpowiednio rozwinąć kolumnę Usunięto kolumny. Klikamy ikonkę ze strzałkami obok nazwy kolumny a następnie wybieramy polecenie Agreguj. Rozwijamy parametr Liczność i wybieramy Maksimum – ponieważ celem jest maksymalna ilość powtórzeń danej wartości (jak na rys. 29).

(Rys. 29) Agregacja po Liczności – wartość maksymalna

Tak ustawione parametry agregacji zatwierdzamy przyciskiem OK. Otrzymamy dane przedstawione na rys. 30.

(Rys. 30) Dane po agregacji, czyli wartość maksymalna dla spadku i wzrostu

Podsumowując - otrzymaliśmy odpowiedź na pytanie, czyli maksymalnie: spadek wystąpił 5 razy, a wzrost 10 razy pod rząd.

W poprzednim zapytaniu otrzymaliśmy bardziej szczegółowe informacje, z datami kiedy powtarzające się wartości zaczynały się i kończyły, jakie były grupy tych wartości itp. Teraz wystarczy te dane załadować do Excela. Wybieramy polecenie Zamknij i załaduj do z karty Strona główna (rys. 31).

(Rys. 31) Polecenie Zamknij i załaduj do

Otworzy się w Excelu okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela. Ze względu na to, że są dwa zapytania, nie możemy wybrać istniejącego arkusza do wklejenia tabeli. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (rys. 32).

(Rys. 32) Parametry importowania danych

Excel wstawi te tabele w nowe arkusze (rys. 33). Możemy sobie zaznaczyć tabelę i wyciąć ją z nowego arkusza za pomocą skrótu klawiszowego Ctrl+X i wkleić do istniejącego arkusza w konkretną komórkę za pomocą skrótu klawiszowego Ctrl+V.

(Rys. 33) Pierwsza tabela w nowym arkuszu

Analogicznie postępujemy z drugą tabelą (rys. 34), zaznaczamy ją, wycinamy z nowego arkusza i wklejamy do istniejącego, tego gdzie znajdują się dane bazowe.

(Rys. 34) Druga tabela w nowym arkuszu

Otrzymamy obie tabele wklejone do Excela przedstawione na rys. 35.

(Rys. 35) Dane wklejone do Excela

Musimy pamiętać o tym, że Power Query przy importowaniu danych do Excela nie formatuje daty (ma ona postać liczbową). Formatowanie można zmienić w karcie Narzędzia główne, w grupie poleceń Liczba. Zaznaczamy kolumny, w których chcemy zmienić formatowanie i rozwijamy listę z typami formatowania, następnie wybieramy typ Data krótka (rys. 36).

(Rys. 36) Zmiana typu formatowania danych

Otrzymamy dane przedstawione na rys. 37. Arkusz ten zawiera przykładowe dane, tabelę ze szczegółowymi pogrupowanymi danymi oraz z maksymalną ilością powtarzających się wartości.

(Rys. 37) Końcowe dane

Polecamy także:

• Dopasowanie rozmyte (fuzzy) w Power Query

• Porównanie budżetu planowanego do faktycznego na wykresie