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!

Niciun comentariu:

Trimiteți un comentariu