II. Formule şi funcţii Excel

Editarea formulelorUtilizarea funcţiilor

Funcţii cu caracter generalFuncţii matematice

1. Editarea formulelor în Excel

orice fomulă începe cu semnul “=“

orice modificare a valorilor numerice implicate în fomulă duce la recalcularea automată a rezultatului fomulei

operaţiile sunt executate de la stânga la dreapta, în funcţie de prioritatea operatorilor

formulele pot fi introduse direct de la tastatură sau prin selectarea referinţelor la celulele care conţin date

Operatori matematiciOperator Semnificaţie Prioritate+ adunare 1 (mică)- scădere 1* înmulţire 2/ împărţire 2% calcul

procentual 3

^ ridicare la putere 3

Operatori logici sunt utilizaţi

pentru a compara valorile din două celule

rezultatul unei expresii logice poate fi TRUE sau FALSE

Operator Semnificaţie<> diferit= egal< mai mic> mai mare<= mai mic sau egal>= mai mare sau

egal

Operator de tip text - exemplu

Permite concatenarea textelor aflate în două celule

Operatorul este &

Etapele introducerii unei fomule 1. Se selectează celula în care dorim să

apară rezultatul 2. Se tastează semnul “=“ în bara de

editare a formulelor 3. Se introduc componentele formulei (fie

prin editarea coordonatelor celulelor implicate în calcul (fie prin clic pe celulă, afisând astfel referinţa la celulă în bara de editare formule) şi a operatorilor necesari

4. Se tastează ENTER

Exemple privind calcul de procente

generarea unui şir simplu de valori: se introduc primele două valori, se selectează cele două celule, apoi se selectează prin “tragerea” mouse-ului din colţul dreapta jos, zona de celule pentru care dorim completarea (AutoFill)

calcularea procentelor Aplicaţie laborator:

generaţi primele 10 valori din şirul numerelor impare

Exemple privind introducerea formulelor cu operatori matematici

Editarea formulei

Aplicaţie laborator:-Editaţi în word expresia

-Calculaţi primele patru valori aleexpresiei de mai sus pentru cazul

când x este şirul numerelor naturale iar y

este şirul numerelor naturale impare

211

21

1

131)( BBA

BA

A

2

32

2

5

y

xyxyx

Exemple privind introducerea formulelor cu operatori logici

Aplicaţie laborator:

Introduceţi două valori

în foaia de calcul şi comparaţi-le utilizând operatorii logici prezentaţi.

2. Utilizarea funcţiilor Excel

Funcţii predefinite sau funcţii definite de utilizator (există şi posibilitatea instalării de pachete suplimentare de funcţii –din tools)

Sintaxa funcţiilornumefunctie (lista parametri de intrare)=parametri de ieşire

Categorii de funcţii (Excel 2003): financiare, de timp, matematice şi trigonometrice, statistice, de tip referinţă, de tip bază de date, de tip text, logice, informative

Introducerea unei funcţii într-o celulă

Se selectează celula în care dorim să fie returnat rezultatul funcţiei

Se tastează semnul “=“ în bara de editare formule şi se scrie numele funcţiei urmat de o paranteză deschisă (sau Insert>Function>Function category>Function name pentru deschiderea unei ferestre de dialog pentru funcţie)

Se selectează zonele de celule care conţin parametrii de intrare

Se tastează ENTER (pentru fereastra de dialog, clic pe O.K.)

Introducerea unei formule matrice

Dacă funcţia returnează mai mult de o valoare, rezultatul trebuie introdus ca o formulă matrice

Nu se poate modifica doar un element al unei formule matrice!Etape:-se selectează o celulă, se aplică funcţia, afişând în celulă doar primul parametru de ieşire-se selectează, începând cu celula iniţială, întreaga zonă

de celule unde dorim să apară rezultatul (este necesar să cunoaştem dimensiunea parametrului de ieşire de tip matrice)-se tastează F2, apoi CTRL+SHIFT+ENTER, pentru afişarea tuturor parametrilor de ieşire (pentru Open Office, se bifează opţiunea Array în caseta de dialog pentru funcţie)

Erori întâlnite la utilizarea formulelor şi funcţiilor

#DIV/0 - împărţire cu 0 #REF! – referinţă invalidă la o celulă #VALUE – tipul valorii este neacceptat #NUM – număr neacceptat de funcţie #NAME? – utilizarea unui nume nerecunoscut

de Excel #N/A – alte erori ##### - rezultatul are o lungime prea mare

pt. celulă, prin urmare lăţimea celulei trebuie majorată sau celula nu este formatată corespunzător datelor

Exemplu de aplicare a unei funcţii Sintaxa funcţiei AVERAGE

AVERAGE(number1,number2,…)=media aritmetică

-parametrii de intrare: number 1, number2, …, reprezintă valorile variabilei pentru care dorim sa calculăm media

-parametrul de ieşire: media aritmetică a parametrilor de intrare

Calculul notei mediiSelectarea funcţiei AVERAGE

Calculul notei mediiSelectarea parametrilor de intrare pentru funcţia AVERAGE

Calculul notei mediiAfişarea rezultatului funcţiei AVERAGE

Exemplu de aplicare a unei funcţii de tip matrice Sintaxa funcţiei COUNTIF

COUNTIF (range, criteria)=(f1, f2, …, fR) Parametrii de intrare:

- range-un vector care cuprinde valorile pe care dorim sa le grupăm ;- criteria-un vector care cuprinde valorile unice dupa care dorim să facem gruparea ; la modul general cuprinde criteriile de numărare care pot fi numere, texte, etc (de exemplu, “>30”.

Parametrii de iesire :- fi-vector de aceeaşi dimensiune cu criteria conţinând frecvenţa de apariţie a valorii xi din criteria, în şirul range.

ExempluSelectarea parametrilor de intrare pentru funcţia COUNTIF

Exemplu COUNTIF -Selectarea zonei pentru răspunsuri multiple-Tasta F2

Exemplu COUNTIFAfişare rezultat multiplu: CTRL+SHIFT+ENTER

Filtrarea datelor iniţiale pentru COUNTIFPasul 1-filtrare avansată

Filtrarea datelor iniţiale pentru COUNTIFPasul 2-criteriul înregistrărilor unice (în Open Office, la filtrare standard, se bifează, no duplication and not empty)

Sistematizarea şi reprezentarea grafică a notelor a 30 de studenţi, pe grupe de note

Date calitative: Repartiţia a 30 de absolvenţi după încadrarea pe piaţa muncii (S=şomeri, CP=încadrat conform pregătirii, NCP= încadraţi conform altor pregătiri, A=alte situaţii)

Crearea unei funcţii noi Se utilizează limbajul de programare Microsoft

Visual Basic for Applications (VBA), integrat în Excel

În documentul Excel >Tools>Macro> Visual Basic Editor

În fereastra Microsoft Visual Basic >Insert>Module

În fereastra Modulen se editează codul pentru funcţia dorită (macrofuncţie sau macrou)

Procedura Function din VBA Pentru definirea macro-funcţiei utilizăm procedura

Function din Visual Basic (Function- End Function)

Macro-funcţia este compusă din -linia de identificare (numele funcţiei şi parametrii de intrare)-corpul funcţiei (enunţuri în Visual Basic care indică operaţiile care trebuie efectuate prin macrou)-instrucţiunea pentru returnarea valorii calculate (rezultatele calculelor se returnează prin atribuire la numele funcţiei)-linia de terminare (închide macrofuncţia)

Utilizarea funcţiei nou create După editarea codului, revenim la foaia de lucru

Excel, din File >Close and Return to Microsoft Excel.

Se utilizează la fel ca şi orice altă funcţie prin apelarea numelui şi selectarea parametrilor de intrare, din bara de editare formule sau din caseta pentru dialog care se asociază automat funcţiei şi care se apelează prin numele funcţiei care a fost adăugat automat în lista de funcţii Excel

Pentru validarea macro-ului într-o altă sesiune de lucru (unde poate fi invalidat datorită nivelului de protecţie selectat), se setează nivelul de securitate din meniul pentru Macro-uri la Medium, se inchide aplicaţia şi la redeschidere se optează pentru Enable Macro

Exemplu: Codul pentru funcţia Notafinală

Rezultatele funcţiei Notafinala Rezultatul funcţiei aplicată

pentru nota medie de laborator 5 şi nota la examen 7 este 6; pentru obţinerea notei în celelalte cazuri se optează pentru AutoFill

Aplicaţie laborator: Creaţi o funcţie care să aibă în

plus opţiunea afişării “nu este admis in examen” pentru cazul când nota la laborator este 0 şi de asemenea o altă funcţie cu utilizare similară dar care să permită în prealabil calcularea notei medii de laborator din 14 note şi afişarea notei 0 pentru nota medie de laborator, în cazul când cel puţin o notă din cele 14 este 0 (studentul lipseşte de la cel puţin un laborator).

3. Funcţii cu caracter general -Funcţii mai des utilizate, cu argumentele de forma x1,x2,...,xn

Funcţie RezultatAVERAGE (x1,x2,...,xn) Media aritmetică a numerelor x1,x2,...,xn

SUM (x1,x2,...,xn) Suma numerelor x1,x2,...,xnCOUNTA (x1,x2,...,xn) Numărul elementelor nevide din

mulţimea x1,x2,...,xn

COUNT (x1,x2,...,xn) Numărul elementelor care conţin valori numerice din mulţimea x1,x2,...,xn

MAX (x1,x2,...,xn) Valoarea maximă din şirul de numere x1,x2,...,xn

MIN (x1,x2,...,xn) Valoarea minimă din şirul de numere x1,x2,...,xn

PRODUCT (x1,x2,...,xn) Produsul numerelor x1,x2,...,xn

Funcţii des utilizate, cu alte tipuri de argumente

Funcţie RezultatPOWER (a,b) Calcularea puterii

MOD (a,b) Calcularea restului împărţirii numărului întreg a la numărul întreg b

INT (x) Partea întreagă a lui x

PI () Valoarea numărului

AND (e1,e2,...,en) Conjuncţia expresiilor logice e1,e2,...,en TODAY () Data calendaristică în formatul lună/zi/anNOW () Data şi ora curentă (lună/zi/an ora:minute)

ba

Alte funcţii generale TRANSPOSE (array) - transformă o zonă verticală de celule

într-o zonă orizontală şi invers (tip referinţă) ISEVEN (number) - returnează valoarea TRUE dacă numărul

este par (tip informaţie) ISODD (number) - returnează valoarea TRUE dacă numărul

este impar (tip informaţie) AND(logical1;logical2; ...) - întoarce TRUE dacă toate

argumentele sale sunt TRUE; întoarce FALSE dacă unul sau mai multe argumente sunt FALSE (logice)-logical1, logical2, ... conţin de la 1 până la 30 de condiţii care se evaluează ca adevărate sau false OR (logical1;logical2; ...) -întoarce TRUE dacă cel puţin un argument este TRUE; întoarce FALSE dacă toate argumentele sunt FALSE (logice)

Funcţia logică IF IF (logical_test;value_if_true;value_if_false) - întoarce o valoare

dacă condiţia specificată este TRUE şi o altă valoare dacă este FALSE.-logical test este orice valoare sau expresie care se poate evalua cu TRUE sau FALSE (A10=100 este o expresie logică evaluată la TRUE dacă valoarea din celula A10 este egală cu 100) - value_if_true este valoarea întoarsă dacă test_logic este TRUE (dacă nu

e specificată şi testul logic e TRUE, acest argument întoarce 0)-value_if_false  este valoarea întoarsă dacă test_logic este FALSE (dacă test_logic este FALSE şi valoare_pentru_fals este omisă, adică după valoare_pentru_adevărat nu există virgulă, atunci este întoarsă valoarea logică FALSE; dacă test_logic este FALSE şivaloare_pentru_fals este necompletată, adică după valoare_pentru_adevărat există o virgulă

urmatăde paranteza de închidere, atunci este întoarsă valoarea 0Exemplu: IF(A3>=5;”admis”;”respins”)=admis dacă valoarea din A3 estemai mare sau egală cu 5

Funcţii de tip text CHAR(number) -întoarce caracterul corespunzător poziţiei date de number (între 1 şi 255) în setul de caractere utilizat de caculator (decodifică un număr) CODE(text) -întoarce un cod numeric pentru primul caracter dintr-un şir de text. Codul returnat corespunde setului de caractere utilizat de computer (codifică un text) LEN(text) - întoarce numărul de caractere din text, spaţiile se numără ca şi caracterele

Funcţia de tip text FIXED FIXED(number;decimals;no_commas) - rotunjeşte un

număr la un număr precizat de zecimale, aplică numărului un format zecimal

utilizând un punct şi virgule şi întoarce rezultatul ca text.-number  - numărul care se rotunjeşte şi se transfomră în text.-decimals - numărul de cifre de la dreapta separatorului zecimal.-no commas - valoare logică, dacă este TRUE, împiedică FIXED să

includă puncte (separator) în textul returnat.

Dacă argumentul zecimale este negativ, argumentul număr este rotunjit spre stânga separatorului zecimal.

Dacă se omite argumentul zecimale, se consideră valoarea implicită, 2 zecimale.

Exemple:FIXED(145,346;2)=145,35 FIXED (145,346;-1)=150 FIXED(1255,76;1)=1.255,8

4. Funcţii matematice Matematice Trigonometrice Statistice Inginereşti

Funcţii trigonometrice directe

COS(number) - întoarce cosinusul unui unghi dat.-number - unghiul în radiani pentru care se calculează cosinusul; dacă unghiul este în grade, se înmulţeşte cu PI/180 pentru a face conversia

în radiani.-Analog funcţiile SIN şi TAN.

COSH(number)- întoarce cosinusul hiperbolic al unui număr. -number - număr real -Formula pentru cosinusul hiperbolic este:

-Analog SINH, cu formula şi TANH.

2cosh(z)

zz ee

2sinh(z)

zz ee

Funcţii trigonometrice inverse

ACOS(number)- întoarce arccos sau inversul cosinusului unuinumăr, unghiul al cărui cosinus este number; unghiul returnat este dat în radiani, în intervalul (0, ); pentru a face conversia rezultatului din radiani în grade, se înmulţeşte cu 180/PI.- number- număr între -1 şi 1. Analog ASIN, ATAN (ambele cu rezultatul în ), ACOSH (numărul trebuie să fie real, mai mare sau egal cu 1), ASINH şi ATANH (pentru orice nr. real in intervalul (-1,1))

2,2

Alte funcţii trigonometrice DEGREES(angle) - transformă radianii în

grade (relaţia de legătură este G=R*180/PI)-angle - unghiul în radiani

RADIANS(angle)- transformă gradele în radiani

-angle – unghiul în grade

PI( ) - întoarce numărul 3,14159265358979, constanta matematică PI, aproximată la 15

cifre.

Funcţii matematice ABS (number)-valoarea absolută a unui număr real

CEILING(number;significance)-întoarce un număr rotunjit, în sensul dinspre zero (prin adaos), la cel mai apropiat multiplu semnificativ

-number - valoarea de rotunjit. -significance - multiplul la care se face rotunjirea.Observaţii Dacă number este un multiplu exact al argumentului

significance, nu se face nici o rotunjire. Dacă argumentele number şi significance au semne diferite,

CEILING întoarce valoarea de eroare #NUM!. ExempleCEILING (4,5;1)=5, CEILING (-2,5;-2)=-4 CEILING (1,5;0,1)=1,5

CEILING (0,146;0,01)=0,15