miercuri, 5 octombrie 2011

Sa obtinem maximul dintr-o zi: prelucrarea datelor calendaristice

De fiecare data cand informatiile sunt colectate in mod cronologic intr-un tabel, cel putin una dintre coloane va contine o data calendaristica : platile realizate in ultimele 6 luni, vanzarile realizate in ultimii 5 ani, fisa contului 601 din 2010, comenzile de papetarie inregistrate in luna curenta, etc, etc sunt doar cateva astfel de exemple.

Atunci cand avem de-a face cu un numar mare de inregistrari, prelucrarea isteata a campurilor de date calendaristice ne poate ajuta sa reorganizam informatia si sa conducem o serie de analize suplimentare: evolutii lunare, anuale, trimestriale, etc.

In acest articol voi prezenta cateva formule care ma ajuta sa "storc" mult mai mult dintr-o simpla data calendaristica: Year(), Month(), Day(), Weekday()

Voi continua exemplul inceput in aricolele anterioare si voi adauga cateva noi cerinte. Aici gasiti fisierul.

Cerinta
Avem baza noastra de date cu vanzarile din ultimii 5 ani. De aceasta data trebuie sa raspundem urmatoarelor intrebari:
  1. Care este evolutia vanzarilor pe ani?
  2. Exista o sezonalitate a vanzarilor? 
  3. Este o anumita zi din saptamana mai propice vanzarilor?
Rezolvare
1. Evolutia vanzarilor pe ani : Year()
In tabelul nostru tranzactiile sunt inregistrate zilnic. 
Am putea realiza un grafic direct pe baza de date, insa cu peste 1600 de inregistrari vom obtine o imagine foarte incarcata.
Ne intereseaza sa obtinem o imagine de ansamblu cu valorile cumulate pe fiecare an in parte:

Pentru a putea regrupa datele pe ani, trebuie sa extragem anul din data fiecarei tranzactii.
Aici ne ajuta functia Year(). Functia are un singur argument, si anume celula in care se afla data calendaristica.
Adaugam o noua coloana in baza de date "Anul" si pe aceasta coloana folosim functia Year() pentru a "extrage" anul din data tranzactiei.

Odata calculat anul, avem mai multe posibilitati pentru calculul valorii toatale a tranzactiilor pe fiecare an. Pentru graficul de mai sus eu am apelat la un tabel pivot si la un grafic. (mai multe despre acestea intr-un articol viitor)

2. Sezonalitate: Month() + IF()
Pentru a analiza sezonalitatea, am putea regrupa tranzactiile pe trimestre. Daca descoperim ca in anumite trimestre vanzarile sunt mai ridicate decat in altele am putea banui ca este vorba de o sezonalitate a vanzarilor.

Avem doua etape pentru rezolvarea aceste probleme:
  • extragerea lunilor din data calendaristica
  • regruparea lunilor pe trimestre
Pentru a extrage numarul lunii folosim functia Month(). La fel ca si Year(), singurul argument al acestei functii este celula in care se afla data calendaristica. Spre exemplu Month(25/06/2011) = 6 (iunie).

Odata extrasa luna, cu ajutorul functiei IF() putem crea coloana "Trimestru" folosind regulile de mai jos
  • lunile  1,    2,   3 = Trim 1 (luna <=3)
  • lunile  4,    5,   6 = Trim 2  (luna <= 6)
  • lunile  7,    8,   9 = Trim 3  (luna <= 9)
  • lunile 10, 11, 12 = Trim 4  (luna >  9)
Odata creata coloana "Trimestru", tranzactiile pot fi regrupate dupa trimestre pentru a obtine o imagine de ansamblu. Eu am apelat din nou la un tabel pivot + un grafic:
Din graficul de mai sus nu reise foarte clar o tendinta de sezonalitate. Observam totusi ca in a doua parte a anului 2011 vanzarile au explodat. Sa fie rezultatul campaniilor de marketing initiate la inceputul anului?

3. Cea mai buna zi pentru vanzari: Weekday() + IF()


Cu ajutorul functiei Weekday() putem extrage numarul zile din saptamana. Acesata are doua argumente:

  • celula cu data calendaristica
  • felul in care sunt numarate zilele : 1 = prima zi este duminica, 2 = prima zi este luni
De exemplu Weekday(05/11/2011, 2)  = 3 (miercuri).

Odata extrase zilele saptamanii, cu ajutorul functiei IF() putem adauga numele fiecarei zi astfel incat datele sa fie mai clare: 1 = luni, 2 = marti, ... etc

Avand creat campul "Ziua din saptamana" putem regrupa imformatiile pentru a vedea daca anumite zile din saptamana sunt mai bune pentru vanzari decat altele. Cu ajutorul unui tabel pivot am obtinut urmatorul grafic:

Este clar, din graficul de mai sus, ca nu putem identifica o anumita zi ca fiind mai speciala. In schimb putem observa ca sambata si duminica nu avem nici o vanzare: avem inchis in weekend :) .

Note finale
Pentru ca functiile de mai sus sa functioneze corect, celulele folosite ca argumente trebuie sa fie de tipul data calendaristica. In caz contrar calculele nu vor functiona si vor aprea erori.

Sfat, daca nu stiti care este exact formatul de data al sistemului vostru: Intr-o celula introduceti data curenta folosind combinatia de taste CTRL + ;


Puteti descarca fisierul cu calculele de aici.

Pentru intrebari, sugestii si comentarii ma puteti contacta si pe Facebook.
Spor!

Niciun comentariu:

Trimiteți un comentariu