Informatic Medical i Biostatistic Conf. dr. Lucian V. Boiculese

1

Subiecte ce vor fi prezentate

referire absolut,

stilul R1C1 ,

funcii pentru calculul indicatorilor statistici de baz,

contorizare, sumare, mediere - n form simpl i forma condiionat,

funca if(),

generator de valori aleatoare.

2

Metode de referire a celulelor

Exist dou metode de referire a celulelor n Microsoft Excel: 1. Stilul de referire A1 este stilul implicit. 2. Stilul R1C1.

Implicit Excel folosete stilul A1 ce l-am folosit i noi pn acum. O celul este identificat prin elementele coloan respectiv rnd, corespunztoare. Exemple:

Celula din coloana A rndul 10 se va referi cu notaia A10 Un bloc de date de pe coloana A ntre rndurile 10 i 21 A10:A21 Un bloc de date pe rndul 17 ntre coloanele D i F D17:F17 Toate celulele din coloana H H:H Toate celulele din coloanele D pn la H D:H Toate celulele din rndul 7 7:7 Toate celulele din rndurile 5 pn la 9 5:9 Blocul de date de la A2 pn la F29 A2:F29

Referirea unui bloc de date dintr-o alt foaie de calcul se va face cu utilizarea prefixului nume _foaie_calcul!

Exemplu: AVERAGE(Sheet3!B1:B24) 3

Referire absolut referire relativ

Referirea absolut Pentru o referire absolut se folosete semnul $. Acesta dac prefixeaz coloana atunci va bloca acea coloan, dac prefixeaz rndul atunci va bloca acel rnd. Exemplu $A$5 - celula A5 este referit absolut (dou semne $).

B C D E

4 credite 8 6

5 anatomie biofizic media ponderat

6 7 8 =(C6*C$4+D6*D$4)/(C$4+D$4)

7 9 7 =(C7*C$4+D7*D$4)/(C$4+D$4)

8 8 10 =(C8*C$4+D8*D$4)/(C$4+D$4)

A B C

1 5 6 =A1*B1

2 4 8 =A2*B2

Referirea relativ Se bazeaz pe poziia relativ a celulei argument a funciei fa de celula n care este scris formula de calcul. Dac poziia celulei care conine formula se schimb prin rnd sau coloan atunci i celulele relative din formul i schim poziia n acelai mod. Exemplu: dac n formula din C1 avem referire la A1, atunci prin aplicarea funciei Autofill, ncrcarea rndului urmtor cu formula de calcul, va face referire la rndul corespunztor poziiei curente ncrcate (deci A2).

4

Referirea mixt Conine doar un singur semn $ -

utilizat doar pentru

rnd sau doar

pentru coloan. Referire mixt: A$5

Referirea 3D

Este vorba de formulele ce fac apel la referine din mai multe foi de calcul. Acestea pot s cuprind blocuri de date pe aceleai poziii din foi diferite de calcul. Exemplu: Dorim s facem media valorilor din celulele B7 din foile de calcul Sheet1 pn la Sheet10 (nu neaprat 10 foi de calcul, doar cele cuprinse intre 1 i 10). = AVERAGE(Sheet1:Sheet10!B7)

Observaie: Dac se adaug foi de calcul noi ntre foile 1 i 10 din exemplul precedent, atunci media final va fi modificat n sensul lurii n calcul i a foilor noi introduse. Dac se terg foi de calcul din domeniu, atunci formula se va actualiza innd cont numai de foile de calcul rmase n domeniu.

Referirea R1C1 2003(Tools + Options + General + R1C1 Reference Style)

2007(Office Button+Excel Options+Formulas+R1C1 style)

2010(File + Excel Options+Formulas+R1C1 style)

Se poate folosi acest stil ca o alternativ a stilului A1. Se pot astfel face referiri la rnduri ct i coloane folosind numere. Ordinea n definire este rnd, coloan. Pentru a referi o celul vom folosi abrevierea R pentru rnd respectiv C pentru coloan. Pentru referiri absolute centrul de referin rmne colul din stnga sus. R2C4 semnific al doilea rnd respectiv coloana a patra - referire absolut. Pentru referirea relativ centrul de referin devine tocmai celula n care se scrie expresia de calcul.

5

Este necesar s avem o metod de difereniere ntre forma de adresare relativ i forma absolut. Astfel se folosesc parantezele ptrate pentru definirea relativitii n adresare.

Referirea relativ: R[2]C[-1] semnific celula aflat dou rnduri n jos respectiv o coloana la stnga (prin valoarea [-1]) fa de poziia curent (celula n care se scrie formula).

Exemple:

R[-1]C Este o referire relativ la celula cu un rnd mai sus din coloana curent. R[2]C[3] Este o referire relativ cu dou rnduri n jos i trei coloane la dreapta. R2C3 O referire absolut a celulei din rndul 2 coloana a 3-a (echivalent $C$2). R[-1] Referin relativ a ntregului rnd ce se afl deasupra rndului curent. R Referin absolut a ntregului rnd curent.

Reinei !!! Utilizarea parantezelor ptrate implic definirea referinei relative.

6

Nr Destinaie (celula n care

suntem poziionai pentru

scrierea formulei)

Stilul A1

formula

Stilul R1C1

formula

1 C1 =A1*B1 =RC[-2]*RC[-1]

2 C3 =A3*A$1 =RC[-2]*R1C[-2]

3 B1 =A:A =C[-1]

4 B1 =A1:A7 =RC[-1]:R[6]C[-1]

5 C7 =$A$3 =R3C1

Exemple de referire realizate prin cele dou stiluri.

7

Funcii n Ms Excel

MIN(bloc1,bloc2,...bloc30)

MAX(bloc1,bloc2,...bloc30)

AVERAGE(bloc1,bloc2,...bloc30)

GEOMEAN(bloc1,bloc2,...bloc30)

HARMEAN(bloc1,bloc2,...bloc30)

MEDIAN(bloc1,bloc2,...bloc30) Este valoarea de mijloc din cadrul irului ordonat

n

x

n

xxxx

n

ii

n

121

...

n i

n

i

nnG xxxxxM

1

321 ...

i

n

ii

nG xx

nn

xxxM lglg

1lg......lglglg

1

21

n

i in

H

x

n

n

xxx

M

121

11...11

1

8

Exemplu de calcul a medianei pentru dou seturi de date (un numr par respectiv impar de valori)

A

1 Data (X)

2 1 Mediana este valoarea din mijlocul irului ordonat (nu conteaz dac irul este ordonat cresctor sau descresctor).

3 2

4 3

5 4

6 5

7 6

8 Formula Descriere

9 =MEDIAN(A2:A6) Mediana primelor 5 numere din list este 3.

10 =MEDIAN(A2:A7) Mediana celor 6 numere este media celor din mijloc adic 3 i 4 (3.5)

Dac numrul de date este par n=2*k, mediana este media dintre Xk i Xk-1. Dac volumul de date este impar, n=2*k+1, mediana este valoarea lui Xk+1.

9

MODE(bloc1,bloc2,...bloc30)

Reprezint valoarea ce se repet de cele mai multe ori (valoarea de frecven maxim, cea mai des ntlnit valoare). (Modul sau valoare modal)

A

1 Data

2 5.6

3 4

4 4

5 3

6 2

7 4

8 Formula Descriere

9 =MODE(A2:A7) Modul, sau cea mai des ntlnit valoare este 4, pentru datele prezentate n tabelul alturat.

10

RANK.EQ(numr, bloc date, [ordonare]) , sau RANK(numr, bloc date, ordonare)

Returneaz poziia (rangul) din lista ordonat a numrului cutat, Numr este numrul de interes. Bloc de date blocul de date surs. Ordonare este metoda de ordonare: 0 descendent (implicit), 1 ascendent (sau 0)

Exemplu:

A

1 Date

2 7

3 4

4 4

5 1

6 2

7 Formula Descriere (Rezultat)

7 =RANK(A3,A2:A6,1) Rangul valorii 4 este 3 (prima valoare 4 se

gsete pe poziia a 3-a).

8 =RANK(A2,A2:A6,1 Rangul valorii 7 este 5 (este corect ?... val.

4 se repet). 11

RANK.AVG (nr, bloc date, ordonare)

Returneaz media rangurilor pentru numrul ce se repet; =RANK.AVG(A3,A2:A6,1)

Rezultatul este 3.5

SUM(bloc1,bloc2, ..., bloc30)

SUMIF(domeniu, criteriu, [domeniu de sumare])

Domeniu este grupul de celule care vor fi evaluate prin criteriu. Criteriu condiiile de evaluare a celulelor (ex. 32, "32", ">32", or activ" ). Domeniu de sumare reprezint celulele ce vor fi sumate dac criteriul este ndeplinit. Dac este omis, atunci domeniul de sumare este identic cu domeniul de evaluare. Domeniul de sumare este opional.

A B

1 Valoare intervenie Comision

2 1000 70

3 2000 140

4 3000 210

5 4000 280

6 Formul Descriere (rezultat)

7 =SUMIF(A2:A5,">1600",B2:B5) Suma comisioanelor pentru Val. interv. > 1600 (630)

8 =SUMIF(A2:A5,">1600") Suma valorilor intrev. mai mari ca 1600 (9000)

9 =SUMIF(A2:A5,"=3000",B2:B3) Suma comisioanelor pentru Val. interv.=3000(210) 12

D E

1 Medicaie Cantitate

2 omeran 3

3 ercefuril 5

4 omeran 5

5 omeran 3

6

Fasconal, omeran,

antibiotic2 4

7 aspirin 3

8 aspirin 3

9 omeran 5

Exemplu:

=sumif(D2:D9,omeran,E2:E9)

Se vor suma cantitile pentru medicaie omeran. Rezultatul va fi: 3+5+3+5=16

Caractere speciale: *, ?

* oricte caractere, ? doar un singur caracter. =sumif(D2:D9,*omeran*,E2:E9) Rezultatul va fi: 3+5+3+4+5=20

Se caut cuvntul omeran n cadrul textului indiferent de poziie.

OMERAN , CAPSULE

Indicaii: Ulcer duodenal evolutiv; ulcer gastric evolutiv; esofagita de reflux; sindrom Zollinger - Ellison. De

asemenea este indicat ca tratament preventiv in caz de recidiva in esofagita de reflux si in ulcerul

gastric si duodenal dificil de controlat.

ERCEFURYL, SUSPENSIE, CAPSULE

Indicaii: Diaree acuta de origine presupus bacteriana, in absenta fenomenelor invazive. Tratamentul nu se

substituie unei rehidratri, daca este necesar. Importanta rehidratrii i calea de administrare (orala, i.v.) trebuie adaptate gradului de diaree, vrstei si terenului pacientului.

13

14

Sumarea valorilor dintr-un domeniu folosind mai multe criterii de selecie.

SUMIFS(domeniu de sumare, domeniu1,criteriu1, [domeniu2, criteriu2],)

Parantezele ptrate arat caracterul opional al condiiei.

Pentru a selecta o valoare trebuie ca toate criteriile s fie ndeplinite este operaia de tip conjuncie (i logic). Forma minim conine cel puin o pereche domeniu-criteriu de selecie, pe lng domeniul de sumare.

Domeniile de sumare i cele ce definesc criteriul de selecie trebuie s aib aceeai dimensiune. Se pot folosi caracterele speciale (metacaractere) * i ? Pentru crearea de filtre. Numrul total de criterii de selecie este 127.

COUNT(bloc1, bloc2, ...bloc30) contorizeaz (numr) celulele ce conin valori numerice.

COUNTA(bloc1, bloc2, ...bloc30) contorizeaz (numr) celulele ce conin date, deci nu sunt vide.

A

1 Data

2 Sales

3 12/8/2008

4

5 19

6 22.24

7 TRUE

8 #DIV/0!

9 Formula Descriere (Rezultat)

1

0

=COUNT(A2:A8) Numrul de celule ce conin date numerice (rspuns: 3)

11 =COUNT(A5:A8) Numrul de celule ce conin date nrce. din ultimele 4 rnduri (2)

1

2

=COUNTA(A1:A7,2) Numrul de celule ce conin date i valoarea 2 (7)

1

3

=COUNTA(A1:A7,two) Numrul de celule ce conin date i textul two (7) 15

COUNTIF(domeniu,criteriu) , criteriu este de form text. A B

1 Tip Numar

2 Omeran 32

3 Fasconal 54

4 Ercefuril 75

5 Omeran 86

6 Formula Descriere (rezultat)

7 =COUNTIF(A2:A5,Omeran") Numrul de celule ce conin cuvntul Omeran (2)

8 =COUNTIF(A2:A5,A4) Nr. de celule ce conin cuvnrul Ercefuril (1)

9 =COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2) Nr. de celule ce conin Fasconal + nr.cel. Omeran (3)

10 =COUNTIF(B2:B5,">55") Nr.cel. din coloana B ce au valori mai mari de 55 (2)

11 =COUNTIF(B2:B5,""&B4) Nr.cel. din coloana B ce au valori diferite de B4=75

(3)

12 =COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85") Diferena dintre dou contorizri (3)

Formula Countif() poate fi nlocuit cu Sumif() pe o coloan ncrcat cu valoarea 1 iar coloana condiie rmane neschimbat. Caractere speciale : *, ? foarte utile n cutarea cuvintelor ntr-un bloc tip text.

16

17

Contorizarea valorilor folosind mai multe criterii de selecie.

COUNTIFS(domeniu1,criteriu1,[domeniu2,criteriu2]) ,

Pentru a selecta o valoare trebuie ca toate criteriile s fie ndeplinite este operaia de tip conjuncie (i logic). Forma minim conine cel puin prima pereche domeniu-criteriu de selecie. Numrul maxim de perechi este 127. Domeniile trebuie s aib aceeai dimensiune. Se pot folosi caracterele speciale * i ? Pentru crearea de filtre.

Criteriu este de form text: >27 ; =30 ; 50; fasconal ; laparoscopie

AVERAGE(bloc1,bloc2, ..., bloc30)

AVERAGEIF(domeniu, criteriu, [domeniu de calcul a mediei])

Domeniu este grupul de celule care vor fi evaluate prin criteriu. Criteriu condiiile de evaluare a celulelor (ex. 32, "32", ">32", or activ" ). Domeniu de sumare reprezint celulele ce vor fi sumate dac criteriul este ndeplinit. Dac este omis atunci domeniul de sumare este identic cu domeniul de evaluare.

18

19

Calculul mediei valorilor folosind mai multe criterii de selecie.

AVERAGEIFS(domeniu mediere, domeniu1, criteriu1, [domeniu2, criteriu2], ...)

Dac prin selecie nu avem celule de mediat sau avem doar text atunci rezultatul este: #DIV/0!

Domeniile trebuie s aib aceeai dimensiune.

IF(test logic, valoare pt. test adevarat, valoare pt. test fals)

Exemplu: B C

1 Nota Evaluare

2 9 Admis

3 5 Admis

4 3 Respins

5 8 Admis

6 5 Admis

7 6 Admis

8 9 Admis

9 7 Admis

10 10 Admis

11 4 Respins

12 5 Admis

13 8 Admis

=IF(B4>=5,"Admis", "Respins")

20

Imbricare comanda IF poate conine ca argument un alt IF, astfel condiiile logice pot prezenta forme cu un grad ridicat de complexitate.

Exemplu

A B C D

1 calificative Nota Evaluare Evaluare calificativ

2 9-10 F.Bine 9 Admis F. Bine

3 7-8 Bine 5 Admis Suficient

4 5-6 Suficient 3 Respins Insuficient

5 < 5 Insuficient 8 Admis Bine

6 5 Admis Suficient

7 6 Admis Suficient

8 9 Admis F. Bine

9 7 Admis Bine

10 10 Admis F. Bine

11 4 Respins Insuficient

12 5 Admis Suficient

13 8 Admis Bine

=IF(B6>=9,"F. Bine", IF(B6>=7, "Bine",IF(B6>=5, "Suficient", "Insuficient")))

21

2 B C D

3

GFR-level (Clearance-ul de Creatinina )

normal 90 ml/min/1.73m2

4 =60, "St1", IF(C9>=30, "St2", IF(C9>15,"St3","IRT"))))

22

GFR=Glomerular filtration rate

RAND( ) genereaz aleator un numr real cuprins n domeniul [0,1). Distribuia este de tip uniform, constant pentru fiecare valoare. RANDBETWEEN(min, max) genereaz aleator un numr ntreg ntre limitele prezente, inclusiv limitele.

0 1

a b

RAND()*(b-a)+a

Note

9.61

9.82

7.66

4.71

7.51

7.16

5.59

5.60

8.07

5.32

9.38

8.70

9.72

=RAND()*6+4

Exemplu: valori ntre 4 i 10.

Edit+Copy

Edit+Paste Special + Value !!!

OBSERVAIE RANDBETWEEN poate fi folosit pentru generare de numere nentregi:

Generare de note cu 2 zecimale ntre 4.00 i 10.00: RANDBETWEEN(400,1000)/100

23

EXEMPLE PRACTICE !

Vom genera date prin metoda aleatorie pentru definirea temperaturii:

Considerm domeniul de lucru: 36.4 38.2

Calculm amplitudinea: A=38.2-36.4=1.8 Formula devine: RAND()*1.8+36.4

pacient1 36.4291547

pacient2 37.592497

pacient3 37.269398

pacient4 36.8512736

pacient5 37.1714804

pacient6 36.5275049

pacient7 38.1098732

pacient8 37.5145279

pacient9 36.7973714

pacient10 37.292335

pacient11 36.5412739

pacient12 37.4885573

pacient13 37.2552864

pacient14 37.1945728 24

EXEMPLE PRACTICE !

Putem folosi generatorul de numere aleatoare pentru a extrage la

ntmplare date dintr-o populaie. METOD: 1 Se genereaz valori aleatoare pentru fiecare rnd din tabelul surs. 2 Se ordoneaz tabelul dup noua coloan creat (astfel se imprim efectul aleatoriu asupra datelor din tabel).

3 Se extrag primele n valori necesare studiului (din totalul de N).

Nume Aleator

pacient1 0.25014443

pacient2 0.705064835

pacient3 0.984345042

pacient4 0.432758962

pacient5 0.214328291

pacient6 0.682210677

pacient7 0.198716129

pacient8 0.641963973

pacient9 0.102331009

pacient10 0.270144243

pacient11 0.70620265

pacient12 0.840126176

pacient13 0.217788009

pacient14 0.206960013

Nume Aleator

pacient9 0.102331009

pacient7 0.198716129

pacient14 0.206960013

pacient5 0.214328291

pacient13 0.217788009

pacient1 0.25014443

pacient10 0.270144243

pacient4 0.432758962

pacient8 0.641963973

pacient6 0.682210677

pacient2 0.705064835

pacient11 0.70620265

pacient12 0.840126176

pacient3 0.984345042

Tabel final ordonat

dup coloana Aleator

25