Sari la conținut

Pivot, SUMIFS(), VLOOKUP?

    Avem de prezentat o situație cu vânzările acumulate pe fiecare agent la finalul lui august.

    O vom face folosind trei metode:

    1. VLOOKUP() + IFERROR(),
    2. SUMIFS ();
    3. tabel pivot.

    Fișierul de lucru aici (pentru download): Pivot_Sumifs_Vlookup

    Avem nevoie de funcții Excel capabile să:

    • caute rând pe rând valorile Ion Ion, Suzana Zidaru, Oliviu Radescu, etc … în tabelul de lookup;
    • când le găsește să extragă valoarea vânzărilor corespunzătoare (vânzări făcute de Ion Ion, etc … în august);
    • când nu le găsește să scrie 0 (zero) pentru valoarea de lookup respectivă.

    VLOOKUP()

    Funcția vlookup are 4 argumente:

    1. A4 = valoarea de lookup
    2. F4:G18 = matricea de lookup = 2 coloane:
      1. în prima coloană se găsește lista de agenți cu vânzări în august, unde vlookup îl caută pe Ion Ion (A4);
      2. în a doua sunt valorile de extras. Dacă Ion Ion este găsit undeva în F4:F18 atunci este returnată valoarea corespondentă din G4:G18. Dacă nu se returnează #N/A (nu este disponibil)
    3. numărul coloanei din tabelul de lookup de unde să returneze valoarile = 2 (a doua coloană)
    4. tipul de lookup = 0, vrem o potrivire exactă. Dacă în F4:F18 scrie Ion_Ion în loc de Ion Ion (valoarea exactă de lookup) rezultatul este #N/A pentru că valoarea Ion Ion nu există.

    Ion Ion, Oliviu Rădescu, Ludovica Vasilache, etc … au fost în concediu în august … așadar nu au vânzări care să apară în lista din august (și nu apar în lista agenților cu vânzări în august).

    Când nu găsește valoarea de lookup, VLOOKUP() ne afișează #N/A … care nu poate fi adunată.

    Ca să obținem 0 în loc de #N/A vom adăuga și funcția IFERROR() în formulă.

    IfERROR() + VLOOKUP()

    Formula din D4 va deveni:

    … care inseamnă:

    • vezi dacă rezultatul funcției vlookup este eroare
    • dacă da, scrie 0;
    • dacă nu, afișează rezultatul lui vlookup.

    Doar că, în tabelul de lookup Selina Vitcu apare de două ori!

    Poate sunt două facturi? Poate a doua (sau prima) înregistrare este greșită? Trebuie investigat.

    … funcțiile clasice de lookup odată ce au găsit o valoare se opresc la ea. Dacă există și o a doua valoare identică, acesta va fi ignorată cu desăvârșire …

    SUMIFS()

    SUMIFS() știe:

    • să aloce valoarea 0 când valoarea de lookup nu este prezent[în tabelul de lookup (Ion Ion, Oliviu Rădescu, Ludovica Vasilache, etc..);
    • să captureze toate valorile de însumat.

    Cele trei argumente ale funcției sumifs sunt:

    1. G4:G18 – zona valorilor de însumat = vânzările din august ale agenților din lista august
    2. F4:F18 – zona unde să caute criteriul de însumare = lista agenți august
    3. A4 – criteriul de însumare = fiecare agent în parte, luat din lista lunii precedente

    Zona valorilor de însumat și zona de căutare a criteriului de însumare sunt fixate cu ajutorul operatorului $ și în fața literei coloanei și în fața (detalii aici)

    Tabelul Pivot

    Organizare Date

    Datele așa cum le-am văzut până acum prezintă trei tipuri de informații organizate într-o matrice cu doar două coloane:

    1. Cine (agenți – coloana 1)
    2. Cât (valoarea vânzărilor – coloana 2)
    3. Când (doar capul de tabel al coloanei 2 ne indica luna în care s-au petrecut vânzările)

    În această organizare matriceală datele nu pot fi încărcate întrun tabel pivot.

    Va trebui mai întâi să normalizăm tabelul sursă … fiecare tip de informație în propria coloană. Așa, pe fiecare rând vom avea Cine, Cât, Când:

    Crearea Tabelului Pivot

    Fișierul de lucru aici (pentru download): Pivot_Sumifs_Vlookup

    În sheet DatePivot, cursorul undeva în tabelul Excel (este un tabel Excel cu numele tbDateNormalizate, nu sunt doar date aranjate în format tabelar. E o diferență importantă între Tabele Excel și date în aranjament tabelar)>

    … click meniul Insert>Pivot Table>From Table/Range:

    Apare un dialog. Vom alege new worksheet:

    Numim noul worksheet ”Pivot”. El va conține:

    Operarea Tabelelor Pivot

    Pentru a folosi pivotul tragem câmpurile de date care ne interesează în zona de operare.

    Raportul este gata:

    Putem face analize multiple doar prin tragerea câmpurilor de date în zona de operare. Iată un alt pivot care folosește datele din worksheetul DatePivot2:

    … și în următoarele 10 secunde putem produce un alt raport cu datele organizate diferit:

    .. și în următoarele 10 secunde un nou raport:

    Ați observat diferențele dintre cele trei rapoarte … zona de rows sau coloane.

    *

    Click pe pivot pentru a afișa câmpurile lui de operare … pe care le tragem în zona de control (cele 4 casete: rows, values, columns, filters).

    Fișierul de lucru aici (pentru download): Pivot_Sumifs_Vlookup

    Etichete: