Excel - Formule si functii, Formatare

Excel. Formule si functii. Formatare.

referentierea celulelor - adrese

Adresele:

- identifica celule sau grupuri de celule pe o foaie de lucru;

- spun aplicatiei in care celule sa caute pentru a gasi valori ce vor fi folosite in formule.

adresele permit utilizarea datelor aflate:

- in diverse parti ale foii de lucru

- in alta foaie de lucru

- in alt registru de lucru

adresele permit utilizarea rezultatului unei alte formule

referintele catre celule din alt registru de lucru se numesc referinte externe

tipuri de referinte (adrese)

referinta (adresa) de tipul A1

Aceasta referinta este utilizata implicit de Excel. Coloanele sunt numerotate de la A la IV si liniile de la 1 la 65536, iar adresa celulei curente este afisata in caseta de denumire din partea stanga a barei cu formule.

de exemplu

C5 - pentru a referentia celula din coloana C, randul 5

C1:C5 - pentru a referentia grupul de celule din coloana C si randurile de la 1 la 5

A1:C1  - pentru a referentia grupul de celule din randul 1 si coloanele de la A la C

- pentru a referentia grupul de celule din randul 1

- pentru a referentia grupul de celule din randurile 1 pana la 3

A:A - pentru a referentia grupul de celule din coloana A

prin copierea unei formule care utilizeaza acest tip de referentiere formula din celula destinatie va fi modificata prin translatarea adreselor tuturor operanzilor.

Pentru exemplificare sa urmarim fig.4.1.3.1.

Celula D1 contine formula = A1*(B1-C1). Copiem aceasta formula in celula F4.

Daca vizulizam continutul celulei E2 vom constata ca ea contine formula = C4*(D4-E4).

Se observa ca translatarea A1→C4, B1→D4, C1→E4 se face cu 2 celule la dreapta si 3 celule in jos, ceea ce reprezinta translatia de la celula D1 la celula F4.

Fig.4.1.3.1

pentru a copia formula se procedeaza astfel

se selecteaza celula celula care contine formula (D1)

se executa clic dreapta si se alege comanda Copy. Celula D1 va fi inconjurata de o linie punctata mobila pentru a semnala ca operatia de copiere a fost efectuata.

se selecteaza celula destinatie (F4)

se executa clic dreapta ti se alege comanda Paste

reamintim ca prin copiere continutul celulei este depus in Clipboard.

referinta (adresa) de tipul R1C1

Aceasta referinta este utilizata cand atat randurile cat si coloanele sunt numarate.

de exemplu

R[-3]C - pentru a referentia o celula aflata trei randuri mai sus, pe aceeasi coloana

RC[-3] - pentru a referentia o celula aflata pe acelasi rand, trei coloane la stanga

R[-3]C - pentru a referentia o celula aflata pe aceeasi coloana, trei randuri mai sus

R[1]C[1] pentru a referentia o celula aflata un rand mai sus, o coloana la dreapta

R3C2 - pentru a referentia celula aflata pe randul 3 coloana 2

R[-2] - pentru a referentia intreg randul de sub celula curenta

R - pentru a referentia randul curent

C - pentru a referentia coloana curenta

referinta (adresa) relativa

Referinta (adresa) relativa este adresa in care se indica numai litera coloanei si numarul liniei si care aflata intr-o formula sau functie, se adapteaza atunci cand este copiata. Daca se copiaza formula pe coloana, se va modifica automat numarul randului, iar daca se copiaza formula pe un rand se va modifica automat denumirea alfabetica a coloanei corespunzatoare.

referinta (adresa) absoluta

Acest tip de referinta adreseaza intotdeauna aceeasi adresa. Adresa absoluta a unei celule dintr-o formula sau functie nu se schimba atunci cand formula este copiata in alta parte. Pentru a face ca o adresa dintr-o formula sau functie sa fie absoluta, trebuie sa ii adaugam semnul $ inaintea literei coloanei, respectiv a numarului liniei care reprezinta adresa celulei sau se actioneaza tasta F4 dupa ce respectiva celula a fost selectata.

