W dzisiejszym poście zajmiemy się wyznaczaniem numeru tygodnia w roku i miesiącu oraz początku i końca tygodnia. W poradach od 341 do 344 omawialiśmy wyznaczanie tych danych w Excelu za pomocą funkcji .
Excel — Numer tygodnia w roku — porada 341
Excel — Początek i koniec tygodnia na podstawie daty — porada 342
Excel — Data początku i końca tygodnia na podstawie numeru tygodnia w roku — porada 343
Excel — Numer tygodnia w miesiącu różne systemy — porada 344
Poznamy możliwości Power Query w tym zakresie. Temat ten omówimy na podstawie przykładowych danych przedstawionych na rys. nr 1.
Mamy tutaj przedstawione dane zawierające zbiór dat i informację jaki to jest dzień tygodnia. Aby zaczytać dane do Power Query wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).
Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą. Klikamy ikonkę kalendarza przy nazwie kolumny Data aby zmienić formatowanie. Z podręcznego menu wybieramy typ danych – Data (rys. nr 3). Wyświetli nam się komunikat o Zmianie typu danych, w którym za pomocą przycisku Zamień bieżącą zatwierdzamy zmianę.
Otrzymamy dane przedstawione na rysunku nr 4.
Chcemy dodać nową kolumnę z danymi zawierającymi numer tygodnia więc korzystamy z poleceń z karty Dodaj kolumnę. Gdybyśmy natomiast chcieli przekształcić nasze dane musimy skorzystać z analogicznych poleceń na karcie Przekształć (rys. nr 5).
Naszym zadaniem jest dodanie nowych kolumn i interesującymi nas danymi. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt 2 na rys. nr 6) z karty Dodaj kolumnę, następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas informację, czyli w naszym przykładzie Tydzień roku (punkt 4).
Otrzymamy dane z nową kolumną zawierającą numer tygodnia w roku odpowiadającego naszej dacie (rys. nr 7).
Istotną informacją tutaj jest, że Power Query działa na systemie pierwszym funkcji NUM.TYGODNIA Excela, czyli data 1 stycznia każdego roku to pierwszy dzień pierwszego tygodnia nowego roku. W Power Query możemy jedynie skorygować od jakiego dnia rozpoczyna się nowy tydzień. W Polsce standardowo nowy tydzień zaczyna się w poniedziałek i takie właśnie jest domyślne działanie funkcji Date.WeekOfYear w Power Query. Możemy to zmienić, wystarczy skopiować nazwę funkcji a następnie nacisnąć przycisk fx obok paska formuły, aby dodać nowy krok i wkleić w pasek formuły zapis =Date.WeekOfYear. Kiedy zatwierdzimy taką formułę klawiszem Enter otrzymamy informacje na temat tej funkcji, między innymi jej parametrów oraz jakie zwraca wartości. Możemy sobie tutaj w polu firstDayOfWeek wybrać dzień jaki chcemy przyjąć za początek tygodnia (rys. nr 8).
Usuwamy ten krok z Zastosowanych kroków, ponieważ wprowadziliśmy go tylko w celu pokazania możliwości zmiany dnia początku tygodnia. Jeśli chcemy wprowadzić zmianę dotyczącą dnia początku tygodnia możemy wpisać w formułę dodatkowy parametr zawierający nazwę dnia, jaki chcemy przyjąć za początek tygodnia. Załóżmy, że chcemy aby tydzień rozpoczynał się we wtorek, więc w formułę wpiszemy Day.Tuesday (rys. nr 9). Formuła powinna wyglądać następująco:
=Table.AddColumn(#"Zmieniono typ", "Tydzień roku" each Date.WeekOfYear([Data], Day.Tuesday), Int64.Type)
Otrzymamy zmienione dane, w których każdy nowy tydzień będzie rozpoczynał się we wtorek (rys. nr 10).
Zamiast Day.Tuesday możemy wpisać wartość 2 w formule. Zadziała ona prawidłowo. Zapis formuły powinien wtedy wyglądać następująco:
=Table.AddColumn(#"Zmieniono typ", "Tydzień roku" each Date.WeekOfYear([Data], 2), Int64.Type)
W Power Query dni są numerowane od niedzieli do soboty. Niedzieli odpowiada wartość 0, poniedziałek to wartość 1 itd., natomiast sobocie przypisana jest wartość 6.
Wszystkie kolejne funkcje, z których tutaj będziemy korzystać mają ten dodatkowy argument, w którym możemy przyjąć jaki dzień chcemy traktować jako początek tygodnia.
W kolejnym przykładzie wyznaczymy sobie numer tygodnia w miesiącu. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt 2 na rys. nr 11) z karty Dodaj kolumnę, następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas informację, czyli w naszym przykładzie Tydzień miesiąca (punkt 4).
Otrzymamy nową kolumnę z numerem tygodnia w miesiącu (rys. nr 12).
Dzień uznawany za początek tygodnia wstawił się domyślnie (poniedziałek). Jeśli chcemy zmienić dzień początku tygodnia musimy to zrobić analogicznie jak w poprzednim przykładzie, czyli dopisać ten argument w formułę funkcji na pasku formuły (rys. nr 13). Zakładamy, że chcemy aby tydzień rozpoczynał się w piątek, czyli musimy wpisać argument Day.Friday lub wartość 5. Zapis powinien wyglądać następująco:
=Table.AddColumn(#"Wstawiono Tydzień roku", "Tydzień miesiąca", each Date.WeekOfMonth([Data],5) Int64.Type)
Gdybyśmy nie mieli widocznego paska formuły, musimy go uruchomić w karcie Widok (rys. nr 14).
Otrzymamy dane przedstawiające numerację rozpoczynającą nowy tydzień w piątek (rys. nr 15).
W kolejnym przykładzie dodamy kolumnę zawierającą Początek tygodnia. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt 2 na rys. nr 16) z karty Dodaj kolumnę, następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas informację, czyli w naszym przykładzie Początek tygodnia (punkt 4).
Dodamy również kolumnę zawierającą Koniec tygodnia w analogiczny sposób jak na rysunku nr 16. Otrzymamy wtedy dwie nowe kolumny przedstawione na rysunku nr 17.
Zostało tu zastosowana numeracja domyślna, czyli tydzień rozpoczyna się w poniedziałek, a kończy w niedzielę.
Użyliśmy w tych przykładach dwóch funkcji Date.StartOfWeek oraz Date.EndOfWeek.
Istotną informacją dla działań w Power Query jest to, że data 1 stycznia zawsze rozpoczyna pierwszy tydzień nowego roku. Możemy jedynie zmieniać dni początku i końca tygodnia. Nie ma możliwości zrobić kontynuacji numeru tygodnia z poprzedniego roku w nowym roku, tak żeby tydzień zawsze miał 7 dni, jak to było możliwe w Excelu.
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).
Otworzy nam się okno Ładowania do, gdzie ustawiamy sposób wyświetlania danych jako Tabela oraz wybieramy miejsce wstawienia danych – Istniejący arkusz i wskazujemy konkretną komórkę (rys. nr 19). Powyższe parametry zatwierdzamy przyciskiem Załaduj.
Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 20.
Możemy sobie jeszcze odpowiednio sformatować dane korzystając ze skrótu klawiszowego Ctrl+1, aby przy tych datach wyświetlała się nazwa dnia tygodnia. Otworzy nam się okno formatowania komórek, gdzie wybieramy formatowanie Niestandardowe w karcie Liczby, następnie w polu Typ musimy dopisać zapis ddd (rys. nr 21).
Otrzymamy sformatowane dane przedstawione na rysunku nr 22.
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
