Cel.ro

Rezultate cautare Categorie: Excel 2003 – Lucrarea 2

Etichetă (Label) în Excel 2003

de gmp
Publicat la: 20 August 2011
Comentarii: Fara comentarii

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)

Crearea şi utilizarea numelor (Name) pentru celule şi formule în Excel 2003

de gmp
Publicat la: 20 August 2011
Comentarii: Fara comentarii

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ă

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

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ă

Nume pentru o constantă

Name in workbook Refers to: Formulă nouă
dobanda =5.75% =dobanda

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ă

Nume pentru o celulă

Name in workbook Refers to: Formulă nouă
procent =Sheet1!$A$5 Din acelaşi registru de lucru:

=procent*50

Din registrul de lucru Book2.xls:

=Book1.xls!procent*50

unde Book1.xls este registrul de lucru în care s-a definit numele “procent”

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)

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

Name in workbook Refers to: Formulă nouă
domeniu1 =Sheet1:Sheet3!$A$1 =max(domeniu1)
domeniu2 =Sheet1:Sheet3!$G$1:$G$3 =sum(domeniu2)

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ă

Nume pentru o formulă

Name in workbook Refers to: Formulă nouă
maxim =MAX(Sheet1!$A$1;Sheet2!$A$2) =2*maxim
suma1 =Sheet1!$A$1+Sheet2!$A$2 =suma1

Formate condiţionale (Conditional Formatting) în Excel 2003

de gmp
Publicat la: 20 August 2011
Comentarii: Fara comentarii

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

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:

Condiţie Formatul se aplică dacă celula conţine
Cell Value Is greater than 100 110
Cell Value Is between 0 and 50 14
Cell Value Is equal to =”text” text
Cell Value Is less than =prag

unde prag este un nume (introdus din meniul Insert->Name) pentru o celulă din registrul de lucru curent.

o valoare mai mică decât valoarea din celula sau constanta denumită prag

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:

Condiţie Formatul se aplică
Formula Is =WEEKDAY(“20.08.2011″)=7 întotdeauna (condiţia este îndeplinită)
Formula Is =MOD(ROW(),2)=0 din 2 în 2 rânduri

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:

Cell Value Is Formula Is Se aplică dacă B1 conţine
not equal to =5*A10 =B1<>5*A10 o valoare diferită de rezultatul produsului 5*A10
greater than or equal to =SUM(A:A) =B1>=SUM(A:A) o valoare mai mare sau egală cu rezultatul funcţiei SUM(A:A)

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

Microsoft Excel 2003 – Lucrarea 2

de gmp
Publicat la: 20 August 2011
Comentarii: Fara comentarii

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

Sortarea datelor (Sort) în Excel 2003

de gmp
Publicat la: 23 August 2011
Comentarii: Fara comentarii

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

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.

Sort

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.

Departament Vânzări
Dep1 1500
Dep2 1200
Dep3 1800

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

Sort Header Row

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

Sort No Header Row

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

Luna Vânzări
aug 1500
iul 1200
iun 1800
mai 1580
sep 1800

Sortare ascendentă după Luna
First key sort order: Normal (sortare alfabetică)

Luna Vânzări
mai 1580
iun 1800
iul 1200
aug 1500
sep 1800

Sortare ascendentă după Luna
First key sort order: ian, feb, mar…
(sortare după listă particularizată – Custom List)

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.

Filtrarea datelor (Filter) în Excel 2003

de gmp
Publicat la: 23 August 2011
Comentarii: Fara comentarii

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:

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:

Caracter Substituie Exemple
* (asterisk) orice serie de caractere t* – toate şirurile de caractere care încep cu “t”

*t – toate şirurile de caractere care se termină cu “t”

t*t – toate şirurile de caractere care încep şi se termină cu “t”

? (question mark) un singur caracter t? – toate şirurile de 2 caractere care încep cu “t”

?t – toate şirurile de 2 caractere care se termină cu “t”

t?t – toate şirurile de 3 caractere care încep şi se termină cu “t”

~ (tilde)
urmat de *, ? sau ~
caracterele *, ?, ~ da~? – înlocuieşte şirul de caractere “da?”

da~*nu –  înlocuieşte şirul de caractere “da*nu”

Pentru a anula filtrul se debifează AutoFilter.

Exemplu:

Avem următoarele date:

Departament Vânzări Angajaţi
Dep1 1500 50
Dep2 1200 80
Dep3 1800 20
Dep4 1150 40
Dep5 1420 35

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

AutoFilter

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

AutoFilter - o înregistrare

Rezultat:

Departament Vânzări Angajaţi
Dep1 1500 50
AutoFilter Custom

AutoFilter Custom

Rezultat:

Departament Vânzări Angajaţi
Dep1 1500 50
Dep5 1420 35

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:

Departament Vânzări Angajaţi
Dep3 1800 20

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.

Advanced Filter

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:

Câmp
Valoare / Expresie

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)

Câmp
Valoare1 / Expresie1
Valoare2 / Expresie2

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)

Câmp1 Câmp2 Câmp3
Valoare1 / Expresie1
Valoare2 / Expresie2
Valoare3 / Expresie3

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)

Câmp Câmp
Valoare1 / Expresie1 Valoare2 / Expresie2

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

Câmp1 Câmp2
Valoare1 / Expresie1 Valoare2 / Expresie2

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)

Câmp Câmp
Valoare11 / Expresie11 Valoare21 / Expresie21
Valoare12 / Expresie12

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.

Câmp1 Câmp2
Valoare11 / Expresie11 Valoare21 / Expresie21
Valoare12 / Expresie12 Valoare22 / Expresie22

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

Câmp
=C5>AVERAGE($C$5:$C$10)

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)


Validarea datelor (Validation) în Excel 2003

de gmp
Publicat la: 27 August 2011
Comentarii: Fara comentarii

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

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

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

Totaluri generale şi subtotaluri (Subtotals) în Excel 2003

de gmp
Publicat la: 27 September 2011
Comentarii: Fara comentarii

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

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.

A B C D
1 Departament Luna Vânzări Angajaţi
2 Dep1 ianuarie 1500 50
3 Dep2 februarie 1200 80
4 Dep3 martie 1800 20
5 Dep2 ianuarie 1150 80
6 Dep1 februarie 1420 48
7 Dep2 martie 1250 82
8 Dep3 ianuarie 1300 22
9 Dep3 februarie 1000 21
10 Dep1 martie 890 48

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 Subtotal

Excel exemplu subtotal

Excel exemplu subtotal

Excel Subtotal

Excel Subtotal

Excel Subtotal exemplu

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.

An Departament Luna Vânzări
2010 Dep1 ianuarie 1500
2010 Dep2 februarie 1200
2010 Dep3 martie 1800
2010 Dep2 ianuarie 1150
2010 Dep1 februarie 1420
2010 Dep2 martie 1250
2010 Dep3 ianuarie 1300
2010 Dep3 februarie 1000
2010 Dep1 martie 890
2011 Dep1 ianuarie 1520
2011 Dep2 februarie 1200
2011 Dep3 martie 1855
2011 Dep2 ianuarie 1200
2011 Dep1 februarie 1465
2011 Dep2 martie 1100
2011 Dep3 ianuarie 1275
2011 Dep3 februarie 970
2011 Dep1 martie 880

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

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

Excel Subtotal exemplu

pagina 1 din 1


Utilizator: , data curenta: 19 March 2024