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 numerowanie poszczególnych elementów danej kategorii
sprzedaży. Rozwiązanie takiego problemu w Excelu omówiliśmy w poradzie 373.
Dziś zajmiemy się rozwiązaniem tego problemu w Power Query. Przykładowe dane do
zadania zostały przedstawione na rys. nr 1.
Rys. nr 1 – przykładowe dane do zadania
Rozwiązanie,
które omówimy opiera się na pomyśle Gena Pulsa (www.excelguru.ce). Zacznijmy od pobrania naszych
danych do zadania. Wybieramy polecenie Z tabeli/ zakresu z karty Dane
(rys. nr 2).
Rys. nr 2 – polecenie Z tabeli/ zakresu
Otworzy nam
się edytor zapytań Power Query z wczytaną tabelą z Excela przedstawiony na rys.
nr 3.
Rys. nr 3 – edytor zapytań z wczytaną tabelą
We wczytanych
danych musimy zrobić drobną korektę. Mianowicie zmienić typ danych w kolumnie
Data, czyli klikamy ikonę kalendarza w tytule kolumny i wybieramy z podręcznego
menu polecenie Data (rys. nr 4).
Rys. nr 4 – zmiana typu danych na Datę
Otworzy nam
się okno Zmiany typu kolumny, gdzie musimy zatwierdzić naszą decyzję za pomocą
przycisku Zamień bieżącą (rys. nr 5).
Rys. nr 5 – okno Zmiany typu kolumny
Naszym celem
jest ponumerowanie elementów poszczególnych kategorii, ale pod warunkiem
zachowania aktualnej kolejności elementów. Z tego powodu w pierwszej kolejności
musimy dodać kolumnę indeksu. W tym celu rozwijamy polecenie KolumnaIndeksu
(punkt 2 na rys. nr 6) z karty Dodaj kolumnę (punkt 1), a następnie
wybieramy polecenie Od 0 (punkt 3).
Rys. nr 6 – ścieżka dostępu do polecenia Od 0 (kolumna indeksu)
Otrzymamy
dane z nową kolumną (kolumna indeksu), która sprawi, że nasze dane zachowają
swoją kolejność (rys. nr 7).
Rys. nr 7 – Dane z dodaną kolumną indeksu
Będziemy
potrzebować paska formuły w dalszych działaniach. Jeśli nie macie go
włączonego, można go uruchomić za pomocą polecenia Pasek formuły na
karcie Widok (oznaczone strzałką na rys. nr 8).
Rys. nr 8 – uruchomienie paska formuły
Dodatkowo
możemy zmienić nazwę nowej kolumny w pasku formuły, żeby nie dodawać kolejnego
kroku do Zastosowanych kroków. Zmieniamy nazwę nowej kolumny na IndeksTmp (rys.
nr 9), żebyśmy wiedzieli że jest on tymczasowy i służy tylko do zachowania
oryginalnej kolejności elementów w tabeli.
Rys. nr 9 – zmiana nazwy nowej kolumny w pasku formuły
W następnym
kroku musimy pogrupować dane po kategorii, czyli wybieramy polecenie Grupowanie
według z karty Narzędzia główne (rys. nr 10).
Rys. nr 10 – polecenie Grupowanie według
Otworzy nam
się okno Grupowania według, gdzie w polu Grupuj według wybieramy grupowanie
według Kategorii (punkt 1 na rys. nr 11), następnie ustawiamy rodzaj Operacji
jako Wszystkie wiersze (punkt 2). Ponadto możemy zmienić nazwę kolumny na TMP
(punkt 3), ponieważ znowu jest to kolumna tymczasowa i w następnych etapach
będziemy ją modyfikować. Tak ustawione parametry grupowania zatwierdzamy
przyciskiem OK.
Rys. nr 11 – parametry grupowania według
Otrzymamy
pogrupowane dane przedstawione na rys. nr 12. Istotne jest, że mamy podział na
4 główne grupy produktów i w kolumnie TMP mamy tabele z poszczególnymi
elementami danej grupy. Warto zaznaczyć, że są to tabele ze wszystkimi
kolumnami z naszej oryginalnej tabeli z zachowaniem kolejności z kolumny
IndeksTmp.
Rys. nr 12 – Pogrupowane dane
Naszym
zadaniem teraz jest dołożenie kolejnej kolumny indeks w tych wewnętrznych
tabelach z kolumny TMP. Dodajemy nową kolumnę indeks, czyli rozwijamy polecenie
Kolumna indeksu z karty Dodaj kolumnę, a następnie wybieramy
polecenie Od 1 (tym razem numerowanie zaczynamy od 1) jak na rys. nr 13.
Rys. nr 13 – polecenie Od 1 (kolumna indeks)
Otrzymamy
dane z nową kolumną przedstawione na rys. nr 14.
Rys. nr 14 – Dane z nową kolumną Indeks
Dzięki
dodaniu tej kolumny w pasku formuły mamy gotowy kod na dodanie kolumny indeksu
(rys. nr 15).
Rys. nr 15 – kod na dodanie kolumny indeks numerowanej od 1
Kopiujemy
ten kod za pomocą skrótu klawiszowego Ctrl+C i usuwamy dodatkowy krok (Dodano
indeks 1), ponieważ był nam potrzebny tylko do otrzymania kodu. Krok usuwamy za
pomocą oznaczenia x przed nazwą danego kroku (rys. nr 16).
Rys. nr 16 – usuwanie kroku
Następnie
wybieramy polecenie Kolumna niestandardowa z karty Dodaj kolumnę
(rys. nr 17).
Rys. nr 17 – polecenie Kolumna niestandardowa
Otworzy nam
się okno Kolumny niestandardowej, gdzie w polu Formuła kolumny niestandardowej
wklejamy nasz skopiowany wcześniej kod (z paska formuły) za pomocą skrótu
klawiszowego Ctrl+V. W formule tej musimy zmienić jedną rzecz ponieważ ta
formuła opierała się na wcześniejszym kroku, czyli na pogrupowaniu wierszy).
Naszym celem jest dodanie kolumny indeksu do tabeli, która znajduje się w
kolumnie TMP. Czyli kasujemy z zapisu formuły nazwę kroku a wstawiamy nazwę
kolumny. Zapis formuły powinien wyglądać następująco:
=Table.AddIndexColumn([TMP],
"Indeks", 1, 1)
Tak
ustawione parametry kolumny niestandardowej zatwierdzamy przyciskiem OK (rys.
nr 18).
Rys. nr 18 – parametry kolumny niestandardowej
Otrzymamy
dane z nową kolumną niestandardową przedstawione na rys. nr 19, gdzie w
kolumnie są ukryte tabele z poprzedniej kolumny ale z dodatkową kolumną indeksu
(ponumerowane elementy w każdej kategorii).
Rys. nr 19 – dane z nową kolumną niestandardową
Możemy teraz
usunąć dwie pierwsze kolumny, ponieważ one nic nam nie wnoszą do wyników,
jakich potrzebujemy. Zaznaczamy dwie kolumny Kategoria i TMP przytrzymując
klawisz Ctrl, następnie usuwamy je za pomocą klawisza Delete. Otrzymamy kolumnę
Niestandardowe, którą będziemy musieli rozwinąć. W tym celu klikamy ikonkę ze
strzałkami obok tytułu kolumny i odznaczamy checkbox przy opcji Użyj
oryginalnej nazwy kolumny jako indeksu. Parametry rozwinięcia zatwierdzamy
przyciskiem OK (rys. nr 20).
Rys. nr 20 – Rozwijanie kolumny niestandardowej
Otrzymamy
rozwinięte dane przedstawione na rys. nr 21.
Rys. nr 21 – rozwinięte dane z kolumny niestandardowej
Przez to że
pogrupowaliśmy dane po kategoriach mamy najpierw wszystkie myszki, potem
klawiatury itd. Pamiętajmy, że dodaliśmy kolumnę IndeksTmp po to, aby móc
wrócić do oryginalnej kolejności elementów. Wystarczy teraz zanzcayć kolumnę
IndeksTmp i wybrać polecenie sortuj od A do Z z karty Narzędzia
główne (rys. nr 22).
Rys. nr 22 – polecenie Sortuj od A do Z
Otrzymamy
dane posortowane według pierwotnej kolejności przedstawione na rys. nr 23.
Rys. nr 23 – posortowane dane
Kolumna
IndeksTmp jest nam już niepotrzebna, więc zaznaczamy ją i usuwamy za pomocą
klawisza Delete. Otrzymamy dane z numerowaniem (indeksem) elementów odpowiednio
w kategoriach przedstawione na rys. nr 24.
Rys. nr 24 – Dane z numerowaniem elementów poszczególnych kategorii
Tak
przygotowane dane możemy zaczytać do Excela. W tym celu wybieramy polecenie Zamknij
i załaduj do z karty Narzędzia główne (rys. nr 25).
Rys. nr 25 – polecenie Zamknij i załaduj do
W Excelu
otworzy nam się okno Importowania danych, gdzie ustawiamy sposób wyświetlania
danych jako Tabela, a następnie wskazujemy miejsce wstawienia danych, czyli
istniejący arkusz i wskazujemy konkretną
komórkę. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem
OK (rys. nr 26).
Rys. nr 26 – parametry importowania danych do Excela
Otrzymamy
dane wstawione do Excela przedstawione na rys. nr 27.
Rys. nr 27 – dane zaimportowane z Power Query
Musimy
pamiętać o odpowiednim formatowaniu danych, ponieważ w kolumnie Data
otrzymaliśmy liczby zamiast dat. Zaznaczamy kolumnę Data i na karcie Narzędzia
główne możemy zmienić formatowanie w kategorii Liczba na Data krótka
(rys. nr 28).
Rys. nr 28 – zmiana formatowania na Datę krótką
Otrzymamy
końcowe dane, odpowiednio sformatowane a przede wszystkim z numeracją
poszczególnych elementów danych kategorii (rys. nr 29).
Rys. nr 29 – Dane z numeracją elementów poszczególnych kategorii
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.
W dzisiejszym poście nauczymy się jak wyciągnąć wartości z
ostatniego i pierwszego wiersza po unikatowych wierszach (wartościach) w Power
Query. Jest to kontynuacja tematów z porad o numerach 307 i 308. Zagadnienie to
omówimy na podstawie przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Wykorzystamy tutaj metodę, której
użyliśmy w poradzie 307 w Excelu, ale tym razem w Power Query. Pierwszym
krokiem jest wczytanie danych z Excela. Wybieramy polecenie Z tabeli (punkt nr
2 na rysunku nr 2) na karcie Dane (punkt 1).
rys. nr 2 — Pobieranie danych zewnętrznych z tabeli
Otworzy nam się Okno – Edytor
zapytań, a w nim dane z tabeli tNotowania (rysunek nr 3).
rys. nr 3 — Edytor zapytań
Power Query niepotrzebnie do daty dodaje czas, więc klikamy na ikonkę
kolumny Data, a następnie wybieramy polecenie Data (tylko data) z podręcznego
menu (punkt nr 2 na rysunku nr 4).
rys. nr 4
Pojawi nam się komunikat dotyczący zmiany typu kolumny.
Klikamy przycisk Zamień bieżącą (rys. nr 5).
rys. nr 5 — Komunikat o zmianie typu kolumny
Power Query nie rozpoznał poprawnie czasu, więc analogicznie
klikamy na ikonkę na kolumnie Czas i wybieramy polecenie Godzina z podręcznego
menu (rys. nr 6).
rys. nr 6
Pojawi się taki sam komunikat jak dla Czasu. Klikamy przycisk
Zamień bieżącą. Otrzymamy wtedy odpowiednio przedstawione dane z rysunku nr 7.
rys. nr 7
Mając tak posortowane dane , chcemy teraz wyciągnąć pierwsze
i ostatnie wiersze. Klikamy znak < zaznaczony na rysunku nr 7 zieloną
strzałką. Nasze zapytanie nazywa się tNotowania. Potrzebujemy 2 razy tego
zapytania, ponieważ raz chcemy wyciągnąć pierwsze wiersze, a za drugim razem
ostatnie wiersze. Klikamy prawym przyciskiem myszy na nazwę zapytania
tNotowania i z podręcznego menu wybieramy polecenie Duplikuj (rys. nr 8).
rys. nr 8 — Polecenie Duplikuj
Klikamy na tNotowania i zaznaczamy sobie kolumny dla których
chcemy wyciągnąć unikatowe wartości, czyli kolumny Data i Firma (rys. nr 9).
rys. nr 9 — Zaznaczone kolumny Data i Firma
W pierwszym kroku tak jak w poradzie 307 musimy usunąć
duplikujące się wartości. Z karty Narzędzia główne wybieramy polecenie Usuń
wiersze (punkt nr 2 na rysunku nr 10), a następnie polecenie Usuń duplikaty
(punkt nr 3).
rys. nr 10 — Usuń duplikaty
W wyniku usunięcia duplikatów otrzymamy pierwsze wiersze
przedstawione na rysunku nr 11.
rys. nr 11 — Pierwsze wiersze
Analogicznie zaczniemy działać dla zapytania tNotowania2. W
pierwszej kolejności powinniśmy posortować dane w kolumnie Data. Użyjemy do
tego polecenia Sortuj rosnąco pokazanego na rysunku nr 12.
rys. nr 12 — Sortuj rosnąco
A następnie musimy posortować kolumnę Czas w odwrotnej
kolejności, czyli polecenie Sortuj malejąco. Posortowanie w ten sposób danych
sprawiło, że na początku danych mamy ostatnie wiersze. Teraz wystarczy usunąć
duplikaty. Zaznaczamy kolumny Data i Firma i z karty Narzędzia główne wybieramy
polecenie Usuń wiersze, a następnie polecenie Usuń duplikaty (identycznie jak
na rysunku nr 10). Otrzymamy ostatnie wiersze przedstawione na rysunku nr 13.
s. nr 13 — Błędne ostatnie wiersze
Od razu widać że są to takie same wartości jak dla pierwszych
wierszy w zapytaniu tNotowania. Z Power Query jest taki problem, że nie odświeża
sobie bufora danych i działa na pierwszym zaczytaniu. Nie uwzględnia sortowania. Możemy obejść ten
błąd. Przed usunięciem duplikatów musimy wykonać operację która każe Powe Query
ponownie zaczytać dane, stworzyć ich nowy układ. Rozwiązanie to podpowiedział
mi Bill Szysz. Najprostszą operacją jest dodawanie kolumny indeksu. Z karty
Dodaj kolumnę, wybieramy polecenie Kolumna indeksu (punkt 2 na rysunku nr 14),
a następnie Od 1 (punkt 3).
rys. nr 14 — Kolumna indeksu
Nie ma znaczenia czy dodamy Kolumnę indeksu od 0 czy od 1 bo
chodzi tylko o ponowne wczytanie danych przez program Power Query. Zaznaczamy
kolumny Data i Firma a następnie wybieramy polecenie Usuń duplikaty z karty
Narzędzia główne (rys. nr 10 wyżej). Teraz otrzymamy dane przedstawione na
rysunku nr 15.
rys. nr 15 — Ostatnie wiersze
Tym razem dane są poprawne, możemy to zauważyć między innymi
po numerze kolumny indeksu (numery są nie po kolei). Kolumna Indeks i Czas są
nam niepotrzebne. Zaznaczamy je, klikamy prawym przyciskiem myszki i z
podręcznego menu wybieramy polecenie Usuń kolumny (rys. nr 16).
rys. nr 16 — Usuń kolumny
Otrzymamy dane dla zapytania tNotowania2 przedstawione na
rysunku nr 17.
rys. nr 17 — Dane dla zapytania tNotowania2
Analogicznie dla zapytania tNotowania usuwamy kolumnę Czas i
otrzymujemy wyniki przedstawione na rysunku nr 18.
rys. nr 18 — Dane po usunięciu kolumny Czas
W kolejnym kroku musimy scalić te zapytania. Zaznaczamy zapytanie tNotowania i z karty Narzędzia główne wybieramy polecenie Połącz, potem Scal zapytania (polecenia oznaczone kolejno na rysunku nr 19).
rys. nr 19 — Scal zapytania
Otworzy nam się okno Scalanie. Musimy pamiętać, aby dla
zapytania tNotowania (punkt nr 1 na rysunku nr 20) przy użyciu przycisku Ctrl
zaznaczyć kolumny Data i Firma, bo po tych kolumnach mamy wyznaczone niepowtarzalność
wiersza. Analogicznie dla zapytania tNotowania2 (punkt nr 2 na rysunku nr 20)
zaznaczamy kolumny Data i Firma. Następnie w punkcie oznaczony numerem 3 na rysunku
poniżej wybrać Rodzaj sprzężenia, czyli Lewe zewnętrzne.
rys. nr 20 — Scalanie
Zatwierdzamy opcje scalania przyciskiem OK i mamy dołożone do
naszego zapytania tNotowania wyniki zapytania tNotowania2 (rys. nr 21).
rys. nr 21 — Scalone dane dla dwóch zapytań
Na razie wyniki drugiego zapytania są w formie tabeli. A
wyniki poszczególnych wierszy przedstawione są poniżej (miejsce zaznaczone
zieloną strzałką na rysunku nr 22)
rys. nr 22
Z tych wartości potrzebujemy wyciągnąć tylko cenę bo inne
wartości już mamy. Klikamy ikonkę dwóch strzałek przy nazwie tNotowania2 i
zaznaczamy opcję Rozwiń, następnie zaznaczamy tylko kolumnę Cena i odznaczamy
checkbox przy poleceniu Użyj oryginalnej
nazwy kolumny jako prefiksu (polecenia kolejno oznaczone numerami od do 3 na
rysunku nr 23).
rys. nr 23
Zatwierdzamy nasz wybór przyciskiem OK i otrzymujemy dane
przedstawione na rysunku nr 24, zawierające ceny z obu zapytań. Możemy
pozmieniać nazwy kolumn na Pierwsza cena i Ostatnia cena.
rys. nr 24 — Dane z obu zapytań
Tak przygotowane dane możemy zamknąć i załadować do Excela.
Polecenie Zamknij i załaduj do na karcie Narzędzia główne (rys. nr 25).
rys. nr 25 — Zamknij i załaduj
W Excelu wyświetli nam się okno Ładowanie do. Wybieramy
sposób wyświetlania danych jako Utwórz tylko połączenie, a następnie
lokalizację wstawienia danych jako Nowy arkusz i Zatwierdzamy nasz wybór
klikając przycisk OK (rys. nr 26).
rys. nr 26 — Ładowanie do
Rozwijamy po prawej stronie okienko Zapytania do. Klikamy
prawym przyciskiem myszy na tNotowania i z podręcznego menu wybieramy polecenie
Załaduj do (rys. nr 27).
rys. nr 27 — Załaduj do
Pokaże się nam okno ładowanie do, w którym wybieramy sposób
wyświetlania danych jako Tabela i lokalizację ich wyświetlenia w Istniejącym
arkuszu, następnie klikamy przycisk Załaduj (rys. nr 28).
rys. nr 28 — przycisk Załaduj
Otrzymamy dane przedstawione na rysunku nr 29.
rys. nr 29 — Końcowe dane
Power Query sprawdza się przy wyznaczaniu wartości z
pierwszego i ostatniego wiersza, ale zdarzają się zgrzyty przy usuwaniu
duplikatów. Wynika to z tego, że Power Query potrzebuje przeczyszczenia buforu
danych, ponownego załadowania danych.
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.
