excel 2007 avansati

5/12/2018 Excel 2007 Avansati - slidepdf.com

http://slidepdf.com/reader/full/excel-2007-avansati 1/124

Capitolul 1 _______________________________________

GRAFICE



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.



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



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:



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:



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



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.



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



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



Î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



Î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ă.



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.



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



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



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)



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.



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



Capitolul 2 _______________________________________

BAZE DE DATE



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.



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.



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



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.



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:



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)



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:



Î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())



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.



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.



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)



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



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)



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.



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



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



Capitolul 3 _________________________________________

FORMULE ŞI FUNCŢII



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



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



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

http://go%28%27/search/redir.aspx?AssetID=HP100698281048&CTT=5&Origin=HP100791871048%27)



http://go%28%27/search/redir.aspx?AssetID=HA012317651048&CTT=5&Origin=HP100791871048%27)







http://go%28%27/search/redir.aspx?AssetID=HA012317651048&CTT=5&Origin=HP100791871048%27)






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.



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.


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

http://appendpopup%28this%2C%27xldefarray_1%27%29/





OR

Întoarce TRUE dacă cel puţin un argument este TRUE; întoarce FALSE dacă toateargumentele sunt FALSE.


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


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.






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


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


VLOOKUP (lookup_value, table_array, col_index_num, range_lookup )



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)



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


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.


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,



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.



INDEX

Returnează conţinutul unei celule dintr -un domeniu. Se utilizează frecvent încombinaţie cu funcţia MATCH.


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.



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.


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:



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:



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.



Median

Returnează numărul median al numerelor date. Prin median se în-elege numărul dinmijlocul unui set de numere.


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.


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



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?


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



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)



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.


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.



COUNTIFS

Contorizează numărul de celule dintr -un interval care întrunesc mai multe criterii.


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.



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



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.



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



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)



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



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



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



Capitolul 4 _________________________________________

UTILITARE AVANSATE DE ANALIZĂ A DATELOR



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)



- 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



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



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



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



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



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



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.



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:



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:



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



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.



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?



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:



Capitolul 5________________________________________

TABELE PIVOT



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



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.



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



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.



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



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



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.



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.



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?



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.



Capitolul 6________________________________________

MACROINSTRUCŢIUNI



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:



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 :



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



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.



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 )



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



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.



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



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.

-



-

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.



Î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?



Capitolul 7_________________________________________

TEHNICI AVANSATE DE EDITARE ŞI DE FORMATARE A

FOILOR DE CALCUL



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.



Î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



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



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:



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



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.



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.



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.



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ă


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.



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ă




3. Variante disponibile: Pentru a adăuga o formatare condițională, faceți clic pe Regulă nouă.


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.





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.





Formatarea unui procentaj Selectați Procent, apoi introduceți o Valoare pentru Minim,Punct median și Maxim.


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.


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ă


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.



Formatare complexă



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








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



Formatarea unui procentaj Selectați Procentaj, apoi introduceți o Valoare pentru Cea maiscurtă bară și Cea mai lungă bară.




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


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.



Formatare complexă


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.


3. Alegeți una dintre următoarele: Pentru a adăuga o formatare condițională, faceți clic pe Regulă nouă.



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




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.


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.


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


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ă,













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.



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ă,












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.


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.



Formatare complexă











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.


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.



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ă,





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.




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.


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.













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.

http://togglediv%28%27divexpcollasst_454482134%27%29/











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.

http://appendpopup%28this%2C%27262712502_1%27%29/












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.

excel 2007 avansati

Documents