Fiecare linie este o tranzacție separată. Trebuie să faceți un raport pentru luna. Câte produse au fost vândute în fiecare lună. Există diferite moduri de a rezolva această problemă, dar să presupunem că sarcina noastră este să extragem luna de la dată, astfel încât mai târziu să fie ușor să rezumam datele despre ele.
Metoda 1. Obțineți luna de la data folosind funcția LUNA din Excel
Scriem formula:
LUNA (A2)
Întindem formula și obținem luna de la dată sub forma unui număr. 5 este luna mai, 8 este august și așa mai departe.
Există funcții similare: AN, LUNA, SĂPTĂMÂNĂ, ZI, ORA, MINUTE, SECUNDE, care funcționează într-un mod similar. De exemplu, dacă în exemplul nostru scriem =YEAR(A2), atunci rezultatul va fi 2013 și așa mai departe
Uneori trebuie să obțineți luna de la o dată în format text: „Ianuarie, februarie, martie...” în acest caz vom folosi o altă funcție.
Metoda 2. Obțineți luna de la data folosind funcția TEXT în Excel
Sintaxa va fi după cum urmează
TEXT(valoare, format)
Valoarea este o referință la o celulă cu o dată
Format - pentru a obține luni trebuie să utilizați majusculă„M”. Mai mult, formatul de afișare va depinde de cantitatea acesteia (Prima literă a lunii, Numele complet al lunii, nume scurt, ca o cifră dublă și o singură cifră)
Acest lucru poate fi văzut clar în captură de ecran.
Să creăm secvențe de date și ore de diferite tipuri:01.01.09, 01.02.09, 01.03.09, ..., Ian, Apr, Iul, ..., Luni, Mar, Mier, ..., primul trimestru, al doilea trimestru,..., 09:00, 10:00, 11:00, ... etc.
Deoarece fiecare valoare de dată corespunde unui anumit număr (vezi articolul), atunci abordările de generare a secvențelor prezentate în articol sunt aplicabile și pentru date. Cu toate acestea, există și unele particularități.
Urmare 01.01.09, 01.02.09, 01.03.09 (primele zile ale lunilor) pot fi formate prin formula =DATEMON(B2,ROW(A1)), într-o celulă B2 data trebuie să fie primul element al secvenței ( 01.01.09 ).
Aceeași secvență poate fi formată folosind butonul din dreapta al mouse-ului. Lăsați să intre în celulă A2 valoarea introdusă 01.01.09 . Selectați o celulă A2 . Țineți apăsat butonul din dreapta al mouse-ului și copiați valoarea din A2 în celulele de mai jos. După ce eliberăm butonul drept al mouse-ului, meniul contextual, în care trebuie să selectați elementul Completați pe lună.
Prin modificarea formatului celulelor care conțin secvența 01.01.09, 01.02.09, 01.03.09, pe MMM(vezi articolul) obținem secvența ian, februarie, martie, ...
Aceeași secvență poate fi introdusă folosind lista de completare automată Buton Office/Opțiuni Excel/Opțiuni de bază/Excel de bază/Editare liste(introduce ian, apoi Marker de umplere copiați în jos).
Celulele nu vor conține date, ca în cazul precedent, ci valori de text.
În mod similar, puteți forma o secvență de zile ale săptămânii Luni, Marți, Miercuri,...
Secvența de sferturi 1 mp, 2 mp,... poate fi format folosind ideile din articol.
Folosind instrumentul, puteți crea secvențe de numai zile lucrătoare. Și, de asemenea, în incremente lunare și anuale.
Secvența primelor luni ale trimestrului Ianuarie, Apr, Iulie, Octombrie, Ianuarie,... poate fi creat prin introducerea primelor două elemente ale secvenței în două celule ( ian, apr), apoi (după ce le-au selectat anterior) copiați marker de umplere. Celulele vor conține valori text. Pentru a face celulele să conțină date, utilizați formula =DATEMON($G$16,(ROW(A2)-ROW($A$1))*3) Se presupune că secvența începe la celulă G16 , formula trebuie introdusă în celulă G17 (vezi exemplu de fișier).
Secvență de timp 09:00, 10:00, 11:00, ... poate fi format folosind . Lăsați să intre în celulă A2 valoarea introdusă 09 :00 . Selectați o celulă A2 . Hai să copiem Marker de umplere, valoare de la A2 în celulele de mai jos. Se va forma secvența.
Dacă trebuie să creați o secvență de timp în trepte de 15 minute ( 09:00, 09:15, 09:30, ... ), apoi puteți folosi formula =B15+1/24/60*15 (presupunând că secvența începe cu celula B15 , formula trebuie introdusă B16 ). Formula va returna rezultatul în format de dată.
O altă formulă =TEXT(B15+1/24/60*15,"hh:mm") va returna rezultatul în format text.
Tabelele Excel oferă posibilitatea de a lucra cu diferite tipuri de text și informații numerice. Procesarea datei este, de asemenea, disponibilă. În acest caz, poate fi necesară izolarea unui anumit număr, de exemplu, un an, din valoarea totală. Există funcții separate pentru aceasta: AN, LUNA, ZI și ZI SĂPTĂMNICĂ.
Exemple de utilizare a funcțiilor pentru procesarea datelor în Excel
Tabelele Excel stochează datele care sunt reprezentate ca o secvență valori numerice. Începe la 1 ianuarie 1900. Această dată va corespunde cu numărul 1. Mai mult, 1 ianuarie 2009 este inclusă în tabele ca și numărul 39813. Acesta este exact numărul de zile dintre cele două date desemnate.
Funcția AN este utilizată în mod similar cu cele aferente:
- LUNĂ;
- ZI;
Toate afișează valori numerice conform calendarului gregorian. Chiar dacă în Foaie de calcul Excel Pentru a afișa data introdusă, a fost selectat calendarul Hijri, apoi la izolarea anului și a altor valori ale componentelor folosind funcții, aplicația va prezenta un număr care este echivalent în sistemul cronologic gregorian.
Pentru a utiliza funcția AN, trebuie să introduceți următoarea formulă a funcției cu un argument într-o celulă:
AN (adresa celulei cu data în format numeric)
Argumentul funcției este necesar. Poate fi înlocuit cu „date_in_numeric_format”. În exemplele de mai jos, puteți vedea clar acest lucru. Este important de reținut că la afișarea datei ca text (orientare automată spre marginea stângă a celulei), funcția AN nu va fi executată. Rezultatul acestuia va fi afișarea #VALOR. Prin urmare, datele formatate trebuie prezentate sub formă numerică. Zilele, lunile și anii pot fi despărțiți de un punct, bară oblică sau virgulă.
Să ne uităm la un exemplu de lucru cu funcția AN în Excel. Dacă trebuie să obținem anul de la data sursă, 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 data 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 sursă în Excel.
Pentru a rezolva problema, trebuie să introduceți formula în celulele coloanei B:
AN (adresa celulei de la data căreia trebuie să extrageți valoarea anului)
Ca urmare, extragem anii 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 poate calcula ziua 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ă:
În al doilea argument opțional al funcției WEEKDAY, ar trebui să specificați 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 utilizat 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ă de implementare a acestei funcții.
Exemple de aplicare practică a funcțiilor pentru lucrul cu date
Aceste funcții primitive sunt foarte utile în 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, vă prezentăm 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 anumite criterii de timp:
Î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 care zi a săptămânii reprezintă cel mai mare număr de vânzări:
În această formă, este foarte convenabil să segmentezi rapoartele de vânzări pentru perioade lungi, medii și scurte de timp.
Merită 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 RAZNDAT special concepută:
Tipul de valori din celulele de date necesită o abordare specială la procesarea datelor. Prin urmare, ar trebui să utilizați adecvat acest tip funcții în Excel.
Fiecare tranzacție este efectuată la un anumit moment sau perioadă și apoi legată de o anumită dată. În Excel, datele sunt convertite în numere întregi. Adică, fiecare dată are propriul său număr întreg, de exemplu, 01/01/1900 este numărul 1, iar 01/02/1900 este numărul 2 etc. Definirea anilor, lunilor și zilelor nu este altceva decât tipul adecvat de formatare pentru următoarele valori numerice. Din acest motiv, chiar și cele mai simple operații cu date efectuate în Excel (de exemplu, sortarea) se dovedesc a fi foarte problematice.
Sortați în Excel după dată și lună
Folosind exemplul tabelului prezentat în figura de mai jos, vom arăta cum să îl pregătim pentru o sortare confortabilă a datelor. Lista noastră de tranzacții cu date acoperă intervalul de celule B1:C15.
În primul rând, să creăm o coloană în care numerotăm toate tranzacțiile. Considerați această acțiune ca fiind o formă bună pentru orice tip de sortare. Într-adevăr, în astfel de cazuri, dacă datele sunt amestecate incorect, avem întotdeauna posibilitatea de a reveni la forma originală a tabelului. Pentru aceasta:
Ca rezultat, coloana va fi completată automat cu o secvență de numere de tranzacție de la 1 la 14.
Sfat util! În Excel, majoritatea problemelor au soluții multiple. Pentru a normaliza automat coloanele în Excel, puteți utiliza butonul din dreapta al mouse-ului. Pentru a face acest lucru, trebuie doar să mutați cursorul peste marcatorul cursorului de la tastatură (în celula A2) și să țineți apăsat doar butonul corect Folosește mouse-ul pentru a desena un marcator de-a lungul coloanei. După eliberarea butonului drept al mouse-ului, va apărea automat un meniu contextual din care trebuie să selectați opțiunea „Umplere”. Și coloana va fi completată automat cu o secvență de numere, similară cu prima metodă de completare automată.
Acum este timpul să creați coloane auxiliare care vă vor ajuta în mod semnificativ la sortarea eficientă a listei de tranzacții. Trebuie să urmați câțiva pași simpli:
- Completați celulele D1, E1, F1 cu numele de titlu: „An”, „Lună”, „Ziu”.
- În funcție de fiecare coloană, introduceți funcțiile corespunzătoare sub titluri și copiați-le de-a lungul fiecărei coloane:
- D1: =AN(B2);
- E1: =LUNA(B2);
- F1: =ZI (B2).
Ca urmare, ar trebui să obținem următorul rezultat:
Tabelul nostru este gata și oferă posibilitatea de a efectua sortarea în mai multe sensuri a tranzacțiilor în funcție de dată. Să facem mai întâi o sortare de test pentru a ne asigura că totul funcționează.
Să presupunem că vrem să sortăm datele tranzacțiilor după lună. În acest caz, ordinea zilelor și anilor nu contează. Pentru a face acest lucru, pur și simplu accesați orice celulă din coloana „Lună” (E) și selectați instrumentul: „DATE” - „Sortați și filtrați” - „Sortați ascendent”.
Acum, pentru a reseta sortarea și a readuce datele tabelului la forma inițială, mergeți la orice celulă a coloanei „Nu.” (A) și selectați din nou același instrument „Sort Ascending”.
Cum să sortați datele după mai multe condiții în Excel
Acum puteți începe sortarea complexă a datelor în funcție de mai multe condiții. Sarcina este următoarea: tranzacțiile trebuie sortate în următoarea ordine:
- Anii în ordine crescătoare.
- Lunile din anumiți ani sunt în ordine descrescătoare.
- Zilele din anumite luni sunt în ordine descrescătoare.
Metoda de implementare a sarcinii:
Ca urmare, am efectuat o sortare complexă a datelor în funcție de mai multe condiții:
Pentru a sorta valorile tabelului în format de dată, Excel oferă opțiuni de listă derulantă, cum ar fi Vechi la Nou și Nou la Vechi. Dar, în practică, atunci când lucrați cu volume mari de date, rezultatul nu este întotdeauna la înălțimea așteptărilor. Din moment ce pentru program date Excel– acestea sunt numere întregi; este mai sigur și mai eficient să le sortați folosind metoda descrisă în acest articol.
