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
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ę
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?
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
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
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
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
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
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)
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
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
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
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
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
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
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ć
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
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
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
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
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
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
Otrzymamy
przefiltrowane dane przedstawione na rys. nr 23.
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)
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.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście zajmiemy się błędem, który pojawia się przy pobieraniu danych, kiedy wcześniej nałoży się filtr. Załóżmy, że mamy w przykładowych danych zakres, który nie jest tabelą. Zaznaczamy ten zakres i będziemy chcieli go nazwać — po prostu NazwanyZakres (w miejscu oznaczonym strzałką na rys. nr 1).
rys. nr 1 — nazywanie zaznaczonego zakresu
Nazwanie
zakresu pozwala nam wrócić do niego z każdego miejsca w arkuszu (z każdej
komórki) rozwijając okno, w którym nazywaliśmy ten zakres (rys. nr 2).
rys. nr 2 — powrót do nazwanego zakresu z każdego miejsca w arkuszu
Kiedy mamy
nazwany zakres możemy za pomocą polecenia Z tabeli/zakresu z karty Dane
pobrać go do Power Query (rys. nr 3).
rys. nr 3 — pobieranie danych do Power Query za pomocą polecenia Z tabeli/zakresu
W takim
przypadku Power Query nie ma żadnych problemów, pobiera dane prawidłowo.
Otworzy nam się Edytor zapytań z wczytanym zaznaczonym zakresem (rys. nr 4).
rys. nr 4 — edytor zapytań z wczytanym zakresem z Excela
Problem
pojawia się jeśli na ten nazwany zakres w Excelu nałożymy filtr, a następnie za
pomocą polecenia Z tabeli/zakresu z karty Dane wczytamy ten zakres do Power
Query. Otworzy nam się edytor zapytań Power Query, ale z błędem (rys. nr 5).
rys. nr 5 — edytor zapytań z wczytanym zaznaczonym zakresem z błędem
Błąd wynika
z tego, że Power Query nie odczytuje zakresu danych tylko próbuje odczytać
filtr, który jest nałożony na ten zakres (rys. nr 6).
rys. nr 6 — próba odczytania danych nie z zaznaczonego zakresu a z nałożonego filtru
Możemy
przyjrzeć się dokładniej funkcji Excel.CurrentWorkbook, gdzie widzimy, że przy
nazwie filtru jest błąd, ponieważ Power Query próbuje pobrać tabelę, której
nazwa nie istnieje (rys. nr 7).
rys. nr 7 — Próba pobrania danych z tabeli, której nazwa nie istnieje
Jako tester
lubię sobie czasem poeksperymentować, poszukać sposobu na obejście tego błędu.
W innym pliku rozwijamy polecenie Pobierz dane (punkt 2 na rys. nr 8) z
karty Dane (punkt 1), następnie rozwijamy polecenie Z pliku
(punkt 3) i wybieramy Ze skoroszytu (punkt 4).
rys. nr 8 — ścieżka dostępu do polecenia Ze skoroszytu
Otworzy nam
się okno Importowanie danych, gdzie wybieramy nasz plik klikając na niego dwa
razy (rys. nr 9).
rys. nr 9 — okno Importowania danych
Otworzy nam
się okno Nawigator, gdzie możemy podejrzeć zawartość skoroszytu, czyli jego
poszczególne elementy (rys. nr 10).
rys. nr 10 — okno Nawigator, gdzie możemy podejrzeć zawartość skoroszytu
Jeśli zaznaczymy sobie cały plik Excela i wciśniemy przycisk Edytuj, to zobaczymy wszystkie elementy i zakresy z tego pliku. Otworzy nam się edytor zapytań Power Query z listą tych wszystkich elementów. Tutaj Power Query użyje innej funkcji Excel.Workbook (rys. nr 11).
rys. nr 11 — Edytor zapytań z listą wszystkich elementów znajdujących się w skoroszycie
W naszych
danych nie interesują nas arkusze, czyli odfiltrujemy kolumnę Kind. W tym celu
klikamy na trójkąt przy nazwie kolumny Kind i odznaczamy checkbox przy nazwie
Sheet (czyli arkusz). Filtr zatwierdzamy przyciskiem OK (rys. nr 12).
rys. nr 12 — nakładanie filtru (odfiltrowanie arkuszy)
Otrzymamy
tabelę tylko ze zdefiniowanymi nazwami przedstawioną na rys. nr 13.
rys. nr 13 — tabela ze zdefiniowanymi nazwami
Interesuje
nas jeszcze kolumna Hidden, która pokazuje które elementy są ukryte. Filtr
nałożony na dane jest ukryty, natomiast nazwany zakres jest widoczny. Przy
pobieraniu danych Power Query sięga niestety po tą tabelę, która jest ukryta
(rys. nr 14).
rys. nr 14 — elementy ukryte — Hidden
W takim
rozwiązaniu Power Query nie ma problemu i poprawnie odczytuje dane.
Jak możemy
obejść ten błąd przy standardowym pobieraniu danych do Power Query?
Wyłączamy
filtr w naszym nazwanym zakresie. Jest on niewidoczny ale dalej się tam
znajduje. Stworzyliśmy tabelę pomocniczą z zakresem do filtrowania i właśnie na
nią nakładamy ten filtr za pomocą skrótu klawiszowego Ctrl+Shift+L (rys. nr 15).
rys. nr 15 — nakładanie filtru za pomocą skrótu klawiszowego Ctrl+Shift+L
Teraz jeśli
pobierzemy dane z tego zakresu za pomocą polecenia Z tabeli/zakresu z karty
Dane, otworzy nam się edytor zapytań Power Query z wczytanym zakresem (rys. nr 16).
rys. nr 16 — Edytor zapytań z wczytanym zakresem
W kroku
Źródło w Zastosowanych krokach możemy podejrzeć sobie funkcję użytą przez Power
Query. Możemy zauważyć, że dalej pojawia nam się błąd mimo, że dane zostały
wczytane (rys. nr 17).
rys. nr 17 — funkcja Excel.CorrentWorkbook
Sposobem na
zlikwidowanie błędu jest zmiana właściwości zakresu w VBA (rys. nr 18).
rys. nr 18 — zmiana właściwości zakresu w VBA
Wynika z
tego, że funkcja Power Query źle odwołuje się do zakresu i stąd wynika problem
(zły zapis zakresu – pojedynczy cudzysłów).
Podsumowując
celem tego filmu było pokazanie, że Power Query nie zawsze chce zwracać dane,
których potrzebujesz. Przede wszystkim w sytuacji kiedy nakładasz filtr na
nazwany zakres.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście policzymy liczbę arkuszy w pliku z podziałem na arkusze ukryte, widoczne i wszystkie. W odcinku VBA #24 ( https://exceliadam.pl/wp-admin/post.php?post=9634&action=edit ) wykonaliśmy to zadanie w VBA. W tej poradzie policzymy arkusze za pomocą dodatku do Excela – Power Query. Przykładowe wyniki naszej operacji możemy zobaczyć na rysunku nr 1.
rys. nr 1
Rozwijamy polecenie Pobierz dane (punkt nr 2 na rysunku nr 2)
z karty Dane (grupa poleceń Pobieranie i przekształcanie danych), następnie
rozwijamy polecenie Z pliku (punkt nr 3) i wybieramy polecenie Ze skoroszytu
(punkt nr 4).
rys. nr 2 — Ze skoroszytu
Otworzy nam się okno Importowania danych, gdzie musimy
odszukać nasz plik (odpowiedni dla tego postu). Wybieramy plik i zatwierdzamy
nasz wybór przyciskiem Importuj (rys. nr 3).
rys. nr 3 — Importowanie danych
Otworzy nam się okno Nawigatora, gdzie wybieramy dowolny
arkusz z pliku, a następnie zatwierdzamy wybór przyciskiem Edytuj (rys. nr 4).
Ten krok nie do końca jest właściwy, ale ze względu na to iż korzystamy z
interfejsu, ułatwia nam pracę.
rys. nr 4 — Nawigator
Otworzy nam się okno Edytora Power Query z pustym arkuszem,
który wybraliśmy. Nie potrzebujemy tutaj żadnych kroków, więc w panelu bocznym
Ustawień zapytania usuwamy z Zastosowanych kroków kroki: Zmieniono typ oraz
Nawigacja (oznaczone strzałką na rysunku nr 5).
rys. nr 5 — edytor zapytań
My potrzebujemy zachować tylko jeden krok, mianowicie Źródło.
Otrzymamy wtedy dane przedstawione na rysunku nr 6.
rys. nr 6 — Dane w edytorze zapytań
Power Query sam napisał ten krok, nie musiałem tego robić
sam, ponieważ jest on skomplikowany. Widać na pasku formuły cała ścieżkę
dostępu do tego pliku (rys. nr 7) oraz funkcję jaka jest potrzebna do wczytania
pliku Excela. Power Query wyciągnął również elementy jakie zawiera dany skoroszyt.
rys. nr 7 — Pasek formuły
Nasz plik Power Query jest plikiem "prostym",
ponieważ w kolumnie Kind ma elementy tylko jednego rodzaju – Sheet czyli arkusz.
Gdyby nasz plik zawierał tabele lub oznaczone zakresy to one również pojawiły
by się w tej tabeli.
Gdybyśmy mieli więcej rodzajów elementów w kolumnie Kind to
moglibyśmy przefiltrować dane, żeby otrzymać tylko te które nas interesują.
Rozwijamy ikonę trójkącika przy nazwie kolumny Kind i z podręcznego menu
rozwijamy polecenie Filtry tekstu a następnie wybieramy polecenie Równa się
(rys. nr 8).
rys. nr 8 — Filtry tekstu
Otworzy nam się okno Filtrowania wierszy, gdzie wpisujemy, że
chcemy zachować wiersze, w których Kind równa się Sheet. Tak wpisany parametr
zatwierdzamy przyciskiem OK (rys. nr 9).
rys. nr 9 — Filtrowanie wierszy
Nałożyliśmy dodatkowy filtr na wypadek gdyby w naszych danych
pojawiły się nowe elementy takie jak np. tabela. Na rysunku nr 6 w kolumnie
Hidden mamy zawartą informację, czy dany arkusz jest widoczny czy ukryty. False
dla widocznego arkusza, natomiast True dla arkusza ukrytego.
Aby na podstawie takiej tabelki zliczyć ilość wszystkich
arkuszy wybieramy polecenie Zlicz wiersze na karcie Przekształć (rys. nr 10).
rys. nr 10 — Zlicz wiersze
Otrzymamy wynik przedstawiony na rysunku nr 11. Ta postać
wyniku nie jest zbyt użyteczna do dalszych działań.
rys. nr 11
Chcemy później wykonać proste operacje więc wystarczy te dane
przekształcić do postaci tabeli. W tym celu wybieramy polecenie Do tabeli z
karty Przekształć (rys. nr 12).
rys. nr 12 — Do tabeli
Otrzymamy poprzedni wynik w formie tabelarycznej, co ułatwi
nam kolejne działania na tym wyniku (rys. nr 13).
rys. nr 13 — Dane
W kolejnym etapie chcemy policzyć arkusze ukryte i widoczne.
W tym celu musimy zduplikować nasze zapytanie. Klikamy prawym przyciskiem myszy
na nazwę zapytania PQ35 i z podręcznego menu wybieramy polecenie Duplikuj (rys.
nr 14).
rys. nr 14 — Duplikuj
W zduplikowanym zapytaniu pousuwać Zastosowane kroki do
momentu Przefiltrowano wiersze, ponieważ właśnie w tym kroku mamy informacje o
ukrytych i widocznych arkuszach. Otrzymamy dane przedstawione na rysunku nr 15.
rys. nr 15 — Dane
Najłatwiej policzyć ilość poszczególnych arkuszy grupując
dane. W tym celu wybieramy polecenie Grupowanie według z karty Narzędzia główne
(rys. nr 16).
rys. nr 16 — Grupowanie według
Otworzy nam się okno Grupowania według, gdzie możemy ustawić
parametry grupowania danych. Zmieniamy nazwę nowej kolumny na Liczba arkuszy,
wybieramy rodzaj operacji jako Zlicz wiersze i tak ustawione parametry
zatwierdzamy przyciskiem OK (rys. nr 17).
rys. nr 17 — Parametry grupowania
Otrzymamy zliczone arkusze ukryte i widoczne przedstawione na
rysunku nr 18.
rys. nr 18 — Pogrupowane dane
W kolejnym etapie chcemy zmienić nazwy elementów z Hidden, FALSE
i TRUE na widoczne i ukryte. Najprostszym sposobem jest dodanie nowej kolumny.
Zaznaczamy kolumnę Hidden i wybieramy polecenie Kolumna warunkowa z karty Dodaj
kolumnę (rys. nr 19).
rys. nr 19 — kolumna warunkowa
Otworzy nam się okno Dodawania kolumny warunkowej. W punkcie
oznaczonym na rysunku nr 20 numerem 1 zmieniamy nazwę nowej kolumny na Typ
arkuszy, następnie w polu Nazwa kolumny wybieramy Hidden (punkt nr 2). Operator
ustawiamy jako Równa się (punkt nr 3). W polu Wartość wpisujemy FALSE (punkt nr
4). Jeśli warunek się zgadza (ma wartość FALSE) to chcemy otrzymać Widoczny
(wartość wyjściowa – punkt nr 5). W przeciwnym wypadku chcemy otrzymać wartość
Ukryty (punkt nr 6). Tak ustawione parametry zatwierdzamy przyciskiem OK.
rys. nr 20 — Dodawanie kolumny warunkowej
Otrzymamy dane, w których usuwamy niepotrzebną kolumnę
Hidden, klikając prawym przyciskiem myszy na tytuł kolumny i wybierając z
podręcznego menu polecenie Usuń. Ponadto przenosimy kolumnę Typ arkusza przed
kolumnę Liczba arkuszy. Uzyskamy postać danych przedstawioną na rysunku nr 21.
rys. nr 21
W zapytaniu PQ35 mamy podaną liczbę wszystkich arkuszy,
natomiast w zapytaniu PQ35 (2) mamy liczbę arkuszy widocznych i ukrytych. W
Power Query nie mamy standardowego pojęcia Sumy jak w Excelu, więc musimy
połączyć oba zapytania. W tym celu rozwijamy polecenie Połącz (punkt nr 2 na
rysunku nr 22) z karty Narzędzia główne i wybieramy polecenie Dołącz zapytania
(punkt nr 3).
rys. nr 22 — Dołącz zapytania
Otworzy nam się okno Dołączania, gdzie wybieramy ilość tabel,
jakie chcemy połączyć. A następnie wybieramy tabelę do dołączenia, czyli PQ35
(rys. nr 23). Tak ustawione parametry zatwierdzamy przyciskiem OK.
rys. nr 23 — Dołączanie
Otrzymamy dane przedstawione na rysunku nr 24.
rys. nr 24
Przy dołączaniu pojawia się problem, polegający na tym, że
zapytanie PQ35 zawierało tylko jedną kolumnę, brakuje kolumny Typ arkusza. Pierwszym
sposobem rozwiązania tego problemu jest zmiana wartości null na Wszystkie, w
tym celu wybieramy ikonkę oznaczoną strzałką na rysunku nr 25 w karcie
Przekształć.
rys. nr 25 ikonka na karcie Przekształć
Otworzy nam się okno Zamieniania wartości, gdzie możemy
zmienić wartość null na dowolną jaką wpiszemy, w naszym przykładzie Wszystkie
(rys. nr 26). Zmianę tą zatwierdzamy przyciskiem OK.
rys. nr 26 — Zamienianie wartości
Otrzymamy dane przedstawione na rysunku nr 27.
rys. nr 27
Drugim sposobem rozwiązania tego problemu jest dodanie nowej
kolumny w zapytaniu PQ35. W tym celu wybieramy polecenie Kolumna niestandardowa
z karty Dodaj kolumnę (rys. nr 28).
rys. nr 28 — Kolumna niestandardowa
Otworzy nam się okno Kolumny niestandardowej, gdzie zmieniamy
jej nazwę na Typ arkusza, a następnie w polu Formuła kolumny niestandardowej
wpisujemy stałą wartość "Wszystkie". Tak ustawione parametry kolumny
zatwierdzamy przyciskiem OK (rys. nr 29).
rys. nr 29 — dodawanie kolumny niestandardowej
Otrzymamy dane przedstawione na rysunku nr 30.
rys. nr 30
Po przejściu na zapytanie PQ 35 (2) otrzymamy zaktualizowane
dane przedstawione na rysunku nr 31.
rys. nr 31
Nie ma znaczenia kolejność kolumn w zapytaniu które
dołączamy, ponieważ o tej kolejności decyduje nasze główne zapytanie. Tak
przygotowane dane możemy załadować do Excela. W tym celu wybieramy polecenie
Zamknij i załaduj do z karty Narzędzia główne (rys. nr 32).
rys. nr 32 — Zamknij i załaduj do
Otworzy nam się okno Importowanie danych. Okienko to może
wyglądać różnie w zależności jaka aktualizację Power Query posiadamy. Ustawiamy
sposób wyświetlania danych jako Tabela. Mamy dwa zapytania dlatego nie możemy
podać konkretnej lokalizacji. Wybieramy Nowy arkusz i zatwierdzamy przyciskiem
OK. (rys. nr 33).
rys. nr 33 — Importowanie danych
Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 34 .
rys. nr 34 — Dane w Excelu
Co jest ważne stworzyliśmy właśnie nowe arkusze, więc kiedy
odświeżymy nasze dane, otrzymamy zaktualizowane wyniki z dodatkowymi arkuszami
przedstawione na rysunku nr 35.
rys. nr 35
Należy bezwzględnie pamiętać o zapisaniu zmian w pliku przed
odświeżeniem zapytania. Nie chcemy mieć poszczególnych wyników z zapytań.
Interesuje nas tylko wynik sumaryczny z zapytania PQ35 (2). Wystarczy, że
klikniemy prawym przyciskiem myszy na arkusz z zapytaniem PQ35 i z podręcznego
menu wybierzemy polecenie Usuń (rys. nr 36).
rys. nr 36 — Usuń
Zapytanie
to zostaje tylko w pamięci, a nie zajmuje osobnego arkusza. Zapytanie PQ35 (2)
możemy zaznaczyć i wyciąć jak zwykłą tabelkę. Po czym wkleić do arkusza, w
którym chcemy mieć wynik. Pusty arkusz z którego wycięliśmy zapytanie możemy
również usunąć jak wyżej na rysunku nr 36. Na tym etapie po wprowadzeniu zmian
musimy zapisać plik za pomocą skrótu klawiszowego Ctrl + S. Następnie klikamy
prawym przyciskiem myszy na zapytanie i z podręcznego menu wybieramy polecenie Odśwież.
Otrzymamy odświeżone dane bez arkuszy które usunęliśmy (rys. nr 37).
rys. nr 37 Ostateczne 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.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście zajmiemy się grupowaniem danych z wielu
arkuszy. W przykładowych danych mamy imiona i nazwiska osób (klientów), z
którymi się spotkaliśmy bądź im coś sprzedaliśmy. Dane te są podzielone na
różne arkusze, np. pod względem miesięcy. Dane te chcemy połączyć, podliczyć,
np. wyznaczyć ile razy odbyliśmy spotkań z konkretnym klientem lub ile
sprzedaliśmy takich samych produktów. Część danych (jeden z arkuszy), na którym
będziemy pracować, został przedstawiony na rysunku nr 1.
rys. nr 1 — przykładowy arkusz z danymi
Naszym
zadaniem jest uzyskanie podsumowania przedstawionego na rysunku nr 2
rys. nr 2 — Nasz cel — pogrupowane dane
Do tych obliczeń chcemy wykorzystać Power Query. Zaczynamy od
pustego arkusza, gdzie wybieramy polecenie Nowe zapytanie (punkt nr 2 na
rysunku nr 3) z karty Dane, a następnie Z pliku (punkt nr 3) i Ze skoroszytu
(punkt nr 4).
rys. nr 3 — Nowe zapytanie ze skoroszytu
Otworzy nam się okno Importowanie pliku, gdzie musimy wybrać
lokalizację naszego pliku z danymi (rys. nr 4). Wybieramy plik i zatwierdzamy
nasz wybór przyciskiem Otwórz.
rys. nr 4 — Importowanie danych
Łączymy się z tym plikiem, z którego chcemy pobierać
informacje. Otworzy nam się okno Nawigator. W opcjach wyświetlania wybieramy
np. Styczeń – zaznaczony na rysunku zieloną strzałką (nie ma to większego
znaczenia, bo nasze nowe zapytanie będziemy edytować) nasz wybór zatwierdzamy
przyciskiem Edytuj (zaznaczone zielonym prostokątem na rysunku nr 5).
rys. nr 5 — Nawigator
Czekamy aż nasze dane ze stycznia zostaną wczytane do Power
Query. Otworzy nam się Edytor zapytań z wczytaną tabelą z danymi ze stycznia.
Dane te zostały przedstawione na rysunku nr 6. Nie interesują nas na tym etapie
te dane, a jedynie Zastosowane kroki (oznaczone na rysunku nr 6 zieloną
strzałką). Usuwamy kroki Zmieniono typ i Nawigacja, a zostawiamy krok Źródło.
rys. nr 6 — Edytor zapytań
Otrzymamy
dane przedstawione na rysunku nr 7.
rys. nr 7 — dane
Dzięki temu nie będziemy musieli sami wpisywać skomplikowanej
formuły, tylko skorzystamy w wbudowanych funkcjonalności Power Query, czyli
będziemy wprowadzać zmiany w istniejącej formule na pasku formuły. W naszych
danych mamy podział na Kind, czyli rodzaje danych w pliku. Sheet to arkusze,
Table to tabela, natomiast DefinedName to zdefiniowane zakresy danych.
Rozwijamy ikonkę ze strzałką w tytule kolumny Kind i w podręcznym menu
zaznaczamy tylko Sheet, bo interesują nas tylko arkusze (rys. nr 8). Podane
parametry filtru zatwierdzamy klikając przycisk OK.
rys. nr 8 — Filtry
W naszych danych mamy jeszcze niepotrzebny ukryty arkusz,
którego nie chcemy widzieć w naszych danych, więc klikamy na ikonkę strzałki w
tytule kolumny Hidden i odznaczamy checkbox True (rys. nr 9). Nasz filtr
zatwierdzamy klikając przycisk OK.
rys. nr 9 — Filtr
Z kolumny Item interesują nas dane tylko z konkretnych
arkuszy : styczeń, luty i marzec. Chcemy odfiltrować dane (arkusze) domyślne,
czyli takie które mają w sobie jakąś nazwę, w naszym przykładzie niepotrzebne
arkusze mają w nazwie litery PQ. Klikamy na ikonkę ze strzałką w tytule kolumny
Item, a następnie wybieramy opcje Filtr tekstu i Nie zawiera (rys. nr 10).
rys. nr 10 — Nie zawiera
Otworzy nam się okno Filtrowanie wierszy, gdzie wpisujemy w
pole Nie zawiera zestaw liter PQ (miejsce zaznaczone strzałka na rysunku nr
11). Musimy tutaj pamiętać, że Power Query jest Case sensitive, czyli zwraca
uwagę na wielkość liter. Nasz filtr zatwierdzamy klikając przycisk OK.
rys. nr 11 — Okno filtrowanie wierszy
Otrzymamy interesujące nas dane (arkusze) przedstawione na
rysunku nr 12.
rys. nr 12 — Interesujące nas arkusze
Dane te z poszczególnych arkuszy znajdują się w kolumnie Data
pod nazwą Table. Na tym etapie interesuje nas tylko kolumna Data, zawierająca
tabelki z danymi. Klikamy prawym przyciskiem myszy na tytuł tej kolumny i z
podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 13).
rys. nr 13 — Usuń inne kolumny
Otrzymamy jedną kolumnę Data, następnie klikamy na ikonkę ze
strzałkami w jej nazwie i z podręcznego menu wybieramy polecenie Rozwiń. Nasze
parametry zatwierdzamy przyciskiem OK (rys. nr 14).
rys. nr 14 — Rozwiń
Otrzymamy dane bez nagłówków, więc musimy wybrać polecenie
Użyj pierwszego wiersza jako nagłówków z karty Narzędzia główne (rys. nr 15).
rys. nr 15 — Użyj pierwszego wiersza jako nagłówków
W danych mamy 96 wierszy. Nasze dane będą się prezentować
następująco (rys. nr 16):
rys. nr 16
W kolejnym etapie wybieramy polecenie Grupowanie według z karty
Narzędzia główne (rys. nr 17).
rys. nr 17 — Grupowanie według
Otworzy nam się okno Grupowanie według. Power Query
automatycznie wstawi parametry grupowania, czyli Nazwisko i Imię (według czego
ma grupować), bo mamy w danych dwie kolumny. W polu Nazwa nowej Kolumny
automatycznie zostało wstawione Liczność (nie musimy zmieniać bo chodzi nam o
policzenie ilości takich samych danych). W polu operacja wybieramy polecenie
Zlicz wiersze. Pole Kolumna pozostaje w naszym przykładzie pusta (jest to pole
do innych operacji). Ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 18).
rys. nr 18 — Okno grupowanie według
Po
przefiltrowaniu danych zostało 75 wierszy. Z karty Narzędzia główne wybieramy
polecenie Sortuj od Z do A (rys. 19).
rys. nr 19 — Sortuj od Z do A
Nasze dane posortowane od największej do najmniejszej pokazaliśmy
na rysunku nr 20.
rys. nr 20 — Posortowane dane
Następnie z karty Narzędzia główne wybieramy polecenie
Zamknij i załaduj do (punkt nr 2 na rysunku nr 21).
rys. nr 21 — Zamknij i załaduj do
Otworzy nam się w Excelu okno Ładowanie do, gdzie wybieramy
sposób przedstawienia danych jako Tabela oraz miejsce załadowania danych –
Istniejący arkusz komórka $A$1 (rys. nr 22). Ustawione parametry zatwierdzamy
klikając przycisk Załaduj.
rys. nr 22 — Okno Ładowanie do
Otrzymamy dane w Excelu przedstawione na rysunku nr 23.
rys. nr 23 — Dane załadowane do Excela
Jeśli dodamy kolejny miesiąc do danych bazowych, to filtry w
Power Query zadziałają prawidłowo i wstawią dodatkowe dane do tabeli w Excelu.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
