1

ComunicareValentin Clocotici

2

Cursul nr. 9Excel: Analiza datelor

3

Proceduri de analiză a datelor• Marea putere a Excel-ului este în procedurile de

analiză a datelor: componente pentru gestiona-rea datelor, analiza statistică, consolidare, reprezentări grafice etc.

• Datorită componentei de programare, posibilită-ţile de analiză a datelor sunt practic nelimitate, o serie de companii dezvoltatoare de software fiind axate pe crearea de noi componente.

• Vom discuta în continuare procedurile de analiză primară a datelor (fără modulul dedicat statisticii, adecvat pentru un curs specializat).

4

Liste• Prin listă (Excel) se înţelege un domeniu

dreptunghiular de celule dintr-o foaie de calcul, domeniu limitat de marginile foii sau de celule vide.

• Este recomandat ca o listă să fie organizată ca o tabelă dintr-o bază de date: fiecare coloană din listă cuprinde date cu aceeaşi semnificaţie (o coloană este similară unui câmp al tabelei), fiecare linie cuprinde date care se referă la o aceeaşi entitate (o linie este similară unei observaţii dintr-o tabelă a bazei de date).

5

Liste

• Prima linie a unei liste este identificată ca linia de anteturi. Se recomandă ca prima linie să conţină denumirile coloanelor.

• Microsoft Excel dispune de proceduri prin care:– informaţia să fie localizată;– lista să fie sortată;– să se realizeze calcule de tip totaluri/subtotaluri;– lista să fie gestionată prin modificare, adăugare de

înregistrări, eliminare de înregistrări.

6

Liste• Pentru ca o zonă să fie recunoscută drept listă:

– liniile şi coloanele trebuie să fie adiacente (nu trebuie să existe linii sau coloane vide);

– domeniul listei trebuie să fie înconjurat de linii şi coloane vide sau să fie limitat de marginile foii.

• Se recomandă ca:– linia de antet să conţină denumirile coloanelor şi să fie

formatată distinct de restul liniilor;– pe o foaie de calcul să existe o singură listă;– alte informaţii să fie scrise deasupra sau sub listă (nu

în stânga sau dreapta listei).

7

Sortarea unei liste• Pentru a ordona (sorta) o listă:

– se selectează lista prin poziţionarea celulei active în listă; selectarea parţială prin glisarea mouse-ului (sau altfel) poate conduce la ordonări doar pentru selecţie, nu pentru toată lista,

– se dă comanda Data – Sort,– se aleg opţiunile necesare

• ordonare crescătoare sau nu, • dacă există sau nu linie de antet, • ordinea de sortare pentru prima cheie în dialogul Sort Options

(modul de a utiliza o ordine definită de o listă utilizator prin Tools – Custom Lists)

• dacă se sortează linii sau coloane.

8

Dialogurile Sort, Sort Options

9

Alte operaţiuni

• Serii de termeni – Utilizatorul poate să şi definească liste (seturi

ordonate de termeni) proprii prin intermediul fişei Custom Lists din dialogul Options (meniul Tools).

10

Alte operaţiuni• Serii de termeni

– În lista din stânga sunt afişate toate listele definite (acestea sunt păstrate în mediul Excel, deci sunt disponibile şi în utilizările ulterioare).

– Pentru definirea unei liste proprii • se selectează intrarea NEW LIST după care • se trec termenii noii liste în zona rezervată List entries. Trecerea de

la un termen la altul se realizează prin ENTER. • La terminarea listei se acţionează butonul Add şi lista definită apare

în zona Custom lists din partea dreaptă. • Eliminarea unei liste se realizează prin selectarea în Custom lists şi

acţionarea butonului Delete.• Având la bază o listă proprie, utilizatorul poate genera o

serie de termeni (prin completarea automată) sau poate realiza o sortare după ordinea dată de listă.

11

Formulare de date

• Prin selectarea unei liste (celula activă în listă) şi comanda Data – Form, se afişează un formular de date creat dinamic pentru lista respectivă.

• Prin intermediul comenzilor din formular– se pot adăuga/modifica/elimina înregistrări,– se poate defini o condiţie compusă şi naviga

doar între înregistrările care îndeplinesc condiţia (căutare după model).

12

Formulare de date

13

Formulare de date• Acţionarea butonului

