Excel Power Query #60 — Pobieranie danych z pliku pdf do Excela

W dzisiejszym poście nauczymy się jak pobrać dane z pliku PDF do Power Query. Omówimy tutaj dwie możliwości. W Excelu, Power Query nie ma możliwości pobrania danych bezpośrednio z pliku PDF, natomiast taka możliwość pojawia się w Power BI Desktop (drugie rozwiązanie). Pierwsze rozwiązanie będzie wykonane na podstawie filmu znalezionego w sieci, gdzie najpierw przekształcimy dane z pliku PDF do takiego formatu, który Power Query będzie w stanie pobrać. Załóżmy, że mamy przykładowy plik PDF jak na rys. nr 1.

Rys. nr 1 – przykładowy plik pdf do pobrania
Rys. nr 1 – przykładowy plik pdf do pobrania

Musimy ten plik otworzyć za pomocą WORDA, czyli klikamy na niego prawym przyciskiem myszy i z podręcznego menu rozwijamy polecenie Otwórz za pomocą, a następnie wybieramy polecenie Wybierz inną aplikację (nie mamy na domyślnej liście programu WORD) – rys. nr 2.

Rys. nr 2 – polecenie Wybierz inną aplikację
Rys. nr 2 – polecenie Wybierz inną aplikację

Otworzy się okno o nazwie Jak chcesz otworzyć ten plik, gdzie musimy znaleźć na liście program WORD, zaznaczyć go i nasz wybór zatwierdzić przyciskiem OK (rys. nr 3).

 Rys. nr 3 – okno o nazwie Jak chcesz otworzyć ten plik?
Rys. nr 3 – okno o nazwie Jak chcesz otworzyć ten plik?

Po zatwierdzeniu otwarcia pliku PDF w programie Word, plik PDF zostaje przekonwertowany na edytowalny dokument tekstowy. Funkcjonalność ta działa od WORDA 2016, we wcześniejszej wersji nie działało. Pojawi nam się okno , gdzie musimy zatwierdzić decyzję, że chcemy przekształcić plik PDF za pomocą przycisku OK. Otworzy nam się WORD z plikiem przedstawionym na rys. nr 4.

Rys. nr 4 – plik pdf otwarty w programie WORD
Rys. nr 4 – plik pdf otwarty w programie WORD

Mamy dane w wordzie, razem z tabelkami, jakie nas interesują. Nie potrzebujemy danych z Worda tylko danych zapisanych jako html, czyli stronę web. Przechodzimy do menu Plik, następnie wybieramy polecenie Zapisz jako (rys. nr 5).

Rys. nr 5 – polecenie Zapisz jako
Rys. nr 5 – polecenie Zapisz jako

Otworzy nam się panel Zapisywania jako, gdzie możemy wybrać lokalizację w jakiej chcemy zapisać plik, zmienić tytuł (nazwę) pliku a przede wszystkim wybrać typ pliku. Rozwijamy listę z rodzajami plików i wybieramy Strona sieci Web (rys. nr 6).

Rys. nr 6 – typ pliku Strona sieci Web
Rys. nr 6 – typ pliku Strona sieci Web

Po wybraniu odpowiedniego typu pliku, a ze względu na to że żyjemy w Polsce, musimy rozwinąć polecenie Więcej opcji (zaznaczone strzałką na rys. nr 7).

Rys. nr 7 – polecenie Więcej opcji
Rys. nr 7 – polecenie Więcej opcji

Otworzy nam się okno Zapisz jako, gdzie w menu Narzędzia wybieramy polecenie Opcje sieci Web (rys. nr 8).

Rys. nr 8 – Opcje sieci Web
Rys. nr 8 – Opcje sieci Web

Otworzy nam się okno Opcji sieci Web, gdzie przechodzimy na zakładkę Kodowanie, a następnie upewniamy się że wybrane jest kodowanie Unicode (UTF‑8), żeby Power Query mógł to prawidłowo odczytać. Tak ustawione kodowanie zatwierdzamy przyciskiem OK (rys. nr 9).

Rys. nr 9 – kodowanie Unicode (UTF-8)
Rys. nr 9 – kodowanie Unicode (UTF‑8)

Automatycznie nastąpi powrót do okna Zapisz jako, gdzie ustawione parametry zatwierdzamy przyciskiem Zapisz. Word stworzył nam plik html i dopiero ten plik będziemy mogli zaczytać do Power Query.

