EXCEL-4

Afisarea bazelor de date

DATA FORM

Sortarea bazelor de date

Sortarea organizeaza datele in ordine alfabetica si numerica , ascendenta dau descendenta ;

EXCEL poate sorta liniile unei liste sau coloanele foii de calcul ;

DATA SORT

Va apare caseta de dialog Sort

intre elementele care se pot selecta si aceasta caseta se numara cheile de sortare, ordinea de sortare si optiunea care stabileste daca datele au sau nu o linie de etichete ca antet .

EXCEL foloseste urmatoarea ordine de sortare :

nr. de la cel mai mic nr. negativ la cel mai mare nr. pozitiv ;

text ;

rezultate false FALSE ;

rezultate adevarate TRUE ;

valori de eroare ;

spatii libere BLANK .

Exista 2 metode:

SORTAREA PRIN COMENZI

Etape:

Se selecteaza celulele ce vor fi sortate pentru a selecta intregul domeniu; atunci cand acesta e inconjurat pe toate laturile de celule goale se executa click pe orice celula apartinand domeniului;

Comanda de sortare va selecta intregul domeniu;

In caseta SORT OF se marcheaza o coloana care se doreste sortata prima (aceasta coloana se numeste prima cheie de sortare);

Se sorteaza apoi ordinea de sortare Ascend sau Descend.

In caseta Them By se selecteaza eticheta coloanei pe care dorim s-o folosim ca a doua cheie de sortare.Aceasta se foloseste numai in cazul in care exista duplicate in primul camp de sortare.

Cel de-al III-lea camp de sortare e utilizat numai daca exista date duplicate in I si al II-lea camp de sortare.

Daca lista are nume de campuri , se selecteaza butonul Header Row pentru ca numele de campuri sa nu fie sortate odata cu datele ( In caz contrar No Header Row ).

Observatie : Daca este nevoie sa se sorteze o lista intr-o ordine de la stanga la dreapta si nu de sus in jos, se urmeaza procedura anterioara dar inainte de a selecta campul de sortare, din caseta DIALOG SORT se alege butonul OPTIONS apare caseta de dialog SORT OPTIONS se selecteaza SORT LEFT TO RIGHT

SORTAREA CU BUTOANELE DIN BARA DE INSTRUCTIE

Se selecteaza o celula din coloana pe care dorim s-o folosim drept cheie de sortare ;

Apoi se executa click pe unul din butoanele :

Revenirea la ordinea initiala dupa o sortare

cand dorim sa sortam o lista , dar mai tarziu dorim sa revenim la ordinea initiala , trebuie adaugat listei un index de inregistrari

acesta asociaza cate un nr. fiecarei inregistrari in concordanta cu pozitia ei, cu data si ora introducerii sau alte indicatoare numerice de inregistrare

Pentru a indexa inregistrarile unei baze de date astfel incat sa poata fi readusa la ordinea sa anterioara , se parcurg

etapele :

inserati o coloana adiacenta listei ( ex Nr. crt.)

introduceti un nr. (ex. 1 ) in cea mai de sus celula a coloanei , iar in urmatoarea nr.2

selectati celulele care contin valorile 1 si 2 si trageti in jos marcajul de umplere pe toata lungimea listei

la eliberarea butonului mouse , langa fiecare linie apare o serie de numere. Acestea se numesc nr. de indexare .

cand se revine la ordinea initiala , se selecteaza coloana cu nr. de indexare in caseta SORT BY si se alege butonul ASCENDING

Regasirea si filtrarea datelor

regasirea datelor intr-o lista , implica selectarea liniilor care satisfac anumite criterii

se poate filtra o lista

filtrarea ascunde temporar toate liniile din lista care nu satisfac criteriul

in EXCEL se pot regasi si filtra date utilizand 3 mecanisme :

Cu ajutorul formatului de date

Folosind AUTOFILTER

Folosind ADVANCED FILTER

Selectati o celula din lista

Din meniul DATA FORM

Se da click pe butonul CRITERIA

Se introduce criteriul sau criteriile in castele corespunzatoare campurilor

Se aleg butoanele FIND PREV si FIND NEXT pentru afisarea inregistrarilor care satisfac criteriul ales ;

Obs.

acest format pentru date este bun pentru regasirea si editarea inregistrarilor care satisfac comparatii simple sau multiple ;

Introduc conditii multiple , pp. Conditia AND

Toate comparatiile din criterii trebuie sa fie adevarate pentru ca o inregistrare sa fie selectata ;

Nu se pot utiliza formatul pentru date cu o conditie OR

Nu se poate folosi formatul pentru date pentru comparatii complexe cu AND si OR.

