In acest articol vorbim despre functii si formule in Excel. Incercam sa facem o introducere, adica sa intelegi cateva elemente de baza care te vor ajuta sa scrii formule in Excel cu mai putine erori si mai putin „la plezneala”.
Functii si formule in Excel
MS Excel Extended
Publicitate: video-ul de mai sus e extras din MS Excel Extended, cursul meu online despre Excel de nivel intermediar. Ceea ce incep aici pe partea de functii si formule se continua in sectiunea de functii in Excel.
Functii vs. formule in Excel
O intrebare pe care o pun de multe ori la cursurile mele e urmatoarea:
Care e diferenta dintre o functie si o formula in Excel?
Primesc multe raspunsuri cel putin interesante. Unii spun ca functiile sunt matematice si formulele nu, altii ca functiile sunt complicate si formulele nu. Desi reusesc sa extrag lucruri bune (cand sunt de treaba) din afirmatiile de mai sus, diferenta e urmatoarea:
- Functiile sunt elemente predefinite: SUM, IF, VLOOKUP sunt exemple de functii
- Formulele le construim noi: mai jos poti vedea un exemplu de formula aiurea construita de mine, dar care are toate elementele componente
=A1+10+SUM(B:B)
Da, stiu, formula de mai sus e cel putin ciudata. Are insa cele 3 elemente componente:
- Numere: 10
- Celule: A1
- Functii: SUM
Daca e sa ne gandim bine, formula e un fel de ciorba, si functiile sunt unele din lucrurile pe care le bagam in acolo. Musai e sa pun aici si o poza cu un castron de ciorba.
Bun, daca tot am pus si poza cu ciorba, hai sa vorbim si despre cum pregatim ingredientele inainte sa ne apucam de gatit.
Functii in Excel
Am mentionat mai sus ca SUM, IF, VLOOKUP sunt functii in Excel. Pe langa acestea 3, mai avem inca vreo 400 cel putin, le putem gasi pe toate aici, dar si in Excel puse pe categorii.
In functie de ceea ce faci vei folosi anumite functii mai mult, iar altele mai putin. Sunt insa cateva lucruri care tin de sintaxa pe care ar trebui sa le ai in vedere ca sa eviti erorile. Hai sa vedem un exemplu, ca sa fie mai clar:
=VELUCAP(COD123 Sheet1!A:B 2 0
Mai sus avem mai multe greseli, hai sa le luam pe rand:
- Numele functiei nu este corect, nu exista functia VELUCAP, avem insa VLOOKUP
- Am pus paranteze la inceput, dar nu si la final, la orice functie avem nevoie de paranteze
- Textul in orice functie in Excel trebuie pus intre ghilimele „”
- Nu am adaugat separatori intre parametrii din functie, in functie de setarile regionale trebuie sa pui , sau ;
- Numar incorect de parametri: Excel-ul crede ca toate informatiile de mai sus constituie un singur parametru, VLOOKUP are nevoie de minim 3
Mai jos poti vedea cum ar trebui sa arate functia scrisa de noi:
=VLOOKUP("COD123", Sheet1!A:B, 2, 0)
Poti vedea lucrurile astea mult mai clar explicate in video-ul de mai sus.
Blocarea elementelor intr-o formula
Se da urmatoarea problema: ai nevoie sa faci o conversie din RON in EUR raportat la un curs scris intr-o celula in Excel. Toate bune si frumoase, scrii urmatoarea formula, ii dai Enter si vezi ca merge.
Dar dupa ce „tragi in jos formula” observi asta:
Multe erori, cauzate de faptul ca pe randul 3 nu mai imparte la W1, ci la W2. Pe masura ce mergi mai jos, vei vedea cum imparte la o celula pozitionata tot mai jos.
Solutia?
Desi facem conversie din RON in EUR, ca sa rezolvam problema, tot de $ avem nevoie. Aici ma refer la caracterul $ pe care sa il punem in formula inaintea elementului pe care vrem sa il blocam. Adica sa punem un $ in felul urmator.
=T2/W$1
Daca imi plac $, pot pune unul si inainte lui W, ce-i sigur ii sigur. Asta va bloca si litera in cazul in care ma gandesc sa trag o formula in dreapta sau in stanga.
Tasta F4
O alternativa la adaugarea manuala a $ acolo unde e nevoie e tasta F4. Daca selectez in formula W1 si apoi apas F4, voi vedea ca apar 2 $ in formula, in felul urmator:
=T2/$W$1
Daca apas de mai multe ori, se vor tot schimba $. Nu, din pacate nu vei vedea mai multi $ in cont daca apesi F4 de mai multe ori.
Functii simple: SUM, AVERAGE, COUNT
Daca ai invatat deja sa faci inmultiri / impartiri, cred ca poti trece la nivelul urmator, sa incerci niste functii simple, cum ar fi SUM, AVERAGE sau COUNT. Hai sa luam urmatorul exemplu:
Pe B vrem sa numaram cate valori sunt, pe C facem media iar pe D facem o suma. Vezi in imaginea de mai jos cum ar trebui sa arate functiile odata scrise.
Hai sa observam cateva lucruri:
- Dupa ce scriem corect numele functiei punem o paranteza
- Putem fie sa selectam celulele pe care vrem sa le calculam, fie sa le scriem
- Atunci cand punem : intre 2 celule ne referim la tot intervalul dintre cele 2 celule
- Asa este, nu am fost foarte creativ cand am denumit produsele
AUTOSUM
Un alt mod prin care poti face calcule simple pe o anumita coloana e sa folosesti optiunea AUTOSUM. Trebuie doar sa te pui sub coloana pe care vrei sa o calculezi si sa apesi butonul, se ocupa Excel-ul de restul. Optiunea o gasesti cum intri in tab-ul home, pe dreapta.
Cele mai utile functii in Excel
Daca ai inteles cum sa faci o suma pe o coloana, e posibil sa vrei sa inveti si alte functii. E insa cam descurajant cant vezi ca sunt cam „fara numar fara numar”. Desi sunt multe, nu toate sunt la fel de utile, vezi mai jos o lista cu cele 2 mai utile 24.
Functii utile in Excel – top 24
Radu este fondatorul onLearn si autorul majoritatii articolelor de pe acest site. Cand nu scrie sau nu inregistreaza vreun tutorial e in sala de curs sau lucreaza pe proiecte de consultanta ca sa invete lucruri noi.
Ai o intrebare?
Daca ai ajuns pana aici cautand sectiunea de comentarii, sa stii ca aceasta nu exista :). Poti insa ca sa ne pui intrebari in partea de forum a site-ului, te asteptam acolo.
Buna! Am si eu o intrebare: incerc sa copiez de pe o coloana pe alta formuele. As vrea sa fie aceleasi (ex. sa nu imi schimbe A cu B etc). Cum se poate face acest lucru? (Ideea mea este sa pastrez aceleasi campuri din care trage informatiile dar sa schimb sheetul de unde trage – asta am descoperit Find/Repace + Options). Multumesc!
Tatiana, asta e modul standard cum se copiaza formulele. Daca vrei sa ramana neschimbate, iti recomand sa le blochezi folosind simbolul $.
Vezi mai multe aici:
https://excelninja.ro/ce-se-intampla-cand-apas-f4/
Ori sa le inghete?!
Radu as vrea sa scrii despre funcția indirect. Mulțumesc.
Am notat, mersi
Daca tot eram pe aceasta pagina 🙂
https://excelninja.ro/functia-indirect-excel/
Salut Radu. Te-ai gandit sa faci un curs prin intermediul skype luand in ordine fiecare rubrica din excel pentru a fi prezentata? M-as bucura sa tii cont si de pret.
Multumesc.
Mihai, am un curs online care e foarte aproape de ceea ce cauti, vezi link-ul de mai jos:
http://officeninja.teachable.com/p/ms-excel-ghid-practic-de-supravietuire
Pingback: Formule Excel: schimba sursa / edit links | excelninja.ro
CUM AS PUTEA SA SCAD 20-30 IN EXCEL SI SA IMI DEA REZULTATUL CU MINUS?
Pai, le scazi pur si simplu. Vezi la formatare sa nu ai setat sa iti arate () in loc de minus. Daca nu vezi minusul, format cells, numeric si acolo selectezi sa vezi minusul.
Am 2 tabele: unul cu coloana data si coloana nume (scris prenume spatiu nume) si altul cu data si coloana nume (scris nume, virgula si spatiu prenume). Am vreo sansa sa extrag intr-o coloana ce persoane nu se regasesc in tabelul 1 dar sunt in tabelul 2 si in ce data?
Am de formatat conditional un camp unde au fost plasate niste comenzi IN ULTIMUL AN si la ANUMITE PRODUSE. cum pot face? multumesc!
Buna, Radu,
Imi place mult excel, imi place mult ce prezinti si folosesc excel-ul bine as zice eu.
Totusi ma poticnesc/blochez rau, cand mi se cere sa calculez la 900 de salariati indice de salarizare pe categorii de personal, puncte de lucru conditia YTD sau cand mi se cere sa calculez procentual diferenta dintre salariile brute pe 2021 vs 2022, pe puncte de lucru, categorie de personal si indice de ierahizare.
Cum pot realiza acestea, la ce criterii ma duc in excel, sa caut mai bine?
Aici ma blochez desi stiu ceva excel.
Te rog sa imi spui de aesemenea ce costuri implica.
Multumesc frumos.
Salut, vezi ca ti-am trimis un email.
Salut Radu.Am de ezemplu 5 numere:
15;24;56;42;33.
Cum pot egala aceste numere intre ele.de
Nu prea inteleg cum vrei sa le egalezi intre ele.
Buna Radu!
Voiam sa te intreb daca exista un truc pentru functia vlookup atunci cand avem de adus informatii de pe mai multe coloane (ex 30 coloane) . Solutia actuala pe care o folosesc este sa copiez functia si schimb manual numarul coloanei de pe care sa imi aduca informatia(am mereu in vedere sa am aceeasi ordine a coloanelor, scriu functia vlookup cu aducerea informatiei de pe coloana 2 si apoi,pe rand, schimb 3,4,5..etc). Intrebarea este daca exista o solutie mai simpla de a trage formula si care sa schimbe automat, in ordine crescatoare, numarul coloanei de pe care aducem informatia.
Multumesc!
Poti sa faci asta daca adaugi un rand ajutator pe care sa il folosesti in formula, sau folosind XLOOKUP. Mi-ai dat o idee de articol nou, stay tunned. 🙂
Tocmai mi-a venit ideea de a incerca cu combinatia de index si match . Testez si revin cu rezultatul.
Am reusit cu index si match, insa, trebuie atentie la blocari cu $ , cu ceva exercitiu, daca stapanesti metoda, vei scrie doar o data formula si tragi de ea si pe lateral si in jos, si ai tot ce trebuie. Cred ca varianta de blocari $ ar fi mers si la vlookup” dar se pierde „magia”: fata de vlookup, poti schimba ordinea coloanelor din „baza” pentru ca ai match pe cap de tabel .
=INDEX(baza!$A:$AE,MATCH($A2,baza!$H:$H,0),MATCH(B$1,baza!$A$1:$AE$1,0))
– A:AE este baza cu informatiile
-H coloana cu cheia unica (intentionat nu este prima, pentru a putea verifica formula)
Inca astept si varianta ta, intotdeauna schimbul de experienta este util!
Revin cu un articol pe tema asta, stau insa cam prost cu timpul.
Comentariile sunt închise.