Criteria transformă formu-larul într-un instrument de definire de condiţii şi de navigare între înregistrări.

• Condiţiile care se referă la câmpuri diferite sunt legate prin operatorul logic “şi”.

• Criteriile de comparare pot fi şiruri de caractere sau expresii.

14

Filtrare

• Prin filtrarea unei liste se înţelege procesul de selectare a înregistrărilor care îndepli-nesc criterii fixate. Filtrarea este similară activării unui filtru pentru o bază de date.

• Condiţiile de filtrare se pot defini– implicit = prin analiza listei, mediul Excel

propune criterii Data – Filter - AutoFilter– explicit utilizatorul scrie condiţii

• asistat de Excel Data – Filter – AutoFilter• fără asistenţă Data - Filter - Advanced Filter

15

Filtrare• Rezultatul filtrării poate fi

– “pe loc” = se ascund liniile care corespund înregistrărilor care nu îndeplinesc criteriile de filtrare; din această cauză nu se scriu, de regulă, date importante în paralel cu lista. Acest tip de filtrare se poate realiza indiferent de comanda utilizată.

– “în altă zonă” = informaţiile filtrate sunt scrise (copiate) într-o zonă precizată de utilizator; în acest mod se pot obţine şi liste parţiale care să conţină mai puţine coloane decât lista nefiltrată; acest tip de filtrare se poate efectua doar prin Data - Filter - Advanced Filter.

16

Filtrare automată• Se selectează lista şi Data – Filter – AutoFilter.• Fiecare antet de listă devine activ prin ataşarea

unui simbol de control drop-down.• Prin acţionarea unui control se poate impune o

condiţie câmpului respectiv:– alegerea unei valori = liniile care au acea valoare, – (All) = anularea filtrării pe coloana respectivă, – (Top 10…) = selectarea primelor înregistrări în

ordinea aleasă, – (Custom) = fixarea unei condiţii utilizând un dialog

special.

17

Filtrare automată

• Condiţiile de la câmpuri diferite sunt legate între ele prin operatorul logic “şi”.

• Pentru a vizualiza toate înregistrările: – se alege la fiecare coloană filtrată valoarea

(All), sau – se dă comanda Data – Filter – Show All.

• Pentru a anula filtrarea se dă din nou comanda Data – Filter – AutoFilter.

18

Filtrare avansată• Prin filtrarea avansată este

posibil ca înregistră-rile selectate să fie copiate (parţial) în altă locaţie a foii de calcul. Alt avantaj este acela că utiliza-torul are control complet asupra condiţiilor de filtrare.

• Etapele urmate sunt:– definirea criteriilor de filtrare,– definirea, dacă se doreşte, a zonei

destinaţie,– selectarea listei (celula activă în

listă), – comanda Data – Filter – Advanced

Filter.

19

Filtrare avansată• Condiţiile de filtrare se aranjează într-o listă având ca

linie de antet denumirile coloanelor (câmpurilor), din lista care se filtrează, pentru care se definesc condiţiile. – Pentru o condiţie creată ca rezultat al unei formule nu trebuie să

existe, totuşi, denumire.• Toate condiţiile scrise pe aceeaşi coloană se referă la

coloana cu denumirea din antet.• Condiţii care se scriu pe aceeaşi linie trebuie să fie

îndeplinite simultan (operatorul logic "şi").• Condiţiile care se scriu pe linii diferite se consideră

legate prin operatorul logic "sau" (deci sunt selectate înregistrările care îndeplinesc cel puţin o linie de condiţie din criterii).

20

Filtrare avansată• Exemplul 1. Trei sau mai multe condiţii pentru o coloană

NumeBeethovenBerliozBach

– Sunt selectate înregistrările care au în coloana Nume una dintre valorile Beethoven, Berlioz, Bach. Este de observat că două valori se pot indica şi în Auto Filter.

• Exemplul 2. Condiţii simultane pentru mai multe coloaneNume Simfonii AnulBeethoven >5 <1900

– Se obţin înregistrările care conţin la Nume valoarea Beethoven, care la Simfonii au o valoare mai mare decât 5 şi care are Anul mai mic de 1900. Se observă că între condiţii există cuvântul "şi", semnificând îndeplinirea simultană a condiţiilor.

21

Filtrare avansată• Exemplul 3. Condiţii alternative pentru mai multe coloane

Nume SimfoniiAnulBeethoven

>5<1900

– Sunt selectate înregistrările care au la Nume valoarea Beethoven, cele care au la Simfonii o valoare mai mare decât 5 şi cele care au Anul mai mic decât 1900.

• Exemplul 4. Condiţii create ca rezultat al unei formule(lipseşte numele de câmp)=Anul>1900

– Se pot utiliza denumirile coloanelor din listă, se remarcă lipsa denumirii în antetul coloanei din zona de criterii. Rezultatul filtrării este corect chiar dacă în foaia de calcul apare o eroare (#NAME?) la scrierea condiţiei deoarece anumite denumiri nu sunt definite prin Insert Names.

22

Filtrare avansată

• Zona destinaţie se organizează ca o listă având ca linie de antet denumirile coloa-nelor care se copie.

• Este indicat ca denumirile să fie aduse prin Copy – Paste (pentru a fi identice celor din lista de bază). Acest procedeu este recomandat şi pentru crearea zonei de criterii de filtrare.

23

Totaluri parţiale• In analiza datelor sunt frecvente situaţiile în care

este necesar să se calculeze anumite funcţii pentru fiecare categorie de înregistrări ale unei liste:– numărul de clienţi din fiecare zonă geografică,– valoarea totală a mărfurilor din stoc, pe categorii etc.

• O asemenea totalizare este posibilă doar dacă în listă există un câmp care permite identificarea automată a categoriilor (zona, tip de marfă etc.).

• Funcţii totalizatoare: suma, produsul, minim, maxim, media aritmetică etc.

24

Totaluri parţiale• Pentru a realiza includerea automată a

subtotalurilor în listă:– se ordonează lista după câmpul de clasificare, încât

toate înregistrările care se referă la o aceeaşi categorie să fie situate grupat; pentru fiecare grup de înregistrări se va genera o linie de total.

– se selectează lista (celula activă în listă),– se dă comanda Data – Subtotals.

• Subtotalurile sunt inserate ca formule, deci modificarea datelor produce modificarea subtotalului corespunzător.

25

Totaluri parţialeDialogul Subtotal

Campul de clasificare

Functia totalizatoare

Campurile totalizate

Parametrii procedurii

26

Totaluri parţiale• Rezultatul este o listă cu linii inserate de subtotaluri. • Se remarcă dotarea listei cu simbolurile de outline prin

care se poate afişa informaţia la generalitatea dorită.

27

Totaluri parţiale• Dacă lista este sortată după mai multe chei, se

pot adăuga subtotaluri pentru mai multe coloane de clasificare prin repetarea procedurii cu anula-rea casetei de control Replace current subtotals.

• Controlul Page break between groups produce tipărirea fiecărui grup pe o pagină separată.

• Anularea operaţiunii se realizează acţionând butonul Remove All – liniile totalizatoare inserate sunt eliminate automat şi lista revine la starea iniţială.

28

Scenarii• Pentru situaţiile în care există mai multe alterna-

tive, caracterizate de valori ale unor parametri (dobânzi, ritmuri de creştere etc.) Microsoft Excel dispune de un mecanism adecvat.

• Numim scenariu (scenario) un set de valori date unui set de parametri. Cu alte cuvinte, un set de valori trecute într-un grup de celule (considerate parametrii foii de calcul).

• Microsoft Excel gestionează scenariile prin aceea că le poate crea/ adăuga/ modifica/ elimina/ raporta.

29

Scenarii• Fiecare scenariu este caracterizat, pe lângă

setul de valori date parametrilor, de numele său (se recomandă denumiri cât mai explicite).

• Comanda principală este Tools – Scenarios. Se va deschide dialogul Scenarios Manager din care prin:– Add = se deschide dialogul de definire a unui nou

scenariu,– Delete, Edit = eliminarea, modificarea scenariului

selectat,– Merge = importul unor scenarii definite în alte caiete.

30

Scenarii

• Prin comanda Show din dialogul Scenarios Manager se activează scenariul selectat: – valorile din scenariu se scriu automat în celulele

parametri– se recalculează foaia de calcul pentru noile valori.

• Prin comanda Summary din dialogul Scenarios Manager se deschide un dialog în care se precizează celulele importante (de sinteză) ale căror valori să fie listate într-un raport al scenariilor. Raportul se obţine pe o foaie nouă.

31

Scenarii

32

Goal Seek

• Procedura Goal Seek (atingerea unui obiectiv) este utilă atunci când se doreşte aflarea unei valori necunoscute (de intrare) care determină un rezultat fixat (de ieşire).

• Formalizând, problema revine la rezolvarea ecuaţiei f(x) = c, c fixat.

• Prin urmare, în Excel, există o celulă care conţine valoarea lui x şi o celulă care conţine formula de calcul pentru f(x).

33

Goal Seek• Observaţia foarte importantă este aceea că

formula se poate referi la x în mod direct sau indirect, funcţia astfel construită putând fi de orice formă (polinomială, exponenţială, liniară sau neliniară etc.).

• Rezolvarea ecuaţiei se realizează prin algoritmi de optimizare, iterativi, astfel încât soluţia obţinută este dependentă de parametrii fixaţi prin Tools – Options – Calculation.

• Procedura se iniţiază prin Tools – Goal Seek.

34

Goal Seek

Valoarea de atins

35

Goal Seek• Ca rezultat se afişează un dialog în care se

precizează dacă s-a obţinut sau nu o soluţie.• Soluţia este cu precizia rezultată ca urmare a

parametrilor fixaţi în Tools – Options – Calculation.

• Soluţia este inserată în celula x ca o constantă şi se recalculează foaia.

36

Tabele• Tabelele Excel (data tables) fac parte din

uneltele a ceea ce se numeşte what-if analysis, împreună cu scenariile, goal seek şi solver.

• Deşi, formal, reprezintă tabelarea unei funcţii de o variabilă sau de două variabile, un tabel trebuie privit ca vizualizarea modului cum se modifică valorile unei funcţii (formule) atunci când se modifică valorile variabilelor.

• Posibilitatea de comparare rapidă a rezultatelor este avantajul utilizării tabelelor.

37

Tabele• Un tabel de date nu este pur şi simplu o

structură de celule, ci un domeniu de celule calculate după o aceeaşi formulă, identificate ca o entitate şi care se bucură de o anumită protecţie (de exemplu nu se poate modifica un tabel prin editare directă într-o celulă calculată).

• Deoarece se consideră că un tabel conţine rezultate care se modifică mai rar, deci nu necesită recalculări frecvente, se poate selecta în Tools – Options – Calculation opţiunea ca recalcularea să fie cu exceptarea tabelelor.

38

Tabele• Există două tipuri de tabele:

– cu o intrare (cu o variabilă),– cu două intrări (cu două variabile).

• Indiferent de tip, pentru crearea unui tabel trebuie să se distingă între celule de intrare (input cells) şi valori de intrare (input values):– celulă de intrare = celulă referită în formula de calcul; – valoare de intrare = valoare care se înlocuieşte

(ipotetic) în celula de intrare corespunzătoare pentru a produce o valoare a funcţiei; înlocuirea este efectuată de mediul Excel la crearea tabelului.

39

Tabele cu o intrare• Pentru a crea un tabel cu o intrare

– se va fixa mai întâi dacă tabelul este pe linie sau pe coloană,

– se scriu datele de intrare (pe linie sau pe coloană),– se scriu formulele (un astfel de tabel poate tabela mai

multe formule) pe coloană, respectiv linie (opus tipului de tabel) astfel încât să formeze cu datele de intrare un unghi drept având ca vârf o celulă vidă,

– se selectează întregul domeniu şi se dă comanda Data – Table,

– se dă referinţa la celula de intrare potrivit tipului de tabel (pe linie sau pe coloană).

40

Tabele cu o intrare

Datele de intrare

Celula de intrare

Formulele

41

Tabele cu două intrări

• Pentru a crea un tabel cu două intrări:– se scrie formula de calcul, care se va referi la două

celule de intrare (una pentru linie, alta pentru coloană),

– se scriu datele de intrare pe linie şi pe coloană astfel încât să formeze un unghi drept având ca vârf celula cu formula de calcul,

– se selectează întregul domeniu şi se dă comanda Data – Table,

– în dialogul afişat se dau referinţele la celulele de intrare.

42

Tabele cu două intrări

Celule de intrare

Valori de intrare

coloana

Valori de intrare linie

Formula