Excel Power Query 57 — Czy element jest na liście

W dzisiejszym poście omówimy funkcjonalność polegającą na sprawdzaniu czy dany element jest na liście. W przykładowych danych przedstawionych na rys. nr 1 mamy tabelę ze sprzedawcami, gdzie w zależności na której liście znajduje się dany sprzedawca, to z tej listy chcemy pobierać dane.

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

Zakładamy, że główna lista zawiera województwo, w którym działa dany sprzedawca i właśnie stamtąd chcemy pobierać dane. W pierwszym kroku musimy pobrać nasze dane z tabel pobrać do Power Query. Możemy to zrobić wybierając 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ą pobraną z Excela przedstawioną na rys. nr 3.

Rys. nr 3 – edytor zapytań Power Query z wczytaną tabelą z danymi
Rys. nr 3 – edytor zapytań Power Query z wczytaną tabelą z danymi

W analogiczny sposób pobieramy dane z drugiej tabeli. Ustawiamy aktywną komórkę w obszarze tabeli z regionami i wybieramy polecenie Z tabeli/ zakresu  z karty Dane (jak na rys. nr 2). Otrzymamy dwa odrębne zapytania w Edytorze zapytań Power Query (rys. nr 4).

Rys. nr 4 – dwa zapytania w Power Query (dwie tabele)
Rys. nr 4 – dwa zapytania w Power Query (dwie tabele)

Możemy przybliżyć dane (powiększyć) za pomocą skrótu klawiszowego Ctrl+Shift+=. Przechodzimy na zapytanie tSprzedaż_k, gdzie naszym zadaniem będzie napisanie kolumny warunkowej, która na podstawie sprzedawcy będzie wybierała wartość z kolumny Świętokrzyskie albo Mazowieckie. W tym celu wybieramy polecenie  Kolumna warunkowa  z karty Dodaj kolumnę (rys. nr 5).

Rys. nr 5 – polecenie Kolumna warunkowa
Rys. nr 5 – polecenie Kolumna warunkowa

Otworzy nam się okno Dodawania kolumny warunkowej. Ta funkcjonalność Power Query posłuży nam do zbudowania struktury funkcji IF (funkcji JEŻELI). Kolumnę tą łatwo będzie stworzyć, ale później dokonamy pewnych modyfikacji, żeby sprawdzić czy dany element jest na liście. W polu Nazwa nowej kolumny wpisujemy Główny region (punkt 1 na rys. nr 6). W polu Nazwa kolumny wybieramy z listy rozwijanej kolumnę Sprzedawca (punkt 2), w polu Operator zostawiamy domyślny, czyli równa się (punkt 3). W polu Wartość wpisujemy nazwę dowolnego sprzedawcy z regionu Świętokrzyskie np. Aleksandra (punkt 4), natomiast w polu Wartość wyjściowa najpierw rozwijamy ikonkę obok pola i wybieramy z listy rodzaj danych – Wybierz kolumnę (oznaczone strzałką) i w polu wybieramy wartość z kolumny Świętokrzyskie (punkt 5). Następnie klikamy przycisk Dodaj regułę, aby stworzyć kolejną strukturę funkcji JEŻELI.

Rys. nr 6 – okno dodawania kolumny warunkowej (pierwsza funkcja JEŻELI)
Rys. nr 6 – okno dodawania kolumny warunkowej (pierwsza funkcja JEŻELI)

W drugiej regule w polu Nazwa kolumny  wybieramy kolumnę Sprzedawca, pole Operator zostaje bez zmian, w polu Wartość  wpisujemy Dominik, następnie zmieniamy typ Wartości wyjściowej na Wybierz kolumnę (jak dla pierwszej reguły) i wybieramy kolumnę Mazowieckie. Powinniśmy stworzyć takie reguły dla każdej pojedynczej osoby. Było by to bardzo czasochłonne. My zbudowaliśmy tu tylko strukturę a później przez odpowiednie przekształcenie, sprawimy, żeby to działało dla dowolnego sprzedawcy. W polu W przeciwnym razie wpisujemy wartość null (oznaczone strzałką na rys. nr 7). Tak ustawione parametry kolumny warunkowej zatwierdzamy przyciskiem OK.

Rys. nr 7 – okno dodawania kolumny warunkowej (druga funkcja JEŻELI)
Rys. nr 7 – okno dodawania kolumny warunkowej (druga funkcja JEŻELI)

