Nie znasz dobrze obsługi Excela? A może nie znasz jej wcale? Doskonale wiem, że w dzisiejszych czasach i realiach, znajomość tego programu jest nie tylko niezbędna, ale przede wszystkim korzystna dla Ciebie samego. Moje szkolenie z Excela online pokaże Ci, że arkusz kalkulacyjny nie jest wcale Twoim wrogiem, a sprzymierzeńcem w porządkowaniu i analizie danych - nie tylko liczbowych. Stworzyłem kurs Excel online na każdym poziomie, zarówno dla osób początkujących, średniozaawansowanych, jak i zaawansowanych. Znajdziesz u mnie także szkolenie z Excela online dedykowane specjalnie przedsiębiorcom. Każdy kurs zawiera starannie wyselekcjonowany zakres wiedzy i zagadnień, które odpowiadają na ściśle sprecyzowane potrzeby jego użytkowników.
Excel - kurs online. Dlaczego warto?
Zastanawiasz się, czy Excel i kurs online to dobre połączenie? Czy taka forma nauki pracy z arkuszem kalkulacyjnym może być efektywna? Pozwól, że rozwieję Twoje wątpliwości.
Od lat z powodzeniem zajmuję się dzieleniem swoją wiedzą zdalnie. Ta forma nauki, szczególnie w ostatnim czasie, mocno zyskała na popularności, a jej techniki znacząco się rozwinęły. Abyś mógł biegle posługiwać się programem Excel, każdy swój kurs zaprojektowałem z ogromną starannością i w oparciu o zasady metodyki nauczania na odległość.
Jednak szkolenie z Excela online, to przede wszystkim ogromna wygoda dla Ciebie, ponieważ nie musisz wychodzić z domu, możesz uczyć się wraz ze mną z dowolnego miejsca i w wybranym przez siebie czasie. Dodatkowo każdy mój kurs Excela online zawiera materiały dydaktyczne dostępne dla Ciebie już po zakończeniu szkolenia. Krótkie nagrania filmowe, prezentujące różnego rodzaju działania w arkuszu kalkulacyjnym, które w dowolnej chwili możesz zatrzymać, odtworzyć ponownie, by jeszcze raz przeanalizować i utrwalić wiedzę, to doskonały sposób, by opanować Excel. Kurs online to nie tylko wygoda, to także oszczędność Twojego i mojego czasu - zamiast dojeżdżać w umówione miejsce, możemy poświęcić więcej chwil na ćwiczenia i praktykę.
W
dzisiejszym poście 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
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
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
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
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
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
Otrzymamy
dane z nową kolumną z wartościami 1 lub 0 przedstawioną na rys. nr 7.
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
Otrzymamy
przefiltrowane dane, mianowicie tylko te z wartością 1 w kolumnie
niestandardowe, przedstawione na rys. nr 9.
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
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
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)
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
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
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
Otrzymamy
tym razem prawidłowe dane, przedstawione na rys. nr 16.
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
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
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
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
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
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
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
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
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.
