itasistenta blog

Meniu:    Data -> Subtotals

Subtotalurile reprezintă o metodă de a crea calcule parţiale pentru datele aşezate într-o zonă compactă de celule sau într-o listă. Excel poate realiza automat atât totaluri generale (calcule pe toate datele dintr-o serie de celule), cât şi totaluri parţiale (subtotaluri) pentru anumite celule din întregul domeniu de celule cu date.

Totaluri generale

Pentru totaluri generale (un singur total situat de obicei sub ultima înregistrare dintr-o zonă compactă de date sau în dreapta ultimei coloane, pentru date aşezate pe orizontală) există metodele:

• însumare automată (AutoSum);
• introducerea manuală a unei funcţii Excel de rezumare, care operează diverse calcule pe un domeniu de celule (coloana sau linia dorită);
• dacă datele sunt definite ca listă (meniul Data->List), din bara de butoane List se activează linia Total Row (se apasă butonul Toggle Total Row), iar din lista derulantă se poate alege o funcţie de rezumare (vezi mai multe informaţii în capitolul Liste).

Butonul AutoSum (Însumare automată) este amplasat pe bara de butoane Standard şi permite alegerea rapidă a unei funcţii care realizează operaţii uzuale pentru seturi de date – sumă (SUM), medie aritmetică (AVERAGE), numărarea datelor de tip numeric (COUNT), maximul (MAX) şi minimul (MIN). Pentru alte funcţii se alege din săgeata derulantă opţiunea More Functions sau se accesează butonul Insert Function.

Excel Autosum

Excel Autosum

Totaluri parţiale (Subtotaluri)

Subtotalurile (Subtotals) se folosesc pentru a sintetiza date pe anumite categorii de înregistrări (un subset din setul total de date). Înainte de a realiza un subtotal, datele trebuie sortate după criteriul (coloana) care ne interesează. După introducerea unui total parţial (meniul Data‑>Subtotals), Excel adaugă setului de date butoane care permit ascunderea sau afişarea detaliilor (se pot afişa în listă doar subtotalul sau înregistrările care au dus la obţinerea subtotalului respectiv). Un subtotal se actualizează automat atunci când se modifică datele de intrare.

Există posibilitatea imbricării mai multor subtotaluri, pentru seturi mai mici de date, în interiorul unui subtotal exterior (sau mai multor subtotaluri exterioare). Înregistrările trebuie sortate anterior după coloanele folosite drept criteriu în sintetizarea datelor.

Exemplu:
Într-o companie există 3 departamente (Dep1, Dep2 şi Dep3). Situaţia vânzărilor şi a angajaţilor este prezentată în următorul tabel.

1. Dorim să calculăm:

- suma vânzărilor companiei în cele 3 luni;
- numărul maxim de angajaţi pe care i-a avut compania într-un departament.

Vom introduce câte un total general în celulele C11, respectiv D11. Folosind butonul AutoSum, în celula C11 introducem funcţia SUM(C2:C10), iar în D11 funcţia MAX(D2:D10). Observaţie: aceste funcţii se puteau introduce şi manual, din butonul Insert Function. Aici am folosit facilitatea AutoSum, care detectează automat domeniul de celule pe care am dori să le sintetizăm.

2. Dorim să calculăm:

- numărul maxim de angajaţi, pe departamente;
- suma vânzărilor, pe luni.

Vom duplica primul tabel şi vom insera în fiecare din cele 2 tabele datele sintetizate cerute, astfel:

• se sortează datele din primul tabel după coloana Departament;
• se introduce (folosind meniul Data->Subtotals) un subtotal: la fiecare modificare în (At each change in:) Departament, folosim funcţia Max şi adăugăm subtotalul la coloana Angajaţi;
• se obţine un tabel cu aspectul celui de mai jos; este calculat numărul maxim de angajaţi pe fiecare Departament.
• se sortează datele din al doilea tabel (identic cu tabelul iniţial) după coloana Luna;
• se introduce un subtotal: la fiecare modificare în (At each change in:) Luna, folosim funcţia Sum şi adăugăm subtotalul la coloana Vânzări;
• se obţine un tabel cu aspectul celui de mai jos; este calculată suma vânzărilor pe fiecare Lună.

Excel Subtotal	Excel exemplu subtotal
Excel Subtotal	Excel Subtotal exemplu

Observaţie: la sortarea datelor pe luni calendaristice, pentru a obţine ordinea ianuarie, februarie, martie se alege în fereastra Sort, butonul Options şi First key sort order: ianuarie, februarie, …

În ambele cazuri se observă că subtotalurile calculate automat au fost introduse sub datele centralizate (Departament, respectiv Luna), datorită opţiunii Summary below data (altfel, aceste rezultate erau afişate deasupra datelor centralizate).

Exemplu:
Situaţia vânzărilor dintr-o companie cu 3 departamente (Dep1, Dep2 şi Dep3) este prezentată în următorul tabel. Se cere situaţia vânzărilor pe ani şi departamente.

Vom calcula datele cerute prin subtotaluri introduse în acelaşi tabel:

• se sortează datele simultan, primul criteriu fiind coloana An, iar al doilea coloana Departament;
• se introduce primul subtotal: la fiecare modificare în coloana An, vom calcula suma vânzărilor (funcţia Sum) şi o vom plasa în coloana Vânzări;
• se introduce al doilea subtotal: la fiecare modificare în coloana Departament, vom calcula suma vânzărilor (funcţia Sum) şi o vom plasa în coloana Vânzări. Observaţie: se va debifa opţiunea Replace current subtotals, astfel încât subtotalul curent să fie adăugat subtotalului calculat la punctul anterior.

Excel Subtotal exemplu

Excel Subtotal exemplu

Dacă se doreşte un mod mai restrâns de prezentare a datelor se pot folosi butoanele din zona stângă a interfeţei pentru a afişa doar anumite niveluri de subtotaluri – de exemplu, vezi figura de mai jos.

Excel Subtotal exemplu

Meniu:    Data -> Validation

Deoarece Excel permite implicit introducerea datelor de orice tip, în orice celulă (numere pozitive/negative, întregi sau cu zecimale, dată calendaristică, timp, text), un aspect important îl constituie introducerea unor informaţii valide astfel încât formulele să opereze corect, aşa cum au fost proiectate.

Pentru a limita apariţia unor erori cauzate de date incorect introduse sau lipsă, avem în Excel următoarele posibilităţi:

• blocarea celulelor (Locked);
• validarea conţinutului (Validation).

Prin blocare (opţiunea Locked din meniul Format->Cells, tab-ul Protection) nu este permisă modificarea/ştergerea datelor din anumite celule. Această facilitate este funcţională dacă întreaga foaie de lucru este protejată (Tools->Protection->Protect Sheet) – vezi capitolul Protejarea datelor în Excel.

Validarea conţinutului celulelor constă în limitarea tipului de date care se pot introduce prin tastare directă  în acele celule şi informarea clară a utilizatorului în legătură cu problema apărută. Se evită astfel eventuale erori de calcul care pot apărea pe parcurs, iar utilizatorul este ajutat la introducerea datelor de prelucrat şi în lucrul cu interfaţa. Validarea datelor dintr-o celulă nu funcţionează atunci când:

• se lipeşte altă celulă;
• se completează automat datele (Fill);
• datele sunt obţinute în urma unei formule care nu dă un rezultat valid (#REF! sau #DIV/0);
• o macrocomandă introduce date incorecte în acea celulă.

Informaţiile introduse în fereastra Data Validation sunt:

• tipul de date permise în celulă: orice valoare (Any value – implicit), număr întreg (Whole number), număr cu zecimale (Decimal), o listă de elemente (List), dată (Date), timp (Time), text cu o lungime specificată (Text length) sau o valoare permisă în urma evaluării True/False a unei formule (Custom);
• un operator de comparare: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to;
• valoarea (valorile minime/maxime) cu care se compară informaţia introdusă în celulă.

Data Validation

Alte opţiuni în validarea celulelor:

• pentru a ignora celulele necompletate (goale) se bifează opţiunea Ignore blanks;
• afişarea unui mesaj de informare (Input Message) atunci când se selectează celula pentru introducerea datelor;
• afişarea unei ferestre cu un mesaj de eroare (Error Alert) atunci când se introduce o valoare care nu respectă condiţia de validare. Există 3 tipuri (niveluri) de eroare:
  • Information (doar informează utilizatorul cu privire la restricţia impusă şi nerespectată);
  • Warning (permite utilizatorului corectarea erorii);
  • Stop (nu permite trecerea mai departe până când eroarea nu este corectată).

Se pot impune următoarele condiţii de validare:

• număr întreg sau zecimal cu o limită (inferioară sau superioară) sau cu două limite (domeniu de valori minime şi maxime);
• valori dintr-o listă: opţiunea In-cell dropdown activează o listă derulantă din care se poate alege foarte uşor elementul dorit al listei. În caseta Source se pot introduce:
  • elemente (numere, text) definite local, separate prin virgulă sau punct şi virgulă pentru Regional Settings România (exemplu: 1, 2, 3 sau unu, doi, trei);
  • un nume al unei zone de celule definit anterior, precedat de semnul = (exemplu: dacă zona de celule A1…A3 are numele dom1, atunci condiţia de validare va fi =dom1);
  • referinţe la celule din aceeaşi foaie de lucru, introduse fie prin tragere cu mouse-ul, fie prin precizarea referinţelor la celule/zona de celule (exemplu: =D11:D15). Dacă validarea necesită celule din altă foaie de lucru se foloseşte metoda anterioară (definirea şi apelarea unui nume de celulă).

Exemplu:

Validation - List

Pentru a crea în celula B1 o listă de validare formată din conţinutul celulelor A1…A3 ca în figura de mai sus, în caseta Source din fereastra Data Validation se poate introduce (fără ghilimele):

“unu, doi, trei” (texte separate prin virgulă sau punct şi virgulă, în funcţie de setările regionale)

“=dom1″ (unde dom1 este numele domeniului de celule A1..A3 care conţin textele unu, doi, respectiv trei)

“=$A$1:$A$3″ (celulele A1…A3 conţin textele unu, doi, respectiv trei)

• date calendaristice sau timp (fiind specificate valori de început, de sfârşit sau într-un interval);
• un text de o lungime impusă (specificată);
• o formulă care calculează o valoare logică TRUE sau FALSE (exemplu: în condiţia de validare Custom, formula =LEN(A2)>2 va permite introducerea unei informaţii în celula selectată curent doar dacă lungimea textului din celula A2 este mai mare decât 2).

Limitele pot fi introduse ca:

• valori numerice constante;
• referinţe la alte celule (validarea se face în funcţie de conţinutul altor celule);
• formule – de exemplu =LEN(A2) forţează conţinutul celulei selectate să aibă o lungime egală cu lungimea textului din celula A2

Pentru a găsi toate celulele care conţin condiţii de validare se foloseşte meniul Edit -> Go to |Special| -> Data validation.

Pentru a aplica aceleaşi condiţii de validare şi altor celule se copiază celula curentă (Copy), apoi se foloseşte meniul Edit -> Paste Special | Validation | pentru celulele destinaţie.

Pentru a şterge condiţia de validare se foloseşte butonul Clear All (fereastra Data Validation).

Meniu: Data -> Filter

Prin operaţia de filtrare a datelor, Excel afişează doar acele înregistrări (rânduri) care corespund unuia sau mai multor criterii impuse de utilizator. Filtrarea nu sortează datele (nu schimbă ordinea lor), ci doar ascunde datele pe care nu dorim să le vizualizăm.

Pentru a filtra o zonă compactă de date se plasează cursorul în una din celulele intervalului evaluat (ca la Sortare) şi se aplică una din cele două variante de filtrare a datelor:

• automată (AutoFilter);
• avansată (Advanced Filter).

Pentru a afişa din nou toate înregistrările se foloseşte meniul Data -> Filter -> Show All.

FILTRARE AUTOMATĂ

Prin filtrare automată (AutoFilter), în antetul coloanelor apar săgeţi pentru liste derulante din care se pot alege pentru afişare:

• o anumită înregistrare (linie) care corespunde selecţiei;
• mai multe înregistrări care îndeplinesc o condiţie specificată de utilizator:
  • (Top 10…) – primele/ultimele n înregistrări identificate ca număr sau ca procent;
  • (Custom…) – permite filtrarea liniilor care îndeplinesc una sau două condiţii legate prin operatorii logici AND şi/sau OR. În fiecare condiţie se specifică:
    • un operator (equals, does not equal, is greater than, is greater than or equal to, is less than, is less than or equal to, begins with, does not begin with, ends with, does not end with, contains, does not contain);
    • o valoare cu care se compară conţinutul celulei.

Prin alegerea unei filtrări particularizate pe coloane diferite se pot afişa înregistrările care îndeplinesc condiţii multiple, cu mai multe criterii (coloane).

În comparaţii de text se pot utiliza următoarele caractere de substituire:

Pentru a anula filtrul se debifează AutoFilter.

Exemplu:

Avem următoarele date:

Aplicarea unui filtru automat (AutoFilter) va duce la apariţia săgeţilor derulante în celulele din antetul tabelului.

Filtrare automată (AutoFilter)

Din aceste săgeţi putem afişa o singură înregistrare – de exemplu, departamentul Dep1 – sau, dacă se alege Custom…, toate departamentele care au vânzări între 1300 şi 1500 inclusiv.

AutoFilter - o înregistrare

Rezultat:

AutoFilter Custom

Rezultat:

Pentru a răspunde la întrebarea “Ce departament are vânzări de peste 1400 şi cei mai puţini angajaţi?” vom introduce criterii de filtrare atât pe coloana Vânzări, cât şi pe coloana Angajaţi astfel:

• Vânzări: Custom -> is greater than 1400;
• Angajaţi: Top10 -> Bottom 1 Items.

Se obţine:

FILTRARE AVANSATĂ

În cazul Filtrării avansate (Advanced Filter) nu sunt adăugate săgeţi pentru listele verticale cu opţiuni (ca la filtrarea automată), ci criteriile de filtrare sunt specificate chiar în celulele registrului de lucru.

Filtrare avansată (Advanced Filter)

Din fereastra Advanced Filter se pot alege:

• unde se face filtrarea datelor:
  • în acelaşi loc (Filter the list, in-place) – vor fi afişate în tabelul iniţial doar înregistrările care îndeplinesc criteriile de filtrare);
  • într-o altă celulă a aceleiaşi foi de lucru (Copy to another location) – datele filtrate vor fi copiate în această nouă locaţie, specificată de utilizator în caseta Copy to;
• intervalul de celule filtrat (List range), detectat automat de Excel dacă se dă clic pe una din celulele intervalului evaluat înainte de a accesa meniul Filter;
• celula (celulele) în care se află criteriul (criteriile) de filtrare (Criteria range) – este preferabil să fie introduse deasupra tabelului sau în altă foaie a registrului de lucru, deoarece filtrarea ar putea ascunde celulele care conţin criteriile de filtrare;
• afişarea înregistrărilor unice (Unique records only) – în rezultatele filtrate nu se afişează înregistrările identice. Prin această opţiune se pot elimina înregistrările care se repetă într-o listă de date.

În criteriile care folosesc şiruri de caractere pentru comparaţii se pot folosi aceleaşi caractere de substituire * (asterisc),  ? (semnul întrebării) sau ~ (tilda) prezentate în capitolul Filtrare automată.

Criteriile simple de filtrare se introduc după modelul:

unde Câmp este numele unei coloane din tabelul de filtrat, iar Valoare / Expesie este criteriul de filtrare.

Pentru criteriile multiple de filtrare există următoarele moduri de introducere:

MAI MULTE CONDIŢII ÎN ACEEAŞI COLOANĂ (SAU LOGIC)

Afişează înregistrările care conţin:

Valoare1 în coloana Câmp
SAU
Valoare2 în coloana Câmp

(sau îndeplinesc condiţiile Expresie1 pentru Câmp SAU Expresie2 pentru Câmp).

O CONDIŢIE ÎNTR-O COLOANĂ SAU ALTA (SAU LOGIC)

Afişează înregistrările care conţin:

Valoare1 în coloana Câmp1
SAU
Valoare2 în coloana Câmp2
SAU
Valoare3 în coloana Câmp3

(sau îndeplinesc condiţiile Expresie1 pentru Câmp1 SAU Expresie2 pentru Câmp2 SAU Expresie3 pentru Câmp3).

O CONDIŢIE ÎN DOUĂ SAU MAI MULTE COLOANE (ŞI LOGIC)

Afişează înregistrările care conţin:

Valoare1 în coloana Câmp
ŞI
Valoare2 în coloana Câmp

(sau îndeplinesc condiţiile Expresie1 pentru Câmp ŞI Expresie2 pentru Câmp).

Afişează înregistrările care conţin:

Valoare1 în coloana Câmp1
ŞI
Valoare2 în coloana Câmp2

(sau îndeplinesc condiţiile Expresie1 pentru Câmp1 ŞI Expresie2 pentru Câmp2).

UNUL DIN DOUĂ SETURI DE CONDIŢII ÎN COLOANE DIFERITE (SAU LOGIC)

Afişează înregistrările care conţin:

Valoare11 în coloana Câmp ŞI Valoare21 în coloana Câmp
SAU
Valoare12 în coloana Câmp

sau îndeplinesc condiţiile (Expresie11 pentru Câmp ŞI Expresie21 pentru Câmp) SAU Expresie12 pentru Câmp.

Afişează înregistrările care conţin:

Valoare11 în coloana Câmp1 ŞI Valoare21 în coloana Câmp2
SAU
Valoare12 în coloana Câmp1 ŞI Valoare22 în coloana Câmp2

sau îndeplinesc condiţiile (Expresie11 pentru Câmp1 ŞI Expresie21 pentru Câmp2) SAU (Expresie12 pentru Câmp1 ŞI Expresie22 pentru Câmp2).

O CONDIŢIE REZULTAT AL UNEI FORMULE

Afişează înregistrările care conţin pe coloana C o valoare mai mare decât media aritmetică a celulelor C5..C10.

Exemplu:
Avem următoarele date introduse în celulele A10…C15:

Pentru fiecare din cazurile de mai jos, în caseta List range (intervalul de celule pe care dorim să-l filtrăm) vom introduce valoarea $A$10:$C$15.

Criteriile de filtrare le vom introduce la începutul foii de lucru, în zona de celule A1:B3, după caz (pot fi introduse oriunde în foaia de lucru curentă, în altă foaie sau în alt registru de lucru).

1. Filtrare: toate vânzările mai mici de 1200 SAU mai mari de 1500

2. Filtrare: departamentele cu vânzări între 1200 ŞI 1500

3. Filtrare: departamentele cu vânzări sub 1300 SAU mai mult de 50 angajaţi

4. Filtrare: departamentele cu vânzări peste 1300 ŞI mai puţini de 50 angajaţi

5. Filtrare: toate departamentele care au vânzări mai mici decât media

Observaţii:

În acest caz se foloseşte funcţia Excel AVERAGE (media aritmetică) drept criteriu de filtrare. Expresia introdusă în celula A2 este evaluată (TRUE sau FALSE), filtrarea făcându-se apoi în funcţie de datele din coloane. Pentru ca filtrarea să se realizeze este obligatorie respectarea următoarelor indicaţii:

• în celula A1 (sau în orice altă celulă în care ar fi introdus), antetul zonei de criterii (“Media vânzări” în acest exemplu) trebuie să fie diferit de antetul coloanei din zona filtrată (“Vânzări” în acest exemplu);
• în celula A2 formula se va scrie exact ca în exemplu. Ea trebuie să conţină:
  • o referinţă relativă la prima celulă a coloanei evaluate (celula B11 în acest exemplu), care va fi modificată de Excel automat pe verticală pentru fiecare înregistrare şi comparată cu media aritmetică a tuturor înregistrărilor (rezultatul funcţiei AVERAGE);
  • referinţele absolute $B$11 şi $B$15, care definesc zona de date a cărei medie aritmetică se calculează (cu funcţia AVERAGE). Pentru setul de date folosit în exemplu, media aritmetică este 1414, iar această valoare va fi comparată succesiv cu celulele B11…B15. În cazul în care celulele conţin valori mai mici decât media, înregistrările (rândurile) respective vor fi afişate.

Exemplu:

Avem următoarele date:

Pentru fiecare din cazurile de mai jos se alege List range (intervalul de celule pe care dorim să-l filtrăm): $A$10:$C$18.

1.  Filtrare: toate persoanele al căror nume începe cu “Dan”

2. Filtrare: toate persoanele al căror nume se termină cu “a” şi au sub 20 ani

Observaţie: Excel consideră formulă orice expresie care începe cu semnul egal şi o calculează (în cazul celulei A2 va da eroare dacă scriem pur şi simplu =*a). De aceea va trebui să introducem expresia =*a ca şir de caractere prin următoarele metode:

• =”=*a” – celula conţine o formulă datorită primului semn egal, prin care valoarea celulei este egală cu şirul de caractere =*a introdus între ghilimele duble ” “;
• ‘=*a – caracterul ghilimele simple ‘ la începutul celulei defineşte conţinutul acesteia de tip Text şi astfel nu va fi evaluat ca formulă;
• formatarea celulei ca fiind de tip Text (meniul Format -> Cells, tab-ul Number, categoria Text).

Observaţie: Pentru a găsi toate numele care conţin ghilimele duble (“) se poate folosi expresia ‘=*”*.

3. Filtrare: toţi băieţii cu vârste peste 20 ani

4. Filtrare: toate persoanele cu nume formate din 5 litere

5. Filtrare: toate persoanele al căror nume este “Dan” (exact)

Meniu:    Data -> Sort
Butoane: Sort Ascending, Sort Descending (bara de butoane Standard)

Această caracteristică permite ordonarea datelor dintr-o foaie de lucru după criterii specificate de utilizator. Principalele facilităţi sunt:

• ordonarea înregistrărilor (liniilor) dintr-un tabel după unul, două sau maxim trei criterii (coloane sau câmpuri) specificate;
• ordonare crescătoare (Ascending) – de la 0 la 9 sau de la A la Z – sau descrescătoare (Descending) – de la 9 la 0 sau de la Z la A – pentru date numerice, respectiv text;
• sortare după o ordine particularizată, fie zilele săptămânii sau lunile anului, fie o listă particularizată (Custom List) creată de utilizator;
• ordonarea rândurilor (coloanele drept criterii de sortare) sau a coloanelor (Sort left to right, atunci când datele sunt amplasate pe linie);
• sortarea unitară a întregului tabel sau a unei singure coloane.

Sortarea datelor în Excel 2003

Pentru a sorta un set compact de date (coloane şi linii adiacente) se dă clic pe o celulă din intervalul de date dorit şi se accesează meniul Sort (acest mod de lucru este recomandat deoarece Excel va detecta automat întreaga zonă cu date şi o va selecta în vederea sortării). Pentru a sorta rapid o zonă compactă de date după o anumită coloană, se dă clic într-o celulă a coloanei folosită drept criteriu de sortare şi se apasă butoanele Sort Ascending / Sort Descending de pe bara de butoane Standard. Dacă se face manual o selecţie de celule, Excel va afişa un mesaj de avertizare şi ulterior va sorta doar datele din acea selecţie.. În acest caz, înregistrările (liniile) iniţiale vor fi alterate prin sortarea (schimbarea ordinii) numai pentru anumite celule (câmpuri) din zona de date.

Sortare în Excel 2003 - extindere selecţie

Zona de date poate avea un antet (cap de tabel) care de obicei rămâne fix (nu este implicat în procesul de sortare) şi este folosit pentru a preciza criteriile de sortare (coloanele) în fereastra Sort.

În acest exemplu antetul este linia formată din celulele Departament şi Vânzări. Dacă se bifează Header row, în fereastra de sortare datele se vor putea ordona după coloanele Departament şi/sau Vânzări.

Sortare cu linie antet (Header Row)

Dacă opţiunea No header row este bifată, prima linie va fi considerată înregistrare şi va fi sortată odată cu celelalte înregistrări (în lipsa liniei de antet, criteriile de sortare devin Column A, Column B etc. sau Row 1, Row 2, … dacă sortarea se face de la stânga la dreapta – Sort left to right).

Sortare fără linie antet (No header row)

Sortarea se poate face după unul, două sau maxim trei criterii de sortare. Excel detectează automat tipul datelor (numeric sau text) şi le va ordona conform opţiunii specificate (crescător sau descrescător). Criteriile de sortare (coloanele) se introduc în casetele Sort by şi Then by. Ordinea de introducere a criteriilor este importantă: primul criteriu de sortare (caseta Sort by) este cel mai important. Dacă Excel identifică două sau mai multe linii cu conţinut identic pentru primul criteriu (celule din coloana respectivă), va sorta datele după al doilea, respectiv al treilea criteriu.

Sortarea se poate face după o ordine specificată în liste particularizate (Custom List): fie listele implicite din Excel (zilele săptămânii – Sunday, Monday, … sau lunile anului – January, February, …), fie liste create de utilizator – vezi capitolul Liste predefinite (Custom Lists). Din butonul Options se alege First key sort order şi se alege lista particularizată după care să se facă sortarea. Excel va sorta datele crescător sau descrescător după ordinea din lista particularizată specificată. Ordinea de sortare particularizată este posibilă doar pentru primul criteriu de sortare (caseta Sort by).

De obicei datele sunt aşezate pe coloane, iar sortarea implicită se face pe verticală, de sus în jos (Sort top to bottom), în ordinea specificată (Ascending sau Descending). Dacă datele sunt aşezate orizontal, din butonul Options se alege Sort left to right.

Obiective:

1. Crearea şi utilizarea numelor (Name) pentru celule şi formule. Etichetă (Label).
2. Formatarea condiţională a celulelor (Conditional Formatting)
3. Sortarea datelor (Sort)
4. Filtrarea datelor (Filter)
5. Validarea datelor (Validation)
6. Subtotaluri (Subtotals)
7. Tabele pivot

Meniu:    Format -> Conditional Formatting

Excel poate aplica un format specific celulelor care îndeplinesc o anumită condiţie impusă de utilizator, astfel încât celulele respective să iasă în evidenţă şi să poată fi uşor identificate. Dacă nu mai este îndeplinită condiţia impusă, celula revine la formatul existent, iar formatarea condiţională va fi reaplicată atunci când condiţia va fi din nou îndeplinită.

Aplicarea unor formate condiţionale nu se poate face pentru un registru de lucru partajat (Shared Workbook), însă se păstrează dacă au fost aplicate înainte de partajarea registrului.

Evidenţierea celulelor constă în stabilirea:

• unui stil, tip de subliniere, efect sau a unei culori pentru font;
• unui stil, unei culori şi a marginilor pentru chenare (Border);
• unei culori şi a unui model pentru culoarea de umplere a celulei (Patterns).

Se pot stabili maxim 3 criterii de formatare pentru o celulă. Dacă mai multe condiţii sunt adevărate, Excel aplică formatarea specificată în prima condiţie îndeplinită.

Conditional Formatting

Ca şi criterii de comparare se pot introduce:

• o valoare (număr, text sau valoarea din altă celulă);
• o formulă.

Pentru a utiliza o valoare drept criteriu de formatare:

1. în fereastra Conditional Formatting se alege Cell Value Is;
2. se alege operatorul de comparare dintre cele disponibile: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to;
3. se introduce valoarea cu care se compară (pentru operatorii care specifică un interval de valori – de exemplu between – se introduc cele două valori limită);
4. se stabileşte un format condiţional pentru celulă;
5. se adaugă dacă este nevoie o nouă condiţie (Add) şi se repetă paşii 1-4 (maxim 3 condiţii) sau paşii 1-3 de mai jos (Formula drept criteriu de formatare).

Exemple:

Pentru a utiliza o formulă drept criteriu de formatare:

1. în fereastra Conditional Formatting se alege Formula Is;
2. se introduce formula al cărei rezultat va fi evaluat. În funcţie de valoarea TRUE sau FALSE a evaluării se va aplica formatul condiţional stabilit.
3. se stabileşte un format condiţional pentru celulă;
4. se adaugă dacă este nevoie o nouă condiţie (Add) şi se repetă paşii 1-3 (maxim 3 condiţii) sau paşii 1-4 de mai sus (Valoare drept criteriu de formatare).

Exemple:

Exemplu: Dorim să formatăm condiţional toate celulele de pe coloana B dacă valoarea din celula A1 este negativă. Se selectează toată coloana B (clic pe litera B din capătul coloanei) şi se introduce:

Formula Is =$A$1<0

Se stabileşte un format condiţional. Dacă în celula A1 se introduce un număr negativ, toată coloana B va avea formatul condiţional specificat.

Exemplu: Dorim să evidenţiem celulele din intervalul A1:A10 a căror valoare este mai mare decât media tuturor celulelor de pe coloana A. Se selectează celulele A1:A10 şi în celula A1 se introduce:

Formula Is =A1>AVERAGE(A:A)

Dacă valoarea din celula A1..A10 este mai mare decât media aritmetică a tuturor celulelor de pe coloana A, respectiva celulă va avea formatul condiţional specificat.

Echivalenţa între cele 2 criterii de formatare condiţională

Dacă dorim să formatăm condiţional celula B1 putem folosi echivalenţele:

Pentru a găsi toate celulele care conţin formate condiţionale: meniul Edit -> Go to |Special| -> Conditional formats.

Meniu:    Insert -> Name
Caseta Name Box (în stânga barei de formule)

O constantă, o celulă, un grup de celule sau o formulă pot avea un nume descriptiv (Name) util în formule pentru a identifica mai uşor un anumit operand. Este mai comod de folosit şi uşor de înţeles o formulă în care avem un operand denumit dobândă, decât o referinţă la o celulă care nu spune foarte mult despre semnificaţia ei.

Numele ales pentru o celulă:

• trebuie să înceapă cu o literă, “_” (underscore) sau “\” (backslash). Celelalte caractere din nume pot fi litere, cifre, “.” (period) sau “_”.
• numele nu pot conţine spaţii şi nu pot fi referinţe la alte celule (nume precum “A1″ sau “$B$10″ nu sunt permise);
• poate avea maxim 255 caractere;
• nu este case sensitive.

Un nume pentru o celulă sau o selecţie de celule (chiar şi neadiacente) dintr-o foaie de lucru se stabileşte rapid din caseta Name Box (aflată în stânga barei de formule). Se selectează celulele dorite şi se introduce un nume în această casetă. Pentru a selecta zone de celule neadiacente se procedează astfel: se selectează prima zonă continuă de celule, apoi se apasă Ctrl + tragere cu mouse-ul pentru a selecta următoarea zonă de celule.

Caseta Name Box pentru o celulă Pentru celula B2 s-a ales numele “nume1″. Se va putea scrie o formulă: =10+nume1

Caseta Name Box pentru un domeniu de celule Pentru domeniul de celule A1..C4 s-a ales numele “zona1″. Se va putea scrie o formulă: =SUM(zona1)

Caseta Name Box poate fi folosită pentru:

• saltul direct la o anumită celulă (se introduce referinţa ei, de exemplu: A100);
• selectarea unui domeniu de celule (se introduce referinţa la domeniu, de exemplu: A1:A100);
• saltul direct la o celulă/zonă căreia i s-a atribuit un Nume (se alege din lista derulantă sau se introduce de la tastatură numele căutat).

Un nume definit poate fi apelat atât în foaia de lucru curentă, cât şi din alte foi de lucru (referinţă 3-D) sau alte registre de lucru (referinţă externă).

Definirea unui nume pentru o constantă

Folosind meniul Insert -> Name -> Define se poate defini un nume pentru o constantă astfel: se introduce în caseta Names in workbook numele dorit pentru constantă, iar în caseta Refers to: valoarea acesteia.

Nume pentru o constantă

Definirea unui nume pentru o celulă

Folosind meniul Insert -> Name -> Define se poate defini un nume pentru o celulă astfel: se introduce în caseta Names in workbook numele dorit pentru celulă, iar în caseta Refers to: referinţa absolută la aceasta.

Nume pentru o celulă

Definirea unui nume pentru mai multe celule, de pe foi diferite

Folosind meniul Insert -> Name -> Define se poate defini un nume pentru un domeniu de celule situate pe mai multe foi de lucru (referinţă 3-D): se introduce în caseta Names in workbook numele dorit pentru domeniul de celule, iar în caseta Refers to: se dă clic pe celula de start din prima foaie, apoi Shift + clic pe celula finală din ultima foaie.

Nume pentru celule pe foi diferite (referinţă 3-D)

Definirea unui nume pentru o formulă

Folosind meniul Insert -> Name -> Define se poate defini un nume pentru o formulă astfel: se introduce în caseta Names in workbook numele dorit pentru formulă, iar în caseta Refers to: expresia acesteia.

Nume pentru o formulă

Meniu: Tools -> Options |Calculation| -> Accept labels in formulas