Otrzymamy dane z nową kolumną przedstawione na rys. nr 8. W kolumnie Główny region stworzyliśmy strukturę, zbudowaną za podstawie prostych obliczeń funkcji JEŻELI.

Rys. nr 8 – Dane z nową kolumną warunkową
Rys. nr 8 – Dane z nową kolumną warunkową

W zapisie formuły kolumny warunkowej w pasku formuły sprzedawca został porównany do konkretnego sprzedawcy, a naszym celem będzie sprawdzenie czy dany sprzedawca jest na liście. Podsumowując zastąpimy to porównanie funkcją List.Contains (rys. nr 9).

Rys. nr 9 – zapis formuły kolumny warunkowej
Rys. nr 9 – zapis formuły kolumny warunkowej

Funkcja List.Contains potrzebuje jako pierwszego argumentu listy wszystkich sprzedawców, listę w Power Query zapisujemy w nawiasach klamrowych. Następnie sprawdzamy czy w danej liście znajduje się ten konkretny sprzedawca z danego wiersza, czyli tutaj możemy użyć struktury zbudowanej przez kolumnę warunkową. Zapis formuły powinien wyglądać następująco:

=Table.AddColumn(#"Zmieniono typ", "Główny region", each if List.Contains ({"Aleksandra", "Robert"}, [Sprzedawca]) then [Świętokrzyskie] else if [Sprzedawca]="Dominik" then [Mazowieckie] else null))

Zmieniliśmy tylko część formuły – dla pierwszych 2 sprzedawców i po zatwierdzaniu formuły otrzymamy zmienione dane przedstawione na rys. nr 10.

Rys. nr 10 – zapis funkcji List.Contains dla pierwszej reguły kolumny warunkowej
Rys. nr 10 – zapis funkcji List.Contains dla pierwszej reguły kolumny warunkowej

Ten sposób jest łatwiejszy, ponieważ bezpośrednio w formule możemy wypisać wszystkich sprzedawców z danego województwa. Musimy pamiętać, że pobraliśmy drugą tabelę z danymi (zapytanie tRegiony_k), gdzie mamy listy sprzedawców z podziałem na województwa. W zapisie formuły możemy się odwołać bezpośrednio do danych z tego zapytania (do poszczególnych kolumn), zamiast wpisywać ręcznie nazwy poszczególnych sprzedawców (rys. nr 11). Zapis formuły będzie wtedy wyglądał następująco:

=Table.AddColumn(#"Zmieniono typ", "Główny region", each if List.Contains (tRegiony_k [Świętokrzyskie], [Sprzedawca]) then [Świętokrzyskie] else if [Sprzedawca]="Dominik" then [Mazowieckie] else null))

Rys nr 11 – odwołanie do kolumny Świętkorzyskie z zapytania tRegiony_k
Rys nr 11 – odwołanie do kolumny Świętokrzyskie z zapytania tRegiony_k

Po zatwierdzeniu formuły otrzymamy dane, gdzie każde wystąpienie sprzedawcy z regionu świętokrzyskiego zostanie rozpoznane. To odwołanie działa dla pierwszej reguły, którą stworzyliśmy dla kolumny warunkowej. Analogicznie możemy zmienić drugą regułę – dla regionu Mazowieckiego. Zapis formuły będzie wyglądał następująco:

=Table.AddColumn(#"Zmieniono typ", "Główny region", each if List.Contains (tRegiony_k [Świętokrzyskie], [Sprzedawca]) then [Świętokrzyskie] else if List.Contains (tRegiony_k [Mazowieckie] then [Mazowieckie] else null))

Power Query przeliczy dane po zatwierdzeniu kolumny i otrzymamy dane przedstawione na rys. nr 12.

Rys. nr 12 – dane po użyciu w zapisie formuły funkcji List.Contains
Rys. nr 12 – dane po użyciu w zapisie formuły funkcji List.Contains

W danych mamy jednego sprzedawcę (Beata), który nie znajduje się na żadnej z list. Formuła zadziała tak, że otrzymamy dla tego sprzedawcy wartość null. Dzieje się tak dlatego, że w kolumnie warunkowej wpisaliśmy, że jeśli warunki nie zostaną spełnione kolumna warunkowa ma nam zwrócić wartość null ( parametr W przeciwnym razie na rys. nr 7).

Tak przekształcone dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do  z karty Narzędzia główne (rys. nr 13).

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

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

Rys. nr 14 – okno Importowania danych
Rys. nr 14 – okno Importowania danych

Otrzymamy dane zaimportowane do Excela i przedstawione na rys. nr 15.

Rys. nr 15 – Dane zaimportowane do Excela
Rys. nr 15 – Dane zaimportowane do Excela

Jak widać na rysunku powyżej dane z odpowiednich kolumn są pobierane do kolumny warunkowej Główny region.


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 56 — Usuwanie ulic z adresów

W dzisiejszym poście omówimy usuwanie części adresów, czyli np. nazw ulic czy placów. Rozwiążemy to zadanie w Power Query. To samo zagadnienie omawialiśmy w poradzie 369, ale za pomocą Excela. Zagadnienie to omówimy na podstawie przykładowych danych przedstawionych na rys. nr 1.

Rys. nr 1 – Przykładowe dane do zadania
Rys. nr 1 – Przykładowe dane do zadania

Przykładowe dane musimy zaczytać do Power Query za pomocą polecenia 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 adresami, następnie na tych danych wykonamy przekształcenia, aby otrzymać pożądany efekt. Przekształcenia, które wykonywaliśmy ręcznie w Excelu, czyli zamiana poszczególnych nazw ulic, alei i placów na ulice (ujednolicenie danych), w Power Query możemy zapisać jako kroki. Nawet kiedy nasze dane się zmienią, Power Query znów zamieni te instancje na ulice i będziemy mogli wyciągnąć tylko miasto z naszych danych.

Zaznaczamy kolumnę Miasto, Adres i wybieramy polecenie Zamienianie wartości z karty Narzędzia główne (rys. nr 3).

Rys. nr 3 – polecenie Zamienianie wartości
Rys. nr 3 – polecenie Zamienianie wartości

Polecenia tego musimy użyć dwukrotnie. Otworzy nam się okno Zamieniania wartości, gdzie w polu Wartość do znalezienia wpisujemy pl. (oznaczenie placu w adresie), następnie w polu Zamień na wpisujemy ul. (oznaczenie ulicy). Tak ustawione parametry zamieniania wartości zatwierdzamy przyciskiem OK (rys. nr 4).

Rys. nr 4 – okno Zamieniania wartości
Rys. nr 4 – okno Zamieniania wartości

Otrzymamy dane, gdzie wszystkie wystąpienia skrótu pl. zostały zamienione na ul. Następnie musimy powtórzyć te czynności, czyli wybieramy polecenie Zamienianie wartości z karty Narzędzia główne. Otworzy nam się okno Zamieniania wartości, gdzie znowu musimy ustawić jego parametry. Tym razem w polu Wartość do znalezienia wpisujemy al. (oznaczenie alei), a w polu Zamień na wpisujemy ul. (analogicznie jak na rys. nr 4).

Otrzymamy zamienione dane przedstawione na rys. nr 5.

Rys. nr 5 – Zamienione dane
Rys. nr 5 – Zamienione dane

Otrzymaliśmy dane, gdzie różne określenia adresów zostały ujednolicone, zamienione na skrót od ulicy (ul.). Teraz wystarczy wyciągnąć z danych tekst przed ulicą.  W tym celu rozwijamy polecenie Wyodrębnij (punkt 2 na rys. nr 6) z karty Przekształć (punkt 1), a następnie wybieramy polecenie Tekst przed ogranicznikiem (punkt 3).

Rys. nr 6 – ścieżka dostępu do polecenia Test przed ogranicznikiem
Rys. nr 6 – ścieżka dostępu do polecenia Test przed ogranicznikiem

Otworzy nam się okno Tekstu przed ogranicznikiem, gdzie w polu Ogranicznik wpisujemy skrót ul. (ujednolicone oznaczenie ulicy w adresie). Tak ustawiony ogranicznik zatwierdzamy przyciskiem OK (rys. nr 7).

Rys. nr 7 – okno Testu przed ogranicznikiem
Rys. nr 7 – okno Testu przed ogranicznikiem

Otrzymamy wyodrębnione dane z nazwami miast, po usunięciu niepotrzebnego tekstu przedstawioną na rys. nr 8.

Rys. nr 8 – Wyodrębnione dane
Rys. nr 8 – Wyodrębnione dane

Musimy pamiętać, że mieliśmy takie adresy, gdzie po nazwie miast była spacja, a dopiero potem nazwa ulicy, jak również sytuacje, kiedy tej spacji nie było w ogóle. Na końcu nazw niektórych miast pozostały spacje, które są nam niepotrzebne (mimo że ich nie widać). W tym celu rozwijamy polecenie Format (punkt 2 na rys. nr 9) z karty Przekształć (punkt 1), a następnie wybieramy polecenie Przycięcie (punkt 3).

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

Polecenie Przycięcie usuwa zbędne spacje na końcu tekstu. Otrzymamy przycięte dane przedstawione na rys. nr 10.

Rys. nr 10 – przycięte dane (po usunięciu spacji na końcu tekstu)
Rys. nr 10 – przycięte dane (po usunięciu spacji na końcu tekstu)

Możemy zrobić jeszcze jedno przekształcenie, mianowicie przy dwuczłonowych nazwach miast powinien być myślnik pomiędzy słowami (Skarżysko – Kamienna). W tym celu wybieramy polecenie Zamienianie wartości z karty Narzędzia główne (jak na rys. nr 3). Otworzy nam się okno Zamieniania wartości, gdzie w polu Wartość do znalezienia wpisujemy spację, a w polu Zamień na wpisujemy myślnik (-). Tak ustawione parametry zamieniania wartości zatwierdzamy przyciskiem OK (rys. nr 11).

Rys. nr 11 – okno Zamieniania wartości
Rys. nr 11 – okno Zamieniania wartości

Otrzymamy zmienione dane. W kolejnym kroku powinniśmy zmienić nazwę kolumny, ponieważ po przekształceniach zawiera ona tylko nazwę miasta. Klikamy dwukrotnie na tytuł kolumny i wpisujemy nową nazwę, czyli Miasto (rys. nr 12).

Rys. nr 12 – Zmiana nazwy kolumny
Rys. nr 12 – Zmiana nazwy kolumny

Otrzymamy dane przedstawione na rys. nr 13.

Rys. nr 13 – Dane po przekształceniach
Rys. nr 13 – Dane po przekształceniach

Po tych wszystkich krokach przekształceń, powinniśmy otrzymać te same wyniki, co w poradzie nr 369. Dzięki temu, że zadanie to wykonaliśmy w Power Query i mamy zapisane poszczególne kroki to w sytuacji, kiedy zmieniłyby nam się dane, wystarczy odświeżyć dane aby otrzymać aktualne, poprawne wyniki. 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 14).

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

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

Rys. nr 15 – okno Importowania danych
Rys. nr 15 – okno Importowania danych

Otrzymamy dane wstawione do Excela z wyciągniętymi nazwami miast z całych adresów przedstawione na rys. nr 16.

Rys. nr 16 – Dane wstawione do Excela
Rys. nr 16 – Dane wstawione do Excela

Książka Mistrz Excela + promo na 35 urodziny

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

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

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

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

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

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

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

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

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

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

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

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

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

Książka Mistrz Excela reklama

Excel Power Query 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 53 — Matura z informatyki 2018 — Zestawienie łączne po miesiącach zad 5.3

W dzisiejszym poście kontynuujemy rozwiązywanie zadań z Matury z informatyki 2018, zajmiemy się rozwiązaniem zadania nr 5.3 w Power Query. Treść zadania została przedstawiona na rys. nr 1.

rys. nr 1 - treść zadania 5.3 z informatyki z Matury 2018
rys. nr 1 — treść zadania 5.3 z informatyki z Matury 2018

Zadanie polega na stworzeniu zostawienia wpływów wody do zbiornika retencyjnego w kolejnych miesiącach roku 2008. Zaczynamy od włączenia sobie okna Zapytania i połączenia, aby mieć dostęp do wcześniej wczytanych danych (które załadowaliśmy sobie do rozwiązania poprzednich zadań). Rys. nr 2.

rys. nr 2 - polecenie Zapytania i połączenia
rys. nr 2 — polecenie Zapytania i połączenia

Klikamy na nazwę zapytania Woda, gdzie mamy wczytane dane z pliku tekstowego zawierającego ilości dopływającej wody do zbiornika retencyjnego. Otworzy nam się Edytor zapytań Power Query z wczytanymi danymi, przedstawionymi na rys. nr 3.

rys. nr 3 - edytor zapytań z wczytanymi danymi
rys. nr 3 — edytor zapytań z wczytanymi danymi

Po raz kolejny będziemy się odwoływać do danych z tego zapytania, czyli klikamy prawym przyciskiem myszy na nazwę zapytania Woda a następnie z podręcznego menu wybieramy polecenie Odwołanie (rys. nr 4).

rys. nr 4 - ścieżka dostępu do polecenia Odwołanie
rys. nr 4 — ścieżka dostępu do polecenia Odwołanie

Będziemy pracować na zapytaniu woda(2). Interesuje nas konkretny rok (2008) i podział na miesiące. W pierwszej kolejności wyciągniemy numer miesiąca z daty, czyli rozwijamy polecenie Data (punkt 2 na rys. nr 5) z karty Dodaj kolumnę (punkt 1), następnie rozwijamy polecenie Miesiąc (punkt 3) i wybieramy polecenie Miesiąc (punkt 4).

rys. nr 5 - ścieżka dostępu do polecenia Miesiąc
rys. nr 5 — ścieżka dostępu do polecenia Miesiąc

Otrzymamy dane z nową kolumną zawierającą numer miesiąca w roku przedstawione na rys. nr 6.

rys. nr 6 - Tabela z nową kolumną z numerem miesiąca
rys. nr 6 — Tabela z nową kolumną z numerem miesiąca

W kolejnym etapie interesuje nas rok – potrzebujemy danych tylko z roku 2008. W tym celu rozwijamy polecenie Data (punkt 2 na rys. nr 7) z karty Przekształć (punkt 1), a następnie rozwijamy polecenie Rok (punkt 3) i wybieramy polecenie Rok (punkt 4).

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

Otrzymamy przekształcone dane w kolumnie Data przedstawione na rys. nr 8.

rys. nr 8 - Dane z przekształconą kolumną Data
rys. nr 8 — Dane z przekształconą kolumną Data

Na tym etapie możemy przefiltrować dane w kolumnie Data. Klikamy ikonkę z trójkącikiem przy nazwie kolumny Data, a następnie zaznaczamy checkbox przy roku 2008. Tak ustawiony parametr filtru zatwierdzamy przyciskiem OK (rys. nr 9).

rys. nr 9 - filtrowanie danych w kolumnie Data
rys. nr 9 — filtrowanie danych w kolumnie Data

Otrzymamy dane zawierające tylko rok 2008. Możemy ponadto zmienić kolejność kolumn, czyli przeciągnąć nową kolumnę o nazwie Miesiąc w miejsce za kolumną Data. Otrzymamy uporządkowane dane przedstawione na rys. nr 10.

rys. nr 10 - uporządkowane dane
rys. nr 10 — uporządkowane dane

Ponadto zmieniamy nazwę kolumny Data na Rok. Kolejny etap będzie polegał na pogrupowaniu danych miesiącami. Dodatkowo potrzebujemy wiersza z podsumowaniem, co w Power Query jest trudniejsze niż standardowo w Excelu. Aby ułatwić sobie zadanie zduplikujemy to, co do tej pory zrobiliśmy, czyli klikamy prawym przyciskiem myszy na nazwę zapytania woda(2) i z podręcznego menu wybieramy polecenie Duplikuj (rys. nr 11).

rys. nr 11 - polecenie Duplikuj
rys. nr 11 — polecenie Duplikuj

Otrzymamy kolejne zapytanie o nazwie woda(3). W zapytaniu woda (2) zrobimy grupowanie po kolumnach Rok i Miesiąc. W tym celu zaznaczamy kolumny Rok i Miesiąc przytrzymując klawisz Ctrl, następnie wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys. nr 12).

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

Otworzy nam się okno grupowania według, gdzie dzięki temu że zaznaczyliśmy dwie kolumny mamy od razu grupowanie zaawansowane. W polu Operacja wybieramy polecenie Suma, w polu Kolumna wybieramy Woda m3, natomiast w polu Nazwa nowej kolumny wpisujemy Przypływy. Tak ustawione parametry grupowania zatwierdzamy przyciskiem OK (rys. nr 13).

rys. nr 13 - parametry Grupowania danych
rys. nr 13 — parametry Grupowania danych

Otrzymamy pogrupowane (zsumowane) dane według miesięcy przedstawione na rys. nr 14. Tabela zawiera w kolumnie Przypływy ilość wody jaka wpływa do zbiornika retencyjnego w poszczególnych miesiącach roku 2008.

rys. nr 14 - Pogrupowane dane w kolumnie Przypływy
rys. nr 14 — Pogrupowane dane w kolumnie Przypływy

Analogiczne działanie wykonamy w zapytaniu woda(3), ale tutaj zrobimy podsumowanie po całym roku. Zaznaczamy tylko kolumnę Rok, następnie wybieramy polecenie Grupowanie według z karty Narzędzia główne (jak na rys. nr 12).

Otworzy nam się okno Grupowania według z typem grupowania podstawowym. W polu Operacja wybieramy Sumę, w polu Kolumna wybieramy kolumnę Woda m3, natomiast w polu Nazwa Nowej kolumny wpisujemy Przypływy. Tak ustawione parametry grupowania zatwierdzamy przyciskiem OK (rys. nr 15).

rys. nr 15 - parametry Grupowania według
rys. nr 15 — parametry Grupowania według

W zapytaniu woda(3) otrzymamy podsumowane dane po cały roku 2008 przedstawione na rys. nr 16.

rys. nr 16 - dane pogrupowane według roku (zsumowane)
rys. nr 16 — dane pogrupowane według roku (zsumowane)

Teraz możemy sobie dołączyć zapytania, w tym celu wybieramy polecenie Dołącz zapytania z karty Narzędzia główne (rys. nr 17).

rys. nr 17 - ścieżka dostępu do polecenia Dołącz zapytania
rys. nr 17 — ścieżka dostępu do polecenia Dołącz zapytania

Do zapytania z podsumowaniem po roku i miesiącach (woda(2)) dołączamy zapytanie z podsumowaniem po roku (woda(3)). Otworzy nam się okno Dołączanie, gdzie w polu Tabela do dołączenia wybieramy zapytanie woda(3). Nasz wybór zatwierdzamy przyciskiem OK (rys. nr 18).

rys. nr 18 - okno dołączania zapytań
rys. nr 18 — okno dołączania zapytań

Dzięki temu, że w obu zapytaniach nazwaliśmy tak samo kolumnę z podsumowaniem (Przypływy) to Power Query automatycznie połączył te kolumny w jedną. Otrzymamy połączone zapytania przedstawione na rys. nr 19.

rys. nr 19 - połączone dane z dwóch zapytań
rys. nr 19 — połączone dane z dwóch zapytań

W kolumnie Miesiąc dla podsumowania po roku otrzymaliśmy wartość null, ponieważ tej kolumny nie było w naszym podsumowaniu w zapytaniu woda (3).

Dobrze byłoby zamiast wartości null wpisać np. wartość tekstową suma. W Power Query pojawia się problem, ponieważ dane w kolumnie Miesiąc zostały określone jako typ liczbowy i kiedy podejmiemy próbę wpisania tekstu to Power Query będzie zgłaszał sprzeciw. W cely wyjaśnienia problemu wybieramy polecenie  Zamienianie wartości  z karty Narzędzia główne (rys. nr 20).

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

Otworzy nam się okno Zamieniania wartości, gdzie w polu Wartość do znalezienia wpisujemy null, natomiast w polu Zamień na wpisujemy Suma. Jeśli spróbujemy zatwierdzić tę zmianę przyciskiem OK, Power Query zaprotestuje, co widać na rys. nr 21.

rys. nr 21 - parametry zamieniania wartości
rys. nr 21 — parametry zamieniania wartości

Musimy anulować okno Zamieniania wartości. Poradzić sobie tutaj możemy zmieniając typ danych w kolumnie Miesiąc, czyli klikamy na ikonkę 123 przy nazwie kolumny Miesiąc i wybieramy typ danych Tekst (rys. nr 22).

rys. nr 22 - zmiana typu danych na tekst
rys. nr 22 — zmiana typu danych na tekst

Po takiej zmianie po raz kolejny wybieramy polecenie  Zamienianie wartości  z karty Narzędzia główne (jak na rys. nr 20). Otworzy nam się okno Zamieniania wartości, gdzie już bez problemu możemy wpisać parametry jak na rys. nr 21 i zatwierdzić je przyciskiem OK. Otrzymamy dane przedstawione na rys. nr 23.

rys. nr 23 - zamienione wartości w danych
rys. nr 23 — zamienione wartości w danych

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

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

Otworzy nam się okno Importowania danych w Excelu. Ponieważ mamy dwa zapytania nie możemy ich od razu wstawić do Istniejącego arkusza, dlatego wybieramy opcję Utwórz tylko połączenie i zatwierdzamy nasz wybór przyciskiem OK (rys. nr 25).

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

W panelu bocznym Zapytania i połączenia mamy wszystkie zapytania wczytane jako połączenie. Aby wiedzieć, które zapytanie jest tym właściwym klikamy na zapytanie woda (2) i przechodzimy do edytora zapytań Power Query, gdzie zmieniamy jego nazwę w polu Nazwa na Woda 5_3. Następnie kolejny raz musimy załadować dane do Excela za pomocą polecenia Zamknij i załaduj z karty Narzędzia główne. Zapytanie załaduje się jako połączenie. Na tym etapie z okienka Zapytań i połączeń możemy załadować to konkretne zapytanie klikając na mnie prawym przyciskiem myszy a następnie wybrać polecenie Załaduj do (rys. nr 26).

rys. nr 26 - polecenie Załaduj do
rys. nr 26 — polecenie Załaduj do

Otworzy nam się po raz kolejny okno Importowania danych, gdzie tym razem możemy wybrać sposób wyświetlania danych w skoroszycie jako Tabela oraz wskazać konkretną komórkę w istniejącym arkuszu. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 27).

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

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

