trucuri

În seria Sfaturi și trucuri în Excel, oferim răspunsuri la întrebările participanților legate de Excel, care nu sunt frecvente în programa cursurilor noastre. Am creat un mozaic de răspunsuri care te-ar putea ajuta să lucrezi cu Excel și tu ...
Aceasta este a cincea parte, în care ne ocupăm în principal de funcțiile timpului, cum să calculăm în Excel vârsta sau durata angajării și așa mai departe.

Ați întâmpinat deja necesitatea de a calcula vârsta unei persoane cu o acuratețe de ani, luni sau chiar cu o acuratețe a unei zile? Sau cu sarcina de a afla dacă un părinte ar trebui să aducă un certificat de vizită la școală deoarece copilul său a împlinit vârsta de 18 ani? Cum se calculează durata angajării în companie? Câte zile va dura până când nava va călători dintr-un port din Asia în Europa?

În Excel, putem face acest lucru ușor și dificil, foarte precis și aproximativ ... rapid și lent 🙂

Funcții de dată în Excel

Printre cele aproape cinci sute de funcții, Excel are câteva care vă ajută să calculați datele și orele.
În imagine puteți vedea în partea de sus o prezentare generală cu o descriere a celor de bază - ZI, LUNĂ, AN, WEEKNUM și ISOWEEKNUM.
Primele trei funcții returnează valoarea zilei, lunii sau anului de la dată. Rezultatul WEEKNUM este numărul de serie al săptămânii datei în celula de referință. ISOWEEKNUM returnează, de asemenea, numărul de secvență al săptămânii datei în celula de referință, dar spre deosebire de WEEKNUM, rezultatul este numărul săptămânii conform standardului internațional. În practică, însă, celelalte două funcții sunt utilizate mai mult.

Apropo - știați că numărul de săptămâni variază în lume?
Există țări care consideră că prima săptămână a anului este cea de la 1 ianuarie. În același timp, există țări care consideră că prima săptămână a anului este luni, marți, miercuri sau joi. Sau când sărbătoresc Anul Nou cu ei cândva în ianuarie ... Deci care este prima săptămână a anului?
Organizația Internațională pentru Standardizare a introdus ISO 8601 la sfârșitul anilor 1980. Prima săptămână a anului ISO este prima joi din anul gregorian, săptămâna 4 ianuarie.

Pentru ce este bine?
De exemplu, ... exportatorul notifică partenerului comercial străin că mărfurile vor fi gata pentru expediere de marți, în săptămâna 23. El comandă un camion, o navă, un avion pentru a transporta marfa la depozitul său. Si nimic. Camionul așteaptă, nu pot găsi transportul în depozit. Telefoane, înjurături, nervozitate. După o oră de căutare, informațiile sunt corecte, dar noul lucrător a folosit denumirea locală a săptămânii. Nu i-a trecut prin minte că o săptămână în străinătate ar putea fi marcată diferit (propria experiență a autorului cu exportatorul). Poate sa.

Cum se calculează vârsta în Excel

Să vedem cum să calculăm data în Excel pe exemple specifice.
Suntem interesați - mai ales de nevoile diverselor sarcini administrative la locul de muncă - cât de vârstă are o persoană (lucrător, copil, ...) față de actualul „azi”. Ne interesează cum să calculăm vârsta în Excel? Numărul de ani lucrați?
Nu este dificil dacă cunoaștem funcțiile corecte sau dacă știm cum funcționează Excel cu datele.

Să presupunem că oamenii pe care urmează să-i descoperim vârsta, i-am aranjat în tabel ca în imagine.

Metoda 1

Excel ne permite să aflăm diferența simplă dintre două date câte zile au trecut între cele două date. Prin urmare, am putea scrie data curentă astăzi în coloana alăturată de lângă data coloanei de naștere (etichetată acum incorect ca vârstă). Și vom scrie în următoarea coloană formula prin care calculăm diferența dintre două celule cu date în rândul de lângă. Așa o fac începătorii, chiar la începutul contării pe Excel.
Rezultatul este corect, dar ...

