W tym poście nauczymy się jak na podstawie roku i numeru tygodnia w roku, wyznaczyć początek i koniec tygodnia. Zagadnienie to omówimy na przykładowych danych z rysunku nr 1.
W zależności od naszych założeń będą to inne formuły, inne obliczenia. Aby stworzyć ten wpis wzorowałem się na formułach ze strony https://www.ablebits.com/office-addins-blog/2015/04/29/excel-weeknum-function-convert-week-number-date/ w języku angielskim.
Są różne sposoby wyznaczania numeru tygodnia w roku, dlatego w zależności jaki system liczenia przyjmiemy, potrzebujemy do obliczeń różnych formuł. Chcemy wyznaczyć początek tygodnia w systemie drugim, w tym celu użyjemy funkcji DATA i DZIEŃ.TYG. Nasza formuła będzie wyglądać następująco:
=DATA(A2;1;-2)-DZIEŃ.TYG(DATA(A2;1;3))
W funkcji DATA musimy określić sobie 3 argumenty: Rok czyli u nas będzie to odwołanie do komórki A2, następnie Miesiąc to wartość 1 dla stycznia oraz Dzień, czyli wartość minus dwa. Wartość ujemna oznacza, że od pierwszego dnia w roku (2015–01-01) cofamy się o 2 dni (tak naprawdę cofamy się o 3 dni, ale pamiętajmy, że przyjęliśmy system drugi obliczeń, czyli dla poniedziałku mamy korektę o wartości 0). Od tego odejmujemy datę 2015-01-03 w następujący sposób: użyjemy funkcji DZIEŃ.TYG dla roku 2015, miesiąca stycznia oraz dnia 3. Formuła ta zapewni nam cofnięcie do początku tygodnia, ale tygodnia jeszcze wcześniej (do pełnego tygodnia, czyli do 2014-12-22) tak więc do naszej formuły musimy dodać 7 dni (u nas B2*7). Formuła wtedy będzie wyglądać następująco:
=DATA(A2;1;-2)-DZIEŃ.TYG(DATA(A2;1;3))+B2*7
Otrzymaliśmy w ten sposób datę początku tygodnia dla systemu drugiego, kiedy czwartek wypada 2015-01-01 (czyli pierwszy tydzień roku zawierający pierwszy czwartek stycznia) rys. nr 2
Aby wyznaczyć koniec tygodnia wystarczy do formuły omówionej powyżej dodać 6 dni, czyli zapis formuły będzie wyglądać następująco =E2+6
Omówmy sobie teraz przypadek, kiedy tydzień zawierający 1 stycznia jest pierwszym tygodniem roku oraz zakładamy, że tydzień rozpoczyna się w poniedziałek. Musimy w tej sytuacji użyć inne formuły (rys. nr 3)
=DATA($A$2;1;1)-DZIEŃ.TYG(DATA($A$2;1;1);11)+($B$2–1)*7+1
Aby zrozumieć formułę powyżej, musimy wiedzieć, że chcemy się cofnąć do wcześniejszego poniedziałku, czyli od 1 stycznia danego roku (DATA($A$2;1;1)) cofamy się o ilość dni, jaka nam wypada z daty (DZIEŃ.TYG(DATA($A$2;1;1);11). Liczba 11 (ostatni argument funkcji DZIEŃ.TYG) w formule oznacza przyjęty system, który zakłada że pierwszy dzień tygodnia to poniedziałek (poniedziałek oznaczony jest cyfrą 1, a niedziela cyfrą 7). Z tej części formuły otrzymujemy niedzielę 2014-12-28, a więc żeby otrzymać poniedziałek musimy dodać 1 oraz dodać ilość dni tygodnia odpowiednio przemnożone. Analogicznie, aby z początku tygodnia wyznaczyć koniec tygodnia wystarczy do naszego wyniku (formuły z rys. 3) dodać 6 dni.
Podobnie rozwiążemy przypadek, kiedy nasz tydzień będzie się rozpoczynał w czwartek, wystarczy zmienić w formule DZIEŃ.TYG drugi argument na system, w którym to czwartek jest traktowany jako pierwszy dzień tygodnia (czwartek oznaczony cyfrą 1, a środa cyfrą 7 – zamieniamy wartość 11 na 14). Wtedy nasza formuła będzie wyglądać następująco:
=DATA($A$2;1;1)-DZIEŃ.TYG(DATA($A$2;1;1);14)+($B$2–1)*7+1
Ostatni dzień tygodnia wyznaczamy jak wyżej, czyli do wyniku z powyższej formuły dodajemy 6 dni (rys. nr 4).
Ostatni omówiony sposób dotyczy sytuacji, kiedy 1 stycznia zawsze rozpoczyna pierwszy tydzień roku (tak jak w systemie pierwszym funkcji NUM.TYG), a my zakładamy początek tygodnia w poniedziałek. Formuła będzie wyglądać następująco:
=MAX(DATA($A$2;1;1);DATA($A$2;1;1)-DZIEŃ.TYG(DATA($A$2;1;1);11)+($B$2–1)*7+1)
Obliczamy to za pomocą funkcji MAX dla różnych argumentów. Drugim argumentem jest nic innego jak funkcja, której użyliśmy do obliczenia początku tygodnia w jednym z przykładów powyżej. Mamy ustalone zapisem MAX(DATA($A$2;1;1), że 1 stycznia to jest początek roku, że nie może to być data wcześniej (rys. nr 5)
Koniec tygodnia obliczymy wtedy korzystając z funkcji MIN. Musimy sobie założyć minimalną datę, możemy to zrobić dodając do roku 1, otrzymamy wtedy rok 2016, natomiast kiedy jako trzeci argument podamy 0, cofniemy się o jeden dzień do tyłu do dnia 2015-12-31. Łatwiejszym sposobem byłoby podanie po prostu w formule miesiąca 12 i dnia 31 – zapis by wyglądał tak: DATA($A$2;12;31)
Zapis funkcji będzie wyglądał następująco (rys. nr 6)
=MIN(DATA($A$2+1;1;0);DATA($A$2;1;1)-DZIEŃ.TYG(DATA($A$2;1;1);11) +$B$2*7)
Jeśli chcielibyśmy aby początek tygodnia był w inny dzień to w podanych formułach wystarczy zmienić drugi argument w funkcji DZIEŃ.TYG. Na przykład dla początku w środę byłaby to wartość 13 (rys. nr 7)
Podsumowując jest to bardzo trudne zagadnienie i ma wiele wariantów rozwiązania 😛
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
