Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere...

109
ABSOLUTE SCHOOL Tehnici avansate de lucru cu Microsoft Excel SUPORT DE CURS Calea Plevnei 136, Bucuresti Tel. 021.31.41.444 / 07 27 357 623 / 07 66 48 73 48 Web: www.pregatire.net E-mail: [email protected] v. 08.2016

Transcript of Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere...

Page 1: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL

Tehnici avansate de lucru cu

Microsoft Excel

SUPORT DE CURS

Calea Plevnei 136, Bucuresti

Tel. 021.31.41.444 / 07 27 357 623 / 07 66 48 73 48

Web: www.pregatire.net

E-mail: [email protected]

v. 08.2016

Page 2: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

2

Cuprins Partea I - Microsoft Excel – Formule. Functii uzuale ................................................................. 4

1. Formule. Functii uzuale ........................................................................................................... 4

2. Functii logice ............................................................................................................................ 9

3.Realizarea legaturilor intre registre de calcul ........................................................................ 11

4. Functii de numarare si insumare ........................................................................................... 12

2.Partea II - Microsoft Excel – Functii Excel ............................................................................ 19

1.Functii matematice si statistice .............................................................................................. 19

2.Functii de data si ora .............................................................................................................. 22

3.Functii de text ......................................................................................................................... 31

4.Functii de cautare si referinta: VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX ................... 37

3.Partea III - Microsoft Excel – Tehnici de sinteza si analiza .................................................... 49

1.Subtotaluri .............................................................................................................................. 49

2.Generarea tabelelor pivot (rapoarte)..................................................................................... 56

Crearea unui tabel pivot: ........................................................................................................... 57

Structura tabelului pivot: .......................................................................................................... 58

Utilizarea mai multor functii in cadrul aceluiasi tabel pivot: .................................................... 59

Actualizarea unui tabel pivot. Modificarea sursei unui tabel pivot: ......................................... 61

Formatarea unui tabel pivot: .................................................................................................... 63

Diferente valorice si procentuale: ............................................................................................. 67

Gruparea datelor ....................................................................................................................... 72

Campuri calculate ...................................................................................................................... 74

Generarea unei diagrame pentru un tabel pivot ...................................................................... 76

EXERCITII TABELE PIVOT ............................................................................................................ 79

4.Partea IV - Microsoft Excel ................................................................................................. 93

Page 3: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

3

1. Formatarea conditionala ....................................................................................................... 93

2.Validarea si auditul datelor .................................................................................................... 97

Validarea datelor ....................................................................................................................... 97

Auditul unei foi de calcul ......................................................................................................... 102

3.Protejarea informatiilor........................................................................................................ 103

Page 4: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

4

Partea I - Microsoft Excel – Formule. Functii uzuale Tipuri de date

Valorile care se pot introduce in celule pot fi de 3 tipuri: texte, date numerice si date calendaristice.

Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat aliniate la stanga in celula. In cazul textelor lungi, daca celula din dreapta e libera textul se revarsa peste aceasta, iar daca e ocupata va fi afisat trunchiat (dar, este memorat in intregime). Recomandare: Nu este indicat sa se lase coloane si linii necompletate. Se vor putea redimensiona pentru ca informatia sa fie afisata in intregime in celula.

Numerele - sunt combinatii de cifre de la 0-9 si caractere speciale. Ele sunt automat aliniate la dreapta in celula. Precizia de reprezentare a numerelor este de 15 cifre semnificative. Daca un numar nu poate fi afisat in spatiul oferit de celula apare ###... (desi nr. este corect memorat, pentru a-l vizualiza este necesar sa redimensionam coloana). Virgula zecimala si separatorul pentru mii se pot vizualiza / schimba din Control Panel – Regional Settings.

Datele calendaristice - exprima date (ex. data angajarii 12/07/2011). Excel le trateaza, de fapt, tot ca pe niste numere. Sunt aliniate implicit la dreapta in celula. Pot fi folosite in calcule. Datele calendaristice sunt tratate ca niste numere deoarece, fiecare data calendaristica este reprezentata de un serial_number. Spre exemplu, daca se va formata data calendaristica 12/07/2011 ca si numar, rezultatul este 40736, iar acest numar semnifica numarul de zile de la 1 ian 1900 pana in 12 iul 2011.

1. Formule. Functii uzuale

a) Referintele celulelor

Adresele sau referintele identifica celule sau grupuri de celule (domenii). Adresele spun programului Excel in care celule sa caute pentru a gasi valori ce vor fi folosite in formule.

Adresele sau referintele de celule cele mai intalnite sunt:

- relative ex. B5

- absolute ex. $B$5 (folosind tasta F4)

O adresare relativa - specifica programului Excel directia si distanta pentru a gasi locatia, de exemplu: “mergi 2 celule in sus si una la dreapta”. Acest tip de referinta spune cum poate fi gasita celula, pornind de la celula care contine formula.

O adresare absoluta - este o adresa exacta a unei celule. Ex. $E$1 (relativ ar fi E1)

B2 => cu doua celule spre stinga

$E$1=> celula de la intersectia liniei 1 cu col. E

Transformarea unei adrese relative in adresa absoluta se face apasand tasta functionala F4.

Page 5: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

5

Exemplu: Diferenta intre o adresa relativa si o adresa absoluta

Rezultatele din coloana Valoare au fost obtinute prin formula: cantitate * pret. Algoritmul de determinare al valorii este identic pentru orice produs. In acest sens, se va determina valoarea pentru primul produs, iar pentru restul produselor se va copia formula (se va selecta celula in care s-a efectuat primul calcul / click pe coltul din dreapta jos al celulei selectate / se trage pana la ultimul produs).

Rezultatele din coloana TVA din Valoare au fost obtinute prin formula: valoare * 24%, unde 24% va fi inserat in formula selectand celula J1. Dat fiind faptul ca valoarea 24% este aceeasi pentru orice produs, atunci celula J1 trebuie fixata. Dupa ce a fost fixata formula, se va putea copia si pentru restul produselor.

Concluzie: Microsoft Excel ajusteaza automat formulele care folosesc adrese relative, daca le copiem in alt loc. Daca vrem ca o adresa sa nu fie modificata prin copierea formulei in alta locatie, folosim adrese absolute.

b) Utilizarea formulelor

O formula este un enunt compus din operatori si operanzi. Formulele sunt memorate in celulele foii de calcul, iar Excel afiseaza in aceste celule rezultatele evaluarii formulelor.

Operatorii sunt simbolurile care precizeaza operatia / actiunea pe care o va efectua asupra operanzilor (ex. +, -, *, / , etc.)

Excel reuneste patru categorii de operatori:

operatori aritmetici - care actioneaza asupra valorilor numerice si au ca rezultat tot valori numerice: + (adunare) , - (scadere), * (inmultire), / (impartire), ^ (ridicare la putere)

Exemplu:

1. Sa se determine: Valoare, TVA din Valoare, Valoare cu TVA, Val. Discount Variabil, Valoare discount Fix, Valoare incasata cu TVA dupa discount utilizand adrese relative si adrese absolute.

Page 6: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

6

operatorul pentru texte - care combina prin concatenare doua sau mai multe valori de tip text intr-o singura valoare de tip text: &.

Exemplu:

Sa se concateneze Numele cu Prenumele.

operatori relationali - care compara doua valori de acelasi tip si au ca rezultat o valoare logica: TRUE sau FALSE: = (egal), > (mai mare strict) , < (mai mic strict) , >=, (mai mare sau egal) <= (mai mic sau egal), <> (diferit)

Exemplu:

operatori pentru referinte de celule sau domenii de celule - care combina doua referinte de celule intr-o referinta compusa.

Page 7: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

7

Operator referinta Semnificatie Exemple

: Operator de zona care produce o referinta la toate celulele dintre doua referinte, inclusiv cele doua referinte.

SUM(B2:D7)

,

sau

;

Operator de uniune care combina referinte multiple intr-o singura referinta

SUM(B2:D2,B4:D4,B6:D6)

sau

SUM(B2:D2;B4:D4;B6:D6)

spatiu Operator de intersectie care combina referinte multiple intr-o singura referinta SUM(B4:D5 C2:C7)

c) Functii uzuale

Excel dispune de aproximativ 15 000 de functii, grupate pe diverse categorii: Statistice, Matematice, Text, Data si ora, Cautare si referinta.

La baza, o functie este tot o formula: efectueaza anumite prelucrari.

Sintaxa unei functii: = numefunctie (argumente).

Cele mai utilizate functii sunt: SUM, MIN, MAX, AVERAGE, COUNT

Functia SUM: determina suma informatiilor din mai multe celule selectate sau suma informatiilor unui domeniu de valori ( = SUM(A1,A3,A5) – suma informatiilor a trei celule, =SUM(A1:A5) – suma informatiilor a cinci celule, de la A1 pana la A5)

Functia MIN: determina minimul dintr-un domeniu de valori

Functia MAX: determina maximul dintr-un domeniu de valori

Functia AVERAGE: determina media aritmetica dintr-un domeniu de valori

Functia COUNT: determina numarul de celule care contin date numerice

Inserarea functiilor uzuale se poate realiza din fila Home / Gruparea editing:

Exemplu:

Page 8: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

8

Realizarea legaturilor intre foile de calcul

Exemple:

1. Pentru determinarea deviatiei dintre 2010 si 2009, la nivel de trimestru se utilizeaza datele din: Foaia Incasari (aici se afla datele de prelucrat) si Foaia Raport (aici se doreste rezultatul)

Foaia Incasari contine datele aflate mai jos:

Page 9: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

9

Foaia Raport cu urmatoarele date:

OBS: Preluarea referintelor (celulelor) in cadrul formulelor cu date din foi diferite necesita fixarea datelor atunci cand este cazul.

2. Functii logice

