Excel - kurs online - oferta dla każdego

Nie znasz dobrze obsługi Excela? A może nie znasz jej wcale? Doskonale wiem, że w dzisiejszych czasach i realiach, znajomość tego programu jest nie tylko niezbędna, ale przede wszystkim korzystna dla Ciebie samego. Moje szkolenie z Excela online pokaże Ci, że arkusz kalkulacyjny nie jest wcale Twoim wrogiem, a sprzymierzeńcem w porządkowaniu i analizie danych - nie tylko liczbowych. Stworzyłem kurs Excel online na każdym poziomie, zarówno dla osób początkujących, średniozaawansowanych, jak i zaawansowanych. Znajdziesz u mnie także szkolenie z Excela online dedykowane specjalnie przedsiębiorcom. Każdy kurs zawiera starannie wyselekcjonowany zakres wiedzy i zagadnień, które odpowiadają na ściśle sprecyzowane potrzeby jego użytkowników.


Excel - kurs online. Dlaczego warto?

Zastanawiasz się, czy Excel i kurs online to dobre połączenie? Czy taka forma nauki pracy z arkuszem kalkulacyjnym może być efektywna? Pozwól, że rozwieję Twoje wątpliwości.

Od lat z powodzeniem zajmuję się dzieleniem swoją wiedzą zdalnie. Ta forma nauki, szczególnie w ostatnim czasie, mocno zyskała na popularności, a jej techniki znacząco się rozwinęły. Abyś mógł biegle posługiwać się programem Excel, każdy swój kurs zaprojektowałem z ogromną starannością i w oparciu o zasady metodyki nauczania na odległość.

Jednak szkolenie z Excela online, to przede wszystkim ogromna wygoda dla Ciebie, ponieważ nie musisz wychodzić z domu, możesz uczyć się wraz ze mną z dowolnego miejsca i w wybranym przez siebie czasie. Dodatkowo każdy mój kurs Excela online zawiera materiały dydaktyczne dostępne dla Ciebie już po zakończeniu szkolenia. Krótkie nagrania filmowe, prezentujące różnego rodzaju działania w arkuszu kalkulacyjnym, które w dowolnej chwili możesz zatrzymać, odtworzyć ponownie, by jeszcze raz przeanalizować i utrwalić wiedzę, to doskonały sposób, by opanować Excel. Kurs online to nie tylko wygoda, to także oszczędność Twojego i mojego czasu - zamiast dojeżdżać w umówione miejsce, możemy poświęcić więcej chwil na ćwiczenia i praktykę.

Excel Power Query 55 — Automatyczne usuwanie pustych kolumn

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

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

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

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

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

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

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

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

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

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

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

(tbl as table) =>

let

            Headers = Table.ColumnNames(tbl),

            Result = Table.SelectColumns(tbl),

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

each_ <> null)))

in

Result

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych. 

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Power Query 51 — Matura z informatyki 2018 — Największe zasilenie zad 5.1

W dzisiejszym poście zajmiemy się rozwiązaniem zadań z Matury 2018 z informatyki na prośbę użytkownika pod kursem Mistrz Excela. Kiedy przyjrzałem się zadaniom uznałem, że może to być ciekawy temat. Zadania są dostępne pod linkiem https://gloswielkopolski.pl/matura-2018-informatyka-odpowiedzi-arkusze-cke-zadania-poziom-rozszerzony/ga/13168512/zd/28926308#matura

Treść zadań została przedstawiona na rys. nr 1.

rys. nr 1 - zadania z matury 2018 z informatyki
rys. nr 1 — zadania z matury 2018 z informatyki

Zadanie 5.1 rozwiążemy przy użyciu Power Query. W kolejnych odcinkach omówimy rozwiązanie pierwszych trzech zadań, ponieważ dużo łatwiej je rozwiązać w tym programie niż w Excelu. Zadanie nr 5.4 bardzo ciężko rozwiązać w Power Query, dużo prostsze rozwiązanie możemy znaleźć w Excelu, dlatego je pominiemy.

Mamy plik, w którym podane są daty oraz ilość metrów sześciennych wpływających do zbiornika. Zadanie 5.1 polega na wyznaczeniu roku, w którym zbiornik retencyjny został zasilony największą liczbą metrów sześciennych wody (rys nr 2).

rys. nr 2 - Treść zadania 5.1
rys. nr 2 — Treść zadania 5.1

Do rozwiązania zadania potrzebujemy danych z pliku woda.txt, które musimy pobrać do Power Query. W tym celu wybieramy polecenie Z pliku tekstowego/CSV z karty Dane (rys. nr 3).

rys. nr 3 - polecenie Z pliku tekstowego/CSV
rys. nr 3 — polecenie Z pliku tekstowego/CSV

Otworzy nam się okno Importowania danych, w którym wybieramy interesujący nas plik (woda.txt), a następnie nasz wybór zatwierdzamy klikając dwukrotnie na jego nazwę (rys. nr 4).

rys. nr 4 - Okno importowania danych
rys. nr 4 — Okno importowania danych

Otworzy nam się okno z wczytanymi danymi, rozdzielonymi na dwie kolumny. W danych tych w pierwszej kolejności jest data, a następnie po znaku tabulacji mamy podaną ilość wody. Power Query bez problemu rozpoznaje ogranicznik, czyli że dane należy oddzielić w miejscu wystąpienia znaku tabulacji na dwie kolumny. Możemy zauważyć że nasze dane mają domyślne nagłówki więc będziemy musieli to odpowiednio przekształcić. Dane te zatwierdzamy przyciskiem Przekształć dane (rys. nr 5).

rys. nr 5 - Okno z danymi które musimy przekształcić
rys. nr 5 — Okno z danymi które musimy przekształcić

Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą z pliku woda.txt. W pierwszym kroku zmienimy nagłówki kolumn klikając dwukrotnie na ich nazwy i ręcznie wpisując odpowiednie nazwy. I tak w pierwszej kolumnie chcemy mieć tytuł Data a w drugiej Woda m3 (rys. nr 6).

rys. nr 6 - Edytor zapytań Power Query z wczytanymi danymi
rys. nr 6 — Edytor zapytań Power Query z wczytanymi danymi

Gdybyśmy rozwiązywali zadanie w Excelu stworzylibyśmy tabelę przestawną a następnie skorzystali z grupowania po roku. W Power Query również mamy opcję grupowania, lecz nie grupuje ona automatycznie po datach. Musimy z daty wyciągnąć rok.

Ze względu na to, że do każdego z zadań jakie zostaną rozwiązane w tym poście będziemy potrzebować tych samych danych bazowych, klikamy na nazwę naszego zapytania prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Odwołanie (rys. 7).

rys. nr 7 - tworzenie nowego zapytania odwołującego się do poprzedniego zapytania
rys. nr 7 — tworzenie nowego zapytania odwołującego się do poprzedniego zapytania

Otrzymamy kolejne zapytanie (Woda(4)) na liście Zapytań, które odwołuje się do zapytania Woda(3). Właśnie na tym zapytaniu będziemy wykonywać kolejne etapy, aby nie utracić danych wejściowych i nie musieć wczytywać ich jeszcze raz. Naszym celem jest przekształcenie kolumny z datą, aby otrzymać tylko rok. W tym celu rozwijamy polecenie Data (punkt 2 na rys. nr 8) z karty Przekształć (punkt 1), a następnie rozwijamy polecenie Rok (punkt 3) i wybieramy polecenie Rok (punkt 4).

rys. nr 8 - ścieżka dostępu do polecenia Rok
rys. nr 8 — ścieżka dostępu do polecenia Rok

Otrzymamy przekształcone dane przedstawione na rys. nr 9, gdzie w kolumnie Data mamy tylko rok. Co istotne nasze przekształcenie wykonaliśmy z karty Przekształć a nie z karty Dodaj kolumnę, gdzie mamy analogiczne polecenie. Z tą jednak różnicą, że gdybyśmy użyli polecenia Rok z karty Dodaj kolumnę, otrzymalibyśmy dodatkową kolumnę z rokiem.

rys. nr 9 - przekształcone dane
rys. nr 9 — przekształcone dane

Na tym etapie 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 Nazwa nowej kolumny wpisujemy np. Wpłynęło, w polu Operacja wybieramy działanie Suma (chcemy otrzymać sumę przepływu wody z każdego roku) a w polu Kolumna wybieramy kolumnę Woda m3. Tak ustawione parametry grupowania zatwierdzamy przyciskiem OK (rys. nr 11).

rys. nr 11 - parametry grupowania ustawiane w oknie Grupowania według
rys. nr 11 — parametry grupowania ustawiane w oknie Grupowania według

Otrzymamy dane pogrupowane (zsumowane) według poszczególnych lat przedstawione na rys. nr 12.

rys. nr 12 - pogrupowane (zsumowane) dane po latach
rys. nr 12 — pogrupowane (zsumowane) dane po latach

Z tych danych interesuje nas rok, w którym wpłynęło najwięcej wody. W Power Query możemy znaleźć największą wartość sortując dane od Z do A za pomocą przycisku sortowania na karcie Narzędzia główne (przycisk na oznaczony strzałką na rys. nr 13).

rys. nr 13 - polecenie sortowania danych od Z do A
rys. nr 13 — polecenie sortowania danych od Z do A

Otrzymamy posortowane dane, w których od razu widać w którym roku wpłynęło najwięcej wody do zbiornika (rys. nr 14).

rys. nr 14-  dane posortowane od największej do najmniejszej
rys. nr 14- dane posortowane od największej do najmniejszej

Aby w Power Query być bardziej precyzyjnym i usunąć zbędne dane możemy rozwinąć polecenie Zachowaj wiersze (punkt 2 na rys. nr 15) z karty Narzędzia główne (punkt 1), a następnie wybrać polecenie Zachowywanie pierwszych wierszy (punkt 3).

rys. nr 15 - ścieżka dostępu do polecenia Zachowywanie pierwszych wierszy
rys. nr 15 — ścieżka dostępu do polecenia Zachowywanie pierwszych wierszy

Otworzy nam się okno Zachowywania pierwszych wierszy, gdzie w polu Liczba wierszy wpisujemy ilość wierszy, jaką chcemy zachować – w naszym przykładzie 1. Tak ustawioną ilość wierszy zatwierdzamy przyciskiem OK (rys. nr 16).

rys. nr 16 - okno Zachowywania pierwszych wierszy
rys. nr 16 — okno Zachowywania pierwszych wierszy

Otrzymamy pierwszy wiersz z rokiem 2015, czyli z najwyższym przepływem rocznym przedstawiony na rys. nr 17.

rys. nr 17 - największy przepływ roczny
rys. nr 17 — największy przepływ roczny

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 18).

rys. nr 18 - polecenie Zamknij i załaduj do
rys. nr 18 — polecenie Zamknij i załaduj do

Otworzy nam się okno Importowania danych. W naszym przypadku mam dwa zapytania więc Power Query nie pozwoli nam wstawić tabeli do arkusza. Możemy jedynie wstawić dane jako połączenie (rys. nr 19).

rys. nr 19 - wstawianie danych z zapytania jako połączenie
rys. nr 19 — wstawianie danych z zapytania jako połączenie

Otrzymamy wczytane zapytania Woda(3) i Woda(4) jako połączenie. Pierwsze zapytanie pobrało dane z pliku tekstowego, natomiast drugie zapytanie przechowuje wyniki naszych przekształceń (rys. nr 20).

rys. nr 20 - zapytanie Woda (4) przechowujące wyniki naszych przekształceń jako połączenie
rys. nr 20 — zapytanie Woda (4) przechowujące wyniki naszych przekształceń jako połączenie

Klikamy prawym przyciskiem myszy na nazwę zapytania Woda(4), które przechowuje wynik naszych działań i z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 21).

rys. nr 21 - polecenie Załaduj do z podręcznego menu
rys. nr 21 — polecenie Załaduj do z podręcznego menu

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

rys. nr 22 - okno Importowania danych
rys. nr 22 — okno Importowania danych

Otrzymamy wynik naszego zadania załadowany do Excela i przedstawiony na rys. nr 23.

rys. nr 23 - dane załadowane do Excela
rys. nr 23 — dane załadowane do Excela

Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych. 

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Excel Power Query 48 — Wybieranie kolumn

W dzisiejszym poście nauczymy się jak usunąć lub wybrać konkretne kolumny z danych. W przykładowych danych mamy zapożyczoną z internetu tabelę z dużą ilością kolumn z informacjami (rys. nr 1).

rys. nr 1 - Przykładowe dane
rys. nr 1 — Przykładowe dane

Nie zawsze potrzebujemy aż tyle danych ze wszystkich tych kolumn. Kiedy pobieramy takie dane do Power Query, (w naszym przykładzie z pliku CSV) to może być ciężko wybrać tylko te, które nas interesują lub usunąć konkretne z nich. W celu pobrania danych wybieramy polecenie Z pliku tekstowego/CSV z karty Dane (rys. nr 2).

rys. nr 2 - polecenie Z pliku tekstowego/CSV
rys. nr 2 — polecenie Z pliku tekstowego/CSV

Otworzy nam się okno Importowania danych, w którym musimy znaleźć lokalizację naszego pliku, a następnie dwa razy na niego kliknąć lub nasz wybór zatwierdzić przyciskiem Otwórz (rys. nr 3).

rys. nr 3 - okno Importowania danych
rys. nr 3 — okno Importowania danych

Otworzy nam się okno z danymi, gdzie możemy wybrać konkretne dane, ale jest to problematyczne przy tak dużej ich ilości. Dlatego klikamy przycisk Edytuj (rys. nr 4).

rys. nr 4 - okno z danymi, z których możemy wybrać konkretne dane
rys. nr 4 — okno z danymi, z których możemy wybrać konkretne dane

Power Query posiada funkcjonalność, która ułatwia takie operacje. Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą z danymi.

Nie znając tej funkcjonalności możemy zaznaczyć kolumny, które chcemy zostawić przytrzymując klawisz Ctrl, a następnie kliknąć prawym przyciskiem myszy i z podręcznego menu wybrać polecenie Usuń inne kolumny (rys. nr 5).

rys. nr 5 - polecenie Usuń inne kolumny
rys. nr 5 — polecenie Usuń inne kolumny

W wyniku tej operacji otrzymamy tylko zaznaczone wcześniej kolumny (rys. nr 6).

rys. nr 6 - kolumny pozostałe po usunięciu pozostałych
rys. nr 6 — kolumny pozostałe po usunięciu pozostałych

To polecenie sprawdza się wtedy kiedy tych kolumn jest mało i łatwo możemy znaleźć te, które nas interesują. Zatem usuwamy ostatni krok (Usunięto inne kolumny) z Zastosowanych kroków.

Kiedy mamy kilkadziesiąt kolumn dużo łatwiejszym sposobem jest wykorzystanie polecenia Wybieranie kolumn z karty Narzędzia główne (rys. nr 7).

rys. nr 7 - polecenie Wybieranie kolumn z karty Narzędzia główne
rys. nr 7 — polecenie Wybieranie kolumn z karty Narzędzia główne

Otworzy nam się okno Wybierania kolumn, gdzie mamy listę wszystkich kolumn w tabeli i możemy wybrać nazwy kolumn, których potrzebujemy za pomocą zaznaczenia/ odznaczenia odpowiednich checkboxów (rys. nr 8). Po wybraniu odpowiednich checkboxów nasz wybór zatwierdzamy przyciskiem OK.

rys. nr 8 - Okno wybieranie kolumn
rys. nr 8 — Okno wybieranie kolumn

Wybraliśmy takie same kolumny jak w pierwszym przykładzie, więc otrzymamy tabelę przedstawioną na rys. nr 6. Tak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj z karty Narzędzia główne (rys. nr 9).

rys. nr 9 - polecenie Zamknij i załaduj
rys. nr 9 — polecenie Zamknij i załaduj

Dane zostały załadowane tylko jako połączenie, więc klikamy prawym przyciskiem myszy na nazwę zapytania w panelu bocznym Zapytania i połączenia i z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 10).

rys. nr 10 - polecenie Załaduj do z podręcznego menu
rys. nr 10 — polecenie Załaduj do z podręcznego menu

Otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako tabela oraz wskazujemy miejsce wstawienia danych, w naszym przykładzie Istniejący arkusz i wskazujemy konkretną komórkę. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 11).

rys. nr 11 - okno Importowania danych
rys. nr 11 — okno Importowania danych

Otrzymamy wybrane przez nas kolumny załadowane do Excela przedstawione na rys. nr 12.

rys. nr 12 - wybrane dane wczytane do Excela
rys. nr 12 — wybrane dane wczytane do Excela

W pierwszym arkuszu mamy konkretne cztery kolumny, które były nam potrzebne, a w kolejnym arkuszu możemy załadować inne kolumny z tego samego pliku CSV. Przechodzimy do Arkusza 2 i analogicznie jak w przykładzie powyżej korzystając z polecenia Z pliku tekstowego/CSV z karty Dane możemy pobrać dane jeszcze raz, z tym że tym razem wybrać inne kolumny, które nas interesują (rys. nr 2).

Załóżmy, że tym razem będzie nas interesował adres klienta, więc po zaczytaniu danych do Power Query rozwijamy polecenie Wybieranie kolumn (punkt 2 na rys. nr 13) z karty Narzędzia główne (punkt 1), a następnie wybieramy polecenie Przejdź do kolumny (punkt 3).

rys. nr 13 - polecenie Przejdź do kolumny
rys. nr 13 — polecenie Przejdź do kolumny

