Manual Excel

www.controller-institut.ro

MS Excel -suport curs-

Cuprins: 1.

INTRODUCERE ......................................................................... .............................................................................................................................. ..................................................... 1

1.1. 1.2. 2.

MICROSOFT EXCEL ........................................................................... .................................................................................................................... ......................................... 1 CUM SPRIJINA MS EXCEL CONTABILITATEA MANAGERIALA - EXEMPLE........................................... 1

FUNCTIILE DE BAZA..................................................................................................................... 2

2.1. DESCHIDEREA EXCEL ....................................................................... ................................................................................................................ ......................................... 2 2.2. NAVIGAREA IN FOILE DE LUCRU ....................................................................... ................................................................................................... ............................ 2 2.2.1. Selectarea unei celule ......................................................................... ..................................................................................................... ............................ 2 2.2.2. Folosirea combinatiilor de taste pentru navigarea in foile de lucru ................................. 3 2.3. SELECTAREA UNUI GRUP DE CELULE ........................................................................... ........................................................................................... ................ 5 2.3.1. Selectarea unui rand ....................................................................................................... ....................................................................................................... 5 2.3.2. Selectarea unei coloane.................................................................................................. 5 2.3.3. Selectarea tuturor celulelor dintr-o foaie de lucru ........................................................... 5 2.3.4. Selectarea celulelor adiacente ........................................................................ ........................................................................................ ................ 6 2.3.5. Selectarea celulelor neadiacente ..................................................................... .................................................................................... ............... 6 2.4. DESPRE FORMULE SI REFERINTE IN CADRUL CELULELOR .............................................................. 7 2.4.1. Crearea de formule....................................................................................................... formule.......................................................................................................... ... 7 2.4.2. Definirea de nume .................................................................. ......................................................................................................... ....................................... 14 3.

ANALIZA ANALIZA PROBLEMELOR SI GENERAREA GENERAREA DE SOLUTII CU AJUTORUL MS-EXCEL MS-EXCEL ......... ......... 18

3.1. EXERCITIU - EXEMPLUL „ANALIZA BILANTULUI CONTABIL“ ........... ................. ........... ........... ........... ........... ............ ............ ............ ........ .. 18 3.2. PREGATIREA DATELOR ...................................................................... ............................................................................................................. ....................................... 19 3.2.1. Fixarea coloanei si/sau randului la o anumita latime si/sau inaltime ............................ 19 3.2.2. Stergerea celulelor, coloanelor, randurilor, continutului unei celule.............................. 22 3.2.3. Inserarea de celule, coloane si randuri r anduri ......................................................................... ......................................................................... 26 3.2.4. Formatarea celulelor............................................................................ celulelor ...................................................................................................... .......................... 27 3.2.5. Aplicarea de margini, linii, culori, modele ..................................................................... ....................................................................... 38 3.2.6. Aplicarea liniilor de grila.................................................................................... grila................................................................................................. ............. 44 3.2.7. Definirea strategiei de design pentru afisarea rezultatelor............................................ 45 3.2.8. Folosirea valorilor de control al intrarilor de date .......................................................... 46 3.2.9. Afisarea si ascunderea liniilor si coloanelor .................................................................. 47 3.2.10. Gruparea continutului foii de lucru (07-Analysis.xls) ..................................................... 48 3.2.11. Blocarea unei ferestre ................................................................................................... ................................................................................................... 53 3.3. FOLOSIREA CALCULELOR IN FISIER.................................................................. ............................................................................................ .......................... 55 3.3.1. Impartirea in ecrane......................................................................................... ecrane....................................................................................................... .............. 55 3.3.2. Evaluarea formulelor folosind auditarea de formula...................................................... 56 3.4. PROTEJAREA SI SALVAREA FISIERULUI DE LUCRU..................................................................... ....................................................................... 58 3.4.1. Protejarea Celulelor ................................................................ ....................................................................................................... ....................................... 58 3.4.2. Protejarea structurii Fisierului de Lucru......................................................................... 61 3.4.3. Protejarea ferestrelor fisierului de Lucru ....................................................................... 61 3.4.4. Protejarea fisierului si a foilor foil or de lucru .......................................................................... .......................................................................... 62 3.5. CREAREA DE HYPERLINK-URI CATRE ZONE IN CADRUL UNUI FISIER .............................................. 64 4.

FOLOSIREA FUNCTIILOR LOGICE.................................................................. LOGICE ............................................................................................ .......................... 65

4.1. 4.2.

APLICAREA FUNCTIEI IF............................................................................................................ 65 APLICAREA FUNCTIILOR SUMIF SI COUNTIF .......................................................................... ............................................................................ 67

5. EVIDENTIEREA OPERATIILOR CU FORMATARE CONDITIONATA – CODIFICAREA CU AJUTORUL CULORILOR ................................................................ .................................................................................................................... .................................................... 68

Cuprins: 1.

INTRODUCERE ......................................................................... .............................................................................................................................. ..................................................... 1

1.1. 1.2. 2.

MICROSOFT EXCEL ........................................................................... .................................................................................................................... ......................................... 1 CUM SPRIJINA MS EXCEL CONTABILITATEA MANAGERIALA - EXEMPLE........................................... 1

FUNCTIILE DE BAZA..................................................................................................................... 2

2.1. DESCHIDEREA EXCEL ....................................................................... ................................................................................................................ ......................................... 2 2.2. NAVIGAREA IN FOILE DE LUCRU ....................................................................... ................................................................................................... ............................ 2 2.2.1. Selectarea unei celule ......................................................................... ..................................................................................................... ............................ 2 2.2.2. Folosirea combinatiilor de taste pentru navigarea in foile de lucru ................................. 3 2.3. SELECTAREA UNUI GRUP DE CELULE ........................................................................... ........................................................................................... ................ 5 2.3.1. Selectarea unui rand ....................................................................................................... ....................................................................................................... 5 2.3.2. Selectarea unei coloane.................................................................................................. 5 2.3.3. Selectarea tuturor celulelor dintr-o foaie de lucru ........................................................... 5 2.3.4. Selectarea celulelor adiacente ........................................................................ ........................................................................................ ................ 6 2.3.5. Selectarea celulelor neadiacente ..................................................................... .................................................................................... ............... 6 2.4. DESPRE FORMULE SI REFERINTE IN CADRUL CELULELOR .............................................................. 7 2.4.1. Crearea de formule....................................................................................................... formule.......................................................................................................... ... 7 2.4.2. Definirea de nume .................................................................. ......................................................................................................... ....................................... 14 3.

ANALIZA ANALIZA PROBLEMELOR SI GENERAREA GENERAREA DE SOLUTII CU AJUTORUL MS-EXCEL MS-EXCEL ......... ......... 18

3.1. EXERCITIU - EXEMPLUL „ANALIZA BILANTULUI CONTABIL“ ........... ................. ........... ........... ........... ........... ............ ............ ............ ........ .. 18 3.2. PREGATIREA DATELOR ...................................................................... ............................................................................................................. ....................................... 19 3.2.1. Fixarea coloanei si/sau randului la o anumita latime si/sau inaltime ............................ 19 3.2.2. Stergerea celulelor, coloanelor, randurilor, continutului unei celule.............................. 22 3.2.3. Inserarea de celule, coloane si randuri r anduri ......................................................................... ......................................................................... 26 3.2.4. Formatarea celulelor............................................................................ celulelor ...................................................................................................... .......................... 27 3.2.5. Aplicarea de margini, linii, culori, modele ..................................................................... ....................................................................... 38 3.2.6. Aplicarea liniilor de grila.................................................................................... grila................................................................................................. ............. 44 3.2.7. Definirea strategiei de design pentru afisarea rezultatelor............................................ 45 3.2.8. Folosirea valorilor de control al intrarilor de date .......................................................... 46 3.2.9. Afisarea si ascunderea liniilor si coloanelor .................................................................. 47 3.2.10. Gruparea continutului foii de lucru (07-Analysis.xls) ..................................................... 48 3.2.11. Blocarea unei ferestre ................................................................................................... ................................................................................................... 53 3.3. FOLOSIREA CALCULELOR IN FISIER.................................................................. ............................................................................................ .......................... 55 3.3.1. Impartirea in ecrane......................................................................................... ecrane....................................................................................................... .............. 55 3.3.2. Evaluarea formulelor folosind auditarea de formula...................................................... 56 3.4. PROTEJAREA SI SALVAREA FISIERULUI DE LUCRU..................................................................... ....................................................................... 58 3.4.1. Protejarea Celulelor ................................................................ ....................................................................................................... ....................................... 58 3.4.2. Protejarea structurii Fisierului de Lucru......................................................................... 61 3.4.3. Protejarea ferestrelor fisierului de Lucru ....................................................................... 61 3.4.4. Protejarea fisierului si a foilor foil or de lucru .......................................................................... .......................................................................... 62 3.5. CREAREA DE HYPERLINK-URI CATRE ZONE IN CADRUL UNUI FISIER .............................................. 64 4.

FOLOSIREA FUNCTIILOR LOGICE.................................................................. LOGICE ............................................................................................ .......................... 65

4.1. 4.2.

APLICAREA FUNCTIEI IF............................................................................................................ 65 APLICAREA FUNCTIILOR SUMIF SI COUNTIF .......................................................................... ............................................................................ 67

5. EVIDENTIEREA OPERATIILOR CU FORMATARE CONDITIONATA – CODIFICAREA CU AJUTORUL CULORILOR ................................................................ .................................................................................................................... .................................................... 68

6.

UTILIZAREA VALIDARII DATELOR........................................................................................... DATELOR............................................................................................. 71

7.

FOLOSIREA FORMULELOR MATRICIALE (VLOOKUP SI HLOOKUP) ................................... 73

8. ANALIZA REZULTATULUI: CUM ESTE REZULTATUL AFECTAT DE MODIFICAREA VALORILOR DE INTRARE ........................................................................... .................................................................................................................. ....................................... 75

8.1. ANALIZA RANDAMENTULUI INVESTITIEI INVESTITIEI ......................................................................... ....................................................................................... .............. 75 8.2. FOLOSIREA GOAL SEEK .................................................................... ........................................................................................................... ....................................... 76 8.3. FOLOSIREA OPTIUNII DE CALCUL TABEL IN CADRUL TABELELOR DE DATE ..................................... 79 8.3.1. Tabele de date cu o singura variabila ........................................................................... ........................................................................... 79 8.3.2. Tabele de date cu doua variabile .................................................................... .................................................................................. .............. 80 8.4. CREAREA DE SCENARII ...................................................................... ............................................................................................................. ....................................... 81 8.4.1. Crearea de Scenarii......................................................................................... Scenarii....................................................................................................... .............. 82 8.4.2. Afisarea unui scenariu selectat in foaia de lucru........................................................... 83 8.4.3. Crearea unui Raport de Scenariu.................................................................................. 83 9.

CREAREA RAPOARTELOR DE CONTABILITATE MANAGERIALA MANAGERIALA CU MS EXCEL.............. 84

9.1. FOLOSIREA RAPOARTELOR ................................................................ ....................................................................................................... ....................................... 84 9.2. CREAREA DE GRAFICE PENTRU VIZUALIZAREA DATELOR............................................................. 86 9.2.1. Prezentarea grafica a datelor .......................................................................... ........................................................................................ .............. 86 9.2.2. Despre ChartWizard...................................................................................................... 88 9.2.3. Crearea unui portofoliu de analiza cu matricea B.C.G.................................................. 98 9.2.4. Displaying Charts in the Background .......................................................................... 105 9.3. CREAREA RAPOARTELOR IN FORMAT TEXT- COPIEREA DATELOR DIN EXCEL IN WORD........... ................ ..... 106 9.3.1. Inserarea datelor din Excel intr-un document Word.................................................... 106 9.3.2. Crearea unei legaturi intre un document Word si datele din din Excel ............................. 107 9.4. PERSONALIZAREA RAPOARTELOR RAPOARTELOR – S – SELECTAREA SI CONSOLIDAREA CONSOLIDAREA DATELOR ORIENTATE CATRE ...................................................................................................................................... .............................................................. 109 DESTINATAR ........................................................................ 9.4.1. Definirea setarilor pentru printare................................................................................ 110 9.4.2. Crearea de vizualizari personalizate ............................................................... ........................................................................... ............ 115 9.4.3. Working with the Report Manager............................................................................... 120 10.

ANALIZA DATELOR ......................................................................... .............................................................................................................. ..................................... 123

10.1. SORTAREA SI FILTRAREA DATELOR SI OBTINEREA DE SUBTOTALURI .......................................... 123 10.1.1. Sortarea datelor........................................................................................................... 123 10.1.2. Filtrarea datelor – Folosirea Comenzii AutoFilter (Filtrare Automata)......................... 124 10.1.3. Calcularea valorilor subtotale si totale......................................................................... totale ......................................................................... 126 126 10.2. OPTIUNILE PIVOTTABLE .................................................................... ......................................................................................................... ..................................... 128 10.2.1. Crearea listelor interactive cu ajutorul PivotTable pentru rezumarea si analizarea datelor 128 10.2.2. Examplu de analiza a vanzarilor (Partea a-2-a) – Exercitiul 1 .................................... 129 10.2.3. Exemple de analiza a vanzarilor (Partea 2)–Exercitiul 2............................................. 134 10.2.4. Personalizarea PivotTable in exemplul “Analiza vanzarilor” (Partea 2)–Exercitiul 3 .. 135 10.2.5. Afisarea paginilor .................................................................... ......................................................................................................... ..................................... 136 10.2.6. Afisarea datelor sursa a unei celule intr-o serie de date ............................................. 136 10.2.7. Actualizarea datelor in PivotTable............................................................................... 136 11.

COMBINATII DE TASTE FOLOSITOARE .................................................................. ............................................................................. ........... 137

1.

Introducere

1.1.

Microsoft Excel

Microsoft Excel (MS Excel) este un program creat de Microsoft Corporation pentru a furniza utilizatorilor calculatoarelor numeroase functii ce permit organizarea si/sau prezentarea grafica a datelor si analizarea informatiilor prin functii de baze de date. Aplicatia poate fi folosita pentru realizarea a numeroase calcule matematice, financiar-matematice si statistice. Mai mult, exista o multitudine de functii speciale care permit utilizatorilor, de exemplu, sa compare date logic, sa consolideze date si sa foloseasca proceduri de repetare. Limbajul de programare Microsoft Visual Basic permite utilizatorilor sa automatizeze serii de actiuni recurente prin folosirea unui instrument de inregistrare macro si sa genereze aplicatii complete. Ideea de baza a unui astfel de program de calcul tabelar este sa reflecte o cale familiara de utilizare a foii de hartie, a creionului si a calculatorului de birou prin tehnologia moderna a computerului. Zona de lucru este reprezentata de foaia de lucru a carei sectiune curenta este afisata pe ecran. Un fisier este alcatuit din una sau mai multe foi de lucru si poate fi salvat ca un fisier Excel. Celulele reprezinta cea mai mica parte a fisierului Excel. Fiecare celula este definita concret prin litera coloanei sale (de la A la IV) si prin numarul liniei sale (de la 1 la 65536) si poate contine numere, date text, formule si functii cu referire la alte celule din foaia de lucru sau chiar din alte fisiere Excel.

1.2. 





Cum sprijina MS Excel contabilitatea manageriala - Exemple Procesul de planificare (stabilirea bugetului) Dezvoltarea, de exemplu, a unui sistem de planificare intr-o organizatie prin introducerea unui centru de cost in fiecare foaie de lucru si prin calculul sumei datelor introduse Raportare Personalizarea, de exemplu, a datelor pentru anumiti destinatari si prezentarea grafica a informatiilor (diagrame, grafice) Crearea de materiale specifice contabilitatii manageriale pentru persoanele care se afla in functii de decizie Dezvoltarea, de exemplu, a unei analize a investitiilor, a unui sistem de analiza a costurilor pe baza activitatilor (ABC), analiza crearii de valore pentru actionari.

© Controller Institut Romania/ Contrast Management Consulting & Training

Pagina 1/137

2. 2.1.

Functiile de baza Deschiderea Excel

Programul Excel se deschide prin aceeasi modalitate prin care se deschid si celelalte aplicatii Microsoft Windows, printr-un click pe comanda Programe din meniul Start si apoi printr-un click pe programul Microsoft Excel.

2.2.

Navigarea in foile de lucru

2.2.1.

Selectarea unei celule

O celula se selecteaza prin mutarea cursorului pe celula dorita si prin apasarea butonului stang al mouse-ului. Marginile celulei active sunt evidentiate prin ingrosare, iar adresa celulei apare in caseta de nume.

Caseta de nume

Bara de formula


Pagina 2/137

Pentru a finaliza introducerea datelor in celula activa, apasati ENTER. Puteti proceda in felul urmator (in conformitate cu setarile standard ale Excel): ENTER ENTER + SHIFT TAB

Muta celula activa cu un rand in jos Muta celula activa cu un rand in sus Muta celula activa cu o coloana la dreapta

(Cand doriti introducerea de valori in lista, de exemplu: daca ajungeti la ultima celula din lista de date introdusa si apasati tasta ENTER, celula activa devine in mod automat cea de la inceputul listei) Setarile de baza ale Excel se pot modifica printr-un click pe Options (Optiuni) din meniul Tools (Unelte) si prin selectarea butonului de Edit (Editare). Pentru modificarea sau editarea continutului unei celule existente:   

Tastati F2 sau Faceti click stanga in bara de formula sau Faceti dublu click pe celula activa

Microsoft Excel distinge intre datele introduse in format text, care sunt aliniate la stanga, si datele introduse de format numar, care sunt aliniate la dreapta.

2.2.2. lucru

Folosirea combinatiilor de taste pentru navigarea in foile de

Mutarea pe o celula la stanga: Mutarea pe o celula la dreapta: Mutarea pe o celula in sus: Mutarea pe o celula in jos:

← → ↑ ↓

Mutarea la inceputul randului: Mutarea la inceputul unei foi de lucru (celula A1): Mutarea la marginea inferioara a regiunii de date dintr-o foaie de lucru (ultima celula care contine valori): Mutarea la marginea stanga a regiunii curente de date: Mutarea la marginea dreapta a regiunii curente de date: Mutarea la marginea superioara a regiunii curente de date: Mutarea la marginea inferioara a regiunii curente de date:

HOME CTRL + HOME CTRL + END

Navigarea intre foile de lucru:

CTRL + PAGE UP ↑ CTRL + PAGE DOWN ↓ CTRL + SHIFT ALT + TAB

Selectarea unei regiuni de date: Navigarea intre diferite programe:

CTRL + ← CTRL + → CTRL + ↑ CTRL + ↓


Pagina 3/137

Sfat

Aceste combinatii de taste permit o navigare si selectare mai rapida in cadrul unor regiuni de date de dimensiuni mari decat prin folosirea mouse-ului. Folosirea combinatiilor de taste este o metoda ajutatoare atunci cand se doreste crearea unei formule si trebuie selectate regiuni de date in foaia de lucru.


Pagina 4/137

2.3.

2.3.1.

Selectarea unui grup de celule

Selectarea unui rand

Pentru a selecta un rand intreg, faceti click stanga pe numarul randului respectiv situat in capatul acestuia. Daca doriti selectarea unui grup de randuri adiacente tineti apasat butonul mouse-ului si miscati cursorul peste numarul tuturor acestor randuri. Pentru selectarea liniei celulei active se poate folosi si combinatia de taste SHIFT + SPACEBAR.

2.3.2.

Selectarea unei coloane

Pentru a selecta o coloana intreaga faceti click stanga pe litera coloanei respective situate in capatul acesteia. Daca doriti selectarea unui grup de coloane adiacente tineti apasat butonul mouse-ului si miscati cursorul peste litera tuturor acestor coloane. Pentru selectarea coloanei celulei active se poate folosi si combinatia de taste CTRL + SPACEBAR.

2.3.3.

Selectarea tuturor celulelor dintr-o foaie de lucru

Selectarea tuturor celulelor dintr-o foaie de lucru se face prin apasarea cu butonul stang al mouse-ului a butonului SELECT ALL (Selecteaza tot) din coltul din stanga sus al f oii de lucru sau prin combinatia de taste CTRL + A.

Selecteaza tot


Pagina 5/137

2.3.4.

Selectarea celulelor adiacente

Pentru a selecta o regiune de celule adiacente faceti click stanga pe coltul din stanga sus al primei celule si trageti de cursor, tinand apasat click stanga pana in coltul din dreapta jos al ultimei celule. Puteti folosi de-asemenea una dintre combinatiile de taste deja mentionate, in timp ce tineti apasat butonul SHIFT (vezi Capitolul 2.2.2.). Alte combinatii de taste utile sunt urmatoarele: CTRL + SHIFT + „+“ CTRL + SHIFT + „-„

2.3.5.

Selecteaza linii si coloane adiacente Deselecteaza linii si coloane adiacente

Selectarea celulelor neadiacente

Pentru a selecta o regiune de celule neadiacente selectati prima celula (sau regiune adiacenta de celule) si apoi tineti apasata tasta CTRL in timp ce selectati si celelalte celule sau regiuni de celule. Pentru a pune in practica subiectele primelor doua capitole se va deschide fisierul “01Introduction.xls” si se vor rezolva cerintele.


Pagina 6/137

2.4.

2.4.1.

Despre formule si referinte in cadrul celulelor

Crearea de formule

Formula incepe cu semnul egal („=“) urmat de o expresie ce contine operatori ce specifica tipul de calcul care se face pentru a combina mai multe date. Pe langa functiile speciale (de exemplu functii matematic-financiare si statistice), MS Excel furnizeaza utilizatorului operatori ce sunt aplicati in ordinea in care sunt apar in lista de mai jos. Daca se folosesc paranteze aceasta ordine este ignorata. Descriere

Operator

Negatia (ex. -5) Procent La putere Inmultirea si impartirea Adunarea si scaderea Pentru a lipi doua siruri de caractere de text (concatenare) – posibil si pentru referintele celulei Comparatii logice

% ^ (SI SPACE) * SI / + SI &

= > < >= <= <>

Egal Mai mare decat Mai mic decat Mai mare sau egal cu Mai mic sau egal cu Diferit de

(Operatorii sunt afisati in ordinea descrescatoare a prioritatii)


Pagina 7/137

2.4.1.1.

Referintele in cadrul celulelor

Referintele in cadrul celulelor se folosesc pentru a include date din diferite parti ale foii de lucru in formula. Exista trei tipuri diferite de referinte: Referinte relative ex. = A1 “Mergi 100 m inainte si apoi 20 m la dreapta!” Referinta depinde de celula in care va aflati Referinte absolute ex. =$A$1 “Mergi la casa de pe Strada Florilor Nr.5.” Referinta ramane intotdeauna aceeasi, indiferent de celula in care va aflati. Referinte mixte ex. =$A1 sau =A$1 Diferentele dintre aceste stiluri de referinta devin evidente doar atunci cand formula se copiaza. Daca pozitia celulelor ce contin formule sau functii este schimbata sau daca se adauga sau se sterg celule (exceptie cazul in care referinta era celula stearsa), referinta se ajusteaza in mod automat, indiferent de stilul de referinta aplicat, in cazul in care referinta face legatura intre celule din acelasi fisier.

2.4.1.2.

Referintele relative si schimbarea stilului de referinta

Exercitiu - 02-Introduction.xls 1.

Introduceti urmatoarele date in foaia de lucru (“Foaia1”)

Calculati rezultatul pentru anul 2005.


Pagina 8/137

1. Modul de lucru cu utilizarea tastaturii 1. 2. 3.

Faceti click pe celula B4. Introduceti formula scriind =B2-B3. Apasati ENTER pentru confirmare.

2. Modul de lucru fara utilizarea tastaturii 1. 2. 3. 4. 5. 6.

Faceti click pe celula B4. Introduceti semnul egal (=). Faceti click pe prima celula de referinta (cu mouse-ul sau prin utilizarea tastelor): B2 Introduceti operatorul: Selectati a doua celula de referinta: B3 Apasati ENTER pentru confirmare.

Prin aceasta modalitate Excel genereaza automat referinte relative. Modificarea stilului de referinte Cand introduceti o referinta (sau dupa ce ati selectat o referinta intr-o formula sau dupa ce ati pozitionat cursorul pe o referinta) si apasati tasta F4 inainte de a trece la urmatorul element in cadrul formulei, puteti schimba stilul de referinta. Prin apasarea repetata a tastei F4, stilul de referinta se modifica astfel: Stilul de referinta

Exemplu

Referinta relative

=A1

Referinta absolute

=$A$1

Referinta mixte (Referinta relativa la coloana cu referinta absoluta la r and)

=A$1

Referinta mixte (Referinta absoluta la coloana cu referinta relativa la r and)

=$A1

Si asa mai departe


Pagina 9/137

2.4.1.3.

Copierea formulelor (Exemplul Introductiv nr. 3)

Exemplele urmatoare arata diferentele dintre diferite stiluri de referinta in momentul in care copiati formulele. Mod de utilizare: Deschideti foaia de lucru ‘03-Introduction.xls’ Introduceti stilurile respective de referinta in celulele selectate, in conformitate cu graficul de mai jos:

Urmatorul pas este acela de a copia aceste formule in restul de celule.


Pagina 10/137

Copierea formulelor Exista doua modalitati diferite pentru a copia formulele: Copierea formulelor prin apasarea comenzii Copy (Copiere) 1.

Faceti click pe celula ce contine ceea ce doriti sa copiati (ex. celula A6)

2.

Faceti click Pe meniul Edit (Editare) sau pe meniul de context (folositi butonul drept al mouseului) si apasati comanda Copy (Copiere) sau Pe butonul COPY sau Folositi combinatia de taste: CTRL + C.







3.

Faceti click pe celula sau pe regiunea de celule unde doriti copierea formulei (regiunea de celule A6:C6)

4.

Apasati pe ENTER (pentru o singura copiere) sau PASTE sau Meniul Edit sau din meniul de context comanda Paste sau Meniul Edit sau din meniul de context comanda PasteSpecial si optiunea ALL









5.

Utilizati combinatia de taste CTRL + V pentru copierea formulei in mod repetat.

Comanda PasteSpecial Cand aplicati comanda PasteSpecial puteti defini in detaliu care parti din datele copiate (sau selectate) vor fi incorporate sau ce alte optiuni vor fi utilizate. Optiune

Rezultat

All (Toate) Formulas (Formule) Values (Valori) Formats (Formate) Comments (Comentarii) Transpose (Transpunere)

Pentru a lipi toate “elementele” (continut, formatare, comentarii, etc.). Pentru a lipi doar formule si valori. Pentru a lipi doar valori sau rezultate calculate cu ajutorul formulelor. Pentru a lipi doar formatarea celulei (formatul numar, aliniament, contur, etc.) Pentru a lipi doar comentariile Pentru schimbarea liniilor si coloanelor (oglindire in jurul diagonalei)


Pagina 11/137

Copierea formulelor prin editarea Drag-and-Drop Selectati celula care contine ceea ce doriti sa mutati sau sa copiati. Apasati tasta CTRL si indreptati-va cursorul catre una dintre marginile celulei selectate (cursorul se transforma intr-o sageata si apare semnul plus). Trageti celula in regiunea in care doriti copierea datelor (ex. F6:G6). Repetati pasii 1-3 pentru a copia formula celulelor E6:G6 in regiunea de celule E7:G8. Puteti copia celule si in urmatorul mod: trageti de coltul din dreapta jos al celulei active, cu butonul stang al mouse-ului apasat, catre o locatie de deasupra / de dedesubt / din dreapta / din stanga. Excel identifica serii de date – de exemplu daca cele doua celule selectate contin numerele 1 si 2 – se adapteaza la acest model. Prin apasarea tastei CTRL, aceste celule pot fi copiate chiar daca reprezinta serii de date. Dezavantajul acestei metode consta in faptul ca informatia completa din celula este copiata in timpul procesului. Prin aceasta metoda, formatul este si el copiat, ceea ce poate duce la anumite neplaceri atunci cand lucrati pe o foaie de lucru deja formatata. Din acest motiv, daca vreti sa copiati formulele fara formatare, procedati cu butonul drept al mouse-ului apasat pentru a activa meniul de context si selectati optiunea Fill without Formatting (Copiere fara formatare).


Pagina 12/137

2.4.1.4.

Aplicarea stilurilor de referinta (Exemplul introductiv nr. 4)

Sarcina exercitiului (“04-Introduction.xls”) este sa calculati suma ce este platita, in functie de diferite perioade de investitie si diferite sume ale capitalului initial la sfarsitul perioadei de investitie. Vi se cere sa introduceti o formula o singura data pentru a calcula o valoare a rezultatului si sa calculati celelalte sume platite prin copierea formulei. Atentie la diferitele stilurile de referinta atunci cand introduceti formula, pentru a evita erorile in momentul copierii.

Formula de calcul al dobanzilor:

Dobanda = Capital * (1+rata dobanzii)^nr. de ani


Pagina 13/137

2.4.2.

Definirea de nume

Prin apasarea comenzii Define (Definire) si Create (Creare) din meniul Insert (Inserare) si selectarea Name (Nume) (sau prin combinatia de taste CTRL + F3), puteti crea nume descriptive pentru reprezentarea de celule sau regiuni de celule (ex. Numele “Sales” (Vanzari) pentru a descrie celula A2). De fiecare data cand doriti sa creati celule de referinta sau formule ce contin date din acele celule sau regiuni de celule, puteti folosi numele lor (ex. “Sales”) pentru a le identifica, in loc sa specificati litera coloanei si numarul liniei (ex. A2). Lucrand cu celule sau regiuni de celule denumite se faciliteaza si posibilitatea urmaririi calculului. In loc sa denumiti individual o celula sau o regiune de celule, puteti folosi eticheta la capatul liniei sau coloanei pentru a face referire la campurile de date 1 si pentru a folosi continuturile pentru referinte. Din momentul in care celulele sau regiunile de celule au fost denumite, aceste nume vor fi afisate in caseta de nume atunci cand celulele sunt selectate. 2.4.2.1.

Utilizarea numelor definite (Exemplul introductiv nr. 5)

Sarcina descrisa in foile de lucru “Introduction4.xls” si “Introduction5.xls” este calcularea profitului din 2004 pana in 2006. In loc sa utilizati celule de referinta, definiti nume si utilizatile pentru a crea formule (litera coloanei si numarul liniei, ex A1). Exercitiu (05a-Introduction.xls)

1

Ordinea (eticheta liniei urmata de eticheta coloanei sau invers) este irelevanta. © Controller Institut Romania/ Contrast Management Consulting & Training

Pagina 14/137

Metoda 1: Utilizarea etichetelor de coloana sau linie pentru a face referire la campuri de date Introduceti formula =Sales 2004-Costs 2004 (Vanzari 2004-Costuri 2004) pentru anul 2004 in celula B4. Copiati formula in celulele C4:D4. Metoda 2: Utilizarea numelor existente comanda Create (Crearea de comanda) in meniul Insert (meniul Introducere), selectand Name (Nume) 1. 2. 3. 4. 5.

Faceti click pe celula sau pe regiunea de celule pe care doriti sa o denumiti si pe celula a carui continut (text) este folosit ca nume: regiune de celule A2:D4. Faceti click pe comanda Create (Creare) in meniul Insert (Introducere) si tineti cursorul pe Name (Nume) . Selectati casetele de selectare - pentru linii sau coloane – ce contin etichetele pe care doriti sa le utilizati pentru nume (activati casetele de selectare). Definiti formula pentru a calcula profitul pe 2004 printr-un click sau prin introducerea formulei =Sales-Costs. Copiati formula in celulele C4:D4.

Metoda 3: Crearea unei celule denumite comanda Define (Crearea de comanda) in meniul Insert (meniul Introducere), selectand Name (Nume) 1. 2. 3. 4. 5.

Faceti click pe celula pe care doriti sa o denumiti: B2 Faceti click pe comanda Define (Definire) in meniul Insert (Introducere) si tineti cursorul pe Name (Nume) . Confirmati numele propus de Excel sau definiti un nume prin urmatoarea introducere: Denumiti celula B2 cu numele “Sales04” prin introducerea textului in caseta de nume de langa bara de formula a fisierului. Repetati pasii 1-3 pentru a denumi celula B3 cu numele “Costs04”. Definiti formula pentru a calcula profitul pe 2004 printr-un click sau prin introducerea formulei =Sales04-Costs04.

Sfat

Observati ca Excel poate identifica nume ce sunt clar atribuite unor celule sau unor regiuni de celule in scopul de a le specifica. Din acest motiv, definirea de nume individuale nu este absolute necesara. (dupa cum se vede si in exemplul nostru).


Pagina 15/137

Metoda 4: Definirea de nume pentru o regiune de celule comanda Define (Crearea de comanda) in meniul Insert (meniul Introducere), selectand Name (Nume) Exercitiu (05b-Introduction.xls):

1. 2. 3. 4. 5. 6. 7.

Faceti click pe regiunea de celule C4:E4. Faceti click pe comanda Define din meniul Insert, tinand cursorul pe Name. Definiti numele “Sales.prod.gr.1” prin tiparirea numelui pentru aceasta regiune in caseta de nume a foii de lucru. Repetati pasii 1-3 pentru atribuirea numelui “Costs.prod.gr.1” regiunii de celule C5:E5. Definiti formula pentru a calcula vanzarile pentru 2004 printr-un click sau prin introducerea formulei =Sales.prod.gr.1-Costs.prod.gr.1. Copiati formula in celulele D6:E6. Repetati acesti pasi, dar folositi numele “Sales.prod.gr.2” si “Costs.prod.gr.2”.


Pagina 16/137

2.4.2.2.

Listarea numelor folosite

Pentru a avea o imagine de ansamblu asupra numelor folosite, puteti crea o lista ce contine toate numele celulelor sau regiunilor de celule prin intrarea in meniul Insert (Introducere), tinand cursorul pe Name (Nume) si facand click pe comanda Paste (Lipire). Mod de utilizare: 1. 2.

Definiti locatia listei de nume pentru a fi incluse in fisier. Selectati celula care ar trebui sa reprezinte celula din marginea din stanga sus a listei: celula A1 in tabela 2. Selectati comanda Paste List din meniul Insert menu – Name – Paste .

Utilizarea numelor este folositoare, mai ales atunci cand se folosesc calcule complexe. Veti recunoaste imediat scopul partilor individuale sau al valorilor din formula. Pentru optimizarea vizibilitatii si urmaririi formulelor, folositi referinte denumite atunci cand celulele fac referire la alte foi de lucru. Puteti evita o cautare inutila a continuturilor celulelor nedenumite. Combinatia de taste CTRL + G permite mutarea la un nume chiar daca se refera la celule din alte foi de lucru. Nota

Daca sunt copiate celule de referinta denumite, stilul de referinta este in mod automat unul absolut. Faceti click pe meniul Insert, tineti cursorul pe Name si gasiti urmatoarele comenzi Define (Definire) Paste (Lipire) Create (Creare) Apply (Aplicare)

Defineste un nume pentru o celula sau o regiune de celule. Introduce un nume definit anterior cand introduceti de exemplu o formula intr-o celula. Foloseste linii si coloane cu etichete la capat. Cere sa faceti click pe numele pe care doriti sa le utilizati in formule, in caseta de nume Apply, unde toate numele definite intr-un fisier, care fac referinta la o celula sau o regiune de celule, sunt listate.


Pagina 17/137

3. Analiza problemelor si generarea de solutii cu ajutorul MS-Excel 3.1.

Exercitiu - Exemplul „Analiza bilantului contabil“

Efectuati o „Analiza a bilantului contabil” pentru Excel Profi PLC. Datele sursa disponibile includ bilanturile anuale ale ultimilor patru ani. Puteti gasi aceste date in fisierul excel denumit “06-BalanceSheet.xls”.


Pagina 18/137

3.2.

Pregatirea datelor

3.2.1. Fixarea coloanei si/sau randului la o anumita latime si/sau inaltime Inaltimea randului sau latimea coloanei sunt stabilite la o valoare standard 2. Pentru a modifica latimea coloanei puteti trage cu mouse-ul de marginea acesteia sau puteti f ace click in meniul Format (Format), tinand cursorul pe Column (Coloana) si facand click pe comanda Width (Latime). Mai mult, puteti ajusta latimea coloanei in functie de cea mai lata celula (“AutoFit Selection”). Inaltimea randului este ajustata automat in functie de cel mai inalt caracter de pe un rand sau pentru a se potrivi cu un text de format “wrapped text” 3. Puteti sa modificati inaltimea randului si prin tragerea capatului randului sau puteti face click in meniul Format (Format), tinand cursorul Row (Rand) si facand click pe comanda Hight (Inaltime).

3.2.1.1.

Selectia AutoFit (potrivire automata)

Exercitiu: Schimbati latimea coloanei A pentru a potrivi continutul ei cu ajutorul selectiei AutoFit Optiunea 1 prin utilizarea unui dublu-click Dublu-click pe limita de separare din partea dreapta a capatului de coloana al coloanei A. 

Optiunea 2 prin apasarea comenzii AutoFit Selectati coloana A sau o celula de pe aceasta coloana, apasati apoi meniul Format, tinand cursorul pe Column si apasand comanda AutoFit Selection. 

2

Puteti modifica setarile de baza ale valorilor standard prin accesarea meniului Format, tinand cursorul pe Coloana sau pe Rand si apasand comanda Standard Width (Latime standard ). 3 Exista posibilitatea sa impartiti textul in linii multiple (faceti click pe meniul Format, tinand cursorul pe Cell (Celula) si facand click pe optiunile Text Alignment (Alinierea textului) si Wrap Text din caseta de optiuni Text Control (Controlul textului)). © Controller Institut Romania/ Contrast Management Consulting & Training

Pagina 19/137

3.2.1.2.

Personalizarea latimii coloanei

Exercitiu: Schimbati coloanele F:I pentru a avea latimea de 12 4 Optiunea 1 prin utilizarea mouse-ului Selectati coloanele F:I. Modificati latimea coloanelor selectate prin tragerea limitei de separare din partea dreapta a capatului de coloana. 

Optiunea 2 prin apasarea comenzii Width (Latime) Faceti click pe coloanele F:I. Modificati latimea coloanelor selectate prin apasarea comenzii Width (Latime) dupa ce ati dat click pe meniul Format, tinand cursorul pe Column (Coloana) 

3.2.1.3.

Personalizarea inaltimii randului

Exercitiu: Stabiliti inaltimea randului (randul 15, randul 36, randul 56 si randul 67) la valoarea de 205. Optiunea 1 prin utilizarea mouse-ului Selectati randurile 15, 36, 56 si 67 sau selectati orice celula in fiecare dintre aceste randuri prin selectarea acestor randuri sau celule si tinand apasat butonul CTRL in acelasi timp. Schimbati inaltimea randurilor prin tragerea de catre limita inferioara de separare a capatului de rand. (ex. randul 15). 

Optiunea 2 prin apsarea comenzii Height (Inaltime) Selectati randurile 15, 36, 56 si 67 sau selectati orice celula in fiecare dintre aceste randuri prin selectarea acestor randuri sau celule si tinand apasat butonul CTRL in acelasi timp. Faceti click pe meniul Format, tinand cursorul pe Row (Rand), apasati comanda Height (Inaltime) si completati valoarea dorita. 

Alta metoda: Puteti sa selectati si un singur rand caruia sa ii modificati inaltimea. Prin deschiderea meniului Edit (Editare), tinand cursorul pe comanda Repeat Row Width (Repetati latimea randului) si tinand apasata combinatia de taste CTRL + Y, acest process se aplica si celorlalte randuri (Selectati randul 15 si apasati combinatia de taste CTRL + Y; apoi selectati randul 36 si apasati combinatia de taste CTRL + Y, si asa mai departe).

4

Latimea coloanei este definita de numarul maxim de caractere ce pot fi afisate intr-o celula formatata in fontul standard. 5 Inaltimea randului este masurata in puncte (1 Punct = 0,035 c m). © Controller Institut Romania/ Contrast Management Consulting & Training

Pagina 20/137

Sfat

Prin utilizarea combinatiei de taste CTRL + Z (echivalentul pictogramei UNDO), puteti anula ultimele comenzi.

Nota

Daca utilizati anumite comenzi in mod repetat, exista urmatoarele posibilitati de actiune: 1) Prin utilizarea meniului principal 2) Prin utilizarea meniului de context (click pe butonul drept al mouse-ului) 3) Prin utilizarea pictogramelor, simbolurilor (puteti organiza in consecinta bara de intrumente) 4) Prin aplicarea combinatiilor de taste (vedeti capitolul 12: Combinatii de taste folositoare)


Pagina 21/137

3.2.2. celule

Stergerea celulelor, coloanelor, randurilor, continutului unei

Daca doriti stergerea elementelor dintr-o lista de date, trebuie sa va hotarati intre urmatoarele:  

Stergeti continutul celulelor selectate sau Stergeti celulele, coloanele, randurile.

Golirea celulelor presupune inlaturarea continutului celulelor (date si formule), formatari, comentarii atasate si/sau hyperlink-uri 6, dar nu celulele in sine. Stergerea celulelor, coloanelor, liniilor presupune indepartarea completa a acestor elemente din lista de date. Spatiul ramas este inlocuit de celulele din vecinatate care vor f i ridicate sau mutate la stanga in mod automat. Nota: 



Daca goliti o celula pentru a inlatura continutul, valoarea celulei golite este 0 (observati ca formulele ce contin referinte la celula golita primesc valoarea 0). Formulele ce contin referinte la celulele sterse afiseaza eroarea “#Ref!”. Formulele ce contin referinte la celulele ridicate automat isi ajusteaza referintele.

6

Un hyperlink este o legatura catre alte documente, catre retea, catre un browser web sau catre alte locatii din fisier. © Controller Institut Romania/ Contrast Management Consulting & Training

Pagina 22/137

3.2.2.1.

Golirea celulelor pentru a inlatura continutul acestora

Exercitiu: Stergeti datele din celula B2 Optiunea 1 Metoda de lucru cu ajutorul comenzii Clear (Golire) Faceti click pe celula: B2. Apoi deschideti meniul Edit (Editare) si tineti cursorul pe comanda Clear (Golire). Puteti sterge intregul continut al celulei, formatarile, datele (=continut) sau doar comentariile.

Optiunea 2 Metoda de lucru cu ajutorul butonului Delete (Del) (Stergere) Selectati celula (celulele) corespunzatoare. Pentru a sterge continutul celulei, puteti apasa butonul DEL. Prin aceasta operatie, numai datele (continutul) sunt sterse. Comentariile, formatarile sau hyperlink-urile raman atasate celulelor.


Pagina 23/137

3.2.2.2.

Stergerea celulelor

Comanda Delete (Stergere) din meniul Edit (Editare) permite inlaturarea celulelor, regiunilor de celule sau coloanei/randului prin utilizarea optiunii potrivite din caseta de optiuni a comenzii Delete. 3.2.2.3.

Stergerea coloanelor

Exercitiu: Stergeti coloanele B:D Optiunea 1 Selectati capetele de coloana de la B la D. In meniul Edit (Editare), apasati comanda Delete (Stergere). 

Optiunea 2 Selectati celulele dintre coloanele B si D (ex. B1:D1) In meniul Edit (Editare) selectati comanda Delete (Stergere) si optiunea ENTIRE COLUMN (Intreaga Coloana). 

Optiunea 3 Selectati celulele dintre coloanele B si D (ex. B1:D1) Apasati combinatia de taste CTRL + „—„ 

Sfat

Puteti selecta intreaga coloana prin folosirea combinatiei de taste CTRL + SPACE.


Pagina 24/137

3.2.2.4.

Stergerea randurilor

Exercitiu: Stergeti linia 37. Optiunea 1 Apasati capatul de rand al randului 37. In meniul Edit (Editare), selectati comanda Delete (Stergere) . 

Optiunea 2 Selectati o celula de pe randul 37 (ex. A37) In meniul Edit (Editare), selectati comanda Delete (Stergere) si optiunea ENTIRE ROW (Intreaga Linie). 

Optiunea 3 Apasati capatul de rand al randului 37. Apasati combinatia de taste CTRL + „—„ 

Puteti selecta randurile intregi prin combinatia de taste SHIFT + SPACE. Sfat

Asigurati-va ca pe randurile si/sau coloanele pe care doriti sa le stergeti nu mai sunt date. Acest lucru este foarte important atunci cand se lucreaza pe foi de lucru mari si complexe. Cea mai facila modalitate de a cauta pe coloane sau pe linii este utilizare combinatiei de taste CTRL + ↑ sau ↓ si CTRL + ← sau →. Cand aplicati aceasta comanda, celula activa trece automat la urmatoarea celula cu continut – sau la sfarsitul sau inceputul foii de lucru.


Pagina 25/137

3.2.3.

Inserarea de celule, coloane si randuri

Pentru a face loc pentru inserarea unor date in viitor si pentru a face foile de lucru mai aerisite, vi se cere sa pregatiti fisierul pentru noi pasi in procesarea datelor, prin inserarea de celule, coloane si randuri. Noile celule, coloane sau randuri sunt introduse intotdeauna in fata (=deasupra sau la stanga) celulei, coloanei sau randului selectate si active. 3.2.3.1.

Inserarea de celule

Exercitiu: Inserarea unei subpozitii in zona mijloacelor fixe. 1. 2. 3.

Faceti click pe celulele A6:A8. Apasati comanda Cells (Celule) in meniul Insert (Introducere). Faceti click pe optiunea SHIFT CELLS RIGHT (DEPLASARE CELULE LA DREAPTA).

3.2.3.2.

Inserarea de coloane

Exercitiu: Inserarea unei coloane in fata coloanei A. Optiunea 1 prin apasarea comenzii Columns (Coloane) din meniul Insert (Introducere) : Selectati o celula de pe coloana A sau selectati capatul de coloana al coloanei A, Faceti click pe meniul Insert (Introducere) si apoi pe comanda Columns (Coloane). 

Optiunea 2 prin apasarea comenzii Cells (Celule) din meniul Insert (Introducere) : Selectati capatul de coloana al coloanei A, faceti click pe comanda Cells (Celule) din meniul Insert (Introducere). 

3.2.3.3.

Inserarea de randuri

Exercitiu: Inserarea a doua randuri in fata randului 1. Optiunea 1 prin apasarea comenzii Rows (Randuri) din meniul Insert (Introducere) : Selectati de ex. celulele A1:B1 sau selectati capetele de randuri ale randurilor 1 si 2, faceti click pe comanda Rows (Randuri) din meniul Insert (Introducere). 

Optiunea 2 prin apasarea comenzii Cells (Celule) din meniul Insert (Introducere): Selectati capetele de randuri ale randurilor 1 si 2, faceti click pe comanda Cells (Celule) din meniul Insert (Introducere). 


Pagina 26/137

3.2.4.

Formatarea celulelor

Introduceti titlul „Balance Sheet Analysis“ (Analiza Bilantului Contabil) in celula B2 si selectati-l. Personalizati formatul numar prin folosirea separatorului de 1000 pentru a imbunatati claritatea datelor afisate. 3.2.4.1.

Modificarea fontului

Optiunea 1 prin folosirea Formatting Toolbar (Bara de instrumente de formatare) 1. 2.

Selectati celula B2. Faceti click pe Formatting Toolbar (Bara de instrumente de formatare), tinand cursorul pe Font si faceti click pe fontul pe care il doriti (ex. Univers)


Pagina 27/137

Optiunea 2 prin selectarea FORMAT CELLS (FORMATAREA CELULELOR) din meniul Format - Cells 1. 2.

Faceti click pe celula ceruta. Faceti click pe meniul Format , tinand cursorul pe Cells (Celule) si faceti click pe fontul dorit in FORMAT CELLS.


Pagina 28/137

3.2.4.2.

Modificarea dimensiunii fontului

Optiunea 1a prin utilizarea Formatting Toolbar (Bara de instrumente de formatare) 1. 2.

Faceti click pe celula ceruta. Faceti click pe Formatting Toolbar (Bara de instrumente de formatare), faceti click pe Font Size (Dimensiunea fontului) si selectati dimensiunea dorita a fontului (ex. 20).

Optiunea 1b prin utilizarea Formatting Toolbar (Bara de instrumente de formatare) 1. Selectati celula ceruta. 2. Faceti click pe Formatting Toolbar si tipariti valoarea dorita (ex. 16) in caseta dimensiunii fontului.


Pagina 29/137

Optiunea 2 prin utilizarea FORMAT CELLS (FORMATUL CELULELOR) din meniul Format Cells 1. Selectati celula dorita. 2. Faceti click pe meniul Format, tinand cursorul pe Cells, faceti click pe FORMAT CELLS si pe Font sau scrieri numele fontului dorit.


Pagina 30/137

3.2.4.3.

Modificarea stilului de font

Exercitiu: Schimbati stilul de font al titlului „Balance Sheet Analysis“ in Bold 





Optiunea 1 prin utilizarea Formatting Toolbar (Bara de instrumente de formatare) Selectati celula ceruta. In Formatting Toolbar, faceti click pe butonul BOLD. Optiunea 2 prin utilizarea FORMAT CELLS din meniul Format – Cells Selectati celula ceruta. In FORMAT CELLS din meniul Format – Cells, faceti click pe FontStyle Bold (Stilul de font Bold). Optiunea 3 prin aplicarea combinatiei de taste CTRL + SHIFT + F Selectati celula ceruta. Apasati combinatia de taste CTRL + SHIFT + F (Prin utilizarea acestei combinatii de taste timp de o secunda, comanda este anulata.)

Sfat

Exista combinatii de taste si pentru formatele I (italic) si U (underline (subliniat)).


Pagina 31/137

3.2.4.4.

Modificarea culorii fontului

Exercitiu: Schimbati culoarea fontului titlului „Balance Sheet Analysis“ in albastru. Optiunea 1 prin utilizarea Formatting Toolbar (Bara de instrumente de formatare) Selectati celula ceruta. In Formatting Toolbar, faceti click pe Font Color (Culoarea fontului) si selectati culoarea blue (albastru).


Pagina 32/137

Optiunea

2

prin

utilizarea

FORMAT

CELLS

din

meniul

Format

–

Cells

Selectati celula ceruta. In FORMAT CELLS din meniul Format - Cells, faceti click pe Color (Culoare) si selectati blue (albastru).

Nota – Aplicare de formate diferite intr-o singura celula

Exista posibilitatea sa aplicati formate diferite de font datelor dintr-o singura celula. Aceasta se face prin selectarea din bara de formula a regiunii de date pe care doriti sa le formatati si prin determinarea formatului dorit al fontului, dupa modalitatile prezentate anterior.


Pagina 33/137

3.2.4.5.

Personalizarea formatelor numar

Exercitiu: Formatati numerele folosind separatorul 1000 pentru a imbunatati lizibilitatea foii de lucru Optiunea 1 prin utilizarea FORMAT CELLS, categoria Number (Numar) din meniul Format – Cells 1. 2. 3.

Selectati regiunea de celule ceruta (D11:G76). In meniul Format, tinand cursorul pe comanda Cells, faceti click pe Format Cells si selectati categoria Number (Numar). Faceti click pe caseta de optiuni 1000 SEPARATOR si Zecimale: 0.

Sfat

Pentru a afisa numere cum ar fi o mie sau un million, puteti face click pe caseta de dialog Format Cells din meniul Format - Cells, faceti click pe selectia „Custom“ din Category box (caseta de categorie) si tipariti in Sample box (caseta mostra)(“Type” (Tip)) propriul format, folosind un punct (pentru 1.000) sau doua puncte (pentru 1.000.000). Daca stergeti complet intrarea din Sample box (caseta mostra) si tipariti „;;;“ (3 punct si virgule in serie), celula va ramane goala – continului este “ascuns”.


Pagina 34/137

Optiunea 2 prin utilizarea butonului 1000 SEPARATOR 1. 2.

Selectati regiunea de celule ceruta (D11:G76). In Formatting Toolbar (Bara de instrumente de formatare), faceti click pe butonul 1000 separator si apoi faceti click pe butonul DECREASE DECIMAL (REDUCERE ZECIMALE) de doua ori. 7

Prezentare generala

Butoanele urmatoare din formatting toolbar (bara de instrumente de formatare) sunt disponibile pentru personalizarea formatelor numar: Buton

Exemplu Inainte sa apasati butonul

CURRENCY (MONEDA)

PERCENT (PROCENT)

1000 -1000

STYLE 1

1000 SEPARATOR

Rezultat

1.000.00 EUR -1.000.00 EUR (in font rosu) 100%

0,2 0,122

20% 12,2%

1000000

1.000.000.00

INCREASE DECIMAL 1,2 (ADAUGARE ZECIMALE)

1,20

DECREASE DECIMAL 1,23 (REDUCERE ZECIMALE) 1,26

1,2

7

1,3

Aceasta procedura face ca numerele din celule sa nu fie afisate in totalitate ca aliniate la dreapta. © Controller Institut Romania/ Contrast Management Consulting & Training

Pagina 35/137

Adaptarea formatului 1,000 (Pictograma) Mod de utilizare: 1. 2.

In meniul Format, faceti click pe comanda Style (Stil). Selectati Comma (Virgula) in caseta style name (numele stilului).

3.

Selectati comanda Modify (Modificare) (sau Define (Defineste) din variantele mai vechi de Excel). Faceti click pe formatul dorit al numarului.

4.


Pagina 36/137

5.

Confirmati modificarile prin OK.

Prin aceeasi procedura puteti selecta CURRENCY (MONEDA) SI PERCENT (PROCENT) in caseta de stil al numelui pentru a le adapta nevoilor dumneavoastra. Sfat

Puteti copia formatele in celule sau regiuni de celule folosind butonul FORMAT PAINTER (FORMAT DESEN - pensula galbena) din bara de instrumente de formatare. Daca apasati pictograma o data puteti copia formatul in in celula selectata o singura data. Daca faceti dublu-click pe pictograma, formatul este copiat in mod repetat in celula selectata.


Pagina 37/137

3.2.5.

Aplicarea de margini, linii, culori, modele

Puteti selecta afisarea marginilor si liniilor din foaia de lucru dintr-o regiune vasta de stiluri variate. Exista si multe culori si modele disponibile pentru a There are also many colors and patterns available pentru a proiecta fundalul celulelor. Aceste elemente de stil imbunatatesc foarte mult folosirea si claritatea foii de lucru. Prezentare generala – Utilizarea butoanelor din Formatting Toolbar (Bara de instrumente de formatare):

Butoanele din Formatting Toolbar (Bara de instrumente de formatare) permit atribuirea de margini, linii, culori sau modele regiunilor de celule selectate. Prin utilizarea butonului Borders (Margini), puteti atribui stilul current de margini si linii regiunii de celule selectate. Daca apasati sageata de langa Borders (Margini), paleta de modele care apare va ofera o selectie de stiluri variate de margini si linii. Prin folosirea butonului Fill Color (Umplere de culoare), puteti atribui culoarea sau modelul de fundal current regiunii de celule selectate. Daca apasati sageata de langa Fill Color (Umple de culoare), puteti alege dintr-o paleta variata de culori si modele de fundal. Sfat

In meniul Tools (Unelte), apasati pe Options (Optiuni), apoi faceti click pe Color (Culoare) si Modify (Modificare) daca doriti sa create propria culoare. Culoarea personalizata este aplicata doar foii de lucru curente.

Nota: Faceti click pe meniul Format, apoi pe Cells (Celule) si apoi pe Border (Margine).

Puteti atribui margini, linii si modele regiunilor de celule selectate prin deschiderea meniului Format si apasarea pe Cells (Celule). In submeniul Border puteti selecta un model presetat pe care il puteti folosi pentru a-l personaliza apoi (ex. pentru a aplica stiluri variate si linii colorate tuturor marginilor sau doar pentru a modifica unele linii).


Pagina 38/137

3.2.5.1.

Aplicarea marginilor si liniilor

Exercitiu: Evidentiati titlul „Balance Sheet Analysis“ Optiunea 1 prin utilizarea butonului BORDERS (MARGINI) 1. 2.

Selectati regiunea de celule ceruta (B2:C2) Apasati sageata de langa butonul Borders (Margini) si faceti click pe stilul liniei de margine preferat din paleta Borderline (Linia de margine).

Sfat

Alta optiune pentru a evidentia o celula selectata sau o regiune de celule este crearea unei margini prin folosirea combinatiei de taste CTRL + SHIFT + „—„.


Pagina 39/137

Optiunea 2 prin utilizarea submeniului BORDER (Margine) din meniul Format - Cells 1. 2.

Selectati regiunea de celule ceruta (B2:C2) Faceti click pe submeniul BORDER (Margine) din caseta de dialog Format Cells din meniul Format – Cells. Stilul de margine selectat poate fi aplicat prin selectarea butonului PRESETS (PRESETARI).


Pagina 40/137

Exercitiu: Se cere sa atribuiti o margine (linie de grila) subpozitiilor mijloacelor fixe. Mod de utilizare: 1. 2.

Selectati regiunea de celule ceruta (C11:G13). Faceti click pe sageata de langa butonul BORDERS (MARGINI) si selectati din paleta stilul de margine preferat.


Pagina 41/137

3.2.5.2.

Atribuirea culorilor si modelelor

Exercitiu: Modificati fundalul celulei in care se afla titlul „Balance Sheet Analysis“. Optiunea 1 prin utilizarea butonului FILL COLOR (UMPLE DE CULOARE): 1. 2.

Selectati regiunea de celule ceruta (B2:C2) Faceti click pe sageata de langa butonul FILL COLOR si selectati modelul dorit (culoarea fundalului – gri deschis -25%).


Pagina 42/137

Optiunea 2 prin utilizarea submeniului PATTERNS (MODELE) din caseta de dialog Format Cells (Formatul celulelor) din meniul Format - Cells: 1. 2.

Selectati regiunea de celule ceruta (B2:C2) In meniul Format, apasati pe Cells si apoi pe Patterns din caseta de dialog Format Cells (Formatul celulelor). Selectati culoarea dorita (gri deschis) si fixati modelul dorit printr-un click pe setarea corespunzatoare („None“).


Pagina 43/137

3.2.6.

Aplicarea liniilor de grila

Prin deschiderea meniului Tools (Unelte), apasand pe Options (Optiuni) si pe View (Vizualizare), puteti modifica optiunile ferestrei pentru a omite liniile de grila. Caseta liniilor de grila trebuie sa fie goala (nu faceti click in caseta de optiuni!). Aceasta optiune se aplica numai pentru vizualizarea de tip ecran. Sfat

Daca doriti sa printati foaia de lucru cu linii de grila, deschideti meniul File (Fisier), faceti click pe Page Setup (Setarea paginii) si apoi pe submeniul Sheet (Foaie) si selectati caseta Gridline (Linie de grila) de sub Print (Printare). Aici puteti selecta caseta capetelor de linii / coloane si a comentariilor daca doriti ca si acestea sa fie printate.


Pagina 44/137

3.2.7.

Definirea strategiei de design pentru afisarea rezultatelor

Un aspect important atunci cand lucrati cu o cantitate mare de date este sa va asigurati ca datele introduse in Excel sunt bine structurate si ca pasii de procesare a datelor sunt transparenti si usor de inteles. Lizibilitatea datelor si usoara orientare asupra lor sunt aspectele cheie. In consecinta, pentru a lucra usor cu multe date, puteti segmenta foaia de lucru intr-un sistem de vizualizare generala ce se bazeaza pe orientarea pe obiecte. Urmatoarea strategie poate fi folosita in cazul multor sarcini: Sectiunea de identificare Autor, versiune, date curente etc. Sectiunea de introducere Face referire la segmentele foii de lucru in care toate datele sunt introduse. Sectiunea de parametri Aceste date subliniaza intregul material al unei cerinte sau proiect si difera de variabilele numite “elemente de control”.

Pe baza unui asa numit camp de decizii sau a unui sistem de luare a deciziilor, datele sunt atribuite segmentului de intrare sau segmentului parametrilor. Cand mai multi utilizatori au acces la o aplicatie Excel, este important sa se faca distinctia intre cele doua sectiuni pentru a permite anumitor grupuri de utilizatori sa modifice sau sa introduca date doar in sectiunea de introducere si sa ii impiedice sa acceseze alte sectiuni prin blocarea acestora (protectie). Sectiunea valorilor subtotale Acest segment este dedicat pentru calculul valorilor subtotale. Acest design garanteaza faptul ca formulele create in sectiunea rezultate totale au expresii mai scurte. Totodata se reduce si riscul de a intampina erori.

Alt avantaj este acela ca valorile subtotale pot fi folosite in mod repetat pentru a crea formule multiple utilizate in calculul valorilor totale. Sectiunea rezultatelor totale Acest segment afiseaza valorile totale intr-o maniera clar structurata.


Pagina 45/137

3.2.8.

Folosirea valorilor de control al intrarilor de date

Puteti verifica o multitudine de erori (ex. erori de tiparire a datelor numerice sau a formulelor) destul de rapid daca introduceti in foaia de lucru asa numitele valori de control. Exemplul cu analiza bilantului contabil arata foarte clar ca anumite erori ce rezulta din intrari incorecte de date pot fi gasite prin verificarea ecuatiei balantei activ = pasiv. Diferenta dintre activ si pasiv este calculata pe linia 39. Daca rezultatul este o valoare diferita de zero, trebuie sa cautati eroarea. Mod de utilizare: 1. 2. 3.

Introduceti textul „control value“ (control al valorii) in celula B39. Scrieti formula =D38-D19 in celula D39. Copiati formula in celulele E39:G39.

Sfat

Puteti ascunde valorile zero pentru imbunatatirea lizibilitatii foii de lucru. Ascunderea o puteti realiza din meniul Tools (Unelte), facand click pe Options (Optiuni) si pe VIEW (Vizualizare) si lasand necompletata caseta de valori 0.


Pagina 46/137

3.2.9.

Afisarea si ascunderea liniilor si coloanelor

Daca doriti selectarea unei portiuni din foaia de lucru si nu a intregii foi de lucru pentru a o printa sau pentru a fi afisata pe ecran, puteti ascunde anumite linii si coloane. Avantajul ascunderii unui linii sau coloane este acele ca puteti selecta segmente in timp ce foaia de lucru ramane completa. Nicio informatie nu este stearsa. Exercitiu 1: Ascundeti linia de control al valorii – ecuatia de bilant

Mod de utilizare: 1. 2.

Selectati randul corespunzator. In meniul Format, apasati Row (Rand) si faceti click pe comanda Hide (Ascunde)

Exercitiu 2: Afiseaza randul de control al valorii – ecuatia de bilant


Selectati regiunea in care doriti sa ascundeti liniile (linia 38 si linia 39). In meniul Format, apasati Row si faceti click pe comanda Unhide (Afiseaza ce este ascuns)

Sfat

Daca va hotarati sa ascundeti randuri sau coloane puteti crea o grupare pentru aceste date. In capitolul urmator veti gasi instructiunile. Datorita simbolurilor caracteristice de grupare ce apar pe ecran, veti putea observa imediat care celule sunt ascunse. Cand ascundeti date grupate, aparenta foii de lucru ramane neschimbata cu exceptia a catorva numere (ale capetelor de linie) sau caractere (pentru capetele de coloana) care nu vor fi afisate. Datele ascunse prezinta dezavantajul ca pot fi trecute cu vederea mai ales cand sunteti foarte ocupati. De exemplu puteti dobandi informatii false din cauza celulelor ascunse care sunt incluse de Excel in unele formule.


Pagina 47/137

3.2.10. Gruparea continutului foii de lucru (07-Analysis.xls) Pentru imbunatatirea lizibilitatii si folosirii foii de lucru, puteti grupa linii sau coloane specifice ce contin datele selectate si puteti crea o grupare pe nivele diferite. Daca deschideti meniul Data (date) si faceti click pe Group and Detail (Grupare si detaliu) si apoi pe Hide Detail (Ascundeti detaliu) sau Show Detail (Afisati detaliu) (vedeti graficul X), nivelele variate ale gruparii pot fi afisate sau ascunse. In exercitiul de mai jos vi se cere sa grupati manual foaia de lucru: 1. Nivel grupare

2. Nivel grupare

3. Nivel grupare

4. Nivel grupare

Date sursa Bilant Imobilizari: Imobilizari necorporale Imobilizari corporale Imobilizari financiare Active curente: Consumabile Creante Casa Capital social: Si asa mai departe Rezerve scutite de taxe Si asa mai departe Si asa mai departe Contul profit & pierderi Calcul subtotal Si asa mai departe Rezultat Si asa mai departe


Pagina 48/137

3.2.10.1.

Gruparea datelor

1.

Selectati regiunea de date a primului grup din primul nivel al gruparii (date: capatul de rand – liniile 7 – 79) 2. In meniul Data (Date) – Group and Outline (Grupare), faceti click pe comanda Group (Grupare). 3. Selectati regiunea de date a primului rand de pe al doilea nivel al gruparii (date: capatul de rand – liniile 9 – 39) 4. In meniul Data (Date) – Group and Outline (Grupare), faceti click pe comanda Group (Grupare). 5. Selectati regiunile de date din al doilea grup de pe al doilea nivel al gruparii (Contul de profit si pierderi: liniile 42-58) 6. In meniul Data (Date) – Group and Outline (Grupare), faceti click pe comanda Group (Grupare). 7. Selectati regiunea de date din al treilea grup de pe al doilea nivel al gruparii (date aditionale: liniile 61-78) 8. In meniul Data (Date) – Group and Outline (Grupare), faceti click pe comanda Group (Grupare). 9. Selectati regiunea de date din primul grup de pe al treilea nivel al gruparii (Imobilizari: liniile 10-12) 10. In meniul Data (Date) – Group and Outline (Grupare), faceti click pe comanda Group (Grupare). 11. Repetati pasii 5 si 6 pentru a selecta si grupa regiunea de date pentru celelalte grupuri de pe al treilea nivel al gruparii.


Pagina 49/137

3.2.10.2.

Afisarea ierarhizata a grupurilor de randuri si coloane

Puteti gasi exercitiul in fisierul „07-Analysis-Solution.xls“. Urmatoarele optiuni de vizualizare se bazeaza pe grupele de date ce au fost create la pasii anteriori:

Afisarea datelor de pe un nivel de grupare 





Pentru afisarea primului nivel de grupare: Apasati butonul cu numarul 1. Pentru afisarea celui de-al doilea nivel de grupare: Apasati butonul cu numarul 2. Pentru afisarea tuturor detaliilor de pe linii si coloane (toate nivelele de grupare): Apasati butonul cu numarul 4.


Pagina 50/137

Afisarea continutului nivelurilor de grupare ulterioare 



Pentru a afisa datele de detaliu (al treilea nivel de grupare) din grupul „Data“ (Date): Faceti click pe butonul „+“ in randul “Data” (Date). Pentru a afisa datele de detaliu (al patrulea nivel de grupare) din grupul „Current Assets” (Active curente): Faceti click pe butonul „+“ in randul “Current Assets” (Active curente).

Ascunderea datelor de detaliu 



Pentru a ascunde datele de detaliu (al treilea nivel de grup) din grupul „Data“ (Date): Faceti click pe butonul „-„ in randul “Data”(Date). Pentru a ascunde datele de detaliu (al patrulea nivel de grupare) din grupul „Current Assets” (Active curente): Faceti click pe butonul „-„ in randul “Current Assets” (Active curente).


Pagina 51/137

Alte note utile in foile de lucru grupate 











Coloanele selectate sunt grupate in aceeasi maniera in care se procedeaza si cu randurile. Daca selectati o singura celula sau o regiune de celule in loc sa selectati un rand sau o coloana intreaga, inainte sa apasati comanda Group (Grupare), veti fi intrebati daca doriti sa grupati randurile sau coloanele. Nivelurile de grupare sunt ascunse sau afisate prin utilizarea butoanelor corespunzatoare plus, minus, numerele 1, 2, 3, 4. In plus, puteti face click pe meniul Data (Date), apoi pe Group and Outline (Grupare) si apoi pe comenzile Hide Detail (Ascunde detaliu) sau Show Detail (Arata detaliu) . Prin apasarea comenzii Ungroup (Degrupare), puteti inlatura gruparea de pe regiunea de date selectata. Pentru a anula gruparea intregii foi de lucru trebuie sa apasati comanda Clear Outline (Stergere grupare) din meniul Data (Date) – Group and Outline (Grupare). Datele din foaia de lucru pot fi grupate in mod automat prin apasarea comenzii Automatic Styles (Stiluri automate) din meniul Data (Date) – Group and Outline (Grupare). Inainte sa aplicati comanda trebuie sa selectati o celula aleatoare din foaia de lucru. Pentru a crea o foaie de lucru grupata automat, Microsoft Excel se adapteaza formulelor si celulelor de referinta existente. Pentru a inlatura simbolurile de grupare de pe ecran, faceti click pe meniul Tools (Unelte), apoi pe optiunea View (Vizualizare) si apoi pe optiunea Outline Symbols show/hide (Afisarea/Ascunderea simbolurilor de grupare). Nivelurile de grupare pot fi indicate prin apasarea comenzii Hide Detail (Ascunde detaliu) sau Show Detail (Arata detaliu) din meniul Data (Date) – Group and Outline (Grupare). Aceeasi operatie este posibila si prin apasarea comenzii Formatting Toolbar PIVOTTABLE (BARA DE INSTRUMENTE DE FORMATARE TABEL-PIVOT) si a butoanelor HIDE DETAIL (ASCUNDE DETALIU) sau SHOW DETAIL (ARATA DETALIU).


Pagina 52/137

3.2.11. Blocarea unei ferestre O metoda pentru afisarea capetelor de coloana sau rand pe ecran in timp ce editati sau vizualizati documentul si trebuie sa navigati mult este sa fixati sectiunea dorita din foaia de lucru. Mod de utilizare: 1. 2.

Selectati celula de mai jos si/sau din dreapta liniei sau coloanei pe care doriti sa o fixati. In meniul Window (Fereastra), faceti click pe command Freeze Panes (Fixare panouri).

3.2.11.1.

Fixarea unei coloane

Exercitiu: Fixati „Labels at the Top of Rows“ (Etichete la inceputul liniei). Mod de utilizare: 1. 2.

Faceti click pe coloana D sau pe celula D1. In meniul Window (Fereastra), faceti click pe comanda Freeze Panes (Fixare panouri).

Cand navigati mult in jos prin document, etichetele de la inceputul liniei (titluri) raman in permanenta pe ecran. Prin apasarea comenzii Unfreeze Panes (Deblocare panouri) din meniul Window (Fereastra), puteti anula operatiunea.


Pagina 53/137

3.2.11.2.

Fixarea coloanelor si randurilor

Exercitiu: Fixati capatul de rand si capatul de coloana – Statement Date. 1. 2.

Selectati celula D5. Faceti click pe comanda Freeze Panes (Fixare panouri) din meniul Window (Fereastra).

Capatul de rand sau coloana intitulat „Statement Date“ ramane vizibil intotdeauna pe ecran, indiferent de cat de mult ati naviga in jos sau la dreapta in document. Prin apasarea comenzii Unfreeze Panes (Fixare panouri) din meniul Window (Fereastra), puteti anula fixarea coloanelor si randurilor.

Sfat

Daca doriti sa va definiti documentele imprimate prin afisarea etichetelor sau capetelor de linii si coloane pe fiecare pagina printata trebuie sa accesati meniul File (Fisier), apoi Page Setup (Setarea paginii) si apoi submeniul PrintTitle (Printeaza titlul). Faceti click pe caseta corespunzatoare de printat titluri (randuri pentru a repeta pe primul rand sau coloane pentru a repeta pe coloana din stanga) si selectati randul sau coloana corespunzatoare din foaia de lucru prin apasarea pe etichetele (titlurile) dorite ale randului sau coloanei.


Pagina 54/137

3.3.

3.3.1.

Folosirea calculelor in fisier

Impartirea in ecrane

Pas cu pas veti invata cum sa introduceti formule (pentru calculul subtotalurilor si totalurilor). Selectarea celulelor corespunzatoare pentru a crea formulele este mai usoara atunci cand ecranul este impartit. Exista trei optiuni valabile: Impartirea ecranului pe verticala Selectati coloana din dreapta locului in care doriti sa apara linia de impartire. Faceti click pe comanda Split (Impartire) din meniul Window (Fereastra). 

Impartirea ecranului pe orizontala Selectati randul de sub linia pe care dori sa apara linia de impartire. Faceti click pe comanda Split (Impartire) din meniul Window (Fereastra). 

Impartirea ecranului in patru zone Selectati celula pe ale carei margini doriti sa apara liniile de impartire. Faceti click pe comanda Split (Impartire) din meniul Window (Fereastra). 

Daca apasati comanda Remove Split (Inlaturare impartire) din meniul Window (Fereastra), operatia este anulata. Sfat

O modalitate rapida de a imparti ecranul este aceea de a apasa pe dreptunghiul mic si gri (caseta de impartire) de deasupra sau din dreapta sagetilor barii de navigare. Cand cursorul se transforma in cursor de impartire, trageti caseta de impartire in pozitia dorita. Operatia poate fi anulata printr-un dublu-click pe linia de impartire.

Exercitiu: Impartiti ecranul pe orizontala pentru a introduce formulele in asa fel incat sa puteti calcula subtotalul si/sau totalul in ecranul de jos. Faceti click pe celulele de referinta din ecranul de sus.


Pagina 55/137


Selectati linia 12. In meniul Window (Fereastra), faceti click pe comanda Split.

3.3.2.

Evaluarea formulelor folosind auditarea de formula

Bara de instrumente Formula Auditing (Auditul de formula) este un instrument folositor in evaluarea celulelor folosite in cadrul celulor de referinta si formulelor si pentru afisarea grafica a relatiilor dintre acestea cu ajutorul sagetilor. Meniul Tools (Instrumente) – Formula Auditing (Auditul de formula) instrumente Formula Auditing furnizeaza utilizatorului mai multe optiuni :

si Bara de

Urmarirea celulelor precedente Sagetile sunt desenate catre celula selectate de la toate celulele care sunt utilizate in formula continuta de celula selectata. 

Urmariea celulelor dependente Sagetile sunt desenate de la celula selectate catre toate celulele in care este utilizat continutul celulei selectate 

3.3.2.1. 





Sageata de urmarire albastra: Indica o referinta directa. Sageata de urmarire intrerupta cu un simbol in varful sau: Indica o referinta catre o lista de date externa. Sageata de urmarire cu buline rosii: Indica formule cu erori de valoare.

3.3.2.2.

1. 2.

Sensul sagetilor de urmarire

Crearea sagetilor de urmarire

Selectati celula pe care doriti sa o verificati. In meniul Tools – Formula Auditing, dati click pe comanda Trace Precedents sau Trace Dependents sau apasati pe butonul potrivt in bara de instrumente FORMULA AUDITING.

Facand click pe comanda Remove All Arrows (Inlatura toate sagetile) din meniul Tools – Formula Auditing sau apasand pe bara de instrumente FORMULA AUDITING , sagetile de urmarire sunt indepartate.


Pagina 56/137

3.3.2.3.

Aplicarea combinatiilor rapide de taste

Urmatoarele combinatii rapide de taste va ajuta sa evaluati relatiile dintre celule: CTRL + [

Selecteaza celulele precedente directe

CTRL + SHIFT+ [

Selecteaza celulele precedente directe si indirecet

CTRL +’

Selecteaza celulele dependente directe

CTRL + SHIFT+ ’

Selecteaza celulele dependente directe si indirecte

3.3.2.4.

Urmarirea Erorilor

Cateodata poate fi dificil si consumator de timp sa cautati sursa unei erori afisate de o formula. In acest scop, selectati o celula care contine o eroare de valoare si faceti click pe comanda Trace Error (Urmareste Eroarea) din meniul Tools – Formula Auditing. Efectul:

Sagetile de urmarire albastre sunt desenate in cazul actiunilor precedente primei formule care afiseaza o eroare de valoare. Sagetile de urmarire cu puncte rosii puncteaza aditional directia seriei de date care conduc spre formule care afiseaza erori de valori.

Erori de valori in Microsoft Excel:

#REF! #DIV/0! #NAME! #NV #VALUE! #NUMBER! # ZERO!

Formula se referea la o celula invalida. Formula contine o diviziune la zero. Formula include un nume necunoscut. Valoarea nu este disponibila. Este folosit tipul gresit de argument sau operator, de ex: un numar este multiplicat cu un text. Exisat o problema cu un numar, de ex:numarul nu poate fi afisat (ca 1*10^309) Seriile de date selectate pentru a produce o intersectie nu se suprapun.


Pagina 57/137

3.4.

Protejarea si Salvarea Fisierului de Lucru

Puteti proteja urmatoarele: Celule Foi de lucru (Continut/Scenarii/Obiecte) (Continut/Scenarii/Obiecte) Fisiere de Lucru (Structura/Afisare) Fisiere(Recomandarea Fisiere( Recomandarea doar citire si/sau protejare prin parola)

   

3.4.1.

Protejarea Celulelor

Pentru protejarea celulelor dintr-un fisier de lucru impotriva modificarilor puteti folosi functia Protect Cells (Protejeaza Celule). Facand click pe eticheta PROTECTION (PROTECTIE) din casuta de dialog Format Cells (Formatare Celule) din meniul Formatare – Celule, puteti face click pe optiunea “locked” (“blocat”) care previne celulele selectate de a fi modificate, mutate, redimensionate sau sterse. In setarile initiale, optiunea “blocat” este activata pentru toate celulele. Pentru protejarea efectiva a celulelor din fisierul de lucru, faceti click pe casuta de optiune “Protect worksheet and contents of locked cells” (Protejati foaia de lucru si continutul celulelor blocate) care se afla in casuta de dialog Protect Sheet ( Protejati Foia) din meniul Tools ( Instrumente) – Protection (Protectie). Exercitiu: Asigurati-va ca va protejati fisierul de lucru astfel incat orice intrare sau modificare a sursei de date sa fie limitate la segmentul segmentul de introducere introducere a datelor.

© Controller Institut Romania/ Contrast Management Consulting Consulting & Training

Pagina 58/137


Selectati celulele pe care doriti sa le blocati. In meniul Formatare – Celule,faceti click pe eticheta PROTECTIE a casutei de dialog Formatare Celule si dezactivati casuta „blocat“

3.

In meniul Instrumente – Protectie – Protejeaza Foie , faceti click pe casuta casuta “Protejati foia de lucru si continutul celulelor blocate” Scrieti o parola (de ex: qwert). 8 Parola trebuie scrisa din nou din motive de securitate.

4. 5.

8

Pentru a preveni utilizatorii sa citeasca parola de pe ecran, aceasta este afisata cu asterisc-uri. © Controller Institut Romania/ Contrast Management Consulting Consulting & Training

Pagina 59/137

Daca doriti sa protejati mai multe foi din fisierul de lucru in acest mod, urmati pasul 1 si 2. Selectati foile potrivite prin apasarea butonului CTRL si faceti click pe toate etichetele foii de lucru si urmati apoi pasul 3. Pentru modificarea celulelor blocate in operatiuni ulterioare, faceti click in meniul Instrumente – Protectie, scrieti parola si accesul este permis. Modul de protejare a celulelor poate fi de asemenea aplicat formulelor complexe care pot fi sterse sau inlocuite neintentionat. In acest caz, introducerea parolei nu este necesara. Facand click pe casuta potrivita din casuta de dialog Protejeaza Foia din meniul Instrumente– Protectie – Foaie puteti de asemenea sa protejati obiecte ( de ex:grafice) sau scenariile selectate.


Pagina 60/137

3.4.2.

Protejarea structurii Fisierului de Lucru

In meniul Tools – Protection, faceti click pe casuta de dialog Protect Workbook (Protejeaza Fisierul de Lucru) si activati casuta de optiune STRUCTURE (STRUCTURA). Consecintele sunt urmatoarele:  

3.4.3.

Nu puteti sterge, muta, ascunde sau redenumi foile de lucru deja existente. Nu puteti insera noi foi de lucru.

Protejarea ferestrelor fisierului de Lucru

In meniul Tools – Protection, faceti click pe casuta de dialog Protect Workbook (Protejeaza Fisierul de Lucru) si activati casuta de optiune WINDOWS (FERESTRE). Aceasta operatiune protejeaza modele prestabilite, cum ar fi marimea, aranjamentele si asa mai departe.


Pagina 61/137

3.4.4.

Protejarea fisierului si a foilor de lucru

Protejarea fisierelor impotriva vizualizarii/deschiderii lor prin setarea unei parole. Pentru a seta o parola pentru un fisier, deschideti meniul File, si selectati optiunea Save As (Salveaza ca) pentru a deschide casuta de dialog Save As. Faceti click pe Tools (Unelte) apoi faceti click pe General Options (Optiuni Generale) si scrieti parola in casuta “PASSWORD TO OPEN” (PAROLA PENTRU A DESCHIDE) a casutei Save Options (Optiuni de salvare) pentru a preveni ca utilizatorii neautorizati sa deshida fisierul de lucru fara sa aiba parola.

Protejarea "sursei fisierului de lucru" impotriva modificarilor prin setarea unei parole. Prin setarea unei parole in caseta de optiune Save Options (Optiuni de Salvare) casuta de optiune “PASSWORD TO MODIFY” ("Parola pentru a modifica") din meniul File - caseta de dialog Save As (Salvare ca) –meniul Tools (Instrumente)- caseta de dialog General Options (Optiuni General), puteti impiedica utilizatorii sa salveze modificarile facute in sursa fisierelor sau foilor de lucru. Utilizatorii au permisiunea de a modifica datele existente (cu exceptia cazului in care fisierul sau foaia de lucru sunt salvate in modul descris in capitolul 3.4.2-3.4.3), dar trebuie sa salveze modificarile facute intr-un fisier denumit diferit. Protejarea "sursei fisierului de lucru" impotriva modificarilor In cazul in care se face click pe caseta de optiune “READ-ONLY RECOMMENDED” ("RECOMANDAT DOAR IN CITIRE"), utilizatorilor care deschid fisierul de lucru sau foaia de lucru, la o data ulterioara, li se cere sa introduca parola necesara pentru acces si a salva modificarile cu un nume diferit. Pentru a elimina parolele pentru un fisier de lucru, trebuie sa deschideti documentul prin utilizarea corecta a parolei. Dupa ce ati facut click pe meniul File- Save As (Salvare ca), faceti clic pe meniul Tools si pe casuta de optiune General Options, puteti sterge parola si sa salvati fisierul din nou.


Pagina 62/137

Sfat

Pentru a diferentia utilizatorii care au permisiunea de a vizualiza si a modifica date de cei care sunt autorizati sa aiba acces fara a face modificari, atribuiti doua parole pentru a va asigura fisierele. Exercitiu: Impiedicati utilizatorii neuatorizati sa deshida sau sa modifice foaia de lucru. Mod de utilizare: 1. 2. 3. 4.

In meniul File selectati Save As, faceti click pe meniul Tools , click pe casuta de optiuni General Options si apoi faceti click pe parola pentru a deschide casuta prin introducerea “Asterix”. Verificati parola prin confirmarea ei. In meniul File selectati Save As, faceti click pe meniul Tools , click pe casuta de optiune General Options si apoi tastati parola “Idefix” pentru a modifica casuta. Inca o data aplicatia va solicita confirmarea parolei.

Efectul:

Protejarea impotriva utilizatorilor neautorizati: Doar persoanele care au parola „Asterix“ pot deschide fisierul de lucru. Daca cu toate acestea, acesti utilizatori nu cunosc parola „Idefix“, nici nu pot face modificari si nici salva sursa fisierului de lucru.

Atentie

In general, utilizatorii care nu au parola corecta nu pot anula protectia (Cu toate acestea, exista mai multi furnizori de internet care ofera servicii adecvate!) Protejarea elementelor (fisierul sau foaia de lucru) nu impiedica persoanele neautorizate sa stearga datele de pe suportul de stocare (de ex: hard disk-ul).


Pagina 63/137

3.5.

Crearea de hyperlink-uri catre zone in cadrul unui fisier

Prin crearea hyperlink-urilor, puteti naviga catre alte locatii in fisierul de lucru, catre alte documente, fisiere stocate in retea sau pe Web. Exercitiu Deschideti fisierul „09-Menu.xls“ si creati un hyperlink pentru a naviga usor dintr-o foaie de lucru in alta. Mod de utilizare: 1. 2. 3. 4.

Selectati celula 5 din foaia de lucru „Menu“. In meniul Insert (Inserare) ,faceti click pe comanda Hyperlink. Confirmati comanda in casuta de dialog Link to (Legatura catre) prin apasarea butonului PLACE IN THIS DOCUMENT (LOC IN ACEST DOCUMENT). Selectati numele foii de lucru (ex Menu) si celula dorita (ex A1).

Sfat

Prin crearea hyperlink-urilor, generati o legatura: Pentru a vedea alte fisiere (de ex: cand va referiti la date specifice) Pentru a crea un mesaj e-mail pentru o anumita adresa (de ex: cand vreti sa trimiteti un email catre autorul unui document) Pentru a introduce documente Web (de ex: Va rugam sa observati urmatoarele note...)


Pagina 64/137

4. 4.1.

Folosirea functiilor logice Aplicarea functiei IF

Functia If este folosita pentru a face comparatii logice intre expresii. Cu alte cuvinte, creaza o formula conditionala (de ex: sa corespunda unei valori specifice) care sa rezulte intr-o valoare logica. Primul exemplu (10-If_True.xls) Celulele C7:E7 afiseaza soldul contului current (inceputul saptamanii calendaristice). Calculati dobanzile care rezulta din urmatoarele conditii: Daca aveti un sold al contului curent negativ, calculati dobanda de credit la rata de 12%. Daca aveti un sold al contului current pozitiv, adaugati valorii dobanzii de plasamanet la o rata de 4%.


Pagina 65/137

Prin folosirea functiei IF puteti compara logic cele doua conditii sau expresii. Crearea Sintaxei =IF(logic_test;value_if_true;value_if_false) Introduceti urmatoarea formula in celula 9: =IF(C7<0;C7*$C$2/52;C7*$C$3/52) Testul logic: Daca aceasta conditie sau expresie este adevarata, este returnata in celula in care se afla formula “valoarea in caz de adevarat”

Vedeti exemplul: Daca soldul balantei curente este negativ atunci:C7<0 Valoare in caz de adevarat: Aceasta este returnata daca testul logic este adevarat.

Vedeti exemplul: In cazul in care conditia este adevarata, adica soldul balantei curente este negativ, atunci dobanda saptamanala este calculata prin multiplicarea soldului balantei (celula C7) cu rata de 12% (celula C2) impartita la 52 (rata anula a dobanzii convertita in rata dobanzii saptamanale) iar rezultatul este afisat in C9. C7*$C$2/52 Valoare in caz de fals Aceasta este returnata daca testul logic este fals.

Vedeti exemplul: In cazul in care conditia este falsa, adica soldul balantei curente nu este negativ (C7<0 este fals)9, atunci dobanda saptamanala este calculata prin multiplicarea soldului balantei (celula C7) cu rata de 12% (celula C2) impartita la 52 (rata anula a dobanzii convertita in rata dobanzii saptamanale) iar rezultatul este afisat in C9. C7*$C$2/52 Sfat

Formula conditionala poate fi folosita pentru a evalua daca o celula este goala. In acest caz, introduceti formula: de ex: G7=““(tastati 2 semne de ghilimele fara spatiu intre ele) Conditia poate de asemenea sa fie folosita pentru a face comparatii logice intre expresii (AND, OR). In acest caz, formula este: =IF(OR(A2=0;A4=1);value_if_true;value_if_false) 9

Conditia rezulta de asemnea intr-o valoare falsa, daca soldul balantei curente este 0.(zero). © Controller Institut Romania/ Contrast Management Consulting & Training

Pagina 66/137

4.2.

Aplicarea functiilor SUMIF si COUNTIF

Crearea unei Sintaxe =SUMIF(range;criteria;sum_range) =COUNTIF(range;criteria) Seria: Aceasta este seria de celule pe care doriti sa o evaluati. Criteriu: Criteriu, ca de exemplu text, numere, expresii, defineste celulele pe care doriti sa le adunati sau numarati. Sum_range ( Seria_suma): Celulele din Seria_suma sunt insumate numai daca celulele corespunzatoare din regiune se adapteaza criteriilor. Sfat

Atentie la faptul ca seria si Seria_suma au intotdeauna aceasi marime, ceea ce inseamna ca referinta liniei trebuie sa ramana la fel in timpul acestei operatiuni; altfel puteti avea valori incorecte.


Pagina 67/137

5. Evidentierea operatiilor cu formatare conditionata – codificarea cu ajutorul culorilor Codificarea cu ajutorul culorilor este un instrument care va lasa sa specificati si sa evidentiati conditii (valori ale celulelor) prin formatarea unei celule ( de ex: culoarea de fundal) in consecinta. Prin codificarea culorilor celulelor de referinta, puteti afisa rezultate prin culoarea verde, galbena sau rosie (ce reprezinta un semafor): Sensul culorilor de fundal Verde Yellow Rosu

Bun (in cadrul sirului de rezultate asteptate sau mai bine) Satisfacator (nu in mod semnificativ deviat) Insuficient (deviat semnificativ)

Exercitiu: Aplicati codul culorilor verde, galben si rosu ca formatare conditionala a datelor din planul de comparatie („11-TrafficLight.xls“). Clasificati rezultatele in functie de conditii, asa cum se specifica mai jos: Culoarea fundalului in functie de urmatoarele conditii: Verde Galben Rosu

Minim 5% mai mult decat in plan Deviatie de +/- 5% fata de plan Deviatie negativa de min 5%


Pagina 68/137

Mod de utilizare: Selectati seria de celule dorita si faceti click pe comanda Conditional Formatting (Formatare conditionala) din meniul Format ( Formatare). Exista trei optiuni prin care puteti sa definiti formulele conditionale: 1.

Prin selectarea frazelor de comparatie pentru a evalua daca o celula se potriveste cu o valoare specifica


Pagina 69/137

2.

Prin selectarea unor fraze de comparatie pentru a evalua daca o valoare a celulei se potriveste cu o formula

3.

Prin aplicarea unei formule conditionale (celulele trebuie sa depinda de valori)

Sfat

Prin folosirea primelor doua optiuni, doar celula care se potrivese cu criteriul de formatare (= valoarea specifica) este formatata. Daca celulele selectate indeplinesc cele trei conditii, cum se intampla pentru cea de-a treia conditie, toate isi schimba formatarea.


Pagina 70/137

6.

Utilizarea validarii datelor

Comanda Validation (Validare) din meniul Data este un intrument care va asigura ca sunt introduse doar date se potrivesc setarilor de validare a datelor pentru celulele unde este utilizata. Setarile de validare a datelor se refera la: •  

Date numerice (intre, nu intre, egal cu, diferit de, mai mare decat, mai mic decat, mai mare sau egal cu, mai mic sau egal cu) Tipuri de date ( de ex.data, timp) Liste de valori posibile ale datelor

Exercitiul 1: Vi se cere sa va optimizati abordarea de evaluare a ecuatiei balantei de echilibru a activelor, asa cum este aratat in exemplul din partea introductiva, prin aplicarea comanzii de validare (Validation) ( '09-Meniu-Solution.xls'). Mod de utilizare: 1. 2. 3. 4. 5.

Selectati seria de celule potrivita (D39:G39). Faceti click in meniul Data, pe comanda Validation (Validare) si selectati “zecimal”: din casuta de optiune a criteriului de validare. In casuta de optiune Data, faceti click pe egal cu si introduceti numarul 0 in casuta de optiune Value (Valoare). In meniul Tools (Instrumente) selectati Formula Auditing si faceti click pe Show Formula Auditing-Toolbar. Pe Bara de intrumente Formula Auditing Toolbar, apasati pe butonul CIRCLE INVALID DATA .


Pagina 71/137

Exercitiul 2: In cazul pregatirii unei liste de proiecte sunteti rugati sa va asigurati ca numai date referitoare la elementele de mai jos sunt introduce in zona de stare a proiectului (12-Validation.xls): Oferta Negociere Incheierea procedurii Ordine

   

Mod de utilizare: 1. 2. 3.

Selectati cea mai apropiata serie de celule (D8:D24). Din meniul Data, faceti click pe Validation si apoi faceti click pe casuta de optiune List in the Allow a criteriilor de validare. In casuta de optiune Sursa introduceti ca lista sursa o referinta la seria de celule B27:B30 sau introduceti numele datelor relevante (intre ghilimele) ( oferta;negociere;incheierea procedurii;ordine).

Nota

Lista sursa trebuie sa fie localizata in foaia de lucru care este selectata pentru validarea datelor. Este imposibil sa faceti referire la date ce apartin altor foi de date.

Prin folosirea casutelor de optiune INPUT MESSAGE (MESAJ DE INTRARE) si/sau ERROR ALERT (ALERTA DE EROARE) din meniu Data – Validation, puteti introduce un mesaj de informare care sa fie afisat cand date invalide sunt introduse in celula selectata. Prin folosirea optiunii Error Alert, un mesaj de eroare este afisat cand date incorecte sunt introduse intr-o celula pentru a informa sau avertiza utilizatorul. Optiunea Error Alert poate preveni utilizatorii sa introduca date invalide.


Pagina 72/137

7. Folosirea formulelor matriciale (VLOOKUP si HLOOKUP) Crearea unei sintaxe: =VLOOKUP(lookup_value;table_array;col_index_num;range_lookup) Functia VLOOKUP cauta valoarea din prima coloana a tabelului matrice (de ex: A1:F30). Bazat pe asa numita valoare de lookup sau criteriul de comparatie ( de ex: numarul specific al articolului), functia returneaza o valoare in aceasi linie din alta coloana in tabelul matrice. =HLOOKUP(lookup_value;table_array;row_index_num;range_lookup) Functia HLOOKUP cauta valoarea din prima linie a tabelului matrice (de ex:A1:F30). Bazata pe asa numita valoare de lookup sau criteriul de comparatie (de ex: numarul specific al articolului), functia returneaza o valoare in aceasi coloana intr-o linie pe care o specificati in tabelul matrice. Numarul de index al coloanei este numarul coloanei in tabelul matrice din care trebuie sa returneze valoarea care se potriveste. Numarul de index al coloanei 1 returneaza valorea din prima coloana in tabelul matrice; numarul de index al coloanei 2 returneaza valorea din a-2 coloana a tabelului matrice, si asa mai departe. Coloana in care este localizata valorea de lookup sau criteriul de comparatie trebuie sa fie prima coloana a tabelului matrice. Retineti ca numarul de indez al coloanei nu trebuie sa fie mai mare decat numarul din coloane a tabelului matrice. Valorile pentru gama de cautare pot fi indicate dupa cum urmeaza: FALS sau 0: O valoare este returnata numai daca o potrivire exacta este gasita. Valorile din prima coloana a tabelului matrice pot fi sortate sau nu. ADEVARAT sau 1: Valorile din prima coloana a tabelului matrice ar trebui sortate in ordine ascendenta. O valoare aproximata este returanta. Cu alte cuvinte, daca o valoare exacta nu este gasita, cea mai mare valoare apropiata care este mai mica decat valoarea de cautare este returnata. Daca valoarea de cautare sau criteriul de comparate sunt gasite de mai multe ori in prima coloana a tabelului matrice, prima valoare care se potriveste este returnata.


Pagina 73/137

Exercitiu: Titlul fisierului „lookup1.xls“ include o lista de articole si un formular de cotatie necompletat. Completati formularul astfel incat sa aveti de introdus (pe langa datele cumparatorului) doar sumele si numerele articolelor. Sfat

Valorile de cautare si seria de date trebuie sa fie formatate identic. Acest lucru este dificil cand datele de la diverse sisteme electronice de prelucrare a datelor sunt cautate deoarece nu poti schimba diferite formate doar facand click pe meniul Format si apsand pe coamanda Format Cells. Puteti aplica urmatoarea metoda: Pentru a aloca un format al numarului la seria de date sau la aria de cautare, scrieti numarul 1 intr-o celula cu un format al numarului si copiati celula peste intreaga serie de date facand click din meniul Edit pe PasteSpecial si facand click pe Multiply.

Nota:

Functia de cautare este potrivita pentru transferul datelor, printr-o cheie primara, ceea ce inseamna ca fiecare valoare de cautare apare o singura data in seria de date sau in aria de cautare. Pentru a va desemna propria cheie primara, puteti folosi functiile practice de text Right, Left, MID si CONCATENATE.


Pagina 74/137

8. Analiza rezultatului: Cum este rezultatul afectat de modificarea valorilor de intrare Uneori, cand doriti sa analizati potentialul riscurilor, este necesar sa examinati in detaliu cum modificarile datelor introduse pot afecta rezultatul. Microsoft Excel furnizeaza urmatoarele functii pentru analiza:   

Goal Seeking Tabele de Date (1 sau 2 variabile) Folosirea optiunii Crearea de Scenarii.

Exemplele urmatoare se axeaza pe evaluarea profitului investitiei si vor solicita sa aplicati metodele mentionate mai sus pentru analiza rezultatului.

8.1.

Analiza randamentului investitiei

Randamentul investitiei (ROI) ca o masura a eficientei utilizarii activelor, (rata profitului calculata ca procent din capitalul investit) este considerat ca fiind indicatorul cheie al sistemului de indicatori de managementului care a fost dezvoltat de catre Compania Du Pont. Analiza componentelor ROI furnizeaza informatii cu privirea la factorii care influenteaza substantial performanta de ansamblu a companiei. Exemplu de analiza ROI In fisierul de lucru „ROI1.XLS“, veti gasi graficul unui schelet ROI care a fost generat prin folosirea datelor incluse in foaia de exercitiu. Foaia include de asemenea o schema de finantare si raportul privind evolutia fluxurilor de numerar pentru a prezenta efectele diferitelor pozitii asupra lichiditatii companiei.


Pagina 75/137

8.2.

Folosirea Goal Seek

Metoda “obtinere a rezultatului” (comanda Goal Seek) ofera posibilitatea de a raspunde la urmatoarele intrebari: 



Care este cea mai mare (mica) valoare de intrare Z (celula de schimbare) pentru a optimiza valoarea celulei tinta dependenta Y si a evita sa scada sub rezultatul dorit Z(valoarea critica). Care este cea mai mare (mica) valoare de intrare Z (celula de schimbare) pentru a optimiza valoarea celulei tinta dependenta Y pentru a atinge rezultatul dorit Z (valoarea tinta).

Doar o singura definitie de intrare (referinta celulei sau numele celulei) este schimbata iar cealalte definitii ale celulelor de intrare raman neschimbate (conditia ceteris paribus) Exercitiul 1: Care este valoarea minima a vanzarilor necesara pentru a atinge un ROI de 10%? Mod de lucru: 1. 2. 3. 4. 5. 6. 7.

In meniul Tools, faceti click pe comanda Goal Seek. Scrieti in casuta SET CELL (SETEAZA CELULA) referinta sau numele celulei (celula ROI: ROI!$S$36). Trebuie intotdeauna sa introduceti o formula in casuta Set Cell. In casuta TO VALUE, introduceti valoarea dorita a celulei tinta (10%). In casuta BY CHANGING CELL, introduceti referinta sau numele celulei pe care doriti sa o schimbati pentru a atinge rezultatul dorit (celula vanzari; $H$6). Celula care variaza trebuie sa abia intotdeauna specificata o valoare. Faceti click pe butonul OK pentru a incepe goal seeking (sau prin apasarea tastei ENTER). Dupa ce procesul de goal seeking este complet, rezultatul este afisat in casuta de dialog din foaia de lucru. Faceti click pe butonul OK pentru a mentine aceste rezultate in tabel. (Alegeti Cancel daca preferati ca sursa de valori sa ramana neschimbata in tabel)


Pagina 76/137

Interpretarea rezultatelor: Pentru a atinge un ROI de 10%, vanzarile trebuie sa fie de cel putin 89,138 (cu referire la conditia ceteris paribus). Pentru a mentine versiunea originala a fisierului, salvati solutia cu un nume sau aplicati comanda Undo din meniul Edit (sau apasati butonul sau folositi o combinatie de taste rapide). Exercitiul 2 Ce rezultat al vanzarilor este necesar pentru a atinge un ROI de 20%? Mod de lucru: 1. In meniul Tools, faceti click pe comanda Goal Seek. 2. Scrieti in casuta SET CELL (SETEAZA CELULA) referinta sau numele celulei (celula ROI: ROI!$S$36(ROI)). 3. In casuta TO VALUE, introduceti valoarea dorita a celulei tinta (20%). 4. In casuta BY CHANGING CELL, introduceti referinta sau numele celulei pe care doriti sa o schimbati pentru a atinge rezultatul dorit (celula vanzari; $H$6 (vanzari)). 5. Faceti click pe butonul OK button pentru a incepe goal seeking (sau prin apasarea tastei ENTER) 6. Dupa ce procesul de goal seeking este complet, rezultatul este afisat in casuta de dialog din foaia de lucru.. 7. Faceti click pe butonul OK pentru a mentine aceste rezultate in tabel. (Alegeti Cancel daca preferati ca sursa de valori sa ramana neschimbata in tabel) Interpretarea rezultatului: Pentru a atinge un ROI de 20%, vanzarile trebuie sa fie de cel putin 92,265 (cu referire la conditia ceteris paribus) .


Pagina 77/137

Atentie

Celula a carei valoare se schimba trebuie sa contina o valoare, nu o formula. Nu puteti aplica goal seeking la o celula cu o formula. Aceasta ar rezulta intr-un mesaj de eroare. In anumite cazuri, puteti rezolva problema mai sus mentionata prin introducerea in celula a carei valoare se schimba a unei valori numerice arbitrare in loc de o formula. Apoi, puteti continua cu goal seeking. Metoda, cu toate acestea, poate fi aplicata daca celulele care sunt referinte in celula in schimbare (precedent) au de asemenea o relatie directa sau indirecta cu celula tinta (celula setata). Inainte sa inceapa goal seeking, este foarte important sa fiti constienti de diferitele interdependente (analiza structurii). Observatie

Chiar daca Microsoft Excel este o aplicatie foarte eficienta, folosirea Goal Seek pentru a defini interogari complexe poate implica perioade de calcul mai mari. Pentru situatiile foarte complexe, cautarea Goal Seek ar putea sa nu mai identifice o valoare a celulei tinta potrivte, chiar daca ar fi doar o solutie fezabila teoretica pentru rezolvarea problemei.


Pagina 78/137

8.3.

Folosirea optiunii de calcul Tabel in cadrul tabelelor de date

Comanda Tabel este un instrument pentru a evalua influenta variatiilor datelor de intrare (maxim 2 termeni pot varia) cu privire la valoarea de iesire (de ex: ROI)

8.3.1.

Tabele de date cu o singura variabila

Sarcina este sa evaluati variatiile ROI, rata profitului si raportul venitului in relatie cu variatii ale vanzarilor. In acest scop, evaluati aceste valori pentru valori ale vanzarilor de 50,000, 60,000, ... , 130,000. Mod de lucru: 1. 2.

3. 4. 5.

Scrieti valorile posibile ale vanzarilor in coloana N6:N14. Introduceti instructiunile de calculare in celula O5 (apoi P5 si Q5) 10 prin folosirea unei formule sau referinte catre o celula care contine formula necesara. De exemplu: O referinta pentru celula „ROI!O32“ (indicator referitor la venit) este introdusa in celula O5 (procedati in acelasi fel referitor la rata profitului si ROI). Selecatti zona dreptunghiulara care contine valorile introduse pentru vanzari si instructiunile (N5:Q14). Faceti click in meniul Data pe comanda Table. In casuta Coloanei de intrare, selectati celula in care doriti sa inserati valorile de intrare alternative, care sa defineasca formula pentru valorile pe care doriti sa le substituiti in celula de intrare (Daca diferitele valori de intrare sunt orientate pe rand, scrieti referinta celulei pentru celula de intrare in caseta de intrare rand).

10

Formula pentru referinta celulei trebuie sa fie intotdeauna scrisa intr-o celula localizata intr-un rand situat mai sus de prima valoare si cu o celula la dreapta fata de coloana valorilor. © Controller Institut Romania/ Contrast Management Consulting & Training

Pagina 79/137

8.3.2.

Tabele de date cu doua variabile

Dorim sa analizam schimbarile in profit in relatie cu vanzarile si marja. In acest scop evaluati ROI pentru vanzari de 50,000, 60,000, 70,000, ..., 130,000 si o marja de 31%, 32%, 33%, ... 37%. Mod de lucru: 1. 2. 3. 4. 5. 6.

Introduceti noile valori de intrare: Scrieti variatiile in vanzari in coloana S6:S14 si variatiile marjei pe linia T5:Z5. Introduceti instructiunile de calcul in celula S5 11 prin folosirea unei formule sau a unei referinte catre o celula ce contine formula necesara. De exemplu: O referinta pentru celula „ROI!S36“ (ROI) este introdusa in celula S4. Selectati zona dreptunghiulara care contine valorile de intrare alternative si insctructiunile. Faceti click in meniul Data pe comanda Table. In caseta de intrari pe linie selectati celula in care doriti sa inserati valorile de intrare alternative ale liniei (Celula $H$11). In caseta de intrari pe coloana selectati celula in care doriti sa inserati valorile de intrare alternative ale coloanei (Celula $H$6).

Facand click pe butonul OK button sau apasand tasta ENTER, valorile de iesire potrivite sunt calculate si introduse dupa cum a fost solicitat in tabelul de date.

11

Aceasta celula trebuie sa fie tot timpul situate in pareta stanga, ,marginea de sus. © Controller Institut Romania/ Contrast Management Consulting & Training

Pagina 80/137

8.4.

Crearea de scenarii

Microsoft Excel furnizeaza intrumentul Scenario Manager (Manager de scenarii) pentru crearea unor scenarii variate si afisarea lor aranjate clar in rapoarte de scenarii. Atat analiza sensitivitatii cat si analiza cu ajutorul Manager-ului Manager-ului de Scenarii evalueaza in mod similar cum variatii ale datelor de intrare afecteaza datele de iesire. In timp ce goal seeking si analiza sensitivitatii sunt tehnici utile pentru examinarea efectelor izolate a una sau doua valori de intrare intr-o serie de celule tinta, tehnica crearii scenariilor este optima pentru a formula datele de intrare folosite pentru formula cu privire la situatii variate (de ex: scenarii potentiale in cel mai bun si cel mai rau caz referitoare la o multitudine de celule cu data de intrare) Observatii

Cand creati scenarii, este util sa asociati nume pentru celulele in schimbare 12 si pentru celulele rezultat. Aceste nume vor fi utilizate in rapoartele de scenarii create ulterior.

Exercitiu: Aplicati instrumentul Manager-ul de Scenarii pentru a analiza rata profitului net, rata cifrei de afaceri, ROI si schimbarile in numerar si soldurile de principale si pentru afisarea calculelor intr-un raport de scenarii.

12

Celulele in schimbare utilizate deja din goal seeking si din crearea de tabele cu una sau doau variabile pot fi modificate prin introducerea de noi valori de intrare.


Pagina 81/137

8.4.1. 1. 2. 3.

Crearea de Scenarii

Pentru a crea crea un scenariu, folositi comanda Add din meniul Tools – Scenarios – Scenario Manager. Introduceti un nume pentru scenariu in caseta de dialog Add Scenario dialog box (de ex: cazul pesimist) Selectati Selectat i celulele in schimbare, de exemplu facand click pe acestea.

Sfat

Dupa ce ati facut click pe comanda Manager-ul de Scenarii si ati introdus caseta de dialog de Add Scenario, puteti descrie scenariul ca o nota in caseta Comment (Comentariu). 4.

In caseta caseta de dialog SCENARIO VALUES (VALORILE SCENARIULUI), SCENARIULUI), definiti definiti valorile valorile celulelor in schimbare pentru scenariu.

5.

Urmati pasii pasii 1,2 si 4 pentru pentru a crea scenariile scenariile „cazul cel mai probabil“ probabil“ si „cazul optimist“ .


Pagina 82/137

Observatie

Numele celulelor pentru care ati atribuit valori alternative ale scenariului vor fi afisate doar daca ati definit nume pentru ele.

8.4.2.

Afisarea unui scenariu selectat in foaia de lucru

Faceti click pe scenariul pe care doriti sa il afisati si apoi faceti click in meniul Tools pe Scenario Manager si apoi pe comanda Show(Arata).

8.4.3.

Crearea unui Raport de Scenariu

Instrumentul Manager-ul de Scenarii va ajuta sa creati un raport care rezuma clar valorile de iesire si rezultatele scenariilor scenariilor definite. Mod de lucru: 1. 2. 3.

Scenario-Manager. Faceti click pe comanda Summary (Rezumat) din meniul Tools – Scenario-Manager In caseta caseta de dialog dialog Scenario Summary, alegeti tipul de raport dorit. In caseta RESULT CELLS (CELULE REZULTAT), selectati celulele (de ex. ex. facand click pe fisierul de lucru), pe care doriti sa le adunati in raportul scenariului (celulele (celulele A1,... sau numele celulelor). Celulele Celulele rezultate trebuie sa fie in lista activa.


Pagina 83/137

9. Crearea rapoartelor de contabilitate manageriala cu MS Excel 9.1.

Folosirea rapoartelor

„Information ist die Grundvoraussetzung nachhaltig erfolgreichen unternehmerischen Handelns.“ original quotation in German (A. Picot: Informationsmanagement und Unternehmensstrategie S. 759) Traducere: Informatia serveste ca baza pentru operatiile de afaceri performante de succes care au efect pe termen lung. (Traducere: A.Picot: Managementul inforamtiilor si Strategia de Afaceri, pag.759) “Informatia nu este insuficienta. Ce este insuficient este timpul manager-ului de a participa la toate informatiile disponibile”. (Herbert Simon) „Das Berichtswesen eines Unternehmens umfasst alle offiziellen, materiell und formell bestimmten Informationen, die den Verantwortlichen zur Aufgabenerfüllung zur Verfügung gestellt werden. Der Controller trägt durch seine Informationsaufgabe die Verantwortung für das Berichtswesen des Unternehmens.“ original quotation in German (vgl. R. Eschenbach: Controlling S. 459) Traducere: Rapoartele de afaeri inlcud oficial documente si informatii formale furnizate persoanele responsabile pentru a-si indeplini un anumit set de sarcini Managerul contabil este responsabil de sistemul de raportare a unei companii. (Compara cu R. Eschenbach: Contabilitate Manageriala, pagina 459).

Intelegerea Rapoartelor si problemele aparute in legatura cu acestea Nevoia de informatii: reprezinta finalizarea strangerii de informatii esentiale pentru a indeplini o sarcina specifica (obiectivul urmarit) Cererea de informatii: Cererea subiectiva de informatii Supply of information: means the availability of information from sources within and outside a company Methods to evaluate the need for information: System of requirements planning (questioning technique) The method of applying critical successful factors © Controller Institut Romania/ Contrast Management Consulting & Training

Pagina 84/137

There are the following types of Reports 



Standard reports: Planning reports Deviation reports Status reports Special reports

Required key factors of reports 13 Information must be: Objective Verifiable Provided within deadlines Object-oriented User-friendly Reports must inlude key facts (one page report). Reports must be customized and recipient-oriented (consolidation) Reports must exclude frequent alterations (in standard reports) Reports must display clear and precise definitions (manual) Consistent Reliable Rational

    

  

Reports can be presented by

Text Tables Visual options (graphics or charts)

  

13

Compare with C. Böhm/J. Müller in R. Eschenbach: Controlling © Controller Institut Romania/ Contrast Management Consulting & Training

Pagina 85/137

9.2.

Crearea de grafice pentru vizualizarea datelor

9.2.1.

Prezentarea grafica a datelor

Prin prezentarea grafica a datelor se poate evidentia usor cum diferite date relationeaza intre ele. 9.2.1.1.

Tipuri de grafice

Sunt disponibile urmatoarele tipuri de grafice:     

Pie charts (bi sau tri dimensional) Bar charts Column charts Line charts XY (scatte) charts

MS Excel va pune la dispozitie de asemenea si urmatoarele subtipuri de grafice fata de cele enumerate mai sus:       

Area charts Doughnut charts Radar charts Surface charts Bubble charts Stock charts Cylinder, cone or pyramide charts


Pagina 86/137

9.2.1.2.

Alegerea celui mai potrivit tip de grafic

Selectarea unui grafic specific este dependenta de datele expuse in proiect si prezinta intotdeauna comparatii de valori. Exista urmatoarele tipuri de comparatii standard 14:

Tip standard

Mesaje

Structura Ordine Perioada de timp

Contributia fiecarei valori in total Seria de obiective Modificari aparute dupa o perioada de timp Marimea distributiei Relatiile dintre valorile variabile 15

Frecventa distributiei Corelatia

Tipuri de diagrame portrivite (Sugestie)

Pie chart Bar chart Column sau line chart Column sau line chart Bar sau xy scatter chart

15

Cu alte cuvinte, doriti sa evaluati daca doua valori ale variabilelor care relationeaza intre ele rezulta intr-un “model normal”: de ex: daca o crestere a vanzarilor rezulta intr-o crestre a profitului. © Controller Institut Romania/ Contrast Management Consulting & Training

Pagina 87/137

9.2.2.

Despre ChartWizard

Prin folosirea ChartWizard-ului se poate crea usor un nou grafic sau se poate modifica unul deja existent. Mod de utilizare Exercitiul este de a va prezenta grafic datele din Registrul de calcul 'Chart1.xls'

Obiectivul este de a prezenta datele cu privire la 1. tendinta in vanzari si rezultate, 2. dezvoltarea structurii vanzarilor, 3. tendinta in costuri legate de vanzari intr-o maniera lizibila.


Pagina 88/137

9.2.2.1.

Folosirea ChartWizard-ului

Facand click pe comanda Grafic din meniul Inserare sau apasand butonul CHARTWIZARD, va apare ChartWizard-ul care va permite sa urmati diversi pasi pentru a va particulariza graficul. Exista doua optiuni pentru a insera datele pentru grafic: Prin introducerea seriei de date in pasul 2 al ChartWizard-ului sau Facand click pe celule inainte ca ChartWizard-ul sa apara  

Cand apare CharWizard-ul, pagina urmatoare este afisata prin apasarea butonului Next (URMATORUL). Daca vreti sa reveniti la pagina anterioara pentru a face modificari apasati butonul BACK (INAPOI). Mod de utilizare: 1. Selectati celulele B4:G4;B8:G8;B10:G10. 2. Apasati butonul CHART Wizard. 9.2.2.2.

Utilizarea pasului 1 din ChartWizard – Tipuri de grafice

In pasul 1, selectati tipul sau subtipul de grafic dorit din tipurile standard: Facand click pe graficul tip coloana Apasand butonul NEXT (Inainte) Facand click pe subtipul coloana (clustere). Apasand butonul NEXT (Inainte).    

Tineti apasat butonul “Press and Hold to View Sample” (Apsati si mentineti apasat pentru a previzualiza graficul) pentru a vizualiza un exemplu de diagrama folosind datele selectate.


Pagina 89/137

9.2.2.3.

Utilizarea pasului 2 din ChartWizard – Sursa de date a graficului

Acest pas va ajuta sa corectati sursa de date selectata (pentru a compara cu punctul 1). Facand click pe una din casutele din zona Series In referitoare la coloane sau randuri puteti alege daca doriti sa afisati seriile de date selectate din randuri sau coloane ca axe in grafic.


Pagina 90/137

9.2.2.4.

Utilizarea pasului 3 din ChartWizard – Optiunile graficului

Aici puteti alege dintr-o varietate de optiuni ale graficului din diferitele foi de calcul (titluri, axe, linii de grila, legenda, etichete de date, tabelul de date):

9.2.2.4.1.

Titluri

Eticheta Titluri va ajuta sa introduceti sau sa modificati titlulul unui grafic precum si descrierile axelor acestuia. In cadrul exemplului, introduceti urmatoarele descrieri sau modificari din tabelul de mai jos: Titlul graficului: Categoria axei (X): Valoarea axei (Y):

Dezvoltarea vanzarilor si rezultatelor An in EUR

Puteti de-asemenea sa introduceti si referinte la anumite celule (de ex. introduceti in casuta Titlul graficului = A1, daca textul din celula A1 va fi f olosit pentru a denumi titlul graficului.).


Pagina 91/137

9.2.2.4.2.

Axe

Aceasta eticheta va ajuta sa selectati axele in relatie cu seriile de date. Puteti ascunde date specifice cand tendintele in dezvoltare, de exemplu, ar trebuie sa fie incluse in grafic fara a afisa valorile efective (aceasta decizie ar putea sa nu fie acceptata de toti) In exemplu, nu ascundeti nici o informatie, cu alte cuvinte nu uitati sa bifati ambele casute.


Pagina 92/137

9.2.2.4.3.

Linii de grila

Prezentarea informatiilor cu linii de grila incorporate pentru axele X si Y imbunatateste vizualizarea valorilor. Linii de grila majore:

Impart diagrama in axa X si axa Y in cateva grupe mari

Linii de grila minore:

Reprezinta subdiviziuni suplimentare

Omiteti liniile de grila in cadrul acestui exercitiu. Mod de utilizare: Nu faceti click pe nici una din casutele de optiune a liniilor de grila.


Pagina 93/137

9.2.2.4.4.

Legenda

Prin folosirea legendei pot fi identificate seriile de date. Desigur, nu sunteti obligati sa afisati o legenda in cadrul graficului ( = Casuta de optiune SHOW LEGEND ( AFISATI LEGENDA) poate fi dezactivata). Eticheta Legenda va lasa sa definiti sau sa alternati locatia acesteia in cadrul graficului (Casuta de optiune PLACEMENT (LOCATIE))


Pagina 94/137

9.2.2.4.5.

Etichete de date

Facand click in Data Labels (Etichete de Date) pe o casuta de optiune (de ex. VALOARE), puteti adauga in cadrul graficului valoarea efectiva a datelor.


Pagina 95/137

9.2.2.4.6.

Tabele de date

Optiunea SHOW DATA TABLE (Arata tabelul de date) permite adaugarea tabelului de date sursa in cadrul graficului. Prin folosirea casutei de optiuni SWOW LEGEND KEYS (ARATATI SIMBOLURILE LEGENDEI) puteti alege daca vreti sa inserati simbolurile legendei sau nu in tabelul cu date. Nu afisati un tabel de date in cadrul acestui exercitiu (nu bifati casuta de optiune SHOW DATA TABLE).


Pagina 96/137

9.2.2.5.

Utilizarea pasului 4 din ChartWizard – Locatia graficului

In acest pas puteti decide unde sa amplasati graficul. 



Ca o foaie de calcul noua (faceti click pe casuta AS NEW SHEET si introduceti numele noii foi de calcul) sau Ca un obiect in foaia de calcul (faceti click pe casuta AS OBJECT IN si selectati foaia de calcul dorita cu sageata jos)

Plasati graficul in Trade Ltd. ( = Tabel 1) in cadrul acestui exercitiu.

Prin apasarea butonului FINISH (FINALIZARE) graficul este completat si plasat conform comenzilor ca o noua foaie de calcul sau ca un obiect in foaia de calcul. Puteti modifica locatia si marimea graficului prin folosirea mouse-ului. Observatie

Puteti ajusta un grafic deja existent pentru a va indeplini cerintele prin folosirea si a altor optiuni. Faceti click pe anumite elemente corespunzatoare din grafic (de ex. categoria axei X sau valoarea axei Y) si faceti click pe comanda corespunzatoare din meniul FORMAT (FORMATARE). In functie de ce element al graficului ati selectat, apar foi care va permit sa selectati alte optiuni ale graficului.


Pagina 97/137

9.2.3.

Crearea unui portofoliu de analiza cu matricea B.C.G

Exercitiu: Exercitiul consta in analiza portofoliul prin crearea unei matrice de tip growth-share (cresterecota de piata) cu patru zone (ca cel din graficul de mai jos) pentru datele a trei unitati de afaceri strategice (Grafic2.xls)

Analiza portofoliului cu ajutorul matricei crestere-parte

Matricea Boston Consulting Group sau matricea growth-share (crestere-cota de piata) este un instrument analitic folosit in managementul portofoliului pentru analiza unitatilor de afaceri sau a liniilor de productie. Matricea este impartita in patru zone.


Pagina 98/137

Mod de utilizare: 1. 2. 3. 4.

Selectati celulele B4:E7. Apasati butonul CHART WIZARD. Selectati tipul de grafic bubble chart. Faceti click pe eticheta DATA RANGE (SERIE DE DATE) si in SERIES IN pe casuta de optiune COLUMN (Coloane).


Pagina 99/137

5.

In eticheta SERIES (SERII) se determina referinta celei mai apropiate celule.


Pagina 100/137

6.

Pentru a defini cea de-a treia serie de date faceti click pe comanda ADD (ADAUGA) si introduceti referinta celulei.

7.

Completati titlul graficului si al etichetelor axelor X si Y.


Pagina 101/137

8. 9.

Faceti click pe casuta de optiune AS OBJECT (CA OBIECT) : Tabel 1. Facand dublu-clik pe valoarea axei X va aparea meniul de context al FORMAT AXIS. Aici puteti face click pe urmatoarele optiuni ale graficului:

1. Grosimea axei de valori X:

2. Scala axei de valori X:


Pagina 102/137

3. Marimea font-ului

4. Procedati in acelasi fel pentru a selecta optiunile graficului pentru axa de valori Y:


Pagina 103/137

10. Facand dublu-click pe zona graficului apare fereastra FORMAT PLOT AREA. Definiti grosimea liniei folosite ca margine.


Pagina 104/137

9.2.4.

Displaying Charts in the Background

Exercise: The task is to insert a graphic titled “Excel for management accountants” (File: “Background.gif”) “Background.gif”) in the background of your portfolio analysis. How to Proceed: 1. 2.

Select the chart area. ChartArea – Patterns – Fill Effects On the Format menu, click the command Selected ChartArea – Picture – Picture. 3. Select the file „Background.gif“. „Background.gif“. 4. Select the chart area. 5. On the Format menu, click the command Selected PlotArea and click in the AREA option box: none. Note

To insert graphs in the background of your worksheet, click the command Sheet – Background on the Format menu and define the desired file.


Pagina 105/137

9.3. Crearea rapoartelor in format text- Copierea datelor din Excel in Word Functiile PASTE (LIPIRE) si LINK (LEGATURA) sunt folosite pentru a incorpora datele Excel intr-un document Word. PASTE (LIPIRE) copiaza datele existente la un moment dat in cadrul unui schimb static de date. Datele din Excel devin parte integranta a documentul Word. In contrast cu aceasta functie, Link (Legatura) leaga permanent datele de fisierul sursa Excel si le actualizeaza (manual sau automat) in conditiile unui schimb de date dinamic.

9.3.1.

Inserarea datelor din Excel intr-un document Word


Faceti click pe datele din Excel. Din meniul Edit (Editare), faceti click pe comanda Copy (Copiere) (sau folositi combinatia de taste CTRL+C) Mutati punctul punctul de inserare in documentul documentul Word in locul unde unde doriti doriti sa inserati datele datele din Excel. Faceti click pe comanda comanda Paste (Lipire) din din meniul meniul Edit (Editare) (Editare) ( sau sau in meniul Edit Edit – Paste Special ), faceti click pe optiunea PASTE si selectati cu ajutorul optiunii AS tipul informatiei pe care vreti sa o lipiti:

Tabelul de obiecte al Microsoft Excel: Puteti aplica functiile MS Word pentru a edita un obiect. Facand dublu-click pe obiect, se deschide Excel iar obiectul (nu cel original) poate fi modificat. Text formatat (RTF): Text neformatat: Poza: Bitmap:

Datele sunt incorporate ca un tabel al MS Word. Celulele sunt separate de spatii goale. Facand dublu-click pe date acestea pot fi modificate in MS Draw. Este posibila editarea in MS Draw.


Pagina 106/137

9.3.2. Excel

Crearea unei legaturi intre un document Word si datele din


Selectati datele din documentul Excel. Faceti click pe comanda Copy din meniul Edit (sau folositi combinatia de taste CTRL+C). Mutati punctul punctul de inserare in documentul documentul Word in locul unde unde doriti doriti sa inserati datele datele din Excel. Faceti click pe optiunea LINK din meniul Edit – Paste Special si definiti informatia incorporata facand click in casuta de option AS (de ex. Ca Microsoft Excel Table Object sau Text Formatat (RTF)).

Metoda mentionata mai sus nu este recomanda r ecomandata ta daca doriti sa inserati celule aditionale, linii sau coloane in tabelul Excel. In acest scop, definiti legatura atribuind un nume seriei de celule dorite: Mod de utilizare: 1. 2. 3. 4.

Dati un nume seriei de celule dorite (vezi Capitolul Definirea Definirea Numelor) Selectati seria de celule dorite si faceti click pe comanda Copy din meniul Edit. Mutati punctul punctul de inserare in documentul documentul Word in locul unde unde doriti doriti sa inserati datele datele din Excel. Faceti click pe optiunea LINK din meniul Edit – Paste Special si definiti informatia incorporata in casuta casuta de option AS (de ex. Ca Microsoft Excel Table Table Object sau Text Formatat (RTF)).


Pagina 107/137

Spargerea si blocarea legaturilor: Faceti click pe Command Break Link (Comanda Spargere Legatura) din meniul Edit– Link pentru a sparge o legatura dinamica. Prin aplicarea optiunii LOCKED (BLOCARE) din meniul Edit – Link, legatura dinamica este sparta temporar (pana cand optiunea LOCKED este inlaturata). Prin folosirea optiunii ACTUALIZARE MANUALA din meniul Edit – Link, puteti determina ca datele nu sunt actualizate automat ci doar facand click pe comanda ( Comanda Actualizeaza Acum din meniul Edit – Link). Optiunea ACTUALIZARE AUTOMATA din meniul Edit menu – Link se aplica pentru a actualiza datele automat.


Pagina 108/137

9.4. Personalizarea rapoartelor – Selectarea si consolidarea datelor orientate catre destinatar In anumite situatii vi se poate solicita sa livrati informatii relevante pentru un anumit destinatar intr-un format adaptat acestuia (fie pe ecrant fie in varianta printata). Pentru acest scop, puteti crea mai multe versiuni ale fisierului de lucru. In ceea ce priveste raportul dumneavoastra, puteti determina: • • • • •

Ce foi de lucru Ce scenarii de lucru (optional) Ce asezari in pagina pentru imprimare (optional) (de exemplu, pentru a imprima intregul fisier de lucru sau numai sectiuni din acesta) Ce ordine Ce setari pentru imprimare (de exemplu, note de subsol)

ar trebui sa fie incluse sau imprimate.


Pagina 109/137

9.4.1.

Definirea setarilor pentru printare

In meniul File (Fisier)– Page Setup (Setare Pagina), puteti defini zona de printare si deasemenea alti parametrii care sunt inclusi in printarea propriu-zisa (antet si note de subsol, margini, etc). 9.4.1.1.

Pagina

Eticheta Page (Pagina) va ofera optiuni variate referitoare la marimea si orientarea paginii.


Pagina 110/137

9.4.1.2.

Margini

Eticheta Margini se refera la marginile paginei, distantele dintre antet, notele de subsol si margine si centrul paginii.

Sfat

In loc sa introduceti in casutele de optiune numere pentru marginile paginii puteti de asemenea sa faceti click pe comanda PRINT PREVIEW (PREVIZUALIZARE PRINTARE) din meniul File si sa selectati butonul MARGINS (MARGINI) de unde acestea pot fi miscate manual. Marginile pot fi de asemenea trase facand click pe iconita Print Preview si apoi selectand butonul Margins.


Pagina 111/137

9.4.1.3.

Antet/Nota de subsol

De aici puteti alege dintre antetele si notele de subsol deja predefinite sau sa particularizati Antetul/Nota de subsol.

Sfat

Fiecare tabel din fisierul de lucru ar trebui sa includeti intotdeauna urmatoarele informatii in antet si nota de subsol: Data





Autorul (numele intreg sau abrevierea, initiale)



Numele fisierului si cateodata si numele listei de date



Listele de date ce contin mai mult de o pagina ar trebui sa includa numarul paginii curente si numarul total de pagini.


Pagina 112/137

9.4.1.4.

Foaia de lucru

Aici puteti defini zona de imprimare din foaia de lucru precum si optiuni aditionale.

Zona de imprimare este definita de celulele de referinta (de ex: A1:F30), de o serie de celule carora li s-a definit un nume sau de diverse serii de celule (Referintele sau numele celulelor trebuie sa fie despartite prin punct si virgula, de ex: A1:F30; G40:K50). Sirurile de celule neadiacente sunt imprimate pe doua pagini.


Pagina 113/137

9.4.1.5.

Grafic

Aici puteti determina scala si calitatea printarii unui grafic, daca zona de imprimare include si un grafic.


Pagina 114/137

9.4.2. Crearea de vizualizari personalizate Folositi comanda Custom Views (Vederi Personalizate)– Add Views din meniul View pentru a seta fisierul de lucru sa apara in modul in care doriti sa il vedeti sau printati. In acest scop puteti defini 



Setari de ecran (de ex: marimea si pozitia ferestrei, simbolurile gruparilor, scalarea, celulele active, etc.) sau Setari de imprimare (de ex: orientare, margini, antet, nota de subsol, scalarea, zona de printare etc.)

Puteti sa va definiti vizualizari personalizate prin salvarea sub un anumit nume. Comanda Custom Views – Add Views din meniul View ofera posibilitatea de a comuta intre vederi personalizate diferite.

9.4.2.1.

Definirea vizualizarilor

Exercitiu Creati rapoarte orientate catre destinatar care sunt bazate pe datele din fisierul de lucru „Report1.XLS“ pentru directori si pentru Directorul departamentului X.


Pagina 115/137

9.4.2.2.

Definierea unei vizualizari pentru intreaga companie - Directori

1. Determinarea setarilor ecranului: Determinati setarile de afisare dorite de pe ecran prin definirea vederii cu ajutorul mai multor functii (divizarea ferestrei, blocarea panourilor, scalarea, ascunderea sau afisarea liniilor/coloanelor, simbolurile gruparilor, etc.). Facand click pe casuta de dialog VIEW din meniul Tools– Options, puteti alege din mai multe optiuni pentru afisarea diferitelor elemente:


Pagina 116/137


Pentru a omite liniile de grila, nu faceti click pe casuta de optiune GRIDLINES Selectati vizualizarea grupata asa cum este aratat in imaginea de mai jos pentru a defini vederea Result-Entire Company-Executives.


Pagina 117/137

2. Determinarea Setarilor de Imprimare Definiti zona dorita pentru printare si parametrii potriviti (ca de exemplu antet si nota de subsol, margini, etc) din meniul File– Page Setup: Mod de utilizare: Definiti diferitii parametrii in foile de lucru potrivite:    

Landscape (pesisaj) Scaling (scalare) 80% Margins (margini): Sus si jos 2; stanga si dreapta 1,5; antet si nota de subsol 1,3 Negru si alb

Definiti antetul si nota de subsol in concordanta cu exemplul.


Pagina 118/137

3. Salvarea vizualizarii si setarile pentru imprimare: In meniul View – Custom Views – Add Views, puteti memora vizualizarile si setarile imprimarii prin atribuirea de nume fiecarei vizualizare definita. Salvarea ajustarilor curente cu numele Entire Company -Executives Faceti click pe comanda Add din meniul View – Custom Views si scrieti numele „Entire company-executives“ in cauta potrivita. Observatie

Tinand cont de faptul ca toate vizualizarile noi pe care le-ati definit pentru fisierul de lucru activ au fost afisate in casuta Name box (casuta de dialog Add View) din meniul View – Custom Views, este util sa includa numele fisierului de lucru activ in numele vizualizarii pentru a asigura identificarea clara. Numele vizualizarilor, cu toate acestea, nu ar trebui sa fie prea mari, doar un anumit numar de caractere pot fi acceptate cand creati o vizualizare personalizata.

Numele este vizibil din aproximativ acest punct.

Faceti click in casuta de optiuni PRINT SETTINGS pentru a salva setarile pentru imprimare curente adaugate in foaia de lucru (Ariile de printare definite se salveaza intotdeauna impreuna cu vizualizarea). Faceti click pe casuta de optiune HIDDEN ROWS AND COLUMNS pentru a salva aceste optiuni. Liniile si coloanele raman ascunse in vizualizarea adaugata. Procedati in acelasi fel pentru a defini celelalte vizualizari.


Pagina 119/137

9.4.2.3. Schimbarea vizualizarii

Pentru a selecta o vizualizare faceti click pe numele vizualizarii dorite in casuta de dialog CUSTOM VIEWS (meniul View – Add Views) si faceti click pe comanda Show.

9.4.3. Working with the Report Manager MS Excel offers the possibility to summarize several custom views that were created for a workbook in reports related to specific target groups. Unfortunately, this procedure can only be applied to one workbook. 9.4.3.1.

Creating a Report

How to Proceed – Creating a Report for the Executives Click the command Add on the View menu – Report-Manager. Enter in the REPORT NAME box the report’s name (Executives). Select the other areas by down arrow: Select the sheet „Result-Entire Company“ and the view „Result-Entire Company-Executives“ and click Add.


Pagina 120/137

Select the other views (sheet and view) in the same manner. In addition, click in the CONSECUTIVE PAGE NUMBER option box so that your report’s pages are numbered consecutively. Confirm your entries by pressing the OK button.


Pagina 121/137

How to Proceed – Creating a Report for the Senior Manager of the Business Unit X Click the command Add on the View menu – Report Manager. Enter in the REPORT NAME box the report’s name (Senior Manager-Business Unit_X). Select the various areas by down arrow: Select the sheet „Result-Business Unit_X“ and the view „Result-Business Unit_X-Overview“ and click Add. Select the sheet „Result-Business Unit_X“ and the view „Result-Business Unit_X-Detail“ and click Add. Confirm your entries by pressing the OK button. Create the reports for the other senior managers in the same way. 9.4.3.2.

Printing a Report

To print your report, select the desired report on the View menu – Report Manager and click the PRINT button.


Pagina 122/137

10. 10.1.

Analiza datelor Sortarea si filtrarea datelor si obtinerea de subtotaluri

10.1.1. Sortarea datelor Puteti sorta datele in ordine ascendenta sau descendenta: alfabetic, numeric sau cronologic (in functie de data, de ex: in functie de luna, zilele saptamanii)

  

Exercitiu: („Resultdata1.xls“) Sortati datele in functie de grupa produsului (ordine ascendenta). Mod de utilizare: 1. 2. 3. 4.

Selectati o celula din tabel (lista). Faceti click pe comanda Sort (Sortare) din meniul Data. Selectati numele campului ((2) Description) ca si criteriu in caseta SORT BY (sortare dupa) si optiunea ASCENDING (crescator) Daca este necesar, faceti click in MY LIST HAS (LISTA MEA CONTINE)...casuta de optiune HEADER ROW (PRIMUL RAND).


Pagina 123/137

5. Faceti clik pe butonul OK. Observatie: Este esential sa selectati intodeauna intreaga serie de date pe care doriti sa o sortati. Daca este selectata o singura celula, este posibil ca MS Excel sa nu recunoasca acea valoare ca o serie de date adiacenta ceea ce ar determina sortarea partiala a datelor. Legatura catre sursa datei s-ar putea pierde. Atentie

Cand selectati serii de tabele care contin formule cu referinte ale celulelor, pot aparea confuzii ale datelor. De exemplu, liniile pot fi incorect folosite pentru calcule.

10.1.2. Filtrarea datelor – Folosirea Comenzii AutoFilter (Filtrare Automata) Functia AutoFilter16 va permite sa ascundeti date ce nu intrunesc criteriul specificat pentru subsetul de date selectate temporar. Cum sa procedati: 1. 2. 3. 4.

16

Selectati o celula din tabel (lista). Faceti click pe comanda AutoFilter din meniul Data – Filter (Filtru). Sagetile Auto Filter-ului vor aparea in partea dreapta a etichetelor coloanei in seria de date filtrata Faceti click pe sageata Auto Filter-ului din coloana REGION (REGIUNE) si selectati „North“ (Nord) ca si criteriu. Faceti click pe sageata Auto Filter-ului din coloana DELIVERY NUMBER (Numarul livrarii) si selectati „Mills“ ca criteriu.

Functia se aplica datelor de vanzare ale grupului de produse ”Mills” doar pentru regiunea Nord.


Pagina 124/137

Pentru a inlatura criteriul filtrului pentru o coloana anume, alegeti optiunea ALL (TOT) din lista derulanta. Facand click pe comanda Show All (Afisati tot) din meniu Data – Filter, datele ascunse anterior sunt afisate din nou. Puteti opri functia Auto Filtru, facand click inca o data pe comanda Auto Filter din meniul Data- Filter. Prin folosirea casutei de dialog Auto Filter Top 10 puteti selecta primele/ultimele 10 (sau selectati un alt numar) valori ale informatiilor continute in coloana. Casuta de dialog Custom Auto Filter va ofera posibilitatea sa definiti doua criterii ale filtrului care relationeaza una cu alta logic.


Pagina 125/137

10.1.3. Calcularea valorilor subtotale si totale Microsoft Excel poate calcula automat subtotalul si totalul valorilor (de ex: sume) pentru anumite coloane din lista. Prin folosirea functiei de subtotal nu trebuie sa introduceti formule in foaia de calcul. Subtotalurile sunt afisate automat in liniile inserate (o eticheta este inclusa). Aditional, MS Excel calculeaza totalul, grupeaza seturile de date si le pune in evidenta. Exercitiu: Sarcina este sa calculati venitul realizat, costurile marfurilor si marja obtinuta pentru grupele de produse in februarie. Mod de utilizare: 1. 





2. 3. 4.

Inserati o coloana pentru fiecare grupa de produse: Venit (Cantitate * Pret de lista * (1-Discount) * (1- Discount Special)), Costul bunurilor (Cantitate * Pret de achizitie) si Marja (Venit – costul bunurilor) . Selectati o celula din lista. Sortati datele din lista in functie de grupele de produse. Faceti click pe comanda Subtotal din meniul Data.


Pagina 126/137

5. 6. 7. 8. 9.

Selectati in casuta AT EACH CHANGE IN (LA FIECARE MODIFICARE A) numele corect al campului (eticheta coloanei). Puteti alege tipul de calcul (rezumat al calculului) in casuta USE FUNCTION (FOLOSESTE FUNCTIA). In casuta ADAUGA SUBOTALUL LA faceti click pe “Venit” si “Marja”. Faceti click pe casuta de optiune SUMMARY BELOW DATA (REZUMA SUB DATE). Faceti click pe butonul OK.

Prin folosirea comenzii Remove All (Elimina tot) din meniul Data - Subtotal, valorile subtotalului si totalului sunt eliminate.


Pagina 127/137

10.2.

Optiunile PivotTable

10.2.1. Crearea listelor interactive cu ajutorul PivotTable pentru rezumarea si analizarea datelor Wizard-ul PivotTable este un instrument folositor ce organizeaza, rezuma si analizeaza cantitati mari de date prin intermediul unor instructiuni de calcul de mare viteza. Caracteristica PivotTable va lasa sa faceti calcule pentru liste afisate in doua dimensiuni, in timp ce subtotal, de exemplu, creeaza calcule in liste “normale”(o “singura dimensiune”). Prin utilizarea Wizard-ului PivotTable, puteti alege dintre criteriile pentru filtrarea si adaptarea datelor la structura de rezultate a PivotTable (pentru a va prezenta rezultatele in acest aranjament). Filtrele aplicate la listele de date sau la valorile de subtotal calculate anterior sunt luate in considerare in noul aranjament. Exemplu:


Pagina 128/137

10.2.2.

Examplu de analiza a vanzarilor (Partea a-2-a) – Exercitiul 1

Exercitiul consta in analiza datelor stranse in lista din prima parte a analizei vanzarilor prin folosirea PivotTable. Incepeti prin crearea unui PivotTable care afiseaza valorile vanzarilor referitoare la grupele de produs si la zona de vanzare. 10.2.2.1.

Deschiderea Wizard-ului PivotTable

Odata ce ati creat lista de date 17, puteti face click pe orice celula din lista, deschide meniul Data si face click pe comanda Tabel Pivot si pe PivotChart Report (Raportul Tabelului Pivot) pentru lansarea PivotTable si a PivotChart Wizard (Wizard-ului Tabelului Pivot). Observatie

Daca faceti click pe o singura celula, este posibil ca Wizard-ul PivotTable sa nu recunoasca intreaga lista de date cand se creaza Tabelul Pivot. Din acest motiv, este necesar sa selectati lista completa.

17

Notati ca selectarea prin click a unei celule din lista de date este posibila doar pentru acest exemplu care reprezinta un PivotTable bazat pe date preluate dintr-o lista Excel. Daca doriti sa reafisati datele din orice alta sursa de date (vezi capitolul 10.2.4.1. Setarea unei surse de date) faceti click pe orice celula din foaia de lucru. © Controller Institut Romania/ Contrast Management Consulting & Training

Pagina 129/137

10.2.2.2.

Definirea sursei de date

In pasul 1, trebuie sa identificati sursa de date pentru PivotTable. Graficul urmator arata ca sursa de date deriva din lista MS Excel, dar PivotTable se pot referi de asemenea la surse de date externe. Cum sa procedati: 1. 2.

Urmati instructiunile si faceti click pe casuta de optiuni MS Excel list or database (Lista sau baza de date MS Excel). Faceti click pe butonul NEXT pentru a ajunge la urmatoarea casuta de dialog.


Pagina 130/137

10.2.2.3.

Selectarea datelor corecte din lista de analizat

In pasul 2, selectati celulele care contin datele din lista pe care doriti sa le analizati. In momentul in care orice celula este activa, MS Excel va propune o serie de celule pe care o puteti confirma sau modifica prin introducerea sau activarea (de ex. prin folosirea mouseului) seriei de celule corecte.

Mod de utilizare: 1. 2. 3.

Verificati daca wizard-ul PivotTable a introdus corect seria de celule propuse pentru PivotTable pe care doriti sa il creati. Altfel va trebui sa modificati seria de celule. Faceti click pe butonul NEXT pentru a ajunge la urmatoarea casuta de dialog.

Nota

Prin folosirea comenzii Browse (Cautare) puteti folosi sursa de date derivata dintr-un fisier de lucru pe care nu l-ati deschis inca prin introducerea seriei de date cerute si numele foii fisierului. Notati ca toate filtrele care au fost aplicate listei de date vor fi luate in calcul de PivotTable (comanda Filtru din meniul Data). Din moment ce wizard-ul calculeaza automat valoarea subtotalului si a totalului, subtotalurile si totalurile care au fost calculate anterior ar trebui sterse din lista de date inainte sa fie folosite in crearea PivotTable.


Pagina 131/137

10.2.2.4.

Definirea zonei tinta si crearea PivotTable

In pasul 3, puteti defini zona tinta pentru pozitionarea raportului PivotTable.

1. 2.

Faceti click pe casuta de optiune NEW WORKSHEET (FOAIE DE LUCRU NOUA) option box. Faceti click pe butonul FINISH (TERMINARE).

10.2.2.5.

Personalizarea continului si asezarea in pagina

In al-4 –lea pas , definiti aparitia si asezarea in pagina a raportului PivotTable prin:   

Stabilirea coloanelor si randurilor, Definirea instructiunilor de calcul Definirea filtrelor pentru limitarea datelor afisate in PivotTable, daca este necesar.

Mod de lucru: 1. 2. 3. 4. 5.

Trageti butonul campului grupei produsului(DESCRIPT2) catre campul Row (rand) al PivotTable. Trageti butonul campului REGION catre campul Column (coloana) al PivotTable. Trageti butonul campului INCOME catre campul DATA a PivotTable. Daca este necesar, modificati tipul calculului facand dublu-click pe butonul campului INCOME in campul DATA. Faceti click pe butonul NEXT pentru a ajunge la urmatoarea casuta de dialog.


Pagina 132/137

Nota

In versiunile curente ale aplicatiei MS Excel, trebuie sa faceti click pe butonul Layout ( Asezare in pagina) al wizard-ului in pasul 3 pentru a manevra aparitia si structura Tabelului Pivot ca in graficul de mai sus. Daca in schimb faceti click pe butonul NEXT, Tabelul Pivot este aranjat si formatat direct in foaia de calcul in acelasi fel. 10.2.2.6.

Explicarea Barei de Instrumente a Tabelului Pivot

Tabelul Pivot este creat automat iar bara de instrumente specifica este afisata. Facand click pe butoanele de optiune afisate in bara de instrumente puteti edita sau modifica Tabelul Pivot pentru a controla felul in care datele sunt afisate.


Pagina 133/137

10.2.3. Exemple de analiza a vanzarilor (Partea 2)–Exercitiul 2 Exercitiul consta in crearea unui PivotTable avand la baza urmatoarele elemente: Prezentati datele vanzarii produselor companiei sortate dupa grupele produselor (afisate pe linii) si dupa agentul de vanzari al companiei sortate dupa sediile sucursalelor (afisate in coloane). Mod de utilizare: 1. 2. 3. 4. 5. 6.

7. 8.

Lansati Wizard-ul PivotTable, definiti sursa de date si confirmati sau modificati sursa de date corecta descrisa anterior. Trageti butonul campului grupei produsului(DESCRIPT2) catre campul ROW al PivotTable. Trageti butonul campului grupei produsului(DESCRIPT1) catre campul ROW al PivotTable sub campul grupa produsului. Trageti butonul campului REGION catre campul COLUMN al PivotTable. Trageti butonul campului omului de vanzari ((LAST)NAME) catre campul COLOANEI PivotTable, la dreapta campului REGION. Trageti butonul campului INCOME catre campul DATA al PivotTable.

Faceti click pe butonul NEXT pentru a ajunge la urmatoarea casuta de dialog. Faceti click pe casuta de optiune FOAIE DE CALCUL NOUA (NEW WORKSHEET) si pe butonul END.


Pagina 134/137

10.2.4. Personalizarea PivotTable in exemplul “Analiza vanzarilor” (Partea 2)–Exercitiul 3 Exercitiul consta in afisarea aceleasi informatii cerute la exercitiul 2 cu exceptia ca datele trebuie filtrate dupa grupa produsului companiei. Mod de utilizare: Optiunea 1 de Glisare a Campurilor Glisati campul Grupa Produsului catre zona care este in afara ferestrei listei campului PivotTable (cu alte cuvinte, trageti campul grupa de produse in afara raportului PivotTable)



1.

Optiunea 2 de Lansare a Wizard-ului PivotTable. Modificati Tabelul Pivot prin deplasarea campului casuta (DESCRIPT2) catre campul PAGE(Pagina) al PivotTable. Confirmati comanda facand click pe butonul END (Finalizare).



1. 2.

grupa

produsului


Pagina 135/137

10.2.5. Afisarea paginilor Butonul SHOW PAGES (AFISEAZA PAGINILE) din Bara de instrumente a Tabelului Pivot si comanda SHOW ALL PAGES Of (AFISATI TOATLE PAGINILE DIN) din meniul context va lasa sa copiati toate “paginile” unui camp al paginii intr-o foaie de calcul separata.

10.2.6. Afisarea datelor sursa a unei celule intr-o serie de date Facand dublu click pe o celula intr-o serie de date o lista bazata pe datele din acea celula se va deschide intr-o foaie de calcul noua.

10.2.7. Actualizarea datelor in PivotTable Modificarile datelor intr-o lista de surse (lista de date selectate) folosite pentru a crea un PivotTable pot fi reflectate prin actualizarea Tabelului Pivot dupa cum urmeaza: Faceti click pe comanda Refresh Data (Actualizeaza Datele) din meniul Data Faceti click pe butonul REFRESH DATA din Bara de intrumente a PivotTable si faceti click pe Outline.  


Pagina 136/137

Manual Excel

Recommend Documents