excel

Verificarea faptului că un număr este mai mare sau mai mic decât alt număr

Afișare totală

Pentru această activitate se va utiliza funcția IF.

Exemplu de foaie de lucru

Exemplul este mai ușor de înțeles dacă este copiat într-o foaie de lucru goală.

Cum?

1

2

3

4

A

Date

15.000

9.000

8.000

Formulă Descriere (Rezultat)

=A2>A3 Este numărul din A2 mai mare decât numărul din A3? (TRUE)

=IF(A3<=A4; "DA";"NU")

Este numărul din A3 mai mic sau egal cu numărul din A4? (NU)

Adunarea datelorAfișare totală

Adunarea unui număr de zile la o dată

Pentru această activitate se va utiliza operatorul de adunare (+).



Cum?

1234

A B

Dată Numărul de zile de adunat

9.6.2007 3

=TODAY() 5

10.12.2008 54


=A2+B2 Se adună 3 zile la 9.6.2007 (12.6.2007)

=A3+B3 Se adună 5 zile la ziua curentă (variază)

=A4+B4 Se adună 54 zile la 10.12.2008 (2.2.2009)

Adunarea unui număr de luni la o dată

Pentru această activitate se vor utiliza funcțiile DATE, YEAR, MONTH și DAY.


Este indicat ca acest exemplu să fie copiat într-o foaie de lucru goală.

Cum?

1234

A B

Dată Numărul de luni de adunat

9.6.2007 3

2.9.2007 5

10.12.2008 25


=DATE(YEAR(A2);MONTH(A2)+B2;DAY(A2))

Se adună 3 luni la 9.6.2007 (9.9.2007)


Se adună 5 luni la 2.9.2007 (2.2.2008)


Se adună 25 luni la 10.12.2008 (10.1.2011)

Adunarea unui număr de ani la o dată




Cum?

1234

A B

Dată Numărul de ani de adunat

9.6.2007 3

2.9.2007 5

10.12.2008 25


=DATE(YEAR(A2)+B2;MONTH(A2);DAY(A2))

Se adună 3 ani la 9.6.2007 (9.6.2010)


Se adună 5 ani la 2.9.2007 (2.9.2012)


Se adună 25 ani la 10.12.2008 (10.12.2033)

Adunarea unei combinații de zile, luni și ani la o dată




Cum?

12

A

Dată

9.6.2007


=DATE(YEAR(A2)+3;MONTH(A2)+1;DAY(A2)+5) Se adună 3 ani, 1 lună și 5 zile la 9.6.2007 (14.7.2010)

=DATE(YEAR(A2)+1;MONTH(A2)+7;DAY(A2)+5) Se adună 1 an, 7 luni și 5 zile la 9.6.2007 (14.1.2009)

Formula de mai sus are următoarele argumente.

Formulă pentru adunarea datelor

start_date: o dată sau o referință la o celulă care conține o dată

add_year: numărul de ani de adunat

add_month: numărul de luni de adunat

add_day: numărul de zile de adunat

Calculul numărului de zile dintre două date calendaristice

Pentru această activitate se vor utiliza operatorul de scădere (-) sau funcția NETWORKDAYS.

Dacă această funcție nu este disponibilă, instalați și încărcați programul de completare Pachet instrumente analiză.

Cum?



Cum?

123

A

Dată

8.6.2007

20.6.2007

Formulă Descriere

=A3-A2 Numărul de zile dintre cele două date calendaristice (12)

=NETWORKDAYS(A2;A3)

Numărul de zile lucrătoare dintre cele două date calendaristice (9)

NOTĂ Pentru a vizualiza datele ca numere, selectați celula și faceți clic pe Celule în meniul Format. Faceți clic pe fila Număr, apoi

faceți clic pe Număr în caseta Categorie.

Calculul numărului de ani dintre două date calendaristice

Utilizați funcția YEAR pentru a efectua această activitate.



Cum?

123

A

Dată

9.6.2007

4.6.2010


=YEAR(A3)-YEAR(A2)

Numărul de ani cuprinși între două date calendaristice (3)

NOTE

Pentru a vizualiza datele calendaristice ca numere, selectați celula și faceți clic pe Celule în meniul Format. Faceți clic pe fila Număr, apoi faceți clic pe Număr în caseta Categorie.

Este imposibil de scăzut o dată care este ulterioară altei date, în acest caz apărând eroarea #### în celulă.

Afișarea datelor ca zi din săptămânăAfișare totală

Formatarea celulelor pentru a afișa datele ca zile din săptămână

1. Selectați celulele care conțin datele de afișat ca zile din săptămână.

2. În meniul Format faceți clic pe Celule, apoi faceți clic pe fila Număr.

3. Sub Categorie, faceți clic pe Particularizare și în caseta Tip, tastați dddd pentru scrierea numelor complete ale zilelor săptămânii (luni,

marți etc.), sau ddd pentru abrevieri (Lun, Mar, Mie etc.).

Conversia datelor în text pentru zilele din săptămână

Pentru această activitate utilizați funcția TEXT.



Cum?

123

A

Dată

19-feb-2007

3-ian-2008


=TEXT(A2, "dddd")

Calculează ziua din săptămână corespunzătoare datei și întoarce numele complet al zilei (luni)

=TEXT(A3, "ddd")

Calculează ziua din săptămână corespunzătoare datei și întoarce numele abreviat al zilei (J)

Calcularea unei balanțe curenteAfișare totală

1. Setați o foaie de lucru ca în exemplul următor sau copiați-o într-o foaie de lucru necompletată.

Cum?

Exemplu

1

2

3

A B C

Depozite Retrageri Bilanț

1.000 LEI 625 LEI =SUM(A2;-B2)

1000 740 =SUM(C2;A3;-B3)

2. Faceți clic în exteriorul celulei C3 pentru a vedea totalul calculat.

3. Pentru menținerea balanței în lucru, adăugați un rând pentru fiecare intrare nouă.

Adunarea numerelor ce nu sunt într-un rând sau coloană contiguă

Pentru aceasta se poate utiliza funcția SUM, ca în exemplul următor.

Exemplu


Cum?

1234567

A B

Vânzător Factură

Florescu 15.000

Florescu 9.000

Ionescu 8.000

Ionescu 20.000

Florescu 5.000

Popescu 22.500


=SUM(B2:B3; B5)

Adună 2 facturi de la Florescu și una de la Ionescu (44.000)

=SUM(B2;B5;B7) Adună facturi individuale de la Florescu, Ionescu și Popescu (57.500)

NOTĂ Funcția SUM poate include maximum 30 de celule sau referințe de zone.

Pentru informații suplimentare vedeți funcția SUM.

Adunarea numerelor bazată pe o condiție

Funcția SUMIF se poate utiliza pentru crearea unei valori totale pentru o zonă bazată pe o valoare din altă zonă, ca în exemplul

următor.

Exemplu


Cum?

1234567

A B

Vânzător Factură

Florescu 15.000

Florescu 9.000

Ionescu 8.000

Ionescu 20.000

Florescu 5.000

Popescu 22.500


=SUMIF(A2:A7; "Florescu";B2:B7)

Suma facturilor pentru Florescu (29.000)

=SUMIF(B2:B7; ">=9.000";B2:B7)

Suma facturilor mari, mai mari sau egale cu 9.000 (66.500)

=SUMIF(B2:B7; "<9000";B2:B7) Suma facturilor mici, mai mici decât 9.000 (13.000)

Funcția SUMIF utilizează următoarele argumente

Formulă cu funcția SUMIF

Zonă de evaluare: se verifică aceste celule pentru a determina dacă un rând îndeplinește criteriile dorite.

