TIC · Fișe progresive · Excel 2/5
Excel, nivelul 2: formule și referințe (relative și absolute)
Materiale TICMaterialul 11 din 34 · mai sunt 23Urmează: Excel, nivelul 3: funcții (AVERAGE, MIN, MAX, COUNT, IF, COUNTIF, ROUND)Vezi 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ă: 45–60 min · Nivel: începător–mediu · Fișier de pornire: materiale/E2-start.xlsx · Model: materiale/modele/E2-model.xlsx
Ai nevoie de: Excel 1.
Ce vei ști după fișă
- scrii formule cu cele patru operații (
+ - * /) și respecți ordinea lor; - înțelegi diferența dintre referința relativă (
B4) și absolută ($B$1); - folosești formatul de celulă (zecimale, monedă, procente).
1. Operatori și ordinea operațiilor
| Operație | Semn | Exemplu | Rezultat |
|---|---|---|---|
| adunare | + |
=3+4 |
7 |
| scădere | - |
=10-4 |
6 |
| înmulțire | * |
=3*4 |
12 |
| împărțire | / |
=12/4 |
3 |
| putere | ^ |
=2^3 |
8 |
| procent | % |
=20% |
0,2 |
Ordinea este cea din matematică: mai întâi paranteze, apoi ^, apoi * /, apoi + -. De exemplu =2+3*4 dă 14, iar =(2+3)*4 dă 20.
2. Referințe relative
Când copiezi o formulă în jos sau în dreapta, adresele din ea se mută odată cu ea.
Exemplu: în C4 ai =B4*0,19. Copiată în C5 devine =B5*0,19; în C6, =B6*0,19. Este referință relativă: „celula din stânga mea, pe același rând”.
3. Problema: TVA-ul
Deschide E2-start.xlsx. În B1 este rata TVA-ului (0,19 = 19%). Sub ea e un tabel cu produse:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 3 | Produs | Preț fără TVA | TVA | Preț cu TVA | Cantitate | Valoare |
| 4 | Caiet | 8,5 | 3 | |||
| 5 | Pix | 2 | 2 | |||
| 6 | Rucsac | 120 | 4 | |||
| … | … | … | … |
Vrei: TVA = Preț × rata; Preț cu TVA = Preț + TVA; Valoare = Preț cu TVA × Cantitate.
În C4 scrii =B4*B1 și tragi în jos. Rezultatul e greșit! În C5 formula a devenit =B5*B2, iar B2 e gol; în C6 apare B3, un text, și vezi eroarea #VALUE!. Referința B1 s-a mutat odată cu formula.
4. Referința absolută: $
Pentru ca adresa să rămână fixă, o blochezi cu semnul dolar: $B$1.
| Scriere | Se comportă | Exemplu |
|---|---|---|
B1 |
relativă (și coloana, și rândul se mută) | B1 → B2 când cobori |
$B$1 |
absolută (nu se mută deloc) | rămâne $B$1 |
B$1 |
rândul fix, coloana se mută | |
$B1 |
coloana fixă, rândul se mută |
Scurtătură: scrii B1 în formulă și apeși F4: devine $B$1.
În C4 scrie =B4*$B$1, în D4 =B4+C4, în F4 =D4*E4. Selectează C4:F4 și trage în jos până la rândul 9. În celula F10 calculezi totalul comenzii: =SUM(F4:F9).
De ce așa?
De ce rata TVA stă într-o celulă separată (B1)? Dacă TVA-ul se schimbă de la 19% la 21%, modifici o singură celulă și tot tabelul se recalculează. Dacă ai fi scris *0,19 în fiecare formulă, ar trebui să le corectezi pe toate (și poți uita una).
5. Rezultate corecte
| Produs | Preț fără TVA | TVA | Preț cu TVA | Cantitate | Valoare |
|---|---|---|---|---|---|
| Caiet | 8,5 | 1,615 | 10,115 | 3 | 30,345 |
| Pix | 2 | 0,38 | 2,38 | 2 | 4,76 |
| Rucsac | 120 | 22,8 | 142,8 | 4 | 571,2 |
| Penar | 25 | 4,75 | 29,75 | 1 | 29,75 |
| Riglă | 3,5 | 0,665 | 4,165 | 1 | 4,165 |
| Foarfecă | 6 | 1,14 | 7,14 | 5 | 35,7 |
| Total | 675,92 |
Total: 30,345 + 4,76 + 571,2 + 29,75 + 4,165 + 35,7 = 675,92.
6. Formatul numerelor
Selectează B4:D9 și F4:F10 → Pornire → Format număr: Număr cu 2 zecimale (sau Monedă / Currency cu simbolul lei). Excel doar afișează rotunjit; valoarea reală din celulă rămâne cu toate zecimalele.
Rata B1 poate fi afișată ca 19% cu butonul % (valoarea reală rămâne 0,19).
De știut
Dacă vezi ##### într-o celulă, coloana e prea îngustă pentru număr: lărgește-o (dublu clic pe marginea antetului). Dacă vezi #DIV/0!, ai împărțit la zero sau la o celulă goală. #NAME? înseamnă un nume de funcție scris greșit.
7. Fișa de autoverificare
| Ai reușit? | Da / Nu |
|---|---|
C4 conține =B4*$B$1 |
|
D4 conține =B4+C4, iar F4 conține =D4*E4 |
|
| toate cele 6 produse au formule (trase în jos) | |
totalul F10 este 675,92 |
|
dacă schimbi B1 în 0,21, totalul se modifică |
|
numerele au 2 zecimale, iar B1 apare ca 19% |
8. Exerciții în plus
1. Tabel de conversie: în B1 cursul euro-leu (de exemplu 4,97); în coloana A sume în euro (10, 20, 50, 100), iar în B formula =A4*$B$1 pentru valoarea în lei.
2. Tabla înmulțirii: scrie în A2:A10 numerele 1–9 și în B1:J1 numerele 1–9; în B2 o singură formulă (=$A2*B$1) pe care o tragi în toate direcțiile. Ce observi?
3. Reducere: preț în A4:A8, reducerea (20%) în B1, prețul redus în B4:B8 cu o referință absolută.
9. Mini-test
1. Ce arată =2+3*4?
a) 20 b) 14 c) 24 d) 9
2. Pentru ca o adresă să nu se mute la copiere, o scrii: a) B1 b) $B$1 c) B1$ d) #B#1
3. Ce scurtătură adaugă $ la adresă?
a) F2 b) F4 c) F5 d) F12
4. Ce înseamnă ##### într-o celulă?
a) formulă greșită b) coloana e prea îngustă c) celula e goală d) număr negativ
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) 14. 2. b) $B$1. 3. b) F4. 4. b).
Exerciții în plus
1. Pentru 10 euro la 4,97 → 49,7 lei; 20 → 99,4; 50 → 248,5; 100 → 497.
2. Formula =$A2*B$1 are coloana A fixă ($A) și rândul 1 fix ($1), deci se poate trage în toate direcțiile. Rezultatul este tabla înmulțirii: de exemplu la rândul 7 și coloana 8 apare 56.
3. Formula din B4 este =A4*(1-$B$1). Pentru un preț de 50 lei și reducere 20% rezultă 40.