Excel: aprēķiniet nedēļas - tā tas darbojas ar formulu

Atrodiet pareizo funkciju

Programmā Microsoft Excel ir pieejama funkcija KALENDĀRA NEDĒĻA, ar kuru jūs varat parādīt kalendāro nedēļu noteiktam datumam. Piemēram, jūs varat automātiski parādīt, kurā nedēļā notiek tikšanās. Arī tabulas funkcijai CALENDAR WEEK programmā Excel ir savi trūkumi. Tas ir tāpēc, ka Vācijā, Austrijā un Šveicē kalendārā nedēļa ir definēta citādi nekā Eiropas kaimiņvalstīs vai ASV. Šis raksts parāda, kā jūs joprojām varat aprēķināt pareizo kalendāro nedēļu un atšķirības starp kalendāro nedēļu DACH reģionā un citās valstīs.

Funkcija KALENDĀRA NEDĒĻA programmā Excel

Funkcija CALENDAR WEEK arī aprēķina kalendārās nedēļas Excel Eiropas versijās, izmantojot amerikāņu standartu. Gada pirmā diena parasti tiek piešķirta 1. kalendārajai nedēļai.

Kalendāra nedēļas aprēķināšanas sintakse ir šāda:

= KALENDĀRA NEDĒĻA (DATE)

Kalendāra nedēļa Vācijā, Austrijā un Šveicē ir definēta atšķirīgi. Būtībā tā ir nedēļa, kas, tāpat kā gada pirmā nedēļa, sastāv no vismaz četrām dienām. Ja pirmais janvāris iekrīt piektdienā, sestdienā vai svētdienā, tabulas funkcija KALENDĀRA NEDĒĻA atgriež Eiropai nepareizu rezultātu.

Pareizi nosakiet kalendāra nedēļas programmā Excel - tā tas darbojas

Lai parādītu pareizo kalendāro nedēļu Vācijai, Austrijai vai Šveicei, jums ir nepieciešama formula. Piemēram, šūnai C4 tam jābūt šādam:

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

Formula ir sarežģīta un plaša. Lai izskaidrotu, kā tā darbojas, mēs atsevišķi aplūkojam formulas atsevišķās sastāvdaļas:

• Funkcija REST (A4-2; 7) formulā parādās vairākas reizes. Šī funkcija dala datumu ar 7 un atgriež atlikumu. Šī funkcija atgriež vērtību 0 pirmdienām, vērtību 1 otrdienām līdz vērtību 6 svētdienai.
• Formulā skaitlis 2 tiek atņemts no datuma vērtības. Iemesls tam ir tas, ka bāzes datums 1/1/1900 bija svētdiena. Sadalīts ar 7 rezultātiem atlikušajā daļā 1. Šī iemesla dēļ pirmdiena tiks atrisināta ar skaitli 2. Lai labotu dienu skaitu līdz 0 svētdienai, 1 pirmdienai utt., Skaitlis 2 tiek atņemts no datuma.
• Pamatojoties uz šo darba dienu aprēķinu, daļa (GADS (A4-REST (A4-2; 7) +3)) nodrošina gada vērtību, kas pirmajās trīs dienas ir samazināta par 1, ja šīs dienas ir piektdienas, sestdienas vai svētdienas akti.
• Šī gada vērtība ir iekļauta datumā funkcijā DATE. Šis datums parasti tiek veidots ar otro argumentu 1 par janvāri.
• Kā dienas vērtību pārsūtiet iepriekš aprakstīto REST funkciju uz funkciju DATE, atņemot vērtību 9. Tā rezultātā parasti tiek iegūta negatīva dienas vērtība. Formula parāda, ka datums, kas aprēķināts pēc DATE, parasti ir decembris.
• Tomēr gada pirmajās trīs dienās ir decembris pirms priekšpēdējā, ar nosacījumu, ka šīs trīs dienas ir piektdienas, sestdienas vai svētdienas.
• Jūs atņemat šo datumu, kas aprēķināts ar funkciju DATE, no datuma A slejā, dalot starpību ar 7 (septiņām nedēļas dienām). Jūs izmantojat visu šo aprēķinu SHORT funkcijā, lai iegūtu tikai veselus skaitļus.

