| Lectia 2| lecția 3 | lecția 4 | lecția 5

Cred că știți deja din ultima lecție că datele și orele în Excel sunt stocate ca numere ordinale, al căror punct de plecare este considerat a fi un anumit 0 ianuarie 1900. Din fericire, în celule nu vedem aceste numere, ci datele și orele familiare nouă, care pot fi stocate într-o varietate de formate. În această lecție, veți învăța cum să introduceți datele și orele în Excel pentru a obține formatarea dorită.

Introducerea datelor și orelor în Excel

Datele și orele în Excel pot fi introduse ca număr ordinal sau fracțiune de zi, dar, după cum înțelegeți, acest lucru nu este foarte convenabil. În plus, cu această abordare, celula va trebui să aplice de fiecare dată un anumit format de număr.

Excel oferă mai multe formate pentru introducerea datelor temporare. Dacă aplicați acest format, Excel va converti automat data (sau ora) introdusă într-un număr ordinal (fracțiune de zi) și va aplica formatul celulei Datele(sau Ora) setat implicit.

Figura de mai jos prezintă un tabel cu opțiunile de introducere a datei și orei pe care Excel le acceptă. Coloana din stânga arată valorile care trebuie introduse în celulă, în timp ce coloana din dreapta arată rezultatul conversiei. Este de remarcat faptul că datele introduse fără a specifica anul sunt atribuite anului curent, și anume cel setat în setările dvs. sistem de operare.

Asta nu e tot opțiuni posibile pe care Excel îl acceptă. Dar chiar și aceste opțiuni vor fi suficiente pentru tine.

Unele dintre opțiunile de afișare a datei prezentate în coloana din dreapta pot diferi. Depinde de setările regionale și de formatul de afișare a datei și orei în setările sistemului de operare.

Lucrul cu celulele Microsoft Excel care conțin o dată sau o oră, le puteți aplica formatări diferite. De exemplu, într-o celulă puteți afișa doar ziua săptămânii sau numai luna și anul sau numai ora.

Puteți obține acces la toate formatele posibile din filă Număr căsuță de dialog Format de celule. Categorie data de iată formatele de date încorporate:

Pentru a aplica formatarea unei celule, trebuie doar să selectați formatul dorit in sectiune Tipși apăsați Bine. Se va aplica formatarea necesară. Dacă formatele de numere încorporate nu sunt suficiente pentru dvs., atunci puteți utiliza categoria Toate formatele. Aici puteți găsi și o mulțime de lucruri interesante.

Dacă niciuna dintre opțiuni nu vi se potrivește, atunci puteți crea un format de număr personalizat. Este destul de ușor de creat dacă utilizați formatele de numere încorporate ca șablon. Pentru a crea un format de număr personalizat, urmați acești pași:

După cum puteți vedea, totul este destul de simplu!

În această lecție, am învățat cum să personalizăm formatul de afișare a datelor și orelor în Microsoft Excel și am analizat, de asemenea, câteva opțiuni utile pentru introducerea lor pe o foaie de lucru. În următoarea lecție, vom vorbi despre

Tabelele Excel oferă posibilitatea de a lucra cu tipuri variate informații textuale și numerice. Procesarea datei este, de asemenea, disponibilă. În acest caz, poate fi necesar să izolați un anumit număr de valoarea generală, de exemplu, un an. Există funcții separate pentru aceasta: AN, LUNA, ZI și ZI SĂPTĂMNICĂ.

Exemple de utilizare a funcțiilor pentru a procesa datele în Excel

Tabelele Excel stochează datele, care sunt reprezentate ca o secvență de valori numerice. Începe la 1 ianuarie 1900. Această dată va corespunde cu numărul 1. În același timp, 1 ianuarie 2009 este inclusă în tabele ca și numărul 39813. Acesta este numărul de zile dintre cele două date indicate.

Funcția AN este utilizată în mod similar cu cele adiacente:

• LUNĂ;
• ZI;

Toate afișează valori numerice corespunzătoare calendarului gregorian. Chiar dacă în Foaie de calcul Excel calendarul Hijri a fost selectat pentru a afișa data introdusă, apoi la extragerea anului și a altor valori compuse prin funcții, aplicația va prezenta un număr care este echivalent în sistemul calendarului gregorian.

Pentru a utiliza funcția AN, introduceți următoarea formulă a funcției cu un singur argument într-o celulă:

YEAR (adresa celulei cu data în format numeric)

