Excel: Izračunajte tedne - tako deluje s formulo

Poiščite pravo funkcijo

V programu Microsoft Excel je na voljo funkcija KALENDARNI TEDEN, s katero lahko prikažete koledarski teden za določen datum. Na primer, lahko samodejno prikažete, v katerem tednu poteka sestanek. Funkcija tabele CALENDAR WEEK v Excelu ima tudi svoje pasti. To je zato, ker je koledarski teden v Nemčiji, Avstriji in Švici opredeljen drugače kot v sosednjih evropskih državah ali ZDA. Ta članek prikazuje, kako lahko še vedno izračunate pravilen koledarski teden in razlike med koledarskim tednom v regiji DACH in drugih državah.

Funkcija TEDEN KALENDARJA v Excelu

Funkcija TEDEN KALENDARJA izračunava tudi koledarske tedne v evropskih različicah Excela po ameriškem standardu. Prvi dan v letu je običajno dodeljen prvemu koledarskemu tednu.

Sintaksa za izračun koledarskega tedna je:

= KALENDARSKI TEDEN (DATUM)

Koledarski teden je v Nemčiji, Avstriji in Švici drugače opredeljen. To je v bistvu teden, ki je kot prvi teden v letu sestavljen iz najmanj štirih dni. Če prvi januar pade na petek, soboto ali nedeljo, funkcija tabele CALENDAR WEEK vrne napačen rezultat za Evropo.

Pravilno določite koledarske tedne v Excelu - tako deluje

Za prikaz pravilnega koledarskega tedna za Nemčijo, Avstrijo ali Švico potrebujete formulo. To mora biti na primer za celico C4 naslednje:

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

Formula je zapletena in obsežna. Da bi razložili, kako deluje, ločeno upoštevamo posamezne sestavine formule:

• V formuli se večkrat pojavi funkcija REST (A4-2; 7). Ta funkcija deli datum s 7 in vrne nastali ostanek.Ta funkcija vrne vrednost 0 za ponedeljke, vrednost 1 za torek do vrednosti 6 za nedelje.
• V formuli se število 2 odšteje od vrednosti datuma. Razlog za to je, da je bil osnovni datum 1. 1. 1900 nedelja. Če ga delite s 7, ostane ostanek 1. Zaradi tega bi bil ponedeljek razrešen na številko 2. Če želite popraviti štetje dni na 0 za nedeljo, 1 za ponedeljek itd., Se številka 2 odšteje od datuma.
• Na podlagi izračuna tega tedna del (LETO (A4-REST (A4-2; 7) +3)) zagotavlja letno vrednost, zmanjšano za 1 za prve tri dni v letu, pod pogojem, da so ti dnevi petki, sobote ali nedelje dejanja.
• Ta letna vrednost je vključena v datum v funkciji DATE. Ta datum se običajno oblikuje z drugim argumentom 1 za mesec januar.
• Kot dnevno vrednost prenesite zgoraj opisano funkcijo REST na funkcijo DATE minus vrednost 9. To običajno povzroči negativno dnevno vrednost. Formula kaže, da je datum, izračunan z DATE, običajno december.
• Za prve tri dni v letu pa je december v letu predzadnji, pod pogojem, da so ti trije dnevi petki, sobote ali nedelje.
• Ta datum, izračunan s funkcijo DATE, odštejete od datuma v stolpcu A, razliko delite s 7 (za 7 dni v tednu). Celoten izračun uporabite v okviru funkcije SHORT, da dobite samo celoštevilčne vrednosti.

Druga možnost: uporabite funkcijski argument WOCHENSTART

Druga možnost za delo s funkcijo CALENDAR WEEK v Microsoft Excelu in doseganje pravilnih rezultatov je, da formuli dodate tedenski argument argument start. Podobno kot pri formuli, prikazani zgoraj, je za izdelavo formule potreben trud.

Argument funkcije WOCHENSTART lahko razlikuje med dvema različnima sistemoma za izračun koledarskega tedna:

1. Sistem 1 (Ameriški sistem) - 1. teden poroča kot teden, ki vključuje prvi januar.
2. Sistem 2 (Evropski standard po ISO 8601) - KW 1 je teden, ki vključuje prvi četrtek v letu.

Če uporabljate funkcijski argument WOCHENSTART in funkcijo koledarskega tedna, morate kot argumente vedno vnesti naslednje številke:

Ponedeljek	2 ali 11
Torek	12
Sreda	13
Četrtek	14
Petek	15
Sobota	16
Nedelja	1 ali 17

Funkcijo TEDEN KALENDARJA povežite s funkcijskim argumentom WOCHENSTART v 6 korakih

Če želite povezati funkcijo koledarskega tedna z začetkom tedenskega argumenta funkcije, storite naslednje:

1. V Excelovo preglednico vnesite datum.

2. Z levo tipko miške kliknite celico, v katero želite vključiti koledarski teden.

3. Preklopite na zavihek FORMULAS in z levim gumbom miške kliknite "Vstavi funkcijo".

   

4. V iskalno polje vnesite TEDEN KALENDARJA.

   

5. Ko najdete funkcijo KALENDARSKI TEDEN, potrdite svojo izbiro z OK.

6. Takoj, ko ste v vnosnem polju funkcije, vnesite komponente formule na naslednji način:

   • Serijska_številka = celica, ki vsebuje datum, na primer A1.
   • Vrsta številke = številčna vrednost za dan v tednu v skladu s zgornjo tabelo.
   