Przy pobieraniu danych Power Query może mieć problem z polskimi znaczkami, dlatego tak ważne było ustawienie kodu UTF‑8. Rozwijamy polecenie Z pliku (punkt 2 na rys. nr 10) z karty Dane (punkt 1), a następnie wybieramy polecenie Ze skoroszytu (punkt 3).

Rys. nr 10 – ścieżka dostępu do polecenia Ze skoroszytu
Rys. nr 10 – ścieżka dostępu do polecenia Ze skoroszytu

Otworzy nam się okno Importowania danych, gdzie na dole obok wybranej Nazwy pliku musimy wybrać opcję Wszystkie pliki a nie tylko pliki Excel. Dzięki tej opcji wyświetlą nam się wszystkie pliki w tym katalogu. Wybieramy plik html i zatwierdzamy nasz wybór przyciskiem Importuj (rys. nr 11).

Rys. nr 11 – okno Importowania danych
Rys. nr 11 – okno Importowania danych

Power Query domyśli się że nie importujemy pliku Excela i otworzy nam okno Nawigatora, gdzie możemy wybrać poszczególne elementy, które chcemy zaczytać do Power Query. Wybieramy tabelę nr 2, następnie przechodzimy do Power Query za pomocą przycisku Przekształć dane. Naszym zadaniem było tylko zaczytanie danych a nie ich przekształcanie więc możemy dane na tym etapie od razu załadować do Excela za pomocą przycisku Załaduj (rys. nr 12).

Rys. nr 12 – okno Nawigator, gdzie wybieramy dane, które chcemy zaczytać do Power Query
Rys. nr 12 – okno Nawigator, gdzie wybieramy dane, które chcemy zaczytać do Power Query

Otrzymamy wybrane dane załadowane do Excela przedstawione na rys. nr 13, dane w dokładnie takiej postaci, w jakiej były w pliku PDF.

Rys. nr 13 – wybrane  dane zaczytane do Excela
Rys. nr 13 – wybrane dane zaczytane do Excela

Istotne jest, że dzięki ustawieniu odpowiedniego kodowania widać polskie znaki w danych. W razie potrzeby dane te możemy edytować za pomocą polecenia Edytuj z karty Zapytanie (rys. nr 14).

Rys. nr 14 – polecenie Edytuj
Rys. nr 14 – polecenie Edytuj

Pierwsze rozwiązanie jest dość długie, ale nie wymaga instalacji dodatkowego oprogramowania, wszystkie działania wykonujemy w pakiecie Microsoft Office.

Drugie rozwiązanie wykorzystuje Power BI Desktop, gdzie rozwijamy polecenie  Pobierz dane  z karty Strona główna, następnie wybieramy polecenie Więcej (rys. nr 15). Power Bi ma możliwość pobierania danych bezpośrednio z pliku pdf.

Rys. nr 15 – Więcej opcji pobierania danych
Rys. nr 15 – Więcej opcji pobierania

Otworzy nam się okno Pobierania danych, gdzie w zakładce Wszystkie wyszukujemy plik typu PDF. Wybór rodzaju pliku do pobrania zatwierdzamy przyciskiem Połącz (rys. nr 16).

Rys. nr 16 – okno Pobierania danych, gdzie wybieramy typ pliku jaki chcemy pobrać
Rys. nr 16 – okno Pobierania danych, gdzie wybieramy typ pliku jaki chcemy pobrać

Otworzy się okno Otwieranie, gdzie musimy wskazać plik pdf, z którego chcemy pobrać dane. Power Bi pamięta ścieżkę dostępu do pliku, na którym wykonujemy poszczególne czynności w tym odcinku. Zaznaczamy plik, który chcemy pobrać i klikamy dwukrotnie lub zatwierdzamy przyciskiem Otwórz (rys. nr 17).

Rys. nr 17 – okno Otwieranie, gdzie wybieramy plik do pobrania
Rys. nr 17 – okno Otwieranie, gdzie wybieramy plik do pobrania

Trochę dłużej trwa wczytywanie (łączenie) niż dla standardowego pliku czy to z Excela, czy np. html, ale Power Bi radzi sobie z wyciąganiem danych z pliku pdf. Otworzy nam się okno Nawigator z wyświetlonymi elementami pliku pdf. Nas interesuje ta tabelka, która wybieraliśmy w pierwszym przykładzie, czyli tutaj Table004 (page 3) i dodatkowo inna tabelka, aby pokazać działanie pobierania danych. Nasze wybrane tabelki zatwierdzamy przyciskiem Załaduj (rys. nr 18).

Rys. nr 18 – okno Nawigatora, gdzie wybieramy interesujące nas elementy pliku pdf
Rys. nr 18 – okno Nawigatora, gdzie wybieramy interesujące nas elementy pliku pdf

Podsumowując pobieramy dwie tabelki z pliku pdf. Ważne jest, że plik pdf nie może być obrazkowy, tzn. nie mogą to być rysunki (zdjęcia) tabel tylko fizycznie utworzone tabelki np. w pliku Word lub Excel. Jeśli mamy do czynienia z plikiem pdf, gdzie znajdują się pliki jpg, czyli zdjęcia jakichś elementów to nic nie jest w stanie odczytać takich danych, przynajmniej ja nie znam takiego programu. Otrzymamy zaczytane do Power Bi dwie tabelki (rys. nr 19).

Rys. nr 19 – tabelki zaczytane do Power Bi
Rys. nr 19 – tabelki zaczytane do Power Bi

Jeśli chcemy je skopiować do Excela, to wystarczy kliknąć w obszar danej tabelki prawym przyciskiem myszy i z podręcznego menu wybrać polecenie Kopiuj tabelę (rys. nr 20).

Rys. nr 20 – polecenie Kopiuj tabelę z podręcznego menu
Rys. nr 20 – polecenie Kopiuj tabelę z podręcznego menu

Po skopiowaniu tabeli wystarczy przejść do okna Excela i ją wkleić za pomocą skrótu klawiszowego Ctrl+V. Dane zostały wklejone do Excela identycznie jak wyglądały w programie Power Bi (rys. nr 21).

Rys. nr 21 – Tabelka wklejona do Excela
Rys. nr 21 – Tabelka wklejona do Excela

Możemy wrócić do Power BI i zrobić szybkie filtrowanie danych, mianowicie kliknąć w ikonkę trójkąta obok nazwy województwa i odznaczyć checkboxy przy nazwach województw, które nas w tym momencie nie interesują. Parametry filtru zatwierdzamy przyciskiem OK (rys. nr 22).

Rys. nr 22 – filtrowanie danych w tabeli
Rys. nr 22 – filtrowanie danych w tabeli

Otrzymamy przefiltrowane dane przedstawione na rys. nr 23.

Rys. nr 23 – przefiltrowane dane w tabeli
Rys. nr 23 – przefiltrowane dane w tabeli

Przygotowaliśmy sobie przefiltrowaną tabelkę, na którą klikamy prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Kopiuj tabelę jak na rys. nr 20. Następnie przechodzimy do Excela, ustawiamy aktywną komórkę obok poprzednio wklejonej tabelki i za pomocą skrótu klawiszowego Ctrl+V, wklejamy przefiltrowaną tabelkę (rys. nr 24).

Rys. nr 24 – Druga tabelka wklejona do Excela (z uwzględnionymi filtrami)
Rys. nr 24 – Druga tabelka wklejona do Excela (z uwzględnionymi filtrami)

Podsumowując Power Bi tak samo jak Power Query przy kopiowaniu danych uwzględnia nałożone wcześniej filtry. Jest to istotne, szczególnie przy pobieraniu danych z pliku pdf.


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 #59 — Lista wszystkich podfolderów we wskazanym katalogu

W dzisiejszym poście omówimy jak za pomocą Power Query możemy wyciągnąć listę wszystkich folderów, podfolderów wskazanego folderu. Rozwiązanie to jest proste, ale ma jedną wadę, o której pomówimy na końcu. W celu wykonania zadania rozwijamy polecenie Pobierz dane (punkt 2 na rys. nr 1) z karty Dane (punkt 1), następnie rozwijamy polecenie Z pliku (punkt 3) i wybieramy polecenie Z folderu (punkt 4).

Rys. nr 1 – ścieżka dostępu do polecenie Z folderu
Rys. nr 1 – ścieżka dostępu do polecenie Z folderu

Na potrzeby naszego zadania przygotowałem katalog z większą ilością podfolderów o nazwie WebinarPQ. Po użyciu polecenia z rys. nr 1 otworzy nam się okno Folder, gdzie musimy wkleić ścieżkę dostępu do tego folderu lub przy użyciu przycisku Przeglądaj, wyszukać folder ręcznie. Wklejoną ścieżkę do wybranego folderu zatwierdzamy przyciskiem OK (rys. nr 2).

Rys. nr 2 – okno Folder
Rys. nr 2 – okno Folder

Wtedy Power Query pobierze informacje o wszystkich plikach znajdujących się w folderze głównym i jego pod folderach. Otworzy się okno z wybranej ścieżki dostępu, gdzie wystarczy kliknąć przycisk Przekształć dane, aby przejść do ich edycji (rys. nr 3). Dzięki drobnym przekształceniom danych otrzymamy listę wszystkich elementów wybranego folderu.

Rys. nr 3 – okno z elementami wybranego folderu
Rys. nr 3 – okno z elementami wybranego folderu

Otworzy nam się Edytor zapytań Power Query z wczytanymi danymi przedstawiony na rys. nr 4.

Rys. nr 4 – fragment danych z edytora zapytań Power Query
Rys. nr 4 – fragment danych z edytora zapytań Power Query

Dla nas istotna jest tylko ostatnia kolumna o nazwie Folder Path, inne są zbędne ponieważ zawierają ind=formacje na temat poszczególnych plików. Klikamy na nazwę kolumny Folder Path prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 5).

Rys. nr 5 – polecenie Usuń inne kolumny
Rys. nr 5 – polecenie Usuń inne kolumny

Otrzymamy listę wszystkich folderów i pod folderów zawartych w katalogu WebinarPQ przedstawioną  na rys. nr 6.

Rys. nr 6 – lista wszystkich folderów i pod folderów katalogu WebinarPQ
Rys. nr 6 – lista wszystkich folderów i pod folderów katalogu WebinarPQ

Rys. nr 7 – polecenie Usuń duplikaty Jak łatwo zauważyć poszczególne ścieżki się powtarzają, więc musimy je usunąć. Zaznaczamy kolumnę i rozwijamy polecenie Usuń wiersze a następnie wybieramy polecenie Usuń duplikaty (rys. nr 7).

Rys. nr 7 – polecenie Usuń duplikaty
Rys. nr 7 – polecenie Usuń duplikaty

Otrzymamy listę ścieżek dostępu do wszystkich pod folderów wybranego katalogu przedstawioną na rys. nr 8.

Rys. nr 8 – Lista ścieżek dostępu do wszystkich pod folderów wybranego katalogu
Rys. nr 8 – Lista ścieżek dostępu do wszystkich pod folderów wybranego katalogu

Jeśli potrzebujemy jakieś dodatkowe dane to możemy dalej przekształcać te dane. Naszym zadaniem było przygotowanie listy wszystkich pod folderów. Nasza lista zawiera również główny folder, czyli WebinarPQ, możemy go usunąć rozwijając podręcznego menu (przycisk z trójkącikiem przy nazwie kolumny), a następnie odznaczając checkbox przy ścieżce dostępu do głównego katalogu. Zmiany w filtrowaniu zatwierdzamy przyciskiem OK (rys. nr 9).

Rys. nr 9 – odfiltrowanie danych (usunięcie ścieżki dostępu do głównego katalogu)
Rys. nr 9 – odfiltrowanie danych (usunięcie ścieżki dostępu do głównego katalogu)

Otrzymamy dane z listą ścieżek dostępu do pod folderów, natomiast bez ścieżki dostępu do głównego katalogu (rys. nr 10).

Rys. nr 10 – lista ścieżek dostępu do pod folderów katalogu WebinarPQ
Rys. nr 10 – lista ścieżek dostępu do pod folderów katalogu WebinarPQ

Rys. nr 11 – polecenie Zamknij i załaduj doTak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 11).

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

Otworzy nam się okno Importowania danych, gdzie ustawiamy sposób wyświetlania danych jako tabela i wskazujemy miejsce ich wstawienia, czyli istniejący arkusz i konkretna komórka. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (rys. nr 12).

Rys. nr 12 – okno Importowania danych
Rys. nr 12 – okno Importowania danych

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

Rys. nr 13 – Dane zaimportowane z Power Query
Rys. nr 13 – Dane zaimportowane z Power Query

Jak wspominaliśmy na początku, rozwiązanie to jest proste, lecz ma jedną wadę. Mianowicie jeśli podejrzymy pliki w folderze WebinarPQ, widzimy że mamy dodatkowy folder o nazwie Nowy folder, który nie pojawił się na naszej liście (rys. nr 14). Dzieje się tak dlatego, że folder ten jest pusty.

Rys. nr 14 – pod foldery katalogu WebinarPQ
Rys. nr 14 – pod foldery katalogu WebinarPQ

Pusty folder nie pojawił się na liście otrzymanej przy użyciu funkcji, która wyciąga informacje o wszystkich plikach znajdujących się we wskazanym folderze, ponieważ nie zawierał żadnych plików. Podsumowując przedstawiony sposób działa poprawnie w 90%, ale musimy pamiętać o pustych folderach.


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