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 nauczymy się jak stworzyć błyskawiczną listę rozwijaną na
pomocą skrótu klawiszowego Alt + Strzałka w dół. Film ten powstał na podstawie
komentarza Pana Jarosława pod filmem z poradą nr 322 (https://www.youtube.com/watch?v=9hpcn-KuwnI).
Zadanie to
wykonamy na podstawie przykładowych danych z rysunku nr 1.
Rys. nr 1 – przykładowe dane
Kiedy już stworzymy sobie listę rozwijaną za pomocą polecenia Sprawdzanie poprawności danych z karty Dane (rys. nr 2).
Rys. nr 2 – polecenie Sprawdzanie poprawności danych
Otrzymamy listę rozwijaną przedstawioną na rys. nr 3.
Rys. nr 3 – stworzona lista rozwijana
Powyższą listę rozwijaną możemy rozwijać bez używania myszki, korzystając ze skrótu klawiszowego Alt+strzałka w dół (rys. nr 4).
Rys. nr 4 – rozwijanie listy za pomocą skrótu klawiszowego Alt+Strzałka w dół
Warto zapamiętać, że ten skrót klawiszowy działa również na zwykłych danych wpisanych w dowolną kolumnę. Mianowicie jeśli mamy np. listę produktów i ustawimy aktywną komórkę pod naszymi danymi (komórka A11), następnie skorzystamy ze skrótu klawiszowego Alt+strzałka w dół, otrzymamy automatyczną listę danych (rys. nr 5).
Rys. nr 5 – automatyczna lista danych
Istotne jest, że ta lista jest unikatowa. Wybierzemy z powyższej listy np. produkt Kokosy, następnie przejdziemy na komórkę poniżej i ponownie skorzystamy ze skrótu klawiszowego Alt+Strzałka w dół, to otrzymamy unikatową listę rozwijaną, czyli żadne elementy nie będą się w niej powtarzać (rys. nr 6).
Rys. nr 6 – automatyczna unikatowa lista rozwijana
Możemy
zauważyć, że produkt Kokosy występują na naszej liście dwukrotnie, natomiast na
liście rozwijanej są tylko raz. Jest to niewątpliwe duże udogodnienie w pracy w
Excelu.
Kolejnym
plusem tego sposobu tworzenia listy rozwijanej jest sytuacja, kiedy wpiszemy w
komórkę pierwszą literę produktu i skorzystamy ze skrótu klawiszowego, to
pojawi nam się lista rozwijana, gdzie aktywny element ustawi się na pierwszym
elemencie rozpoczynającym się od danej, wpisanej przez nas litery (rys. nr 7).
Rys. nr 7 – automatyczne przejście na liście do produktów rozpoczynających się wpisaną literą
Można wpisać
więcej znaków, wtedy na liście podświetlony zostanie element odpowiadający tym
znakom.
Nie jest to
lista rozwijana z pełnym wyszukiwaniem, ale jest to szybkie rozwiązanie, które
w wielu sytuacjach ułatwi i usprawni nam pracę. Lista uzyskana ze sprawdzania
poprawności danych nie daje nam możliwości wpisania znaków, od których ma się
zaczynać nasz szukany element (rys. nr 8).
Rys. nr 8 – możliwości listy rozwijanej ze sprawdzania poprawności danych
Jeśli interesuje Cię lista rozwijana z pełną opcją wyszukiwania to polecam dodatek Jona Acampora. W poradzie nr 379 pokazywałem jak zainstalować Add-inn (https://www.youtube.com/watch?v=ChAG8UtGkPE), natomiast w poradzie nr 380 jak wyciągnąć z niej kod (https://www.youtube.com/watch?v=ChAG8UtGkPE). Wyciągnięcie kodu jest potrzebne, aby ten dodatek przenosił się wraz z plikiem przy jego kopiowaniu, a nie był podpięty do jednego komputera.
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ę błędem, który pojawia się przy pobieraniu danych, kiedy wcześniej nałoży się filtr. Załóżmy, że mamy w przykładowych danych zakres, który nie jest tabelą. Zaznaczamy ten zakres i będziemy chcieli go nazwać — po prostu NazwanyZakres (w miejscu oznaczonym strzałką na rys. nr 1).
rys. nr 1 — nazywanie zaznaczonego zakresu
Nazwanie
zakresu pozwala nam wrócić do niego z każdego miejsca w arkuszu (z każdej
komórki) rozwijając okno, w którym nazywaliśmy ten zakres (rys. nr 2).
rys. nr 2 — powrót do nazwanego zakresu z każdego miejsca w arkuszu
Kiedy mamy
nazwany zakres możemy za pomocą polecenia Z tabeli/zakresu z karty Dane
pobrać go do Power Query (rys. nr 3).
rys. nr 3 — pobieranie danych do Power Query za pomocą polecenia Z tabeli/zakresu
W takim
przypadku Power Query nie ma żadnych problemów, pobiera dane prawidłowo.
Otworzy nam się Edytor zapytań z wczytanym zaznaczonym zakresem (rys. nr 4).
rys. nr 4 — edytor zapytań z wczytanym zakresem z Excela
Problem
pojawia się jeśli na ten nazwany zakres w Excelu nałożymy filtr, a następnie za
pomocą polecenia Z tabeli/zakresu z karty Dane wczytamy ten zakres do Power
Query. Otworzy nam się edytor zapytań Power Query, ale z błędem (rys. nr 5).
rys. nr 5 — edytor zapytań z wczytanym zaznaczonym zakresem z błędem
Błąd wynika
z tego, że Power Query nie odczytuje zakresu danych tylko próbuje odczytać
filtr, który jest nałożony na ten zakres (rys. nr 6).
rys. nr 6 — próba odczytania danych nie z zaznaczonego zakresu a z nałożonego filtru
Możemy
przyjrzeć się dokładniej funkcji Excel.CurrentWorkbook, gdzie widzimy, że przy
nazwie filtru jest błąd, ponieważ Power Query próbuje pobrać tabelę, której
nazwa nie istnieje (rys. nr 7).
rys. nr 7 — Próba pobrania danych z tabeli, której nazwa nie istnieje
Jako tester
lubię sobie czasem poeksperymentować, poszukać sposobu na obejście tego błędu.
W innym pliku rozwijamy polecenie Pobierz dane (punkt 2 na rys. nr 8) z
karty Dane (punkt 1), następnie rozwijamy polecenie Z pliku
(punkt 3) i wybieramy Ze skoroszytu (punkt 4).
rys. nr 8 — ścieżka dostępu do polecenia Ze skoroszytu
Otworzy nam
się okno Importowanie danych, gdzie wybieramy nasz plik klikając na niego dwa
razy (rys. nr 9).
rys. nr 9 — okno Importowania danych
Otworzy nam
się okno Nawigator, gdzie możemy podejrzeć zawartość skoroszytu, czyli jego
poszczególne elementy (rys. nr 10).
rys. nr 10 — okno Nawigator, gdzie możemy podejrzeć zawartość skoroszytu
Jeśli zaznaczymy sobie cały plik Excela i wciśniemy przycisk Edytuj, to zobaczymy wszystkie elementy i zakresy z tego pliku. Otworzy nam się edytor zapytań Power Query z listą tych wszystkich elementów. Tutaj Power Query użyje innej funkcji Excel.Workbook (rys. nr 11).
rys. nr 11 — Edytor zapytań z listą wszystkich elementów znajdujących się w skoroszycie
W naszych
danych nie interesują nas arkusze, czyli odfiltrujemy kolumnę Kind. W tym celu
klikamy na trójkąt przy nazwie kolumny Kind i odznaczamy checkbox przy nazwie
Sheet (czyli arkusz). Filtr zatwierdzamy przyciskiem OK (rys. nr 12).
rys. nr 12 — nakładanie filtru (odfiltrowanie arkuszy)
Otrzymamy
tabelę tylko ze zdefiniowanymi nazwami przedstawioną na rys. nr 13.
rys. nr 13 — tabela ze zdefiniowanymi nazwami
Interesuje
nas jeszcze kolumna Hidden, która pokazuje które elementy są ukryte. Filtr
nałożony na dane jest ukryty, natomiast nazwany zakres jest widoczny. Przy
pobieraniu danych Power Query sięga niestety po tą tabelę, która jest ukryta
(rys. nr 14).
rys. nr 14 — elementy ukryte — Hidden
W takim
rozwiązaniu Power Query nie ma problemu i poprawnie odczytuje dane.
Jak możemy
obejść ten błąd przy standardowym pobieraniu danych do Power Query?
Wyłączamy
filtr w naszym nazwanym zakresie. Jest on niewidoczny ale dalej się tam
znajduje. Stworzyliśmy tabelę pomocniczą z zakresem do filtrowania i właśnie na
nią nakładamy ten filtr za pomocą skrótu klawiszowego Ctrl+Shift+L (rys. nr 15).
rys. nr 15 — nakładanie filtru za pomocą skrótu klawiszowego Ctrl+Shift+L
Teraz jeśli
pobierzemy dane z tego zakresu za pomocą polecenia Z tabeli/zakresu z karty
Dane, otworzy nam się edytor zapytań Power Query z wczytanym zakresem (rys. nr 16).
rys. nr 16 — Edytor zapytań z wczytanym zakresem
W kroku
Źródło w Zastosowanych krokach możemy podejrzeć sobie funkcję użytą przez Power
Query. Możemy zauważyć, że dalej pojawia nam się błąd mimo, że dane zostały
wczytane (rys. nr 17).
rys. nr 17 — funkcja Excel.CorrentWorkbook
Sposobem na
zlikwidowanie błędu jest zmiana właściwości zakresu w VBA (rys. nr 18).
rys. nr 18 — zmiana właściwości zakresu w VBA
Wynika z
tego, że funkcja Power Query źle odwołuje się do zakresu i stąd wynika problem
(zły zapis zakresu – pojedynczy cudzysłów).
Podsumowując
celem tego filmu było pokazanie, że Power Query nie zawsze chce zwracać dane,
których potrzebujesz. Przede wszystkim w sytuacji kiedy nakładasz filtr na
nazwany zakres.
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 nauczymy się wyznaczać ilość dni w
miesiącu pomiędzy datami w Power Query. Odcinek ten powstał jako odpowiedź na
komentarz Billa Szyszkowskiego do postu z pytaniami od widzów nr 121,
poruszającego ten sam problem. Wyznaczymy ilość dni pomiędzy datami w danym
miesiącu ale tym razem za pomocą Power Query. Musimy pamiętać, że kiedy w
Excelu mamy daty to tak naprawdę mamy do czynienia z liczbami, bo daty w Excelu
to liczby. Bill Szysz zwrócił uwagę, że czasami nie uwzględniamy tego pierwszego
dnia, np. między datami 2018-01-02 a 2018-01-04 są 2 dni różnicy, ale jeśli
policzymy to dokładnie to mamy 2018-01-02, 2018-01-03 i 2018-01-04, a więc 3
dni. W zależności czy liczymy ten pierwszy dzień czy nie to otrzymujemy różne
wyniki. Bill Szysz zaproponował rozwiązanie tego problemu w Power Query.
Zagadnienie to omówimy na przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Standardowo mamy dane umieszczone w tabeli i w Excelu od 2016
wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane, aby zaczytać
dane do Power Query.
rys. nr 2 — Polecenie Z tabeli
Otworzy nam się okno Edytora zapytań dla danych zaczytanych z
tabeli tDaty. Musimy pamiętać, że Power Query chce zapisać dane jako daty, a my
chcemy skorzystać z liczb. Klikamy ikonkę (oznaczoną zieloną strzałką na
rysunku nr 3) przy nazwie kolumny Od i z podręcznego menu wybieramy polecenie
Liczba całkowita.
rys. nr 3 — Zmiana formatu danych
Pojawi się nam komunikat o zmianie typu kolumny, gdzie musimy
kliknąć przycisk Zamień bieżącą (rys. nr 4).
rys. nr 4 — Komunikat o zmianie typu kolumny
Tę samą czynność powtarzamy dla kolumny obok nazwanej Do. Klikamy
ikonkę przy jej nazwie i z podręcznego menu wybieramy polecenie Liczba
całkowita. Pojawi nam się komunikat o zmianie typu kolumny, gdzie klikamy
przycisk Zamień bieżącą. Otrzymamy dane przedstawione na rysunku nr 5.
rys. nr 5 — Dane po zmianie z daty na format liczbowy
W kolejnym kroku wybieramy polecenie Kolumna niestandardowa
(punkt nr 2 na rysunku nr 6) z karty Dodaj kolumnę.
rys. nr 6 — Kolumna niestandardowa
Pojawi nam się okno Kolumna niestandardowa, w której zmienimy
nazwę kolumny na Miesiąc, a następnie w okienku formuły kolumny niestandardowej
wpiszemy ={[od]..[do]}. Następnie nasze ustawienia zatwierdzamy klikając
przycisk OK (rys. nr 7).
rys. nr 7 — Parametry kolumny niestandardowej
Musimy tutaj pamiętać , że pod wartościami [od] i [do] kryją
się liczby. Otrzymamy dane przedstawione na rysunku nr 8, gdzie dla każdego
wiersza w kolumnie Miesiąc mamy listę liczb między tymi datami – zapisanymi też
jako liczby.
rys. nr 8 — Kolumna niestandardowa Miesiąc
Dla pierwszego wiersza lista ta została przedstawiona na
rysunku nr 9.
rys. nr 9 — Dni kryjące się w tabeli pod nazwą List
W kolejnym kroku zaznaczamy kolumnę Miesiąc, klikamy prawym
przyciskiem myszy na nazwę tej kolumny i
z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 11).
rys. nr 11 — Usuń inne kolumny
Zostanie nam jedna kolumna. Następnie klikamy ikonkę
oznaczoną na rysunku nr 12 zieloną strzałką i z podręcznego menu wybieramy
polecenie Rozwiń do nowych wierszy.
rys. nr 12 — Rozwiń do nowych wierszy
Otrzymamy listę wszystkich dni pomiędzy datami (rys. nr 13).
rys. nr 13 — Lista dni między datami
Nas interesuje konkretnie ilość dni w danym miesiącu, więc w
pierwszym kroku musimy znowu zmienić typ danych na daty. Rozwijamy ikonkę przy
nazwie kolumny i z podręcznego menu wybieramy polecenie Data (rys. nr 14).
rys. nr 14 — Zmiana typy wyświetlania danych
Otrzymamy listę dni w postaci dat, przedstawioną na rysunku
nr 15 (jej fragment).
rys. nr 15 — Lista dni między datami w postaci dat
Przy tak przygotowanych danych wybieramy polecenie Data
(punkt nr 2 na rysunku nr 16) z karty Dodaj kolumnę.
rys. nr 16 — Polecenie Data
Za pomocą tego polecenia możemy z daty wyciągać różne
parametry, takie jak rok, kwartał, miesiąc itd. My wybieramy Rok (rys. nr 17),
następnie analogicznie wybieramy Miesiąc.
rys. nr 17 — Dodanie kolumny Rok
Otrzymamy dane przedstawione na rysunku nr 18.
rys. nr 18 — Dane z dodaną kolumną Rok
W kolejnym kroku z karty Przekształć wybieramy polecenie
Data, a następnie Miesiąc i Nazwa miesiąca (po kolei kroki oznaczone na rysunku
nr 19).
rys. nr 19 — Dodawanie kolumny Miesiąc
Kiedy korzystamy z poleceń z karty Dodaj kolumnę to powstaje
nowa kolumna, jeśli zaś korzystamy z polecenia z karty Przekształć, to działamy
na istniejącej kolumnie i w niej wprowadzamy zmiany. Otrzymamy dane
przedstawione na rysunku nr 20.
rys. nr 20 — Dane z dodaną kolumną Miesiąc
Zaznaczamy wszystkie trzy kolumny a następnie wybieramy
polecenie Grupowanie według (punkt nr 2 na rysunku nr 21) z karty Narzędzia
główne.
rys. nr 21 — Grupowanie według
Otworzy
nam się okno Grupowanie według, gdzie wybieramy grupowanie Zawansowane (punkt
nr 1 na rysunku nr 22). Automatycznie pojawią nam się trzy poziomy grupowania
(punkt nr 2). Następnie nazywamy nową kolumnę Ile dni (punkt nr 3) i wybieramy
operację Zlicz wiersze (punkt nr 4), która ma zliczyć dni z danego miesiąca.
Nie interesuje nas jakie to były dni tylko ich ilość. Nasze parametry
zatwierdzamy przyciskiem OK.
rys. nr 22 — Parametry w oknie Grupowanie według
Otrzymamy pogrupowane dane według miesięcy przedstawione na
rysunku nr 23.
rys. nr 23 — Pogrupowane dane
Tak przygotowane dane chcemy załadować do Excela, więc
korzystamy z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 24).
rys. nr 24 — Zamknij i załaduj
Otworzy nam się okno Ładowanie do, gdzie ustawiamy parametry wstawianych danych, sposób wyświetlania danych jako Tabela i miejsce wstawienia danych jako istniejący arkusz i wybieramy konkretną komórkę, gdzie chcemy umieścić nasze dane. Parametry zatwierdzamy przyciskiem Załaduj (rys. nr 25).
rys. nr 25 — parametry okna Ładowanie do
Otrzymamy dane przedstawione na rysunku nr 26.
rys. nr 26 — Dane załadowane do Excela
W naszych wczytanych dołożyliśmy Rok tylko dla sytuacji kiedy
mamy w danych przeskok lat. Przygotowałem sobie dodatkowy wiersz, który kiedy
dodam do tabeli z pierwotnymi danymi, a następnie naszą tabele z Power Query
odświeżę – klikamy prawym przyciskiem myszy na dane (dowolną komórkę w tabeli
zwróconej przez zapytanie Power Query), a następnie z podręcznego menu wybieramy
polecenie Odśwież (rys. nr 27).
rys. nr 27 — Odśwież zaczytane dane
Otrzymamy przeliczone dane z uwzględnionymi miesiącami w
kolejnym roku (który dołożyliśmy do tabeli i danymi bazowymi), przedstawione na
rysunku nr 28.
rys. nr 27 — Odświeżone dane
Podsumowując
Power Query świetnie sprawdza się w wyznaczaniu ilości dni między datami w
danym miesiącu. Rozwiązanie jest dużo szybsze i prostsze niż 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.
W dzisiejszym poście zajmiemy się tematem scalania tego
samego zapytania w Power Query. Zagadnienie to omówimy na podstawie danych z
poprzedniego postu przedstawionych na rysunku nr 1.
rys. nr 1 — Przykładowe dane
Poprzedni post dotyczył wyciągania pierwszych i ostatnich
wierszy po unikatowych wartościach z kolumn Data i Firma. Stworzyliśmy dwa
zapytania i scaliliśmy je ze sobą. Tym razem chcemy mieć jedno zapytanie.
Zagadnienie to jest wstępem do języka zapytań M, czyli kulis zapytań Power
Query. Pozwoli nam to w miarę naszego rozwoju nie korzystać tylko z gotowych
rozwiązań tego dodatku do Excela.
Pierwszym krokiem jest wczytanie naszych danych z tabeli do
Power Query. Musimy w tym celu użyć polecenia Z tabeli z karty Dane (rys. nr 2).
rys. nr 2 — Pobieranie danych zewnętrznych
Tak jak w poprzednim poście dane zostały wczytane w innym
formacie więc musimy dla kolumny Data i Czas zmienić typ kolumny na bieżącą –
klikamy prawym przyciskiem myszy na tytuł kolumn Data i analogicznie Czas,
następnie wybieramy polecenie Data lub dla drugiej kolumny Godzina i na
komunikacie, który się wyświetli klikamy przycisk Zamień bieżącą (zaznaczono
zieloną strzałką na rysunku nr 3). Celem tych działań jest to, żeby Power Query
odpowiednio interpretował te dane.
rys. nr 3 — Zmiana typu kolumny
Na karcie Widok musimy mieć włączony Pasek formuły (rys. nr
4). Będziemy robić na nim drobne zmiany w kodzie.
rys. nr 4 — Pasek formuły
Wczytane dane przedstawione zostały na rysunku nr 5.
rys. nr 5 — Wczytane dane
Kolejnym krokiem jest posortowanie danych. Klikamy prawym
przyciskiem myszki na trójkącik przy tytule kolumny Data i wybieramy polecenie
Sortuj rosnąco (rys. nr 6).
rys. nr 6 — Sortuj rosnąco
Te same działania musimy powtórzyć dla kolumny Czas. Klikamy
prawym przyciskiem myszki na trójkącik przy tytule kolumny Czas i wybieramy
polecenie Sortuj rosnąco. Z boku ekranu w Ustawieniach zapytania mamy wpisane
kroki, które wykonaliśmy na naszych danych (rys. nr 7).
rys. nr 7 — Ustawienia zapytania
Możemy zmienić nazwę tego kroku, aby w każdym momencie
działań widzieć co konkretnie zrobiliśmy. Naciskamy klawisz F2 i zmieniamy
nazwę na Posortowano Asc (nie napisałem rosnąco, bo ze względu na język
programowania staram się nie używać polskich znaków) – rys. nr 8.
rys. nr 8 — Zmiana nazwy kroku
Teraz tak jak w poprzednim poście musimy usunąć duplikaty po
kolumnach Data i Firma. Zaznaczamy kolumny Data i Firma, a następnie wybieramy
z karty Narzędzia główne polecenie Usuń wiersze (punkt nr 2 na rysunku nr 9), a
potem Usuń duplikaty (punkt nr 3 na rysunku nr 9).
rys. nr 8 — Usuń duplikaty
Otrzymamy wyciągnięte pierwsze wiersze po unikatowych
wartościach. Zostały one przedstawione na rysunku nr 10.
rys. nr 10 — Pierwsze wiersze po unikatowych wartościach
Analogicznie jak dla poprzedniego etapu zmieniamy nazwę
zastosowanego kroku w Ustawieniach zapytania za pomocą klawisza F2 z Usunięto
duplikaty na Pierwsze.
Chcemy aby teraz kolejny krok jaki wykonamy nie był zależny
od kroku Pierwsze tylko od wcześniejszego czyli Posortowano Asc. Klikamy na
krok Posortowano Asc i kopiujemy formułę z paska formuły za pomocą skrótu
klawiszowego Ctrl+C. Następnie przechodzimy do ostatniego naszego kroku, czyli
Pierwsze i na pasku formuły kliknąć przycisk funkcyjny fx – dodać krok
niestandardowy i wkleić (Ctrl+V lub kliknąć prawym przyciskiem myszy i z
podręcznego menu wybrać polecenie Wklej) w pasek formuły skopiowaną wcześniej
formułę z kroku Posortowano Asc.
Musimy zmienić część wklejonej formuły, bo chcemy aby kolumna
Czas była posortowana malejąco. Najprościej wystarczyło by zmienić rodzaj
sortowania klikając na znaczek sortowania przy nazwie kolumny Czas (rys. nr 12).
rys. nr 12 — Sortuj malejąco po kolumnie Czas
Dla bardziej zaawansowanych użytkowników jest inny sposób.
Wystarczy zmienić w formule dla kolumny czas sortowanie z Ascending na Descending,
co zostało pokazane na rysunku nr 13.
rys. nr 13 — Zmiana na pasku formuły
Oba sposoby zadziałają w ten sam sposób. Zmieniamy nazwę
kroku Niestandardowe 1 za pomocą klawisza F2 na Posortowano Desc. Musimy
pamiętać, że Power Query ma problem z tym, że dane nie zostały wczytane od
nowa, pamięta wcześniejszy bufor więc jak w poprzednim poście musimy dodać
kolumnę indeksu. Z karty Dodaj kolumnę wybieramy polecenie Kolumna indeksu
(punkt nr 2 na rysunku nr 14) a następnie Od 0 (punkt nr 3 na rysunku nr 14).
Nie ma znaczenia jaki typ kolumny indeksu wybierzemy bo jest to kolumna
pomocnicza i w późniejszym etapie ją usuniemy.
rys. nr 14 — Kolumna indeksu
Power Query dzięki dodaniu dodatkowej kolumny od nowa zaczyta
dane i zapamięta kolejność z odwrotnym sortowaniem. Zaznaczamy kolumny Data i
Firma i z karty Narzędzia główne wybieramy polecenie Usuń wiersze a następnie
Usuń duplikaty (analogicznie jak na rysunku nr 9 dla pierwszych wierszy).
Otrzymamy ostatnie wiersze dla unikatowych wartości przedstawione na rysunku nr 15.
rys. nr 15 — Ostatnie wiersze dla unikatowych wartości
Zaznaczamy kolumnę Czas i Indeks i usuwamy za pomocą polecenia Usuń kolumny z podręcznego menu (rys. nr 16).
rys. nr 16 — Usuń kolumny
W
poprzednim poście te same wyniki otrzymaliśmy w dwóch osobnych zapytaniach. W
tym poście otrzymaliśmy takie same wyniki w jednym zapytaniu tylko w innych
krokach. Pierwsze wiersze są w kroku Pierwsze, a ostatnie wiersze są w kroku
Usunięto kolumny. Zmienimy jeszcze nazwę ostatniego kroku za pomocą klawisza F2
na Ostatnie (rys. nr 17). Teraz wiemy, które kroki chcemy połączyć.
rys. nr 17 — Kroki które trzeba połączyć
Z karty Narzędzia główne wybieramy polecenie Połącz (punkt nr
2 na rysunku nr 18), a następnie Scal zapytania (punkt nr 3 na rysunku nr 18).
rys. nr 18 — Scal zapytania
Otworzy nam się okno Scalanie. Scalać będziemy to samo
zapytanie, czyli wybierzemy tNotowania i w drugim okienku tNotowania (bieżący).
Zaznaczamy w obu zapytaniach kolumny Data i Firma. One są na razie identyczne.
W kolejnym kroku zrobimy pewną zmianę. Zatwierdzamy nasz wybór przyciskiem OK
(rys. nr 19).
rys. nr 19 — Okno scalanie
W tym kroku bardzo ważne jest, że widzimy pasek formuły, bo
mamy tam podane, na którym kroku wykonujemy dane zadanie (rys. nr 20).
rys. nr 20 — Pasek formuły
W naszej formule powinniśmy zmienić pierwsze wystąpienie
słowa Ostanie na słowo Pierwsze (nazwy kroków zapytania). Aby otrzymać
prawidłowe dane formuła na pasku formuły powinna wyglądać jak na rysunku nr 21.
rys. nr 21 — Zmiana nazwy kroku w formule
W formule powyżej mamy informację, że chcemy połączyć dane z
dwóch kroków w tym samym zapytaniu – krok Pierwsze i krok Ostatnie.
Z naszej tabeli musimy się pozbyć kolumny Czas za pomocą
polecenie Usuń kolumnę z podręcznego menu. Otrzymamy dane przedstawione na
rysunku nr 22.
rys. nr 22 — Usuń kolumnę
Następnie rozwijamy kolumnę Ostatnie (strzałki w prawym roku
nazwy kolumny), odznaczamy Datę i Firmę, a zostawiamy tylko Cenę. Nie chcemy
używać oryginalnej nazwy kolumny jako prefiksu i na koniec zatwierdzamy nasze
ustawienia przyciskiem OK (rys. nr 23).
rys. nr 23
W otrzymanych danych zmienimy nazwę dwóch ostatnich kolumn z
Cenami na Pierwsze i Ostatnie. Otrzymamy końcowe dane dla pierwszych i
ostatnich wierszy unikatowych wartości przedstawione na rysunku nr 24.
rys. nr 24 — Pierwsze i ostatnie wiersze dla unikatowych wartości
Są to dane uzyskane w jednym zapytaniu, włącznie ze
scalaniem. Był to wstęp do zagłębiania się w język M, aby poznać lepiej formuły
i możliwości jakie nam dają. Ostatnim krokiem jest załadowanie danych do Excela
za pomocą polecenia Zamknij i załaduj z karty Narzędzia główne (rys. nr 25).
rys. nr 25 — Zamknij i załaduj
W Excelu otworzy nam się okno Ładowanie do, gdzie wybieramy
sposób wyświetlania danych jako Tabela i miejsce załadowania danych jako
Istniejący arkusz (konkretną komórkę). Nasz wybór zatwierdzamy przyciskiem
Załaduj (rys. nr 26).
rys. nr 26 — Okno Ładowanie do
Dane wklejone do Excela prezentują się jak na rysunku nr 27.
Dane wklejone do Excela
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.
