2013-2014 INFOAPL - Curs 4

1

CURS 4: Aplicatii ale functiei IF

CURS 4Program de calcul tabelar

EXCELFunctii IF si aplicatii

2


=IF(conditie, expresie1, expresie2)

Functia de decizie

Se evaluează valoarea expresiei logice conditie. Dacă conditia este adevarata (TRUE), atunci funcţia IF returnează valoarea specificată prin expresie1, altfel returnează valoarea specificată prin expresie2

Expresie2

Expresie1 TRUE

FALSE

conditie

3


Exemple de probleme rezolvate cu ajutorul functiei IF

1.Sa se calculeze valorile functiilor f(x) si g(x) pe domeniul [-10, 10]. (100 valori pe fiecare domeniu).

0 xdaca 250 xdaca 25

)(2

2

xxxx

xf

=IF(A2<=0,5*A2^2+2*A2, -5*A2^2-2*A2)

TRUE

FALSE

X<=0

xx 25 2

25 2 xx

SCHEMA LOGICA

Functie in EXCEL

4



2.Sa se calculeze valorile functiei pe domeniul [-5, 20]. (26 valori)

=IF(A2<=0,5*A2^2+2*A2, IF(A2<=15,5*sin(A2),exp(A2)))

TRUE

FALSE

X<=0

xx 25 2

SCHEMA LOGICA

Functie in EXCEL

15 xdaca 1015x0 daca 5sin(x)

0 xdaca2x x5)(

7-x

2

exf

X<=15

)sin(5 x

7-x 10e

TRUE

FALSE

5


3. Daca scorul este :mai mare ca 89 afiseaza Aintre 80 si 89 afiseaza Bintre 70 si 79 afiseaza Cintre 60 to 69 afiseaza Dmai mic ca 60 afiseaza E

Afiseaza E

=IF(A2>89,”A”,IF(A2>=80,”B”,IF(A2>=70,”C”,IF(A2>=60,”D”,”E”) ) ) )

Afiseaza ATRUE

FALSE

scor>89

scor>=80

TRUE

FALSE

Afiseaza B

scor>=70

TRUE

FALSE

Afiseaza C

scor>=60

TRUE

FALSE

Afiseaza DSCHEMA LOGICA

Functie in EXCEL

6


4. Sa se rezolve ecuatia de gradul 2 pentru diverse valori ale coeficientilor

TRUE

FALSE

a<>0

TRUE

FALSE

delta>0

a2deltabx1

a2deltabx2

a2bre

adeltaim

2

bcx

02 cbxax

7


a b c delta Tip solutii x1 x2 re im

Formula de calcul a solutiilor

1 4 3 4 Reale -1 -3

DELTA=C10*C10-4*B10*D10

5 88-

99 9724 Reale 1,06 -18,66

X1=(-C10+SQRT(E10))/(2*B10)

3 6 9 -72 Complexe -1+3,46i -1-3,46i -1 3,46

X2=(-C10-SQRT(E10))/(2*B10)

1 1 1 -3 Complexe -0,5+1,22i -0,5-1,22i -0,5 1,22

3 5 8 -71 Complexe -0,83+3,44i-0,83-3,44i -0,83 3,44

5 -8 -6 184 Reale 2,16 -0,56

8


=AND(conditie1, conditie2)

Conditie multipla

Se evaluează valoarea expresiei logice conditie si se genereaza valoarea TRUE daca ambele conditii sunt adevarate

=OR(conditie1, conditie2)Se evaluează valoarea expresiei logice conditie si se genereaza valoarea TRUE daca cel putin una din conditii este adevarate

9


1 2 3

1 1 2 -2

2 5 -3 0

3 -8 5 0

4 9 6 -7

5 7 -9 4

6 -2 4 3

7 0 -2 4

8 1 5 8

1 2 3

1 1 2 -2

2 5 -3 0

3 -800 5 0

4 9 6 -7

5 7 -900 4

6 -2 4 3

7 0 -2 4

8 1 5 8

1 2 3

1 1 2 -200

2 5 -300 0

3 -800 5 0

4 9 6 -700

5 7 -900 4

6 -200 4 3

7 0 -200 4

8 1 5 8

=IF(AND(B2<0,B2<=-8),B2*100,B2) =IF(OR(B2<0,B2<=-8),B2*100,B2)

Exemplu 1 : Daca elementul este mai mic decat 0 SI mai mic decat -8, se inmulteste cu 100

Exemplu 2 : Daca elementul este mai mic decat 0 SAU mai mic decat -8, se inmulteste cu 100

10


=COUNTIF(domeniu, conditie)

Functia statistica cu decizie

Se evaluează valoarea expresiei logice conditie pe domeniul selectat si se numara cate valori indeplinesc conditia

=COUNTIF(domeniu, conditie1)+ COUNTIF(domeniu, conditie2)

Functie statistica cu conditii multiple

11



5. Calculati cati studenti au luat nota 10 la proba 3Calculati cati studenti au bursa integrala

=COUNTIF (domeniu, conditie)

Functie de numarare dupa o conditie

3 3 3 3 3 3

3 3 3 3 3 3

3 3 3 3 3 3

3 3 3 3 3 3

3 3 3 3 3 3

3 3 3 3 3 3

3 10 10 3 3 3

3 3 3 3 3 3

3 3 3 3 3 3

3 3 10 3 3 3

3 3 2 3 3 3

3 3 3 3 3 3

3 3 3 3 10 3

3 3 3 3 3 3

3 3 3 3 3 3

3 3 3 3 3 3

3 3 3 3 10 3

3 3 3 3 3 3

3 3 3 3 3 3

=COUNTIF(F15:K33;10)

Rezultat 5 valori

12


=COUNTIF(G12:I19,"<0")+COUNTIF(G12:I19,">0")

1 2 3

1 1 2 -200

2 5 -300 0

3 -800 5 0

4 9 6 -700

5 7 -900 4

6 -200 4 3

7 0 -200 4

8 1 5 8

Cate valori sunt mai mici si mai mari decat 0

Rezultatul este 3

13


Numarare dupa conditii multiple

=SUMPRODUCT((G12:I19<0)*(G12:I19>-500))

Atentie! Se apasa CTRL+SHIFT+ENTER

1 2 3

1 1 2-

200

2 5-

300 100

3 -800 5 0

4 9 6-

700

5 7-

900 4

6 -200 4 3

7 0-

200 4

8 1 5 8

Cate numere sunt mai mici ca zero si mai mari ca -500

=SUMPRODUCT((G12:I19<0)+(G12:I19>-500)) Efect OR

Efect AND

14


D E F

14 a 2 1 2 0 0 0

15 a 1 1 1 1 1 1

16 a 1 1 1 1 1 1

17 a 1 1 1 1 1 1

18 a 1 1 1 1 1 1

19 a 1 1 1 1 1 1

20 a 2 1 1 1 1 1

21 a 2 1 1 1 1 1

22 a 2 1 1 1 1 1

23 a 1 1 1 1 1 1

24 a 1 1 1 1 1 1

25 a 1 1 1 1 1 1

26 f 1 1 1 1 1 5

27 g 1 1 1 1 1 1

28 a 1 1 1 1 1 1

Numarare dupa conditii multiple

=SUMPRODUCT((D14:D28="a")*(E14:E28<>"2")*F14:F28)

Atentie! Se apasa CTRL+SHIFT+ENTER

15


=SUMIF(domeniu, conditie)

Functia statistica cu decizie

Se evaluează valoarea expresiei logice conditie pe domeniul selectat si se insumeaza valorile care indeplinesc conditia

=SUMIF(G12:I19,"<0")

1 2 3

1 1 2 -200

2 5 -300 0

3 -800 5 0

4 9 6 -700

5 7 -900 4

6 -200 4 3

7 0 -200 4

8 1 5 8

Sa se insumeze valorile mai mici ca 0

Rezultatul este -3300

16


Start End 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 361 2 x x2 3 x x4 4 x5 6 x x7 8 x x8 8 x9 11 x x x9 10 x x

10 11 x x12 26 x x x x x x x x x x x x x x x20 26 x x x x x x x21 32 x x x x x x x x x x x x26 32 x x x x x x x33 35 x x x36 36 x

Sa se realizeze urmatorul grafic Gant in EXCEL conform urmatoarelor cerinte:

-daca numarul coloanei este in intervalul [Start, End] casutele vor lua valoarea x- pentru conditie dubla se utilizeaza functia AND-se aplica conditional formating pentru celulele care au valoarea x-orice modificare a intevalului se va reflecta automat in grafic

1) =IF(AND(C$1>=$A2;C$1<=$B2);"X";" ")2) Formatare pentru celula sa aiba fundal rosu daca are valoarea X