Alternatīvi: izmantojiet funkcijas argumentu WOCHENSTART

Vēl viena iespēja darbam ar funkciju KALENDĀRA NEDĒĻA programmā Microsoft Excel un pareizu rezultātu sasniegšanai ir formulai pievienot funkcijas argumentu nedēļas sākums. Līdzīgi kā iepriekš parādītajai formulai, formulas izveide prasa pūles.

Funkcijas arguments WOCHENSTART var atšķirt divas dažādas kalendārās nedēļas aprēķināšanas sistēmas:

1. Sistēma 1 (Amerikāņu sistēma) - ziņo 1. nedēļu par nedēļu, kas ietver pirmo janvāri.
2. Sistēma 2 (Eiropas standarts saskaņā ar ISO 8601) - KW 1 ir nedēļa, kas ietver gada pirmo ceturtdienu.

Ja izmantojat funkcijas argumentu WOCHENSTART un kalendāra nedēļas funkciju, kā argumenti vienmēr jāievada šādi skaitļi:

Pirmdiena	2 vai 11
Otrdiena	12
Trešdiena	13
Ceturtdiena	14
Piektdiena	15
Sestdiena	16
Svētdiena	1 vai 17

6 soļos saistiet funkciju KALENDĀRA NEDĒĻA ar funkcijas argumentu WOCHENSTART

Lai saistītu kalendāra nedēļas funkciju ar funkcijas argumentu nedēļas sākums, rīkojieties šādi:

1. Ievadiet datumu savā Excel izklājlapā.

2. Ar peles kreiso pogu noklikšķiniet uz šūnas, kurā vēlaties iekļaut kalendāro nedēļu.

3. Pārslēdzieties uz cilni FORMULAS un ar peles kreiso pogu noklikšķiniet uz "Ievietot funkciju".

   

4. Meklēšanas laukā ievadiet KALENDĀRA NEDĒĻA.

   

5. Kad esat atradis funkciju KALENDĀRA NEDĒĻA, apstipriniet savu izvēli ar OK.

6. Tiklīdz atrodaties funkcijas ievades laukā, ievadiet formulas komponentus šādi:

   • Sērijas_numurs = šūna, kurā ir datums, piemēram, A1.
   • Skaitļa tips = skaitliskā vērtība nedēļas dienai saskaņā ar iepriekš minēto tabulu.
   

Kalendāra nedēļa tiek pareizi izvadīta saskaņā ar ISO 8601.

Kā aprēķināt jebkuras kalendāra nedēļas sākumu un beigas programmā Excel

Kalendāra nedēļām ir izšķiroša loma profesionālajā plānošanā. Tikšanās bieži tiek plānotas vai saskaņotas konkrētai kalendārajai nedēļai. Tādā gadījumā jums ir

1. Ziniet arī to, kurā kalendārajā nedēļā iekrīt noteikts datums
2. Uzziniet, kurā datumā sākas vai beidzas konkrēta kalendārā nedēļa.

Šī informācija jums palīdz, piemēram, plānojot ražošanas datumus rūpnieciskā vidē vai saskaņojot piegādes vai norēķinu datumus.

Izmantojot īpašu formulu, jūs varat viegli noteikt jebkuras kalendārās nedēļas sākumu un beigas. Formulai ir šāda struktūra:

= TEXT (DATE (gada numurs; 1; 7 * nedēļas 3)
WEEKDAY (DATE (gada numurs ;;); 3)); "DD.MM.YY")
& "-" & TEXT (DATE (gada numurs; 1; 7 * kalendārā nedēļa + 3-
WEEKDAY (DATE (gada numurs ;;); 3)); "DD.MM.YY")

Jūs nododat divus argumentus formulai: Ar argumentu Gada numurs definējiet gadu, kurā atrodas kalendārā nedēļa. Ar argumentu jūs nododat kalendārās nedēļas numuru Kalweek.

Formulas rezultāts ir šīs kalendārās nedēļas pirmdienas datums, kam seko defise un kalendārās nedēļas svētdienas datums.

Parādītajā darbgrāmatā šūnā D4 esošā formula nodrošina periodu "30.09.2013. - 10.06.13.":

