0
0 Produkty w koszyku

No products in the cart.

W dzisiejszym poście nauczymy się wyznaczać ilość dni w miesiącu pomiędzy datami w Power Query. Odcinek ten powstał jako odpowiedź na komentarz Billa Szyszkowskiego do postu z pytaniami od widzów nr 121, poruszającego ten sam problem. Wyznaczymy ilość dni pomiędzy datami w danym miesiącu ale tym razem za pomocą Power Query. Musimy pamiętać, że kiedy w Excelu mamy daty to tak naprawdę mamy do czynienia z liczbami, bo daty w Excelu to liczby. Bill Szysz zwrócił uwagę, że czasami nie uwzględniamy tego pierwszego dnia, np. między datami 2018-01-02 a 2018-01-04 są 2 dni różnicy, ale jeśli policzymy to dokładnie to mamy 2018-01-02, 2018-01-03 i 2018-01-04, a więc 3 dni. W zależności czy liczymy ten pierwszy dzień czy nie to otrzymujemy różne wyniki. Bill Szysz zaproponował rozwiązanie tego problemu w Power Query. Zagadnienie to omówimy na przykładowych danych z rysunku nr 1.

rys. nr 1 — Przykładowe dane

Standardowo mamy dane umieszczone w tabeli i w Excelu od 2016 wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane, aby zaczytać dane do Power Query.

rys. nr 2 — Polecenie Z tabeli

Otworzy nam się okno Edytora zapytań dla danych zaczytanych z tabeli tDaty. Musimy pamiętać, że Power Query chce zapisać dane jako daty, a my chcemy skorzystać z liczb. Klikamy ikonkę (oznaczoną zieloną strzałką na rysunku nr 3) przy nazwie kolumny Od i z podręcznego menu wybieramy polecenie Liczba całkowita. 

rys. nr 3 — Zmiana formatu danych

Pojawi się nam komunikat o zmianie typu kolumny, gdzie musimy kliknąć przycisk Zamień bieżącą (rys. nr 4).

rys. nr 4 — Komunikat o zmianie typu kolumny

Tę samą czynność powtarzamy dla kolumny obok nazwanej Do. Klikamy ikonkę przy jej nazwie i z podręcznego menu wybieramy polecenie Liczba całkowita. Pojawi nam się komunikat o zmianie typu kolumny, gdzie klikamy przycisk Zamień bieżącą. Otrzymamy dane przedstawione na rysunku nr 5.

rys. nr 5 — Dane po zmianie z daty na format liczbowy

W kolejnym kroku wybieramy polecenie Kolumna niestandardowa (punkt nr 2 na rysunku nr 6) z karty Dodaj kolumnę.

rys. nr 6 — Kolumna niestandardowa

Pojawi nam się okno Kolumna niestandardowa, w której zmienimy nazwę kolumny na Miesiąc, a następnie w okienku formuły kolumny niestandardowej wpiszemy ={[od]..[do]}. Następnie nasze ustawienia zatwierdzamy klikając przycisk OK (rys. nr 7).

rys. nr 7 — Parametry kolumny niestandardowej

Musimy tutaj pamiętać , że pod wartościami [od] i [do] kryją się liczby. Otrzymamy dane przedstawione na rysunku nr 8, gdzie dla każdego wiersza w kolumnie Miesiąc mamy listę liczb między tymi datami – zapisanymi też jako liczby.

rys. nr 8 — Kolumna niestandardowa Miesiąc

Dla pierwszego wiersza lista ta została przedstawiona na rysunku nr 9.

rys. nr 9 — Dni kryjące się w tabeli pod nazwą List

W kolejnym kroku zaznaczamy kolumnę Miesiąc, klikamy prawym przyciskiem myszy na nazwę tej kolumny  i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 11).

rys. nr 11 — Usuń inne kolumny

Zostanie nam jedna kolumna. Następnie klikamy ikonkę oznaczoną na rysunku nr 12 zieloną strzałką i z podręcznego menu wybieramy polecenie Rozwiń do nowych wierszy.

rys. nr 12 — Rozwiń do nowych wierszy

Otrzymamy listę wszystkich dni pomiędzy datami (rys. nr 13).

rys. nr 13 — Lista dni między datami