(Dacă o dată fără vorbire apare în coloană în locul numărului de zile, nu disperați. Problema este doar în format de celulă. Setați selecția în fereastra de sus din fila Acasă din panoul Număr la General.)

Rezultatul obținut are un dezavantaj - va fi valabil numai la data de 7.2.2020. Prin urmare, o „excelență” puțin mai avansată va lua în considerare o formulă cu o funcție care ar facilita calculul.

Pentru a nu fi legat de o singură dată „azi”, pe care am introdus-o în coloana D în metoda anterioară de calcul, vom folosi o altă funcție în calculul din formulă. Funcția dată AZI în Excel folosește întotdeauna data curentă din calendarul computerului. Deci nu trebuie să scriem deloc data curentă în tabel. Calculul va avea loc de fiecare dată când deschidem fișierul, la noua dată curentă „de astăzi”. Bine, totuși?

Funcția arată un pic ciudat atunci când scrieți, deoarece este fără argumente - nu are niciun argument între paranteze. Formula cu acesta arată astfel într-o celulă:

Nu scriem nimic în interiorul funcției, nici un spațiu. Numai paranteze stânga și dreapta.
AZI ne va arăta rezultatul în celulă sub forma datei curente.

În exemplul nostru, vom aprecia funcția, deoarece după ce ați folosit-o, soluția arată astfel.
În loc de dată, introduceți formula diferenței în prima celulă a coloanei D (D7):

și copiați-l în celulele de mai jos făcând dublu clic pe mânerul pătrat din colțul celulei.
(La fel ca în procedura anterioară, dacă în coloană apare o dată care nu vorbește în loc de numărul de zile, problema este doar în format de celulă. Setați formatul selectat în caseta de sus din fila Acasă în loc de Data la General. )

Așa am obținut numărul de zile din viața unui muncitor. Dacă vrem un număr de ani, trebuie să transformăm rezultatul în ani. Cum?
Deoarece fiecare al patrulea an este permeabil, împărțim numărul de zile de viață câștigate la 365,25. Obținem vârsta destul de precisă a tuturor angajaților.
Întreaga formulă din prima celulă arată astfel:

Faceți dublu clic pe mâner pentru al copia în întreaga coloană.
Diferența dintre două date în ani am aflat așa printr-o formulă cu funcția TODAY.
Se calculează diferența dintre două celule cu date.

Putem afișa rezultatul doar pentru ani întregi folosind funcția INT (întreg), care asigură afișarea doar a părții întregi. Ar arăta astfel:

Notă:
În cazul în care trebuie să calculăm vârsta pentru o anumită zi, de exemplu începând cu 31.12.2019, în loc de AZI vom scrie o dată specifică în formularul DATE (2019; 12; 31).

Metoda 2

Vom folosi o altă funcție de dată Excel pentru calcul - YEARFRAC. Funcția YEARFRAC are două argumente obligatorii (data de început și de sfârșit) și un argument opțional în funcție de operațiunea financiară relevantă (neutilizat în mod obișnuit).

Vom face calculul în coloana laterală, astfel încât să putem compara rezultatele. Prima celulă va arăta astfel:

Faceți dublu clic pe mânerul pătrat pentru al copia în restul coloanei.
Am găsit diferența dintre cele două date folosind funcția YEARFRAC, prin care calculăm diferența a două celule cu date.

Când comparăm cele două coloane Age, vedem o diferență. Vârsta calculată prin împărțirea și împărțirea la 365,25 nu este complet corectă. Rezultatul obținut de funcția YEARFRAC este corect, cu valori introduse corect (în cazuri excepționale de numărare a anilor bancari 30/360 cu o dată de începere de 29.2. Poate da rezultate incorecte).
Dacă ne interesează doar numărul de ani, ambele calcule sunt suficiente.

