Funcții și formule în Microsoft Excel

În acest tutorial voi vorbi despre funcții și formule în Microsoft Excel.

Funcţiile sunt utilizate pentru efectuarea calculelor specifice aplicaţiei folosind conţinutul unor celule dintr-o foaie de calcul sau din mai multe foi de calcul.

Funcţia este o formulă predefinită (existentă în aplicaţie) identificată printr-un nume şi conţine între paranteze o listă de argumente.

= nume_functie (lista_argumente)

Argumentele pot fi constante (valori numerice sau text), referinţe de celule, referinţe de regiuni de celule (domenii). Există şi funcţii fără argumente (de exemplu funcţia NOW()).

Formulele sunt expresii formate din operanzi (constante şi/sau referiri de celule), operatori matematici (aritmetici şi relaţionali) şi funcţii; formula este precedată de semnul “=”.

Referinţele reprezintă adrese ale celulelor și sunt de 3 feluri:

• referinţe relative
• referinţe absolute
• referinţe mixte

Referința (adresa) relativă este acea adresă de celulă dintr-o formulă, care se modifică atunci când formula este copiată. Exemplu:

=A1+B1

O referință este absolută atunci când formula se copiază sau se mută într-o nouă locație, iar operanzii indică aceleași celule ca în original. Pentru a construi o referință absolută se adaugă semnul dolar ($) înaintea literei şi a numărului ce alcătuiesc adresa celulei. Exemplu:

=$A$1+$B$1

Referința mixtă are fie o coloană absolută şi un rând relativ, fie un rând absolut şi coloană relativă. Exemplu:

=$A1+B$1; ==A$1+$B1

Funcții agreate standard – MAX, MIN, SUM, AVERAGE, COUNT, COUNTA

• MAX(numar1,numar2,…) – returnează cea mai mare valoare dintr-o listă de valori
• MIN(numar1,numar2,…) – returnează cea mai mică valoare dintr-o listă de valori
• SUM(numar1,numar2,…) – adună numerele din zona de celule specificată
• AVERAGE(numar1,numar2,…) – returnează media aritmetică a argumentelor funcției
• COUNT (valoare1; valoare2;…) – determină câte celule conțin numere și câte sunt cuprinse în cadrul listei de argumente
• COUNTA(valoare1,valoare2,…) – numară celulele care nu sunt goale și valorile din lista de argumente.

Pentru exemplificarea felului în care lucrează aceste funcții se consideră următorul tabel cu situația vânzărilor unui anumit produs în primele 6 luni ale anului, în județele specificate.

Funcții logice: IF, AND, OR, NOT

Funcția IF

Funcția IF execută un test logic și returnează o valoare dacă rezultatul testului logic este TRUE și altă valoare dacă rezultatul testului logic este FALSE.

Exemple clasice de utilizare sunt cele legate de evaluarea unui chestionar, de rezultatul unui examen format din mai multe probe, de aplicarea diferențiată a cotei de TVA pentru anumite produse sau servicii etc.

Funcția IF are trei argumente:

IF(logical_test, value_if_true, [value_if_false])

• logical_test – aici introducem o expresie logică (o comparație) și în funcție de valorea ei de adevăr (TRUE/FALSE), funcția IF va returna fie valoarea specificată la al doilea argument (dacă expresia logică returnează TRUE) fie pe cea specificată la al treilea argument (dacă expresia returnează FALSE).
• value_if_true, [value_if_false] – chiar dacă denumirea acestor argumente sugerează introducerea unei valori nu ne vom limita doar la această opțiune. Putem introduce număr, text, sau o expresie (o formulă) pentru fiecare argument în parte.

Funcția IF se poate utiliza ca argument pentru o altă funcție IF, numărul de imbricări permise în Excel 2019 fiind 64.

Considerăm următorul exemplu:

La un examen (test) se înregistrează punctajul obținut. Pentru candidații care au nota peste 7 calificativul să fie “AVANSAT” iar pentru ceilalți candidați calificativul să fie “ÎNCEPĂTOR”

Sintaxa funcției IF este:

IF(nota>=7, “AVANSAT”, “ÎNCEPĂTOR”)

Unde nota este referința celulei ce conține nota candidatului.

Dar dacă dorim o clasificare pe 3 nivele, AVANSAT, MEDIU, ÎNCEPĂTOR?

Vom avea nevoie de 2 funcții IF imbricate.

IF(nota>=7, “AVANSAT”,IF(nota>=5,”MEDIU”, ”ÎNCEPĂTOR”))

Sintaxa de mai sus se interpretează astfel:

Dacă nota depășește 7 atunci categoria este AVANSAT. Dacă nu (adică este mai mică decât 7) și depășește 5 atunci categoria este MEDIU, iar dacă nu (este mai mică decât 5) atunci este în categoria ÎNCEPĂTOR.

Se poate vizualiza construcția funcției IF, mult mai ușor, folosind o schemă de genul:

Funcția AND

Se folosește pentru evaluarea mai multor condiții logice și returnează TRUE dacă toate argumentele sale sunt TRUE. Returnează FALSE dacă unul sau mai multe argumente sunt FALSE.

Funcția OR

Se folosește pentru evaluarea mai multor condiții logice și returnează TRUE dacă cel puţin un argument este TRUE; întoarce FALSE dacă toate argumentele sunt FALSE.

Funcția NOT

Are ca argument o condiție sau o expresie logică și schimbă valoarea argumentului într-o valoare opusă (dacă argumentul era TRUE, funcția returnează FALSE și reciproc).

Funcții gestionare text

Funcțiile LEFT, MID, RIGHT

Cele trei funcții sunt utilizate pentru extragerea unui număr de caractere dintr-un text.

• LEFT(text; [nr_caractere]) – funcția extrage dintr-un text primele n caractere, unde n reprezintă un număr. Are două argumente, al doilea fiind opțional, în sensul că are o valoare implicită asociată, și anume, 1. Astfel, dacă folosiți funcția cu sintaxa LEFT(„Diana”) veți obține inițiala „D„, iar dacă o folosiți ca LEFT(„Diana”,2) veți obține „Di„.
• RIGHT(text; [nr_caractere]) – funcția extrage dintr-un text ultimele “n” caractere, unde n reprezintă un număr. Similar funcției LEFT, funcția RIGHT are al doilea argument opțional (cu valoarea impicită 1). Astfel, dacă folosiți funcția cu sintaxa RIGHT(„ABC”) veți obține „C„, iar dacă o folosiți ca RIGHT(„ABC”,2) veți obține „BC„.
• MID(text; num_start; nr_caractere) – funcția întoarce un anumit număr de caractere dintr-un șir de text, începând din poziția specificată, pe baza numărului de caractere specificat. Să zicem că dorim să extragem caracterele care corespund lunii dintr-un CNP (de exemplu, 1881210111111). Funcția noastră va arăta așa: MID(„1881210111111”,4,2) și înseamnă „de la al patrulea caracter, inclusiv, extrage 2 caractere). Vom obține „12„.

Funcțiile UPPER, PROPER, LOWER

Se pune întrebarea, de multe ori, dacă există o modalitate de transformare a textului în majuscule la fel ca în Word. Da, există, dar nu ca în Word, unde combinația de taste SHIFT+F3 trece textul selectat din majuscule în text scris cu litere mici, în formatul cu prima literă majusculă și invers.

Cu toate că Excel nu este un program de prelucrare text ci unul de calcul tabelar, există și pentru această operație funcții:

• Funcția UPPER(text) – convertește textul în text cu majuscule.
• Funcția PROPER(text) -va scrie textul cu inițiala majusculă.
• Funcția LOWER(text) – va scrie textul cu litere mici.

Trebuie să aplicăm funcția într-o altă celulă decât cea care conține textul. Dacă, să zicem, avem
o coloană cu numele și prenumele unor persoane și dorim să le scriem cu majuscule, aplicăm, într-o altă coloană funcția UPPER, copiem coloana cu rezultatele funcției, folosim apoi peste coloana
inițială, comanda Paste Special cu opțiunea Value și ștergem coloana cu funcția.

Funcțiile LEN, TRIM, TEXT, CLEAN, CONCATENATE, VALUE

Informațiile de tip text sunt printre cele mai greu de prelucrat. Ați obținut de multe ori eroarea

N/A când ați aplicat funcția VLOOKUP pe o coloană cu text?

Ați făcut suma unei coloane cu „numere” și ați obținut valoarea 0 (zero)? Ați aplicat funcția
MONTH unei celule care părea a fi o dată calendaristică și ați obținut o eroare de tip #VALUE?

De ce se întâmplă aceste lucruri?

Numerele sau datele calendaristice au fost stocate ca text, sau cele două texte comparate de
Vlookup diferă printr-un spațiu.

Înainte de toate să vedem cum se compară două șiruri de caractere.

Două șiruri de caractere sunt egale (identice) dacă au exact aceleași caractere pe aceleași poziții.
Un simplu spațiu în plus înseamnă text diferit.

Pentru gestionarea acestei situații Excel pune la dispoziție o serie de funcții de tip text și
instrumentul Text to Columns.

Printre cele mai utilizate funcții de prelucrare a informațiilor de tip text sunt:

• LEN(text) – funcția se aplică unui text și returnează numărul de caractere al acestuia. Puteți verifica dacă un text conține spații în plus.

• TRIM(text) – funcția elimină spațiile în plus (spațiile dinainte, de după și, dacă între cuvinte sunt mai multe spații, păstrează doar unul).
• TEXT(value, format_text) – este utilă în situația în care doriți să exportați datele din Excel într-o aplicație care necesită informația în format text. În cazul în care modificați formatul celulei care conține, spre exemplu, o dată calendaristică veți observa că nu se păstrează formatul (să zicem dd.mm.yyyy) ci se afișează numărul asociat acelei date calendaristice. Pentru al doilea argument al funcției va trebui să introduceți, între ghilimele, masca pentru formatul umărului. Pentru a păstra și formatul datei, vom transforma data respectivă în text utilizând funcția ca în imagine:

• CLEAN(text) – Este similară funcției Trim dar elimină caracterele neimprimabile din text (informații despre codul Ascii și despre codurile caracterelor “neimprimabile” găsiți la adresa: http://www.asciitable.com/)
• VALUE(text) – Face conversia într-un număr a unui text care reprezintă un număr.
• CONCATENATE(text1, [text2], …) – alătură două sau mai multe șiruri de text într-un singur șir.

Funcții agregate condiționale COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS

Funcțiile enumerate mai sus, chiar dacă în Excel sunt prezentate în categorii diferite (statistice,
matematice) au caracteristici și acțiuni similare. Singura diferență constă în operația pe care o
efectuează (count-numărare, sum-sumă, average-media aritmetică).

Ați folosit de multe ori funcția SUM, spre exemplu, pentru a calcula totalul valorilor înscrise pe
o coloană. Dar, dacă nu dorim să calculăm totalul pe toată coloana ci doar pentru acele valori care
corespund unor informații aflate pe o altă coloană?

În această situație aplicăm un “filtru”. Ce înseamnă un filtru? O condiție logică, pe care o
impunem unui set de date pentru care dorim să păstrăm doar informațiile care respectă condiția
impusă.

COUNTIF(range, criteria)

• range – zona din tabelul sursă căreia îi impunem o condiție de filtrare (poate să fie denumirea unei zone).
• criteria – condiția pe care va trebui să o îndeplinească zona selectată și pentru care se face numărarea.

Criteriile pentru funcțiile din această categorie se construiesc sub formă de expresie și conțin
operatorul logic (de comparație) și valoarea cu care se compară. Dacă se face comparația prin
egalitate, operatorul “=” (egal cu), nu se scrie. Expresia se introduce între ghilimele.

Exemple de criterii:

”>100” mai mare decât 100, “Ionescu” egal Ionescu, “<>P5” diferit de P5.

Dacă am scris criteriul într-o celulă, atunci se va folosi adresa celulei respective.

Să considerăm următorul exemplu:

Avem un tabel în care am înregistrat o serie de comenzi ale unor produse, comenzi efectuate de diferiți clienți. Dorim să numărăm, pentru fiecare client, câte comenzi a efectuat (adică de câte ori apare înregistrat pe coloana client), care este valoarea totală, per client, a produselor comandate, și care este cantitatea medie comandată de fiecare client.

Pentru prima cerință, vom folosi funcția COUNTIF. Argumentul range îl reprezintă coloana client din tabelul sursă, criteriul pentru numărul de comenzi il reprezintă, de fapt, apariția codului unui client anume în listă.

Formula va fi:

Countif(c2:c149,”Client1”)

Aceasta numără de câte ori apare numele Client1 în coloana cu codurile clienților (această apariție semnifică faptul că acel client a făcut o comandă).

Funcțiile SUMIF și AVERAGEIF

Funcțiile SUMIF și AVERAGEIF au un al treilea argument, opțional, și anume, celulele (zona), pentru care se calculează efectiv suma / media.

SUMIF(range, criteria,[sum_range]), respectiv
AVERAGEIF(range, criteria, [average_range])

De ce este necesar? Dacă impunem condiția de filtrare pe coloana Client, dorim să adunăm valorile corespunzătoare acelui client, de pe coloana Total (euro).

Dacă, însă, aplicăm condiția și operația de adunare pe aceeași coloană Total (euro), al treilea argument nu mai este necesar. Spre exemplu, dorim să adunăm toate valorile mai mari decât 100.

Pentru funcțiile SUMIF și AVERAGEIF, coloana pe care aplicăm filtrul și filtrul, sunt aceleași ca pentru COUNTIF dar va trebui să specificăm, pe ce coloane din dreptul valorilor valide, să se calculeze efectiv suma / media.

La SUMIF pe coloana Total

ar la AVERAGEIF pe coloana Cantitate.

Într-un tabel Excel putem aplica mai multe filtre. Să zicem, vânzările produsului X pe luna Y, necesită două filtre: unul pe coloana cu produse (să fie produsul X), și unul pe coloana cu date calendaristice (să fie doar date din luna Y).

Dacă dorim să numărăm câte vânzări ale produsului X s-au înregistrat în luna Y, sau să calculăm totalul încasărilor sau cantitatea medie pentru perechea specificată, vom folosi cele trei funcții: COUNTIFS, SUMIFS și AVERAGEIFS.

Acestea sunt similare cu funcțiile COUNTIF, SUMIF, AVERAGEIF, cu deosebirea că putem pune condiții de filtrare pe mai multe coloane și coloana pe care se efectuează operația (pentru funcțiile SUMIF și AVERGEIF) nu mai este opțională, ea trebuie specificată.

Sintaxele sunt următoarele:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

În exemplul prezentat anterior am analizat doar vânzările pe client.

Să facem acum o analiză similară pe client, detaliat pe produs, și anume: câte comenzi a efectuat fiecare client pentru fiecare produs, ce cantitate medie a comandat din fiecare produs și care a fost valoarea totală a comenzilor pentru fiecare produs.

Pentru a număra comenzile fiecărui client, pe fiecare produs, vom organiza tabelul de analiză astfel:
Lista clienților ca și cap de rând, lista produselor, cap de coloană (sau invers).

Putem, de asemenea, să creăm două liste de tip Data Validation și, în funcție de alegerea pe care o facem, să se afișeze informația asociată perechii alese (Client-Produs).

În figura de mai jos, în sintaxa funcției Countifs, client este numele zonei C2:C149 (coloana cu clienți) din tabelul sursă, iar Produs este coloana D2:D149 din tabelul sursă (coloana cu denumirile produselor).

Sintaxa funcției:

=COUNTIFS(client,$B3,produs,C$2)

Se interpretează astfel:

“Numără dacă pe coloana client am clientul din celula B3 și pe coloana produs am produsul din celula C2”

În mod analog, pentru SUMIFS:

“Adună valorile de pe coloana total_euro pentru clientul din M3 și produsul din celula N2”

Funcții de tip DATE & TIME – DATE, TIME, TODAY, NOW, MONTH, YEAR, WORKDAY, NETWORKDAYS, WEEKDAY, WEEKNUM, DAY

Funcția DATE permite compunerea unei date calendaristice din elemente introduse separat.

Spre exemplu, dacă pe trei coloane diferite avem introduse ziua, luna (ca număr) și anul, funcția DATE compune data calendaristică corespunzătoare.

În mod analog, funcția TIME formează o dată de tip Time din elemente componente scrise separat:

Funcțiile TODAY, NOW, MONTH, YEAR, DAY

Funcția TODAY() returnează data, iar funcția NOW() returnează data și ora.

Funcția MONTH(număr_serial) returnează luna datei calendaristice. Luna este dată ca un
întreg, cuprins între 1 (ianuarie) și 12 (decembrie).

Funcția YEAR(număr_serial) returnează anul corespunzător unei date calendaristice.

Funcția DAY(serial_number) extrage ziua dintr-o dată calendaristică (din serial_number), sub
forma unui număr din intervalul [1,31].

Funcțiile WORKDAY și NETWORKDAYS

Pentru a calcula numărul de zile dintre două date calendaristice sau data de peste un număr de
zile, folosim operațiile aritmetice de adunare și scădere.

Dar dacă dorim să aflăm data de peste 20 de zile lucrătoare sau numărul de zile lucrătoare între
două date calendaristice?

Pentru aceasta vom folosi cele două funcții menționate. Ambele necesită, însă, o listă cu
sărbătorile legale. Dacă nu avem o asemenea listă, funcțiile vor elimina din calcul doar zilele de
weekend.

Funcția WORKDAY returnează data de peste un număr de zile lucrătoare (atenție, nu se include
în numărătoare și data de început).

WORKDAY(data de inceput; nr. zile lucratoare; [lista zilelor nelucratoare])

Funcția NETWORKDAYS calulează numărul de zile lucrătoare dintre două date calendaristice.

Sintaxa funcției este:

NETWORKDAYS(data de inceput; data de sfarsit; [lista zilelor nelucratoare])

Sfat: Pentru sărbătorile legale puteți crea o listă de tip Custom (atenție, se stochează ca text, nu ca număr) căreia îi puteți da un nume sugestiv (spre exemplu, SL21, pentru sărbătorile legale din 2021) și le puteți introduce în orice foaie de calcul din orice fișier, ori de câte ori aveți nevoie. Nu uitați, inainte de a le folosi în cele 2 funcții, să le transformați din text în dată calendaristică. Altfel, funcțiile WORKDAY și NETWORKDAYS vor returna eroarea #VALUE care apare în cazul în care folosiți un alt tip de dată decât cea necesară unui argument (text în loc de dată sau număr).

Pentru crearea listei de tip Custom, dacă vă este mai ușor, puteți introduce datele sărbătorilor legale direct în Excel și le transformați în text folosind funcția TEXT cu sintaxa ca în imagine.

Argumentul al doilea reprezintă formatul (masca) numărului care va fi transfomat în text și va fi afișat ca atare. Dacă folosiți doar Format Cells – Text, data calendaristică nu-și păstrează formatul ci va fi afișat numărul asociat.

Copiați apoi rezultatul funcției TEXT și cu Paste Special, opțiunea Value and number format, păstrați doar valoarea și formatul rezultatului și îl suprascrieți peste datele inițiale.
Importați apoi lista cu sărbători legale (inclusiv titlul coloanei SL21) în colecția cu liste personalizate (Custom List), și în orice fișier, dacă veți scrie într-o celulă SL21 (cu litere mari sau mici) și utilizați Autofill, veți completa lista sărbătorilor legale (ca text).

Le transformați în date calendaristice cu instrumentul Text to Columns, folosiți apoi funcția DATE și le utilizați ca argumente în sintaxa funcțiilor WORKDAY și NETWORKDAYS.

Funcțiile WEEKDAY și WEEKNUM

Ambele funcții au două argumente: data și modul în care se face numărătoarea zilelor din săptămână.

Funcția WEEKDAY returnează numărul zilei din săptămână (de la 1 la 7 sau de la 0 la 6, în funcție de opțiunea aleasă la al doilea argument) iar funcția WEEKNUM returnează numărul săptămânii în an.

O modalitate rapidă de evidențiere a zilelor de weekend se poate realiza cu funcția WEEKDAY.

Dacă avem o listă de date calendaristice, fie ea orizontală sau verticală, selectăm întreaga listă și, în fila HOME – grupul Styles – din meniul Conditional Formatting – alegem New Rule:

În fereastra New Rule alegem opțiunea „Use a formula to determine which cells to format”
și, în caseta „Format values where this formula is true” scriem formula:

=WEEKDAY(D3;2)>5

În D3 am scrisă prima dată calendaristică din lista selectată, argumentul al doilea (2) numără cu 1 ziua de luni și cu 7 pe cea de duminică, iar condiția”>5″ întoarce valoarea TRUE doar pentru ziua 6 (sâmbătă) sau 7 (duminică).

După ce am aplicat formatul condiționat, putem, cu ușurință, să filtrăm coloana cu date după culoare și să vizualizăm doar zilele de weekend.

Funcții de căutare – VLOOKUP, HLOOKUP, MATCH, INDEX

Funcția VLOOKUP

Funcția VLOOKUP este una dintre cele mai populare funcții de căutare.

Care sunt elementele necesare pentru utilizarea funcției și când este necesară apelarea ei?

În primul rând, avem un tabel sau o zonă de date formată din coloane și rânduri (o matrice).

Dorim să căutăm o informație pe (Atenție!) prima coloană a acestei zone și să obținem o informație aflată pe același rând, de pe o altă coloană.

Utilizăm pentru aceasta funcția VLOOKUP (căutare verticală, de acolo vine denumirea).

Considerăm următorul tabel al angajaților unei firme și dorim să aflăm departamentul din care face parte angajatul MIHAI Dinu. Cum procedăm?

Identificăm argumentele funcției:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value (ce caut?) – Îl căutăm pe MIHAI Dinu.
• table_array (în ce zonă?) – Aici selectăm zona (întregul tabel) care conține numele angajatului pe prima coloană.
• col_index_num (ce vreau să obțin?) – Vreau să obțin departamentul și va trebui să trecem numărul de ordine al coloanei din zona selectată de pe care doresc să obțin răspunsul. În cazul nostru, 4. Coloana a patra a tabelului nostru conține departamentul.
• [range_lookup] (cât de exactă să fie căutarea?) – Acest argument este unul special și reprezintă o alegere pe care o facem și în funcție de care, obținem un rezultat conform așteptărilor noastre.

Argumentul poate avea valoarea FALSE (sau 0-zero) dacă ne dorim o căutare exactă și True (sau 1) dacă dorim o căutare cu aproximare prin lipsă.

Ce reprezintă fiecare situație?

• Când alegem FALSE, funcția noastră va căuta angajatul MIHAI Dinu pe prima coloană a tabelului și, dacă nu-l găsește, va returna mesajul de eroare #N/A (Not Available).
• Pentru căutarea cu aproximare (argumentul TRUE) trebuie să sortăm mai întâi tabelul după prima coloană (cea în care se face căutarea), în ordine ascendentă (alfabetic pentru text și crescător pentru numere). Dacă nu este găsită informația pe care o căutăm, este aproximată această informație cu valoarea din prima coloană cea mai apropiată, aflată înaintea celei căutate.

Mai exact, dacă nu este găsit MIHAI Dinu și există în tabel o persoană MIHAI Carmen, sunt returnate informații despre această persoană. În cazul problemei noastre se potrivește opțiunea de căutare exactă.

Dar să revenim la exemplul nostru: la ce departament lucrează MIHAI Dinu?

Vom aplica funcția Vlookup astfel:

VLOOKUP("MIHAI Dinu",C1:F22,4,FALSE)

Dacă avem o listă de persoane pentru care dorim să aflăm departamentul din care fac parte, trebuie să „blocăm” al doilea argument (dacă nu folosim referința absolută, la copierea formulei, toate argumentele se actualizează păstrând poziția inițială în care s-au aflat față de formulă).

VLOOKUP cu aproximare

Să spunem că, în exemplul de mai jos, în sheet-ul 1 ai nevoie pe coloana E să îți aducă valoarea din sheet-ul 2 de pe coloana D în funcție de intervalul în care se încadrează de pe coloanele B (minimum limit) și C (maximum limit).

Cum faci asta?

=VLOOKUP(D6,'2'!$B$3:$D$9,3)

Pe coloana cu limite, din foaia 2, se trece doar limita inferioară a intervalului (nu mai este nevoie de coloana cu maxim). De ce? Pentru că funcția VLOOKUP, cu aproximare (cu ultimul argument TRUE sau 1, sau deloc – este implicit) caută un număr într-o listă, încadrându-l în intervale de tipul [min,max)

Deci, dacă completezi pe două coloane informația sub forma:

Când cauți 7 pe prima coloană, cu VLOOKUP, el nu găsește valoarea 7 și atunci îți aproximează cu valoarea (din lista 0, 20, 30) cea mai apropiată de 7, dar mai mică decât ea, și anume, cu 0.

Va returna de pe cealaltă coloană, corespondentul din dreptul lui 0 și anume, a.
Deci, pentru toate valorile pe care le cauți, între 0 și 19,999999 inclusiv, vei obține a.
Pentru valorile între 20 și 29.9999999 inclusiv, vei obține b.
Și pentru intervalul [30,+∞), vei obține c.

Funcția HLOOKUP

Funcția HLOOKUP este o funcție care face o căutare pe orizontală în primul rând al zonei selectate și returnează valoarea de pe aceeași coloană din oricare rând al selecției. HLOOKUP se foloseste când valorile de comparare se află într-un rând în partea de sus a unui tabel și doriți să căutați în jos un anumit număr de rânduri.

Sintaxa funcției este:

HLOOKUP(Lookup_value; Table_array; Row_index_num; [Range_lookup])

• Lookup_value sau căutare valoare – este câmp obligatoriu și trebuie completat cu valoarea ce trebuie căutată în primul rând al zonei selectate;
• Table_array sau matrice_tabel – este câmp obligatoriu și reprezintă zona de celule în care se va face căutarea după primul rând și din care se va returna rezultatul;
• Row_index_num sau num_index_rând – câmp obligatoriu și reprezintă numărul rândului din zona de celule selectate din care va fi returnat rezultatul. Dacă zona selectată conține rândurile 2,3,4 și 5 și se dorește returnarea rezultatelor din rândul 3, atunci la Row_index_num se va trece 2, pentru rândul 3 este al doilea rând al selecției;
• Range_lookup sau zonă căutare – este un câmp opțional. Valoarea TRUE pentru acest câmp (sau necompletarea câmpului) presupune că se face o căutare aproximativă, valoarea FALSE duce la o căutare exactă.

Exemplu – căutarea stocului de stilouri

Funcția INDEX

Funcția INDEX, are două variante de utilizare.

În formatul standard, returnează o informație dintr-o matrice (“tabel”, zonă dreptunghiulară de
date), aflată la intersecția unui rând cu o coloană (al căror număr de ordine trebuie să-l specificăm în sintaxa funcției).

Sintaxa funcției este următoarea:

INDEX(tabel-zona în care caut,[numărul rândului],[numărul coloanei])

Semnificația argumentelor:

• tabel–zona în care caut – zona dreptunghiulară (poate fi și un singur rând sau o singură coloană)
• numărul rândului – argument opțional, reprezintă numărul de ordine al rândului din tabelul selectat pe care se află informația pe care doresc să o extrag. Nu se scrie dacă primul argument are un singur rând.
• numărul coloanei – argument opțional, reprezintă numărul de ordine al coloanei din tabelul selectat pe care se află informația pe care doresc să o extrag. Nu se scrie dacă primul argument are o singură coloană.

În exemplul de mai jos, vom căuta informația din tabelul (zona) selectat, aflată la intersecția celui
de-al doilea rând cu a treia coloană.

O altă utilizare a funcției este cea cu referințe.

Ce înseamnă acest lucru?

Dorim să căutam o informație într-o zonă care nu este formată din celule adiacente (în zone
disparate, aflate în aceeași foaie de calcul).

În acest caz, sintaxa funcției este următoarea:

INDEX(zonele în care caut, [numărul rândului],[numărul coloanei],[numărul zonei])

Semnificație argumente:

• zonele în care caut – în cazul în care sunt mai multe zone, se trec între paranteze rotunde.
• numărul rândului – argument opțional, reprezintă numărul de ordine al rândului pe care se află informația pe care doresc să o extrag. Nu se scrie dacă zona din care se extrag datele are un singur rând (are valoarea implicită 1).
• numărul coloanei – argument opțional, reprezintă numărul de ordine al coloanei pe care se află informația pe care doresc să o extrag. Nu se scrie dacă zona din care extrag datele are o singură coloană (are valoarea implicită 1).
• numărul zonei – argument opțional, reprezintă numărul de ordine al zonei (tabelului) în care se află informația pe care doresc să o extrag. Nu se scrie dacă dorim extragerea din zona 1 (are valoarea implicită 1).

În imaginea de mai jos, este utilizată funcția INDEX pentru extragerea informației de pe al doilea rând și a treia coloană a zonei 2.

Funcția MATCH

Funcția MATCH caută o valoare (expresie, conținutul unei celule) într-un șir liniar (rând sau coloană) și returnează numărul de ordine al acestuia.

MATCH are următoarea sintaxă:

MATCH(valoare căutată, vectorul în care caut, cum se face căutarea)

Semnificație argumente:

• valoare căutată – poate fi un text (între ghilimele), un număr, sau o referință de celulă
• vectorul în care caut – obligatoriu un șir liniar de celule (un rând sau o coloană)
• cum se face căutarea – argument opțional, poate fi 1, 0 sau -1.

Dacă alegeți 1 (sau omiteți al treilea argument) căutarea se va face cu aproximare la valoarea aflată imediat înaintea celei căutate (aproximare prin lipsă). Șirul în care se face căutarea trebuie să fie, în acest caz, ordonat crescător.

Alegeți 0 (zero), dacă doriți să faceți o căutare exactă și -1 dacă doriți să faceți căutarea cu aproximare la valoarea imediat mai mare decât cea căutată (aproximare prin adaos), caz în care, șirul în care se face căutarea trebuie să fie ordonat descrescător.

Exemple:

Semnificația sintaxei funcției MATCH este:

Caut conținutul celulei B14 (codul CA0001) în coloana D3:D9 (Cod Produs) în mod exact, în sensul: dacă se găsește codul, dă-mi poziția lui în șir, dacă nu, atunci obțin N/A (Not Available).

Folosim, pentru acestă situație, funcția MATCH cu aproximare prin lipsă.

Cum valoarea 6 are în listă două valori mai mici decât ea (0 și 5), va fi aproximată la cea mai apropiată valoare (adică la 5). Astfel, pentru toate valorile de la 0 la 4 obținem poziția 1 (corespunzătoare capătului inferior al intervalului, adică 0), pentru valori de la 5 la 9 obținem 2 (poziția lui 5), etc. Întrucât în Excel, ca și în modul nostru natural de a face o căutare, se caută de sus în jos, și de la stânga la dreapta, șirul în care se face căutarea va trebui să fie ordonat crescător (de sus în jos pentru coloane și de la stânga la dreapta pentru rânduri).

În cazul vechimilor, la aceeași căutare, de data aceasta în șirul 25,15,10,5,0, vom obține poziția lui 10, adică 3.

Funcția este foarte utilă în combinație cu INDEX.

Cum procedez dacă vreau să caut pe coloana a treia și să obțin o informație din coloana 1 sau dacă vreau să caut pe rândul 15 și să obțin o informație de pe rândul 3?

Folosesc combinația de funcții: Index cu Match.

Să vedem întâi ce face fiecare.

Funcția Index, are două variante de utilizare.

În formatul standard, returnează o informație dintr-o matrice (“tabel”, zonă dreptunghiulară de date), aflată la intersecția unui rând cu o coloană (al căror număr de ordine trebuie să-l specificăm în definiția funcției).

În exemplul de mai jos, vom căuta informația din tabelul (zona) selectat, aflată la intersecția celui de-al doilea rând cu a treia coloană.

În practică însă, nu cunoaștem cu exactitate nici numărul rândului și nici al coloanei de pe care dorim să extragem o informație, fără să facem căutări și numărări “manuale”.

Avem însă o funcție care “numără” în locul nostru: funcția MATCH prezentată anterior.
Dorim să găsim prețul produselor listate în al doilea tabel din imagine.Va trebui să căutăm codul produsului în a treia coloană a primului tabel, și să ne returneze prețul, aflat pe a doua coloană.
Rețineți faptul că, la căutarea pe o coloană, determinăm numărul rândului pe care se află informația noastră în coloana respectivă, iar la căutarea pe rând obținem numărul coloanei.

Căutăm mai întâi codul produsului pe coloana cu Cod Produs utilizând funcția MATCH care are următoarea sintaxă prezentată anterior:

MATCH(valoare căutată, vectorul în care caut, cum se face căutarea)

În cazul nostru, valoarea căutată este cea din celula B14 (CA0001), vectorul în care caut este D3:D9 (coloana tabelului cu coduri de produse, inclusiv celula de antet) și căutarea o dorim, în acest caz, exactă (alegem ultimul argument 0).

Funcția ne va returna 5 (a câta în listă este valoarea căutată), număr care va fi argumentul al doilea al funcției INDEX și anume, numărul rândului din tabel de pe care doresc să extrag o informație.

Bun, știm de pe ce rând extragem informația, dar, de pe ce coloană? De pe coloana cu prețuri.

Folosim din nou funcția MATCH, pentru a căuta denumirea “Pret” aflată în celula C13 în lista B3:F3 (antetul tabelului). Ne va returna 2 (numărul coloanei de pe care dorim un răspuns), și-l vom folosi ca al treilea argument al funcției INDEX.

Atenție la adresele relative și absolute din formulă (ce anume fixăm).