eBook - Arsenal de Ninja

Cuprins

Introducere . ...................................................................................................................... 3

Ce sunt formulele de fapt . ................................................................................................ 4

Tasta F4 in Excel . ............................................................................................................... 7

Functii aritmetice de baza: SUM, AVERAGE, COUNT, MIN, MAX . ................................. 10

Functii aritmetice conditionale: COUNTIF, SUMIF, AVERAGEIF, SUMIFS . ..................... 13

Functii gestionare text: PROPER, LEFT, RIGHT, SEARCH, LEN, CONCATENATE ............. 18

Functii logice: IF, AND, OR . ............................................................................................. 21

Functii de cautare: VLOOKUP . ........................................................................................ 24

Functii de cautare: INDEX, MATCH ................................................................................. 27

Alte functii: IFERROR . ...................................................................................................... 30

Autor ................................................................................................................................ 32

http://www.excelninja.ro

http://www.romcom.ro/



Ce sunt formulele de fapt

Incep acest eBook raspunzand la intrebarea ce sunt formulele in Excel. Fac asta pentru

ca nu de multe ori la cursurile pe care le tin vad multe persoane pentru care formule in

Excel = SUM(A1:A5).

Formule si masini de tocat carne

Formulele sunt expresii care calculeaza valoarea unei celule si au multe lucruri in comun

cu o masina de tocat carne:

Intrari: dupa cum carnea pe care o introducem in masina influenteaza produsul

finit, tot asa si intrarile vor influenta rezultatul unei formule

Un mecanism: daca vrem carnea taiata marunt folosim un anumit tip de cutit, in

acelasi fel, in functie de ce vrem sa facem vom folosi anumite formule

Iesiri: si aici putem face paralela, in functie de „cutitul” pe care il vom alege, vom

avea un anumit output

Formule, functii, variabile, constante, operatori

Nu te-ai intrebat niciodata care e diferenta dintre functie si formula? sau ce alte lucruri

mai sunt incluse in scrierea formulelor?

Da-mi voie sa iti dau raspunsul meu printr-un exemplu simplu:

= (SUM(A1:A5)+100)/2+B2

SUM(A1:A5) – functie, putem observa ca are un nume si anumite argumente in

paranteza

A1:A5, B2 – variabile, spunem ca sunt variabile pentru ca in cazul in care

modificam valorile din aceste celule rezultatul formulei se schimba

100, 2 – constante

+, / – operatori

= (SUM(A1:A5)+100)/2+B2 – formula, adica toate de mai sus puse cap la cap



Cum scriem formulele

Formulele se scriu dupa =, daca din anumite motive ai relatii mai tulburate cu tasta egal,

poti arunca un click pe f(x), vezi imaginea de mai jos.

Unde gasesc o functie?

Radu, caut o functie care sa imi faca media dintre anumite valori dar

nu mai stiu cum se numeste, parca incepe cu av, cum fac sa o gasesc?

Foarte buna intrebarea, avem mai multe posibilitati:

1. Scriem =av intr-o celula si vedem ca apar cateva variante

2. Putem vedea in tab-ul formulas toate formulele puse pe categorii; daca de

exemplu stim ca formula noastra e legata de operatii logice, vom cauta in

sectiunea Logical, destul de logic nu?



3. Dupa un click pe F(x) vedem imaginea de mai jos, scriem ce vrem sa facem si Excel

ne da o solutie

Bun, cred ca am vorbit destul despre formule la modul general, haideti sa trecem la

exemple.

Setari de limba - important

Inainte sa ne apucam de treaba, vreau sa iti spun un lucru important despre functii:

modul de scriere a lor variaza in functie de setarile de limba de la calculator.

Mai sus poti vedea un exemplu al unei functii. VLOOKUP, ca si multe alte functii, are mai

multe argumente (lookup value, table array, etc.). Separarea dintre argumente se face

la mine prin ,(virgula) pentru ca laptop-ul meu e setat pe limba engleza. La tine

separarea e posibil sa se faca prin ;(punct si virgula). Ca sa stii sigur iti recomand sa scrii

o formula si sa vezi prin ce sunt separate argumentele la tine.

Asta ca sa stii de ce iti apar erori la formulele scrise de mine.



Tasta F4 in Excel

Un lucru pe care probabil il folosesti in Excel e multiplicarea formulelor. In acest capitol

vom incerca sa deslusim taina dupa care Excel ne multiplica formulele atunci cand

tragem in jos de coltul din dreapta jos a unei celule (vezi imaginea de mai jos).’

Luam imaginea de mai sus ca si exemplu. Avem 2 coloane asupra carora vrem sa facem

urmatoarele operatii:

A+B

A+B2 (adaugam la coloana A valoarea din B2)

