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.

rys. nr 1 — Przykładowe dane

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

rys. nr 2 — Obliczenie początku tygodnia w systemie 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

rys. nr 3 — Obliczenie początku tygodnia dla założenia, że tydzień zawierający 1 stycznia jest 1‐szym tygodniem roku

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

rys. nr 4 — Obliczenie początku tygodnia dla założenia, że tydzień rozpoczyna się w czwartek

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)

rys. nr 5 — Obliczenie początku tygodnia za pomocą funkcji MAX

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)

rys. nr 6 — Obliczenie początku tygodnia za pomocą funkcji MIN

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)

rys. nr 7 — Systemy przyjmowania początku i końca tygodnia

Podsumowując jest to bardzo trudne zagadnienie i ma wiele wariantów rozwiązania 😛


Właśnie dodałem mój kurs o Power BI Desktop firmy Microsoft na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Power BI Desktop to:
- Ponad 6 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki pobierania, łączenia i analizy danych, a na koniec ich wizualizacji.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o PowerBI Desktop:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę analizy i wizualizacji danych za pomocą odpowiednio stworzonych zapytań i relacji w PowerBI Desktop.

  1. Wstęp do aplikacji PowerBI Desktop i jej możliwości
  2. Tworzenie i modyfikowanie zapytań (pobieranie danych)
  3. Modelowanie danych w PowerBI Desktop
  4. Wizualizacja danych i tworzenie raportów
  5. Usługa internetowa
  6. PowerBI Pro — kilka słów o płatnej części usługi PowerBI

Wejdź na stronę kursu PowerBI Desktop i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy,
żeby przekonać się czy to kurs dla Ciebie.