Excel C3 Referire,Functii, If, Aleator
-
Upload
cush-gisca -
Category
Documents
-
view
70 -
download
7
description
Transcript of Excel C3 Referire,Functii, If, Aleator
-
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