SISergiu Ilincameditații informatică
Meniu

TIC · Fișe progresive · Excel 5/5

Excel, nivelul 5: mai multe foi, formatare condiționată, validare și proiect

Materiale TICMaterialul 14 din 34 · mai sunt 20Urmează: Fișa F04 — Calcul tabelar (Excel): toate operațiile din subiecteVezi toate lecțiile
Cuprins

Fișiere PDF și de lucru

Durată: 60–90 min · Nivel: avansat (nivel examen) · Fișier de pornire: materiale/E5-start.xlsx (foile Elevi și Clase)

Ai nevoie de: Excel 1–4.

Ce vei ști după fișă

  • lucrezi cu mai multe foi și faci formule care folosesc date din altă foaie;
  • aplici formatare condiționată (colorare automată după reguli);
  • limitezi ce se poate scrie într-o celulă cu validarea datelor;
  • îngheți rândul de antet, folosești AVERAGEIF, COUNTIF, VLOOKUP;
  • finalizezi un proiect de tip examen.

1. Foile

Foi

E5-start.xlsx are două foi:

  • Elevi (12 elevi, clasa și trei note); coloanele Nr, Elev, Clasa, Nota 1, Nota 2, Nota 3;
  • Clase: lista claselor cu dirigintele (a VI-a A → prof. Marin; a VI-a B → prof. Stoica).

Dublu clic pe numele unei foi o redenumește; clic dreapta → Inserare adaugă o foaie nouă.

2. Media pe fiecare elev

În foaia Elevi, G1 = „Media”, în G2: =ROUND(AVERAGE(D2:F2);2), trage până la rândul 13. Câteva rezultate: Andrei 8,33 · Bianca 7 · Cosmin 6 · Ionuț 4,67.

3. Înghețarea rândului de antet

Pe un tabel lung, antetul dispare când derulezi. Selectează celula A2 → Vizualizare (View) → Înghețare panouri (Freeze Panes) → Înghețare rând de sus (Freeze Top Row). Acum antetul rămâne mereu vizibil.

4. Validarea datelor

Vrei ca în D2:F13 să se poată scrie doar note între 1 și 10.

  1. Selectează D2:F13.
  2. Date → Validare date (Data Validation).
  3. Permite (Allow): Număr întreg (Whole number); Date: între (between); Minim 1; Maxim 10.
  4. Fila Mesaj de eroare (Error Alert): titlul „Notă greșită”, mesajul „Introduceți o notă între 1 și 10.”.

Acum, dacă scrii 11 într-o celulă, Excel refuză.

De ce așa?

De ce validare în loc să fim atenți? Oamenii greșesc: un 100 în loc de 10 strică media clasei. Validarea oprește greșeala la intrare, iar apoi toate formulele pot avea încredere în date.

5. Formatarea condiționată

Vrei ca notele sub 5 să se vadă imediat, în roșu.

  1. Selectează D2:F13.
  2. Pornire → Formatare condiționată (Conditional Formatting) → Reguli pentru evidențierea celulelor (Highlight Cells Rules) → Mai mic decât (Less Than)… → 5 → Umplere roșie deschisă cu text roșu închis.
  3. La fel pentru G2:G13 (media): sub 5 → roșu.

Rezultat: în tabel sunt 10 note sub 5 (3 în clasa A și 7 în clasa B), iar o singură medie este sub 5 (Ionuț, 4,67).

De știut

Formatarea condiționată se aplică automat: dacă modifici o notă, culoarea se schimbă singură. Spre deosebire de o culoare pusă „de mână”, ea ține evidența regulii.

6. Date din altă foaie: VLOOKUP

Vrei ca fiecare elev să aibă diriginte, luat din foaia Clase. În H1 scrie „Diriginte”, apoi în H2:

=VLOOKUP(C2;Clase!$A$2:$B$3;2;FALSE)

Pe părți:

Argument Ce înseamnă
C2 valoarea căutată (clasa elevului)
Clase!$A$2:$B$3 unde caută: foaia Clase, domeniul fix A2:B3 (de aceea cu $)
2 din care coloană a domeniului ia rezultatul (coloana a 2-a = diriginte)
FALSE potrivire exactă

Trage formula până la H13. Elevii din a VI-a A primesc „prof. Marin”, iar cei din a VI-a B „prof. Stoica”.

De ce așa?

De ce Clase!$A$2:$B$3 cu dolari? Domeniul căutării trebuie să rămână același când tragi formula în jos; fără $ ar aluneca la A3:B4, A4:B5 … și ai primi #N/A. Prefixul Clase! spune în ce foaie se află.

7. Foaia de rezumat

Adaugă o foaie nouă, Rezumat:

Celulă Conținut Formula
A1 Clasa
B1 Nr. elevi
C1 Media clasei
A2 a VI-a A
B2 =COUNTIF(Elevi!C2:C13;A2)
C2 =ROUND(AVERAGEIF(Elevi!C2:C13;A2;Elevi!G2:G13);2)
A3 a VI-a B
B3, C3 la fel, cu A3

Rezultate: a VI-a A → 6 elevi, media 7,33; a VI-a B → 6 elevi, media 6,56.

8. Proiectul final: „Catalogul clasei”

Pornește de la E5-start.xlsx. Livrabil: Catalog-E5.xlsx cu:

Cerință Puncte
media fiecărui elev (rotunjită la 2 zecimale) 15
coloana „Rezultat”: Promovat dacă media ≥ 5, altfel Corigent 10
validare 1–10 pe note 10
formatare condiționată: note sub 5 în roșu 15
rând de antet înghețat, antet formatat 10
diriginte cu VLOOKUP 15
foaia Rezumat cu număr de elevi și media pe clasă 15
un grafic cu coloane: media pe clasă 10
Total 100

9. Fișa de autoverificare

Ai reușit? Da / Nu
mediile sunt corecte (Andrei 8,33; Ionuț 4,67)
scrii 11 într-o notă și Excel refuză
notele sub 5 sunt colorate (10 în total)
derulând, antetul rămâne vizibil
H2 arată „prof. Stoica” (Andrei e în clasa B)
Rezumat arată 6 și 6 elevi, cu mediile 7,33 și 6,56

10. Mini-test

1. Ce face validarea datelor? a) colorează celule b) limitează ce poți scrie într-o celulă c) sortează d) adună

2. De ce folosim $ în Clase!$A$2:$B$3? a) pentru bani b) ca domeniul să nu alunece la copierea formulei c) este obligatoriu d) pentru a ascunde foaia

3. Ce înseamnă FALSE în VLOOKUP? a) potrivire exactă b) rezultat greșit c) ascunde d) sortează

4. =COUNTIF(Elevi!C2:C13;"a VI-a A") întoarce: a) media clasei A b) numărul de elevi din clasa A c) suma notelor d) eroare

Te-ai blocat la lecția asta?

Scrie-mi ce nu iese și vedem împreună de unde se blochează.

Răspunsuri

Mini-test

1. b). 2. b). 3. a) potrivire exactă. 4. b) numărul de elevi din clasa A (6).

Proiect

  • Medii: Andrei 8,33 · Bianca 7 · Cosmin 6 · Diana 7,67 · Eduard 5,33 · Flavia 8 · Gabriel 8 · Hanna 7 · Ionuț 4,67 · Julia 8,67 · Karina 7 · Lucian 5,67.
  • Rezultat: o singură medie e sub 5 (Ionuț, 4,67) → 1 corigent, 11 promovați. Formula: =IF(G2>=5;"Promovat";"Corigent").
  • Atenție: aici „promovat” se decide după medie, nu după fiecare notă (spre deosebire de fișa E3).
  • Dacă VLOOKUP dă #N/A: verifică dacă numele clasei din foaia Elevi este scris identic cu cel din foaia Clase (inclusiv spațiile).