Deschideti fisierul 2.functia IF.xls (foaia de calcul IF(1).

Sa se determine DACA:

targetul din 2012 a fost atins

targetul din 2013 a fost atins

vanzarile din 2013 sunt mai mari decat vanzarile din 2012

atat targetul din 2012, cat si targetul din 2013 au fost atinse

cel putin targetul din 2012 a fost atins sau targetul din 2013 a fost atins

Sintaxa: IF(logical_test;value_if_true;value_if_false)

Page 10: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

10

Unde:

Logical_test – conditia / criteriul

Value_if_true – valoarea / formula / mesajul returnat in cazul in care conditia este adevarata

Value_if_false – valoarea / formula / mesajul returnat in cazul in care conditia este falsa

Functia AND - intoarce TRUE daca toate argumentele sale sunt TRUE; intoarce FALSE daca unul sau mai multe argumente sunt FALSE.

Sintaxa: AND(logical1;logical2, ...)

Logical1, logical2, ... sunt de la 1 pana la 255 de conditii pe care le testati si care pot fi TRUE sau FALSE.

Argumentele trebuie sa poata fi evaluate la valori logice cum ar fi TRUE sau FALSE sau argumentele trebuie referinte care contin valori logice.

Daca un argument referinta contine text sau celule goale, acele valori sunt ignorate.

Daca zona specificata nu contine nici o valoare logica, AND intoarce valoarea de eroare #VALUE!.

Functia OR - intoarce TRUE daca cel putin un argument este TRUE; intoarce FALSE daca toate argumentele sunt FALSE.

Sintaxa: OR(logical1;logical2;...)

Logical1,logical2,... sunt de la 1 pana la 255 conditii pe care vreti sa le testati si care pot fi TRUE sau FALSE.

Argumentele trebuie sa poata fi evaluate la valorile logice TRUE sau FALSE sau sa contina referinte catre valori logice.

Page 11: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

11

Daca un argument referinta contine text sau celule goale, acele valori sunt ignorate.

Daca zona specificata nu contine nici o valoare logica, OR intoarce valoarea de eroare #VALUE!.

3.Realizarea legaturilor intre registre de calcul

In coloana Status RCA afisati textul DA in cazul in care incasarile sunt cel putin egale cu targetul (incasarile si target-ul se vor prelua din fisierul 3.1 Target si vanzari.xls).In caz contrar (incasarile sunt mai mici decat targetul), afisati textul NU.

Target RCA

Target CASCO

Target LOCUINTE

450000 600000 300000

Agenti Judet Asigurare

RCA

Elena Grecu Iasi 412.445

Elena Grigore Arad 378.000

Florin Cretu Timis 650.000

Florin Dumitru Gorj 717.703

Ion Florea Dolj 230.000

Ion Negru Teleorman 323.100

Ion Vicol Giurgiu 450.000

Ionel Maxim Iasi 392.016

John Negru Arad 377.119

Leonora Mihai Timis 362.221

Mabel Ionescu Gorj 347.323

Madalina Cretu Dolj 332.425

Agenti Judet Status RCA

Elena Grecu Iasi

=IF('[3.1 Target si vanzari.xls]vanzari'!C7>='[3.1 Target si vanzari.xls]target'!$A$4;"Da";"nu")

Elena Grigore Arad nu

Florin Cretu Timis Da

Florin Dumitru Gorj Da

Ion Florea Dolj nu

Ion Negru Teleorman nu

Page 12: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

12

Ion Vicol Giurgiu Da

Ionel Maxim Iasi nu

John Negru Arad nu

Leonora Mihai Timis nu

4. Functii de numarare si insumare

Functia COUNTIF este o functie statistica. Numara cate celule indeplinesc un singur criteriu.

Sintaxa : COUNTIF(range;criteria)

Range - este zona din care vor fi numarate celulele (zona in care se regaseste criteriul)

Criteria - este criteriul de selectare, sub forma unui numar, expresie sau text, referinta catre o celula. De exemplu, criteria poate fi exprimat ca 32, "Franta".

Exemplu:

Sa se determine numarul de facturi / tranzactii la nivel de supermarket, unde baza de date este:

iar rezultate se doreste in tabelul:

Page 13: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

13

unde:

- Range-ul este domeniul de celule B1:B120 fixat, intrucat, indiferent de criteriu, in aceeasi zona de celule se va face verificarea / numararea

- Criteria: este celula F2 care contine textul Selgros. Celula F2 nu se fixeaza pentru a putea copia formula, iar urmatorul rezultat sa fie pentru Metro (celula F3).

Sau

Unde:

Range-ul este intreaga coloana B:B, care nu este obligatoriu sa fie fixata, intrucat, in momentul copierii formulei, Excel vizualizeaza intreaga coloana.

Criteria: F2 fara a fi fixata.

Functia SUMIF - Aduna dintr-un domeniu valoric doar informatiile care indeplinesc un singur criteriu (suma salariilor angajatilor din departamentul Marketing – criteriul este Marketing, suma incasarilor din decembrie – criteriul este decembrie, suma incasarilor rezervarilor hoteliere din Franta – criteriul este Franta etc).

Sintaxa: SUMIF(range;criteria;sum_range)

unde:

range - zona de celule in care se regaseste criteriul

Page 14: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

14

criteria - un criteriu de selectie, sub forma unui numar, expresie sau text, care defineste care celule sa fie adunate. De exemplu, criteriul poate fi exprimat ca 32, „Franta”.

sum_range - celulele care trebuie insumate. Celulele din sum_range sunt insumate numai daca celulele care le corespund in argumentul range indeplinesc criteriul de selectie. Daca sum_range este omis, sunt insumate celulele specificate in argumentul range.

Exemplu : Se considera baza de date de mai jos, unde fiecare rand din tabel reprezinta o factura / tranzactie. Trebuie determinate : suma incasarilor pentru fiecare supermarket, precum si numarul de tranzactii pentru fiecare supermarket.

Rezultatele se doresc in tabelul:

Criterii introduse de la tastatura :

Page 15: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

15

Criteriul Selgros este inserat de la tastatura, acest lucru inseamna ca formula nu poate fi copiata, doarece indiferent pentru ce supermarket se doreste prelucrarea, criteriul va fi Selgros.

Utilizarea corecta si eficienta a functiei SUMIF presupune :

Fixarea domeniilor Range si Sum_Range deoarece, indiferent pentru ce supermarket se doreste insumarea datelor, Excel « se uita » in aceleasi domenii de valori.

Criteriul este recomandat sa fie inserat prin referintele celulelor (selectand supermarketul) pentru a putea copia formula. In caz contrar, se va calcula pentru fiecare supermarket functia SUMIF.

Criterii utilizate prin referintele celulelor :

O alta modalitate de calcul:

In exemplul de mai sus au fost fixate domeniile Range si Sum_range pentru a putea copia formula.

Daca in foaia de calcul nu mai exista alte date in partea de jos a datelor destinate calculelor, atunci fixarea celor doua domenii nu mai este necesara. Pentru Range se va selecta toata coloana B (click pe eticheta coloanei), iar pentru Sum_range se va selecta toata coloana D (click pe eticheta coloanei).

Page 16: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

16

Functia COUNTIFS este o functie statistica. Numara cate celule indeplinesc cel putin un criteriu (de preferat minim doua criterii; daca se foloseste un singur criteriu, se poate utiliza functia COUNTIF)

Sintaxa : COUNTIFS(criteria_range1;criteria1;criteria_range2;criteria2......)

Criteria_Range1 - este zona din care vor fi numarate celulele (zona in care se regaseste criteriul1)

Criteria1 - este primul criteriu de selectare, sub forma unui numar, expresie sau text, referinta catre o celula.

Criteria_Range2 - este zona din care vor fi numarate celulele (zona in care se regaseste criteriul2)

Criteria2 - este al doilea criteriu de selectare, sub forma unui numar, expresie sau text, referinta catre o celula.

etc

Exemplu: Se considera baza de date de mai jos:

Sa se determine numarul de clienti din fiecare subcategorie (Productie, Comert, servicii,

etc) la nivel de tip client (small, medium, gold).

Page 17: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

17

Unde:

Criteria_range1 este coloana Categorie client (B) fixata, intrucat va fi copiata formula la

dreapta, iar daca nu va fi fixata, va fi preluata coloana Tip_Client (C);

Criteria1: este primul criteriu (Productie – celula A14, unde se fixeaza doar coloana A,

pentru ca atunci cand se va copia formula in jos si la dreapta, criteriile trebuie sa ramana

in coloana A, modificandu-se doar randurile)

Criteria_range2 este coloana Tip_client (C) fixata, intrucat va fi copiata formula la dreapta,

iar daca nu va fi fixata, va fi preluata coloana An inregistrare (D);

Criteria2: este al doilea criteriu (small – celula B13, unde se fixeaza doar randul 13, pentru

ca atunci cand se va copia formula in jos si la dreapta, criteriile trebuie sa ramana in randul

13, modificandu-se doar coloanele)

Rezultatul va fi:

Numar clienti Tip Client

Categorie small medium gold

Productie 11 33 30

Servicii 12 22 16

Comert 14 5 5

Institutii publice 15 0 1

TOTAL 0 0 0

Functia SUMIFS este o functie matematica. Aduna rezultatele filtrate dupa cel putin un criteriu (de preferat minim doua criterii; daca se foloseste un singur criteriu, se poate utiliza functia SUMIF)

Sintaxa : SUMIFS(sum_range;criteria_range1;criteria1;criteria_range2;criteria2......)

Sum_range – este zona din care vor fi adunate datele

Criteria_Range1 - este zona in care se regaseste criteriul1

Criteria1 - este primul criteriu de selectare, sub forma unui numar, expresie sau text, referinta catre o celula.

Page 18: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

18

Criteria_Range2 - este zona in care se regaseste criteriul2

Criteria2 - este al doilea criteriu de selectare, sub forma unui numar, expresie sau text, referinta catre o celula.

etc

Exemplu: Se considera baza de date de mai jos:

Sa se determine suma vanzarilor din 2012 pentru fiecare categorie de client (Productie, Comert,etc) si pentru fiecare tip de client (Small si Gold).

Functia:

Unde:

Sum_range – coloana Vanzari 2012 (coloana E) fixata intrucat tot timpul se vor aduna datele din aceasta coloana, iar in momentul copierii formulei, datele de adunat trebuie sa ramana tot in coloana D

Restul argumentelor functiei sunt identice cu cele ale functiei COUNTIFS.

Page 19: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

19

2.Partea II - Microsoft Excel – Functii Excel

EXCEL are o biblioteca de functii predefinite, grupate pe mai multe categorii:

functii matematice si trigonometrice (Math & Trig): - permit efectuarea de calcule matematice simple si complexe;

functii statistice (Statistical) - permit efectuarea unor calcule statistice utilizand serii de valori;

functii logice (Logical) - determina valoarea de adevar corespunzatoare unei conditii;

functii de baze de date (Database) - permit efectuarea de calcule utilizand subseturi de date ;

functii de cautare si referinta (Lookup & Reference) - permit localizarea continutului unei celule;

functii financiare (Financial) - permit realizarea de calcule economico-financiare predefinite;

functii de data si ora (Date & Time) - manipuleaza numere care reprezinta date calendaristice sau timp;

functii de text (Text) - ofera informatii legate de textul existent in celule si permit operatii cu acestea

functii definite de utilizator.

Sintaxa unei functii: =numefunctie(arg1, arg2, … , arg n)

Unde:

numefunctie este numele functiei (SUM, AVERAGE, COUNTIF, VLOOKUP etc)

arg 1, arg 2, … , arg n sunt argumentele unei functii (celule: A1, B6 etc ; sau domenii=grup de celule: A1:A4)

1.Functii matematice si statistice

Functii matematice:

Functia ROUND - Rotunjeste un numar la un numar specificat de cifre.

Sintaxa :ROUND (numar , numar_pozitii)

unde numar_pozitii:

rotunjirea la dreapta pozitiilor zecimale.

= 0 rotunjirea la intreg.

< 0 rotunjire la partea intreaga.

Page 20: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

20

Exemple:

ROUND(2,15; 1) egal 2,2

ROUND(2,149; 1) egal 2,1

ROUND(-1,475; 2) egal -1,48

ROUND(21,5; -1) egal 20

Functia ROUNDUP - rotunjeste prin adaos un numar la un numar specificat de cifre.

Sintaxa : ROUNDUP (numar , numar_pozitii)

Exemple:

ROUNDUP(3,2;0) egal 4

ROUNDUP(76,9;0) egal 77

ROUNDUP(3,14159; 3) egal 3,142

ROUNDUP(-3,14159; 1) egal -3,2

ROUNDUP(31415,92654; -2) egal 31.500

Functia ROUNDDOWN - rotunjeste prin lipsa un numar la un numar specificat de cifre.

Sintaxa : ROUNDDOWN (numar , numar_pozitii)

Exemple:

ROUNDDOWN(3,2; 0) egal 3

ROUNDDOWN(76,9;0) egal 76

ROUNDDOWN(3,14159; 3) egal 3,141

ROUNDDOWN(-3,14159; 1) egal -3,1

ROUNDDOWN(31415,92654; -2) egal 31.400

Functia TRUNC – truncheaza la un numar specificat de zecimale (ATENTIE: nu rotunjeste, ci „taie” la un numar specificat de zecimale.

Exemple:

TRUNC(3,145;0) egal 3

TRUNC(7,489;2) egal 7,48

Functia MROUND – rotunjeste la un multiplu specificat. Functia MROUND rotunjeste prin adaos, in sensul dinspre 0, daca restul impartirii numarului la multiplu este mai mare sau egal cu jumatate din valoarea multiplului.

MROUND(1234;100)=1200

MROUND(1278;100)=1300

Page 21: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

21

Functia SUMPRODUCT- Inmulteste componentele corespondente din sirurile date si intoarce suma acelor produse.

Sintaxa: SUMPRODUCT(array1;array2;array3; ...)

Array1, array2, array3, ... sunt de la 2 pana la 255 de siruri ale caror componente vreti sa le inmultiti si apoi sa adunati produsele.

Argumentele trebuie sa aiba aceleasi dimensiuni. Daca nu, SUMPRODUCT intoarce valoarea de eroare #VALUE!.

SUMPRODUCT trateaza valorile din siruri care nu sunt numerice ca si cum ar fi egale cu zero.

Exemplu:

Stiind Pret Unitar si Cantitatea, sa se determine suma incasarilor, total discount valoric, Total TVA.

Valoare totala =SUMPRODUCT(B2:B18;C2:C18)

Total Discount Valoric =SUMPRODUCT(B2:B18;C2:C18;D2:D18)

Total TVA =SUMPRODUCT(B2:B18;C2:C18;E2:E18)

Stiind Pretul Unitar si Total Cantitate Vanduta, sa se determine suma incasarilor.

Page 22: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

22

=SUMPRODUCT(B34:J34;B35:J35)

Functia Sumproduct se poate utiliza si in cazul in care dorim sa adune valori numerice care indeplinesc mai multe criterii (este o alternativa pentru functia Sumifs disponibila doar in versiunile de Office 2007,2010 si 2013).

Exemplu 1:Se considera baza de date de mai jos. Sa se calculeza valoarea totala a incasarilor din localitatea Bucuresti (criteriul este Bucuresti)

Incasari totale Bucuresti =SUMPRODUCT((B6:B26="Bucuresti")*(G6:G26))

Exemplu 2: Sa se calculeze incasarile totale ale agentului Popa in Ploiesti (in acest caz avem 2 criterii care se introduc intre paranteze rotunde ca in exemplul de mai sus)

=SUMPRODUCT((A6:A26="Popa")*(B6:B26="Ploiesti")*(G6:G26))

2.Functii de data si ora

Functiile de data si ora prelucreaza date calendaristice sau serial_number-ul aferent unei date calendaristice. Spre exemplu, daca se va introduce data 25/12/2010 si se va formata ca numar, atunci serial_numberul va fi 40537.

Acest serial_number inseamna numarul de zile care a trecut de la 01/01/1900. Din acest lucru rezulta ca diferenta dintre doua date calendaristice este un numar de zile. Daca se doreste determinarea duratei unui proiect atunci formula utilizata va fi : data de sfarsit minus data de inceput.

Functia DATE – creeaza data calendaristica, specificand anul / luna / ziua.

Sintaxa : =DATE(an;luna;zi)

Anul - poate fi format din una pana la patru cifre. Excel interpreteaza argumentul an in concordanta cu sistemul de data calendaristica utilizat. Implicit, Excel pentru Windows utilizeaza sistemul de data calendaristica 1900.

Functia DATE este foarte utila in formulele in care anul, luna si ziua nu sunt constante ci sunt la randul lor formule.

Exemplu

Page 23: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

23

Sa se determine data fabricarii listei de medicamente, stiind ziua, luna si anul fabricarii.

Functia NOW - Intoarce data si ora curenta.

Sintaxa: =NOW( )

Functia TODAY - Intoarce data curenta.

Sintaxa: =TODAY( )

Atat functia NOW, cat si functia TODAY actualizeaza data curenta si ora curenta, respectiv data curenta.

Daca se doreste inserarea datei curente, fara ca aceasta sa se actualizeze, atunci se va utiliza combinatia de taste: CTRL+;

Functia YEAR - Returneaza anul corespunzator unei date calendaristice. Anul este returnat ca un intreg cuprins in intervalul 1900-9999.

Sintaxa: =YEAR(numar_serial)

Page 24: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

24

unde serial_number va fi introdus printr-o data calendaristica.

Exemplu: Sa se determine anul, luna, ziua expirarii medicamentelor din lista, stiind data expirarii.

Functia MONTH - Returneaza luna unei date calendaristice reprezentate printr-un numar serial. Luna este data ca un intreg, cuprins intre 1 (ianuarie) si 12 (decembrie).

Sintaxa: =MONTH(numar_serial)

Functia DAY - Returneaza ziua din data calendaristica, reprezentata ca un serial_number. Ziua este un intreg cuprins intre 1 si 31.

Sintaxa : =DAY(numar_serial)

Page 25: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

25

Functia Datevalue face conversia unei date stocate ca text intr-un numar serial pe care Excel il recunoaste ca data.

Exemplu: Calculati data scadenta a facturilor (conform imaginii de mai jos); intrucat in coloana C, Data emiterii este stocata ca text (este afisata in partea stanga), este necesar sa o convertim in nr. serial dupa care se aduna cu numarul de zile de gratie.

Functia Text efectueaza conversia unei valori numerice in text si permite specificarea modului de afisare.

Exemplul 1: =Text(A1;” RON 0,00”) afiseaza valoarea numerica din celula A1 (23,5) astfel: RON 23,50.

Exemplul 2: Afisati luna livrarii sub forma „mmmm”; rezultatul va fi : iunie

Page 26: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

26

Calculul diferentei dintre doua date calendaristice

a) Calculul numarului de ore dintre doua date calendaristice din aceeasi zi, precum si din zile diferite:

b) Calculul numarului de zile dintre doua date calendaristice

OBS: Diferenta dintre doua date calendaristice este un numar de zile. Nu se tine cont ca cele doua date sunt din acelasi an sau din ani diferiti.

c) Calculul numarului de luni dintre doua date calendaristice din acelasi an si din ani diferiti:

d) Calculul numarului de ani dintre doua date calendaristice

Page 27: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

27

Functia DATEDIF – returneaza numarul exact de ani / luni / zile dintre doua date calendaristice. Este utilizata in calcularea vechimii, varstei unui angajat.

