Post on 27-Jun-2015
Cursul BTI – Ms EXCEL 2003 - continuare
Cuprins
1. Tipuri de funcţii – I (continuare) – s2
2. Liste – s3
3. Sortare – s4
4. Filtrare – s5
5. Tipuri de funcţii – II – s7
6. Formatare condiţionată – s9
7. Creare şi formatare diagrame – s10
8. Desenare şi introducerea elementelor de grafică – s16
9. Însumarea datelor într-o listă - Subtotal – s20
10. Analiza datelor - Pivotare – s24
11. Referat - s26
2
1. Tipuri de funcţii – I (continuare)Funcţii logice
verifică dacă o condiţie simplă sau multiplă este adevărată sau falsă
Funcţia Returnează
And(logical1, logical2,...)verifică valorile logice şi returnează adevăr dacă toate argumentele sunt adevărate, altfel returnează fals
Or(logical1,logical2,...)verifică valorile logice şi returnează fals dacă toate argumentele sunt false, altfel returnează adevăr
Not(logical) adevăr pentru fals şi invers
True() adevăr
False() fals
If(logical_test,value_if_true,value_if_false) dacă logical_test este adevărat atunci funcţia returnează valoarea value_if_true altfel value_if_false; Obs: dacă pe value_if_false se testează cu funcţia If atunci vom vorbi despre If imbricat.
3
2. Liste
• Lista– modalitate de a stoca date într-o foaie de calcul– serie etichetată de rânduri care conţin date similare
• Caracteristici comune:– primul rând conţine etichete de coloană;– celulele de pe aceeaşi coloană conţin date similare;– rândurile dintr-o coloană conţin seturi similare de date.
• Utilizarea listei ca bază de date:– rândurile sunt înregistrări;– coloanele sunt câmpuri;– Excel recunoaşte automat lista ca bază de date, executând
operaţii specifice: regăsire, sortare sau subtotalizarea datelor
• Întreţinerea listei folosind machete de date• Liste particularizate: Tools - Options... - Custom Lists
4
3. Sortare• Sortare = organizează datele dintr-o listă în ordine
alfabetică, numerică sau cronologică• Tipuri de sortare:
– Data – Sort: simplă, după mai multe criterii– Butoanele de sortare: rapidă– Ascendentă, descendentă
– Sortarea se anulează cu UNDO
5
4. Filtrare• Filtrare = extrage dintr-o listă un subset de date care
îndeplinesc un anumit criteriu. • Filtrarea nu strică lista (cum face sortarea) ci doar
ascunde înregistrările care nu îndeplinesc criteriul solicitat.
• Tipuri de filtrări:– Filtrare automată: Data – Filter – Autofilter
apar la fiecare etichetă de coloană o listă ascunsă de unde se poate alege criteriul după care se face filtrarea
criteriul poate fi simplu sau particularizat (Custom) se anulează din Data – Filter – Autofilter
6
4. Filtrare– Filtarea avansată: Data – Filter – Advanced
Filter lista filtrară poate să apară peste sau în alt loc faţă de
lista iniţială (Copy to another location) zonă de criterii cu elemente de legătură etichetele de
coloană ale listei iniţiale, în zona de criterii pot exista una sau mai multe coloane în funţie de tipul filtrării. Dacă există mai multe coloane atunci filtarea poate fi de tip OR (criteriile sunt introduse pe rânduri diferite) sau AND (criteriile sunt introduse pe acelaşi rând)
criteriile pot fi de comparare(“>1000”, “=text”) sau calculate
7
5. Tipuri de funcţii - IIFuncţii de căutare
• Lista poate fi utilizată şi ca un tabel de căutare.• Elementul căutat în listă va fi valoarea de căutare.• Pentru a căuta în listă, ea trebuie să fie sortată
după valoarea de căutare şi trebuie să conţină pe prima linie valoarea de căutare.
• Funcţiile care realizează căutări sunt din categoria Lookup & Reference.
• Vom studia căutarea pe verticală sau pe coloană cu ajutorul funcţiei VLOOKUP.
• Sintaxa funcţiei VLOOKUP este următoarea:– VLOOKUP(valoare de căutare, tabel, indice coloană,
valoare de adevăr),• valoare de căutare – valoare pe are o căutam• tabel – tabelul în care se face căutarea• indice coloană - numărul coloanei care conţine datele de
returnat• valoare de adevăr - implicit False
8
5. Tipuri de funcţii - IIFuncţii de căutare
• Căutarea poate fi exactă (de tip text) sau aproximativă (în interval)
• Dacă căutarea este exactă atunci valoare de adevăr este TRUE
• Dacă căutarea este în interval atunci valoare de adevăr este FALSE
9
6. Formatare condiţionată• permite monitorizarea celulelor care îndeplinesc
anumite criterii• Format – Conditional Formatting...
– selectare celulelor de formatat– lansarea ferestrei de fomatare condiţionată– Condition 1 – condiţia de îndepinit
• Cell value is: valori din celule selectate • Formula is: se indică formula
– Format... - formatarea propriu-zisă– Add – adaugare condiţii– Delete –ştergere condiţii
• copierea formatării: Format Painter
10
7. Creare şi formatare diagrame• Diagrama = reprezentare grafică a datelor într-o fc• Punctele de date (val celulelor) sunt afişate în:
– Diagrame de bare (forma implictă a expertului diagramei)– Diagramă de linii– Diagramă de coloane– Diagrame sectoare de cerc, etc.
• Punctele de date sunt grupate în serii de date(diferenţiate prin culori sau modele)
• Tipuri de diagrame:– Diagramă încorporată = obiect într-o foaie de calcul– Foaie diagramă
• Crearea unei diagrame cu ajutorul expertului diagramă:– Pas 1: stabiliţi fc activă, selectaţi zona de date de
reprezentat grafic / o celulă din cadrul zonei de date de reprezentat
– Pas 2: INSERT – CHART..., buton expert diagramă – Pas 3: stabilire tip grafic – implicit de tip coloană, Next
11
7. Creare şi formatare diagrame
• Crearea unei diagrame cu ajutorul expertului diagramă:– Pas 4: stabilire sursă de date, Next– Pas 5: configurarea componentelor graficului:
• Tiltul• Axe• Linii de grilă• Legendă• Etichete de date• Tabel de date, Next
– Pas 6: stabilirea locului de amplasare a diagramei:• Încorporată în fc activă• Foaie de diagramă ca fc nouă, Next
– Pas 7: Finish
• Mutare şi dimensionare diagramă încorporată: glisare• Ştergere diagramă încorporată: Delete
12
7. Creare şi formatare diagrame• Tipuri de diagrame:
Realizari firma
0200400600800
100012001400160018002000
1996 1997 1998 1999
ani
valo
ri
Productie
Servicii
Comert
Diagramă tip coloană Diagramă tip bară
Diagramă tip suprafaţă planăDiagramă tip linie
Realizari firma
0 500 1000 1500 2000
1996
1997
1998
1999
ani
valo
ri
Comert
Servicii
Productie
Realizari firma
0
500
1000
1500
2000
2500
3000
1996 1997 1998 1999
ani
valo
ri
Comert
Servicii
Productie
Realizari firma
0
500
1000
1500
2000
2500
3000
1996 1997 1998 1999
ani
valo
riComert
Servicii
Productie
13
7. Creare şi formatare diagrame• Tipuri de diagrame:
Diagramă tip structură radială Diagramă tip structură inelară
Diagramă tip bară 3-DDiagramă tip suprafaţă plană 3-D
Realizari firma
13%
22%
26%
39%
17%
22%
28%
33%
17%
26%
26%
31%
1999
1998
1997
1996
Realizari firma
55%
27%
18%
Productie
Servicii
Comert
19961997
19981999
600
300200
1000
400300
1200
500
300
1800
600
350
0
500
1000
1500
2000
2500
3000
valo
ri
ani
Realizari firma
Comert
Servicii
Productie
0 500 1000 1500 2000
1996
1997
1998
1999
Realizari firma
Comert
Servicii
Productie
14
7. Creare şi formatare diagrame• Tipuri de diagrame:
Diagramă tip colonă 3-D Diagramă tip linie 3-D
Diagramă de structură 3-D
0
200400
600800
1000
120014001600
1800
1996 1997 1998 1999
Realizari firma
Productie
Servicii
Comert
0500
1000
15002000
1996 1997 1998 1999
Productie
Servicii
Comert
Realizari firma
Productie
Servicii
Comert
Realizari firma
Productie, 13%
Productie, 22%
Productie, 26%
Productie, 39% 1996
1997
1998
1999
15
7. Creare şi formatare diagrame
• Modificarea diagramei: meniul contextual al ferestrei de diagramă, Chart Options ...– Serii de date– Categorii– Nume categorii– Legenda– Axa– Marcatorii de date– Titluri– Linii degrilă– Eticheta de date
• Stabilirea formatului diagramei: meniul contextual al elementului aferent graficului, Format ...
16
8. Desenare şi introducerea elementelor de grafică
• Obiectele grafice nu fac parte efectiv din fc sau din diagramă, fiind inserate pe un start transparent intins pe fc sau pe diagramă şi au formatări independente de fc sau diagramă.
• Crearea şi ştergerea obiectelor desenate– creare: bara de desenare DRAWING
– ştergere: selecţie obiect şi apoi acţionaţi DELETE
Pentru a desena: Faceţi clic pe acest buton:
O linie dreaptă
Un pătrat sau un dreptunghi
Un cerc sau o elipsă
O săgeată
O formă automată
17
8. Desenare şi introducerea elementelor de grafică
• Forme automate: AutoShapes de pe bara DRAWING– curbe şi forme libere
mâzgăleală formă libere curbă
• Formatarea obiectelor desenate– culoare de umplere, culoare linie– stil linie, stil linie întreruptă, stil săgeată
• Redimensionarea obiectelor desenate: glisare pe ghidajul obiectului
• Adăugarea umbrei unui obiect desenat: buton Shadow• Aplicarea efectului 3-D obiectelor desenate: butonul 3-D• Gruparea şi degruparea obiectelor desenate: selectare
obiecte şi acţionare comandă Group / Ungroup din meniul Draw a barei de desenare DRAWING
• Rotirea obiectelor desenate: selectarea obiectului desenat şi aplicarea comenzii de rotire (liberă, 90° la st sau dr)
18
8. Desenare şi introducerea elementelor de grafică
• Rotirea obiectelor desenate:– liberă– cu 90° la stânga– cu 90° la drepta
• Răsturnarea obiectelor desenate: selectarea obiectului desenat, comanda Flip Horizontal / Vertical din meniul Draw al barei de desenare DRAWING
• Mutarea şi poziţionarea obiectelor desenate:– selectare obiect desenat
glisare la noua locaţie deplasare fină: comanda Nudge a meniului Draw de pe bara DRAWING
• Adăugarea unui text sau a unui efect de text– Adăugarea unui text: Casetă de text (Text Box) din DRAWING– Efect de text: WordArt din bara de desenare DRAWING
• Lucrul cu imagini şi miniaturi• imagini: INSERT – Picture – From File, buton • miniaturi: INSERT – Picture –ClipArt, buton
19
8. Desenare şi introducerea elementelor de grafică
• Editarea unei imagini: bara de instrumente Picture
– Inserare imagine– Control imagine– Butoane control constrastului/luminozitate– Trunchiere– Rotire imagine– Stil linie– Comprimare /Recolorare / Formatare imagine– Stabilire culoare transparentă– Reiniţializare imagine
20
9. Însumarea datelor într-o listă - Subtotal
• Însumarea datelor într-o listă:1. Subtotaluri = cale rapidă de a însuma datele dintr-o
listă2. Totaluri complexe = tabel pivot – tabel creat
interactiv din listă
• Subtotaluri automateUtilizând grupuri de date alese de dvs., Excel:
– calculează automat valorile pentru subtotaluri sau totaluri generale
– inserează rânduri de subtotal– etichetează rândurile de subtotal– elaborează planul listei (ierarhie)
Cerinţe pentru lucrul cu subtotaluri:– lista trebuie să conţină coloane etichetate– lista trebuie sortată după coloanele pentru care se
doresc subtotalurile
21
9. Însumarea datelor într-o listă - Subtotal
• Paşii pentru a crea subtotaluri într-o listă:a. sortare listă după coloană pentru care se
calculează subtotalulb. vă poziţionaţi pe o singură celulă din listăc. Data - Subtotal
At each change inconţine coloana de subtotal
Use functionfuncţie agregat
Add subtotal tocutiile selectate pt col ce conţin val ptcare se doresc subtotaluri
Replace current subtotals:înlocuire subtotaluri curente
Summary below data:însumare sub date
Remove All:Eliminare subtotaluri
22
9. Însumarea datelor într-o listă - Subtotal
• Subtotaluri imbricate (subtotal în subtotal)– sortare listă după câmpurile dorite– aplicarea primului subtotal– revenire pe o celulă din listă– Data –Subtotal– At each change in selectaţi următoarea coloană pentru care
doriţi subtotal– goliţi cutia Replace current subtotals şi apoi OK
• Funcţii agregat
Utilizaţi funcţia: Pentru a rezuma:
Sum() Suma valorilor dintr-o listă
Count() Numărul elementelor dintr-o listă
Average() Media aritmedică a valorilor dintr-o listă
Max()/Min() Cea mai mare/mică valoare dintr-o listă
Product() Rezultatul înmulţirii tuturor valorilor dintr-o listă
23
9. Însumarea datelor într-o listă - Subtotal
• Ascunderea şi afişarea datelor de detaliu într-o listă cu subtotaluri
• Creare de totaluri complexe: SumIf ,Countif
Pentru a vedea: Faceţi clic pe:
Datele detaliu dintr-un grup Simbolul arată detaliu
Un anumit nivel într-un plan Simbolul nivel rând sau coloană
Toate detaliile dintr-un plan Simbolul pentru cel mai de jos rând şi coloană
Pentru a ascunde: Faceţi clic pe:
Datele detaliu din cadrul unui grup Bara de nivel a rândului
Faceţi clic oriunde în bara de nivel sau pe simbolul ascunde detaliu
Un anumit nivel dintr-un plan Simbolul nivel sau coloană precedent
Toate detaliile dintr-un plan Simbolul primului nivel
24
10. Analiza datelor - Pivotare
• Tabel pivot:– o structură totalizatoare interactivă, construită într-o fc Excel– operează rapid cantităţi mari de date dintr-o listă sau BD, utilizând formatul
şi metodele de calcul alese de utiliziator– instrument foarte util când volumul datelor ce trebuie sintetizate este mare
şi greu de prelucrat manual– manipulează date din liste şi tabele: reanjarea, filtrarea, rezumarea şi
analizarea interactivă a datelor– e construit pe baza unor surse de date– operează următoarele entităţi:
câmp: categorie de date care derivă dintr-un câmp al listei sursă articol: subcategorie dintr-un câmp al tabelului pivot buton de câmp: buton din tabelul pivot care afişează numele câmpului
din lista sursă zona paginii: câmpuri cu care se va construi un criteruiu de filtrare zona rândurilor: câmpuri ale căror articole vor deveni etichete pt rânduri zona coloanelor: câmpuri ale căror articole vor deveni etichete pt
coloane zona datelor: câmpuri pentru care se calculează subtotalul
25
10. Analiza datelor - Pivotare
• Crerarea tabelului pivot:– poziţionaţi pe o celulă a listei alegeţi: Data – PivotTabel and
PivotChartReport...– În programul expert PivotTable Wizard se vor completa 3 paşi:
1. precizare sursă date2. domeniul de date3. alegere aspect tabel
26
11. ReferatReferatul trebuie sa conţină:
- Textul problemei si explicarea modului de rezolvare a acesteia, explicitând elementele utilizate (pentru un aspect mai plăcut se poate redacta in MS WORD, si prelua capturi din aplicaţia rezolvată in Ms EXCEL)
- Rezolvarea problemei se va efectua în Ms EXCEL, registru de lucru ce va conţine:- Foaie de calcul cu tabelul sursă ce va conţine: funcţii, formule,
comenzi studiate- Foaie de calcul cu tabelul sortat- Foaie de calcul cu tabelul filtrat
Elementele obligatorii in EXCEL sunt:1. Formule2. Funcţii statistice (max, min, average,count/counta/countblank, countif)3. Funcţii logice (if simplu, if imbricat, if cu and/if cu or)4. Funcţia de căutare vlookup (căutare exacta şi căutare în interval)5. Sortare6. Filtrare automată
Elemente opţionale: subtotal, pivotare