0
0 Produkty w koszyku

No products in the cart.

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

Excel Power Query 57 — Czy element jest na liście

W dzisiejszym poście omówimy funkcjonalność polegającą na sprawdzaniu czy dany element jest na liście. W przykładowych danych przedstawionych na rys. nr 1 mamy tabelę ze sprzedawcami, gdzie w zależności na której liście znajduje się dany sprzedawca, to z tej listy chcemy pobierać dane.

Rys. nr 1 – przykładowe dane
Rys. nr 1 – przykładowe dane

Zakładamy, że główna lista zawiera województwo, w którym działa dany sprzedawca i właśnie stamtąd chcemy pobierać dane. W pierwszym kroku musimy pobrać nasze dane z tabel pobrać do Power Query. Możemy to zrobić wybierając 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ą pobraną z Excela przedstawioną na rys. nr 3.

Rys. nr 3 – edytor zapytań Power Query z wczytaną tabelą z danymi
Rys. nr 3 – edytor zapytań Power Query z wczytaną tabelą z danymi

W analogiczny sposób pobieramy dane z drugiej tabeli. Ustawiamy aktywną komórkę w obszarze tabeli z regionami i wybieramy polecenie Z tabeli/ zakresu  z karty Dane (jak na rys. nr 2). Otrzymamy dwa odrębne zapytania w Edytorze zapytań Power Query (rys. nr 4).

Rys. nr 4 – dwa zapytania w Power Query (dwie tabele)
Rys. nr 4 – dwa zapytania w Power Query (dwie tabele)

Możemy przybliżyć dane (powiększyć) za pomocą skrótu klawiszowego Ctrl+Shift+=. Przechodzimy na zapytanie tSprzedaż_k, gdzie naszym zadaniem będzie napisanie kolumny warunkowej, która na podstawie sprzedawcy będzie wybierała wartość z kolumny Świętokrzyskie albo Mazowieckie. W tym celu wybieramy polecenie  Kolumna warunkowa  z karty Dodaj kolumnę (rys. nr 5).

Rys. nr 5 – polecenie Kolumna warunkowa
Rys. nr 5 – polecenie Kolumna warunkowa

Otworzy nam się okno Dodawania kolumny warunkowej. Ta funkcjonalność Power Query posłuży nam do zbudowania struktury funkcji IF (funkcji JEŻELI). Kolumnę tą łatwo będzie stworzyć, ale później dokonamy pewnych modyfikacji, żeby sprawdzić czy dany element jest na liście. W polu Nazwa nowej kolumny wpisujemy Główny region (punkt 1 na rys. nr 6). W polu Nazwa kolumny wybieramy z listy rozwijanej kolumnę Sprzedawca (punkt 2), w polu Operator zostawiamy domyślny, czyli równa się (punkt 3). W polu Wartość wpisujemy nazwę dowolnego sprzedawcy z regionu Świętokrzyskie np. Aleksandra (punkt 4), natomiast w polu Wartość wyjściowa najpierw rozwijamy ikonkę obok pola i wybieramy z listy rodzaj danych – Wybierz kolumnę (oznaczone strzałką) i w polu wybieramy wartość z kolumny Świętokrzyskie (punkt 5). Następnie klikamy przycisk Dodaj regułę, aby stworzyć kolejną strukturę funkcji JEŻELI.

Rys. nr 6 – okno dodawania kolumny warunkowej (pierwsza funkcja JEŻELI)
Rys. nr 6 – okno dodawania kolumny warunkowej (pierwsza funkcja JEŻELI)

W drugiej regule w polu Nazwa kolumny  wybieramy kolumnę Sprzedawca, pole Operator zostaje bez zmian, w polu Wartość  wpisujemy Dominik, następnie zmieniamy typ Wartości wyjściowej na Wybierz kolumnę (jak dla pierwszej reguły) i wybieramy kolumnę Mazowieckie. Powinniśmy stworzyć takie reguły dla każdej pojedynczej osoby. Było by to bardzo czasochłonne. My zbudowaliśmy tu tylko strukturę a później przez odpowiednie przekształcenie, sprawimy, żeby to działało dla dowolnego sprzedawcy. W polu W przeciwnym razie wpisujemy wartość null (oznaczone strzałką na rys. nr 7). Tak ustawione parametry kolumny warunkowej zatwierdzamy przyciskiem OK.

