W dzisiejszym poście nauczymy się jak zamienić wszystkie x w tabeli na nagłówki kolumn. Jest to zadanie zaczerpnięte od Oza du Soleila https://www.youtube.com/watch?v=abQrUE10Xqo , on rozwiązał to za pomocą kilku zapytań. Nasze zadanie ma na celu podstawienie w miejsce x w tabeli odpowiednich nagłówków. Przykładowe dane i cel do którego dążymy został przedstawiony na rysunku nr 1.

rys. nr 1 — Dane i cel do którego dążymy

Na przykład chcemy aby zamiast znaku x dla Scoobiego zostało wstawione Jedzenie (czyli nagłówek kolumny, w której został wstawiony ten znak x). Pokażemy jak to zrobić w Power Query za pomocą tylko jednego zapytania. Załóżmy, że mamy przykładową tabelkę z danymi przedstawioną na rysunku nr 2.

rys. nr 2 — Przykładowe dane

Z karty Dane wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 3).

rys. nr 3 — Z tabeli

Otworzy nam się edytor zapytań tCechy z wczytaną tabela z Excela (rys. nr 4). W pierwszym etapie chcemy usunąć krok Zmieniono tym z Zastosowanych kroków, bo nie jest nam on potrzebny.

rys. nr 4 — Edytor zapytań

W naszych wczytanych danych jest jedna postać, która w każdej kolumnie ma Null, czyli nie ma w żadnej kolumnie znaku x. to był największy problem, żeby rozwiązać to zadanie za pomocą jednego zapytania. Klikamy na tytuł komuny Imię prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Anuluj przestawienie innych kolumn (rys. nr 5).

rys. nr 5 — Anuluj przestawienie innych kolumn

W efekcie otrzymamy tabelkę z tylko tymi osobami gdzie był znak x (rys. nr 6). Nie została uwzględniona tutaj postać Duch, u której nie było w ogóle tego znaku.

rys. nr 6 — tabela z danymi

Wynika z tego, że nie możemy użyć tego polecenia. Potrzebujemy sposobu na uwzględnienie osoby, która nie zaznaczyła żadnego znaku x (czyli np. nie wybrała żadnego kursu z naszej oferty). Usuwamy z Zastosowanych kroków ostatni etap, ponieważ zwracał niekompletne dane. Można to zrobić za pomocą Kolumny indeksu. Z karty Dodaj kolumnę wybieramy polecenie Kolumna indeksu (punkt nr 2 na rysunku na 7), a następnie od 0 lub 1 (nie ma to znaczenia, ponieważ jest to kolumna pomocnicza, dzięki której osoba Duch nie zniknie z danych, ponieważ będzie miała przypisaną jakąś wartość).

rys. nr 7 — Kolumna indeksu

Otrzymamy dane przedstawione na rysunku nr 8.

rys. nr 8 — Dane z nową kolumną

Na tym etapie po raz kolejny wybieramy polecenie z podręcznego menu Anuluj przestawienie innych kolumn (identycznie jak na rysunku nr 5 wyżej). Otrzymamy dane przedstawione na rysunku nr 9.

rys. nr 9 — Dane

Kolumna z Wartościami jest nam nie potrzebna, natomiast potrzebujemy drugiej kolumny z Atrybutami. Klikamy prawym przyciskiem myszy na nagłówek kolumny Atrybut i z podręcznego menu wybieramy polecenie Duplikuj kolumnę (rys. nr 10).

rys. nr 10 — Duplikuj kolumnę

W kolejnym etapie usuwamy kolumnę z wartościami. Klikamy na jej nagłówek prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Usuń (rys. nr 11).

rys. nr 11 — Usuń

Otrzymamy dane przedstawione na rysunku nr 12.

rys. nr 12

Mamy dwie kolumny z takimi samymi wartościami, część z tych danych ma posłużyć jako nagłówki kolumn a część jako wartości. Aby tak się stało musimy znaleźć na karcie Przekształć ikonkę Kolumny przestawnej (punkt nr 2 na rysunku nr 13). Ważne jest aby aktywna (zaznaczona) była kolumna Atrybut.

rys. nr 13 — Kolumna przestawna

Pojawi się okno Kolumny przestawnej (rys. nr 14). Polecenie to utworzy nowe kolumny przy użyciu danych z kolumny Atrybut. Jeśli jakaś nazwa się powtórzy to kolumna pozostanie pojedyncza, nie zostanie zduplikowana np. dla danej Jedzenie. W polu Kolumna wartości (punkt nr 1 na rysunku nr 14) musimy wybrać daną, jaka ma być przechowywana w tej nowej kolumnie. Tę wartość chcemy wziąć z kolumny Atrybut – kopia. Nasza kolumna z imionami natomiast zostanie na miejscu. Ważne jest tutaj użycie opcji zaawansowanych – w polu Agreguj funkcje wartości (punkt nr 2) wybieramy polecenie Nie agreguj, ponieważ nie chcemy tutaj wykonywać żadnych obliczeń. Tak ustawione parametry  kolumny przestawnej zatwierdzamy klikając przycisk OK.

rys. nr 14 — Okno kolumny przestawnej

Otrzymamy dane przedstawione na rysunku nr 15.

rys. nr 15 — Dane z kolumną przestawną

W kolejnym etapie musimy usunąć kolumnę indeks, która była kolumną pomocniczą. Klikamy prawym przyciskiem myszy na nagłówek kolumny Indeks i z podręcznego menu wybieramy polecenie Usuń. Otrzymamy wynik końcowy, o który nam chodziło (rys. nr 16).

Rys nr 16 — Ostateczny wynik

Następnie wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 17).

rys. nr 17 — Zamknij i załaduj do

Otworzy nam się okno Ładowanie do, gdzie wybieramy rodzaj wyświetlania danych jako Tabela i miejsce ich wstawienia jako istniejący arkusz i konkretną komórkę. Powyższe parametry zatwierdzamy klikając przycisk Załaduj (rys. nr 18).

rys. nr 18 — Okno Ładowania do

Otrzymamy dane w Excelu przedstawione na rysunku nr 19.

rys. nr 19 — Dane wczytane do Excela

Jedyną wadą takiego rozwiązania jest to, że Power Query sortuje wszystkie dane, łącznie z nazwami kolumn. Możemy sobie sprawdzić działanie zapytania dodając dodatkowe dane do tabeli z dany mi bazowymi (dodatkowe wiersze i kolumnę). Następnie klikamy prawym przyciskiem myszy na komórkę z tabeli z Power Query i z podręcznego menu wybieramy polecenie Odśwież (rys. nr 20).

rys. nr 20 — Odśwież dane

Otrzymamy prawidłowo przeliczone dane przedstawione na rysunku nr 21.

rys. nr 21 — Przeliczone dane

Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama