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 #62 — Wszystkie cyfry z tekstu

W dzisiejszym poście nauczymy się jak wyciągnąć wszystkie cyfry z tekstu w Power Query. W poprzednim odcinku na temat Power Query pokazywaliśmy jak wyciągnąć cyfry znajdujące się na początku tekstu bądź na jego końcu. W przykładowych danych mamy tabelę, gdzie tekst został pomieszany z cyframi (rys. nr 1).

Rys. nr 1 – przykładowe dane
Rys. nr 1 – przykładowe dane

W celu zaczytania danych do Power Query wybieramy polecenie Z tabeli/zakresu  z karty Dane (rys. nr 2).

Rys. nr 2 – polecenie Z tabeli/zakresu
Rys. nr 2 – polecenie Z tabeli/zakresu

Otworzy nam się edytor zapytań Power Query z wczytaną tabelą z przykładowymi danymi przedstawiony na rys. nr 3.

Rys. nr 3 – edytor zapytań Power Query z wczytanymi danymi
Rys. nr 3 – edytor zapytań Power Query z wczytanymi danymi

Teraz zajmiemy się odpowiednim przekształcaniem tych danych. W pierwszym kroku musimy skopiować naszą kolumnę. W tym celu wybieramy polecenie Duplikuj kolumnę  z karty Dodaj kolumnę (rys. nr 4).

Rys. nr 4 – polecenie Duplikuj kolumnę
Rys. nr 4 – polecenie Duplikuj kolumnę

Otrzymamy dane ze zduplikowaną kolumną przedstawione na rys. nr 5.

Rys. nr 5 – dane ze zduplikowaną kolumną
Rys. nr 5 – dane ze zduplikowaną kolumną

Teraz zmienimy sobie nazwę nowej kolumny w pasku formuły, aby nie dodawać nowego kroku. Zmieniamy tekst zaznaczony na rys. nr 6 na „Cyfry”. Zapis ze zmienioną nazwą kolumny powinien wyglądać następująco:

=Table.DuplikateColumn(#"Zmieniono typ", "Tekst", "Cyfry")

Rys. nr 6 – nazwa nowej kolumny, którą chcemy zmienić w pasku formuły
Rys. nr 6 – nazwa nowej kolumny, którą chcemy zmienić w pasku formuły

Jeśli nie pokazuje Ci się pasek formuły, możesz go uruchomić zaznaczając checkbox przy poleceniu Pasek formuły na karcie Widok (rys. nr 7).

Rys. nr 7 – uruchamianie paska formuły
Rys. nr 7 – uruchamianie paska formuły

Kiedy mamy już zduplikowaną kolumnę, zaznaczamy ją i rozwijamy polecenie Podziel kolumny (punkt 2 na rys. nr 8) z karty Strona główna (punkt 1), następnie wybieramy polecenie Według liczby znaków (punkt 3).

Rys. nr 8 – ścieżka dostępu do polecenia Według liczby znaków
Rys. nr 8 – ścieżka dostępu do polecenia Według liczby znaków

Otworzy nam się okno Dzielenia kolumny według liczby znaków, gdzie w polu Liczba znaków wpisujemy wartość 1, następnie wybieramy w polu Podziel checkbox przy opcji Powtarzalne. Ważne jest abyśmy rozwinęli opcje zaawansowane, gdzie w polu Podziel na wybieramy dzielenie na Wiersze. Tak ustawione parametry dzielenia kolumny zatwierdzamy przyciskiem OK (rys. nr 9).

Rys. nr 9 – parametry dzielenia kolumny według liczby znaków
Rys. nr 9 – parametry dzielenia kolumny według liczby znaków

Otrzymamy podzielone dane, których fragment został przedstawiony na rys. nr 10.

Rys. nr 10 – fragment danych podzielonych według liczby znaków
Rys. nr 10 – fragment danych podzielonych według liczby znaków

W kolumnie Tekst mamy zduplikowane nasze oryginalne wartości, a w kolumnie Cyfry mamy podział na wszystkie pojedyncze znaki. Nasza kolumna Cyfry zaimportowała się domyślnie jako dane tekstowe, musimy zmienić typ danych. W tym celu klikamy na ikonkę ABC przy nazwie kolumny i z listy rozwijanej wybieramy typ Liczna całkowita (rys. nr 11).

Rys. nr 11 – zmiana typu danych w kolumnie Cyfry
Rys. nr 11 – zmiana typu danych w kolumnie Cyfry

Pojawi nam się okienko, w którym musimy potwierdzić decyzję o zmianie typu danych za pomocą przycisku Zamień bieżącą (rys. nr 12).

Rys. nr 12 – okno Zmień typ kolumny
Rys. nr 12 – okno Zmień typ kolumny

Otrzymamy zmienione dane, gdzie w miejscach znaków tekstowych otrzymamy "Error", ponieważ Power Query nie rozpozna cyfry. Natomiast w miejscach, gdzie mieliśmy do czynienia ze spacją otrzymamy wartość "null" (rys. nr 13).

Rys. nr 13 – dane po zmianie typu danych z tekstu na liczby całkowite
Rys. nr 13 – dane po zmianie typu danych z tekstu na liczby całkowite

Dzięki temu, że otrzymaliśmy błędy (error), w miejscach znaków tekstowych, możemy je łatwo usunąć. Rozwijamy polecenie Usuń wiersze (punkt 2 na rys. nr 14) z karty Strona główna (punkt 1), następnie wybieramy polecenie Usuń błędy (punkt 3).

Rys. nr 14 – ścieżka dostępu do polecenia Usuń błędy
Rys. nr 14 – ścieżka dostępu do polecenia Usuń błędy

Otrzymamy dane po usunięciu wierszy z błędami przedstawione na rys. nr 15 .

Rys. nr 15 – dane po usunięciu wierszy z błędami (error)
Rys. nr 15 – dane po usunięciu wierszy z błędami (error)

W kolejnym kroku chcielibyśmy pozbyć się wierszy z wartościami null, które powstały w miejsce spacji. W tym celu rozwijamy filtry (ikona trójkąta obok nazwy kolumny) i z podręcznego menu wybieramy polecenie Usuń puste (rys. nr 16).

Rys. nr 16 – polecenie Usuń puste
Rys. nr 16 – polecenie Usuń puste

Otrzymamy dane bez pustych wierszy przedstawione na rys. nr 17. 

Rys. nr 17 – dane po usunięciu pustych wierszy
Rys. nr 17 – dane po usunięciu pustych wierszy

Możemy zauważyć, że przy konkretnym tekście mamy tylko te wiersze, gdzie znalazły się cyfry, bez względu czy były one na początku, na końcu czy w środku tekstu. Otrzymaliśmy wyciągnięte wszystkie cyfry z tekstu, lecz nie są one jeszcze odpowiednio zgrupowane. Zaznaczamy kolumnę Tekst i wybieramy polecenie Grupowanie według z karty Strona główna (rys. nr 18).

Rys. nr 18 – polecenie Grupowanie według
Rys. nr 18 – polecenie Grupowanie według

Otworzy nam się okno Grupowania według, gdzie wybieramy typ grupowanie Podstawowy. W polu Nazwa nowej kolumny zmieniamy domyślną nazwę Liczność na Cyfry, następnie w polu Operacja wybieramy z listy rozwijanej Wszystkie wiersze. Tak ustawione parametry grupowania według zatwierdzamy przyciskiem OK (rys. nr 19).

Rys. nr 19 – parametry Grupowania według
Rys. nr 19 – parametry Grupowania według

Otrzymamy pogrupowane dane przedstawione na rys. nr 20.

Rys. nr 20 – pogrupowane dane
Rys. nr 20 – pogrupowane dane

Pod skrótem Table ukryta jest tabelka zarówno z danymi z kolumny Tekst jak i z kolumny Cyfry. Nam zależy tylko na danych z kolumny cyfry, więc zrobimy małe przekształcenie w pasku formuły. Zamiast znaku podkreślenia, który oznacza cały wiersz wpiszemy nazwę kolumny. Zapis formuły powinien wyglądać następująco:

=Table.Group (#"Przefiltrowano wiersze", {"Tekst"}, {{"Cyfry" each [Cyfry], type table [Tekst=anynonnull, Cyfry=numer]}})

Otrzymamy dane tylko z kolumny Cyfry ukryte pod słowem List w naszej tabeli przedstawione na rys. nr 21.

Rys. nr 21 – dane pogrupowane jako lista
Rys. nr 21 – dane pogrupowane jako lista

W pasku formuły nadal mamy typ danych table oraz dodatkowe domyślne dane. Musimy wprowadzić dodatkowe zmiany, między innymi zamienić table na list. Zapis formuły powinien wyglądać następująco:

=Table.Group (#"Przefiltrowano wiersze", {"Tekst"}, {{"Cyfry" each [Cyfry], type list}})

Dzięki takiej zmianie oprócz słowa list mamy zaznaczony odpowiednią ikonką typ danych w kolumnie cyfry, co widać na rys. nr 22.

Rys. nr 22 – dane po zmianie typu na listopad
Rys. nr 22 – dane po zmianie typu na listopad

Dodatkowo dzięki takiej zmianie możemy bez problemu rozwinąć takie dane. W tym celu rozwijamy ikonkę ze strzałkami obok nazwy kolumny Cyfry i wybieramy polecenie Wyodrębnij wartości (rys. nr 23).

Rys. nr 23 – polecenie Wyodrębnij wartości
Rys. nr 23 – polecenie Wyodrębnij wartości

Otworzy nam się okno Wyodrębniania wartości z listy, gdzie z listy rozwijanej musimy wybrać Ogranicznik, jakiego Power Query ma użyć do połączenia danych. W naszym przykładzie, ze względu na to że mamy do czynienia z cyframi wybieramy Brak ogranicznika. Tak ustawione parametry wyodrębniania wartości zatwierdzamy przyciskiem OK (rys. nr 24).

Rys. nr 24 – parametry Wyodrębniania wartości z listy
Rys. nr 24 – parametry Wyodrębniania wartości z listy

Otrzymamy wyodrębnione cyfry w drugiej kolumnie przedstawione na rys. nr 25. 

Rys. nr 25 – dane wyodrębnione z listy
Rys. nr 25 – dane wyodrębnione z listy

Co istotne mimo że wyciągnęliśmy z danych cyfry mają one przypisany typ danych Tekst. Gdyby zależało nam żeby zmienić typ danych na liczby to stracilibyśmy zera wiodące, czyli nie otrzymalibyśmy wszystkich cyfr z tekstu.

Tak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Strona główna (rys. nr 26).

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

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

Rys. nr 27 – okno Importowania danych
Rys. nr 27 – okno Importowania danych

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

Rys. nr 28 – dane wstawione do Excela
Rys. nr 28 – dane wstawione do Excela

Jak widać Power Query bez problemu poradził sobie z wyciągnięciem wszystkich cyfr z tekstu bez względu, gdzie się one znajdowały, czy były na końcu tekstu, czy na początku, czy też w środku.


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 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 54 — Matura z informatyki 2018 — Cykl pracy zbiornika zad 5.4

W dzisiejszym poście będziemy kontynuować rozwiązywanie zadań z Matury z informatyki 2018. Dziś rozwiążemy zadanie nr 5.4 w Power Query. Rozwiązanie tego zadania pomogł mi znaleźć Bill Szysz. Był on w stanie rozwiązać całe zadanie w jednym zapytaniu, lecz okazało się to bardzo skomplikowane. W jego rozwiązaniu część zadania wykonano w Power Query a wyciągnięcie samych wyników nastąpiło w Excelu. Nam zależy aby całe zadanie rozwiązać w Power Query. Rozbijemy zadanie na kilka zapytań, aby łatwiej znaleźć rozwiązanie.

Zadanie nr 5.4 opiera się o cykl pracy zbiornika retencyjnego. W pierwszym kroku zaczytamy dane z tabeli w Excelu. Wybieramy polecenie Z tabeli/ zakresu z karty Dane (rys. nr 1). 

rys. nr 1 - polecenie Z tabeli/zakresu
rys. nr 1 — polecenie Z tabeli/zakresu

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

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

Dane te musimy odpowiednio przekształcić. W pierwszym kroku musimy zmienić typ danych, możemy to zrobić usuwając słowo time z formuły w pasku formuły (zaznaczone na niebiesko na rys. nr 3). Power Query automatycznie wybrał typ danych data i czas, a nam czas nie jest potrzebny, interesuje nas tylko data.

rys. nr 3 - Zmiana w formule - usunięcie czasu z daty
rys. nr 3 — Zmiana w formule — usunięcie czasu z daty

Otrzymamy dane ze zmienionym typem danych w kolumnie Data przedstawione na rys. nr 4.

rys. nr 4 - zmieniony typ danych w kolumnie Data
rys. nr 4 — zmieniony typ danych w kolumnie Data

Nas interesuje tylko kolumna Woda m3. Klikamy prawym przyciskiem myszy na tytuł kolumny Woda m3 i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 5).

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

Jeśli skorzystamy z powyższego polecenia Power Query użyje funkcji Table.SelectColumn, która zwraca nam tabelę. Nasze zapytanie nazywa się tDane6, zmienimy jego nazwę w Ustawieniach zapytania na tWoda (rys. nr 6).

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

Ikonka przy nazwie zapytania oznaczona strzałką na rys. nr 7 wskazuje nam, że nasze dane mają formę tabeli.

rys. nr 7 - dane w formie tabeli
rys. nr 7 — dane w formie tabeli

My nie potrzebujemy tabeli, więc musimy zmienić użytą formułę, czyli zamiast funkcji Table.SelectColumns powinniśmy użyć funkcji Table.Column. Zapis formuły powinien wyglądać następująco:

=Table.Column (#"Zmieniono typ", "Woda m3")

Otrzymamy dane w postaci listy przedstawione na rys. nr 8.

rys. nr 8 - dane w postaci listy
rys. nr 8 — dane w postaci listy

Aby mieć pewność, że ta lista się zmieni, tzn. nie zmieni się kolejność elementów musimy dołożyć dodatkową funkcję o nazwie List.Buffer. Zapis formuły powinien wyglądać następująco:

=List.Buffer(Table.Column (#"Zmieniono typ", "Woda m3"))

Funkcja List.Buffer sprawia, że nasza lista nie ulegnie zmianie, tzn. że w tej liście mamy ilość wody wpływającej do zbiornika każdego kolejnego dnia i kolejność wpływów wody nie może ulec zmianie. Aby mieć jasne informacje, że dany krok odpowiada za przekształcenie danych z tabeli w listę, zmieniamy sobie nazwę kroku Usunięto inne kolumny na LWoda w Ustawieniach zapytania za pomocą klawisza F2 (rys. nr 9).

rys. nr 9 - zmiana nazwy kroku
rys. nr 9 — zmiana nazwy kroku

Zmieniamy nazwę kroku, ponieważ w późniejszym etapie będziemy się do niego odwoływać.

Kolejnym krokiem jest policzenie ilości wierszy w danych. Ze względu na to iż zmieniliśmy typ danych (nasze dane już nie są tabelą), nie możemy korzystać z operacji przekształcania danych na karcie Przekształć. Musimy dodać kolejny krok za pomocą ikonki fx przy pasku formuły, ponadto ten krok musimy napisać samodzielnie. Skorzystamy tutaj z funkcji Table.RowCount, pamiętając o użyciu odpowiedniej wielkości liter, ponieważ Power Query jest case sensitive. W funkcji tej musimy się odwołać do kroku, w którym mamy jeszcze cała tabelę, czyli u nas do kroku Zmieniono typ. Klikamy na nazwę kroku, przechodzimy do edycji nazwy kroku za pomocą klawisza F2 a następnie kopiujemy jego nazwę za pomocą skrótu klawiszowego Ctrl+C. Należy pamiętać, aby przed wklejeniem nazwy kroku wpisać znak hashtag (#) ponieważ w nazwie kroku występuje spacja. Zapis formuły powinien wyglądać następująco:

=Table.RowCount (#"Zmieniono typ")

Otrzymamy ilość wierszy w naszych danych przedstawioną na rys. nr 10.

rys. nr 10 - ilość wierszy w danych
rys. nr 10 — ilość wierszy w danych

Ilość wierszy będzie nam potrzebna do następnego, najtrudniejszego kroku, gdzie za pomocą funkcji List.Generate wyznaczymy listę rekordów. Funkcja ta ma 4 argumenty, więcej szczegółów na temat funkcji przedstawia rys. nr 11.

rys. nr 11 - informacje o funkcji List.Generate
rys. nr 11 — informacje o funkcji List.Generate

Pierwszym argumentem są wartości początkowe, czyli wartość z pierwszego wiersza, drugi argument to warunek, przy którym zatrzymujemy generowanie listy, a trzeci to sposób w jaki przekształcimy dane aby otrzymać kolejne wartości. Działanie funkcji wyjaśnimy na prostym przykładzie przedstawionym na rys. nr 12.

rys. nr 12 - przykładowe argumenty funkcji List.Generate
rys. nr 12 — przykładowe argumenty funkcji List.Generate

Zaczynami liczenie od wartości 10, czyli początkową wartością jest liczba 10. Pierwszy warunek jest taki, że wykonujemy obliczenia dopóki ta wartość jest większa od 0, drugi warunek to odjęcie wartości 1 na każdym kroku. Taka formuła funkcji generuje listę wartości od 10 do 1 (rys. nr 13).

rys. nr 13 - wynik działania przykładowej funkcji z rysunku powyżej
rys. nr 13 — wynik działania przykładowej funkcji z rysunku powyżej

W naszym zadaniu będziemy korzystać z dużo bardziej skomplikowanej formuły, ponieważ musimy wygenerować dużo więcej danych, które muszą spełnić określone warunki (rys. nr 14).

rys. nr 14 - zapis funkcji do zadania 5.4
rys. nr 14 — zapis funkcji do zadania 5.4

Pierwsze dwie linijki formuły z rysunku nr 14 to pierwszy argument funkcji, czyli wartości początkowe. Zapis w nawiasach kwadratowych oznacza, że mamy do czynienia z rekordem. Odwołujemy się do poszczególnych kolumn, najpierw do kolumny Data (początkowa data to 31.12.2007), której odpowiada wartość z kolumny Woda m3, czyli wartość 0 dla tej daty. Stan= 500000 to stan wody w zbiorniku na początku obliczeń. W danych do zadania mamy informację, że należy wylać ze zbiornika 2% stanu wody z dnia mierzenia, stąd wartość Wylać=10000. Są to podstawowe kolumny, których potrzebujemy. Druga linijka (wartości _2) to informacje do rozwiązania trzeciego podpunktu zadania 5.4 z matury z informatyki. Wartość i jest wartością pomocniczą, zliczającą w którym aktualnie jesteśmy wierszu. Zapis formuły powinien wyglądać następująco:

=List.Generate (()=> [Date=#date(2007,12,31), #Woda m3=0, Stan=500000, Wylać=10000, Stan_2=Stan, Wylać_2=Wylać, I=0]

Musimy zmienić nazwę kroku Niestandardowe 1, w którym obliczaliśmy ilość wierszy, na RowCount (Ustawienia zapytania) – rys. nr 15.

rys. nr 15 - zmiana nazwy kroku
rys. nr 15 — zmiana nazwy kroku

Jest to warunek, że tworzymy listę dopóki nie wygenerujemy wszystkich wartości z kroku RowCount, czyli wszystkich dni. Ma zostać spełniony warunek, że i jest mniejsze od ilości wierszy +1. Jest to nasz drugi argument funkcji. Warunek powinien wyglądać następująco:

each [i] < RowCount+1

Trzeci argument funkcji obejmuje wszystkie obliczenia jakie musimy wykonać w zadaniu. W kolejnym kroku wartość z kolumny Date jest równa wartości z kolumny Date z poprzedniego wiersza powiększona o 1. Korzystamy tutaj z funkcji Date.AddDays, czyli dodajemy dni do wiersza powyżej. Wartość w kolumnie Woda m3 jest równa wartości z kroku LWoda dla określonego wiersza i. Stan wody jest równy stanowi wody z wcześniejszego kroku pomniejszony o ilość wody, którą należało wylać oraz powiększony o ilość dowy jaka włynęła w danym dniu (Woda m3).

Warunek ilości wylewanej wody polega na tym, że jeśli stan zbiornika przekracza 1000000 m³ wody to trzeba wylać tą nadwyżkę (ilość która przekracza 1000000 m³), a następnego dnia rano wylać dodatkowo 2% stanu w momencie pomiaru. W przeciwnej sytuacji, czyli kiedy zbiornik nie jest przepełniony to należy wylać tylko 2% stanu zbiornika z momentu pomiaru. Wartości te mają być zaokrąglone w górę, czyli użyjemy tutaj funkcji Number.RoundUp.

Podobne obliczenia wykonamy dla Stanu_2, gdzie nie wylewamy wody jeśli przekroczymy 1000000 m³ wody w zbiorniku, ale zawsze wylewamy te 2% stanu z czasu pomiaru. Cały drugi warunek powinien wyglądać następująco:

each [

            Date=Date.AddDays ([Date], 1),

            # "Woda m3" = LWoda { [i] },

            Stan = [Stan] – [Wylać] + # "Woda m3",

            Wylać = if Stan > 1000000 then (Stan – 1000000) + Number.RoundUp (0,02* ([Stan] – [Wylać] + # "Woda m3")) else Number.RoundUp (0,02* ([Stan] – [Wylać] + # "Woda m3")),

            Stan_2 = [Stan_2] — [Wylać] + # "Woda m3",

Wylać_2 = Number.RoundUp (0,02* ([Stan_2] – [Wylać_2] + # "Woda m3")),

i = [i] +1

]

Całą formułę przedstawioną na rys. nr 14 zaznaczamy i kopiujemy za pomocą skrótu klawiszowego Ctrl+C, a następnie w Power Query dodajemy nowy krok za pomocą ikonki fx i wklejamy skopiowaną formułę do paska formuły za pomocą skrótu klawiszowego Ctrl+V. Zatwierdzamy przyciskiem Enter i otrzymamy dane przedstawione na rys. nr 16.

rys. nr 16 lista rekordów
rys. nr 16 lista rekordów

Otrzymaliśmy listę rekordów, zawierającą informacje o dacie, stanie wody danego dnia, ilości wody jaką musimy wylać itd., przedstawioną na rys. nr 17

rys. nr 17 - każdy rekord zawiera szczegółowe dane
rys. nr 17 — każdy rekord zawiera szczegółowe dane

W kolejnym kroku musimy przekształcić nasze dane z listy na tabelę, skorzystamy tutaj z polecenia Do tabeli z karty Przekształć (rys. nr 18).

rys. nr 18 - konwertowanie listy do tabeli
rys. nr 18 — konwertowanie listy do tabeli

Otworzy nam się okno Do tabeli, gdzie musimy wybrać ogranicznik, jakim mają być oddzielone dane (wybieramy Brak) oraz w polu Sposób obsługi dodatkowych kolumn wybieramy opcję Pokaż jako błędy. Tak ustawione parametry konwertowania danych do tabeli zatwierdzamy przyciskiem OK (rys. nr 19).

rys. nr 19 - okno Do tabeli - parametry konwertowania
rys. nr 19 — okno Do tabeli — parametry konwertowania

Otrzymamy listę rekordów przekonwertowaną na tabelę co widać na rys. nr 20.

rys. nr 20 - tabela z rekordami
rys. nr 20 — tabela z rekordami

Nasze rekordy dalej wyglądają po rozwinięciu tak samo jak dla listy ale tym razem zapisane są w formie tabeli. Dzięki zmianie typu danych na tabelę możemy rozwinąć te dane. Klikamy na ikonkę ze strzałkami obok nazwy kolumny Column1, zaznaczamy wszystkie kolumny (oprócz kolumny i, która była tylko kolumną pomocniczą) i odznaczamy opcję Użyj oryginalnej nazwy kolumny jako prefiksu, następnie zatwierdzamy rozwijanie danych przyciskiem OK (rys. nr 21).

rys. nr 21 - rozwijanie rekordów tabeli
rys. nr 21 — rozwijanie rekordów tabeli

Otrzymamy rozwinięte dane w postaci tabeli przedstawione na rys. nr 22.

rys. nr 22 - rozwinięte dane w tabeli
rys. nr 22 — rozwinięte dane w tabeli

Kolumny Stan i Wylać są nam potrzebne do rozwiązania podpunktu a i b z zadania a kolumny Stan_2 i Wylać_2 do rozwiązania podpunktu c. Ostatnim etapem pracy nad danymi w Power Query jest dopasowanie typu danych. Kolumna Date powinna być typu Data, a pozostałe kolumny typu Liczba całkowita (rys. nr 23).

rys. nr 23 - zmiana typu danych w poszczególnych kolumnach
rys. nr 23 — zmiana typu danych w poszczególnych kolumnach

Dla każdej części zadania zrobimy sobie osobne zapytanie, bo uda nam się wykorzystać proste polecenia ze wstążki. Tak przygotowane dane możemy załadować do Excela, korzystając z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 24).

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

W Excelu otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela oraz wskazujemy miejsce wstawienia danych – konkretna komórka w istniejącym arkuszu. Tak ustawione parametry wstawienia danych zatwierdzamy przyciskiem OK (rys. nr 25).

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

Otrzymamy dane zaczytane do Excela przedstawione na rys. nr 26.

rys. nr 26 - dane zaczytane do Excela
rys. nr 26 — dane zaczytane do Excela

Mamy do rozwiązania trzy części zadania przedstawione na rys. nr 27. Pierwszy podpunkt zadania polega na wyznaczeniu dnia, w którym pierwszy raz wypuszczono nadmiar wody po przepełnieniu.

rys. nr 27 - treść zadań do wykonania
rys. nr 27 — treść zadań do wykonania

Aby uzyskać rozwiązanie w Power Query klikamy dwukrotnie na zapytanie tWoda w zakładce Zapytania i połączenia, aby przejść do Edytora zapytań Power Query. Otworzy nam się Edytor zapytań z naszymi danymi, gdzie klikamy prawym przyciskiem myszy na nazwę zapytania tWoda i z podręcznego menu wybieramy polecenie Odwołanie (rys. nr 28).

rys. nr 28 - polecenie Odwołanie
rys. nr 28 — polecenie Odwołanie

Powstanie zapytanie tWoda (2), któremu zmieniamy nazwę na OdpA w Ustawieniach zapytania. Nasze zadanie polega na znalezieniu daty, kiedy w zbiorniku było powyżej 1000000 m3 wody, czyli wystarczy nałożyć filtr po kolumnie Stan. Rozwijamy menu klikając na ikonkę z trójkątem przy nazwie kolumny i rozwijamy polecenie Filtry liczb, następnie wybieramy polecenie Większe niż (rys. nr 29).

rys. nr 29 - ścieżka dostępu do polecenia Większe niż
rys. nr 29 — ścieżka dostępu do polecenia Większe niż

Otworzy nam się okienko Filtrowania wierszy, gdzie w polu jest większe niż wpisujemy wartość od której chcemy otrzymać większą wartość (wpisujemy 1000000). Tak ustawiony próg zatwierdzamy przyciskiem OK (rys. nr 30).

rys. nr 30 - okno Filtrowania wierszy
rys. nr 30 — okno Filtrowania wierszy

Otrzymamy przefiltrowane dane tylko z wierszami, w których stan wody przekraczał 1000000 m3 wody przedstawione na rys. nr 31 .

rys. nr 31 - przefiltrowane dane
rys. nr 31 — przefiltrowane dane

Interesuje nas pierwszy dzień, w którym ilość wody w zbiorniku przekroczyła 1000000 m3. Z naszych danych wynika, że tym dniem jest 18.04.2019 ale musimy pamiętać że pomiar danego dnia odbywa się tuż po północy, więc faktycznie jest to już 19.04.2019. Możemy to rozwiązać klikając prawym przyciskiem myszy na pierwszą komórkę z datą i z podręcznego menu wybrać polecenie Wyszczególnij (rys. nr 32).

rys. nr 32 - polecenie Wyszczególnij w podręcznym menu
rys. nr 32 — polecenie Wyszczególnij w podręcznym menu

Otrzymamy wyszczególnioną datę 18.04.2019. W pasku formuły mamy zapis, że został wyciągnięty wiersz o indeksie 0, ponieważ Power Query zaczyna numerację wierszy od 0, a nie jak Excel od 1 oraz informację z jakiej kolumny została wyciągnięta informacja (rys. nr 33).

rys. nr 33 - wyszczególnione dane
rys. nr 33 — wyszczególnione dane 

Problem polega na tym, że dalej wyświetla się nam 18.04.2019 a wiemy że poprawną odpowiedzią jest 19.04.2019. Aby otrzymać prawidłową odpowiedź w formule możemy dodać jeden dzień. W celu dodania jednego dnia musimy skorzystać z funkcji #duration  a następnie w nawiasie wpisać ilość dni jakie chcemy dodać oraz ilość godzin, minut i sekund (1,0,0,0). Zapis całej formuły powinien wyglądać następująco:

=#"Przefiltrowano wiersze"{0}[Date] + #duration (1,0,0,0)

Po zatwierdzeniu powyższej formuły otrzymamy prawidłową odpowiedź przedstawioną na rys. nr 34.

rys. nr 34 - działanie funkcji #duration
rys. nr 34 — działanie funkcji #duration

Uzyskaliśmy odpowiedź na pierwszą część zadania, którą teraz możemy załadować do Excela korzystając z polecenia Zamknij i załaduj do z karty Narzędzia główne (jak na rys. nr 24). Otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela oraz wskazujemy gdzie chcemy wstawić dane – konkretna komórka w istniejącym arkuszu. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (rys. nr 35).

rys. nr 35 - okno Importowania danych do Excela
rys. nr 35 — okno Importowania danych do Excela

Otrzymamy odpowiedź na pierwszą część zadania wstawioną do Excela przedstawioną na rys. nr 36.

rys. nr 36 - rozwiązanie pierwszej części zadania
rys. nr 36 — rozwiązanie pierwszej części zadania

Druga część zadania polega na podaniu liczby dni, kiedy poziom wody w zbiorniku przekraczał 800 000 m3. Po raz kolejny klikamy dwukrotnie na zapytanie tWoda w panelu bocznym Zapytania i połączenia i przechodzimy do Edytora zapytań Power Query. Następnie robimy odwołanie do tego zapytania klikając na jego nazwę prawym przyciskiem myszy i wybierając z podręcznego menu polecenie Odwołanie (jak na rys. nr 28). W ustawieniach zapytania zmieniamy jego nazwę na OdpB. Aby uzyskać wyniki stanu wody powyżej 800 000 m3 wystarczy przefiltrować dane. Jak w rozwiązaniu pierwszej części zadania klikamy na ikonkę z trójkątem obok nazwy kolumny Stan, następnie rozwijamy polecenie Filtry liczb i wybieramy polecenie Większe niż (jak na rys. nr 29). Otworzy nam się okno Filtrowania wierszy, gdzie w polu jest większe niż wpisujemy naszą wartość – 800000. Ustawiony próg zatwierdzamy przyciskiem OK (rys. nr 37).

rys. nr 37 - okno Filtrowania wierszy - parametry filtrowania
rys. nr 37 — okno Filtrowania wierszy — parametry filtrowania

Otrzymamy wszystkie wiersze, dla których stan wody przekraczał 800000 m3 przedstawione na rys. nr 38.

rys. nr 38 - przefiltrowane dane
rys. nr 38 — przefiltrowane dane

Aby otrzymać ilość tych wierszy zaznaczamy kolumnę Data i rozwijamy polecenie Statystyka (punkt 2 na rys. nr 39) z karty Przekształć (punkt 1), a następnie wybieramy polecenie Zlicz wartości (punkt 3).

rys. nr 39 - ścieżka dostępu do polecenia Zlicz wartości
rys. nr 39 — ścieżka dostępu do polecenia Zlicz wartości

Otrzymamy liczbę wierszy, gdzie stan wody przekraczał 800 000m3 przedstawioną na rys. nr 40.

rys. nr 40 - odpowiedź do drugiej części zadania
rys. nr 40 — odpowiedź do drugiej części zadania

Możemy zauważyć że zapytania z odpowiedziami do punktu A i B różnią się ikonkami. OdpA jest datą, a OdpB jest liczbą co widać na rys. nr 41.

rys. nr 41 - typy danych w odpowiedziach do poszczególnych części zadania 5.4
rys. nr 41 — typy danych w odpowiedziach do poszczególnych części zadania 5.4

Uzyskaliśmy odpowiedź na drugą część zadania, którą teraz możemy załadować do Excela korzystając z polecenia Zamknij i załaduj do z karty Narzędzia główne (jak na rys. nr 24). Otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela oraz wskazujemy gdzie chcemy wstawić dane – konkretna komórka w istniejącym arkuszu. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (jak na rys. nr 35).

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

rys. nr 42 - odpowiedź na drugą część zadania w Excelu
rys. nr 42 — odpowiedź na drugą część zadania w Excelu

Trzecia część zadania polega na podaniu największego stanu wody w zbiorniku, gdyby zrezygnować z wylewania jej nadmiaru po przekroczeniu 1000000m3. W zadaniu tym będziemy korzystać z kolumny Stan_2. Przechodzimy do Edytora zapytań Power Query klikając dwukrotnie na zapytanie tWoda w Excelu. Po raz kolejny robimy odwołanie do tego zapytania klikając na jego nazwę prawym przyciskiem myszy i wybierając z podręcznego menu polecenie Odwołanie (jak na rys. nr 28). W ustawieniach zapytania zmieniamy jego nazwę na OdpC. Aby uzyskać rozwiązanie zadania wystarczy znaleźć wartość maksymalną w kolumnie Stan_2. Zaznaczamy kolumnę Stan_2, następnie rozwijamy polecenie Statystyka (punkt 2 na rys. nr 43) z karty Przekształć (punkt 1) i wybieramy polecenie Maksimum (punkt 3).

rys. nr 43 - ścieżka dostępu do polecenia Maksimum
rys. nr 43 — ścieżka dostępu do polecenia Maksimum

Otrzymamy maksymalny stan zbiornika przedstawiony na rys. nr 44.

rys. nr 44 - odpowiedź na trzecią część zadania - maksymalny stan zbiornika
rys. nr 44 — odpowiedź na trzecią część zadania — maksymalny stan zbiornika

Uzyskaliśmy odpowiedź na trzecią część zadania, którą teraz możemy załadować do Excela korzystając z polecenia Zamknij i załaduj do z karty Narzędzia główne (jak na rys. nr 24). Otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela oraz wskazujemy gdzie chcemy wstawić dane – konkretna komórka w istniejącym arkuszu. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (jak na rys. nr 35).

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

rys. nr 45 - odpowiedzi na trzy części zadania zaczytane do Excela
rys. nr 45 — odpowiedzi na trzy części zadania zaczytane do Excela

Na tym kończymy rozwiązywanie zadań z matury z informatyki z 2018 roku.


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 52 — Matura z informatyki 2018 — Najdłuższy okres zad 5.2

W dzisiejszym poście będziemy kontynuować rozwiązywanie zadań z Matury 2018 z informatyki. Dziś zajmiemy się rozwiązaniem zadania nr 5.2. Treść zadania została przedstawiona na rys. nr 1.

rys. nr 1 - treść zadania nr 5.2 z matury z informatyki 2018
rys. nr 1 — treść zadania nr 5.2 z matury z informatyki 2018

Naszym zadaniem jest znalezienie najdłuższego okresu, kiedy do zbiornika retencyjnego codziennie dopływało co najmniej 10 000 metrów sześciennych wody. Kolejnym krokiem jest znalezienie daty początkowej i końcowej tego okresu. Zadanie to zostanie wykonane w Power Query. Dane bazowe mamy już zaczytane (zostały zaczytane do poprzedniego zadania w Power Query odcinku 51). Rys. nr 2

rys. nr 2 - wczytane dane bazowe
rys. nr 2 — wczytane dane bazowe

Otwieramy nasze zapytanie w Edytorze zapytań Power Query. W pierwszym kroku musimy sobie przygotować dodatkowe zapytanie z tymi danymi, na którym będziemy pracować. W tym celu rozwijamy panel boczny Zapytania i klikamy prawym przyciskiem myszy na nazwę zapytania Woda a następnie wybieramy z podręcznego menu polecenie Odwołanie (rys. nr 3).

rys. nr 3 - polecenie Odwołanie z podręcznego menu
rys. nr 3 — polecenie Odwołanie z podręcznego menu

Otrzymamy nowe zapytanie z naszymi danymi, którego nazwę zmienimy na Woda 5_2 we właściwościach zapytania w polu Nazwa (rys. nr 4).

rys. nr 4 - zmiana nazwy zapytania
rys. nr 4 — zmiana nazwy zapytania

W pierwszym kroku musimy znaleźć okresy kiedy do zbiornika wpływa powyżej 10 tys. Metrów sześciennych wody. W tym celu wybieramy polecenie Kolumna warunkowa z karty Dodaj kolumnę (rys. nr 5).

rys. nr 5 - ścieżka dostępu do polecenia Kolumna warunkowa
rys. nr 5 — ścieżka dostępu do polecenia Kolumna warunkowa

Otworzy nam się okno Dodawanie kolumny warunkowej, w którym musimy określić parametry nowej kolumny. W polu Nazwa kolumny wybieramy kolumnę, którą chcemy sprawdzić, czyli Woda m3. W polu Operator wybieramy rodzaj porównania, czyli w naszym przykładzie Jest większe niż, w polu Wartość wpisujemy 10 000, ponieważ szukamy okresów kiedy ilość wody wpływającej do zbiornika przekracza tę wartość. W polu Wartość wyjściowa wpisujemy wartość 1, natomiast w polu W przeciwnym wypadku – wartość 0. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 6).

rys. nr 6 - parametry kolumny warunkowej
rys. nr 6 — parametry kolumny warunkowej

Otrzymamy dane z nową kolumną z wartościami 1 lub 0 przedstawioną na rys. nr 7.

rys. nr 7 - dane z nową kolumną warunkową
rys. nr 7 — dane z nową kolumną warunkową

Aby otrzymać tylko dane z wartościami 1, wystarczy przefiltrować dane. Klikamy ikonę trójkąta przy nazwie kolumny Niestandardowe i w filtrach odznaczamy checkbox przy wartości 0, następnie nasz wybór zatwierdzamy przyciskiem OK (rys. nr 8).

rys. nr 8 - filtrowanie danych
rys. nr 8 — filtrowanie danych

Otrzymamy przefiltrowane dane, mianowicie tylko te z wartością 1 w kolumnie niestandardowe, przedstawione na rys. nr 9.

rys. nr 9 - przefiltrowane dane
rys. nr 9 — przefiltrowane dane

W naszych danych na tym etapie mamy wszystkie dni, w których wpłynęło więcej niż 10 000 metrów sześciennych wody. Usuwamy krok Przefiltrowano wiersze, ponieważ łatwiej będzie nam znaleźć okresy, kiedy ta wartość była równa 1. Naszym celem jest pogrupowanie danych po okresach. Z karty Narzędzia główne wybieramy polecenie Grupowanie według (rys. nr 10).

rys. nr 10 - ścieżka dostępu do polecenia Grupowanie według
rys. nr 10 — ścieżka dostępu do polecenia Grupowanie według

Otworzy nam się okno Grupowania według, gdzie wybieramy opcję zaawansowanego grupowania(punkt 1 na rys. nr 11). Wynika to z tego, że potrzebujemy długości okresu, daty początkowej i końcowej, czyli aż trzech parametrów. W polu Grupuj według wybieramy kolumnę Niestandardowe (punkt 2). W polu operacja wybieramy Minimum (punkt 3) aby otrzymać początek danego okresu. Następnie dodajemy jeszcze dwie agregacje w których w polu Operacja wybieramy Maksimum (punkt nr 4), aby otrzymać koniec danego okresu oraz Zlicz wiersze (punkt 5), aby otrzymać sumę dni z danego okresu. Dla operacji Minimum wpisujemy nazwę nowej kolumny jako Data początku okresu (punkt 6). Dla operacji Maksimum wpisujemy nazwę nowej kolumny Data końca okresu (punkt 7), a dla Zlicz wiersze wpisujemy Ilość dni (punkt 8). Tak ustawione parametry zatwierdzamy przyciskiem OK.

rys. nr 11 - parametry grupowania według
rys. nr 11 — parametry grupowania według

Otrzymamy dane, gdzie będą tylko dwa okresy, jeden dla wartości 0 a drugi dla wartości 1. Nie jest to wynik, o który nam chodziło (rys. nr 12).

rys. nr 12 - pogrupowane dane (globalnie)
rys. nr 12 — pogrupowane dane (globalnie)

Naszym celem jest uzyskanie każdego okresu, kiedy wartości w kolumnie Niestandardowe ulegają zmianie. Za pomocą Power Query możemy to uzyskać. Przede wszystkim musimy dokładnie sprawdzić jak działa funkcja Table.Group. W tym celu wycinamy z paska formuły za pomocą skrótu klawiszowego Ctrl+X argumenty funkcji Table.Group (część zaznaczona na niebiesko na rys. nr 13) a następnie samą funkcję zatwierdzamy przyciskiem Enter.

rys. nr 13 - wycięte argumenty funkcji Table.Group
rys. nr 13 — wycięte argumenty funkcji Table.Group

Power Query opisuje nam działanie tej funkcji i jej parametry. W polu groupKind jest coś takiego jak sposób grupowania (rys. nr 14).

rys. nr 14 - opcjonalny parametr funkcji Table.Group - groupKind
rys. nr 14 — opcjonalny parametr funkcji Table.Group — groupKind

Możemy wybrać grupowanie globalne, które jest parametrem domyślnym lub grupowanie lokalne, czyli GroupKind.Local. Jest to grupowanie po pojedynczych okresach w danych, które ulegają zmianie. Każdorazowo gdy dane w kolumnie Niestandardowe się zmienią otrzymamy nowy okres. Podsumowując wklejamy wcześniej wycięte argumenty funkcji do paska formuły, a następnie dopisujemy opcjonalny argument funkcji GroupKind.Local i zatwierdzamy przyciskiem Enter. Musimy pamiętać aby dobrze wpisać nazwę argumentu ponieważ Power Query jest Case Sensitive, czyli zwraca uwagę na wielkość liter (rys. nr 15).

rys. nr 15 - zapis funkcji Table.Group z dodatkowych parametrem
rys. nr 15 — zapis funkcji Table.Group z dodatkowych parametrem

Otrzymamy tym razem prawidłowe dane, przedstawione na rys. nr 16.

rys. nr 16 - dane pogrupowane okresami
rys. nr 16 — dane pogrupowane okresami

Otrzymaliśmy poszczególne okresy, kiedy wpływało do zbiornika więcej niż 10 000 metrów sześciennych wody i kiedy wpływało mniej. Przede wszystkim interesują nas okresy powyżej 10 000 metrów sześciennych wody więc możemy użyć filtrów. W tym celu klikamy na ikonkę trójkąta przy nazwie kolumny Niestandardowa i odznaczamy checkbox przy wartości 0. Tak ustawiony filtr zatwierdzamy przyciskiem OK (rys. nr 17).

rys. nr 17 - filtrowanie danych
rys. nr 17 — filtrowanie danych

Możemy również osunąć kolumnę o nazwie Niestandardowa dla lepszej prezentacji danych za pomocą klawisza Delete. Naszym zadaniem jest znalezienie najdłuższego okresu, kiedy do zbiornika wpływało powyżej 10 000 metrów sześciennych wody, czyli posortujemy kolumnę Ilość dni od Z do A z karty Narzędzia główne (rys. nr 18).

rys. nr 18 - sortowanie danych od Z do A
rys. nr 18 — sortowanie danych od Z do A

Interesuje nas tylko pierwszy wiersz posortowanych danych więc tak jak w poprzednim poście skorzystamy z polecenia Zachowywania wierszy. Rozwijamy polecenie Zachowaj wiersze (punkt 2 na rys. nr 19) z karty Narzędzia główne (punkt 1), a następnie wybieramy polecenie Zachowywanie pierwszych wierszy (punkt 3).

rys. nr 19 - ścieżka dostępu do polecenia Zachowywanie pierwszych wierszy
rys. nr 19 — ś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ć, czyli 1. Tak ustawiony parametr zatwierdzamy przyciskiem OK (rys. nr 20).

rys. nr 20 - okno zachowywania pierwszych wierszy
rys. nr 20 — okno zachowywania pierwszych wierszy

Otrzymamy jeden wiersz z danymi przedstawiającymi początek, koniec i długość najdłuższego okresu, w którym do zbiornika retencyjnego wpływało ponad 10 000 metrów sześciennych wody (rys. nr 21).

rys. nr 21 - dane po usunięciu niepotrzebnych wiwerszy
rys. nr 21 — dane po usunięciu niepotrzebnych wiwerszy

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

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

W Excelu otworzy nam się okno Importowania danych, gdzie ustawiamy ich parametry, mianowicie sposób wyświetlania danych jako Tabela oraz wskazujemy miejsce wstawienia danych jako istniejący arkusz i wskazujemy konkretną komórkę. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 23)

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

Otrzymamy dane wstawione do Excela przedstawione na rys. nr 24, czyli rozwiązanie zadania 5.2 z matury z informatyki 2018.

rys. nr 24 - dane w Excelu, rozwiązanie zadania 5.2 z matury z informatyki w Power Query
rys. nr 24 — dane w Excelu, rozwiązanie zadania 5.2 z matury z informatyki w Power Query

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 50 — Automatyczne wybieranie kolumn z tabeli z listy

W tym poście zajmiemy się automatycznym wybieraniem kolumn z tabeli z listy.

W Power Query odcinku 48 pokazywaliśmy jak ręcznie z dużej ilości danych wybrać konkretne kolumny ( https://www.youtube.com/watch?v=sy0ZAca-0qw ). Pod tym postem otrzymaliśmy pytanie od widza, jak to zrobić bazując na liście, czyli zakładamy że mamy listę kolumn, które chcemy wyciągnąć z większej tabeli z danymi.

Zadanie to wykonamy na przykładowych danych z rysunku nr 1.

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

Okazuje się to bardzo proste. Wystarczy że mamy listę kolumn, których potrzebujemy. Ustawiamy aktywną komórkę w obszarze tej listy i zaczytujemy dane do Power Query za pomocą polecenia Z tabeli/ zakresu z karty Dane (rys. nr 2).

rys. nr 2 - Z tabeli/zakresu
rys. nr 2 — Z tabeli/zakresu

Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą tListaKolumn, która zawiera wartości null w pustych komórkach. Usunięciem tych wartości zajmiemy się w późniejszym etapie (rys. nr 3).

rys. nr 3 - Edytor zapytań Power Query z wczytaną tabelą tListaKolumn
rys. nr 3 — Edytor zapytań Power Query z wczytaną tabelą tListaKolumn

Co istotne nasze zapytanie zostało wczytane jako tabela, co widać w Liście zapytań (zaznaczone czerwoną strzałką na rys. nr 4).

rys. nr 4 - dane wczytane do Power Query jako Tabela
rys. nr 4 — dane wczytane do Power Query jako Tabela

Do dalszych działań potrzebujemy listy a nie tabeli. W tym celu wybieramy ikonkę polecenia Konwertuj na listę z karty Przekształć (rys. nr 5).

rys. nr 5 - polecenie Konwertuj na listę
rys. nr 5 — polecenie Konwertuj na listę

Otrzymamy przekonwertowane dane na listę przedstawione na rys. nr 6.

rys. nr 6 - Przekonwertowane dane na listę
rys. nr 6 — Przekonwertowane dane na listę

Na tym etapie pracy wykonaliśmy wszystko, co było potrzebne z naszymi danymi. Tak przygotowane dane musimy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 7).

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

Otworzy się nam okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Połączenie, czyli zaznaczamy checkbox przy opcji Utwórz tylko połączenie. Tak ustawione opcje importowania danych zatwierdzamy przyciskiem OK (rys. nr 8).

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

W kolejnym etapie pobieramy właściwe połączenie, które w naszym przykładzie jest plikiem CSV. Wybieramy polecenie Z pliku tekstowego/CSV z karty Dane (rys. nr 9).

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

Otworzy się nam okno Importowania danych, gdzie musimy znaleźć interesujący nas plik, zaznaczyć go i zatwierdzić nasz wybór przyciskiem OK (rys. nr 10).

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

W pliku CSV mamy dużą ilość danych, z których chcemy wyciągnąć tylko kolumny z naszej listy. Otworzy nam się okno z tymi danymi, gdzie klikamy przycisk Edytuj, aby przejść do edycji tego zapytania (rys. nr 11).

rys. nr 11 - polecenie Edytuj zapytanie
rys. nr 11 — polecenie Edytuj zapytanie

Otworzy nam się edytor zapytań Power Query z wczytanymi wszystkimi danymi przedstawiony na rys. nr 12.

rys. nr 12 - Edytor zapytań z wczytanymi danymi z pliku CSV
rys. nr 12 — Edytor zapytań z wczytanymi danymi z pliku CSV

Przykładowo możemy usunąć niepotrzebne kolumny, czyli zaznaczyć interesujące nas kolumny, następnie kliknąć prawym przyciskiem myszy na dowolną z nich i z podręcznego menu wybrać polecenie Usuń inne kolumny (rys. nr 13).

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

Nie ma znaczenia, które kolumny usuniemy, o ile Power Query użyje funkcji Table.SelectColumns, która ma w zapisie formuły listę kolumn, które chcemy zachować (rys. nr 14). 

rys. nr 14 - argumenty funkcji Table.SelectColumns
rys. nr 14 — argumenty funkcji Table.SelectColumns

W Power Query możemy się odwołać do innego zapytania, czyli w zapisie funkcji zastąpić listę kolumn, które zostały wpisane automatycznie, zapytaniem z listą, które przygotowaliśmy sobie na początku pracy (zapytanie tListaKolumn). Zapis funkcji powinien wyglądać następująco: (rys. nr 15)

=Table.SelectColumns(#"Zmieniono typ", tListaKolumn) 

rys. nr 15 - Zapis funkcji Table.SelectColumns z odwołaniem do zapytania z listą
rys. nr 15 — Zapis funkcji Table.SelectColumns z odwołaniem do zapytania z listą

Po zatwierdzeniu formuły otrzymamy błąd, wynikający z wartości null na liście (rys. nr 16). Power Query nie może przekonwertować wartości null.

rys. nr 16 - Błąd funkcji wynikający z wartości null na liście
rys. nr 16 — Błąd funkcji wynikający z wartości null na liście

Aby usunąć błąd przechodzimy do zapytania tListaKolumn, następnie co istotne w kroku poprzedzającym krok Lista wybór (krok Zmieniono typ), nakładamy na dane filtr. Rozwijamy ikonkę trójkąta w nagłówku kolumny z następnie wybieramy polecenie Usuń puste (rys. nr 17).

rys. nr 17 - Filtrowanie listy za pomocą polecenia Usuń puste
rys. nr 17 — Filtrowanie listy za pomocą polecenia Usuń puste

Pojawi nam się komunikat Wstawianie kroku, w którym musimy zatwierdzić nasze działanie przyciskiem Wstaw (rys. nr 18).

rys. nr 18 - Komunikat o wstawieniu nowego kroku
rys. nr 18 — Komunikat o wstawieniu nowego kroku

Dzięki temu krokowi wartości null zostały usunięte z listy. Otrzymamy listę przedstawioną na rys. nr 19.

rys. nr 19 - Odfiltrowana lista kolumn
rys. nr 19 — Odfiltrowana lista kolumn

Nasza lista potrzebnych kolumn nie zawiera już wartości null i dzięki temu jak przejdziemy do naszego zapytania FoodMart Klienci, błędu nie będzie. Wpisana wcześniej formuła z odwołaniem do zapytania tListaKolumn zadziała prawidłowo i otrzymamy dane zawierające kolumny z listy (rys. nr 20).

rys. nr 20 - prawidłowe działanie funkcji Table.SelectColumns
rys. nr 20 — prawidłowe działanie funkcji Table.SelectColumns

Tak przygotowane dane możemy załadować do Excela za pomocą polecenie Zamknij i załaduj do z karty Narzędzia główne (analogicznie jak na rys. nr 7).

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

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

Otrzymamy dane wczytane do Excela zawierające tylko kolumny z listy, przedstawione na rys. nr 22.

rys. nr 22 - Dane zaimportowane do Excela
rys. nr 22 — Dane zaimportowane do Excela

Tabela wczytana z Power Query działa dynamicznie. Przykładowo możemy zmienić nasze dane w liście kolumn (zmieniamy kolumnę Nazwisko na Adres i dokładamy nową kolumnę Liczba dzieci), następnie odświeżyć dane z Power Query klikając prawym przyciskiem myszy na dowolną komórkę z obszaru tabeli i wybierając polecenie Odśwież (rys. nr 23).

rys. nr 23 - polecenie Odśwież
rys. nr 23 — polecenie Odśwież

Po odświeżeniu danych otrzymamy zmienione dane, w których kolumna Nazwisko została zastąpiona kolumną Adres i została dołożona nowa kolumna Liczba dzieci (rys. nr 24).

rys. nr 24 - automatycznie zaktualizowane dane
rys. nr 24 — automatycznie zaktualizowane dane

Podsumowując stworzyliśmy tabelę z kolumnami z listy, która działa dynamicznie i zawiera kolumny, które aktualnie są na liście.


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