elemente avansate de excel

Calcul tabelar avansat în MS Excel.

Formule, funcţii şi baze de date.

Reprezentări grafice.

Instrumente de analiză statistică - Analysis ToolPak.

Rezolvarea problemelor de optimizare - Goal Seek, Solver

0. Noţiuni introductive MS Excel

Un fişier din Excel este format din foi de calcul numite Sheet-uri; fiecare fişier are

iniţial 3 foi de calcul.

Fig 1. Foi de calcul în Excel

Număr foilor de calcul poate fi modificat prin ştergere (Delete) sau adăugare de foi

noi(Insert) în funcţie de necesităţi.

Sheet1 Sheet2

Fig. 2 Operaţii cu foi de calcul

Fiecare foaie de calcul este formată din celule ce pot fi referite prin adrese. O

adresă de celula este compusă din numărul coloanei+numărul liniei. Ex: A1, A2, ... E24

Adresele de celule pot fi rândul lor de două tipuri:

- adrese relative : A1, C2, D24

- adrese absolute : $A1 (coloana A este absolută, linia 1 se poate modifica), A$2

(coloana A este relativă, linia 2 este absolută), $A$2 (atât coloana cât şi linia sunt absolute)

În Excel funcţiile pot accepta ca şi argument domeniu sau înşiruire de celule. Un

domeniu este exprimat prin semnul : (două puncte). (Ex. A1: D3 – celulele

A1,A2,A3,B1,B2,B2,D1, D2, D3); Înşiruirea de celule prin , (virgulă) sau ;(punct

virgulă).

Exemplu: Să se calculeze media valorilor din celulele C2:F2, C3:F3,... C7:F7,

precum şi media C2:F7

A B C D E F

1 Nr.crt. Nume Nota1 Nota2 Nota3 Nota4 Media

2 1 Popescu 10 6 3 8 =(C2+D2+E2+F2)/4

3 2 Ionescu 8 7 4 6 =SUM(C3:F3)/4

4 3 Vasilescu 9 4 5 4 =SUM(C4:F4)/4

5 4 Andrei 5 3 6 8 =SUM(C5:F5)/4

6 5 Sasu 3 6 9 9 =SUM(C6:F6)/4

7 6 Pop 4 10 5 10 =SUM(C7:F7)/4

Media generală =SUM(C2:F7)/COUNT(C2:F7)

Media generală =SUM(G2:G7)/6

1. Calcul tabelar avansat. Formule, funcţii şi baze de date

În Excel funcţiile sunt împărţite pe categorii:

- funcţii financiare – Financial - permit realizarea calculelor economico-

financiare predefinite

- funcţii cu date calendaristice – Date &Time – manipulează numere care

reprezintă date calendaristice sau timp;

- funcţii matematice şi trigonometrice – Math& Trig- permit efectuarea de

calcule simple sau complexe;

- funcţii statistice – Statistical – permit calcule statistice utilizând serii de

valori;

- funcţii de informare - Information – afişează informaţii despre celule şi

câmpuri;

- funcţii logice – Logical - determină valoarea de adevărat sau fals

corespunzătoare unei condiţii;

- funcţii cu baze de date - Database – efectuează diferite calcule asupra

câmpurilor din baza de date, corespunzătoare unor criterii predefinite

- funcţii de căutare şi consultare – Lookup& Reference – permit

localizarea conţinutului unei celule;

- funcţii pentru text sau şiruri de caractere – Text – oferă informaţii legate

de textul existent în celulă şi permit operaţii cu etichete

Funcţiile reprezentative (sau cele mai des utilizate) în funcţie de categorie:

1.1 Funcţii Financiare

Funcţiile financiare efectuează o serie de calcule economico-financiare furnizând

prin valorile returnate informaţii utile referitoare la amortizare, la rentabilitatea

investiţiilor, plasamentelor, împrumuturilor etc.:

a) PV(rata dobânzii; număr de perioade; mărimea plăţii;[valoare

viitoare;tipul])

- Descriere:

Returnează valoarea actuală (present value) aferentă unei sume investite sau

depozitate la bancă, prin plăţi periodice, în condiţiile unei rate constante a dobânzii.

Funcţia financiara PV calculează deci valoarea prezentă a unei sume investite,

adică valoarea curenta a unei serii de plăti viitoare. Funcţia se utilizează pentru a se

determina dacă valoarea de revenire a unei anumite investiţii este favorabilă sau nu,

ţinând cont de costul iniţial al investiţiei.

b) FV(rata dobânzii;număr de perioade[;mărimea platii; valoarea prezentă;

tipul])

- Descriere

Returnează valoarea viitoare (future value) a unei investiţii sau plasament în condiţii

de anuitate (plăţi şi rate ale dobânzii constante). Argumentele functiei financiare FV sunt

identice ce cele ale functiei PV cu exceptia faptului că unul din argumente reprezintă

valoarea prezentă a investiţiei sau plasamentului.

c) =PMT(rata dobânzii;număr de perioade;valoare prezenta[;valoare

viitoare;tip])

- Descriere

Calculează valoarea lunară sau anuală a plăţii pentru o investiţie sau un împrumut.

d) =RATE(număr de perioade;valoarea plăţii;valoare prezentă)

- Descriere:

Returnează rata dobânzii pe perioada unei anuităţi, pentru un împrumut sau o

investiţie.

1.2. Funcţii cu date calendaristice – Date &Time

În Excel, datele de tip dată calendaristică sunt salvate ca şi numere. Datei

01/01/1900 îi este atribuită valoarea 1, şi cu această dată s-a început numărătoarea.

- =NOW() returnează un număr corespunzător datei curente - cu zecimale

ce reprezintă ora;

- =TODAY() returnează un număr corespunzătore datei curente;

- =DATEVALUE("sir de caractere") calculează numărul-data

corespunzător şirului de caractere în format data calendaristica (sirul

trebuie plasat între ghilimele);=

- DATE(an;luna;zi) calculează numărul-data pentru data calendaristica

specificata ca argument;

- =YEAR(număr-data) returnează corespunzător anului, un număr cuprins

între 0 (1900) şi 199 (2099) - extragând rezultatul dintr-un număr-data;

- =MONTH(număr-data) extrage luna dintr-un număr-data, sub forma de

valori cuprinse între 1 şi 12;

- =DAY(număr-data) generează un număr corespunzător zilei cu valori

între 1 şi 31;

- =WEEKDAY(X) returnează numărul zilei din săptămâna corespunzător

argumentului X care poate fi de tip număr data calendaristică sau text în

format data calendaristică;

- =DAYS360(data debut;data sfarsit) calculează numărul de zile între

două date calendaristice considerând anul ca având 360 de zile;

- =TIME(ora;minut;secunda) calculează un număr-timp corespunzător

orei, minutului şi secundei;

- =TIMEVALUE(“sir de caractere”) returnează numărul-timp

corespunzător şirului de caractere specificat în format data/ora (între

ghilimele);

- =HOUR(număr-timp) extrage ora dintr-un număr-timp (0,000000 pentru

ora 24:00:00 şi 9,999988426 pentru ora 23:59:59), sub forma unui număr

cuprins între 0 şi 23;

- =MINUTE(număr-timp) extrage minutul dintr-un număr-timp, sub

forma unui număr întreg cuprins între 0 şi 59;

- =SECOND(număr-timp) extrage secunda dintr-un număr-timp sub

forma unui număr întreg cuprins între 0 şi 59;

Exemple:

1) Pentru studenţii născuţi în luna curentă, să se afişeze mesajul: „LA MULŢI

ANI!”

2) În funcţie de data curentă şi data naşterii, să se afişeze vârsta fiecărui student.

1) 2)

Nr.

crt. Nume Data_nasterii

Luna din

data nasterii Mesajul Varsta

1 Popescu 15.01.1983 =MONTH(C3)

=IF(MONTH(C3)=MONTH(NOW())

; "La multi ani!";MONTH(C3))

=YEAR(NOW())-

YEAR(C3)

2 Ionescu 15.03.1990 =MONTH(C4)



=YEAR(NOW())-

YEAR(C4)

3 Vasilescu 01.03.1992 =MONTH(C5)



=YEAR(NOW())-

YEAR(C5)

4 Andrei 01.04.2001 =MONTH(C6)



=YEAR(NOW())-

YEAR(C6)

5 Sasu 01.03.2000 =MONTH(C7)



=YEAR(NOW())-

YEAR(C7)

6 Pop 18.03.2000 =MONTH(C8)



=YEAR(NOW())-

YEAR(C8)

1.3. Funcţii matematice şi trigonometrice – Math& Trig

- =SUM(domeniu) aduna valorile din domeniu precizat ca argument.

- =PRODUCT (domeniu) multiplica valorile continute în domeniu.

=SUBTOTAL(referinta-tip;camp de regrupat) calculează un rezultat ce

provine dintr-o grupare a datelor operand diferite operatii specifice

(conform referintelor-tip) asupra unui camp de regrupat.