Nas interesuje konkretnie ilość dni w danym miesiącu, więc w pierwszym kroku musimy znowu zmienić typ danych na daty. Rozwijamy ikonkę przy nazwie kolumny i z podręcznego menu wybieramy polecenie Data (rys. nr 14).

rys. nr 14 — Zmiana typy wyświetlania danych

Otrzymamy listę dni w postaci dat, przedstawioną na rysunku nr 15 (jej fragment).

rys. nr 15 — Lista dni między datami w postaci dat

Przy tak przygotowanych danych wybieramy polecenie Data (punkt nr 2 na rysunku nr 16) z karty Dodaj kolumnę.

rys. nr 16 — Polecenie Data

Za pomocą tego polecenia możemy z daty wyciągać różne parametry, takie jak rok, kwartał, miesiąc itd. My wybieramy Rok (rys. nr 17), następnie analogicznie wybieramy Miesiąc.

rys. nr 17 — Dodanie kolumny Rok

Otrzymamy dane przedstawione na rysunku nr 18.

rys. nr 18 — Dane z dodaną kolumną Rok

W kolejnym kroku z karty Przekształć wybieramy polecenie Data, a następnie Miesiąc i Nazwa miesiąca (po kolei kroki oznaczone na rysunku nr 19).

rys. nr 19 — Dodawanie kolumny Miesiąc

Kiedy korzystamy z poleceń z karty Dodaj kolumnę to powstaje nowa kolumna, jeśli zaś korzystamy z polecenia z karty Przekształć, to działamy na istniejącej kolumnie i w niej wprowadzamy zmiany. Otrzymamy dane przedstawione na rysunku nr 20.

rys. nr 20 — Dane z dodaną kolumną Miesiąc

Zaznaczamy wszystkie trzy kolumny a następnie wybieramy polecenie Grupowanie według (punkt nr 2 na rysunku nr 21) z karty Narzędzia główne.

rys. nr 21 — Grupowanie według

Otworzy nam się okno Grupowanie według, gdzie wybieramy grupowanie Zawansowane (punkt nr 1 na rysunku nr 22). Automatycznie pojawią nam się trzy poziomy grupowania (punkt nr 2). Następnie nazywamy nową kolumnę Ile dni (punkt nr 3) i wybieramy operację Zlicz wiersze (punkt nr 4), która ma zliczyć dni z danego miesiąca. Nie interesuje nas jakie to były dni tylko ich ilość. Nasze parametry zatwierdzamy przyciskiem OK

rys. nr 22 — Parametry w oknie Grupowanie według

Otrzymamy pogrupowane dane według miesięcy przedstawione na rysunku nr 23.

rys. nr 23 — Pogrupowane dane

Tak przygotowane dane chcemy załadować do Excela, więc korzystamy z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 24).

rys. nr 24 — Zamknij i załaduj

Otworzy nam się okno Ładowanie do, gdzie ustawiamy parametry wstawianych danych, sposób wyświetlania danych jako Tabela i miejsce wstawienia danych jako istniejący arkusz i wybieramy konkretną komórkę, gdzie chcemy umieścić nasze dane. Parametry zatwierdzamy przyciskiem Załaduj (rys. nr 25).

rys. nr 25 — parametry okna Ładowanie do

Otrzymamy dane przedstawione na rysunku nr 26.

rys. nr 26 — Dane załadowane do Excela

W naszych wczytanych dołożyliśmy Rok tylko dla sytuacji kiedy mamy w danych przeskok lat. Przygotowałem sobie dodatkowy wiersz, który kiedy dodam do tabeli z pierwotnymi danymi, a następnie naszą tabele z Power Query odświeżę – klikamy prawym przyciskiem myszy na dane (dowolną komórkę w tabeli zwróconej przez zapytanie Power Query), a następnie z podręcznego menu wybieramy polecenie Odśwież (rys. nr 27).

rys. nr 27 — Odśwież zaczytane dane

Otrzymamy przeliczone dane z uwzględnionymi miesiącami w kolejnym roku (który dołożyliśmy do tabeli i danymi bazowymi), przedstawione na rysunku nr 28.

rys. nr 27 — Odświeżone dane

Podsumowując Power Query świetnie sprawdza się w wyznaczaniu ilości dni między datami w danym miesiącu. Rozwiązanie jest dużo szybsze i prostsze niż w Excelu 


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