A2+B (adaugam la coloana B valoarea din A2)

A2+B2 (facem suma dintre A2 si B2 pe toata coloana)

A+B

Dupa ce scriem pe prima coloana formula foarte complicata

A2+B2, dupa ce multiplicam formula vom vedea ca

formulele din tabel arata ca si in imaginea alaturata.

Probabil ca acest scenariu e cel mai comun dar si cel mai

facil.



A+B2

Aici lucrurile se complica putin, nu putem proceda ca si in cazul anterior. Vrem cumva

ca atunci cand multiplicam formula B2 sa ramana “inghetat” .

Cum facem asta?

Foarte buna intrebarea, raspunsul e unul simplu, tasta F4. Adica? Hai sa luam de

exemplu, formula de mai jos:

=A1+B2

Daca vrem sa “inghetam” B2 tot ce trebuie sa facem e ca dupa ce scriem formula sa

selectam B2 din formula si sa apasam F4. Dupa ce facem asta formula va arata in felul

urmator:

=A2+$B$2

Dupa ce am apasat F4 vedem ca Excel a adaugat semnul $ in fata lui B si a lui 2. Dupa ce

am facut asta, trebuie doar sa multiplicam formula pana jos si suntem gata.

A2+B

Similar cu situatia de mai sus, numai ca de data asta selectam A2 atunci cand scriem

formula si apasam F4 si formula va arata in felul urmator:

=$A$2+B2

A2+B2

In aceasta ultima situatie atunci cand scriem formula selectam atat A2 cat si B2 si

apasam F4, formula va arata asa:

=$A$2+$B$2



Taina dolarului :)

Eu v-am aratat in exemplul de mai sus un singur mod de a insera simbolul $ in formula.

Sunt de fapt 4 moduri in care putem folosi simbolul $:

A1 – fara vreun simbol $: atunci cand multiplicam in jos formula creste valoarea

numerica (A2,A3,..), cand multiplicam in dreapta creste valoarea literei (B1,C1,…)

$A1 – cand multiplicam formula in jos, pe coloana, valoarea numerica creste,

cand multiplicam in dreapta nu se intampla nimic pentru ca am pus $ langa A

A$1 – cand multiplicam formula in jos, pe coloana, nu se intampla nimic pentru

ca am pus $ langa numar; daca multiplicam in dreapta creste valoarea literei

(B1,C1, …)

$A$1 – fie ca multiplicam in jos pe coloana, fie ca multiplicam in dreapta, pentru

ca am pus $ atat in fata lui A cat si in fata lui 1 formula ramane neschimbata

In concluzie

Atunci cand facem referinta la o celula o scriem in felul urmator: A1. Atunci cand

adaugam semnul $ in stanga literei sau cifrei, asta face ca acel lucru sa ramana fix atunci

cand multiplicam formula.

E randul tau

Daca nu ai facut asta deja, incearca si tu tasta F4 in fisierul cu acelasi nume ca si acest

articol.



Functii aritmetice de baza: SUM, AVERAGE,

COUNT, MIN, MAX

Eu nu cred ca atunci cand pleci cu masina de pe loc incepi cu viteza a 5-a. O luam si noi

cu viteza 1 la formule ca sa nu ne moara motorul, incepem cu formule aritmetice de

baza.

„Radu eu le stiu pe astea”

E foarte posibil ca tu sa intelegi aceste formule, stai linistit ca am lucruri mai complexe

si pentru tine. Nu toti sunt la fel de ninja ca si tine :).

Tocmai ne-am apucat de productie

Suntem parte a unei companii de productie care a avut intr-o zi providentiala o idee

extraordinara, sa scrie intr-un document Excel cei mai importanti indicatori de

productie. Vezi mai jos o mica parte din acel tabel, ai poti deschide si tu exemplul cu

acelasi nume ca si acest articol.

Data Schimb Unitati produse Defecte % defecte

1/10/2014 A 6085 168 3%

1/10/2014 B 5998 151 3%

1/10/2014 C 5747 195 3%

Odata ce zilele au trecut si raportul a depasit bariera psihologica de 3 randuri,

conducerea a vazut relevanta urmaririi unor indicatori pe care ii vedeti mai jos.

Statistici

Total unitati produse

Medie defecte / schimb

Numar zile masurate

Maxim unitati produse

Minim unitati produse

Acesti indicatori vrem sa ii calculam folosind cateva formule simple.



Total unitati produse

Excel inca nu functioneaza ca si Siri, nu merge sa ii spun: spune-mi cate unitati am

produs, poate in versiunea urmatoare. Ca sa aflam totalul unitatilor produse vom face

o suma folosind functia SUM pe coloana C:

=SUM(C:C)

Puteam sa scriu si SUM(C2:C31) si in exemplul meu mi-ar fi dat acelasi rezultat. Daca

