Kilka lat temu wystarczyło w miarę dobrze znać Excela, aby móc analizować dane. Dziś, gdy danych jest coraz więcej i są rozproszone, wydaje się, że trzeba poszukać nowych narzędzi. Albo jeszcze lepiej poznać Excela.

Analizowanie danych nigdy wcześniej nie było tak łatwe i przyjemne – przynajmniej od strony technicznej. Jeśli chodzi o treść i rozproszenie danych, żyjemy w czasie chaosu i przeciążenia, ale mamy coraz lepsze narzędzia do ogarniania tego zamieszania. Excel nadal jest podstawą pracy dla wielu z nas, ale jeśli potrzebujemy czegoś więcej, mamy multum opcji. Piszemy procedury w VBA, aby zautomatyzować pewne czynności. Konstruujemy zapytania bezpośrednio do baz danych, aby sięgać po informacje u źródła. Analizujemy je za pomocą narzędzi BI lub takich aplikacji jak ­KNIME. Wizualizacja także lepiej wygląda w takich programach jak Tableau czy Qlik.

Wydawać by się mogło, że w tej sytuacji Excel będzie usuwać się w cień. Tymczasem nasza ulubiona aplikacja nieustannie się rozwija i dzięki nowym elementom może śmiało stawać w zawody z konkurencją. Jednym z elementów, który – zdaniem niektórych – odmienia oblicze Excela, jest dodatek Power Query – zaimplementowany z wersji 2016. Dzięki temu rozszerzeniu można znacząco zautomatyzować przetwarzanie danych, zwłaszcza ze źródeł zewnętrznych. Spotkałem się z teorią, że mając Power Query, można zapomnieć o VBA. Ja sam, mając ten dodatek, nie czuję potrzeby zaprzęgania KNIME do nawet dość skomplikowanych przekształceń danych.

Można też sięgać do danych w bazach MySQL, nawet jeśli nie potrafimy tworzyć zapytań w tym języku. I na tym chciałbym się dzisiaj skupić.

Power Query jest dodatkiem oficjalnie wypuszczonym przez Microsoft. W przypadku wersji Excela starszych niż 2016 należy go pobrać i zainstalować – wtedy na wstążce pojawia się osobna karta. W wersji 2016 Power Query znajdował się na karcie Dane w grupie Pobieranie i przekształcanie, natomiast w wersji 2019 i 365 grupa ta nazywa się Pobieranie i przekształcanie danych. Kiedy wybierzemy polecenie Pobierz dane, na rozwijanej liście zobaczymy sporą liczbę opcji podłączania danych. Lista ta jest oczywiście wydłużana w wersji 365 dzięki aktualizacjom. Aby pobrać dane ze źródła ­MySQL, wybieramy opcję Z bazy danych, a następnie Z bazy danych programu MySQL i... w tym momencie może pojawić się dość lakoniczny komunikat: „Aby można było użyć tego łącznika, należy zainstalować co najmniej jeden dodatkowy składnik”. Niestety, w tym miejscu Excel nie informuje, że chodzi o dodatek MySQL for Excel, który jest do pobrania ze strony https://www.mysql.com/why-mysql/windows/excel/. Pozwala on na przetwarzanie informacji z baz danych w Excelu, ale z naszego punktu widzenia – przede wszystkim instaluje procedury niezbędne do połączenia się z bazą danych.

W trakcie instalacji można utworzyć lokalną bazę danych, ale to, co najważniejsze, to ułatwiony dostęp do baz znajdujących się na serwerach. Po zainstalowaniu na karcie Dane pojawi się przycisk MySQL for Excel. Kliknięcie go otworzy boczny panel, pozwalający na dodanie nowego połączenia do bazy. Wystarczy kliknąć polecenie New Connection, a następnie w oknie dialogowym podać wszystkie niezbędne informacje. Jeśli chcemy połączyć się z konkretną bazą danych, powinniśmy ją wskazać w polu Default Schema (Rysunek 1).

Aby pobrać dane do Excela dzięki zainstalowanemu właśnie dodatkowi, należy dwukrotnie kliknąć na nowym panelu w naszą bazę i tak klikając, zejść na poziom tabeli lub widoku danych. W tym momencie mamy dwie opcje. Możemy kliknąć na polecenie Import MySQL Data, co pozwoli nam na zaimportowanie danej tabeli wraz ze wskazaniem, ile wierszy i od którego wiersza chcemy umieścić w arkuszu Excela (Rysunek 2). Można też kliknąć na tabeli prawym przyciskiem myszy i wybrać z menu Import Selected and Related Tables. W oknie, które się pojawi, zobaczymy nie tylko wybraną przez nas tabelę, ale także wszystkie tabele z nią powiązane (Rysunek 3). Co ważniejsze, widzimy też tabele pozostające w relacji do tych powiązanych tabel, zatem możemy prześledzić powiązania w naszej bazie i zdecydować, jak głęboko chcemy zejść.

Pobieranie danych za pomocą MySQL for Excel to zwykłe kopiowanie danych z tabel do arkuszy. Nie ma tutaj żadnej magii, która pozwalałaby na sprawniejsze przetwarzanie danych – wszystko trzeba robić tak jak dawniej. A przecież nie po to instalowaliśmy ten dodatek, prawda? Oczywiście, że nie. Zainstalowaliśmy go, aby w pełni wykorzystać Power Query. I to tam jest magia.

Na karcie Dane ponownie przechodzimy do grupy Pobieranie i przekształcanie danych i klikamy Pobierz dane, a następnie rozwijamy menu Z bazy danych. Po kliknięciu komendy Z bazy danych programu MySQL pojawi się okno dialogowe, w które musimy wpisać adres dostawcy oraz nazwę bazy danych (Rysunek 4). Reszta jest opcjonalna, ale pozwala na przykład na wprowadzenie w całości kodu zapytania do SQL. W następnym oknie zostaniemy poproszeni o podanie danych do logowania (Rysunek 5).

Pojawi się okno Nawigator, w którym możemy dokonać wyboru tabel, z których dane zostaną przekazane do dodatku ­Power ­Query. To okno jest już wspólne dla wszystkich źródeł danych, z jakich korzystalibyśmy za pomocą tego dodatku. Po wskazaniu dowolnej tabeli w prawej części okna wyświetli się jej podgląd. Można zaznaczyć pole wyboru pozwalające wskazać i zaimportować dane z kilku elementów. Po wybraniu właściwych danych mamy do wyboru jedną z trzech opcji. Z rozwijanego przycisku Załaduj można wybrać opcję Załaduj – wtedy nasze dane trafią do kolejnych arkuszy Excela, a my znowu nie skorzystamy z magii. Możemy wybrać Załaduj do... aby wskazać, gdzie dokładnie mają pojawić się dane, czy chcemy od razu utworzyć z nich tabelę lub wykres przestawny. Można też wybrać opcję utworzenia tylko połączenia, dzięki czemu dane zostaną powiązane z naszym plikiem Excel, ale nie pojawią się w żadnym arkuszu. Bez względu na wybraną opcję możemy dodać dane do modelu danych – dzięki temu będzie można skorzystać z nich w innym rewelacyjnym dodatku, jakim jest Power Pivot.

Można wreszcie wybrać Przekształć dane – bo to właśnie wtedy trafimy do dodatku Power Query i zacznie się magia.

Okno Power Query nie różni się interfejsem od Excela (Rysunek 6). U góry widać wstążkę, na której znajdują się polecenia pozwalające na manipulowanie danymi. Po lewej stronie w chowanym panelu znajdują się linki do wszystkich zapytań, jakie zastosowaliśmy. Z prawej strony widzimy panel z ustawieniami zapytania, pokazujący nazwę oraz, co najważniejsze, zastosowane kroki. Dzięki nim widzimy dokładnie, w jaki sposób edytowaliśmy dane, jak też poprzez kliknięcie na dowolnym z nich możemy podejrzeć dane na tym konkretnym kroku.

Pośrodku zaś widać podgląd danych na obecnym stopniu przekształcenia. Zwracam uwagę na dwie rzeczy widoczne na załączonym zrzucie ekranu (Rysunek 7). Ponad danymi aplikacja ostrzega mnie, że prezentowane dane mogą być aktualniejsze u źródła, zachęcając do odświeżenia widoku. Z kolei nagłówki kolumn przekazują dwie ważne informacje na temat zawartych w nich danych. Po pierwsze – pokazują rodzaj danych. Jak widać w załączonym przykładzie, są to liczby całkowite w pierwszej kolumnie, ciągi znaków w dwóch kolejnych oraz tabele w dwóch ostatnich. Oznacza to, że do tej tabeli dołączone są dwie inne za pomocą klucza. W takim wypadku z prawej strony nagłówka kolumny zamiast przycisku rozwijającego filtr jest przycisk pozwalający na wstawienie wybranych kolumn z powiązanej tabeli. Jeśli do niej również będzie dołączona inna tabela, zostanie także wstawiona z możliwością kolejnego rozwinięcia. Kliknięcie na pole w takiej kolumnie wyświetli u dołu ekranu listę możliwych pozycji z powiązanej tabeli.

Z tak prezentowanymi danymi można przystąpić do ich przekształcania. Pierwsza możliwość edycji pojawia się zresztą już na poziomie nagłówków kolumn. Kliknięcie w symbol rodzaju danych pozwala go zmienić. Jest to szczególnie wygodne, gdy z jakiegoś powodu kolumna z datą została zaimportowana jako ciąg znaków lub liczba.

Na wstążce u góry, na karcie Strona główna, znajdują się te polecenia, z których będziemy korzystać najczęściej. Oto przegląd najważniejszych z nich. Polecenie Wybieranie kolumn pozwala nam na określenie, które z nich będą podlegały dalszemu przekształcaniu. Z kolei przycisk Podziel kolumny pozwala na rozdzielenie jednej kolumny z ciągiem znaków na więcej, w zależności od wybranego sposobu dzielenia. A tych jest trochę, poza klasycznym ogranicznikiem lub liczbą znaków można też na przykład dzielić w momentach przechodzenia z cyfry na inny znak.

Grupowanie według (Rysunek 8) to jedno z tych narzędzi, które – jeśli zostanie użyte w rozsądny sposób – oszczędzi nam sporo czasu. Uruchomienie tego polecenia stworzy de facto nową tabelę z danymi zagregowanymi i wybranymi kolumnami. Po kliknięciu tego polecenia pojawia się okno, w którym wskazujemy kolumnę, której dane mają być elementami grupującymi. Z kolei poniżej wskazujemy kolumnę, która ma zawierać dane zbiorcze, zagregowane według wskazanej operacji. Jeśli chcemy zgrupować więcej kolumn lub według większej liczby kluczy, przełączmy widok na zaawansowany.

Jeśli z jakiegoś powodu (choć jest to prawdopodobne przy innych źródłach danych niż baza danych) nasze nagłówki nie zostały rozpoznane i są pierwszym wierszem, również możemy to zmienić za pomocą przycisku Użyj pierwszego wiersza jako nagłówków. Przycisk ten jest rozwijany i można również przenieść nazwy z nagłówków do pierwszego wiersza.

O sile Power Query stanowi między innymi kolejne polecenie – Połącz. Jest to przycisk rozwijany, który pozwala wskazać, jak połączyć dwie tabele, o ile nie ma między nimi relacji wykrywanej przez aplikację. Dostępne opcje są dwie. Dołącz zapytania – ustawia wyniki zapytania jeden pod drugim, w związku z czym oba muszą mieć ten sam wymiar szerokości. Z kolei polecenie Scal zapytania to odpowiednik znanego niektórym polecenia JOIN z języka zapytań SQL.

Scalanie takie jest proste. Po kliknięciu pojawia się tabela, w której wskazujemy, które źródła danych mają być podłączane ze sobą. W obu podglądach zaznaczamy kolumnę, która jest kluczem. Wskazujemy też rodzaj relacji z rozwijanego pola z danymi. Także tutaj możemy załadować ten wynik jako nową tabelę lub zmodyfikować dane źródłowe.

Już tylko te dwie metody, czyli grupowanie i łączenie, pozwalają tworzyć bardziej skomplikowane, ale pełniejsze zestawienia. Mamy z nimi do czynienia także na następnej karcie, czyli Przekształć. Znajdują się na niej wszystkie polecenia niezbędne do obróbki danych i tworzenia nawet rozbudowanych zestawień. Z kolei karta Dodaj kolumnę pozwala na wstawienie kolumny zawierającej daną wartość lub działanie.

A skoro już o działaniach mowa – Power Query ma własny język konstruowania zapytań – język M. Nie jest to SQL, ale jego nauka nie jest zbyt czasochłonna czy trudna. To nie czas i miejsce na zagłębianie się w jego składnię, ale jeśli ktoś chciałby podejrzeć, jak wygląda takie zapytanie w języku M, to na karcie Strona główna musi kliknąć Edytor zaawansowany.

Kiedy już zorganizujemy pobrane dane na nowo w Power Query i zechcemy je załadować od Excela, wystarczy kliknąć Zamknij i załaduj na karcie Strona główna. Z tego rozwijanego polecenia można także wybrać opcję Zamknij i załaduj do... dzięki której możliwe będzie wskazanie miejsca lub jedynie utworzenie połączenia. Kiedy załadujemy dane do Excela, wszystkie nasze połączenia i zapytania są widoczne w panelu Zapytania i połączenia – dostęp do niego poprzez przycisk o tej samej nazwie na karcie Dane.

Wydawać by się mogło, że to już wszystko, co możemy zrobić z danymi pozyskanymi z bazy MySQL. Jednak tu ponownie przychodzi nam z pomocą dodatek MySQL for Excel. Jeśli włączymy jego panel boczny, zobaczymy jeszcze dwa istotne polecenia. Append Excel Data to Table pozwala na dodanie danych do już istniejącej tabeli, o ile kolumny w naszym arkuszu pokrywają się z tymi w tabeli albo możliwe jest ich zmapowanie. Z kolei klikając polecenie Export Excel Data to New Table, możemy utworzyć w naszej bazie zupełnie nową tabelę zawierającą przekształcone dane.

Tym sposobem Excel przekształca się w narzędzie pozwalające na łączenie danych z różnych źródeł, ich rozbudowaną edycję, a następnie aktualizację tychże źródeł. To właśnie dzięki takim możliwościom dodatku Power Query analitycy dostali nowe, potężne narzędzie w ramach swojej ulubionej aplikacji. Warto mu się przyjrzeć dokładniej, ponieważ ma ono wszelkie cechy, aby stać się podstawą w naszej pracy.

Ulubione Drukuj

Zobacz również

Tylko on-line nr 23/2020

Wycena wartości godziwej aktywów w arkuszu Excel

CiRZ_3_20.jpg

Regulacje bilansowe, jak ustawa o rachunkowości, czy Międzynarodowe Standardy Rachunkowości/ /Sprawozdawczości Finansowej przewidują wycenę niektórych aktywów i pasywów w wartości godziwej. Jednak ani definicja, ani przepisy uprawniające do wyboru tego standardu nie wyjaśniają, jak ma wyglądać proces wyceny wartości godziwej.

Czytaj więcej
Tylko on-line nr 23/2020

Biała lista podatników VAT w Excelu

CiRZ_02_18.jpg

Na temat białej listy podatników powstało mnóstwo artykułów poświęconych przede wszystkim istocie jej działania, obowiązkom nałożonym na przedsiębiorców oraz sankcjom grożącym za niedopełnienie obowiązku weryfikacji. Na rynku pojawiło się sporo rozwiązań ułatwiających weryfikację rachunków bankowych z pominięciem wyszukiwarki Ministerstwa Finansów. W tym artykule przeanalizujemy w jaki sposób Excel umożliwia sprawdzenie kont kontrahentów, oczywiście odwołując się do danych białej listy podatników VAT.

Czytaj więcej

Tworzenie raportów w Excelu na przykładzie miesięcznego raportu sprzedaży

INFO_23_27.jpg

Program Excel jest bardzo często wykorzystywany do tworzenia różnego rodzaju raportów. Mogą być one mniej lub bardziej rozbudowane, mniej lub bardziej skomplikowane oraz do ich wykonania możemy stosować przeróżne narzędzia Excela. Wszystko zależy od tego, co nasz raport ma przedstawiać i w jakiej formie mamy do niego dane źródłowe. W niniejszym artykule pokażę na przykładzie miesięcznego raportu sprzedaży, jak tworzyć go za pomocą tabel przestawnych.

Czytaj więcej

Przejdź do

Partnerzy

Reklama