SISergiu Ilincameditații informatică
Meniu

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
Cuprins

Fișiere PDF și de lucru

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.

Referințe

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.