Sintaxa: =DATEDIF(data de inceput; data de sfarsit;Output)

Unde Ouput-ul este unul dintre urmatoarele argumente:

Output Descriere Explicatii

"d" Zile Numarul de zile dintre Data1 si Data2

"m" Luni Numarul de luni intregi dintre Data1 si Data2

"y" Ani Numarul de ani intregi dintre Data1 si Data2

"yd" Zile excluzand Ani Numarul de zile dintre Data1 si Data2, din acelasi an

"ym" Luni excluzand Ani Numarul de zile dintre Data1 si Data2, din acelasi an, aceeasi luna

"md" Zile excluzand Ani si

Luni Numarul de luni dintre Data1 si Data2, din acelasi an

Exemplu: Sa se determine vechimea angajatilor din lista (numar ani intregi).

Functia NETWORKDAYS - returneaza numarul zilelor lucratoare cuprinse intre start_date si end_date. Zilele lucratoare exclud weekend-urile si toate datele identificate ca zile nelucratoare. NETWORKDAYS se utilizeaza pentru a calcula castigurile / tichetele de masa ale angajatilor in functie de numarul zilelor lucrate intr-un anumit interval de timp.

Sintaxa: =NETWORKDAYS(start_date;end_date;holidays)

Observatie: datele calendaristice ar trebui introduse utilizand functia DATE sau ca rezultate ale altor formule sau functii. De exemplu, se utilizeaza DATE(2008;5;23) pentru 23 mai 2008. Daca datele calendaristice sunt introduse ca text pot aparea probleme. Daca unul din argumente nu este o data calendaristica valida, NETWORKDAYS returneaza valoarea de eroare #VALUE!.

Start_date este data calendaristica de inceput.

End_date este data de sfarsit.

Holidays este un interval optional cu una sau mai multe date care se exclud din zilele lucratoare, cum ar fi sarbatorile nationale si religioase. Lista poate

Page 28: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

28

fi un domeniu de celule care contine datele a numerelor seriale care reprezinta datele. Argumentul Holidays nu permite introducerea unui numar, spre exemplu 3, pentru a exclude din numarul total de zile lucratoare, 3 zile.

Exemplu: Sa se determine numarul de zile lucratoare pentru proiectul Implementare ORACLE – echipament din lista:

Functia WORKDAY calculeaza o data calendaristica viitoare decalata cu un numar de zile lucratoare.

Exemplu: Sa se calculeze data scadenta pentru facturile din lista de mai jos:

Argumentele functiei sunt urmatoarele:

Start_date reprezinta data de debut (data emiterii facturii)

Days reprezinta numarul de zile lucratoare (poate fi un numar sau adresa celulei care contine nr. de zile)

Holidays este un interval optional cu una sau mai multe date care se exclud din zilele lucratoare, cum ar fi sarbatorile nationale si religioase. Lista poate fi un domeniu de celule care contine datele a numerelor seriale care reprezinta datele.

Page 29: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

29

Functia WEEKDAY determina a cata zi din saptamana este ziua specificata (data specificata)

Sintaxa: = WEEKDAY(serial_number;[return type]), unde return type poate lua una din valorile:

- Daca se specifica 1 – saptamana incepe de duminica si are 7 zile

- Daca se specifica 2 – saptamana incepe de luni si are 7 zile

Functia WEEKNUM determina a cata saptamana din an contine ziua mentionata.

Sintaxa: = WEEKNUM(serial_number;[return type]), unde return type poate lua una din valorile:

Functia EDATE intoarce numarul serial care reprezinta data returnata peste un numar indicat de luni, din aceeasi zi, inainte sau dupa o data specificata (start_date). Se utilizeaza EDATE pentru calculul datelor scadentelor care cad in aceeasi zi a lunii ca si data emiterii.

Sintaxa: =EDATE(start_date;months)

Start_date este o data care reprezinta data de inceput.

Page 30: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

30

Months este numarul de luni dinainte sau dupa start_date. O valoare pozitiva pentru argumentul months da o data viitoare; o valoare negativa da o data trecuta.

Functia EOMONTH - întoarce numarul serial pentru ultima zi din luna, care este cu un numar indicat de luni inainte sau dupa data de inceput (start_date). Se utilizeaza EOMONTH pentru a calcula datele scadentei atunci cand acestea cad in ultima zi a lunii.

Sintaxa: =EOMONTH(start_date;months)

Start_date este o data care reprezinta data de inceput.

Months este numarul de luni dinainte sau dupa start_date. O valoare pozitiva pentru argumentul months da o data viitoare; o valoare negativa da o data trecuta.

Daca argumentul months nu este un intreg, el este trunchiat.

OBS:

Daca argumentul start_date nu este o data valida, EOMONTH intoarce valoarea de eroare #NUM!.

Daca suma start_date plus months da o data invalida, EOMONTH intoarce valoarea de eroare #NUM!.

Exemplu EDATE si EOMONTH

Sa se determine data scandenta a facturilor din lista din aceeasi zi, de la sfarsitul lunii, cat si peste un numar indicat de zile.

Page 31: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

31

3.Functii de text

Concatenarea textelor:

Fie exemplul urmator:

s-a utilizat operatorul & de concatenare

s-a utilizat functia CONCATENATE ( a se tine cont de caracterul spatiu)

Functia UPPER – Transforma textul in majuscule.

Sintaxa: =UPPER(text)

Page 32: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

32

Functia PROPER - Transforma in majuscula prima litera dintr-un sir de text si orice alta litera din text care urmeaza dupa orice alt caracter in afara de o litera. Face conversia tuturor celorlalte litere la litere mici.

Sintaxa: =PROPER(text)

Functia LOWER – Transforma textul in minuscule

Sintaxa: =LOWER(text)

Exemplu : Sa se transforme in Majuscule, Minuscule si caractere de tip titlu numele si prenumele din lista de mai jos:

Functia LEN - determina lungimea unui sir.

Sintaxa: =LEN(text)

Exemplu:

=LEN(2830515180947)=13

Functia LEFT - returneaza primele caractere dintr-un sir text.

Sintaxa: =LEFT(text;num_chars)

Text - este sirul de text ce contine caracterele pe care le vom extrage.

Num_chars - indica numarul de caractere pe care le va extrage LEFT.

Num_chars trebuie sa fie mai mare sau egal cu zero.

Daca num_chars este mai mare decat lungimea textului, LEFT returneaza tot textul.

Daca num_chars este omis, se considera egal cu 1.

Functia RIGHT - returneaza ultimele caractere dintr-un sir text.

Sintaxa: =RIGHT(text;num_chars)

Text - este sirul de text ce contine caracterele pe care le vom extrage.

Num_chars - indica numarul de caractere pe care le va extrage.

Num_chars trebuie sa fie mai mare sau egal cu zero.

Daca num_chars este mai mare decat lungimea textului, RIGHT returneaza tot textul.

Daca num_chars este omis, se considera egal cu 1.

Page 33: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

33

Functia MID - intoarce un anumit numar de caractere dintr-un sir de text, incepand din pozitia specificata, pe baza numarului de caractere specificat.

Sintaxa: =MID(text;start_num;num_chars)

Text - este sirul de text care contine caracterele pe care le veti extrage.

Start_num - este pozitia din text a primului caracter pe care vreti sa-l extrageti. Pentru primul caracter din text, start_num este 1 si asa mai departe.

Num_chars - specifica numarul de caractere ce trebuie extrase din text de functia MID. Daca num_chars este negativ, MID intoarce valoarea de eroare #VALUE!.

Exemplu

Se considera baza de date de mai jos:

Sa se determine Grupa, Tara si Id Produs stiind ca: primele 3 caractere din Cod reprezinta Grupa, urmatoarele 4 caractere reprezinta Id Produs, iar ultimele 2 reprezinta Tara.

Functia VALUE - face conversia intr-un numar a unui sir de text care reprezinta un numar.

Sintaxa: =VALUE(text)

Text este un text cuprins intre ghilimele sau o referinta la o celula care contine textul caruia vreti sa-i faceti conversia. Argumentul text poate fi in orice format de numar constant, data sau ora, recunoscut de Microsoft Excel. Daca argumentul text nu este intr-unul din aceste formate, VALUE intoarce valoarea de eroare #VALUE!.

OBS:

In general, nu este nevoie sa utilizati functia VALUE intr-o formula, deoarece Microsoft Excel face automat conversia textului in numere cand este cazul. Aceasta functie este disponibila pentru compatibilitatea cu alte programe care lucreaza cu foi de calcul.

Exemplu: Sa se converteasca Id Produs in Numar.

Page 34: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

34

Functia LEN – determina lunimea unui sir de caractere (spre exemplu, lungimea denumirii produselor)

Sintaxa: = LEN(text)

Functia TRIM – elimina suplimentare aferente unui sir de caractere.

Sintaxa: = TRIM(text)

Exemplu: eliminati spatiile suplimentare din coloana Nume si prenume.

Functiile FIND si SEARCH

Functia Find returneaza pe ce pozitie se afla un caracter(sir de caractere) in interiorul unui sir de caractere (este o functie case-sensitive) Functia Search returneaza pe ce pozitie se afla un caracter(sir de caractere) in interiorul unui sir de caractere (nu este case-sensitive)

Sintaxa: FIND(Find_text;within_text;start_num)

Unde:

Find_text este textul cautat ( spre exemplu spatiu)

Within_text este textul in care se cauta

Start_num – pozitia de unde se incepe cautarea (spre exemplu 1 inseamna de la primul caracter)

Exemplul 1: Afisati in coloana Spatiu 1 pe ce pozitie se afla primul spatiu liber in coloana Nume si prenume.

Exemplul 2:

Page 35: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

35

Functia SEARCH-sintaxa:

=Search(Find_text;within_text;start_num)

Se observa ca argumentele sunt identice cu cele ale functiei Find.

Exemplu:

Afisati in coloana Spatiu 2 pe ce pozitie se afla al doilea spatiu liber in coloana Nume si prenume.

Functiile REPLACE si SUBSTITUTE

Functia Replace inlocuieste un sir de caractere cu altul pe baza unui nr de caractere specificat.

Old text- textul in care se face inlocuirea

Start_num- pozitia din care se va face inlocuirea

Num_chars- cate caractere vor fi inlocuite

New_text- textul cu care se face inlocuirea

Exemplu:

Page 36: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

36

Functia SUBSTITUTE inlocuieste un text cu altul intr-un sir de caractere (case sensitive).

Argumentele functiei Substitute sunt urmatoarele:

Text- textul in care se face inlocuirea

Old_text- textul care va fi inlocuit

New_text-textul cu care se face inlocuirea

Instance_num- se specifica la a cata aparitie sa se faca inlocuirea(in cazul in care aceasta caseta nu se completeaza, implicit se va face inlocuirea de la prima aparitie)

Exemplu:

Page 37: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

37

4.Functii de cautare si referinta: VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX

1.Functia VLOOKUP

Sintaxa : =VLOOKUP (valoare_cautata , tabel_cautare , nr_coloana_rezultat , tip_ cautare)

Functia VLOOKUP efectueaza cautarea pe verticala, cautand valoare_cautata in coloanele specificate in tabel_cautare.

Tot timpul valoare_cautata trebuie sa fie in prima coloana din tabel_cautare.

Prima coloana din tabel_cautare trebuie sa contina date UNICE, in caz contrar, functia VLOOKUP va returna prima valoare identificata.

Nr_coloana_rezultat este numarul coloanei in care se regaseste rezultatul, numarand coloanele din tabel_cautare.

Tip_cautare e valoare logica (TRUE (valori apropiate), FALSE (valoare exacta), respectiv 1 sau 0.

a) Exemplu Vlookup-cautare exacta

Page 38: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

38

Se considera baza de date de mai jos:

Extrageti din baza de date pentru fiecare cod Client: numele clientului, localitatea si valoarea incasarilor

In cazul in care apare eroarea #N/A afisati textul " Nu exista"(se utilizeaza functia Iferror)

Intrucat codurile pentru care dorim sa extragem informatiile se afla in tabela de cautare pe coloana, vom folosi functia Vlookup.

Argumentele functiei sunt urmatoarele:

Lookup_value- valoarea pe care o cautam (ex: codul D-154 care se in celula I44)

Table_array-tabela de cautare (baza de date in care cautam $A$42:$G$63); fixam zona care contine baza de date intrucat la copierea formulei dorim ca datele sa fie cautate mereu in aceeasi tabela(aceeasi zona)

Col_index_num-numarul coloanei din care dorim sa extragem datele (ex: clientul se afla in coloana 2)

Range_lookup- tipul de cautare 0 (dorim sa caute cu exactitate codul D-154)

Page 39: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

39

In cazul in care un anumit cod nu exista in tabela de cautare, functia Vlookup afiseaza eroarea #N/A.

Daca dorim ca in celulele unde apare eroarea #N/A sa se afiseze un text (ex: „Nu exista”), se utilizeaza functia IFERROR.

Functia IFERROR returneaza o valoare specificata de utilizator daca o formula are ca rezultat o eroare; in caz contrar, afiseaza rezultatul formulei.

Sintaxa functiei este urmatoarea:

Value-argumentul care este verificat pentru a gasi erorile (in cazul de fata, formula)

Value_if_error- mesajul afisat in cazul in care este identificata o eroare (ex: unde intalneste eroarea #N/A va afisa textul „Nu exista”)

b) VLOOKUP-cautare apropiata

Se considera tabelul cu targeturi RCA si CASCO si bonusul procentual aferent fiecarei tip de asigurari.

Pentru agentii de vanzari din cel de-al doilea tabel, stiind vanzarile acestora, trebuie sa se calculeze bonusul procentual in functie de targetul atins.

Page 40: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

40

Sintaxa functiei VLOOKUP este urmatoarea:

Lookup_value – valoarea pentru care se cauta procentul bonusului(B51=199.000)

Table_array – tabelul in care se va efectua cautarea este $A$40:$B$44 (fixat, pentru ca se doreste copierea formulei). Tabelul este selectat incepand cu cea de-a doua coloana din tabelul de baza pentru ca, obligatoriu pentru functia VLOOKUP este ca datele pentru care se efectueaza cautarea sa fie in prima coloana din matricea de cautare.

Col_index_num – numarul colonei din care se doreste identificarea rezultatului este 2 – bonusul RCA

Range_lookup - tipul de cautare va fi 1, deoarece vanzarile agentilor sunt apropiate de targeturile mentionate

Eroarea #N/A mentioneaza ca vanzarile agentilor nu se incadreaza in niciunul din targeturi.

Page 41: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

41

Eroarea #N/A este o eroare asociata functiilor de cautare si referinta si arata ca nu exista corespondent cautarii respective.

