„Excel“: Apskaičiuokite savaites - taip tai veikia naudojant formulę

Raskite tinkamą funkciją

„Microsoft Excel“ yra funkcija KALENDORIŲ SAVAITĖ, su kuria galite rodyti konkrečios datos kalendorinę savaitę. Pavyzdžiui, galite automatiškai parodyti, kurią savaitę vyksta susitikimas. „Excel“ lentelės funkcija KALENDORIŲ SAVAITĖ taip pat turi savo spąstų. Taip yra todėl, kad Vokietijoje, Austrijoje ir Šveicarijoje kalendorinė savaitė yra apibrėžta kitaip nei kaimyninėse Europos šalyse ar JAV. Šiame straipsnyje parodyta, kaip vis tiek galite apskaičiuoti teisingą kalendorinę savaitę ir skirtumus tarp kalendorinės savaitės DACH regione ir kitose šalyse.

„Excel“ funkcija KALENDORIŲ SAVAITĖ

Funkcija CALENDAR WEEK taip pat apskaičiuoja kalendorines savaites Europos „Excel“ versijose, naudojant Amerikos standartą. Pirmoji metų diena paprastai priskiriama 1 kalendorinei savaitei.

Kalendorinės savaitės skaičiavimo sintaksė yra tokia:

= KALANDO SAVAITĖ (DATA)

Kalendorinė savaitė Vokietijoje, Austrijoje ir Šveicarijoje yra apibrėžta skirtingai. Iš esmės tai savaitė, kurią, kaip ir pirmąją metų savaitę, sudaro mažiausiai keturios dienos. Jei sausio pirmoji patenka į penktadienį, šeštadienį ar sekmadienį, lentelės funkcija KALENDORIUS SAVAITĖ pateikia neteisingą rezultatą Europai.

Teisingai nustatykite kalendoriaus savaites „Excel“ - tai veikia

Norint parodyti teisingą Vokietijos, Austrijos ar Šveicarijos kalendorinę savaitę, jums reikia formulės. Pavyzdžiui, C4 langelyje tai turi būti taip:

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

Formulė sudėtinga ir plati. Norėdami paaiškinti, kaip tai veikia, atskirai atsižvelgiame į atskirus formulės komponentus:

• Funkcija REST (A4-2; 7) formulėje pasirodo kelis kartus. Ši funkcija padalija datą iš 7 ir grąžina gautą likutį. Ši funkcija grąžina pirmadienio reikšmę 0, antradienio - 1, sekmadienio - 6.
• Formulėje skaičius 2 atimamas iš datos vertės. Priežastis ta, kad bazinė data 1 1/1/1900 buvo sekmadienis. Padalintas iš 7 rezultatų ir likęs 1. Dėl šios priežasties pirmadienis bus išspręstas 2. Norėdami ištaisyti dienų skaičių iki 0 sekmadieniui, 1 - pirmadieniui ir pan., Skaičius 2 atimamas iš datos.
• Remiantis šiuo darbo dienų skaičiavimu, dalyje (METAI (A4-REST (A4-2; 7) +3)) pateikiama metinė vertė, sumažinta 1 pirmąsias tris metų dienas, jei šios dienos yra penktadieniai, šeštadieniai arba sekmadieniai aktų.
• Ši metinė vertė įtraukiama į datos funkciją DATE. Ši data paprastai formuojama su antruoju sausio mėnesio argumentu 1.
• Kaip dienos vertę, perkelkite aukščiau aprašytą REST funkciją į DATE funkciją, atėmus vertę 9. Paprastai gaunama neigiama dienos vertė. Formulė rodo, kad data, apskaičiuota pagal DATE, paprastai yra gruodžio mėn.
• Tačiau pirmąsias tris metų dienas yra gruodžio mėnuo priešpaskutinę, su sąlyga, kad šios trys dienos yra penktadieniai, šeštadieniai ar sekmadieniai.
• Šią datą, apskaičiuotą pagal funkciją DATE, atimate iš A stulpelyje esančios datos, padaliję skirtumą iš 7 (7 savaitės dienas) Jūs naudojate visą šį skaičiavimą SHORT funkcijoje, kad gautumėte tik sveiko skaičiaus reikšmes.

