Nowy typ danych w Excelu jaki możemy strwożyć w Power Query to kolejna rewolucja w tym jak działa Excel. Przez lata nauczyliśmy się i weszło nam w krew, że w jednej komórce może być tylko jeden typ danych przechowujący jedną wartość. Komórka albo przechowuje tekst albo liczbę. Choć widzimy wartości, daty, czas, wartości pieniężne to zawsze jest to zwykła liczba, która ma po prostu ustawiony odpowiedni format, czyli sposób jej wyświetlania.
A co jeśli chcielibyśmy pokazać wiele różnych typów danych w jednej komórce? I jeszcze używać na nich formuły? Od niedawna jest to możliwe, a do tego działa to bardzo prosto. Wystarczy kilka kliknięć w Power Query. Od teraz twoje zestawienia, analizy i raporty mogą dostarczyć więcej informacji, jednocześnie poprawiając wizualizację danych.
Spis Treści
Jak utworzyć nowy typ danych w Excelu?
Zacznijmy od przykładowych danych. Mamy prosty zestaw informacji o kliencie oraz wartości jego zamówienia. Jak to zwykle bywa dla analityka kluczowa jest kolumna z wartością zakupu, ale do różnych analiz potrzeba szeregu innych danych jak ID klienta, adres, wiek itd. Choć dane o kliencie są bardzo ważne to niekoniecznie chcemy, aby zajmowały nam większość widoku. Problem ten rozwiąże utworzenie nowego typu danych.
W pierwszym kroku musimy załadować dane do Power Query. Zaznaczamy dowolną komórkę w tabeli z danymi, następnie przechodzimy do karty Dane i wybieramy przycisk Z tabeli/zakresu.
Gdy już dane załadują się do Power Query musimy zaznaczyć kolumny, z których informacje chcemy zgrupować w nowy typ danych. Będą to wszystkie kolumny od Nazwisko i imię do kolumny Wiek. Następnie na dowolnym nagłówku z zaznaczonych kolumn klikamy prawym przyciskiem myszy. Wybieramy opcję Utwórz typ danych.
Otworzy się nam okienko tworzenia nowego typu danych. Mamy tu do ustawienia dwie rzeczy. Pierwsza to nazwa typu danych, w tym przypadku dajmy tu nazwę „Klient”. Druga opcja to wybór kolumny, z której wartości będą się wyświetlać w podstawowym widoku komórek. Inaczej mówiąc określamy z której kolumny wartości będą widoczne na pierwszy rzut oka. Powinna to być kolumna, która najbardziej ułatwi weryfikację jakiego klienta dane przechowywane są w danej komórce. Dlatego wybieramy tu kolumnę Nazwisko i imię. Przechodząc do ustawień zaawansowanych dodatkowo widzimy pola prezentujące jakie jeszcze kolumny z danymi mamy w tabeli oraz które z nich chcemy zawrzeć w nowym typie danych. To okno może jest szczególnie przydatne, gdy chcemy łatwo manipulować kolejnością danych lub pracujemy na dziesiątkach kolumn, które różnie są rozmieszczone w tabeli. Wtedy lista kolumn będzie po prostu wygodniejsza.
Po tym działaniu dane z zaznaczonych kolumn zostały przeniesione do kolumny Klient. Przy nazwie kolumny widać symbol typu danych zdefiniowanego przez użytkownika. Klikając na dowolną komórkę w tej kolumnie, zobaczymy na dole edytora Power Query okienko z listą wartości jakie są przechowywane dla tego klienta. Jeśli wszystko jest ok to klikamy Zamknij i załaduj i ładujemy dane do zwyklej tabeli.
W arkuszu załadowane dane mają dodatkowy symbol przy komórkach przechowujących wiele informacji. W następnym akapicie poznamy możliwości tak przechowywanych danych.
Nowy typ danych w Excelu i jego możliwości
Nowy typ danych jaki utworzyliśmy za pomocą Power Query daje nam trzy zupełnie nowe możliwości pracy z danymi. Oto one:
1. Wyświetlanie zestawu informacji z wybranej komórki
Aby wyświetlić wszystkie przechowywane dane w komórce wystarczy kliknąć na symbol typu danych lub użyć kombinacji Ctrl+Shift+F5. Pojawi nam się okienko, które w przejrzysty sposób wyświetli nam informacje o kliencie. Dzięki temu zamiast przechowywać wszystkie dane w osobnych kolumnach i przeglądać wielką tabelę, możemy to zastąpić podejrzeniem wartości dla interesujących nas klientów.
2. Łatwe wyciąganie kolumn z danymi, które załadowane są w nowym typie danych
Niektórzy mogą pomyśleć, że jeżeli chcielibyśmy teraz filtrować dane po wieku klientów to musielibyśmy wchodzić do Power Query i edytować zapytanie. Na szczęście twórcy tego narzędzia przewidzieli takie sytuacje. Dali nam możliwość bardzo prostego dodawania kolumn ukrytych w nowym typie danych. Służy do tego nowy symbol, który wyświetla się w prawym górnym rogu naszej tabeli. Po jego kliknięciu wyświetla się lista kolumn, które są ujęte w kolumnach zawierających utworzony przez nas nowy typ danych.
Po wybraniu kolumny Wiek zostaje ona od razu dodana do tabeli danych. Co ważne nie wpływa to na dane przechowywane w kolumnie Klient.
3. Używanie ukrytych danych bezpośrednio w formułach
Dane które są zgrupowane w kolumnie Klient możemy bezpośrednio wykorzystywać w formułach. Nie musimy ich w żaden sposób dodawać do wyświetlanej tabeli. Możliwości zastosowania tych danych w formułach są przeogromne. Poniżej prezentacja różnych sposobów odwoływania się do ukrytych danych.
Wyciągnięcie danych wprost z tabeli z tego samego wiersza
W tym odwołaniu podajemy najpierw nazwę tabeli, następnie w nawiasach kwadratowych nazwę kolumny poprzedzoną znakiem @, który symbolizuje ten wiersz/rekord. Następnie wstawiamy kropkę i nazwę kolumny, z danymi które nas interesują, np. wiek.
Oczywiście twórcy tego rozwiązania uprościli nam pracę. Zamiast wpisywać to ręcznie, to wystarczy kliknąć na kolumnę Klient i od razu pojawi się lista wyboru kolumn, które są tam przechowywane.
Odwołanie się wprost do komórki
Jeśli ktoś nie lubi używać w formułach nazw tabel i kolumn, to na pewno spodoba mu się zwykłe odwołanie do komórki. Gdy odwołamy się do komórki B4 i damy kropkę to również możemy wybrać z listy dane które nas interesują.
Odwołanie do całej komórki
Jeśli chcemy w komórce dać po prostu możliwość wyświetlania zgrupowanych tam danych to możemy odwołać się po prostu do samej komórki. Dzięki temu w komórce F6 również możemy sobie wyświetlić wszystkie informacje o kliencie.
Odwołanie do całych kolumn
Równie często możemy wykonywać różnego rodzaju obliczenia na całej kolumnie. Jeśli chcemy poznać średni wiek klientów to wystarczy w formule ŚREDNIA zaznaczyć kolumnę Klient a po kropce wybrać Wiek. Oczywiście ten sam sposób działania jest dla zakresu komórek.
Jak już wspomniałem kolumna D wyświetlająca wiek klienta jest tu w ogóle niewykorzystywana. Służy tylko do sprawdzenia wyników.
Nowy typ danych w Excelu – przykład praktycznego zastosowania
Zgrupowanie danych klienta w nowym typie danych pozwala na zupełnie inne, lepsze podejście do tworzenia narzędzi dla odbiorców raportów i analiz. Przykładowo, użytkownik skoroszytu ma wprowadzić ID klienta, aby dowiedzieć się jaka jest wartość jego zakupów.
W standardowych rozwiązaniach zapewne dodalibyśmy tutaj kilka komórek, które prezentowałyby informacje o kliencie, którego sprawdzamy. Dzięki zastosowaniu nowego typu danych stworzonego w Power Query wystarczy, że wyszukamy komórkę, która te wartości przechowuje. Po kliknięciu symbolu w komórce użytkownik Excela zobaczy wszystkie informacje o kliencie. Proste i genialne w jednym.
Ograniczenia utworzonych nowych typów danych
Niestety występują dwa istotne ograniczenia tak utworzonych zbiorów danych. Pierwszy to brak możliwości podglądania zawartości danych oraz ich rozwijania w tabelach przestawnych. Drugi to brak działania formuł na tych danych w modelu danych w Power Pivot. Na pewno za jakiś czas te możliwości zostaną dodane, więc pozostaje nam cierpliwie czekać.
Podsumowanie
Jak widać Excel zmienia się na naszych oczach i to bardzo. Gdy pisałem o dynamicznych formułach tablicowych/ oraz X.WYSZUKAJ to wskazywałem, że w Excelu doszło do rewolucji. Został wdrożony nowy silnik obliczeniowy, który pozwala na pracę z tablicami oraz nowymi typami danych w sposób bardzo prosty i intuicyjny. Problem w tym, że twórcy Excela są chyba skrajnie skromni i nie chwalą się wszędzie swoimi nowymi wynalazkami. Otwierając Excela nie wyskakują nam dziesiątki powiadomień o nowych funkcjach i możliwościach tak jak w grach mobilnych na telefonie. W tym przypadku to my musimy być ciekawi i dociekliwi, a wtedy poznamy narzędzia i możliwości, o których inni może nigdy nie usłyszą.