17


Reprezentari grafice: sa se realizeze un grafic pe care sa se evidentieze punctele de minim si de maxim

Max Max

0

2

4

6

8

10

12

14

0 2 4 6 8 10

yMax max

min

Modificarea marcarelor de pe grafic-Se deseneaza-Se copiaza in memorie CTRL+C-Se selecteaza marcarul si se apasa CTRL+V

18


Calcul matriceal in EXCEL1. Adunarea a 2 matrici

Pentru a aduna doua matrici conditia care trebuie respectata este ca dimensiunea (numarul de linii si de coloane) celor doua matrici sa fie aceeasi.

Daca A este o matrice de dimensiune mxn si B este o matrice cu aceeasi dimensiune mxn atunci termenii matricii rezultat C = A + B se vor exprima prin:

În Excel, adunarea matricilor se realizeaza prin introducerea unei formule de calcul în care se folosesc adresele relative ale celulelor care contin valorile matricilor. =Adresa elementului a11 + adresa elementului b11

2. Inmultirea cu un scalar Se inmulteste primul element al matricii cu scalarul real dorit întroducând formula de calcul într-o celula alaturata matricii introduse în foaia de calcul Excel:

= scalarul * adresa primului element din matrice

19


Calcul matriceal in EXCEL

1. Sa se calculeze într-o foaie de calcul Excel suma matricilor de dimensiune 5x7

