Excel - kurs online - oferta dla każdego

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ę.

Excel Power Query 58 — Indeks numerowanie wewnątrz grup

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
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
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ą
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ę
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
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 Kolumna Indeksu (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)
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
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
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
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
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
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
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)
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
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
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
Rys. nr 16 – usuwanie kroku

Następnie wybieramy polecenie Kolumna niestandardowa z karty Dodaj kolumnę (rys. nr 17).

Rys. nr 17 – polecenie Kolumna niestandardowa
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
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ą
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
Rys. nr 20 – Rozwijanie kolumny niestandardowej

Otrzymamy rozwinięte dane przedstawione na rys. nr 21.

Rys. nr 21 – rozwinięte dane z kolumny niestandardowej
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
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
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
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
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
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
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ą
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
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.

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

Power Query 36 — Dynamiczna ilość kolumn przy podziale ogranicznikiem — Interfejs użytkownika

W dzisiejszym poście nauczymy się jak w Power Query stworzyć dynamiczną ilość kolumn przy podziale kolumny według ogranicznika. Rozwiązanie tego problemu zostało przedstawione na rysunku nr 1.

rys. nr 1 — Rozwiązanie zadania

W Power Query często pojawia się problem, kiedy dzielimy dane na kolumny według ogranicznika, to w zależności ile tych produktów było rozdzielonych tym ogranicznikiem, Power Query mógł się nie odświeżyć poprawnie. Power Query zapisuje konkretną ilość kolumn powstałych w kroku Podziel kolumnę według ogranicznika i kiedy dopiszemy więcej danych, nie pojawią się nowe kolumny. Rozwiązanie, które przedstawimy wykorzystuje interfejs użytkownika. W pliku do pobrania możecie znaleźć trzy rozwiązania. Pierwsze, którego nie będę omawiał, bo przy większej ilości danych (powyżej 10) Power Query się gubił. Drugie rozwiązanie podpowiedziane przez Billa Szysza, z wykorzystaniem interfejsu użytkownika oraz trzecie dla bardziej zaawansowanych użytkowników, wykorzystujące język M.

Temat ten omówimy na podstawie przykładowych danych z rysunku nr 2.

rys. nr 2 — Przykładowe dane

Wybieramy polecenie Z tabeli/zakresu z karty Dane (rys. nr 3). 

rys. nr 3 — Z tabeli

Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą danych. Usuwamy krok Zmieniono typ z Zastosowanych kroków. Interesuje nas tylko kolumna Produkt, więc klikamy na jej nagłówek prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 4).

rys. nr 4 — Usuń inne kolumny

Otrzymamy dane przedstawione na rysunku nr 5.

rys. nr 5 — Dane po usunięciu innych kolumn

Do prawidłowego działania zapytania przy użyciu interfejsu musimy dodać Kolumnę indeksu z karty Dodaj kolumnę (rys. nr 6).

rys. nr 6 — kolumna indeksu

Bez znaczenia czy jest indeksowana od wartości 0 czy 1, ważne aby w każdym wierszu były inne liczby. Otrzymamy dane przedstawione na rysunku nr 7.

rys. nr 7 — Dane z kolumną indeksu

W kolejnym etapie dzielimy naszą kolumnę Produkt po ograniczniku. Rozwijamy polecenie Podziel kolumny (punkt nr 2 na rysunku nr 8) z karty Przekształć i z listy wybieramy polecenie Według ogranicznika (punkt nr 3 na rysunku nr 8).

rys. nr 8 — Podziel kolumny

Otworzy nam się okno Dzielenia kolumny według ogranicznika, gdzie musimy określić parametry podziału. Wybieramy rodzaj ogranicznika — przecinek, następnie, kiedy dzielimy dane, czyli w naszym przykładzie przy każdym wystąpieniu ogranicznika. Następnie w opcjach zaawansowanych zaznaczamy podział na wiersze. Podział na kolumny ma ustawianą ilość kolumn na 11 ponieważ tyle najwięcej kolumny powstanie dla naszych danych, więc gdy dodamy większą ilość produktów Power Query ich nie pokaże, bo zapisze sobie w pamięci podział na 11 kolumn. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 9).

rys. nr 9 — Dzielenie kolumny według ogranicznika

Otrzymamy dane przedstawione na rysunku nr 10.

rys. nr 10 — Podzielone dane

Kolumna indeks odpowiednio się zduplikowała. Mamy w niej informacje, z którego wiersza pochodzą poszczególne dane. W kolejnym etapie możemy zrobić grupowanie po indeksie, żeby policzyć, ile było elementów w poszczególnych pozycjach w danych wejściowych. Wybieramy polecenie Grupowanie według z karty Przekształć (rys. nr 11).