Arba: naudokite funkcijos argumentą WOCHENSTART

Dar viena galimybė dirbti su funkcija KALENDORIUS SAVAITĖ „Microsoft Excel“ ir pasiekti teisingų rezultatų - prie formulės pridėti funkcijos argumentų savaitės pradžią. Panašiai kaip aukščiau pateikta formulė, formulės sukūrimas reikalauja pastangų.

Funkcijos argumentas WOCHENSTART gali atskirti dvi skirtingas kalendorinės savaitės skaičiavimo sistemas:

1. 1 sistema (Amerikos sistema) - 1 savaitę nurodo kaip savaitę, į kurią įeina sausio pirmoji.
2. 2 sistema (Europos standartas pagal ISO 8601) - KW 1 yra savaitė, į kurią įeina pirmasis metų ketvirtadienis.

Jei naudojate funkcijos argumentą WOCHENSTART ir kalendorinės savaitės funkciją, kaip argumentus visada turite įvesti šiuos skaičius:

Pirmadienis	2 arba 11
Antradienį	12
Trečiadienį	13
Ketvirtadienis	14
Penktadienis	15
Šeštadienis	16
Sekmadienis	1 arba 17

6 veiksmais susiekite funkciją KALENDORIŲ SAVAITĖ su funkcijos argumentu WOCHENSTART

Norėdami susieti kalendorinės savaitės funkciją su funkcijos argumento savaitės pradžia, atlikite šiuos veiksmus:

1. Įveskite datą „Excel“ skaičiuoklėje.

2. Kairiuoju pelės mygtuku spustelėkite langelį, į kurį norite įtraukti kalendorinę savaitę.

3. Eikite į skirtuką FORMULAS ir kairiuoju pelės mygtuku spustelėkite „Įterpti funkciją“.

   

4. Paieškos laukelyje įveskite KALENDO SAVAITĘ.

   

5. Suradę funkciją KALENDORIUS SAVAITĖ patvirtinkite savo pasirinkimą OK.

6. Kai būsite funkcijos įvesties lauke, įveskite formulės komponentus taip:

   • Serijos_numeris = langelis, kuriame yra data, pavyzdžiui, A1.
   • Skaičiaus tipas = skaitinė savaitės dienos vertė pagal aukščiau pateiktą lentelę.
   

Kalendorinė savaitė teisingai išvedama pagal ISO 8601.

Kaip apskaičiuoti bet kurios kalendorinės savaitės pradžią ir pabaigą „Excel“

Kalendorinės savaitės vaidina svarbų vaidmenį planuojant profesionalų darbą. Susitikimai dažnai planuojami arba susitariami konkrečiai kalendorinei savaitei. Tokiu atveju jūs turite

1. Taip pat žinokite, kuri kalendorinė savaitė patenka į tam tikrą datą
2. Sužinokite, kokia data prasideda ar baigiasi konkreti kalendorinė savaitė.

Ši informacija jums padeda, pavyzdžiui, planuojant gamybos datas pramoninėje aplinkoje arba derinant pristatymo ar atsiskaitymo datas.

Naudodami specialią formulę galite lengvai nustatyti bet kurios kalendorinės savaitės pradžią ir pabaigą. Formulė turi tokią struktūrą:

= TEXT (DATE (metų numeris; 1; 7 * savaitės-3-
WEEKDAY (DATE (metų numeris ;;); 3)); "DD.MM.YY")
& "-" & TEXT (DATE (metų numeris; 1; 7 * kalendorinė savaitė + 3-
WEEKDAY (DATE (metų numeris ;;); 3)); "DD.MM.YY")

Jūs pateikiate du argumentus į formulę: Su argumentu Metų skaičius nustatyti metus, kuriais yra kalendorinė savaitė. Su argumentu perduodate kalendorinės savaitės skaičių Kalweek.

Formulės rezultatas yra šios kalendorinės savaitės pirmadienio data, po jos - brūkšnelis ir kalendorinės savaitės sekmadienio data.