2. Înmultiti matricea A cu valoarea 3/5

20



3. Inmultirea a 2 matrici

Pentru inmultirea a doua matrica exista restrictia numarul de coloane a primeimatrici sa fie egal cu numarul de linii a celei de a doua matrici.

In EXCEL exista functia MMULT cu sintaxa =MMULT(array1,array2)

Array1 reprezinta domeniul de celule in care s-a introdus prima matriceArray2 reprezinta domeniul de celule in care s-a introdus cea de a doua matrice.

Pentru afisarea rezultatului functiei selectati mai intai o zona de celule dedimensiune m xn. Incheiati dialogul prin tastarea simultana a tastelor Ctrl+Shift+Enter.

21


Calcul matriceal in EXCEL4. Determinantul unei matrici

Acest calcul asociat unei matrici patratice este realizat in Excel de functiaMDETERM.

Sintaxa functiei MDETERM este:=MDETERM(array)

Unde array reprezinta adresa domeniului de celule din foaia de calcul unde a fostintrodusa matricea.

22



5. Inversarea matricilor

Formula matematica de calcul a matricii inverse a unei matrici patratice A de dimensiune n este:

A* reprezinta matricea adjuncta. Aceasta matrice se obtine prin înlocuirea fiecaruielement al matricii A cu determinantul obtinut din matricea patratica A din cares-a eliminat linia si coloana corespunzatoare elementului ai j.

In Excel se obtine cu ajutorul functiei MINVERSE care are sintaxa:

=MINVERSE(array)Unde Array reprezinta adresa domeniului de celule unde a fost introdusa matricea

23



6. Aplicatie: rezolvarea sistemelor de ecuatii liniare de tip Cramer

Prin sistem Cramer intelegem un sistem de ecuatii polinomiale liniare cu numaregal denecunoscute si ecuatii in care determinantul asociat sistemului este diferit de zero.Un astfel de sistem de ecuatii poate privit si sub forma lui matriceala:A X = Bunde

§ A este matricea sistemului formata din coeficientii necunoscutelor§ X reprezinta vectorul necunoscutelor§ B este coloana termenilor liberi

24


Calcul matriceal in EXCEL6. Se da sistemul de ecuatii.

Rezolvati acest sistem matriceal.

1. Inversati matricea sistemului folosind functia matematica Excel MINVERSE.2. Înmultiti matricea inversa cu matricea coloana a termenilor liberi B folosind functia matematica Excel MMULT.3. Fiecare valoare a matricii coloana obtinute corespunde câte unei variabile, înordinea aparitiei lor în sistemul de ecuatii. Rezultatul se va afisa sub forma:

25


2 -1 1 -1 1

A= 2 -1 0 -3 B= 2

3 0 -1 1 -3

2 2 -2 5 -6

det A= -9transpusa lui A 0,333 0,3 -0,3 0,3

0,667 1,7 -2,7 1,7

1 0,3 -1,3 0,7

-0 -0,7 0,7 -0,3

x 0

2

1,667

-1,33

26


Solutia unei ecuatii: GOAL SEEK

Pas 1: se da valoare lui xPas 2: se calculeaza f(x)Pas 3: se deschide GOAL SEEKPas 4: SET cell – se alege celula cu formulaPas 5: TO Value: termenul liberPas 6: Changing cell – celula cu valoarea lui xPas 7: OK

1 0

27


Solver: determina valoarea minimaPas 1: se introduc tempPas 2: se introduc valori exp ale lui PPas 3: se introduc valori pt A,B,C fictivePas 4: se calc log(p)Pas 5: se calc log(p) cu valorile a,b,cPas 6: Se calc suma patratelorPas 7: se minimizeaza suma patratelor cu Solver

Suma patratelor are valoarea minima

Determinarea parametrilor a,b,c

28


Solver: determina valoarea minima

2013-2014 INFOAPL - Curs 4

Documents

Transcript of 2013-2014 INFOAPL - Curs 4