= TEKSTS (DATUMS (C4; 1; 7 * B4-3-WEEKDAY)
(DATUMS (C4 ;;); 3)); "DD.MM.YY") & "-" & TEXT
(DATUMS (C4; 1; 7 * B4 + 3-WEEKDAY (DATE (C4 ;;); 3));
"DD.MM.YY")

Šis periods ir 40. kalendārā nedēļa no šūnas B4 2013. gadā no šūnas C4.

Formulas rezultāts izvada tekstu, kuru nevar izmantot, lai turpinātu aprēķināšanu. Lai noskaidrotu izvades laika periodu, rezultātu varat parādīt tā, lai tiktu izvadītas arī nedēļas dienas. Lai to izdarītu, mainiet skaitļa formātu divās formulas teksta funkcijās šādi:

= TEKSTS (DATUMS (C4; 1; 7 * B4-3-WEEKDAY)
(DATUMS (C4 ;;); 3)); “DDD DD.MM.
YY “) &“ - “& TEXT (DATE (C4; 1; 7 * B4 + 3-
WEEKDAY (DATE (C4 ;;); 3)); "DDDDD.MM.YY")

Pēc formulas nokopēšanas visās tabulas rindās datumi vienmēr tiek izvadīti ar nedēļas dienām.

Kā aprēķināt nedēļas programmā Excel un atrast pareizo datumu

Daudzos plānos nedēļām ir vienības loma, piemēram, kalendārajām nedēļām vai ražošanas nedēļām. Nedēļas var aprēķināt un parādīt arī programmā Excel, izmantojot viegli saprotamas veidlapas.

Nākamajā attēlā parādīts datuma vērtību saraksts A slejā. B slejā ir vērtība, kas norāda, cik nedēļas šī datuma vērtība ir jāatliek:

C slejā vēlaties izmantot formulu, kas aprēķina jauno datumu, pamatojoties uz datumu A slejā un nedēļu skaitu B slejā. Ja nedēļu skaits ir lielāks par nulli, datums tiek pārvietots uz nākotni, ja vērtība ir negatīva, tā tiek pārvietota uz pagātni.

Šo mērķi var sasniegt, reizinot nedēļu skaitu ar vērtību 7 un pievienojot to sākotnējam datumam. Izmantojot iepriekš aprakstīto tabulas struktūru, šūnas C2 formula izskatās šādi:

= A1 + 7 * B1

Formula ir piemērojama, jo Microsoft Excel datuma vērtības iekšēji saglabā kā skaitļus. Attālums no vienas dienas uz otru ir 1. Nākamajā attēlā parādīta formulas izmantošana tabulas paraugā:

Secinājums: Kalendāra nedēļas ir būtiskas profesionālai tikšanās vadībai

Tikšanās nosaka mūsu dzīvi gan profesionālajā, gan privātajā dzīvē. Plānojot tikšanās, kalendārās nedēļas kļūst svarīgākas, palielinoties tikšanās intensitātei, jo tās var izmantot, lai radītu lielāku skaidrību tikšanās plānošanā.

Microsoft Excel katram datumam var norādīt pareizo kalendāro nedēļu. Izšķirošais faktors šeit ir tas, ka, veidojot Excel formulas, lietotāji Eiropā ievēro Eiropas datumu sistēmu saskaņā ar ISO 8601. Pēc noklusējuma kalendārā nedēļa tiek aprēķināta pēc ASV sistēmas, kas noved pie novirzēm. ASV gada pirmā diena parasti tiek piešķirta 1. kalendārajai nedēļai. Turpretī Vācijā, Austrijā un Šveicē pirmā kalendārā nedēļa parasti tiek definēta kā nedēļa, kas, kā gada pirmā nedēļa, sastāv no vismaz četrām dienām. Praksē tas nozīmē, ka 1. nedēļa Eiropas sistēmā ir nedēļa, kas ietver gada pirmo ceturtdienu.

Lai pareizi aprēķinātu kalendāro nedēļu, programmā Excel varat izmantot gan funkciju CALENDAR WEEK, gan funkciju WEEKSTART. Ir iespējams arī iestatīt atsevišķas formulas un pielāgot tās tieši izklājlapai.