celula A3 contine functia =SUM(A1:A2). Daca aceasta functie (formula) este copiata din celula A3 in celula B3, in celula B3 vom avea functia =SUM(B1:B2). Adresele A1, respectiv A2 au fost modificate prin copierea functiei in celula B3.

celula A3 contine functia =SUM($A$1:$A$2). Daca aceasta functie (formula) este copiata din celula A3 in celula B3, in celula B3 vom avea functia =SUM($A$1:$A$2), deci adresele $A$1 respectiv $A$2 au 'ramas' aceleasi chiar daca functia a fost copiata in celula B3.

Fig.4.1.3.2

referinta (adresa) mixta

Acest tip de referinta pastreaza constanta doar o parte din adresa celulei. Cand o formula sau functie in care s-a folosit o adresa mixta este copiata in alta celula, nu se va modifica in formula sau functia copiata, decat partea relativa a adresei.

A$1 - A este adresa relativa, iar $1 este adresa absoluta (care nu se modifica)

$A1 - $A este adresa absoluta (care nu se modifica), iar 1 este adresa relativa

presupunem ca celula A4 contine functia =SUM($A1:A$2).

daca aceasta functie este copiata din celula A4 in celula B4, atunci in celula B4 vom avea functia =SUM($A1:B$2), deci adresa relativa A (din adresa mixta A$2) s-a 'mutat' cu o coloana la dreapta si ajunge in coloana B.

Fig.4.1.3.3

referinta (adresa) cu ajutorul numelor sau etichetelor

Acest tip de referinta permite adresarea unei celule prin intermediul numelui sau. Pentru a eticheta o celula:

- Insert Name Define

Fig. 4.1.3.4

- in caseta Names in worbook: se introduce numele dorit

- in caseta Refers to: este afisata celula asociata cu respectiva eticheta. Pentru a asocia numele cu o anumita celula, se actioneaza butonul situat in partea dreapta a casetei, apoi se selecteaza celula dorita

Fig. 4.1.3.5

in cazul unei referinte prin nume de cate ori este selectata celula respectiva, in locul adresei va fi afisat numele celulei.

Fig. 4.1.3.6

pentru a denumi o celula sau un domeniu se poate utiliza si urmatoarea metoda

se selecteaza celula sau domeniul de celule care urmeaza sa fie denumit

se executa clic pe caseta Name

se introduce numele pentru celula sau domeniu

se actioneaza tasta Enter

numele unei celule sau al unui domeniu trebuie sa respecte urmatoarele reguli:

lungimea maxima este de 255 de caractere

primul caracter trebuie sa fie o litera sau caracterul de subliniere

numele poate cuprinde litere, cifre, puncte. Nu sunt permise alte semne de punctuatie

numele nu poate cuprinde spatii

numele nu poate fi o singura litera, o singura cifra

numele nu poate arata ca o referinta de celula - A3, G17, etc.

prin mutarea unei celule se muta o data cu continutul ei si numele ei

avantajele utilizarii numelor de celule sau domenii

formulele din foaia de lucru devin mai clare.

gasirea erorilor este mai usoara.

navigarea prin foaia de lucru este mai usoara, deoarece se poate sari direct la o anumita celula sau un anumit domeniu alegand numele din lista de nume.

lista derulanta cu nume poate fi folosita si pentru completarea formulelor. In loc de tastarea numelui (sau adresei) celulei se alege numele din lista si se executa clic.

pentru a elimina un domeniu denumit

Insert→Name→Define

se selecteaza din lista

se actioneaza butonul Delete

se actioneaza butonul OK

operatori de adresa

(doua puncte) indica o zona in foaia de lucru care are ca rezultat o adresa asociata blocului de date ce cuprinde toate celulele dintre doua adrese. De exemplu C2:E3 indica blocul de date format din celulele C2, C3, D2, D3, E2, E3.