rys. 11 — Grupowanie według

Otworzy nam się okno Grupowania według, w którym musimy określić jego parametry. W polu Grupowania według wybieramy kolumnę Indeks, Wpisujemy nazwę nowej kolumny (Liczność) i wybieramy operację jaka ma zostać wykonana, czyli w naszym przykładzie Zlicz wiersze. Zatwierdzamy przyjęte założenia przyciskiem OK (rys. nr 12).

rys. 12 — Parametry grupowania danych

Otrzymamy dane przedstawione na rysunku nr 13.

rys. 13 — pogrupowane dane

W powyższych wynikach interesuje nas maksymalna wartość z kolumny Liczność. Zaznaczamy kolumnę Liczność i rozwijamy polecenie Statystyka (punkt nr 2 na rysunku nr 14) z karty Przekształć, a następnie wybieramy polecenie Maksimum (punkt nr 3 na rysunku nr 14).

rys. nr 14 — Maksimum

Power Query wyciągnie nam, jako wynik, maksymalną wartość i otrzymamy daną przedstawioną na rysunku nr 15.

rys. 15 — Maksymalny wynik

Przechodzimy do kroku Usunięto inne kolumny w Zastosowanych krokach, czyli do danych przedstawionych na rysunku nr 5 wyżej, następnie kopiujemy po wciśnięciu klawisza F2 nazwę tego kroku za pomocą skrótu klawiszowego Ctrl+C. Dołożymy teraz nowy krok w działaniu – zmienimy formułę w pasku formuły, a dokładniej zastąpimy nazwę kroku Obliczona wartość maksymalna na tą, którą skopiowaliśmy wcześniej, czyli Usunięto inne kolumny (rys. nr 16).

rys. 16 — Pasek formuły

Zmianę tą zatwierdzamy przyciskiem Enter i otrzymamy dane przedstawione na rysunku nr 17.

rys. 17

W kroku Niestandardowe1 mamy kopię kroku Usunięto inne kolumny. Na tym etapie rozwijamy polecenie Podziel kolumny z karty Narzędzia główne, a następnie wybieramy polecenie Według ogranicznika (rys. nr 18).

rys. 18 — Podziel kolumny

Otworzy nam się okno Dzielenia kolumny według ogranicznika. W polu wybierz lub wprowadź ogranicznik wybieramy opcję Niestandardowe, a następnie wpisujemy ogranicznik przecinek i spacja (, ). Na liście Podziel przy wybieramy opcję Każde wystąpienie ogranicznika. Następnie w opcjach zaawansowanych wybieramy podział na kolumny i wpisujemy ilość kolumn, na która zostanie podzielona kolumna jako wartość 2. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 19). 

rys.19 — Parametry dzielenia według ogranicznika

Otrzymamy dane przedstawione na rysunku nr 20. Usuwamy krok Zmieniono typ z zastosowanych kroków.

rys. 20 — Podzielone dane

Gdybyśmy mieli wpisaną wartość domyślną 11, jako liczbę kolumn, na która podzielić kolumny na rysunku nr 19, to Power Query w pasku formuły wypisałby 11 nazw (rys. nr 21).

rys.21 Ilość nazw kolumn wyznaczona na pasku formuły

Użyliśmy tutaj funkcji Table.SplitColumn, więc kiedy podejrzymy sobie jej działanie tej funkcji otrzymamy między innymi informacje przedstawione na rysunku nr 22.

rys. 22 — Table.SplitColumn

Interesuje nas opcja ColumnNamesOrNumbers, czyli liczba kolumn bądź ich nazwy. W pasku formuły zamiast nazw kolumn (podświetlone na rysunku nr 23) wpisujemy wartość 11.

rys. nr 23 — Pasek formuły

Zatwierdzamy zmiany w formule przyciskiem Enter i otrzymujemy dane przedstawione na rysunku nr 24.

rys. 24 — Dane

Nie chcemy, aby wartość 11 była wpisana na stałe. Chcemy aby Power Query pobierał nam tą wartość z poprzedniego kroku, czyli z kroku Obliczona wartość maksymalna. W tym celu kopiujemy (po wciśnięciu klawisza F2) nazwę tego kroku za pomocą skrótu klawiszowego Ctrl+C. Teraz zamiast wartości 11 w pasku formuły wklejamy nazwę skopiowanego kroku poprzedzoną znakiem # (hash) i w cudzysłowie (rys. nr 25).

rys. 25 — Zmiany w pasku formuły

Po zatwierdzeniu formuły klawiszem Enter, otrzymujemy dane przedstawione na rysunku nr 26.

rys. 26 — Końcowe dane

Tak przygotowane dane możemy zaimportować do Excela. W tym celu wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 27).

rys. 27 — Zamknij i załaduj do

Otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela i wskazujemy miejsce ich wstawienia, czyli Istniejący arkusz oraz wskazujemy konkretną komórkę. Tak określone parametry zatwierdzamy przyciskiem OK (rys. nr 28)

rys. 28 — Okno importowania danych

Otrzymamy dane przedstawione na rysunku nr 29.

rys. 29 Dane zaimportowane do Excela

Jeśli w danych wejściowych w wierszu nr 5, który rozpatrujemy, dopiszemy sobie dodatkowy produkt, a następnie odświeżymy nasze dane z Power Query, otrzymamy powiększoną tabelę o ten dopisany produkt. Tabela wczytana z Power Query jest tabelą dynamiczną, która reaguje po odświeżeniu na wprowadzane zmiany w danych bazowych. To rozwiązanie wykonaliśmy za pomocą interfejsu, natomiast w kolejnym filmie skorzystamy z kodu M (rozwiązanie podpowiedziane przez Billa Szysza).


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

Power Query #28 — Pary produktów, które wystąpiły razem

W dzisiejszym poście nauczymy jak w Power Query znaleźć pary produktów, które wystąpiły razem w danych. Nie ma znaczenia czy będziemy bazować na Id produktu czy na jego nazwie. Załóżmy, że mamy listę zakupów z produktami oznaczonymi numerami Id. Ręczne wyszukiwanie par jest pracochłonne i męczące. Rozwiązanie tego problemu omówimy na podstawie przykładowych danych przedstawionych na rysunku nr 1.

rys. nr 1 — Przykładowe dane

W pierwszym kroku musimy wczytać nasze dane do Power Query, w tym celu wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane.

rys. nr 2 — Z tabeli

Otworzy nam się Edytor zapytań z wczytaną tabelą tIdProduktu. W polu Zastosowane kroki usuwamy krok Zmiana typu, ponieważ nie jest nam potrzebny. Otrzymamy dane przedstawione na rysunku nr 3.

rys. nr 3 — edytor zapytań

W kolejnym etapie musimy zduplikować tą kolumnę. Wybieramy polecenie Duplikuj kolumnę (punkt nr 2 na rysunku nr 4) z karty Dodaj kolumnę.

rys. nr 4 — duplikuj kolumnę

Następnie dokładamy jeszcze kolumnę z indeksem, żebyśmy widzieli które to były zakupy. Wybieramy polecenie Kolumna indeksu (punkt nr 2 na rysunku nr 5) a następnie od 01 (punkt nr 3 na rysunku nr 5) z karty Dodaj kolumnę.

rys.. nr 5 — Dodaj kolumnę indeksu

Aby w Power Query znaleźć pary produktów musimy podzielić dane po ogranicznikach a następnie odpowiednie dane połączyć. Wybieramy polecenie Podziel kolumny (punkt nr 2 na rysunku nr 6) z karty Narzędzia główne, a następnie polecenie Według ogranicznika (punkt nr 3 na rysunku nr 6).

rys. nr 6 — Podziel kolumny według ogranicznika

Otworzy nam się okno Dzielenia kolumny według ogranicznika, gdzie musimy określić parametry tego dzielenia. W polu Wybierz lub wprowadź ogranicznik wybieramy Niestandardowy (punkt nr 1 na rysunku nr 7), a następnie wpisujemy użyty u nas ogranicznik, czyli przecinek i spacja. W opcji Podziel przy wybieramy checkbox przy Każdym wystąpieniu ogranicznika (punkt nr 2 na rysunku nr 7). Natomiast w Opcjach zaawansowanych Podziel na (punkt nr 3 na rysunku nr 7) zaznaczamy checkbox przy Wierszach. Tak dobrane ustawienia zatwierdzamy przyciskiem OK.

rys. nr 7 — Dzielenie kolumny według ogranicznika

Otrzymamy dane, których fragment został przedstawiony na rysunku nr 8.

rys. nr 8 Podzielone dane

Power Query przy okazji dzielenia kolumny zamienił nam dane na liczby. Nie ma to większego znaczenia. Analogicznie postępujemy dla drugiej kolumny (kopii pierwszej). Wybieramy polecenie Podziel kolumny, a następnie według ogranicznika z karty Narzędzia główne (jak na rysunku nr 6). Pojawi się nam okno Dzielenia kolumny według ogranicznika, w której ustawiamy takie same parametry jak dla pierwszej kolumny (jak na rysunku nr 7) i wybrane parametry zatwierdzamy klikając przycisk OK. Otrzymamy dane przedstawione na rysunku nr 9.

rys. nr 9 — takie same wartości w dwóch kolumnach