- =AVERAGE(domeniu) – calculează media aritmetică

- = COUNT (domeniu) – numără celulele dintr-un domeniu

- = MAX (domeniu) – determină maximum

- = MIN (domeniu) – determină minimum

- =SUMIF(domeniul; criteriu; camp de însumat) - adună conţinutul

celulelor potrivit unui criteriu dat.

- =COUNTIF(domeniu, criteriu) – numără condiţional celulele din

domeniu care satisfac criteriu specificat

- =RAND() returnează un număr aleator cuprins între 0 şi 1;

- =ABS(număr) returnează valoarea absoluta dintr-un număr;

- =LN(număr) calculează logaritmul natural

- =LOG(număr;baza) returnează logaritmul unui număr într-o bază

specificată;

- =LOG10(număr) returnează logaritmul în baza 10 dintr-un număr;

- =EXP(X) calculează baza logaritmului natural ridicată la puterea X. Baza

este o constantă şi are valoarea 2,7182818….;

- =MOD(X;Y) calculează restul împărţirii argumentului X la arg. Y;

- =FACT(număr) calculează factorialul unui număr pozitiv;

- =POWER(număr;putere) returnează rezultatul unui număr ridicat la putere

- =SQRT(număr) calculează rădăcina pătrată a argumentului;

- =SIN(X) returnează valoarea argumentului X în radiani;

- =COS(X) calculează cosinusul argumentului X în radiani ;

- =TAN(X) calculează tangenta argumentului X în radiani;

- =ASIN(X) calculează arc-sinusul argumentului X în radiani (similar

=ACOS(X) şi =ATAN(X);

- =PI() returnează valoarea numarului PI;

- =INT(număr) afişează partea întreagă a argumentului (a numărului real),

fără a-l rotunji.

Exemplificare:

- Diferenţa între SUMIF şi SUM

- Diferenţa între COUNTIF şi COUNT

Nr.crt. Nume Nota1 Nota2 Nota3 Nota4 Media

1 Popescu 10 6 3 8 =AVERAGE(C2:F2)

2 Ionescu 8 7 4 6 =AVERAGE(C3:F3)

3 Vasilescu 9 4 5 4 =AVERAGE(C4:F4)

4 Andrei 5 3 6 8 =AVERAGE(C5:F5)

5 Sasu 3 6 9 9 =AVERAGE(C6:F6)

6 Pop 4 10 5 10 =AVERAGE(C7:F7)

Suma Mediei,pt. nota1>5 =SUMIF(C2:C7;">5";G2:G7)

Suma Mediei,pt. nota2>5 =SUMIF(D2:D7;">5";G2:G7)

Suma tuturor mediilor =SUM(G2:G7)

1.4. Funcţii statistice – Statistical

- =MAX(domeniu) returnează cea mai mare valoare din domeniu

- =MIN(domeniu) returnează cea mai mică valoare din domeniu;

- =AVERAGE(domeniu) calculează media valorilor din domeniu;

- =GEOMEAN(domeniu) calculează media geometrica a dintr-un domeniu

- =HARMEAN(domeniu) calculează media armonica a valorilor dintr-un

domeniu

- =MEDIAN(domeniu) calculează valoarea mediana dintr-un domeniu;

- =COUNT(domeniu) numără celulele ocupate dintr-un domeniu;

1.5. Funcţii de informare - Information

- =ISBLANK(celulă) determină dacă conţinutul celulei este sau nu vid.

Funcţia returnează TRUE ,dacă celula este vidă şi FALSE –valoarea

logica de fals- în caz contrar;

- =ISNUMBER(celulă) verifica dacă celula conţine o valoare numerică.

Functia returnează TRUE – adevarat- daca celula e număr, altfel

returnează FALSE sau fals.

- =ISTEXT(celulă) verifică dacă celula conţine un şir de caractere,

returnând după caz TRUE sau FALSE.

- =ISNONTEXT(celulă) verifică dacă celula nu conţine un şir de caractere,


- =ISLOGICAL(celulă) verifică dacă celula conţine o valoare de tip logic


- =ISERROR(celulă) verifică dacă celula conţine o valoare de tip eroare,


1.6. Funcţii logice – Logical

- =IF(conditie;valuarea în caz de TRUE; valoarea în caz de FALSE)

testează argumentul condiţie şi în funcţie de rezultatul evaluării logice,

generează Value_if_true dacă condiţia este adevarată sau Value_if_false

dacă aceasta este falsă.

- =AND(evaluare logica1,evaluare logica2,...) returnează valoarea logica

TRUE dacă toate argumentele sunt adevarate şi valoarea logica FALSE

dacă unul sau mai multe argumente sunt false;

- =OR(evaluare logica1,evaluarea logica2,...) returnează valoarea logică

TRUE dacă orice argument este adevarat şi valoarea logică FALSE dacă

toate argumentele sunt false;

- =NOT(evaluarea logica) inversează valoarea argumentului, returnând

după caz TRUE sau FALSE;

- =TRUE() returnează valoarea logică TRUE;

- =FALSE() returnează valoarea logică TRUE;

Exemplu:

Pentru un set de date, să se calculeze media aritmetică a două note astfel:

- dacă notele sunt >=5, să se afişeze media

- dacă o notă <=5 să se afişeze „RESPINS”

1.7 Funcţii specifice bazelor de date - Database

Funcţiile tip bază de date au în mod invariabil aceeaşi listă de argumente:

-database: - domeniul care defineşte baza de date. O bază de date este

formată din liniile şi coloanele Sheet-ului.

- field: - adresa ce conţine numele câmpului sau câmpurilor din baza de

date;

- criteria: domeniul în care sunt specificate condiţiile impuse câmpurilor din

secţiunea filed.

Exemple de funcţii ce operează asupra bazelor de date:

- =DSUM(baza de date; câmpul; criteriile) returnează suma valorilor unui

câmp aferent unei baze de date, care răspunde unui criteriu de selecţie;

- =DMAX(baza de date; criterii ) returnează cea mai mare valoare dintr-un

câmp aferent unei baze de date, corespunzător unui criteriu de selecţie;

- =DMIN(baza de date; câmp; criterii) returnează cea mai mică valoare

dintr-un câmp al unei baze de date, corespunzător unui criteriu de selecţie;

- =DAVERAGE(baza de date; câmp; criterii) calculează media valorilor

unui câmp dintr-o baza de date, potrivit criteriului de selecţie specificat

- =DCOUNT(baza de date;câmp; criterii) numără celulele nevide din

câmpul specificat, conform criteriilor specificate;

- =DGET(baza de date; câmp; criterii) returnează conţinutul unui câmp al

bazei de date, corespunzător unui criteriu specificat. Funcţia este utilă

pentru a regăsi o informaţie unică;

Exemple de funcţii cu baze de date:

Se doreşte să se calculeze suma notelor >=5 pentru Nr.crt.>=3 şi media acestora.

Domeniul corespunzător bazei de date este : A1:C12, câmpul cu care se calculează

suma, respectiv media este câmpul Nota aflat la adresa C1, iar criteriile care trebuiesc

respectate sunt în domeniul A16:C17.

1.8 Funcţii de căutare şi consultare – Lookup& Reference

- =CHOOSE(index-numeric;lista de valori) returnează în urma unei

alegeri dintr-o listă de valori, o acţiune sau o valoare, ce urmează a fi

activată sau executată, corespunzător unui index numeric. Indexul numeric

determină care valoare (de tip text, numerică sau referinţă celulară) din

lista de argumente va fi selectată. Indexul este un număr cuprins între 0 şi

29.

- =COLUMN(referinţă celulară sau câmp) returnează numărul coloanei

corespunzătoare referinţei celulare sau numărul primei coloane pentru

câmpul specificat;

- =COLUMNS(camp) returnează numărul de coloane aferente câmpului

specificat ca argument;

- =ROW(referinta celulara sau camp) returnează numarul liniei

corespunzătoare referinţei celulare sau numărul primei linii a câmpului


- =ROWS(camp) returnează numărul de linii pe care îl ocupa câmpul


- =AREAS(referinţă celulară) indică numărul de zone contigue dintr-un

câmp;

- =VLOOKUP(cheie;camp de consultare;coloana de recuperat)

returnează conţinutul unei celule ce figurează într-o coloana dintr-un

tablou de consultare verticală.

1.9 Funcţii pentru text sau şiruri de caractere – Text

Funcţiile text (Text): permit diferite operaţii cu şiruri de caractere şi furnizeaza în

egală măsură informatii legate de textul existent în celulă:

- =CHAR(cod numeric ASCII) returnează caracterul corespunzator

codului numeric ASCII specificat ca argument;

- =TRIM(text) afişează şirul de caractere specificat ca argument în care

toate spatiile inutile sunt anulate (cu excepţia spatiilor care separa

cuvintele textului);

- =CODE(text) returnează codul numeric pentru primul caracter din textul


- =CONCATENATE(text1;text2;…) concatenează mai multe şiruri de

caractere specificate ca argumente, într-unul singur;

- =EXACT(text1;text2) verifică dacă doua şiruri de caractere sunt identice.

Comparând cele doua şiruri, funcţia returnează valoarea logica TRUE

dacă acestea sunt identice sau valoarea logica FALSE în caz contrar;

- =UPPER(text) afişează cu majuscule textul specificat ca argument;

- =LOWER(text) afişează cu minuscule textul specificat ca argument;

- =MID(text;N;X) afişează X caractere ale textului specificat ca argument,

începând cu poziţia “N”;

- =LEN(text) returnează numărul caracterelor ce formează textul specificat

ca argument;

- =SUBSTITUTE(text-sursa;N;X;text-nou) returnează un nou şir de

caractere (text-nou) la a “N”-a poziţie a textului-sursă, după ce au fost

anulate X caractere;

- =REPT(text;număr de ori) repeta afişarea textului de un număr

specificat de ori;

- =PROPER(text) determină scrierea cu majuscula a fiecărei prime litere

din textul specificat ca argument;

- =VALUE(text) converteşte un text ce reprezintă un număr într-o valoare

numerica;

- =DOLLAR(număr;zecimale) converteşte un număr în text, folosind un

format monetar;

- =FIND(text1;text2;N) localizează poziţia la care începe textul1 în textul2

începând căutarea cu poziţia N;

2. Reprezentări grafice.

MS Excel este capabil să reprezinte grafic datele dintr-o foaie de calcul; astfel sunt

definite mai multe categorii de tipuri de grafice: Bar, Column, Area, Pie, Scatter,... care

permit reprezentarea atât a unei diagrame simple cât şi a unor dependenţe funcţionale(XY

Scatter) sau grafice de suprafaţă. În cursul de faţă vom trece în revistă dependenţa

funcţională.

1) Grafice de tip XY (Scatter)

Pentru a „reprezenta grafic” o funcţie în Excel trebuie să parcurgem următorii paşi:

- Se alege domeniul de definiţie al funcţiei f;

- Se alege o diviziune echidistantă a domeniului de definiţie;

- Se calculează valorile funcţiei în punctele diviziunii generate

- Se selectează domeniul care se va reprezenta, iar ca tip de grafic se alege

XY(Scatter)

Exemplu:

Fie funcţia: . Să se traseze graficul funcţiei.

Etape:

i) alegem domeniul de definiţie [-10, 10];

ii) alegem diviziunea echidistantă de valoare 1;

iii) se calculează valorile funcţiei în punctele de diviziune cu funcţia =

POWER(A1,5);

iv) se reprezintă grafic

Rezultatul obţinut:

-10 -100000

-9 -59049

-8 -32768

-7 -16807

-6 -7776

-5 -3125

-4 -1024

-3 -243

-2 -32

-1 -1

0 0

1 1

2 32

3 243

4 1024

5 3125

6 7776

7 16807

8 32768

9 59049

10 100000

-150000

-100000

-50000

0

50000

100000

150000

-15 -10 -5 0 5 10 15

2) Grafice de suprafaţă (Surface)

Fie funcţia . Să se reprezinte grafic funcţia.

Etape:

i) Se stabileşte un domeniu de definiţie al funcţiei. In cazul dat vom alege

domeniul [-10,10]x[-10,10];

ii) Alegem diviziunea echidistantă de valoare 1

iii) Se determină valorile funcţiei în punctele alese

A calcula clasic valoarea funcţiei în fiecare punct, înseamnă a consuma mult timp.

Există aşa numitele formule pe domeniu. Pentru a aplica o formulă în tot domeniul

procedăm astfel:

- selectăm domeniul în care urmează să scriem funcţia; pentru exemplul nostru

domeniul e: B2:V22;

- scriem funcţia (x2+y2) selectând ca valoare a lui x întreg domeniul lui x, adică

B1:V1, iar pentru y tot domeniul lui y, A2:A22. Deci:

= power(B1:V1,2)+power(A2:A22,2)

- apăsăm combinaţia de taste CTRL+SHIFT+ENTER, moment în care funcţia de

mai sus se va transforma în funcţie aplicată pe domeniu, adică

{= power(B1:V1,2)+power(A2:A22,2)}

iv) Se selectează domeniul pentru reprezentarea funcţiei (A1:V22) şi se

alege ca tip de grafic Surface

-10 -7 -4 -1 2 5 8 -10

-2

6

02040

6080

100

120

140

160

180

200

3. Instrumente de analiză statistică (Analysis ToolPak).

Componenta inclusă la cerere Analysis ToolPak din Microsoft Excel furnizează un

set de instrumente de analiză a datelor — denumit Analysis ToolPak (Pachet de

instrumente de analiză)— care se utilizează pentru a economisi etape atunci când se

dezvoltă analize complexe, statistice sau de inginerie. Se furnizează datele şi parametrii

pentru fiecare dintre analize; instrumentul de analiză utilizează macro-funcţiile statistice

sau de inginerie corespunzătoare şi afişează rezultatele într-un tabel de ieşire. Unele

instrumente auxiliare generează şi diagrame auxiliare tabelelor de ieşire. Dacă componenta

nu este instalată este necesar a selecta opţiunea Excel Option de la butonul Office, apoi

din secţiunea Add-Ins se selectează utilitarele ce urmează a fi instalate.

Componenta Analysis ToolPak instalată se găseşte în meniul Data, în grupul

Analysis şi include următoarele instrumente:

În cursul de faţă ne vom opri la descrierea unor componente uzuale.

3.1) Anova

Instrumentele de analiză Anova furnizează diferite tipuri de analiză de varianţă.

Instrumentul de utilizat depinde de numărul factorilor şi de numărul eşantioanelor existente

din cadrul populaţiilor de testat.

Anova: Single Factor Acest instrument efectuează o analiză simplă de varianţă

asupra datelor pentru două sau mai multe eşantioane. Analiza furnizează o testare a

ipotezei că fiecare eşantion este derivat din aceeaşi distribuţie de probabilitate de bază faţă

de ipoteza că distribuţia de probabilitate de bază nu este aceeaşi pentru toate eşantioanele.

Dacă sunt numai două eşantioane, se poate utiliza la fel de bine funcţia foii de lucru,

TTEST. Când sunt mai mult de două eşantioane, nu este adecvată generalizarea funcţiei

TTEST, ci poate fi utilizat numai modelul Single Factor Anova.

Anova: Two-Factor With Replication Acest instrument de analiză este util când

datele pot fi clasificate după două dimensiuni diferite.

Anova: Two-Factor Without Replication Acest instrument de analiză este util

când datele sunt clasificate după două dimensiuni diferite, ca în cazul instrumentului Two-

Factor case With Replication. Pentru acest instrument, însă, presupunem că există o

singură observaţie pentru fiecare pereche. Utilizând acest instrument, se pot aplica testele

din prima şi a doua etapă a cazului Anova: Two-Factor With Replication, dar nu avem

suficiente date pentru a aplica testul din a treia etapă.

3.2) Corelaţie

Funcţiile CORREL şi PEARSON calculează ambele coeficientul de corelaţie dintre

două variabile de măsurare când măsurarea fiecărei variabile este observată pentru fiecare

dintre N subiecţi. (Orice observaţie lipsă pentru oricare dintre subiecţi provoacă ignorarea

acelui subiect în analiză.) Instrumentul de analiză a corelaţiei este în special util când

există mai mult de două variabile de măsurare pentru fiecare dintre N subiecţi. Acesta

furnizează un tabel cu rezultate, o matrice de corelaţii, arătând valoarea funcţiei CORREL

(sau PEARSON) aplicată fiecărei perechi posibile de variabile de măsurare.

Coeficientul de corelaţie, la fel ca cel de covarianţă, este o măsură a gradului de

variaţie comun al celor două variabile de măsurare. Spre deosebire de covarianţă,

coeficientul de corelaţie este măsurat pe o scară, astfel că valoarea sa este independentă de

unitatea în care se exprimă variabilele. (De exemplu, dacă cele două variabile de măsurare

sunt greutatea şi înălţimea, valoarea coeficientului este neschimbată chiar dacă greutatea se

măsoară în kilograme sau livre.) Valoarea oricărui coeficient de corelaţie trebuie să fie

între -1 şi +1 inclusiv.

Se poate utiliza instrumentul de Corelaţie pentru a examina fiecare pereche de

variabile de măsurare şi a determina dacă cele două variabile de măsurare tind să se mute

împreună — aceasta însemnând, dacă valorile mari ale unei variabile tind să poată fi

asociate cu valorile mari ale celeilalte variabile (corelaţie pozitivă), dacă valorile mici ale

unei variabile tind să poată fi asociate cu valorile mici ale celeilalte variabile (corelaţie

negativă) sau dacă valorile celor două variabile tind să fie necorelate (corelaţie aproape

zero).

3.3) Covarianţa

Instrumentele Corelaţie şi Covarianţă pot fi utilizate ambele în aceleaşi condiţii,

când aveţi N diferite variabile de măsurare observate pe un set de indivizi. Instrumentele

Corelaţie şi Covarianţă dau fiecare un tabel de rezultate, o matrice, care arată coeficientul

de corelaţie sau, respectiv, de covarianţă dintre fiecare pereche de variabile de măsurare.

Deosebirea constă în aceea că în timp ce coeficientul de corelaţie este măsurat pe o scară

de la -1 la +1 inclusiv, covarianţa corespunzătoare nu este măsurată pe o scară. Atât

coeficientul de corelaţie, cât şi cel de covarianţă sunt măsuri ale gradului de variaţie comun

pentru cele două variabile.

Instrumentul Covarianţă calculează valoarea funcţiei COVAR a foii de calcul,

pentru fiecare pereche de variabile de măsurare. (Utilizarea directă a funcţiei COVAR în

locul instrumentului Covarianţă este o alternativă rezonabilă când există numai două

variabile de măsurare, adică N=2.) Intrarea pe diagonala tabelului cu rezultate al

instrumentului Covarianţă din rândul i, coloana i este covarianţa variabilei de măsurare i cu

ea însăşi; este exact varianţa populaţiei pentru acea variabilă, calculată cu funcţia VARP a

foii de lucru.

Se poate utiliza instrumentul Covarianţă pentru a examina fiecare pereche de

variabile de măsurare şi a determina dacă cele două variabile de măsurare tind să se mute

împreună — aceasta însemnând, dacă valorile mari ale unei variabile tind să poată fi

asociate cu valorile mari ale celeilalte variabile (covarianţă pozitivă), dacă valorile mici ale

unei variabile tind să poată fi asociate cu valorile mici ale celeilalte variabile (covarianţă

negativă) sau dacă valorile celor două variabile tind să fie necorelate (covarianţă aproape

zero).

3.4) Statistici descriptive

Acest instrument de analiză generează un raport de statistici univariabile pentru

datele din zona de intrare, furnizând informaţii despre tendinţa centrală şi variabilitatea

datelor.

Statisticile determinate sunt: media, eroarea standard, valoarea mediană, deviaţia

standard, varianţa, distanţele Kurtoisis şi Skewness, Suma, Minimul, maximul şi numărul

intrărilor.

Pentru următoarele date de intrare se pot calcula statisticile la Frecvenţă, mem.

RAM şi HDD

Nr.crt. Familie Denumire Frecventa RAM HDD

1 Intel P4 2800 256 50

2 Intel P4 3200 128 80

3 AMD Athlon XP 1500 256 40

4 Intel Celeron 1500 1024 80

5 Intel P4 1660 512 60

6 Intel Intel Core Duo 2160 256 120

7 Intel Celeron M 1700 2048 100

8 AMD Sempron M 2600 1024 80

9 AMD Turion 64 3300 512 70

10 Intel P4Mobile 1200 512 60

Statisticile obţinute:

Frecventa RAM HDD

Mean 2162.00 Mean 652.80 Mean 74.00

Standard Error 241.26 Standard Error 183.47 Standard Error 7.48

Median 1930.00 Median 512.00 Median 75.00

Mode 1500.00 Mode 256.00 Mode 80.00

Standard

Deviation 762.94

Standard

Deviation 580.17

Standard

Deviation 23.66

Sample

Variance 582084.44

Sample

Variance 336600.18

Sample

Variance 560.00

Kurtosis -1.51 Kurtosis 3.32 Kurtosis 0.31

Skewness 0.39 Skewness 1.76 Skewness 0.60

Range 2100.00 Range 1920.00 Range 80.00

Minimum 1200.00 Minimum 128.00 Minimum 40.00

Maximum 3300.00 Maximum 2048.00 Maximum 120.00

Sum 21620.00 Sum 6528.00 Sum 740.00

Count 10.00 Count 10.00 Count 10.00

4. Rezolvarea problemelor de optimizare (Goal Seek,

Solver)

4.1 Goal Seek

Goal Seek reprezintă un instrument ce poate realiza o operaţie numita "reverse

formula". De ex. "Ce notă trebuie să iau la matematică ca să obţin media finală 9.30?”

Nota Info Nota PMG Nota Drept

Nota

Mate Media

Nume1 8 10 9.5 =AVERAGE(B2:E2)

Pentru a folosi opţiunea Goal Seek se formulează mai întâi problema, se introduc

variabilele şi formulele. Celula cu rezultate trebuie să conţină obligatoriu o formulă în

componenţa căreia trebuie să intre şi o referire la adresa celulei al cărui conţinut urmează

să fie determinat cu goal seek

Pentru problema noastră, trebuie să determinăm nota la mate astfel încât media

finală să fie 9.30

Rezolvare:

- se alege din meniul Data, grupul Data Tools, opţiunea What – If

Analysis apoi Goal Seek

Fereastra Goal Seek

- la Set cell – se stabileşte adresa celulei în care voi avea rezultatul; pentru

exemplul nostru F2 (celula cu valoarea mediei)

- To value – presupune setarea valorii la care trebuie să se ajungă în celula

specificată la Set cell; (9.30)

- By changing cell – se specifică adresa celulei care se va modifica; în

exemplu adresa celulei cu nota la mate (E2);

Rezultatul obţinut:

Găseşte o soluţie pentru problema dată; în celula E2 valoarea 9.70 conduce la o

medie în F2 egală cu 9.30;

Cu ajutorul opţiunii Goal Seek se poate modifica conţinutul unei singure celule, şi

în plus asupra celulelor nu pot fi impuse restricţii.

De exemplu „Să se determine nota la matematică astfel încât media să fie 9.70”

Stabilind valoarea mediei de 9.70 se obţine pentru nota de la mate valoarea 11.30,

valoare ce depăşeşte pragul logic 10 /(zece).

Pentru a putea rezolva acest tip de problemă este necesar să se modifice valorile

mai multor celule. Modificarea mai multor celule se poate realiza doar cu Solver-ul.

4.2. Solver

MS Excel are o componentă numită Solver care permite rezolvarea unor probleme

de:

- programare liniară sau de transport;

- sisteme liniare sau neliniare

Această componentă se lansează din meniul Data, grupul Analysis. Dacă nu apare

în grupul Analysis, atunci trebuie instalată din Add-Ins.

Descrierea parametrilor din componenţa Solver-ului:

- Set Target Cell - adresa celulei în care va fi o valoare specificată (Value

of), o valoare maximă (Max) sau minimă (Min)

- By Changing Cells – adresa celulelor al căror conţinut se poate modifica

- Subject to the Constrants – adăugarea constrângerilor

Pentru a determina notele astfel încât media să fie 9.70 se introduc următoarele

constrângeri:

- fiecare nota<=10

- fiecare nota >=5

Solver-ul modifică fiecare celulă astfel încât să se ajungă la soluţia dorită.

Problemă de transport

Se consideră că există m centre de aprovizionare (depozite) şi n centre de consum (tabere

unităţi militare etc.). Se pune problema să se determine un plan de transport pentru un

produs omogen care se află în cantitatea ai la depozitul i (1 ≤ i ≤ m) şi este cerut în

cantitatea bj la centrul j (1 ≤ j ≤ n). Se notează cu xij cantitatea necunoscută ce va fi

transportată de la depozitul i la centrul de consum j şi cu cij costul transportului unei unităţi

din produsul considerat de la depozitul i la centrul j (pentru simplificare se presupune că

acest cost unitar nu depinde de cantitatea transportată pe ruta respectivă).

Costul total al transportului de la toate cele m depozite la toate cele n centre de consum

este

Pentru a putea efectua transportul este necesar ca

Se pot exprima atunci următoarele mărimi:

– cantitatea cerută de la depozitul i la toate cele n centre de consum

ai = xi1 + xi2 + ... + xin = cantitatea aflată la depozitul i,

– cantitatea transportată de la toate cele m depozite la centrul de consum j

bj = x1j + x2j + ...+ xmj = necesarul la centrul de consum j,

O condiţie evidentă este xij ≥ 0, 1 ≤ i ≤ m, 1 ≤ j ≤ n .

Trebuie rezolvată următoarea problemă:

Sisteme de ecuaţii liniare

Pentru rezolvarea unui sistem de ecuaţii liniare de tip Cramer se pot folosi funcţiile

Excel MMULT şi MINVERSE astfel:

• se introduc în foaia electronică de calcul matricea coeficienţilor şi vectorul termenilor

liberi;

• se selectează zona de memorie care urmează să conţină soluţia sistemului;

•în zona de editare fx se tastează =MMULT

(MINVERSE(left_top_coeficienti:right_bottom_coeficienti);left_top_T_liberi:

right_bottom_T_liberi), unde: left_top_coeficienti este colţul din stânga sus şi

right_bottom_coeficienti este colţul din dreapta jos al matricei coeficienţilor,

left_top_T_liberi şi right_bottom_T_liberi sunt celulele de început şi sfârşit ale zonei în

care se alflă termenii liberi.

elemente avansate de excel

Documents

Transcript of elemente avansate de excel