insa va mai fi adaugata o valoare pe randul 32, aceasta nu ar mai fi fost luata in calcul.

De asta am ales sa scriu C:C.

Medie defecte / schimb

Mergem mai departe, vrem sa stim cat de mult o dam in bara in medie. Ne-am prins ca

avem nevoie de o formula pentru asta, nu e formula MEDIAN daca la asta te-ai gandit,

e formula AVERAGE.

Aplicam aceasta formula in exemplul nostru:

=AVERAGE(D:D)

Numar zile masurate

Directorul general vrea sa stie de cate zile facem acest raport. Ne uitam pe raport si

vedem ca pentru fiecare zi avem cate 3 randuri. Dupa cateva saptamani de cugetare ne

vine o idee geniala, sa folosim o functie care sa numere randurile si apoi sa impartim

rezultatul la 3.

=COUNT(A:A)/3

De mentionat ca formula count nu stie sa numere celulele text, daca aplicam aceasta

formula pe coloana B vom vedea ca rezultatul e 0.

Daca am fi avut doar celule text am fi folosit functia COUNTA, in exemplul nostru in

acest fel:

=(COUNTA(B:B)-1)/3

Ofer un topor de crapat Excel-uri daca imi spuneti de ce scad 1 in formula de mai sus.



Maxim unitati produse

Nici aici nu avem functia CELMAIMARE, avem din fericire functia MAX. O folosim cu

incredere pe coloana de unitati produse:

=MAX(C:C)

Minim unitati produse

Functia pe care o folosim in acest caz se numeste MIN. Ca si functia MAX, o putem aplica

pe o coloana, serie de celule, chiar si o singura celula (asta daca vrem sa aflam minimul

dintre 2 si 2).

=MIN(C:C)

Si cam atat, sper ca nu doar ai citit ce am scris eu, sper ca ai testat si tu in Excel-ul cu

acelasi nume ca si acest capitol.



Functii aritmetice conditionale: COUNTIF, SUMIF,

AVERAGEIF, SUMIFS

Trecem de la functiile aritmetice simple la unele mai pretentioase, care pun conditii. Ca

sa le intelegem cat mai usor ne vom pune in papucii unei companii care cumpara si

vinde „chestii”, sper ca am fost suficient de clar.

Rapoarte existente

Aceasta companie, ca sa stie cat vinde si cat cumpara are 2 rapoarte, unui de achizitii si

altul de vanzari. Le gasesti pe amandoua in fisierul cu acelasi nume ca si acest articol.

Raportul de achizitii

Data Produs Furnizor Cantitate Pret unitar

Total

2014/01/01 Produs 16 Furnizori 8 19 49.00 931.00



Si cel de vanzari

Data emiterii

Produs Client Cantitatea Pret unitar Total Data incasarii

Status

2014/01/01 Produs 10 Client 45 21 32.00 672.00 2014/01/11 Platit



Evident ca ambele raporte au mai mult de 3 randuri, nu le-am pus in intregime aici, nu

am vrut sa intrec Enciclopedia Britannica la numar de pagini.



Stocuri

Firma noastra si-a dat seama ca are nevoie, pentru fiecare produs in parte, de anumite

informatii. Au pus pe cineva sa calculeze manual cu pix si hartie informatiile din tabelul

de mai jos, dupa ce aceasta persoana si-a dat demisia au zis ca incearca sa automatizeze

procesul.

Nume produs Nr. comenzi achizitii

Nr. comenzi vanzari

Total bucati cumparate

Total bucati vandute

Pret unitar mediu achizitii

Pret unitar mediu vanzari

Total facturi restante

Total facturi anulate

Produs 1

Produs 2

Produs 3

Facem un targ?

Eu o sa te ghidez pas cu pas prin acest exemplu, cu rezerva ca eu o sa ma ocup de

achizitii, te las pe tine sa gasesti rezolvarea pentru vanzari.

Numar de comenzi

Atunci cand spunem comanda ne referim efectiv la o linie din tabelul de achizitii. Vrem

sa vedem, pentru fiecare produs in parte cate comenzi au fost plasate, nu valoarea lor,

doar numarul.

De exemplu, pentru Produs 1 putem vedea ca avem 6 comenzi.

Data Produs Furnizor Cantitate Pret unitar

Total







COUNTIF

Pentru a gasi numarul de comenzi fara sa abuzam de filtre, vom folosi formula COUNTIF,

formula care face o numarare daca o conditie este indeplinita.



Asa arata sintaxa acestei functii:

=COUNTIF(range, criteria)

range: adica zona de celule unde vrem sa facem numararea

criteria: valoarea cu care vom compara zona de celule

