miercuri, 19 octombrie 2011

Siruri de caracter...e: si textul poate contine date utile

De cele mai multe ori, cand vorbim de prelucrarea datelor in Excel / OpenOffice / Google Docs, etc ne gandim la prelucrarea cifrelor, numerelor, sumelor. Insa campurile cu text nu trebuie ignorate!Sirurile de caractere pot deveni o adevarata "mina de date". Trebuie doar sa stim cum sa le prelucram.

Ce inseamna pentru mine siruri de caractere? Simplu: nume si prenume, adrese, coduri de produse, numere de telefon, etc. Din toate acestea se poate "stoarce" mai mult decat pare posibil la prima vedere iar analiza noastra si viteza de realizare a acesteia va capata noi dimensiuni.

Excelul ne pune la dispozitie o serie de formule specializate in prelucrarea sirurilor de caractere. Pentru mine, 8 dintre ele sunt mereu la indemana:
  • lower, UPPER, Proper : pentru a schimba din litere mari in mici si invers
  • Left, Right: pentru a extrage un numar de caractere din stanga sau din dreapta
  • Mid, Find : pentru a extrage un numar de caractere din interiorul unui sir mai lung
  • Concatenate sau & : pentru a combina mai multe siruri de caractere
lower, UPPER, Proper
Pentru a schimba din litere mari in litere mici si invers, cele trei functii sunt perfecte.

lower() : transforma toate caracterele din celula selectata in litere mici.
Se poate intampla ca anumite informatii sa fie scrise cu LITERE MARI sau cu o COmBinAtiE de litere mari si mici.
Oricare ar fi fost intentia autorului, se poate ca pentru anumite rapoarte sau prezentari ale informatiei aceasta forma a textului sa fie deranjanta.
Aplicand functia lower() toate caracterele vor fi scrise cu litere mici.

UPPER():  transforma toate caracterele din celula selectata in litere MARI
Este, practic, functia inversa lui lower(). Foarte utila cand vrem sa accentuam anumite date.

Proper(): prima litera din fiecare cuvant va fi scrisa cu majuscule iar restul cu litere mici
Este varianta de mijloc intre cele doua. Practica pentru nume proprii sau pentru titluri: IONUT POPESCU va deveni Ionut Popescu prin aplicarea functie Proper()

Left, Right
Cand vrem sa extragem partea de inceput sau de final al unui sir de caractere, aceste doua functii sunt foarte utile.

Left(sir de caractere, nr de caractere): extrage primele caractere de la inceputul unui text (din partea stanga).
Numarul de caractere care vor fi extrase este specificat in al doilea argument al functiei. Caracterele sunt numarate de la stanga la dreapta

Right(sir de caractere, nr de caractere): extrage ultimele caractere de la finalul unui text (din partea dreapta).
Numarul de caractere care vor fi extrase este specificat in al doilea argument al functiei. Caracterele sunt numarate de la dreapta la stanga.

Mid si Find
Combinatia acestor doua functii este foarte puternica in prelucrarea avansata a sirurilor de caractere.

Mid: extrage un sub-sir de caractere dintr-un text mai lung. Are urmatoarea sintaxa:
Mid(text, de unde sa inceapa extractia de date, cate caractere sa fie extrase)

De exemplu MID("Ghidul excelului", 8, 5) va extrage 5 caractere incepand cu al 8-lea din "Ghidul excelului".
Al 8-lea caracter este "e" si daca extragem 5 caractere incepand cu "e" obtinem "excel". (si spatiile sunt considerate caractere si sunt numarate).

Find: cauta un text intr-un sir de caractere si returneaza pozitia pe care se afla textul cautat:
Find(text cautat, unde se cauta, de la ce caracter incepe cautarea)
Spre exemplu, avem un numar de telefon: 0269/123333. Daca vreau sa aflu pe pe ce pozitie se afla "/" pot aplica functia Find astfel:
Find("/", "0269/123333", 1). Rezultatul va fi pozitia pe care se "/" in sirul de caractere, si anume 5.

Mid + Find = love
Separate, cele doua functii sunt utile, insa impreuna sunt extrem de utile.

Sa continuam exemplul cu numarul de telefon si sa presupunem ca vrem sa extragem doar numarul, fara prefix dintr-o lista cu numere din capitala si din provincie (deci lungime variabila a numerelor).

Pentru exemplul nostru vom considera doar doua numere, dar functia se poate aplica la o baza de date cu mii de numere: 0269/123456 si 021/1234567.