Utilizarea comenzii AUTOFILTER

Se poate utiliza una din cele 3 metode de regasire a datelor :

a)        Folosind lista derulanta pentru a regasi inregistrarile care indeplinesc exact criteriul;

b)       Folosind comanda CUSTOM din lista derulanta (BSM) pentru a crea comparatii simple sau din 2 campuri

c)        Folosind comanda TOP 10 , se pot selecta din partea de sus catre partea de jos un increment specificat de date , fie printr-un numar,  fie printr-un procent.

Spre deosebire de formatul pentru date , AUTOFILTER afiseaza date in foaia de calcul , deoarece AUTOFILTER ascunde liniile care contin inregistrari care nu indeplinesc criteriul selectat, nr. de linie apar pe sarite .

Nr. de linie sunt albastre pentru a indica faptul ca este vorba despre un subset al listei .Pentru a crea un AUTOFILTER se executa :

v         Selectarea unei celule din lista ;

v         Din meniul DATA FILTER AUTOFILTER

v         Se executa click pe lista derulanta pentru coloana in care se doreste introducerea criteriului;

v         Se selecteaza una din optiunile :

ALL =permite vizualizarea tutror inregistrarilor din acel camp

TOP 10=afiseaza caseta de dialog AUTOFILTER Top,  si permite filtrarea unei cantitati specificate de informatii , fie prin procent , fie prin nr. de elemente incepand din partea de sus sau de jos a listei;

CUSTOM = afiseaza caseta de dialog CUSTOM AMPLIFIER si permite crearea de criterii AND sau OR

BLANKS = afiseaza toate inregistrarile cu spatii in acest camp .

NONBLANKS = afiseaza toate inregistrarile care nu contin spatii in acest camp.

Dupa o selectie AUTOFILTER , foaia de calcul va ascunde liniile care nu indeplinesc criteriul .

Obs.

pentru a afisa toate inregistrarile si a inlatura criteriile din tote comenzile AUTOFILTER, din meniul DATA FILTER Show All .

Pentru a parasi AUTOFILTER , se merge in DATA FILTER se deselecteaza comanda AUTOFILTER .

Folosirea comenzii TOP 10 AUTOFILTER

Etape :

v         Executati click pe sageata listei derulante in camp ale carei valori se vor folosi pentru a filtra lista ;

v         Din lista derulanta se selecteaza optiunea TOP 10 si se deschide caseta de dialog TOP 10 AUTOFILTER

v         Din prima caseta , se sorteaza comanda TOP sau BOTTOM

v         In a 2-a caseta se introduce valoarea pe care dorim s-o folosim pentru a filtra datele

v         In a 3-a caseta selectam ITEMS sau PERCENT

v         Apoi se da OK

Folosirea comenzii ADVANCED FILTER

Comanda permite cautarea de date care indeplinesc criterii complexe pe baza operatorilor AND si OR si spre deosebire de AUTOFILTER permite extragerea din lista a unei copii a datelor filtrate.

Pentru utilizarea comenzii, se creaza intai un domeniu de criterii

Linia cea mai de sus din domeniul de criterii contine numele de campuri care trebuie scrise exact la fel ca numele de campuri de deasupra listei.

Nu este necesara includerea in domeniul de criterii fiecare nume de camp din lista

Domeniul de criterii include cel putin o linie goala sub numele de campuri;

In aceasta linie se introduc criterii pe care trebuie sa le indeplineasca inregistrarile cautate;

Excel compara criteriile aflate sub un nume de camp, in domeniul de criterii cu datele aflate sub acelasi nume de camp din lista;

Obs.

nu se include in domeniul de criterii linii goale, neutilizate.Acestea determina programul EXCEL sa le caute in toate inregistrarile listei.

Etape :

v     Se creaza un domeniu de criterii;

v     Se selecteaza o celula din lista;

v     Din meniul DATA FILTER ADVANCED FILTER

v     Se deschide caseta de dialog ADVANCED FILTER

Se selecteaza optiunea Filtre the list, in place astfel incat sa vedem doar inregistrarile care indeplinesc criteriile.

Daca datele trebuie plasate in alta zona a foii de calcul, se selecteaza COPY TO ANOTHER LOCATION.

In caseta LIST RANGE, se introduce domeniul listei (cu ajutorul mouse se face selectarea);

In caseta CRITERIA RANGE- se selecteaza domeniul de criterii .Daca se doresc inlaturate prin filtrare inregistrarile duble, se selecteaza caseta Unigen Records One. Aceasta optiunearata doar prima inregistrare care indeplineste optiunile , si elimina duplicatele ei. Apoi se da OK .

Obs.

Se pot introduce criterii multiple pe aceasi line de criterii; toate criteriile trebuie sa fie indeplinite pentru ca inregistrarea sa fie selectata (conditia AND )

Pentru a gasi inregistrari care sa indeplineasca, unul sau altul din elemente (conditia OR ), se creaza un domeniu de criterii cu mai multe linii;

Copierea datelor filtrate intr-o noua locatie.

Exista 2 metode

a)          Cu comanda AUTOFILTER

Se selecteaza inregistrarile care indeplinesc conditiile (lista filtrata )

EDIT COPY+PASTE

b)          Copierea in aceiasi foaie folosind ADVANCED FILTER.

Se introduc criteriile in domeniul de criterii

Se afiseaza caseta de dialog ADVANCED FILTER

Se alege optiunea COPY TO ANOTHER LOCATION

In caseta LIST RANGE , se selecteaza domeniul cu ajutorul mouse;

In caseta CRITERIA RANGE -se selecteaza domeniul de criterii

In caseta COPY TO -numele de domeniu, sau, se trage de-a lungul zonei din foaia de calcul care contine domeniul in care se vor copia datele filtrate;

Apoi, se da butonul OK.

Obs

Nu este recomandabil sa se plaseze domeniul COPY TO sub domeniul listei deoarece se impiedica extinderea domeniului listei.

La mesajul CANNOT CHANGE PART OF AN ARRAY (nu se pot modifica parti ale unei matrici )

Pentru a se putea modifica valorile individuale dintr-un tabel trebuie sa se inlocuiasca matricea cu valorile celulelor sale;

Pentru aceasta se procedeaza astfel:

Se selecteaza lista, din meniul EDIT COPY si

EDIT PASTE SPECIAL

In caseta de dialog ce se deschide , selectam optiunea VALVES si OK.

Din acest moment matricea nu mai exista, raman doar valorile ei.

CREAREA SUBTOTALURILOR

Etape:

se selecteaza oricare celula din lista

din meniul DATA SUBTOTALS

Din caseta At Each Change in, se slecteaza modul de grupare pe subtotaluri

Aceasta lista afiseaza coloanele din baza de date

Din caseta USE FUNCTION se alege functia dorita: Ex. SUM, AVERAGE, COUNT, MAX, MIN, PRODUCT

Se selecteaza datele cu care dorim sa se efectueze calculele din caseta Add Subtotal To;

Aceasta caseta afiseaza campurile din baza de date;

Pentru a insera un salt de pagina inaintea fiecarui grup ,  selectati Page Break Between Groups

De obicei subtotalurile si total general apar la sfarsitul grupului de date ; daca se doreste afisarea acestora inaintea grupului de date ,  se selecteaza Sumary Below Data

Apoi se da OK.

ELIMINAREA UNUI SUBTOTAL

DATA SUBTOTALS Remove All

Folosirea functiilor de cumuluri multiple

Pentru a afisa 2 sau mai multe functii de cumulare pentru acelasi set de date ,  parcurgeti etapele :

DATA SUBTOTALS in caseta Use Function se selecteaza prima functie ;

Se da O.K.

EXCEL insereaza randurile de subtotal

Se alege din nou comanda si se selecteaza alta functie

Se deselecteaza caseta Replace Curent Subtotals

EXCEL insereaza un rand suplimentar de subtotal cu noul calcul

In partea stanga apar butoanele 2 3 - +

In functie de nivelul de subtotaluri create , EXCEL creaza o sintetizare pe niveluri .Pentru a afisa doar totalul general si etichetele de coloana , se executa click pe butonul de nivel 1

Pentru a afisa doar subtotalurile si totalul general ,  executati click pe simbolul 2 nivel de rand 2

Se poate da click pe butoanele _ Hde Detail sau + Display Detail pentru a condensa sau extinde sintetizarea.

T A B E L E P I V O T ( tabel de sinteza )

Reprezinta un mecanism interactiv pe care EXCEL il pune la dispozitie in vederea rezumarii , sintetizarii si analizarii rapide a unor masiva de date .

Tabelele pivot se creaza utilizand PIVOT TABLE WIZARD

Etape :

Din DATA PIVOT TABLE

Va apare prima caseta de dialog (Step 1 of 4 )

Aici se cere precizarea sursei datelor

Prima optiune : Microsoft Excel List or Database lista sau domeniu de coloane dintr-o eticheta , foaie de calcul Excel.

External Data Source

fisiere sau tabele create in alte aplicatii (Paradox ,  Dbase , Acces , Fox )

Multiple Consolidation Ranges

domenii multiple cu linii si coloane cu eticheta din foile de calcul Excel

Another Pivot Table

un tabel pivot existent in registrul de calcul activ.

se alege prima optiune , apoi se da click pe butonul NEXT

apare caseta de dialog nr.2 (Step 2 of 4 ) ;aici in caseta RANGE -selectam domeniul care contine datele .

Daca apare mesajul : "Pivot Table Field name is not valid " verificati daca domeniul specificat include in prima linie o celula goala .

a 3-a caseta de dialog afiseaza o macheta a tabelului sinteza si etichetele randurilor si coloanelor;

determinati campurile care contin datele ce se doresc cumulate si trageti butoanele corespunzatoare in zona DATA

v         pentru aranjarea articolelor dintr-un camp in coloane cu etichete deasupra partii superioare , trageti butonul acelui camp in zona COLUMN .

v         pentru aranjarea aricolelor dintr-un camp in linii cu etichete de-a lungul laturii din stanga, trageti bitonul acelui camp in zona ROW .

v         se alege butonul NEXT apare caseta 4

v         ea comunica programului Excel unde sa puna tabelul pivot

v         poate fi pus in oricare foaie de calcul din oricare registru de calcul

v         in caseta Pivot Table Starting Call, se introduce celula din stanga susu a tabelului pivot;

v         se poate da un nume tabelului pivot in caseta Pivot Table Name

v         se pot introduce totaluri si subtotaluri ,  iar pentru inchiderea tabelului pivot se alege FINISH.

Tema laborator

se creaza mapa de lucru PRODUSE .XLS

prima foaie se va numi VANZARI , cu structura: Nr.crt; Cod produs; Cod raion; Denumire produs; Pret vanzare Cantitate vanduta ; Valoare vanduta;

			- codul produsului=100 - 199 - cod raion = 1

se vor introduce 5 inregistrari

se vor introduce 5 inregistrari

			- codul produsului=300 - 399 - cod raion = 3

se vor introduce 5 inregistrari

val. Vanduta = pret vanzare * cantitatea vanduta

folosind formularul pentru date se vor mai adauga 3 inregistrari cu cod produs = 400- 499 si cod raion = 4

folosind formularul pentru date s vor cauta produsele cu :cod raion =3

se vor sterge inregistrarile cu :cod raion =2 , folosind formularul pentru date

se va sorta lista dupa cod raion (cheie principala ) si cod produs (cheie secundara )

se vor afisa primele 7inregistrari cu pretul de vanzare cel mai mare ;TOP

se vor afisa ultimele 5 inregistrari cu cantitatea vanduta cea mai mica ;LOTTOP

sa se adauge la sfarsitul listei , campul FABRICA;

pentru primele 3 inregistrari , FABRICA este Bucuresti;

pentru urmatoarele 3 , FABRICA este Brasov;

pentru urmatoarele 3 , FABRICA este Timisoara

restul inregistrarilor se completeaza individual

folosind comanda AUTOFILTER sa se afiseze numai inregistrarile cu cod raion=1 si Fabrica Brasov

aceste inregistrari sa se copieze intr-o alta foaie de lucru care se va numi Brasov;

se revine in foaia de lucru VANZARI, unde se vor afisa numai inregistrarile cu Fabrica- Timisoara si se va copia intr-o alta foaie (Timisoara )

lista filtrata din foaia de lucru Timisoara sa se sorteze dupa denumirea produsului;

se revine in foaia Vanzari si folosind comanda ADVANCED FILTER sa se copieze inregistrarea cu cod raion=2 si pretul de vanzare> 50.000 (tot in foaia Vanzari )

sa se afiseze inregistrarile care au Fabrica Bucuresti si lista filtrata sa se copieze intr-o alta foaie care se va numi Bucuresti

in foaia Vanzari se va realiza un sutotal pe codul raionului si un total general folosind comanda SUBTOTALS din meniul DATA

raportul rezultat sa se formateze si sa se copieze intr-o noua foaie de lucru numita RAPORT 1

se va insera un titlu acestui raport : " Situatia Vanzarilor la data de- - - - "

data se va afisa folosind functia pentru data.

Exemplu :

Advanced Filter

domeniul de crirterii deasupra listei pe un rand

CODR PRETV

=2 (AND ) daca le trecem pe acelasi rand (conditiile)se creaza functia AND iar daca

(AND ) sunt pe randuri diferite functia OR

Editarea tabelului pivot

v     se pot modifica numele campurilor si pe cele ale articolelor unui tabel pivot;

v     se selecteaza campul sau articolul si se scrie noul nume;

v     EXCEL nu lasa duplicarea numelui; iar modificarea numelui campurilor sau articolelor nu modifica numele lor din baza de date sursa.