2.Functia HLOOKUP

Sintaxa : =HLOOKUP (valoare_cautata , tabel_cautare , nr_rand_rezultat , tip_ cautare)

Functia HLOOKUP efectueaza cautarea pe orizontala, cautand valoare_cautata in randurile specificate in tabel_cautare.

Tot timpul valoare_cautata trebuie sa fie in primul rand (antetul tabelului) din tabel_cautare.

Primul rand din tabel_cautare trebuie sa contina date UNICE, in caz contrar, functia HLOOKUP va returna prima valoare identificata.

Nr_rand_rezultat este numarul randului in care se regaseste rezultatul, numarand randurile din tabel_cautare.

Tip_cautare e valoare logica (TRUE (valori apropiate), FALSE (valoare exacta), respectiv 1 sau 0.

a) HLOOKUP-cautare exacta

Se considera tabelul de date de mai jos (incasarile trimestriale pentru fiecare clasa de asigurari)

Sa se determine valorile incasate in Trim 2, Trim 3 si Trim 4 pentru clasele de asigurari RCA si CASCO.

Indicatii:

Se va utiliza functia HLOOKUP, deoarece valorile pentru care se doreste identificarea (Trim 2, Trim 4 si Trim 3) sunt in primul rand din tabelul de cautare, deci cautarea va fi pe orizontala.

Tipul de cautare este exacta, deoarece valorile pentru care se efectueaza cautarea se regasesc in mod identic in tabelul de cautare.

Ex: dorim sa extragem din tabelul de cautare valoarea incasarilor RCA din trim 2.

Page 42: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

42

Lookup_value: valoarea cautata Trim 2 (se afla in celula B16)

Table_array: tabela de cautare(C2:C8 fixata)

Row_index_num: numarul randului: (RCA se afla pe randul 3)

Range_lookup: tipul de cautare 0 (fixa)

Rezultatul este afisat in tabelul de mai jos:

b) HLOOKUP-cautare apropiata

Se considera zona de date de mai jos (valoarea facturilor si discountul acordat in functie de valoare)

Dorim sa extragem din tabela de cautare discountul aferent fiecarei facturi.

Sintaxa functiei HLOOKUP va fi urmatoarea:

Page 43: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

43

Lookup_value: valoarea pe care o cautam (se afla in celula B52)

Table_array: tabela de cautare ($B$45:$G$46 fixata)

Row_index_num: numarul randului din care extragem datele (2)

Range_lookup: tipul de cautare apropiata (1)

Rezultatul este urmatorul:

3) FUNCTIA LOOKUP

Functia LOOKUP este o functie de cautare care are doua forme de sintaxa si anume forma vectoriala si forma matriciala.

a) Forma vectoriala a functiei este urmatoarea:

LOOKUP(lookup_value, lookup_vector, result_vector)

Lookup_value - valoarea pe care functia LOOKUP o cauta in vectorul lookup_vector

Lookup_vector este domeniul format dintr-un singur rand sau coloana si care contine valoarea cautat;

Result_vector este un domeniu format dintr-un singur rand sau coloana si care trebuie sa aiba aceeasi dimensiune cu vectorul lookup_vector. Acesta este vectorul care furnizeaza rezultatul cautarii.

Exemplu:

Se considera baza de date de mai jos:

Page 44: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

44

Sa se extraga pentru fiecare produs pretul de achizitie.

Vom folosi functia Lookup vector (cautam datele intr-o anumita coloana si extragem informatiile de pe acelasi rand dintr-o alta coloana) iar argumentele sunt urmatoarele:

Lookup_value: produsul pe care il cautam (aflat in celula G5)

Lookup_vector: coloana (zona) in care cautam B5:B33 (datele trebuie sa fie sortate crescator)

Result_vector: coloana (zona) din care extragem datele (D5:D33)

b) Forma matriciala Aceasta forma a functiei LOOKUP face cautarea automat in primul rand sau in prima coloana in functie de marimea domeniului matricial si extrage pentru acel element valoarea din ultimul rand sau ultima coloana. Forma matriciala a functiei este urmatoarea: LOOKUP(lookup_value,array)

Lookup_value este valoare pe care functia LOOKUP o cauta in matrice;

Page 45: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

45

Array este un domeniul matricial (format din mai multe coloane si randuri) in care se face cautarea. Observatii:

Daca functia LOOKUP nu gaseste valoarea lookup_value, foloseste cea mai mare valoare care este mai mica sau egala cu valoarea lookup_value.

Daca valoarea pe care o cautam este mai mica decat cea mai mica valoare din primul rand sau prima coloana (depinde de dimensiunea matricii) a domeniului de cautare, functia LOOKUP returneaza valoarea de eroare #N/A.

Exemplu:

Se considera zona de date A4:B9.

In tabelul 2 (coloana Bonus RCA) sa se extraga valoarea % a bonusului in functie de incasari.

Se utilizeaza functia LOOKUP forma matrice dearece dorim sa cautam valoarea in matricea (zona) A4:B9 si sa extragem rezultatul de pe acelasi rand din ultima coloana a matricei.

Page 46: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

46

4.FUNCTIA MATCH

Cauta un element specificat intr-o zona de celule, apoi returneaza pozitia relativa a acelui element din zona.

Sintaxa:

= MATCH(Lookup_value; Lookup_array; Match_Type), unde:

Lookup value este valoarea cautata

Lookup array este zona in care se realizeaza cautarea

Match Type are 3 valori:

1 sau omis – gaseste cea mai mare valoare mai mica decat valoarea cautata cu mentiunea ca zona Lookup array trebuie sortata ascendent

0 – gaseste prima valoare care este exact egala cu valoarea cautata

-1 – gaseste cea mai mica valoarea care este mai mare sau egala cu valoarea cautata cu mentiunea ca zona Lookup array trebuie sortata descendent

5.FUNCTIA INDEX

Returneaza valoarea unui element dintr-un tabel aflat la intersectia unui rand cu o coloana.

Sintaxa:

= INDEX(array; row_num; column_num), unde:

Array - este tabelul in care se efectueaza cautarea

Row_num – este numarul randului din care se extrage valoarea

Column_num – este numarul coloanei din care se extrage valoarea

Exemplu 1:

Se considera o baza de date din domeniul imobiliar ( Id anunt, Tip apartament, Localitate, Pret, an constructie, etc)

Sa se extraga pentru fiecare ID anunt detaliile solicitate: Status, Pret, Confort, Localiatate

Pentru a extrage statusul pentru ID-ul 104 cu ajutorul functiilor Index si Match, trebuie sa aflam mai intai pe ce rand (pozitia) se afla in zona de cautare ID-ul 104 si in ce coloana se afla Status.

Page 47: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

47

Pentru a obtine pozitia Id-ului 104 se utilizeaza functia Match astfel:

Lookup value- valoarea cautata (celula L30 contine Id-ul 104)

Lookup_array- zona in care se face cautarea (zona A7:A33 contine toate Id-urile)

Match_type- tipul de cautare este fix (dorim sa gaseasca pozitia exacta), deci introducem valoarea 0.

Pentru a obtine pozitia coloanei Status, se utilizeaza functia Match astfel:

Lookup value- valoarea cautata (celula M29 contine numele campului Status)

Lookup_array- zona in care se face cautarea (zona A6:I6 contine toate campurile)

Match_type- tipul de cautare este fix (dorim sa gaseasca pozitia exacta), deci introducem valoarea 0.

In continuare vom folosi functia Index pentru a extrage din zona de date A7:I33, valoarea aflata la intersectia randului 1 cu coloana 7.

.

Exemplu 2:

Se considera zona de date de mai jos:

Afisati in coloana B procentul comisionului pentru fiecare incasare (valoare). Procentul se va prelua din zona de date F1:G5.

Page 48: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

48

Vom folosi functiile Index si Match (cautare apropiata) astfel:

- Mai intai trebuie sa aflam pozitia valorii 4700 in zona F1:F5 (in zona de cautare datele trebuie sa fie sortate crescator).

Sintaxa functiei Match este urmatoarea:

Tipul de cautare 1 gaseste valoarea cea mai apropiata mai mica decat valoarea cautata (datele trebuie sa fie sortate crescator)

Pentru a obtine % comisionului pentru fiecare valoare vom folosi functiile Index si match astfel:

Procentul obtinut pentru valoarea de 4700 este de 5%.

Tipul de cautare -1 gaseste valoarea cea mai apropiata mai mare decat valoarea cautata (datele din tabela de cautare trebuie sa fie sortate descrescator)

Page 49: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

49

3.Partea III - Microsoft Excel – Tehnici de sinteza si analiza

1.Subtotaluri

O metoda rapida de rezumare a datelor dintr-o lista este introducerea unor totaluri partiale si a unui total general pentru anumite campuri ale inregistrarilor. Aceasta facilitate este utila pentru realizarea operatiilor de bilant.

Exemple de subtotaluri:

a) Subtotal la nivel de : 1 coloana, 1 functie, 1 coloana de calculat

Exemplu: Se considera o baza de date din domeniul IMOBILIARE. Se cere: insumarea preturilor la nivel de localitate

Pasi:

Sortarea inregistrarilor in functie de criteriul corespunzator (localitate)

Lansarea comenzii DATA – SUBTOTAL

In caseta de dialog atasata comenzii, indicam:

in lista At each change in se selecteaza numele campului pe care se bazeaza gruparea articolelor bazei de date (localitate)

din lista Use function se selecteaza numele functiei care va fi utilizata pentru determinarea rezultatului itermediar al articolelor care apartin aceluiasi grup.

Page 50: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

50

lista Add subtotal to contine un sir de butoane de optiune, care afiseaza numele campurilor din baza de date. Aceste butoane de optiune permit selectarea acelor coloane pentru care vor fi calculate diferite rezultate intermediare (Pret)

Obs: Este posibila selectarea mai multor coloane pentru efectuarea aceluiasi calcul.

Daca este selectata optiunea Replace current subtotals, atunci rezultatele intermediare calculate anterior vor fi inlocuite cu rezultate intermediare noi. Daca este selectata optiunea Page break between groups, atunci fiecare grup va fi dispus pe o pagina noua. Daca este activata optiunea Summary below data, atunci liniile corespunzatoare subtotalurilor si totalului general vor fi inserate sub datele grupate. In cazul in care aceasta optiune nu este selectata liniile inserate vor apare deasupra datelor grupate.

b) Subtotal la nivel de: 1 coloana, 1 functie, mai multe coloane de calculat:

Exemplu: Se considera o baza de date din domeniul IMOBILIARE. Se cere: insumarea preturilor si comisioanelor la nivel de Tip Apartament

sortarea inregistrarilor in functie de criteriul corespunzator (Tip Apartament)

lansarea comenzii DATA – SUBTOTAL

in caseta de dialog atasata comenzii, indicam:

at each change in: Tip Apartament (nr. Camere)

use function: SUM

add substotal to: Pret, Comision

Page 51: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

51

c) Subtotal la nivel de: 1 coloana, mai multe functii, 1 coloana de calculat:

Exemplu: Sa se determine la nivel de Status (vandut, liber) suma preturilor, media preturilor.

sortarea inregistrarilor in functie de criteriul corespunzator (Status)

lansarea comenzii DATA – SUBTOTAL

cele 2 calcule (suma si medie) nu pot fi executate dintr-un singur pas. Acest lucru presupune crearea unui prim subtotal la nivel de Status, utilizand doar functia SUM din Pret.

in caseta de dialog atasata comenzii, indicam:

at each change in: Status

use function: SUM

add substotal to: Pret

Se va obtine:

Page 52: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

52

Pentru a determina media preturilor, se vor executa aceiasi pasi pe subtotalul creat anterior:

data / Subtotal

at Each change in: Status

functie: AVERAGE

add subtotal to: Pret

Obs: Se va debifa optiunea Replace current subtotals pentru a nu inlocui subtotalul creat anterior cu cel nou.

Rezultatul va fi:

Page 53: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

53

d)Subtotal la nivel de: 1 coloana, mai multe functii, mai multe coloane de calculat

Exemplu : Sa se determine la nivel de Status media preturilor si numarul de apartamente.

sortarea inregistrarilor in functie de criteriul corespunzator (Status)

lansarea comenzii DATA – SUBTOTAL

cele 2 calcule (suma si numarul) nu pot fi executate dintr-un singur pas. Acest lucru presupune crearea unui prim subtotal la nivel de Status, utilizand doar functia SUM din Pret.

in caseta de dialog atasata comenzii, indicam:

at each change in: Status

use function: SUM

add substotal to: Pret

Se va obtine:

Page 54: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

54

Pentru a determina numarul de apartamente, se vor executa aceiasi pasi pe subtotalul creat anterior:

data / Subtotal

at Each change in: Status

functie: COUNT

add subtotal to: Id Pret

Rezultatul va fi:

e)Subtotal la nivel de: 2 coloane, 1 functie, 1 coloana de calculat

Exemplu: Sa se determine la nivel de Status si Confort suma preturilor.

Page 55: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

55

Daca se doreste utilizarea unui subtotal pe doua campuri, datele vor fi sortate de la inceput dupa cele doua coloane: Status si Confort

Crearea unui subtotal nu permite adaugarea celor doua campuri: Status si Confort de la inceput.

Se va crea primul subtotal, la nivel de Status:

Rezultatul va fi:

Se va crea cel de-al doilea subtotal, la nivel de Confort:

Page 56: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

56

Obs: Se debifeaza optiunea Replace current subtotals.

Subtotalurile permit ascunderea / afisarea informatiilor utilizand butoanele din partea stanga a unui subtotal:

2.Generarea tabelelor pivot (rapoarte)

Dintr-o baza de date se poate genera un tabel pivot (denumit si raport) prin care se pot sintetiza diferite informatii. Comanda PivotTable Report din fila Insert permite crearea unui raport complex in care datele sunt organizate conform criteriilor specificate de utilizator. Acest raport poate fi ulterior editat, iar datele pot fi analizate in diverse moduri.

OBS: Crearea unui tabel pivot impune ca baza de date utilizata sa fie bine structurata:

Page 57: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

57

datele din antet sa fie unice

datele din fiecare coloana sa fie de acelasi tip

nu se admit coloane fara antet

Exemplu:

Se considera o baza de date cu facturile inregistrate de clienti in mai multe Supermarketuri.

Crearea unui tabel pivot:

Exemplu: Sa se determine suma incasarilor la nivel de supermaket si judet.

se pozitioneaza cursorul in interiorul bazei de date

din meniul Insert / Pivot Table

Sursa tabelului pivot:

Select a table or range: baza de date curenta