Koledarski teden je pravilno izpisan v skladu s standardom ISO 8601.

Kako izračunati začetek in konec katerega koli koledarskega tedna v Excelu

Koledarski tedni igrajo ključno vlogo pri profesionalnem urniku. Pogosto so načrtovani ali dogovorjeni termini za določen koledarski teden. V tem primeru morate

1. Vedite, kateri koledarski teden pade določen datum
2. Ugotovite, kateri datum se določen koledarski teden začne ali konča.

Ti podatki vam na primer pomagajo pri načrtovanju datumov proizvodnje v industrijskem okolju ali pri določanju datumov dostave ali obračunavanja.

S posebno formulo lahko preprosto določite začetek in konec katerega koli koledarskega tedna. Formula ima naslednjo strukturo:

= BESEDILO (DATE (številka leta; 1; 7 * calweek-3-
TEDEN (DATE (številka leta ;;); 3)); "DD.MM.LLL")
& "-" & BESEDILO (DATUM (leto številka; 1; 7 * koledarski teden + 3
TEDEN (DATE (številka leta ;;); 3)); "DD.MM.LLL")

Formuli posredujete dva argumenta: Z argumentom Številka leta določite leto, v katerem se nahaja koledarski teden. Z argumentom posredujete številko koledarskega tedna Kalweek.

Rezultat formule je datum ponedeljka tega koledarskega tedna, ki mu sledi vezaj in datum nedelje koledarskega tedna.

V prikazanem delovnem zvezku je naslednja formula v celici D4 rezultat obdobja "09/30/13 - 10/6/13" kot rezultat:

= BESEDILO (DATUM (C4; 1; 7 * B4-3-TEDEN
(DATE (C4 ;;); 3)); "DD.MM.YY") & "-" & BESEDILO
(DATUM (C4; 1; 7 * B4 + 3-TEDENSKI (DATUM (C4 ;;); 3));
"DD.MM.YY")

To obdobje je koledarski teden 40 iz celice B4 leta 2013 iz celice C4.

Rezultat formule prikaže besedilo, ki ga ne morete uporabiti za nadaljevanje izračuna. Če želite pojasniti izhodno časovno obdobje, lahko rezultat prikažete tako, da so prikazani tudi dnevi v tednu. Če želite to narediti, spremenite obliko števila v dveh funkcijah BESEDILO formule na naslednji način:

= BESEDILO (DATUM (C4; 1; 7 * B4-3-TEDEN
(DATUM (C4 ;;); 3)); „DDD DD.MM.
YY “) &“ - “& BESEDILO (DATUM (C4; 1; 7 * B4 + 3-
TEDEN (DATE (C4 ;;); 3)); "DDDDD.MM.YY")

Po kopiranju formule v vse vrstice tabele se datumi vedno prikažejo z dnevi v tednu.

Kako izračunati tedne v Excelu in poiskati pravilen datum

V mnogih načrtih tedni igrajo vlogo enote, na primer koledarski ali produkcijski. Tedne je mogoče izračunati in prikazati tudi v Excelu z uporabo razumljivih obrazcev.

Naslednja slika prikazuje seznam vrednosti datuma v stolpcu A. V stolpcu B je vrednost, ki označuje, koliko tednov je treba to vrednost datuma prestaviti:

V stolpcu C želite uporabiti formulo, ki izračuna nov datum na podlagi datuma v stolpcu A in števila tednov v stolpcu B. Če je število tednov vrednost večja od nič, se datum premakne v prihodnost, če je vrednost negativna, se premakne v preteklost.

Ta cilj lahko dosežete tako, da število tednov pomnožite z vrednostjo 7 in ga dodate prvotnemu datumu. Z zgoraj opisano strukturo tabele je formula za celico C2 videti tako:

= A1 + 7 * B1

Formula se uporablja, ker Microsoft Excel notranje shranjuje datumske vrednosti kot številke. Razdalja od enega dneva do drugega je 1. Naslednja slika prikazuje uporabo formule v vzorčni tabeli:

Zaključek: Koledarski tedni so ključni za strokovno vodenje sestankov

Imenovanja določajo naše življenje tako v poklicnem kot zasebnem življenju. Ko načrtujete sestanke, koledarski tedni postajajo vse pomembnejši z naraščajočo intenzivnostjo sestankov, saj jih je mogoče uporabiti za več jasnosti pri načrtovanju sestankov.

Microsoft Excel lahko za vsak datum določi pravilen koledarski teden. Odločilni dejavnik pri tem je, da uporabniki v Evropi pri gradnji svojih Excelovih formul upoštevajo evropski sistem datumov po ISO 8601. Koledarski teden je privzeto izračunan po ameriškem sistemu, kar vodi do odstopanj. V ZDA je prvi dan v letu običajno dodeljen prvemu koledarskemu tednu. Nasprotno pa je v Nemčiji, Avstriji in Švici prvi koledarski teden na splošno opredeljen kot teden, ki je kot prvi teden v letu sestavljen iz najmanj štirih dni. V praksi to pomeni, da je 1. teden v evropskem sistemu teden, ki vključuje prvi četrtek v letu.

Za pravilen izračun koledarskega tedna lahko uporabite tako funkcijo CALENDAR WEEK kot argument WEEKSTART v Excelu. Možno je tudi nastaviti posamezne formule in jih prilagoditi posebej preglednici.