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