Prezentarea datelor in excel

PREZENTAREA DATELOR IN EXCEL

Pregatirea unei foi de calcul in vederea prezentarii acesteia altor persoane, fie pe ecran, fie pe hirtie, este, in mare masura, o problema de formatare. Exista doua tipuri principale de formatare:

-formatarea numerelor, determina modul in care valorile sunt interpretate.

-formatul aspectului, afecteza atributele grafice ale datelor si altor obiecte din pagina, cum ar fi fonturile si culoarea.

Instructiuni de utilizare a instrumentului autoformat

In vederea aplicarii facilitatii AutoFormat:

-selectam acea portiune a foii pe care dorim sa o formatam

-selectam FORMAT AUTOFORMAT din bara de meniuri. Va aparea caseta de dialog AUTOFORMAT

-din lista Table Format, selectam numele unui format predefinit. O imagine de previzualizare a acestui format va aparea in caseta Sample.

-selectam O.K. EXCEL va aplica portiuni selectate a foii un set prestabilit de formate pentru aspect

Sugestii in vederea utilizarii facilitatii Auto format

Remarcam ca rezultatele utilizarii facilitatii AUTOFORMAT nu sunt tocmai perfecte. Titlurile ar trebui sa fie mai mari , iar subtitlul (situat in cel de al doilea rind) ar trebui sa fie mai mic decit titlul principal (din primul rind).Un mod grafic imbunatatit se poate obtine prin editarea titlurilor Disposition si Problem cu un al doilea tip de font, distinct de primul, deoarece tabelul se refera, in primul rind, la aceste doua lucruri.

Cauza acestor imperfectiuni este aceea ca formatele predefinite sunt destinate unei foi 'tipice', pe cind aceasta este ceva mai neobisnuita. Latimile coloanelor nu sunt uniforme si aceasta influenteaza centrarea titlurile. De asemenea, dispunerea capetelor de coloana difera de cea din formatul prestabilit. Cu toate acestea, puteti obtine o multime de lucruri cu acea singura comanda AUTOFORMAT.

Selectarea optiunilor pentru autoformat

Vom fi in masura sa imbunatatim aspectul unei anumite foi, repetind pur si simplu comanda FORMAT AUTOFORMAT si selectind un alt format predefinit.

Putem reseta unele dintre optiunile AUTOFORMAT:

-selectam un tabel care trebuie formatat

-selectam FORMAT AUTOFORMAT din bara de meniuri. Va aparea caseta de dialog AUTOFORMAT.

-in caseta de dialog, selectam unul dintre formatele din lista TABLE FORMAT

-tot din caseta de dialog, selectam butonul OPTION. Va aparea un set casete de validare.

-marcam sau anulam marcajul casetelor de validare FORMAT TO APPLY pentru a stabili ce seturi de potiuni vor fi aplicate. Pe masura ce modificam fiecare setare, efectul acestora va fi prezentat in caseta de previzualizare SAMPLE.

-cind aplicajul din SAMPLE corespunde exigentelor pe care dorim sa le impunem portiunii selectate din foaie, selectam O.K. pentru a inchide caseta de dialog.

Formatarea prin copiere cu ajutorul instrumentului format painter

Daca creem un bloc de celule formatate , putem copia aspectul acestora in alte celule. Se procedeaza astfel:

-selectam celulele cu care au formatul aspectului pe care il dorim

-in bara de instrumente STANDARD (de sus), efectuam un clic pe butonul FORMAT PAINTER. O linie intrerupta mobila va inconjura celulele formatate pe care le-am selectat si simbolul unei pensule va aparea in indicatorul de celula.

-marcam un bloc de celule in care se va copia formatul. Sau: daca al doilea bloc de celule are aceleasi dimensiuni cu primul, efectuam un clic numai pe prima celula de la noua locatie. Doar formatul va fi copiat in aceste celule, nu si datele.

Trei reguli de aliniere

In EXCEL, exista trei reguli simple care determina modul in care programul va pozitiona datele in interiorul celulelor:

1- textul se aliniaza la stinga

2- valorile se aliniaza la dreapta

3- mesajele de eroare si valorile logice TRUE si FALSE se centreaza

Schimbarea alinierii

Pentru a schimba alinierea datelor intr-o celula sau intr-un bloc de celule:

-selectam celula sau blocul. (Toate datele din interiorul zonei selectate vor fi afectate, indiferent daca sunt text, valori, sau si una sau alta).

-efectuam un clic pe unul din butoanele ALIGNMENT :ALIGN LEFT, sau ALING RIGHT.

Retinem ca alinierea se refera, de obicei, la pozitia datelor in interiorul celulelor luate in considerare individual. Daca selectam un bloc de celule si apoi selectam instrumentul CENTER , EXCEL va centra fiecare valoare in raport cu celula pe care o contine, nu in raport cu blocul mai mare.

Selectarea aspectului caracterelor

Majoritatea caracterelor pe care le putem aduce aspectului caracterelor din text sau numerelor pot fi stabilite prin intermediul instrumentelor din bara de instrumente Formatting.

Instrumentele bold, italic si underline

Putem aplica unele sau pe toate dintre aceste atribute celulelor selectate:

1- selectati o celula sau un bloc de celule

2- efectuam un clic pe butoanele BOLD, ITALIC, UNDERLINE

