Facilitati de baza ale sistemului Excel

Teme propuse

Tema nr. 1

1. Sa se lanseze în executie tabelatorul Excel si sa deschida o noua mapa de lucru. Sa se redenumeasca foaia de lucru cu numele Sheet1; noua denumire va fi Produse.

2. În foaia de lucru cu numele Produse se va introduce, începând cu celula A1, un tabel care sa contina pe linii denumirile a cel putin trei produse ale unei firme imaginare. Coloanele vor reprezenta lunile Ianuarie, Februarie si Martie, iar la intersectia dintre o linie si o coloana a tabelului se va afla numarul de bucati vândute din produsul corespunzator respectivei linii în luna corespunzatoare coloanei. Într-o ultima coloana a tabelului se va mentine pretul unitar pentru fiecare dintre produsele firmei. Pentru formatarea capului de tabel se va folosi fontul Arial Italic de 13 puncte, iar pentru celelalte celule fontul Times New Roman Normal de 14 puncte.

3. Sa se calculeze, pentru fiecare produs în parte, sumele obtinute din vânzari pe toate cele trei luni, apoi sa se calculeze încasarile totale ale firmei pe fiecare dintre cele trei luni. Se vor utiliza operatii de copiere a continutului unor celule.

4. Sa se mute tabelul creat anterior în zona având celula B3 ca si colt stânga-sus, dupa care sa se salveze mapa de lucru curenta într-un fisier cu numele firma.xls.

Tema nr. 2

1. Sa se lanseze în executie tabelatorul Excel si sa se deschida o noua mapa de lucru. Sa se redenumeasca foaia de lucru cu numele Sheet2; noua denumire va fi Telefon. Aceasta foaie de lucru va fi apoi adusa pe prima pozitie în cadrul mapei curente.

2. Sa se creeze, în cadrul foii de lucru Telefon, un tabel Excel care sa simuleze evidentele unei companii de telefoane. Pentru fiecare abonat se vor retine: numele abonatului, numarul de telefon si sumele de plata pe fiecare din lunile anului. Pentru editarea capului de tabel se va utiliza fontul Times New Roman de 13 puncte.

3. Pe baza datelor din tabel sa se calculeze, pentru fiecare abonat, o suma lunara medie de plata, precum si suma totala pe care compania o are de încasat de la abonati.

4. Sa se copieze capul de tabel într-o alta foaie de lucru a mapei curente, foaie care va primi denumirea Tabel. Sa se salveze mapa de lucru curenta într-un fisier cu numele abonati.xls.

Tema nr. 3

1. Sa se deschida o noua mapa de lucru Excel care sa fie salvata într-un fisier cu numele librar.xls.

2. Sa se creeze, în cadrul foii de lucru curente, un tabel Excel care sa descrie vânzarile unei librarii pentru patru titluri. Pentru fiecare titlu se vor retine denumirea cartii, autorul, pretul unitar, intrari (numarul de produse primite), iesiri (cantitate vânduta). Pentru editarea capului de tabel se va utiliza fontul Times New Roman, înclinat si de 12 puncte, iar pentru liniile tabelului se va folosi fontul Arial de 11 puncte. Foaia de lucru va avea numele CARTE.

3. Pe baza datelor din tabelul anterior, sa se calculeze stocul existent la fiecare titlu si suma obtinuta din vânzarea fiecarui tip de produs. Sa se determine apoi vânzarile totale.

Figura nr. 7 - Exemplu de foaie de lucru

Prelucrarea datelor cu operatii specifice bazelor de date

O baza de date contine un ansamblu de date structurate într-o forma specifica; structura de memorare si organizare a informatiilor faciliteaza gestiunea lor (operatii de memorare, regasire a datelor etc.).

Un tabel Excel poate fi privit ca o baza de date pentru care numele de date sau câmpurile sunt numele coloanelor tabelului iar fiecare set de valori pentru aceste nume (înregistrare) se gaseste într-o linie a tabelului. Astfel, un tabel Excel implementeaza în mod natural ceea ce este cunoscut sub numele de model relational de organizare a bazelor de date. În Excel, pentru o structura de acest tip se mai foloseste si denumirea de lista.

Operatii cu liste
Teme propuse

Operatii cu liste

a) Crearea unei liste se face prin introducere de valori, respectând principiile descrise în subcapitolele anterioare (numele câmpurilor apar ca nume de coloane). Listei astfel construite i se poate asocia un nume cu optiunea Insert - Name - Define.

b) Actualizarea listei - presupune operatii de modificare a valorilor, adaugare si eliminare de înregistrari din lista. Aceste operatii se realizeaza foarte usor folosind formularele de date. Un astfel de formular se poate crea prin selectarea comenzii Data - Form în timp ce cursorul este pozitionat în cadrul tabelului de date. Fereastra formularului permite modificarea valorilor prin operatii uzuale de editare, adaugarea unei înregistrari (New), eliminarea înregistrarii curente (Delete) (a se vedea figura nr.8).

Dupa efectuarea acestor operatii, modificarile corespunzatoare apar si în foaia de lucru.

Figura nr.8 - Exemplu de formular pentru date

c) Cautarea înregistrarii care îndeplineste anumite proprietati se poate face folosind tot formularul de date: Data - Form. Butonul Criteria permite specificarea valorilor cautate, care se vor introduce într-o fereastra de tip înregistrare. Se va realiza pozitionarea pe înregistrarea (înregistrarile) care îndeplinesc conditiile specificate. În cazul mai multor înregistrari, se va putea trece de la una la alta.

De exemplu, în lista din figura de la Teme propuse se poate cauta o anumita persoana sau sau toate persoanele cu proprietatea "Media >9".

d) Sortarea înregistrarilor dintr-o lista este operatia de rearanjare a înregistrarilor, dupa valorile unuia sau mai multor câmpuri, numite chei, în ordine alfabetica, numerica sau cronologica (crescatoare sau descrescatoare).

Sortarea se realizeaza folosind comanda Data - Sort. În fereastra care se deschide se specifica cheile de sortare (a se vedea figura nr.9). Primul câmp dupa care se face ordonarea se numeste cheie principala. Daca exista mai multe înregistrari care au aceeasi valoare pentru aceasta cheie, putem specifica alta cheie de ordonare (secundara). Astfel, în Excel, la o operatie de sortare se pot specifica maximum trei chei de sortare.

Figura nr. 9 - Fereastra de dialog Sort

Pentru fiecare cheie se poate opta între sortare crescatoare sau descrescatoare (Ascending/Descending).

Observatie.

Cu o tehnica mai deosebita, utilizatorul îsi poate defini ordini în liste speciale care contin anumite valori (de exemplu: zilele saptamânii, lunile anului) folosind Tools - Options - Custom List - List Entries.

e) Selectarea înregistrarilor care îndeplinesc anumite proprietati (filtrare).

Sa presupunem, de exemplu, ca dorim ca toate persoanele retinute în foaia de lucru din figura de la Teme propuse si care au media mai mare decât 9 (Media > 9) sa fie afisate separat în foaia curenta de lucru.

Aceast tip de prelucrare a informatiilor se realizeaza prin intermediul comenzii Data - Filter. Exista doua posibilitati de obtinere a rezultatelor dorite:

1) cu AutoFilter - specificam pentru fiecare câmp valoarea dorita sau o proprietate (fiecarui câmp i se ataseaza o lista de forma: All, Custom, lista de valori). În exemplul nostru, pentru toate câmpurile specificam All în afara de câmpul Media, unde specificam cu Custom conditia >9 (greater than 9).

2) cu Advanced Filter - se pot introduce domenii de selectare mai complexe. Se va cere:

• 
  domeniul listei din care se face selectia (List range);
  
• 
  domeniul de criterii (Criteria range). Într-un domeniu de criterii câmpurile pentru care se specifica conditiile se depun ca si cap de tabel (vezi figura nr.11). Între conditiile din acelasi rând se aplica operatorul logic Si iar între conditiile din rânduri diferite - operatorul logic Sau.
  
• 
  eventual domeniul destinatie sau coltul sau stânga sus, daca nu se doreste ca selectia sa se realizeze în pozitia curenta (Copy to - a se vedea figura nr.10).
  

Figura nr. 10 - Ferestra de dialog Advanced Filter

Exemplul 1

Folosim exemplul din figura de la Teme propuse. Sa presupunem ca dorim sa copiem toate înregistrarile studentilor care au media cuprinsa în intervalul [8.5, 9.5] în foaia de lucru curenta, începând cu celula I30. Câmpurile ferestrei de dialog Advanced Filter se vor completa dupa cum urmeaza: List range va contine întreaga lista ($A$3:$F$24), Criteria range va contine celulele criteriului de selectie ($H$19:$I$20) - vezi figura nr.11, iar Copy to va contine locatia unde se va realiza copierea ($I$30).

Figura nr.11 - Exemplu de domeniu de criterii

Observatie.

Daca se doreste reafisarea tuturor înregistrarilor din lista, se alege optiunea Data - Filter - Show All.

f) Rezumarea datelor

În fiecare grup de înregistrari care au aceeasi valoare pentru un câmp dat, asupra valorilor unor câmpuri specificate se pot aplica diverse functii de sinteza (medie, numarare, implicit suma). Astfel, se pot realiza simplu totaluri si subtotaluri. În locul functiei suma, se pot specifica si alte functii: numar de elemente (Count), medie (Avg), maxim (Max), minim (Min) etc. Operatia se poate repeta pentru valorile altui câmp; astfel se va obtine un rezumat al listei initiale.

Anterior, înregistrarile trebuie sortate dupa câmpul/câmpurile ale carui/caror grupe de valori egale se urmaresc.

Daca se doreste sa se realizeze calcule asupra unei liste folosind un domeniu de conditii (adica sa se prelucreze doar liniile care îndeplinesc acele conditii), se vor utiliza functiile specifice bazelor de date: DSUM (suma pentru o baza de date), DAVERAGE (medie pentru o baza de date), DCOUNT (numarare pe o baza de date) etc. Aceste functii sunt caracterizate de trei parametri: domeniul bazei de date, câmpul pe care se face calculul si domeniul de criterii care selecteaza liniile implicate în calcul.

În exemplul din figura nr.7 se calculeaza numarul de note cuprinse în fiecare din intervalele: [0.5, 1.5), [1.5, 2.5), [2.5, 3.5), ...., [8.5, 9.5), [9.5, 10]. În acest scop se utilizeaza functia DCOUNT specificând pe rând: domeniul asupra caruia se aplica functia, câmpul dupa care se realizeaza numararea, precum si domeniul de criterii care contine conditia de îndeplinit pentru înregistrarile asupra carora se aplica operatia.

g) Tabele de sinteza

Tabelele de sinteza sau tabelele pivot (Pivot Table) realizeaza sintetizarea datelor dintr-o baza de date Excel din mai multe perspective. Utilizatorul poate alege câmpurile care sa constituie titluri de pagina, de linie sau de coloana, sau câmpuri care sa fie utilizate în realizarea unor calcule.

Vom explica utilizarea tabelelor pivot pe un exemplu concret. Sa presupunem ca avem baza de date Studenti care contine înregistrarile din figura nr.12 si ca dorim sa aflam câti studenti din fiecare grupa sunt deja memorati în baza de date.

Figura nr. 12 - Baza de date Studenti

Se vor efectua urmatorii pasi:

• 
  se lanseaza în executie comanda Pivot Table din meniul Data;
  
• 
  în fereastra de dialog deschisa (Step 1 of 4), se alege optiunea Microsoft Excel List or Database si se va actiona butonul Next;
  
• 
  în fereastra de dialog deschisa (Step 2 of 4), câmpul Range se completeaza cu seria de celule care contin datele ce vor fi analizate (în cazul nostru, seria este A1:D12) si actioneaza butonul Next;
  
• 
  în fereastra de dialog deschisa (Step 3 of 4), se gliseaza butonul Grupa în câmpul DATA. Se va afisa Sum of GRUPA (functia care realizeaza însumarea datelor din coloana GRUPA);
  
• 
  deoarece nu dorim însumarea datelor din coloana GRUPA, vom realiza un dublu clic cu mouse-ul pe butonul Sum of GRUPA din câmpul DATA. Se afiseaza o noua fereastra de dialog care permite alegerea functiei Excel dorite (în acest caz - Count pentru numarare);
  
• 
  dupa apasarea butonului OK si închiderea ultimei ferestre de dialog, în câmpul DATA va fi afisat butonul Count of GRUPA;
  
• 
  se gliseaza butonul GRUPA în câmpul ROW (linie) pentru a utiliza aceasta denumire ca titlu de linie. Deasupra câmpului ROW se va afisa butonul GRUPA (figura nr.13);
  
• 
  se actioneaza butonul Next pentru a trece la urmatorul pas;
  

Figura nr.13 - Construirea tabelului pivot

• 
  în fereastra de dialog nou deschisa (Step 4 of 4), câmpul Pivot Table Starting Cell se completeaza cu adresa celulei la care va începe afisarea tabelului pivot (F1) în cadrul foii de calcul, iar câmpul Pivot Table Name va contine numele tabelului pivot;
  
• 
  dupa actionarea butonului Finish, foaia de calcul va aspectul din figura nr.14.
  

Figura nr. 14 - Aspectul final al foii de calcul