Otworzy nam się okno Przejdź do kolumny, gdzie możemy wybrać nazwę konkretnej kolumny i przeskoczyć do niej. Polecenie to przydaje się w sytuacji, kiedy mamy bardzo dużą ilość kolumn i ciężko znaleźć tę, która nas aktualnie interesuje (rys. nr 14).

rys. nr 14 - okno Przejdź do kolumny
rys. nr 14 — okno Przejdź do kolumny

Power Query automatycznie przeskoczy do wybranej kolumny i zaznaczy ją (rys. nr 15).

rys. nr 15 - wybrana kolumna zaznaczona w danych
rys. nr 15 — wybrana kolumna zaznaczona w danych

Po raz kolejny wybieramy polecenie Wybieranie kolumn z karty Narzędzia główne (jak na rys. nr 7). Otworzy nam się okno Wybierania kolumn, gdzie zgodnie z założeniem zaznaczamy checkboxy przy kolumnach związanych z adresem. Zaznaczone kolumny zatwierdzamy przyciskiem OK (rys. nr 16).

rys. nr 16 - Okno Wybierania kolumn
rys. nr 16 — Okno Wybierania kolumn

Otrzymamy dane z wybranymi kolumnami przedstawione na rys. nr 17.

rys. nr 17 - dane z wybranymi wcześniej kolumnami
rys. nr 17 — dane z wybranymi wcześniej kolumnami

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 18).

rys. nr 18 - polecenie Zamknij i załaduj do
rys. nr 18 — polecenie Zamknij i załaduj do

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

rys. nr 19 - okno Importowania danych
rys. nr 19 — okno Importowania danych

Otrzymamy dane w Excelu zawierające wybrane przez nas kolumny związane z adresami klientów (rys. nr 20).

rys. nr 20 - dane wczytane do Excela
rys. nr 20 — dane wczytane do Excela

Podsumowując wyciąganie konkretnych kolumn z tabel, gdzie mamy kilkadziesiąt kolumn jest dużo łatwiejsze dzięki funkcjonalności Wybierania kolumn.


Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych. 

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Power Query 45 — Ile ciasteczek było w paczkach — wszystkie pary

W dzisiejszym poście nauczymy się jak policzyć wszystkie komórki zawierające konkretny tekst. Naszym zadaniem będzie policzenie ile ciasteczek było w paczkach, oraz wskazanie wszystkich możliwych par.

W Power Query odcinku 43 obliczaliśmy ile produktów w paczkach sprzedał konkretny sprzedawca. Pojawiał się tam taki problem, że mieliśmy np. czterech sprzedawców a tylko trzech z nich sprzedało produkt ciasteczka. W wyniku naszych działań pojawiali się tylko sprzedawcy, którzy dany produkt sprzedali (rys. nr 1).

rys. nr 1 — Dane ze sprzedawcami i produktami

Naszym zadaniem będzie otrzymanie wyniku, w którym Power Query będzie pokazywał każdą możliwą parę sprzedawca – produkt, również „puste” pary jak na rys. nr 2.

rys. nr 2 — zestawienie danych ze sprzedawcami, którzy nie sprzedali danego produktu

W odcinku Power Query nr 44 omawialiśmy iloczyn kartezjański, który moglibyśmy wykonać ręcznie, chociaż jest to bardzo czaso- i pracochłonne przy większej ilości danych. Zależy nam, aby Power Query sam wyznaczył pary sprzedawca- produkt oraz dodatkowo podliczył ile poszczególnych produktów sprzedał każdy sprzedawca. Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 3.

rys. nr 3 — Przykładowe dane

W pierwszym kroku musimy zaczytać nasze przykładowe dane do Power Query. W tym celu wybieramy polecenie Z tabeli/zakresu z karty Dane (rys. nr 4).

rys. nr 4 - polecenie zaczytania danych z tabeli/zakresu
rys. nr 4 — polecenie zaczytania danych z tabeli/zakresu

Otworzy nam się Edytor zapytań z wczytaną tabelą tPaczki. Do dalszej pracy musimy zduplikować nasze zapytanie. Na tych zapytaniach wykonamy różne operacje, których wyniki odpowiednio ze sobą połączymy. Będą nam potrzebne takie dane jak unikatowa lista Produktów, Sprzedawców oraz podsumowane wartości sprzedaży. Musimy wyznaczyć ilość konkretnych produktów sprzedanych przez każdego sprzedawcę. Będziemy potrzebować 3 zapytań z naszymi danymi bazowymi. W panelu bocznym z listą zapytań klikamy na nazwę zapytania tPaczki prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Duplikuj (zaznaczone zieloną strzałką na rys. nr 5). Czynność tę powtarzamy, aby otrzymać trzy jednakowe zapytania.

rys. nr 5 - polecenie Duplikuj zapytanie
rys. nr 5 — polecenie Duplikuj zapytanie

Zaczniemy od grupowania. W tym celu klikamy na zapytanie tPaczki(3) i w panelu bocznym Ustawień zapytania po prawej stronie ekranu zmieniamy jego nazwę na tGrupowanie (rys. nr 6).

rys. nr 6 - Zmiana nazwy zapytania w Ustawieniach zapytania
rys. nr 6 — Zmiana nazwy zapytania w Ustawieniach zapytania

Nasze dane pogrupujemy po sprzedawcach i produktach, w tym celu musimy najpierw rozdzielić paczki na poszczególne produkty. Zaznaczamy kolumnę Paczki i rozwijamy polecenie Podziel kolumny (punkt 2 na rys. nr 7) z karty Narzędzia główne (punkt 1), a następnie wybieramy polecenie Według ogranicznika (punkt 3). Należy pamiętać, że nasze dane chcemy rozdzielić na wiersze.

rys. nr 7 - polecenie Podziel kolumny Według ogranicznika
rys. nr 7 — polecenie Podziel kolumny Według ogranicznika

Otworzy nam się okno Dzielenia kolumny według ogranicznika, gdzie w polu Wybierz lub wprowadź ogranicznik wybieramy typ –Niestandardowe–, a następnie w polu poniżej wpisujemy nasz ogranicznik, czyli przecinek i spacja. Rozwijamy opcje zaawansowane, w których w polu Podziel na, zaznaczamy checkbox Wiersze. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 8).