Use an external data source: se utilizeaza o sursa externa: baza de date SQL, Oracle, un alt fisier Excel, o baza de date Access care necesita o conexiune ODBC.

Locatia tabelului pivot :

Page 58: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

58

New worksheet (foaie noua de calcul)

Existing worksheet (foaia de calcul curenta)

Structura tabelului pivot:

Un tabel pivot este structurat astfel:

Zona de campuri: Choose fields to add to report. Aici pot fi observate toate campurile (coloanele) existente in baza de date (sau range-ul selectat)

Zona Report Filter: presupune crearea unui filtru din cadrul caruia pot fi selectate informatiile ce vor fi vizualizate in tabelul pivot

Zona Row Labels: se vor selecta campurile care vor fi dispuse pe randuri

Zona Coumn Labels: se vor selecta campurile care vor fi dispuse ca si coloane in cadrul tabelului pivot

Zona Values: reprezinta zona de calcul

Pentru exemplul anterior datele pot fi dispuse astfel:

Zona de randuri: Supermarket

Zona de coloane: Judet

Page 59: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

59

Zona de calcul: suma vanzarilor

Exemplu : Sa se determine suma incasarilor la nivel de localitate si an.

Se urmeaza aceiasi pasi ca mai sus

Utilizarea mai multor functii in cadrul aceluiasi tabel pivot:

In cadrul aceluiasi tabel pivot pot fi utilizate mai multe functii.

Exemplu: Sa se determine suma incasarilor, media incasarilor, minimul si maximul incasarilor la nivel de supermarket si an.

Page 60: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

60

se creaza tabelul pivot utilizand functia implicita (SUM, daca datele asupra carora se efectueaza calculele sunt numerice, COUNT, daca datele sunt de tip text)

in zona Values se va « trage» din nou campul Vanzari si se va modifca functia aplicata asupra campului respectiv, click pe noul camp adaugat din zona Values / Value Field Settings :

De asemenea se poate stabili un nou nume pentru coloana ce contine media vanzarilor

Page 61: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

61

Exemplu: Sa se determine suma incasarilor la nivel de supermarket, precum si numarul de facturi.

Se urmeaza aceiasi pasi ca mai sus:

Actualizarea unui tabel pivot. Modificarea sursei unui tabel pivot:

a) Actualizare unui tabel pivot (Refresh Data)

Un tabel pivot este creat avand o anumita sursa de date. In cazul in care se executa modificari in cadrul sursei (se modifica anumite date numerice, se sterg numite randuri din baza de date), raportul poate fi actualizat.

Exemplu: Sa se modifice valoarea facturii Fact 106 din 21 in 2100. Sa se actualizeze ultimul pivot creat.

Pasi:

se modifica in cadrul sursei de date valoarea din 21 in 2100.

click dreapta pe tabelul pivot/ Refresh

Page 62: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

62

b) Actualizare pivot (Options)

Un tabel pivot poate fi actualizat in mod automat setand optiunea de actualizare automata:

click dreapta pe pivot / Pivot Table Options / Data se bifeaza optiunea Refresh data when opening the file – actualizare pivot la deschiderea fisierului:

c) Modificarea sursei tabelului pivot

Sursa unui tabel pivot poate fi modificata prin adaugarea unor noi informatii. In acest caz, tabelul pivot „citeste” datele din baza de date initiala, nu cea cu noile adaugiri.

Exemplu: Sa se adauge urmatoarea factura:

Modificarea sursei unui tabel pivot se realizeaza astfel:

click pe fila Options (activa doar daca este selectat tabelul pivot) / Change data source:

Page 63: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

63

se reselecteaza noua sursa /ok

Formatarea unui tabel pivot:

a)Formatarea datelor numerice din cadrul tabelelor pivot poate fi efectuata in mod uzual accesand comanda Format .

Exemplu: Sa se formateze media aritmetica din cadrul tabelului pivot de mai jos astfel incat datele sa fie afisate cu doua zecimale:

in zona Values, se acceseaza campul MEDIA

se acceseaza comanda pe value field settings

Se acceseaza butonul Number Format

Page 64: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

64

Tabelele pivot din Excel 2010 dispun de optiuni suplimentare fata de versiunile anterioare.

b)Fila Design este specifica formatarilor fie prin:

adaugare subtotaluri

adaugare totaluri

aspect pivot

inserare randuri goale

grilaj tabel

template-uri de formatare

Exemplu : Se considera tabelul pivot de mai jos :

Page 65: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

65

Optiunea Subtotals : permite adaugarea / eliminarea subtotalurilor din cadrul tabelului pivot :

Optiunea Grand Totals : permite adaugarea / eliminarea totalurilor din cadrul tabelului pivot :

Optiunea Report Layout permite structurarea tabelului pivot.

Show in Compact Form : dispune datele grupate in aceeasi coloana

Page 66: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

66

Show in Outline Form : dispune datele sub forma unei schite

Show in Tabular Form : dispune datele grupate precum si subtotalurile gruparilor

Repeat All Item Labels : repeta datele grupate astfel incat tabelul pivot sa devina o baza de date.

Optiunile PivotTable Styles : afiseaza template-uri de formatare astfel incat tabelul pivot sa fie usor de citit si interpretat :

Page 67: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

67

Optiunile PivotTable Style Options (Row Headers, Coumn Headers, Banded Rows, Banded Columns) permit afisarea grilajului in cadrul tabelului pivot.

Diferente valorice si procentuale:

% Of Grand Total

% Of Column Total

% Of Row Total

% Of

% Of Parent Row Total

% Of Parent Column Total

% Of Parent Total

Difference From

% Difference From

Page 68: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

68

Running Total In

% Running Total In

Rank Sallest to Largest

Rank Largets to Smallest

Exemplu (% Of Grand Total) : Sa se determine la nivel de Judet si an suma incasarilor, precum si cat % reprezinta vanzarile fiecarui judet la nivel de an din total

se va crea tabelul pivot de mai jos :

in zona Values se mai « trage » o data campul Vanzari

click pe Sum of Vanzari 2 / value field settings / Show values as / % Of Grand Total

Page 69: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

69

Exemplu (% Of Column Total): Sa se determine la nivel de Judet si An suma incasarilor, precum si cat % reprezinta vanzarile fiecarui judet la nivel de an.

Exemplu (% Of Row Total): Sa se determine la nivel de Judet si an suma incasarilor, precum si cat % reprezinta vanzarile din fiecare an la nivel de judet.

Exemplu (% Of): Sa se determine cat reprezinta procentual fiecare judet, comparatia facandu-se cu judetul BIHOR.

Page 70: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

70

Exemplu (% Of Parent Row Total): Sa se determine cat reprezinta procentual fiecare supermarket, la nivel de judet si an.

OBS: Diferenta dintre % of column si % of parent row este ca, in cazul in care se foloseste prima optiune totalul valorilor procentuale pe fiecare judet este diferit de 100%. In cazul celei de-a doua optiune totalul valorilor la nivel de judet va fi 100% iar totalurille valorilor judetelor vor insuma, de asemenea, 100%.

Exemplu (% Of Parent Column Total): Sa se determine cat reprezinta procentual fiecare supermarket, la nivel de judet si an.

Exemplu (% Of Parent Total): Sa se determine cat reprezinta procentual fiecare supermarket, la nivel de judet si an.

Page 71: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

71

Exemplu ( Difference From): Sa se determine cu cat au crescut / scazut vanzarile fata de anul anterior:

Exemplu ( % Difference From): Sa se determine cu cat au crescut / scazut procentual vanzarile fata de anul anterior:

Exemplu ( Running Total In): Sa se suma cumulativa la nivel de judet si an.

Page 72: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

72

Exemplu ( % Running Total In): Sa se suma cumulativa procentuala la nivel de judet si an.

Exemplu (Rank Smallest to Largest): Sa se distribuie rankul (pozitia) pe care se regaseste fiecare judet in functie de vanzarile realizate, in ordine crescatoare:

Gruparea datelor

Informatiile din cadrul unui tabel pivot pot fi grupate in functie de statisticile dorite.

Exemplu: Se considera baza de date cu facturi, datele fiind la nivel de luna. Se doreste determinarea incasarilor la nivel de Trimestru.

Pasi:

se va crea urmatorul tabel pivot: suma incasarilor la nivel de luna

Page 73: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

73

pentru gruparea informatiilor (ian, feb, mar – Trim 1; apr, mai, iun – Trim 2; iul, aug, sep – Trim 3; oct, nov, dec – Trim 4), datele trebuie sa fie adiacente. In cazul in care datele nu sunt adiacente, acestea trebuie mutate astfel incat sa devina adiacente.

Mutarea datele se va realiza astfel:

se selecteaza celula care trebuie mutata

click dreapta /Move

pentru gruparea datelor: se selecteaza celulele pentru care trebuie grupate (ian, feb, mar)

click dreapta pe selectie / Group

Rezultatul este:

Page 74: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

74

Se pot redenumi etichetele Group1, Group2, Group3, Group4 in Trim1, Trim2, Trim3, Trim4.

Gruparea informatiile presupune si efectuarea unor calcule la nivel de grupare. Spre exemplu, sa se determine suma incasarilor la nivel de trimestru.

se selecteaza o eticheta (Trim1 sau Trim2 sau Trim3 sau Trim4)

click dreapta pe selectie / Subtotal <<nume coloana>>

Campuri calculate

In cadrul unui tabel pivot pot fi efectuate calcule, in cazul in care sursa pivotului nu contine informatiile necesare.

Page 75: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

75

Exemplu: Sa se determine suma incasarilor la nivel de supermarket, tva-ul din incasari si suma incasarilor cu tva.

Baza de date nu contine tva-ul la nivel de factura, precum nici valoarea facturii cu tva.

Pasi:

se va crea urmatorul tabel pivot (suma incasarilor la nivel de supermarket)

In cadrul tabelului pivot de mai sus trebuie adaugate coloana cu TVA (24% din vanzari), precum si coloana Valoare cu TVA (Vanzari + TVA).

se va selecta tabelul pivot / fila Options / Calculations / Fields, Items & Sets / Calculated Field

se introduce numele campului calculat (coloanei): TVA, precum si formula de calcul: = 24%*Vanzari / Ok

Page 76: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

76

In mod analog se procedeaza cu inserarea campului Valoare cu TVA, formula de calcul fiind: = Vanzari+TVA

OBS: Bazele de date in Excel pot fi de forma:

unde este o singura coloana An si contine 2009 si 2010 sau de forma:

unde sunt doua coloane separate: An 2009 si An 2010.

Pentru a determina deviatia vanzarilor 2010 versus 2009 in cazul 1 se utilizeaza diferentele procentuale, iar in cazul 2 se utilizeaza campurile calculate.

Generarea unei diagrame pentru un tabel pivot

Pentru a genera o diagrama pentru un tabel pivot se parcurg urmatorii pasi:

se genereaza tabelul pivot pentru care se va crea diagrama

Exemplu: Sa se determine suma incasarilor la nivel de supermarket si an.

Page 77: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

77

pentru a genera graficul aferent tabelului pivot: se va selecta tabelul pivot / Fila Insert / se alege tipul de grafic:

Diagrama rezultata este conectata la tabelul pivot si orice modificare in grafic se propaga si asupra tabelului pivot, precum si orice modificare in tabelul pivot se propaga si asupra graficului.

Datele din cadrul graficului pot fi vizualizate prin selectarea / bifarea sau debifarea optiunilor din cadrul legendei, precum si din cadrul etichetelor.

Spre exemplu, daca se doreste afisarea informatiilor pentru 2010, atunci, din cadrul legendei se va bifa doar anul 2010.

Page 78: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

78

Fila Design

Din cadrul filei Design pot fi stabilite urmatoarele proprietati:

modificare tip grafic:

modificare sursa grafic pe randuri sau pe coloane

selectarea datelor graficului

template-uri care includ grafice cu titlu / denumire axe etc

template-uri de formatare

Fila Layout:

Din cadrul filei Layout pot fi stabilite urmatoarele proprietati:

Page 79: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

79

titlul graficului (Chart title)

titlul axelor (Axis titles)

legenda (Legend)

afisarea valorilor pe grafic (Data Labels)

afisarea sursei (Data Table)

EXERCITII TABELE PIVOT Crearea unui tabel pivot

1. Deschideti fisierul 3. Pivot.xlsx (foaia de calcul „Baza date”); a) creati un tabel pivot (raport) in care afisati incasarile totale pentru fiecare

agent (tabelul pivot se va crea intr-o foaie de calcul noua); redenumiti foaia de calcul cu numele: incasari agenti

b) redenumiti Sum of Valoare in Total incasari c) redenumiti Row Labels in Agenti d) redenumiti Grand Total in Total e) adaugati in tabelul pivot campul judet (in zona Column Labels); se observa

ca raportul evidentiaza totalul incasarilor fiecarui agent pe judete f) mutati Judetul din zona Column in zona Row Label (sub campul Agent);

pentru a structura tabelul pivot intr-un alt mod , se pot utiliza optiunile din Pivot Table Tools/Design/Report Layout (utilizati optiunea Show in Tabular Form)

2. Creati un tabel pivot in care afisati pentru fiecare client incasarile totale pe an(in zona Row Labels vom avea campurile Client si Data facturii iar in zona Values vom avea campul Valoare)

a) Grupati datele calendaristice pe ani b) Aplicati Show in Tabular Form c) Adaugati Judetul in zona Report Filter; dorim sa vedem incasarile clientilor

pe ani dintr-un anumit Judet/Judete; selectati din lista judetul Constanta d) Afisati incasarile clientilor pe ani din judetele Constanta, Brasov si Timis. e) Afisati incasarile pentru toate judetele f) Eliminati judetul din zona Report Filter

3. Creati un tabel pivot in care afisati pentru fiecare client totalul incasarilor/an, media incasarilor/an si maximul incasarilor/an

a) Redenumiti Sum of Valoare factura cu Total, Average of Valoare cu Media si Max of Valoare cu Maxim.

b) Afisati Total, Media si Max pe rand ca in imaginea de mai jos (se utilizeaza optiunea Move Values to Rows)

c) Afisati in tabelul pivot simbolul monetar €; setati valorile numerice cu 2 zecimale

d) Eliminati totalurile aflate pe coloane

Page 80: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

80

Anul

Agent Values 2011 2012 2013 Grand Total

Dobre Mihai Total 8524 25139 33663

Media 8524 3142,375 3740,333333

Maxim 8524 4876 8524

Petre Oana Total 91075 7899 98974

Media 6071,666667 2633 5498,555556

Maxim 45127 4000 45127

Popescu Marian Total 40827 44263 85090

Media 5103,375 6323,285714 5672,666667

Maxim 12587 13256 13256