In cazul nostru, pentru a vedea cate comenzi sunt de exemplu pentru produsul 1 vom

folosi aceasta formula:

=COUNTIF(Achizitii!B:B,Stoc!A2)

Te las pe tine sa vezi cum ar trebui adaptata aceasta formula pentru a ne returna

numarul de comenzi pe partea de vanzari.

Total bucati cumparate / vandute

Bun, am terminat cu numaratul, trecem la adunat, lucruri mai complexe. Vrem sa stim

in functie de fiecare produs cate unitati am cumparat. Da, ai intuit bine, pentru asta vom

folosi SUMIF.

Inainte sa „intram in pita”, haideti sa ne uitam putin peste sintaxa functiei:

=SUMIF(range, criteria, [sum_range])

range: similar cu COUNTIF, se refera la zona de celule unde facem compararea

criteria: si aici seamana, aici vom pune valoarea cu care vom compara

sum_range (optional): zona de celule unde facem suma efectiva

Radu, de ce sum_range e optional?

Foarte buna intrebarea, daca nu introducem sum_range, va face insumarea in zona

definita de noi ca si range. Introducem sum_range atunci cand facem comparatia pe o

coloana si vrem sa facem suma pe alta coloana.

In cazul nostru vom face comparatie pe coloana cu numele produsului si vom face suma

pe coloana cantitate.

Formula noastra arata asa (pentru primul rand):

=SUMIF(Achizitii!B:B,Stoc!A2,Achizitii!D:D)

Te las pe tine sa scrii formula pentru total bucati vandute.



Pret unitar mediu achizitii

In compania noastra fictiva ne-am propus ca preturile sa difere in functie de comanda,

volum si in general cat de mult putem sa fraierim un client. Nu de alta dar la fel

procedeaza si furnizorii cu noi.

Asta inseamna ca nu avem un pret standard pentru fiecare produs, putem avea insa un

pret mediu, atat la cumparare cat si la vanzare. Daca vindem in medie mai ieftin decat

cumparam, asta ar trebui sa ne dea de gandit.

Ca sa gasim acest pret mediu vom folosi formula AVERAGEIF. Hai sa punem putin lupa

pe aceasta formula.

=AVERAGEIF(range, criteria, [average_range])

range: parca am mai auzit de asta nu? adica zona de celule pe care o vom folosi

in comparare

criteria: idem ca si la SUMIF si COUNTIF

average_range (optional): adica zona de celule pe intre care facem media

Radu, de ce average range e optional?

Raspunsul meu e la fel ca si la sum range, cred ca nu ai fost atent mai inainte. Adaugam

average range doar atunci cand facem media pe alta coloana decat cea pe care facem

comparatia.

Te las pe tine sa vezi cum scrii formula pentru a vedea care e pretul mediu de vanzari

pentru fiecare dintre produse.

Total facturi restante

Ne apropiem de final la acest exercitiu, vrem sa vedem acum totalul facturilor restante

/ anultat pentru fiecare produs in parte.

Si care e problema? am mai facut asta

Acest caz e unul mai particular pentru ca vrem sa facem un SUMIF cu mai multe conditii,

numele sa fie egal cu al produsului vizat, iar la status sa avem restant.



Cum formula SUMIF nu stie sa testeze decat o singura conditie, avem nevoie de o

alternativa. Aceasta se numeste SUMIFS sau SUMIF la plural, cu ajutorul careia putem

testa mai multe conditii inainte sa facem suma.

Hai sa punem microscopul pe SUMIFS:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

sum_range: celulele pe care vrem sa le adunam

criteria_range: celulele cu care vom compara

criteria: valoarea cu care vom compara criteria_range

Cu ajutorul SUMIFS putem sa testam oricate conditii dorim inainte de a face suma.

Revenim la oile noastre, vrem sa vedem valoarea facturilor restante pentru fiecare

produs in parte. Dupa cum v-am spus, avem 2 conditii:

1. Numele produsului sa fie egal cu ceea ce avem pe coloana A in tabelul de stoc-

uri

2. Statusul sa fie egal cu „restant”

Eu zic sa scriem formula in felul urmator (pentru linia a 2-a):

=SUMIFS(Vanzari!E:E,Vanzari!B:B,Stoc!A2,Vanzari!H:H,"Restant")

Te las pe tine sa testezi formula, incearca sa scrii si formula potrivita pentru a vedea si

valoarea facturilor anulate.



Functii gestionare text: PROPER, LEFT, RIGHT,

SEARCH, LEN, CONCATENATE

Ai avut vreodata o lista luuunga de nume pe care trebuia sa o corectezi sau sa o modifici

in vreun fel?

Poate ai nevoie, dupa cum vedem si in imaginea de mai jos, doar sa scrii prenumele cu

