Laborator Excel BD 1 by k01nsY8

VIEWS: 14 PAGES: 6

									                                                  EXCEL

                        LISTE DE GESTIUNE (BAZE DE DATE)

                                         Lucrare de laborator

A.
1. Să se introducă următoarea bază de date în EXCEL:

  Locul                                              Cod               Data                        Salar de
                 Nume            Prenume                                              Filiala
 naşterii                                          personal          angajării                       bază
Sibiu        Marin              Ion               01423564        01-Jan-1998            1         1700000
Braşov       Albu               Alin              01543210        15-May-2000            2         1300000
Sibiu        Mitrică            Mircea            01467890        01-Feb-1999            2         2000000
Braşov       Moldovan           Laura             02572601        05-Apr-2001            3         2100000
Cluj         Ciurea             Teo               01852720        20-Oct-1997            1         1900000
Sibiu        Popescu            Geo               01432109        15-Jul-1999            3         2500000
Mediaş       Aleman             Dana              02795412        14-Nov-2000            2         1500000
Sibiu        Barbu              Călin             01456213        15-Sep-1999            1         2200000
Braşov       Albu               Mircea            01567890        18-Dec-2000            2         2100000

2. Să se efectueze următoarele operaţii cu această bază de date:
a) Să se sorteze lista alfabetic după nume şi prenume;
b) Să se sorteze lista după filiala la care sunt angajaţi, în ordine crescătoare;
c) Să se sorteze lista după salariul de bază, în ordine descrescătoare;
d) Să se modifice ordinea câmpurilor din baza de date astfel încât ele să fie:
   Nume, Prenume, Cod personal, Locul naşterii, Filiala, Salar de bază, Data
   angajării;
e) Să se sorteze baza de date întâi după locul naşterii, apoi după nume şi apoi
   după prenume;
f) Ştiind că în codul personal, prima cifră reprezintă sexul (1 = M, 2 = F) şi
   următoarea cifră reprezintă localitatea, să se sorteze lista întâi după localitate
   şi apoi după sex, folosind codul personal.
3. Să se filtreze lista folosind următoarele criterii:
a) lista filtrată să conţină numai angajaţii din filiala 1 [şi câmpurile: Nume,
    Prenume, Filiala];
b) lista filtrată să conţină numai angajaţii din filiala 1 care au salariul de bază
    mai mare decât 1800000 [şi câmpurile: Nume, Prenume, Salariul de bază]*;
c) lista filtrată să conţină angajaţii cu o vechime mai mare de 2 ani şi câmpurile:
    Nume, Prenume, Filiala;
d) lista filtrată să conţină angajaţii cu au sexul feminin şi câmpurile: Nume,
    Prenume, Filiala;


 Paranteze dreaptă presupune că se va rezolva cerinţa pct. respectiv în două cazuri: cu toate câmpurile din listă
afişate şi apoi numai cu câmpurile cerute în paranteză, afişate.
                                                        1
e) lista filtrată să conţină angajaţii care s-au născut în Sibiu sau Cluj;
f) lista filtrată să conţină angajaţii care au salariul cuprins în intervalul
    [1500000, 2000000];
g) lista filtrată să conţină angajaţii cu salariul între [1500000, 2200000] şi care
    au vechimea mai mică de un an.
4. Să se facă un subtotal cu veniturile obţinute din salariul de bază, după filiale.
B. Se dă baza de date de mai jos:

        Institutul          Filiala    Sponsorizări     Venituri proprii      Cheltuieli
Fizică atomică                 1         2000 $            5000 $              3000 $
Relaţii internaţionale         1         4000 $            4000 $              3500 $
Chimie                         2          700 $            2000 $              1000 $
Fizică atomică                 2         3000 $            1000 $              2300 $
Chimie                         2         2500 $            2300 $              1500 $
Chimie                         3          800 $            4500 $              2000 $
Fizică atomică                 3         1200 $            1000 $               800 $
Relaţii internaţionale         2          900 $            2000 $              1500 $

a) Să se sorteze baza de date după Institut, apoi după Filială şi apoi după
   valoarea veniturilor nete realizate;
b) Să se facă un subtotal care să cuprindă pe institute şi filiale valoarea
   veniturilor proprii şi a veniturilor nete;
c) Să se afişeze Institutul şi Filiala care realizează venituri nete mai mari de
   2500 $ şi au abţinut sponsorizări mai mari sau egale cu 2000 $.
C. Se dă baza de date de mai jos:

                               Produs
        Luna                                          Firma                Preţ unitar
                            achiziţionat
Ianuarie                 Anvelope               Tofan                      1500000
Martie                   Anvelope               Victoria                    700000
Februarie                Baterii                Italia                      650000
Mai                      Baterii                România                     500000
Septembrie               Furtune                Fartec                       25000
Decembrie                Furtune                Victoria                     14000
Aprilie                  Ulei                   Carrier                     100000
Octombrie                Ulei                   Castrol                     150000

Să se sorteze această bază întâi după produsul achiziţionat şi apoi după luna de
achiziţie, în ordinea normală a lunilor anului (Ianuarie, Februarie, etc.)




                                            2
                                           REZOLVARE

A.
1. Introducerea bazei de date.
Aceasta trebuie să fie despărţită de restul conţinutului foii prin rânduri şi
coloane goale situate deasupra, dedesubtul, la stânga şi la dreapta bazei. Se pot
folosi 2 metode.
Metoda I. Se introduce normal de la tipărire primul rând şi conţinutul bazei,
folosind facilitatea AutoComplete (un nume deja scris apare pe video invers, la
scrierea primei litere).
Metoda II. Se scrie primul rând ce conţine numele câmpurilor. Se marcheză
zona ce va conţine baza de date, primul articol din bază, şi se apelează Data,
Form, care va deschide un formular în care se poate introduce conţinutul bazei,
articol, apăsând butonul New, după fiecare articol introdus. Deplasarea între
articolele introduse se face prin Find Prev şi Find Next, iar închiderea
formularului cu Close.
2. a) Ne plasăm în baza de date şi marcăm conţinutul foii şi numele câmpurilor
şi apelăm Data, Sort, care acţionează similar cu sortarea tabelelor în Word.
Alegem
             Sort by     Nume                        Ascending

                 Then by       Prenume                         Ascending

b) Marcăm baza, dăm Data, Sort şi apoi alegem

                 Sort by       Filiala                          Ascending

c) Marcăm baza, dăm Data, Sort şi apoi alegem

                 Sort by      Salar de bază                     Descending

d) Ataşăm ca ultimă linie a bazei de date o linie care conţine oridnea
   câmpurilor, respectiv

 Locul                                                    Data
               Nume        Prenume       Cod personal               Filiala   Salar de bază
naşterii                                                angajării

           conţinutul bazei


   4              1           2               3            7          5            6



       ultima linie suplimentară
Marcăm baza şi ultima linie suplimentară şi dăm Data, Sort. Alegem Options.
Din ecranul de dialog deschis, din zona Orientation, alegem  Sort left to
right. După apăsarea butonului OK, ne întoarcem în ecranul Sort şi specificăm
                                                  3
la Sort by - numărul liniei pe care îl are ultima linie suplimentară introdusă
(care este ultima în lista derulantă de la Sort by).
e) Marcăm baza, dăm Data, Sort şi alegem

            Sort by          Locul naşterii             Ascending

            Then by                   Nume              Ascending

            Then by                  Prenume            Ascending

f) Ataşăm o coloană suplimentară numită Sex, calculată din coloana Cod
   personal cu formula = LEFT (adresa celulei în care este codul personal al
   primei înregistrări, 1).
Este preferabil să se numească câmpurile primei înregistrări din baza de date cu
numele câmpurilor date în prima linie a bazei astfel:

  Locul       Nume      Prenume           Cod          Data      Filiala    Salar
 naşterii                               personal    angajării
  Sibiu       Marin       Ioan          1423564    01 Ian 1998     1       1700000

Marcăm domeniul specificat mai sus, dăm Insert, Name, Create şi alegem Top
row.
La fel putem ataşa o coloană în care introducem codul oraşului cu formula:
= MID (adresa codului personal a primei înregistrări, 2, 1), după care facem
sortarea după coloanele Sex şi Cod oraş.
3. Filtrarea o putem face prin mai multe metode:
a) Metoda I: Dacă dorim ca filtrarea să se facă exact în locul în care se află
baza, folosim Data, Filter, AutoFilter şi, din listele derulante ataşate
câmpurilor, alegem pentru câmpul Filiala, valoarea 1. Pentru a reveni la baza
iniţială, dăm din lista All sau demarcăm din meniul Data opţiunea AutoFilter.
Metoda II: Folosind Data, Form şi apăsând butonul Criteria, după care, la
câmpul Filială specificăm valoarea 1 şi apăsăm Find Next. Deplasarea între
articolele filtrate se face cu Find Prev şi Find Next.
Metoda III: Folosind Advanced Filter, dacă dorim ca lista filtrată să o obţinem
în altă parte în foaia de calcul sau dacă dorim să fie afişate numai anumite
câmpuri. Astfel pentru afişarea Nume, Prenume, Filiala scriem undeva în foaia
de calcul numele acestor câmpuri, exact cum apar ele în baza de date, marcăm
apoi zona suficient de mare pentru a include datele filtrate (şi care începe cu
numele câmpurilor). Acesta va reprezenta domeniul Extract.
În prealabil, în altă parte a foii de calcul definim domeniul Criteria sub forma:

                           Filiala

                                 1


Apelăm apoi Data, Filter, Advance Filter, în care specificăm
                                             4
       Copy to another location
      List range      - domeniul bazei de date (marcat prin tragere cu mousul)
      Criteria rang - domeniul de criterii
      Copy to         - domeniul Extract
Se poate alege şi opţiunea  Unique Record pentru îndepărtarea duplicatelor.
b) Idem pct. a), dar criteriul se aplică câmpurilor Filiala şi Salar de bază.
Metoda I: Autofilter:

             Filiala                         Salar de bază   ▼
                1

                                                 Customize



Pentru Salar de bază alegem opţiunea Customize şi apoi:

      Salar de bază

     is greater than     ▼                 1800000

Metoda II: Form - nu se poate folosi
Metoda III: Advanced Filter
Criteriul se scrie sub forma:

                       Filiala   Salar de bază
                          1       >1800000

c) Folosim Advanced Filter şi criteriul calculat numit Vechime.

            Vechime
            (NOW()-Data angajării)/365 > 2

d) Folosim Advanced Filter,criteriul calculat se numeşte Sex şi domeniul
Extract format din câmpurile: Nume, Prenume, Filiala.
e) Data, Filter, Autofilter
f) Data, Filter, Autofilter, la Custom se alege:
La Show row where: is greater than 1500000
                   And
                      is less than     2000000
g) Idem pct.f), dar se indică la câmpul Vechime <1

4. Se selectează baza de date. Apoi din meniul Data, comanda Subtotals.
       At each change in:       Filiala
       Use function:            Sum
       Add subtotal to:         Salar de bază
                                       5
B. Se va introduce un nou câmp "Venituri nete".
Venituri nete = Venituri proprii + Sponsorizări - Cheltuieli
Deci, se scriu formulele de calcul respective.
a) Data, Sort
      Sort by:     Institut
      Then by: Filială
      Then by: Venituri nete
b) Se selectează baza de date. Apoi Data, Subtotals
      At each change in:         Institut
      Use function:              Sum
      Add subtotal to:           Venituri proprii
                                 Venituri nete
c) Data, Filter, Autofilter




                                       6

								
To top