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
Fișiere PDF și de lucru
- Fișa în PDF
versiunea de printat
- fise-progresive.zip
fișierele de pornire și modelele pentru fișele progresive (Word, Excel, PowerPoint, Access), 190 KB
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
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.
- Selectează
D2:F13. - Date → Validare date (Data Validation).
- Permite (Allow): Număr întreg (Whole number); Date: între (between); Minim 1; Maxim 10.
- 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.
- Selectează
D2:F13. - 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. - 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ă
VLOOKUPdă#N/A: verifică dacă numele clasei din foaia Elevi este scris identic cu cel din foaia Clase (inclusiv spațiile).