Excel: Számítsa ki a heteket - ez így működik egy képlettel

Keresse meg a megfelelő funkciót

A Microsoft Excel programban elérhető a NAPPALI HET funkció, amellyel megjelenítheti a naptári hetet egy adott dátumhoz. Például automatikusan megjelenítheti, hogy melyik héten kerül sor a megbeszélésre. Az Excel CALENDAR WEEK táblafüggvényének is megvannak a buktatói. Ennek oka az, hogy Németországban, Ausztriában és Svájcban másként határozzák meg a naptári hetet, mint a szomszédos európai országokban vagy az USA -ban. Ez a cikk bemutatja, hogyan lehet továbbra is kiszámítani a helyes naptári hetet, valamint a különbségeket a naptári hét között a DACH -régióban és más országokban.

A NAPPALI HET függvény az Excelben

A CALENDAR WEEK függvény az amerikai szabvány szerint az Excel európai verzióinak naptári heteit is kiszámítja. Az év első napja általában az 1. naptári héthez tartozik.

A naptári hét kiszámításának szintaxisa a következő:

= NAPTÁR HETI (DÁTUM)

A naptári hetet Németországban, Ausztriában és Svájcban másképp határozzák meg. Ez alapvetően az a hét, amely az év első heteként legalább négy napból áll. Ha január elseje péntekre, szombatra vagy vasárnapra esik, a NAPTÁR HETI táblázatfunkció helytelen eredményt ad vissza Európára.

Határozza meg helyesen a naptári heteket az Excelben - ez így működik

Németország, Ausztria vagy Svájc helyes naptári hetének megjelenítéséhez képletre van szüksége. Például ennek a következőnek kell lennie a C4 cella esetében:

= TRIM ((A4-DATE (ÉV (A4-REMAIN (A4-2,7) +3); 1; REMAIN (A4-2,7) -9)) / 7)

A képlet összetett és kiterjedt. Annak érdekében, hogy megmagyarázzuk, hogyan működik, külön vesszük figyelembe a képlet egyes összetevőit:

• A REST (A4-2; 7) funkció többször megjelenik a képletben. Ez a függvény osztja a dátumot 7 -gyel, és visszaadja a kapott maradékot. Ez a függvény hétfőn 0 -t, kedden 1 -et, vasárnap pedig 6 -ot ad vissza.
• A képletben a 2 -es számot kivonjuk a dátum értékéből. Ennek az az oka, hogy az alap dátuma 1961. 01. 1. vasárnap volt. Elosztva 7 eredménnyel a maradék 1 -ben. Emiatt a hétfőt a 2 -es számra kell megoldani. Ha a vasárnapi napszámlálót 0 -ra, hétfőt 1 -re stb. Szeretné korrigálni, a 2 -es számot kivonjuk a dátumból.
• E hétköznapi számítás alapján a rész (YEAR (A4-REST (A4-2; 7) +3) az év első három napján 1-gyel csökkentett éves értéket biztosít, feltéve, hogy ezek a napok péntek, szombat vagy vasárnap) cselekmények.
• Ezt az éves értéket a DÁTUM függvény dátuma tartalmazza. Ez a dátum általában a január 1 -es második argumentummal kerül kialakításra.
• Napi értékként vigye át a fent leírt REST függvényt a DATE függvényre mínusz 9 értékkel. Ez általában negatív napi értéket eredményez. A képlet azt mutatja, hogy a DATE által kiszámított dátum általában decemberben van.
• Az év első három napján azonban az utolsó előtti év decembere van, feltéve, hogy ez a három nap péntek, szombat vagy vasárnap.
• Ezt a DÁTUM függvénnyel számított dátumot kivonja az A oszlop dátumából, a különbséget elosztja 7 -gyel (a hét 7 napjára). Ezt az egész számítást egy RÖVID függvényen belül használja fel, hogy csak egész számokat kapjon.

Alternatív megoldásként: használja a WOCHENSTART függvény argumentumot

Egy másik lehetőség a Microsoft Excel CALENDAR WEEK funkciójával való együttműködésre és a helyes eredmények elérésére, ha hozzáadja a hét elején kezdődő függvény argumentumot a képlethez. A fenti képlethez hasonlóan erőfeszítést igényel a képlet felépítése.

A WOCHENSTART függvény argumentum megkülönbözteti a két különböző rendszert a naptári hét kiszámításához:

1. 1. rendszer (Amerikai rendszer) - az 1. hetet a január elsejét magában foglaló hétként jelenti.
2. 2. rendszer (Európai szabvány az ISO 8601 szerint) - A KW 1 az a hét, amely magában foglalja az év első csütörtökét.

Ha a WOCHENSTART függvény argumentumot és a naptári hét függvényt használja, akkor mindig a következő számokat kell megadnia argumentumként:

hétfő	2 vagy 11
kedd	12
szerda	13
csütörtök	14
péntek	15
szombat	16
vasárnap	1 vagy 17

Kapcsolja össze a CALENDAR WEEK függvényt a WOCHENSTART függvény argumentummal 6 lépésben

A naptári hét függvény és a hét kezdete függvény argumentum összekapcsolásához tegye a következőket:

1. Adjon meg egy dátumot az Excel táblázatban.

2. Kattintson a bal egérgombbal arra a cellára, amelybe be szeretné illeszteni a naptári hetet.

3. Váltson a FORMULAS fülre, és kattintson a bal egérgombbal a "Insert function" gombra.

   

4. Írja be a KALENDÁR HETET a keresőmezőbe.

   

5. Miután megtalálta a NAPPALI HET funkciót, erősítse meg a választást az OK gombbal.

6. Amint a függvény beviteli mezőjében van, írja be a képlet összetevőit az alábbiak szerint:

   • Sorozatszám = a dátumot tartalmazó cella, például A1.
   • Számtípus = a hét napjának számértéke a fenti táblázat szerint.
   

A naptári hét az ISO 8601 szabványnak megfelelően kerül kiadásra.

Hogyan lehet kiszámítani bármely naptári hét elejét és végét az Excelben

A naptári hetek döntő szerepet játszanak a szakmai ütemezésben. A kinevezéseket gyakran egy adott naptári hétre tervezik vagy egyeztetik. Ebben az esetben muszáj

1. Tudja meg, hogy melyik naptári hétre esik egy bizonyos dátum
2. Tudja meg, hogy egy adott naptári hét melyik napon kezdődik vagy ér véget.

Ez az információ segít például a gyártási dátumok ipari környezetben történő megtervezésében, vagy a szállítási vagy számlázási dátumok megszervezésében.

Egy speciális képlet segítségével könnyedén meghatározhatja bármely naptári hét kezdetét és végét. A képlet a következő szerkezetű:

= TEXT (DATE (évszám; 1; 7 * calweek-3-
HETI NAP (DATE (évszám;;); 3)); "NN.HH.ÉÉ")
& "-" & TEXT (DATE (évszám; 1; 7 * naptári hét + 3-
HETI NAP (DATE (évszám;;); 3)); "NN.HH.ÉÉ")

Két érvet ad át a képletnek: Az argumentummal Évszám határozza meg azt az évet, amelyben a naptári hét található. Az argumentummal átadod a naptári hét számát Kalweek.

A képlet eredménye ennek a naptári hétnek a hétfőjének dátuma, amelyet kötőjel és a naptári hét vasárnapjának dátuma követ.

A bemutatott munkafüzetben a következő képlet a D4 cellában a "09/30/13 - 10/6/13" periódust adja eredményül:

= TEXT (DATE (C4; 1; 7 * B4-3-WEEKDAY)
(DATE (C4;;); 3)); "DD.MM.YY") & "-" & TEXT
(DÁTUM (C4; 1; 7 * B4 + 3-HETI NAP (DÁTUM (C4;;); 3));
"DD.MM.YY")

Ez az időszak a 40. naptári hét a B4 cellából 2013 -ban a C4 cellából.

A képlet eredménye olyan szöveget eredményez, amelyet nem használhat a számítás folytatásához. A kimeneti időtartam tisztázása érdekében megjelenítheti az eredményt úgy, hogy a hét napjai is megjelenjenek. Ehhez módosítsa a képlet két SZÖVEG függvényében a számformátumot az alábbiak szerint:

= TEXT (DATE (C4; 1; 7 * B4-3-WEEKDAY)
(DÁTUM (C4;;); 3)); „DDD DD.MM.
YY “) &“ - “& TEXT (DATE (C4; 1; 7 * B4 + 3-
HETI NAP (DÁTUM (C4;;); 3)); "NNNNN.HH.ÉÉ")

Miután a képletet a táblázat minden sorába másolta, a dátumok mindig a hét napjaival kerülnek kiadásra.

Hogyan lehet kiszámítani a heteket az Excelben, és megtalálni a megfelelő dátumot

Sok tervben a hetek egységként játszanak szerepet, például a naptári hetek vagy a gyártási hetek. A hetek könnyen kiszámítható űrlapok segítségével Excelben is kiszámíthatók és megjeleníthetők.

Az alábbi ábra az A oszlop dátumértékeinek listáját mutatja. A B oszlopban van egy érték, amely azt jelzi, hogy ezt a dátumértéket hány héttel kell elhalasztani:

A C oszlopban olyan képletet szeretne használni, amely kiszámítja az új dátumot az A oszlopban található dátum és a hetek száma alapján a B oszlopban. Ha a hetek száma nullánál nagyobb érték, akkor a dátum a jövőbe tolódik, ha negatív, akkor a múltba.

Ezt a célt úgy érheti el, ha megszorozza a hetek számát a 7 értékkel, és hozzáadja az eredeti dátumhoz. A fent leírt táblázat szerkezetével a C2 cella képlete így néz ki:

= A1 + 7 * B1

A képlet alkalmazható, mert a Microsoft Excel a dátumértékeket belsőleg számként tárolja. Az egyik naptól a másikig tartó távolság 1. Az alábbi ábra egy példa táblázatban mutatja be a képlet használatát:

Következtetés: A naptári hetek kulcsfontosságúak a professzionális kinevezések kezelésében

A kinevezések határozzák meg életünket mind a szakmai, mind a magánéletben. A találkozók tervezésekor a naptári hetek egyre fontosabbá válnak a növekvő kinevezési intenzitással, mivel ezek segítségével egyértelműbbé tehető a találkozók tervezése.

A Microsoft Excel minden dátumhoz képes megadni a megfelelő naptári hetet. A döntő tényező itt az, hogy az európai felhasználók Excel képleteik készítésekor betartják az ISO 8601 szerinti európai dátumrendszert. Alapértelmezés szerint a naptári hetet az amerikai rendszer szerint számítják ki, ami eltérésekhez vezet. Az USA -ban az év első napját általában az 1. naptári héthez rendelik. Ezzel szemben Németországban, Ausztriában és Svájcban az első naptári hetet általában úgy határozzák meg, hogy az év első hete legalább négy napból áll. A gyakorlatban ez azt jelenti, hogy az európai rendszer 1. hete az a hét, amely magában foglalja az év első csütörtökét.

A naptári hét helyes kiszámításához használhatja a CALENDAR WEEK függvényt és a WEEKSTART függvény argumentumát az Excelben. Lehetőség van egyedi képletek beállítására és a táblázathoz való igazítására is.