Parodytoje darbaknygėje ši formulė D4 langelyje pateikia laikotarpį „09/30/13 - 10/6/13“:

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

Šis laikotarpis yra 40 kalendorinė savaitė iš B4 langelio 2013 m. Iš C4 langelio.

Formulės rezultatas pateikia tekstą, kurio negalite naudoti toliau skaičiuoti. Norėdami paaiškinti išvesties laikotarpį, rezultatą galite parodyti taip, kad būtų rodomos ir savaitės dienos. Norėdami tai padaryti, pakeiskite skaičių formulę dviejose formulės TEXT funkcijose taip:

= TEKSTAS (DATA (C4; 1; 7 * B4-3-WEEKDAY)
(DATA (C4 ;;); 3)); „DDD DD.MM.
YY “) &“ - “& TEXT (DATA (C4; 1; 7 * B4 + 3-
SAVAITGALIS (DATA (C4 ;;); 3)); "DDDDD.MM.YY")

Nukopijavus formulę į visas lentelės eilutes, datos visada pateikiamos kartu su savaitės dienomis.

Kaip apskaičiuoti savaites „Excel“ ir rasti tinkamą datą

Daugelyje planų savaitės atlieka vieneto vaidmenį, pavyzdžiui, kalendorinės savaitės ar gamybos savaitės. Savaites taip pat galima apskaičiuoti ir rodyti „Excel“ naudojant lengvai suprantamas formas.

Šis paveikslėlis rodo datos verčių sąrašą A stulpelyje. B stulpelyje yra reikšmė, nurodanti, kiek savaičių ši datos vertė turėtų būti atidėta:

C stulpelyje norite naudoti formulę, kuri apskaičiuoja naują datą pagal datą A stulpelyje ir savaičių skaičių B stulpelyje. Jei savaičių skaičius yra didesnis už nulį, data perkeliama į ateitį, o jei neigiama - perkeliama į praeitį.

Šį tikslą galite pasiekti padauginę savaičių skaičių iš 7 ir pridėję prie pradinės datos. Naudojant aukščiau aprašytą lentelės struktūrą, ląstelės C2 formulė atrodo taip:

= A1 + 7 * B1

Formulė yra tinkama, nes „Microsoft Excel“ datos reikšmes saugo viduje kaip skaičius. Atstumas nuo vienos dienos iki kitos yra 1. Toliau pateiktame paveikslėlyje parodyta formulės pavyzdžio lentelės pavyzdys:

Išvada: Kalendorinės savaitės yra labai svarbios profesionaliam susitikimų valdymui

Paskyrimai lemia mūsų gyvenimą tiek profesiniame, tiek privačiame gyvenime. Planuojant susitikimus, didėjant susitikimų intensyvumui, svarbesnės tampa kalendorinės savaitės, nes jos gali būti panaudotos siekiant daugiau aiškumo planuojant susitikimus.

„Microsoft Excel“ gali nurodyti teisingą kalendorinę savaitę kiekvienai datai. Lemiamas veiksnys yra tas, kad vartotojai Europoje, kurdami „Excel“ formules, laikosi Europos datų sistemos pagal ISO 8601. Pagal numatytuosius nustatymus kalendorinė savaitė apskaičiuojama pagal JAV sistemą, todėl nukrypimai atsiranda. JAV pirmoji metų diena paprastai priskiriama 1 kalendorinei savaitei. Priešingai, Vokietijoje, Austrijoje ir Šveicarijoje pirmoji kalendorinė savaitė paprastai apibrėžiama kaip savaitė, kurią, kaip pirmąją metų savaitę, sudaro mažiausiai keturios dienos. Praktiškai tai reiškia, kad 1 savaitė Europos sistemoje yra savaitė, į kurią įeina pirmasis metų ketvirtadienis.

Norėdami teisingai apskaičiuoti kalendorinę savaitę, „Excel“ galite naudoti funkciją CALENDAR WEEK ir WEEKSTART. Taip pat galima nustatyti atskiras formules ir pritaikyti jas konkrečiai jūsų skaičiuoklei.