Time management – creaza rapid un dashboard de impresionat colegii

206531097

 

 

 

 

 

Sa pornim de la premisa ca tocmai ni s-a cerut un nou raport de activitate din partea managerului.

Deschidem Excel-ul unde avem stocate informatiile necesare, ne uitam la el, el la noi si ne gandim cum sa sintetizam datele in asa fel incat sa putem pune intr-un sheet raportul cerut.

Sa presupunem ca avem urmatorul cap de tabel:

Client, Produs, Sales Person, Regiune, Data vanzare, Valoare, An, Luna, Trimestru, Channel Partners

Din raport ar trebui sa reiasa urmatoarele informatiile, daca se poate si intr-un mod interactiv 🙂

  • valoarea vanzarilor per client
  • valoarea vanzarilor per produs
  • valoarea vanzarilor per angajat
  • top 10 Channel Partners

Este necesar ca analiza sa fie efectuata pe toti cei 3 ani.

In functie de imaginatia fiecaruia, raportul poate arata diferit.

Va propun varianta de mai jos:

Pentru a ajunge la aceasta varianta de raport tot ce trebuie sa facem este sa utilizam 4 optiuni ale Excel-ului pe baza noastra de date:

  • tabel – datele noastre sunt prezentate sub forma tabelara. Avantajul este dat de redimesioanrea automata a tabelului in cazul in care sursa de data sufera modificari.
  • pivot tabel – pentru sintetizarea datelor
  • grafic – afisarea grafica a informatiilor
  • slicer – aplicarea de filtre interactive

Sa o luam cu inceputul 🙂  Puteti deschide fisierul de aici.

Fisierul contine 3 sheet-uri: Raport, Pivot si Sursa.

In sheet-ul Sursa avem baza noastra de date.

Pentru a avea datele asezate ordonat  si a fi mai usor de prelucrat am inserat un tabel (clic in zona de date – apasam combinatia de taste CTRL+T sau dam clic pe tab-ul Insert si butonul Table pentru a insera un tabel).

In coloana G – An am extras anul din coloana E – Data vanzare folosind functia Year, functie ce returneaza anul dintr-o data calendaristica. Sintaxa este: = year(serial_number).

Formula aplicata:

=YEAR(E2)

In coloana H – Luna am extras luna din coloana E-Data vanzare folosind o formula compusa din functia Month, ce returneaza luna dintr-o data in format numeric de la 1 la 12, 1 fiind asociat pentru Ianuarie, iar 12 pentru Decembrie si a carei sintaxa este: = month(serial_number) si functia Choose ce returneaza o valoare dintr-o lista pe baza unui numar. Sintaxa functiei Choose este =choose(index_num; value1,[value2];….;[value254])

Formula aplicata:

=CHOOSE(MONTH(E2);”Ianuarie”;”Februarie”;”Martie”;”Aprilie”;”Mai”;”Iunie”;”Iulie”;”August”;”Septembrie”;”Octombrie”;”Noiembrie”;”Decembrie”)

In coloana I – Trimestru am determiant in ce trimestru a fost efectuata tranzactia folosind o formula compusa din functia Month si functia IF ce testeaza o conditie si returneaza ce ii indicam in cazul in care conditia este indeplinita sau nu. Sintaza functiei IF este: = if(logical_test; [value_if_true];[value_if_false]).

Formula apliacata:

=IF(MONTH(E2)>9;”Q4″;IF(MONTH(E2)>6;”Q3″;IF(MONTH(E2)>3;”Q2″;”Q1″)))

Pentru a putea trece la pasul urmator, PivotTable, trebuie sa ne asiguram ca avem spatii goale  in sursa de date, iar in caz ca exista sa le eliminam si sa avem capul de tabel completat cu titluri relevante.

Pasul 1 – Inserarea Pivotului

Pentru a insera pivotul trebuie sa dam clic in zona de date din sursa noastra, apoi din tablul Insert dam clic pe butonul PivotTable.

Se va deschide fereastra de dialog Create PivotTable:

  • In zona Select a table or a range avem selectat tabelul pe care l-am creat anterior – Table 1
  • In zona Choose where you want the PivotTable report to be placedavem doua optiuni: New Worksheet – asta inseamna ca va plasa pivotul intr-un nou sheet si Existing Worksheet – ii vom indica noi in ce sheet sa plaseze pivotul creat.

Pentru primul pivot creat am ales optiunea de New Worksheet, urmand ca pentru celelalte sa utilizez cea de-a doua optiune.

Acest nou sheet creat l-am redenumit Pivot. Aici vom observa 2 zone: zona unde vom crea pivotul si zona de campuri ( PivotTable Fields) zona unde vom aduce campurile pentru raportare.

PivotTabel Fields are 2 zone mari:

  • Zona de campuri – unde regasim capul din tabelul nostru sursa
  • Zona 2 – este impartita in 4 casute:
    • Rows – zona in care vom aduce capetele de tabel ce vrem sa apara pe randuri in raportul nostru;
    • Columns – zona in care vom aduce acele capete de tabel ce vrem sa apara pe coloane in raportul nostru;
    • Values – zona in care vom aduce acele capete de tabel cu ajutorul carora vom efectua calcule;
    • Filters – zona in care vom aduce acele capete de tabel cu ajutorul carora vom aplica filtre in raportul nostru.

Pentru primul raport am dus Clientul in zona de randuri (rows) si Valoarea in zona de  valori (Values).

Pasul 2 a fost inserarea unui grafic aferent raportului creat. Pentru asta am dat clic pe butonul PivotChart din tab-ul Analyze. Acest tab este activ atat timp cat pivotul creat este activ.

Se va deschide fereastra Insert Chat. De aici selectam tipul de grafic si apoi apasam butonul Ok.

Pasul 3 a fost inserarea unui slicer – tab-ul Analyze, butonul Insert Slicer.  In fereastra Insert Slicers am bifat campul pentru care vreau sa inserez slicer, in acest caz Client, apoi am dat clic pe butonul Ok.

Am repetat pasii 1-3 pentru a insera si ceilati 3 pivoti, cu graficele si slicer-ele aferente.

Apoi am inserat un nou sheet pe care l-am redenumit Raport.

Am mutat toate slider-ele si graficele din sheet-ul Pivot in sheet-ul Raport si le-am pozitionat dupa bunul plac :).

Pentru ca aceste slidere sa fie interconectate, adica in momentul in care aplic filtru folosind optiunea de slicer, aceasta filtrare sa fie activa la toti pivotii mei, inclusiv pe grafice, trebuie sa cream legaturi intre toate slider-ele.

Acesta legatura se realizeaza in felul urmator: clic dreapta pe slider, alegem optiunea Report Connections sau  din tab-ul Slicer Tools- Options dam clic pe butonul Report Connections. Se va deschide fereastra Report Connections si vom alage din aceasta tabela cu ce alt  pivot vom lega slicer-ul creat prin bifarea casutelor de dialog. Apoi dam clic pe butonul Ok.

Se repeta operatiunea si pentru celelalte slicere create, daca vream sa fie interconectate.

 

 

Tagged , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.