Nie znasz dobrze obsługi Excela? A może nie znasz jej wcale? Doskonale wiem, że w dzisiejszych czasach i realiach, znajomość tego programu jest nie tylko niezbędna, ale przede wszystkim korzystna dla Ciebie samego. Moje szkolenie z Excela online pokaże Ci, że arkusz kalkulacyjny nie jest wcale Twoim wrogiem, a sprzymierzeńcem w porządkowaniu i analizie danych - nie tylko liczbowych. Stworzyłem kurs Excel online na każdym poziomie, zarówno dla osób początkujących, średniozaawansowanych, jak i zaawansowanych. Znajdziesz u mnie także szkolenie z Excela online dedykowane specjalnie przedsiębiorcom. Każdy kurs zawiera starannie wyselekcjonowany zakres wiedzy i zagadnień, które odpowiadają na ściśle sprecyzowane potrzeby jego użytkowników.
Excel - kurs online. Dlaczego warto?
Zastanawiasz się, czy Excel i kurs online to dobre połączenie? Czy taka forma nauki pracy z arkuszem kalkulacyjnym może być efektywna? Pozwól, że rozwieję Twoje wątpliwości.
Od lat z powodzeniem zajmuję się dzieleniem swoją wiedzą zdalnie. Ta forma nauki, szczególnie w ostatnim czasie, mocno zyskała na popularności, a jej techniki znacząco się rozwinęły. Abyś mógł biegle posługiwać się programem Excel, każdy swój kurs zaprojektowałem z ogromną starannością i w oparciu o zasady metodyki nauczania na odległość.
Jednak szkolenie z Excela online, to przede wszystkim ogromna wygoda dla Ciebie, ponieważ nie musisz wychodzić z domu, możesz uczyć się wraz ze mną z dowolnego miejsca i w wybranym przez siebie czasie. Dodatkowo każdy mój kurs Excela online zawiera materiały dydaktyczne dostępne dla Ciebie już po zakończeniu szkolenia. Krótkie nagrania filmowe, prezentujące różnego rodzaju działania w arkuszu kalkulacyjnym, które w dowolnej chwili możesz zatrzymać, odtworzyć ponownie, by jeszcze raz przeanalizować i utrwalić wiedzę, to doskonały sposób, by opanować Excel. Kurs online to nie tylko wygoda, to także oszczędność Twojego i mojego czasu - zamiast dojeżdżać w umówione miejsce, możemy poświęcić więcej chwil na ćwiczenia i praktykę.
W
dzisiejszym poście omówimy rozwiązanie wyzwania nr 4 Kena Pulsa (Power Query
Challenge 4) https://www.excelguru.ca/blog/2018/11/29/power-query-challenge‑4/ . Zadaje on czasem takie pytania do
społeczności. Problem w tym zadaniu polega na tym, że mamy w danych wejściowych
dwie tabele, w których pierwszy wiersz jest wierszem nagłówkowym, ale nie do
końca ponieważ chcemy aby pierwsza kolumna nadal nazywała się Name (rys. nr 1).
rys. nr 1 — Przykładowe dane
Jeśli już
działaliście w Power Query to pewnie zdajecie sobie sprawę, że jest to problem.
Podwyższenie nagłówków, czyli standardowa opcja, gdzie zastępujemy nagłówki
pierwszym wierszem z danych, zniszczy nam nazwę pierwszej kolumny.
Mamy już
zaczytane tabele do Power Query, więc klikamy na zapytanie Admin, które jest
tylko połączeniem w panelu bocznym Zapytania i połączenia (rys. nr 2).
rys. nr 2 — Lista zapytań w panelu bocznym Zapytania i połączenia w Excelu
Otworzy nam się Edytor zapytań z wczytanymi 3 tabelami (Admin, Sales oraz MakeMyHeaders), przy czym trzecia tabela odwołuje się do tabeli Admin (rys. nr 3).
rys. nr 3 — Edytor zapytań Power Query z trzema tabelami
Nasze zadanie polega na zmianie nagłówków. Zmieniamy odwołanie do w trzeciej tabeli na tabelę Sales, a następnie klikamy na ikonę tabelki w lewym rogu i z podręcznego menu wybieramy polecenie Użyj pierwszego wiersza jako nagłówków (rys. nr 4).
rys. nr 4 — polecenie Użyj pierwszego wiersza jako nagłówków
Otrzymamy
tabelę przedstawioną na rys. nr 5. Możemy zauważyć, że cały pierwszy wiersz
został przeniesiony do nagłówków, a nam zależało aby nazwa pierwszej kolumny
(wcześniej Name) pozostała bez zmian.
rys. nr 5 — Dane, w których cały pierwszy wiersz został przeniesiony do nagłówków
Możemy
zmienić tą nazwę ręcznie, ale zostanie wtedy dodany kolejny krok w
Zastosowanych krokach. Zauważmy, że Power Query wstawi wtedy standardowe
polecenie, które możemy podejrzeć w pasku formuły (rys. nr 6).
rys. nr 6 — polecenie zmiany nazwy nagłówka w pasku formuły
W tym
działaniu Power Query odwołuje się konkretnie do nazw kolumn, w związku z tym,
kiedy zmienimy nasze źródło początkowe w kroku Source w pasku formuły z Sales
na Admin to nasze kroki odnoszące się do konkretnej kolumny przestaną działać i
otrzymamy błąd. Dzieję się tak dlatego, że w danych nie mamy kolumny Sales (rys.
nr 7).
rys. nr 7 — Błąd wynikający z braku podanej nazwy, po zmianie odwołania tabeli MakeMyHeaders
Wracamy do
odwołania tabeli MakeMyHeaders do tabeli Sales. W zastosowanych krokach
zmieniamy kolejność kroków, mianowicie chcemy aby krok Zmieniono nazwy kolumn
był przed krokiem Zmieniono typ. W tym celu przenosimy ręcznie krok Zmieniono
nazwy kolumn o jedno miejsce w górę (rys. nr 8).
rys. nr 8 — ręczna zmiana położenia kroku Zmieniono nazwy kolumn
W kroku Zmieniono
Typ otrzymamy błąd ponieważ Power Query chce zmienić typ danych w kolumnie
Sales, a takiej kolumny nie mamy (rys. nr 9).
rys. nr 9 — Błąd w kroku Zmieniono typ, wynikający z przesunięcia kroku Zmieniono nazwy kolumn
Możemy
zmienić w pasku formuły odwołanie do kolumny Sales na kolumnę Name i wtedy
formuła zadziała (rys. nr 10).
rys. nr 10 — Zmiana nazwy kolumny w pasku formuły
Naszym celem
jest rozwiązanie zadania tak, aby przy zmianie źródła danych (tabela Sales lub
Admin) formuły działały prawidłowo.
Rozwiązanie, które zostało zaproponowane przez kilka osób polegało na tym, żeby odwołać się do kroku źródła i wyszczególnić informację z pierwszego wiersza kolumny Name. W tym celu musimy kliknąć prawym przyciskiem myszki na komórkę w pierwszym wierszu kolumny Name a następnie z podręcznego menu wybrać polecenie Wyszczególnij (rys. nr 11).
rys. nr 11 — polecenie Wyszczególnij w podręcznym menu
Otworzy nam
się okno Kroku nawigacji, w którym zatwierdzamy operację przyciskiem Kontynuuj
(rys. nr 12).
rys. nr 12 — okno Kroku nawigacji
Gdybyście
chcieli uzyskać więcej informacji na temat wyszczegóławiania to jest ona
dostępna w jednym z filmów pod tytułem Wyszczegóławianie informacji w moim
kursie Mistrz Power Query w Excelu. Jest to jeden z filmów, które są
udostępnianie za darmo na stronie https://www.udemy.com/course/mistrz-power-query/learn/?couponCode=PQ.START2019 (rys. nr 13).
rys. nr 13 — część kursu dostępna za darmo w Mistrz Power Query w Excelu
Tutaj
pokażemy rozwiązanie na krokach, które już stworzyliśmy. Przechodzimy do kroku
Zmieniono nazwy kolumn, gdzie musimy wyszczegółowić tą informację. W pasku
formuły zamiast odwołania do konkretnej nazwy Sales, musimy odwołać się do
kroku źródło (source) i do pierwszego wiersza, któremu przypisana jest wartość
zero zapisana w nawiasach klamrowych (pierwszy wiersz ma indeks 0), następnie w
nawiasach kwadratowych odwołać się do nazwy kolumny (rys. nr 14). Zapis formuły
powinien wyglądać następująco:
=Table.RenameColumns(#"Nagłówki
o podwyższonym poziomie", {{Source{0}[Name], "Name"}})
rys. nr 14 — zapis formuły do odwołania się bezpośrednio do źródła (source)
Dzięki
powyższym zmianom w formule nasza tabela stanie się dynamiczna. Działania te
wykonywaliśmy na źródle Sales, więc teraz możemy łatwo sprawdzić poprawność
działania formuł poprzez zmianę źródła na tabelę Admin (rys. nr 15).
rys. nr 15 — dynamiczna tabela
Na rysunku
powyżej widać, że wszystkie formuły działają prawidłowo dla obu tabel (Sales i
Admin). Podsumowując, dzięki prostej sztuczce odwołania się do konkretnej
komórki stworzyliśmy dynamiczne rozwiązanie.
Możemy stworzyć jeszcze bardziej dynamiczne rozwiązanie dopisując funkcję w kodzie M. W tym celu wybieramy polecenie Edytor zaawansowany z karty Narzędzia główne (rys. nr 16).
rys. nr 16 — polecenie Edytor zaawansowany z karty Narzędzia główne
Otworzy nam
się Edytor zaawansowany, gdzie z naszego zapytania stworzymy funkcję. W tym
celu musimy dopisać pierwszą linijkę w edytorze zaawansowanym. Zapis powinien
wyglądać następująco:
(Tabela as table) as table =>
Wprowadzone w edytorze zaawansowanym zmiany zatwierdzamy przyciskiem Gotowe (rys. nr 17).
rys. nr 17 — zapis funkcji w Edytorze zaawansowanym
Stworzyliśmy
funkcję dodając odpowiednią linijkę na początku (rys. nr 18).
rys. nr 18 — stworzona funkcja
W polu
Wprowadź parametr możemy sobie wybrać do której tabeli chcemy się odwołać (rys.
nr 19).
rys. nr 19 — odwołanie do tabeli w polu Wprowadź parametry
Po wybraniu odpowiedniej tabeli (np. Admin) naszą decyzję zatwierdzamy przyciskiem Wywołaj. Otrzymamy wywołaną tabelę (rys. nr 20).
rys. nr 20 — wywołana tabela Admin
Analogicznie
możemy wywołać funkcję dla tabeli Sales i otrzymamy wtedy wynik przedstawiony
na rys. nr 21.
rys. nr 21 — analogicznie wywołana tabela Sales
Nie
dopatrzyliśmy jednej rzeczy, mianowicie jak podajemy funkcji argument to musimy
go potem użyć, ponieważ w formule mamy na stałe wpisany Admin a powinniśmy
zmienić na Tabela w Edytorze zaawansowanym (miejsce pokazane strzałką na rys.
nr 22).
rys. nr 22 — zmiany w edytorze zaawansowanym
Teraz po
wywołaniu funkcji dla tabeli Sales otrzymamy prawidłowy wynik (rys. nr 23).
rys. nr 23 — wywołana tabela Sales
Dzięki
powyższym operacjom otrzymaliśmy dynamiczny szablon rozwiązania naszego
problemu.
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.
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.
W dzisiejszym poście zajmiemy się grupowaniem danych z wielu
arkuszy. W przykładowych danych mamy imiona i nazwiska osób (klientów), z
którymi się spotkaliśmy bądź im coś sprzedaliśmy. Dane te są podzielone na
różne arkusze, np. pod względem miesięcy. Dane te chcemy połączyć, podliczyć,
np. wyznaczyć ile razy odbyliśmy spotkań z konkretnym klientem lub ile
sprzedaliśmy takich samych produktów. Część danych (jeden z arkuszy), na którym
będziemy pracować, został przedstawiony na rysunku nr 1.
rys. nr 1 — przykładowy arkusz z danymi
Naszym
zadaniem jest uzyskanie podsumowania przedstawionego na rysunku nr 2
rys. nr 2 — Nasz cel — pogrupowane dane
Do tych obliczeń chcemy wykorzystać Power Query. Zaczynamy od
pustego arkusza, gdzie wybieramy polecenie Nowe zapytanie (punkt nr 2 na
rysunku nr 3) z karty Dane, a następnie Z pliku (punkt nr 3) i Ze skoroszytu
(punkt nr 4).
rys. nr 3 — Nowe zapytanie ze skoroszytu
Otworzy nam się okno Importowanie pliku, gdzie musimy wybrać
lokalizację naszego pliku z danymi (rys. nr 4). Wybieramy plik i zatwierdzamy
nasz wybór przyciskiem Otwórz.
rys. nr 4 — Importowanie danych
Łączymy się z tym plikiem, z którego chcemy pobierać
informacje. Otworzy nam się okno Nawigator. W opcjach wyświetlania wybieramy
np. Styczeń – zaznaczony na rysunku zieloną strzałką (nie ma to większego
znaczenia, bo nasze nowe zapytanie będziemy edytować) nasz wybór zatwierdzamy
przyciskiem Edytuj (zaznaczone zielonym prostokątem na rysunku nr 5).
rys. nr 5 — Nawigator
Czekamy aż nasze dane ze stycznia zostaną wczytane do Power
Query. Otworzy nam się Edytor zapytań z wczytaną tabelą z danymi ze stycznia.
Dane te zostały przedstawione na rysunku nr 6. Nie interesują nas na tym etapie
te dane, a jedynie Zastosowane kroki (oznaczone na rysunku nr 6 zieloną
strzałką). Usuwamy kroki Zmieniono typ i Nawigacja, a zostawiamy krok Źródło.
rys. nr 6 — Edytor zapytań
Otrzymamy
dane przedstawione na rysunku nr 7.
rys. nr 7 — dane
Dzięki temu nie będziemy musieli sami wpisywać skomplikowanej
formuły, tylko skorzystamy w wbudowanych funkcjonalności Power Query, czyli
będziemy wprowadzać zmiany w istniejącej formule na pasku formuły. W naszych
danych mamy podział na Kind, czyli rodzaje danych w pliku. Sheet to arkusze,
Table to tabela, natomiast DefinedName to zdefiniowane zakresy danych.
Rozwijamy ikonkę ze strzałką w tytule kolumny Kind i w podręcznym menu
zaznaczamy tylko Sheet, bo interesują nas tylko arkusze (rys. nr 8). Podane
parametry filtru zatwierdzamy klikając przycisk OK.
rys. nr 8 — Filtry
W naszych danych mamy jeszcze niepotrzebny ukryty arkusz,
którego nie chcemy widzieć w naszych danych, więc klikamy na ikonkę strzałki w
tytule kolumny Hidden i odznaczamy checkbox True (rys. nr 9). Nasz filtr
zatwierdzamy klikając przycisk OK.
rys. nr 9 — Filtr
Z kolumny Item interesują nas dane tylko z konkretnych
arkuszy : styczeń, luty i marzec. Chcemy odfiltrować dane (arkusze) domyślne,
czyli takie które mają w sobie jakąś nazwę, w naszym przykładzie niepotrzebne
arkusze mają w nazwie litery PQ. Klikamy na ikonkę ze strzałką w tytule kolumny
Item, a następnie wybieramy opcje Filtr tekstu i Nie zawiera (rys. nr 10).
rys. nr 10 — Nie zawiera
Otworzy nam się okno Filtrowanie wierszy, gdzie wpisujemy w
pole Nie zawiera zestaw liter PQ (miejsce zaznaczone strzałka na rysunku nr
11). Musimy tutaj pamiętać, że Power Query jest Case sensitive, czyli zwraca
uwagę na wielkość liter. Nasz filtr zatwierdzamy klikając przycisk OK.
rys. nr 11 — Okno filtrowanie wierszy
Otrzymamy interesujące nas dane (arkusze) przedstawione na
rysunku nr 12.
rys. nr 12 — Interesujące nas arkusze
Dane te z poszczególnych arkuszy znajdują się w kolumnie Data
pod nazwą Table. Na tym etapie interesuje nas tylko kolumna Data, zawierająca
tabelki z danymi. Klikamy prawym przyciskiem myszy na tytuł tej kolumny i z
podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 13).
rys. nr 13 — Usuń inne kolumny
Otrzymamy jedną kolumnę Data, następnie klikamy na ikonkę ze
strzałkami w jej nazwie i z podręcznego menu wybieramy polecenie Rozwiń. Nasze
parametry zatwierdzamy przyciskiem OK (rys. nr 14).
rys. nr 14 — Rozwiń
Otrzymamy dane bez nagłówków, więc musimy wybrać polecenie
Użyj pierwszego wiersza jako nagłówków z karty Narzędzia główne (rys. nr 15).
rys. nr 15 — Użyj pierwszego wiersza jako nagłówków
W danych mamy 96 wierszy. Nasze dane będą się prezentować
następująco (rys. nr 16):
rys. nr 16
W kolejnym etapie wybieramy polecenie Grupowanie według z karty
Narzędzia główne (rys. nr 17).
rys. nr 17 — Grupowanie według
Otworzy nam się okno Grupowanie według. Power Query
automatycznie wstawi parametry grupowania, czyli Nazwisko i Imię (według czego
ma grupować), bo mamy w danych dwie kolumny. W polu Nazwa nowej Kolumny
automatycznie zostało wstawione Liczność (nie musimy zmieniać bo chodzi nam o
policzenie ilości takich samych danych). W polu operacja wybieramy polecenie
Zlicz wiersze. Pole Kolumna pozostaje w naszym przykładzie pusta (jest to pole
do innych operacji). Ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 18).
rys. nr 18 — Okno grupowanie według
Po
przefiltrowaniu danych zostało 75 wierszy. Z karty Narzędzia główne wybieramy
polecenie Sortuj od Z do A (rys. 19).
rys. nr 19 — Sortuj od Z do A
Nasze dane posortowane od największej do najmniejszej pokazaliśmy
na rysunku nr 20.
rys. nr 20 — Posortowane dane
Następnie z karty Narzędzia główne wybieramy polecenie
Zamknij i załaduj do (punkt nr 2 na rysunku nr 21).
rys. nr 21 — Zamknij i załaduj do
Otworzy nam się w Excelu okno Ładowanie do, gdzie wybieramy
sposób przedstawienia danych jako Tabela oraz miejsce załadowania danych –
Istniejący arkusz komórka $A$1 (rys. nr 22). Ustawione parametry zatwierdzamy
klikając przycisk Załaduj.
rys. nr 22 — Okno Ładowanie do
Otrzymamy dane w Excelu przedstawione na rysunku nr 23.
rys. nr 23 — Dane załadowane do Excela
Jeśli dodamy kolejny miesiąc do danych bazowych, to filtry w
Power Query zadziałają prawidłowo i wstawią dodatkowe dane do tabeli w Excelu.
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.
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.
