Excel: Vypočítajte týždne - takto to funguje so vzorcom

Nájdite správnu funkciu

V programe Microsoft Excel je k dispozícii funkcia KALENDÁR TÝŽDEŇ, pomocou ktorej môžete zobraziť kalendárny týždeň pre konkrétny dátum. Môžete napríklad automaticky zobraziť, v ktorom týždni sa schôdzka koná. Tabuľková funkcia KALENDÁRNY TÝŽDEŇ v Exceli má tiež svoje úskalia. Kalendárny týždeň je totiž v Nemecku, Rakúsku a Švajčiarsku definovaný inak ako v susedných európskych krajinách alebo USA. Tento článok ukazuje, ako môžete stále vypočítať správny kalendárny týždeň a rozdiely medzi kalendárnym týždňom v regióne DACH a iných krajinách.

Funkcia KALENDÁRNY TÝŽDEŇ v programe Excel

Funkcia KALENDÁRNY TÝŽDEŇ taktiež vypočítava kalendárne týždne v európskych verziách Excelu pomocou amerického štandardu. Prvý deň v roku je spravidla zaradený do kalendárneho týždňa 1.

Syntax na výpočet kalendárneho týždňa je:

= TÝŽDEŇ KALENDÁRA (DÁTUM)

V Nemecku, Rakúsku a Švajčiarsku je kalendárny týždeň definovaný odlišne. Je to v podstate týždeň, ktorý ako prvý týždeň v roku pozostáva najmenej zo štyroch dní. Ak prvý január pripadne na piatok, sobotu alebo nedeľu, tabuľková funkcia KALENDÁRNY TÝŽDEŇ vráti pre Európu nesprávny výsledok.

Správne určte kalendárne týždne v Exceli - takto to funguje

Na zobrazenie správneho kalendárneho týždňa pre Nemecko, Rakúsko alebo Švajčiarsko potrebujete vzorec. V bunke C4 to napríklad musí vyzerať takto:

= OREZAŤ ((DÁTUM A4 (ROK (A4-ZOSTÁVA (A4-2,7) +3); 1; ZOSTÁVA (A4-2,7) -9)) / 7)

Vzorec je komplexný a rozsiahly. Aby sme vysvetlili, ako to funguje, zvažujeme jednotlivé zložky vzorca oddelene:

• Vo vzorci sa niekoľkokrát objaví funkcia REST (A4-2; 7). Táto funkcia vydelí dátum číslom 7 a vráti výsledný zvyšok. Táto funkcia vráti hodnotu 0 v pondelok, hodnotu 1 v utorok a hodnotu 6 v nedeľu.
• Vo vzorci sa číslo 2 odpočíta od hodnoty dátumu. Dôvodom je to, že základný dátum 1. 1. 1900 bol nedeľa. Delené 7 výsledkami vo zvyšku 1. Z tohto dôvodu bude pondelok vyriešený na číslo 2. Ak chcete opraviť počet dní na 0 v nedeľu, 1 v pondelok atď., Číslo 2 sa odpočíta od dátumu.
• Na základe výpočtu pre tento deň v týždni časť (ROK (ODPOČINOK A4 (A4-2; 7) +3) poskytuje ročnú hodnotu zníženú o 1 počas prvých troch dní v roku za predpokladu, že týmito dňami sú piatok, sobota alebo nedeľa) koná.
• Táto ročná hodnota je zahrnutá v dátume vo funkcii DATE. Tento dátum sa spravidla tvorí s druhým argumentom 1 na mesiac január.
• Ako dennú hodnotu preneste vyššie uvedenú funkciu REST do funkcie DATE mínus hodnota 9. Toto spravidla vedie k zápornej dennej hodnote. Vzorec ukazuje, že dátum vypočítaný podľa DATE je spravidla v decembri.
• Prvé tri dni v roku je však december roku predposledného za predpokladu, že tieto tri dni sú piatok, sobota alebo nedeľa.
• Tento dátum vypočítaný funkciou DATE odpočítate od dátumu v stĺpci A, rozdiel vydelíte 7 (za 7 dní v týždni) Celý tento výpočet použijete v rámci funkcie SHORT, aby ste získali iba celočíselné hodnoty.

Alternatívne: Použite funkčný argument WOCHENSTART

Ďalšou možnosťou, ako pracovať s funkciou KALENDÁRNY TÝŽDEŇ v programe Microsoft Excel a dosiahnuť správne výsledky, je pridať do vzorca argument argument týždňa začiatok. Podobne ako vo vyššie uvedenom vzorci, pri zostavovaní vzorca je potrebné vynaložiť úsilie.

Argument funkcie WOCHENSTART môže rozlišovať medzi dvoma rôznymi systémami na výpočet kalendárneho týždňa:

1. Systém 1 (Americký systém) - hlási 1. týždeň ako týždeň, ktorý zahŕňa prvý január.
2. Systém 2 (Európska norma podľa ISO 8601) - KW 1 je týždeň, ktorý zahŕňa prvý štvrtok v roku.

Ak používate argument funkcie WOCHENSTART a funkciu kalendárneho týždňa, musíte ako argumenty vždy zadať nasledujúce čísla:

Pondelok	2 alebo 11
Utorok	12
Streda	13
Štvrtok	14
Piatok	15
Sobota	16
Nedeľa	1 alebo 17

V 6 krokoch prepojte funkciu CALENDAR WEEK s funkčným argumentom WOCHENSTART

Ak chcete prepojiť funkciu kalendárneho týždňa s funkčným argumentom začiatok týždňa, postupujte takto:

1. Zadajte dátum do tabuľky programu Excel.

2. Kliknite ľavým tlačidlom myši na bunku, do ktorej chcete zahrnúť kalendárny týždeň.

3. Prepnite na kartu VZORY a kliknite ľavým tlačidlom myši na položku „Vložiť funkciu“.

   

4. Do vyhľadávacieho poľa zadajte KALENDÁRNY TÝŽDEŇ.

   

5. Potom, čo nájdete funkciu KALENDÁRNY TÝŽDEŇ, potvrďte svoj výber pomocou OK.

6. Hneď ako sa dostanete do vstupného poľa funkcie, zadajte zložky vzorca nasledovne:

   • Sériové číslo = bunka obsahujúca dátum, napríklad A1.
   • Typ čísla = číselná hodnota pre deň v týždni podľa vyššie uvedenej tabuľky.
   

Kalendárny týždeň je správne produkovaný podľa ISO 8601.

Ako vypočítať začiatok a koniec akéhokoľvek kalendárneho týždňa v programe Excel

Kalendárne týždne hrajú kľúčovú úlohu v profesionálnom plánovaní. Schôdzky sú často plánované alebo dohodnuté na konkrétny kalendárny týždeň. V takom prípade musíte

1. Zistite tiež, na ktorý kalendárny týždeň pripadá určitý dátum
2. Zistite, akým dátumom sa konkrétny kalendárny týždeň začína alebo končí.

Tieto informácie vám pomôžu napríklad pri plánovaní termínov výroby v priemyselnom prostredí alebo pri dojednávaní termínov dodania alebo fakturácie.

Pomocou špeciálneho vzorca môžete ľahko určiť začiatok a koniec akéhokoľvek kalendárneho týždňa. Vzorec má nasledujúcu štruktúru:

= TEXT (DÁTUM (číslo roku; 1; 7 * calweek-3-
WEEKDAY (DATE (číslo roku;;); 3)); „DD.MM.RR“)
& "-" & TEXT (DÁTUM (číslo roku; 1; 7 * kalendárny týždeň + 3-
WEEKDAY (DATE (číslo roku;;); 3)); „DD.MM.RR“)

Do vzorca odovzdáte dva argumenty: S argumentom Číslo roku definujte rok, v ktorom sa nachádza kalendárny týždeň. S argumentom odovzdáte číslo kalendárneho týždňa Kalweek.

Výsledkom vzorca je dátum pondelka tohto kalendárneho týždňa, za ktorým nasleduje pomlčka a dátum nedele kalendárneho týždňa.

V zobrazenom zošite nasledujúci vzorec v bunke D4 poskytuje ako výsledok obdobie „09/30/13 - 10/6/13“:

= TEXT (DÁTUM (C4; 1; 7 * B4-3-TÝŽDEŇ)
(DATE (C4;;); 3)); "DD.MM.YY") & "-" & TEXT
(DÁTUM (C4; 1; 7 * B4 + 3-TÝŽDEŇ (DÁTUM (C4;;); 3));
„DD.MM.RR“)

Toto obdobie je kalendárny týždeň 40 z bunky B4 v roku 2013 z bunky C4.

Výsledkom vzorca je text, ktorý nemôžete použiť na pokračovanie vo výpočte. Aby ste objasnili časové obdobie, ktoré je výstupom, môžete výsledok zobraziť tak, že sa budú zobrazovať aj dni v týždni. Za týmto účelom zmeňte formát čísel v dvoch TEXTOVÝCH funkciách vzorca nasledovne:

= TEXT (DÁTUM (C4; 1; 7 * B4-3-TÝŽDEŇ)
(DÁTUM (C4;;); 3)); „DDD DD.MM.
YY “) &„ - “& TEXT (DÁTUM (C4; 1; 7 * B4 + 3-
WEEKDAY (DATE (C4;;); 3)); "DDDDD.MM.YY")

Po skopírovaní vzorca do všetkých riadkov tabuľky sa dátumy vždy zobrazia s dňami v týždni.

Ako vypočítať týždne v programe Excel a nájsť správny dátum

V mnohých plánoch zohrávajú úlohu týždne, napríklad kalendárne týždne alebo výrobné týždne. Týždne je tiež možné vypočítať a zobraziť v programe Excel pomocou ľahko zrozumiteľných formulárov.

Nasledujúci obrázok zobrazuje zoznam hodnôt dátumu v stĺpci A. V stĺpci B je hodnota, ktorá naznačuje, o koľko týždňov sa má táto hodnota dátumu odložiť:

V stĺpci C chcete použiť vzorec, ktorý vypočíta nový dátum na základe dátumu v stĺpci A a počtu týždňov v stĺpci B. Ak je počet týždňov hodnota väčšia ako nula, dátum sa posunie do budúcnosti, ak je hodnota záporná, posunie sa do minulosti.

Tento cieľ môžete dosiahnuť vynásobením počtu týždňov hodnotou 7 a pripočítaním k pôvodnému dátumu. So štruktúrou tabuľky popísanou vyššie vyzerá vzorec pre bunku C2 takto:

= A1 + 7 * B1

Vzorec je použiteľný, pretože Microsoft Excel ukladá hodnoty dátumu interne ako čísla. Vzdialenosť od jedného dňa do druhého je 1. Nasledujúci obrázok ukazuje použitie vzorca v príkladovej tabuľke:

Záver: Kalendárne týždne sú rozhodujúce pre profesionálne riadenie schôdzok

Schôdzky určujú náš život v profesionálnom aj súkromnom živote. Pri plánovaní schôdzok sa kalendárne týždne stávajú dôležitejšími s rastúcou intenzitou schôdzok, pretože ich možno použiť na zvýšenie prehľadnosti plánovania schôdzok.

Program Microsoft Excel dokáže pre každý dátum určiť správny kalendárny týždeň. Rozhodujúcim faktorom je, že používatelia v Európe pri vytváraní vzorcov programu Excel dodržujú európsky systém dátumov podľa ISO 8601. Štandardne je kalendárny týždeň vypočítaný podľa systému USA, čo vedie k odchýlkam. V USA je prvý deň v roku spravidla priradený ku kalendárnemu týždňu 1. V Nemecku, Rakúsku a Švajčiarsku je naopak prvý kalendárny týždeň spravidla definovaný ako týždeň, ktorý ako prvý týždeň v roku pozostáva najmenej zo štyroch dní. V praxi to znamená, že 1. týždeň v európskom systéme je týždeň, ktorý zahŕňa prvý štvrtok v roku.

Na správny výpočet kalendárneho týždňa môžete v Exceli použiť funkciu KALENDÁRNY TÝŽDEŇ aj argument funkcie WEEKSTART. Je tiež možné nastaviť jednotlivé vzorce a prispôsobiť ich konkrétne vašej tabuľke.