Argumentul funcției este necesar. Poate fi înlocuit cu „date_in_number_format”. În exemplele de mai jos, puteți vedea clar acest lucru. Este important de reținut că la afișarea datei ca text (alinierea automată la stânga a celulei), funcția AN nu va fi executată. Rezultatul acestuia va fi afișarea #VALOR. Prin urmare, datele formatate trebuie să fie numerice. Zilele, lunile și anul pot fi separate printr-un punct, bară oblică sau virgulă.

Luați în considerare un exemplu de lucru cu funcția AN în Excel. Dacă trebuie să obținem anul de la data inițială, funcția DREPT nu ne va ajuta, deoarece nu funcționează cu date, ci doar cu text și valori numerice. Pentru a separa anul, luna sau ziua de o dată completă, Excel oferă funcții pentru lucrul cu datele.

Exemplu: Există un tabel cu o listă de date și în fiecare dintre ele este necesar să se separe valoarea doar a anului.

Să introducem datele inițiale în Excel.

Pentru a rezolva problema, trebuie să introduceți formula în celulele coloanei B:

AN (adresa celulei, de la data căreia doriți să extrageți valoarea anului)

Ca urmare, extragem ani din fiecare dată.

Un exemplu similar al funcției LUNA în Excel:

Un exemplu de lucru cu funcțiile DAY și WEEKDAY. Funcția DAY ajunge să calculeze numărul oricărei zile dintr-o dată:

Funcția WEEKDAY returnează numărul zilei săptămânii (1-luni, 2-marți... etc) pentru orice dată:

Al doilea argument opțional al funcției WEEKDAY ar trebui să fie numărul 2 pentru formatul nostru de numărare a zilei săptămânii (de la luni-1 până duminică-7):

Dacă omiteți al doilea argument opțional, atunci va fi folosit formatul implicit (în engleză duminică-1 până sâmbătă-7).

Să creăm o formulă din combinații ale funcțiilor INDEX și WEEKDAY:

Să obținem o formă mai înțeleasă a implementării acestei funcții.



Exemple de utilizare practică a funcțiilor pentru lucrul cu date

Aceste funcții primitive sunt foarte utile pentru gruparea datelor după: ani, luni, zile ale săptămânii și anumite zile.

Să presupunem că avem un raport simplu de vânzări:

Trebuie să organizăm rapid datele pentru analiza vizuală fără a folosi tabele pivot. Pentru a face acest lucru, vom prezenta raportul într-un tabel în care puteți grupa convenabil și rapid datele în funcție de an, lună și zi a săptămânii:

Acum avem un instrument pentru a lucra cu acest raport de vânzări. Putem filtra și segmenta datele în funcție de criterii de timp specifice:

În plus, puteți crea o histogramă pentru a analiza cele mai bine vândute zile ale săptămânii, pentru a înțelege în ce zi a săptămânii se încadrează cel mai mare număr vânzări:

În această formă, este foarte convenabil să segmentezi rapoartele de vânzări pentru perioade lungi, medii și scurte de timp.

Trebuie remarcat imediat că, pentru a obține diferența dintre două date, niciuna dintre funcțiile descrise mai sus nu ne va ajuta. Pentru această sarcină, ar trebui să utilizați funcția RAZDAT special concepută:

Tipul de valori din celulele „dată” necesită o abordare specială la procesarea datelor. Prin urmare, ar trebui să utilizați funcțiile adecvate pentru acest tip în Excel.

Datele sunt adesea o parte importantă a analizei datelor. Adesea pui întrebări precum când a fost achiziționat produsul, cât timp va dura sarcina din proiect sau care este venitul mediu pentru trimestrul fiscal? Introducerea corectă a datelor este o condiție prealabilă pentru rezultate precise. Cu toate acestea, pentru intelegere corecta aceste rezultate, trebuie să formatați datele pentru a le face mai ușor de înțeles.

Important: Deoarece regulile care guvernează modul în care datele sunt interpretate de către orice program de calculator sunt complexe, ar trebui să fiți cât mai specific posibil cu fiecare intrare. Acest lucru va asigura că calculele datei sunt cât mai precise posibil.

Despre calcule și formate de date

Excel stochează datele ca numere consecutive, care sunt numite valori consecutive. De exemplu, în Excel pentru Windows, 1 ianuarie 1900 este numărul de serie 1, iar 1 ianuarie 2008 este numărul de serie 39448, deoarece este la 39.448 de zile după 1 ianuarie 1900.

Excel stochează timpii ca zecimale, deoarece timpul este tratat ca parte a unei zile. Numar decimal este o valoare cuprinsă între 0 (zero) și 0,99999999 reprezentând ora de la 0:00:00 (12:00:00) la 23:59:59 (23:59:59).

Deoarece valorile datei și orei sunt valori, ele pot fi adăugate, scăzute și incluse în alte calcule. Puteți vizualiza o dată ca valoare ordinală și o oră ca zecimală schimbând formatul celulei care conține data sau ora la formatul general.

Despre cele două sisteme de date

Excel pentru Mac și Excel pentru Windows acceptă sistemele de dată 1900 și 1904. Sistemul de dată implicit pentru Excel pentru Windows este 1900; iar sistemul de date implicit pentru Excel pentru Mac este 1904.

Excel pentru Windows a fost inițial bazat pe sistemul de date 1900, deoarece a îmbunătățit compatibilitatea cu alte programe de calcul dezvoltate pentru sistemul de operare MS-DOS și Microsoft Windows, și astfel a devenit sistemul de date standard. Excel pentru Mac s-a bazat inițial pe sistemul de date din 1904, deoarece a îmbunătățit compatibilitatea cu primele computere Macintosh care nu acceptau date anterioare datei de 2 ianuarie 1904, așa că a devenit sistemul de dată implicit.

Tabelul de mai jos arată prima dată și data ultimei perioade pentru fiecare sistem de date și valoarea secvențială asociată fiecărei date.

Deoarece cele două sisteme de date utilizează zile de început diferite, aceeași dată este reprezentată de numere ordinale diferite în fiecare sistem de date. De exemplu, 5 iulie 2007 poate avea două valori consecutive diferite, în funcție de sistemul de date utilizat.

Diferența dintre cele două sisteme de date este de 1.462 de zile; Aceasta înseamnă că numărul de serie al unei date în sistemul de date 1900 este întotdeauna cu 1462 de zile mai mic decât valoarea consecutivă a aceleiași date în sistemul de date 1904. În schimb, numărul de serie al unei date în sistemul de date 1904 este întotdeauna 1462 de zile mai mică decât valoarea de serie a aceleiași date de date în sistemul de date 1900. 1462 de zile este egal cu 4 ani și o zi (inclusiv o zi bisectă).

Modificați modul în care este interpretat un an cu două cifre

Important: Pentru ca valorile anului să fie interpretate corect, introduceți patru cifre (de exemplu, 2001, nu 01). Dacă introduceți ani de patru cifre, Excel nu va interpreta acel secol.

Dacă introduceți o dată a anului din două cifre într-o celulă text sau într-un argument text într-o funcție, cum ar fi „= year” („1/1/31”), Excel interpretează anul după cum urmează:

00 – 29 interpretat ca ani din 2000 până în 2029. De exemplu, la introducerea unei date 5/28/19 Excel crede că data ar putea fi 28 mai 2019.

de la 30 la 99 interpretat ca ani din 1930 până în 1999. De exemplu, la introducerea unei date 5/28/98 Excel crede că data ar putea fi 28 mai 1998.

În Microsoft Windows, puteți modifica modul în care anul din două cifre este interpretat pentru toată lumea programe instalate Windows.

Windows 10

Panou de controlși apoi selectați Panou de control.

In sectiune ore, limbă și regiune faceți clic pe .

Faceți clic pe pictograma Limbă și standardele regionale.

În caseta de dialog regiune apasa butonul Opțiuni suplimentare.

Deschide o filă data de .

În câmp

Faceți clic pe butonul Bine.

Windows 8

Căutare Căutare), introduceți în câmpul de căutare elementul panoului de control Panou de control.

In sectiune ore, limbă și regiune faceți clic pe .

În caseta de dialog regiune apasa butonul Opțiuni suplimentare.

Deschide o filă data de .

În câmp după introducerea a două cifre ale anului, interpretați-l ca fiind anul între rame prin modificarea limitei superioare a pleoapei.

Pe măsură ce limita superioară se modifică, anul se schimbă automat la limita inferioară.

Faceți clic pe butonul Bine.

Windows 7

Faceți clic pe butonul startși selectați elementul Panou de control.

Selectați un articol.

În caseta de dialog regiune apasa butonul Opțiuni suplimentare.

Deschide o filă data de .

În câmp după introducerea a două cifre ale anului, interpretați-l ca fiind anul între rame prin modificarea limitei superioare a pleoapei.

Pe măsură ce limita superioară se modifică, anul se schimbă automat la limita inferioară.

Faceți clic pe butonul Bine.

Schimbați formatul implicit de dată pentru a afișa anul ca patru cifre

În mod implicit, când introduceți datele într-un registru de lucru, datele sunt formatate pentru a afișa două cifre pentru anul. Când modificați formatul implicit de dată într-un alt format utilizând această procedură, afișarea datelor introduse anterior în registrul de lucru va fi schimbată în noul format până când se va formata în " Format de celule" "(pe fila Acasăîntr-un grup număr apasa butonul caseta de dialog apel).

Windows 10

În caseta de căutare din bara de activități, tastați Panou de controlși apoi selectați Panou de control.

In sectiune ore, limbă și regiune clic modificați formatele de dată, oră și numere .

Faceți clic pe pictograma Limbă și standardele regionale.

În caseta de dialog regiune apasa butonul Opțiuni suplimentare.

Deschide o filă data de .

Pe lista scurta Formatul datei

Faceți clic pe butonul Bine.

Windows 8

Glisați dinspre marginea dreaptă a ecranului, apăsați butonul Căutare(sau, dacă utilizați un mouse, mutați indicatorul mouse-ului în colțul din dreapta sus al ecranului, mutați-l în jos și faceți clic pe butonul Căutare), introduceți în câmpul de căutare elementul panoului de control, apoi apăsați sau Selectați Panou de control.

In sectiune ore, limbă și regiune clic modificați formatele de dată, oră sau numere.

În caseta de dialog regiune apasa butonul Opțiuni suplimentare.

Deschide o filă data de .

Pe lista scurta Formatul datei alegeți un format care utilizează patru cifre pentru anul (aaaa).

Faceți clic pe butonul Bine.

Windows 7

Faceți clic pe butonul startși selectați elementul Panou de control.

Selectați un articol limbă și standarde regionale.

În caseta de dialog regiune apasa butonul Opțiuni suplimentare.

Deschide o filă data de .

Pe lista scurta Formatul datei alegeți un format care utilizează patru cifre pentru anul (aaaa).

Faceți clic pe butonul Bine.

Schimbați sistemul de date în Excel

Sistemul de date se schimbă automat când deschideți un document creat pe o altă platformă. De exemplu, dacă lucrați în Excel și ați deschis un document creat în Excel pentru Mac, „ sistem de date 1904" instalat automat.

Puteți schimba sistemul de date făcând următoarele:

Selectați Fişier > Parametrii > În plus.

In sectiune la calculul acestei cărţi selectați cartea dorită, apoi bifați sau debifați utilizați sistemul de dată 1904 .

Problemă: problemele de dată apar între registrele de lucru care utilizează sisteme de date diferite

Este posibil să întâmpinați probleme când copiați și lipiți date sau când creați legături externe între cărți bazate pe două sisteme diferite datele Datele pot fi afișate cu până la patru ani mai devreme sau mai târziu decât data estimată. Aceste probleme pot apărea dacă utilizați Excel pentru Windows, Excel pentru Mac sau ambele.

De exemplu, dacă copiați data 5 iulie 2007 dintr-un registru de lucru care utilizează sistemul de date 1900 și apoi o inserați într-un registru de lucru care utilizează sistemul de date 1904, data apare ca 5 iulie 2011, adică 1.462 de zile mai târziu. De asemenea, dacă copiați data 5 iulie 2007 dintr-un registru de lucru care utilizează sistemul de date 1904 și apoi o inserați într-un registru de lucru care utilizează sistemul de date 1900, data va consta în 4 iulie 2003, care este cu 1.462 de zile mai devreme . Informatii generale poate fi găsit în Date Systems în Excel.

Corectarea erorilor la copiere și lipire

Pentru a seta data la patru ani și o zi mai târziu, adăugați 1462. Exemplu

Sheet1!$A$1+1462

Pentru a seta data la patru ani sau mai devreme, scădeți din ea 1.462. Exemplu

Sheet1!$A$1-1462

informatii suplimentare

Puteți oricând să puneți o întrebare comunității Excel Tech, să cereți ajutor în comunitatea Răspunsuri și, de asemenea, să sugerați optiune noua sau o îmbunătățire a site-ului web Excel User Voice

Dacă lucrați cu date bazate pe timp, poate fi necesar să introduceți un număr de date în tabel. De exemplu, într-o aplicație de urmărire a vânzărilor, puteți introduce o serie de date pentru un trimestru, asigurându-vă că săriți peste datele care cad în weekend, desigur.

Folosind completarea automată

Cel mai metoda eficienta introduceți mai multe date, care nu necesită nicio formulă - utilizați funcția de completare automată. Introduceți prima dată, apoi trageți mânerul de umplere a celulei în timp ce apăsați butonul din dreapta al mouse-ului. Eliberați butonul mouse-ului și selectați comanda meniului contextual Completați pe lună(Fig. 108.1).

În unele situații, trebuie să introduceți două date înainte de a utiliza funcția de completare automată. De exemplu, dacă trebuie să introduceți date care sunt luni consecutive, trebuie să introduceți primele două date din serie. Apoi selectați ambele date, faceți clic dreapta și selectați Completare după zile din meniul contextual.

Folosind formule

O altă modalitate de a introduce o serie de date se bazează pe utilizarea formulelor. Avantajul formulei (mai degrabă decât funcția de autocompletare) este că poți schimba prima dată și toate celelalte vor fi actualizate automat. Introduceți o dată de începere într-o celulă și apoi utilizați formulele (copierând coloana) pentru a crea date suplimentare. În următoarele exemple, să presupunem că introduceți prima dată din serie în celula A1 și formula în celula A2. Apoi puteți copia această formulă în coloană de câte ori este necesar.

Pentru a genera o serie de date separate de șapte zile, utilizați această formulă: =A1+7 .

Pentru a crea o serie de date separate de o lună, utilizați următoarea formulă: =DATA(YEAR(A1),MONTH(A1)+1,DAY(A1))

Pentru a crea o serie de date separate de un an, utilizați această formulă: =DATA(YEAR(A1)+1;MONTH(A1);DAY(A1)

Pentru a crea o serie care include numai zilele lucrătoare (toate zilele, cu excepția sâmbătei și duminicii), utilizați următoarea formulă. Se presupune că data din celula A1 nu este o sărbătoare: =IF(WEEKDAY(A1)=6,A1+3,A1+1) . Această formulă revine număr de serie date, așa că trebuie să formatați celulele pentru a afișa datele.

Cel mai simplu și drumul rapid introduceți data sau ora curentă în celulă - aceasta este pentru a apăsa combinația de taste rapide CTRL + ";" (data curentă) și CTRL+SHIFT+";" (ora curentă).

Este mult mai eficient să folosiți funcția TODAY(). La urma urmei, nu numai că setează, ci și actualizează automat valoarea celulei în fiecare zi, fără intervenția utilizatorului.

Cum se pune data curentă în excel

Pentru a introduce data curentă în Excel, utilizați funcția TODAY(). Pentru a face acest lucru, selectați instrumentul „Formule” - „Data și oră” - „AZI”. Această funcție nu are argumente, așa că puteți pur și simplu să tastați în celulă: „=TODAY()” și să apăsați ENTER.

Data curentă în celulă:

Dacă este necesar ca valoarea din celulă să fie actualizată automat nu numai data curenta, dar atunci este mai bine să folosiți funcția „=DATE()” pentru oră.

Data și ora curente din celulă.



Cum să setați data curentă în Excel pe antete și subsol

Inserarea datei curente în Excel este implementată în mai multe moduri:

1. Prin setarea parametrilor antet și subsol. Avantajul acestei metode este că data și ora curente sunt plasate pe toate paginile simultan.
2. Folosind funcția TODAY().
3. Folosind combinația de taste rapide CTRL+; – pentru a seta data curentă și CTRL+SHIFT+; – pentru a seta ora curentă. Dezavantajul este aceasta metoda valoarea celulei nu va fi actualizată automat la valorile curente atunci când documentul este deschis. Dar, în unele cazuri, lipsa datelor este un avantaj.
4. Utilizarea macrocomenzilor VBA folosind funcții din codul programului: Date();Time();Now() .

Anteturile și subsolurile vă permit să setați data și ora curente în partea de sus sau de jos a paginilor documentului care va fi scos la imprimantă. În plus, antetul și subsolul ne permit să numerotăm toate paginile documentului.

Pentru a face data curentă în Excel și paginarea folosind antete și subsoluri, procedați astfel:

Anteturile și subsolurile ne permit să facem mai mult decât să stabilim datele și numerele paginilor. De asemenea, puteți adăuga un loc pentru semnătura persoanei responsabile de raport. De exemplu, să edităm partea din stânga jos a paginii în zona antetului și subsolului.