Download - II Formule Excel, Functii Excel
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