(virgula) indica uniunea care are ca rezultat o adresa ce include cele doua adrese. De exemplu B3, D3

' ' (spatiu) indica intersectia care are ca rezultat o adresa pentru celulele comune celor doua adrese. De exemplu B7:D7 C6:C8 genereaza celula C7.

concepte de baza pentru scrierea formulelor

orice formula incepe cu semnul egal "

forma generala a unei formule este = expresie

expresie se compune din:

constante - intregi, zecimale, matriciale
- siruri de caractere
- logice

referinte de celule sau domenii de celule

referinte de functii

nume de celule, domenii de celule, constante si functii

operatori - matematici
- logici
- text

paranteze - pentru stabilirea ordinii in care se executa operatiile matematice

la selectarea unei celule:

formula este afisata in bara de formule

rezultatul formulei este afisat in celula corespunzatoare

la editarea unei formule ea este afisata atat in bara de formule cat si in celula in care a fost introdusa

daca se doreste ca in celule sa fie afisate formulele in locul rezultatelor:

se actioneaza combinatia de taste Ctrl + ` (apostroful din coltul stanga sus al tastaturii)
sau

Tools→Options si in fisa View se valideaza optiunea Formulas (fig. 4.1.3.7)

Fig. 4.1.3.7

operatori matematici

celulele referentiate intr-o formula sunt evidentiate printr-o bordura colorata.

in editarea unei formule se poate trage bordura de la o celula la alta modificand astfel adresa celulei din formula. De exemplu, daca formula este =A1 * B1 si se doreste modificarea ei in = A1 * C1, se trage bordura de la B1 la C1.

in mod similar, se poate extinde un domeniu. De exemplu, daca se doreste extinderea domeniului A1:A5 la A1:A7 se trage punctul de dimensionare in jos pana la celula A7.

punct de dimesionare

prin tragere in jos formula s-a modificat:

din SUM(A1:A5) a devenit SUM(A1:A7)

Fig. 4.1.3.8

Introducerea unei formule

se selecteaza celula in care se doreste introducerea formulei

se tasteaza semnul '='. Ca urmare, pe bara de start se afiseaza Enter (fig.4.1.3.9).

Fig. 4.1.3.9

se introduc elementele formulei. Formula este afisata pe bara de formule (fig.4.1.3.10)

Fig. 4.1.3.10

pentru a accepta formula se tasteaza Enter sau se efectueaza un clic pe simbolul Enter de pe bara de formule (fig.4.1.3.11).
Pentru a abandona formula se tasteaza Esc sau se efectueaza un clic pe simbolul Cancel de pe bara de formule (fig.4.1.3.12).

Fig.4.1.3.11  Fig.4.1.3.12

adresele celulelor implicate in formula se pot introduce de la tastatura sau se da clic pe celula a carei referinta trebuie sa apara in formula.

daca se foloseste mouse-ul pentru a indica celulele implicate intr-o formula in bara de stare se afiseaza textul Point (fig.4.1.3.13), pentru a indica faptul ca regimul de lucru este prin indicarea celulelor.

Fig. 4.1.3.13

pentru a edita o formula trebuie selectata celula care contine formula

editarea unei formule se poate realiza direct in celula care o contine prin efectuarea unui dublu clic in celula respectiva.

editarea unei formule se poate realiza in bara de formule prin efectuarea unui clic in bara de formule sau prin actionarea tastei F2.

celulele implicate in formula sunt indicate cu chenare colorate.

Erorile standard asociate formulelor

Cod eroare	Cauza erorii
#NULL!	Este specificata o zona formata din doua serii care nu se intersecteaza. De exemplu A1:B1 C4:D5
#DIV/0!	In formula se face o impartire cu zero. De exemplu =A1 / B1, iar continutul celulei B1 este zero
#VALUE!	Operatorii folositi in formula sunt incompatibili. De exemplu =A1 + B1, unde con'inutu; celulei A1 este numar iar continutul celulei B1 este text
#REF!	In formula se referentiaza o celula care nu mai exista. De exemplu o celula dintr-o foaie de calcul care a fost stearsa
#NAME?	In formula se utilizeaza un nume de celula sau un nume de functie inexistent. De exemplu = Cerc(1,2)
#NUM!	In formula se utilizeaza un operand numeric care nu apartine domeniului admisibil De exemplu = LN(-1), logaritmul nu poate avea argument negativ
#N/A	in formula se referentiaza o celula al carei continut nu este disponibil la momentul executiei formulei.

Lucrul cu functii

se executa comanda Function din meniul Insert

sau se da clic pe butonul (Insert function) situat in partaea stanga a barei de formule

din lista Select a category se alege categoria functiei (matematice, statistice, financiare, )

din lista Select a function se alege functia dorita si se actioneaza butonul OK.

in fereastra specializata Function arguments se introduc operanzii functiei selectate

pentru a usura selectarea celulelor fereastra Function Arguments poate fi minimizata prin actionarea butonului

revenirea la dimensiunea normala a acestei ferestre se face prin actionarea butonului

Utilizarea functiilor suma, medie, minim, maxim, numarare.

Pentru a calcula suma unei serii se executa comanda Function din meniul Insert.

din lista Select a function se alege functia SUM si se actioneaza butonul OK.

in urma selectarii unei functii in partea de jos a ferestrei este prezentata sintaxa comenzii (daca se doreste introducerea comenzii de la tastatura).

in casetele number1, number2, se introduc (sau se selecteaza) seriile care vor fi insumate

numarul maxim de operanzi permis este de 30

Formula result afiseaza valoarea calculata a functiei

o metoda mai simpla pentru calcularea sumei unei serii este utilizarea butonului AutoSum

pentru a calcula media aritmetica, elementul minim sau maxim al unei serii sau pentru a numara termenii unei serii se procedeaza ca si in cazul calculului unei sume, cu diferenta ca se va apela din domeniul Statistical una din functiile AVERAGE, MIN, MAX, COUNT.

Utilizarea functiei IF

Pentru rezolvarea situatiilor in care trebuie sa alegem intre doua posibilitati este necesara utilizarea functiei IF. Aceasta functie permite evaluarea unei expresii si returnarea unei valori daca in urma evaluarii conditia este indeplinita si o alta valoarea daca in urma evaluarii conditia nu este indeplinita.

Sintaxa functiei IF este urmatoarea:

IF(logical_test,value_if_true,value_if_false)

variabila logical_test returneaza valoarea 1 daca expresia evaluata este adevarata si 0 daca expresia evaluata este falsa.

variabila logical_test poate contine orice valoare sau orice expresie ce poate fi evaluata ca fiind adevarata sau falsa.

operatorii de comparare care pot fi folositi in variabila logical_test sunt:

> - mai mare,

>= - mai mare sau egal,

< - mai mic,

<= - mai mic sau egal

<> - diferit

variabila value_if_true contine valoarea returnata de functia IF daca conditia este adevarata.

variabila value_if_false contine valoarea returnata de functia IF daca conditia este falsa.

Exemple:

functia IF poate fi argument al altei functii IF

IF(A1>=10,IF(A1<=20,A2+A3,A2*A3),A2-A3)

in urma executiei acestei formule se returneaza:

- suma dintre continutul celulei A2 si continutul celulei A3, daca continutul celulei A1 apartine intervalului inchis [10,20]

- produsul dintre continutul celulei A2 si continutul celulei A3, daca continutului celulei A1 este mai mare decat 20

- diferenta dintre continutul celulei A2 si continutul celulei A3, daca continutul celulei A1 este mai mic decat 10

formatarea numerelor

se selecteaza celula sau celulele ce urmeaza sa fie formatate

se executa comanda Cells din meniul Format sau se executa clic dreapta si se alege comanda Format cells

in fereastra Format Cells se actioneaza butonul (tab-ul) Number

se alege din lista Category, optiunea Number

in caseta Decimal places, se stabileste numarul de cifre pentru partea zecimala

in caseta Use 1000 Separator(,) se bifeaza daca se doreste utilizarea virgulei ca separator pentru mii

in caseta Negative numbers se stabileste modul de reprezentare al numerelor negative

se actioneaza butonul OK

formatarea datelor calendaristice

se selecteaza celula sau celulele ce urmeaza sa fie formatate

se executa comanda Cells din meniul Format sau se executa clic dreapta si se alege comanda Format cells

in fereastra Format Cells se actioneaza butonul (tab-ul) Number

se alege din lista Category, optiunea Date

din lista Type se alege modul de afisare al datei calendaristice

se actioneaza butonul OK

lista Type este diferita in functie de alegerea locatiei din caseta Locale (location)

formatarea valorilor de tip procent

se selecteaza celula sau celulele ce urmeaza sa fie formatate

se executa comanda Cells din meniul Format sau se executa clic dreapta si se alege comanda Format cells

in fereastra Format Cells se actioneaza butonul (tab-ul) Number

se alege din lista Category, optiunea Percentage

in caseta Decimal places, se stabileste numarul de cifre pentru partea zecimala

se actioneaza butonul OK

formatarea textului

se selecteaza celula sau celulele care urmeaza sa fie formatate

se executa comanda Cells din meniul Format sau se executa clic dreapta si se alege comanda Format cells

in fereastra Format Cells se actioneaza butonul (tab-ul) Font

se alege fontul dorit din lista Font

se alege stilul:
Regular = stil obisnuit (valoareimplicita)

Italic = inclinat (cursiv)
Bold = ingrosat (aldin)

Bold Italic = ingrosat si inclinat

din lista Size se alege dimensiunea fontului

din lista Underline se alege stilul de subliniere:
Single, Single Accounting = subliniere simpla
Double, Double Accounting = subliniere dubla

optiunile Efects permit:
Strike Through = taierea textului
Superscript = scrierea indicilor superiori
Subscript = scrierea indicilor inferiori

se actioneaza butonul OK

efectul alegerii unei formatari este afisat in caseta Preview

o metoda mai rapida pentru formatarea unui text este utilizarea butoanelor situate pe bara de formatare (fig.4.1.3.14)

Fig. 4.1.3.14

dublu subliniat

subliniat

inclinat

bold (ingrosat)

alegerea dimensiunii fontului

alegerea fontului

pentru a aplica o culoare continutului unei celule sau unui grup de celule

se selecteaza celula respectiva sau grupul de celule

se executa comanda Cells din meniul Format

se executa clic pe butonul   asociat casetei Color

se alege culoarea dorita din paleta de culori disponibila

se actioneaza butonul OK

o modalitate mai rapida o reprezinta actionarea sagetii asociate butonului  (Font Color) situat pe bara de formatare, ceea ce permite alegerea culorii dorite din paleta oferita. Actionarea butonului are ca efect selectarea culorii utilizate anterior.

pentru a aplica o culoare de fundal unei celule sau unui grup de celule

se selecteaza celula respectiva sau grupul de celule

se executa comanda Cells din nmeniul Format

se actioneaaza butonul Patterns

din fereastra care se deschide se selecteaza culoarea dorita

daca se deschide lista combinata Pattern se poate alege un model de hasurare

rezultatul selectiei este afisat in zona Sample

o cale mai simpla de aplicare a unei culori de fond este utilizarea butonului

( Fill Color) situat pe bara de formatare

pentru a copia formatarea unei celule sau unui grup de celule

se selecteaza celula sau grupul de celule a carei formatare se copiaza

se actioneaza butonul Format Painter  de pe bara de instrumente. Cursorul se transforma intr-o pensula.

se selecteaza celula sau grupul de celule unde se doreste copierea formatarii. In momentul eliberarii butonului stang al mouse-ului formatarea este aplicata destinatiei.

daca se doreste copierea formatarii in locuri diferite din foaia de calcul, se va da dublu clic pe butonul Format Painter.

pentru a incadra textul intr-o celula

se selecteaza celula sau grupul de celule in care se doreste scrierea pe mai multe randuri

se executa comanda Cells din meniul Format

se alege optiunea Alignement

se selecteaza Wrap Text

pentru a ajusta textul dintr-o celula

se selecteaza celula sau grupul de celule in care se doreste scrierea pe mai multe randuri

se executa comanda Cells din meniul Format

se alege optiunea Alignement

se selecteaza Shrink to fit

Fig.4.1.3.15

pentru a centra textul pe mai multe coloane

se selecteaza celulele care urmeaza sa fie imbinate

se actioneaza butonul   Merge and Center aflat pe bara de instrumente

sau

se selecteaza celulele care urmeaza sa fie imbinate

se selecteaza comanda Cells din meniul Format

se bifeaza Merge cells din sectiunea Text control

Fig. 4.1.3.16

imbinarea mai multor celule pretinde ca doar celula din stanga sa contina informatii

in caz contrar suntem informati ca selectia contine date multiple si in urma contopirii celulelor se pastreaza doar continutul celulei din coltul stanga-sus

Fig. 4.1.3.17

pentru a aplica o formatare conditionata

se selecteaza celula sau celulele carora trebuie sa le aplicam formatarea

se executa comanda Conditional Formating din meniul Format

daca se alege in caseta Condition 1 se alege optiunea Cell Value Is, atunci in caseta urmatoarele 3 casete se poate stabili ce conditie sa indeplineasca valorile din celulele selectate

se actioneaza butonul Format pentru a stabili formatarea care se va aplica daca conditia este indeplinita

Fig. 4.1.3.18

sau

daca in caseta Condition 1 se alege optiunea Formula Is, atunci in caseta urmatoare se va introduce o formula

se actioneaza butonul Format pentru a stabili formatarea care se va aplica daca celula selectata contine formul definita in caseta precedenta

alinieri

pentru alinierea textului se pot utiliza cele 4 butoane de aliniere situate pe bara de instrumente:

- aliniere la stanga

- centrare

- aliniere la dreapta

pentru alinierea textului se poate proceda si dupa cum urmeaza:

din meniul Format se executa comanda Cells

seactioneaza butonul Alignement

din lista combinata Horizontal se selecteaza alinierea orizontala

din lista combinata Vertical se selecteaza alinierea verticala

Fig.4.1.3.18

alinierile orizontale posibile sunt:

General - este alinierea implicita: textul la stanga, numerele la stanga, mesajele de eroare si variabilele logice centrate

Left - alinierea la stanga

Center - centrare

Right - alinierea la dreapta

alinierile verticale posibile sunt:

Top - aliniere sus

Center - centrare

Bottom - aliniere jos

pentru a modifica orientarea textului

se selecteaza celula sau celulele pentru care se doreste modificarea orientarii textului

se executa comanda Cells din meniul Format

se actioneaza butonul Alignement

se stabileste unghiul cu care doriti sa modificati orientarea textului in caseta Degrees (prin introducere de la tastatura sau prin actiionarea sagetilor din dreapta casetei)

(vezi fig.4.1.3.18)

pentru a adauga borduri unei celule sau unui grup de celule

se selecteaza celula sau grupul de celule

se executa comanda Cells din meniul Format

se actioneaza butonul Borders din fereastra Format Cells

din caseta Style se alege tipul liniei

din lista derulanta Color se alege culoarea liniei

butoanele din sectiunea Border permite stabilirea care margine a celulei va fi bordata

butoanele Presets permite selectarea bordurii exterioare, respectiv a liniilor interioare

Fig. 4.1.3.19