Adrese, formule. Formatarea documentelor Excel

Enuntul temei

Pornind de la un fisier brut (neformatat si care nu contine nici o formula) ce include 3 foi de calcul, se vor insera formulele necesare si se va face un minim de formatare menita sa asigure un aspect corespunzator al documentului.

Documentul Excel initial contine foile de calcul: Produse, Servicii, Centralizare. Foaia de calcul Produse (vezi figura 1) contine īn zona C4:G13 un tabel care furnizeaza informatii despre produsele aprovizionate, pretul unitar de intrare si cantitatea aprovizionata. Pe baza acestor date se va determina valoarea stocului pentru fiecare produs, īn LEI si īn EURO. Fireste Valoare stoc in LEI=Pret intrare x Cantitate [buc]. Pentru aflarea valorii stocului īn EURO, valoarea stocului īn LEI se īmparte (pentru fiecare produs) la cursul LEU/EURO. Paritatea LEU/EURO este continuta īn celula I3. Tabelul mai dispune  si de o celula TOTAL, care contine totalul valorii stocului īn EURO.

Foaia de calcul Servicii (vezi figura 2) contine īn zona C4:E10 un tabel care īnregistreaza o lista de servicii si tarifele percepute, exprimate īn LEI si respectiv EURO. Exista si aici un TOTAL, care sumeaza tarifele exprimate īn EURO.

Foaia de calcul Centralizare (vezi figura 3) contine īn zona E4:K7 un tabel centralizator care aduna date din foile de calcul Produse si Servicii, īn scopul calcularii unui sold (celula K7). Soldul reprezinta diferenta dintre īncasari si diferitele tipuri de cheltuieli. Īn esenta soldul pozitiv īnseamna profit, iar un sold negativ ar īnsemna o pierdere.

Īn ceea ce priveste formatarea documentului, se vor urmari tot figurile 1, 2 si 3.

Se mai fac urmatoarele precizari legate de formatarea tabelelor din foile de calcul:

Toate celulele (din toate cele trei foi de calcul) ce contin valori exprimate īn LEI sau EURO sunt formatate cu separator de mii si cu doua zecimale. Valorile ce exprima cantitatea (exprimata īn bucati) sunt formatate cu zero zecimale;

Pentru foaia de calcul Produse, zona G5-G12 se formateaza conditionat astfel: pentru valori mai mari sau egale cu 1000 EURO, se va folosi culoarea rosie;

Celula K7, din foaia de calcul Centralizare se formateaza conditionat astfel: pentru valori mai mici decāt 4000 EURO se va folosi un fond galben fosforescent pentru celula;

Pentru foaia de calcul Centralizare, se va ascunde caroiajul implicit;

Capetele de tabel se formateaza astfel īncāt continutul sa se dispuna centrat pe verticala si pe orizontala;

Acolo unde continutul unei celule nu are loc integral, se va folosi scrierea pe mai multe rānduri. Trecerea fortata pe urmatorul rānd se face prin apasarea simultana a tastelor ALT+ENTER;

Pentru foaia de calcul Centralizare, Incasarile [EURO] si Cheltuielile [EURO] se scriu īn celulele E4, respectiv H4, dar se centreaza pe zonele E4:G4, respectiv H4:J4.

Figura 1

Figura 2 - foaia de calcul Servicii

Figura 3 - foaia de calcul Centralizare

Mod de lucru

1. Se īncepe cu completarea foii de calcul Produse. Īn celula F5 se scrie formula =D5*E5. Pentru rapiditate, dupa inserarea caracterului =, se puncteaza [cu mouse-ul] īn celula D5 (īn F5 se va scrie automat adresa D5), se tasteaza caracterul *, dupa care se puncteaza celula E5 (īn F5 se va scrie automat adresa E5). Celulele F6:F12 se completeaza prin copiere īn tehnica drag & drop (se selecteaza celula sursa - F5 - si se trage īn jos de coltul dreapta jos al celulei F5 - cel unde apare un patratel; cānd pointerul de mouse se gaseste īn zona patratelului, pointerul īsi schimba aspectul - vezi figura 4).
2. Īn celula G5 (tot din foaia de calcul Produse) se insereaza formula = F5/I3, folosind punctarea cu mouse-ul - nu se scrie efectiv. Copierea īn zona G6:G12 se va face tot folosind tehnica drag & drop (ca mai sus), dar nu īnainte de a face o mica corectura formulei din celula G5: se va insera un caracter $ īnaintea numelui rāndului, astfel ca se va ajunge la formula F5/I$3. Acest tip de adresa se numeste mixta pentru ca pentru coloana s-a folosit adresarea relativa, iar pentru rānd cea absoluta. S-ar fi putut insera semnul $ si īnaintea adresei de coloana dar, deoarece la copierea formulei se modifica numai pozitia relativa la nivel de rānd (nu si de coloana) nu ar fi fost de nici un ajutor.

Īn celula G13 se īnscrie formula =SUM (G5:G12). Se poate scrie direct, sau se poate folosi butonul   , urmānd ca adresele sa se obtina prin punctarea īn celula G5 si "tragere" pāna la celula G12.

Figura 4

3. Se continua cu foaia de calcul Servicii. Aici se completeaza valorile din celulele D5:D9. Īn celula E5 se insereaza formula =D5/Produse!I$3. Se poate scrie efectiv, sau se poate folosi punctarea cu mouse-ul. Atentie, la completarea numitorului se va puncta īn celula I3 din foaia de calcul Produse. S-a stabilit astfel o legatura īntre o celula dintr-o foaie de calcul si una din alta foaie de calcul. Inserarea totalului din celula E10 a foii Servicii se face asa cum s-a facut īn cazul celulei G13 din foaia de calcul Produse.
4. Īn foaia de calcul Centralizare, se executa doua mari categorii de operatii: se preiau date din celelalte doua foi de calcul si apoi acestea sunt prelucrate prin intermediul unor formule (expresii) simple. Īn figura 5 se poate vedea care sunt celulele īn care se preiau date (cele īn care īn adrese apar numele celorlalte doua foi de calcul). Īn alte celule pur si simplu se īnscriu valori, pentru ca īn altele sa apara sume sau o simpla diferenta.

Figura 5

5. Pentru aplicarea caracteristicilor de formatare se fac mai jos o serie de precizari:

Pentru alinierea continutului celulelor capetelor de tabel, se va folosi cāmpul Format Celule Aliniere. Se alege optiunea de aliniere Centru, atāt pe orizontala cāt si pe verticala. La fel se va proceda pentru toate tabele vezi figura 6.

Alinierile pe orizontala se pot seta si cu ajutorul butoanelor .

 

Figura 6

Amplasarea de chenare de diverse tipuri se poate face cu ajutorul cāmpului Format Celule Bordura - vezi figura 7. Īn sectiunea Bordura se poate alege tipul de linie cu care se va trasa chenarul (Linie/Stil), laturile pe care se va desena chenarul (Bordura), sau culoarea cu care se va desena chenarul. Este posibila amplasarea rapida a unui chenar si cu ajutorul butonului Īn figura 8 se poate vedea ce variante de amplasare a unui chenar se pot alege īn acest caz.

Pentru colorarea fondului unei celule se va utiliza cāmpul Format Celule Modele - vezi figura 10. Se poate alege o culoare si/sau un model care ar putea umple celula/celulele selectate. Pentru alegerea unei culori, se poate folosi si butonul . Īn figura 9 se poate vedea cum se poate alege o culoare īn acest fel.

Figura 7

Figura 8

Figura 9

Figura 10

Pentru formatarea cu separatori de mii, sau pentru alegerea formatului Simbol monetar se va folosi cāmpul Format Celule Numar. Īn figurile 11 si 12 se poate vedea ce setari au fost alese pentru formatarea īn sistem Simbol monetar, respectiv cu separatori de mii. 

Figura 11

Figura 12

Pentru formatarea conditionala, se va utiliza cāmpul Format Formate conditionale. Dupa cum se poate vedea īn figura 13, pentru o celula se pot preciza un numar maxim de trei conditii de formatare, īntre care opereaza o relatia logica de tip SAU.

Figura 13

Dupa precizarea conditiei (Conditia 1, 2 sau 3), se apasa butonul Format, care conduce la o caseta de dialog de tipul celei din figura 14, cu ajutorul careia se pot aplica celulei/celulelor selectata/selectate anumite caracteristici de formatare:

stilul de font, culoarea caracterelor, optiunile de subliniere (sectiunea Font);

chenare (sectiunea Bordura - se lucreaza ca īn cazul aplicarii normale a chenarelor);

un anumit fundal (sectiunea Modele).

Figura 14

6. Blocarea la modificare a  unei arii dintr-o foaie de calcul (celulele D5:D12 si F5:F12 din foaia de calcul Produse), se va realiza astfel:

Se selecteaza īntreaga foaie de calcul prin executarea unui clic stānga pe coltul din stānga sus al foii de calcul - vezi figura 15.

Figura 15

Se foloseste cāmpul Format Celule Protectie. Se va ajunge la caseta de dialog Format celule, sectiunea Protectie - vezi figura 16. Se anuleaza bifa din dreptul cāmpului Blocate.

Se selecteaza zonele D5:D12 si F5:F12 din foaia de calcul Produse (se va folosi tasta CTRL pentru selectia unor zone neadiacente). Se acceseaza īnca o data caseta de dialog Format celule, sectiunea Protectie si se reactiveaza bifa din cāmpul Blocate - vezi figura 16.

Figura 16

Se foloseste cāmpul Instrumente Protectie Protejare foaie (se poate alege varianta Protejare registru de lucru, caz īn care se va proteja īntregul document si nu numai foaia de calcul curenta) si se ajunge astfel la caseta de dialog Protejare foaie - vezi figura 17. Aici se poate preciza (nu este obligatoriu) si o parola care ar fi solicitata la de-protejarea foii (documentului) - atentie, parola odata uitata, nu exista nici o posibilitate de a mai face foaia (sau documentul) editabila - sau se poate preciza la nivel de amanunt ce operatii mai pot fi executate asupra foii protejate.

Figura 17

Din acest moment zonele D5:D12 si F5:F12 nu mai sunt practic accesibile pentru nici o operatie de formatare sau editare.

Atentie !

Se propune reluarea exercitiului cu schimbarea stilului de adresare. Astfel īn loc de stilul de adresare A1, implicit, se va utiliza stilul de adresare R1C1. Schimbarea stilului de adresare se poate face cu ajutorul casetei de dialog Optiuni, sectiunea General (vezi figura 18).  Zona de interes Setari Stil referinta R1C1 este evidentiata īn figura 18.

Figura 18

Pentru a se putea vedea legaturile dintre celule (dinspre tinta catre sursa) fara sa se faca o citire a formulelor, este posibila vizualizarea acestor legaturi cu ajutorul facilitatii Instrumente Formula de audit Trasare precedente. Este posibila si vizualizarea legaturilor dinspre sursa catre tinta, se poate folosi cāmpul Instrumente Formula de audit Trasare dependente. Īn ambele cazuri este necesara selectia celulei tinta (sau sursa) dupa care se actioneaza cāmpul Instrumente Formula de audit Trasare precedente, sau Instrumente Formula de audit Trasare dependente. Efectul ce se va obtine va fi de tipul celui prezentat īn figura 19 (este vorba de o operatie de tip Trasare precedente). Au fost selectate celulele G13, G5:G7.

Figura 19