Crearea unui Tabel Pivot

Haideți să ne uităm puțin pe acest tabel simplu. Pentru a vă prezenta mai usor tabelul, am să activez instrumentul de filtrare și observați că, pe coloana COMPANIE, există doar trei companii unice (X, Y, Z), iar pe coloana PRODUS avem, iată, șase produse.

De asemenea, există foarte multe linii în acest tabel. Am să fac CTRL + Săgeată jos pentru a mă poziționa pe ultima linie de tabel și observați că avem mii de linii.

Cu alte cuvinte, cele trei firme vând cele 6 produse de sute și sute de ori.

Să răspundem împreună la urmatoarele întrebări, iar apoi urmează să vă explic, în detaliu, cum se fac aceste tabele pivot și, mai ales, ce reprezintă ele din punct de vedere conceptual.

Prima întrebare: Care este suma costurilor pentru Firma Y? Adică să căutăm pe coloana Companie firma Y, iar apoi de pe coloana COST să le adunăm costurile.

Punem un filtru pe coloana Companie pentru a găsi liniile specifice firmei Y. Selectam de aici Firma Y, iată, au rămas costurile doar de la această firmă. Selectăm cu CTRL + SHIFT+ Săgeată jos toate aceste costuri și observați că suma lor este:  553.642,97

Ne întoarcem cu Ctrl + Săgeată sus și observați că această sumă am scris-o eu manual în celula C3

A doua intrebare: Care este suma costurilor pentru produsul 3? La fel, o să ieșim din modul Filter și o luăm de la capăt. Adăugăm un filtru nou, de pildă, pentru produsul 3, selectăm costurile acestui produs (cu CTRL + Shift Săgeată jos) și observați această sumă calculată automat de Excel:  177,734.75 pe care am copiat-o manual în celula C5.

Mergem mai departe.

A treia intrebare: Care este suma costurilor pentru toate tranzactiile firmei Y pentru produsul 3? Observați că sunt cele două căutări de dinainte executate simultan.

Punem un filtru cu doua conditii logice, alegem firma Y și de la această firmă alegem doar produsul 3.

Iată că am adăugat două conditii logice, selectăm toate aceste costuri și, iată suma:  71,155.00 pe care am scris-o în celula C7.

În concluzie, în primul exemplu am făcut suma Costurilor pentru compania Y. Observați că o să pun în acest centralizator toate rezultatele la care am ajuns în exemplele de dinainte.

Observați că am pus toate produsele pe verticală, toate companiile pe orizontală.

Suma Cost pentru compania Y ar trebui copiată în centralizator în celula aceasta de aici: K11, deoarce este vorba despre suma tuturor produselor firmei Y.

Unde ar trebui, în mod normal, să copiez Suma pentru produsul 3? Firește, în celula M7, deoarece este vorba despre suma pentru toate companiile ce au vandut Produsul 3.

În al treilea exemplu, vă aduceți aminte că am avut doua condiții logice de cautare. Produsul 3 și Firma Y, cu alte cuvinte trebuie să copiem suma calculată și să o punem în centralizator la intersecția dintre coloana Firmei Y și rândul produsului 3.

Până la urmă, ce am reușit să calculăm până acum? Suma pentru coloana Y pentru toate Produsele. Suma pentru toata linia Produsului 3, respectiv valoarea aflată la intersecția dintre Firma Y și Produsul 3.

Bineînțeles că nu este suficient! Trebuie completat tabelul centralizator și calculate TOATE valorile agregate. Nu numai pentru Firma Y sau Produsul 3, ci pentru orice combinație de Firmă și Produs! Pentru că asta se spune în enunț: care este suma costurilor pentru toate Produsele vândute de fiecare Companie în parte.

Vă dați seama că această metodă folosită până acum nu mai putem să o continuăm. Dacă aveam sute de produse unice și zeci de companii, ar fi absurd să trebuiască să calculăm manual fiecare celulă în parte.

Am să vă arăt, în urmatoarele 5 minute, cum putem să-l punem pe Excel să calculeze automat valorile din nomenclatorul nostru. Cu fix 4 click-uri de mouse!

Vă rog să ne mai uităm înca odată. Iată, toate produsele unice au fost puse pe verticală (pe linii, în jos), iar toate companiile sunt scrise în nomenclator pe orizontală.

Ca să facem automat tabelele pivot TREBUIE să ne răspundem (în mintea noastră) la 4 întrebări:

Prima întrebare: Care este coloana din tabelul inițial cu date ce stabilește RÂNDURILE din pivot? Din moment ce pe rânduri avem puse produsele, evident că răspunsul este: Coloana Produs.

A doua intrebare: Care este coloana din tabelul inițial cu date ce stabilește COLOANELE din pivot? Răspuns: Coloana Companie.

A treia intrebare: Care este coloana din tabelul inițial cu date ce AGREGĂ? Coloana de adunat o să fie COST.

Ultima intrebare: Ce funcție Agregata dorim? Evident că dorim SUMA (cu alte cuvinte, funcția agregată pe care Excel o va folosi este: SUM)

Dupa ce am răspuns la toate aceste patru întrebări, ne va fi foarte ușor acum să generăm automat tabelul Pivot.

Să fim pozitionați pe tabelul original cu date și, din meniul Insert alegem Pivot Table. Observați că Excel detectează automat tabelul de Pivotat, dăm OK și. iată că ni se creează o foaie separată.

În dreapta, apar casete de marcare ce ne permit să-i răspundem Excel-ului la cele 4 întrebări.

Prima întrebare era: care este coloana care generează rândurile (adică Rows)? Știm raspunsul, este vorba despre coloana Produs. Observați că, în stanga apare tabelul generat automat, având toate produsele puse unice pe verticală.

A doua intrebare era: care este coloana care generează coloanele (adică, Columns). Nu avem de făcut decât să tragem coloana Companie peste caseta COLUMNS. Observați cum, în stânga în tabelul pivot, vom avea câte o coloană creată automat pentru fiecare Companie în parte.

A treia intrebare: Ce coloană însumăm? Coloana Cost o punem în caseta Σ VALUES de aici de jos.

Observați că, automat face suma. GATA! Avem, în centralizator, calulată suma costurilor pentru fiecare produs vandut de toate companiile. Cât de simplu!

Poate ar fi bine să copiem rezultatul final peste tabelul inițial pregătit de mine. Putem lua datele din tabelul pivot cu Copy, apoi peste acolo de unde am plecat. Iată cum avem toate datele calculate. Unde avem celule necompletate, înseamnă că nu există, în tabelul inițial cu date, o companie care să fi vândut produsul respectiv. De pildă, produsul numarul 4 nu a fost niciodată vândut de firma Z.

Haideți să mai facem un exemplu, iată, l-am scris aici: Care este Media costurilor pentru fiecare Luna din fiecare AN.

Observați coloana Data Tranzactiei care deține informațiile legate de an și lună. Vreau să vă spun că acest tip de tabele Pivot, ce au în componența lor informații legate de An, Luna, Zi și așa mai departe sunt cele mai des cerute nomenclatoare în mediul de business.

Trebuie să răspundem la cele 4 întrebări:

Care este coloana din tabelul inițial cu date ce stabilește RÂNDURILE din pivot?

Am avem două opțiuni, ori punem pe rând anii ori lunile. Să vedem cam câți ani avem în tabelul inițial cu date. De la 2008 la 2015 sunt 8 ani.

Având în vedere că sunt mai puțini ani decât luni (care bineînteles că sunt 12 la numar), o să punem LUNILE pe rânduri. Vă recomand ca tabelul Pivot generat să aibă cât mai multe rânduri și cât mai puține coloane, pentru a fi mai compact și mai ușor de urmărit. Pentru că avem 12 luni și 8 ani, o să punem Lunile pe verticală în jos (fiind mai multe) și Anii pe orizontală (fiind mai puțini).

Deci:

Prima întrebare: Care este coloana din tabelul inițial cu date ce stabilește RÂNDURILE din pivot? LUNA.

A doua intrebare: Care este coloana din tabelul inițial cu date ce stabilește COLOANELE din pivot? ANUL

 Însă nu există aceaste două coloane. Va trebui să le calculăm separat. Adăugăm cele două coloane calculate. Prima este AN: o calculăm cu funcția Year…

=YEAR(B13)

… și Luna pe care o calculăm cu funcția Month.

=MONTH(B13)

Selectăm cele două celule calculate și facem o singură dată dublu click pe mânerul de umplere pentru a-l pune pe Excel să calculeze cele două coloane până jos.

Avem calculate Anul și Luna și răspundem iar la cele patru intrebari:

1. Care este coloana din tabelul inițial cu date ce stabilește RĂNDURILE din pivot? Va rog să ne imaginăm puțin, am zic că LUNA.

2. Care este coloana din tabelul inițial cu date ce stabilește COLOANELE din pivot? ANUL.

3. Care este coloana de agregat, adică acea coloană cu care facem calcule matematice? Coloana Cost.

4. Și, în sfârșit, care este funcția agregată dorită, adică ce tip de socoteală facem? Am zis că media (Average)

Bun, trecem la treabă, să fim poziționați in tabelul cu date pe care il dorim pivotat, apoi alegem din Insert opțiunea Pivot Table. Dăm OK. Observați că, în dreapta, apar toate coloanele din tabel.

Luna genereaza rândurile. Am zis că LUNA: Uitați cele 12 luni că apar deja.

Care este coloana care genereaza coloanele? Am spus ca ANUL: Iată anii puși pe coloane.

Și, în sfârșit, coloana COST o tragem peste caseta VALUE, observați că face SUMA însă noi dorim Media. Apăsăm pe Suma și alegem, din Field Settings, Average și GATA!

Avem, iată Media costurilor pentru fiecare Luna din fiecare AN. CU fix 4 click-uri de mouse.