rys. nr 28 - Dane wczytane do Excela
rys. nr 28 — Dane wczytane do Excela

Dla lepszej prezentacji danych możemy zmienić formatowanie w kolumnie Przypływy. Korzystając ze skrótu klawiszowego Ctrl+1 otwieramy okno Formatowania komórek. Przechodzimy na Kategorię Liczbowe, a następnie ustawiamy 0 miejsc po przecinku i zaznaczamy checkbox przy opcji Użyj separatora. Tak ustawione parametry formatowania zatwierdzamy przyciskiem OK (rys. nr 29).

rys. nr 29 - Formatowanie komórek
rys. nr 29 — Formatowanie komórek

Kolejnym etapem zadania jest stworzenie wykresu przypływów wody do zbiornika retencyjnego. Zaznaczamy dane z kolumny Przypływy, ale bez komórki podsumowującej dla całego roku a następnie wybieramy wykres kolumnowy z karty Wstawianie (rys. nr 30).

rys. nr 30 - ścieżka dostępu do polecenia wykres kolumnowy
rys. nr 30 — ścieżka dostępu do polecenia wykres kolumnowy

Pierwszą rzeczą jaką poprawimy na standardowym wykresie, będzie pogrubienie kolumn. Zaznaczamy serie na wykresie, a następnie korzystając ze skrótu klawiszowego Ctrl+1, otworzymy panel Formatowania serii danych, gdzie możemy zmienić szerokość odstępu między seriami (rys. nr 31).