Aceste instrumente sunt comutatoare cu doua pozitii: efectuati o data clic pe butonul BOLD si textul va fi afisat cu litere aldine. Efectuam din nou un clic pe BOLD iar textul va reveni la caracterele normale. Daca dorim, putem afisa textul in orice combinatie de caractere aldine, cursive sau subliniate.

Alte atribute ale textului pot fi activate prin intermediul instrumentelor DOUBLE UNDERLINE ( subliniere dubla) si STRIKETHROUGH (barare), care pot fi gasite in categoria TEXT FORMATTING.

Proceduri de analiza a datelor

Marea putere a aplicatiei Microsoft Excel consta in multiplele posibilitati de analiza a datelor. Aceste proceduri contin componente pentru gestionarea Datelor, analiza statistica a datelor, concentrarea datelor, reprezentari grafice Etc. O mare parte dintre aceste proceduri sunt prezentate in continuare. Trebuie remarcat ca, datorita posibilitatii de programare in Visual Basic, Procedurile de analiza a datelor sunt practiv nelimitate, o serie intreaga de Companii dezvoltatoare de software fiind axate pe creare unor noi componente.

Liste

Prin lista se intelege o serie de linii succesive intr-o foaie de calcul, fiecare coloana din lista continand date cu aceeasi semnificatie logica. De exemplu, o Lista de clienti poate contine o coloana cu numele, o coloana cu prenumele, o coloana cu numarul de telefon si o coloana cu valoarea comenzii. Prima linie a unei liste este interpretata drept antetul listei si este recomandabil Ca ea sa contina denumirile coloanelor. Microsoft Excel dispune de proceduri prin care informatia dintr-o lista poate fi localizata dupa anumite criterii (de exemplu gasirea numarului de telefon al unui client), poate fi ordonata

(de exemplu ordonarea alfabetica a listei clientilor), poate fi totalizata (de exemplu calcularea automata a totalului comenzilor unui client) etc. Din acest punct de vedere, multe dintre operatiunile

Elementare asupra unei baze de date pot fi efectuate pentru o lista

Excel.

O lista este de altfel recunoscuta automat drept o baza de date prin:

· Coloanele listei sunt campuri ale bazei de date

· Etichetele de coloane sunt denumirile campurilor

· Fiecare linie din lista (cu exceptia primei linii) este o inregistrare. pentru ca o zona dreptunghiulara cu date sa fie recunoscuta drept lista trebuie (sau este recomandabil) ca:

· Liniile si coloanele listei trebuie sa fie adiacente, cu alte cuvinte se va evita lasarea unor linii sau coloane libere.

· Domeniul listei trebuie sa fie inconjurat de linii si coloane libere (in caz contrar se va considera ca lista include si informatia adiacenta, care, de regula, este altfel structurata)

· Linia de antet se va diferentia prin format (culoare, fundal, font) si Nu prin separarea cu o linie goala.

· Este recomandat ca o foaie de calcul sa contina o sigura lista (anumite operatiuni nu se pot efectua simultan pe mai multe liste din aceeasi foaie, dar se pot efectua simultan pe liste din foi diferite).

· Datele importante se vor plasa in coloanele din interiorul listei si nu pe extremitati.

· La introducerea informatiilor text se va evita tastarea unor spatii Initiale sau finale, in caz contrar sortarea si cautarea pot sa nu produca rezultatele dorite (un spatiu este considerat ca apartinand unei constante text).

Selectarea unei liste

Pentru a aplica o operatiune specifica, lista trebuie sa fie selectata in prealabil. Selectarea se poate efectua total, prin procedeul uzual, dar acest fapt nu este Necesar. Este insa suficient ca celula activa sa fie in interiorul listei pentru ca domeniul listei sa fie identificat in mod corect.

Sortarea unei liste

Ordonarea unei liste dupa anumite criterii este o operatiune utila atat in analiza datelor cat si pentru prezentarea datelor. Se pot ordona liniile (inregistrarile) Listei sau coloanele (operatiune mai rara in practica) potrivit valorilor din coloane, respectiv linii specificate de utilizator. Este posibila, de asemenea, ordonarea valorilor dintr-o linie sau coloana, Independent de datele adiacente, daca in prealabil se selecteaza total celulele respective si se raspunde adecvat la dialogul afisat in acest caz de Excel (intrebarea este daca sa se extinda selectia la datele adiacente si se va raspunde negativ pentru o sortare partiala).

Ordinea de sortare

Datele pot fi sortate in ordine alfabetica, numerica (dupa valoare) sau calendaristica, in ordine crescatoare sau descrescatoare, dupa una sau mai multe coloane. La sortarea informatiilor de tip text, Microsoft Excel ordoneaza de la stanga la dreapta, caracter dupa caracter. Sortarea ascendenta a textului este bazata pe ordinea 0 1 2 3 4 5 6 7 8 9 ' - (spatiu) ! ' # $ % & ( ) * , . / : ; ? @ [ ] ^ _ ` ~ + < =

> A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Sortarea ascendenta a numerelor este de la cel mai mic numar negativ la cel mai mare pozitiv. Ordinea valorilor logice este FALSE TRUE.

Valorile eroare sunt egale (indiferent de tipul erorii). Celulele goale sunt intotdeauna ordonate ultimele (indiferent de ordinea Crescatoare sau descrescatoare). Este posibil sa se aleaga, pentru informatii cu semnificatii locale, ordinea data de o lista creata de utilizator (meniul Tools, Options, Custom lists, vezi

explicatiile date anterior).

Optiunile de sortare

Sortarea este condusa de parametrii fixati in dialogul Sort Options (afisat prin comanda Sort din meniul Data si actionarea butonului Options, vezi dialogul Sort explicat mai departe). First key sort order – in lista derulanta se selecteaza ordinea de sortare (normal sau dupa o lista definita de utilizator), Case sensitive – se marcheaza daca ordonarea face diferenta Intre litere mari si mici, Orientation – se fixeaza sortarea liniilor (Sort top to bottom) sau coloanelor (Sort left to right).

Dupa fixarea optiunilor, acestea raman valabile pana la urmatoarea modificare. Optiunile sunt permise doar pentru prima cheie de sortare (cheuia principala).

Ordonarea crescatoare/descrescatoare dupa o coloana

Aranjarea listei dupa valorile dintr-o coloana (se modifica totusi ordinea tuturor liniilor, cu pastrarea alinierilor pe orizontala) se poate realiza prin actionarea uneltelor dedicate:

· Click pe o celula din coloana dorita

· Click Sort Ascending sau Sort Descending ,

Ordonarea liniilor dupa doua sau mai multe coloane

Ordonarea poate fi efectuata dupa mai multe coloane (chei de sortare). In acest tip de sortare este importanta fixarea prioritatilor pentru coloane: liniile vor fi ordonate dupa prima coloana indicata, in caz de egalitate dupa prima coloana se ordoneaza dupa a doua coloana indicata, in caz de egalitate dupa primele doua coloane se ordoneaza dupa a treia coloana.

· Se selecteaza lista (eventual doar prin pozitionarea celulei active in lista)

· Se da comanda Sort din meniul Data. Este afisat dialogul Sort prezentat in continuare.

· In listele derulante Sort by si Then by se aleg, in ordine, coloanele (campurile) de sortare. Pentru fiecare cheie de sortare se fixeaza in grupul de butoane radio alaturat ordinea de sortare:

- crescatoare (Ascending) sau

- descrescator (Descending). Nu este necesar ca ordinea sa fie aceeasi pentru toate campurile.

In grupul My list has se specifica daca lista are linie antet (linia cu denumirile campurilor – Header row) sau nu dispune de antet (No header row). In primul caz se vor sorta doar inregistrarile reale (liniile 2 – …) din lista. Butonul Options afiseaza dialogul Sort Options (explicat anterior).

Observatie 1. Daca se doreste sortarea listei dupa mai mult de trei coloane se alege urmatorul mecanism:

– se stabileste ordinea de prioritate a cheilor de sortare (de la cea mai

importanta la cea mai putin importanta);

– se efectueaza sortarea dupa cele mai putin importante trei coloane;

– se continua cu urmatoarele trei coloane de prioritate mica s.a.m.d.

Observatie 2. Daca liniile sortate apartin unui outline (vezi subiectul respectiv), atunci sortarea pastreaza gruparile existente

Sortarea coloanelor dupa continutul liniilor

La sortare se poate inversa rolul liniilor si al coloanelor. Tot ceea ce s-a spus la ordonarea liniilor se pastreaza, schimband 'linii' in 'coloane' si invers, daca se marcheaza in Sort Options caseta de control Sort left to right, dupa care se fixeaza analog liniile care au rolul de chei de sortare.

Sortarea dupa date calendaristice (luni, zile) sau liste proprii

Deoarece anumite informatii care se prezinta drept texte (de exemplu: luni, marti, miercuri, joi etc.) nu pot fi ordonate ca text, neproducand ordinea corecta, se alege ordonarea dupa liste implicite sau create de utilizator.

· Se selecteaza lista.

· Se da comanda Sort din meniul Data.

· Click Options.

· Sub First key sort order se alege lista care da ordinea dorita si apoi OK.

· Se continua in mod uzual cu fixarea celorlalte chei de sortare.

Observatie. Ordinea listei selectate in First key sort order se aplica doar coloanei mentionate in zona Sort by. Pentru a aplica ordini definite la mai multe coloane, fiecare coloana va fi ordonata separat, in ordinea inversa a prioritatilor.

Filtrarea informatiilor dintr-o lista

Intelegem prin operatiunea de filtrare a informatiilor procesul prin care se selecteaza dintr-o lista doar acele inregistrari care satisfac anumite conditii impuse de utilizator. Procesul este, in buna parte, similar procesului de interogare a unei baze de date. Comanda principala este Filter din meniul Data, dar sectiunile urmatoare vor mai trata subiecte care se pot inscrie in acelasi proces de filtrare (comanda

Form, comanda Outline etc.). Trebuie retinut ca o operatiune de filtrare presupune stabilirea unor criterii de

selectie, care se pot defini ca o entitate distincta (intr-o zona separata a foii de calcul) sau se pot selecta dintr-o suita de criterii creata in mod automat de catre Microsoft Excel prin analiza informatiilor din lista.

Comanda Filter

Accesul la principalele instrumente de selectare a informatiilor se realizeaza prin comanda Filter din meniul Data. Submeniul afisat dispune de trei noi comenzi

– AutoFilter, explicata in continuare.

– Show All, prin care se afiseaza toate inregistrarile listei (anuleaza filtrarile active).

– Advanced Filter, explicata in continuare.

Comanda AutoFilter

Operatiunea cea mai rapida de regasire a unor informatii dintr-o lista se realizeaza prin succesiunea de comenzi Filter (din meniul Data) urmata de AutoFilter (din submeniul afisat). Ca urmare a comenzii, fiecare denumire de camp apare pe foaia de calcul insotit de un buton de lista derulanta. Prin actionarea butonului de derulare apare o lista de optiuni, primele trei fiind standard: (All), (Top 10…), (Custom…), urmate de lista valorilor existente in coloana respectiva a listei. Selectarea unei valori din lista derulanta produce afisarea doar a acelor inregistrari care au ca valoare a campului valoarea selectata (in acest mod se realizeaza filtrarea). Selectarea intrarii (All) produce afisarea tuturor inregistrarilor (anularea filtrarii pentru campul respectiv). Selectarea intrarii (Top 10…) este posibila pentru campuri numerice si arata doar primele inregistrari, in ordinea crescatoare sau descrescatoare a valorilor, ordinea fiind fixata intr-un dialog intermediar. Se poate alege si numarul inregistrarilor afisate (nu este obligatoriu 10).

Selectarea intrarii (Custom…) afiseaza un dialog care permite stabilirea unei conditii de

filtrare mai complexe (de exemplu doar inregistrarile cu salariatii care au salariu mai mic de

1000000 de lei dar mai mare sau egal cu 700000 de lei). Un asemenea dialog este in figura

alaturata

In primul grup de optiuni se fixeaza prima conditie prin alegerea operatorului in prima zona si valorii de comparatie in a doua zona. Se alege operatorul logic dintre conditii (daca exista o a doua conditie) prin butoanele radio, dupa care se fixeaza a doua conditie. Conditia compusa realizata astfel se aplica doar campului pe care se stabileste filtrarea curenta.

Observatie. Pentru a realiza o filtrare care implica mai multe campuri (de exemplu angajatii care au salariu mai mic de un milion de lei si au mai mult de doi copii) se stabileste cate un filtru pe fiecare camp. Actiunea comuna produce selectarea dorita (o data aplicat filtrul pentru o coloana, orice alta filtrare

ulterioara se aplica doar inregistrarilor deja selectate).

Comanda Advanced Filter

Prin comanda Advanced Filter este posibil ca inregistrarile selectate sa fie copiate in alta locatie din foaia de calcul. Un alt avantaj este acela al vizibilitatii conditiilor prin faptul ca se defineste o zona, numita in general Criteria, care contine conditiile de filtrare pentru toate coloanele dorite. Lista trebuie sa aiba obligatoriu etichete de coloane pentru a aplica aceasta procedura. Pasii urmati sunt

Definirea criteriilor de filtrare.

Se copie etichetele coloanelor implicate in filtrare

Se alipesc intr-o prima linie a zonei care va fi zona Criteria.

In liniile urmatoare se scriu conditiile de filtrare (vezi explicatiile care urmeaza).

Comanda Advanced Filter.

- Se selecteaza lista (eventual doar prin pozitionarea celulei active in lista).

- Comanda Filter din meniul Data si apoi Advanced Filter.

- Se aleg optiunile dorite (vezi figura care urmeaza).

- Se actioneaza OK.

Este obligatoriu ca zona de criterii sa fie separata prin cel putin o linie (coloana) goala de lista propriu-zisa (altfel identificarea automata a listei este gresita). Este recomandabil ca zona de criterii sa fie sub lista. Aranjarea criteriilor pe linii comune cu lista poate produce neajunsuri prin ascunderea in urma filtrarii a acestor linii.

In grupul Action se alege modul de efectuare a filtrarii: Filter the list, in-place produce filtrarea listei pe loc (prin ascunderea liniilor neselectate), Copy to another location produce copierea inregistrarilor selectate in alta zona a foii de calcul.

In List range se specifica domeniul ocupat de lista. De regula acest domeniu este stabilit automat, dar este posibil sa se specifice un subdomeniu, daca nu se doreste filtrarea intregii liste. In Criteria range se specifica domeniul ocupat de criteriile de filtrare. Daca in foaie exista o zona denumita Criteria, adresa zonei apare automat in Criteria range. In Copy to se specifica domeniul in care se copie inregistrarile selectate (daca este marcata optiunea Copy to another location. Este suficient sa se indice doar coltul din stanga sus al zonei receptoare. Caseta de control Unique records only se marcheaza daca se doreste ca selectia obtinuta in urma filtrarii sa contina inregistrari unice.

Exemple de criterii pentru Advanced Filter

Conditiile de filtrare se aranjeaza intr-o lista avand ca linie de antet denumirile coloanelor (campurilor), din lista care se filtreaza, pentru care se definesc conditiile. Pentru o conditie creata ca rezultat al unei formule nu trebuie sa existe, totusi, denumire.

Toate conditiile scrise pe aceeasi coloana se refera la coloana cu denumirea din antet. Conditii care se scriu pe aceeasi linie trebuie sa fie indeplinite simultan (operatorul logic 'si'). Conditiile care se scriu pe linii diferite se considera legate prin operatorul logic 'sau' (deci sunt selectate inregistrarile care indeplinesc cel putin o linie de conditie din criterii).

Exemplul 1. Trei sau mai multe conditii pentru o coloana

Sunt selectate inregistrarile care au in coloana Nume una dintre valorile Beethoven, Berlioz, Bach. Este de observat ca doua valori se pot indica si in Auto Filter.

Exemplul 2. Conditii simultane pentru mai multe coloane

Se obtin inregistrarile care contin la Nume valoarea Beethoven, care la Simfonii au o valoare mai mare decat 5 si care are Anul mai mic de 1900. Se observa ca intre conditii exista cuvantul 'si', semnificand indeplinirea simultana a conditiilor.

Exemplul 3. Conditii alternative pentru mai multe coloane

Sunt selectate inregistrarile care au la Nume valoarea Beethoven, cele care au la Simfonii o valoare mai mare decat 5 si cele care au Anul mai mic decat 1900.

Exemplul 4. Conditii create ca rezultat al unei formule

Se pot utiliza denumirile coloanelor din lista, se remarca lipsa denumirii in antetul coloanei din zona de criterii. Rezultatul filtrarii este corect chiar daca in foaia de calcul apare o eroare (#NAME?) la scrierea conditiei deoarece anumite denumiri nu sunt definite prin Insert Names.

Formulare de date

Microsoft Excel dispune de o componenta dedicata introducerii dirijate a inregistrarilor intr-o lista si a navigarii intre inregistrarile care indeplinesc o anumita conditie. Desi pentru liste de mici dimensiuni

componenta pare a fi declasata de operarea uzuala din foaia de calcul, utilitatea se vede pentru liste de dimensiuni mai mari, care depasesc, sa spunem, 2 ecrane. Comanda principala este Form din

meniul Data, atunci cand lista este selectata. Se afiseaza un dialog

construit dinamic de mediul Excel, adaptat la lista procesata. Un exemplu este oferit de figura urmatoare. Se observa ca in partea din stanga se insiruie zone de editare cu denumirile campurilor din lista (in cazul exemplului nume, prenume si telefon), dotate inclusiv cu shortcut-uri. In partea din dreapta sunt butoanele care permit operatiile uzuale pentru inregistrari, primul mesaj aratand a cata inregistrare este afisata din numarul total.

New – se furnizeaza o inregistrare vida, ale carei campuri pot fi completate in zonele respective. Navigarea intre acestea se poate efectua cu TAB, mouse, shortcut   (ALT+litera subliniata). Inregistrarea este creata atunci cand se termina cu o noua comanda New sau cu ENTER.

Delete – se sterge ingistrarea curenta (cea afisata).

Restore – se poate reface starea initiala a inregistrarii daca s-a modificat vreo valoare. Nu are legatura cu anularea stergerii unei inregistrari (stergerea este permanenta).

Find Prev – trece la inregistrarea precedenta, daca exista.

Find Next – trece la inregistrarea urmatoare, daca exista.

Criteria – permite trecerea formularului in starea de filtrare a

informatiilor. In aceasta stare, zonele de editare devin zone in care se pot scrie sabloane de cautare (cautare dupa model). Prin butoanele Find Prev si Find Next se poate atunci naviga doar intre intregistrarile care

indeplinesc conditiile de filtrare. In figura alaturata este prezentat un

exemplu de cautare. Se observa mesajul Criteria (pentru starea

formularului), New este inhibata, Clear produce anularea sablonului,

Restore, Find Prev si Find Next au functiile explicate mai sus iar Form este butonul prin care se revine la starea de formular de culegere de date. Exemplul din imagine produce filtrarea si navigarea intre inregistrarile care au in campul Nume valori care incep cu 'Dum'. In cazul in care se completeaza si celelalte campuri, conditiile se verifica simultan (operatorul logic 'si'). Dupa stabilirea filtrului, comanda Find Prev sau Find Next pozitioneaza formularul pe prima inregistrare care satisface conditia (in sensul de parcurgere selectat), trece formularul in starea formular de culegere, dar filtrul ramane activ pana cand se

trece in Criteria si se da comanda Clear care anuleaza filtrul impus.

Totaluri partiale

In analiza datelor sunt foarte frecvente situatiile in care este necesar sa se calculeze anumite functii pentru fiecare categorie de inregistrari ale unei liste. De exemplu,

– castigul mediu pentru fiecare compartiment al unei intreprinderi;

– numarul de clienti din fiecare zona geografica;

– valoarea totala a marfurilor din stoc, pe categorii etc.

In asemenea situatie este normal ca un camp al inregistrarii sa realizeze clasificarea inregistrarilor (sectia, zona geografica, categoria de marfa etc.), lipsa acestei informatii facand imposibila calcularea automata a functiilor totalizatoare. Din exemplele de mai sus se observa ca o functie totalizatoare poate fi

– adunarea,

– numararea,

– media aritmetica,

– valoarea extrema (maxima sau minima),

– o alta functie statistica.

Pentru a realiza includerea automata in lista a rezultatelor functiilor totalizatoare se parcurg pasii:

– se ordoneaza lista dupa campul clasificator (incat toate inregistrarile care apartin unei categorii sa fie situate grupat);

– se selecteaza lista;

– se da comanda Subtotals din meniul Data. Ca urmare se afiseaza dialogul Subtotal

prezentat in figura alaturata si explicat in continuare. At each change in – se fixeaza pe campul care realizeaza clasificarea (la fiecare schimbare in valoarea acestui camp se introduce un subtotal.

Use function – se selecteaza din lista derulanta functia de totalizare.

Add subtotal to – se marcheaza campurile pentru care se calculeaza totalizarile.

Replace current subtotals – se marcheaza daca lista are deja subtotaluri, care se elimina.

Page break between groups – se marcheaza daca, la tiparire, fiecare grup apare pe o pagina noua.

Summary below data – se marcheaza pentru includerea unui total general. Acesta este obtinut din inregistrari si nu din subtotalurile intermediare.

Remove All – actionarea acestui buton elimina toate subtotalurile din lista selectata.

Ca rezultat al comenzii se restructureaza linii listei prin inserarea liniilor de total iar structura obtinuta este dotata cu instrumentul outline (explicat intr-o sectiune separata). Un exemplu este prezentat in figura urmatoare.

Se observa ca la fiecare schimbare in campul nume (selectat in optiunea At each change in) se calculeaza un total pentru campul valoare (marcat in Add subtotal to). La sfarsitul listei a fost calculat si totalul general (efectul marcarii optiunii Summary below data). Este de mentionat, in final, ca modificarea inregistrarilor listei duce in mod automat la recalcularea totalurilor.

Trasarea datelor (Outline)

In situatia in care structura datelor dintr-o lista este complicata prin introducerea unor coloane sau linii totalizatoare (de tipul subtotalurilor discutate anterior), este utila, ca instrument de analiza, afisarea, la un moment dat, doar a rezultatelor sintetice. Se realizeaza astfel separarea informatiilor esentiale de detalii care pot sa ingreuneze receptarea corecta a situatiilor prelucrate. Procedura oferita de Microsoft Excel pentru situatia discutata este cea de outline si consta in dotarea foii de calcul cu o serie de simboluri grafice active

prin actionarea carora se ascund sau se vizualizeaza detalii de un anumit nivel. Observatie. Termenul outline poate fi tradus prin trasare, conturare, schitare etc., dar pentru a crea (psihologic) imaginea complexitatii operatiunii vom utiliza frecvent termenul original.

Trasarea automata

Pentru a putea fi analizata in mod automat si a se realiza trasarea, foaia de calcul trebuie sa fie organizata, pe portiunea listei implicate, intr-un mod sistematic prin includerea unor linii/coloane de totalizare. Formulele care compun o linie/coloana totalizatoare trebuie sa se refere la celule dispuse intrun

acelasi mod (in aceeasi directie) si sa fie fie plasate intr-o aceeasi parte fata de detalii (toate la stanga sau toate la dreapta, toate deasupra sau toate sub). Un outline se poate realiza automat prin:

– selectarea domeniului prelucrat

– comanda Group and Outline din meniul Data

– comanda Auto Outline din submeniul afisat de comanda precedenta.

Pe laturile foii de calcul se afiseaza simbolurile de outline explicate in continuare.

Row Level Bar (bara de nivel pentru linii), cu imaginea alaturata, pe care se remarca o linie in forma de paranteza patrata care grupeaza liniile detaliu (utilizate in formula totalizatoare) si linia totalizatoare.

Liniile detaliu sunt indicate prin puncte iar linia totalizatoare este indicata de butonul de control de la extremitatea simbolului. Butonul de control, de tip comutator, poate fi actionat cu mouse-ul si contine in forma extinsa semnul '–', care se transforma in '+' pentru forma restransa.

Column Level Bar (bara de nivel pentru coloane), cu imaginea alaturata, asemanatoare barei de nivel pentru linii dar asezata orizontal.

Level Symbol (simbol de nivel), grupate pe verticala

si orizontala in coltul stanga sus al foii de calcul, cate

un simbol pentru fiecare nivel de totalizare. Fiecare

simbol este un buton de control, vizibil in figura alaturata, si poate fi actionat cu mouse-ul. Pe fiecare simbol de nivel este trecut numarul nivelului   respectiv (1

este pentru nivelul maxim). Imaginea alaturata arata un outline realizat automat. Se remarca in structura datelor detaliile (datele privind vanzari in fiecare magazin al unei retele), liniile si coloanele totalizatoare.

Se observa ca, pentru simplitate, s-a ales o structura de date initiale care are doar doua nivele: detalii si totaluri. Se pot insa introduce pana la opt niveluri multiple de outline (de exemplu s-ar putea grupa magazinele si pe un nivel intermediar al zonei, s-ar pute introduce un supranivel prin apartenenta retelei de magazine la un grup comercial cu multiple activitati etc.). Fiecare grup ar dispune atunci de barele de nivel si de simbolurile de nivel proprii

Trasarea manuala

Pentru date structurate in modul prezentat la trasarea automata, se poate realiza gruparea detaliilor prin operatiunile:

– selectarea liniilor (coloanelor) care contin detaliile,

– comanda Group and Outline din meniul Data,

– comanda Group din submeniul meniul afisat.

Ca urmare a procedurii se afiseaza simbolurile de outline respective. Operatiunea se continua pentru urmatorul grup s.a.m.d. Este recomandabil sa se efectueze mai intai gruparea detaliilor pentru obtinerea

nivelului 2, apoi pentru obtinerea nivelului 3 etc.

Parametrii unui outline

Microsoft Excel presupune in mod implicit ca liniile totalizatoare sunt situate dedesubtul liniilor cu detalii iar coloanele totalizatoare sunt situate la dreapta coloanelor cu detalii. Aceste valori pot fi modificate prin succesiunea de comenzi

– Group and Outline din meniul Data,

– Settings din submeniul afisat.

Se obtine dialogul Outline Settings in care se pot marca zonele din Direction:

Summary rows below detail – pentru totaluri situate sub liniile de detalii;

Summary columns to right of detail – pentru totaluri situate la dreapta coloanelor de detalii.

Simbolurile grafice de outline pot vizualizate sau ascunse potrivit casetei de

control Outline symbols din fisa View a dialogului afisat la Options din

meniul Tools. Se recomanda totusi vizualizarea acestor simboluri pentru o mai

usoara operare cu un outline.

Operarea unui outline

Un outline poate fi in starea extinsa in care sunt vizibile atat detaliile unui anumit nivel cat si totalizarea respectiva, sau in starea restransa in care sunt vizibile doar totalizarile. Trecerea dintr-o stare in cealalta se realizeaza prin actionarea controalelor din barele/simbolurile de nivel sau prin comenzi (meniul Data, comanda Group and Outline etc.). Actiunile specifice sunt sistematizate in continuare.

Este posibil sa se elimine gruparea corespunzatoare unei bare de nivel prin:

– selectarea liniilor/coloanelor dorite,

– comanda Group and Outline din meniul Data

– comanda Ungroup din submeniul afisat.

Pentru eliminarea completa a unei trasari se va utiliza comanda Clear Outline

din submeniul afisat prin comanda Group and Outline. In acest mod se elimina doar simbolurile de outline, fara stergerea detaliilor sau a liniilor/coloanelor totalizatoare.

In imaginea alaturata se poate vedea efectul unor operatiuni de ascundere a detaliilor din outline-ul prezentat intr-o imagine anterioara. Sunt vizibile doar liniile/coloanelor totalizatoare si se observa transformarea barelor de nivel doar in butoanele care permit expandarea grupurilor

Tabele pivotante

Tabelele pivotante sunt cel mai puternic si mai elaborat procedeu de analiza a datelor. Prin crearea unui tabel pivotant se realizeaza un tabel de cu mai multe intrari in care se rezuma date provenite din diverse surse: liste Excel, baze de date, surse de date externe etc. Tabelul poate fi exploatat interactiv. Puterea unui tabel pivotant consta in aceea ca imaginea grafica formata in foaia de calcul este dotata cu o serie de elemente de control si cu o serie de proceduri care permit rearanjarea automata a tabelului (pentru a vizualiza diferite aspecte), inserarea de linii/coloane de totaluri si subtotaluri, includerea de functii utilizator. In cazul in care numarul de dimensiuni este mai mare, tabelul este organizat pe pagini, fiecare dintre acestea devenind vizibila printr-o simpla selectare. Pentru a avea o imagine intuitiva sa consideram lista Excel alaturata. Este evident ca analiza datelor este dificila in cazul existentei unui mare numar de inregistrari. Ar trebuie un mijloc de selectare a unui anumit reprezentant, anumit oras, anumit produs, totaluri pe luni/produse etc. Desi o asemenea operatiune este posibila prin procedura de filtrare a unei liste Excel, o imagine mai completa a realitatii se poate obtine formand un tabel pivotant bazat pe aceste date. Un exemplu este dat in figura urmatoare.

Se poate observa aranjarea pe pagini a rezultatelor, fiecare pagina fiind determinata de optiunea selectata in lista derulanta de langa butonul reprezentant (in figura este data pagina care cuprinde date pentru toti reprezentantii). Structura de linii si coloane este determinata de numarul campurilor de linii sau coloane (in figura se observa doua campuri care determina linii – luna si produs – si un camp care determina coloanele – localitate). Datele interioare sunt obtinute prin prelucrarea automata a campului valoare, functia totalizatoare fiind suma (mesajul din coltul stanga sus al tabelului). Tabelul mai contine subtotaluri pentru fiecare luna si totalurile generale pe linii si coloane.

In utilizarea unui tabel pivotant, structura acestuia se poate modifica interactiv prin dragarea butoanelor campuri in alte zone ale tabelului: de exemplu, daca se trage butonul luna peste zona de pagina, langa reprezentant, atunci o pagina este definita de o optiune pentru reprezentant si o optiune pentru luna (aceasta nu va mai figura pe linii). Restructurarea dinamica produce recalcularea tabelului. Principalele operatiuni asupra unui tabel pivotant sunt prezentate in continuare.

Elementele unui tabel pivotant

Un tabel pivotant contine campuri de mai multe tipuri. Un camp corespunde, in general, unui camp din baza de date sau lista de baza, dar poate fi creat si de catre utilizator pe baza unei formule. Fiecare camp are corespondent un buton care poate fi agatat si tras in alta zona a tabelului, producand restructurarea acestuia. Datorita posibilitatii de modificare a structurii tabelului pivotant, clasificarea data campurilor este relativa.

Camp de pagina (page field) – este un camp din datele sursa care produce clasificarea informatiilor pe pagini. In exemplul anterior campul reprezentant este camp de pagina. Campul este insotit de un control de tip ComboBox din care se selecteaza pagina vizibila. In lista derulanta sunt afisate toate valorile

acestui camp care se intalnesc in datele sursa.

Camp de linie (row field) – este un camp din date care a primit o orientare orizontala, pe linie. In exemplu, campurile luna si produs sunt campuri linie. Daca sunt mai multe campuri linie, acestea formeaza in prezentarea vizuala o structura ierarhica, doar nivelul exterior fiind listat o singura data.

Camp de coloana (column field) – este un camp din date care a primit o orientare verticala, pe coloana. In exemplu, campul localitate este un camp coloana. Daca sunt mai multe campuri coloane, acestea formeaza in prezentarea vizuala o structura ierarhica, doar nivelul exterior fiind listat o singura data.

Camp de date (data field) – este un camp din datele sursa care contin informatia rezumata. In exemplu este campul valoare. Pentru datele numerice functia implicita de rezumare este sumarea (sum), pentru datele text functia implicita este cea de numarare (count).

Intrari (items) – sunt valorile (categoriile) unui camp. De exemplu, 'ian' si 'feb' sunt intrari ale campului luna. Aceste intrari sunt utilizate in tabelul pivotant drept etichete de linii/coloane/pagini.

Regiunea datelor (data area) – este partea tabelului pivotant care contine rezumatele datelor, cu alte cuvinte zona din tabel fara liniile si coloanele de campuri si etichete.

Crearea unui tabel pivotant

Microsoft Excel dispune de un wizard specializat pentru crearea unui tabel pivotant. Cei patru pasi pe care trebuie sa-i parcurga utilizatorul in mod asistat corespund celor patru dialoguri ale wizard-ului.

Pasul 1 – fixarea datelor sursa

Comanda PivotTable Report din meniul Data afiseaza primul dialog al wizardului.

Din dialog este reprezentata doar zona de optiuni, butoanele de navigare intre

ecranele dialogurilor fiind uzuale si sunt prezentate intr-un dialog ulterior.

Ca sursa de date se poate indica:

Microsoft Excel list or database – este cazul frecvent al operarii in Excel si fixeaza ca sursa o lista Excel. Este util in acest caz sa se selecteze lista inainte de a se da comanda PivotTable Report.

External data source – se arata ca datele sursa se afla intr-un fisier extern (baza de date Access etc.).

Multiple consolidation range – permite realizarea unei unificari de date sub forma de tabel pivotant.

Another PivotTable – sursa de date este un tabel pivotant creat anterior.

Se actioneaza butonul Next pentru a se trece la urmatorul dialog.

Pasul 2 – referinta la datele sursa

In al doilea dialog al wizard-ului se precizeaza locul exact, adresa, de unde se preiau datele sursa. Daca acestea sunt intr-o lista Excel dialogul este cel din figura urmatoare.

In partea inferioara se observa butoanele de navigare intre ecranele wizard-ului:

Cancel – anuleaza operatiunea de creare a tabelului pivotant.

Back – se revine la dialogul precedent.

Next – se accepta optiunile din dialogul curent si se trece la pasul urmator.

Finish – termina faza de preluare a optiunilor si creeaza tabelul pivotant fara a mai afisa celelalte dialoguri.

Primul buton permite obtinerea de ajutor. In zona Range se da referinta la domeniul datelor sursa, aceasta poate fi stabilita dinamica prin utilizarea butonului de restrangere a dialogului sau prin cautare actionand Browse.

Pasul 3 – definirea campurilor

Se afiseaza un dialog, prezentat in continuare, care permite stabilirea interactiva a campurilor tabelului. Dialogul contine o schema a tabelului cu figurarea zonelor acestuia si, in partea dreapta, o lista cu toate campurile definite in datele sursa. Prin tragerea butonului corespunzator unui camp din lista sursa in regiunea dorita a tabelului (Page – pagini, Row – linii, Column – coloane, Data – regiunea datelor) se creeaza in tabel un camp de pagina, de linie, de coloana sau, respectiv, de date. In timpul cat dialogul este vizibil se poate efectua orice operatiune de drag-and-drop cu butoanele reprezentand campurile. La crearea unui camp de date se stabileste implicit functia de   centralizare, dar aceasta se poate modifica prin click dublu pe campul de date, caz in care se poate selecta o alta functie predefinita.

Pasul 4 – localizarea tabelului pivotant

Ultimul dialog al wizard-ului determina locul unde se creeaza tabelul si diverse optiuni privind structura acestuia.

New worksheet – tabelul se creeaza pe o foaie noua,

Existing workshet – tabelul se creeaza intr-o zona a unei foi existente. Zona poate fi selectata dinamic.

Prin actionarea butonului Finish se termina wizard-ul si se creeaza tabelul pivotant pe baza optiunilor selectate. In grupul de butoane este afisat in acest dialog butonul Options care deschide un nou dialog pentru configurarea tabelului. Dialogul este prezentat in figura urmatoare.

Optiunile, suficient de sugestive prin denumirile lor, se refera in principal la includerea in tabel a liniilor/coloanelor de total (Grand totals for columns, Grand totals for rows), la formatarea automata a tabelului rezultat (AutoFormat table), la modul de afisare a celulelor vide sau cu erori (For error values, show…, For empty cells, show…).

Grupul Data options fixeaza relatiile dintre tabel si datele sursa (salvare, actualizare etc.). Prin OK se revine la pasul 4 al wizard-ului.