prima litera mare. La o lista de 1000 de randuri sa faci acest lucru manual e cam munca

de chinez, parerea mea.

prenume NUME Prenume Nume Prenume, Nume Prenume Nume

prenume 1 NUME 1

prenume 2 NUME 2

prenume 3 NUME 3

prenume 4 NUME 4

prenume 5 NUME 5

In cele ce urmeaza vom folosi „muschii” Excel-ului pentru a ne simplifica viata cand

lucram cu liste care contin text. Vom lua pe rand fiecare coloana din tabelul de mai sus

si vom vedea ce formule putem sa folosim pentru a aduce textul in forma dorita.

De la „prenume” la „Prenume”

Pentru inceput vrem sa scriem prenumele cu litera mare, cum am invatat in clasa a doua

parca. Pentru asta folosim functia PROPER().

Aplicam aceasta functie pentru celula A2 si mai departe o multiplicam pana jos,

problema rezolvata.

=PROPER(A2)

Eu zic ca e o functie foarte simpla, arata-mi ca ai inteles cum functioneaza aplicand-o si

pentru coloana NUME.

Radu, dar daca vreau sa fac textul sa fie cu MAJUSCULE?

Atunci vei folosi functia UPPER.



Si daca vreau sa fac textul sa fie scris cu litere mici, ca in coloana 1?

Atunci folosesti LOWER.

Prenume, Nume

Prea mult am indurat separatia dintre nume si prenume, vrem sa facem o „mare unire”

in care sa le aducem impreuna sub forma Prenume, Nume.

Am avea aici nevoie de o functie de lipire a 2 elemente de tip text. Am incercat =LIPIRE

dar nu a functionat, nici =CAPSARE nu a fost de mare ajutor.

Functia de care avem nevoie se numeste CONCATENATE si are urmatoarea sintaxa:

=CONCATENATE(text1, text2, ...)

Ca sa rezolvam problema mari uniri din Excel, pare ca ar trebui sa scriem functia in acest

fel (pentru E2):

=CONCATENATE(C2, D2)

Vedem insa ca rezultatul arata in felul urmator: Prenume 1Nume 1 si nu Prenume 1,

Nume 1. Ne lipseste virgula si spatiul dintre ele.

Nici o problema, le adaugam si pe acestea si formula finala va fi urmatoarea:

=CONCATENATE(C2, ”, “, D2)

Prenume

Dupa fiecare mare unire mai vin si ganduri de secesiune. Ne-am hotarat ca vrem totusi

sa avem numele si prenumele pe 2 randuri separate. Hai sa vedem cum putem face asta

in cateva secunde, chiar daca vorbim de un tabel cu sute de randuri.

Vom incepe cu prenumele si pentru asta avem nevoie de functia LEFT:

=LEFT(Text, [numar_caractere])

Fiindca in mod normal nu vom avea vreodata o lista de persoane in care toate

prenumele sa aiba lunginea egala, mai avem nevoie de o functie care sa ne spuna la

fiecare prenume cate caractere sa rupem.



Ma refer la functia SEARCH:

=SEARCH(find_text, within text, [start num])

find text: adica ce vrem sa cautam, in cazul nostru vom cauta caracterul , (virgula)

within text: textul in care cautam, in cazul nostru pe coloana cu in care avem

numele complet

Rezolvarea pentru prima valoare mai jos:

=SEARCH(",", E2)

Functia search ne va returna un numar care ne indica numarul de caractere pe care

trebuie sa il rupem din nume.

Separam prenumele cu urmatoarea formula:

=LEFT(E2,SEARCH(",",E2)-1)

Am scazut 1 din SEARCH pentru ca nu am vrut sa imi ia si virgula. Daca iti place virgula

poti sa o lasi.

Nume

Vreau sa te las sa te descoperi singur cum ar trebui scrisa formula pentru extragerea

numelui. Ca si idee, formula e usor diferita fata de ultimul exemplu.

In primul rand, vom folosi functia RIGHT si nu LEFT, sintaxa e la fel.

In al doilea rand, trebuie sa tinem cont ca lungimea numelui nu e egala cu pozitia unde

se afla virgula. Ca sa intelegem mai bine luam un exemplul lui Ion, Popescu

Lungime Prenume: 3 (pozitia unde se afla virgula - 1)

Lungime Nume: 7 (lungime text - pozitia unde se afla virgula)

Asta inseamna ca avem nevoie de o functie care sa ne spuna lungimea textului, adica

LEN:

=LEN(text)

Te las pe tine sa vezi cum ar trebui scrisa formula, daca totusi nu reusesti sa ii dai de

cap, poti gasi aici raspunsul.



http://excelninja.ro/functii-left-right-search-len-concatenate/

Functii logice: IF, AND, OR

In cele ce urmeaza ne intoarcem putin in timp la momentele in care primeam note de

la 1 la 10, liceu, facultate, alegeti voi.

O sa folosim in acest exemplu 3 functii conditionale, care ne vor afisa un anumit mesaj

in functie de notele fiecarui persoane.

Vezi mai jos lista cu cei 3 elevi din clasa noastra, nu uita ca pe masura ce citesti sa

deschizi fisierul pe care vom lucra.

Matematica Romana Geografie Trecut la Matematica Integralist Corigent

Radu 7 7 7

Gigel 3 5 2

Maricica 4 5 5

Trecut la Matematica

Imi aduc aminte in liceu singura materie importanta si sfanta era Matematica, restul

erau considerate „balet”. Avand asta in vedere, primul lucru pe care il vom face este sa

vedem daca o persoana a promovat la matematica.

Cum facem asta?

Avem nevoie de formula IF pentru asta, hai sa punem lupa pe ea putin:

=IF(logical_test, [value_if_true], [value_if_false])

logical test: lucruri de genul A2>15, sau cum ar fi situatia in cazul nostru B>=5

value if true: ce afisam daca testul logic este adevarat

value if false: ce afisam daca testul logic este invalid

La intrebarea, trecut la matematica, in cazul in care valoarea de pe coloana B este mai

mica decat 5 sa afisam Nu, iar in caz contrar sa afisam Da. Ecce formula pentru elevul

Radu:

=IF(B2>=5,"Da","Nu")



Integralist / corigent

Aici totul se rezuma la cateva comparatii. In cazul in care punem eticheta integralist ne

intereseaza ca TOATE notele sa fie peste 5 iar cand ii scriem in frunte corigent ne

intereseaza ca CEL PUTIN UNA dintre note sa fie sub 5.

Asta se traduce in mai multe comparatii, descrise in limbaj simplu in felul urmator:

Integralist: DACA nota_matematica > 5 SI DACA nota_romana > 5 SI DACA

nota_georgrafie > 5

Corigent: DACA nota_matematica < 5 SAU nota_romana < 5 SAU nota_geografie

< 5

Radu, formulele de mai sus nu functioneaza, ai uitat sa pui egal

Evident ca “formulele de mai sus” nu functioneaza, e doar ca sa vedem gandirea din

spate. Ca sa putem scrie formulele efective avem nevoie sa intelegem functiile AND si

OR.

=AND(logical1, logical2, ...)

logical 1,2, ... reprezinta anumite conditii pe care le evaluam, functia AND ne va

da rezultatul TRUE in cazul in care toate conditiile sunt adevarate

dupa cum am vazut mai sus, o conditie poate fi: DACA nota_matematica > 5

=OR(logical1, logical2, ...)

OR seamana ca si sintaxa destul de bine cu AND, diferenta este data de

mecanismul din spate, va returna TRUE daca una dintre conditii este adevarata si

FALSE daca niciuna nu este adevarata

Avand in vedere aceste formule, cred ca e destul de logic de ce am ales AND ca sa

punem eticheta de integralist si OR ca sa punem eticheta de corigent.

Formulele exacte pentru Radu, inregistrarea de pe linia, 2 sunt urmatoarele:

Integralist = AND(IF(B2>=5,1,0),IF(C2>=5,1,0),IF(D2>=5,1,0))

Corigent = OR(IF(B2<5,1,0),IF(C2<5,1,0),IF(D2<5,1,0))



Dar totusi, atatea formule pentru 3 persoane si 3 materii ...

In cazul nostru, in care avem 3 elevi si 3 materii e clar ca folosirea acestor formule in

combinatie e destul de inoportuna, ca si cum ai trage cu tunul dupa muste. Ideea

exemplului e sa ne arate cum putem sa folosim mai multe IF-uri impreuna pentru a testa

conditii multiple.



Functii de cautare: VLOOKUP

Exista anumite facilitati in Excel pe care dupa ce le-ai inteles te fac sa iti pui intrebarea:

Oare cum m-am descurcat fara?

Din punctul meu de vedere VLOOKUP se incadreaza in aceasta categorie. Inainte de a

vorbi despre aceasta functie, hai sa vedem povestea lui Gigel.

Omul nostru lucreaza in departamentul de resurse umane al unei companii si are foarte

multe liste cu informatiile despre angajati. Mai jos puteti vedea tabelul cu datele de

contact.

Badge Nume Telefon Email Departament Manager

131261 Nume 1 0740 111332 [email protected]



Probabil ca ati observat campurile lipsa. Omul nostru vrea sa completeze campurile

lipsa, din fericire informatiile se gasesc in acest al doilea tabel.

Badge Nume Departament Manager

131261 Nume 1 Departament 15 Manager 1