rys. nr 31 - Formatowanie serii danych
rys. nr 31 — Formatowanie serii danych

W kolejnym etapie musimy zmienić tytuł wykresu, czyli klikamy dwukrotnie na napis Tytuł wykresu i zmieniamy jego nazwę na Przypkywy Wirki w roku 2008 [m3]. Aby w Excelu uzyskać indeks górny musimy napisać normalną 3, następnie ją zaznaczyć i korzystając ze skrótu klawiszowego Ctrl+1, otworzyć okno Czcionki, gdzie zaznaczmy check box przy opcji Indeks górny (rys. nr 32).

rys. nr 32 - indeks górny w oknie czcionka
rys. nr 32 — indeks górny w oknie czcionka

Ponadto potrzebujemy na wykresie zamieścić informacje przy osiach (co przedstawiają dane osie). Od Excela 2013 klikamy na ikonę + w rogu wykresu i zaznaczamy checkbox przy opcji Tytuły osi (rys. nr 33).

rys. nr 33 - Elementy wykresu (tytuły osi)
rys. nr 33 — Elementy wykresu (tytuły osi)

Tytuły osi wstawiają się jako domyślne, czyli musimy je sami odpowiednio zmienić. Klikamy dwukrotnie na tytuł osi i wpisujemy dla osi pionowej Woda m3, natomiast dla osi poziomej Nr miesiąca. Otrzymamy poprawny wykres przedstawiony na rys. nr 34

rys. nr 34 - poprawiony wykres Przypływów do zbiornika retencyjnego
rys. nr 34 — poprawiony wykres Przypływów do zbiornika retencyjnego

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