Dacă trebuie să calculăm diferența de date pentru o anumită zi, de exemplu începând cu 31.12.2019, chiar și atunci când folosim funcția YEARFRAC în loc de TODAY (), scriem o dată specifică sub forma DATE (2019; 12; 31 ) inauntru.

(Ultimul rând afișează formulele utilizate în celulele din celula evidențiată din coloana de deasupra lor.)

Cum se calculează durata unei relații de muncă

Calculăm durata relației de muncă în Excel, similar cu cazul anterior.
Uită-te la masă.
În loc de data nașterii, introducem sau copiem dintr-un alt tabel datele de angajare a angajaților. Procedura și logica pentru crearea unei formule sunt similare.

Efectuăm calculul utilizând funcția YEARFRAC din coloana G.
Prima celulă va arăta astfel:

Faceți dublu clic pe mânerul pătrat din colțul celulei pentru a copia formula în restul coloanei.

Dacă aveți nevoie doar de ani sau nu vă place numărul de zecimale, îl puteți ajusta folosind instrumentele săgeată din fila Acasă din panoul Număr (Format numeric). Adăugați și scădeți numărul de zecimale afișate. Sau putem folosi funcția INT menționată mai sus direct în formula după cum urmează:

În cazul în care trebuie să numărăm numărul de ani lucrați într-o anumită zi, de exemplu la 31.12.2019, în loc de TODAY () scriem o dată specifică în forma DATE (2019; 12; 31) în interiorul funcției, deci formula va arăta astfel:

Desigur, putem calcula și durata relației de muncă în primul mod descris mai sus - prin scăderea a două date și împărțirea diferenței la 365,25. Rezultatul va fi mai puțin precis de păr decât funcția YEARFRAC.

Pentru cunoscătorii din Excel

Calculul vârstei sau perioadei poate lua mai multe forme în funcție de cerințele pentru afișarea rezultatului.
Formula pentru afișarea simplă a numărului de ani (supraviețuiri, ore lucrate, ...) până în prezent poate fi după cum urmează:

unde celula C3 va fi data de la care numărăm anii, i. data nașterii, data începerii angajării, ...
Formula pentru o reprezentare mai exactă a calculului de ani, luni și zile poate arăta astfel:

Mai multe metode de calcul, inclusiv probleme cu utilizarea cuvintelor slovace flexionate an, ani, ani, ... chiar și cu versiunea în limba engleză pot fi văzute în imaginea de mai jos. Dacă cineva dorește să testeze aceste formule sau să le folosească în practică, puteți descărca fișierul de calcul tabelar Excel pe computer făcând clic pe butonul de la sfârșitul articolului.

Când calculez în această formulă, am folosit funcția de dată Excel mai puțin cunoscută DATEIF.
Puteți găsi descrierea mai detaliată din 2019 în ajutorul Excel (tasta F1).

Concluzie

După cum se poate observa din ultimul tabel, există mai multe soluții la calcularea datei.
Pentru utilizare practică, vă recomand - alegeți-l pe cel pe care îl înțelegeți cel mai bine. Astfel încât să îl puteți interpreta sau regla cu ușurință, după cum este necesar, în orice moment.

Aceleași principii și formule pot fi folosite în practică, de exemplu, pentru a determina dacă un părinte ar trebui să ofere angajatorului un certificat de vizită la școală (în formula folosim data nașterii copilului și AZI sau în loc de AZI o dată specifică folosind DATA pentru care stabilim obligația) și alte sarcini practice. Excel ne va ușura viața cu funcțiile sale functions

Doriți să aflați mai multe despre lucrul cu funcțiile Excel și timp? Vino la un curs Excel, învață și încercă exemple. După două zile în cursul Excel I sau Excel II, veți afla mai multe despre lucrul cu această foaie de calcul, veți afla comenzi rapide de la tastatură, modalități de a distinge tipurile de foi de calcul și când să le utilizați, veți învăța cum să creați formule, să utilizați funcții, ... există mult.
Veți ști cum să calculați data după Excel II, preluăm funcțiile de timp mai detaliat pe Excel III 🙂