Problema e data de modul in care preluam informatiile. Fiindca timpul limitat nu ne

permite sa fim ineficienti, vom vedea cum ne poate ajuta VLOOKUP.

Varianta trista

Sunt mai multe moduri in care putem lua departamentul si managerul din al doilea

tabel. Vreau sa va prezint cea mai trista si consumatoare de timp varianta, asta ne va

ajuta sa intelegem VLOOKUP.

1. Copiem numarul de badge din primul tabel

2. Cautam acel numar de badge in al doilea tabel

3. Dupa ce am gasit numarul de badge, copiem departamentul de pe acel rand

4. Ii dam paste inapoi in primul tabel

5. Si repetam asta de 3554 de ori si apoi trecem la coloana manager



VLOOKUP

Nu stiu voi cum sunteti dar mie nu imi place munca repetitiva, VLOOKUP vine ca si

alternativa.

Radu, ce face functia asta

VLOOKUP vine de la Vertical LOOKUP – cautare pe verticala. Functia va cauta o valoare

intr-un tabel si atunci cand o va gasi, va returna o valoare de pe acel rand, similar putin

cu ceea ce v-am descris mai sus (varianta trista).

Sintaxa e urmatoarea:

=VLOOKUP(lookup value, table_array, col_index_num, [range_lookup])

lookup value: valoarea pe care o cautam

table array: tabelul in care cautam

col index num: numarul coloanei pe care o returnam, insist ca e vorba despre un

numar (1,2,3,...) si nu despre numele coloanei

range lookup (true / false): daca vrem sa facem cautare aproximativa sau exacta

Teoria ca si teoria, spune-mi cum folosesc functia ca sa preiau

departamentul

Functia pe care o scriem in E2 si apoi o multiplicam in jos e urmatoarea:

=VLOOKUP(A2,Departament!A:D,3,FALSE)

lookup value – A2: cautam dupa numarul de badge

table array – Departament!A:D: cautam in tabelul al doilea pe care l-am pus in

sheet-ul departament

col index num – 3: vrem sa returnam valoarea de pe a treia coloana numarand

de la stanga la dreapta din table array, ai ghicit, acolo se afla departamentele

range lookup – FALSE: vrem cautare exacta, nu aproximativa

Bun, acum hai sa vedem cum scriem functia pentru a prelua

manager-ul.

Te las pe tine sa ii dai de cap, daca ai inteles cum sa iei departamentul, nu ar trebui sa

intampini probleme.



De ce trebuie sa tinem cont

Aceasta functie ne poate da in mai multe situatii erori pe care le putem elimina cu

usurinta daca intelegem urmatoarele lucruri:

1. Avem nevoie de un element de legatura dintre tabele, adica o valoare pe care sa

o regasim in ambele tabele; nu putem folosi VLOOKUP daca nu avem un element

de legatura, ca si numarul de badge din exemplul nostru.

2. In tabelul in care cautam (table array) avem nevoie ca elementul de legatura sa

fie pe prima coloana (coloana badge din tabelul 2 se afla pe prima coloana) pentru

ca VLOOKUP poate sa mearga doar in dreapta.

3. Atunci cand selectam table array, e recomandat sa selectam toata coloana si nu

doar valorile vizibile din tabel, vezi imaginea de mai jos; in acest fel nu vom avea

probleme in multiplicarea formulelor.



Functii de cautare: INDEX, MATCH

Vom vorbi acum despre formulele INDEX si MATCH, formule de cautare foarte utile care

vin in ajutorul nostru atunci cand alte formule (nu dau nume ca sa nu se supere

VLOOKUP) isi ating limitele. Haideti sa nu vorbim teoretic, sa luam un exemplu.

Exemplu

Vom lua exemplul de mai jos, stiu, e foarte complicat, greu de inteles, va explic acum.

Vedem acolo o casuta libera la vanzari, am vrea acolo, in functie de numarul lunii scris

putin mai jos, sa ne apara valoarea de vanzari.

Functia INDEX

Pentru asta vom folosi functia INDEX, hai sa vedem mai exact cum arata sintaxa.

=INDEX(array, row_num, [column_num])

Array: zona de celule unde facem cautarea

Row_num: numarul randului pe care vrem sa il returnam

Column_num: numarul coloanei pe care o returnam (optional in cazurile in care

zona de celule are o singura coloana)

Radu, eu nu inteleg

Imagineaza-ti o tabla de sah, array reprezinta patratelele de pe tabla de sah. Row si

Column reprezinta coordonatele pe care noi le dam.

Am inteles, deci e o formula pentru sahisti

Daca asta te ajuta pe tine sa mergi mai departe, poti sa consideri ca da.



Revenind la exemplu

In functie de valoarea introdusa in E1, vrem sa returnam valoarea de vanzari

corespunzatoare. Daca ati citit descrierea de mai sus probabil ca sunteti de adcord cu

mine ca atunci cand scriem formula INDEX:

Array: sunt valorile de la A2 la A7, adica numerele de vanzari

Row_num: va fi egal cu ceea ce scriem in E1

Column_num: nu ne intereseaza in acest caz, zona de celule selectata de noi e pe

o singura coloana

Formula noastra va fi urmatoarea:

=INDEX(A2:A7,E1)

Hai sa complicam putin lucrurile

Acum ca ati inteles ideea de baza dupa care functioneaza INDEX, hai sa vedem un

exemplu putin mai complex. Firma noastra s-a extins si acum vinde in 3 zone: Oradea,

Cluj si Bucuresti. Acum raportul arata asa:

Am vrea ca in functie de zona si luna pe care o scriem, sa ne arate vanzarile.

Functia MATCH

Probabil ca v-ati dat seama ca functia INDEX nu va functiona de una singura daca ii vom

da ca si argumente Oradea si Feb. Functia asta are nevoie de cifre. Am avea nevoie de



inca o functie care sa caute zona si luna si sa ne returneze un numar. Exista, se numeste

MATCH si uite cum arata:

=MATCH(lookup_value, lookup_array,[match_type])

lookup_value: valoarea pe care o cautam

lookup_array: zona de celule in care cautam

match_type (optional): tip de cautare, mai mare, mai mic, egal

Pentru a vedea a cata valoare este G1 din A1:C2 avem:

=MATCH(G1,A1:C1,0)

Pentru a vedea a cata valoare este G2 din D2:D7 avem:

=MATCH(G2,D2:D7,0)

INDEX+MATCH

Aflam valoarea vanzarilor in functie de zona si luna vom folosi INDEX si MATCH in

combinatie. Trec direct la subiect, ecce formula:

=INDEX(A2:C7,MATCH(G2,D2:D7,0),MATCH(G1,A1:C1,0))

Legatura cu VLOOKUP

Unii dintre voi probabil ca v-ati lovit de acest lucru, formula VLOOKUP nu stie sa caute

decat in dreapta in cadrul table array, asta ne poate pune probleme in anumite cazuri.

Din fericire, folosind INDEX si MATCH putem rezolva aceasta problema, cu aceste

formule putem face un fel de cautare VLOOKUP in stanga.



Alte functii: IFERROR

Vreau in acest articol sa iti spun un truc pe care l-am invatat atunci cand am facut

programul de facturare in Excel. Probabil ca te-ai prins din titlu ca e vorba despre functia

IFERROR.

Problema

Voiam sa fac un model de factura in Excel in care sa ai un tabel cu informatiile despre

clienti si inca unul cu informatiile despre fiecare factura in parte. Informatiile le luam

din cele 2 tabele si le punem intr-un sheet nou folosind mai multe formule VLOOKUP.

M-am lovit aici de o problema, voiam ca atunci cand am informatii legat de un rand din

factura sa le afiseze iar atunci cand nu, sa nu se afiseze nimic, ca si in imaginea de mai

sus. Problema e ca VLOOKUP returneaza #N/A atunci cand nu gaseste nimic.

Cum ar fi fost sa pun pe site un model de factura care sa arate asa:

Radu, cam nasol



http://excelninja.ro/program-de-facturare-in-excel-varianta-2/

Dar totusi avem IFERROR

Din fericire am gasit formula IFERROR, o formula care trateaza exact problema pe care

o aveam eu. In cazul in care gasesc o eroare sa aleg eu ce vreau sa afisez.

In cazul meu, daca apare o eroare, sa afiseze … nimic, adica un caracter gol. Are

urmatoarea sintaxa:

=IFERROR(Ce testam, Ce afisam in schimb)

In cazul meu testam formula VLOOKUP si in cazul in care gaseam probleme afisam un

spatiu (adica ” “).

=IFERROR(VLOOKUP(CONCATENATE($C$2,”-“,$B17),Facturi!$C:$N,3,0),” “)

Ce ai tu de facut

Stai linistit ca nu te las sa parcurgi acest capitol pana la final fara ca sa ai ceva de lucru.

Daca ai deschis deja fisierul cu acelasi nume probabil ai vazut ca e nevoie sa aplici functia

IFERROR in factura.

Eu iti spun doar spor la treaba, daca rezolvi aceasta treaba atunci ai obtinut si un mini-

program de facturare numai bun pentru afaceri mici PFA-uri.






mailto:[email protected]

http://www.romcom.ro

Date post:	13-Jul-2016
Category:	Documents
Upload:	ana-tricolici
View:	246 times
Download:	3 times

eBook - Arsenal de Ninja

Documents