Excel-Baza de date

Gestiunea bazelor de date i optimizare n Microsoft Excel

Gestiunea bazelor de date i optimizare n Microsoft Excel

Oradea, 2011

Partiumi Kiad

Nagy Lajos, Csipks Margit, Tth Rka, Kelemen Erzsbet, Plhegyi-Farkas Lszl

Acest manual a fost realizat n cadrul Programului de cooperare transfrontalier Ungaria-Romnia 20072013 (www.huro-cbc.eu), cu finanarea Uniunii Europene i cofinanarea Fondului Euro-pean de Dezvoltare Regional, respectiv a Republicii Maghiare i a Romniei, derulat n cadrul proiectului Computational support for the decision making of the cross-border SMEs (Asistare computerizat n activitatea decizional a ntreprinderilor mici i mijlocii din zona transfrontalier) CaDS-SMsE HURO/0901/255/2.5.1

Descrierea CIP a Bibliotecii Naionale a RomnieiGestiunea bazelor de date i optimizare n Microsoft Excel / Nagy Lajos, Csipks Margit, Tth Rka, .... - Oradea : Partium, 2011 ISBN 978-606-8156-28-6I. Nagy, LajosII. Csipks, MargitIII. Tth, Rka004.42 EXCEL

REDACTORZAKOTA Zoltn

DEsign BCS Bence

TEhnOREDACTAREDr. TARNCZi Tibor

iSBN-978-606-8156-28-6

Dou ri, un scop, succes comun!

1

CUPRINS

CUPRINS .................................................................................................................................. 1

INTRODUCERE ......................................................................................................................... 4

1. NOIUNI FUNDAMENTALE PRIVIND BAZELE DE DATE.......................................................... 5

1.1. BAZE DE DATE RELAIONALE, NORMALIZARE .................................................................................. 5

1.2. TABELUL - BAZ DE DATE ......................................................................................................... 11

1.2.1. Crearea unui tabel - baz de date din date existente ............................................... 12

1.2.2. Crearea unui tabel-baz de date gol ........................................................................ 16

1.2.3. Introducerea datelor cu formular de introducere a datelor (Form) .......................... 17

1.3. TRANSFORMAREA TEXTULUI N TABEL ........................................................................................ 20

1.4. ELIMINAREA REPETIIILOR DIN BAZA DE DATE............................................................................... 24

1.5. FORMULE, FUNCII, REFERINE LA CELULE (ADRESE)...................................................................... 28

1.5.1. Formule i funcii ...................................................................................................... 29

1.5.2. Referine (adrese) relative, absolute i mixte ........................................................... 34

1.5.2.1. Ce este referina structurat? ......................................................................................... 42

1.6. VALIDAREA DATELOR N TABELE ................................................................................................ 44

1.7. PROTECIA FIIERULUI I PROTECIA FOII DE LUCRU....................................................................... 65

2. CUTARE I FILTRARE N BAZA DE DATE ............................................................................ 71

2.1. CUTARE, NLOCUIREA DATELOR I SALTURI N BAZA DE DATE ......................................................... 71

2.2. UTILIZAREA FUNCIILOR DE CUTARE ......................................................................................... 77

2.2.1. Funcia ADDRESS() .................................................................................................... 78

2.2.2. Funcia VLOOKUP() ................................................................................................... 80

2.2.3. Funcia de cutare HLOOKUP() ................................................................................. 83

2.2.4. Funcia MATCH() ....................................................................................................... 87

2.2.5. Funcia INDEX() ......................................................................................................... 89

2.2.6. Funcia INDIRECT() .................................................................................................... 94


2

2.2.7. Funcia LOOKUP() ..................................................................................................... 96

2.2.8. Funcia GETPIVOTDATA() ....................................................................................... 100

2.2.9. Funciile COLUMN() i COLUMNS() ......................................................................... 103

2.2.10. Funciile ROW() i ROWS() .................................................................................... 106

2.2.11. Funcia AREAS() .................................................................................................... 108

2.2.12. Funcia TRANSPOSE() .......................................................................................... 110

2.2.13. Funcia CHOOSE() ................................................................................................. 111

2.3. ARANJAREA DATELOR ........................................................................................................... 113

2.4. FILTRAREA DATELOR ............................................................................................................. 117

2.4.1. Utilizarea filtrrii automate.................................................................................... 118

2.4.2. Filtrarea special sau complex (filtrare dirijat) .................................................. 121

2.5. FUNCII AFERENTE BAZELOR DE DATE ...................................................................................... 124

2.6. APLICAIILE FUNCIILOR AFERENTE BAZELOR DE DATE ................................................................. 131

2.7. GRUPAREA DATELOR I SUBTOTALURI ...................................................................................... 139

3. ANALIZA SCENARIILOR ..................................................................................................... 146

3.1. ANALIZE TIP CE-AR FI DAC... (WHAT IF) ............................................................................... 148

3.1.1. Managerul de scenarii ............................................................................................ 148

3.1.2. Tabelul de date (Data Table) .................................................................................. 156

3.2. CUTAREA VALORII INT CU SOLVER ...................................................................................... 162

3.2.1. Cutarea valorii int ............................................................................................. 163

3.3. SOLVER ............................................................................................................................. 165

3.3.1. Configuraiile de baz ale Solver ........................................................................... 165

3.3.2. Programarea linear n rezumat ............................................................................ 172

3.3.3. Prezentarea aplicaiilor Solver................................................................................ 174

3.3.3.1. Schema elaborrii unui model de programare linear n Excel .................................... 174

3.3.3.2. Aplicaii Solver .............................................................................................................. 192

4. AUTOMATIZAREA OPERAIILOR ...................................................................................... 207

4.1. AUTOMATIZAREA CU AJUTORUL MACROURILOR ......................................................................... 208


3

4.1.1. Inregistrarea unui macro .......................................................................................209

4.1.2. Rularea unui macro ................................................................................................214

4.1.3. Editarea macrourilor ...............................................................................................219

4.1.4. Crearea macrourilor cu ajutorul Microsoft Visual Basic .........................................221

4.1.5. tergerea unui macro .............................................................................................222

4.2. PROTECIE N CAZUL MACROURILOR ........................................................................................222

4.3. NTOCMIREA FORMULARELOR ................................................................................................225

BIBLIOGRAFIE .......................................................................................................................235

ANEXA ..................................................................................................................................239


4

Introducere

Se ntmpl adesea ca programele de administrare a bazelor de date i

programele de administrare a tabelelor s fie tratate ca sinonime, dei diferena

dintre ele este semnificativ. Programele de administrare a bazelor de date

descriu mulimi de date cu corelaiile complexe existente ntre ele, pe cnd

programele de administrare a tabelelor se ocup doar cu prezentarea datelor ntr-

o anumit form. Totui exist i elemente comune, ex. i n programele de

administrare a tabelelor exist posibilitatea (re)organizrii datelor dup anumite

criterii (ex. dup anumite coloane), sau putem ntocmi tabele pivot, adic putem

selecta anumite elemente dintr-un tabel, sau putem ntocmi un nou tabel pe baza

unei caracteristici descrise ntr-un alt tabel utiliznd diferite funcii. Deci

programele de administrare a tabelelor i cele de administrare a bazelor de date

nu se deosebesc ntru totul. De exemplu, programul Microsoft Excel, unul dintre

cele mai rspndite programe de administrare a tabelelor (calcul tabelar) are i

funciuni de administrare a bazelor de date.

n acest volum vom trece n revist noiunile fundamentale referitoare la bazele

de date, vom analiza modul de utilizare a programului Excel pentru elaborarea

bazelor de date i modul cum se pot realiza diferite analize de date cu ajutorul

acestuia. La sfritul crii vom face cunotin cu ctevamodaliti de

automatizare a sarcinilor de rutin zilnic, pentru a ne accelera i eficientiza

activitatea.


5

1. Noiuni fundamentale privind bazele de date

1.1. Baze de date relaionale, normalizare

Baza de date relaional se definete ca fiind totalitatea datelor realizate prin

aplicarea principiului modelului relaional de date, adic o mulime finit a aa

numitelor relaii definite n cadrul sistemului de noiuni al modelului relaional

de date. Fundamentele modelului au fost publicate n anul 1970 de ctre Edgar

F. Codd, angajat al IBM, n articolul su A Relational Model of Data for Large

Shared Data Banks.Cel mai mare avantaj al modelului l constituie simplitatea i

flexibilitatea. Baza de date este format din tabele (colecii de date)

bidimensionale; rndurile se numesc nregistrri (record-uri), iar coloanele se

numesc cmpuri de nregistrri (record-uri). Rndurile conin date

interdependente din punct de vedere logic, iar coloanele conin caracteristicile/

atributele datelor. O baz de date const, n general, n mai multe tabele/colecii

de date. Baza de date relaional realizeaz corelarea tabelelor prin intermediul

cmpurilor cu date corelate (cheie). (De exemplu, corelaia dintre dou cmpuri

se realizeaz dac acestea au coloane cu aceeai denumire.)

Baza de date relaional poate fi elaborat cu ajutorul soft-ului de gestiune a

bazelor de date, special dezvoltat n acest scop, respectiv, n cazul utilizrii

limbajului standard de acces i interogare (SQL - Structured Query Language)

poate fi realizat, editat i interogat prin comenzi scrise.


6

n modelul relaional de baz de date, mulimea relaiilor i, corespunztor,

fiecare element al relaiei (rnd) este unic. Gestionarii bazelor de date

relaionale tipice utilizeaz, totui, trei modificri: pe de o parte relaiile nu sunt

descrise ca mulimi, ci drept saci (engl.: bag, totalitatea elementelor

neorganizate, n care se permit i repetiii), pe de alt parte nu este permis ca o

relaie s conin dou atribute (coloane) cu denumiri identice, iar n al treilea

rnd este posibil utilizarea valorilor nule (goale, necunoscute).

Crearea bazelor de date relaionale constituie domeniul proiectrii bazelor de

date.

Normalizarea nseamn sistematizarea datelor aflate n baza de date. Ca

rezultate, aceasta poate duce la crearea de tabele i crearea de corelaii ntre ele,

pe baza unor reguli. Scopul regulilor este flexibilizarea datelor (ex. eliminarea

redundanelor i corelaiilor inconsistente).

Datele redundante ocup, inutil, suprafee ale hard disk-ului, provocnd

probleme de ntreinere. Dac trebuie modificate date aflate n mai multe locuri,

modificarea trebuie realizat identic n fiecare loc. De exemplu, dac trebuie

modificat o anumit informaie referitoare la un partener, modificarea este mai

simpl dac informaia respectiv figureaz numai n tabelul Parteneri, nu i

n alt parte a bazei de date.

Ce este corelaia inconsistent? Dac un utilizator este interesat de adresa unui

partener, e de la sine neles s-o caute n tabelul Parteneri, dar nu ar fi logic ca


7

salariile angajailor partenerilor s fie stocate n acelai tabel. Salariul

angajatului aparine de angajai, deci trebuie inclus n tabelul Angajai.

Urmarea corelaiilor inconsistente poate fi accesul dificil la date, fiindc drumul

de acces la acestea poate fi incomplet sau greit.

Prin normalizare ne propunem s parcurgem un proces n trepte n urma cruia

s formm grupuri de date legate organic ntre ele i care depind n ntregime de

identificatorul unic (cheia primar) al grupului. Normalizarea bazelor de date are

unele reguli, numite i forme normale. Dac baza de date se supune regulii nr. 1,

aceasta se afl n forma normal 1. Dac se supune primelor trei reguli, se afl n

forma normal 3. Astfel pot fi create tabelele tip NF1, NF2, NF3 (BCNF), NF4

i NF5. n practic se utilizeaz de obicei tabelele NF3.

n cazul formelor normale de rang superior, interdependena datelor se poate

exprima prin corelaia funcional: valoarea t(2) a unui tabel T este n corelaie

funcional cu valoarea t(1) dac fiecrei valori t(1) a tabelului i corespunde

numai o singur valoare a lui t(2). Valorile t(1) i t(2) pot fi i complexe. Dac

t(2) depinde funcional de t(1) dar nu depinde de nici o submulime a lui t(1),

atunci spunem c t(2) este n ntregime funcional dependent de t(1), iar t(1) se

numete determinantul lui t(2). Un tabel este de forma NF2 dac este n NF1 i

toate acele atribute care nu sunt chei primare sau pri ale acesteia, sunt total

dependente funcional de cheia primar. Putem vorbi de NF3 dac tabelul are

forma NF2 i toate dependenele provin de la cheia primar. Un tabel poate fi de

forma BCNF numai dac determinanii coninui n el sunt n acelai timp i

chei primare (ex. Popescu i colaboratorii, 2005).


8

Uneori problemele reale nu pot fi aduse n coresponden total cu regulile i

specificaiile. Normalizarea presupune n general, crearea de noi tabele, ceea ce

este o munc obositoare. Dac nu ndeplinim una dintre primele trei reguli ale

normalizrii, trebuie s ne convingem c aplicaia elimin eventualele probleme

aprute (ex. redundana datelor i inconsistena corelaiilor).

NF1 (tabel normal de forma 1) este tabelul n al crui cmpuri apar numai

atribute simple. Acesta nu conine dou rnduri asemenea, ntotdeauna va exista

cte un atribut sau mai multe care le deosebesc.

n acest caz, scopul normalizrii este eliminarea grupurilor repetate i crearea

cte unui nou tabel pentru fiecare mulime de date corelate.

Tabelulul 1.: Tabel cu repetiii

Produs Locul depozitrii

Distribu ie logic

Furnizor 1

Furnizor 2

A Magazia 1 D30/R H&K Futr

B Magazia 2 Hala A Sebes Baracs

Tabelul poate avea numai dou dimensiuni. ntruct unui produs i corespund

mai muli furnizori, produsele trebuie enumerate ntr-un tabel separat. La

nregistrrile de mai sus ale tabelului 1, Furnizor 1 i Furnizor 2 constituie

defecte de proiectare.

n tabelele de calcul se utilizeaz adesea i a treia dimensiune, dar n tabelele de

date acest lucru nu este permis. O alt abordare a problemei este c nu pot


9

coexista n acelai tabel, cele dou laturi ale unei corelaii tip 1 la n (1 la multe).

De aceea vom elimina repetiia (grupul repetat) prin crearea unui tabel de tip

NF1 (Tabel 2).

NF2 (forma normal 2)se creeaz cu scopul eliminrii redundanei datelor. n

acest scop, pentru grupele de valori referitoare la mai multe nregistrri vom

crea tabele separate. Tabelele le com interconecta cu chei exterioare.

nregistrrile vor putea depinde numai de respectivele chei primare ale tabelelor

(dac este nevoie,cheii pot fi combinate).

Tabelul 2.: Primul tabel fr repetiii


Distribu ie logic

Furnizor

A Magazia 1 D30/R H&K

A Magazia 1 D30/R Futr

B Magazia 2 Hala A Sebes

B Magazia 2 Hala A Baracs

n tabelul 2, fiecare produs are mai muli furnizori. Furnizorul nu depinde

funcional de produs (cheie primar), de aceea relaia nu este n forma normal

2.

Prin separarea Tabelului 2 am obinut NF2 (tabelul 3 i tabelul 4).

NF3 (a treia form normal) se realizeaz prin filtrarea datelor pentru

eliminarea celor care nu depind de cheie.


10

Tabelul 3.: Tabelul Produse


Distribu ie logic

A Magazia 1 D30/R

B Magazia 2 Hala A

n tabelul 3, distribuia logic nu depinde funcional de locul depozitrii.

Rezolvarea nseamn s separm acest cmp de produse i s nfiinm un nou

tabel pentru locul depozitrii (tabelele 5 i 6).

Tabelul 4.: Originea produselor

Produs Furnizor

A H&K

A Futr

B Sebes

B Baracs

Tabelul 5.: Noul tabel Produse

Produsul Locul depozitrii

A Magazia 1

B Magazia 2

Atenie! Dei utilizarea NF3 este necesar teoretic n orice situaie, n practic

aceasta poate duce la probleme. De exemplu, dac avem un tabel pentru Clieni

i am vrea s eliminm toateinterdependenele posibile dintre cmpuri, ar trebui


11

s creem tabele separate pentru localiti, coduri potale, reprezentani

comerciali, birouri de aprovizionare i oricare alt element repetitiv din

nregistrri. Dar numrul mare de tabele de mici dimensiuni poate reduce

eficacitatea, iar numeroasele fiiere deschise ocup, inutil, memoria. De aceea

este recomandat s se utilizeze a treia form normal numai n cazul n care

datele se schimb des. Dac rmn cteva cmpuri dependente, aplicaia trebuie

astfel proiectat nct s cear utilizatorului, verificarea cmpurilor conexe la

fiecare modificare.

Tabelul 6.: Tabelul Locuri de depozitare

Locul depozitrii

Distribu ie logic

Magazia 1 D30/R

Magazia 2 Hala A

1.2. Tabelul - baz de date

Dei n Excel nu avem posibilitatea de a utiliza baze de date relaionale, totui

putem efectua operaii asemntoare celor de gestionare a bazelor de date, astfel

putnd s ne eficientizm activitatea de zi cu zi. Tabelul baz de date este

bidimensional, aa cum s-a descris n capitolul 1.1. n versiunile anterioare ale

Excel, acestea se numeau liste, n timp ce, n Excel 2007 au primit denumirea de

tabele. Cu tabelele astfel realizate pot fi efectuate diverse operaii specifice

bazelor de date, organizri, nsumri, fiindc structura tabelelor respective este

asemenea bazelor de date, adic este format din nregistrri i cmpuri. Cnd


12

creem un tabel pe o foaie de lucru n aplicaia Microsoft Excel, putem trata i

analiza datele din tabel n mod independent fa de datele dinafara tabelului.

Putem filtra coloanele tabelului, putem s-i adugm un rnd pentru sume, l

putem formata.

De fapt nregistrarea (record) este un rnd al tabelului i conine caracteristicile

unei uniti (ex. denumirea firmei, adresa, numrul de telefon, cifra anual de

afaceri etc.). Cmpul reprezint o coloan a tabelului, deci cuprinde datele de

acelai fel din toat baza de date (ex. coloana Denumirea firmei va cuprinde

toate denumirile firmelor incluse n baza de date).

Deci n Excel, rndurile tabelului vor fi nregistrrile, iar denumirile coloanelor

vor fi denumirile cmpurilor.

Se pot crea baze de date n mai multe feluri:

Definim drept baz de date, un tabel completat cu denumiri de cmpuri i

cu date;

Creem o baz de date goal, apoi o completm;

Utilizm un formular de introducere a datelor.

1.2.1. Crearea unui tabel - baz de date din date existente

Dac vrem s transformm n baz de date un tabel completat, selectm meniul

INSERARE (INSERT), n care din cmpul Tabele (Tables) selectm iconia

Table.


13

Exemplu 1.: S transformm tabelul cu lista angajailor n baz de date.

Selectm tabelul, facem clic pe iconia Table aflat n banda de meniu

INSERT, pe locul al doilea din stnga. Dup clic, zona selectat va fi

nconjurat de o linie ntrerupt i apare o mic fereastr cu titlul Create table.

Aici, dac este necesar, mai putem corecta selecia, respectiv, selectnd

opiunea My table has headers, denumirile cmpurilor se atribuie primului

rnd al tabelului - baz de date creat (fig. 1.).

Fig. 1.: Crearea tabelului - baz de date


14

Ca rezultat al operaiunii obinem tabelul-baz de date. n primul rnd al

tabelului vom gsi denumirile cmpurilor (Numele angajatului, Localitatea

etc.), iar n celelalte rnduri gsim nregistrrile (Fig. 2.).

Fig. 2.: Tabelul - baz de date

Dac se face clic pe oricare dintre elementele tabelului, la sfritul benzii de

meniu apare un nou element de meniu (Table Tools/DESIGN) (Fig. 3.).

n cele ce urmeaz, putem redenumi tabelul (n starea de baz, acesta se numete

Table1/TableX, aa cum se vede n partea stng mijloc a benzii de meniu (Fig.

3.), iar n exemplul nostru l redenumim Angajai.

Putem de asemenea s extindem baza de date, adugnd rnduri i coloane noi.

Acest lucru se realizeaz insernd date noi n rndul urmtor sau n coloana

urmtoare. n cazul coloanelor noi nu trebuie scpat din vedere c n primul rnd

se nscrie denumirea noului cmp (identificarea coloanei) (Fig. 5.). Astfel, baza

de date se extinde automat cu un rnd sau o coloan.


15

Fig. 3.: Banda de meniu Table Tools / Design

Fig. 4.: Redenumirea tabelului


16

Fig. 5.: Introducerea de date noi

Excel recunoate automat, tabelul creat ca n exerciiul anterior, totui trebuie s

avem grij ca ntre tabelul existent i noul rnd (sau noua coloan) s nu existe

rnd liber (coloan liber), fiindc spaiile libere delimiteaz baza de date. Pe o

foaie de lucru pot fi create mai multe baze de date, dar ne ferim de multe

neplceri dac pe o foaie de lucru stocm doar una singur.

1.2.2. Crearea unui tabel-baz de date gol

Pentru a ntocmi un tabel gol tip baz de date, selectm o celul de pe foaia de

lucru Excel, facem clic pe iconia Table din banda de meniu INSERT/Tables.

Ca urmare obinem un tabel format din dou celule (Fig. 6.). Denumirea

coloanei o nlocuim cu denumirea dorit i putem insera noi denumiri de celule

pentru a extinde tabelul conform necesitilor noastre. Dup aceea putem ncepe

introducerea nregistrrilor. i n acest caz trebuie avut grij s nu rmn

rnduri goale n baza de date.


17

Fig. 6.: Tabel gol

1.2.3. Introducerea datelor cu formular de introducere a datelor (Form)

Formularele (form, n lb. englez) ofer o metod simpl de completare a unui

domeniu de date sau pentru completarea n ntregime a tabelului, respectiv

verificarea coninutului fr rulare orizontal. Utilizarea formularului de

introducere a datelor uureaz mult sarcinile de introducere a datelor, fiindc nu

mai este necesar trecerea de la o coloan la alta cnd vrem s accesm mai

multe date dect ncap pe ecran. Este raional s utilizm formularul de

introducere a datelor atunci cnd e suficient un formular simplu, n care

cmpurile sunt prezentate ca etichete, iar funciile de formular, speciale sau

individuale, ex. cmp de listare (cu derulare pe vertical) sau sgeata pentru

deplasare ntre celule nu sunt necesare.

n versiunea de baz, butonul form nu se gsete n bara de instrumente. Se

poate include n meniul de comenzi rapide prin clic pe CUSTOMIZE QUICK

TOOLBAR/MORE COMMANDS . Se caut iconia Form prin Choose

commands from, cutnd n All commands i fcnd clic pe iconia Form.. .


18

(fig. 7. i fig. 8.), apoi pe Add. (nota trad.: iconia va aprea pe bara de comenzi

rapide, sub bara de instrumente).

Introducerea datelor este foarte simpl. Selectm oricare celul a bazei de date i

facem clic pe butonul Form . Ca exemplu s vedem cum putem introduce date

n tabelul Vnzri. n urma selectrii butonului , lng tabelul nostru apare o

fereastr cu denumirile cmpurilor i cu datele primei nregistrri (dac baza de

date este goal, n mod normal, fr date). n partea din dreapta sus a

formularului vedem numrul rndului (nregistrrii) n care ne aflm i numrul

total de rnduri/ nregistrri al tabelului. Deci 1/5 nseamn c ne aflm pe

rndul nti, iar numrul total de rnduri/ nregistrri este 5. Dedesubt se gsete

butonul New. Fcnd clic pe acest buton, ne poziionm pe urmtorul rnd gol,

n care putem introduce date.

Fig. 7.: Introducerea butonului Form n meniul de comenzi rapide (pasul 1)


19

Fig. 8.: Introducerea butonului Form n meniul de comenzi rapide (pasul 2)

Cu butonul Delete se poate terge nregistrarea actual. tergerea efectuat nu se

poate revoca! Cu butonul Restore se pot terge datele din rndul tocmai

completat sau putem restaura datele dintr-o nregistrare pe care tocmai am

modificat-o. Butoanele Find Prev i Find Next se utilizeaz pentru deplasarea

pe vertical prin baza de date. Cu butonul Close se poate iei din baza de date,

salvnd n acelai timp, nregistrrile i modificrile efectuate(Fig. 9.).


20

Fig. 9.: Introducerea datelor cu ajutorul Form (formular de introducere

date)

1.3. Transformarea textului n tabel

Se poate ntmpla s putem descrca baze de date de pe internet, din editorul de

texte sau din alte surse, doar obinnd un format text. Transformarea manual n

tabel este complicat i solicit foarte mult timp. n astfel de situaii, expertul

din Excel, cu ajutorul cruia conversia se face rapid i destul de precis, este de

mare ajutor. Pentru aceasta deschidem banda de meniu DATA , iar din cmpul

DATA TOOLS , acionm comanda Text to columns. (fig. 10.)

Exemplu 2.: Vom arta paii conversiei printr-un exemplu. Tabelul 7. prezint

un fragment al unei liste de preuri pentru plci ceramice, pe care l-am copiat n

Excel de pe site-ul productorului. Textul copiat conine doar separarea pe

rnduri i n final, coninutul coloanei A trebuie aranjat n trei coloane.


21

Fig. 10.: Iconia comenzii Text to columns

Tabelul 7.: Tabelul original

Selectm domeniul A1:A5, apoi facem clic pe butonul Text to Table. Apare

fereastra Convert text to column Wizard (Fig. 11.).

Referitor la tipul datelor iniiale, expertul (wizard) ne propune imediat c datele

sunt delimitate prin vreun semn sau spaiu (tab), respectiv dac se aflau n

coloane de lime fix. n exemplul nostru, textul dat este delimitat prin semn

sau spaiu, aa va aprea i n fereastra Preview of selected data.

n al doilea pas al expertului trebuie s precizm semnul care delimiteaz datele.

Este simplu, fiindc la gsirea semnului de delimitare adecvat, n fereastra Data

Preview apare o linie despritoare. Acum putem verifica i corectitudinea

delimitrii.


22

Fig. 11.: Transformarea textului n tabel. Pasul 1

n pasul al treilea avem posibilitatea s stabilim tipul datelor din fiecare coloan

a noului tabel i locaia tabelului (Destination) (Fig. 13.). La precizarea tipului

de date din coloan (Column data format) este de ajuns opiunea General, dar se

poate ntmpla c la datele numerice din tabelul original, redate prin fracii

zecimale, separatorul nu este virgul, ci punct. n astfel de cazuri, prin clic pe

butonul de comand Advanced se poate stabili semnul separator, ct i numrul

zecimalelor, fiindc se poate ntmpla ca Excel s interpreteze unele valori

numerice ca date calendaristice, aceasta putnd fi o surs de erori n tabel.

Dac lucrm corect, obinem un tabel bine organizat, corespunztor scopului

nostru. (Tabelul 8.).


23

Fig. 12.: Transformarea textului n tabel. Pasul 2

Fig. 13. Pasul final al transformrii textului n tabel


24

Tabelul 8.: Rezultatul transformrii textului n tabel

1.4. Eliminarea repetiiilor din baza de date

n subcapitolul 1.1. ne-am ocupat cu bazele de date relaionale i cu diferitele

forme normale. Datele identice redundanele pot produce adesea probleme

n prelucrarea ulterioar, nemaivorbind de faptul c baza noastr de date se

poate umfla inutil.

Pentru eliminarea repetiiilor vom selecta tabelul care conine repetiii, facem

clic pe meniul DATA , apoi n banda DATA TOOLS facem clic pe comanda

Remove duplicates (Fig. 14.).

Fig. 14.: Iconia comenzii Eliminare dublu

Atenie! Dup eliminarea repetiiilor, datele originale se terg, de aceea se

recomand insistent, salvarea unei versiuni iniiale a bazei de date.


25

Exemplu 3.: Tabelul 9. conine datele despre vnzrile pe primul trimestru ale

unei reele de magazine en detail. Dac se studiaz tabelul, se constat c acesta

conine mai multe repetiii. Fiecare nregistrare conine datele de baz ale

magazinelor respective, ceea ce pe de o parte mrete inutil dimensiunile

tabelului, iar pe de alta face dificile eventualele modificri. S eliminm din

tabel, informaiile care nu sunt relevante din punctul de vedere al rezultatelor

economice i s construim un tabel al datelor despre vnzri.

Tabelul 9.: Datele despre vnzrile unit ilor comerciale


26

Ca prim pas s facem o copie a bazei de date originale, pe care s-o

denumimDate magazine (n. Trad: n acest caz,denumirea bazei de date este

denumirea foii de lucru Excel, n stnga jos a foii).

Pentru a crea o copie, se face clic dreapta cu mouse-ul pe titlul Date vanzari al

foii de lucru i se selecteaz comanda Move or copy. (Fig. 15.). Apoi se

selecteaz locul unde se va depozita copia i se bifeaz ptratul Create a copy.

(Fig. 16.). Apoi copia va fi redenumit:Date magazine.

Fig. 15.: Comanda Move or Copy (mutare sau copiere)

n etapa urmtoare s eliminm repetiiile din foaia de lucru Date magazine. Se

selecteaz tabelul i se face clic pe comanda Remove Duplicates din bara

DATA TOOLS a meniului DATA . n fereastra care se deschide, se bifeaz

numai ptratul dinaintea coloanei Bolt szma (Fig. 17.).


27

Fig. 16.: Realizarea unei copii a foii de lucru

Fig. 17.: Eliminarea repetiiilor


28

Dup eliminarea repetiiilor rmn 6 rnduri (Fig 18.). Tabelul conine dou

cmpuri fr legtur cu datele de baz: Perioada (Idszak) i Vnzri, mii Ft

(Forgalom EFt), care pot fi terse.

Fig. 18.: Lista unitilor comerciale dup eliminarea repetiiilor

n ultimul pas, din foaia Date vanzarise pot terge coloanele ef magazin, Jude,

Localitate i Adres. Drept rezultat al muncii noastre obinem dou tabele

normalizate, care nu conin date redundante (Table 10.).

1.5. Formule, funcii, referin e la celule (adrese)

Acest subcapitol nu este strns legat de capitolele anterioare, iar cele de mai jos

vor fi tratate pe larg n alte volume ale seriei. Scopul nostru este o scurt trecere

n revist a funciilor i a referinelor la celule (adrese), pentru a nlesni

nelegerea celor descrise n cartea de fa.


29

Tabelul 10.: Tabelele cu datele magazinelor i datele vnzrilor dup

eliminarea repetiiilor

1.5.1. Formule i funcii

Adesea datele introduse n celulele tabelului provin din calcule efectuate pe baza

datelor existente. Calculele se efectueaz cu ajutorul formulelor. Formulele sunt

ecuaii cu ajutorul crora putem realiza: efectuarea de calcule, redarea

informaiilor, modificarea coninutului altor celule, verificarea condiiilor .a.

Formulele ncep ntotdeauna cu semnul egal (=). Formula poate s conin:

funcii, referine, operatori (semne ale operaiilor) i constante.

Exemplu 4.: Fie formula din celula B4 (Fig. 19.)


30

Fig. 19.: Formul, referin, operatori, constante

=IF(2*B1^2-(B1-B2)*B1>B3;

aria formei geometrice depete valoarea maxim!;

2*B1^2-(B1-B2)*B1)

n formul apar:

Funcia IF () (dac...),

constante (cifra 2, i o constant text: aria formei geometrice depete

valoarea maxim!)

referiri la celule (B1, B2, B3)

operatori (*:nmulire; ^:putere, -: scdere; >: mai mare dect)

Funciile pot avea ca rezultate una sau, uneori, mai multe valori. Pentru aceasta,

funciile au nevoie de date de intrare (parametri, argumente). Acestea pot fi

valori (ex. numere) sau referiri la alte celule. Pot fi simple, ex. valoarea celulei

C4=C2+C3, dar putem folosi i funciile din zestrea Excel sau, la nivel avansat,

cu ajutorul Visual Basic Editor putem i noi s creem funcii specifice unui

anumit scop. n fiecare caz, funciile au denumiri unice.


31

Forma general a unei funcii este:

DENUMIRE FUNCIE (parametru 1; [parametru 2]; ...)

Forma general arat c funcia are o denumire i c ntre paranteze poate avea

mai muli parametri. Multe funcii pot avea pn la 255 de parametri, ex. funcia

SUM.

Introducerea unei funcii se poate face astfel:

1. Putem utiliza Inserarea automat a funciei. Trebuie doar nceput

scrierea funciei n celul, cu semnul = sau +, iar sistemul prezint

automat formulele posibile (fig. 20.). De exemplu:

Dac gsim funcia dorit n list, prin clic simplu se pot obine informaii

despre aceasta, iar prin dublu clic se poate selecta i utiliza.

Fig. 20.: Inserarea automat a funciei

2. Dac nu cunoatem funcia pe care dorim s o utilizm, n loc s fie

nscris n celul putem opta pentru una dintre urmtoarele modaliti:


32

din bara de formule se acioneaz butonul (fx) (Insert Function), sau

se face clic pe Shift F3, sau

din bara de meniuri se selecteaz banda FORMULAS butonul

INSERT FUNCTION

pe partea dreapt a benzii HOME , din dreptul simbolului

AUTOSUM () din fereastra EDITING (Fig. 21.) se acioneaz

triunghiul de derulare.

Fig. 21.: Inserare de formul cu iconia AUTOSUM

Putem insera diferite categorii de funcii chiar din meniul FORMULAS ,

selectnd din categoriile de funcii din dreptunghiul FUNCTION LIBRARY

(Fig. 22.).

n interiorul panelului INSERT FUNCTION deosebim trei pri: cutarea

funciei, categoria funciei i denumirea funciei. Cmpul Cutare funcie

(Search for a function) ofer informaii despre locul unde trebuie cutat

funcia. Or select a category ne ajut s selectm dintre categoriile de funcii:

financiare, date calendaristice, metematic i trigonometrie, matrici, baze de


33

date, text, logice, informaie, proiectare, cub, compatibilitate, toate, respectiv

ultima funcie folosit (Most recently used) (Fig. 23.).

Fig. 22.: Categorii de funcii din meniul FORMULAS

Fig. 23.: Fereastra de inserare a unei funcii

n cel de al treilea cmp putem alege dintre funciile aparinnd categoriei date.

Dac facem clic n list, sistemul vizualizeaz funciile posibile. Cnd se face


34

clic pe denumirea funciei, n partea de jos a ferestrei apare o scurt descriere a

funciei respective. Putem aplica funcia prin dublu clic pe denumirea acesteia.

n acest caz se deschide fereastra funciei cu titlul Function arguments, n care

parametrii obligatorii figureaz ntotdeauna cu litere ngroate. Restul

parametrilor i introducem n msura n care avem datele respective.

n grupul FORMULA AUDITING al meniului FORMULAS se pot trece n

revist formulele folosite, ct i referirile funciilor la alte celule.

1.5.2. Referine (adrese) relative, absolute i mixte

Referinele la celule se mai numesc i adrese ale celulelor i sunt de trei feluri:

relative, absolute i mixte. Diferitele tipuri de adrese sunt necesare pentru

copierea formulelor.

n Microsoft Excel, adresa de baz este adresa relativ. Caracteristica acesteia

este c n adres nu figureaz semnul dolar ($). Cnd se copiaz o formul

dintr-o adres relativ, aceasta se modific n modul de dorit n operaiile din

tabele, respectiv n fiecare celul nou se readreseaz n direcia i la distana la

care s-a copiat formula. De exemplu, formula din celula A1: =5*A2, copiat n

celula B1 devine =5*B2, iar n celula C1 devine =5*C2.

Caracteristica comun a adreselor absolute i mixte este c ele conin simbolul

$. Semnul $ poate sta n faa denumirii liniei i/ sau a coloanei. Pe parcursul


35

copierii, coordonata n faa creia se gsete semnul $ nu se modific. Atunci

cnd coninutul unei celule reprezint o constant ntr-o formul, aceast adres

va fi adres absolut. n adresa absolut, semnul $ st att naintea coordonatei

liniei, ct i naintea coordonatei coloanei. n cazul adresei mixte, semnul $

apare numai n faa denumirii liniei sau a coloanei. Pentru plasarea semnului $

n faa unei coordonate se poate utiliza tasta $ sau tasta F4.

Exemplu 5.: Pentru mai buna nelegere a referinei absolute i relative am

pregtit o aplicaie ca exemplu. La totalizarea pe rnduri i coloane am utilizat

referina relativ, pe cnd la valorile distribuiei am utilizat referine absolute.

(Tabelul 11.).

Tabelul 11.: Exemplu de referin absolut i referin relativ

3. Dac nu cunoatem funcia pe care dorim s o utilizm, n loc s fie

nscris n celul putem opta pentru una dintre urmtoarele modaliti:

din bara de formule se acioneaz butonul (fx) (Insert Function), sau

se face clic pe Shift F3, sau


36

din bara de meniuri se selecteaz banda FORMULAS butonul

INSERT FUNCTION

pe partea dreapt a benzii HOME , din dreptul simbolului

AUTOSUM () din fereastra EDITING (Fig. 21.) se acioneaz

triunghiul de derulare.

Fig. 24.: Cmp calculat ntr-un tabel

Ca prim pas, nscriem n celula D1, titlul Adaos comercial %, prin aceasta

adugm tabelului nostru un cmp nou. n celula D2 calculm adaosul comercial

n procente al magazinului nr. 123, scriind formula =C2/B2 (Fig. 24.). Tastnd

ENTER, celelalte celule ale coloanei se completeaz automat. Stabilim formatul

% i astfel, problema este rezolvat (Fig. 25.).

Exemplu 7.: S rezolvm aceeai problem, introducnd formula nu prin

tastare/ dactilografiere, ci prin selectarea celulelor. n acest caz formula devine

=[@[Adaos comercial mii Ft]]/[@[Vnzri mii Ft]],

iar noi am realizat aa zisa referin structurat (fig. 26.). i n acest caz, dup

tastarea Enter se completeaz restul celulelor, cu acelai rezultat ca n fig. 26.


37

Mai ncolo, n acest capitol ne vom ocupa i mai amnunit de referinele

structurate.

Fig. 25.: Cmpul calculat dup completarea automat

Fig. 26.: Cmp calculat cu referin structurat

n baza de date putem insera i un rndsum, astfel: Facem clic pe o celul a

tabelului. La mijlocul submeniului TABLE TOOLSDESIGN se gsete

capitolul Table Styles, aici vom bifa dreptunghiul Total Row. (fig. 27.).Ca efect,

Excel calculeaz automat, suma ultimei coloane care conine valori numerice.

Totui, putem calcula i sume pentru celelalte coloane.

Dup crearea rndului sum, dac se completeaz baza de date cu noi

nregistrri, rndurile corespunztoare vor aprea deasupra rndului sum.


38

Inerarea se poate efectua i aa, c selectm rndul sum i inserm deasupra un

rnd nou, sau, mai simplu, fixm cu mouse-ul,triunghiul din colul ultimei celule

a rndului sum i tragem n jos tot rndul.

Fig. 27.: Adugarea rndului sum la un tabel

Dei prin selectarea dreptunghiului Total Row, Excel calculeaz sum doar

pentru ultima coloan, putem nsuma celelalte coloane prin utilizarea funciei

adecvate (fig. 28.).

n fig. 28, n rndul sum am calculat vnzrile totale (celula B8), adaosul

comercial n mii Ft (celula C8) i adaosul comercial mediu n % (celula D9) (fig.

29.).

Calculul sumelor se realizeaz cu ajutorul funciei Subtotal. Numele funciei nu

trebuie s induc n eroare: aceasta poate realiza mai mult dect simple adunri,

fiind un instrument pentru analize variate.


39

Fig. 28. Introducerea funciei dorite n rndul sum

Fig. 29.: Tabelul Vnzri dup calculul rndului sum

Sintaxa funciei este

=SUBTOTAL(function_num, ref1,[ref2],...]),

care conine urmtoarele argumente:


40

Function_num: este obligatoriu de introdus. Este un numr cu valorile

ntre 1 i 11 (cnd trebuie incluse i valorile ascunse) sau ntre 101 i 111

(cnd valorile ascunse trebuie ignorate). Definete funcia utilizat la

stabilirea sumei pariale, conform tabelului 16.

Valori ascunse pot aprea n cazul bazelor de date filtrate. De exemplu,

dac ne intereseaz vnzrile sau numai adaosul comercial al magazinelor

cu o cifr de afaceri de peste 5 milioane de forini, atunci celelalte valori

rmn ascunse.

Tabelul 16.: Codurile de funcie ale funciei SUBTOTAL ()

Function_num Function_num Funcia (cnd trebuie

incluse i valorile escunse)

(cnd valorile ascunse trebuie

ignorate) 1 101 AVERAGE 2 102 CountA 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP

Ref1: este obligatoriu de introdus. Este primul domeniu sau referin, cu

denumire proprie, a crei sum parial dorim s fie calculat.


41

Ref2,...: nu este obligatorie introducerea. Se refer la domeniul numrul 2

pn la 254 pe care trebuie calculat suma parial.

Observaii: Dac n domeniile ref1, ref2, ... exist, deja, sume pariale

ncorporate, funcia le va ignora.

n cazul cnd constantele Function_num iau valori de la 1 la 11,

SUBTOTAL() va lua n considerare valorile ascunse prin irul de comenzi

ExcelHomeCellsFormatVisibility-Hide&Unhide-Hide Rows. n

cazul valorilor constantelor Function_num cuprinse n intervalul 101111,

SUBTOTAL() nu ia n considerare valorile ascunse prin irul de comenzi

de mai sus. Aceste constante servesc la calcularea sumelor pariale ale

valorilor neascunse din list.

Funcia SUBTOTAL() a fost proiectat pentru obinerea totalurilor

pariale ale coloanelor de date sau a domeniilor verticale; nu se poate

utiliza pentru totalizarea domeniilor orizontale. De exemplu, dac se

totalizeaz un domeniu orizontal cu o valoare a lui Function_num cuprins

ntre 101 i 111, ex. SUBTOTAL(109; B2:G2), ascunderea vreunei

coloane nu va influena suma parial, n timp ce ascunderea vreunui rnd

al domeniului vertical va influena aceast sum.

Dac printre referine figureaz o referin tridimensional, SUBTOTAL()

va reda ca rezultat, mesajul de eroare #VALUE!.

Formulele din rndul sume al fig. 29.:

B8 = SUBTOTAL(109; [Vnzri mii Ft])

C8 = SUBTOTAL(109; [Adaos com Mii Ft])


42

D8 = Vnzri[[#Sume]; [Adaos com mii Ft]] / Vnzri[[#Sume];

[Vnzri mii Ft]]

n celulele B8 i C8 am calculat sumele Vnzrii Adaos comercial, de aceea

figureaz atributul numeric Function_num de 109 aferent funciei SUM, iar n

parantezele drepte figureaz denumirea domeniului care conine datele. n celula

D8 am calculat adaosul comercial mediu n %, prin mprirea celulelor =D8/C8

i se poate observa c i aici exist referine structurate. Pentru o mai bun

nelegere, n cele ce urmeaz trecem n revist, referinele structurate.

1.5.2.1. Ce este referina structurat?

Referinele structurate simplific mult i fac de la sine neleas prelucrarea

datelor din tabele cu ajutorul formulelor, att referitor la o parte a tabelului, ct

i la ntregul acestuia. ntruct domeniile de date ale tabelelor se modific

adesea, iar adresele din referinele structurate se adapteaz automat la

modificrile respective, aceast funciune reprezint un uria avantaj: n cazul

modificrilor de structur ale tabelelor (inserarea i tergerea liniilor i

coloanelor), respectiv n cazul mprosptrii datelor din exterior, necesitatea

rescrierii formulelor scade la minim.

Aa cum am vzut deja, Excel confer fiecrui tabel nou, o denumire de baz

(Tabel1, Tabel2, ...), unic n cadrul fiierului respectiv. La rndul nostru putem

modifica denumirea cu uurin, pentru a o face mai sugestiv,ex. denumirea


43

Tabel1 am transformat-o n Vnzri, prin irul de comenzi Design Properties

Table name, dar i prin fereastra Rename.

Denumirea tabelului se refer la ntreg domeniul de date al tabelului, cu excepia

rndului de titluri i a rndurilor sume (totalizatoare). Tabelul denumit Vnzri

din fig. 30. refer la domeniul de celule A2:D7.

La fel cu denumirile tabelelor, i titlurile coloanelor desemneaz coloane ntregi,

cu excepia rndului de titlu i a rndurilor totalizatoare. n exemplul nostru,

denumirea [Vnzri mii Ft] se refer la domeniul de celule B2:B7, [Adaos

comercial mii Ft] se refer la domeniul de celule C2:C7. n afara cmpurilor

ntlnim i marcaje speciale. Astfel sunt marcajele #Toate, care se refer la

ntregul tabel (A1:A8), #Date care se refer numai la date, #Titluri care

desemneaz numai titlurile coloanelor (A1:D1), #Sume(A8:D8). Marcajul @

desemneaz rndul actual (fig. 30.).

Fig. 30.: Elementele de referin structurat ale tabelului Vnzri


44

Pe baza celor expuse vom nelege ce nseamn formula

= [@[Adaos comercial mii Ft]] / [@[Vnzri mii Ft]]

nscris n celula D2 din fig. 26. Expresia @[Adaos comercial mii Ft] nseamn

intersecia cmpului Adaos comercial mii Ft cu rndul actual, deci dac ne

aflm n celula D2, se refer la elementul C2, iar @[Vnzri mii Ft] nseamn

elementul B2. Astfel devine de la sine neles c formula nscris n celula D2

fr referin structurat este =C2/D2.

1.6. Validarea datelor n tabele

n acest subcapitol vom face cunotin cu o funciune Excel important:

validarea datelor. Cu ajutorul acestei funciuni putem aduga tabelelor Excel

diferite elemente dinamice fr a face apel la macrouri sau la elementele de

programare din Visual Basic. Ce nseamn aceasta?

Prin aplicarea validrii datelor se poate reglementa natura datelor care pot fi

inserate n anumite celule. Astfel, la introducerea datelor din chestionare ntr-o

baz de date, prin aplicarea acestei funciuni se pot elimina problemele datorate

greelilor de introducere a datelor.

Validarea datelor este deosebit de util atunci cnd un fiier este partajat ntre

mai muli utilizatori ai aceleiai organizaii i dorim ca datele nscrise n el s fie

precise i consistente.


45

Cnd este oportun folosirea validrii datelor?

Cnd putem alege numai dintre rspunsuri prestabilite. De exemplu, la

urmrirea frecvenei colare se

pot nscrie n celule numai zilele

de lucru.

Se va enumera ntr-o list

separat, care sunt zilele pe care

sistemul le accept pe parcursul

introducerii datelor.

Restricionarea numerelor

care cad n afara unui

domeniu dat:

Restricionarea termenelor

pentru a se ncadra ntr-un

interval dat: n cazul

evidenei consultaiilor medicale, dac orarul de consultaie este 8.00-

12.00, intrarea pacienilor se poate nscrie numai n acest interval orar.

Restricionarea numrului caracterelor text: stabilirea lungimii textului

care se poate nscrie ntr-o celul dat.

Validarea datelor pe baza formulelor sau valorilor din alte celule: dac am

10 lei, nu pot cumpra pentru 20 de lei o cutie de ciocolat.

Exemplu 9.: vrem ca ntr-o celul s poat fi introduse numai numere ntregi

cuprinse ntre 1 i 100. n cazul nscrierii unei valori invalide, Excel revine cu

un mesaj de eroare, mpiedicnd introducerea datei incorecte (fig. 31.).


46

Fig. 31.: Mesaj de eroare la introducerea unei date invalide, n cazul

aplicrii valid rii datelor

Validarea datelor se poate realiza cu irul de comenzi DATADATA TOOLS

Data Validation (fig. 32.).

n mod implicit, ntr-o celul pot fi nscrise orice fel de date (fig. 33.). Aceasta

este configuraia de baz n Excel. Pentru a determina natura datelor care se pot

nscrie n celule, avem la ndemn diverse instrumente.

Fig. 32.: Selectarea comenzii Validare date...

Posibiliti de introducere a restriciilor:

Exemplu 10.: Restricionarea datelor introduse la numere ntregi cuprinse ntre

anumite valori.


47

Fig. 33.: Posibiliti de validarea datelor

Din fereastra Allow (Permite) care se deruleaz (v. Fig.33.), se selecteaz

opiunea Whole Number (Numr ntreg). Prin lista Data, Excel ofer mai multe

opiuni de restricie, dintre care vom selecta varianta optim pentru noi (fig.

34.).

Fig. 35. prezint utilizarea validrii datelor printr-un exemplu de chestionar

simplu. n chestionar exist patru ntrebri care solicit evaluarea unor nsuiri

pe cte o scal de la 1 la 5. Deci trebuie s mpiedicm nscrierea - chiar i

accidental - a oricrei valori n afar de 1, 2, 3, 4 sau 5.


48

Fig. 34.: Posibiliti de restricionare a introducerii numerelor ntregi

Fig. 35.: Exemplu de validare a datelor


49

ntruct aici avem de-a face cu un interval, selectm opiunea between (ntre

valorile), unde valoarea minim va fi 1, iar cea maxim: 5. n afara acestei setri

mai putem introduce mesaje de atenionare (Input Message) i de eroare (Error

Alert) (fig. 36.).

Fig. 36.: Restricionarea datelor introduse la numere ntregi

Pentru vizualizarea mesajelor de atenionare, la selectarea celulei se bifeaz

csua Show input message when cell is selected. Apoi introducem titlul

mesajului de atenionare, iar n fereastra de jos, textul mesajului. Mesajele de

atenionare au un rol important n informarea utilizatorului; prin folosirea

acestora, introducerea datelor poate deveni mai inteligibil i mai clar. n

exemplul nostru atenionm utilizatorul s introduc cifre de la 1 la 5 i l

informm i despre semnificaia acestora. Ca efect al mesajului, cnd se face

clic pe celula C9, apare atenionarea, facilitnd nscrierea datelor.


50

Fig. 37.: Inserarea mesajului de atenionare

Fig. 38. Aa apare mesajul de atenionare cnd seface clic pe celula validat


51

Mesajul de eroare (Error Alert) apare cnd introducem o valoare invalid n

celula cu date validate. Mesajul ca atare apare numai dac bifm csua Show

error alert after invalid data is entered. n lista Style putem alege ntre trei

tipuri de mesaje, a cror explicaie amnunit se vede n fig. 39.

Revenind la exemplul nostru, n acest caz fiind vorba despre note acordate, nu

vom accepta introducerea de date greite, de aceea alegem stilul Stop

(Meglls). n mesaj notificm utilizatorul s introduc valori de la 1 la 5, deci l

ajutm n corectarea greelii (fig. 40.).

Fig. 39.: Tipuri de mesaje de eroare

Exemplu 11.: Restrngerea datelor introduse la o fracie zecimal cuprins ntre

dou valori limit.


52

Fig. 40.: Configurarea mesajului de eroare

Din fereastra Allow (Permite) care se deruleaz, se selecteaz opiunea Decimal

(v. Fig. 33.). Din lista Data, Excel ofer diferite variante corespunztoare

cerinelor restrictive, dintre care putem selecta varianta corespunztoare.

Opiunile se aseamn cu cele deja ntlnite n cazul numerelor ntregi (fig. 34.).

n celula B1 trebuie introdus rabatul, care nu poate depi 60%. Deci, pentru a

preveni greelile de introducere a datelor, vrem ca utilizatorul s nu poat insera

valori mai mari.

Ca prim pas, formatm celula B1 pentru procente, cu precizia de o zecimal (fig.

41.). Pentru aceasta vom face clic pe butonul Procente , apoi, dup clic pe

Cretere numr zecimale (Increase Decimal) , reglm precizia la 1 zecimal


53

(acest lucru este necesar fiindc n configuraia de baz, butonul Procente se

refer la valori ntregi ale procentelor).

Fig. 41.: Formatarea celulei B1

Pasul urmtor este introducerea reglajelor de validare a datelor. Din lista Allow

selectm opiunea Decimal (fracie zecimal) in fereastra Data alegem less than

or equal to (mai mic sau egal), drept valoare maxim nscriem 0.6 (fig. 42.).

Bineneles, i aici se poate configura mesaj de atenionare i regula de aplicat n

cazul introducerii de date invalide. n exemplul nostru am aplicat mesajul de

eroare Stop (fig. 43.).

Exemplu 12.: Limitarea datelor introduse la o list de valori

E o problem frecvent n bazele de date din Excel c n tabel apare de mai

multe ori aceeai denumire, dar din cauza unor mici greeli de tastare apar

diferene de form. De exemplu, denumirile Budapest, B.pest, Budap., Bp.

nseamn, n fond, acelai lucru. Totui, cnd vrem s totalizm sau s filtr m

vnzrile din Budapesta, multitudinea de denumiri creeaz probleme. Pentru a

evita aceast eroare, exist diferite variante. O posibilitate este codificarea


54

oraului, ex. codul 1 pentru Budapesta, dup care, dac e nevoie, denumirea

corespunztoare codului se introduce cu o funcie de cutare. O alt variant mai

simpl este limitarea datelor introduse la o list de valori, adic utilizarea

funciei de validare a datelor.

Fig. 42.: Introducerea condiiilor de validare a datelor pentru fracii

zecimale

Fig. 43. Mesaj de eroare n cazul introducerii datelor invalide


55

n fig. 44. este ilustrat modul de restricionare a datelor introduse printr-o list

prestabilit (ntrebarea nr. 2).

Fig. 44. Introducerea unei liste de validare a datelor dintr-un domeniu

extern


56

Pentru a evita complicaiile ulterioare la prelucrarea datelor, rspunsul la

ntrebarea nr. 2 poate fi ales de ctre utilizator dintr-o list. Rspunsurile

posibile se gsesc pe aceeai foaie, n coloana S2:S9.

Primul pas n validarea datelor este listarea rspunsurilor posibile, aceasta va fi

sursa listei (n cazul nostru, completarea celulelor S2:S9).

Atenie! Datele din list i celula n care se insereaz valoarea trebuie s se afle

pe aceeai foaie de lucru!

n pasul al doilea selectm, din lista Allow, elementul List, iar lng acesta

selectm In-cell Dropdown (list care se deruleaz). Ca surs selectm

domeniul S2:S9.

n pasul al treilea i al patrulea putem insera i aici, mesaje de atenionare i de

eroare, aa cum s-a explicat mai sus.

Dup terminarea operaiunii, n celula C6 apare o list din care utilizatorul poate

selecta rspunsul corespunztor printr-un clic (fig. 45.).

Putem introduce drept surs, nu doar un domeniu extern, ci putem, pur i

simplu, s dactilografiem elementele listei, separate prin punct cu virgul. De

exemplu, la ntrebarea a treia (Ai gsit ceea ce ai cutat?) dorim s obinem

rspunsul DA sau NU n celula C7. Nu dorim rspunsul: n parte, nici

prescurtri, ex. Y, N, de aceea restricionm introducerea datelor prin precizarea

Sursei (fig. 46.).


57

Exemplu 13.: Restricionarea datelor introduse la datecalendaristice cuprinse

ntr-un interval de timp

Fig. 45.: Inserarea de date din list

Fig. 46.: Specificarea listei de validare a datelor prin dactilografiere


58

Din lista Allow s selectm opiunea Date. Din lista Data selectm restricia

dorit. De exemplu, pentru a permite date calendaristice ulterioare unei anumite

date, selectm opiunea Greater than . S nscriem data iniial, final sau data

concret permis. Se poate introduce i o formul care red o dat.

De exemplu, dac dorim s permitem perioada de timp de o sptmn cu

ncepere de azi, din lista Data selectm opiunea Between, apoi scriem formula

=TODAY() la Minimum, i =TODAY() + 7 n cmpul Maximum.

La fel ca n cazurile anterioare, i aici poate fi inserat un mesaj de atenionare n

fereastra corespunztoare. Apoi putem determina, n fereastra Error Alert,

modul n care Microsoft Office Excel va semnaliza dac utilizatorul a introdus

date nevalabile (fig. 47.).

Exemplu 14.: Limitarea datelor introduse la un interval de timp

n panoul Data Validation s acionm butonul Settings. Din lista Allow se

selecteaz varianta Time. n lista Data se selecteaz tipul de restricie dorit. De

exemplu, pentru a permite momente anterioare unui anumit reper n timp, se

alege opiunea less than. Se va nscrie coordonata de timp iniial, final sau

concret permis, dar se poate insera i o funcie care red un reper n timp.

De exemplu, n celula G2 vrem s fixm perioada n care ntr-un un restaurant

este servit micul dejun. Dac vrem ca intervalul de servire a micului dejun s fie

de trei ore de la ora deschiderii, din lista Data vom selecta opiunea between


59

apoi la Start Time nscriem formula =G1, iar n rubrica End Time nscriem

=G1+3:00. Vom avea grij ca att formatul celulei G1 ct i cel al celulei G2 s

fie formate de timp.

Fig. 47.: Introducerea datelor temporale restricionate

Bineneles, i n acest caz pot fi specificate mesaje de atenionare care apar la

selectarea celulei, respectiv mesaje de eroare n cazul introducerii de date

invalide (fig. 48.).

Exemplu 15.: Restricionarea lungimii textului introdus


60

Fig. 48. Limitarea datelor introduse la un interval de timp dat

Din panoul de dialog DATA VALIDATION s facem clic pe butonul Settings.

Din lista Allowed s alegem opiunea Text length. n lista Data vom selecta tipul

de restricie dorit. S nscriem lungimea maxim, minim sau exact, permis, a

textului. Putem introduce i formul a crei rezultat s fie o valoare numeric.


61

De exemplu, dac dorim s prevenim greeala de tastare n rubrica anul naterii,

n celula C18 restricionm lungimea textului la 4 caractere. Aa cum am

procedat i nainte, putem insera un mesaj de avertizare care apare cnd este

selectat celula, respectiv un mesaj de eroare cnd seintroduc date invalide (fig.

49.).

Exemplu 16.: Calculul valorii permise cu o formul

Dac niciuna dintre posibilitile prezentate anterior nu realizeaz restricia

dorit, atunci n panoul de dialog Data Validation s facem clic pe Settings, iar

din lista care se deschide s selectm opiunea Custom. n cmpul Formula

putem introduce o formul care are ca rezultat, o valoare logic (n caz de date

valide d rezultatul Adevrat (TRUE), iar n caz de date neconforme, rezultatul

Fals (FALSE)).

S vedem cum funcioneaz aceast restricie, cu ajutorul chestionarului studiat

anterior. ntrebarea nr. 5 se refer la frecvena cumprrilor de pe internet.

Rspunsurile posibile se regsesc n tabelul 13.

ntrebarea nr. 6 se refer la procentul de cumprturi efectuale pe internet, de

aceea la introducerea datelor am prevzut o restricie conform creia, dac

rspunsul la ntrebarea nr. 5 a fost Niciodat, atunci respondentul s nu poat

rspunde la aceast ntrebare.


62

Fig.49.: Limitarea numrului de caractere

Bineneles, la fel ca n cazurile anterioare, i aici putem introduce mesaj de

avertizare i, pentru cazul nscrierii de date neconforme, mesaj de eroare (fig.

50.).


63

Tabelul 13.: Rspunsurile posibile la ntrebarea nr. 5 din chestionar

ntrebarea 5

Niciodat

O dat pe an

Anual de mai mult ori

O dat pe lun

Lunar de mai mult ori

Sptmnal

Fig. 50.: Validarea datelor cu ajutorul formulei


64

Pe lng cele prezentate n exemplul model, validarea datelor cu ajutorul

formulei are multe alte utilizri practice (tabelul 14.).

Tabelul 14.: Cteva exemple de validare de date cu formule

Mesaj de atenionare Funcia In celula curent(B1) introducerea datelor este posibil dac D1 i D2 este mai mic dect 1000000

=AND(D1>0; D1


65

1.7. Protecia fiierului i protecia foii de lucru

Cnd creem un tabel, apare adesea cerina ca utilizatorii s nu poat din

ntmplare sau voit modifica, rearanja sau terge date importante, formule n

foaia de lucru sau n fiier. Cu ajutorul proteciei foilor de lucru fau a fiierelor

putem proteja anumite elemente ale acestora, cu sau fr utilizarea unor parole.

Dac protejm doar foaia de lucru sau anumite elemente ale acesteia, n general

o facem cu scopul de a preveni eventualele greeli ntmpltoare ale

utilizatorilor, cum ar fi suprascrierea unor date importante sau formule.

mpotriva utilizatorilor ru intenionai este indicat protejarea prin parol a

ntregului fiier. n cele ce urmeaz vom prezenta, cu ajutorulexemplului simplu

din fig. 9., modalitile de protejare a muncii noastre cu ajutorul Excel.

Exemplu 17.: nostru dorim, ca prim pas, ca utilizatorul s poat modifica numai

coninutul celulelor B1, B2 i B3, fr a putea interveni n vreo alt celul.

n acest scop mai nti vom selecta domeniul B1:B3, apoi derulm n banda de

meniu HOME (acas), n cmpul CELLS (celule), elementul Format i s

alegem punctul Format Cells (sau cu butonul dreapta al mouse-ului deschidem

meniul local n care alegem Format Cells) (fig. 51.).

Dup aceasta, din dreptunghiul din dreptul titlului Protection tergem bifa (n

modul de baz, celulele sunt protejate (locked) (fig. 52.). n urmtorul pas s

dechidem din nou n cmpul CELLS din HOME , elementul Format i s

selectm Protect Sheet. Se vede c dreptunghiurile aferente celulelor blocate i,


66

respectiv, celulelor deblocate sunt active. Dac vrem s mpiedicm selectarea

cu cursorul a altor celule cu excepia celor care pot fi completate, trebuie s

debifm csua din faa inscripiei Locked cells.

Fig. 51.: Selectarea Format Cells

Putem proteja foaia de lucru i prin parolare. Trebuie s reinem parola, fiindc

n cazul pierderii nici noi nu mai putem realiza editri ulterioare (fig. 53.).


67

Dup activarea proteciei foii de lucru, n urma oricrei ncercri de modificare a

celulelor din afara domeniului B1:B3, primim un mesaj de atenionare.

Fig. 52.: Eliminarea blocrii din titlul Protection

Fig. 53.: Posibiliti de configurare a proteciei foii de lucru.


68

Fig. 54.: Mesaj afiat n cazul ncercrii de a selecta celule blocate prin

protejarea foii

Protecia foii de lucru este foarte util, dar protecia structurii ntregului fiier, a

aranjamentului i dimensiunilor ferestrelor se poate realiza prin protecia

fiierului. Pentru aceasta, din meniul REVIEW alegem grupul CHANGES, n

care selectm butonul de meniu Protect Workbook. La stabilirea razei de

aciune a proteciei (Structure and Windows) avem dou opiuni: Structure

(structura) i Windows (ferestrele) (fig. 55.).

Fig. 55.: Configurarea razei de aciune a proteciei fiierului


69

Dac se alege butonul Structure (Felpts), interzicem urmtoarele manevre de

operator:

Deschiderea foilor de lucru ascunse

Reaezarea foilor de lucru, tergerea, ascunderea, redenumirea acestora.

Inserarea de foi de lucru sau foi cu diagrame noi (foaia cu diagram este o

foaie a fiierului care conine numai diagrame. Este util cnd se dorete

vizualizarea diagramelor, graficelor ridicate pe baza datelor din foaia de

lucru, separat de datele, situaiile de acolo).

Relocarea sau copierea foilor de lucru n alt fiier.

Vizualizarea datelor surs ale celulelor din domeniul de date, respectiv

vizualizarea foilor e lucru laterale pe foi de lucru separate.

Elaborarea de rapoarte centralizatoare despre cazurile nregistrate.

Utilizarea instrumentelor de analiz din pachetul Analiza Datelor, prin

care rezultatele se plaseaz pe o foaie de lucru nou.

n cazul selectrii butonului Window (fereastr) interzicem urmtoarele operaii

de utilizator:

Modificarea dimensiunilor i poziiei ferestrelor foii de lucru la

deschiderea foii de lucru.

Reaezarea, redimensionarea i nchiderea ferestrelor.

Ca i n cazul protejrii foilor de lucru, i aici putem utiliza protecia prin parol.

Dac nu introducem o parol, att protecia foilor, ct i protecia fiierelor pot

fi dezactivate: se realizeaz prin clic pe ReviewChangesProtect sheets,


70

respectiv Protect workbook. Bineneles, dac exist parol, protecia se poate

elimina doar dup introducerea parolei.


71

2. Cutare i filtrare n baza de date

2.1. Cutare, nlocuirea datelor i salturi n baza de date

Putem cuta pe ntreaga foaie de lucru fr selectare, sau pe un interval selectat

al acesteia, astfel: n partea dreapt a meniului HOME se gsete submeniul

EDITING , cu butonul de deschidere a ferestrei Find and Select. Aici putem

alege ntre comenzile Find (cutare), Replace (nlocuire) sau Go To (salt la...)

(fig. 56.).

Fig. 56.: Meniul desfurat al comenzii Cutare i Selecie

La pornirea meniului Cutare scriem n dreptul ferestrei Find what, irul de

caractere pe care l cutm i care poate conine litere, cifre, semne de

punctuaie, respectiv caractere nlocuitoare. Dac selectm dreptunghiul Match

case (recunoaterea majusculelor i minusculelor), atunci programul va cuta

corespondena i n funcie de caracterul literelor. Dac bifm dreptunghiul


72

Match entire cell contents, programul va cuta corespondena total pe foaia

respectiv de lucru (fig. 57.).

Fig. 57.: Fereastra meniului de cutare FIND

Din lista Within (domeniul de cutare) se determin dac cerem cutarea pe

foaia de lucru sau n ntregul fiier. Din lista Search se determin direcia de

cutare: dac se va efectua pe rnduri sau pe coloane, pornind de la celula dat.

Din lista Look in selectm locurile unde se caut: n formule, n valori sau n

comentarii.

Dac este necesar, n lista Format se poate prescrie cum trebuie s fie celula

cutat.Astfel i formatul celulei devine un element al cutrii.

Cu ajutorul butonului Next din partea de jos a ferestrei se caut urmtoarea

coresponden, iar cu ajutorul butonului Find All vor fi cutate toate

corespondenele. Butonul Close nchide cutarea.


73

n meniul Replace putem ordona nlocuirea setului de caractere cutat, printr-un

alt set de caractere. Parametrii referitori la cutare sunt similari cu cei descrii la

meniul de cutare.

n fereastra Replace with se poate nscrie irul de caractere prin care vrem s fie

nlocuit irul de caractere vizat. Cu iconia Format se poate stabili formatul

celulei dup nlocuirea coninutului (fig. 58.).

Fig. 58.: Fereastra meniului de nlocuire Replace

Butonul Find next din partea de jos a ferestrei propune urmtoarea posibilitate

de nlocuire, pornind de la celula dat, iar prin clic pe Replace acceptm

nlocuirea i putem cuta urmtoarea celul de nlocuit. Cu ajutorul butonului

Find All putem aduna ntr-o list toate celulele care corespund criteriului de

selecie, care apoi pot fi nlocuite una cte una cu Replace sau toate mpreun,

cu comanda Replace All.

Meniul Go To (salt la..) se utilizeaz mai ales la cutarea greelilor; n acest caz

trebuie date locul saltului i valoarea referinei (fig. 59.). Cu ajutorul butonului


74

Special pot fi selectate toate celulele precedente sau urmtoare, formulele

greite, celulele goale, respectiv diferenele.

Fig. 59.: Fereastra meniului Salt la... (Go to)

Meniul Special poate fi accesat i cu butonul Special al meniului Go To. Putem

scrie o adres de celul n cmpul Referina al meniului Salt La sau putem

alege din Salt La..., unde anume vrem s efectum saltul. n cazul utilizrii

repetate, denumirile de domenii utilizate anterior, respectiv adresele de celule

atinse anterior rmn n fereastra Salt La.... Prin selectarea dirijat putem

desemna cu uurin celule cu aceleai caracteristici, iar caracteristicile se aleg

n fereastra de meniu (ex. dac vrem s selectm celulele care conin text)

n cazul bazelor de date de dimensiuni nu prea mari mrim claritatea prin

butonul HIDE/UNHIDE al benzii VIEW , fiindc asigur mai mult spaiu pe

ecran pentru tabelul nostru. Cu acest buton pot fi vizualizate sau ascunse: bara

de instrumente, barele de titlu, rigla, bara de formule, liniile de gril. O alt


75

rezolvare poate fi comanda VIEW FULL SCREEN, cu ajutorul creia putem

folosi ntregul ecran pentru editarea tabelului.

Un instrument ajuttor pentru o mai mare claritate este comanda ZOOM din

VIEW, cu ajutorul creia putem mri sau micora tabelul de pe ecran.

Dac tabelul nostru este prea lat sau prea lung, este util folosirea butonului

Freeze Panes din VIEW . Scopul acestuia este ca, n cazul tabelelor

mari, zonele de denumiri din partea superioar i stnga s fie mereu vizibile.

naintea lansrii comenzii aezm cursorul mouse-ului sau selectm prin tastare,

celula deasupra sau la stnga creia se afl zonele cu denumiri ale tabelului

nostru. Pentru revocarea comenzii se va utiliza acelai buton.

Comanda VIEW SPLIT mparte ecranul n 2 sau 4 pri, n care pot fi

vizualizate diferite pri ale tabelului. n aceast situaie benzile de derulare se

despart. naintea lansrii comenzii trebuie selectat celula deasupra creia,

respectiv la stnga creia dorim s mprim tabelul. Pentru revocarea comenzii

se acioneaz acelai buton .

Acelai scop l au simbolurile dinpartea de sus a benzii de derulare pe

vertical i din captul dreapta al benzii de derulare orizontale. Aceste

simboluri pot fi prinse i trase cu mouse-ul la locul unde dorim s mprim

tabelul i napoi.


76

Mai rar se utilizeaz butonul ARRANGE ALL din VIEW , cu ajutorul cruia

putem vedea concomitent pe ecran, diferite pri ale unui tabel mare. Este util s

se foloseasc pentru vizualizarea mai multor fiiere. Aranjarea poate fi sub

form de mozaic, suprapus, alturat, n trepte, fiind posibil continuarea

activitii n alt fiier (fig. 60.).

Fig. 60.: Posibiliti de aezare a foilor de lucru cu ajutorul comenzii

Arrange All

O nou oportunitate n Excel 2007 este comanda WATCH WINDOW

(fereastra de verificare) din FORMULAS, n care, prin butonul Add Watch

putem introduce mai multe celule n fereastra de verificare. Cu ajutorul acestei

faciliti se poate compara coninutul celulelor selectate, respectiv valorile

obinute (fig. 61.).


77

2.2. Utilizarea funciilor de cutare

Pe lng comanda Find putem utiliza i funciile de cutare care, n versiunile

anterioare, se numeau funcii de cutare i de referin sau funcii matriceale.

Cu ajutorul acestora se obin informaii n legtur cu coninutul celulelor

(valoare, text, caractere speciale), ct i despre referine (de exemplu, despre

locul celulei cu ajutorul funciei .MATCH()).

Fig. 61.: Afiajul ferestrei de verificare Watch Window

Funciile de cutare pot fi accesate cu meniul Lookup and Reference din

FORMULAS . Funciile de cutare sunt ADDRESS(), AREAS(), CHOOSE(),

COLUMN(), COLUMNS(), GETPIVOTDATA(), HLOOKUP(), INDEX(),

INDIRECT(), LOOKUP(), MATCH(), ROW(), ROWS(), RTD(),

TRANSPOSE(), VLOOKUP() (fig. 62.).


78

2.2.1. Funcia ADDRESS()

Funcia ADDRESS() red referina corespunztoare adresei unei celule

(numrul rndului i numrul coloanei). Sintaxa formulei este (fig. 63.):

=ADRRESS(Row_num; Column_num; Abs_num; a1; Sheet_text)

Referinele Row_numi Column_num trebuie s conin numrul

rndului i numrul coloanei careconin adresa dat.

Fig. 62.: Deschiderea listei funciilor de cutare

Abs_num potare fi de 4 feluri:


79

1: referina absolut la rnd i coloan; n unele cazuri valoarea

poate fi #LIPS;

2: referin absolut de rnd i referin relativ de coloan;

3: referin relativ de rnd i referin absolut de coloan;

4: referin relativ de rnd i coloan.

Valoarea lui A1 nu trebuie dat n mod obligatoriu (nu este evideniat cu

litere boldate). Valoarea care caracterizeaz referina poate fi de dou

feluri:

De tip A1: poate avea valoarea1 sau ADEVRAT sau nu este

precizat, iar atunci valoarea funciei ADDRESS() este egal cu

valoarea A1.

De tip S1O1: valoarea este 0 sau FALS.

Fig. 63. Argumentele funciei ADDRESS()


80

Completarea cmpului Sheet_text nu este obligatorie. Dac se d, aceasta

trebuie s fie denumirea curent a foii de lucru. Dac nu se completeaz,

funcia nu va folosi denumire de foaie de lucru.

n tabelul 15. prezentm cteva variante posibile ale funciei ADDRESS(),

difereniate pe baza adresei absolute sau adresei relative utilizate ca argumente.

Tabelul 15.: Variantele posibile ale funciei ADDRESS()

Funcia Rezultatul

=ADDRESS(2011; 1) $A$2011

=ADDRESS(2011; 1; 2) A$2011

=ADDRESS(2011; 1; 3) $A2011

=ADDRESS(2011; 1; 4) A2011

=ADDRESS(2011; 1; 4; TRUE) A2011

=ADDRESS(2011; 1; 4; FALSE) S[2011]O[1]

2.2.2. Funcia VLOOKUP()

Funcia VLOOKUP() caut o valoare n prima coloan din stnga unui tabel,

apoi red valoarea aflat n celula de intersecie dintre rndul respectiv i o

coloan dat. n prima coloan pot fi doar numere, text sau o valoare logic.

Funcia nu poate deosebi literele majuscule de cele minuscule. Sintaxa funciei

este (fig. 64.):

=VLOOKUP(Lookup_value; Table_array; Col_index_num; Range_lookup)


81

Valoarea de cutare Lookup_value poate fi orice valoare, referin sau ir

de caractere.

n rubrica Table_array trebuie selectat domeniul n care se realizeaz

cutarea.

La Col_index_num(numrul de ordine al coloanei) se nscrie acel numr

care reprezint valoarea locului ocupat n tabel. Dac valoarea coloanei

este 5, atunci obinem ca rezultat, valoarea nscris n coloana a 5-a a

tabelului. n aceast parte nu putem nscrie numr de coloan mai mic

dect 1, fiindc n acest caz, sistemul d mesajul de eroare #VALUE.

Dac valoarea introdus este mai mare dect numrul de coloane al

tabelului, mesajul de eroare va fi #REF.

n fereastra Range_lookup se poate nscrie o valoare logic pentru o

precizie mai mare sau mai mic a cutrii. Aceast list de comenzi poate

fi utilizat numai dac prima coloan a bazei de date este aranjat n

ordine cresctoare. Dac introducem valoarea TRUE (Adevrat), atunci

valoarea redat aproximeaz valoarea cutat. Aceasta nseamn c n

cazul n care nu gsete valoarea, funcia va reda n locul ei valoarea

urmtoarecea mai mare, dar mai mic dect argumentul cutat. Dac baza

de date nu este aranjat dup mrime, funcia va da un rezultat eronat.

Exemplu 18.: n aplicaia urmtoare vom aplica funcia VLOOKUP() pentru

cutarea datelor de vnzri ale agenilor de vnzri (fig. 65.):

1. Prima ntrebare este: ct a fost totalul vnzrilor lui Istvn ?

2. A doua ntrebare: ct a vndut n total, Pista?


82

Fig. 64.: Argumentele funciei VLOOKUP()

Pentru obinerea rspunsului la prima ntrebare, introducem criteriul de

cutare n celula A8. Tabelul este domeniul A1:E6, totalurile se gsesc n

coloana a cincea, deci Col_index_num=5. Nu putem accepta dect rezultat

exact, de aceea n fereastra Range_lookup nscriem expresia logic FALSE.

Astfel, funcia devine:

=VLOOKUP((A8; A1:E6; 5; FALSE),

iar rezultatul va fi 74415.

La a doua ntrebare procedm la fel, dar aici, criteriul de cutare se gsete n

celula A9, iar restul parametrilor corespund. Ca rezultat obinem mesajul de

eroare #N/A, fiindc nu exist agent de vnzri cu numele Pista.


83

Fig. 65. Aplicaie pentru utilizarea funciei VLOOKUP

2.2.3. Funcia de cutare HLOOKUP()

n denumirea funciei HLOOKUP(), litera H desemneaz direcia orizontal

(horizontal). Funcia caut un element n primul rnd al unui bloc, apoi red o

valoare din coloana n care se afl acest element, pe baza parametrilor dai.

Utilizarea funciei seamn n principiu cu aceea a funciei VLOOKUP().

Funcia nu recunoate diferena ntre literele majuscule i minuscule.

Utilizarea funciei poate fi util n cazurile n care valorile comparate se gsesc

n rndul de sus al blocului (tabelului) i dorim s aflm o valoare aflat ntr-un

rnd aflat mai jos. Sintaxa formulei este (fig. 66.):

=HLOOKUP(Lookup_Value; Table_array; Row_index_num; Range_lookup).


84

Cmpul valorii de cutare Lookup_value trebuie s conin o valoare pe

care funcia o caut n rndul de sus al tabelului. Coninutul celulei poate

fi valoare, text sau referin.

Fig. 66.: Argumentele funciei HLOOKUP()

n cmpul Table_array trebuie s fie identificat un bloc (desmnarea sau

denumirea unui domeniu) n care se va efectua cutarea. Tabelul sau

blocul selectat poate conine cap de tabel, numere, valori logice.

Cmpul Row_index_num trebuie s conin numrul rndului din care

ateptm rezultatul. De exemplu, dac introducem valoarea 3, funcia va

reda valoarea din rndul al treilea al tabelului/blocului.

Dac nscriem un numr mai mare dect numrul de rnduri al tabelului

selectat, funcia returneaz mesajul de eroare #REF. Dac nscriem o

valoare mai mic dect 1, vom obine mesajul de eroare #VALUE.

Coninutul cmpului Range_lookup este o valoare logic, putnd fi

adevrat (TRUE) sau fals (FALSE). Prin utilizarea acestora, cutarea


85

poate fi exact sau aproximativ. Dac valoarea este TRUE, atunci valoarea

redat va fi aproximativ, ceea ce nseamn c dac funcia nu gsete o

coresponden perfect, atunci va da un rezultat mai mic dect argumentul

cutat. n acest caz, elementele primului rnd al tabelului selectat trebuie

s fie aezate n ordine cresctoare. n orice alt situaie, rezultatul funciei

va fi eronat. Dac valoarea domeniului este FALSE, atunci funcia caut

coresponden exact. Dac nu exist o astfel de coresponden, funcia

red un semnal de eroare (#N/A). Pentru utilizarea argumentului FALSE

nu este necesar aranjarea n ordine a tabelului.

Exemplu 19.: n cele ce urmeaz vom prezenta posibilitatea de aplicare a

funciei HLOOKUP() printr-un exemplu (fig. 67.). n tabel se gsesc datele

anuale de vnzri ale unor ageni de vnzri. Vrem rspunsuri la urmtoarele

ntrebri:

1. Ct este totalul vnzrilor pe anul 2002?

2. S creem o funcie cu ajutorul creia s putem afla valoarea oricrei celule

a bazei de date.

Rspunsul la prima ntrebare este foarte simplu. Valoarea de cutare se gsete

n celula A8. Ca domeniu de cutare selectm ntreg tabelul; rndul totalurilor

este al 6-lea, acceptm numai coresponden precis, de aceea n cmpul

Range_lookup nscriem valoarea logic FALSE. Funcia va deveni:

=HLOKUP(A8;A1:E6;6;FALSE)


86

Fig. 67.: Posibilitatea de utilizare a funciei HLOOKUP()

Pentru rezolvarea celei de a doua ntrebri vom utiliza cele nvate anterior.

n primul rnd vom ntocmi un mic tabel ajuttor (domeniul A10:B11 n

imagine), apoi, cu ajutorul comenzii de validare a datelor s creem liste verticale

pentru ani n celula A11 i pentru nume n celula C11. Apoi putem ntocmi

funcia. Valoarea de cutare va fi celula A11, fiindc aici se gsesc

identificatoarele rndurilor. Domeniul (Table_array) este tabelul n ntregime

(A1:E6). Numrul rndului Row_index_num se calculeaz cu ajutorul funciei

(MATCH() despre care se va vorbi mai pe larg n capitolul urmtor. i aici se

accept numai coresponden exact, de aceea valoarea logic va fi FALSE.

Funcia va fi:

=HLOOKUP(A11; A1:E6; MATCH(B11; A1:A6; 0); FALSE)

Dup aceasta, tot ce avem de fcut este ca n celula A11 s selectm perioada, n

B11 s selectm numele, iar n C11 putem vedea imediat rezultatul aferent

criteriilor. Lista care se deruleaz n ferestrele verticale asigur cutarea numai a


87

acelor elemente care exist ntr-adevr, astfel nu trebuie s ne ateptm la

mesajul de eroare #N/A.

2.2.4. Funcia MATCH()

Funcia MATCH() red poziia unui element ntr-un bloc, corespunztoare unei

valori date ntr-un mod dat. Nici aceast funcie nu deosebete literele majuscule

de cele minuscule. Sintaxa formulei este (fig. 68.):

=MATCH(Lookup_value; Lookup_array; Match_type)

Fig. 68.: Argumentele funciei MATCH()

Valoarea de cutare Lookup_value este acel numr, text, acea valoare

logic sau referin la o astfel de valoare, al crui/crei loc l cutm n

tabel.


88

Lookup_array este o selecie coerent (bloc) n care funcia caut

valoarea cutat.

Cmpul Match_type nu trebuie completat n mod obligatoriu. Dac nu se

completeaz, funcia va lucra cu valoarea intrinsec 1.

Dac valoarea este 1, atunci funcia va cuta valoarea egal sau cea mai

mare valoare mai mic dect valoarea cutat. n acest caz, datele din baza

de date trebuie s fie selectate n ordine cresctoare.

Dac valoarea este 0, atunci funcia va cuta valoarea care corespunde

exact valorii de cutare. n acest caz nu se impune aranjarea dup mrime

a tabelului. Dac valoarea cutat este text, atunci putem utiliza

caracterele nlocuitoare * (stea) sau ? (semnul ntrebrii). Semnul * se

utilizeaz atunci cnd la dreapta poziiei date nu cunoatem caracterele.

Poate fi vorba despre orice numr de caractere necunoscute. Semnul ?

poate nlocui un singur caracter.

Dac valoarea este -1, atunci funcia va reda valoarea egal cu cea cutat

sau cea mai mic valoare mai mare dect valoarea cutat. i aici,

aranjarea dup mrime este esenial. n cazul n care funcia nu gsete o

valoare care corespunde valorii cutate, apare mesajul de eroare #N/A.

Exemplu 20.:

Excel-Baza de date

Documents

Transcript of Excel-Baza de date