Rys. nr 7 – okno dodawania kolumny warunkowej (druga funkcja JEŻELI)
Rys. nr 7 – okno dodawania kolumny warunkowej (druga funkcja JEŻELI)

Otrzymamy dane z nową kolumną przedstawione na rys. nr 8. W kolumnie Główny region stworzyliśmy strukturę, zbudowaną za podstawie prostych obliczeń funkcji JEŻELI.

Rys. nr 8 – Dane z nową kolumną warunkową
Rys. nr 8 – Dane z nową kolumną warunkową

W zapisie formuły kolumny warunkowej w pasku formuły sprzedawca został porównany do konkretnego sprzedawcy, a naszym celem będzie sprawdzenie czy dany sprzedawca jest na liście. Podsumowując zastąpimy to porównanie funkcją List.Contains (rys. nr 9).

Rys. nr 9 – zapis formuły kolumny warunkowej
Rys. nr 9 – zapis formuły kolumny warunkowej

Funkcja List.Contains potrzebuje jako pierwszego argumentu listy wszystkich sprzedawców, listę w Power Query zapisujemy w nawiasach klamrowych. Następnie sprawdzamy czy w danej liście znajduje się ten konkretny sprzedawca z danego wiersza, czyli tutaj możemy użyć struktury zbudowanej przez kolumnę warunkową. Zapis formuły powinien wyglądać następująco:

=Table.AddColumn(#"Zmieniono typ", "Główny region", each if List.Contains ({"Aleksandra", "Robert"}, [Sprzedawca]) then [Świętokrzyskie] else if [Sprzedawca]="Dominik" then [Mazowieckie] else null))

Zmieniliśmy tylko część formuły – dla pierwszych 2 sprzedawców i po zatwierdzaniu formuły otrzymamy zmienione dane przedstawione na rys. nr 10.

Rys. nr 10 – zapis funkcji List.Contains dla pierwszej reguły kolumny warunkowej
Rys. nr 10 – zapis funkcji List.Contains dla pierwszej reguły kolumny warunkowej

Ten sposób jest łatwiejszy, ponieważ bezpośrednio w formule możemy wypisać wszystkich sprzedawców z danego województwa. Musimy pamiętać, że pobraliśmy drugą tabelę z danymi (zapytanie tRegiony_k), gdzie mamy listy sprzedawców z podziałem na województwa. W zapisie formuły możemy się odwołać bezpośrednio do danych z tego zapytania (do poszczególnych kolumn), zamiast wpisywać ręcznie nazwy poszczególnych sprzedawców (rys. nr 11). Zapis formuły będzie wtedy wyglądał następująco:

=Table.AddColumn(#"Zmieniono typ", "Główny region", each if List.Contains (tRegiony_k [Świętokrzyskie], [Sprzedawca]) then [Świętokrzyskie] else if [Sprzedawca]="Dominik" then [Mazowieckie] else null))

Rys nr 11 – odwołanie do kolumny Świętkorzyskie z zapytania tRegiony_k
Rys nr 11 – odwołanie do kolumny Świętokrzyskie z zapytania tRegiony_k

Po zatwierdzeniu formuły otrzymamy dane, gdzie każde wystąpienie sprzedawcy z regionu świętokrzyskiego zostanie rozpoznane. To odwołanie działa dla pierwszej reguły, którą stworzyliśmy dla kolumny warunkowej. Analogicznie możemy zmienić drugą regułę – dla regionu Mazowieckiego. Zapis formuły będzie wyglądał następująco:

=Table.AddColumn(#"Zmieniono typ", "Główny region", each if List.Contains (tRegiony_k [Świętokrzyskie], [Sprzedawca]) then [Świętokrzyskie] else if List.Contains (tRegiony_k [Mazowieckie] then [Mazowieckie] else null))

Power Query przeliczy dane po zatwierdzeniu kolumny i otrzymamy dane przedstawione na rys. nr 12.

Rys. nr 12 – dane po użyciu w zapisie formuły funkcji List.Contains
Rys. nr 12 – dane po użyciu w zapisie formuły funkcji List.Contains

W danych mamy jednego sprzedawcę (Beata), który nie znajduje się na żadnej z list. Formuła zadziała tak, że otrzymamy dla tego sprzedawcy wartość null. Dzieje się tak dlatego, że w kolumnie warunkowej wpisaliśmy, że jeśli warunki nie zostaną spełnione kolumna warunkowa ma nam zwrócić wartość null ( parametr W przeciwnym razie na rys. nr 7).

Tak przekształcone dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do  z karty Narzędzia główne (rys. nr 13).

Rys. nr 13 – polecenie Zamknij i załaduj do
Rys. nr 13 – polecenie 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 – konkretny arkusz oraz wskazujemy konkretną komórkę. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (rys. nr 14).

Rys. nr 14 – okno Importowania danych
Rys. nr 14 – okno Importowania danych

Otrzymamy dane zaimportowane do Excela i przedstawione na rys. nr 15.

Rys. nr 15 – Dane zaimportowane do Excela
Rys. nr 15 – Dane zaimportowane do Excela

Jak widać na rysunku powyżej dane z odpowiednich kolumn są pobierane do kolumny warunkowej Główny region.


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

Excel Power Query 56 — Usuwanie ulic z adresów

W dzisiejszym poście omówimy usuwanie części adresów, czyli np. nazw ulic czy placów. Rozwiążemy to zadanie w Power Query. To samo zagadnienie omawialiśmy w poradzie 369, ale za pomocą Excela. Zagadnienie to omówimy na podstawie przykładowych danych przedstawionych na rys. nr 1.

Rys. nr 1 – Przykładowe dane do zadania
Rys. nr 1 – Przykładowe dane do zadania

Przykładowe dane musimy zaczytać do Power Query za pomocą polecenia 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 adresami, następnie na tych danych wykonamy przekształcenia, aby otrzymać pożądany efekt. Przekształcenia, które wykonywaliśmy ręcznie w Excelu, czyli zamiana poszczególnych nazw ulic, alei i placów na ulice (ujednolicenie danych), w Power Query możemy zapisać jako kroki. Nawet kiedy nasze dane się zmienią, Power Query znów zamieni te instancje na ulice i będziemy mogli wyciągnąć tylko miasto z naszych danych.

Zaznaczamy kolumnę Miasto, Adres i wybieramy polecenie Zamienianie wartości z karty Narzędzia główne (rys. nr 3).

Rys. nr 3 – polecenie Zamienianie wartości
Rys. nr 3 – polecenie Zamienianie wartości

Polecenia tego musimy użyć dwukrotnie. Otworzy nam się okno Zamieniania wartości, gdzie w polu Wartość do znalezienia wpisujemy pl. (oznaczenie placu w adresie), następnie w polu Zamień na wpisujemy ul. (oznaczenie ulicy). Tak ustawione parametry zamieniania wartości zatwierdzamy przyciskiem OK (rys. nr 4).

Rys. nr 4 – okno Zamieniania wartości
Rys. nr 4 – okno Zamieniania wartości

Otrzymamy dane, gdzie wszystkie wystąpienia skrótu pl. zostały zamienione na ul. Następnie musimy powtórzyć te czynności, czyli wybieramy polecenie Zamienianie wartości z karty Narzędzia główne. Otworzy nam się okno Zamieniania wartości, gdzie znowu musimy ustawić jego parametry. Tym razem w polu Wartość do znalezienia wpisujemy al. (oznaczenie alei), a w polu Zamień na wpisujemy ul. (analogicznie jak na rys. nr 4).

Otrzymamy zamienione dane przedstawione na rys. nr 5.

Rys. nr 5 – Zamienione dane
Rys. nr 5 – Zamienione dane

Otrzymaliśmy dane, gdzie różne określenia adresów zostały ujednolicone, zamienione na skrót od ulicy (ul.). Teraz wystarczy wyciągnąć z danych tekst przed ulicą.  W tym celu rozwijamy polecenie Wyodrębnij (punkt 2 na rys. nr 6) z karty Przekształć (punkt 1), a następnie wybieramy polecenie Tekst przed ogranicznikiem (punkt 3).

Rys. nr 6 – ścieżka dostępu do polecenia Test przed ogranicznikiem
Rys. nr 6 – ścieżka dostępu do polecenia Test przed ogranicznikiem

Otworzy nam się okno Tekstu przed ogranicznikiem, gdzie w polu Ogranicznik wpisujemy skrót ul. (ujednolicone oznaczenie ulicy w adresie). Tak ustawiony ogranicznik zatwierdzamy przyciskiem OK (rys. nr 7).

Rys. nr 7 – okno Testu przed ogranicznikiem
Rys. nr 7 – okno Testu przed ogranicznikiem

Otrzymamy wyodrębnione dane z nazwami miast, po usunięciu niepotrzebnego tekstu przedstawioną na rys. nr 8.

Rys. nr 8 – Wyodrębnione dane
Rys. nr 8 – Wyodrębnione dane

Musimy pamiętać, że mieliśmy takie adresy, gdzie po nazwie miast była spacja, a dopiero potem nazwa ulicy, jak również sytuacje, kiedy tej spacji nie było w ogóle. Na końcu nazw niektórych miast pozostały spacje, które są nam niepotrzebne (mimo że ich nie widać). W tym celu rozwijamy polecenie Format (punkt 2 na rys. nr 9) z karty Przekształć (punkt 1), a następnie wybieramy polecenie Przycięcie (punkt 3).

Rys. nr 9 – ścieżka dostępu do polecenia Przycięcie
Rys. nr 9 – ścieżka dostępu do polecenia Przycięcie

Polecenie Przycięcie usuwa zbędne spacje na końcu tekstu. Otrzymamy przycięte dane przedstawione na rys. nr 10.

Rys. nr 10 – przycięte dane (po usunięciu spacji na końcu tekstu)
Rys. nr 10 – przycięte dane (po usunięciu spacji na końcu tekstu)

Możemy zrobić jeszcze jedno przekształcenie, mianowicie przy dwuczłonowych nazwach miast powinien być myślnik pomiędzy słowami (Skarżysko – Kamienna). W tym celu wybieramy polecenie Zamienianie wartości z karty Narzędzia główne (jak na rys. nr 3). Otworzy nam się okno Zamieniania wartości, gdzie w polu Wartość do znalezienia wpisujemy spację, a w polu Zamień na wpisujemy myślnik (-). Tak ustawione parametry zamieniania wartości zatwierdzamy przyciskiem OK (rys. nr 11).

Rys. nr 11 – okno Zamieniania wartości
Rys. nr 11 – okno Zamieniania wartości

Otrzymamy zmienione dane. W kolejnym kroku powinniśmy zmienić nazwę kolumny, ponieważ po przekształceniach zawiera ona tylko nazwę miasta. Klikamy dwukrotnie na tytuł kolumny i wpisujemy nową nazwę, czyli Miasto (rys. nr 12).

Rys. nr 12 – Zmiana nazwy kolumny
Rys. nr 12 – Zmiana nazwy kolumny

Otrzymamy dane przedstawione na rys. nr 13.

Rys. nr 13 – Dane po przekształceniach
Rys. nr 13 – Dane po przekształceniach

Po tych wszystkich krokach przekształceń, powinniśmy otrzymać te same wyniki, co w poradzie nr 369. Dzięki temu, że zadanie to wykonaliśmy w Power Query i mamy zapisane poszczególne kroki to w sytuacji, kiedy zmieniłyby nam się dane, wystarczy odświeżyć dane aby otrzymać aktualne, poprawne wyniki. Tak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 14).

Rys. nr 14 – polecenie Zamknij i załaduj do
Rys. nr 14 – polecenie Zamknij i załaduj do

W Excelu otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabel i wskazujemy miejsce wstawienia danych – istniejący arkusz oraz wskazujemy konkretną komórkę. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (rys. nr 15).

Rys. nr 15 – okno Importowania danych
Rys. nr 15 – okno Importowania danych

Otrzymamy dane wstawione do Excela z wyciągniętymi nazwami miast z całych adresów przedstawione na rys. nr 16.

Rys. nr 16 – Dane wstawione do Excela
Rys. nr 16 – Dane wstawione 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

Excel Power Query 55 — Automatyczne usuwanie pustych kolumn

W tym poście zajmiemy się usuwaniem pustych kolumn w Power Query. Do mojego kursu na Udemy dostałem zapytanie czy jest możliwe szybkie usunięcie pustych kolumn z danych. W tego typu sytuacjach sam szukam odpowiedzi w gogle. Rozwiązanie znalazłem na stronie www.community.powerbi.com, mianowicie kod funkcji, którą będziemy mogli wykorzystać w naszym zadaniu (rys. nr 1).

 Rys. nr 1 – kod funkcji do usuwania pustych kolumn
Rys. nr 1 – kod funkcji do usuwania pustych kolumn 

Kopiujemy funkcję za pomocą skrótu klawiszowego Ctrl+C, a następnie wklejamy za pomocą skrótu Ctrl+V do Notepada ++, gdzie możemy podejrzeć elementy funkcji w kolorach (rys. nr 2).

Rys. nr 2 – formuła funkcji do usuwania pustych kolumn w Notepadzie ++
Rys. nr 2 – formuła funkcji do usuwania pustych kolumn w Notepadzie ++

Co istotne w tym zapisie – funkcja pobiera nam dane jako tabelę i usuwa puste kolumny z danych. Pobranej funkcji możemy użyć w Power Query. Załóżmy, że mamy przykładowe dane pokazane na rys. nr 3. Wynika z nich że zawierają one dużo pustych kolumn.

Rys. nr 3 – Przykładowe dane
Rys. nr 3 – Przykładowe dane

Rozwijamy polecenie Pobierz dane (punkt 2 na rys. nr 4) z karty Dane (punkt 1), następnie rozwijamy polecenie Z innych źródeł (punkt 3) i wybieramy polecenie Puste zapytanie (punkt 4).

Rys. nr 4 – ścieżka dostępu do polecenia Puste zapytanie
Rys. nr 4 – ścieżka dostępu do polecenia Puste zapytanie

Otworzy nam się pusty Edytor zapytań Power Query, gdzie musimy wkleić nasz kod M, czyli zapis funkcji skopiowany z Notepada++. Wybieramy polecenie Edytor zaawansowany z karty Narzędzia główne (rys. nr 5).

Rys. nr 5 – polecenie Edytor zaawansowany
Rys. nr 5 – polecenie Edytor zaawansowany

Otworzy nam się okno Edytora zaawansowanego, gdzie wklejamy za pomocą skrótu klawiszowego Ctrl+V naszą skopiowaną wcześniej funkcję (rys. nr 6). Zapis powinien wyglądać następująco:

(tbl as table) =>

let

            Headers = Table.ColumnNames(tbl),

            Result = Table.SelectColumns(tbl),

List.Select(Headers, each List.MatchesAny(Table.Column(tbl,_),

each_ <> null)))

in

Result

Rys. nr 6 – Edytor zaawansowany z zapisem funkcji
Rys. nr 6 – Edytor zaawansowany z zapisem funkcji

Wklejoną funkcję zatwierdzamy w Edytorze zaawansowanym przyciskiem Gotowe. Otrzymamy gotową funkcję przedstawioną na rys. nr 7.

Rys. nr 7 – Funkcja w Power Query
Rys. nr 7 – Funkcja w Power Query

Nie ma znaczenia, że zapis tej funkcji był w języku angielskim. Istotne jest, że ta funkcja pobiera tabelę i odpowiednio ją przekształca. Nasze zapytanie nazywa się Zapytanie 1, więc zmienimy jego nazwę w Ustawieniach zapytania na DeleteEmptyColumns, czyli Usuń puste kolumny. Co istotne nasze zapytanie ma postać funkcji co widać przy nazwie zapytania – skrót fx (rys. nr 8).

Rys. nr 8 – Zapytanie w postaci funkcji – oznaczenie fx
Rys. nr 8 – Zapytanie w postaci funkcji – oznaczenie fx

Sposób, którego używamy jest najprostszym sposobem użycia tej funkcji, czyli zaczytanie pustego zapytania i wstawienie do niego funkcji, następnie zaczytanie danych z Excela w formie tabeli do drugiego zapytania. Nie wychodząc z Power Query możemy rozwinąć sobie polecenie Nowe źródło (punkt 1 na rys. nr 9) na karcie Narzędzia główne, następnie rozwinąć polecenie Plik (punkt 2) i wybrać polecenie Excel (punkt 3).

Rys. nr 9 – ścieżka dostępu do polecenia Excel
Rys. nr 9 – ścieżka dostępu do polecenia Excel

Otworzy nam się okno Importowania danych, w którym musimy znaleźć plik z naszymi przykładowymi danymi z rys. nr 3. Wybieramy plik i klikamy na niego dwukrotnie lub zatwierdzamy nasz wybór przyciskiem Otwórz (rys. nr 10).

Rys. nr 10 – Okno Importowania danych
Rys. nr 10 – Okno Importowania danych

Otworzy nam się okno Nawigatora, gdzie na liście elementów wybieramy jeden arkusz, ten który chcemy przekształcić – o nazwie Dane. Nasz wybór zatwierdzamy przyciskiem OK (rys. nr 11).

Rys. nr 11 – okno Nawigator
Rys. nr 11 – okno Nawigator

Arkusz ten zostanie zaczytany do Power Query jako odrębne zapytanie co widać na rys. nr 12.

Rys. nr 12 – Arkusz zaczytany jako odrębne zapytanie
Rys. nr 12 – Arkusz zaczytany jako odrębne zapytanie

Dodatkowo możemy usunąć krok Zmieniono typ z Zastosowanych kroków (Ustawienia zapytania), ponieważ krok ten nic nie wnosi do naszych danych.

Aby usunąć puste kolumny wystarczy przejść na zapytanie z naszą funkcją i w polu tbl (Wprowadź parametr) wybrać naszą zaczytaną tabelę z drugiego zapytania. Wybór tabeli zatwierdzamy przyciskiem Wywołaj (rys. nr 13).

Rys. nr 13 – Wprowadzanie parametru funkcji (wybór tabeli z drugiego zapytania)
Rys. nr 13 – Wprowadzanie parametru funkcji (wybór tabeli z drugiego zapytania)

W wyniku zadziałania funkcji otrzymamy dane w formie tabeli, z których zostały usunięte wszystkie puste kolumny (rys. nr 14).

Rys. nr 14 – Dane w formie tabeli po usunięciu pustych kolumn
Rys. nr 14 – Dane w formie tabeli po usunięciu pustych kolumn

Co istotne do poprawnego zadziałania tej funkcji musi być całkowicie pusta kolumna (np. kolumna nr 7 ma test w przedostatnim wierszu i już nie została usunięta). Po nagłówkach kolumn doskonale widać jak dużo kolumn zostało usuniętych.

Podsumowując pokazaliśmy sposób usunięcia pustych kolumn w Power Query znaleziony w internecie. Jeśli jesteś bardziej zaawansowanym użytkownikiem możesz zaczytać dane do Power Query i wkleić tą funkcję pod razu w jednym zapytaniu. Innym sposobem wywołania funkcji jest polecenie Wywołaj funkcję niestandardową z karty Dodaj kolumnę (rys. nr 15).

Rys. nr 15 – polecenie Wywołaj funkcję niestandardową
Rys. nr 15 – polecenie Wywołaj funkcję niestandardową

Tak przygotowane dane musimy załadować do Excela, w tym celu wybieramy polecenie Zamknij i załaduj z karty Narzędzia główne (rys. nr 16).

Rys. nr 16 – polecenie Zamknij i załaduj
Rys. nr 16 – polecenie Zamknij i załaduj

Nie przekształcaliśmy dodatkowo danych, zależało nam jedynie na usunięciu pustych kolumn z danych. Zapytania zostały załadowane do Excela jako połączenia (ze względu na to, że mamy kilka zapytań) – rys. nr 17.

Rys. nr 17 – Zapytania załadowane do Excela jako połączenia
Rys. nr 17 – Zapytania załadowane do Excela jako połączenia

Zaznaczamy zapytanie Wywołano funkcję i klikamy na nie prawym przyciskiem myszy, następnie z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 18).

Rys. nr 18 – polecenie Załaduj do
Rys. nr 18 – polecenie Załaduj do

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

Rys. nr 19 – okno Importowania danych
Rys. nr 19 – okno Importowania danych

Otrzymamy dane wstawione do Excela przedstawione na rys. nr 20.

Rys. nr 20 – Dane zaczytane do Excela
Rys. nr 20 – Dane zaczytane do Excela

Tak przygotowane dane wymagają jeszcze obróbki, mianowicie uporządkowania ich, usunięcia zbędnych wierszy i zbędnych informacji. Najważniejsze że udało nam się wykonać pierwszy krok, czyli automatyczne usuwanie niepotrzebnych kolumn za pomocą funkcji znalezionej w internecie.


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