Analizując dane, czasem musimy stawić czoło przeciwnościom losu. Zdarzeniom, które spowalniają nas w drodze do wniosków końcowych. Zdarza się jednak przeciwnik, który nie powstał przypadkiem, a stworzyła go jakaś nieżyczliwa nam dusza. Bo jak inaczej patrzeć na dane, które ktoś niewłaściwie ułożył?

Zmiana układu danych w Power Query

Pracowanie z danymi w Excelu to nie tylko sposób na zarabianie pieniędzy. To pewien styl życia. Organizacji informacji. To stan umysłu, przejawiający się w porządkowaniu wszystkiego, co tylko się da, w zgrabnych tabelkach, oczywiście najlepiej w Excelu. Albo w bazach danych. A najlepiej w excelowych tabelach stylizowanych na bazy danych. Jeżeli, szanowna czytelniczko lub szanowny czytelniku, jesteś choć trochę jak ja, to wiesz, że informacje w Excelu muszą być sformatowane jako tabela. I ta tabela musi być odpowiednio zaprojektowana, mieć przemyślany układ kolumn i najlepiej jeszcze utworzoną od razu tabelę przestawną. Ot tak, na wszelki wypadek. Człowiek nigdy nie wie, czy akurat jakaś tabela przestawna mu się w życiu nie przyda.

Niestety, nie wszyscy ludzie są tacy jak my – choć oni sami pewnie powiedzieliby „na szczęście”. Z pewnością każde z nas spotkało na swojej drodze takiego osobnika, który tabelki robi w Wordzie, a jeśli już coś wpisze w Excela, to uważa, że samo dodanie obramowań tworzy z zakresu tabelę. No i jeszcze to rozmieszczanie danych. O ile całkiem łatwo wytłumaczyć komuś, że scalanie komórek, choć ładnie wygląda, nieznacznie utrudnia potem analizę, o tyle przekonanie takiej osoby do stosowania bazodanowego układu danych w tabeli to już wyższa szkoła jazdy. Dostajemy dane już przestawione, rozmieszczone bez ładu i składu, za to z ładnymi kolorami. Albo z dziwnymi wierszami lub kolumnami. Bo tak eksportuje dane do arkusza kalkulacyjnego nasz system księgowy czy magazynowy.

Jeszcze nie tak dawno radzenie sobie z tymi niedogodnościami wymagało ręcznego usuwania wierszy lub kolumn, przeklejania zakresów i było dość uciążliwe. Ewentualnie można było stworzyć jakieś makro, które porządkowało dane w arkuszu, ale tego typu narzędzia były z kolei obarczone ryzykiem, że coś pójdzie nie tak. Na szczęście dostaliśmy do ręki cudowne narzędzie, którym jest Power Query, a wraz z nim możliwość szybkiego radzenia sobie z takimi problemami. Wszystkie poniższe przykłady będę opisywał, opierając się na Excelu 365, ale w każdej wcześniejszej wersji, o ile ma zaimplementowany lub zainstalowany dodatek Power Query, wykonanie tych czynności nie powinno nastręczać problemów.

W poniższym artykule pokażę, jak rozprawić się z problematycznymi danymi w trzech możliwych przypadkach. Pierwszy nie wydaje się taki tragiczny, ale warto się go pozbyć. Drugi dość często bywa zmorą w plikach otrzymywanych od innych użytkowników. Trzeci zaś jest kompletnym wynaturzeniem i jego naprawianie jest zaszczytnym obowiązkiem każdej osoby analizującej dane.

Bardzo zły wilk

Zacznijmy od czegoś prostego. Dostajemy w Excelu zakres wypełniony danymi nie w sposób bazodanowy, czyli jeden rekord w jednym wierszu, ale w formie już zestawionej do tabeli (Rysunek 1). Jak widać, tego typu dane może ładnie wyglądają wydrukowane, ale do dalszej analizy w Excelu nadają się średnio. Kiedyś być może rwalibyśmy sobie włosy z głowy, zwłaszcza przy większym zakresie. Dziś nasza fryzura jest niezagrożona.

Aby sobie poradzić z tym problemem, zaimportujmy dane do Power Query. W tym celu zaznaczmy cały zakres, najszybciej przez aktywowanie należącej do niego komórki i wciśnięcie Ctrl + a. Teraz na karcie Dane, w grupie Pobieranie i przekształcanie danych, wybieramy polecenie Z tabeli/zakresu. Można je też odnaleźć w menu Pobierz dane, opcja Z innych źródeł. Chociaż polecenie mówi o tabeli lub zakresie, Power Query uprze się, że z żadnym zakresem pracować nie będzie i zaproponuje przekształcenie go na tabelę. Na co oczywiście ochoczo się zgodzimy, ponieważ tabele są super i bez nich życie było jakieś takie szare. Po zatwierdzeniu tworzenia tabeli, otworzy się okno Power Query z nowym zapytaniem bazującym na naszych danych.

Aby posprzątać ten bałagan, skorzystamy z polecenia Anuluj przestawienie kolumn na karcie Przekształć. W naszym przykładzie będziemy anulować przestawienie wszystkich kolumn z datami miesięcy. W tym celu możemy zaznaczyć je wszystkie z wciśniętym przyciskiem Ctrl i kliknąć wspomniane polecenie. Życie potrafi jednak zaskoczyć mnogością kolumn, wymagających odwrócenia przestawności, zatem zamiast zaznaczać je wszystkie, skorzystajmy z rozwiązania dla leniwych. Zaznaczmy tę jedną kolumną, która pozostanie bez zmian – czyli Region. Następnie z rozwijanego menu w opisanym już poleceniu wybierzmy Anuluj przestawienie innych kolumn. I gotowe. Teraz można analizować dane dalej w Power Query albo wyeksportować do Excela za pomocą polecenia Zamknij i załaduj.

Smok, być może wawelski

Innym przykładem tabeli jest taka, w której ktoś złośliwie, albo co gorsza – w dobrej wierze – scalił komórki (Rysunek 2). Bo tak ładniej wygląda. Kwestia gustu... Gdyby to ode mnie zależało, scalanie komórek zostałoby bezpowrotnie usunięte z Excela. Ale nie ma co marzyć o lepszym świecie, trzeba jakoś spróbować naprawić ten, w którym przyszło nam żyć. Ponownie więc zaznaczamy całość i wysyłamy do Power Query jako dane z zakresu lub tabeli. Oczywiście Excel uprze się, żeby przekształcić nasz zakres w tabelę, dzięki czemu możemy zaobserwować jedno ciekawe zjawisko – Excel automatycznie rozbija scalone komórki. Trudno chyba o lepszy dowód na to, że scalanie nie jest naturalne dla naszych danych.

Mając dane w Power Query widzimy, że komórki odzyskane po cofnięciu scalenia są traktowane jako puste, ze słowem null (Rysunek 3). Nie jest to zgodne z intencją twórcy danych. Ktoś, kto tworzył ten zakres chciał, aby do tych konkretnych rekordów była przypisana jakaś wartość. Zadbajmy o to, aby się pojawiła. W tym celu na karcie Przekształć, w grupie Dowolna kolumna, znajdujemy polecenie Wypełnij. Sprawi ono, że puste komórki zostaną wypełnione wartościami znajdującymi się w komórce przylegającej do pustego obszaru. Zwracam uwagę na fakt, że polecenie to pozwala na wypełnienie wartości jedynie pionowo w dół lub górę, a w naszym przykładzie mamy do czynienia także z pustymi komórkami w poziomie. Jak sobie z tym poradzić? Tu już trzeba przyjrzeć się danym i postępować ostrożnie.

W naszym przykładzie zadbajmy najpierw o te kolumny, gdzie bez problemu możemy skopiować dane w dół. Czyli pierwszą, dotyczącą regionu, i przedostatnią, z danymi maja. Zaznaczamy je i wybieramy polecenie Wypełnij w dół. Teraz, aby móc dokonać podobnej rzeczy w poziomie, musimy przekształcić kolumny w wiersze. Prosta transpozycja usunęłaby jednak nazwy kolumn. Aby tego uniknąć, zabezpieczmy je jako pierwszy rząd tabeli. Na karcie Narzędzia główne, w grupie przekształć, rozwijamy menu obok polecenia Użyj pierwszego wiersza jako nagłówków i wybieramy Użyj nagłówków jako pierwszego rzędu. Teraz możemy zamieniać wiersz i kolumny miejscami. Dlatego na karcie Przekształć, tym razem w grupie Tabela, używamy polecenia Transponuj. Zaznaczmy te kolumny, w których występuje null, czyli w naszym przykładzie dotyczące północy i południa. Ponownie wybieramy Wypełnij w dół. Na sam koniec ponownie transponujemy dane i używamy pierwszego wiersza jako nagłówków, przywracając tabeli pierwotny układ.

A co potem? Wiadomo. Cofamy przestawienie kolumn z danymi w sposób opisany w poprzednim przykładzie, a następnie analizujemy dane, jakby jutra miało nie być. Doprowadzenie takich danych do porządku jest nieco bardziej czasochłonne, ale pozwala nam na szybkie i bezpieczne uzyskanie bazodanowego widoku.

Godzilla, król potworów

Ostatni przykład to końcowy boss w walce ze zmianą układu danych. Abominacja, z którą obyśmy nie musieli się mierzyć – ale zapewne los nie będzie dla nas na tyle łaskawy. To wynaturzenie, polegające na tym, że wszystkie dane są w jednej kolumnie (Rysunek 4). Być może jakiś archaiczny system księgowy tak właśnie zapisuje dane w formacie txt, może jakiś plik został niewłaściwie wczytany do Excela, a może nasz dostawca danych nas autentycznie nie lubi. To w tej chwili nieistotne. Skupmy się, jak pozbyć się tego monstrum.

Najpierw zacznijmy od tego, że rozpoznamy układ naszych danych, aby określić, ile kolumn będzie miał docelowo nasz zakres. W naszym przykładzie będą to cztery kolumny: region, miasto, miesiąc i wartość. Uzbrojeni w tę wiedzę możemy wciągnąć tę przeraźliwie długą kolumnę do Power Query. Aby móc zestawić te dane, posłużymy się prostym trikiem, który przypisze każdą z komórek do określonego wiersza i kolumny. W tym celu z karty Dodaj kolumnę, grupa Ogólne, wybierzmy polecenie Kolumna indeksu – na rozwijanej liście klikamy Od 0. Użyjmy go dwukrotnie, dzięki czemu na prawo od naszych danych pojawią się dwie identyczne kolumny, z kolejnymi numerami. Klikając dwukrotnie na ich nagłówkach, zmieńmy ich nazwy: pierwszą z nich nazwijmy Wiersze, a drugą – Kolumny.

Zaznaczmy kolumnę Wiersze, a następnie na karcie Przekształć, w grupie Kolumna liczb, rozwińmy polecenie Standardowy i wybierzmy Podziel bez reszty. Jako wartość, przez którą będziemy dzielić, wpiszmy określoną zawczasu liczbę kolumn, czyli w naszym przypadku 4. Dla kolumny opisanej jako Kolumny z tego samego polecenia wybieramy Modulo. Zwróci nam to resztę z dzielenia. Oczywiście ponownie wybieramy dzielenie przez 4.

Od sukcesu dzieli nas jeden krok. Zaznaczmy kolumnę Kolumny, a następnie z karty Przekształć, z grupy Dowolna kolumna, wybieramy opcję Kolumna przestawna. W oknie opcji jako kolumnę wartości wskazujemy naszą kolumnę z danymi, a w rozwijanych opcjach zaawansowanych wybieramy brak agregacji. Gotowe. Wszystko, co możemy jeszcze zrobić, to usunąć pozostałą kolumnę Wiersze, ponieważ nie będzie już potrzebna. Możemy też zmienić nagłówki naszych kolumn.

Podsumowanie

Powyższe przykłady wydają się wyczerpywać wszystkie możliwości natknięcia się na dziwnie zestawione dane, które będziemy musieli egzorcyzmować za pomocą Power Query. Jednak czy jest tak w rzeczywistości? Czy gdzieś, w mrocznych zakamarkach złych importów danych z przestarzałych systemów nie kryją się przerażające Behemoty zakresów z danymi? Niewykluczone. Jednak mając oparcie w Power Query, uzbrojeni w możliwość przestawiania kolumn i cofania tego przestawienia, możemy stawić czoło złu, czającemu się w arkuszu, który dopiero odkryjemy.

Możesz zobaczyć ten artykuł, jak i wiele innych w naszym portalu Controlling 24. Wystarczy, że klikniesz tutaj.

Ulubione Drukuj

Zobacz również

Wiekowanie w KNIME – na przykładzie rozrachunków

Wiekowanie w KNIME – na przykładzie rozrachunków

Gdyby życie i biznes były idealne, nie musielibyśmy się martwić wiekowaniem. Niestety – ani życie, ani biznes nie chcą sięgać ideału.

Czytaj więcej

Alternatywa dla wykresu spaghetti w Excelu

Alternatywa dla wykresu spaghetti w Excelu

Tworząc wykres, niejednokrotnie popadamy w różne pułapki związane z właściwą formą prezentacji danych. Taka sytuacja może mieć miejsce, gdy na jednym wykresie zamierzamy przeanalizować historię wielu serii danych, które – co gorsza – mogą się charakteryzować podobnymi wartościami. Chcąc przedstawić zmienność danych w czasie, sięgamy po wykres liniowy, którego standardowa forma w tym przypadku okazuje się niewystarczająca.

Czytaj więcej

Przejdź do

Partnerzy

Reklama

Polityka cookies

Dalsze aktywne korzystanie z Serwisu (przeglądanie treści, zamknięcie komunikatu, kliknięcie w odnośniki na stronie) bez zmian ustawień prywatności, wyrażasz zgodę na przetwarzanie danych osobowych przez EXPLANATOR oraz partnerów w celu realizacji usług, zgodnie z Polityką prywatności. Możesz określić warunki przechowywania lub dostępu do plików cookies w Twojej przeglądarce.

Usługa Cel użycia Włączone
Pliki cookies niezbędne do funkcjonowania strony Nie możesz wyłączyć tych plików cookies, ponieważ są one niezbędne by strona działała prawidłowo. W ramach tych plików cookies zapisywane są również zdefiniowane przez Ciebie ustawienia cookies. TAK
Pliki cookies analityczne Pliki cookies umożliwiające zbieranie informacji o sposobie korzystania przez użytkownika ze strony internetowej w celu optymalizacji jej funkcjonowania, oraz dostosowania do oczekiwań użytkownika. Informacje zebrane przez te pliki nie identyfikują żadnego konkretnego użytkownika.
Pliki cookies marketingowe Pliki cookies umożliwiające wyświetlanie użytkownikowi treści marketingowych dostosowanych do jego preferencji, oraz kierowanie do niego powiadomień o ofertach marketingowych odpowiadających jego zainteresowaniom, obejmujących informacje dotyczące produktów i usług administratora strony i podmiotów trzecich. Jeśli zdecydujesz się usunąć lub wyłączyć te pliki cookie, reklamy nadal będą wyświetlane, ale mogą one nie być odpowiednie dla Ciebie.