EXCEL Avansat - antrenament pe un set real de date
La BAC ti se cere Excel de baza (formule simple, sortare, un grafic). Pagina asta te duce un nivel mai sus: lucrezi pe un registru cu 922 de vanzari reale dintr-un magazin online (data, ora, mod de plata, produs, brand, pret) si inveti exact functiile care fac diferenta intre "stiu Excel" si "ma descurc cu orice tabel".
Cum folosesti pagina: descarca fisierul, rezolva fiecare exercitiu pe cont propriu, apoi deschide raspunsul. Exercitiile sunt in ordine - cele de la inceput pregatesc datele pentru cele de la final. Separatorul de argumente folosit este ; (Excel pe setari romanesti); daca la tine functioneaza ,, foloseste virgula.
Pasul 0 - imblanzeste fisierul (2 exercitii)
E1. Deschide fisierul si apasa F9 de cateva ori. Coloana Price se schimba singura! De ce, si cum o "inghete" la valorile curente?
Raspuns: Celulele din Price contin formula =RANDBETWEEN(100;500) - o functie volatila, care se recalculeaza la orice modificare. Ca sa fixezi valorile: selectezi coloana H (Price), Ctrl+C, apoi Home > Paste > Paste Special > Values peste aceeasi coloana. Formulele dispar, raman numerele.
Explicatie: fara pasul asta, orice suma sau grafic facut pe Price isi schimba rezultatul de fiecare data. La orice fisier primit "de afara", verifica intai ce e formula si ce e valoare.
E2. Transforma intervalul A1:H923 intr-un Tabel Excel cu numele "Vanzari". Ce castigi?
Raspuns: Click oriunde in date > Ctrl+T > bifezi "My table has headers" > OK. Apoi in Table Design > Table Name scrii Vanzari.
Explicatie: tabelul iti da filtre automate pe antet, formatare alternata pe randuri, iar formulele pot folosi nume clare (Vanzari[Price] in loc de H2:H923). Cand adaugi un rand nou, totul se extinde singur.
Formule conditionale pe 922 de randuri (4 exercitii)
E3. Cate vanzari are brandul Apple?
Raspuns: =COUNTIF(G2:G923;"Apple")
Explicatie: COUNTIF numara aparitiile unui criteriu intr-un interval. Pe tabel: =COUNTIF(Vanzari[Brand];"Apple").
E4. Care este suma incasarilor (Price) pentru brandul Apple?
Raspuns: =SUMIF(G2:G923;"Apple";H2:H923)
Explicatie: cauti in coloana Brand, criteriul "Apple", aduni din coloana Price. Ordinea argumentelor: unde caut; ce caut; de unde adun.
E5. Cate vanzari Apple au fost platite cu cardul (Payment Option = "Credit card")? Doua conditii simultan.
Raspuns: =COUNTIFS(G2:G923;"Apple";D2:D923;"Credit card")
Explicatie: COUNTIFS (cu S la final) accepta perechi interval;criteriu - toate conditiile trebuie indeplinite simultan. Sora ei pentru sume este =SUMIFS(H2:H923;G2:G923;"Apple";D2:D923;"Credit card") - atentie, la SUMIFS intervalul de adunat vine PRIMUL.
E6. Care este pretul mediu al produselor mai scumpe de 300?
Raspuns: =AVERAGEIF(H2:H923;">300")
Explicatie: cand criteriul contine un operator, il pui in ghilimele. Daca media s-ar calcula pe alta coloana decat cea cu conditia, ai folosi al treilea argument.
Functii de cautare (2 exercitii)
E7. Construieste o "caseta de cautare": scrii un Sales ID (de exemplu s500) in J1 si in J2 apare pretul acelei vanzari.
Raspuns: =VLOOKUP(J1;A2:H923;8;FALSE)
Explicatie: cauta valoarea din J1 in PRIMA coloana a intervalului si returneaza coloana a 8-a (Price) de pe acelasi rand. FALSE = potrivire exacta - aproape intotdeauna vrei FALSE.
E8. Acelasi lucru, dar vrei numele produsului, si sa nu se strice daca cineva insereaza o coloana in tabel.
Raspuns: =INDEX(F2:F923;MATCH(J1;A2:A923;0))
Explicatie: MATCH gaseste pozitia (randul) Sales ID-ului, INDEX aduce valoarea de pe acea pozitie din coloana Product name. Spre deosebire de VLOOKUP, nu depinde de "a cata coloana" - daca tabelul se modifica, formula rezista. In Excel nou poti folosi si =XLOOKUP(J1;A2:A923;F2:F923).
Date calendaristice si timp (2 exercitii)
E9. Adauga in coloana I luna fiecarei vanzari (1-12), apoi calculeaza cate vanzari au fost in ianuarie.
Raspuns: In I2: =MONTH(B2), dublu-click pe coltul din dreapta-jos ca sa se copieze pana la capat. Apoi =COUNTIF(I2:I923;1).
Explicatie: MONTH/YEAR/DAY extrag componente dintr-o data. Coloana ajutatoare este cel mai cinstit instrument din Excel - nu te feri de ea.
E10. Cate vanzari s-au facut dimineata, inainte de ora 12:00? (coloana Time)
Raspuns: =COUNTIF(C2:C923;"<12:00")
Explicatie: orele sunt numere (0,5 = ora 12:00), deci se compara ca numerele. Alternativa cu coloana ajutatoare: =HOUR(C2) si apoi COUNTIF pe rezultat.
PivotTable - raportul de un minut (2 exercitii)
E11. Fa un raport: incasari totale pe fiecare brand, sortat descrescator, fara nicio formula.
Raspuns: Click in date > Insert > PivotTable > OK (foaie noua). Tragi Brand in Rows si Price in Values (devine Sum of Price). Click dreapta pe o suma > Sort > Largest to Smallest.
Explicatie: PivotTable face in 30 de secunde ce ar cere zeci de formule SUMIF. Este cel mai puternic instrument "avansat" si cel mai usor de invatat.
E12. In acelasi Pivot: incasarile pe brand, defalcate pe mod de plata, si doar pentru vanzarile cu Payed = "Yes".
Raspuns: Tragi Payment Option in Columns si Payed in Filters, apoi alegi "Yes" din filtrul de deasupra tabelului.
Explicatie: Rows x Columns iti da o matrice de totaluri (brand pe randuri, mod de plata pe coloane). Filters scoate din calcul ce nu te intereseaza, fara sa stergi nimic din date.
Vizual si finisaj (2 exercitii)
E13. Evidentiaza automat cele mai scumpe 10% dintre vanzari si gaseste eventualele Sales ID duplicate.
Raspuns: Pentru top 10%: selectezi H2:H923 > Home > Conditional Formatting > Top/Bottom Rules > Top 10%. Pentru duplicate: selectezi A2:A923 > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Explicatie: formatarea conditionata "avansata" nu inseamna reguli complicate, ci sa stii ca exista categorii gata facute: Top/Bottom, Duplicate, Data Bars, Color Scales.
E14. Pregateste fisierul de lucru zilnic: antetul ramane vizibil cand derulezi si se tipareste pe fiecare pagina.
Raspuns: View > Freeze Panes > Freeze Top Row (antet vizibil la derulare). Apoi Page Layout > Print Titles > la "Rows to repeat at top" selectezi randul 1 (antet pe fiecare pagina tiparita).
Explicatie: pe 922 de randuri, fara Freeze Panes nu mai stii ce coloana citesti. Sunt doua setari diferite: una pentru ecran, una pentru hartie.
Capcane clasice la nivelul asta
- RANDBETWEEN/RAND/NOW/TODAY sunt volatile - rezultatele se schimba la orice recalcul. Ingheata-le cu Paste Special > Values inainte de orice analiza (E1).
- VLOOKUP fara FALSE la final returneaza "cea mai apropiata" potrivire, adica adesea un rezultat gresit care PARE corect.
- La SUMIFS intervalul de adunat e primul argument; la SUMIF e ultimul. Cea mai frecventa inversare.
- Sortarea doar a unei coloane amesteca randurile intre ele - selecteaza mereu tot tabelul sau lucreaza intr-un Tabel Excel (E2).
- Separatorul de argumente difera dupa setarile regionale:
;pe romaneste,,pe engleza. Daca formula da eroare la virgula, incearca punct-virgula.
Legatura cu examenul: subiectele de BAC cer nivelul din lectia Excel (formule de baza, sortare, grafic, formatare conditionata simpla). Tot ce e pe pagina asta e peste cerinta minima - dar exact asta iti ridica viteza si siguranta la proba si nivelul de competenta certificat.