rys. nr 8 - Okno Dzielenia kolumny według ogranicznika
rys. nr 8 — Okno Dzielenia kolumny według ogranicznika

Otrzymamy podzielone dane przedstawione na rys. nr 9.

rys. nr 9 - Dane podzielone według ogranicznika
rys. nr 9 — Dane podzielone według ogranicznika

Podzielone dane chcemy pogrupować według sprzedawcy, aby mieć jasne wyniki ile dany sprzedawca sprzedał produktów. Zaznaczamy kolumny Sprzedawca oraz Paczka, następnie 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 możemy pozostać przy domyślnych ustawieniach, ponieważ naszym celem jest zliczenie produktów. Domyślne parametry zatwierdzamy przyciskiem OK (rys. nr 11).

rys. nr 11 - Okno Grupowania według
rys. nr 11 — Okno Grupowania według

Otrzymamy pogrupowane dane przedstawione na rys. nr 12.

rys. nr 12 - Pogrupowane dane
rys. nr 12 — Pogrupowane dane

Teraz przechodzimy do zapytania tPaczki (2), któremu zmieniamy nazwę na tProdukty w Ustawieniach zapytania. Naszym zadaniem w tym zapytaniu jest podzielenie paczek na poszczególne produkty. Inne kolumny są zbędne. Zaznaczamy kolumnę Paczki, a następnie klikamy prawym przyciskiem myszy na jej obszarze i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 13).

rys. nr 13 - polecenie Usuń inne kolumny
rys. nr 13 — polecenie Usuń inne kolumny

Otrzymamy kolumnę Paczki przedstawioną na rys. nr 14.

rys. nr 14 - dane po usunięciu niepotrzebnych kolumn
rys. nr 14 — dane po usunięciu niepotrzebnych kolumn

Następnie powyższą kolumnę musimy podzielić według ogranicznika. Rozwijamy polecenie Podziel kolumny z karty Narzędzia główne, a następnie wybieramy polecenie Według ogranicznika (tak jak na rys. nr 7).

Otworzy nam się okno Dzielenia kolumny według ogranicznika, w którym ustawiamy takie same parametry jak dla zapytania tGrupowanie (jak na rys. nr 8). Parametry te zatwierdzamy przyciskiem OK.

Otrzymamy listę wszystkich produktów z poszczególnych paczek przedstawioną na rys. nr 15.

rys. nr 15 - lista produktów z poszczególnych paczek
rys. nr 15 — lista produktów z poszczególnych paczek

Naszym zadaniem jest stworzenie unikatowej listy produktów, więc musimy usunąć powtarzające się dane. Zaznaczamy kolumnę, rozwijamy polecenie Usuń wiersze (punkt 2 na rys. nr 16) z karty Narzędzia główne (punkt 1), a następnie wybieramy polecenie Usuń duplikaty (punkt 3).

rys. nr 16 - polecenie Usuń duplikaty
rys. nr 16 — polecenie Usuń duplikaty

Ponadto posortujemy sobie naszą listę alfabetycznie od A do Z za pomocą polecenia na karcie Narzędzia główne (zaznaczone strzałką na rys. nr 17).

rys. nr 17 - Sortowanie danych od A do Z
rys. nr 17 — Sortowanie danych od A do Z

Otrzymamy posortowaną unikatową listę produktów przedstawioną na rys. nr 18.

rys. nr 18 - posortowana unikatowa lista produktów
rys. nr 18 — posortowana unikatowa lista produktów

Ponadto zmienimy nazwę naszej kolumny na Produkty (rys. nr 19).

rys. nr 19 - Zmiana nazwy kolumny
rys. nr 19 — Zmiana nazwy kolumny

Teraz przechodzimy do naszego zapytania głównego tPaczki. Zmieniamy nazwę zapytania w Ustawieniach zapytania na tPary (rys. nr 20).

rys. nr 20 - Zmiana nazwy zapytania w Ustawieniach zapytania
rys. nr 20 — Zmiana nazwy zapytania w Ustawieniach zapytania

W pierwszym etapie chcemy uzyskać unikatową listę sprzedawców. Zaznaczamy kolumnę Sprzedawca, klikamy prawym przyciskiem myszy na obszarze kolumny i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 21).

rys. nr 21 - polecenie Usuń inne kolumny
rys. nr 21 — polecenie Usuń inne kolumny

Otrzymamy kolumnę Sprzedawca, z której chcemy usunąć powtarzających się sprzedawców. Rozwijamy polecenie Usuń wiersze z karty Narzędzia główne, a następnie wybieramy polecenie Usuń duplikaty (tak jak na rys. nr 16).

Uzyskaną listę sortujemy od A do Z za pomocą polecenia z karty Narzędzia główne (jak na rys. nr 17).

Otrzymamy posortowaną unikatową listę sprzedawców przedstawioną na rys nr 22.

rys. nr 22 - posortowana unikatowa lista sprzedawców
rys. nr 22 — posortowana unikatowa lista sprzedawców

Na tym etapie do listy sprzedawców z zapytania tPary chcemy dołożyć listę produktór z zapytania tProdukty. Kopiujemy nazwę zapytania tProdukty za pomocą skrótu klawiszowego Ctrl+C. Przechodzimy do zapytania tPary (ważne które zapytanie jest aktywne) i wybieramy polecenie Kolumna niestandardowa z karty Dodaj kolumnę (rys. nr 23).

rys. nr 23 - polecenie Kolumna niestandardowa z karty Dodaj kolumnę
rys. nr 23 — polecenie Kolumna niestandardowa z karty Dodaj kolumnę