Inseamna ca vrem sa extragem pana la 7 caractere care se afla dupa "/". Problema este ca acest caracter are pozitie variabila (pe 5 sau pe 4).

Am putea folosi functia Mid, pur si simplu si sa punem de mana argumentul "de unde sa inceapa extractia de date", insa la o baza de date cu zeci-sute-mii de inregistrari va fi extrem de dificil.

O varianta pe care am putea-o folosi este ca la argumentul "de unde sa inceapa extractia de date" sa punem functia find(), adica sa avem ceva de genul:
Mid( nr tel, find("/",nr tel, 1)+1, 7), adica "din fiecare numar de telefon extragem 7 carctere care se afla imediat dupa backslash".
Astfel, indiferent de pozitia pe care se afla "/" vom obtine rezultatul dorit.

Concatenate sau &
Aceasta functie este un fel de "adunare" a sirurilor de caractere. Este foarte utila atunci cand vrem sa combinam informatiile existente in mai multe celule.

Spre exemplu daca in A1 avem "Ionut" si in B1 "Popescu" functia Concatenate(A1,A2) va returna "IonutPopescu".
Daca vreau sa returneze "Ionut Popescu" va trebui sa scriu astfel Concatenate(A1, " ",A2).

Concatenate este un nume cam lung pentru o functie, dar avem si variata prescurtata: &
Exemplul de mai sus ar putea fi scris astfel: =A1&" "&A2


Note finale
Functiile de prelucrare a sirurilor de caractere nu fac parte din "meniul" meu zilnic de Excel insa sunt extrem de utile atunci am nevoie de o prelucrare a unor date mai "pretentioase". De fiecare data cand apelez la ele castig enorm de mult timp, timp pe care il pot folosi in analiza rezultatelor.

Aici gasiti un fisier cu un exemplu de utilizare a functiilor prezentate mai sus.

Pentru comentarii sau sugestii puteti folosi acest blog sau pagina noastra de Facebook.
Spor!

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.

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!

duminică, 2 octombrie 2011

Sa punem conditii: IF()

Mi se intampla frecvent sa vreau sa obtin rezultate in functie de respectarea unor conditii prestabilite.

Spre exemplu: daca valoarea ramasa de plata este mai mare de zero, atunci marcheaza factura ca neplatita; in caz contrar, marcheaza factura ca platita.

In astfel de situatii apelez cu incredere la functia IF()

Daca va amintiti, in articolul despre Vlookup am creat o baza de date cu vanzari pe clienti. Vom prelua acel exemplu si vom merge mai departe.

Cerinta
Sa presupunem ca avem o noua cerinta: lista cu tranzactiile reprezinta o serie facturi neincasate. De data aceasta avem doua intrebari:
  1. De unde provin cele mai multe facturi neincasate: din servicii sau din productie?
  2. Care este repartitia facturilor pe vechime?
Pentru a doua intrebare, categoriile de vechime sunt stabilite astfel:

Vechime Categorie
sub 30 de zile recente
intre 31 si 90 de zile medii
intre 91 si 180 de zile vechi
peste 180 de zile foarte vechi


Rezolvare
Practic, in aceasta situatie ni se cere sa regrupam baza noastra de date dupa doua noi criterii:
  1. macro-domeniul in care activeaza clientul
  2. vechimea facturilor
In ambele situatii voi folosi functia IF() pentru a regrupa datele
Sa le luam pe rand.

1. Gruparea dupa macro-domeniu: productie sau servicii
In articolul trecut am vazut ca avem mai multe domenii de activitate in care clientii nostri activeaza:
  • transport
  • comert
  • productie
  • consultanta
  • educatie
Acum trebuie ca "productia" sa treaca in macro-domeniul "productie" iar celelalte sa fie grupate in "servicii".
In baza noastra de date voi adauga un nou camp "Macro-domeniu". Aici va trebui sa avem doar doua valori: productie sau servicii, in functie de domeniul in care activeaza clientul.
Practic vrem sa-i spune Excelului ceva de genul:

Daca clientul activeaza in domeniul "productie" atunci macro-domeniul este "productie" iar daca activeaza in alt domeniu atunci macro-domeniul este "servicii".

Este exact ceea ce Excel-ul stie sa faca prin functia IF(). Sintaxa acestei functii este:

IF(conditia, rezultat daca se respecta conditia, rezultat daca nu se respecta conditia)


In situatia noastra:
  • conditia: Domeniul de activitate este productie?
  • rezultat daca se respecta conditia: productie
  • rezultat daca nu se respecta conditia: servicii
In fisierul atasat veti gasi campul "macro-domeniu" calculat cu ajutorul acestei functii.


