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