Nie znasz dobrze obsługi Excela? A może nie znasz jej wcale? Doskonale wiem, że w dzisiejszych czasach i realiach, znajomość tego programu jest nie tylko niezbędna, ale przede wszystkim korzystna dla Ciebie samego. Moje szkolenie z Excela online pokaże Ci, że arkusz kalkulacyjny nie jest wcale Twoim wrogiem, a sprzymierzeńcem w porządkowaniu i analizie danych - nie tylko liczbowych. Stworzyłem kurs Excel online na każdym poziomie, zarówno dla osób początkujących, średniozaawansowanych, jak i zaawansowanych. Znajdziesz u mnie także szkolenie z Excela online dedykowane specjalnie przedsiębiorcom. Każdy kurs zawiera starannie wyselekcjonowany zakres wiedzy i zagadnień, które odpowiadają na ściśle sprecyzowane potrzeby jego użytkowników.
Excel - kurs online. Dlaczego warto?
Zastanawiasz się, czy Excel i kurs online to dobre połączenie? Czy taka forma nauki pracy z arkuszem kalkulacyjnym może być efektywna? Pozwól, że rozwieję Twoje wątpliwości.
Od lat z powodzeniem zajmuję się dzieleniem swoją wiedzą zdalnie. Ta forma nauki, szczególnie w ostatnim czasie, mocno zyskała na popularności, a jej techniki znacząco się rozwinęły. Abyś mógł biegle posługiwać się programem Excel, każdy swój kurs zaprojektowałem z ogromną starannością i w oparciu o zasady metodyki nauczania na odległość.
Jednak szkolenie z Excela online, to przede wszystkim ogromna wygoda dla Ciebie, ponieważ nie musisz wychodzić z domu, możesz uczyć się wraz ze mną z dowolnego miejsca i w wybranym przez siebie czasie. Dodatkowo każdy mój kurs Excela online zawiera materiały dydaktyczne dostępne dla Ciebie już po zakończeniu szkolenia. Krótkie nagrania filmowe, prezentujące różnego rodzaju działania w arkuszu kalkulacyjnym, które w dowolnej chwili możesz zatrzymać, odtworzyć ponownie, by jeszcze raz przeanalizować i utrwalić wiedzę, to doskonały sposób, by opanować Excel. Kurs online to nie tylko wygoda, to także oszczędność Twojego i mojego czasu - zamiast dojeżdżać w umówione miejsce, możemy poświęcić więcej chwil na ćwiczenia i praktykę.
W dzisiejszym poście nauczymy jak w Power Query znaleźć pary
produktów, które wystąpiły razem w danych. Nie ma znaczenia czy będziemy
bazować na Id produktu czy na jego nazwie. Załóżmy, że mamy listę zakupów z
produktami oznaczonymi numerami Id. Ręczne wyszukiwanie par jest pracochłonne i
męczące. Rozwiązanie tego problemu omówimy na podstawie przykładowych danych
przedstawionych na rysunku nr 1.
rys. nr 1 — Przykładowe dane
W pierwszym kroku musimy wczytać nasze dane do Power Query, w
tym celu wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane.
rys. nr 2 — Z tabeli
Otworzy nam się Edytor zapytań z wczytaną tabelą tIdProduktu.
W polu Zastosowane kroki usuwamy krok Zmiana typu, ponieważ nie jest nam
potrzebny. Otrzymamy dane przedstawione na rysunku nr 3.
rys. nr 3 — edytor zapytań
W kolejnym etapie musimy zduplikować tą kolumnę. Wybieramy
polecenie Duplikuj kolumnę (punkt nr 2 na rysunku nr 4) z karty Dodaj kolumnę.
rys. nr 4 — duplikuj kolumnę
Następnie dokładamy jeszcze kolumnę z indeksem, żebyśmy
widzieli które to były zakupy. Wybieramy polecenie Kolumna indeksu (punkt nr 2
na rysunku nr 5) a następnie od 01 (punkt nr 3 na rysunku nr 5) z karty Dodaj
kolumnę.
rys.. nr 5 — Dodaj kolumnę indeksu
Aby w Power Query znaleźć pary produktów musimy podzielić
dane po ogranicznikach a następnie odpowiednie dane połączyć. Wybieramy
polecenie Podziel kolumny (punkt nr 2 na rysunku nr 6) z karty Narzędzia
główne, a następnie polecenie Według ogranicznika (punkt nr 3 na rysunku nr 6).
rys. nr 6 — Podziel kolumny według ogranicznika
Otworzy nam się okno Dzielenia kolumny według ogranicznika,
gdzie musimy określić parametry tego dzielenia. W polu Wybierz lub wprowadź
ogranicznik wybieramy Niestandardowy (punkt nr 1 na rysunku nr 7), a następnie
wpisujemy użyty u nas ogranicznik, czyli przecinek i spacja. W opcji Podziel
przy wybieramy checkbox przy Każdym wystąpieniu ogranicznika (punkt nr 2 na
rysunku nr 7). Natomiast w Opcjach zaawansowanych Podziel na (punkt nr 3 na
rysunku nr 7) zaznaczamy checkbox przy Wierszach. Tak dobrane ustawienia
zatwierdzamy przyciskiem OK.
rys. nr 7 — Dzielenie kolumny według ogranicznika
Otrzymamy dane, których fragment został przedstawiony na
rysunku nr 8.
rys. nr 8 Podzielone dane
Power Query przy okazji dzielenia kolumny zamienił nam dane
na liczby. Nie ma to większego znaczenia. Analogicznie postępujemy dla drugiej
kolumny (kopii pierwszej). Wybieramy polecenie Podziel kolumny, a następnie
według ogranicznika z karty Narzędzia główne (jak na rysunku nr 6). Pojawi się
nam okno Dzielenia kolumny według ogranicznika, w której ustawiamy takie same
parametry jak dla pierwszej kolumny (jak na rysunku nr 7) i wybrane parametry
zatwierdzamy klikając przycisk OK. Otrzymamy dane przedstawione na rysunku nr 9.
rys. nr 9 — takie same wartości w dwóch kolumnach
W danych tych pojawia się problem – mamy zduplikowane pary
(zaznaczone na rysunku nr 9 zielonym prostokątem). Można łatwo rozwiązać ten
problem, ponieważ interesuje nas sytuacja, kiedy danej z kolumny pierwszej są
mniejsze od danych z kolumny drugiej. Wystarczy dodać kolumnę warunkową z karty
Dodaj kolumnę (rys. nr 10)
rys. nr 10 — Dodaj kolumnę warunkową
Otworzy nam się okno Dodawania kolumny warunkowej, gdzie
musimy określić Nazwę kolumny – Pary (punkt nr 1 na rysunku nr 11), Następnie
wypełnić warunek w punkcie 2 na rysunku nr 11. Ważne jest, że jeśli warunek
zostanie spełniony to chcemy otrzymać wartość z pierwszej kolumny, natomiast
jeśli nie zostanie spełniony chcemy otrzymać wartość null (punkt nr 3 na
rysunku nr 11). Tak dobrane parametry kolumny warunkowej zatwierdzamy
przyciskiem OK.
rys. nr 11 — okno dodawania kolumny warunkowej
Konkretnie chcemy otrzymać wartość z pierwszej kolumny tylko
wtedy kiedy wartość z pierwszej kolumny jest mniejsza od wartości z drugiej
kolumny. Otrzymamy dane przedstawione na rysunku nr 12. Nie jest to jeszcze
wynik docelowy, bo chcemy otrzymać parę, ale ważne jest, że para pokazuje się
dla jednej sytuacji w pierwszym zakupie.
rys. nr 12 — Dane z kolumną warunkową
Aby otrzymać jako wynik parę, musimy wprowadzić zmiany w zapisie formuły w pasku formuły. Dołożyć z ogranicznikiem wartość z kolumny drugiej. Zapis formuły został przedstawiony na rysunku nr 13 (zieloną strzałką zaznaczono wprowadzoną zmianę). Wprowadzoną zmianę zatwierdzamy przyciskiem Enter.
rys. nr 13 — zapis formuły
W wyniku otrzymamy błąd ponieważ nasze dane zostały zmienione
na liczby. Aby obejść ten problem musielibyśmy użyć funkcji Number.ToText. My
użyjemy łatwiejszego sposobu – z zastosowanych kroków usuniemy krok zmiany typu
danych z tekstu na liczby – Zmieniono typ i Zmieniono typ1 (rys. nr 14).
rys. nr 14 — usunięcie kroków
Otrzymamy wtedy prawidłowe dane przedstawione na rysunku nr 15.
rys. nr 15 — Dane w formie tekstowej
Pozostaje nam pogrupować te produkty po ilości, użyjemy do
tego polecenia Grupowanie według (punkt nr 2 na rysunku nr 16) z karty
Narzędzia główne.
rys. nr 16 — Grupowanie według
Otworzy się nam okno Grupowania według, w którym musimy
określić nazwę nowej kolumny i rodzaj operacji jaką chcemy wykonać. Wybrane
parametry zatwierdzamy przyciskiem OK (rys. nr 17).
rys. nr 17 — Okno Grupowania według
Otrzymamy dane przedstawione na rysunku nr 18.
rys. nr 18 — pogrupowane dane
W danych tych wiele razy powtórzyła nam się wartość null,
więc wystarczy przefiltrować dane, żeby odfiltrować wyniki dla null. Klikamy na
ikonkę trójkącika w nazwie kolumny Pary i odznaczamy w filtrach null (rys. nr 19).
rys. nr 19 — Odfiltrowanie danych dla null
W kolejnym etapie chcemy posortować dane malejąco w obu
kolumnach. Korzystamy z podręcznego menu po naciśnięciu ikonki trójkącika w
tytule kolumn, gdzie wybieramy polecenie Sortuj malejąco (rys. nr 20).
rys. nr 20 — Sortuj malejąco
Otrzymamy końcowe dane, które możemy załadować do Excela.
Korzystamy z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 21).
rys. nr 21 — Zamknij i załaduj do
W Excelu otworzy nam się okno Ładowania do, gdzie ustawiamy
parametry wstawianych danych. Sposób wyświetlania danych jako tabela i miejsce
wstawienia danych jako istniejący arkusz i wskazujemy konkretną komórkę.
Powyższe parametry zatwierdzamy przyciskiem Załaduj (rys. nr 22).
rys. nr 22 — Okno Ładowanie do
Otrzymamy dane wstawione do Excela przedstawione na rysunku
nr 23.
rys. nr 23 — dane wczytane do Excela
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście nauczymy się jak zamienić wszystkie x w
tabeli na nagłówki kolumn. Jest to zadanie zaczerpnięte od Oza du Soleila https://www.youtube.com/watch?v=abQrUE10Xqo , on
rozwiązał to za pomocą kilku zapytań. Nasze zadanie ma na celu podstawienie w
miejsce x w tabeli odpowiednich nagłówków. Przykładowe dane i cel do którego
dążymy został przedstawiony na rysunku nr 1.
rys. nr 1 — Dane i cel do którego dążymy
Na przykład chcemy aby zamiast znaku x dla Scoobiego zostało
wstawione Jedzenie (czyli nagłówek kolumny, w której został wstawiony ten znak
x). Pokażemy jak to zrobić w Power Query za pomocą tylko jednego zapytania.
Załóżmy, że mamy przykładową tabelkę z danymi przedstawioną na rysunku nr 2.
rys. nr 2 — Przykładowe dane
Z karty Dane wybieramy polecenie Z tabeli (punkt nr 2 na
rysunku nr 3).
rys. nr 3 — Z tabeli
Otworzy nam się edytor zapytań tCechy z wczytaną tabela z
Excela (rys. nr 4). W pierwszym etapie chcemy usunąć krok Zmieniono tym z Zastosowanych
kroków, bo nie jest nam on potrzebny.
rys. nr 4 — Edytor zapytań
W naszych wczytanych danych jest jedna postać, która w każdej
kolumnie ma Null, czyli nie ma w żadnej kolumnie znaku x. to był największy
problem, żeby rozwiązać to zadanie za pomocą jednego zapytania. Klikamy na
tytuł komuny Imię prawym przyciskiem myszy i z podręcznego menu wybieramy
polecenie Anuluj przestawienie innych kolumn (rys. nr 5).
rys. nr 5 — Anuluj przestawienie innych kolumn
W efekcie otrzymamy tabelkę z tylko tymi osobami gdzie był
znak x (rys. nr 6). Nie została uwzględniona tutaj postać Duch, u której nie
było w ogóle tego znaku.
rys. nr 6 — tabela z danymi
Wynika z tego, że nie możemy użyć tego polecenia.
Potrzebujemy sposobu na uwzględnienie osoby, która nie zaznaczyła żadnego znaku
x (czyli np. nie wybrała żadnego kursu z naszej oferty). Usuwamy z
Zastosowanych kroków ostatni etap, ponieważ zwracał niekompletne dane. Można to
zrobić za pomocą Kolumny indeksu. Z karty Dodaj kolumnę wybieramy polecenie Kolumna
indeksu (punkt nr 2 na rysunku na 7), a następnie od 0 lub 1 (nie ma to znaczenia,
ponieważ jest to kolumna pomocnicza, dzięki której osoba Duch nie zniknie z
danych, ponieważ będzie miała przypisaną jakąś wartość).
rys. nr 7 — Kolumna indeksu
Otrzymamy dane przedstawione na rysunku nr 8.
rys. nr 8 — Dane z nową kolumną
Na tym etapie po raz kolejny wybieramy polecenie z podręcznego
menu Anuluj przestawienie innych kolumn (identycznie jak na rysunku nr 5
wyżej). Otrzymamy dane przedstawione na rysunku nr 9.
rys. nr 9 — Dane
Kolumna z Wartościami jest nam nie potrzebna, natomiast
potrzebujemy drugiej kolumny z Atrybutami. Klikamy prawym przyciskiem myszy na
nagłówek kolumny Atrybut i z podręcznego menu wybieramy polecenie Duplikuj
kolumnę (rys. nr 10).
rys. nr 10 — Duplikuj kolumnę
W kolejnym etapie usuwamy kolumnę z wartościami. Klikamy na
jej nagłówek prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Usuń
(rys. nr 11).
rys. nr 11 — Usuń
Otrzymamy dane przedstawione na rysunku nr 12.
rys. nr 12
Mamy dwie kolumny z takimi samymi wartościami, część z tych
danych ma posłużyć jako nagłówki kolumn a część jako wartości. Aby tak się
stało musimy znaleźć na karcie Przekształć ikonkę Kolumny przestawnej (punkt nr
2 na rysunku nr 13). Ważne jest aby aktywna (zaznaczona) była kolumna Atrybut.
rys. nr 13 — Kolumna przestawna
Pojawi się okno Kolumny przestawnej (rys. nr 14). Polecenie
to utworzy nowe kolumny przy użyciu danych z kolumny Atrybut. Jeśli jakaś nazwa
się powtórzy to kolumna pozostanie pojedyncza, nie zostanie zduplikowana np.
dla danej Jedzenie. W polu Kolumna wartości (punkt nr 1 na rysunku nr 14) musimy
wybrać daną, jaka ma być przechowywana w tej nowej kolumnie. Tę wartość chcemy
wziąć z kolumny Atrybut – kopia. Nasza kolumna z imionami natomiast zostanie na
miejscu. Ważne jest tutaj użycie opcji zaawansowanych – w polu Agreguj funkcje
wartości (punkt nr 2) wybieramy polecenie Nie agreguj, ponieważ nie chcemy
tutaj wykonywać żadnych obliczeń. Tak ustawione parametry kolumny przestawnej zatwierdzamy klikając
przycisk OK.
rys. nr 14 — Okno kolumny przestawnej
Otrzymamy dane przedstawione na rysunku nr 15.
rys. nr 15 — Dane z kolumną przestawną
W kolejnym etapie musimy usunąć kolumnę indeks, która była
kolumną pomocniczą. Klikamy prawym przyciskiem myszy na nagłówek kolumny Indeks
i z podręcznego menu wybieramy polecenie Usuń. Otrzymamy wynik końcowy, o który
nam chodziło (rys. nr 16).
Rys nr 16 — Ostateczny wynik
Następnie wybieramy polecenie Zamknij i załaduj do z karty
Narzędzia główne (rys. nr 17).
rys. nr 17 — Zamknij i załaduj do
Otworzy nam się okno Ładowanie do, gdzie wybieramy rodzaj
wyświetlania danych jako Tabela i miejsce ich wstawienia jako istniejący arkusz
i konkretną komórkę. Powyższe parametry zatwierdzamy klikając przycisk Załaduj
(rys. nr 18).
rys. nr 18 — Okno Ładowania do
Otrzymamy dane w Excelu przedstawione na rysunku nr 19.
rys. nr 19 — Dane wczytane do Excela
Jedyną wadą takiego rozwiązania jest to, że Power Query sortuje wszystkie dane, łącznie z nazwami kolumn. Możemy sobie sprawdzić działanie zapytania dodając dodatkowe dane do tabeli z dany mi bazowymi (dodatkowe wiersze i kolumnę). Następnie klikamy prawym przyciskiem myszy na komórkę z tabeli z Power Query i z podręcznego menu wybieramy polecenie Odśwież (rys. nr 20).
rys. nr 20 — Odśwież dane
Otrzymamy prawidłowo przeliczone dane przedstawione na
rysunku nr 21.
rys. nr 21 — Przeliczone dane
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
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.
W dzisiejszym poście nauczymy się jak wyciągnąć rok, miesiąc i dzień z daty. Nauczymy się tworzyć prostą tabelę kalendarza w Power Query. W tym przykładzie interesujące jest to, że nie mamy żadnych danych bazowych, zaczynamy od pustego arkusza w Excelu. Wszystko stworzymy w Power Query. Aby przejść do Edytora zapytań w Power Query rozwijamy polecenie Nowe zapytanie (punkt nr 2 na rysunku nr 1) z karty Dane, a następnie wybieramy kolejno polecenia Z innych źródeł (punkt nr 3) i Puste zapytanie (punkt nr 4).
rys. nr 1 — Z innych źródeł
Otworzy nam się pusty Edytor zapytań. W pasku formuły
wpiszemy funkcję List.Dates. Musimy pamiętać o tym, że Power Query jest case
sensitive, czyli zwraca uwagę na wielkość liter. Po wpisaniu tej funkcji w
pasku formuły wciskamy przycisk Enter. Power Query rozpozna funkcję List.Dates
i pokaże jej parametry, które musimy określić (rys. nr 2).
rys. nr 2 — Funkcja List.Dates
Power
Query podpowiada nam co to jest za funkcja i podaje przykład jej zastosowania.
W miejscu wprowadź parametry w polu Start podajemy datę początkową, u nas
2017-01-01. W polu Count wpisujemy 365, co oznacza, że chcemy mieć rozpisany
cały rok. Natomiast w polu Step wpisujemy wartość 1, czyli chcemy mieć przeskok
o jeden dzień. Wpisane parametry zatwierdzamy przyciskiem Wywołaj (rys. nr 3).
rys. nr 3 — Wprowadź parametry funkcji
Otrzymamy listę dni w roku 2017, począwszy od daty 2017-01-01
z krokiem co jeden dzień. Wywołanie funkcji spowoduje, że pojawi się nam ona na
liście zapytań, jako nowe zapytanie (oznaczone zieloną strzałką na rysunku nr 4).
rys. nr 4 — Lista zapytań
Jeśli nie chcemy aby pokazywało się nam nowe zapytanie, to
możemy wyciąć (Ctrl+X) parametry z paska formuły dla zapytania Wywołano funkcję
(część zaznaczona na rysunku nr 5), a następnie wkleić je (Ctrl+V) w pasku
formuły do zapytania fx Zapytanie2.
rys. nr 5 — Zmiana w pasku formuły
Lista z datami wyświetli nam się teraz w zapytaniu Zapytanie
2, a w zapytaniu Wywołano funkcję wyświetli się błąd. W kolejnym kroku musimy
usunąć to zapytanie — klikamy prawym przyciskiem myszy na jego nazwę i z
podręcznego menu wybieramy polecenie Usuń (rys. nr 6).
rys. nr 6 — Usuń zapytanie
Pojawi nam się komunikat Usuwanie zapytania, w którym musimy
potwierdzić naszą decyzję klikając przycisk Usuń (rys. nr 7).
rys. nr 7 — Komunikat usuwanie zapytania
Nasze zapytanie cały czas jest listą, a my potrzebujemy
tabeli, bo chcemy z tej listy wyciągnąć rok, miesiąc i dzień. Klikamy prawym
przyciskiem myszy na nagłówek Lista i z podręcznego menu wybieramy polecenie Do
tabeli.
rys. nr 8 — Do tabeli
Pojawi się nam okno Do tabeli, w którym możemy wybrać
ogranicznik i sposób obsługi dodatkowych danych. Na tym etapie nic nie
zmieniamy, zostawiamy domyślne parametry, a następnie zatwierdzamy przyciskiem
OK. (rys. nr 9).
rys. nr 9 — Okno Do tabeli
Otrzymamy tabelę danych z jedną kolumną Column1, którą możemy
dowolnie przekształcać (rys. nr 10).
Na stronie Microsoftu możemy znaleźć informację, że jeśli
chcemy mieć nazwy kolumn to musimy zmienić w pasku formuły parametr
"null". Nazwę wpisujemy w nawiasach klamrowych {"Data"} co
pokazaliśmy na rysunku nr 12.
rys. nr 12 — zmiana parametru null w formule
Jeśli nie znamy na tyle Power Query wystarczy dwa razy
kliknąć na nazwę kolumny i ją zmienić.
Jeśli chcemy wyciągnąć z naszych dat rok, miesiąc i dzień to
najlepiej dodać kolumny. Możemy również użyć poleceń z karty Przekształć, ale
wtedy przekształcimy istniejącą kolumnę a nam chodzi o dodanie nowych kolumn z
interesującymi nas danymi. Z karty Dodaj kolumnę wybieramy kolejno polecenia
Data (punkt nr 2 na rysunku nr 13), następnie Rok (punkt nr 3) i znowu Rok
(punkt nr 4).
rys. nr 13 — Dodaj kolumnę Rok
Otrzymamy tabelę z dodatkową kolumną Rok. Analogicznie
korzystając z polecenia Data z karty Dodaj kolumnę, a następnie Miesiąc i
Miesiąc. Następnie powtarzamy ścieżkę z tą różnicą że na ostatnim etapie
wybieramy polecenie Nazwa miesiąca (punkt nr 4 na rysunku nr 14).
rys. nr 14 — Dodaj kolumnę Nazwa miesiąca
Wybierając na końcu polecenie Dzień otrzymamy kolumnę z
numerem dnia w miesiącu. Możemy też wybrać w czwartym kroku polecenie Nazwa
dnia, wtedy otrzymamy nazwy dni tygodnia przypisane do konkretnych dat (rys. nr 15).
rys. nr 15 — Dodaj kolumnę Nazwa dnia
Otrzymamy dane przedstawione na rysunku nr 16.
rys. nr 16 — Dane z dodanymi kolumnami
W panelu bocznym Ustawienia zapytania możemy zmienić nazwę zapytania na Kalendarz (rys. nr 17).
rys. nr 17 — Zmiana nazwy zapytania
Nasz kalendarz jest już gotowy i możemy go sobie wczytać do
Excela. W tym celu używamy polecenia Zamknij i załaduj do (punkt nr 3 na
rysunku nr 18) z karty Narzędzia główne.
rys. nr 18 — Zamknij i załaduj do
Otworzy nam się okno Ładowanie do, gdzie możemy ustawić
parametry naszego kalendarza. Mianowicie wybrać sposób wyświetlania danych jako
tabela i miejsce wstawienia danych – Istniejący arkusz i konkretna komórka.
Ustawione parametry zatwierdzamy przyciskiem Załaduj (rys. nr 19).
rys. nr 19 Okno Ładowanie do
Otrzymamy dane przedstawione na rysunku nr 20.
rys. nr 20 — Dane załadowane do Excela
Z mojego niedopatrzenia wynika sposób wyświetlania danych w kolumnie Data, nie zmieniliśmy sposobu wyświetlania danych. Wybieramy polecenie Edytuj (punkt nr 2 na rysunku nr 21) z karty Zapytanie.
rys. nr 21 — Edytuj zapytanie
Wracamy do Edytora zapytań z naszym kalendarzem. Klikamy na
tytuł kolumny Data i z podręcznego menu wybieramy polecenie Data, aby zmienić
formatowanie domyślne (rys. nr 22).
rys. nr 22 — Zmiana typu formatowania danych
Power Query zmieni nam typ wyświetlania danych. Ponownie używając polecenia Zamknij i załaduj do z karty Narzędzia główne wczytujemy dane do Excela. Otrzymamy kalendarz przedstawiony na rysunku nr 23.
rys. nr 23 — Dane końcowe
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ę tworzeniem tabeli
pomocniczej przefiltrowanej z tabeli głównej. Taki sam temat został omówiony w
pytaniu od widzów nr 25, gdzie wyciągaliśmy interesujące nas informacje z
tabeli głównej na podstawie jakiegoś kryterium i z tych informacji tworzyliśmy
tabelę pomocniczą. Zagadnienie to omówimy na podstawie przykładowych danych z
rysunku nr 1.
rys. nr 1 — Przykładowe dane
W pytaniu od widzów nr 25 do rozwiązania problemu
wykorzystywaliśmy skomplikowaną formułę tablicową. W tym poście omówimy
rozwiązanie w dodatku do Excela — Power Query, w którym to zadanie jest bardzo
szybkie i proste. Warunkiem szybkiego rozwiązania tego zadania jest to, że
kryterium według którego będziemy wyciągać dane, nie może się często zmieniać i
nie powinno być skomplikowane. W naszym przykładzie wyciągniemy z tabeli
głównej dane według kryterium nazwy fabryki – Żuczek. Załóżmy że mamy tabelę
główną z danymi sprzedażowymi (rys. nr 2).
rys. nr 2 — Dane sprzedażowe
Pierwszym krokiem jest zaczytanie danych do Power Query. W
tym celu korzystamy z polecenia Z tabeli (punkt nr 2 na rysunku nr 3) z karty Dane.
rys. nr 3 — Z tabeli
Otworzy nam się Edytor zapytań z wczytaną tabelą tMain
przedstawioną na rysunku nr 4.
rys. nr 4 — tMain
Aby uzyskać tabelę zawierającą tylko dane z fabryki Żuczek,
możemy sobie nałożyć na nasza tabelę główną filtr. Klikamy na ikonkę trójkącika
(Zaznaczony strzałką na rysunku nr 5) w rogu tytułu kolumny Fabryka i w
podręcznym menu zaznaczamy filtr Żuczek (zaznaczony zielonym kwadratem na
rysunku nr 5). Nasz wybór zatwierdzamy klikając przycisk OK.
rys. nr 5 — Filtry
Otrzymamy przefiltrowane dane, które możemy wczytać do Excela
za pomocą polecenia Zamknij i załaduj do (punkt nr 2 na rysunku nr 6) z karty
Narzędzia główne.
rys. nr 6 — Zamknij i załaduj do
Otworzy nam się w Excelu okno Ładowanie do, gdzie możemy
ustawić parametry wczytywanych danych. Wybieramy sposób wyświetlania danych
jako Tabela (punkt nr 1 na rysunku nr 7), następnie lokalizację wstawienia
danych – Istniejący arkusz i wskazujemy konkretną komórkę (punkt nr 2 na
rysunku nr 7). Nasze ustawienia zatwierdzamy klikając przycisk Załaduj.
rys. nr 7 — Okno Ładowanie do
Otrzymujemy wczytane dane spełniające kryterium jakie
ustawiliśmy, czyli fabrykę Żuczek przedstawione na rysunku nr 8.
rys. nr 8 — Wczytane dane do Excela
Jeśli dołożymy sobie dane do naszej tabeli głównej, wystarczy
że klikniemy na dowolną komórkę w obszarze tabeli pomocniczej zaczytanej z
Power Query prawym przyciskiem myszy i z podręcznego menu wybierzemy polecenie
Odśwież (rys. nr 9), a otrzymamy aktualne dane.
rys. nr 9 — Odświeżanie danych
Możemy
również użyć poleceń Odśwież lub Odśwież wszystko z karty Dane (rys. nr 10).
rys. nr 10 — Odśwież wszystko
Kiedy mamy stałe warunki naszych filtrów zadanie to jest
bardzo proste, natomiast jeśli potrzebujemy bardziej skomplikowanych kryteriów
polecam odcinek Power Query #10 https://exceliadam.pl/?s=power+query+%2310
.
Dodatkową zaletą użycia Power Query jest to, że dane mogą być zapisane w
różnych miejscach (strony internetowe, baza danych lub nawet połączonych ze
sobą wiele plików), natomiast przy formułach tablicowych musimy mieć wszystkie
dane w jednym miejscu.
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.