2. Gruparea datelor dupa vechimea facturilor
Aceasta grupare este un pic mai "tricky". Pentru indeplinirea acestei cerinte avem doua cuvinte cheie: vechime si grupare
In primul rand trebuie sa calculam vechimea facturilor si apoi in functie de aceasta sa grupam datele in functie de cele patru categorii cerute.

a. Vechimea: TODAY() - data tranzactiei
Vom crea un nou camp in care vom calcula vechimea facturilor in functie de data curenta.
Practic il vom intreba dl. Excel cat timp a trecut de cand au fost emise facturile pana la data curenta.
In Excel data curenta este obtinuta functia TODAY(), fara nici un parametru.
De asemenea, Excel-ul stie sa faca scaderi intre doua date calendaristice, returnand numarul de zile intre aceste doua date.
Astfel, daca ii spun Excel-ului sa calculeze 01.10.2011-30.09.2011 va returna 1 (o zi).
Asadar in campul vechimea ii voi spune Excel-ului sa calculeze astfel:
TODAY()- data tranzactiei

b. Regruparea datelor
Vom regrupa facturile in functie de vechimea acestora si vom respecta cerinta. Astfel va trebui sa-i spunem Excel-ului:
  • daca vechimea este mai mica sau egala cu 30 de zile atunci factura este recenta
  • daca vechimea este cuprinsa intre 31 si 90 de zile, factura are vechime medie
  • daca vechimea este cuprinsa inte 91 si 180 de zile, factura este veche
  • daca vechimea este peste 181 de zile, factura este foarte veche
Vom folosi functia IF() la fel ca in cazul gruparii pe macro-domenii, insa aici vom avea 4 conditii de pus.
Cum un IF poate verifica o singura conditie, va trebui sa aplicam functia de mai multe ori, si vom traduce "mot-a-mot" conditiile de mai sus.

Dupa cum spuneam sintaxa pentru IF() este:

IF(conditia, rezultat daca se respecta conditia, rezultat daca nu se respecta conditia)


Pentru a putea aplica IF() de mai multe ori, la "rezultat daca nu se respecta conditia" voi introduce in nou IF().
Practic, vom avea o structura de genul:

IF(conditie1, "recente", IF(conditie2, "medii", IF(conditie3,"vechi","foarte vechi")))

In cazul nostru:
  • conditie1: vechime mai mica sau egala cu 30 de zile
  • conditie2: vechime mai mica sau egala cu 90 de zile
  • conditie3: vechime mai mica sau egala cu 180 de zile
Exemplul din fisierul atasat prezita aceasta formula "in actiune".

3. Prezantarea rezultatelor
Dupa ce am regrupat datele dupa criteriile cerute vom putea prezenta rezultatele sintetic, intr-un grafic de tipul:

Un astfel de raport se poate obtine usor cu ajutorul tabelelor pivot. Mai multe despre acestea intr-un aricol viitor.

Fisierul de lucru
Puteti descarca fisierul de lucru de aici.
Aveti modul de aplicare a formulei IF() si raportul cu graficul de mai sus.

Pentru intrebari si sugestii, puteti folosi pagina noastra de Facebook sau comentariile.

Spor!

sâmbătă, 24 septembrie 2011

VLOOKUP - si cautarile nu vor mai fi la fel...

Daca ar fi trebui sa pastrez o singura formula in Excel, atunci VLOOKUP() ar fi aceea.
Odata stapanita aceasta formula lucrul cu mai multe tabele se va simplifica iar productivitatea va creste simtitor.

Fara prea multa teorie, voi incerca sa prezint utilitatea acestei formule pe baza unui exemplu (fictiv).


Date de intrare

Sa presupunem ca am o baza de date cu 500 de clienti de forma:
Denumire client Judet Domeniu de activitate








In acelasi timp am o lista cu vanzarile realizate spre cei 500 de clienti in ultimii 5 ani. In total peste 60 000 de inregistrari sub forma:
Denumire client Data tranzactiei Valoarea tranzactiei







Cerinta

“Care este situatia vanzarilor in fiecare judet, cu detaliu pe domenii de activitate ?”

Termen
Pana maine dimineata, daca se poate!

Rezolvare
Evident aceasta cerinta impune combinarea informatiilor din cele doua tabele astfel incat sa ajungem la o lista de tipul:
Denumire client Data tranzactiei Valoarea tranzactiei Judet Domeniu de activitate










Cu o astfel de lista, datele pot fi regrupate pentru obtinerea rezultatelor cerute.

Insa cum sa combinam cele doua tabele?

In era pre-excel
Inainte sa cunosc Excelul as fi facut ochii cat cepele in prima faza.
Apoi mi-as fi “suflecat manecile” si as fi inceput foarte constiincios sa adaug celor peste 60000 de inregistrari detaliile legate de judet si domeniu de activitate pentru cei 500 de clienti.
Probabil ca dupa vreo saptamana m-as fi apropiat de final... insa termenul de “pana maine dimineata” ar fi fost depasit iar raportul n-ar mai fi avut nici o valoare.

Acum ca avem Excelul, avem VLOOKUP
Vestea foarte buna este ca in astfel de situatii nu e nevoie sa ne “suflecam manecile”: Excelul (sau orice alt program de calcul tabelar) ne pune la dispozitie functia VLOOKUP pentru a ne ajuta in astfel de situatii extreme.

Cu VLOOKUP ii pot spune Excelului :

“Pentru fiecare inregistrare din lista de tranzactii cauta-mi denumirea clientului in baza de date cu clienti si adu-mi de acolo judetul si domeniul de activitate al clientului”

Foarte constiincios, Excelul se va ocupa de acesta cerinta in mai putin de 30 de secunde pentru toate cele peste 60000 de inregistrari. 
Apoi imi va ramane doar sa regrupez datele in functie de cerinta si sa trimit raportul : maxim 1 ora de lucru. Pentru detalii, vezi fisierul atasat acestui articol.


Cum functioneaza VLOOKUP?
Sintaxa acestei functii este urmatoarea (denumirea argumentelor poate varia in functie de programul utilizat, insa ordinea e aceeasi):

VLOOKUP(lookup value, array, index column, aproximative match)

adica:
  • lookup value = valoare dupa care se realizeaza cautarea
  • array = tabelul in care vrei sa faci cautarea
  • index column = pe ce coloana din tabel (array) se afla valoarea cautata
  • aproximative match = se accepta doar potriviri exacte sau si asemanari (pt Excel “Client 11” poate fi asemanator cu “Client 10”). Aici se poate trece 0 sau 1

Pas cu pas in exempul nostru:
  • lookup value = Denumirea clientului, din lista de tranzactii
  • array = Baza de date clienti unde am judetul si domeniul de activitate
  • index column = in exemplul dat cautam doua informatii legate de un client : judetul, care se afla pe coloana 3 si domeniul de activitate care se afla pe coloana 4 din baza de date clienti
  • aproximative match = nu, vrem sa gaseasca clientii in mod corect.

Cateva note legate de VLOOKUP:
  1. Valoarea dupa care se realizeaza cautarea trebuie sa fie pe prima coloana din tabelul in care se face cautarea. In caz contrar nu va functiona
  2. Numarul coloanei pe care se afla rezultatul cautat (judetul, in exemplul nostru) este relativ la tabelul selectat. Adica daca selectez tabelul “B1:D3000” si informatia pe care o caut se afla pe coloana D, atunci index column va fi 3. De ce ? Pai coloana B = prima coloana din tabelul selectat, C= a doua si D = a treia coloana.
  3. Pentru ultimul argument “aproximative match” , eu am folosit 0 in 99% din cazuri.
Aici puteti descarca fisierul care simuleaza exemplul pe care l-am prezentat mai sus , inclusiv aplicarea formulei VLOOKUP.


Pentru intrebari/ sugestii ma puteti contacta pe Facebook sau lasand comentarii pe acest blog.

Ca sa fiu cu adevarat productiv in Excel, vreau sa...

Excel4Us?
Scopul Excel4Us este de a va ajuta sa tranformati Excelul intr-o "arma" foarte puternica in viata de birou: o "arma" in lupta impotriva muncii in zadar. Viata este prea scurta pentru a face calcule de mana! Pana la urma, de ce n-am lasa Excelul sa munceasca pentru noi?

Ce vreau de la "prietenia" mea cu Excel-ul?
0. Vreau sa las cat mai multe in sarcina Excelului :)
1. Vreau sa gasesc rapid informatii din mai multe tabele : VLOOKUP()
2. Vreau sa obtin rezultate in functie de anumite conditii: IF()
3. Vreau sa prelucrez datele calendaristice: DAY(), MONTH(), YEAR()
4. Vreau sa combin siruri de caractere: CONCATENATE()
5. Vreau sa fac adunari in functie de anumite conditii: SUMIF()
6. Vreau sa pot tranforma rapid textul in date utile: Left(), Right(), MID()
7. Vreau sa pot formata siruri de caractere: UPPER(), lower(), Proper()
8. Vreau sa stiu daca doua valori sunt identice: EXACT()
9. Vreau sa pot analiza rapid datele: Tabele Pivot
10. Vreau sa controlez datele introduse: DataValidation

Pentru mine, aceste aptitudini constituie baza productivitatii in lucrul cu tabelele. Adaug aici scurtaturile din taste si viteza creste simtitor.

Articolele acestui blog se vor concentra in jurul listei de mai sus, explicand modul de utilizare a diferitelor functionalitati.

marți, 20 septembrie 2011

Formulele magice: teorie "practica"

Puterea programelor de calcul tabelar (Excel, OpenOffice, etc) vine din faptul ca pun la dispozitie o serie de formule utile in prelucrarea rapida a datelor.

Odata ce vei stapani aceste formule vei putea atinge performante ridicate in prelucrarea datelor si realizarea analizelor.

Desigur, oricand poti apela la un departamentul IT pentru ajutor. Insa este mult mai rapid daca iti poti transpune direct gandurile in formule astfel incat sa ajungi la rezultatul dorit.

Poate parea surprinzator, dar nu este nevoie sa cunosti mai mult de 10-15 formule pe care sa le folosesti in mod curent pentru rezultate remarcabile. Pentru restul exista Help-ul sau "prietenul" Google.

In articolele urmatoare voi prezenta cateva dintre formulele pe care le folosesc frecvent, felul in care le folosesc si de ce le folosesc.

Deocamdata, putina teorie "practica" legata de aceste formule.

1. Formulele transpun in limbaj Excel gandurile noastre
Pentru aceasta, inainte de a folosi o formula, trebuie sa ne fie foarte clar ce anume vrem sa realizam. Ce anume vrem sa "traducem" din romana in "exceliana"?
De exemplu:
"As vrea sa am o suma a tuturor vanzarilor realizate in judetul Sibiu"
Pai atunci trebuie sa-i spun Excelului sa caute doar vanzarile realizate in Sibiu si sa le adune. Deci sa faca o suma numai daca o anumita conditie este indeplinita. Automat ma gandesc la SUMIF (vom detalia aceasta functie foarte utila in articolele urmatoare).

Pornind de la o cerinta foarte clara, putem sa o transpunem fara probleme in Excel. Daca nu avem o cerinta clara, atunci trebuie sa o clarificam inainte de a cauta formule care sa ne ajute

2. Anatomia unei formule
Toate formule din Excel (si din celelalte programe de calcul tabelar) respecta o anumita structura:
NumeFormula (argument1, argument2, ...argumentN) = Rezultat

Practic o formula preia anumite date (argumente) si le transforma in informatii utile, rezultate.
Important de retiunut ca intotdeauna argumentele unei formule se pun intre paranteze rotunde.
De asemenea, daca o formula are mai multe argumente, acestea se despart prin virgula sau punct si virgula (depide de setarile de sistem pe care le aveti)

Am sa dau un exemplu foarte simplu si poate banal, pentru a intelege structura unei formule si felul in care acestea functioneaza:

O moara de macinat cereale. Putem spune ca o astfel de moara are functia (formula) de a MACINA si am putea-o sintetiza astfel:

MACINA (tipul de cereala, granulatie) = Rezultat

Asadar in functie de tipul de cereala si de granulatie, prin macinare vom obtine diferite rezultate:
MACINA (Porumb, fin) = malai
MACINA(Grau, fin) = faina

Departe de mine gandul de a ma considera un expert in morarit; totusi cred ca un astfel de exemplu explica foarte clar structura une formule.


3. Cum inserez o formula?
Odata ce ai decis ca trebuie sa folosesti o anumita formula, inserarea ei in tabel este foarte simpla.
In fapt aveti doua variate:

a. Apasati pe butonul "fx" de pe tool bar. Cautati functia dorita si completati argumenetele ei
b. Intr-o celula scrieti "=" urmat de numele formulei si argumentele acesteia.


In concluzie:
> Formulele ne ajuta sa fim mai buni (cel putin la birou)
> Inainte de a utiliza o formula trebuie sa stim foarte clar ce anume vrem sa obtinem.
> Pentru a avea rezultate, trebuie respectata structura unei formule: numele si argumentele acesteia

In articolele urmatoare voi prezenta cateva formule pe care le consider utile si felul in care le folosesc.

Pana atunci, mult spor!

Iar daca aveti intrebari sau sugestii, nu ezitati sa ne contactati pe Facebook sau sa ne scrieti in comentarii.