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:
- System 1 (system amerykański) – podaje tydzień 1 jako tydzień, który obejmuje pierwszy styczeń.
- 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:
-
Wprowadź datę w arkuszu kalkulacyjnym Excel.
-
Kliknij lewym przyciskiem myszy komórkę, w której chcesz uwzględnić tydzień kalendarzowy.
-
Przejdź do zakładki FORMUŁY i kliknij lewym przyciskiem myszy na „Wstaw funkcję”.
-
Wpisz TYDZIEŃ KALENDARZA w polu wyszukiwania.
-
Po znalezieniu funkcji TYDZIEŃ KALENDARZA potwierdź swój wybór przyciskiem OK.
-
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
- Dowiedz się, w którym tygodniu kalendarzowym przypada określona data
- 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.