Etichetele (Labels) sunt reprezentate de capetele liniilor sau coloanelor, care descriu semnificaţia datelor introduse în celule. Aceste etichete pot fi folosite în formule pentru identificarea celulelor într-o manieră mai uşor de înţeles. Implicit Excel nu acceptă utilizarea etichetelor în formule, de aceea trebuie activată opţiunea din meniul de mai sus.

=SUM(Produs1)
va afişa 36 (suma pe coloana B, etichetată “Produs1″)

=MAX(Februarie)
va afişa 22 (maximul pe linia 3, etichetată “Februarie”)

=Ianuarie Produs3
va afişa 20, adică intersecţia liniei 2, etichetată “Ianuarie”, cu coloana D, etichetată “Produs3″

În situaţia etichetelor stratificate (mai multe niveluri de etichete), pentru a identifica datele va trebui specificat fiecare nivel, de la nivelul superior spre cel inferior. Astfel, pentru date pe coloane nivelul scade de sus în jos, iar pentru date pe linii, nivelul scade de la stânga la dreapta.

=SUM(2010 Produs2)
va afişa 17 (suma celulelor din coloana C, etichetată “Produs2″, eticheta părinte “2010″)

=SUM(2011 Produs2)
va afişa 30 (suma celulelor din coloana F, etichetată “Produs2″, eticheta părinte “2011″)

=2010 Produs3 Ianuarie
va afişa 20, adică intersecţia coloanei D, etichetată “Produs3″ (eticheta părinte “2010″) cu linia 3, etichetată “Ianuarie”

=Ianuarie 2011 Produs1
va afişa 11, adică intersecţia liniei 3, etichetată “Ianuarie”, cu coloana etichetată “Produs1″ (eticheta părinte “2011″)

=SUM(Martie 2010 Produs1; Martie 2010 Produs2; Martie 2010 Produs3)
va afişa 44 (suma celulelor B5, C5 şi D5)

Meniu: Tools -> Goal Seek

Este o funcţie de analiză folosită atunci când se cunoaşte rezultatul dorit al unei formule şi se doreşte aflarea valorii de intrare care ar duce la acel rezultat. Excel ajustează valorile din celula de intrare până când se obţine la ieşire valoarea impusă (dorită).

Exemplu:

Pentru un credit de 100.000 lei luat pe o perioadă de 240 luni (20 ani), la o dobândă anuală de 10,65%, rata lunară este 1008,48 lei. Ce sumă pot împrumuta în aceleaşi condiţii (dobândă, perioadă), dacă doresc să plătesc o rată lunară de 900 lei?

În B4 avem formula: =PMT(B3/12;B2;B1). Folosim Goal Seek pentru a afla cât trebuie să fie celula B1 (suma împrumutată) pentru a avea o rată lunară (celula B4) de 900 lei.

Goal Seek

Funcţia Goal Seek returnează valoarea 89.244 (lei)  pentru suma care poate fi împrumutată.

Meniu: Edit -> Fill

Excel facilitează completarea automată a unui domeniu de celule cu date care se repetă într-un anumit format. Se pot completa automat următoarele tipuri de date:

• text şi constante numerice;
• date calendaristice şi timpi consecutivi;
• serii de numere în progresie aritmetică / geometrică sau serii particularizate de date calendaristice / timpi;
• formule;
• liste predefinite (Custom Lists) cu informaţii de tip text.

În operaţia de umplere automată (Fill) se pot folosi comenzile din meniul Edit -> Fill sau indicatorul de umplere (fill handle) care apare în colţul din dreapta-jos al oricărei celule.

Umplere cu text şi constante numerice

Cu indicatorul de umplere:

1. Se selectează celula de început, care conţine textul sau numărul care trebuie multiplicat.
2. Se trage de indicatorul de umplere în direcţia dorită (stânga/dreapta pentru umplere pe linie, respectiv sus/jos pentru umplere pe coloană.

Din meniu:

1. Se selectează domeniul de celule care trebuie umplut (linie sau coloană), păstrând celula cu datele sursă la una din margini (stânga/dreapta, respectiv sus/jos).
2. Din meniul Edit -> Fill -> Down / Right / Up / Left corespunzător se umple domeniul de celule selectat.

Exemple:

1. În celula C3 avem textul “text”. Dacă se trage de indicatorul de umplere pe linie sau coloană, celulele A3..E3, respectiv C1..C5, vor conţine acelaşi text, “text”.

2. În celula C3 avem numărul 100. Dacă se trage de indicatorul de umplere pe linie sau coloană, celulele A3..E3, respectiv C1..C5, vor conţine acelaşi număr, 100.

Fill text

Fill număr

Umplere cu date calendaristice şi timpi

Cu indicatorul de umplere:

Modul de lucru este ca la umplerea cu text / constante numerice, cu observaţia că Excel creează liste consecutive de date calendaristice / timpi, în funcţie de direcţia în care se trage cu mouse-ul:

- descrescătoare dacă se trage în sus (pe coloană) sau la stânga (pe linie);
- crescătoare dacă se trage în jos (pe coloană) sau la dreapta (pe linie).

Pentru a multiplica aceeaşi dată sau acelaşi timp, se ţine tasta Ctrl apăsată în timp ce se trage de indicatorul de umplere.

Din meniu:

Modul de lucru este ca la umplerea cu text / constante numerice. Excel nu creează liste în acest caz, ci multiplică informaţia din celula sursă.

Exemple:

1. În celula C3 avem data 01.08.2011. Dacă se trage de indicatorul de umplere, celulele vor conţine:

- pe linie: A3: 30.07.2011, B3: 31.07.2011, D3: 02.08.2011, E3: 03.08.2011

- pe coloană: C1: 30.07.2011, C2: 31.07.2011, C4: 02.08.2011, C5: 03.08.2011

2. În celula C3 avem ora 09:00. Dacă se trage de indicatorul de umplere, celulele vor conţine:

- pe linie: A3: 07:00, B3: 08:00, D3: 10:00, E3: 11:00

- pe coloană: C1: 07.00, C2: 08:00, C4: 10:00, C5: 11:00

Fill dată calendaristică

Fill timp

Serii de numere în creşteri liniare / exponenţiale sau serii particularizate de date calendaristice / timpi

Cu indicatorul de umplere:

Pentru a defini o progresie aritmetică sau o serie particularizată de date calendaristice/timpi sunt necesare 2 informaţii:

- valoarea de start
- pasul (raţia)

Dacă se selectează 2 celule, nu neapărat adiacente, atunci pasul va fi dat de diferenţa dintre ele. Trăgând cu butonul stâng al mouse-ului de indicatorul de umplere al domeniului de celule selectate, următoarele celule se vor umple cu informaţii calculate în funcţie de celula de start, pas şi forma selecţiei iniţiale.

Exemple:

În figura de mai jos s-au evidenţiat celulele de start (au fost îngroşate – bold) şi domeniul de celule care s-a multiplicat (cu galben). Umplerea automată s-a făcut pe coloane.

Fill serie de date

- dacă A1=1 şi A2=5, trăgând de indicatorul de umplere al domeniului A1:A2 în jos vom avea A3=9, A4=13, …
- dacă B1=-1 şi B3=5, trăgând de indicatorul de umplere al domeniului B1:B3 în jos vom avea B4=11, B6=17, …
- dacă C1=01.08.2011 şi C2=08.08.2011, Excel va număra din 7 în 7 zile
- dacă D1=00:00 şi D2=03:00, trăgând de indicatorul de umplere al domeniului D1:D3 în jos vom avea D4=06:00, D5=09:00, …
- dacă E1 conţine “text1″, prin tragere în jos textul va deveni text2, text3, …
- dacă F1=”1 ian” şi F2=”1 mar”, Excel va număra din 2 în 2 luni (1 mai, 1 iul, …)
- dacă G1 conţine textul “1st”, trăgând de indicatorul de umplere al domeniului G1:G2 în jos vom avea în G3 textul “2nd”, în G5 textul “3rd” etc.

Din meniu:

Pentru a defini un anumit tip de evoluţie a datelor (progresie aritmetică, geometrică sau o serie de date calendaristice) se foloseşte meniul Edit -> Fill -> Series. Se introduce într-o celulă valoarea de start a seriei şi selectează domeniul de celule unde trebuie inserată seria. Dacă se impune în fereastra Series valoarea de oprire a seriei (Stop value), datele vor fi introduse automat până la această valoare, chiar dacă selecţia de celule este mai mare. Dacă selecţia de celule este mai mică decât valoarea de oprire, introducerea automată se va opri la dimensiunea selecţiei.

Excel detectează automat orientarea datelor selectate (pe linie sau pe coloană) şi dacă sunt de tip dată calendaristică sau numerice. Se pot introduce:

• o progresie aritmetică (Linear), caracterizată prin diferenţa constantă între 2 termeni consecutivi (raţia progresiei aritmetice). Pasul este adăugat la valoarea de pornire, după care se adaugă fiecărei valori următoare.
• o progresie geometrică (Growth), caracterizată prin raportul constant între 2 termeni consecutivi (raţia progresiei geometrice). Pasul este înmulţit cu valoarea de pornire; produsul rezultat este înmulţit din nou cu pasul şi aşa mai departe, până la ultimul element.
• o serie de date calendaristice exprimate în zile, zile de lucru (Weekday), luni sau ani;
• umplere cu aceeaşi valoare (AutoFill).

Pentru toate cazurile se pot specifica pasul (raţia) progresiei şi valoarea de oprire.

Atât în metoda utilizării indicatorului de umplere, cât şi din fereastra Series, este posibilă analiza tendinţelor (Trend) pentru o creştere liniară sau exponenţială. Dacă avem 3 sau mai multe celule selectate (pentru a mări precizia):

• dacă se glisează cu butonul stâng al mouse-ului indicatorul de umplere al celor 3 celule selectate, se realizează completarea valorilor pentru cea mai potrivită tendinţă lineară;
• dacă se glisează cu butonul drept al mouse-ului indicatorul de umplere al celor 3 celule selectate, din meniul contextual se poate alege completarea valorilor pentru o tendinţă lineară (Linear Trend) sau exponenţială (Growth Trend). În funcţie de valorile introduse şi sensul în care se glisează, seriile pot fi crescătoare sau descrescătoare.

Pentru a extinde analiza datelor complexe şi neliniare există în Excel următoarele posibilităţi:

• funcţii statistice pentru valori predictabile (preconizate): FORECAST, TREND, GROWTH, LINEST, LOGEST;
• analize complexe de regresie utilizând pachetul Data Analysis (un add-in care se găseşte în meniul Tools)

Formule

O caracteristică importantă a Excel constă în posibilitatea de a folosi referinţe relative la celule şi de a multiplica formulele între mai multe celule sau foi de lucru păstrându-se aceste referinţe relative.

Dacă o celulă conţine o formulă cu referinţe la alte celule, prin glisarea indicatorului de umplere, formula va fi multiplicată pe întregul domeniu de celule selectate, ajustându-se automat referinţele relative sau mixte la celule.

Fill formula

Exemplu: dacă în celula B1 avem formula =A1:

• prin tragerea indicatorului de umplere în jos vom avea: în celula B2 formula =A2, în B3 formula =A3 etc.
• prin tragerea indicatorului de umplere spre dreapta vom avea: în celula C1 formula =B1, în D1 formula =C1 etc.