Otworzy nam się okno Kolumny niestandardowej, gdzie w polu Nazwa nowej kolumny wpisujemy Produkty oraz w polu Formuła kolumny niestandardowej wklejamy skopiowaną nazwę drugiego zapytania za pomocą skrótu klawiszowego Ctrl+V. Zapis powinien wyglądać następująco: =tProdukty (w polu formuły robimy odwołanie do innego zapytania – zaznaczone strzałką na rys. nr 24). Tak ustawione parametry zatwierdzamy przyciskiem OK.

rys. nr 24 - parametry kolumny niestandardowej
rys. nr 24 — parametry kolumny niestandardowej

Otrzymamy pary sprzedawca – produkty. W kolumnie Produkty są tabele z listą unikatowych produktów (rys. nr 25).

rys. nr 25 - Pary sprzedawca - produkty
rys. nr 25 — Pary sprzedawca — produkty

Aby rozwinąć te listy produktów musimy kliknąć na ikonkę ze strzałkami przy nazwie kolumny Produkty. Otworzy nam się okienko, gdzie zaznaczamy checkbox przy poleceniu Rozwiń, a następnie odznaczamy checkbox przy poleceniu Użyj oryginalnej nazwy kolumny jako prefiksu. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 26).

rys. nr 26 — polecenie Rozwiń

Otrzymaliśmy listę wszystkich możliwych par sprzedawca – produkt przedstawioną na rys. nr 27.

rys. nr 27 - lista wszystkich możliwych par sprzedawca - produkt
rys. nr 27 — lista wszystkich możliwych par sprzedawca — produkt

Zmieniła się nazwa kolumny z produktami, aby ją zmienić rozwijamy pasek formuły i tam wprowadzamy zmianę (zaznaczone strzałką na rys. nr 28).

rys. nr 28 - zmiana nazwy kolumny w pasku formuły
rys. nr 28 — zmiana nazwy kolumny w pasku formuły

W zapytaniu tPary otrzymaliśmy wszystkie możliwe kombinacje par między sprzedawcą a produktem, natomiast w zapytaniu tGrupowanie mamy listę tych par, które faktycznie istnieją. Z zapytania tGrupowanie będziemy chcieli skorzystać z kolumny Liczność. W tym celu scalimy te zapytania.

Główne zapytanie (tPary) musi być aktywne. Rozwijamy polecenie Scal zapytania (punkt 2 na rys. nr 29) z karty Narzędzia główne (punkt 1), a następnie wybieramy polecenie Scal zapytania (punkt 3).

rys. nr 29 - droga dostępu do polecenia Scal zapytania
rys. nr 29 — droga dostępu do polecenia Scal zapytania

Otworzy nam się okno Scalania, gdzie w drugim polu wybieramy zapytanie tGrupowanie. Zaznaczamy kolumny Sprzedawca i Produkty z pierwszego zapytania przytrzymując klawisz Ctrl a następnie te same kolumny z drugiego zapytania. Wybieramy rodzaj sprzężenia jako Lewe zewnętrzne. Należy zwrócić uwagę, aby zaznaczanie kolumn odbyło się w tej samej kolejności, czyli jeśli w pierwszym zapytaniu najpierw zaznaczymy kolumnę Sprzedawca to w drugim musimy zrobić tak samo. Nazwy kolumn nie muszą się pokrywać. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 30).

rys. nr 30 - Okno Scalania z ustawionymi parametrami
rys. nr 30 — Okno Scalania z ustawionymi parametrami

Otrzymaliśmy listę wszystkich możliwych par sprzedawca – produkt oraz ilość tych produktów z zapytania tGrupowanie (rys. nr 31).

rys. nr 31 - lista wszystkich możliwych par sprzedawca – produkt oraz ilość tych produktów z zapytania tGrupowanie
rys. nr 31 — lista wszystkich możliwych par sprzedawca – produkt oraz ilość tych produktów z zapytania tGrupowanie 

Sprawdzimy teraz co się dzieje w sytuacji sprzedawcy, który nie sprzedał żadnego produktu z listy. Sprzedawca Robert nie sprzedał żadnych ciasteczek, więc jego tabela jest pusta (rys. nr 32).

rys. nr 32 - pusta tabela dla sprzedawcy, który nie sprzedał danego produktu
rys. nr 32 — pusta tabela dla sprzedawcy, który nie sprzedał danego produktu

Na tym etapie musimy rozwinąć kolumnę tGrupowanie, czyli klikamy na ikonkę ze strzałkami przy nazwie kolumny i zaznaczamy polecenie Rozwiń oraz kolumnę, którą chcemy rozwinąć. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 33).

rys. nr 33 - Polecenie Rozwiń
rys. nr 33 — Polecenie Rozwiń

Otrzymamy listę sprzedawców, produktów oraz ilość sprzedanych produktów (rys. nr 34).

rys. nr 34 - rozwinięte dane
rys. nr 34 — rozwinięte dane

Chcemy zmienić nazwę kolumny otrzymanej po rozwinięciu na Ilość, w tym celu zmieniamy nazwę w pasku formuły. Pierwsze wystąpienie nazwy Liczność jest nazwą kolumny, którą rozwijaliśmy, natomiast drugie wystąpienie – nazwą kolumny docelowej (rys. nr 35).

rys. nr 35 - Zmiana nazwy kolumny w pasku formuły
rys. nr 35 — Zmiana nazwy kolumny w pasku formuły

Po zmianie nazwy kolumny otrzymamy dane przedstawione na rys. nr 36.

rys. nr 36 - Dane po zmianie nazwy kolumny
rys. nr 36 — Dane po zmianie nazwy kolumny

Wartości null w danych po załadowaniu danych do Excela przekształcą się w puste komórki. Jeśli chcemy aby przekształciły się w wartość zero, to musimy przekształcić naszą kolumnę. W tym celu rozwijamy ikonę polecenia Zamieniania (punkt 2 na rys. nr 37) z karty Przekształć (punkt 1), a następnie wybieramy polecenie Zamienianie wartości (punkt 3).

rys. nr 37 - polecenie Zmienianie wartości
rys. nr 37 — polecenie Zmienianie wartości