Criterii: condiția ce e necesar să fie îndeplinită de celulele evaluate pentru ca rândul să fie inclus în sumă.

Zonă de însumare: se adună numerele în aceste celule dacă rândul satisface condiția.

Adunarea numerelor bazată pe mai multe condiții

Pentru aceasta se pot utiliza funcțiile IF și SUM, ca în exemplul următor.

Exemplu


Cum?

1234

A B C D

Regiune Vânzător Tip Vânzări

Sud Florescu Băuturi

3.571

567891011

Vest Marcu Lactate 3.338

Est Ionescu Băuturi

5.122

Nord Ionescu Lactate 6.239

Sud Popescu Produs 8.677

Sud Marcu Carne 450

Sud Marcu Carne 7.673

Est Ionescu Produs 664

Nord Marcu Produs 1.500

Sud Popescu Carne 6.596


=SUM(IF((A2:A11="Sud")*(C2:C11="Carne");D2:D11))

Totalul vânzărilor de carne din regiunea de sud (14.719)

=SUM(IF((A2:A11="Sud")+(A2:A11="Est");D2:D11)) Totalul vânzărilor de carne din regiunile de sud sau est (32.753)

NOTĂ E necesară introducerea formulelor din exemplu ca formule matrice. După copierea exemplului pe o foaie de lucru goală,

selectați celula formulă. Apăsați F2, apoi apăsați CTRL+SHIFT+ENTER. Dacă formula nu este introdusă ca o formulă matrice, se

returnează eroarea #VALUE!.

Pentru informații suplimentare vedeți funcțiile SUM și IF.

Calcularea procentelorAfișare totală

Procentele se calculează cu ajutorul acestei ecuații:

cantitate/total = procent

Unde procentul este în format zecimal. În Excel, se poate calcula simplu oricare dintre aceste variabile. De asemenea, rezultatele se pot

afișa rapid ca procente, făcând clic pe Stil procent din bara de instrumenteFormatare.

Calcularea cantității dacă se cunoaște totalul și procentul

De exemplu, dacă vă cumpărați un computer de 24 de milioane lei și taxa pe valoarea adăugată este de 19%, cât trebuie să plătiți

pentru aceasta? În acest exemplu, doriți să aflați 19% din 24.000.000.

Pentru a calcula acest rezultat, inițializați o foaie de calcul ca următoarea sau copiați exemplul pe o foaie de lucru necompletată.

Cum?

12

A B

Prețul de achiziție

TVA (în format zecimal)

24.000.000 0,19


=A2*B2 Înmulțește 24.000.000 cu 0,19 pentru a afla suma de plată pentru TVA (4.560.000 lei)

NOTĂ Pentru a face conversia numărului din format de procent în format zecimal, îl împărțim la 100. De exemplu, taxa din acest

exemplu (19) împărțită la 100 este 0,19.

Calcularea diferenței dintre două numere ca procent

De exemplu, veniturile dvs. în luna noiembrie sunt de 23.420.000 lei și de 25.000.000 în decembrie. Care este modificarea procentuală

a veniturilor de la o lună la alta? Pentru aceasta, utilizați funcția ABS și operatorii de scădere (-) și împărțire (/).

Pentru a calcula rezultatul, inițializați o foaie de calcul ca următoarea sau copiați exemplul pe o foaie de lucru necompletată.

Cum?

12

A B

Venituri noiembrie

Venituri decembrie

23.420.000 25.000.000


=(B2-A2)/ABS(A2)

Împarte diferența dintre al doilea număr și primul număr la valoarea absolută a primului număr pentru a obține procentul de modificare (0,06746 sau 6,75%)

NOTĂ Pentru a afișa numărul ca procent, selectați celula și faceți clic pe Stil procent din bara de instrumente Formatare.

Creșterea sau descreșterea procentuală a unui număr

De exemplu, cheltuiți în medie 2.500.000 lei săptămânal pe alimente și doriți să reduceți cu 25% cheltuielile săptămânale cu

alimentele. Cât să cheltuiți? Sau, dacă doriți să măriți suma destinată săptămânal alimentelor cu 25%, care va fi noua sumă?

Pentru a calcula rezultatul, inițializați o foaie de calcul ca următoarea sau copiați exemplul pe o foaie de lucru necompletată.

Cum?

12

A B

Număr Procent

2.500.000 25%


=A2*(1-B2) Scade 25% din 2.500.000 (1.875.000)

=A2*(1+B2) Adaugă 25% la 2.500.000 (3.175.000)

=A2*(1+35%)

Adaugă 35% la 2.500.000 (3.375.000))

NOTĂ Când tastați un număr urmat de semnul de procent (%), numărul este interpretat ca o sutime din valoarea sa. De exemplu, 5%

este interpretat ca 0,05.

Combinarea numelor de familie cu prenumeleAfișare totală

Pentru această activitate se vor utiliza funcția CONCATENATE sau operatorul ampersand (&).



Cum?

1

2

3

A B

Prenume Nume de familie

Florica Marcu

Andrei Fuller


=A2&" "&B2 Combină numele de deasupra, separate de un spațiu (Florica Marcu)

=B3&", "&A3 Combină numele de deasupra, separate de o virgulă

(Fuller, Andrei)

=CONCATENATE(A2;" ";B2) Combină numele de deasupra, separate de un spațiu (Florica Marcu)

NOTĂ Pentru a înlocui formula cu rezultatele, selectați celulele, faceți clic pe Copiere , faceți clic pe Lipire , faceți clic

pe Opțiuni lipire , apoi faceți clic pe Numai valori.

Detalii funcție

CONCATENATE

Modificarea textului în majuscule/minusculeAfișare totală

Pentru această activitate se vor utiliza funcțiile UPPER, LOWER sau PROPER.



Cum?

1

2

A

Nume

nadia Marcu


=UPPER(A2) Modifică tot textul în MAJUSCULE (NADIA MARCU)

=LOWER(A2) Modifică tot textul în minuscule (nadia marcu)

=PROPER(A2)

Modifică textul în caractere de tip titlu (Nadia Marcu)

Numărarea celulelor care nu sunt necompletateAfișare totală

Pentru această activitate se va utiliza funcția COUNTA.



Cum?

1

2

34

5

6

A

Date

Vânzări

19

TRUE


=COUNTA(A2:A6) Numără celulele care nu sunt necompletate din lista de mai sus (3)

=COUNTA(A2:A3; A6)

Numără celulele care nu sunt necompletate în cadrul unui grup format din primele două celule și ultima celulă din listă (1)

Contorizarea aparițiilor unei valori utilizând o funcție

Se utilizează funcția COUNTIF pentru îndeplinirea acestei activități.

Exemplu

Exemplul este mai ușor de înțeles dacă-l copiați într-o foaie de lucru goală.

Cum?

1

2

A B

Agent de vânzări Factură

3

4

5

6

7

Florescu 15.000

Florescu 9.000

Ionescu 8.000

Ionescu 20.000

Florescu 5.000

Georgescu 22.500


=COUNTIF(A2:A7;"Florescu") Număr de intrări pentru Florescu (3)

=COUNTIF(A2:A7;A4) Număr de intrări pentru Ionescu (2)

=COUNTIF(B2:B7,"< 20000") Numărul de valori de facturare mai mici de 20.000 (4)

=COUNTIF(B2:B7,">="&B5) Numărul de valori de facturare mai mari sau egale cu 20.000 (2)

Rotunjirea prin adaos a unui număr

Pentru această activitate se vor utiliza funcțiile ROUNDUP, EVEN sau ODD.



Cum?

1234

A

Date

20,3

-5,9

12,5493


=ROUNDUP(A2;0)

Rotunjește 20,3 prin adaos, până la cel mai apropiat număr întreg (21)

=ROUNDUP(A3;0)

Rotunjește -5,9 prin adaos (-6)

=ROUNDUP(A4;2)

Rotunjește 12,5493 prin adaos până la sutimea cea mai apropiată, la două zecimale (12,55)

=EVEN(A2) Rotunjește 20,3 prin adaos până la cel mai apropiat număr par (22)

=ODD(A2) Rotunjește 20,3 prin adaos până la cel mai apropiat număr impar (21)

Ridicarea la putere a unui numărAfișare totală

Pentru această activitate se vor utiliza operatorul „^” sau funcția POWER.



Cum?

1

2

3

A B


=POWER(5;2)

Calculează cinci la pătrat (25)

=5^3 Calculează cinci la cub (125)

Înmulțirea numerelor din diferite celule utilizând o formulă

Pentru această activitate se vor utiliza operatorul asterisc (*) sau funcția PRODUCT.



Cum?

1234

A

Date

5

15

30


=A2*A3 Înmulțește numerele din primele două celule (75)

=PRODUCT(A2:A4) Înmulțește toate numerele din zonă (2250)

=PRODUCT(A2:A4;2) Înmulțește toate numerele din zonă și cu 2

(4500)

Numărarea numerelor mai mari sau mai mici decât un numărAfișare totală

Pentru această activitate se va utiliza funcția COUNTIF.



Cum?

1

2

3

4

5

6

7

A B

Vânzător Factură

Florescu 15.000

Florescu 9.000

Ionescu 8.000

Ionescu 20.000

Florescu 5.000

Vasilescu 22.500


=COUNTIF(B2:B7;">9000") Numere mai mari decât 9.000 (3)

=COUNTIF(B2:B7;"<=9000")

Numere mai mici sau egale cu 9.000 (3)

Transformarea unităților de măsurăAfișare totală

Pentru transformarea unităților de măsură pentru o gamă variată de mărimi (greutate, distanță, timp, presiune, forță, energie, putere,

câmp magnetic, temperatură și pentru lichide) se utilizează funcția CONVERT.

Dacă această funcție nu este disponibilă, instalați și încărcați programul de completare Pachet instrumente analiză.

Cum?



Cum?

1

2

A

Date

6


=CONVERT(A2;"C";"F") Transformă 6 grade Celsius în grade Fahrenheit (42,8)

=CONVERT(A2;"tsp";"tbs")

Transformă 6 lingurițe în linguri (2)

=CONVERT(A2;"gal";"l") Transformă 6 galoane în litri (22,71741274)

=CONVERT(A2;"mi";"km")

Transformă 6 mile în kilometri (9,656064)

=CONVERT(A2;"km";"mi")

Transformă 6 kilometri în mile (3,728227153)

=CONVERT(A2;"in";"ft") Transformă 6 inch în picioare (0,5)

=CONVERT(A2;"cm";"in") Transformă 6 centimetri în inch (2,362204724)

NOTĂ Pentru a vedea o listă completă a unităților de măsură care pot fi transformate, consultați termenul de ajutor pentru funcția

CONVERT

Calculul celui mai mic sau celui mai mare număr dintr-un intervalAfișare totală

Dacă celulele sunt într-un rând sau într-o coloană contigue

1. Selectați o celulă situată dedesubtul sau la dreapta numerelor pentru care se caută cel mai mic număr

2. Faceți clic pe săgeata de lângă Însumare automată , apoi faceți clic pe Min (calculează minimul), sau pe Max (calculează maximul)

și apăsați pe ENTER.

Dacă celulele nu sunt într-un rând sau într-o coloană contigue

Pentru această activitate se vor utiliza funcțiile MIN, MAX, SMALL sau LARGE.



Cum?

1234567

A

Date

10

7

9

27

0

4


=MIN(A2:A7) Cel mai mic număr din interval (0)

=MAX(A2:A7) Cel mai mare număr din interval (27)

=SMALL(A2:A7; 2)

Al doilea dintre cele mai mici numere din interval (4)

=LARGE(A2:A7;3) Al treilea dintre cele mai mari numere din interval (9)

Calculul medianei unui grup de numereAfișare totală

Pentru această activitate se va utiliza funcția MEDIAN. Mediana este valoarea de la centrul unui interval ordonat de numere.



Cum?

1

2

A

Date

3

4

5

6

7

10

7

9

27

0

4


=MEDIAN(A2:A7) Mediana numerelor din lista de mai sus (8)

Calculul mediei numerelorAfișare totală

Prin medie înțelegem media aritmetică.

Calculul mediei numerelor dintr-un rând sau coloană contigue

1. Faceți clic pe o celulă aflată dedesubt sau la dreapta numerelor pentru care se va calcula media.

2. Faceți clic pe săgeata de lângă Însumare automată pe bara de instrumente Standard, apoi faceți clic pe Medie și apăsați tasta

ENTER.

Calculul mediei numerelor care nu se află într-un rând sau coloană contigue

Pentru această activitate se va utiliza funcția AVERAGE.



Cum?

1234567

A

Date

10

7

9

27

0

4


=AVERAGE(A2:A7) Calculează media tuturor numerelor din lista de mai sus (9,5)

=AVERAGE(A2:A4;A7) Calculează media primelor trei numere și a ultimului număr din listă (7,5)

=AVERAGE(IF(A2:A7<>0; A2:A7;""))

Calculează media numerelor din listă cu excepția celor care conțin zero, precum în celula A6 (11,4)

NOTĂ Ultima formulă din exemplu se va introduce ca formulă matrice. După copierea exemplului într-o foaie de lucru goală, selectați

celula A11. Apăsați F2, apoi apăsați CTRL+SHIFT+ENTER. Dacă formula nu este introdusă ca formulă matrice, va fi întoarsă eroarea

#VALUE!.

Detalii funcție

AVERAGE

Calculul mediei ponderate

Pentru această activitate se vor utiliza funcțiile SUMPRODUCT și SUM.



Cum?

În acest exemplu se calculează prețul unitar mediu pentru trei achiziții, fiecare achiziție fiind pentru numere diferite de unități de

produs la prețuri unitare diferite.

1234

A B

Preț unitar Număr de unități

20 500

25 750

35 200


=SUMPRODUCT(A2:A4;B2:B4)/SUM(B2:B4) Împarte costul total al celor trei comenzi la numărul total de unități comandate (24,66)

GETPIVOTDATA (funcția GETPIVOTDATA)

Acest articol descrie sintaxa de formulă și utilizarea funcției GETPIVOTDATA în Microsoft Excel.

Descriere

Întoarce datele memorate într-un raport PivotTable. Se poate utiliza GETPIVOTDATA pentru a regăsi rezumatul de date dintr-un raport

PivotTable, dacă rezumatul de date este vizibil în raport.

NOTĂ Se poate introduce rapid o formulă simplă GETPIVOTDATA prin tastarea = (semnul egal) în celula în care se dorește întoarcerea

valorii, apoi clic pe celulă în raportul PivotTable care conține datele dorite pentru întoarcere.

SintaxăGETPIVOTDATA(câmp_date, tabel_pivot, [câmp1, element1, câmp2, element2], ...)

Sintaxa funcției GETPIVOTDATA are următoarele argumente:

Câmp_date Obligatoriu. Este numele, inclus în ghilimele, pentru câmpul de date ce conține datele pe care doriți să le regăsiți. Tabel_pivot Obligatoriu. Este o referință la orice celulă, zonă de celule sau zonă de celule denumite într-un raport PivotTable. Această

informație este utilizată pentru determinarea raportului PivotTable care conține datele pe care doriți să le regăsiți. Câmp1, Element1, Câmp2, Element2 Obligatoriu. Sunt de la unu la 126 de perechi de nume de câmpuri și nume de elemente care

descriu datele pe care doriți să le regăsiți. Perechile pot fi în orice ordine. Numele câmpurilor și numele pentru elemente altele decât date calendaristice și numere sunt incluse între ghilimele. Pentru rapoarte PivotTable OLAP, elementele pot conține numele de sursă al dimensiunii și, de asemenea, numele de sursă al elementului. O pereche formată din câmp și element pentru un PivotTable OLAP poate arăta astfel:

"[Produs]";"[Produs].[Toate produsele].[Alimente].[Produse brutărie]"

Observații

În calculele funcției GETPIVOTDATA sunt incluse câmpuri sau elemente calculate și calcule utilizator. Dacă tabel_pivot este o zonă care include două sau mai multe rapoarte PivotTable, datele regăsite vor fi din cel mai recent raport creat în

acea zonă. Dacă argumentele câmp și element descriu o singură celulă, va fi returnată valoarea acelei celule, indiferent că este un șir, un număr, o

eroare și așa mai departe. Dacă un element conține o dată, valoarea trebuie exprimată ca un număr serial sau ocupat prin utilizarea funcției DATE astfel încât

valoarea va fi reținută dacă foaia de lucru este deschisă în alt loc. De exemplu, un element care face referire la data 5 martie 1999 poate fi introdus ca 36224 sau DATE(1999;3;5). Orele pot fi introduse ca valori zecimale sau utilizând funcția TIME.

Dacă tabel_pivot este o zonă în care nu se găsește un raport PivotTable, GETPIVOTDATA întoarce #VALUE!. Dacă argumentele nu descriu un câmp vizibil sau dacă ele conțin un filtru de raport în care se datele filtrate nu se afișează, GETPIVOTDATA

returnează valoarea de eroare #REF!.

Exemplu

Din motive practice, datele exemplificative afișate mai jos nu conțin date reale PivotTable, însă aici este un instantaneu al datelor.

Formulele și rezultatele apar în exemplu.

Copiați datele din exemplele din următorul tabel și lipiți-le în celula A1 a noii foi de lucru Excel. Pentru ca formulele să afișeze rezultate,

selectați-le, apăsați pe F2, apoi pe Enter. Dacă trebuie, puteți ajusta lățimea coloanei pentru a vedea toate datele.

FORMULĂ REZULTAT

GETPIVOTDATA("Vânzări"; $A$4) Returnează totalul general pentru câmpul Vânzări, 49325 lei.

GETPIVOTDATA("Sumă de Vânzări"; $A$4)

Returnează, de asemenea, totalul general pentru

câmpul Vânzări, 49325 lei; numele câmpului poate fi introdus așa cum arată pe foaie sau ca rădăcină (fără „Sumă a”, „Numărare a” etc.).

GETPIVOTDATA("Vânzări"; $A$4; "Lună"; "Martie")

Returnează totalul general pentru Martie, 30337 lei.

GETPIVOTDATA("Vânzări"; $A$4; "Lună"; "Martie"; "Produs"; "Legume și fructe"; "Vânzător"; "Crisiarcu")

Returnează 10.201 lei.

GETPIVOTDATA("Vânzări"; $A$4; "Regiune"; "Sud")

Returnează valoarea de eroare #REF!, deoarece datele din regiunea Sud nu sunt vizibile.

GETPIVOTDATA("Vânzări"; $A$4; "Produs"; "Băuturi"; "Vânzător"; "Roman")

Returnează valoarea de eroare #REF!, deoarece nu există nicio valoare de total pentru vânzările de băuturi ale lui Roman.

Crearea unui raport PivotTable pentru analiza datelor externe

Posibilitatea de a analiza toate datele vă poate ajuta să luați decizii mai bune de afaceri. Dar uneori este greu să știți unde să începeți,

mai ales atunci când aveți multe date care sunt stocate în afara programului Excel, cum ar fi într-o bază de date Microsoft Access sau

Microsoft SQL Server sau într-un fișier cub OLAP (Online Analytical Processing). În acel caz, vă veți conecta la sursa de date externă,

apoi veți crea un raport PivotTable pentru a rezuma, analiza, explora și prezenta datele.

Iată cum să creați un raport PivotTable utilizând o conexiune de date externă existentă:

1. Faceți clic pe orice celulă din foaia de lucru.

2. Faceți clic pe Inserare > PivotTable.

3. În caseta de dialog Creare PivotTable, sub Alegeți datele pe care le veți analiza, faceți clic pe Utilizare sursă de date externă.

4. Faceți clic pe Alegere conexiune.

5. În fila Conexiuni, în caseta Afișare, păstrați selectate Toate conexiunile sau alegeți categoria de conexiuni care are sursa de date la

care doriți să vă conectați.

Pentru a reutiliza sau partaja o conexiune existentă, utilizați o conexiune din Conexiuni în acest registru de lucru.

6. În lista de conexiuni, selectați conexiunile dorite, apoi faceți clic pe Deschidere.

7. Sub Alegeți locul unde se va plasa raportul PivotTable, selectați o locație.

Pentru a plasa raportul PivotTable într-o foaie de lucru nouă care începe cu celula A1, alegeți Foaie de lucru nouă. Pentru a plasa raportul PivotTable în foaia de lucru activă, alegeți Foaie de lucru existentă, apoi, în casetaLocație, introduceți celula

în care doriți să înceapă raportul PivotTable.

8. Faceți clic pe OK.

Excel adaugă un raport PivotTable gol și afișează Lista de câmpuri, astfel încât să afișați câmpurile dorite și să le rearanjați pentru a

crea propriul aspect.

9. În secțiunea de listă de câmpuri, bifați caseta de selectare de lângă un nume de câmp pentru a plasa câmpul într-o zonă implicită din

secțiunea de zone din Listă de câmpuri.

De obicei, câmpurile non-numerice se adaugă la zona Rânduri, câmpurile numerice se adaugă la zona Valori, iar câmpurile de dată și

oră se adaugă la zona Coloane. Puteți muta câmpurile în altă zonă, după cum este necesar.

SFAT Puteți și să faceți clic cu butonul din dreapta pe un nume de câmp, apoi să selectați Se adaugă la filtrul de raport, Se

adaugă la etichetele de coloană, Se adaugă la etichetele de rând sau Se adaugă la valori pentru a plasa câmpul în acea zonă

sau secțiune de zone sau puteți să glisați un câmp din secțiunea de câmp într-o zonă din secțiunea de zone.

Utilizați Listă de câmpuri pentru a rearanja mai departe datele din raportul PivotTable făcând clic cu butonul din dreapta pe câmpuri

în secțiunea de zone și selectând zona dorită sau glisând câmpurile între zonele din secțiunea de zone.

Conectarea la o sursă de date externă nouă

Pentru a crea o conexiune de date externă nouă la SQL Server și pentru a importa datele în Excel ca un tabel sau raportul PivotTable,

procedați astfel:

1. Faceți clic pe Date > Din alte surse.

2. Faceți clic pe conexiunea dorită.

Faceți clic pe Din SQL Server pentru a crea o conexiune la un tabel SQL Server. Faceți clic pe Din Analysis Services pentru a crea o conexiune la un cub SQL Server Analysis.

3. În Expert Conexiune date, parcurgeți pașii pentru a stabili o conexiune.

În pagina 1, introduceți serverul de bază de date și specificați cum doriți să se efectueze conectarea la server. În pagina 2, introduceți baza de date, tabelul sau interogarea care conține datele pe care le doriți. În pagina 3, introduceți fișierul de conexiune pe care doriți să-l creați.

Pentru a crea o conexiune nouă la o bază de date Access și pentru a importa datele în Excel ca tabel sau raport PivotTable, procedați

astfel:

1. Faceți clic pe Date > Din Access.

2. În caseta de dialog Selectare sursă de date, găsiți baza de date la care doriți să vă conectați și faceți clic peDeschidere.

3. În caseta de dialog Selectare tabel, selectați tabelul dorit, apoi faceți clic pe OK.

Dacă există mai multe tabele, bifați caseta de selectare Activați selectarea multiplă a tabelelor, astfel încât să puteți bifa casetele

de selectare pentru tabelele dorite, apoi faceți clic pe OK.

4. În caseta de dialog Import date, selectați modul în care doriți să vizualizați datele în foaia de lucru și locul în care doriți să le puneți, apoi

faceți clic pe OK.

Tabelele sunt adăugate automat la Modelul de date și baza de date Access este adăugată la conexiunile de registru de lucru.

Crearea unui raport PivotTable pentru analiza datelor dintr-o foaie de lucru

Posibilitatea de a analiza toate datele din foaia dvs. de lucru vă poate ajuta să luați decizii de afaceri mai bune. Dar uneori este greu să

știți de unde să începeți, mai ales atunci când aveți foarte multe date. Excel vă poate ajuta prin recomandarea și crearea automată de

rapoarte PivotTable, care sunt o modalitate foarte bună de rezumare, analiză, explorare și prezentare a datelor dvs.

1. Asigurați-vă că datele dvs. au titluri de coloane sau anteturi de tabel și că nu există rânduri necompletate.

2. Faceți clic pe orice celulă dintr-o zonă de celule sau din tabel.

3. Faceți clic pe Inserare > PivotTable recomandate.

4. În caseta de dialog Diagrame PivotTable recomandate, faceți clic pe orice aspect PivotTable pentru a obține o examinare, apoi

selectați una care afișează datele așa cum doriți.

5. Faceți clic pe OK.

Excel plasează raportul PivotTable într-o foaie de lucru nouă și afișează Lista de câmpuri, astfel încât să putețirearanja mai departe

datele PivotTable, după cum este necesar

NOTE

Utilizarea unui raport PivotTable recomandat este o modalitate rapidă de a începe pe calea cea bună. Însă, puteți în continuare să creați un raport PivotTable gol și să adăugați propriile câmpuri și aspect. Trebuie doar să faceți clic pe PivotTable în fila Inserare în loc de PivotTable recomandate.

De asemenea, puteți crea un raport PivotTable din date externe, cum ar fi o sursă de date Online Analytical Processing (OLAP) sau puteți baza un raport PivotTable pe Modelul de date, astfel încât să analizați datele din mai multe tabele.

În loc de Expertul PivotTable și PivotChart care putea fi utilizat în versiunile anterioare de Excel, acum veți utiliza comenzile PivotTable și PivotTable recomandate din panglică pentru a crea rapoarte PivotTable. Însă, dacă vă lipsește expertul, este în continuare disponibil. Îl puteți adăuga la Bara de instrumente Acces rapid sau să apăsați ALT, D, P pentru a-l porni.

Lucrul cu relațiile în rapoartele PivotTable

Rapoartele PivotTable au fost construite în mod tradițional utilizând cuburi OLAP și alte surse de date complexe care au deja conexiuni

îmbogățite între tabele. Totuși, în Excel 2013, sunteți liber să importați mai multe tabele și să vă construiți propriile conexiuni între

tabele. Deși această flexibilitate este puternică, ea facilitează totodată colectarea unor date care nu sunt asociate, conducând la

rezultate ciudate.

Ați creat vreodată un astfel de raport PivotTable? Ați intenționat să creați o defalcare a achizițiilor în funcție de regiune și ați fixat un

câmp pentru volumul achizițiilor în zona Valori și un câmp pentru regiunile de vânzări în zona Etichete de coloană. Însă rezultatele

sunt greșite.

Cum puteți remedia acest lucru?

Problema este următoarea: câmpurile pe care le-ați adăugat în raportul PivotTable pot fi în același registru de lucru, însă tabelele care

conțin fiecare coloană nu sunt asociate. De exemplu, puteți avea un tabel care listează fiecare regiune de vânzări și alt tabel care

listează achizițiile pentru toate regiunile. Pentru a crea raportul PivotTable și a obține rezultatele corecte, trebuie să creați o relație

între cele două tabele.

După ce creați relația, raportul PivotTable combină corect datele din tabelul de achiziții cu lista de regiuni, iar rezultatele arată în felul

următor:

Excel 2013 conține tehnologie dezvoltată de Microsoft Research (MSR) pentru detectarea și remedierea automată a problemelor cu

relațiile, precum aceasta.

ÎNCEPUTUL PAGINII

Utilizarea detectării automate

Detectarea automată verifică noile câmpuri pe care le adăugați într-un registru de lucru care conține un raport PivotTable. Dacă noul

câmp nu este asociat la anteturile de rând și de coloană ale raportului PivotTable, apare un mesaj în zona de notificare din partea de

sus a raportului PivotTable, care vă anunță că poate fi necesară o relație. Excel va analiza, de asemenea, datele noi, pentru a găsi

potențiale relații.

Puteți să continuați să ignorați mesajul și să lucrați cu raportul PivotTable; totuși, dacă faceți clic pe Creare, algoritmul începe să

calculeze și vă analizează datele. În funcție de valorile din datele noi și de dimensiunea și complexitatea raportului PivotTable și de

relațiile pe care le-ați creat deja, acest proces poate dura câteva minute.

Procesul are două etape:

http://office.microsoft.com/ro-ro/excel-help/lucrul-cu-relatiile-in-rapoartele-pivottable-HA102836070.aspx?CTT=1#top

Detectarea relațiilor. Puteți să revizuiți lista de relații sugerate, după terminarea analizei. Dacă nu anulați, Excel va trece automat la următorul pas: crearea relațiilor.

Crearea relațiilor. După ce relațiile au fost aplicate, apare un dialog de confirmare și puteți să faceți clic pe linkulDetalii pentru a vedea o listă a relațiilor care au fost create.

Puteți să anulați procesul de detectare, dar nu puteți să anulați procesul de creare.

Algoritmul MSR caută „cel mai bun” set de relații posibil, pentru a conecta tabelele din modelul dvs. Algoritmul detectează toate

relațiile posibile pentru datele noi, luând în considerare numele coloanelor, tipurile de date din coloane, valorile din coloane și coloanele

care se află în rapoartele PivotTable.

Excel alege apoi relația cu cel mai mare scor de „calitate”, conform euristicii interne. Pentru mai multe informații, consultați Prezentare

generală a relațiilor și Depanarea relațiilor.

Dacă detectarea automată nu vă oferă rezultatele corecte, puteți să editați relațiile, să le ștergeți sau să creați manual relații noi.

Pentru mai multe informații, consultați Crearea unei relații între două tabele sau Crearea relațiilor în opțiunea Vizualizare diagramă

ÎNCEPUTUL PAGINII

Rânduri necompletate în tabelele Pivot (Membru necunoscut)

Deoarece un raport PivotTable reunește tabele de date asociate, dacă oricare dintre tabele conține date care nu pot fi asociate de o

cheie sau de o valoare potrivită, datele respective trebuie gestionate cumva. În bazele de date cu mai multe dimensiuni, modalitatea

de gestionare a datelor nepotrivite este atribuirea tuturor rândurilor care nu au valori potrivite la Membru necunoscut. Într-un raport

PivotTable, membrul necunoscut se afișează ca titlu necompletat.

De exemplu, în cazul în care creați un raport Pivot Table care trebuie să grupeze vânzările după magazin, însă unele înregistrări din

tabelul de vânzări nu au listat numele magazinului, toate înregistrările fără un nume de magazin valid sunt grupate împreună.

Dacă ajungeți să aveți rânduri necompletate, aveți două variante. Fie definiți o relație de tabel care funcționează, poate prin crearea

unui lanț de relații între mai multe tabele, fie eliminați din raportul PivotTable câmpurile care provoacă apariția rândurilor

necompletate.

http://office.microsoft.com/ro-ro/excel-help/lucrul-cu-relatiile-in-rapoartele-pivottable-HA102836070.aspx?CTT=1#top

25 easy PivotTable reportsShow All

APPLIES TO

Microsoft Excel 2002

Having trouble visualizing how to use a PivotTable© report to analyze your data? This article shows how to start with a basic data

source and create PivotTable reports that answer common questions about your data. While you can use these techniques for just

about any type of source data, the following sections show reports you could use to:

Compare your salespeople Compare your products Compare your orders Compare your customers

To work directly with these PivotTable reports, download Excel 2002 Sample: PivotTable Reports, the companion workbooks for this

article. In addition to copies of all of the source data and reports in the article, the workbooks contain four PivotChart reports that show

you how to present the information graphically.

To make the examples extra easy to follow, they're all formatted with the Report 5 format, which you can apply to your own PivotTable

reports with the Format Report command.

Compare your salespeople

Start with source data that contains information about your salespeople. Here are some of the source data records used for the reports

in this section. To see the entire source range, download the companion workbooks for this article.

To set up your PivotTable:

1. Click a cell in the source data.

2. On the Data menu, click PivotTable and PivotChart Report.

3. Follow the instructions in steps 1 through 3 of the wizard.

NOTE You can lay out the PivotTable report by clicking the Layout button in step 3 of the wizard, or you can lay out the report directly

on the worksheet.

Now you are ready to try the following reports:

What are the order amounts for each salesperson?

What are the order amounts for salespeople in a specific country?

How are salespeople ranked by order amounts?

Who are the top five salespeople?

How did salespeople perform in a specific quarter?

How does each salesperson's performance vary by quarter?

What are the details for a specific order amount?

What percent is a specific order of the total order amounts?

What bonus amount should each salesperson receive?

Compare your products

Start with source data that contains information about your products. Here are some of the source data records used for the reports in

this section. To see the entire source range, download the companion workbooks for this article.






on the worksheet.

Now you are ready to try any of the following reports:

What are the sales totals for each category of product?

What are the sales totals for each product?

What are the three best-selling products in each category?

What are the quarterly sales by product?

How do the sales in the first quarter compare with those in the second?

What are the average, largest, and smallest Beverage sales subtotals?

What is the average sale and minimum sale?

Compare your orders

Start with source data that contains information about your orders. Here are some of the source data records used for the reports in







on the worksheet.

Now you are ready to try the following reports:

How do you view an individual order?

How many units of each product were sold?

What's the average unit price of each product?

What are the ten most expensive products?

Compare your customers

Start with source data that contains information about your customers. Here are some of the source data records used for the reports in







on the worksheet.

PivotTable reports 101Show All

APPLIES TO

Microsoft Office Excel 2003Microsoft Excel 2002

Let's suppose you've compiled a large list of data—for example, sales figures for every product your company makes. You're now ready

to extract some meaningful information from the data, and find answers to questions like:

What are the total sales for each product by region? Which products are selling best over time? Who is your highest-performing salesperson?

To answer these and other questions, you can create a PivotTable® report—an interactive table that automatically extracts, organizes,

and summarizes your data. You can use this report to analyze the data, make comparisons, detect patterns and relationships, and

discover trends.

For example, you can use the data in the following illustration...

...to create a PivotTable report that displays how each product is selling in each sales region, as shown in the next illustration.

The following sections provide an overview and map out a sequential process for effectively working with PivotTable reports.

VLOOKUPShow All

Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the

left of the data that you want to find.

Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value The value to search in the first column of the table array. Lookup_value can be a value or a reference. If lookup_value

is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

Table_array Two or more columns of data. Use a reference to a range or a range name. The values in the first column of table_array

are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are

equivalent.

Col_index_num The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns

the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If

col_index_num is:

Less than 1, VLOOKUP returns the #VALUE! error value. Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct

value. You can put the values in ascending order by choosing the Sort command from the Datamenu and selecting Ascending. For

more information, see Default sort orders.

If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

Remarks

When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not have leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP may give an incorrect or unexpected value. For more information on functions that you can use to clean text data, see Text and Data functions.

When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, VLOOKUP may give an incorrect or unexpected value. For more information, seeConvert numbers stored as text to numbers.

If range_lookup is FALSE and lookup_value is text, then you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

http://office.microsoft.com/en-001/excel-help/redir/HP003055900.aspx?CTT=5&origin=HP005209335




This example searches the Density column of an atmospheric properties table to find corresponding values in the Viscosity and

Temperature columns. (The values are for air at 0 degrees Celsius at sea level, or 1 atmosphere.)

1

2

3

4

5

6

7

8

9

10

A B C

Density Viscosity Temperature

0.457 3.55 500

0.525 3.25 400

0.616 2.93 300

0.675 2.75 250

0.746 2.57 200

0.835 2.38 150

0.946 2.17 100

1.09 1.95 50

1.29 1.71 0

Formula Description (result)

=VLOOKUP(1,A2:C10,2) Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A which is 0.946, and then returns the value from column B in the same row. (2.17)

=VLOOKUP(1,A2:C10,3,TRUE) Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A, which is 0.946, and then returns the value from column C in the same row. (100)

=VLOOKUP(.7,A2:C10,3,FALSE) Using an exact match, searches for the value .7 in column A. Because there is no exact match in column A, an error is returned. (#N/A)

=VLOOKUP(0.1,A2:C10,2,TRUE)

Using an approximate match, searches for the value 0.1 in column A. Because 0.1 is less than the smallest value in column A, an error is returned. (#N/A)

=VLOOKUP(2,A2:C10,2,TRUE) Using an approximate match, searches for the value 2 in column A, finds the largest value less than or equal to 2 in column A, which is 1.29, and then returns the value from column B in the same row. (1.71)

Example 2



This example searches the Item-ID column of a baby products table and matches values in the Cost and Markup columns to calculate

prices and test conditions.

1

2

A B C D

Item-ID Item Cost Markup

ST-340 Stroller $145.67 30%

3

4

5

6

BI-567 Bib $3.56 40%

DI-328 Diapers $21.45 35%

WI-989 Wipes $5.12 40%

AS-469 Aspirator $2.56 45%


= VLOOKUP("DI-328", A2:D6, 3, FALSE) * (1 + VLOOKUP("DI-328", A2:D6, 4, FALSE))

Calculates the retail price of diapers by adding the markup percentage to the cost. ($28.96)

= (VLOOKUP("WI-989", A2:D6, 3, FALSE) * (1 + VLOOKUP("WI-989", A2:D6, 4, FALSE))) * (1 - 20%)

Calculates the sale price of wipes by subtracting a specified discount from the retail price. ($5.73)

= IF(VLOOKUP(A2, A2:D6, 3, FALSE) >= 20, "Markup is " & 100 * VLOOKUP(A2, A2:D6, 4, FALSE) &"%", "Cost is under $20.00")

If the cost of an item is greater than or equal to $20.00, displays the string "Markup is nn%"; otherwise, displays the string "Cost is under $20.00". (Markup is 30%)

= IF(VLOOKUP(A3, A2:D6, 3, FALSE) >= 20, "Markup is: " & 100 * VLOOKUP(A3, A2:D6, 4, FALSE) &"%", "Cost is $" & VLOOKUP(A3, A2:D6, 3, FALSE))

If the cost of an item is greater than or equal to $20.00, displays the string Markup is nn%"; otherwise, displays the string "Cost is $n.nn". (Cost is $3.56)

Example 3



This example searches the ID column of an employee table and matches values in other columns to calculate ages and test for error

conditions.

A B C D E

ID Last name First name

Title Birth date

1 Davolio Nancy Sales Representative

12/8/1968

2 Fuller Andrew Vice President, Sales

2/19/1952

3 Leverling Janet Sales Representative

8/30/1963

4 Peacock Margaret Sales Representative

9/19/1958

5 Buchanan Steven Sales Manager 3/4/1955

6 Suyama Michael Sales Representative

7/2/1963


=INT(YEARFRAC(DATE(2004,6,30), VLOOKUP(5,A2:E7,5, FALSE), 1))

For the fiscal year 2004, finds the age of the employee with ID equal to 5. Uses the YEARFRAC function to

subtract the birth date from the fiscal year end date and displays the result as an integer using the INT function. (49)

=IF(ISNA(VLOOKUP(5,A2:E7,2,FALSE)) = TRUE, "Employee not found", VLOOKUP(5,A2:E7,2,FALSE))

If there is an employee with an ID of 5, displays the employee's last name; otherwise, displays the message "Employee not found". (Buchanan)The ISNA function returns a TRUE value when the VLOOKUP function returns the #NA error value.

=IF(ISNA(VLOOKUP(15,A3:E8,2,FALSE)) = TRUE, "Employee not found", VLOOKUP(15,A3:E8,2,FALSE))

If there is an employee with an ID of 15, displays the employee's last name; otherwise, displays the message "Employee not found". (Employee not found)The ISNA function returns a TRUE value when the

VLOOKUP function returns the #NA error value.

=VLOOKUP(4,A2:E7,3,FALSE) & " " & VLOOKUP(4,A2:E7,2,FALSE) & " is a " & VLOOKUP(4,A2:E7,4,FALSE) & "."

For the employee with an ID of 4, concatenates the values of three cells into a complete sentence. (Margaret Peacock is a Sales Representative.)

NOTE The first formula in the example above uses the YEARFRAC function. If this function is not available, and returns the #NAME?

error, install and load the Analysis ToolPak add-in.

Learning VLOOKUP in Excel

As September approaches, I can count on a series of spreadsheet questions. One of the popular Excel tutorial requests is how do you look up a value on one Excel worksheet and use it on another Excel worksheet. For example, you need to translate a product number into a product name. One of my favorite Excel functions is the VLOOKUP function and it can help with this task. (Includes Excel VLOOKUP Example file and video in reference section.)

A recent case involved some voter registration data I needed to analyze. On one Excel spreadsheet, the voter’s party was listed as an alphanumeric value called "Pcode" and not the political party. This coding wasn't intuitive. For example, “D” was for “American Independent Party”, but some thought it meant “Democratic Party”.

One way to solve this problem is to create a worksheet with the Pcode and translation and have Excel use the VLOOKUP function for the party name. You might think of VLOOKUP as an Excel translator. I could then add a column called “Political Party” to my original worksheet to show the information from a lookup table.

http://www.timeatlas.com/5_minute_tips/general/learning_vlookup_in_excel

http://www.timeatlas.com/images/stories/vlookup2.png

Creating a Lookup Table

A lookup table includes the values you wish to "lookup" such as our Pcode and the translation such as political party. You can place this table on the same worksheet, but for this Excel tutorial I'll add a worksheet called "Political Party".

How to Create a Lookup Table,

1. Right-click your spreadsheet’s tab and select Insert…

2. On the Insert dialog, double-click Worksheet. This will be on the General tab.

3. Rename this new worksheet tab with a descriptive name such as “Party Codes”

4. In Column A, enter the unique values that exist on your main worksheet. In my example, these were the codes that showed in the Pcode column in the thumbnail. These values should be inascending order.

5. In Column B, enter the translated value. You can have more values in column A than appear on your main spreadsheet. For example, I have an entry for “Citizen Party” even though I didn’t show a registered voter with that affiliation.

Using the VLOOKUP Function

Excel’s VLOOKUP function uses 4 pieces of information. The function panel may seem intimidating with the terms, but it’s simpler than it looks. (Note: If you have Excel 2007 or 2010, you can find an additional PDF file with updated screen prints in the resources section at the bottom.)

To lookup a value using VLOOKUP,

1. Add your new column on your original worksheet that will display the info pulled from the Lookup table. In my example, I added a column called Political Party in Column D. This is where I will insert the Excel function.

2. Place your cursor in the first blank cell in that column. In my example, this is cell D2.

3. From the Insert menu, select Function…. The Insert Function dialog will appear.

4. In the Search for a function: text box, type “vlookup” and click Go.

5. Highlight VLOOKUP and click OK.



Defining the VLOOKUP Values

After you click OK, Excel’s Function Arguments dialog appears and allows you to define the four values. You’ll see that your starting cell and the formula bar show the beginning part of the function=VLOOKUP(). The Function Arguments dialog adds the needed data elements that will display between ().

For illustration purposes, I have overlaid the Party Codes worksheet on top to show the relationships.

1. Lookup_value – Think of this field as your starting point. In my example, I’ll click cell C2 so the value is filled in the dialog. I'm requesting Excel take the value of C2, which displays as the Pcode of “A”, and find the matching political party on my lookup table on the Party Codes worksheet.

2. Table_array – This is the range for your lookup table. The range can be on your existing worksheet or another worksheet such as our “Party Codes”. When you click another tab and define the range, Excel prepends that tab name to the range such as ‘Party Codes’.

Rules & Caveats

There are several rules to remember about this table array.


Rule 1 - The left column must contain the values being referenced. In other words, I couldn’t have our first column be Political Party.

Rule 2 - You can’t have duplicate values in the leftmost column of the lookup range. I couldn’t have two entries with the value “A” with one being “Democratic” party and another “A” for the “Humanist” party. Excel would complain.

Rule 3 - When referencing a lookup table, you don’t want your cell references to change when you drag and fill to populate the other cells with the VLOOKUP function. As example, if I want to use the same function in cells D3 through D7, I don’t want my lookup cell references to shift each time I move down to the next cell. I need the cell references to be the same. After you define your range, you need to press F4 which will cycle through absolute and relative references. You want to select the option that includes a $ before your Column and Row. ( 'Party Codes'!$A$2:$B$45. ) You can get around this if you know how to use Excel name ranges.

Col_index_num – This is the number of the column on your lookup table that has the information you need. In our example, we want column 2 from the Party Codes worksheet which has the name of the political party.

Range-lookup – this field defines how close a match should exist between your Lookup_value (C2) and the value in the leftmost column on our lookup table. In our case, we want an exact match so we’ll use “FALSE”.

After clicking various cells, my dialog looks like this:

You can see in the circled formula bar above, I now have more information based on my entries in the Function Arguments dialog box.

The other item of interest is that when you build these functions, Excel displays the result in theFormula result = text line. This is great feedback which can show if your function is on target. In our example, we can see Excel looked up the Pcode of “A” and returned the Political Party “Democratic”.


Copying the VLOOKUP Function to Other Cells

It doesn’t make sense to use VLOOKUP for one cell in your Excel spreadsheet. Instead, I want to copy the function to other cells in the same column.

To copy VLOOKUP to other column cells,

1. Click the cell containing the VLOOKUP arguments. In our example, this would be D2.

2. Grab the cell handle that displays in the lower right corner.

3. Left-click and drag down the cell handle to cover your column range.

Note: If I hadn’t changed to absolute reference as mentioned in Rule 3, I would’ve seen my table array entry shift by one cell as we dragged down through the other cells.

VLOOKUP is a powerful Excel function that can leverage spreadsheet data from other sources. There are many ways you can benefit from this function. In this example, I used a 1:1 code translation, but you could also use it for group assignments. For example, you could assign state codes to a region such as CT, VT, and MA to a region called “New England”. And for the adventurous, you can use VLOOKUP in your Excel formulas.

Tabelele pivot din Excel constituie o caracteristica pe care ar trebui sa o invatati sa o folositi, deoarece, in loc sa analizati nenumarate intrari intr-un

document Excel, un tabel pivot va poate structura informatia dorita in doar cateva click-uri. Veti avea de asemenea posibilitatea de a muta coloane intregi pe

randuri si viceversa. Problema este ca multi dintre noi au impresia ca a crea un tabel pivot este dificil. In schimb, eu va voi demonstra cat este de usor.

Ce este un tabel pivot?

Ai putea sa te gandesti la un tabel pivot ca la tabel-rezumat al foii de calcul originale, creat de utilizator. Tu creezi tabelul prin deciziile pe care le iei in

legatura cu diferite campuri pe care vrei sa le vezi si cum sa fie afisata informatia. Bazandu-se pe selectiile de campuri, Excel va organiza datele in asa fel

incat tu vei putea avea o alta infatisare a tabelului de fiecare data.

Ca exemplu, am uploadat un fisier Excel cu 200 de votanzi fictivi, care include urmatoarele field-uri de date:

numar curent

partidul afiliat

sectia de votare

grupa de varsta

data cand au votat ultima oara

anii de cand au fost inregistrati

Urmarind, spre exemplu, primele 20 de intrari din tabel, se poate vedea foarte usor cat de plictisitor este structurata informatia in sine, dar si faptul ca singura

informatie pe care o putem obtine din acest tabel, in acest moment este: cati votazi sunt in total, in toate sectiile de votare.

Insa, folosind un tabel pivot, poti face o analiza mult mai detaliata a datelor, analiza care sa cuprinda, spre exemplu, raspunsul la intrebarile:

1. numar curent

2. partidul afiliat

3. sectia de votare

4. grupul de varsta

5. cand au votat ultima oara

6. anii de cand au fost inregistrati

Un tabel pivot te va lasa sa grupezi foaia de calcul sau surse de date externe, in functie de orice data field. Imaginea de mai jos, iti va da posibilitatea de a

numara in functie de partid si de sectie.

http://www.e-learn.ro/includes/tutorials/business_aplication/ms_excel/515/images/1mare.jpg

Folosind un tabel pivot, poti continua sa fragmentezi informatia detinuta, pana in momentul in care vei avea in fata raportarea dorita, cu ajutorul selectiei

campurilor aditionale dorite din Pivot Table Field List. De exemplu, putem folosi aceleasi date pentru a segmenta informatia dorita per grupa de varsta


Sa intelegem structura unui Tabel Pivot

Daca ne uitam la imaginea de mai sus, vom vedea ca un tabel pivot are 3 sectiuni importante:

1. Pivot Table Filed List ��" aceasta sectiune, aflata in partea de dreapta sus, iti arata toate campurile continute de foaia ta de calcul.

2. Casutele din dreapta ��" jos ��" aceasta sectiune defineste unde si cum vor arata informatiile in foaia ta de calcul. Poti face ca un camp sa fie aratat in

rand sau in coloana. De asemenea, poti cere ca datele respective sa fie adunate, inmultite, filtrate, s.a.m.d.

3. Area tabelara din foaia de calcul, care este rezultatul imbinarii ariilor 1 si 2.

Cum sa creezi un Tabel Pivot

Sunt mai multe metode de a crea un tabel pivot. Excel-ul are o logica prin care stie de ce tip este un anumit camp si va incerca sa il plaseze in coloana sau

randul corect, in cazul in care bifezi casuta respectiva. De exemplu, datele numerice, cum ar fi campul „Sectie” in cazul nostru, va aparea in dreptul

coloanelor, iar datele textuale, ca „Partidul” va aparea pe un rand.


Desi poti sa bifezi doar campurile pe care vrei sa le vezi in tabelul pivot, si sa lasi Excel-ul sa iti construiasca tabelul dorit, eu prefer sa folosesc metoda „drag

and drop”. Si asta deoarece imi place sa vizualizez datele in coloane si randuri:

1. Deschide foaia de calcul originala si scoateti din ea orice camp blank.

2. Asigura-te ca fiecare coloana are un titlu, deoarece acesta va fi transpus in Field List-ul tabelului.

3. Asigura-te ca fiecare celula este formatata corecta pentru tipul in care se incadreaza.

4. Evidentiaza campul pe care doresti sa il vizualizezi.

5. Cick pe tab-ul Insert.

6. Selecteaza butonul Pivot Table din grupul Tables.

7. Selecteaza din lista Pivot Table.

8. Check Table/Range: value.

9. Selecteaza New Worksheet.

10. Click pe OK

Comenzile Macro reprezinta o unealta Excel ce este utilizata atunci cand actiunile tale trebuiesc repetate asupra unor celule sau chiar a unor foi de calcul

diferite. In principiu, aceste comenzi iti permit inregistrarea si apoi repetarea actiunilor tale.

Definirea si executarea unui macro

Sa presupunem ca avem un tabel similar celui de mai jos:

Mergi la View > Macros > Record Macro.

In fereastra care se deschide, seteaza un nume de Macro, tastele ce vor activa executarea acestuia, locatia in care va fi salvat si o scurta descriere a actiunilor

pe care le va realiza.

Apasa OK pentru a incepe inregistrarea actiunilor. In continuare, toate operatiile pe care le efectuezi in foaia de calcul vor fi retinute in macro.

Selecteaza Pivot Table Report, apoi click dreapta si selecteaza Refresh.

Opreste inregistrarea, mergi in tabel si schimba niste valori in coloana Space.

Selecteaza iar View > Macro > View Macros.

Selecteaza macro-ul pe care l-ai definit anterior (in acest exempluSampleMacroForPivotUpdate) si apasa butonul Run pentru a-l executa.

In acest mod, macro-ul va reinnoi datele din tabelul pivot.

Editarea unui macro

Aceasta sectiune se adreseaza utilizatorilor ce doresc sa editeze macro-ul direct din codul acestuia.

Selecteaza iar View > Macros > View Macros si apasa Edit pe macro-ul creat mai sus -SampleMacroForPivotUpdate. Se va deschide editorul de

cod VBA (Visual Basic for Applications), ce va afisa codul macro-ului.

Sterge ultimele doua randuri din cod.

Scrie un mesaj de informare adaugand o noua linie de cod.

MsgBox "Pivot Table data has been refreshed", vbExclamation + vbInformation, "Macro Finished"

http://www.e-learn.ro/includes/tutorials/business_aplication/ms_excel/16/7.jpg


Inchide editorul si ruleaza macro-ul din nou pentru a vedea rezultatele.

Mergi iar la View > Macros >View Macros, selecteaza macro-ulSampleMacroForPivotUpdate, apoi apasa Step Into.

Editorul VBA se va deschide iar si codul macro va fi afisat ( numele macro-ului are fundal galben).


Apasa tasta F8. Cursorul va avansa pe noua linie executabila. Apasa tasta F8 de inca 2 ori si ruleaza "propozitia" Refresh.

Efectueaza debug pe fiecare linie pentru rezultatele finale, apoi inchide editorul de codVBA.



excel

Documents