excel 2007 avansati
Post on 14-Jul-2015
2.686 Views
Preview:
DESCRIPTION
TRANSCRIPT
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 1/124
Capitolul 1 _______________________________________
GRAFICE
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 2/124
Crearea Graficelor
Graficele din Excel 2007 diferă foarte mult de cele din versiunile anterioare de excel.
În primul rând, noul Ribbon vă permite formatarea şi personalizarea graficelor cu doar
câteva apăsări de mouse şi cu câteva casete de dialog. Deasemenea, deoarece Excel
2007 utilizează în comun graficele cu celelalte aplicaţii ale pachetului Office, importul
acestora in Microsoft Word 2007 sau Microsoft Power Point 2007 se poate face mult mai
simplu.
Selectarea Datelor pentru Grafic
Primul pas în crearea unui grafic constă în selectarea datelor. Dacă doriţi să faceţi un
grafic folosind datele dintr-un tabel este suficient să selectaţi doar o celulă din tabelulrespectiv, dar, dacă doriţi să creaţi graficul folosind anumite rânduri sau coloane din
tabelul de date atunci, selectaţi zonele respective.
Alegerea Tipului De Grafic
După ce aţi selectat datele, trebuie să alegeţi tipul graficului. Pentru a vedea modelele
disponibile activaţi tab-ul Insert de pe ribbon. Grupul ce conţine tipurile de grafic senumeşte Charts:
Fiecare tip de grafic standard din grupul Charts are la rândul lui o galerie de subtipuri.
Pentru a vizualiza o galerie a unui tip de grafic, doar daţi click pe modelul preferat şi seva afişa lista de opţiuni.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 3/124
Odată creat graficul pe Ribbon vor apărea încă trei tab-uri, sub Chart Tools , şi anume: Design, Layout şi Format.
Dacă doriţi să schimbaţi tipul graficului urmăriţi paşii:
Daţi click în orice parte a graficului
Apoi de pe tab-ul Design daţi click pe butonulChange Chart Type din grupul Type
Caseta de dialog care apare include toate tipurile de grafice din Excel, inclusiv
subtipurile acestora.
Schimbând Rândurile şi Coloanele
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 4/124
Ar fi foarte bine dacă Excel ar şti întotdeauna să vă creeze un grafic din prima încercare
care să corespundă dorinţelor dumneavoastră. Din nefericire, deşi programul este
inteligent, nu este clarvăzător. Una din problemele pe care nu le prevede reprezintă
seriile şi datele. De exemplu, presupunem că avem tabelul de valori din imagine:
Folosind acest tabel de valori doriţi să creaţi un grafic de tip coloane în două dimensiuni.
Dacă urmaţi primii doi paşi descrişi mai sus pentru a crea acest grafic, acesta o să arate
ca în imaginea de mai jos:
Acest grafic reprezintă vânzarile celor trei ani pe cele două oraşe. Dacă doreaţi de fapt să
reprezentaţi Vănzările realizate de cele două oraşe pe cei trei ani, atunci:
Selectaţi graficul, dând click pe orice parte din grafic
De pe tab-ul Design , din grupul Data, daţi click pe butonul Switch Row/Column
Atunci graficul arată astfel:
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 5/124
Alegerea formatului pentru grafic(Chart Layout)
Chart Layout se referă la combinaţia de variante afectând elemente ca titlul graficului
(Chart Title), legenda (Legend), axele (Axes), titlul axelor(Axis Title) şi grilele din spatele
graficului(Gridlines). Fiecare tip de grafic ales, conţine câte un pachet de formaturi
(Chart layouts).
Având graficul selectat, puteţi vedea layout-urile accesibile dând click pe butonul Design,
sub tab-ul Cahat Tools de pe Ribbon, şi apoi deschizând galeria Chart Layout. Înimaginea de mai jos puteţi vedea galeria de layout pentru tipul de grafic linie:
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 6/124
Alegerea unui stil pentru grafic(Chart Style)
Stiulu unui grafic se fereră la combinaţia dintre imaginea de prim plan şi fondul de culori
creat pentru a se asorta cu stilul celulelor , al obiectelor grafice, şi al tabelelor pentru a
da foii de calcul un aspect profesional.
Pentru a aplica un stil nou unui grafic, selectaţi graficul, alegeţi Design de sub tab-ul
Chart Tools de pe Ribbon, şi apoi deschideţi galeria Chart Styles.
Mutarea graficului pe o foaie separată
Dacă nu aveţi nevoie ca datele şi graficul făcut pe baza datelor să fie pe aceeaşi foaie
de calcul, mai aveţi şi varianta de a avea graficul pe o foaie de calcul separată. Această
foaie de calcul pentru grafic are un format special, astfel încât tot spaţiul foii este
destinat doar graficului.
Pentru a muta graficul pe o altă foaie de calcul:
Daţi click-drepata pe grafic şi alegeţi opţiunea MoveChart
Apare caseta de dialog de mai jos, şi alegeţi opţiunea New sheet
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 7/124
Sau
Selectaţi graficul, şi daţ click pe butonul Move Chart, de pe tab-ul Design
Apare aceeaşi casetă de dialog de mai sus, Move Chart
Selectaţi opţiunea NewSheet, eventual schimbaţi numele foii şi acţionaţi butonul
OK
Formatarea avansată a graficelor
Crearea unui grafic presupune alegerea datelor care vor fi reprezentate, tipul de
grafic care va evidenţia cel mai bine datele alese şi, nu în ultimul rând, personalizarea
elementelor graficului (alegerea unui fundal, a elementelor grafice, axelor, liniilor de
tendinţă) în funcţie de preferinţele utilizatorilor.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 8/124
Pentru a deschide lista obiectelor graficului selectaţi mai întâi un obiect al acestuia
apoi daţi clic în tab-ul Layout sau Format de pe Ribbon.Lista obiectelor graficului apare
sub forma unei liste derulante imediat sub butonul Microsoft Office în colţul din stânga
sus al ferestei Excel. Iniţial apare numele obiectului pe care l-aţi selectat iniţial. Pentru a
vedea întreaga lista de obiecte daţi clic pe săgeata din dreptul casetei. Puteţi alege
oricare element din lista pentru a-l formata.
După ce aţi selectat un element din lista puteţi deschide caseta de dialog pentru
formatarea acestuia printr-un clic pe opţiunea Format Selectionsituată imediat sub lista
derulantă a obiectelor din partea stângă a Ribbon-ului.
Horizontal
(Category) Axis
Plot Area
Vertical (Value)
Axis
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 9/124
Repoziţionarea obiectelor cu ajutorul mouse-ului Puteţi muta titlul, casetele cu numele axelor, ale seriilor de date sau legenda doar
tragându-le cu mouse-ul. Puteţi utiliza mouse-ul pentru a „exploda” un sector al unui
grafic de tip diagrama circulară. Pentru a restaura poziţia iniţială a elementelor imediat
după ce le-aţi mutat, apăsaţi Ctrl+Z.
Formatarea liniilor, chenarelor şi suprafeţelor Excel utilizează linii pentru axe, grafice de tip liniar şi linii de tendinţă. De asemenea
pentru chenarele diferitelor obiecte grafice care compun reprezentarea: chenare pentru
chart area, plot area, pentru legendă şi chiar pentru barele sau coloanele din cadrul
graficului. Opţiunile de formatare pentru linii sunt în general aceleaşi pentru toatesituaţiile enumerate mai sus. Puteţi modifica grosimea liniilor, culoarea, stilul (linie
continuă, simplă, dublă, punctată), etc.
Formatarea liniilor se realizează astfel:
- selectaţi obiectul pentru care doriţi să schimbaţi aspectul liniei - alegeţi opţiunea Format Selection (spre exemplu Format Axis) - din lista de opţiuni alegeţi Line Color sau Line Style în funcţie de ceea ce doriţi să
modificaţi
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 10/124
În acelaşi mod, selectând din listă puteţi modifica fundalul diverselor obiecte aplicând
o culoare, o combinaţie de culori (gradient), o textură sau o imagine salvată într-un fişier
alegând opţiunea Fill.
Lucrul cu axele Pe lângă aspectul liniilor puteţi modifica în această situaţie şi alte elemente componente
ale axelor cum ar fi:
- marcatorii de linie (liniile care împart axele în intervale regulate şi care pentruaxa valorilor determină scala valorilor iar pentru axa categoriilor separăcategoriile)
- formatul numerelor (din fereastra Format Axis alegeţi opţiunea Number)
- scala (din fereastra Format Axis alegeţi Axis Options şi aveţi posibilitatea de aseta valoarea minimă, maximă a scalei, lungimea intervalelor în care va fi
împărţită aceasta şi tipul scalei: liniar sau logaritmic)
Formatarea axei categoriilor
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 11/124
În cazul în care axa categoriilor se bazează pe celule ce conţin date calendaristice,
Excel utilizează scala de tip Date în mod implicit. Dacă opţiunile dumneavoastră diferă de
cele conţinute în celulele utilizate pentru a denumi categoriile, puteţi să le personalizaţi
utilizând Axis Options.
În cazul graficelor tridimensionale se utilizează o a treia axă numita axa de
perspectivă (depth) care conţine numele seriilor şi care de asemenea poate fi formatată.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 12/124
Formatarea seriilor de date
Asignarea axei secundare unei serii de date
Utilizarea axei secundare este necesară în cazul în care seriile de date conţin date ce
nu pot fi reprezentate cu aceelaşi tip de date (de exemplu: cantitate vândută şi valoarea
vânzărilor) sau când valorile unei serii sunt foarte mari (tendinţa vanzarilor anuale
comparativ cu vânzările lunare pe produse)
$0
$10,000
$20,000
$30,000$40,000
$50,000
$60,000
$70,000
$80,000
$90,000
$100,000
Total
Jan
Prin introducerea axei secundare pentru seria Jan şi alegerea unui alt tip de grafic, se
poate urmări mult mai uşor variaţia valorilor.
Pentru introducerea axei secundare se selectează seria pentru care dorim sa aplicăm
formatarea fie din lista obiectelor graficului fie cu un clic pe una din coloanele (data
point) corespunzătoare acestei serii. Un al doilea clic va selecta numai un element al
seriei de date. După selectarea întregii serii (apare un mic punct în dreptul fiecărei
coloane) şi alegerea opţiunii Format Selection apare fereastra format Data Series şi din
Series Options alegem opţiunea Secondary Axis.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 13/124
Dacă schimbăm şi tipul graficului pentru una din serii (clic dreapta, chart type şi
alegem un alt tip de grafic), graficul nostru va arăta astfel:
$0
$2,000
$4,000
$6,000
$8,000
$10,000
$12,000
$0
$10,000
$20,000
$30,000
$40,000
$50,000
$60,000
$70,000
$80,000
$90,000
$100,000
Jan
Total
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 14/124
Adăugarea unei linii de tendinţă pentru o serie
Liniile de tendinţă sunt utilizate pentru a afişa grafic tendinţele în date şi pentru a
analiza probleme de prognoză. Astfel de analize sunt intitulate de asemenea şi analize
de regresie. Utilizând analize de regresie, se poate extinde o linie de tendinţă într-odiagramă peste datele actuale pentru a prognoza valori viitoare( prin utilizarea opţiunii
forward sau backward din secţiunea Forecast din fereastra Format Trendline, Trendline
Options).
Pentru adăugarea unei linii de tendinţă:
-selectaţi o serie de date
- clic dreapta şi selectaţi Add Trendline.
-Se deschide fereastra Format Trendline în care se alege tipul liniei (exponenţial,
liniar, polinomial,logaritmic, polinomial, putere sau medie mobilă) în funcţie de
repartizarea datelor
- se poate denumi linia de tendinţă, se poate prelungi pentru a prognoza valoriviitoare
- de asemenea, prin alegerea opţiunii de afişare a coeficientului de determinare (R-
squared) a abaterii medii pătratice se poate găsi cea mai bună alegere a tipului de linie(
cu cât valoarea acestuia este mai apropiată de 1 cu atât aproximarea este mai exactă)
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 15/124
Exerciţii
1) Deschideţi foaia de calcul Departamente din fişierul Angajaţi.
2) Realizaţi un grafic de tip column pe baza tabelului
Total
salarii
Nr.
Angajati
Resurse umane 15850 5
Marketing 118550 35
IT 149350 42
Contabilitate 87600 26
3) Introduceţi ca titlu “Salarii pe număr de angajaţi” deasupra graficului (Above
chart)
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 16/124
4) Formataţi graficul astfel :
Alegeţi oimagine de fundal sau o textură pentru zona graficului (Chart area)
Renunţaţi la fundalul pentru zona graficului (Plot area)
Introduceţi pentru seria Nr. angajaţi o axă secundară şi schimbaţi tipul graficuluipentru această serie alegând opţiunea Line
Mutaţi legenda în partea de jos a graficului
Introduceţi pentru axele verticale câte un titlu: pentru axa principală “Lei” iarpentru axa secundară “Nr. Angajaţi”
1) Deschideţi foaia Total vanzari din fişierul Centralizator facturi
2) Realizaţi un grafic pe baza datelor din tabel şi adăugaţi o linie de tendinţă pentru
a evidenţia tendinţa pentru anii următori
3) Pentru aceasta: selectaţi datele cuprinzând în selecţie şi liniile incomplete
corespunzătoare anilor 2009 şi 2010
4) Clic pe seria de date şi clic dreapta Add Trendline. Selectaţi linia de tendinţă care
consideraţi că aproximează cel mai bine modelul sugerat de repartiţia datelorbifând şi opţiunea de afişare a coeficientului de determinare (R-squared) a
abaterii medii pătratice.
5) Analizaţi diversele tipuri de linii de tendinţă şi alegeţi-o pe cea mai potrivită
6) Pentru a afişa pe axa orizontală valorile care reprezintă anul, daţi clic dreapta pe
zona graficului şi alegeţi opţiunea Select Data Source. Se deschide fereastra cu
acelaşi nume şi în zona Horizontal (Category) Axis Labels daţi clic pe butonul Edit
şi alegeţi apoi din table domeniul care conţine anii.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 17/124
7) Mutaţi graficul pe o nouă foaie de calcul de tip Chart pe care o denumiţi
“Tendinte pe grafic”.
8) Pentru aceasta utilizaţi butonul “Move Chart Location” din partea dreaptă a
Ribbon-ului în fila Design
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 18/124
Capitolul 2 _______________________________________
BAZE DE DATE
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 19/124
Gestiunea informaţiilor în tabele
În Excel 2007 s-au introdus o serie de facilităţi pentru manipularea datelor cum ar fi :
- Organizarea datelor în tabele.Spre deosebire de ediţiile anterioare, Excel 2007permite definirea unei liste ca tabel. În acest fel, organizarea datelor se apropiemult mai mult de organizarea specifică programelor de gestiune a bazelor dedate (definirea coloanelor ca şi câmp şi a liniilor ca şi înregistrări). Acesta fiind de
fapt unul din aspectele caracteristice acestei versiuni: o mai bună organizare adatelor pentru export şi import în/din alte surse.
- Îmbunătăţirea posibilităţilor de sortare. Sortareadatelor nu se mai limitează latrei criterii. Puteţi realiza sortări după câte criterii doriţi
- Autoexpandarea. Dacă adăugaţi o linie sau o coloană formatările condiţionate,formulele de calcul, regulile de validare se extind pentru linia sau coloana nouă.Graficele construite pe baza datelor din tabel sunt de asemenea actualizate
- Referinţe structurate. În formulele care adresează elemente ale unui tabel se pot
folosi nume de coloane sau alte etichete în locul obişnuitei adresări de celule şidomenii
- Îmbunătăţirea posibilităţilor de filtrare. Filtrarea se poate face după criteriimultiple, după setul de culori al celulelor, după un icon ataşat acestora prinformatarea condiţionată. Se pot utiliza de asemenea filtre bazate pe definiţiadinamică a datelor cum ar fi săptămâna trecută sau trimestrul curent.
- Replicarea formulelor. Dacă adăugaţi o coloană în care se realizează un calculbazat pe datele din tabel excel extinde metoda de calcul automat, pentru toatacoloana.
- Ştergerea duplicatelor.Cu o simplă comandă se pot evidenţia dublurile (liniile)
sau se pot şterge. - Stiluri de tabele. Introducerea unei biblioteci de stiluri oferă utilizatorului
posibilitatea aplicării unui format deosebit şi consistent tabelului de date.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 20/124
Sortarea datelor
Pentru a sorta rândurile unui tabeldupă o coloană (Nume, spre exemplu) se
selectează o celulă de pe acea coloană după care se dă clic pe butonul Sort A to Z din
grupul Sort & Filter din fila Data, sau Sort Z To A pentru ordine inversă. Sortarea se poate
realiza de asemenea cu clic dreapta în celula selectată şi alegerea opţiunii dorite din
meniul Sort.
Puteţi sorta după mai multe coloane utilizând butonul Add Level dincaseta de dialog
Sort pe care o deschideţi apelând comanda sort din grupul Sort & Filter al filei Data.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 21/124
Puteţi sorta de asemenea şi coloanele unui tabel după valorile unui rând. Pentru
aceasta este necesară renunţarea la formatul de tip tabel, altfel opţiunile suplimentare
din fereastra sort sunt inactive. După reconversie opţiunea Sort left to right devine
activă.
- clic pe butonul Options... din fereastra Sort- selectaţi opţiunea Sort left to right - adăugaţi rândul după care doriţi să se facă sortarea şi direcţia acesteia
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 22/124
Filtrarea datelor
Filtrele sunt extrem de utile când se lucrează cu liste mari. Filtrele permit să se
afişeze numai datele cu care se doreşte să se lucreze.
Când creaţi un tabel, Excel adaugă automat filtre pentru capul de tabel. Pentru a
activa sau pentru a renunţa la aceste filtre, daţi clic într-o celulă oarecarea tabelului şi
apoi pe butonul Filter din grupul Sort & Filter din fila Data.
Când sunt active un clic pe sageata din dreptul numelui coloanei afişează o listă cu
valorile acelei coloanescrise o singură dată. Se poate aplica astfel un filtru de afişare a
tuturor înregistrărilor.
Aveţi posibilitatea să utilizaţi filtrarea după mai multe coloane. Filtrele sunt aditive,adică fiecare filtru suplimentar se bazează pe filtrul curent şi restrânge mai departe
subsetul de date.
Utilizând Filtrare automată, aveţi posibilitatea să creaţi trei tipuri de filtre: după o
listă de valori, după format sau după criterii. Fiecare dintre aceste tipuri de filtre le
exclude pe celelalte pentru fiecare zonă de celule sau etichetă de coloană. De exemplu,
aveţi posibilitatea să filtraţi după o culoare de celulă sau după o listă de numere, dar nu
după ambele; aveţi posibilitatea să filtraţi după pictograme sau după un filtru
particularizat, dar nu după ambele.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 23/124
Toate aceste opţiuni pe care le oferă filtrarea automată se aplică datelor de pe
coloana pe care se selectează filtrul. În cazul în care datele dintr-o coloană trebuie să
îndeplinească o condiţie care să ţină cont şi de valorile dintr-o altă coloană există
posibilitatea utilizării unui filtru avansat. Pentru aceasta activaţi comanda Advanced din
grupul Sort & Filter al filei Data.
În urma acţionării acestei comenzi apare fereastra:
Se stabileşte o zonă separată pentru definirea criteriilor de selecţie - Criteria Range.Lista (baza de date) va fi aleasă ca domeniu - List Range.
În mod normal, zona criteriilor de selecţie va fi compusă dintr-o serie de linii subbaza de date (listă)
În zona de criterii denumirile coloanelor trebuie să fie aceleaşi (nu toate şi nuneapărat în aceeaşi ordine) ca şi cele din lista iniţială (baza de date); pentru aceastase recomandă ca acestea să fie copiate
Se introduc criteriile de selecţie Pentru a afişa înregistrările care răspund unui număr de posibilităţi pentru acelaşi
câmp, acestea se introduc pe linii separate de ex. se introduc: "C*" şi"B*" pe liniiseparate în aceeaşi coloană pentru a se afişa înregistrările, care pentru acestcâmp încep cu secvenţele de mai sus
Când se utilizează criterii multiple:
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 24/124
Criteriile introduse pe aceeaşi linie înseamnă că trebuie satisfăcute întotalitate
Pentru a afişa înregistrările care răspund oricăruia dintre anumite criterii,acestea se introduc pe linii separate.
Să nu se introducă rânduri goale (blank rows) în domeniul - criteriu, aceasta va derutaExcel-ul în căutarea criteriilor!
Rezultatul căutării poate rămâne în acelaşi loc, ca şi în cazul AutoFilter , sau se poatecopia în altă zonă.
Dacă se doreşte copierea în altă zonă, va fi uşor dacă se vor defini câmpurile exact cacele din lista iniţială. (Atenţie, să nu se suprapună datele).
Se apasă butonul OK pentru efectuarea operaţiei.
Exemple de criterii:
1. Pentru a găsi rândurile care satisfac unul din două seturi de condiţii, unde fiecare set include
condiţii pentru mai mult de o coloană, tastaţi criteriile în rânduri separate. De exemplu,
următoarea zonă de criterii afişează rândurile care conţin şi „Popescu Ioana” şi valorile
vânzărilor mai mari de 3.000 LEI şi afişează, de asemenea, rândurile pentru vânzătorul „Goga
David” cu valorile vânzărilor mai mari de 1.500 LEI.
Nume Prenume Vanzari
Popescu Ioana >3000
Goga David >1500
Între valorile scrise pe rând se subânţelege existenţa operatorului logic AND („şi”logic) iar între cele scrise pe aceeaşi coloană operatorul logic OR („sau” logic)
În interpretarea unui astfel de criteriu se ţine cont de precedenţa operatorilor
(conjuncţia logică are întâietate faţa de disjuncţie) astfel că putem interpreta criteriul de
mai sus după următoarea formulă:
(Nume=Popescu AND Prenume=Ioana AND Vanzari>3000) OR (Nume=Goga AND
Prenume=David AND Vanzari>1500)
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 25/124
1) Dacă dorim pentru un anumit câmp să afişăm valori dintr-un interval numeric (de
exemplu: să se afişeze vânzările cuprinse în intervalul 1500-2500) se mai scrie odată
numele coloanei căreia îi aplicăm o a doua limită şi criteriul nostru va arăta astfel:
Vanzari Vanzari
>1500 <2500
2) Se poate utiliza drept criteriu o valoare calculată ca rezultat al unei formule .Reţineţi următoarele aspecte importante:
Formula trebuie evaluată ca TRUE sau FALSE.
Deoarece utilizaţi o formulă, introduceţi formula ca de obicei şi nu tastaţiexpresia ca mai jos:
=''=intrare''
Nu utilizaţi etichete de coloană drept etichete de criterii; fie lăsaţietichetele de criterii necompletate, fie utilizaţi o etichetă care nu este oetichetă de coloană din zonă.
Dacă utilizaţi în formulă o etichetă de coloană în locul referinţei relative lacelule sau în locul numelui unei zone, Excel afişează în celula care conţinecriteriul o valoare de eroare, cum ar fi #NAME? sau #VALUE!. Aceastăeroare se poate ignora deoarece nu afectează modul de filtrare a zonei.
Formula utilizată pentru criterii trebuie să utilizeze o referinţărelativăpentru a face referire la celula corespunzătoare din primul rând (înexemplele de mai jos, C7 şi A7).
Toate celelalte referinţe din formulă trebuie să fie referinţe absolute. La introducerea(selectarea) zonei de criterii se precizează şi celula vidă de
deasupra formulelor din criterii (face parte din logica realizării condiţiilordin criterii).
Exemplu: Să se afişeze lista angajaţilor care îşi sărbătoresc ziua de naştere în lunacurentă.
Vom utiliza următorul criteriu:
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 26/124
În zona Criteria Range se va trece domeniul A1:A2.
Dacă dorim ca la afişarea rezultatelor să nu se treacă toate datele din tabel, copiemnumele câmpurilor care ne interesează( în cazul nostru Nume, Prenume, Vârsta) şi în
zona Copy to se menţionează domeniul format din celulele care conţin numele
coloanelor pentru care dorim rezultatul.
În cazul nostru vom obţine:
Nume Prenume Varsta
Achim Madalina 52
Alexandru Benjamin 44
Chiroiu Gelu 38
Dinu Raluca 57
Goga David 42
Oprea Tudor 57
Pana Andrei 40
Popescu Catalina 39
Rosca Mihai 31
Stan Alin 32
Orice modificare adusă tabelului sursă nu se va reflecta şi în tabelul rezultat în urma
interogării. Pentru a se vedea modificările făcute în tabelul de bază şi în tabelul rezultat
trebuie să refaceţi filtrarea.
Eliminarea înregistrărilor duplicate
În versiunile anterioare o modalitate de eliminare a înregistrărilor duble era utilizareaopţiunii Unique Records Only din fereastra Advanced Filter.
=MONTH(tabel_angajati!C2)=MONTH(TODAY())
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 27/124
Acum este mult mai uşor de realizat acest lucru prin acţionarea unei simple comenzi dingrupul Data tools a filei Data.
Pentru a şterge complet duplicatele, selectaţi o celulă din listă sau din tabel şi apoiacţionaţi comanda Remove Duplicates. În caseta de dialog care apare lăsaţi bifate toatecasetele corespunzătoare coloanelor.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 28/124
Excel vă va afişa câte duplicate a găsit şi câte rânduri au rămas.
Pentrua şterge dublurile parţiale se recomandă salvarea unei copii a tabelului (dacă este cazul) apoi se sortează coloanele în care nu doriţi valori duble. Sortarea după coloanelecare conţin dubluri permite ca după eliminarea duplicatelor să se păstreze prima
înregistrare din lista dublurilor.
Spre exemplu, doriţi să afişaţi decanul de vârsta pentru fiecare nivel de salarizare. Sortaţiîntâi câmpul salariu descrescător şi apoi Vârsta descrescător. În fereastra RemoveDuplicates clic pe butonul Unselect All, apoi selectaţi coloana pentru care doriţi săeliminaţi duplicatele, în cazul nostru, Salariul.
Vor fi eliminate rândurile cu înregistrările care au aceeaşi valoare pe câmpul „Salariul” şirămân doar primele din fiecare grup.
Organizarea ierarhică a foii de calcul
Dacă aveţi o listă de date pe care doriţi să o grupaţi şi să o rezumaţi, aveţi posibilitatea săcreaţi o schiţă cu până la opt nivele ierarhice, cu un nivel pentru fiecare grup. Fiecarenivel interior, reprezentat de un număr mai mare în simbolurile ierarhice (simboluri carese utilizează pentru a modifica aspectul unei foi de lucru. Se pot arăta sau ascunde datelede detaliu apăsând pe semnul plus, semnul minus şi pe numerele 1, 2, 3 sau 4, indicând
nivelul de detaliere) afişează datele de detalii . Pentru subtotaluri automate şi schiţe dintr -o foaie de lucru, rândurile sau coloanele subtotal care sunt totalizate de datele rezumat.Datele detaliu sunt în mod tipic adiacente datelor rezumat şi sunt situate deasupra sau lastânga lor. Utilizaţi o schiţă pentru a afişa rapid rezumate de rânduri sau coloane oripentru a afişa datele de detalii pentru fiecare grup. Aveţi posibilitatea să creaţi o schiţă derânduri, o schiţă de coloane sau o schiţă de rânduri şi coloane.
Comenzile de grupare pe nivele ierarhice sunt disponibile în grupul Outline din fila Data.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 29/124
Pentru gruparea manuală a liniilor sau coloanelor se sortează linia (coloana) după care
doriţi să se facă gruparea, se selectează cele care formează un grup după care apăsaţi
butonul Group. Degruparea se realizează cu butonul Ungroup.
Din caseta de dialogcare apare selectaţi rânduri sau coloane în funcţie de ce anume
doriţi să grupaţi; (dacă selectaţi un rând sau o coloană întreagă, nu va apare această
casetă de dialog)
Dacă sintetizarea dumneavoastră arată altfel decât se aşteaptă Excel, utilizaţi comanda
Settings pentru a comunica programului Excel cum sunt organizate datele şi să creaţisintetizarea.
Selectaţi direcţia în care sunt organizate datele, dacă doriţi să aplicaţi stiluri, validaţi
caseta ; alegeţi .
Subtotaluri
Excel poate foarte uşor să genereze subtotaluri dintr-o listă numerică de date (de
ex.Coloana Quantity din fişierul ExExcel2007 , foaia de calcul Comenzi)
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 30/124
Pentru a genera subtotaluri:
o
Întâi se sortează după categoria după care să se facă acestea o Se selecteează orice celulă în listăo Se selectează Subtotal din grupul Outline al filei Datao Va apărea dialogul următor:
o În At Each Change in: se specifică pentru ce se doreşte să se facă subtotaluri.Acesta este motivul pentru care lista trebuie întâi sortată.
o Se poate stabili ce funcţie să se utilizeze în opţiunea Use Function,
o După ce se fac subtotalurile se poate utiliza AutoFormat pentru a modifica înfăţişarea listei finale.
o Opţiunea Add Subtotal to: permite definirea câmpurilor numerice pentru carese fac subtotaluri
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 31/124
o Dacă se selectează opţiunea Replace Current Subtotals, vor fi afişate numelenoii linii de subtotaluri. Pentru a le adăuga la liniile existente (de ex. se doreşteafişarea sumei şi numărului de înregistrări), se dezactivează această opţiune.
o Se selectează opţiunea Check the Page Break Between Groups, dacă sedoreşte ca fiecare grup să fie afişat la imprimantă pe o pagină nouă.
o Dacă se selectează Summary Below Data, (opţiune implicită) liniile de totaluri
apar sub liniile de detaliu.
Subtotalurile nu se recalculează când se filtreză o listă. De aceea se
recomandă să se facă filtrarea înaintea subtotalurilor.
Simbolurile de evidenţiere (outline) a subtotalurilor
După generarea subtotalurilor, Excel afişează o serie de simboluri sau butoane în
partea stângă a foi de calcul. Aceste butoane se pot folosi pentru a ascunde sau a afişa
diferitele nivele de detaliu în foaia de calcul.
Butoanele de nivel indică diferite valori de detaliu:
Nivelul 1- afişează numai totalul general (grand total)
Nivelul 2 - afişează subtotalurile
Nivelul 3 - afişează întraga listă
La nivelul 2 ecranul poate arăta astfel:
Butonul de Expandare indică existenţa unor detalii şi apăsându-l ramura
corespunzătoare se va detalia (expanda)
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 32/124
Butonul de Collapse poate fi utilizat pentru a ascunde liniile marcate din dreptul
liniei corespunzătoare. În exemplul de mai sus va rămâne numai totalul general.
Pentru a renunţa la subtotaluri folosiţi butonul Remove All al ferestrei Subtotals.
Sintetizarea foii de calcul pe rânduri (Outline)
Sintetizarea vă oferă posibilitatea să extindeţi sau să micşoraţi foile de calcul sau
rapoartele, astfel încât să le vedeţi mai mult sau mai puţin detaliat. în funcţie de
persoana căreia îi este adresat raportul, este nevoie să aveţi nevoie doar de 2
niveluri de detaliu sau de mai multe detalii.
Figura de mai jos arată un raport cu vânzări de produse pe tip de produs şi pe luni.
Acestui raport i s-a aplicat o sintetizare automată. Raportul necesită un efort de derulare
pentru a vedea cumulul pe rânduri pentru fiecare regiune sau cumulul pe coloane
pentru fiecare trimestru.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 33/124
Sintetizarea automată poate fi făcută dacă formulele de cumulare sunt consecvente
în direcţia către care indică. Toate formulele de cumulare din rânduri ar trebui să
cumuleze celulele de deasupra şi toate totalurile din coloane ar trebui să cumuleze
celulele din stânga. Sintetizarea automată funcţionează pe cumulările care se referă la
celulele de dedesubt sau de la dreapta dacă toate cumulurile sunt consecvente în
direcţia către care indică. Puteţi schimba configurarea automată sau puteţi grupa
manual rândurile sau coloanele.
Pentru a aplica pe aceeaşi foaie de calcul un subtotal şi o grupare Auto Outline
ordinea este următoarea: întâi aplicaţi subtotalurile şi apoi alegeţi opţiunea Auto Outline
din lista Group din grupul Outline al filei Data.
Pentru eliminarea subtotalurilor şi a grupării coloanelor, ordinea este aceeaşi (întâi
subtotalurile şi apoi, dacă este cazulopţiunea Clear Outline din lista Ungroup din grupul
Outline al filei Data.
Exerciţii
1. Deschideţi foaia de calcul “Facturi” din fişierul ExExcel2007
Sortaţi tabelul după coloana Produs
Adăugaţi subtotaluri pentru Valoare vânzări pe produse
Afişaţi doar nivelul 2 al structurii ierarhice
Utilizând comanda Select Visible Cells (Alt + ; ) copiaţi tabelul în altă locaţie şi
reprezentaţi grafic totalul vânzărilor pe produse
Renunţaţi la subtotaluri
2. Deschideţi foaia de calcul “Outline”
Sortaţi tabelul după coloana “Categoria”
Introduceţi subtotaluri pentru fiecare câmp ce reprezintă date ce pot fi însumate
(pe luni, trimestre, anualşi valoare totală) pe categorii de produse
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 34/124
Creaţi o sintetizare a coloanelor cu comanda Auto Outline
Afişaţi nivelul 2 al structurii ierarhice pentru subtotaluri şi nivelul 3 pentru
Outline
Copiaţi tabelul rezultat pe o nouă foaie de calcul şi creaţi un grafic cu vânzarile pe
trimestre şi categorii de produse
3. În foaia de calcul Outline să se afişeze produsele din categoria Condimente şi
categoria Dulciuri cu vânzări pe trimestrul 1 mai mari decât 100. (Utilizaţi filtre
automate)
4. În foaia de calcul Outline să se afişeze produsele din categoria Condimente cu vânzări
pe trimestrul 1 mai mari decât 100 şi cele din categoria Dulciuri cu vânzări pe
trimestrul 1 mai mici decât 100.
Pentru aceasta utilizaţi filtrarea avansată cu următorul criteriu:
Categoria Trim1
Condimente >100
Dulciuri <100
Copiaţi rezultatul filtrării într-o altă locaţie pe foaia Outline
5. În aceeaşi foaie de calcul, eliminaţi înregistrările duplicate.
6. Să se afişeze numele, prenumele şi vârsta angajaţilor care sunt născuţi în luna
următoare celei curente.
Se foloseşte criteriul tip formulă şi opţiunea Copy to another location. Se va
utiliza formula=MONTH(tabel_angajati!D2)=MONTH(TODAY())+1
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 35/124
Capitolul 3 _________________________________________
FORMULE ŞI FUNCŢII
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 36/124
Utilizarea referinţelor structurateîn tabele
Formulele în Excel sunt extrem de folositoare dar nu depuţine ori şi greu de decriptat.Spre exemplu o formulă de genul =TODAY()-D2 nu putem spune întotdeauna la ce
anume se referă dar o formulă de genul= TODAY()-[Data nasterii] putem spune că
returnează vârsta, evident exprimată în zile ca orice altă diferenţă de date calendaristice
.
Referinţa structurată uşurează şi face mai intuitiv lucrul cu datele din tabel atunci
când utilizaţi formule care fac referire la un tabel, fie că este vorba de o parte dintr-un
tabel sau de un tabel întreg. Referința structurată este utilă mai ales deoarece zonele dedate dintr-un tabel se modifică deseori, iar referințele la celule pentru referințele
structurate se modifică automat. Astfel, se minimizează nevoia de a rescrie formulele
atunci când se adaugă sau se şterg rânduri şi coloane într-un tabel sau când sunt
reîmprospătate datele externe.
Pentru a lucra eficient cu tabele şi referinţe structurate, trebuie să înţelegeţi modul în
care se creează sintaxa referinţelor structurate atunci când creaţi formule.
De fiecare dată când inseraţi un tabel, Microsoft Office Excel creează un nume
implicit de tabel (Tabel1, Tabel2, etc.) la nivelul sau domeniul global al registrului de
lucru. Numele poate fi modificat cu uşurinţă pentru a fi mai semnificativ pentru
dumneavoastră. De exemplu, pentru a modifica Table1 la Angajaţi, utilizaţi caseta de
dialog Table name. (În fila Design, în grupul Properties, editaţi numele tabelului în caseta
Table name).
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 37/124
Un nume de tabel se referă la toată zona de date din tabel, cu excepţia rândurilor de
anteturi şi totaluri.
În exemplul tabelului Angajaţi, numele tabelului se referă la zona de celule A2:G109.
În mod similar cu numele de tabel, specificatorii de coloană reprezintă referinţe la
toate coloanele de date, cu excepţia coloanei de anteturi şi de totaluri. În exemplul
tabelului Angajaţi, specificatorul de coloană [Nume] se referă la zona A2:A109, iar
specificatorul de coloană [Data nasterii] se referă la zona D2:D109.
Angajati[[#This Row],[Data nasterii]] reprezintă celula de pe rândul curent şi
coloana Data nasterii din tabelul
Angajati
Pentru mai multă flexibilitate în specificarea zonelor de celule, aveţi posibilitatea să
utilizaţi următorii operatori de referinţe pentru a-i combina cu specificatorii de coloană.
Această referinţă structurată:
Se referă la: Utilizând:Se referă
la:
=Angajati[[Salariul]:[Varsta]] Toate celulele din
două sau mai
multe coloane
adiacente
operatorul de
zonă : (două
puncte)
F2:G109
=Angajati[Salariul],Angajati[Nume] O combinaţie de
două sau mai
multe coloaneneadiacente
operatorul de
uniune , (virgulă)
A2:A109,
F2:F109
=Angajati[[Nume]:[Data
angajarii]]Angajati[[Prenume]:[Data
nasterii]]
Intersecţia a
două domenii, în
cazul nostru
A2:C109 cu
Domeniul
B2:D109
Operatorul de
uniune estepauza
(Space)
B2:C109
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 38/124
Aveţi de asemenea posibilitatea să utilizaţi elemente speciale pentru a vă referi la
diferite porţiuni dintr-un tabel, cum ar fi doar rândul de totaluri, pentru a face mai
uşoară referirea la aceste porţiuni din formulă. Mai jos se află specificatori de elemente
speciale, care pot fi utilizaţi într-o referinţă structurată:
Specificatori speciali Semnificaţie
# DataZona de date a tabelului cu excepţia antetului şi rândului detotaluri
#Totals Rândul de totaluri (dacă există)
#Headers Linia de antet (dacă există)
#All Întregul tabel, inclusiv linia de antet, de total şi zona de date
#This Row
Doar porţiunea coloanelor din rândul curent. #This Row nupoate fi combinat cu niciun alt specificator de elementspecial. Utilizaţi-l pentru a impune un comportamentimplicit de intersecţie pentru referinţă sau pentru a înlocuicomportamentul implicit de intersecţie şi a face referire lavalori individuale pentru o coloană.
Funcţii uzuale
Funcţii logice
AND Returnează TRUE dacă argumentele sunt toate adevărate
FALSE Returnează valoarea logică FALSE
IF Specifică un test logic ce trebuie executat
IFERROR Returnează o valoare specificată de dvs. dacă o formulă are ca rezultat oeroare; altfel, returnează rezultatul formulei.
NOT Inversează valoarea logică a argumentului
OR Returnează TRUE dacă unul dintre argumente este adevărat
TRUE Returnează valoarea logică TRUE
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 39/124
IF
Funcţia IF întoarce o valoare specificată dacă testul logic este adevărat - TRUE şi altadacă testul este fals - FALSE.
Sintaxa funcţiei este:
IF (logical_test, value_if_true, value_ if_false)
logical_test
testul logic
este o expresie care poate fi evaluată cu adevărat - TRUE sau fals -FALSE.
value_if_true
valoarea dacătestul esteadevărat
este o valoare obţinută dacă testul logic este adevărat - logical_test is TRUE. Dacă value_if_true este omisă şi logical_test esteTRUE,rezultatul este – TRUE
value_if_false
valoarea dacătestul este fals
este o valoare obţinută dacă testul logic este fals - logical_test isFALSE. Dacă value_if_false este omisă şi logical_test esteFALSE,rezultatul este - FALSE
o Pot fi imbricate până la 64 de funcţii IF ca argumente value_if_true şivalue_if_false pentru a construi teste mai elaborate.
o Când sunt evaluate argumentele value_if_true şi value_if_false, IF întoarcevaloarea returnată de acele instrucţiuni.
o Dacă un argument pentru IF este matrice, fiecare element al matricei este evaluat în timpul executării instrucţiunii IF.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 40/124
Microsoft Excel furnizează funcţii suplimentare care pot fi utilizate pentru a analizadatele pe baza unei condiţii. De exemplu, pentru a contoriza numărul de apariţii pentru unşir de text sau pentru un număr dintr -o zonă de celule, se utilizează funcţiile foii de lucruCOUNTIF şi COUNTIFS. Pentru a calcula o sumă pe baza unui şir de text sau a unuinumăr dintr -o zonă, se utilizează funcţiile foii de lucru SUMIF şi SUMIFS.
Într-o macroinstrucţiune “valoarea dacă testul este adevărat sau fals” poate fi înlocuită deo instrucţiune GOTO sau altă funcţie activă.
AND
Întoarce TRUE dacă toate argumentele sale sunt TRUE; întoarce FALSE dacă unul saumai multe argumente sunt FALSE.
Sintaxa funcţiei este:
AND(logical1;logical2; ...)
Logical1, logical2, ... sunt de la 1 până la 255 de condiţii pe care le testaţi şi care pot fi
TRUE sau FALSE.
o Argumentele trebuie să poată fi evaluate la valori logice cum ar fi TRUE sauFALSE, sau argumentele trebuie să fie matrice sau referinţe care conţin valorilogice.
o Dacă un argument matrice sau referinţă conţine text sau celule goale, acele valorisunt ignorate.
o Dacă zona specificată nu conţine nicio valoare logică, AND întoarce valoarea deeroare #VALUE!.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 41/124
OR
Întoarce TRUE dacă cel puţin un argument este TRUE; întoarce FALSE dacă toateargumentele sunt FALSE.
Sintaxa funcţiei este:
OR(logical1;logical2; ...)
Logical1, logical2, ... sunt de la 1 până la 255 de condiţii pe care le testaţi şi care pot fiTRUE sau FALSE.
o Argumentele trebuie să poată fi evaluate la valori logice cum ar fi TRUE sauFALSE, sau argumentele trebuie să fie matrice sau referinţe care conţin valorilogice.
o Dacă un argument matr ice sau referinţă conţine text sau celule goale, acele valorisunt ignorate.
o
Dacă zona specificată nu conţine nicio valoare logică, OR întoarce valoarea deeroare #VALUE!.
NOT
Schimbă valoarea argumentului într -o valoare opusă. Utilizaţi NOT atunci când vreţi săvă asiguraţi că o valoare nu este egală cu o valoare particulară.
Sintaxa funcţiei este:
NOT(logical)
Logical este o valoare sau o expresie care poate fi evaluată ca TRUE sau FALSE.
o Dacă logical este FALSE, NOT întoarce TRUE; dacă logical este TRUE, NOT întoarce FALSE.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 42/124
IFERROR
Returnează o valoare specificată de dvs. dacă o formulă are ca rezultat o eroare; altfel,returnează rezultatul formulei. Utilizaţi funcţia IFERROR pentru a găsi şi gestiona erorile
într-o formulă.
Sintaxa funcţiei este:
IFERROR(value,value_if_error)
Value este argumentul care este verificat pentru a găsi erorile.
Value_if_error este valoarea de returnat dacă formula are ca rezultat o eroare. Seevaluează următoarele tipuri de erori: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!,#NAME? sau #NULL!.
o Dacă argumentele value sau value_if_error sunt o celulă goală, IFERROR letratează ca o valoare de şir necompletat ("").
o Dacă valoarea este o formulă matrice, IFERROR returnează o matrice de rezultate pentru fiecare celulă din intervalul specificat în valoare.
Funcţii de căutare
VLOOKUP - căutare vertical ă
Funcţia VLOOKUPcaută cea mai la stânga coloană a unui tabel pentru o valoareparticulară, întorcând valoarea într-o celulă specificată.
Sintaxa funcţiei este:
VLOOKUP (lookup_value, table_array, col_index_num, range_lookup )
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 43/124
lookup_value
valoarea
căutată
este valoarea de căutat în prima coloană a tabelei. Aceasta poate fivaloare, referinţă sau şir de text
table_array
tabela
este tabela ce conţine informaţiile în care se caută coincidenţa. Serecomandă să se utilizeze un nume de tabel.
col_index_num
număr coloană
este numărul, în tabelă, de unde se va întoarce valoareacorespunzătoare estimării coincidenţei.
range_lookup
tipulcoincidenţei
este o valoare logică. Dacă este adevăr - TRUE sau omisă, VLOOKUP
va întoarce cu aproximaţie valoarea cea mai mare apropiată careeste mai mică decât lookup_value. Dacă este fals -FALSE,VLOOKUP va căuta o coincidenţă exactă. Dacă nu va găsi va damesajul N/A.
În exemplul de mai josfuncţia VLOOKUP determină dacă cifra de afaceri este mare,medie sau mică.
În această situaţie argumentul range_lookup este setat pe opţiunea true astfel încâtfuncţia VLOOKUP să returneze aceeaşi valoare (“Small”) pentru toate valorileargumentului lookup_value cuprinse în intervalul [100 000, 250 000)
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 44/124
HLOOKUP - căutare orizontal ă
Funcţia HLOOKUP este echivalentă cu funcţia VLOOKUP şi caută prima linie dintr-untabel pentru o valoare particulară, întorcând valoarea în celula indicată.
Sintaxa funcţiei este:
HLOOKUP (lookup_value, table_array, row_index_num, range_lookup )
LOOKUP
Această funcţie a fost inclusă în Excel numai pentru compatibilitate cu alte programede foi de calcul. Posibilitatea de eroare este mai mare decât la VLOOKUP sauHLOOKUP, drept pentru care se recomandă utilizarea ultimelor funcţii.
MATCH
Întoarce poziţia relativă a unui element dintr -o matrice care corespunde unei valorispecificate într-o ordine specificată. Utilizaţi funcţia MATCH în locul uneia din funcţiileLOOKUP atunci când aveţi nevoie de poziţia unui element dintr-o zonă şi nu deelementul însuşi.
Sintaxa funcţiei este:
MATCH(lookup_value;lookup_array;match_type)
Lookup_value este valoarea utilizată pentru a găsi valoarea dorită într -un tabel.
Lookup_value este valoarea care vreţi să se potrivească în matricea lookup_array. De
exemplu, atunci când căutaţi numărul de telefon al unei persoane în cartea de telefon,
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 45/124
utilizaţi numele persoanei ca valoare de căutare (lookup_value), dar numărul de telefoneste valoarea pe care o doriţi.
Lookup_value poate fi o valoare (număr, text, valoare logică) sau o referinţă de celulăspre un număr, text sau valoare logică.
Lookup_array este o zonă de celule care conţin valori posibil de căutat. Lookup_arraytrebuie să fie o matrice sau o referinţă de matrice.
Match_type este numărul -1, 0 sau 1. Match_typespecifică modul în care Microsoft Excel
va potrivi valoarea din lookup_value cu valorile din matricea lookup_array.
Dacă match_typeeste 1, MATCH găseşte cea mai mare valoare care este mai mică sauegală cu lookup_value. Lookup_array trebuie aşezată în ordine crescătoare: ...-2, -1, 0, 1,
2, ..., A-Z, FALSE, TRUE.
Dacă match_typeeste 0, MATCH găseşte prima valoare care este egală culookup_value. Lookup_array poate fi în orice ordine.
Dacă match_typeeste -1, MATCH găseşte cea mai mică valoare care este mai mare sauegală cu lookup_value. Lookup_array trebuie aşezată în ordine descrescătoare: TRUE,FALSE, Z-A,...2, 1, 0, -1, -2,... şi aşa mai departe.
Dacă match_type este omis, se consideră egal cu 1.
o MATCH întoarce poziţia valorii care se potriveşte în matricea lookup_array şi nuvaloarea însăşi. De exemplu, MATCH("b";{"a";"b";"c"};0) întoarce 2, poziţiarelativă a lui „b” în matricea {"a";"b";"c"}.
o MATCH nu face deosebire între litere mari şi litere mici atunci când potriveştevalori text.
o Dacă MATCH este nu găseşte o potrivire, atunci întoarce valoarea de eroare#N/A.
o Dacă match_type este 0 şi lookup_valuea este text, aveţi posibilitatea să utilizaţimetacaracterele, cum ar fi semnul de întrebare (?) şi asteriscul (*) înlookup_value. Un semn de întrebare se potriveşte cu orice caracter unic, asterisculse potriveşte cu orice secvenţă de caractere. Dacă doriţi să găsiţi un semn de
întrebare sau asterisc în sine, tastaţi tilda (~) înainte de caracter.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 46/124
INDEX
Returnează conţinutul unei celule dintr -un domeniu. Se utilizează frecvent încombinaţie cu funcţia MATCH.
Sintaxa funcţiei este:
INDEX(array;row_num;column_num)
Arrayreprezintă domeniul de date
Row_numnumărul liniei din domeniul de date de pe care vrem să fie returnat răspunsul.
Column_numnumărul coloanei din domeniul de date de pe care vrem să fie returnatrăspunsul.
Atât row_num cât şi column_num pot fi rezultatul funcţiei MATCH.
Funcţii pentru baze de date şi li ste
DAVERAGE - media în baze da date
Funcţia DAVERAGE calculează mediile valorilor pentru un câmp - coloană (carerespectă criteriile) dintr-o bază de date. Este identică cu funcţia AVERAGE , dar în plustrebuie ţinut cont de criteriile specificate.
Modul de construire al criteriilor pentru funcţiile din această categorie este acelaşi cucel de la filtrarea avansată. Deosebirea constă în faptul că o funcţie îşi schimbă valoareaîn momentul modificării unuia dintre argumente.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 47/124
Astfel, în cazul funcţiilor din categoria Database, la modificarea datelor înscrise îndomeniul care reprezintă criteriul se actualizează şi valoarea rezultatului.
Dacă iniţial am calculat media vânzărilor pentru produsul Printer, la introducerea încriteriu a unui alt produs, funcţia va afişa media vânzărilor de computere, spre exemplu.
În cazul filtrării avansate însă, rezultatul filtrării nu se modifică la schimbarea valorilor din zona de criterii.
Sintaxa funcţiei este:
DAVERAGE ( database, field, criteria)
database = baza de date, field = câmp, criteria = criteriu
Deschideţi fişierul ExExcel2007.xls, foaia de calcul DAVERAGE şi studiaţi funcţiaDAVERAGE. În exemplul de mai joscu funcţia DAVERAGE se calculează medianumărului de vânzări (No of Sales) de imprimante - Printer (Product).
Pentru aceasta se construieşte criteriul în domeniul B3:B4. Pentruargumentul field puteţi introduce fie adresa celulei care conţine numele câmpului fie numărul acestuia (acâta coloană din tabel este cea asupra căreia doriţi să aplicaţi calculul) număr care ar
putea fi şi rezultatul unei funcţii MATCH (căutarea numelui câmpului în antetultabelului)
Astfel cele trei variante ar fi următoarele:
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 48/124
Alte func ţ ii uzuale pentru baze de date şi liste
Funcţia Descriere
DCOUNT() Întoarce numărul de înregistrări a căror câmpuri numerice
coincid cu un criteriuDCOUNTA() Întoarce numărul înregistrărilor nevide a căror câmpuri numerice
coincid cu un criteriu
DGET() Întoarce valoarea unui câmp specificat pentru o singură coincidenţă de înregistrare cu un criteriu
DMAX Întoarce valoarea maximă a unui câmp specificat pentruînregistrări care coincid cu un criteriu
DMIN() Întoarce valoarea minimă a unui câmp specificat pentruînregistrări care coincid cu un criteriu
DPRODUCT() Întoarce produsul valorilor unui câmp specificat pentruînregistrări care coincid cu un criteriu
DSUM() Întoarce suma valorilor unui câmp specificat pentru înregistrăricare coincid cu un criteriu
Toate funcţiile de mai sus au aceleaşi argumente ca şi DAVERAGE:
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 49/124
Dxxxxxxx (database, field, criteria)
database
baza de date
este domeniul ce conţine baza de date sau lista
field
câmp
este câmpul utilizat în funcţie
criteria
criteriu
este domeniul ce conţine criteriile
Funcţii de anali ză statistică
În practică, de multe ori, aveţi nevoie în analiza unui şir de valori de stabilireatendinţei centrale a unui grup de numere într-o distribuţie statistică.
Cele mai comune mărimi ale tendinţei centrale sunt:
o Medie(funcţia AVERAGE) care este media aritmetică şi se calculează prinadunarea unui grup de numere şi împărţirea la numărul de elemente al grupului.De exemplu, media numerelor 2, 3, 3, 5, 7 şi 10 este 30 împărţit la 6, adică 5.
o Median (funcţia MEDIAN)care este numărul din mijloc al unui grup de numere;adică jumătate dintr e numere au valori mai mari decât medianul şi jumătate auvalori mai mici. De exemplu, medianul pentru 2, 3, 3, 5, 7 şi 10 este 4.
o Mod(funcţia MODE) care este cel mai întâlnit număr dintr -un grup de numere.De exemplu, mod pentru 2, 3, 3, 5, 7 şi 10 este 3.
În cazul unei distribuţii simetrice a unui grup de numere, aceste trei măsuri de tendinţăcentrală sunt identice. În cazul unei distribuţii asimetrice a unui grup de numere, pot fidiferite.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 50/124
Median
Returnează numărul median al numerelor date. Prin median se în-elege numărul dinmijlocul unui set de numere.
Sintaxa funcţiei este:
MEDIAN(number1;number2;...)
Number1, number2, ...
sunt de la 1 până la 255 de numere pentru care calculaţi medianul.
o Dacă în set este un număr par de numere, atunci MEDIAN calculează media adouă numere din mijlocul setului. Vezi a doua valoare din exemplu.
o Argumentele pot să fie numere sau nume, matrice sau referinţe care conţinnumere.
o Valorile logice şi reprezentările text ale numerelor pe care le tastaţi direct în listade argumente sunt numărate.
o Dacă un argument matrice sau referinţă conţine text, valori logice sau celule
goale, acele valori sunt ignorate; oricum, celulele cu valori zero sunt incluse încalcule.o Argumentele care sunt valori de erori sau texte ce nu pot fi interpretate ca numere
cauzează erori.
Mode
Returnează valoarea care apare cel mai frecvent sau se repetă, într -o matrice sau într-uninterval de date.
Sintaxa funcţiei este:
MODE(number1;number2;...)
Number1, number2, ... sunt de la 1 până la 255 de argumente pentru care vreţi săcalculaţi valoarea mode. Aveţi posibilitatea de asemenea să utilizaţi o singură matrice sau
o referinţă a unei matrice în locul argumentelor separate prin virgulă.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 51/124
o Argumentele pot să fie numere sau nume, matrice sau referinţe care conţinnumere.
o Dacă un argument matrice sau referinţă conţine text, valori logice sau celulegoale, acele valori sunt ignorate; oricum, celulele cu valori zero sunt incluse încalcule.
o Argumentele care sunt valori de erori sau texte ce nu pot fi interpretate ca numerecauzează erori.
o Dacă setul de date nu conţine nicio valoare duplicată, MODE returnează valoareade eroare #N/A.
Forecast –funcţie de estimare liniară
Funcţia Forecast calculează, estimează o valoare viitoare plecând de la un eşantion de date cunsocute, având la bază regresia liniară. Eşantionul de date cunoscute estereprezentat în două şiruri de numere cunoscute x şi y, associate astfel:
La valoarea X1 a corespuns valoarea Y1
La valoarea X2 a corespuns valoarea Y2.
…
La valoarea Xn a corespuns valoarea Yn
Având în vedere corespondenţele de mai sus, care va fi valoarea estimată pentru Xn+1?
Sintaxa funcţiei este:
FORECAST(x, known y’s, known x’s)
Sfera de aplicabilitate este mare, deoarece se pot modela scenarii din lumea reală îndomenii diverse. Funcţia Forecastestimează valorile viitoare bazîndu-se pe o relaţieliniară între cele două şiruri de date X şi Y. Prin urmare, dacă observaţi că un model decreştere liniară se potriveşte cu datele dumneavoastră reale, utilizaţi această funcţie.
De exemplu,: pentru lunile ianuarie – mai 2005( reprezentate în tabel la adresele B3:B8)vînzările realizate apar trecute în tabelul de mai jos (C3:C8):
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 52/124
Plecând de la aceste date concrete, se pot estima vînzările pe următoarea lună utilizîndfuncţia forecast ca în imaginea de mai sus.
GROWTH- estimare exponenţială
Funcţia Growth calculează, estimează mai multe valori viitoare avînd la bază uneşantion de valori cunoscute, pe modelul unei creşteri exponenţiale. Dacă funcţia Forecastmodela o creştere liniară, în cazul Funcţiei Growth avem cu un ritm de creştere mult mai
rapid, cel exponenţial. Dacă acest model se potriveşte cu datele dumneavostră reale,utilizarea lui este similară cu funcţia Forecast.
Sintaxa funcţiei Growth:
GROWTH ( known_y's ,known_x's,new_x's,const)
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 53/124
Funcţii condiţionale
În funcţie de tipul operaţiei efectuate, aceste funcţii sunt cuprinse în categorii diferite
(AVERAGEIF, AVERAGEIFS,SUMIF şi SUMIFS în categoria funcţiilor matematice,COUNTIF şi COUNTIFS în categoria funcţiilor statistice) însă toate returnează rezultatuloperaţiei respective doar pentru înregistrările corespunzătoare unui criteriu.
Vă prezentăm mai jos funcţiile COUNTIF şi COUNTIFS, pentru toate celelalte metodade lucru este similară.
COUNTIF
Funcţia COUNTIF socoteşte numărul celulelor nevide dintr-un domeniu, care respectă uncriteriu dat.
Sintaxa funcţiei este:
COUNTIF (range, criteria)
range
domeniu
este domeniul pentru care se calculează celulele nevide
criteria
criteriu
este un criteriu sub formă numerică, expresie sau text de ex.: 100, >100,"Loan accounts"
Deschideţi fişierul ExExcel2007.xls, foaia de calcul COUNTIF şi studiaţi funcţiaCOUNTIF.
În exemplul de mai jos funcţia COUNTIF este utilizată pentru numărarea celulelor nevidecare îndeplinesc criteriul ca cifra de afaceri să fie >300,000.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 54/124
COUNTIFS
Contorizează numărul de celule dintr -un interval care întrunesc mai multe criterii.
Sintaxa funcţiei este:
COUNTIFS (interval1, criterii1,interval2, criterii2… )
Interval1, interval2, … sunt intervale de la la 1 la 127 în care se evalueazăcriteriile asociate. Celulele din fiecare interval trebuie săfie numere, matrice sau referinţe care conţin numere. Se
ignoră valorile text şi valorile necompletate.Criterii1, criterii2 , … sunt criterii de la 1 la 127 sub formă de număr, expresie,
referinţă în celulă sau text care defineşte care celule vor ficontorizate. De exemplu, criteriile se pot exprima ca 32,"32", ">32", "mere" sau B4.
o Fiecare celulă din interval este contorizată numai dacă toate criteriilecorespunzătoare specificate pentru celulă sunt adevărate.
o Dacă criteriul este o celulă necompletată, COUNTIFS o tratează ca pe o valoare 0.o Aveţi posibilitatea să utilizaţi metacaractere în criterii, cum ar fi semnul de
întrebare (?) şi asteriscul (*). Un semn de întrebare se potriveşte cu orice caracterunic, asteriscul se potriveşte cu orice secvenţă de caractere. Dacă doriţi să găsiţiun semn de întrebare sau asterisc, tastaţi tilda (~) înainte de caracter.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 55/124
Funcţii f inanciare
Funcţiile financiare pe bază de anuităţi operează cu o investiţie sau un depozit la care
toate plăţile sunt egale şi efectuate la intervale regulate de timp. La introducereaargumentelor trebuie să ţineţi cont de următoarele reguli:
Plăţile pe care le efectuaţi (de exemplu depozite de economii) vor fi reprezentateprin numere negative
Sumele pe care le primiţi (de exemplu dobânzi din economii, credite) vor fireprezentate prin numere pozitive.
PV, Present Value - valoare prezentă
Funcţia PV, calculează o valoare a unei investiţii (anuitate). Valoarea prezentă estesuma veniturile viitoare discountate referitoare la prezent.
Sintaxa:
PV ( rate, nper, pmt , [fv],[type])
rate
rata dobânzii
dobânda sau rata de discount pe perioadă. Dacă se utilizează perioadelunare şi rată anuală, rata se împarte la 12;
nper
număr deperioade
numărul de perioade de plată de ex. dacă plăţile sunt lunare pe operioadă de 2 ani, numărul de perioade este 24;
pmt
mărimea plăţii
plata periodică. Aceasta trebuie să rămână constantă pe perioada
plăţii. În mod normal această sumă include capitalul principal, fărăcheltuieli sau taxe.
fv
valoare viitoare
valoarea viitoare este o valoare care rămâne după ultima plată. Acestcâmp este opţional, iar dacă este omis, va considerat zero.
type
tip
este 0 dacă plata se face la sfârşitul perioadei şi 1 dacă plata se face la începutul fiecarei perioade
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 56/124
Exemplu:O persoană fizică doreşte să economisească 500000 € pe o durată de 20 ani cu
o plată lunară fixă în valoare de 150 €. Care este avansul pe care trebuie sa-l depună ştiindcă banca percepe o dobândă anuală de 15%?
Deschideţi fişierul ExExcel2007.xls, foaia de calcul PV şi studiaţi funcţia PresentValue.
FV, FutureValue - valoare viitoare
Sintaxa:
FV ( rate, nper, pmt ,[pv],[type])
Funcţia FV calculează valoarea finală a unei investiţii. Valoarea finală se obţine prinadăugarea dobânzii.
Exemplu: O persoană fizică doreşte efectuarea unui plasament de 25 000 € pe o perioadă
de 9 ani la o bancă ce oferă o dobândă anuală de 8 %. Persoana urmează să depună la bancă lunar, alături de depozitul iniţial câte 500 € pe aceeaşi perioadă. Cu ajutorulfuncţiei FV se calculează valoarea viitoare a sumei depuse.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 57/124
Deschideţi fişierul ExExcel2007.xls, foaia de calcul FV şi studiaţi funcţia FutureValue.
PMT, Payment - plata pe bază de anuităţi
Funcţia PMT calculează valoarea plăţilor periodice pentru o investiţie sau un împrumut.
Sintaxa:
PMT ( rate, nper , pv , [fv], [type])
Pentru o descriere mai completă a argumentelor din PMT, vezi funcţia PV.
Rate este rata dobânzii pentru împrumut.
Nper este numărul total de plăţi pentru împrumut.
Pv este valoarea actualizată sau suma totală pe care o valorează în prezent o serie de
plăţi viitoare, denumită şi capital de bază.
Fv este valoarea viitoare sau balanţa în numerar la care vreţi să ajungeţi după efectuarea
ultimei plăţi. Dacă fv este omis, se presupune a fi 0 (zero), ceea ce înseamnă că valoarea
viitoare a împrumutului este 0.
Type este numărul 0 (zero) sau 1 şi indică momentul când sunt datorate plăţile.
Exemplu: Doriţi să depuneţi anual o sumă constantă într -un cont de economii cu o rată adobânzii de 12% pe an şi după 15 ani să aveţi economisită suma de 80 000 EUR. Care
trebuie să fie suma pe care trebuie să o economisiţi anual:
a) la plata anuală a dobânzii; b) cu dobândă pe mai puţin de un an (de exemplu , 2 termene de plată pe an) c) la plata lunară a dobânzii
Argumentul pv rămâne necompletat deoarece se porneşte fără depunere iniţială.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 58/124
Deschideţi fişierul ExExcel2007.xls, foaia de calcul PMT şi studiaţi funcţia PMT.
=PMT(B1;B2;0;B3)
=PMT(B1/2;B2*2;0;B3)
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 59/124
Exerciţii
Deschideţi foaia de calcul tabel _angajaţi din fişierul angajaţi.
Introduceţi un nou câmp numit Vechime şi calculaţi vechimea în ani a fiecărui angajat.
Adăugaţi un câmp numit Spor Vechime care să conţină valoarea sporului de vechime în funcţie
de grila de vechime următoare:
pentru vechime <= 3 ani - nu se acordă spor de vechime (adică este zero)
pentru vechime cuprinsă între (3 şi 5+ ani - spor de vechime 5% din salariu de încadrare
pentru vechime cuprinsă între *5 şi 10) ani - spor de vechime 10% din salariu de încadrare
pentru vechime cuprinsă între *10 şi 15) ani - spor de vechime 15% din salariu de încadrare
pentru vechime cuprinsă între *15 şi 20) ani - spor de vechime 20% din salariu de încadrare
pentru vechime de peste 20 ani - spor de vechime 25% din salariu de încadrare
Rezolvaţi problema cu ajutorul funcţiei IF.
Rezolvaţi cerinţa de mai sus folosind funcţia VLOOKUP cu argumentul true.
Introduceţi un nou câmp Salariu Brut în care adunaţi salariul de încadrare cu sporul de
vechime, un câmp Impozit în care calculaţi 16% din salariul brut şi un câmp Salariu net ca
diferenţă între salariul brut şi impozit.
Care este numărul angajaţilor de la departamentul Contabilitate?
Să se determine numărul de angajaţi cu peste 10 ani vechime.
Să se afişeze media de vârstă a angajaţilor din societate şi cea mai frecventă vârstă (funcţiile
AVERAGE şi MODE).
Să se calculeze suma salariilor nete pentru angajaţii cu vechime de peste 15 ani
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 60/124
Deschideţi foaia de calcul DAVERAGE.Presupunând că în urmatorul tabelcoloana No of Sales
conţine valoarea vânzărilor pe fiecare produs din fiecare sucursală, calculaţi pentru produsul
PRINTER următoarele valori:
Product Branch No of Sales
Printer Cluj 25
Printer Craiova 17
Printer Bucuresti 120
Printer Timisoara 64
Printer Botosani 33
LAN_Interface Bucuresti 1200
LAN_Interface Craiova 876
LAN_Interface Cluj 834
Computer Bucuresti 300
Computer Craiova 512
Computer Cluj 245
Valoarea totală a vânzărilor ;
Media vânzărilor pe fiecare sucursală ;
Valoarea maximă a vânzărilor ;
Valoarea minimă a vânzărilor;
Introduceţiîn lista din foaia de calcul Forecast valori şi pentru celelalte luni, până la sfârşitul
anului. Calculaţi, folosind estimarea liniară, tendinţa până la sfârşitul anului.
luna vanzari 2005
1 € 2,500.00
2 € 3,450.00
3 €1,567.00
4 €2,234.00
5 €1,623.00
6 €1,835.00
7 €1,387.60
8
910
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 61/124
11
12
13
Aceeaşi cerinţă pentru foaia de calcul Growth utilizând estimarea exponenţială.
În foaia de calcul Vlookup2 introduceţi în celulele B26:E26 formulele potrivite astfel că la
introducerea numărului de telefon în celula A26 să fie afişate pe aceeaşi linie datele
corespunzătoare din tabel. (Indicaţie: utilizaţi funcţia VLOOKUP cu argumentul false)
Introduceti numarul de
telefonFirst Name Last Name Address City
2124094378
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 62/124
Capitolul 4 _________________________________________
UTILITARE AVANSATE DE ANALIZĂ A DATELOR
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 63/124
Utilizarea tabelelor de ipoteze (Data Table)
Un tabel de ipoteze analizează impactul generat de o variabilă sau de două variabileasupra rezultatului unei formule care utilizează aceste variabile. Pentru a crea asemenea
tabele:
Clic pe butonul What-If Analysis din grupul Data Tools al filei Data
Clic pe Data Table pentru a crea un tabel de ipoteze fie bazat pe diversevalori ale unei singure variabile fie pe un set de valoripentru fiecare din celedouă variabile pe care se bazează un tabel bidimensional.
Tabele de ipoteze bazate pe o variabilă
Să presupunem că avem de realizat o situaţie statistică la nivel de societate şi vrem săcalculăm media salariilor pe departamente. Acest lucru se realizează cu ajutorul funcţieiDAVERAGE aplicată tabelului Angajati pe cîmpul Salariul folosind criteriul:
Departament
IT
Dacă dorim însă să afişăm media salariilor pentru toate departamentele ar trebui săfolosim câte un criteriu pentru fiecare departament. Întrucât criteriul reprezintă unargument pentru funcţia DAVERAGE putem sa-l considerăm variabilă şi folosindtabelele de ipoteze să afişăm valoarea funcţiei pentrru diverse valori ale acestei variabileastfel:
- se aplică funcţia DAVERAGE pentru o valoare a variabilei (în cazul nostru IT)
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 64/124
- scrieţi valorile cu care doriţi să înlocuiţi variabila IT în formulăpe rândul de
deasupra celui în care aţi scris formula, începând cu coloana următoare, ca înfigura:
IT Contabilitate Marketing Resurse umane
3555.952381
- selectaţi tabelul de două linii astfel format şi selectaţi Data Table în modulprezentat mai sus
- în caseta de dialog Data Table alegeţi pentru Row Input Cell celula care conţinevaloarea “IT” din zona în care aţi scris criteriul corespunzător celui de-al treileaargument al funcţiei şi apăsaţi OK
Veţi obţine următorul rezultat:
IT Contabilitate Marketing Resurse umane
Media salariilor 3555.952381 3369.230769 3387.142857 3170
În cazul în care doriţi să scrieţi pe coloană valorile pe care trebuie să le ia variabilaaleasă, poziţia acestora faţă de celula în care este scrisă funcţia trebuie să fie următoarea:
IT 3555.952381
Contabilitate
Marketing
Resurse umane
şi în caseta de dialog Data Table alegeţi opţiunea Column input cell.
Formula
Valori cu care se
înlocuieşte
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 65/124
Tabele de ipoteze bazate pe două variabile
În cazul în care dorim să realizăm o situaţie statistică la nivel de sociatate şi trebuie să
calculăm suma salariilor pe departamente şi funcţii.
Construim un criteriu:
Departament Functie
IT analist
şi utilizând funcţia DSUM pe tabelul Angajaţi pe coloana Salariul având ca şi criteriudomeniul de mai sus vom determina suma salariilor analiştilor din departamentul IT.
Numărul total de aplicări ale funcţiei DSUM este egală cu cardinalulprodusuluicartezian dintre departamente şi funcţii, în total 24 de operaţii. Pentru a evita o asemeneasituaţie putem folosi tabelele de ipoteze în două variabile: una reprezentată celula care
conţine numele departamentului în criteriu iar cealaltă, celula care conţine numelefuncţiei angajatului.
Datele cu care va fi înlocuită funcţia angajatului le vom scrie pe linie (cap de tabel),cele care vor înlocui numele departamentului pe coloană (cap de rând), iar în celula aflatăla intersec-ia celor două domenii se scrie formula (în cazul nostru funcţia DSUM) ca înfigura următoare:
39650 analist contabil economist merceolog operator programator
IT
Contabilitate
Marketing
Resurse
umane
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 66/124
Selectaţi acest tabel şi în caseta Data Table pentru Row input cell selectaţi celula ce
conţine în cadrul criteriului funcţia angajatului iarîn Column input cell celula ce conţinenumele departamentului în criteriul utilizat în definiţia funcţiei.
Se obţine următorul rezultat:
39650 analist contabil economist merceolog operator programator
IT 39650 0 33200 0 21150 55350
Contabilitate 0 35950 33900 17750 0 0
Marketing 0 0 45550 73000 0 0
Resurseumane 0 15850 0 0 0 0
Într-un tabel de date puteţi modifica formula iniţială, datele care sunt introduse ca şicap de rând şi/sau cap de coloană dar nu puteţi modifica datele obţinute ca şi rezultat altabelului de ipoteze.
Funcţii obiectiv (Goal seek)
În mod tipic în Excel se introduc date în anumite celule, se fac calcule, iar rezultatul se
pune în altă celulă. Goal seek inversează procesul şi determină ce valoare este necesară
în datele iniţiale pentru a ajunge la rezultatul dorit.
Fără a utiliza Goal seek se calculează plăţile pe care trebuie să le faceţi dacă
împrumutaţi o valoare de 20000$ pe care o înapoiaţi în 4 ani la o rată a dobânzii de 12%.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 67/124
Calculele pot consta în mai multe variabile, dar Goal seek permite manipularea unei
singure variabile. Goal seek utilizează o metodă iterativă pentru a găsi soluţia,
încercându-se diferite serii de valori până ce se determină o convergenţă către soluţie.
Pentru a utiliza Goal seek:
Pe foaia de calcul, se completează datele de intrare, formulele şi rezultatul de ieşire
Selectaţi opţiunea Goal Seek din lista What-If Analysis din grupul Data Tools al fileiData
Va apărea fereastra de dialog Goal Seek
În opţiunea Set cell:, se introduce referinţacelulei care conţine formula. Goal seek poateacţiona numai într-o celulă unde se dă oformulă
În zona To value:, se introduce rezultatulaşteptat
În zona By changing cell:, se introduce
referinţa celulei (variabile) ce se doreşte să semodifice pentru a ajunge la rezultatul dorit
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 68/124
În exemplul de mai sus, se doreşte să se răspundă la întrebarea: “Ce valoare voi putea
împrumuta, dacă aş plăti $300 pe lună?”
Se apasă butonul OK pentru a definitiva oparaţia.
După apariţia rezultatului, se poate menţine acesta apăsând butonul OK sau se poate
renunţa, revenind la valorile originale, apăsând butonul Cancel .
Goal seek utilizează un proces iterativ, iar pentru operaţii complexe poate
dura mult timp. Pentru a opri Goal seek, se selectează butonul Pause din
dialogul stării Goal Seek Status. Se poate opera procesul executând câte o
operaţie o dată utilizând butonul Step. Se apasă butonul Continue pentru
a reporni Goal seek.
Scenarii
În afaceri, se operează cu diferite variante de supoziţii, presupuneri şi estimări.
Fiecare dintre acestea se pot schimba şi se doreşte să se poată vizualiza rezultatele luând
în consideraţie diferite seturi de date sau scenarii.
În exemplul de mai jos, se pot modela efectele schimbărilor în valoarea împrumutului,
rata procentuală sau perioadele de plată.
Valorile dif erite pentru fiecare celulă de intrare pot fi reprezentate într-un scenariu
specific cu un anumit nume. Aceste scenarii pot fi vizualizate separat.
Stabilirea scenariilor
Se decide ce celule vor fi folosite conţinând valori ce se vor schimba. Exceldenumeşte aceste celule: Changing Cells (32 maximum)
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 69/124
Aceste celule nu trebuie să fie formule
În acest exemplu, B1 până la B3 reprezintă celulele care se schimbă Changing cells
Dacă se lucrează cu foi de calcul complexe, fiecare celulă trebuie definită indiv idual.Se pot utiliza nume pentru definirea de referinţe în formule.
Introduceţi scenariul în foaia de calcul.
Scenario Manager din lista What-If Analysis a grupului Dta Tools din fila Data
Se va afişa dialogul Scenario Manager .
Se apasă butonul Add...
Aceasta va antrena dialogul Add Scenario
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 70/124
Se dă un nume scenariului în Scenario Name: box
În opţiunea Changing Cells: box, se introduc ireferinţele la celulele care se vorschimba
Acestea pot fi introduse ca domeniu
Celulele ne-adiacente pot fi separate prin virgule
Celulele se pot selecta şi cu ajutorul mouse-ului.
În opţiunea Comment:, se introduce descrierea scenariului.
Se selectează butonul OK pentru execuţie
Se va afişa dialogul Scenario Values
Se introduc valorile schimbate
Dacă s-au specificat mai multe celula pentru a fi modificate, apar mai multe opţiunipentru a fi completate.
Pentru a adăuga mai multe scenarii, se apasă butonul Add şi se revine la dialogul Add Scenario.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 71/124
Generarea unui sumar de scenarii
Se poate genera un raport sumar al scenariilor în care se afişează efectul pentru
diferitele valori ale variabilelor
Pentru generarea unui sumar de scenarii
Se activează Scenario Manager
Se apasă butonul Summary
Se va afişa dialogul Scenario Summary:
Se alege Scenario Summary
Celulele rezultante Result Cells: este opţional pentru raportul sumar. Acesta estefolosit pentru a include acele celule care deţin formule ce depind de celulele are seschimbă
Se selectează butonul OK pentru a realiza operaţia.
Se va genera un sumar al scenariilor:
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 72/124
Raportul sumarului scenariilor va conţine butoane de analiză/sinteză care potascunde sau afişa diferite nivele de subtotaluri.
Aplicaţia Solver
Solver este un add-in al programului Excel, ce trebuie instalat şi adăugat filei Data.
Pentru a-l instala daţi clic pe Microsoft Office Button, Excel options, categoria Add-Ins şi
clic pe butonul Go. Bifaţi caseta din dreptul Solver Add-in şi clic pe butonul OK.
Acest utilitar identifică valoarea optimă a unei formule dintr-o celulă (celula ţintă)
modificând valoarea mai multor parametri implicaţi în formula respectivă. Solver
lucrează cu mai multe celule Excel (celule ajustabile) relaţionate direct sau indirect cu
celula ţintă, modificînd valoarea lor conform cu constrângerile şi specificaţiile
utilizatorului.
Puteţi utiliza Solver pentru a afla valoarea maximă şi minimă a unei formule
schimbând valorile celulelor ajustabile.
Să luăm ca exemplu figura de mai jos:
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 73/124
În obţinerea profitului(celula B15) intră următorii factori:
Factorul sezonier - celulele B3:E3 Numărul de produse vîndute - celulele B5:E5 Costuri associate cu vînzarea produselor - celulele B7:E7 Cheltuieli cu personalul de la vînzări - celulele B10:E10 Cheltuieli cu publicitatea produselor vîndute- celulele B11:E11 Cheltuieli administrative- celulele B12:E12
Profitul final din celula B15 depinde de toţi factorii menţionaţi mai sus, prin urmarede celulele aferente din tabel. Conducerea companiei doreşte maximizarea profitului
final(B15), ştiind că se pot modifica cheltuielile cu publicitatea (B11:E11), avînd însă
constrîngerea că bugetul de publicitate total(F11) nu poate depăşi 40 000 $. Pentru
aceasta apelăm la plicaţia Solver, ca în figura următoare, avînd grijă să introducem
corespunzător celulele corespunzătoare astfel:
Celula ţintă : B15 deoarece se doreşte maximizarea profitului
Se selectează funcţia Max, corespunzător cerinţei problemei
Celulele ajustabile sunt B11:E11 deoarece reprezintă cheltuielile cu publicitatea.
Constrângerea se aplică pe celula F11 deoarece reprezintă totalul cheltuielilor cu
publicitatea.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 74/124
Se apasă butonul Solve pentru aflarea răspunsului optim. Odată ce Solver a identificat
o soluţiesunteţi întrebat dacă doriţi păstrareavalorilornoi obţinute.
Rezultatul va apărea scris in tabelul iniţial.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 75/124
Exerciţii
Deschideţi fişierul Angajati foaia de calcul tabel_angajaţi .
1. Introduceţi o nouă foaie de calcul numită rapoarte 2. Să se afişeze suma salariilor nete pe departamente (utilizaţi funcţia DSUM şi un tabel
de ipoteze cu o variabilă).3. Să se afişeze salariul maxim pe funcţii (DMAX) 4. Să se calculeze media salariilor pe filiale şi departamente. (DAVERAGE şi un tabel de
ipoteze cu două variabile) 5. Să se calculeze mărimea plăţilor lunare aferente unui credit de 120 milioane lei,
contractat pe 5 ani, pentru mai multe variante de rata a dobânzii (4%-12%) şi deperioade de rambursare (1-5 ani):
6. Deschideţi foaia de calcul goalseek. Ce salariu de încadrare ar trebui să aibă un angajatdacă la angajare negociază un salariu net în valoare de 3000 lei?
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 76/124
7. Deschideţi fişierul ExExcel2007 , foaia de calcul vanzari.
8. Clic pe butonul What-If Analysis din grupul Data Tools al filei Data şi alegeţi ScenarioManager. Apare fereastra Scenario Manager. Daţi clic pe butonul Add.
9. În fereastra Add Scenario, în caseta Scenario Name introduceţi textul “Cele mai bunevânzări” şi apăsaţi tasta Tab. În casetaChanging Cells specificati adresele celulelor pecare doriti sa le schimbati adica domeniulC5:C8
10. Dati click pe butonul OK din caseta de dialog Add Scenario. Apare caseta ScenarioValues in care se introduc valoricare reprezintă numarul posibil de vanzari: pentru C5 introduceţi valoarea 1 500 000 pentru C6 introduceţi valoarea 1 800 000 pentru C7 introduceţi valoarea 1 500 000 pentru C8 introduceţi valoarea 1 600 000
11. Click pe butonul Add pentru ava reintoarce in caseta Add Scenario.
12. Urmaţi aceiaşi paşi ca mai sus pentru a crea un nou scenariu numit ”Cele mai scăzutevânzari” . Pentruultimul pasintroduceti valorile: pentru C5 introduceţi valoarea800 000 pentru C6 introduceţi valoarea900 000 pentru C7 introduceţi valoarea700 000 pentru C8 introduceţi valoarea600 000
13. Pentru a vedea modificările în foaia de calculselectaţi mai întâi scenariul ”Cele maibune vânzari”, apoi apăsaţi butonul Show din caseta Scenario Manager.
14. Selectaţi apoi scenariul “Cele mai slabe vânzari” apasati butonul Show şi vizualizaţischimbările.
15. Pentru a vedea sumarul acestor scenarii (Summary Scenario) în aceeaşi casetă dedialog Scenario Manager apăsaţi butonul Summary. Se obţine:
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 77/124
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 78/124
Capitolul 5________________________________________
TABELE PIVOT
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 79/124
Tabele Pivot
Tabelele pivot reprezintă o facilitate puternică a Excelului ce permite însumarea şi analizadatelor în maniere diferite. Pe scurt, acestea permit însumarea datelor într-un câmp
(denumit câmp de tip date - Data Field) şi organizarea acestora corespunzător datelor în aldoilea câmp (denumit câmp de tip linie -Row Field). Avantajul tabelelor pivot este că ele
permit diferitelor câmpuri şi categorii să fie aranjate şi manipulate uşor prin pivotare.
Deschideţi fişierul ExExcel2003.xls foaia de calcul Filtrare, copiaţi-o într-un altfişier, schimbaţi-i numele în Tabele Pivot şi studiaţi modul de creare a unui tabel pivot.
În exemplul următor:
datele pot fi însumate pe sucursale - Branch
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 80/124
Pentru a crea o tabelă pivot
Selectaţi o singură celulă a sursei de date şi aplicaţi una din următoarele metode:
Clic pe fila Insert şi apoi Pivot Table din grupul Tables
Dacă sursa de date e formatată ca tabel, în fila Design, în grupul Tools subTableTools selectaţi Summarize With Pivot Table
După ce aţi aplicat oricare din cele două metode apare fereastra Create Pivot Table.
În mod implicit tabelul pivot va fi plasat într-un nou worksheet dar dacă doriţi să-l plasaţi într -o locaţie anume alegeţi locaţia după ce bifaţi opţiunea Existing Worksheet.Excel generează un format de tabel necompletat în zona din stânga foii de calcul. Se
deschide automat în dreapta fereastra PivotTable Field List.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 81/124
După ce creaţi un raport PivotTable sau PivotChart, utilizaţi Lista de câmpuriPivotTable pentru a adăuga câmpuri. Dacă doriţi să modificaţi un raport PivotTable sauPivotChart, utilizaţi Lista de câmpuri pentru a rearanja şi elimina câmpuri. În modimplicit, Lista de câmpuri PivotTable afişează două secţiuni: o secţiune în partea de sus
pentru adăugarea şi eliminarea câmpurilor şi o secţiune de aspect în partea de jos pentrurearanjarea şi repoziţionarea lor. Aveţi posibilitatea să fixaţi Lista de câmpuri PivotTable
în oricare parte a ferestrei şi să o redimensionaţi orizontal. De asemenea, aveţi posibilitatea să deblocaţi Lista de câmpuri PivotTable, caz în care se poate redimensionaatât vertical, cât şi orizontal.
Dacă nu vedeţi Lista de câmpuri PivotTable, asiguraţi-vă că faceţi clic pe raportulPivotTable sau PivotChart. Dacă tot nu vedeţi Lista de câmpuri PivotTable, pentru unraport PivotTable, în fila Options, în grupul Show/Hide, faceţi clic pe Field list, iar pentruun raport PivotChart, în fila Analyze, în grupul Show/Hide, faceţi clic pe Field list.
Pentru stabilirea poziţiei câmpurilor în tabelul pivot trageţi numele câmpului în zona
Report Filter, Column Labels, Row Labels sau ∑ Values în funcţie de modul în caredoriţi ca datele să fie prelucrate în raport. În zona ∑ Values se introduc de obicei datecărora li se poate aplica o operaţie aritmetică (date numerice), situaţie în care Excelintroduce implicit suma valorilor (subtotaluri) pentru fiecare schimbare în fiecare câmpdin zona de tip linie – Row.
În momentul în care dati clic pe tabelul pivot se activează două file: Options şi Design
În fila Option s aveţi următoarele grupuri:
Pivot Table: aici puteţi denumi tabelul în caseta Pivot Table Name sau puteţi stabili proprietăţile generale ale tabelului pivot, proprietăţi pe care le puteţi activa dinfereastra PivotTable Options
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 82/124
Active Field în care puteţi denumi câmpul activ.
În cazul în care câmpul activ esteValues (în care funcţia implicită pentru câmpurinumerice este SUM iar pentru celelalte COUNT) puteţi modifica metoda de calcul prinselectarea opţiunii Field Settings. Apare următoarea casetă de dialog:
În fila Summarize by puteţi alege o altă funcţie după care se va face calculul, iar în filaShow values as puteţi personaliza metoda de calcul pentru a calcula procente din totalulgeneral, din totalul pe rând sau coloană, pentru determinarea diferenţei faţă de un item,etc.
Pentru a realiza un calcul corect funcţia de bază trebuie să fie SUM şi o selectaţi iniţial înfila Summarize by.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 83/124
Dacă niciuna din opţiunile din lista propusă nu satisface cerinţele de analiză pe caredoriţi să le aplicaţi asupra datelor, Excel oferă posibilitatea adăugării unor câmpuricalculate.
Pentru aceasta, selectaţi o celulă oarecare din tabelul pivot pentru a activa fila Options.În grupul Tools clic pe butonul Formulas. Apare următoarea fereastră:
În caseta Name completaţi numele câmpului calculat iar în caseta Formula scrieţiformula de calcul pe baza căreia se vor completa datele din acest câmp. Spre exemplu, întabelul“comenzi” din foaia de calcul Lista?comenzi a fişierului Comenzi introducem în
tabelul pivot un nou câmp calculat “Valoare” obţinut prin înmulţirea cantităţii comandatecu preţul unitar.
Pentru aceasta, în caseta formula introducem numele câmpului “Quantity” selectând-uldin lista Fields şi apăsând butonul Insert Field. Scriem apoi de la tastatură operatorul ”*”şi introducem apoi numele celui de-al doilea câmp prin aceeaşi metodă.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 84/124
În tabelul pivot va apărea noul câmp calculat, Sum of Valoare:
Pentru a afişa lista câmpurilor calculate împreună cu formula utilizatădaţi clic pe butonul Formulas din grupul Tools al filei Options. În meniul care se afişează alegeţi ListFormulas. Excel va afişa lista într -un nou worksheet astfel:
Group. Tabelul pivot grupează valorile câmpurilor în funcţie de ordinea în careacestea au fost introduse în zona de câmpuri. Dacă doriţi să realizaţi o grupare
personalizată selectaţi itemii pe care doriţi să-i includeţi într -un grup şi apoiacţionaţi butonul Group selection din secţiunea Group a filei Options. Degruparea
se realizează prin acţionarea butonuluiUngroup.
În cazul în care doriţi o gruparesemiautomată a datelor (spre exemplu grupare
pe trimestre a datelor calendaristice) utilizaţiopţiunea Group Field care permite grupareaunui numar exact de itemi, gruparea datelorcalendaristice pe anumite intervale, etc. înfuncţie de tipul de date conţinute de câmpul pecare se realizează gruparea.
De exemplu, în foaia de calculdetalii_comenzi dacă realizăm un tabel pivot
cu produsele comandate pe date calendaristice,putem grupa aceste date trimestre.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 85/124
Prin colapsare (butonul din grupul Active field al filei Options) obţinem următorulrezultat:
Prin dublu clic pe orice valoare de sumarizare din tabelul pivot Excel afişează într -unnou worksheet datele din tabelul iniţial pe baza cărora s-a făcut subtotalul respectiv.
Puteţi de asemenea sorta datele din tabelul pivot utilizând opţiunile de sortare dingrupul Sort al filei Options. În grupul data aveţi opţiunile de actualizare a datelor dintabelul pivot când datele din tabelul sursă sunt modificate sau de a modifica proprietăţileconexiunii în situaţia în care tabelul pivot a fost generat pe baza datelor din surse externe.
Puteţi de asemenea renunţa la afişarea antetului de câmp sau la butoanele de structurăierarhică (+ sau -).
În fila Design aveţi la dispoziţie câteva opţiuni de afişare a datelor din tabelul pivot din punctul de vedere al aspectului raportului în vederea tipăririi.
Grafice pivot (Pivot Charts) În grupul Tools al filei Options, prin acţionarea butonului PivotChart se deschide
fereastra Insert Chart prin intermediul căreia puteţi alege tipul de grafic pe care doriţi
sa-l utilizaţi în reprezentarea datelor din tabelul pivot.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 86/124
Apare fereastra PivotChart Filter Pane care vă prezintă informaţii despre câmpurile
active din grafic. Puteţi oricând modifica modul de vizualizare, care să fie datele pe care
doriţi să le evidenţiaţi grafic şi puteţi modifica absolut toate câmpurile sau modul de
calcul, ca şi în cazul tabelelor pivot.
Graficul pivot poate fi formatat în acelaşi mod în care se formatează graficele
obişnuite după cum aţi văzut în Capitolul 1.
La un clic pe zona graficului pivot se activează filele Design, Layout, Format, Analyze
prin intermediul cărora se poate interveni asupra elementelor graficului: Titlu, axe, linii
de tendinţă etc.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 87/124
Exerciţii
Deschideţi foaia de calcul Subtotaluri.
Creaţi un tabel pivot ce are ca sursă tabelul existent în această foaie cu următoarea
structură:
Coloana Product – câmp de tip column;
Coloana Branch – câmp de tip row;
Coloana No of Sales – câmp de tip ∑ Value;
Cu ajutorul tabelului pivot vizualizaţi subtotalurile pe produse, apoi pe sucursală.
Adăugaţi funcţiile Min, Max, Avarage pentru a vizualiza valoarea maximă, minimă şi
media vânzărilor pe sucursale.
Scoateţi funcţiile Min, Max şi Avarage adaugate la punctul 4.
Afişaţi următoarele informaţii de analiză a vânzărilorsucursalelor din provincie faţă de
Bucureşti:
Cu cât a vândut fiecare sucursală, mai mult sau mai puţin, faţă de Bucureşti;
Cu cât la sută avândut fiecare sucursală, mai mult sau mai puţin, faţă de Bucureşti;
Care este contribuţia fiecărui produs la total vânzări pe societate?
Care este contribuţia fiecărei sucursale la total vânzări pe societate?
Care este contribuţia fiecărui produs din cadrul fiecărei sucursale la total vânzări pe
societate?
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 88/124
Creaţi un grafic având ca sursă un tabel pivotce afişează vânzările produselorpefiecare
sucursală.
Creaţi un grafic având ca sursă un tabel pivotce afişează vânzările sucursalelorpe fiecare
produs.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 89/124
Capitolul 6________________________________________
MACROINSTRUCŢIUNI
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 90/124
MACROINSTRUC ŢIUNI – MACROS
Macroinstrucţiunile pot fi gândite ca mici programe ce conţin o listă de instrucţiuni.
Ele combină un număr de comenzi, funcţii şi instrucţiuni într-o singură procedură ce
poate fi apelată uşor.
O macroinstrucţiune Excel poate executa tot ce poate executa Excel. În forma sa
simplă, ea poate automatiza mici sarcini repetitive. În mâna unui expert, se pot crea
aplicaţii foarte elaborate.
Crearea şi modificarea unei macroinstrucţiuni
Primul pas în crearea unei macroinstrucţiuni este planificarea procesului pe care
doriţi să-l automatizaţi. Calculatoarele actuale sunt destul de rapide astfel că
înregistrarea unui pas în plus nu produce întârzieri semnificative în schimb lipsa unuia
poate afecta întregul proces şi sunteţi nevoiţi să reînregistraţi macroinstrucţiunea.
După planificarea procesului puteţi crea o macroinstrucţiune utilizând butonul Record
Macro din lista Macros a grupului cu acelaşi nume din fila View. În caseta de dialog care apare:
Se poate completa numele macroinstrucţiunii şi descrierea sa în Macro Name: şiDescription
Se poate completa o literă în Shortcut Key dacă se doreşte ca macroinstrucţinueadefinită să poată fi invocată şi prin apăsarea tastei Ctrl şi litera specificată
Folosind secţiunea Store macro in se poate alege locul de memorare a
macroinstrucţiunii curente, care poate fi:
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 91/124
Personal Macro Workbook - dacă se foloseşte pentru uz personal. Aceastămacroinstrucţiune personală este memorată într-o carte de lucru (PersonalMacro Workbook) separată, numită PERSONAL.xlsb şi care trebuie să fie
înregistrată în subcatalogul "C:\Program Files \Microsoft Office \Office\XLStart". Când se lansează Excelul, această macroinstrucţiune va fi încărcatăautomat în memorie.
This Workbook - dacă macro este necesară numai în fişierul actual New Workbook - dacă macro este creată pentru toate fişierele noi
Se selectează butonul OK.
Se execută toţi paşii care se doresc să se înregistreze în macroinstrucţiune pentru a fiexecutaţi la lansarea acesteia.
Când se consideră că s-a terminat se acţionează butonul de Stop Recording careapare în locul butonului Record Macro din grupul Macros al filei View .
Acţiunile executate au fost înregistrate în Visual Basic. Înregistrarea poate fi găsită într-un modul în funcţie de opţiunea indicată în Store macro in.
Pentru modificarea unei macroinstrucţiuni înregistrate puteţi şterge şi reeditamacroinstrucţiunea sau puteţi face o modificare rapidă în editorul Visual Basic adăugând
sau modificând o instrucţiune a sa.
Să înregistrăm o macroinstrucţiune care să formateze titlul şi să înregistreze textul
„Raport Vânzări”
Pentru acesta :
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 92/124
- Într-un nou worksheet selectaţi celula B4 - Utilizaţi comanda Record Macro
În fereastra care apare completaţi câmpurile ca în figura:
- Apăsaţi butonul OK. Din acest moment începe înregistrarea tuturor acţiunilor pecare le executaţi, inclusiv a greşelilor.
- Efectuaţi următoarele acţiuni:
a) Selectaţi domeniul B4:H4 b) Schimbaţi tipul fontului în Arial c) Schimbaţi dimensiunea fontului la 12
d) Selectaţi Bold
e) Selectaţi Merge& Center
f) Selectaţi chenarul Top&Bottom Borders
g) Selectaţi fundalul albastru deschis
- Scrieţi textul “Raport vanzari” - Apăsaţi Enter - Acţionaţi comanda Stop Recording din fila View, lista Macros
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 93/124
Utilizarea adreselor relative în macroinstrucţiuni
Adresele relative şi absolute determină modul în care o macroinstrucţiune va
înregistra locaţia unei celule.
Macroinstrucţiunea “Titlu” înregistrată mai sus utilizează adrese absolute, astfel căindiferent de celula selectată înainte de rularea macroinstrucţiunii se va crea un titlu în
domeniul B4:H4.
Adresele absolute utilizează locaţia exactă înregistrată în macroinstrucţiune. Această
modalitate de înregistrare a locaţiilor celulelor este implicită.
Când se utilizează adrese relative, se înregistrează doar poziţia domeniului destinaţie
(în care se va depune rezultatul macroinstrucţiunii) faţă de celula selectată înainte de
rularea macroinstrucţiunii.
Spre exemplu: selectăm celula B2 înainte de înregistrare dar rezultatul va fi trecut în
domeniulB4:H4, cu două rânduri mai jos. Astfel că dacă selectăm celula A4 şi
rulămmacroinstrucţiunea pe care am înregistrat-o folosind adrese relative, rezultatul va
fi trecut în domeniul aflat cu două rânduri mai jos începând cu celula A4.
Să înregistrăm o macroinstrucţiune care va insera data curentă folosind funcţia
TODAY().
Pentru a nu genera o modificare dinamică a datei, după inserare, vom copia rezultatul
într-o altă celulă folosind opţiunea Values and number formats a comenzii Paste Special .
Această macroinstrucţiune o salvăm în Personal macro workbook pentru a putea fi
utilizată şi în alte fişiere Excel.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 94/124
Pentru aceasta, într-un nou workbook:
- Selectaţi celula B4
- Clic pe butonul Record Macro din fila View, lista Macros
- În căsuţa de dialog Record Macro introduceţi datele ca în figura de mai jos:
Prin aceasta asignaţi macroinstrucţiunii o combinaţie de taste care va permite
apelarea mai rapidă a macroinstrucţiunii.
- Clic OK pentru a începe înregistrarea
- Clic pe butonul din fila View lista Macros. Acestbuton este activ când apare o casetă de accent în jurul butonului. - În celula B4 scrieţi “= TODAY()” şi apăsaţi ENTER - Cu celula B4 selectată formataţi celula ( clic dreapta, Format Cells, în tab-ul
Number selectaţi Date apoi costom şi scrieţi formatul dd-mmm-yyyy )
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 95/124
- Copiaţi celula B4 - Clic dreapta în celula B6 şi alegeţi Paste Special, Values and number formats.
- Ştergeţi celula B4 - Clic pe butonul Stop Recording- Salvaţi şi închideţi fişierul Excel. După ce aţi salvat un macro în Personal macro
workbook, la părăsirea Excel-ului sunteţi întrebaţi dacă doriţi să salvaţimodificările
Apăsaţi butonul Yes.
Notă: Pentru a accesa mai rapid opţiunile de înregistrare ale unei macroinstrucţiuni
aveţi nevoie de o filă suplimentară pe Ribbon numită Developer. Pentru a afişa
această filă clic pe Microsoft Office Button şi apoi pe Excel Options. În categoria
Popular selectaţi Show Developer Tab In The Ribbon şi daţi OK. Pentru a
înregistra o macroinstrucţiune daţi clic pe Record macro din grupul Code al filei
Developer.
Rularea uneimacroinstrucţiuni
Dacă aţi asignat o combinaţi de taste unei macroinstrucţiuni, rularea acesteia se faceimediat după tastarea combinaţiei respective.
În exemplul anterior am stocat o macroinstrucţiune în Personal macro workbook la
deschiderea unui nou Workbook şi tastarea combinaţiei CTRL+d în celula situată cu două
rânduri mai jos faţă de celula curentă va fi afişată data curentă (datorită faptului că la
înregistrare s-au folosit adrese relative).
Un alt mod de a rula o macroinstrucţiune este de a apela comanda Run din fereastra
Macro pe care o deschideţi utilizând butonul Macros din grupul Code al filei Developer
sau din fila View, lista Macros.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 96/124
Este destul de incomod să activaţi o macroinstrucţiune în acest mod, de aceea Excel
oferă câteva posibilităţi de a reduce timpul necesar activării unui macro prin ataşarea
acestuia unui buton prin a cărui simplă apăsare rulăm macroinstrucţiunea.
Pentru aceasta, deschideţi fereastra Excel Options(fie cu Microsoft Office Button,
Excel Options, fie cu clic pe săgeata din dreapta barei de acces rapid şi apoi clic pe
opţiunea More Comands)
Din fereastra Excel Options, Customize, clic pe săgeata din dreptul casetei Choose
Commands From şi alegeţi opţiunea Macros. Daţi clic pe numele macroinstrucţiunii pecare vreţi să o asignaţi unui buton, clic pe Add şi apoi OK.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 97/124
Rularea macroinstrucţiunii se face automat la apăsarea butonului corespunzător de
pe bara de acces rapid.
Puteţi schimba imaginea asociată butonului din fereastra Excel Options, clic pe
butonul asociat macrocomenzii şi apoi apăsaţi butonul Modify… Se va deschide fereastra
Modify Button î n care puteţi selecta o opţiune din cele 160 disponibile.
De asemenea, puteţi asocia o macroinstrucţiune unui buton de acţiune sau unui
controldin lista Insert a grupului Controls din fila Developer.
În grupul Form Controls, primul control, Button poate fi asociat unei macoinstrucţiuni
astfel: selectaţi butonul şi pe foaia de calcul desenaţi controlul ţinând apăsat butonulstânga al mouse-ului. La eliberarea butonului stânga al mouse-ului se deschide fereastra:
Buton asociat macroinstruc iunii
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 98/124
Se alege din listă numele macroinstrucţiunii ce va fi asignată butonului şi se apasă OK.
Din acest moment orice apăsarea butonul va rula macroinstrucşunea ataşată. Pentru
a edita proprietăţile acestui control daţi clic dreapta pe buton şi din meniul care apare
alegeţi opţiunea de formatare dorită:
- editarea textului de pe buton (Edit Text)- formatarea fontului (Format Control) etc.
-
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 99/124
-
Exerciţii
Întrucât prin aplicarea unui filtru avansat rezultatul filtrări este obţinut doar pentru o
singură aplicare a acestui mecanism (la modificarea criteriului nu se modifică şi
rezultatul filtrării, asrfel că pentru a filtra datele după un alt criteriu trebuie să aplicaţi
din nou filtrarea avansată) putem automatiza acest proces prin înregistrarea unei
macroinstrucţiuni de filtrare şi asignarea acesteia unui buton de acţiune.
Deoarece în urma aplicării unui filtru rezultatul primei filtrări ar putea cuprinde un
număr mai mare de înregistrări decât cele care urmează e necesar să înregistrăm o altă
macroinstrucţiune care să readucă froaia de calcul în starea iniţială.
1. Deschideţi fişierul Angajaţi. Vom înregistra o macroinstrucţiune care să filtreze
angajaţii după departament şi funcţie.
Într-un nou worksheet copiaţi antetul coloanelor Departament şi Funcţie îndomeniul A1:B1
Vom utiliza domeniul A1:B2 ca şi criteriu
Zona în care vom copia rezultatele va începe cu celula A5
Începeţi înregistrarea macroinstrucţiunii activând comanda Record Macro dingrupul Macros al filei View. Denumiţi macroinstrucţiunea “filtrare” şi alegeţi calocaţie This Workbook
Urmaţi paşii de aplicare a filtrării avansate şi acţionaţi butonul Stop Recording.
Într-un nou worksheet copiaţi antetul coloanelor Departament şi Funcţie îndomeniul A1:B1
Vom utiliza domeniul A1:B2 ca şi criteriu.
Zona în care vom copia rezultatele va începe cu celula A5
Începeţi înregistrarea macroinstrucţiunii activând comanda Record Macro dingrupul Macros al filei View. Denumiţi macroinstrucţiunea “filtrare” şi alegeţi calocaţie This Workbook
Urmaţi paşii de aplicare a filtrării avansate şi acţionaţi butonul Stop Recording.
2. Înregistram o macroinstrucţiune pe care o denumim “revenire” care va şterge
rezultatul filtrării de la exerciţiul 1.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 100/124
Începeţi înregistrarea macroinstrucţiunii activând comanda Record Macro dingrupul Macros al filei View. Denumiţi macroinstrucţiunea “revenire” şi alegeţi calocaţie This Workbook
Selectaţi liniile care conţin rezultatul de la exerciţiul 1 (liniile de la 5 la 113inclusiv)
Daţi un clic în afara selecţiei şi apoi acţionaţi butonul Stop Recording.
3.Introduceţi pe foaia de calcul, în dreptul zonei în care aţi înregistrat criteriile două
butoane de acţiune. Unuia îi asociaţi macroinstrucţiunea “filtrare” iar celuilalt
macroinstrucţiunea “revenire” ca î n figură.
4. Asignaţi cele două macroinstrucţiuni unor butoane pe bara de acces rapid
schimbând imaginea asociată butonului pentru”filtrare” cu şi butonului pentru
“revenire” cu .
5. Verificaţi funcţionalitatea celor două comenzi afişând pe rând următoarele date:
- Care sunt programatorii din departamentul IT?- Care sunt operatorii din departamentul IT?- Care sunt economiştii de la departamentul Marketing?- Care sunt economiştii de la Resurse Umane?- Dar cei de la IT?
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 101/124
Capitolul 7_________________________________________
TEHNICI AVANSATE DE EDITARE ŞI DE FORMATARE A
FOILOR DE CALCUL
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 102/124
Tehnici de editare.
Opţiunea Autofill
Există o serie de comenzi prescurtate (shortcuts) pentru a completarea unui domeniucu date.
Umplerea domeniului în mod automat
Există două comenzi prescurtate simple pentru a realiza aceasta.
Metoda “Ctrl & Enter”
Se selectează un domeniu cu ajutorul mouseului Se introduce o anumită dată (de ex. 1300) Această dată apare în prima celulă a domeniului Se ţin apăsate tastele Ctrl şi Enter Data introdusă va apărea în tot domeniul
Metoda indicatorului de umplere - “Fill handle”
Se introduce data într-o celulă Se mută cursorul în colţul din dreapta jos a celulei Cursorul se va transforma într-o cruciuliţă; aceasta este indicatorul de umplere - Fill
Handle Se păstrează apăsat butonul stâng al mouse-ului şi se trage cruciuliţa de-a lungul
domeniului destinaţie alăturat din dreapta sau în jos
Toate celulele domeniului destinaţie vor avea aceeaşi dată copiată în ele.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 103/124
În cazul în care doriţi să introduceţi într-un domeniu o serie liniară (de exemplu 2001,
2002, 2003, …) introduceţi primele două elemente ale seriei aritmetice în primele două
celule, le selectaţi şi folosind aceiaşi paşi ca mai sus trageţi cursorul până la valoarea
dorită.
La fiecare acţionare a acestei comenzi de umplere automată apare un smart tag.Un clic pe săgeata ataşată va deschide un meniu cu patru opţiuni ca în imaginea de maisus.
În cazul în care se copiază o singură valoare, opţiunea Copy Cells este implicită, la fel
ca în cazul al doilea când se selectează două valori ale seriei şi opţiunea Fill Series esteimplicită. Puteţi folosi acest meniu în situaţia în care doriţi să copiaţi cele două valori din
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 104/124
serie într-un domeniu sau să copiaţi doar formatul celulelor respective. Puteţi realizacopierea celulelor care compun o serie folosind tasta CTRL când trageţi cursorul.
Meniul prescurtat de umplere automată - “AutoFill”
Pentru utilizarea mai sofisticată a facilităţii AutoFill, se poate utiliza meniul prescurtat..
Pentru utilizarea meniului:
Se poziţionează cursorul în colţul din stânga jos a celulei pentru a identificaindicatorul de umplere
Se apasă butonul din dreapta al mouse-ului şi se trage indicatorul în lunguldomeniului unde se doreşte să se facă extinderea
Va apare meniul prescurtat AutoFill cu mai multe opţiuni. În cazul în care datele suntde tipul dată calendaristică aveţi posibilitatea de umplere a celulelor cu date avândzile, luni diferite (în ordine crescătoare) sau ani diferiţi.
Opţiunea Linear Trend utilizează metoda lineară a celor mai mici pătrate pentru aextinde tendinţa
Opţiunea Growth Trend utilizează o metodă similară pentru a crea o tendinţă decreştere bazată pe produse.
Opţiunea Series deschide o fereastră cu acelaşi nume în care puteţi crea serii numericepersonalizate stabilind pasul de creştere în caseta Step value (raţia, în limbajmatematic, pentru progresia aritmetică ce corespunde tipului Linear sau pentru ceageometrică Growth) şi limita maximă până la care va creşte seria în caseta Stop value
(în cazul în care domeniul selectat are mai multe celule decât numărul de termeni alseriei aceştia se vor repeta).
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 105/124
Această opţiune mai poate fi activată şi din meniul Fill din grupul Editing al filei Home.
Tot în acest meniu aveţi pe lângă opţiunile pe care le cunoaşteţi deja şi comanda
Justify. Această comandă este utilă în situaţia în care doriţi ca un text pe care l-aţi scris
într-o singură celulă să fie împărţit în mai multe celule.
În cazul în care aţi scris un text mai lung într-o celulă, selectaţi un domeniu în care
doriţi să fie returnat rezultatul şi apoi utilizaţi comanda justify.
Crearea seriior de text
Excel este capabil să completeze serii de text, dacă acestea se includ în facilitateaAutoFill..
Pentru a extinde seriile de date:
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 106/124
Se selectează celulele de unde se doreşte să se înceapă seriile
Se poziţionează cursorul mouseului în colţul din dreapta jos a ultimei celule dindomeniu până se obţine cruciuliţa - fill handle
Se apasă butonul stâng al mouseului şi se trage peste celulele unde se doreşte să seextindă seria
AutoFill va umple seria text În exemplul de mai sus, dacă s-a introdus Jan, Feb, Excel va şti să continue să
completeze cu seria July, September, etc.
Acest lucru este posibil deoarece Excel conţine o serie de liste predefinite. În cazul încare folosiţi în mod repetat o anumită listă cum ar fi lista funcţiilor sau lista produselor
puteţi salva această listă personalizată şi o puteţi folosi cu metoda AutoFill.
Pentru a crea o listă personalizată va trebui să efectuaţi următorii paşi:
1. Clic pe Microsoft Office Button şi apoi pe Excel Options
2. Selectaţi categoria Popular. În zona Top Options For Working With Excel Areadaţi clic pe butonul Edit Custom List
3. Se deschide fereastra Options
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 107/124
4. Având selectată opţiunea NEW LIST în zona Custom lists scrieţi în zona List
Entriestoate elementele pe care vreţi să le includeţi în listă
5. Clic pe butonul Add şi apoi OK.
Puteţi de asemenea să importaţi o listă pe care o aveţi scrisă într -o foaie de calcultot înfereastra Optionsintroducând în zona Import list from cells adresa domeniului în careaveţi lista şi apăsând apoi butonul Import.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 108/124
Tehnici de formatare
Formatarea condiţionată
Utilizați un format condițional pentru a vă ajuta să explorați vizual și să analizați date, să detectațiprobleme critice și să identificați modele și tendințe.
Despre formatarea condițională
Când analizați date, vă puneți întrebări precum:
Care sunt excepțiile în sinteza de profituri pentru ultimii cinci ani? Care sunt tendințele într -un sondaj de opinie de marketing pentru ultimii doi ani? Cine a vândut de mai mult de 50.000 de lei în această lună? Care este distribuția generală de vârstă a angajaților? Care produse au creșteri în vânzări mai mari de 10% din an în an? Care sunt cei mai buni și cei mai slabi studenți dintr -o clasă de boboci?
Formatarea condițională oferă răspunsuri la aceste întrebări ușurând evidențierea celulelor interesantesau a zonelor de celule, punerea accentului pe valori neobișnuite și vizualizarea datelor utilizând barede date, scale de culori și seturi de pictograme. Un format condițional modifică aspectul unei zone decelule pe baza unei condiții (sau criteriu). În cazul în care condiția este adevărată, zona de celule este
formatată pe baza acelei condiții; în cazul în care condiția este falsă, zona de celule nu este formatată pe baza acelei condiții.
NOTĂ Când se creează un format condițional, aveți posibilitatea să faceți referire la alte celule dinfoaia de lucru, cum ar fi =FY2006!A5, dar nu aveți posibilitatea să utilizați referințe externe în altregistru de lucru.
Formatarea tuturor celulelor utilizând o scală în două culori
Scalele în culori sunt ghizi vizuali care ajută la înțelegerea distribuției și variației datelor. O scală dedouă culori permite compararea unei zone de celule utilizând o gradație de două culori. Umbra culoriireprezintă valori mai mari sau mai mici. De exemplu, într -o scală care conține culorile verde și roșu,aveți posibilitatea să specificați că celulele cu valori mai mari au o culoare mai verde iar cele cu valorimai mici au o culoare mai roșie.
Formatare rapidă
1. Selectați o zonă de celule sau asigurați-vă că celula activă este într -un tabel sau într-un raportPivotTable.
2. În fila Pagină de pornire, în grupul Stiluri, faceți clic pe săgeata de lângă Formatare condițională,apoi pe Scară culori.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 109/124
3. Selectați o scală din două culori.
SFAT Țineți cursorul peste pictogramele scalele de culori pentru a vedea care dintre ele esteo scală de două culori. Culoarea de sus reprezintă valorile mai mari iar culoarea de josreprezintă valorile mai mici.
Formatare complexă
1. Selectați o zonă de celule sau asigurați-vă că celula activă se află într -un tabel sau într-un raportPivotTable.
2. În fila Pagină de pornire, în grupul Stiluri, faceți clic pe săgeata de lângă Formatare condițională,apoi pe Gestionare reguli.
Se afișează caseta de dialog Gestionar reguli formatare condițională.
3. Variante disponibile: Pentru a adăuga o formatare condițională, faceți clic pe Regulă nouă.
Se afișează caseta de dialog Regulă nouă de formatare.
Pentru a modifica o formatare condițională, procedați astfel: 1. Asigurați-vă că este selectată foaia de lucru sau tabelul potrivit în caseta listă Afișare
reguli formatare pentru.
2. Opțional, modificați zona de celule făcând clic pe Restrângere dialog în caseta Seaplică la pentru a ascunde temporar caseta de dialog, selectând zona nouă de celule în
foaia de lucru, apoi selectând Extindere dialog .3. Selectați regula, apoi faceți clic pe Editare regulă.
Se afișează caseta de dialog Editare regulă de formatare.
4. Sub Se selectează un tip de regulă, faceți clic pe Se formatează toate celulele pe baza valorilorlor.
5. Sub Se editează descrierea regulii:, în caseta listă Stil format, selectați Scală 2-culori.6. Selectați un Tip pentru Minim și Maxim. Efectuați una din următoarele: Formatarea celor mai mici și a celor mai mari valori Selectați Cea mai mică valoare și
Cea mai mare valoare.
În acest caz, nu introduceți o Valoare pentru Minim și Maxim.
Formatarea unei valori de număr, dată sau oră Selectați Număr, apoi introduceți o Valoare pentru Minim și Maxim.
Formatarea unui procentaj Selectați Procent, apoi introduceți o Valoare pentru Minim șiMaxim.
Valorile valide sunt între 0 și 100. Nu introduceți semnul procent.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 110/124
Utilizați semnul procent atunci când doriți să vizualizați toate valorile proporțional dincauză că distribuția valorilor este proporțională.
Formatarea unui procentaj Selectați Procent, apoi introduceți o Valoare pentru Minim șiMaxim.
Valorile procentuale valide sunt între 0 și 100. Nu este posibilă utilizarea unei valori procentuale dacă zona de celule conține mai mult de 8.191 puncte de date.
Utilizați un procentaj atunci când doriți să vizualizați un grup de valori mari (cum ar fi primele douăzeci de procente) într -o culoare și valorile mici (cum ar fi ultimele douăzecide procente) în altă culoare, deoarece ele reprezintă valorile extreme care pot determina ovizualizare asimetrică a datelor dvs.
Formatarea rezultatului unei formule Selectați Formulă, apoi introduceți o Valoare pentruMinim și Maxim.
Formula trebuie să returneze o valoare de număr, dată sau oră. Începeți formula cusemnul egal (=). Nu se aplică niciun format pentru formulele nevalide. Se recomandă sătestați formula în foaia de lucru pentru a vă asigura că nu returnează o valoare de er oare.
Valorile Minim și Maxim sunt valorile minime și maxime pentru zona de celule. Asigurați-vă că valoarea Minim este mai mică decât valoarea Maxim.
Aveți posibilitatea să alegeți un alt Tip pentru Minim și Maxim. De exemplu, alegeți unNumăr Minim și un Procent Maxim.
7. Pentru a alege o scală de culori Minim și Maxim, faceți clic pe Culoare pentru fiecare, apoi selectațio culoare.
Dacă doriți să alegeți culori suplimentare sau să creați o culoare particularizată, faceți clic peMai multe culori.
Scala de culori pe care ați selectat-o se afișează în caseta Examinare.
Formatarea celulelor utilizând o scală de trei culori
Scalele în culori sunt ghizi vizuali care ajută la înțelegerea distribuției și variației datelor. O scală detrei culori permite compararea unei zone de celule utilizând o gradație de trei culori. Umbra culoriireprezintă valori mai mari, medii sau mai mici. De exemplu, într -o scală care conține culorile verde,
galben și roșu, aveți posibilitatea să specificați că celulele cu valori mai mari au culoarea verde, celecu valori mijlocii au culoarea galbenă iar cele cu valori mai mici au culoarea roșie.
Formatare rapidă
1. Selectați o zonă de celule sau asigurați-vă că celula activă se află într -un tabel sau într-un raportPivotTable.
2. În fila Pagină de pornire, în grupul Stiluri, faceți clic pe săgeata de lângă Formatare condițională,apoi pe Scară culori.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 111/124
3. Selectați o scală din trei culori. Culoarea de sus reprezintă valorile mai mari, culoarea din centrureprezintă valorile din mijloc, iar culoarea de jos reprezintă valorile inferioare.
SFAT Țineți cursorul peste pictogramele scalele de culori pentru a vedea care dintre ele esteo scală de trei culori.
Formatare complexă
1. Selectați o zonă de celule sau asigurați-vă că celula activă se află într -un tabel sau într-un raportPivotTable.
2. În fila Pagină de pornire, în grupul Stiluri, faceți clic pe săgeata de lângă Formatare condițională,apoi pe Gestionare reguli.
Se afișează caseta de dialog Gestionar reguli formatare condițională.
3. Variante disponibile: Pentru a adăuga o formatare condițională, faceți clic pe Regulă nouă.
Se afișează caseta de dialog Regulă nouă de formatare.
Pentru a modifica o formatare condițională, procedați astfel:
1. Asigurați-vă că este selectată foaia de lucru sau tabelul potrivit în caseta listă Afișarereguli formatare pentru.
2. Opțional, modificați zona de celule făcând clic pe Restrângere dialog în caseta Seaplică la pentru a ascunde temporar caseta de dialog, selectând zona nouă de celule în
foaia de lucru, apoi selectând Extindere dialog .3. Selectați regula, apoi faceți clic pe Editare regulă.
Se afișează caseta de dialog Editare regulă de formatare.
4. Sub Se selectează un tip de regulă, faceți clic pe Se formatează toate celulele pe baza valorilorlor.
5. Sub Se editează descrierea regulii, în caseta listă Stil format, selectați Scală 3-culori.6. Selectați un Tip pentru Minim, Punct median și Maxim. Efectuați următoarele: Formatarea valorilor minime și maxime Selectați un Punct median.
În acest caz, nu introduceți o Valoare pentru Minim și Maxim.
Formatarea unei valori de număr, dată sau oră Selectați Număr, apoi introduceți o Valoare pentru Minim, Punct median și Maxim.
Formatarea unui procentaj Selectați Procent, apoi introduceți o Valoare pentru Minim,Punct median și Maxim.
Valorile valide sunt între 0 și 100. Nu introduceți semnul procent.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 112/124
Utilizați semnul procent atunci când doriți să vizualizați toate valorile proporțional dincauză că distribuția valorilor este proporțională.
Formatarea unui procentaj Selectați Procent, apoi introduceți o Valoare pentru Minim,Punct median și Maxim.
Valorile procentuale valide sunt între 0 și 100. Nu este posibilă utilizarea unei valori procentuale dacă zona de celule conține mai mult de 8.191 puncte de date.
Utilizați un procentaj atunci când doriți să vizualizați un grup de valori mari (cum ar fi primele douăzeci de procente) într -o culoare și valorile mici (cum ar fi ultimele douăzecide procente) în altă culoare, deoarece ele reprezintă valorile extreme care pot determina ovizualizare asimetrică a datelor dvs.
Formatarea rezultat de formulă Selectați Formulă, apoi introduceți o Valoare pentru Minim,Punct median și Maxim.
Formula trebuie să returneze o valoare de număr, dată sau oră. Începeți formula cusemnul egal (=). Nu se aplică niciun format pentru formulele nevalide. Se recomandă sătestați formula în foaia de lucru pentru a vă asigura că nu returnează o valoare de eroare.
7. Valorile Minim, Punct median și Maxim sunt valorile minime, punct median și maxime
pentru zona de celule. Asigurați-vă că valoarea Minim este mai mică decât valoarea Punctmedian și că aceasta este, la rândul ei, mai mică decât valoarea Maxim.
Aveți posibilitatea să alegeți un alt Tip pentru Minim, Punct median și Maxim. Deexemplu, alegeți un Număr Minim, un Procentaj Punct median și un Procent Maxim.
În multe cazuri, valoarea pentru Punct median de 50 este cea mai bună, dar aveți
posibilitatea să reglați această valoare pentru a se potrivi cu cerințele dvs.
8. Pentru a alege o scală de culori Minim, Punct median și Maxim, faceți clic pe Culoare pentrufiecare, apoi selectați o culoare.
Dacă doriți să alegeți culori suplimentare sau să creați o culoare particularizată, faceți clic peMai multe culori.
Scala de culori pe care ați selectat-o se afișează în caseta Examinare.
Formatarea celulelor utilizând barele de date
O bară de date permite vizualizarea valorii unei celule relativ la alte celule. Lungimea barei de datereprezintă valoarea din celulă. O bară mai lungă reprezintă o valoare mai mare, în timp ce o bară maiscurtă reprezintă o valoare mai mică. Barele de date sunt utile pentru a observa numerele mai mari saumai mici, mai ales atunci când sunt prezente cantități mari de date, cum ar fi jucăriile vândute cel mai
bine și cel mai slab într -un raport de vânzări de sărbători.
Formatare rapidă
1. Selectați o zonă de celule sau asigurați-vă că celula activă se află într -un tabel sau într-un raportPivotTable.
2. În fila Pornire, în grupul Stil, faceți clic pe săgeata de lângă Formate condiționale, pe Bare dedate, apoi selectați o pictogramă de bară de date.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 113/124
Formatare complexă
1. Selectați o zonă de celule sau asigurați-vă că celula activă se află într -un tabel sau într-un raportPivotTable.
2. În fila Pagină de pornire, în grupul Stiluri, faceți clic pe săgeata de lângă Formatare condițională,apoi pe Gestionare reguli.
Se afișează caseta de dialog Gestionare reguli formatare condițională.
3. Alegeți una dintre următoarele:
Pentru a adăuga o formatare condițională, faceți clic pe Regulă nouă.
Se afișează caseta de dialog Regulă nouă de formatare.
Pentru a modifica o formatare condițională, procedați astfel: 1. Asigurați-vă că este selectată foaia de lucru sau tabelul potrivit în caseta listă Afișare
reguli formatare pentru.
2. Opțional, modificați zona de celule făcând clic pe Restrângere dialog în caseta Seaplică la pentru a ascunde temporar caseta de dialog, selectând zona nouă de celule în
foaia de lucru, apoi selectând Extindere dialog .3. Selectați regula, apoi faceți clic pe Editare regulă.
Se afișează caseta de dialog Editare regulă de formatare.
4. Sub Se selectează un tip de regulă, faceți clic pe Se formatează toate celulele pe baza valorilorlor.
5. Sub Se editează descrierea regulii, în caseta listă Stil format, selectați Bară de date.6. Selectați un Tip pentru Cea mai scurtă bară și Cea mai lungă bară. Efectuați una din următoarele:
Formatarea valorilor minime și maxime Selectați Cea mai mică valoare și Cea mai marevaloare.
În acest caz, nu introduceți o Valoare pentru Cea mai scurtă bară și Cea mai lungăbară.
Formatarea unei valori de număr, dată sau oră Selectați Număr, apoi introduceți o Valoare pentru Cea mai scurtă bară și Cea mai lungă bară.
Formatarea unui procent Selectați Procent, apoi introduceți o Valoare pentru Cea mai scurtăbară și Cea mai lungă bară.
Valorile valide sunt între 0 și 100. Nu introduceți semnul procent.
Utilizați semnul procent atunci când doriți să vizualizați toate valorile proporțional dincauză că distribuția valorilor este proporțională.
Formatarea unui procentaj Selectați Procentaj, apoi introduceți o Valoare pentru Cea maiscurtă bară și Cea mai lungă bară.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 114/124
Valorile procentuale valide sunt între 0 și 100. Nu este posibilă utilizarea unei valori procentuale dacă zona de celule conține mai mult de 8.191 puncte de date.
Utilizați un procentaj atunci când doriți să vizualizați un grup de valori mari (cum ar fi primele douăzeci de procente) într -o culoare și valorile mici (cum ar fi ultimele douăzeci
de procente) în altă culoare, deoarece ele reprezintă valorile extreme care pot determina ovizualizare asimetrică a datelor dvs.
Formatarea unui rezultat de formulă Selectați Formulă, apoi introduceți o Valoare pentruCea mai scurtă bară și Cea mai lungă bară.
Formula trebuie să returneze o valoare de număr, dată sau oră. Începeți formula cusemnul egal (=). Nu se aplică niciun format pentru formulele nevalide. Se recomandă sătestați formula în foaia de lucru pentru a vă asigura că nu returnează o valoare de eroare.
7. Asigurați-vă că valoarea pentru Cea mai scurtă bară este mai mică decât valoare pentru Cea
mai lungă bară. Aveți posibilitatea să alegeți un alt Tip pentru Cea mai scurtă bară și Cea mai lungă bară.
De exemplu, alegeți un Număr pentru Cea mai scurtă bară și un Procent pentru Cea mailungă bară.
8. Pentru a alege o scală de culori pentru Cea mai scurtă bară și Cea mai lungă bară, faceți clic peCuloare bară.
Dacă doriți să alegeți culori suplimentare sau să creați o culoare particularizată, faceți clic peMai multe culori.
Culoarea barei pe care ați selectat-o se afișează în caseta Examinare.
9. Pentru a afișa numai bara de date, fără valoarea celulei, selectați Se afișează doar bara.
Formatarea tuturor celulelor utilizând un set de pictograme
Utilizați un set de pictograme pentru a adnota și clasifica datele în trei până la cinci categorii separate de o valoare prag. Fiecare pictogramă reprezintă o zonă de valori. De exemplu, în setul de pictograme3 săgeți, săgeata roșie în sus reprezintă valori mai mari, săgeata laterală galbenă reprezintă valoriimedii iar săgeata verde în jos reprezintă valorile mai mici.
Formatare rapidă 1. Selectați o zonă de celule sau asigurați-vă că celula activă se află într -un tabel sau într-un raport
PivotTable.2. În fila Pornire, în grupul Stil, faceți clic pe săgeata de lângă Formate condiționale, pe Set
pictograme, apoi selectați un set de pictograme.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 115/124
Formatare complexă
1. Selectați o zonă de celule sau asigurați-vă că celula activă se află într -un tabel sau într-un raportPivotTable.
2. În fila Pagină de pornire, în grupul Stiluri, faceți clic pe săgeata de lângă Formatare condițională,
apoi pe Gestionare reguli.
Se afișează caseta de dialog Gestionar reguli formatare condițională.
3. Alegeți una dintre următoarele: Pentru a adăuga o formatare condițională, faceți clic pe Regulă nouă.
Se afișează caseta de dialog Regulă nouă de formatare.
Pentru a modifica o formatare condițională, procedați astfel: 1. Asigurați-vă că este selectată foaia de lucru sau tabelul potrivit în caseta listă Afișare
reguli formatare pentru.2. Opțional, modificați zona de celule făcând clic pe Restrângere dialog în caseta Seaplică la pentru a ascunde temporar caseta de dialog, selectând zona nouă de celule în
foaia de lucru, apoi selectând Extindere dialog .3. Selectați regula, apoi faceți clic pe Editare regulă.
Se afișează caseta de dialog Editare regulă de formatare.
4. Sub Se selectează un tip de regulă, faceți clic pe Se formatează toate celulele pe baza valorilorlor.
5. Sub Se editează descrierea regulii, în caseta listă Stil format, selectați Set de pictograme.
1. Selectați un set de pictograme. Setul implicit este 3 Lumini de semafor (neîncadrate). Numărul de pictograme, operatorii impliciți de comparație și valorile prag pentru fiecare pictogramă pot varia pentru fiecare set de pictograme.
2. Dacă doriți, aveți posibilitatea să reglați operatorii de comparație și valorile prag. Zoneleimplicite de valori pentru fiecare pictogramă sunt egale în dimensiuni, ar aveți posibilitatea să lereglați pentru a se potrivi cerințelor dvs. Asigurați-vă că pragurile se află în secvență logică de lacele mai mari la cele mai mici, de sus în jos.
3. Alegeți una dintre următoarele: Formatarea unei valori număr, dată sau oră Selectați Număr. Formatarea unui procent Selectați Procent.
Valorile valide sunt între 0 și 100. Nu introduceți semnul procent.
Utilizați semnul procent atunci când doriți să vizualizați toate valorile proporționaldin cauză că distribuția valorilor este proporțională.
Formatarea unui procent Selectați Procent.
Valorile procentuale valide sunt între 0 și 100. Nu este posibilă utilizarea unei valori procentuale dacă zona de celule conține mai mult de 8.191 puncte de date.
Utilizați un procentaj atunci când doriți să vizualizați un grup de valori mari (cum ar
fi primele douăzeci de procente) într -o culoare și valorile mici (cum ar fi ultimele
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 116/124
douăzeci de procente) în altă culoare, deoarece ele reprezintă valorile extreme care pot determina o vizualizare asimetrică a datelor dvs.
Formatarea rezultatului unei formule Selectați Formulă, apoi introduceți o formulă înfiecare casetă Valoare.
Formula trebuie să returneze o valoare de număr, dată sau oră. Începeți formula cusemnul egal (=). Nu se aplică niciun format pentru formulele nevalide. Serecomandă să testați formula în foaia de lucru pentru a vă asigura că nu returnează ovaloare de eroare.
4. Pentru a plasa pictograma în cealaltă parte a celulei, selectați Ordine pictograme inversată.5. Pentru a afișa numai pictograma, fără valoarea celulei, selectați Se afișează doar pictograma.
NOTE
Este posibil să fie necesar să reglați lățimea coloanei pentru a se potrivi cu dimensiunilepictogramei. Există trei dimensiuni de pictograme. Dimensiunea afișată depinde de dimensiunea fontului care
este utilizat în acea celulă.
Formatarea numai a celulelor care conțin valori text, număr sau dată șioră
Pentru a găsi mai ușor anumite celule într-o zonă de celule, aveți posibilitatea să formatați acele celule pe baza unui operator de comparație. De exemplu, într -o foaie de lucru de inventar sortată pecategorii, aveți posibilitatea să evidențiați produsele cu mai puțin de 10 elemente disponibile cuculoarea galbenă. Sau, într -o foaie de lucru de sinteză a unui magazin, aveți posibilitatea săidentificați toate magazinele cu profituri mai mari de 10%, cu volume de vânzări mai mici de 100.000de lei și cu regiunea egală cu "SudEst".
Formatare rapidă
1. Selectați o zonă de celule sau asigurați-vă că celula activă se află într -un tabel sau într-un raportPivotTable.
2. În fila Pornire, în grupul Stil, faceți clic pe săgeata de lângă Formate condiționale, apoi peEvidențiere reguli celule.
3. Selectați comanda dorită, cum ar fi Între, Egal cu textul care conține sau O dată anume.4. Introduceți valorile pe care doriți să le utilizați, apoi selectați formatul.
Formatare complexă
1. Selectați o zonă de celule sau asigurați-vă că celula activă este în tabel sau în raportul PivotTable.2. În fila Pagină de pornire, în grupul Stiluri, faceți clic pe săgeata de lângă Formatare condițională,
apoi pe Gestionare reguli.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 117/124
Se afișează caseta de dialog Gestionar reguli formatare condițională.
3. Alegeți una dintre următoarele: Pentru a adăuga o formatare condițională, faceți clic pe Regulă nouă.
Se afișează caseta de dialog Regulă nouă de formatare.
Pentru a modifica o formatare condițională, procedați astfel: 1. Asigurați-vă că este selectată foaia de lucru sau tabelul potrivit în caseta listă Afișare
reguli formatare pentru.
2. Opțional, modificați zona de celule făcând clic pe Restrângere dialog în caseta Seaplică la pentru a ascunde temporar caseta de dialog, selectând zona nouă de celule în
foaia de lucru, apoi selectând Extindere dialog .3. Selectați regula, apoi faceți clic pe Editare regulă.
Se afișează caseta de dialog Editare regulă de formatare.
4. Sub Se selectează un tip de regulă, faceți clic pe Se formatează toate celulele pe baza valorilorlor.
5. Sub Se editează descrierea regulii, în caseta listă Se formatează numai celulele cu, efectuați unadin următoarele:
Formatarea după număr, dată sau oră Selectați Valoare celulă, un operator decomparație, apoi introduceți un număr, o dată sau o oră.
De exemplu, selectați Între, apoi introduceți 100 și 200 sau selectați Egal cu șiintroduceți1.1.2006.
De asemenea, aveți posibilitatea să introduceți o formulă care returnează o valoare denumăr, dată sau oră. Dacă introduceți formula, începeți cu semnul egal (=). Nu se aplicăniciun format pentru formulele nevalide. Se recomandă să testați formula în foaia de lucru
pentru a vă asigura că nu returnează o valoare de eroare.
Formatare după text Selectați Text specific, selectați un operator de comparație, apoiintroduceți text.
De exemplu, selectați Conține, apoi introduceți Argintiu sau selectați începe cu, apoiintroduceți Tri.
Ghilimelele sunt incluse în șirul de căutare și se pot utiliza metacaracterele. Lungimea
maximă a unui șir este de 255 de caractere.
De asemenea, aveți posibilitatea să introduceți o formulă care returnează text. Dacăintroduceți formula, începeți cu semnul egal (=). Nu se aplică niciun format pentruformulele nevalide. Se recomandă să testați formula în foaia de lucru pentru a vă asiguracă nu returnează o valoar e de eroare.
Formatare după dată Selectați Date care reapar, apoi un operator de comparație a datei.
De exemplu, selectați Ieri sau Săptămâna următoare.
Formatarea celulelor cu spații necompletate sau a celulelor nevide Selectați Celule libere sau Nevide.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 118/124
NOTĂ O celulă liberă este o celulă care nu conține date și este diferită de o celulă careconține un spațiu sau mai multe spații (care sunt considerate text).
Formatarea celulelor cu valori de eroare sau fără valori de eroare Selectați Erori sau Nicioeroare.
Valorile de eroare includ: #####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, #NUM!,și #NULL!.
6. Pentru a aplica o formatare, faceți clic pe Format.
Se afișează caseta de dialog Formatare celule.
7. Selectați formatul de număr, font, bordură sau umplere pe care doriți să-l aplicați atunci când valoareacelulei întâlnește condiția, apoi faceți clic pe OK.
Aveți posibilitatea să alegeți mai mult de un format. Formatele pe care le selectați se afișează în caseta Examinare.
Formatarea numai a valorilor de rang superior sau de rang inferior
Aveți posibilitatea să găsiți valorile maxime și minime dintr -o zonă de celule pe baza unei valori deseparare. De exemplu, aveți posibilitatea să găsiți cele mai bine vândute cinci produse dintr -un raportregional, ultimele 15% de produse dintr-un sondaj de piață sau primele 25 de salarii dintr -o analiză aangajaților dintr -un departament.
Formatare rapidă
1. Selectați o zonă de celule sau asigurați-vă că celula activă este în tabel sau în raportul PivotTable. 2. În fila Pornire, în grupul Stil, faceți clic pe săgeata de lângă Formate condiționale, apoi pe Reguli
primii/ultimii.
3. Selectați comanda dorită, cum ar fi Primele 10 elemente sau Partea de jos 10 %.4. Introduceți valorile pe care doriți să le utilizați, apoi selectați formatul.
Formatare complexă
1. Selectați o zonă de celule sau asigurați-vă că celula activă este în tabel sau în raportul PivotTable. 2. În fila Pagină de pornire, în grupul Stiluri, faceți clic pe săgeata de lângă Formatare condițională,
apoi pe Gestionare reguli.
Se afișează caseta de dialog Gestionar reguli formatare condițională.
3. Alegeți una dintre următoarele: Pentru a adăuga o formatare condițională, faceți clic pe Regulă nouă.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 119/124
Se afișează caseta de dialog Regulă nouă de formatare.
Pentru a modifica o formatare condițională, procedați astfel: 1. Asigurați-vă că este selectată foaia de lucru sau tabelul potrivit în caseta listă Afișare
reguli formatare pentru.
2. Opțional, modificați zona de celule făcând clic pe Restrângere dialog în caseta Seaplică la pentru a ascunde temporar caseta de dialog, selectând zona nouă de celule în
foaia de lucru, apoi selectând Extindere dialog .3. Selectați regula, apoi faceți clic pe Editare regulă.
Se afișează caseta de dialog Editare regulă de formatare.
4. Sub Se selectează un tip de regulă, faceți clic pe Se formatează numai valorile de rang superiorsau de rang inferior.
5. Sub Se editează descrierea regulii, în caseta listă Se formatează valorile care se află în, selectațiSus sau Jos.
6. Variante disponibile: Pentru a specifica un număr superior sau inferior, introduceți un număr, apoi debifați caseta
de selectare % din intervalul selectat. Valorile valide sunt de la 1 la 1000. Pentru a specifica un procentaj superior sau inferior, introduceți un număr, apoi bifați caseta
de selectare % din intervalul selectat. Valorile valide sunt de la 1 la 100.7. Pentru a specifica o formatare, faceți clic pe Format.
Se afișează caseta de dialog Formatare celule.
8. Selectați formatul de număr, font, bordură sau umplere pe care doriți să-l aplicați atunci când valoareacelulei întâlnește condiția, apoi faceți clic pe OK.
Aveți posibilitatea să alegeți mai mult de un format. Formatele pe care le selectați se afișează în caseta Examinare.
Formatarea numai a valorilor care sunt deasupra mediei sau sub medie
Aveți posibilitatea să găsiți valorile care sunt deasupra sau dedesubtul unei deviații standard dintr -ozonă de celule. De exemplu, găsiți în raportul anual de performanțe persoanele cu performanțe pestemedie sau găsiți materialele produse care sunt sub deviația standard în ceea ce privește nivelulcalității.
Formatare rapidă
1. Selectați o zonă de celule sau asigurați-vă că celula activă este în tabel sau în raportul PivotTable. 2. În fila Pornire, în grupul Stil, faceți clic pe săgeata de lângă Formate condiționale, apoi pe Reguli
primii/ultimii.
3. Selectați comanda dorită, cum ar fi Primele 10 elemente sau Partea de jos 10 %.4. Introduceți valorile pe care doriți să le utilizați, apoi selectați formatul.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 120/124
Formatare complexă
1. Selectați o zonă de celule sau asigurați-vă că celula activă este în tabel sau în raportul PivotTable. 2. În fila Pagină de pornire, în grupul Stiluri, faceți clic pe săgeata de lângă Formatare condițională,
apoi pe Gestionare reguli.
Se afișează caseta de dialog Gestionar reguli formatare condițională.
3. Alegeți una dintre următoarele: Pentru a adăuga o formatare condițională, faceți clic pe Regulă nouă.
Se afișează caseta de dialog Regulă nouă de formatare.
Pentru a modifica o formatare condițională, procedați astfel: 1. Asigurați-vă că este selectată foaia de lucru sau tabelul potrivit în caseta listă Afișare
reguli formatare pentru.
2. Opțional, modificați zona de celule făcând clic pe Restrângere dialog în caseta Seaplică la pentru a ascunde temporar caseta de dialog, selectând zona nouă de celule în
foaia de lucru, apoi selectând Extindere dialog .3. Selectați regula, apoi faceți clic pe Editare regulă.
Se afișează caseta de dialog Editare regulă de formatare.
4. Sub Se selectează un tip de regulă, faceți clic pe Formatarea numai a valorilor care suntdeasupra mediei sau sub medie.
5. Sub Se editează descrierea regulii, în caseta listă Se formatează numai valorile care sunt,efectuați una din următoarele:
Pentru a formata celule care sunt peste sau sub media tuturor celulelor din zonă, selectațiDeasupra sau Dedesubt. Pentru a formata celule care sunt peste sau sub una, două sau trei deviații standard pentru
toate celulele din zonă, selectați o deviație standard. 6. Faceți clic pe Format pentru a afișa caseta de dialog Formatare celule.7. Selectați formatul de număr, font, bordură sau umplere pe care doriți să-l aplicați atunci când valoarea
celulei întâlnește condiția, apoi faceți clic pe OK.
Aveți posibilitatea să alegeți mai mult de un format. Formatele pe care le selectați se afișează în caseta Examinare.
Formatarea numai a valorilor unice sau a valorilor duble
Formatare rapidă
1. Selectați o zonă de celule sau asigurați-vă că celula activă este în tabel sau în raportul PivotTable. 2. În fila Pornire, în grupul Stil, faceți clic pe săgeata de lângă Formate condiționale, apoi pe
Evidențiere reguli celule.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 121/124
3. Select Valori dublate.4. Introduceți valorile pe care doriți să le utilizați, apoi selectați un format.
Formatare complexă
1. Selectați o zonă de celule sau asigurați-vă că celula activă este în tabel sau în raportul PivotTable.2. În fila Pagină de pornire, în grupul Stiluri, faceți clic pe săgeata de lângă Formatare condițională,
apoi pe Gestionare reguli.
Se afișează caseta de dialog Gestionar reguli formatare condițională.
3. Alegeți una dintre următoarele: Pentru a adăuga o formatare condițională, faceți clic pe Regulă nouă.
Se afișează caseta de dialog Regulă nouă de formatare.
Pentru a modifica o formatare condițională, procedați astfel: 1. Asigurați-vă că este selectată foaia de lucru sau tabelul potrivit în caseta listă Af ișarereguli formatare pentru.
2. Opțional, modificați zona de celule făcând clic pe Restrângere dialog în caseta Seaplică la pentru a ascunde temporar caseta de dialog, selectând zona nouă de celule în
foaia de lucru, apoi selectând Extindere dialog .3. Selectați regula, apoi faceți clic pe Editare regulă.
Se afișează caseta de dialog Editare regulă de formatare.
4. Sub Se selectează un tip de regulă, faceți clic pe Se formatează numai valorile unice sau valorile
duble.5. Sub Se editează descrierea regulii, în caseta listă Formatare totală, selectați unic sau dublare.6. Faceți clic pe Format pentru a afișa caseta de dialog Formatare celule.7. Selectați formatul de număr, font, bordură sau umplere pe care doriți să-l aplicați atunci când valoarea
celulei întâlnește condiția, apoi faceți clic pe OK.
Aveți posibilitatea să alegeți mai mult de un format. Formatele pe care le selectați se afișează în caseta Examinare.
Utilizarea unei formule pentru a determina care celule să fie formatate
Dacă formatarea dvs. condițională are cerințe mai complexe, aveți posibilitatea să utilizați o formulălogică pentru a specifica criteriile de formatare. De exemplu, este posibil să doriți să comparațivalorile cu un rezultat returnat de o funcție sau să evaluați date din celule aflate în afara zoneiselectate.
1. Selectați o zonă de celule sau asigurați-vă că celula activă este în tabel sau în raportul PivotTable. 2. În fila Pagină de pornire, în grupul Stiluri, faceți clic pe săgeata de lângă Formatare condițională,
apoi pe Gestionare reguli.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 122/124
Se afișează caseta de dialog Gestionar reguli formatare condițională.
3. Alegeți una dintre următoarele: Pentru a adăuga o formatare condițională, faceți clic pe Regulă nouă.
Se afișează caseta de dialog Regulă nouă de formatare.
Pentru a modifica o formatare condițională, procedați astfel: 1. Asigurați-vă că este selectată foaia de lucru sau tabelul potrivit în caseta listă Afișare
reguli formatare pentru.
2. Opțional, modificați zona de celule făcând clic pe Restrângere dialog în caseta Seaplică la pentru a ascunde temporar caseta de dialog, selectând zona nouă de celule în
foaia de lucru, apoi selectând Extindere dialog .3. Selectați regula, apoi faceți clic pe Editare regulă.
Se afișează caseta de dialog Editare regulă de formatare.
4. Sub Se selectează un tip de regulă, faceți clic pe Se utilizează o formulă pentru a determina carecelule să fie formatate.
1. Sub Se editează descrierea regulii, în caseta listă Se formatează valorile unde formula esteadevărată, introduceți o formulă.
Trebuie să începeți formula cu semnul egal (=), iar formula trebuie să returneze o valoarelogică de ADEVĂRAT (1) sau FALS (0).
Exemplul 1: Utilizarea unei formatări condiționale cu mai multe criterii și referințe de
celule în afara zonei de celule
În această formulă, o formatare condițională cu mai multe criterii aplicată zonei A1:A5formatează celulele cu culoarea verde dacă valoarea medie pentru toate celulele din zonăeste mai mare decât valoarea din celula F1 și oricare dintre celulele din zonă are o valoareminimă mai mare sau egală decât valoarea din G1. Celulele F1 sau G1 sunt în afara zoneide celule pentru care se aplică formatarea condițională. Funcția AND combină mai multecriterii, iar funcțiile AVERAGE și MIN calculează valorile.
FORMULĂ FORMAT
=AND(AVERAGE($A$1:$A$5)>$F$1, MIN($A$1:$A$5)>=$G$1)Culoare verde
Exemplu 2: Umbrirea fiecărui al doilea rând utilizând funcțiile MOD și ROW
Această formulă umbrește fiecare al doilea din zona de celule cu culoarea albastru.Funcția MOD returnează restul obținut de la împărțirea unui număr (primul argument) laîmpărțitorul său (al doilea argument). Funcția ROW returnează numărul rândului curent.Când împărțiți numărul rândului curent la 2, veți obține întotdeauna restul 0 pentrunumere pare și 1 pentru numere impare. Deoarece 0 este FALS și 1 este ADEVĂRAT, seva formata fiecare rând cu număr impar.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 123/124
FORMULĂ FORMAT
=MOD(ROW(),2)=1Culoare albastră
2. Faceți clic pe Format pentru a afișa caseta de dialog Formatare celule.3. Selectați formatul de număr, font, bordură sau umplere pe care doriți să-l aplicați atunci când
valoarea celulei întâlnește condiția, apoi faceți clic pe OK.
Aveți posibilitatea să alegeți mai mult de un format. Formatele pe care le selectați seafișează în caseta Examinare.
NOTĂ Aveți posibilitatea să introduceți referințe la celulă într-o formulă selectând celuledirect dintr-o foaie de lucru. Selectarea celulelor din foaie inserează referințe absolute lacelulă. Dacă intenționați ca Microsoft Office Excel să adapteze referințele pentru fiecare celulădin zona selectată, utilizați referințe relative la celulă.
Golirea formatărilor condiționale
Alegeți una dintre următoarele:
Foaie de lucru
1. În fila Pagină de pornire, în grupul Stiluri, faceți clic pe săgeata de lângă Formatarecondițională, apoi pe Golire reguli.
2. Faceți clic pe Întreaga foaie.
O zonă de celule, un tabel sau un raport PivotTable
1. Selectați zona de celule, tabelul sau raportul PivotTable pentru care doriți să goliți formatările
condiționale. 2. În fila Pagină de pornire, în grupul Stiluri, faceți clic pe săgeata de lângă Formatarecondițională, apoi pe Golire reguli.
3. În funcție de ce ați selectat, faceți clic pe Celulele selectate, Acest tabel sau pe AcestPivotTable.
Exercitii:
Deschideti fisierul Angajati.
1. Să se formateze condiţional data angajării pentru luna în care s-au făcut cele mai multeangajări în anul precedent.
5/12/2018 Excel 2007 Avansati - slidepdf.com
http://slidepdf.com/reader/full/excel-2007-avansati 124/124
2. Să se formateze condiţional numele salariaţilor de gen masculin angajaţi la Contabilitate,născuţi înainte de 1980 3. Sa se evidentieze printr-un format conditional numele angajatilor de la departamentul IT cusalariul mai maredecat media salariilor pe societate.
top related