Tehnici avansate de lucru cu Microsoft EXCEL...2021/01/01 · ABSOLUTE SCHOOL Tehnici avansate de...

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]

http://www.pregatire.net/mailto:[email protected]

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


3

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

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

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

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

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


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.


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.


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)


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:


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:


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)


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.


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


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:


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


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 :


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).


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).


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.


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.


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.


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


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.


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


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)


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)


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


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


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


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.


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.


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.


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)


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.


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.


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:


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:


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:


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


38


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)


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.


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.


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.


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:


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:



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;


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.


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.


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.


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)


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.


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


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)


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.


at each change in: Status

use function: SUM

add substotal to: Pret

Se va obtine:


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:


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)


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.


at each change in: Status

use function: SUM

add substotal to: Pret

Se va obtine:


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.


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:


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:


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 :


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 Filter: presupune crearea unui filtru din cadrul caruia pot fi selectate informatiile ce vor fi vizualizate in tabelul pivot

Zona Rows: se vor selecta campurile care vor fi dispuse pe randuri

Zona Coumns: 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


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.


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


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


62

b) Actualizare pivot (meniul Analyze)

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

click dreapta pe pivot / Pivot Table Analyze / 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 meniul Options / Pivot Table Analyze (meniul este activ doar daca este selectat tabelul pivot) / Change data source:


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


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 :


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


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 :


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


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


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.


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.


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.


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


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:


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

Campuri calculate

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


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 / meniul Options sau Pivot Table Analyze / Calculations / Fields, Items & Sets / Calculated Field

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


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.


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.


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:


79

titlul graficului (Chart title)

titlul axelor (Axis titles)

legenda (Legend)

afisarea valorilor pe grafic (Data Labels)

afisarea sursei (Data Table)

!!! Daca NU gasim meniul Layout atunci trebuie sa dam click pe butonul + care apare in coltul din dreapta sus al graficului

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


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


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)


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%


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



35000 35000

% din Total 0.00% 67.51% 64.79%


% din Total 38.08% 14.95% 15.88%


% din Total 16.02% 13.13% 13.25%


% din Total 45.90% 4.41% 6.09%


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



35000 35000

% din Total 0.00% 100.00% 100.00%


% din Total 9.66% 90.34% 100.00%


% din Total 4.87% 95.13% 100.00%


% din Total 30.40% 69.60% 100.00%


2177.36 51846.59 54023.95

Total % din Total

4.03% 95.97% 100.00%


84

% OF

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

Anul



35000 35000

% din Total

100.00% 100.00%


% din Total

22.14% 24.51%


% din Total

19.45% 20.45%


% din Total

6.54% 9.39%


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


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 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 2

Date post:	05-Feb-2021
Category:	Documents
Upload:	others
View:	21 times
Download:	5 times

Tehnici avansate de lucru cu Microsoft EXCEL...2021/01/01 · ABSOLUTE SCHOOL Tehnici avansate de...

Documents