Excel: Oblicz tygodnie - tak to działa z formułą

Znajdź odpowiednią funkcję

W programie Microsoft Excel dostępna jest funkcja TYDZIEŃ KALENDARZA, za pomocą której można wyświetlić tydzień kalendarzowy dla określonej daty. Na przykład możesz automatycznie wyświetlić, w którym tygodniu odbywa się spotkanie. Funkcja tabelowa TYDZIEŃ KALENDARZA w programie Excel ma również swoje pułapki. Dzieje się tak, ponieważ tydzień kalendarzowy jest inaczej definiowany w Niemczech, Austrii i Szwajcarii niż w sąsiednich krajach europejskich czy USA. Z tego artykułu dowiesz się, jak nadal można obliczyć prawidłowy tydzień kalendarzowy oraz różnice między tygodniem kalendarzowym w regionie DACH a innymi krajami.

Funkcja TYDZIEŃ KALENDARZA w Excelu

Funkcja TYDZIEŃ KALENDARZA oblicza również tygodnie kalendarzowe w europejskich wersjach programu Excel przy użyciu standardu amerykańskiego. Pierwszy dzień roku jest zazwyczaj przypisany do pierwszego tygodnia kalendarzowego.

Składnia obliczania tygodnia kalendarzowego to:

= TYDZIEŃ KALENDARZOWY (DATA)

Tydzień kalendarzowy jest inaczej definiowany w Niemczech, Austrii i Szwajcarii. Jest to w zasadzie tydzień, który jako pierwszy tydzień roku składa się z co najmniej czterech dni. Jeśli pierwszy stycznia przypada w piątek, sobotę lub niedzielę, funkcja tabelowa TYDZIEŃ KALENDARZA zwróci niepoprawny wynik dla Europy.

Określ poprawnie tygodnie kalendarzowe w Excelu - tak to działa

Aby wyświetlić prawidłowy tydzień kalendarzowy dla Niemiec, Austrii lub Szwajcarii, potrzebujesz formuły. Na przykład musi to wyglądać następująco dla komórki C4:

= PRZYCINANIE ((A4-DATA (ROK (A4-POZOSTAŁO (A4-2,7) +3); 1; POZOSTAŁO (A4-2,7) -9)) / 7)

Formuła jest złożona i rozbudowana. Aby wyjaśnić, jak to działa, rozważymy poszczególne składniki formuły osobno:

• Funkcja REST (A4-2; 7) pojawia się w formule kilka razy. Ta funkcja dzieli datę przez 7 i zwraca wynikową resztę.Ta funkcja zwraca wartość 0 dla poniedziałków, wartość 1 dla wtorków do wartości 6 dla niedziel.
• We wzorze liczba 2 jest odejmowana od wartości daty. Powodem tego jest to, że datą bazową była niedziela. Podzielone przez 7 daje resztę 1. Z tego powodu poniedziałek zostanie rozliczony na 2. Aby skorygować liczbę dni na 0 dla niedzieli, 1 dla poniedziałku itd., liczba 2 jest odejmowana od daty.
• Na podstawie tego obliczenia dnia tygodnia, część (ROK (A4-ODPOCZYNEK (A4-2; 7) +3) zapewnia roczną wartość pomniejszoną o 1 dla pierwszych trzech dni w roku, pod warunkiem, że są to piątki, soboty lub niedziele dzieje.
• Ta roczna wartość jest zawarta w dacie w funkcji DATE. Ta data jest zwykle tworzona z drugim argumentem 1 dla miesiąca stycznia.
• Jako wartość dzienną przenieś opisaną powyżej funkcję REST do funkcji DATE minus wartość 9. Daje to zazwyczaj ujemną wartość dzienną. Wzór pokazuje, że data obliczona przez DATE przypada na ogół w grudniu.
• Jednak przez pierwsze trzy dni w roku jest to grudzień roku poprzedzającego przedostatni, pod warunkiem, że te trzy dni to piątki, soboty lub niedziele.
• Odejmujesz tę datę obliczoną przez funkcję DATE od daty w kolumnie A, dzielisz różnicę przez 7 (dla 7 dni tygodnia) Używasz całego obliczenia w funkcji SHORT, aby uzyskać tylko wartości całkowite.

Alternatywnie: użyj argumentu funkcji WOCHENSTART

Inną możliwością pracy z funkcją KALENDARZ TYDZIEŃ w programie Microsoft Excel i uzyskania poprawnych wyników jest dodanie do formuły argumentu funkcji początek tygodnia. Podobnie jak w przypadku wzoru pokazanego powyżej, zbudowanie wzoru wymaga wysiłku.

Argument funkcji WOCHENSTART może rozróżniać dwa różne systemy obliczania tygodnia kalendarzowego:

1. System 1 (system amerykański) – podaje tydzień 1 jako tydzień, który obejmuje pierwszy styczeń.
2. System 2 (Norma europejska wg ISO 8601) - KW 1 to tydzień obejmujący pierwszy czwartek w roku.

W przypadku użycia argumentu funkcji ROZPOCZNIJKAL i funkcji tygodnia kalendarzowego jako argumenty należy zawsze wprowadzać następujące liczby:

poniedziałek	2 lub 11
Wtorek	12
Środa	13
czwartek	14
piątek	15
sobota	16
niedziela	1 lub 17

Połącz funkcję TYDZIEŃ KALENDARZA z argumentem funkcji WOCHENSTART w 6 krokach

Aby połączyć funkcję tygodnia kalendarza z argumentem funkcji początek tygodnia, wykonaj następujące czynności:

1. Wprowadź datę w arkuszu kalkulacyjnym Excel.

2. Kliknij lewym przyciskiem myszy komórkę, w której chcesz uwzględnić tydzień kalendarzowy.

3. Przejdź do zakładki FORMUŁY i kliknij lewym przyciskiem myszy na „Wstaw funkcję”.

   

4. Wpisz TYDZIEŃ KALENDARZA w polu wyszukiwania.

   

5. Po znalezieniu funkcji TYDZIEŃ KALENDARZA potwierdź swój wybór przyciskiem OK.

6. Gdy tylko znajdziesz się w polu wejściowym funkcji, wprowadź składniki formuły w następujący sposób:

   • Numer_seryjny = komórka zawierająca datę, na przykład A1.
   • Typ liczby = wartość liczbowa dla dnia tygodnia zgodnie z powyższą tabelą.
   

Tydzień kalendarzowy jest poprawnie wyprowadzany zgodnie z ISO 8601.

Jak obliczyć początek i koniec dowolnego tygodnia kalendarzowego w programie Excel

Tygodnie kalendarzowe odgrywają kluczową rolę w profesjonalnym planowaniu. Spotkania są często planowane lub uzgadniane na konkretny tydzień kalendarzowy. W takim razie musisz

1. Dowiedz się, w którym tygodniu kalendarzowym przypada określona data
2. Dowiedz się, kiedy zaczyna się lub kończy określony tydzień kalendarzowy.

Informacje te pomagają na przykład przy planowaniu dat produkcji w środowisku przemysłowym lub ustalaniu terminów dostaw lub rozliczeń.

Korzystając ze specjalnej formuły, możesz łatwo określić początek i koniec dowolnego tygodnia kalendarzowego. Formuła ma następującą strukturę:

= TEKST (DATA (numer roku; 1; 7 * caltydzień-3-
DZIEŃ TYGODNIA (DATA (numer roku;;); 3)); "DD.MM.RR")
& "-" & TEKST (DATA (numer roku; 1; 7 * tydzień kalendarzowy + 3-
DZIEŃ TYGODNIA (DATA (numer roku ;;); 3)); "DD.MM.RR")

Do formuły przekazujesz dwa argumenty: Z argumentem Numer roku określić rok, w którym znajduje się tydzień kalendarzowy. Podajesz numer tygodnia kalendarzowego z argumentem Kalweek.

Wynikiem formuły jest data poniedziałku tego tygodnia kalendarzowego, po której następuje myślnik i data niedzieli tygodnia kalendarzowego.

W pokazanym skoroszycie następująca formuła w komórce D4 podaje jako wynik okres „09/30/13 – 10/6/13”:

= TEKST (DATA (C4; 1; 7 * B4-3-DZIEŃ TYGODNIA
(DATA (C4;;); 3)); "DD.MM.RR") & "-" & TEKST
(DATA (C4; 1; 7 * B4 + 3 DZIEŃ TYGODNIA (DATA (C4 ;;); 3));
"DD.MM.RR")

Ten okres to 40 tydzień kalendarzowy z komórki B4 w 2013 roku z komórki C4.

Wynik formuły wyprowadza tekst, którego nie można użyć do dalszego obliczania. W celu wyjaśnienia wyprowadzanego okresu czasu można wyświetlić wynik w taki sposób, aby wyprowadzane były również dni tygodnia. Aby to zrobić, zmień format liczb w dwóch funkcjach TEKST formuły w następujący sposób:

= TEKST (DATA (C4; 1; 7 * B4-3-DZIEŃ TYGODNIA
(DATA (C4;;); 3)); „DDD DD.MM.
RR “) &” - “& TEKST (DATA (C4; 1; 7 * B4 + 3-
DZIEŃ TYGODNIA (DATA (C4 ;;); 3)); "DDDDDD.MM.RR")

Po skopiowaniu formuły do wszystkich wierszy tabeli daty są zawsze wyprowadzane z dniami tygodnia.

Jak obliczyć tygodnie w Excelu i znaleźć poprawną datę?

W wielu planach tygodnie pełnią rolę jednostki, na przykład tygodnie kalendarzowe lub tygodnie produkcyjne. Tygodnie można również obliczać i wyświetlać w programie Excel za pomocą łatwych do zrozumienia formularzy.

Poniższy rysunek przedstawia listę wartości dat w kolumnie A. W kolumnie B znajduje się wartość, która wskazuje o ile tygodni ta wartość daty powinna zostać przesunięta:

W kolumnie C chcesz użyć formuły, która oblicza nową datę na podstawie daty w kolumnie A i liczby tygodni w kolumnie B. Jeżeli liczba tygodni jest wartością większą od zera to data jest przesuwana w przyszłość, jeżeli wartość jest ujemna to data jest przesuwana w przeszłość.

Możesz osiągnąć ten cel, mnożąc liczbę tygodni przez wartość 7 i dodając ją do pierwotnej daty. W przypadku opisanej powyżej struktury tabeli formuła komórki C2 wygląda tak:

= A1 + 7 * B1

Formuła ma zastosowanie, ponieważ Microsoft Excel przechowuje wartości dat wewnętrznie jako liczby. Odległość od jednego dnia do drugiego wynosi 1. Poniższy rysunek przedstawia użycie wzoru w przykładowej tabeli:

Wniosek: Tygodnie kalendarzowe mają kluczowe znaczenie dla profesjonalnego zarządzania wizytami

Spotkania determinują nasze życie zarówno w życiu zawodowym, jak i prywatnym. Podczas planowania spotkań tygodnie kalendarzowe stają się ważniejsze wraz ze wzrostem intensywności spotkań, ponieważ można je wykorzystać do zapewnienia większej przejrzystości w planowaniu spotkań.

Microsoft Excel jest w stanie określić poprawny tydzień kalendarzowy dla każdej daty. Decydującym czynnikiem jest tutaj to, że użytkownicy w Europie przestrzegają europejskiego systemu dat zgodnie z ISO 8601 podczas tworzenia formuł Excela. Domyślnie tydzień kalendarzowy liczony jest według systemu amerykańskiego, co prowadzi do odchyleń. W USA pierwszy dzień roku jest zazwyczaj przypisywany do 1. tygodnia kalendarzowego. Natomiast w Niemczech, Austrii i Szwajcarii pierwszy tydzień kalendarzowy jest ogólnie definiowany jako tydzień, który jako pierwszy tydzień w roku składa się z co najmniej czterech dni. W praktyce oznacza to, że tydzień 1 w systemie europejskim to tydzień obejmujący pierwszy czwartek w roku.

Aby poprawnie obliczyć tydzień kalendarzowy, możesz użyć zarówno funkcji TYDZIEŃ KALENDARZA, jak i argumentu funkcji POCZĄTEK TYGODNIA w programie Excel. Możliwe jest również ustawienie indywidualnych formuł i dostosowanie ich specjalnie do arkusza kalkulacyjnego.