Formule și funcții în Microsoft Excel 2010Formule și funcții în Microsoft Excel 2010 Tema 3...

Formule și funcții în Microsoft Excel 2010 Tema 3

Suport de curs, anul I Pag. 1

Unități de învățare:

Construirea formulelor de calcul

Utilizarea diferitelor tipuri de referințe în foile de calcul

Aspecte generale privind utilizarea funcțiilor predefinite

Obiectivele temei:

construirea formulelor și utilizarea operatorilor în cadrul acestora;

utilizarea referințelor relative, absolute și mixte;

lucrul cu referințele externe;

utilizarea numelor de celule și domenii în formulele de calcul;

utilizarea facilităților oferite de Excel în lucrul cu funcții predefinite și completarea corectă a argumentelor acestora;

imbricarea funcțiilor pe mai multe niveluri;

protejarea și ascunderea formulelor scrise în celulele foii de calcul;

identificarea erorilor în formulele foii de calcul.

Bibliografie recomandată:

Abbot Katz, Beginning Microsoft Excel 2010, Apress, New York, 2010

Andre Odnoha, Excel 2010 Financials Cookbook, Packt Publishing, Birmingham, 2011

Curtis D. Frye, Microsoft Excel 2010 – Step by step, Microsoft Press, Washington, 2010

John Walkenbach, Microsoft Excel 2010, Wiley Publishing, Inc., Indianapolis, 2010

Lițoiu V., Buligiu I, Bușe R., Etegan C., Bădică A., Ionașcu C., Birotică, Editura Universitaria, Craiova, 2005, pag 160-167

Silvia Curteanu, Excel prin exemple, Polirom, Iași, 2004

Wayne L. Winston, Microsoft Excel 2010 – Data Analysis and Business Modeling, Microsoft Press, Washington, 2011

Tema 3 FORMULE ȘI FUNCȚII ÎN MICROSOFT EXCEL 2010


Pag. 2

3.1 CONSTRUIREA FORMULELOR

Cu ajutorul formulelor se pot efectua calcule asupra celulelor din aceeași foaie de calcul, din foi de calcul diferite și chiar din registre de calcul diferite (ceea ce leagă registrele și foile de calcul între ele).

În Excel o formulă întotdeauna începe cu semnul =. Introducerea formulelor se poate face direct, folosind tastatura, sau cu mouse-ul.

O formulă poate conține oricare din următoarele elemente: - operatori matematici, cum ar fi cei pentru adunare (+), scădere (-),

înmulțire (*), împărțire (/) ș.a.; - referințe de celule (incluzând și domenii de celule și nume de domenii); - valori sau text; - funcții (de ec. SUM sau AVERAGE). Excel oferă o serie de facilități pentru a ușura scrierea formulelor, de

exemplu dacă un domeniu care urmează a fi inclus în formulă, utilizatorul îl poate selecta cu mouse-ul pe foaia de calcul, operatorul de domeniu : va fi inserat automat de către Excel. La fel și operatorul de reuniune (, sau ;), atunci când sunt selectate domenii sau celule neadiacente.

Lucrul cu operatori în formule Cu ajutorul operatorilor, Excel vă permite utilizarea unei mari varietăți de

operații în formulele scrise. Operatorii sunt simboluri care indică ce operație matematică se dorește a fi efectuată.

În continuare, sunt prezentați operatorii cu care puteți lucra în cadrul formulelor:

Operator Denumire operației

+ Adunare

- Scădere

* Înmulțire

/ Împărțire

^ Ridicare la putere (exponențiere)

& Concatenare (pentru text)

= Comparare logică (egal cu)

> Comparare logică (mai mare)

< Comparare logică (mai mic)

>= Comparare logică (mai mare sau egal)



- în loc să scriem direct A1:C20, se poate selecta domeniul cuprins între A1 și C20; pentru aceasta cel mai indicat este să dați mai întâi un click în celula A1, apoi apăsați tasta Shift și dați click în celula C20 .

În exemplu de mai sus, pentru a scrie formula =D5*E5, în loc să scriem totul de la tastatură, este mai indicat să parcurgem următoarea secvență:

1. mai întâi de tastează operatorul = 2. dăm click în celula D5 3. se tastează operatorul * 4. dăm click în celula E5 5. validăm formula prin apăsarea tastei Enter.

Scrierea formulei se încheie întotdeauna cu apăsarea tastei Enter (de la tastatură sau de pe bara de formule) sau printr-un clic în afara celulei. Ulterior acestei operații, în celula în care s-a introdus formula este vizibil rezultatul calculului, dar în bara de formule este afișată formula de calcul. Cu alte cuvinte, în celula respectivă se află, de fapt, o formulă, ceea ce presupune atenție în utilizarea conținutului celulei (de exemplu, într-o copiere).

Figura 3.1 – Editarea unei formule în Excel

La crearea unor formule care conțin mai mult de un operator, Excel utilizează o anumită ordine pentru a calcula rezultatul, respectiv:

Operator Denumire operației Precedență

^ Ridicare la putere (exponențiere) 1

* Înmulțire 2

/ Împărțire 2

+ Adunare 3

- Scădere 3

& Concatenare (pentru text) 4

= Comparare logică (egal cu) 5

> Comparare logică (mai mare) 5

< Comparare logică (mai mic) 5

Operatorii de referire (referințele de celule) sunt reprezentați de numele celulelor, respectiv asocierea literă-cifră ce desemnează linia și coloana la intersecția cărora se află celula.


Pag. 4

Când, într-o formulă, un operand se referă la mai multe celule se folosesc următorii operatori:

1. Operatorul de domeniu 2. Operatorul de reuniune 3. Operatorul de intersecție. 1. Caracterul : (două puncte), folosit pentru a defini un domeniu, mai

poartă și numele de operator de domeniu. De exemplu, A1 și C3 fiind referințe de celule, A1:C3 este domeniul care include toate celulele de la A1 la C3 (A1, A2, A3, B1, B2, B3, C1, C2, C3). Rezultatul formulei din figura 3.2 – însumarea valorii tuturor celulelor anterior menționate - va fi 95.

Figura 3.2 – Utilizarea operatorului de domeniu

Se pot include într-un domeniu toate celulele dintr-o linie sau dintr-o coloană sau din mai multe linii sau din mai multe coloane. De exemplu, scriind E:E sunt specificate toate celulele din coloana E, iar 3:3 indică toate celulele din linia 3. E:G se referă la toate celulele din coloanele E, F și G.

2. Un alt operator de referire este caracterul ; (punct și virgulă), numit operator de reuniune, deoarece reunește mai multe referințe de celule sau domenii. De exemplu, A1;C3 înseamnă celulele A1 și D3, iar rezultatul formulei din figura 3.3 va fi 33.

Figura 3.3 – Utilizarea operatorului de reuniune



Operatorul de reuniune (uniune) poate fi folosit împreună cu operatorul de domeniu. De exemplu, A1:D4;F1:H4 înseamnă domeniile A1:D4 ș i F1:H4.

Atenție: În funcție de setările sistemului de operare, operatorul de reuniune poate fi caracterul ; (punct și virgulă) sau caracterul , (virgulă).

3. Operatorul de intersecție este caracterul spațiu. De exemplu, în figura 3.4 rezultatul va fi zero (0), deoarece domeniile A1:A3 și B2:C3 nu au nici un element comun.

În figura 3.5, rezultatul afișat în celula A5, suma intersecției dintre domeniile A1:C2 și B2:C3, este 17 deoarece cele două domenii au în comun celulele B2 și C2.

Ordinea prestabilită a operațiilor în calculul unei formule poate fi modificată folosind paranteze care împart formula în segmente. Excel efectuează mai întâi toate operațiile din paranteze, în sensul dinspre interior către exterior, apoi, conform ordinii operațiilor, pe celelalte.

Figura 3.4 – Utilizarea operatorului de intersecție

Figura 3.5 – Utilizarea operatorului de intersecție

Utilizarea referințelor în formulele de calcul Excel O referință la o celulă este reprezentată de adresa celulei în cadrul foii de

calcul, dată de litera coloanei și numărul liniei pe care se află. Într-o formulă, referința unei celule se poate determina fie notând linia și coloana la intersecția cărora se găsește celula respectivă, fie selectând, cu mouse-ul celula respectivă.


Pag. 6

Celulele pot avea diferite tipuri de referințe, în funcție de modul în care vor fi utilizate în formule: relative, absolute și mixte.

O referință (adresare) relativă este o locație relativă la poziția operanzilor față de celula în care se va depune rezultatul formulei. Referințele relative se actualizează la modificarea poziției formulei (se schimbă în funcție de poziția formulei în foaia de calcul).

O celulă poate avea un conținut vizibil (extern) – reprezentat de valoarea datelor afișate în celulă și un conținut ascuns (intern, invizibil) – reprezentat de formulele afișate în bara de formule și care generează conținutul vizibil. De exemplu, celula F2 din figura 3.6 are conținut vizibil: numărul 27 și conținut ascuns: =B2+C2

Figura 3.6 – Adresarea relativă într-o foaie de calcul Excel

De, exemplu, să considerăm următorul exemplu (figura 3.6.): celula F2 conține formula =B2+C2 (rezultat 12+15=27). Dacă vom copia această formulă în tot domeniul F2:H4 (fie în mod clasic, cu ajutorul comenzilor Copy-Paste, fie utilizând butonul de extindere a formatării), programul Excel schimbă automat formula astfel:

o în celula F3 formula devine B3+C3 (rezultat 10+9=19); o în celula F4 formula devine B4+C4 (rezultat 4+5=9); o în celula H2 formula devine D2+E2; o în celula H4 formula devine D4+E4 etc.

Este modul de referire relativă. Referința relativă este acea adresă de celulă dintr-o formulă, care se modifică atunci când formula este copiată. În mod normal, programul Excel interpretează referirile la celule și domenii din cadrul unei formule ca adresări relative. Atunci când se copiază sau se mută formula, programul Excel redefinește automat adresările operanzilor, astfel încât să reflecte poziția lor relativă față de noua locație.

Sunt cazuri în care nu este nevoie ca adresele celulelor care indică operanzii să fie modificate când formulele sunt copiate. În această situație se folosesc referințe absolute la numele celulei (adresare absolută). Formulele care



conțin referințe absolute se vor referi în continuare la aceleași celule, chiar dacă se mută formula în altă poziție (aceste referințe sunt fixate definitiv). Deci, dacă este necesară folosirea valorilor unor celule în diferite locuri din foaia de calcul, se vor folosi referințe absolute. Acestea se construiesc cu ajutorul simbolului $. De exemplu: $A$1 (celula A1 desemnată prin referință absolută) sau $A$1:$B$4 (domeniul absolut Al : B4).

Figura 3.7 – Adresarea absolută într-o foaie de calcul

Deci, o referință este absolută atunci când formula se copiază sau se mută într-o nouă locație, iar operanzii indică aceeași celule ca în original. Pentru a construi o adresare absolută se adaugă semnul dolar ($) înaintea literei și numărului ce alcătuiesc adresa celulei. De exemplu, în figura 3.7, scriem în celula F2 formula =$B$2+$C$2 (rezultat 12+15=27). La prima vedere nu este nici o modificare față de exemplul precedent. Dar, acum, oriunde am copia această formulă, operanzii și implicit rezultatul rămân aceeași =$B$2+$C$2 (rezultat 12+15=27).

O referință (adresă) care este numai parțial absolută, ca de exemplu B$4 sau $B4, este numită referință mixtă (referință parțial relativă sau referință parțial absolută). Dacă o formulă care utilizează o referință mixtă este copiată către altă celulă, numai o parte din adresele operanzilor vor fi modificate.

Nu este obligatoriu ca referințele de domenii să fie în întregime absolute sau relative. Se pot construi, după necesități, și referințe mixte. O referință mixtă poate avea coloana absolută și linia relativă sau coloana relativă și linia absolută. Semnul $ (dolar) indică linia sau coloana ca fiind absolută, deci care nu se schimbă. De exemplu, pentru ca la mutarea formulei să se mențină neschimbată coloana C, dar să se modifice linia, se utilizează o referință mixtă de forma $C3. Invers, C$3 fixează linia și permite schimbarea coloanei.

Pentru fiecare celulă există patru tipuri de exprimare a referinței: relativ, absolut și două tipuri mixte. Pentru celula A1 se pot identifica următoarele patru exprimări ale adresei sale: A1 – referință relativă; $A$1 – referință absolută; $A1 și A$1 – referințe mixte.

Pentru a schimba tipul referinței unei celule într-o formulă, există o metodă mai rapidă decât tastarea semnului dolar:

o se efectuează dublu clic pe celula care conține formula; în figura 3.6, pe celula F2, care conține formula = B2 + C2;

o în interiorul formulei se efectuează clic pe referința celulei al cărei tip urmează a fi modificat (B2 în exemplul considerat în figura 3.7);

o se apasă tasta (F4) până când referința va fi de tipul dorit; apăsarea repetată a acestei

o taste va comuta în mod ciclic între toate tipurile posibile de referințe. Referințe externe În practică, se întâlnesc des cazurile în care este necesară efectuarea de

calcule cu adrese de celule ce fac parte din altă foaie de calcul decât cea în care ne aflăm (foaia de calcul curentă).

Astfel de referințe poartă denumirea de referințe externe (sau referințe 3D), care includ numele foii de calcul și nume de celule sau domenii (un exemplu


Pag. 8

de referință externă este Sheet2!A4 care referă conținutul celulei A4 ce face parte din foaia de calcul ”Sheet2”.

Cea mai simplă metodă de a insera o astfel de referință într-o formulă este ca, la momentul editării formulei, chiar pentru exemplul de mai sus, mai întâi se scrie simbolul ”=”, apoi din zona de etichete foi de calcul

, se alege foaia de calcul în care se află celula la care se va face referirea, , cazul nostru foaia Sheet2 și apoi se selectează din această foaie de calcul celula A4, moment în care vom vedea în zona de editare a formulei construcția =Sheet2!A4

Lucrurile nu se opresc aici: se poate face referire la conținutul celulelor ce fac parte și din alte registre de calcul, adică alte fișiere Excel, numite registre sursă, aceste referințe externe de registru sau mai sunt numite și legături.

Aceste referințe externe de registru au un regim de lucru aparte, deoarece legătura creată presupune actualizarea automată a datelor atunci când datele la care se face referire din registrul sursă sunt modificate, cu condiția ca ambele registre (destinație și sursă) să fie ambele deschise, altfel este necesară confirmarea actualizării la deschiderea registrului sursă.

Exemplu de formă pentru o referință externă este următorul: =[Salarii.xlsx]Ianuarie!$A$3

Într-o referință externă de registru, numele registrului sursă la care se face referirea, este încadrat între paranteze pătrate, urmat imediat de numele foii de calcul, apoi separator se folosește semnul exclamării și apoi referința celulei, formând următoarea sintaxă [nume_registru_calcul_sursă]nume_foaie_de_calcul!referință_celulă

Formulele creează legături între registrele de calcul, astfel încât dacă registrul destinație (cel în care se face referirea) și cel sursă (surse) sunt deschise, orice modificare a datelor implicate în referințele sursei (surselor) este efectuată automat și în registrul destinație.

Dacă registrele sunt închise, la deschiderea registrului destinație (numit și registru dependent), utilizatorul va fi întrebat asupra actualizării informațiilor de legătură.

Figura 3.8 – Confirmarea actualizării legăturilor de date

Dacă se apasă butonul Update, sistemul actualizează valorile curente din registrele de calcul sursă, dacă se alege butonul Don’t Update, acest lucru nu va fi făcut.

Deschiderea unuia din registrele sursă determină recalcularea automată a formulei din registrul destinație, cu noile valori din registrul sursă deschis.

Utilizarea în formule a numelor de celule și domenii



Pentru a adăuga eficiență în scrierea formulelor, se pot folosi în locul referințelor de celule sau domenii, nume ale acestora. De exemplu, puteți scrie o formulă de calcul Excel sub forma =procent_tva*total_factura, unde atât procent_tva cât și total_factură sunt nume asociate de către utilizator pentru două referințe de celule care conțin aceste valori. Formula de mai sus este echivalentă cu expresia =A7*D20, dar să recunoaștem că prima este mai ușor de interpretat și de utilizat.

După crearea numelor de celule sau a etichetelor, acestea vor fi folosite automat în locul referințelor atunci când celulele respective sunt incluse în formule.

Trebuie respectate anumite reguli la atribuirea acestor nume ca identificatori:

- să înceapă cu o literă sau caracterul de subliniere (_); - nu trebuie să conțină spații, pentru separarea cuvintelor se poate folosi

liniuța de subliniere sau majuscule; - nu se recomandă folosirea puncte în numele date, deoarece interferă cu

sintaxele VBA ce utilizează acest separator la subordonarea obiectelor; - numărul maxim de caractere este de 255. Utilizarea în formule a numelor pentru identificarea celulelor este similară

utilizării referințelor absolute. Cea mai rapidă metodă de a denumi o celulă sau

un domeniu este prin utilizarea casetei de nume (imaginea din stânga, situată în partea stângă a barei de editare a formulei, chiar deasupra foii de calcul.

Pentru aceasta, mai întâi se selectează celula sau domeniul de celule care urmează a fi denumit, apoi se efectuează click în caseta de nume și se tastează numele dorit și se apasă tasta Enter. În momentul în care se selectează domeniul etichetat anterior, în caseta Name va apărea numele acestuia.

Pe parcursul lucrului cu foaia de calcul se vor crea mai multe nume, iar dacă se dorește la un moment dat selectarea unui nume, se accesează caseta Name, pe indicatorul de listă derulantă și se alege numele dorit.

Pentru gestionarea denumirilor date celulelor, se folosește tab-ul Formulas din ribbon, grupul de comenzi Defined Names (vezi figura alăturată). Pentru crearea unui nou nume prin intermediul acestor comenzi, primul pas este selectarea celulei sau domeniului ce se dorește a fi denumit.

Comanda Define Name va deschide o fereastră, în care utilizatorul precizează numele referinței selectate (caseta Name), componenta la nivelul căreia poate fi apelată (în lista Scope, precizând domeniul de vizibilitate întregul registru de calcul sau una din foile acestuia de calcul), un eventual comentariu (opțional), iar în partea de


Pag. 10

jos, în caseta Refers to: este completată deja referința selectată, dar este posibilă modificarea acesteia oricând.

Dacă se dorește inserarea unui nume într-o formulă, se poate utiliza lista Use in formula din același grup de comenzi, care oferă posibilitatea alegerii din lista a unuia din numele create anterior.

Cel mai avansat instrument în gestionarea numelor pentru referințe este Name Manager, care permite adăugarea (New…), modificarea (Edit…), ștergerea (Delete), precum și vizualizarea tuturor informațiilor asociate cu numele de referințe în lista

din interiorul ferestrei de dialog.



TEST DE EVALUARE

1. Care este operatorul de domeniu? o Răspuns: Caracterul : (două puncte), folosit pentru a defini

un domeniu, mai poartă și numele de operator de domeniu (range operator).

2. Ce este o referință relativă? Răspuns:

Întrebări tip grilă Exemplu rezolvat: Celula H10 conține formula = B10+$C$10. Se copiază această formulă în

celula J10. Care va fi conținutul intern al celulei J10? a) = D10+$C$10; b) = C10+$C$10; c) = B10+$C$10; d) = C10+$D$10.

Răspuns: ●○○○ De rezolvat: Referința $A$1 este: a) relativă; b) parțial absolută; c) mixtă; d) absolută.

Răspuns: ○○○○


Pag. 12

3.2 ASPECTE GENERALE PRIVIND UTILIZAREA FUNCȚIILOR PREDEFINITE

Funcțiile sunt formule predefinite care efectuează operații matematice sau returnează informații specificate de formulă. O funcție are unul sau mai multe argumente și returnează (oferă) un rezultat.

Fig. 3.8. Selectarea categoriei de funcții

Procesorul de calcul tabelar Microsoft Excel, pentru efectuarea calculelor complexe, dispune de un număr apreciabil de funcții predefinite, cu o acoperire largă a domeniilor de utilizare, structurându-le astfel, pentru o mai ușoară regăsire a acestora, în următoarele categorii, selectabile la momentul inserării unei funcții (figura 3.8):

Tabel 3.1. Lista categoriilor de funcții implementate în Excel Categorie Descriere

Most Recently Used

Lista funcțiilor utilizate recent

All Lista tuturor funcțiilor implementate în Excel

Financial Funcții financiare

Date & Time Funcții care permit calculul valorilor de dată și oră

Math & Trig Funcții matematice și trigonometrice

Statistical Funcții statistice care permit calculul mediilor aritmetice, calculul derivației standard, etc.

Lookup & Ref-erence

Funcții de căutare a valorilor și stabilirea referințelor pentru aceste

Database Funcții de căutare și calcul aplicate listelor sau bazelor de date în Excel

Text Funcții ce prelucrează text sau etichete

Logical Funcții logice (IF, AND, OR etc.)



Information Funcții ce returnează informații despre valori și despre foaia de lucru

User Defined Funcții personalizate, create de utilizator

În Excel există o serie de funcții predefinite care au deci rolul de a simplifica scrierea formulelor. Funcțiile au nume specifice și acestea trebuie scrise corect pentru a fi recunoscute în Excel.

Câteva exemple de funcții în Excel: o SUM(domeniu) - calculează totalul pentru un domeniu precizat, o MIN(domeniu), returnează elementul minim din domeniul precizat, o MAX(domeniu) - returnează elementul maxim din domeniul precizat; o AVERAGE(domeniu) - calculează media valorilor numerice cuprinse în

domeniul ce se află pe poziția argumentului. În total, Excel conține peste 450 de funcții predefinite. În plus, se pot

construi funcții personalizate, specifice unei anumite activități. O funcție are un nume unic și o listă de argumente. Forma generală a unei

expresii de funcție este următoarea:

=nume_functie(argument1, argument2, ...)

Un argument este referința din cadrul funcției (elementul la care face

referire funcția): un număr, un șir de caractere, o valoare logică, o referință de celulă sau numele unor foi de lucru care fac referire la oricare dintre elementele precedente. Argumentele unei funcții, plasate între paranteze rotunde după numele funcției, sunt în număr fixat, utilizarea corectă a unei funcții fiind condiționată de acest număr.

Argumentele funcției trebuie separate prin virgulă sau punct și virgulă, în funcție de setările implicite (cel mai ușor vă puteți ghida după caseta de asistare numită tips, pe care Excel o afișează în timp ce scrieți funcția, așa cum se vede în figura de mai jos). Dacă într-o funcție un argument este omis intenționat, atunci în locul lui trebuie pusă o virgulă.

3.9. Asistarea scrierii unei funcții prin afișarea unei casete

Scrierea funcțiilor în Excel este foarte ușoară, ajutorul oferit de program în acest sens fiind remarcabil.

Cea mai rapidă metodă de scriere a unei funcții este utilizarea sistemului de asistare, când utilizatorul trebuie să precizeze doar elementele de bază ale funcției (argumentele sau parametrii acesteia), iar sistemul se ocupă de restul elementelor și de respectarea regulilor de sintaxă.

O a doua posibilitate este scrierea directă, în celula selectată, prin tastarea de către utilizator a numelui de funcție și a tuturor elementelor acesteia.

Caseta de asistare tips


Pag. 14

În această situație, pașii parcurși pentru scrierea unei funcții sunt cei descriși mai jos.

1. Se selectează celula în care urmează să apară rezultatul formulei.

2. Se apasă butonul (Insert Function) de pe bara de formule, ceea ce are drept efect apariția unei ferestre numite Insert Function (figura 3.10) sau Se tastează, în celula curentă, semnul =. Pe bara de formule, caseta Name Box (în care este precizată celula curentă sau numele domeniului selectat) își schimbă aspectul într-o listă ascunsă. Deschiderea acestei liste, afișează cele mai recente funcții utilizate, iar la sfârșitul listei apare opțiunea More Functions… Apelarea acestei opțiuni conduce la apariția ferestrei Insert Function.

Figura 3.10 – Fereastra Insert Function

3. În caseta Search for a function se poate introduce, pe scurt, ceea ce se dorește, și apoi se apasă butonul Go. În lista ascunsă Or select a category, apare o nouă categorie Recommended (recomandat) în care este o listă cu funcțiile oferite de sistem pentru cerința formulată de utilizator sau Din lista ascunsă Or select a category, se selectează o categorie de funcții (din: cele mai recent utilizate, toate, financiare, statistice etc.).

4. Din lista Select a function (selectează o funcție) se selectează funcția dorită și apoi se apasă butonul OK sau se dă un dublu click cu butonul stâng al mouse-ului pe funcția dorită. De exemplu funcția AVERAGE.



5. Apare fereastra Function Arguments care conține (vezi figura 3.11 ca exemplu pentru funcția AVERAGE):

a. Numele funcției b. Argumentele ce vor fi completate de utilizator

c. Butoane de restrângere a ferestrei de dialog d. Sintaxa funcției e. Rezultatele parțiale și finale f. Mesaje de eroare

Figura 3.11. – Fereastra Function Arguments pentru funcția SUM

6. Se scriu argumentele funcției și se selectează butonul OK. La acest pas, dacă în caseta argumentelor funcției se dorește să se precizeze de exemplu un domeniu sau o referință din foaia de calcul, se pot folosi butoanele de inserare a referințelor din foaia de calcul astfel:

- se apasă butonul restrângere a ferestrei de dialog (etichetat Collapse Dialog) ce reduce la minim fereastra de dialog în forma de mai jos:

- foaia de calcul devine vizibilă și cu mouse-ul se poate preciza prin click celula ce se dorește a fi referită sau prin selectarea mai multor celule ce formează domeniul dorit direct pe foaia de calcul, în cazul nostru domeniul A1:A3;

- se apasă din nou butonul (etichetat acum Expand Dialog) pentru a reveni la forma desfășurată a casetei de dialog sau se apasă tasta Enter. Scrierea directă, prin tastarea de către utilizator a tuturor elementelor

funcției, în bara de formule, este mai dificilă, dar și aici sistemul ajută utilizatorul. După scrierea semnului egal, a numelui funcției și a primei paranteze deschise‚ =AVERAGE( de exemplu, apar explicații (tips-uri) cu privire la sintaxa funcției.

Reguli sintactice de bază pentru scrierea funcțiilor:

Argumentele funcției

Descrierea funcției Descrierea argumentului

Butonul restrângere fereastra de dialog

Rezultatul formulei


Pag. 16

o Numele funcției trebuie să fie precedat de semnul = . o Indiferent de tipul literelor folosite la tastarea numelui funcției, mici sau

mari, sistemul le va afișa cu majuscule. o Nu se lasă spații între numele funcției și argumente și nici între argumente. o Argumentele funcțiilor sunt incluse între paranteze rotunde. o O funcție poate avea ca argument altă funcție. o Când o funcție devine argument al altei funcții, fiecare dintre ele trebuie

să aibă argumentele cuprinse între paranteze. o Dacă sunt mai multe argumente, acestea se separă prin virgulă sau punct

și virgulă, corespunzător delimitatorului stabilit. o Sistemul atribuie valoarea zero tuturor celulelor libere ale căror referințe

sunt folosite ca argumente ale funcțiilor. Dacă celulele referite în funcții sunt șterse la un moment dat, funcțiile nu

vor mai efectua calcule, ci vor afișa mesaje de eroare. Se pot însă converti rezultatele funcțiilor (formulelor) la valori, astfel încât celulele sursă să poată fi șterse dacă nu mai sunt necesare, fără a modifica rezultatele produse de formule. Conversia formulă - valoare se realizează astfel:

o se selectează formula; o se apelează comanda Copy (opțiune în meniul contextual sau în tab-ul

Home, grupul de comenzi Clipboard); o se apelează comanda Paste Special (opțiune în meniul contextual sau în

lista butonului Paste, din tab-ul Home, grupul de comenzi Clipboard), apare fereastra Paste Special, iar din secțiunea Paste se alege opțiunea Values.

o se apasă butonul OK. Imbricarea funcțiilor în cadrul formulelor Excel Imbricarea sau încapsularea funcțiilor este termenul folosit pentru

cazurile în care se dorește utilizarea uneia sau mai multor funcții ca argumente ale unei alte funcții. Pentru a înțelege mai bine acest lucru și cum funcționează imbricarea funcțiilor, vom exemplifica prin următoarea problemă: să se însumeze un set de numere aflate în domeniul A1:A10, numai dacă media intervalului de celule B1:B10 este mai mare decât 5, altfel se returnează valoarea 0.

Rezolvarea problemei este scrierea formulei: =IF(AVERAGE(B1:B10)>5;SUM(A1:A10);0)

și să explicăm această soluție: se folosește funcția IF, care testează valoarea de adevăr pentru o expresie logică plasată ca prim argument al funcției (în cazul nostru este vorba de condiția AVERAGE(B1:B10)>5) – dacă aceasta este adevărată, se returnează valoarea celui de-al doilea argument (în cazul nostru SUM(A1:A10), adică se returnează suma setului de numere din domeniul A1:A10), în caz contrar (adică dacă expresia logică evaluată ar fi falsă) se returnează cel de-al treilea argument, adică valoarea 0.

Pentru această rezolvare, vom spune că am folosit funcțiile AVERAGE și SUM imbricate în funcția IF.

Procedura de creare a unei astfel de formule este următoarea: 1. Se va efectua click pe celula unde se dorește introducerea formulei (să

zicem A12);



2. Pentru a insera formula în cadrul formulei, se va folosi butonul -

Insert Function de pe bara de formule 3. Se va selecta funcția dorită prin procedurile descrise anterior – din

cadrul ferestrei Insert Function, în cazul nostru este vorba de funcția IF;

4. În fereastra Function Arguments se vor completa argumentele funcției IF (vezi figura de mai jos)

Figura 3.12. Utilizarea funcțiilor imbricate

Observați cum prin completarea argumentelor funcției în caseta de dialog

Function Arguments, sistemul a completat automat întreaga formulă dorită în zona de editare a celulei (partea superioară a imaginii).

Dacă problema se dorea a fi rezolvată prin scriere directă a formulei (prin tastare în celula selectată sau în zona de editare), imaginea ar fi fost următoarea (a se observa cum Excel vă asistă la scrierea funcțiilor și argumentelor acesteia):

Protejarea și ascunderea formulelor scrise în celulele foii de calcul Implicit, formula se afișează în bara de formule (conținutul intern sau

invizibil) iar în celula care conține formula se afișează rezultatul calculat (conținutul extern sau vizibil).


Pag. 18

Sunt și cazuri când nu doriți ca alte persoane care lucrează cu foaia de calcul proiectată de dumneavoastră (introduc date sau folosesc rezultatele) să cunoască formulele pe care le-ați utilizat ci doar rezultatele acestor formule. Aceasta deoarece nu vreți ca formulele să fie modificate de alte persoane sau formulele conțin informații confidențiale pe care nu doriți să le faceți publice. Pentru a ascunde apariția formulelor în bara de formule, parcurgeți pașii următori:

1. Selectați celula care conține formula pe care doriți să o ascundeți (dacă doriți să ascundeți mai multe formule din foaia de calcul curentă, selectați toate celulele care conțin formulele respective).

2. Apelați opțiunea Format Cells… din meniul contextual obținut din click pe butonul drept al mouse-ului

3. Din fereastra Format Cells, alegeți rubrica Protection și bifați caseta de validare Hidden. Nu uitați să lăsați bifată și caseta de validare Locked (blocat). Dacă deselectați această casetă și bifați caseta Hidden formula se ascunde, dar oricine deschide foaia de calcul o poate înlocui cu o altă formulă sau cu o constantă.

4. Accesați de pe ribon, din tab-ul Home, butonul Format ce aparține grupului Cells, iar din lista apărută, varianta Protect Sheet… . Apare fereastra Protect Sheet (figura alăturată), unde trebuie să fie bifată caseta de validare Protect worksheet and contents of locked cells (protejează foaia de calcul și conținutul celulelor blocate).

5. În caseta de text Password to unprotect sheet (parolă pentru deprotejarea foii de calcul) se completează parola ce trebuie introdusă de utilizatorul care dorește deblocarea celulelor.

6. Se vor verifica opțiunile dorite în lista Allow all users of this worksheet to:, implicite sunt doar primele două, care permit doar selecția celulelor blocate sau nu

7. Se apasă butonul OK, va apărea fereastra Confirm password ce conține caseta de text Reenter password to proceed, în care reintroducem parola. Atenție: parola este de tipul case-sensitvie, adică face diferența între literele mari și literele mici.

8. Apelați butonul OK pentru a închide fereastra (caseta de dialog). Cei care vor utiliza foaia dumneavoastră de calcul nu vor mai vedea formulele și nici nu le vor putea edita.

Pentru a arăta din nou formulele, apelați din nou butonul Format, varianta Unprotect Sheet (și evident, dacă ați pus parolă, trebuie să dați corect parola).

Programul Excel recalculează automat toate formulele de fiecare dată când deschideți sau salvați registru de lucru (fișierul de tip .xlsx). Atunci când modificați valoarea dintr-o celulă, programul Excel recalculează toate formulele care se referă la celula respectivă și care se află în oricare dintre foile de calcul



ale registrului de lucru curent. Calculul are loc în fundal, procesul fiind instantaneu.

Identificarea erorilor în formulele foii de calcul Cu cât o formulă este mai complexă cu atât mai mult există posibilitatea

de a introduce erori în formulă. În principiu, programul Excel dispune de o multitudine de caracteristici pe care le puteți utiliza pentru a depana erorile din formule.

În majoritatea cazurilor, programul Excel nu vă lasă să introduceți o formulă folosind o sintaxă incorectă. Dacă faceți una sau mai multe erori uzuale în sintaxa sau punctuația unei formule, programul Excel se oferă să o corecteze în locul dumneavoastră, iar, de cele mai multe ori, chiar reușește acest lucru. Această caracteristică, numită Formula AutoCorrect, poate detecta și remedia oricare din următoarele erori:

o parantezele, acoladele, apostrofurile și ghilimelele fără corespondent; o referirile inverse la celule (de exemplu, 23D în loc de D25) sau operatorii

de comparare scriși invers (de exemplu, => în loc de >=); o operatori suplimentari (de exemplu, un semn egal sau un semn plus de la

începutul sau de la sfârșitul unei formule; o spațiile suplimentare din adresele celulelor (de exemplu, V 23 în loc de

V23), dintre operatori sau dintre numele unei funcții și argumentele sale; o punctele suplimentare sau operatorii suplimentari: în principiu,

programul Excel utilizează punctul zecimal sau operatorul aflat cel mai la stânga și îi elimină pe toți ceilalți (de exemplu: 123.45.67 devine 123.4567; 23/*5 devine 23/5).

o operatorii de domenii și operatorii de reuniune incorecți, cum ar fi semnele două puncte (:) sau punct și virgulă (;) suplimentari între identificatorii de rând și de coloană;

o înmulțirea implicită, dacă omiteți semnul de înmulțire sau îl introduceți greșit; de exemplu, dacă introduceți (C5+V23)27 sau introduceți semnul x, atunci programul Excel adaugă semnul corect *.

Toate mesajele de eroare din Excel încep cu semnul diez (#). În total sun opt mesaje de eroare, tabelul nr. 3.2.

Tabelul nr. 3.2 Mesajele de eroare în formulele din Excel

Codul de eroare afișat

Semnificația erorii Modul de depanare

#### Coloana nu este suficient de lată pentru a afișa valoarea introdusă sau calculată

Măriți lățimea coloanei.


Pag. 20

Codul de eroare afișat

Semnificația erorii Modul de depanare

#DIV/0! Formula încearcă să efectueze o împărțire la zero sau la o celulă goală

Verificați numitorul din formulă sau schimbați referința. Ați putea adăuga și o procedură de tratare a erorilor cu ajutorul funcției =IF() sau ați putea utiliza formatarea condiționată pentru a preveni apariția acestei erori.

#N/A

Formula nu conține o valoare valabilă sau au fost folosite argumente nepotrivite. Altfel spus, nu este disponibilă nici o valoare pentru argumentul transmis.

Verificați argumentele funcției și domeniile de unde se preiau date.

#NAME?

Formula conține un text care nu reprezintă nici o funcție validă, nici un nume definit în foaia de lucru activă.

Verificați cu atenție formula. Probabil, ați editat greșit numele unei funcții. Sau ați greșit numele atribuit unui domeniu sau o etichetă, pe care le-ați folosit într-o formulă scrisă în limbaj natural.

#NULL! Într-o formulă este referită intersecția a două zone care, în realitate, nu se intersectează.

Verificați modul în care ați scris referința sau modul în care ați conceput formula.

#NUM!

Un număr folosit într-o formulă sau rezultat în urma unui calcul dintr-o formulă este prea mare sau prea mic pentru a putea fi afișat. De exemplu, ați încercat calcularea rădăcinii pătrate a unui număr negativ

Se corectează argumentele; rezultatul unui calcul trebuie să se încadreze între -1 x 10307 și 1 x 10307.

#REF!

Formula conține referințe care nu sunt valabile. Sau nu poate localiza celulele referite (de exemplu, celulele au fost șterse).

Se refac celulele șterse, se transformă în valori, după care ștergerea se poate efectua.

#VALUE!

Formula conține un argument de un tip greșit. Probabil că ați combinat două tipuri de date incompatibile în cadrul unei formule. De exemplu ați încercat să adunați un număr cu un text

Verificați formula.



TEST DE EVALUARE

1. Ce semnifică, în Excel, mesajul #NUM!, apărut în urma editării unei formule?

Răspuns Într-o formulă este referită intersecția a două zone care, în realitate, nu se intersectează.

2. Cum se depanează eroarea de la punctul 1? Răspuns:

Întrebări tip grilă Exemplu rezolvat: Introduceți o formulă și apare următorul mesaj de eroare: #DIV/0!? Care

acțiune, din cele enumerate mai jos, corectează eroarea? a) se schimbă numitorul în formulă; b) se scrie formula cu litere mici; c) se refac celulele șterse sau se transformă în valori; d) se mărește lățimea coloanei.

Răspuns: ●○○○ De rezolvat: Care din afirmațiile de mai jos este adevărată? a) numele funcției trebuie să fie precedat de semnul *; b) indiferent de tipul literelor folosite la tastarea numelui funcției, mici

sau mari, sistemul le va afișa cu minuscule; c) între numele funcției și argumente sale și între argumente se lasă

spații; d) o funcție poate avea ca argument altă funcție.

Răspuns: ○○○○


Pag. 22

Rezumatul temei

Excel este un program de calcul tabelar, iar cu ajutorul formulelor se pot efectua operații aritmetice și logice asupra datelor. O formulă începe cu semnul =. Introducerea formulelor se poate face direct, folosind tastatura, sau cu mouse-ul. Varianta a doua este mai operativă și elimină erorile de tastare.

Într-o formulă, operanzii pot fi constante sau valoarea unor celule din foaia de calcul curentă, din alte foi de calcul ale registrului curent sau din alte registre. Când, într-o formulă, un operand se referă la mai multe celule se folosesc următorii operatori: operatorul de domeniu, operatorul de reuniune și operatorul de intersecție.

O celulă poate avea un conținut vizibil (extern) – reprezentat de valoarea datelor afișate în celulă și un conținut ascuns (intern, invizibil) – reprezentat de formulele afișate în bara de formule și care generează conținutul vizibil.

Celulele pot avea diferite tipuri de referințe, în funcție de modul în care vor fi utilizate în formule: relative, absolute și mixte.

Funcțiile sunt formule predefinite care efectuează operații matematice sau returnează informații specificate de formulă. O funcție are unul sau mai multe argumente și returnează (oferă) un rezultat. Programul Excel este puternic și datorită celor peste 450 de funcții pe care le conține. El oferă facilități deosebite utilizatorului, care poate să introducă funcții (până la șapte niveluri de imbricare) fără a fi necesară memorarea sintaxei fiecărei funcții.

Dar , atunci când utilizați funcțiile să respectați următoarele reguli: o Numele funcției trebuie să fie precedat de semnul = . o Indiferent de tipul literelor folosite la tastarea numelui funcției, mici sau

mari, sistemul le va afișa cu majuscule. o Nu se lasă spații între numele funcției și argumente și nici între

argumente. o Argumentele funcțiilor sunt incluse între paranteze rotunde. o O funcție poate avea ca argument altă funcție. o Când o funcție devine argument al altei funcții, fiecare dintre ele trebuie

să aibă argumentele cuprinse între paranteze. o Dacă sunt mai multe argumente, acestea se separă prin virgulă sau punct

și virgulă, corespunzător delimitatorului stabilit. o Sistemul atribuie valoarea zero tuturor celulelor libere ale căror referințe

sunt folosite ca argumente ale funcțiilor. De asemenea, trebuie să țineți seama, atunci când operați cu funcții, de

modul de referire a unei celule sau a domeniilor de celule într-o funcție: relativ, absolut și mixt.

Formule și funcții în Microsoft Excel 2010Formule și funcții în Microsoft Excel 2010 Tema 3...

Documents

Transcript of Formule și funcții în Microsoft Excel 2010Formule și funcții în Microsoft Excel 2010 Tema 3...