Otworzy nam się okno Zamieniania wartości, gdzie w polu wartość do znalezienia wpisujemy null, natomiast w polu Zamień na wpisujemy zero (0). Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 38).

rys. nr 38 - okno Zamieniania wartości
rys. nr 38 — okno Zamieniania wartości

Otrzymamy zmienione dane przedstawione na rys. nr 39.

rys. nr 39 - dane ze zmienionymi wartościami null
rys. nr 39 — dane ze zmienionymi wartościami null

Aby dane były bardziej czytelne musimy je posortować za pomocą polecenia na karcie Narzędzia główne. Najpierw sortujemy kolumnę Produkt a następnie kolumnę Sprzedawca. Otrzymamy dane przedstawione na rys. nr 40.

rys. nr 40 - posortowane dane
rys. nr 40 — posortowane dane

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 41).

rys. nr 41 - polecenie Zamknij i załaduj do
rys. nr 41 — polecenie Zamknij i załaduj do

Otworzy nam się okno Importowania danych. Tutaj pojawi się pewien problem, mianowicie stworzyliśmy trzy zapytania, więc nie możemy ich wstawić w konkretną komórkę. Dlatego wybieramy sposób wyświetlania danych w skoroszycie jako Utwórz tylko połączenie (rys. nr 42).

rys. nr 42 - Okno Importowania danych
rys. nr 42 — Okno Importowania danych

Wstawiliśmy dane jako połączenie ponieważ interesuje nas tylko jedno ze stworzonych zapytań. Klikamy prawym przyciskiem myszy na zapytanie tPary i z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 43).

rys. nr 43 - polecenie Załaduj do (ponieważ wstawiliśmy dane jako połączenie)
rys. nr 43 — polecenie Załaduj do (ponieważ wstawiliśmy dane jako połączenie)

Otworzy nam się okno Importowania danych, gdzie wybieramy rodzaj wyświetlania danych w skoroszycie jako Tabela, a następnie wskazujemy konkretną komórkę w istniejącym arkuszu, gdzie ma zostać wstawiona. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 44).

rys. nr 44 - ustawienia sposobu wstawienia danych z zapytania
rys. nr 44 — ustawienia sposobu wstawienia danych z zapytania

Otrzymamy wstawione dane do Excela przedstawione na rys. nr 45. Otrzymaliśmy listę par sprzedawca – produkt, zawierającą każde możliwe połączenie.

rys. nr 45 -  lista par sprzedawca – produkt, zawierającą każde możliwe połączenie
rys. nr 45 — lista par sprzedawca – produkt, zawierającą każde możliwe połączenie 

Książka Mistrz Excela reklama

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

Power Query #29 — Jak połączyć listy osób zapisanych na różne kursy

W dzisiejszym poście nauczymy się jak połączyć listy osób zapisanych na różne kursy za pomocą Power Query – dodatku do Excela. W przykładowych danych do tego zagadnienia przedstawionych na rysunku nr 1 mamy podane trzy listy adresów mailowych związanych z różnymi kursami. Naszym zadaniem jest połączenie tych list i stworzenie skumulowanej tabeli z adresami mailowymi z zaznaczonymi kursami, na jaki właściciel danego maila się zapisał.

rys. nr 1 — Przykładowe dane

Nasze zadanie ma również na celu to, żeby konkretna osoba która zapisała się na wszystkie trzy kursy w naszej tabeli występowała jeden raz (np. Andrzej@gazeta.pl zapisał się na trzy kursy).

Pierwszym krokiem jest zaczytanie naszych danych bazowych, w tym celu zaznaczamy dowolną komórkę w pierwszej tabeli i wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane. Ułatwieniem tutaj jest, że tabele wejściowe zostały nazwane Tabela1, Tabela2 i Tabela3.

rys. nr 2 — Z tabeli

Otworzy nam się Edytor zapytań z wczytaną tabelą Tabela1 (rys. nr 3). Z Zastosowanych kroków (Ustawienia zapytania) usuwamy krok Zmieniono typ, ponieważ jest on niepotrzebny.

rys. nr 3 — Edytor zapytań

Pozostałe tabele możemy wczytać w łatwy sposób, wystarczy że zduplikujemy sobie Zapytanie. Klikamy prawym przyciskiem myszy na nazwę Zapytanie1 i z podręcznego menu wybieramy polecenie Duplikuj (rys. nr 4). Czynność tę powtarzamy dwa razy ponieważ potrzebujemy trzech tabel.

rys. nr 4 — Duplikuj zapytanie

Zaznaczamy sobie zapytanie Tabela1 (2) i w pasku formuły zmieniamy nazwę Tabela1 na Tabela2 (oznaczone zieloną strzałką na rysunku nr 5). Wprowadzoną zmianę zatwierdzamy przyciskiem Enter.

rys. nr 5 — Zmiany w pasku formuły

Otrzymamy zapytanie Tabela1 (2) z wczytaną drugą tabelą z danymi (osoby zapisane na kurs Dashboardy) przedstawione na rysunku nr 6. Po raz kolejny usuwamy niepotrzebny krok Zmieniono typ z Zastosowanych kroków.

rys. nr 6 — Połączone zapytania

Analogicznie wygląda sytuacja dla trzeciego zapytania. Klikamy na nazwę zapytania Tabela1 (3) i w pasku formuły zmieniamy nazwę Tabela1 na Tabela3. Wprowadzoną zmianę zatwierdzamy przyciskiem Enter. Otrzymamy trzecie zapytanie z wczytanymi danymi z tabeli numer 3 przedstawionymi na rysunku nr 7. Po raz kolejny usuwamy niepotrzebny krok Zmieniono typ z Zastosowanych kroków.

rys. nr 7 — Dane z trzech tabel

Kolejnym krokiem jest zmiana nazwy zapytań nr 2 i 3 we Właściwościach w panelu bocznym z Ustawieniami zapytania (oznaczone zieloną strzałką na rysunku nr 8).

rys. nr 8 — Zmiana nazwy zapytania

Kiedy już mamy zaczytane wszystkie trzy tabele, możemy je połączyć. Rozwijamy polecenie Połącz (punkt nr 2 na rysunku nr 9) z karty Narzędzia główne i wybieramy polecenie Dołącz zapytania (punkt nr 3 na rysunku nr 9).

rys. nr 9 — Dołącz zapytania

Pojawi nam się okno Dołączanie, gdzie zaznaczamy checkbox przy opcji Co najmniej trzy tabele (punkt nr 1 na rysunku nr 10). Następnie dodajemy Tabelę2 i Tabelę3 z pola Dostępne tabele do pola Tabele do dołączenia za pomocą przycisku Dodaj (punkt nr 2 na rysunku nr 10). Dołączone tabele zatwierdzamy przyciskiem OK.

rys. nr 10 — Okno Dołączania zapytań

Otrzymamy jedną tabelę ze skumulowanymi danymi ze wszystkich trzech tabel (rys. nr 11).

rys. nr 11 — Skumulowane dane

Mimo że dane zostały dołączone to nadal osoby które zapisały się na większą ilość kursów są wyświetlane osobno, a nam chodzi o to by takie osoby wyświetlane były w tabeli tylko raz. W tym celu musimy anulować przestawienie innych kolumn poza kolumną Mail. Klikamy prawym przyciskiem myszy na tytuł kolumny Mail i z podręcznego menu wybieramy polecenie Anuluj przestawienie innych kolumn (rys. nr 12).

rys. nr 12 — Anuluj przestawienie innych kolumn

Otrzymamy dane przedstawione na rysunku nr 13, gdzie stworzą nam się nowe kolumny. Kolumna Atrybuty będzie zawierała nazwy kolumn, natomiast kolumna Wartość będzie zawierała wartości jakie były przypisane do odpowiednich adresów mailowych w tych kolumnach.

rys. nr 13 — Dane z nowymi kolumnami

Najważniejszy efekt działania tego polecenia to pozbycie się z danych wartości null. W kolejnym etapie musimy przekształcić jedną z tych kolumn (Atrybuty bądź Wartość), zrobimy operację odwrotną. Z karty Przekształć wybieramy polecenie Kolumna przestawna (punkt nr 2 na rysunku nr 14).

rys. nr 14 — Kolumna przestawna

Otworzy nam się okno Tabeli przestawnej, gdzie stworzymy nowe kolumny przy użyciu kolumny Atrybyt, czyli powstaną kolumny z nazwami VBA, Dashboardy i Excel (rys. nr 15). Odpowiednio w każdej z tych kolumn będzie wpisana wartość z kolumny Wartość (punkt nr 1 na rysunku nr 15). Ponadto musimy wybrać w Opcjach zaawansowanych wybieramy w jaki sposób będą agregowane nasze dane – Nie agreguj (punkt nr 2 na rysunku nr 15). Wybrane parametry zatwierdzamy przyciskiem OK.

rys. nr 15 — Okno kolumny przestawnej

Otrzymamy dane przedstawione na rysunku nr 16, gdzie mamy już skumulowane dane. Przykładowo właściciel maila Andrzej@gazeta.pl występuje tylko raz na liście mimo że zapisał się aż na trzy kursy.

rys. nr 16 — Skumulowane dane

Tak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj (punkt nr 2 na rysunku nr 17) z karty Narzędzia główne.

rys. nr 17 Zamknij i załaduj

Ponieważ jednocześnie pobieraliśmy dane z trzech tabel, to utworzyło nam się w Excelu tylko połączenie. Dane te będą widoczne w panelu bocznym Zapytania. Gdyby nie były widoczne należy wybrać polecenie Pokaż zapytania z karty Dane (rys. nr 18).

rys. nr 19 — Pokaż zapytania

Interesuje nas zapytanie Tabela1, bo to w tym zapytaniu połączyliśmy wszystkie dane (rys. nr 19).

rys. nr 19 — Zapytanie Tabela1

Klikamy prawym przyciskiem myszy na nazwę zapytania Tabela1 i z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 20).

rys. nr 20 — Załaduj do

Otworzy nam się okno Ładowania do, gdzie wybieramy sposób przedstawienia załadowanych danych jako Tabela, a następnie lokalizację tych danych jako Istniejący arkusz i wskazujemy konkretną komórkę (rys. nr 21). Powyższe parametry zatwierdzamy przyciskiem Załaduj.

rys. nr 21 — Okno Ładowanie do

Otrzymamy dane załadowane do Excela przedstawione na rysunku nr 22.

rys. nr 22 — Dane w Excelu

Przy tworzeniu danych został zrobiony błąd, którego nie zauważyliśmy. W Excelu w oknie Zapytania dotyczące skoroszytu jest on wyszczególniony (rys. nr 23).

rys. nr 23 — Błąd w zapytaniu

Po kliknięciu na ten błąd, otworzy nam się edytor zapytań z wczytanym błędem w zapytaniu Tabela1 (rys. nr 24).

rys. nr 24 — Edytor zapytań z błędem

W panelu bocznym Ustawienia zapytania klikamy na Źródło w Zastosowanych krokach. Mamy podany numer wiersza w którym był błąd, więc możemy go łatwo znaleźć. Kiedy klikniemy na ten wiersz wyświetli nam się pod tabelą informacja o tym błędzie (rys. nr 25).

rys. nr 25 — Informacja o błędzie

Błąd ten dotyczy maila Sebastian@yahoo.com, który wystąpił w danych źródłowych dwa razy (dla tego samego kursu) co widać na rysunku nr 26.

rys. nr 26 — Miejsce błędu

Musimy poprawić te dane W Excelu i usunąć powtarzający się adres mailowy, następnie klikamy prawym przyciskiem myszy na dowolną komórkę w danych z zapytania i z podręcznego menu wybieramy polecenie Odśwież (rys. nr 27).

rys. nr 27 — Odśwież

Otrzymamy prawidłowe dane przedstawione na rysunku nr 28.

rys. nr 28 — Prawidłowe dane

Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych. 

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama