REGASIREA DATELOR IN BAZE DE DATE EXTERNE
(Microsoft Querry
EXCEL este furnizat cu programul MS QUERRY.
Cu ajutorul acestui program puteti regasi informatii memorate in baze de date externe (Fox, Dbase, Paradox ) iar apoi se pot insera aceste date in foaia de calcul.
MS QUERRY comunica cu BD externe prin diferite drivere.
ODBC (Open Data Base Conectivity)
Un driver ODBC este un set special de rutine - programe memorate in DLL (Dynamic Link Library) care permite aplicatiilor sa foloseasca limbajul SQL (Structure Querry Language).
In terminologia QUERY, informatiile le gasim intr-o sursa de date .
Datele sunt memorate in tabele organizate pe randuri (inregistrari) si coloane (campuri).
Operatia de preluare a unor date dintr-o baza de date se numeste interogare.
O interogare este o intrebare pe care o punem asupra datelor din sursa de date .
Atunci cand executa o interogare asupra sursei de date, Query acceseaza tabelele sursei si culege datele care raspund la intrebarea pusa, apoi le depune in ceea ce numim setul rezultat.
LANSAREA PROGRAMULUI MS QUERY
Trebuie sa avem instalata aplicatia MS QUERY impreuna cu driverele ODBC pe care dorim sa le folosim;
Din meniul DATA GET EXTERNAL DATA;
Se afiseaza fereastra MS QUERY si caseta de dialog Select Data Source
Fereastra Query este impartita in mai multe zone numite panouri:
a) panoul tabel;
b) panoul de criterii;
c) panoul de date;
a) Panoul tabel
partea din fereastra de interogare care afiseaza tabelele in care sunt gasite datele aflate in urma interogarii ;
pot fi afisate mai multe tabele;
b) Panoul de criterii
afiseaza criteriile folosite pentru a selecta inregistrari in setul selectat;
el nu este afisat cand se creaza prima data cererea de interogare, deoarece nu exista inca criterii definitive pentru a fi afisate;
c) Panoul de date
afiseaza rezultatele interogarii;
cand se creaza prima data cererea de interogare, panoul de date este gol;
trebuie sa se adauge la panoul de date campurile pe care dorim sa le afisam pentru ca acestea sa fie adaugate si la setul rezultat;
panoul de date afiseaza o coloana goala in partea sa dreapta, astfel
incat se poate determina cu usurinta unde trebuie adaugat un
Specificarea sursei de date
Pentru a selecta o sursa de date existenta in caseta de dialog Select Data Source, se alege numele sursei de date din caseta Available Data Source, apoi se alege butonul USE;
MS QUERY afiseaza pe ecran un format de interogare necompletat apoi afiseaza caseta Add Tables
Ascunderea si afisarea panourilor tabel
din meniul VIEW TABLES Show / Hide Tables
Ascunderea sau afisarea panoului de criterii
din meniul VIEW CRITERIA Show / Hide Criteria
Adaugarea sau inlaturarea tabelelor din panoul tabel
pentru adaugare:
din meniul TABLE ADD TABLE
se va afisa caseta de dialog Add Tables
In caseta Table Name se selecteaza tabelul (baza de date) din lista de sub caseta;
Se alege butonul ADD pentru a adauga fisierul la panoul tabel;
Se pot adauga cate baze de date dorim;
Caseta de dialog se inchide cu butonul CLOSE;
Cand se plaseaza un tabel in panoul-tabel, acesta este afisat ca o lista de campuri;
Lista de campuri este o fereastra care are numele tabelului in bara de titlu si contine o lista a tuturor campurilor din acest tabel;
Stergerea unui tabel din panoul-tabel
se executa un click oriunde in tabel;
se apasa tasta DELETE.
Adaugarea, inserarea si stergerea de campuri din panoul de date
pentru a adauga campuri unul cate unul, se executa dublu click pe numele de camp din lista de campuri sau trageti cu mouse-ul numele de camp din lista de campuri peste coloana goala din panoul de date;
pentru a adauga toate campurile odata : se da dublu click pe asterixul (*) de la inceputul listei de campuri sau trageti asterixul peste coloana goala din panoul de date;
pentru a insera un
pentru a inlatura un camp din panoul de date, se selecteaza coloana executand un click pe titlul corespunzator, apoi se da tasta DELETE.
Adaugarea criteriilor
din meniul VIEW CRITERIA Add Criteria.
se deschide fereastra Add Criteria.
in caseta Field selectam campul care contine valorile de indeplinit;
in caseta Operator se selecteaza conditia dorita;
in caseta Value se tasteaza valoarea ce se doreste indeplinita;
se alege butonul ADD;
cand s-au terminatde ales criteriile, se alege butonul CLOSE.
Executarea unei interogari
pentru a activa Auto Query se executa click pe butonul AUTO QUERY de pe bara de instrumente;
butonul ramane apasat pentru a arata ca optiunea Auto Query ramane activa; (se poate face acelasi lucru, alegand din meniul RECORDS Automatic Query Again )
atunci cand Auto Query este inactiva trebuie sa se execute interogarea manual pentru a actualiza panoul de date in cazul in care schimbam criteriile de interogare;
pentru a executa interogarea manual, selectati butonul Query Now !
sau din meniul RECORDS QUERY NOW
Salvarea si inchiderea unei interogari
in momentul terminarii creerii interogarii, daca mai avem nevoie de ea, se poate salva intr-un fisier cu extensia implicita .QRY excutandcomanda Save Query sau Save As. (din meniul FILE)
dupa salvarea interogarii, fereastra ramane afisata si interogarea deschisa;
pentru a inchide interogarea, se da din meniul FILE Close Query
se poate folosi comanda FILE Return Data To Excel
la intoarcerea datelor spre Excel, datele inserate in foaia de calcul sunt o copie a datelor regasite de MS Query.
ETAPE
FILE Return Data To Excel;
se va afisa caseta de dialog Get External Data
In caseta Destination se introduce numele foii de calcul si referinta celulei in care vor fi plasate datele;
Se selecteaza una din optiunile din caseta Options :
a) Keep Query Definition- pentru a se pastra interogarea folosita pentru a pastra setul rezultat ce se insereaza.
b) Include Field Name- pentru a include in foaia de calcul Excel pentru datele returnate numele de
campuri ca titluri de coloana.
c) Include Row Numbers- pentru a include in foaia de calcul Excel, numarul de linie.
d) Keep Password- pentru a salva parola la sursa externa de date.
multe din bazele de date in care se gasesc informatii sunt baze de date informationale;
pentru a regasi in diferite baze de date informatiile de care avem nevoieeste necesar sa se deplaseze mai multe tabele in panoul-tabel, dar sa si asociem aceste tabele prin intermediul campurilor de legatura;
din panoul-tabel sunt conectate prin linii de legatura care arata campurile de legatura;
in unele cazuri, Query plaseaza automat linii de legatura in panoul-tabel.
Adaugarea liniilor de legatura
Exista doua metode:
Cu mouse-ul se trage un camp dintr-un tabel peste cel cu care il asociem (din alt tabel)
Query adauga linia de legatura;
Daca vrem sa asociem 2 campuri care contin tipuri diferite de date, Query intreaba confirmarea legatura (o astfel de legatura nu este utila, deci se raspunde cu No).
Cu caseta de dialog JOINS
Etape
din meniul TABLE JOINS
se deschide caseta de dialog JOINS
in caseta Left se selecteaza tabelul si campul din stanga liniei de legatura;
in caseta Right se selecteaza tabelul si campul din dreapta liniei de legatura;
in caseta Operator se selecteaza un operator pentru a determina relatia relatia de inlantuire dintre campuri;
selectati una din optiunile 1), 2) sau 3)pentru a determina ce include legatura;
nu se pot folosi optiunile 2) si 3) daca exista mai mult6 de doua tabele in panoul-tabel;
se alege butonul ADD pentru a adauga linia de legatura la panoul-tabel;
se alege butonul CLOSE pentru inchiderea ferestrei de dialog.
Stergerea liniei de legatura
Exista 2 metode:
Cu mouse-ul se selecteaza linia de legatura si se apasa tasta DELETE.
Cu caseta de dialog JOINS
se selecteaza in caseta JOINS IN QUERRY linia de legatura ce dorimsa o stergem;
se alege butonul REMOVE.
Lucrul cu datele din setul rezultat
Ascunderea unei coloane
se da click oriunde in coloana ce dorim sa o ascundem;
din meniul FORMAT HIDE COLUMNS.
Afisarea unei coloane ascunse
din meniul FORMAT SHOW COLUMNS;
se selecteaza din lista Columns coloana pe care dorim sa o afisam, apoi se alege butonul SHOW.
OBS: coloanele ascunse nu au in dreptul lor un semn de validare.
Exista 2 metode:
Cu mouse-ul se trage de marginea coloanei;
Cu caseta COULMN WIDTH (din meniul FORMAT)
Modificarea ordinii coloanelor
se da un click in bara de titlu a coloanei;
se trage coloana de bara de titlu pentru a o aduce intr-o noua pozitie;
pe masura ce tragem coloana, o linie verticala va fi afisata indicand pozitia in care va fi inserata coloana.
Modificarea titlurilor de coloana
din meniul RECORDS EDIT COLUMN sau se da dublu click pe titlul coloanei;
in caseta COLUMN HEADING, se tasteaza noul titlu;
apoi se da OK.
Sortarea datelor
din meniul RECORDS SORT;
se deschide caseta de dialog SORT
DESEN
se selecteaza in caseta COLUMN coloana cu care dorim sa executam sortarea;
se alege : . Ascending sau Descending;
se da ADD
Query realizeaza sortarea datelor din coloana respectiva apoi adauga coloana la lista SORT IN QUERY;
se da CLOSE.
OPERATII CU INREGISTRARI SI CAMPURI
Trecerea de la o inregistrare la alta inregistrare
Folosim tastele de pozitionare.
Inregistrarea curenta contine puncte de inserare si are o sageata in selectorul de inregistrare.
Editarea datelor
pentru a activa / dezactiva optiunea de editare se alege Records Allow Editing. Cand aceasta optiune este activa, imediat langa coloana apare un semn de validare.
nu se pot edita date atunci cand avem de-a face cu tabele multiple, campuri calculate sau blocate.
Obs
Editarea datelor in panoul de date afecteaza datele din documentul-sursa.
Adaugarea inregistrarilor
ori de cate ori se activeaza optiunea Allow Editing, Query adauga o inregistrare goala la sfarsitul setului rezultat;
in selectorul din stanga inregistrarii goale apare un (*) asterix.
Etape
din meniul RECORD ALLOW EDITING;
se trece la primul camp al inregistrarii goale si introduceti valoarea pentru camp, apoi completati toate campurile;
se apasa tasta TAB pentru a trece la urmatoarea inregistrare goala;
Obs:
Daca setul rezultat este sortat, noua inregistrare trece in locatia corespunzatoare ordinii de sortare;
daca avem introduse criterii de selectie si se introduce o noua inregistrare care nu corespunde criteriilor specificate, noua inregistrare este adaugata bazei de date sursa, dar nu este afisata in panoul de date.
Stergerea inregistrarilor
se selecteaza inregistrarea, apoi DELETE;
se cere confirmarea stergerii.
Instalarea programului Microsoft Query si Microsoft Query Add-In
se face folosind programul Excel setup;
daca nu ati instalat aceste componente atunci cand ati instalat prima data Excel, se poate rula programul Excel Setup in orice moment pentru a adauga aceste componente.
ETAPE
se da click pe butonul START SETTINGS CONTROL PANEL;
se da dublu click pe Add/ Remove Programs;
se selecteaza eticheta Install / Uninstall si din caseta se alege MS Office;
fisierele aplicatiei MS Query impreuna cu fisierele HELP si fisierele drivere ODBC sunt incluse in optiunea Data Acces;
selectati optiunile:- CONVERTERS ; - Filter and Data Acces;
alegeti butonul CHANGE OPTIONS pentru a afisa DATA ACCES;
alegeti butonul CONTINUE si urmati instructiunile pentru terminarea instalarii.
Instalarea in Excel a programului MS Query Add-In
pentru a face MS Query disponibil in Excel trebuie sa folosim MS Query Add-In. Acesta este memorat intr-un fisier de pe HD numit XLQUERY. XLA (in dosarul \ LIBRARY\ MS QUERY aflat in Excel)
programul MS Query Add In adauga la meniul DATA comanda GET EXTERNAL DATA. Daca aceasta comanda nu este prezenta este nevoie de activarea programului MS Query Add-In.
Pentru a activa acest program se parcurg etapele:
din meniul TOOLS Add-Ins;
apare caseta de dialog Add Ins ( cu lista Add Ins Available )
din lista se alege MS Query Add In astfel incat un semn de validare apare in caseta din stanga listei.
Se da OK.
daca MS Query Add In nu apare in lista, se selecteaza butonul BROWSE pentru a cauta fisierul XLQUERY. XLA
TEMA LABORATOR
sa se creeze directorul "JAN 21" pe HDD
sa se lanseze aplicatia FOXPRO.
sa se creeze in "JAN 21" bazele de date :
LOCATARI. DBF
PLATI. DBF
a) COD BLOC , C , 4
NR. AP. , N, 2
NUME FAM, C, 30
NR. PERS. , N, 2
b) COD BLOC C, 4
NR.AP. N, 2
INTRETINERE N, 6
RESTANTE N, 7
in LOCATARI. DBF 12 inregistrari:
3 cu COD BLOC = 10 G
5 cu COD BLOC = 10 F
4 cu COD BLOC = 11 A
campul RESTANTE nu este obligatoriu pentru toti locatarii;
in baza de date PLATI. DBF se aduc date (cu APPE FROM)
se inchide FOX;
se lanseaza EXCEL, apoi MS Query;
pentru interogare se folosesc bazele de date create anterior;
intre cele 2 baze de date vor exista 2 campuri de legatura;
in panoul de date se vor afisa campurile: COD BLOC, NR. AP, NUME FAM, INTRET, RESTANTE
se vor afisa numai locatarii cu codul blocului 10F cu restante;
se vor afisa toate inregistrarile;
se vor transfera datele in EXCEL;
la sfarsitul listei se vor adauga 2 campuri: PENALIZARI, TOTAL PLATA;
campul " Penalizari " se va calcula numai pentru cei cu restante, ca fiind 0, 5* Restante (functia IF)
campul " Total plata " se va calcula ca fiind Restante + Intretinere + Penalizari
se formateaza domeniul listei;
situatia va avea un titlu;
foaia de calcul se va numi " Plati "
in dreapta jos se va afisa data si ora curenta (folosind FILE PAGE SETUP HEADER/FOOTER;
situatia se va lista la imprimanta.
TEMA LABORATOR
(Tabele pivotante )
multe inregistrari diferite;
se deschide registrul "Produse" si foaia "Vanzari";
se copiaza lista din foaia "Vanzari" intr-un nou registru numit "19 Jan";
sa se realizeze un tabel pivotant din aceasta lista in aceeasi foaie de lucru;
- se vor cumula datele "Cantit. Vanduta" si "Val. Vanduta" pentru fiecare produs in parte ( aceste
campuri trebuie trase in zona Data);
articolele dintr-un
campul-pagina va fi "cod raion" (se trage in zona Page);
sa se sorteze tabelul-pivot dupa denumirea produsului in ordine descrescatoare;
sa se adauge campul pagina "cod produs";
sa se imparta paginile din tabelul-pivotin foi de calcul separate;impartirea se va face dupa campul "cod raion";
sa se vizualizeze paginile (foile de calcul cu produsele respective)
TEMA LABORATOR
(DIAGRAME)
se creaza mapa de lucru "ANGAJATI" in folderul Personal;
se creaza o lista cu urmatoarea structura: MARCA, Nume si prenume, Functia, Salariul brut, Impozit.
impozitul se va calcula ca fiind 15% din Salariu brut;
se introduc 8 inregistrari;
se sorteaza lista dupa "Nume si prenume";
se realizeaza o diagrama 3 - D COLUMN in aceeasi foaie de lucru;
diagrama se realizeaza dupa coloanele: "Nume si prenume", "Salariu brut" si "Impozit";
diageama va avea un titlu si o legenda;
se adauga titluri de axa diagramei si linii de grila;
se adauga etichete de date pentru diagrama;
se modifica marginile, culoarea si modelul markerilor de date;
se alege o culoare si fontul ARIAL + Bold + 12 pentru legenda;
legenda se plaseaza in coltul dreapta-sus;
se alege o culoare pentru zona diagramei;
se va formata baza diagramei;
pentru peretii diagramei se alege culoarea chenarului si grosimea.
|