W danych tych pojawia się problem – mamy zduplikowane pary (zaznaczone na rysunku nr 9 zielonym prostokątem). Można łatwo rozwiązać ten problem, ponieważ interesuje nas sytuacja, kiedy danej z kolumny pierwszej są mniejsze od danych z kolumny drugiej. Wystarczy dodać kolumnę warunkową z karty Dodaj kolumnę (rys. nr 10)

rys. nr 10 — Dodaj kolumnę warunkową

Otworzy nam się okno Dodawania kolumny warunkowej, gdzie musimy określić Nazwę kolumny – Pary (punkt nr 1 na rysunku nr 11), Następnie wypełnić warunek w punkcie 2 na rysunku nr 11. Ważne jest, że jeśli warunek zostanie spełniony to chcemy otrzymać wartość z pierwszej kolumny, natomiast jeśli nie zostanie spełniony chcemy otrzymać wartość null (punkt nr 3 na rysunku nr 11). Tak dobrane parametry kolumny warunkowej zatwierdzamy przyciskiem OK.

rys. nr 11 — okno dodawania kolumny warunkowej

Konkretnie chcemy otrzymać wartość z pierwszej kolumny tylko wtedy kiedy wartość z pierwszej kolumny jest mniejsza od wartości z drugiej kolumny. Otrzymamy dane przedstawione na rysunku nr 12. Nie jest to jeszcze wynik docelowy, bo chcemy otrzymać parę, ale ważne jest, że para pokazuje się dla jednej sytuacji w pierwszym zakupie.

rys. nr 12 — Dane z kolumną warunkową

Aby otrzymać jako wynik parę, musimy wprowadzić zmiany w zapisie formuły w pasku formuły. Dołożyć z ogranicznikiem wartość z kolumny drugiej. Zapis formuły został przedstawiony na rysunku nr 13 (zieloną strzałką zaznaczono wprowadzoną zmianę). Wprowadzoną zmianę zatwierdzamy przyciskiem Enter.


rys. nr 13 — zapis formuły 

W wyniku otrzymamy błąd ponieważ nasze dane zostały zmienione na liczby. Aby obejść ten problem musielibyśmy użyć funkcji Number.ToText. My użyjemy łatwiejszego sposobu – z zastosowanych kroków usuniemy krok zmiany typu danych z tekstu na liczby – Zmieniono typ i Zmieniono typ1 (rys. nr 14).

rys. nr 14 — usunięcie kroków

Otrzymamy wtedy prawidłowe dane przedstawione na rysunku nr 15.

rys. nr 15 — Dane w formie tekstowej

Pozostaje nam pogrupować te produkty po ilości, użyjemy do tego polecenia Grupowanie według (punkt nr 2 na rysunku nr 16) z karty Narzędzia główne.

rys. nr 16 — Grupowanie według

Otworzy się nam okno Grupowania według, w którym musimy określić nazwę nowej kolumny i rodzaj operacji jaką chcemy wykonać. Wybrane parametry zatwierdzamy przyciskiem OK (rys. nr 17).

rys. nr 17 — Okno Grupowania według

Otrzymamy dane przedstawione na rysunku nr 18.

rys. nr 18 — pogrupowane dane

W danych tych wiele razy powtórzyła nam się wartość null, więc wystarczy przefiltrować dane, żeby odfiltrować wyniki dla null. Klikamy na ikonkę trójkącika w nazwie kolumny Pary i odznaczamy w filtrach null (rys. nr 19).

rys. nr 19 — Odfiltrowanie danych dla null

W kolejnym etapie chcemy posortować dane malejąco w obu kolumnach. Korzystamy z podręcznego menu po naciśnięciu ikonki trójkącika w tytule kolumn, gdzie wybieramy polecenie Sortuj malejąco (rys. nr 20).

rys. nr 20 — Sortuj malejąco

Otrzymamy końcowe dane, które możemy załadować do Excela. Korzystamy z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 21).

rys. nr 21 — Zamknij i załaduj do

W Excelu otworzy nam się okno Ładowania do, gdzie ustawiamy parametry wstawianych danych. Sposób wyświetlania danych jako tabela i miejsce wstawienia danych jako istniejący arkusz i wskazujemy konkretną komórkę. Powyższe parametry zatwierdzamy przyciskiem Załaduj (rys. nr 22).

rys. nr 22 — Okno Ładowanie do

Otrzymamy dane wstawione do Excela przedstawione na rysunku nr 23.

rys. nr 23 — dane wczytane 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.

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

Power Query #26 — Zamień wszystkie x w tabeli na nagłówki kolumn

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

Power Query #19 — Scalanie tego samego zapytania

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.

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