luni, 10 octombrie 2011

Adunari conditionate: SUMIF()

Atunci cand vreau sa-i spun Excel-ului : "Calculeaza o suma a valorilor care corespund anumitor criterii" ma las, cu incredere, pe mana functiei SUMIF().

In viata de zi cu zi sunt destul de frecvente situatiile in care trebuie adunate doar valorile care corespund anumitor criterii: "care este suma facturilor emise in 2011 pe clientul X?" , "care este suma tranzactiilor a caror valoare este mai mare decat media?", "care este suma valorilor mai mici decat 100 ron?", etc etc.

In exemplul de azi ne vom baza pe SUMIF() pentru a cumula datele din doua tabele diferite si pentru a conduce o scurta analiza. In demersul nostru vom descoperi si functia EXACT()

Datele de intrare
  1. Baza de date cu facturile emise, cu urmatoarea structura:

    Client | Data facturii | Valoare

    Cuprinde toate facturile emise cu detaliu pe zile, in 2011, catre diversi clienti. Pot exista mai multe facturi pentru un client
  2. Baza de date cu incasarile, avand urmatoarea structura:

    Client | Data incasarii | Valoare

    Cuprinde incasarile realizate in 2011 cu detaliu pe client. Pot exista mai multe incasari pentru un client. O incasare poate cuprinde mai multe facturi.
Cerinte
Pe baza datelor din cele doua tabele trebuie realizata o situatie cu sumele ramase de incasat pe fiecare client in parte

In functie de sumele ramase de incasat, de realizat o ierarhizare a clientilor, astfel incat actiunile de recuperare a datoriilor sa fie concentrate pe principalele zone cu probleme.

Rezolvare
Intai si-ntai trebuie sa clarificam cerintele si sa le "spargem" in mici actiuni:

1. Trebuie sa realizam o lista in care fiecare client sa apara o singura data. De ce? Vrem sa obtinem totalul facturilor si incasarilor pe fiecare client in parte, iar in bazele noastre de date un client poate aparea de mai multe ori.

2. Trebuie sa calculam valoarea totala a facturilor si a incasarilor pe fiecare client in parte.

3. Trebuie sa calculam valoarea ramasa de incasat pe fiecare client in parte

4. Trebuie sa regrupam clientii in functie de importanta sumelor ramase de incasat, astfel incat sa ne concentram eforturile pe problemele majore. Speram sa putem folosi regula 80-20: 80% din sumele ramase de incasat sa fie generate de 20% din clientii nostri.

Si acum sa luam fiecare pas in parte.


1. Lista in care fiecare client sa apara o singura data
Dupa cum am precizat in datele de intrare, avem mai multe facturi pentru un client. De asemenea avem mai multe incasari pentru un client.
In plus nu este obligatoriu ca toti clientii din baza de date de facturi sa existe in baza de date de incasari (putem avea situatii in care nu s-a realizat nici o incasare pe un client).

Asadar, vrem o lista care sa cuprinda:
a) toti clientii din baza de date de factuari si toti clientii din baza de date de incasari, si
b) fiecare client sa apara o singura data

Pentru punctul a) cream un nou "sheet" in care pe coloana A copiem toti clientii din baza de date facturi. Sub acestia copiem toti clientii din baza de date incasari. Asadar avem lista cu toti clientii din cele doua baze de date, dar un client poate aparea de mai multe ori. Trecem la punctul b) pentru eliminarea duplicatelor

Pentru punctul b) exista mai multe variante.

Cei care folosesc Excel 2007 sau 2010 pot utiliza "Remove Duplicates" din meniul "Data": selectati coloana unde ati copiat toti clientii si lansati "Remove Duplicates". Vor ramane doar valorile unice.

Pentru ceilalti, exista o alternativa. In plus vom invata si functia Exact(). Iata ce trebuie facut:
  • Sortam coloana pe care am copiat clientii ( Data -> Sort)
    Vom avea o lista in care "exemplarele" multiple ale unui client for aparea unul sub altul. 
  • Acum trebuie sa-i spunem Excel-ului sa ne identifice valorile care se repeta.
    Rationamentul este simplu: daca valoarea de pe celula curenta este identica cu valoarea de pe celula de deasupra, inseamna ca avem de-a face cu un duplicat, in caz contrar este o valoare unica.

    In aceasta situatie folosim functia EXACT(). Aceasta "stie" sa verifice doua siruri de caractere iar daca sunt identice va returna "TRUE" . In caz contrar va returna "FALSE". Pe coloana B vom aplica formula EXACT() dupa modelul: EXACT(A2,A1).
  • Pe cloana B filtram si pastram doar valorile "FALSE": valorile unice.
  • Copiem aceste valori in foia in care vrem sa facem analiza
Pare complicat? In practica este foarte simplu. In fisierul atasat acestui articol, pe foia "pasul 1: lista de clienti" gasiti modul in care am pastrat doar valorile unice.

2. Valoarea facturilor si incasarilor pe fiecare client in parte
Acum incepe partea "distractiva". Am creat o foaie "Analiza incasari pe client" in care am copiat lista de clienti obtinuta la pasul 1.

Creez doua coloane noi: Total facturat si Total incasat.
Practic vreau sa-i pun Excel-ului urmatoarea intrebare:

"Pentru fiecare client in parte, care este totalul facturilor emise si care este totalul incasarilor din cele doua baze de date?"

Acum intra in scena vedeta acestui articol: SUMIF().

Sintaxa acestei functii este:

SUMIF(range, criteria, sum_range)


Adica:
  • range = pe ce coloana se face cautarea
  • criteria = care este criteriul de cautare
  • sum_range = de pe coloana sa fie adunate valorile
In cazul nostru, pentru Total facturat:
  • range = coloana cu toti clientii din baza de date cu facturile emise
  • criteria = clientul pentru care vreau sa calculez totalul
  • sum_range = coloana pe care se afla valoarea facturilor emise in baza de date cu facturile.
Pe romaneste: "Pentru Clientul 1: du-te in baza de date si cauta toate inregistrarile care au pe coloana Client (range) valoarea<Client 1> (criteria) si calculeaza totalul de pe coloana cu valorile facturilor (sum_range)".

Acelasi rationament se aplica si pentru Total incasat, doar ca trebuie realizate cautari in baza de date de incasari.

In fisierul atasat gasiti detaliile acestui calcul.

3. Restul de incasat pe fiecare client in parte
Dupa realizarea pasilor 1 si 2, calculul restului de incasat pare extrem de simplu. Voi crea o noua coloana Rest de incasat iar valoarea acesteia se calculeaza ca diferenta intre Total facturat si Total incasat.

4. Regruparea clientilor in functie de importanta sumelor ramase de incasat

Asa cum spuneam mai sus, vrem sa regrupam clientii astfel incat sa ne concentram pe acei clienti cu restantele cele mai mari. Speram sa putem avea 20% din clienti care sa fie raspunzatori de 80% din restante. In felul acesta cu minimul de efort putem rezolva o mare parte din probleme.

Pentru a identifica cei 80% din suma ramasa de incasat putem urma pasii de mai jos:
  • Sortam tabelul descrescator dupa valoarea restului de incasat
    In felul acesta avem clientii cu cele mai mari restante in partea de sus a tabelului
  • Calculam valoarea cumulata a restantelor , pe coloana Cumulat
  • Calculam valoarea procentula a restantelor cumulate, pe coloana %Cumulat ( =  Cumulat/Sum(Rest de incasat). Vrem sa identificam punctul unde avem 80% din restante
  • Adaugam o coloana Prioritati : 1 = pentru restantele care cumulat reprezinta 80% din total restante si 2 pentru restul de 20%.
Acum avem toate datele necesare pentru a vedea daca intr-adevar 80% din restante sunt generate de 20% din clienti. In plus vom putea extrage rapid o lista cu toti clientii din categoria 1 de prioritati.

Cu ajutorul unui tabel pivot si a unor grafice putem obtine o imagine de ansamblu a situatiei:


Dupa cum putem vedea suntem departe de situatia ideala: 80% din restante sunt generate de peste 45% din clienti. Aceasta inseamna ca lista noastra de prioritati 1 va fi destul de lunga si vom avea ceva de munca pentru a o rezolva. Lista clientilor de prioritate 1 este realizata intr-un tabel pivot, in fisierul atasat.

Analiza suplimentara
Pe langa analiza de la punctul 4 am putea realiza si o analiza de tip ABC.
Acesta analiza se utilizeza pentru gestiunea portofoliului de clienti, a stocurilor etc.
Am putea aplica aceeasi idee si la situatia noastra
Seamana cu analiza 80-20 insa are trei categorii.

In situatia ideala:
A: 70% din restante sunt generate de 20% din clienti
B: 25% din restante sunt generate de 30% din clienti
C: 5% din restante sunt generate de 50% din clienti

Modul de calcul al celor 3 categorii se aseamna cu modul de lucru de la punctul 4 si nu o sa-l detaliez aici. Poate intr-un alt articol.

Cu ajutorul unui tabel pivot si al unui grafic putem obtine o imagine a modului in care restantele sunt gestionate , vis-a-vis de situatia ideala:


In gri, repartitia restantelor pe cele trei categorii. In verde repartitia teoretica a numarului de clienti. In orange repartitia reala a numarului de clienti in cele trei categorii.

Din graficul de mai sus putem observa ca repartitia reala a clientilor este in totala contradictie cu repartitia teoretica.
Aceasta situatie ridica intrebari serioase legate de modul in care creantele companiei sint gestionate si impune o analiza mai detaliata a cauzelor acestei situatii.

Note finale
Observati ca, atunci cand am aplicat formula SUMIF, am blocat range-ul si sum_range-ul folosind semnul $ inaintea coloanei si a randului. Aceasta imi permite sa "trag" formula SUMIF pentru toti clientii fara ca cele doua "range"-uri sa se miste. Folosirea acestui blocaj este obligatorie in astfel de situatii.

Daca nu l-ati deschis pana acum, fisierul de calcul este aici.

Pentru comentarii sau sugestii folositi acest blog sau pagina noastra de Facebook.

Niciun comentariu:

Trimiteţi un comentariu