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 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
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 ++
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
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 Pustezapytanie (punkt 4).
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
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
Wklejoną
funkcję zatwierdzamy w Edytorze zaawansowanym przyciskiem Gotowe. Otrzymamy
gotową funkcję przedstawioną na rys. nr 7.
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
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
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
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
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
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)
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
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łajfunkcjęniestandardową
z karty Dodaj kolumnę (rys. nr 15).
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
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
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
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
Otrzymamy
dane wstawione do Excela przedstawione na rys. nr 20.
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.
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.
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
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
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
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ć
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
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
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
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
Na tym etapie wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys. nr 10).
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
Otrzymamy
dane pogrupowane (zsumowane) według poszczególnych lat przedstawione na rys. nr 12.
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
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
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
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
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
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
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
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
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
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
Otrzymamy
wynik naszego zadania załadowany do Excela i przedstawiony na rys. nr 23.
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.
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 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
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
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
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
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
W wyniku tej
operacji otrzymamy tylko zaznaczone wcześniej kolumny (rys. nr 6).
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
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
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
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
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
Otrzymamy
wybrane przez nas kolumny załadowane do Excela przedstawione na rys. nr 12.
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
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
Power Query
automatycznie przeskoczy do wybranej kolumny i zaznaczy ją (rys. nr 15).
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
Otrzymamy
dane z wybranymi kolumnami przedstawione na rys. nr 17.
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
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
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
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.
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 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
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
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
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
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
Otrzymamy
podzielone dane przedstawione na rys. nr 9.
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
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
Otrzymamy
pogrupowane dane przedstawione na rys. nr 12.
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
Otrzymamy
kolumnę Paczki przedstawioną na rys. nr 14.
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
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
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
Otrzymamy posortowaną
unikatową listę produktów przedstawioną na rys. nr 18.
rys. nr 18 — posortowana unikatowa lista produktów
Ponadto
zmienimy nazwę naszej kolumny na Produkty (rys. nr 19).
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
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
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
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ę
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
Otrzymamy
pary sprzedawca – produkty. W kolumnie Produkty są tabele z listą unikatowych
produktów (rys. nr 25).
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
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
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
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
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
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
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ń
Otrzymamy
listę sprzedawców, produktów oraz ilość sprzedanych produktów (rys. nr 34).
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
Po zmianie
nazwy kolumny otrzymamy dane przedstawione na rys. nr 36.
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
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
Otrzymamy
zmienione dane przedstawione na rys. nr 39.
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
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
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
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)
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
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
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 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.
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.