Sarbu Bogdan Total 64533 77991 45048 187572

Media 7170,333333 5999,307692 5631 6252,4

Maxim 45127 45127 7456 45127

Stan Iulia Total 26256 26256

Media 2625,6 2625,6

Maxim 4875 4875

4. Creati un tabel pivot in care afisati pentru fiecare agent incasarile totale pe judet si numarul de clienti in fiecare judet

Agent Judet Total Nr. Clienti

Dobre Mihai BIHOR 9778 2 BRASOV 11513 3 CONSTANTA 4876 1 TIMIS 7496 3

Dobre Mihai Total 33663 9

Petre Oana BIHOR 15692 5 BRASOV 4000 1 BUCURESTI 10218 3 CONSTANTA 14809 5 DOLJ 45127 1 TIMIS 9128 3

Petre Oana Total 98974 18

Popescu Marian BRASOV 44379 6 BUCURESTI 7827 2 CONSTANTA 10516 3 TIMIS 22368 4

Popescu Marian Total 85090 15

5. Creati un tabel pivot in care afisati la nivel de an si trimestru, suma incasarilor si total TVA. Adaugati in zona Report Filter campul Judet;

a) Afisati datele de mai sus pentru judetul Timis b) Afisati datele in modul Tabular Form(utilizati optiunea Show in Tabular

Form) c) Inserati un SLICER pentru Judet d) Selectati in slicer judetele Brasov si Constanta e) Redenumiti in tabelul pivot Qtr1,Qtr2,Qtr3 si Qtr4 cu Trim1, Trim2, Trim3,

Trim4 f) Redenumiti in tabelul pivot Sum of Valoare factura si Sum of TVA cu

Valoare Totala si Total TVA

Page 81: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

81

Actualizarea unui tabel pivot

1 Deschideti fisierul 4.Pivot-actualizare.xlsx. a. Creati un tabel pivot in care afisati pentru fiecare furnizor, produsul si

cantiatea.(in zona Row Labels afisati campurile Furnizor si Produs; in zona Values afisati campul Cantitate)

b. Inlocuiti Sum of Cantitate cu Cantitate (intrucat noua denumire coincide cu numele campului din baza de date se mai adauga un spatiu liber)

c. Efectuati 2 modificari in baza de date: i. La Nr. Comanda 1 stabiliti cantitatea de 10000 ii. Inlocuiti Id Produs LVB-2213 cu LXC-2200

d. Actualizati tabelul pivot; urmariti modificarile in tabelul pivot (se utilizeaza optiunea Refresh din Pivot Table Tools/Options)

In cazul in care se fac modificari in continutul celulelor (nr. de coloane si randuri din sursa nu se schimba), actualizarea se face cu butonul Refresh sau click dreapta in interiorul tabelului pivot/Refresh

2 Sortati crescator campul Numar Comanda in baza de date.

a. Stergeti comenzile 10 si 11(se sterg randurile cu cele 2 comenzi)

b. Actualizati tabelul pivot (Se utilizeaza optiunea Refresh)

In cazul in care se sterg randuri sau coloane din sursa, actualizarea se face cu Refresh.

3 Adaugati in sursa urmatoarele 2 randuri:

Nr. Comanda

Id Produs Cantitate Cantitate Data achizitiei

23 BDU-2071 4000 Atlas 6/6/2016

24 RVO-2702 5000 Atlas 6/6/2016

a. Actualizati tabelul pivot (se utilizeaza Pivot Table Tools/Options/Change Data Source). Se observa in tabelul pivot ca au aparut noile id-uri de produs. De asemenea.

In cazul in care se adauga randuri sau coloane in sursa de date, actualizarea tabelului pivot se realizeaza cu comanda: Pivot Table Tools/Options/Change Data Source/ se reface selectia/ok).

4 Stergeti datele din primele 3 randuri (in sursa). Actualizati tabelul pivot (se utilizeaza optiunea Referesh)

In cazul in care se sterg datele din unul sau mai multe randuri/coloane, actualizarea se face cu optiunea Refresh. Dezavantaj!!!!!: In tabelul pivot apar Blank-uri

5 Deschideti foaia de calcul Sursa 2. Transformati zona de date in tabel (se utilizeaza optiunea Insert/Table). In campul Furnizor efectuati o sortare crescatoare.

a. Creati un tabel pivot in care afisati la nivel de Furnizor, cantitatea totala a facturilor si cantitatea maxima.

b. Stergeti din Sursa 2 toate randurile cu Atlas actualizati tabelul pivot (Refresh)

Page 82: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

82

c. In coloana Furnizor stergeti Furnizorul Ikea (se sterg datele din celule); actualizati tabelul pivot (Refresh)

d. Adaugati 2 randuri in sursa (introduceti un furnizor nou: New Desk)

Numar Comanda

Id Produs Cantitate Furnizor Data Achizitiei

23 FGL-3041 3000 New Desk 6/6/2016

24 VGX-4021 2000 New Desk 6/6/2016

Actualizati tabelul pivot(se utilizeaza optiunea Refresh).

In cazul in care sursa de date este un tabel de date (s-a utilizat optiunea Insert/Table), actualizarea tabelului pivot se realizeaza cu comanda Refresh indiferent de modificarile care se fac in sursa.

Observatie!!! Un tabel pivot poate fi actualizat automat setand optiunea de actualizare automata la deschiderea fisiserului.

-se executa click dreapta in tabelul pivot/ Pivot Table Options/Data/Refresh data when opening the file.

Diferente procentuale si valorice

Deschideti fisierul 5. Pivot-diferente procentuale si valorice.xlsx

% Of Grand Total

1. Sa se determine la nivel de Articol si An suma incasarilor precum si cat reprezinta procentual vanzarile fiecarui Articol la nivel de an din totalul general.

Anul

Articol Values 2006 2007 Grand Total

Birou Total Incasari

35000 35000

% din Total 0.00% 64.79% 64.79%

Dosar Total Incasari 829.12 7749.94 8579.06

% din Total 1.53% 14.35% 15.88%

Set Stilou Total Incasari 348.74 6808.64 7157.38

% din Total 0.65% 12.60% 13.25%

Stilou Total Incasari 999.5 2288.01 3287.51

% din Total 1.85% 4.24% 6.09%

Total Total Incasari

2177.36 51846.59 54023.95

Total % din Total

4.03% 95.97% 100.00%

Page 83: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

83

% Of Column Total

2. Sa determine la nivel de Articol si An suma incasarilor precum si cat reprezinta procentual incasarile din fiecare articol la nivel de an din totalul incasarilor/an (din totalul pe coloana)

Anul

Articol Values 2006 2007 Grand Total

Birou Total Incasari

35000 35000

% din Total 0.00% 67.51% 64.79%

Dosar Total Incasari 829.12 7749.94 8579.06

% din Total 38.08% 14.95% 15.88%

Set Stilou Total Incasari 348.74 6808.64 7157.38

% din Total 16.02% 13.13% 13.25%

Stilou Total Incasari 999.5 2288.01 3287.51

% din Total 45.90% 4.41% 6.09%

Total Total Incasari

2177.36 51846.59 54023.95

Total % din Total

100.00% 100.00% 100.00%

% Of Row total

3. Sa se determine la nivel de articol si an suma incasarilor precum si cat reprezinta procentual vanzarile din fiecare an la nivel articol

Anul

Articol Values 2006 2007 Grand Total

Birou Total Incasari

35000 35000

% din Total 0.00% 100.00% 100.00%

Dosar Total Incasari 829.12 7749.94 8579.06

% din Total 9.66% 90.34% 100.00%

Set Stilou Total Incasari 348.74 6808.64 7157.38

% din Total 4.87% 95.13% 100.00%

Stilou Total Incasari 999.5 2288.01 3287.51

% din Total 30.40% 69.60% 100.00%

Total Total Incasari

2177.36 51846.59 54023.95

Total % din Total

4.03% 95.97% 100.00%

Page 84: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

84

% OF

4. Sa se determine cat reprezinta % incasarilor fiecarui articol, comparatia facandu-se cu articolul Birou.

Anul

Articol Values 2006 2007 Grand Total

Birou Total Incasari

35000 35000

% din Total

100.00% 100.00%

Dosar Total Incasari 829.12 7749.94 8579.06

% din Total

22.14% 24.51%

Set Stilou Total Incasari 348.74 6808.64 7157.38

% din Total

19.45% 20.45%

Stilou Total Incasari 999.5 2288.01 3287.51

% din Total

6.54% 9.39%

Total Total Incasari

2177.36 51846.59 54023.95

Total % din Total

Difference From

5. Sa se determine cu cat au crescut/scazut vanzarile fata de anul anterior(2006) pentru fiecare articol.

Anul Values

2006

2007

Total Total Incasari Total Diff

Articol Total Incasari Diff

Total Incasari Diff

Birou

35000 35000 35000

Dosar 829.12

7749.94 6920.82 8579.06

Set Stilou 348.74

6808.64 6459.9 7157.38

Stilou 999.5

2288.01 1288.51 3287.51 Grand

Total 2177.36

51846.59 49669.23 54023.95

Page 85: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

85

% Difference From

6. Sa se determine cu cat au crescut/scazut procentual incasarile fata de anul anterior pentru fiecare articol.

Anul Values

2006

2007

Total Total Incasari

Total Diff

Articol Total Incasari Diff

Total Incasari Diff

Birou

35000

35000 Dosar 829.12

7749.94 834.72% 8579.06

Set Stilou 348.74

6808.64 1852.35% 7157.38 Stilou 999.5

2288.01 128.92% 3287.51

Grand Total 2177.36

51846.59 2281.17% 54023.95

Gruparea datelor

Deschideti fisierul 6. Pivot-gruparea datelor.xls

1. Creati un tabel pivot in care afisati produsele grupate astfel:

Row Labels Sum of Valoare

Birou Accesorii 523180

Camera Foto 732670

Camera Video 236300

Papetarie Cartus 495600

Hartie 406100

Rame Foto 443520

Toner 851040

Grand Total 3688410

2. Creati un tabel pivot in care afisati incasarile totale la nivel de client.

Creati 3 grupuri astfel:

Group 1 (Alexe Mihaela, Bogdan Florea, Constantin Paul, Cristea Marius, Cristina Mihaela Dobre, Georgian Tiberiu, Georgiana Stan); redenumiti Group 1 cu Gold

Group 2 (Ioana Sima, Ionescu Mihai, Ionut Cristea, Laurentiu Damian, Neata Luiza, Popa

Adriana, Popescu Mihaela); redenumiti Group 2 cu Medium

Group 3 (Popescu Mihai, Popescu Mirela, Stancu Adrian, Steliana Cozma, Toader Flavius, Valentin Apostol, Valentina Andrei); redenumiti Group 3 cuSmall

Page 86: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

86

Afisati suma incasarilor la nivel de grup .

Row Labels Sum of Valoare

Gold 1251230

Medium 1230560

Small 1206620

Grand Total 3688410

3. Creati un tabel pivot in care afisati la nivel de produs data achizitiei si valoarea totala. Grupati datele calendaristice pe ani.

Vaoarea Anul achiitiei

Produs 2011 2012 2013 2014 Grand Total

Accesorii 73260 254930 95460 99530 523180

Camera Foto 96350 385400 130380 120540 732670

Camera Video 33830 117980 41820 42670 236300

Cartus 60550 252350 98000 84700 495600

Hartie 59830 192820 74710 78740 406100

Rame Foto 59520 224880 73680 85440 443520

Toner 118080 409920 156960 166080 851040

Grand Total 501420 1838280 671010 677700 3688410

Campuri calculate

1. Deschideti fisierul 7. Pivot-campuri calculate.xlsx (foaia de calcul Sursa 1).

Creati un tabel pivot in care afisati la nivel de Produs :valoarea totala a vanzarilor si Vanzari+TVA.

Produs Sum of Vanzari

Sum of Vanzari + TVA

Arrowroot 3168.72 3802.464 Chocolate Chip 3094.2 3713.04 Oatmeal Raisin 2191 2629.2

Potato Chips 1420.65 1704.78

Pretzels 81.09 97.308

Saltines 1400.08 1680.096

Grand Total 11355.74 13626.888

Page 87: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

87

2. Creati un tabel pivot in care afisati la nivel de Categorie Produs: valoarea totala a vanzarilor si un discount de 5% din valoare totala a vanzarilor

Produs Vanzari Discount

Cookies 8453.92 422.696

Crackers 1400.08 70.004

Snacks 1501.74 75.087

Grand Total 11355.74 567.787

3. Deschideti foaia de calcul Sursa 2. Creati un tabel pivot in care afisati la nivel de punct de lucru incasarile totale si profitul (Profit=Incasari-Cheltuieli)

Punct de lucru Incasari totale Profit

Bacau 100927 92027

Bucuresti 36074 27524

Oradea 39378 30578

Pitesti 38962 29662

Timisoara 39225 30125

Grand Total 254566 209916

Generarea unei diagrame pentru un tabel pivot

1. Deschideti fisierul 8.Pivot-chart.xlsx. Creati un tabel pivot in care afisati cantitatea totala la nivel furnizor si luna. Generati graficul aferent tabelului pivot (Grafic de tip Column)

Cantitate Luna

May Jun Grand Total

Furnizorul Atlas 26422 3159 29581

Bic 19618 8570 28188

CarniProd 14976 3326 18302

ColorAs 13959 3309 17268

Ikea 4326 9599 13925

Grand Total 79301 27963 107264

2. Creati un tabel pivot in care afisati la nivel de furnizor cantitatea totala.

Generati graficul aferent tabelului pivot (Grafic de tip Pie).

Furnizorul Cantitate

Atlas 29581

Bic 28188

CarniProd 18302

ColorAs 17268

Ikea 13925

Grand Total 107264

Page 88: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

88

3. Creati un tabel pivot in care afisati la nivel de Id Produs cantitatea totala pe fiecare luna. Generati graficul aferent tabelului pivot (Grafic de tip Line).

Cantitate Luna

May Jun Grand Total

Id Produs BDU-2071 12094

12094

IDI-4744 4326

4326

LNR-9553 14328 3326 17654

LVB-2213 10635 8570 19205

RVO-7202 10652 3159 13811

XRQ-2356 3307 3309 6616

XVJ-6482 14976

14976

XVY-8409 8983 9599 18582

Grand Total 79301 27963 107264

Crearea unui grafic dinamic

1. Deschideti foaia de calcul Incasari. Convertiti in tabel (Insert /Table) datele din zona A1:C7.

2. Construiti un grafic de tip Coloana in care afisati punctele de lucru si incasarile din 2012 si 2013.

3. Introduceti titlul: Incasari 2012-2013.

4. Adaugati in tabelul de date punctul de lucru Oradea si valorile incasarilor:

Oradea 9000 10000

5. Urmariti modificarile aparute.

Formatarea unui tabel pivot (fila design)

1. Deschideti fisierul 9. Pivot-formatare.xls ( foaia de calcul Formatare)

2. Afisati subtotalul pentru fiecare grup in partea de sus

3. Afisati totalul general (Grand Total) doar pe coloana

4. Inserati cate un rand sub fiecare grup

5. Eliminati culoarea de fundal din antetul tabelului pivot (Column Headings)

6. Aplicati un template de formatare tabelului pivot .

Exercitii pivoti (Tehnici de analiza si sinteza III/ Exercitii 3)

1. Deschideti fisierul 2. Pivoti.xlsx (foaia de calcul Sursa pivot)

Page 89: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

89

Creati un tabel pivot in care afisati la nivel de agent incasarile totale si numarul de facturi.

a. Efectuati o sortare crescatoare in coloana Agent

b. Redenumiti foaia de calcul cu numele Raport 1

Agent Total valoare Nr. facturi

Alexe Mihaela 9410 4

Bogdan Florea 2790 1

Constantin Paul 620 1

Cristina Mihaela Dobre 3330 1

Ioana Sima 8390 4

Ionut Cristea 820 1

Laurentiu Damian 5020 3

Neata Luiza 2550 2

Popa Adriana 4530 3

Popescu Mihaela 2800 1

Popescu Mihai 3690 1

Popescu Mirela 1480 1

Stancu Adrian 4320 1

Steliana Cozma 960 1

Toader Flavius 3220 2

Valentin Apostol 2400 1

Valentina Andrei 2560 3

Grand Total 58890 31

2. Creati un tabel pivot in care afisati la nivel de produs media incasarilor, maximul incasarilor, minimul incasarilor si numarul de facturi emise.

a. Adaugati in zona Row Labels campul Agent

b. Afisati datele in Compact Form

c. Afisati subtotalurile in partea de sus

d. Restrangeti toate produsele (Collapse entire Field)

e. Expandati produsul Camera video

f. Redenumiti foaia de calcul cu numele Raport 2

Produs Media Maxim Minim Nr. facturi

Accesorii 1942,5 3330 1110 4

Camera Foto 1947,5 3690 820 4

Camera Video 977,5 1360 340 4

Alexe Mihaela 1190 1190 1190 1

Popa Adriana 1020 1020 1020 1

Toader Flavius 1360 1360 1360 1

Valentina Andrei 340 340 340 1

Cartus 2450 3500 1400 5

Hartie 2170 2790 620 5

Rame Foto 840 1440 240 4

Toner 2592 4320 480 5

Grand Total 1899,677419 4320 240 31

3. Creati un tabel pivot in care afisati la nivel de Agent totalul incasarilor .

a. Afisati pentru fiecare agent ponderea incasarilor din incasarile tuturor agentilor(din totalul general); redenumiti coloana cu numele „Pondere”

Page 90: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

90

b. Calculati pentru fiecare agent un comision de 2,5% din totalul incasarilor agentului

c. In coloanele Pondere si Comision afisati valorile cu 2 zecimale.

d. Redenumiti foaia de calcul cu numele Raport 3

Agent Total incasari Pondere Comision

Alexe Mihaela 9410 15,98% 235,25

Bogdan Florea 2790 4,74% 69,75

Constantin Paul 620 1,05% 15,50

Cristina Mihaela Dobre 3330 5,65% 83,25

Ioana Sima 8390 14,25% 209,75

Ionut Cristea 820 1,39% 20,50

Laurentiu Damian 5020 8,52% 125,50

Neata Luiza 2550 4,33% 63,75

Popa Adriana 4530 7,69% 113,25

Popescu Mihaela 2800 4,75% 70,00

Popescu Mihai 3690 6,27% 92,25

Popescu Mirela 1480 2,51% 37,00

Stancu Adrian 4320 7,34% 108,00

Steliana Cozma 960 1,63% 24,00

Toader Flavius 3220 5,47% 80,50

Valentin Apostol 2400 4,08% 60,00

Valentina Andrei 2560 4,35% 64,00

Grand Total 58890 100,00% 1472,25

4. Creati un tabel pivot in care afisati la nivel de produs cantitatea totala si incasarile totale.

a. Afisati pentru fiecare produs % cu care au crescut/scazut incasarile fata de produsul Camera foto

b. Redenumiti foaia de calcul cu numele Raport 4

Produs Total cantitate Total valoare % fata de Camera Foto

Accesorii 23 7770 99,74%

Camera Foto 40 7790 100,00%

Camera Video 15 3910 50,19%

Cartus 32 12250 157,25%

Hartie 21 10850 139,28%

Rame Foto 24 3360 43,13%

Toner 31 12960 166,37%

Grand Total 186 58890

5. Creati un tabel pivot in care afisati la nivel de agent (Row labels) si luna(Column labels) incasarile totale.

a. Creati un grup cu numele Bucuresti pentru primii 9 agenti

b. Creati un grup cu numele Iasi pentru restul agentilor

c. Redenumiti coloana in care se afla cele 2 grupuri cu numele Punct de lucru

d. In celule goale din tabelul pivot afisati valoarea zero

e. Afisati tabelul pivot astfel incat sa devina o baza de date (ca in imaginea de mai jos)

f. Aplicati un template de formatare

g. Redenumiti foaia de calcul cu numele Raport 5

Page 91: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

91

Incasari Luna

Punct de lucru Agent mar apr mai Grand Total

Bucuresti Alexe Mihaela 4320 3650 1440 9410

Bucuresti Bogdan Florea 0 0 2790 2790

Bucuresti Constantin Paul 0 0 620 620

Bucuresti Cristina Mihaela Dobre 0 3330 0 3330

Bucuresti Ioana Sima 3500 2800 2090 8390

Bucuresti Ionut Cristea 0 820 0 820

Bucuresti Laurentiu Damian 0 5020 0 5020

Bucuresti Neata Luiza 1110 1440 0 2550

Bucuresti Popa Adriana 2790 1740 0 4530

Bucuresti Total 11720 18800 6940 37460

Iasi Popescu Mihaela 0 0 2800 2800

Iasi Popescu Mihai 0 0 3690 3690

Iasi Popescu Mirela 0 1480 0 1480

Iasi Stancu Adrian 0 4320 0 4320

Iasi Steliana Cozma 0 960 0 960

Iasi Toader Flavius 1360 1860 0 3220

Iasi Valentin Apostol 0 0 2400 2400

Iasi Valentina Andrei 0 820 1740 2560

Iasi Total 1360 9440 10630 21430

Grand Total 13080 28240 17570 58890

6. Creati un tabel pivot in care afisati pentru fiecare luna totalul incasarilor si % cu care au crescut/scazut incasarile fata de luna anterioara (afisati lunile in ordinea : mar, apr, mai). Redenumiti foia de calcul cu numele Raport 6

Luna Total incasari % fata de luna anterioara

mar 13080

apr 28240 115,90%

mai 17570 -37,78%

Grand Total 58890

7. Creati un tabel pivot in care afisati la nivel de produs incasarile totale si pozitia pe care se gaseste fiecare produs in functie de incasari (rank descrescator).

a. Sortati crescator datele in coloana Rank incasari

b. Redenumiti foaia de calcul cu numele Raport 7

Produs Sum of Valoare Rank incasari

Toner 12960 1

Cartus 12250 2

Hartie 10850 3

Camera Foto 7790 4

Accesorii 7770 5

Camera Video 3910 6

Rame Foto 3360 7

Grand Total 58890

8. Creati un tabel pivot in care afisati la nivel de produs totalul incasarilor si media incasarilor. Afisati media incasarilor cu 2 zecimale

a. Creati un grafic de tip Column aferent tabelului pivot

b. Introduceti titlul graficului: Incasari 2014

Page 92: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

92

c. Afisati valorile intr-un tabel de date

d. Afisati pe grafic incasarile si media incasarilor doar pentru produsele Accesorii, Camera Video si Rame Foto

e. Eliminati filtrul

f. Adaugati in baza de date (foaia de calcul Sursa pivot) urmatoarele date:

Numar factura Denumire produs Agent Cantitate Valoare Data facturii

32 Laptop Bratu Mihai 10 6000 20.05.2014

g. Actualizati tabelul pivot (urmariti modificarile aparute in tabelul pivot si in grafic)

h. Redenumiti foaia de calcul cu numele Raport 8

Row Labels Total incasari

Media incasarilor

Accesorii 7770 1942,50

Camera Foto 7790 1947,50 Camera Video 3910 977,50

Cartus 12250 2450,00

Hartie 10850 2170,00

Rame Foto 3360 840,00

Toner 12960 2592,00

Laptop 6000 6000,00

Grand Total 64890 2027,81

Page 93: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

93

4.Partea IV - Microsoft Excel

1. Formatarea conditionala

Formatarea conditional ne permite evidentierea celulelor care indeplinesc anumite conditii.

a) Se selecteaza zona de date in care dorim sa aplicam formatarea conditional

b) Din meniul Home/gruparea Styles se apasa butonul Conditional Formatting

c) se selecteaza optiunea dorita

Exemplu 1

Sa se aplice in coloana Valoare factura urmatoarea formatare conditionala:

-daca valoarea facturii este mai mare de 4000 celulele vor avea culoare de fundal

galbena

-daca valoarea facturii este mai mica de 1000, celulele vor avea fundal rosu si font alb

1. Se selecteaza coloana Valoare factura

2. Din meniul Home/gruparea styles se acceseaza butonul Conditional Formatting

3. Se selecteaza optiunea Highlight Cells Rules/Greater Than (mai mare de)/ se

introduce valoarea de 4000

4. Se aplica formatarea dorita/OK

Page 94: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

94

Pentru a evidentia celulele care contin valori mai mici de 1000, din lista Highlight Cells

Rules se selectea optiunea Less Than(mai mic decat), se introduce valoarea de 1000 si

apoi se aplica formatarea dorita.

Exemplu 2

In coloana Data facturii aplicati o formatare conditionala astfel: pentru facturile cu data

cuprinsa intre 15.10.2012 si 20.03.2013, culoarea de fundal a celulelor sa fie albastra

1. Se selecteaza coloana Data facturii

2. Din meniul Home/gruparea styles se acceseaza butonul Conditional Formatting

3. Se selecteaza optiunea Highlight Cells Rules/Between

4. Se aplica formatarea dorita/OK

Exemplu 3

.In coloana Incasari 2012 aplicati o formatare conditionala astfel: primele 5 incasari (cele

mai mari) sa aiba fundal galben

1. Se selecteaza coloana Incasari 2012

2. Din meniul Home/gruparea styles se acceseaza butonul Conditional Formatting

3. Se selecteaza optiunea Top/Bottom Rules/Top 10 Items/se introduce valoarea 5

Page 95: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

95

5. Se aplica formatarea dorita/OK

Exemplu 4 (formatare conditionala cu formula)

Aplicati in coloana incasari o culoare de findal galbena daca data incasarii este

15.03.2013.

1. Se selecteaza coloana Incasari

2. Din meniul Home/gruparea styles se acceseaza butonul Conditional Formatting

/New Rule

3. Se selecteaza optiunea Use a formula to determine which cells to format

4. Se introduce formula: =B2=Date(2013;03;15)

5. Se aplica formatarea dorita/ok

Exemplu 5:

Aplicati urmatoarea formatare conditionala in coloana Data scadenta: daca data

scadenta este este mai mica decat data curenta, celulele sa aiba fontul rosu si ingrosat

1. Se selecteaza coloana Data scadenta

2. Se selecteaza optiunea Use a formula to determine which cells to format

3. Se introduce formula=B2<today()

4. Se aplica formatarea dorita/ok

Page 96: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

96

Exemplu 6

Aplicati o culoare de fundal galbena randurilor(inregistrarilor) care contin facturi

neachitate.

1. Se selecteaza toata baza de date

2. Se selecteaza optiunea Use a formula to determine which cells to format

3. Se introduce formula=$D2=”nu”

4. Se aplica formatarea dorita/OK

Exemplu 7

Aplicati o formatare conditionala randurilor care contin produsul procesor la pretul de

200 (culoare de fundal verde)

1. Se selecteaza toata baza de date

2. Se selecteaza optiunea Use a formula to determine which cells to format

3. Se introduce formula =AND($B2="procesor";$D2=200)

4. Se aplica formatarea dorita/ok

Page 97: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

97

2.Validarea si auditul datelor

Prin validarea datelor intelegem procedurile activate astfel incat in anumite celule sa nu se poata introduce decat date care indeplinesc conditii impuse. Procesul poate asigura atat afisarea unui mesaj informativ sau de avertizare la activarea celulei, cat si afisarea unui mesaj de eroare daca s-a completat o valoare neacceptabila.

Prin auditul foii de calcul intelegem procedurile care permit localizarea valorilor de tip eroare.

Validarea datelor

Restrictiile care pot fi impuse pentru continutul unei celule se refera la :

tipul datei (intreg, zecimal, text etc.);

limite intre care pot fi valorile numerice sau de tip data calendaristica;

lungimea unui text;

Dupa introducerea datelor si efectuarea calculelor, se poate efectua auditul foii pentru a localiza si corecta datele eronate.

Exemplu :Se considera pontajul pe o saptamana pentru angajatii unei firme. Tabelul contine urmatoarele informatii :

In acest tabel ziua trebuie sa fie intre 04/07/2011 si 08/07/2011, marca sa contina 4 caractere, orele lucrate sa fie intre 4 si 8, orele suplimentare intre 0 si 2. Datele care nu corespund acestor criterii dorim sa fie evidentiate.

Pentru a realiza acest obiectiv vom impune urmatoarele restrictii:

a) Restrictii pentru datele numerice

Acestea pot fi impuse pentru valori numerice sau de tip calendaristic.

se selecteaza celule pentru care se definesc restrictiile. De ex. selectam celulele cu datele calendaristice.

data/ Data Validation si se alege eticheta Settings.

Page 98: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

98

in lista Allow se selecteazã tipul de data numerica impus: Whole number – numar intreg, Decimal – numar zecimal, Date – data calendaristicã, Time – ora.

in lista Data se alege operatorul utilizat la validare (numere intre anumite limite, mai mici decat, mai mari decat etc.). In functie de operatorul selectat se completeazã zonele care urmeaza (in figura Start Date si End Date deoarece s-a ales operatorul between). Aceste zone pot sa contina valori, referinte de celule care contin valorile, formule.

Daca se permite ca in celulele selectate sa poata aparea blancuri, atunci se va bifa zona Ignore blank. Daca se doreste ca restrictiile impuse unei celule goale sa o trateze ca si cum ar contine zero, atunci se debifeaza din Ignore blank.

Pentru impunerea restrictiei pe data alegem:

pentru afisarea unui mesaj de atentionare la activarea unei celule pentru care se definesc restrictiile, din eticheta Input Message, se bifeaza optiunea Show input message when cell is selected. Aceasta va determina afisarea mesajului scris in zona Input message. Acesta este afisat fie ca si caseta de tip balon langa Office Assistant, fie ca o caseta alaturata celulei, doar cand aceasta este activã. Titlul dat in zona Title identifica mesajul si este afisat o data cu textul acestuia.

Page 99: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

99

pentru afisarea unui mesaj de eroare, in cazul cand in celulã s-a introdus o valoare care nu respecta restrictia impusa, din eticheta Error Alert se bifeaza Show error alert… . In lista Style se alege optiunea doritã :

Stop - afiseaza un mesaj cu butoanele Retry, care permite intoarcerea la editarea celulei, si Cancel.

Information - afiseaza un mesaj de informare, care are butoanele OK si Cancel.

Warning - afiseaza un mesaj de atentionare cu textul "Continue?" urmat de butoanele Yes, No si Cancel.

Butoanele OK si Yes accepta data introdusa, butonul No nu accepta data si lasa celula in starea de editare iar Cancel reface starea precedenta a celulei.

In zona Title se trece titlul mesajului de eroare, textul acestuia introducandu-se in zona Error message.

Page 100: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

100

Pentru impunerea restrictiilor pe orele lucrate, se selecteaza datele, din Data/ Data Validation /Settings se alege Whole number, between 4 si 8 si apoi se introduc mesajele de atentionare si eroare dorite utilizand etichetele Input Message si Error Alert. Similar se procedeaza si cu orele suplimentare.

b) Limitarea numarului de caractere

Desi este caracteristica datelor de tip text, limitarea numarului de caractere poate fi utilizata si pentru alte tipuri de date.

se selecteaza celule pentru care se definesc restrictiile.

Data/ Data Validation /Settings

in lista Allow se selecteazã Text Length.

in lista Data se alege operatorul dorit si se completeaza apoi numarul minim de caractere, numarul maxim de caractere, potrivit operatorului selectat. Prin selectarea casetei de control Ignore blank nu se verifica restrictia pentru celulele goale sau daca asemenea celule sunt referite in definirea limitelor.

De exmplu, pentru a stabili restrictia pe marca de 4 caractere se aleg urmatoarele optiuni :

Page 101: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

101

pentru mesajele de ajutor si de eroare se vor efectua etapele 5 si 6 expuse in sectiunea Restrictii privind datele numerice.

OBS: Dupa stabilirea restrictiilor si a mesajelor pentru o celula, acestea pot fi modificate prin parcurgerea etapelor urmatoare:

se selecteaza celula pentru care se modifica validarea.

se da comanda Data Validation din fila Data.

se modifica, pe toate etichetele, optiunile dorite.

daca se doreste ca aceeasi modificare sa fie efectuata pentru toate celulele care au aceleasi restrictii si mesaje ca si celula curenta, se va marca zona de control Apply these changes to all other cells with the same settings de pe eticheta Settings.

c) Crearea unei liste derulante (drop down list)

Lista derulanta reprezinta un ansamblu de date, din care pot fi alese anumite elemente.

Exemplu: Se considera baza de date cu pontajul pe o saptamana a angajatilor. Se cere sa se insereze o coloana noua, numita Departament, prevazuta cu butoane pentru fiecare celula. Din cadrul butonului sa se aleaga departamentul din care face parte fiecare angajat.

Rezolvare:

Se insereaza alaturi lista tuturor departamentelor din companie. Se insereaza o coloana noua, numita Departament.

- Se selecteaz zona de celule goale din coloana Departament

- Din meniul Data / Data Validation / Data Validation

- Din zona Allow / List

- La Source se selecteaza departamentele inserate alturi

- Se acceseaza butonul Ok

Page 102: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

102

Fiecare celula din coloana Departament este prevazuta cu Drop Down List.

Auditul unei foi de calcul

Prin auditul foii de calcul intelegem operatiunile efectuate pentru identificarea intrarilor incorecte si evidentierea erorilor.

Pentru identificarea erorilor provenite inainte de a aplica restrictiile (validarea) datelor se procedeaza astfel:

Din fila Data / Data Validation / Circle Invalid Data – Identifica si marcheaza prin incercuire celulele care contin valori in afara limitelor stabilite la validarea datelor .

Page 103: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

103

Optiunea Clear Validation Circles – Elimina marcajele de celule create prin Circle Invalid Data.

3.Protejarea informatiilor

la nivel de fisier

la nivel de structura

la nivel de foaie de calcul

Securitatea datelor dintr-un registru Microsoft Excel poate fi asigurata la mai multe nivele. Un prim nivel este acela de acces la fisier. Al doilea mare nivel este acela care priveste protejarea informatiilor dupa ce utilizatorul a deschis fisierul Excel.

La nivel de fisier

Asigurarea parolelor de acces la fisier este efectuata in momentul salvarii fisierului. Se pot stabili parole pentru deschiderea fisierului, pentru modificarea si salvarea fisierului, precum si atasarea unei recomandari de a deschide fisierul doar in citire (read-only).

Parola pentru deschiderea fisierului

Un registru Excel protejat prin parola pentru deschidere poate fi accesat doar de utilizatorii care cunosc parola.

Pentru stabilirea acestui tip de parola se urmeaza etapele urmatoare.

se da comanda Save As din fila File.

se actioneaza butonul Tools / General Options. Apare fereastra urmatoare.

Page 104: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

104

in zona Password to open se introduce parola dorita si se da OK.

in fereastra de confirmare se mai introduce o data parola si se da OK.

se actioneaza Save din fereastra de salvare a fisierului, confirmand eventual prin Yes inlocuirea versiunii precedente a fisierului.

Parola poate contine orice combinatie de litere, cifre, spatii si simboluri si poate avea pana la 15 caractere.

Parola pentru modificarea fisierului

Stabilirea unei parole pentru salvarea fisierului are ca efect interzicerea salvarii fisierului sub acelasi nume, orice modificari fiind astfel neincluse in versiunea originala. In acest mod se asigura faptul ca fiecare utilizator deschide mereu aceeasi versiune a registrului Excel.

Parola se stabileste prin parcurgerea etapelor urmatoare.

se da comanda Save As din meniul File.

se actioneaza butonul Tools/ General Options. Apare fereastra Save Options prezentata anterior.

in zona Password to modify se introduce parola dorita si se da OK.

in fereastra de confirmare se mai introduce o data parola si se da OK.

se actioneaza Save din fereastra de salvare a fisierului, confirmand eventual prin Yes inlocuirea versiunii precedente a fisierului.

OBS: Un registru protejat poate fi deschis ca read-only, modificat si salvat ulterior sub un alt nume. Noua versiune, cu un nou nume, nu mai este protejat prin parola. Se poate astfel spune ca securitatea la acest nivel nu are decat rolul de a pastra mereu

Page 105: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

105

versiunea originala si nu acela de a interzice total accesul la informatiile, uneori complexe (macro-uri), din registru.

Recomandarea ca deschiderea fisierului sa fie read-only

Se poate asocia registrului recomandarea de a fi deschis doar in citire, micsorandu-se sansa de modificare accidentala a registrului. Aceasta este insa doar o recomandare de care utilizatorul poate sa nu tina seama.

se da comanda Save As din meniul File.

se actioneaza butonul Tools / General Options. Apare dialogul Save Options.

se bifeaza optiunea Read-only recommended si se da OK.

se actioneaza Save din fereastra de salvare a fisierului, confirmand eventual prin Yes inlocuirea versiunii precedente a fisierului.

La deschiderea registrului se va afisa un mesaj privind recomandarea de deschidere in citire in cazul in care nu sunt necesare modificari in registru.

Aceasta procedura nu stabileste atributul de read-only pentru fisier, atribut care poate fi dat prin operarea uzuala din sistemul de operare Windows.

Protectia la nivel de structura - protectia la nivel de foaie de calcul

Comanda principala este Review / Protect Workbook.

Stabilirea protectiei la acest nivel de structura are ca efect prevenirea actiunilor de modificare a structurii fisierului: inserarea unei noi foi, afisarea unei foi ascunse, stergerea, redenumirea unei foi de calcul.

Page 106: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

106

Protejarea la nivel de informatie

Stabilirea protectiei la acest nivel are ca efect prevenirea actiunilor de modificare a informatiilor din foaia de calcul. Proiectantul foii poate interzice modificarea continutului unor celule (sau a tuturor celulelor), vizualizarea liniilor/coloanelor ascunse, vizualizarea formulelor utilizate in anumite celule.

Comanda utilizata pentru stabilirea acestui nivel de protectie este Review / Protect Sheet.

Intr-o foaie de calcul, o celula poate fi schimbata daca se indica, inaintea stabilirii protectiei, ca respectiva celula este deblocata. Deblocarea se realizeaza astfel:

se selecteaza celulele care raman deblocate dupa protejare

se da comanda Format / Cells

in eticheta Protection se elimina bifa din zona Locked.

Dupa protejarea foii, aceste celule sunt singurele care permit interventia utilizatorului. Acest mecanism permite organizarea foii de calcul drept formular in care anumite zone (titluri, explicatii etc.) sunt fixe – celule blocate – iar alte zone sunt completate de utilizator cu informatiile necesare – celule neblocate.

Exercitii protejarea datelor

1. Deschideti fisierul 3. Protejare fisier.xls. Parolati fisierul la deschidere si modificare.

2. Eliminati parolele introduse.

3. Parolati fisierul la modificare

4. Eliminati parola la modificare si setati optiunea Read Only recommended.

5. Protejati foaia de calcul Stat plata (2).

6. In foaia de calcul Stat plata (3) protejati zonele care contin formule; in celelate zone este permisa modificarea.

Page 107: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

107

7. In foaia de calcul stat plata (4) protejati zonele care contin formul.Ascundeti formulele din zona protejata; in celelalte zone este permisa modificarea.

8. Protejati fisierul la nivel de structura.

9. Protejati fisierul la nivel de fereastra (Window)

Simulare examen – Excel avansat

1. Functia MATCH:

a. Este o functie de cautare, care extrage valori unice dintr-un domeniu de valori

b. Este o functie de baze de date care insumeaza valori in functie de criteriile stabilite

c. Este o functie de cautare si referinta cu 3 tipuri de cautare: 0, 1 si -1

2. Functia IF de mai jos determina:

a. 2% taxa daca data emiterii este diferita de decembrie 2008

b. 2% taxa daca data emiterii este in luna decembrie 2008

c. 0 taxa daca data emiterii este in luna decembrie 2008

3. Functia SUMIF determina:

a. Determina suma valorilor pentru un criteriu specificat

b. Determina numarul de valori pentru un criteriu specificat

c. Extrage valori unice dintr-un domeniu specificat

4. Un tabel pivot permite:

a. Utilizarea mai multor functii in cadrul aceluiasi tabel pivot

b. Se poate utiliza doar o singura functie matematica

c. Utilizarea diferentelor procentuale, doar daca datele sunt sortare

5. Functia VLOOKUP de cautare 1 determina:

a. Incadrarea valorilor intr-un interval mentionat

b. Valorile unice dintr-un domeniu mentionat

c. Suma valorilor care indeplinesc un criteriu mentionat

6. Pentru a determina daca un numar de telefon mobil este corect (incepe cu 07 )se va utiliza:

a. =IF(LEFT(A2,2)="07","corect","eronat")

Page 108: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

108

b. =IF(RIGHT(A2,2)="07","corect","eronat")

7. Extragerea numarului din coloana Date de mai jos se realizeaza:

a. =MID(A2,4,3) pentru toate celulele

b. =MID(A2,4,LEN(A2)-3)

c. =RIGHT(A2,5)

8. Validarea datelor presupune :

a. Aplicarea unor restrictii pe o baza de date

b. Identificarea erorilor dintr-o baza de date

c. Parolarea fisierului la deschidere

9. Functia SUMPRODUCT determina:

a. Suma produselor a doua sau mai multe coloane de valori

b. Rotunjirea prin adaos

c. Rotunjirea doar prin lipsa

10. Functia EOMONTH:

a. Determina ultima zi dintr-o luna specificand data de inceput si numarul de luni

b. Determina ultima zi dintr-o luna specificand data de inceput si numarul de zile

c. Determina ultima zi dintr-o luna specificand data de inceput si numarul de ani

11. Un tabel pivot poate fi actualizat :

a. Nu. Se va crea tabelul pivot de cate ori este necesar

b. Da. Se va seta optiunea Refresh data when opening the file

c. Da, doar daca tabelul pivot utilizeaza o sursa cu cel mult 3000 de randuri

12. Pentru utilizarea unui subtotal, prima operatie este :

a. Se vor filtra datele

b. Se va crea un tabel pivot

c. Se vor sorta datele

13. Care dintre urmatoarele variante este corecta?

a. Functia MID este o functie de text care extrage din interiorul unui text un numar de caractere

Page 109: Tehnici avansate de lucru cu Microsoft EXCEL...2016/08/01  · Textele - sunt siruri de caractere formate din litere , cifre, semne speciale - max. 32000 caractere. Textele sunt automat

ABSOLUTE SCHOOL Curs Excel avansat I – www.pregatire.net

109

b. Functia LEFT este o functie de text care extrage din interiorul unui text un numar de caractere

c. Functia RIGHT este o functie de text care extrage din interiorul unui text un numar de caractere

14. Rezultatul functiei EDATE(12/05/2009;2) este:

a. 14/05/2009

b. 31/07/2009

c. 12/07/2009

15. Pentru a obtine rezultatul “ Curs Informatica–Excel” se va utiliza urmatoarea sintaxa:

a. =A1&"-"&B1&" "&C1

b. =A1&" "&B1&" "&C1

c. =A1&" "&B1&"-"&C1

16. Determinati cate luni s-a lucrat la urmatorul proiect

a. =month(B2) – month(B1)

b. =year(B2)-year(B1) + month(B2)-month(B1)

c. =(year(B2)-year(B1))*12 +month(B2)-month(B1)

17. Functia LEN:

a. Extrage primele caractere specificate in cadrul functiei

b. Determina lungimea unui sir de caractere

c. Determina valori unice dintr-un domeniu specificat

18. Protejarea unui fisier la nivel de structura presupune:

a. Stabilirea unei parole la deschiderea fisierului

b. Restrictionarea accesului in ceea ce priveste lucrul cu foile de calcul