Data Validation -liste dinamice

Intrucat completarea pe care a propus-o Răzvan Popa in comentariul subiectului

Instrumentul Data Validation-List, crearea listelor dependente , este utila in multe situatii, m-am gandit ca n-ar fi rau sa generalizez un pic  abordarea pe care am propus-o in raspuns si sa vedem care ar fi situatiile in care ne-am dori ca lista utilizata ca sursa pentru Data Validation sa fie una dinamica, in sensul ca se poate actualiza ( se adauga informatii noi, se sterg informatii existente). Nu e deloc comod sa actualizezi denumirile din Name Manager ori de cate ori faci o modificare a listei.

Cum cream o lista dinamica pe care sa o folosim ca sursa pentru Data Validation, si in ce situatii o putem aplica?

 Pentru aceasta avem nevoie de cateva  functii: OFFSET, INDIRECT, ADDRESS, ROW, MATCH, COUNTA si de o lista  sursa pentru Data Validation.

Sa luam exemplul judetelor si a localitatilor, prezentat in topicul Instrumentul Data Validation-List, crearea listelor dependente

Pregatim o foaie de calcul cu lista judetelor ca antet de tabel si cu localitatile judetului respectiv pe coloana corespunzatoare, ca in imagine:

Am denumit fila respectiva “sursa”.

In foaia de calcul in care doresc sa realizez doua liste cu Data Validation, una pentru judete si una pentru localitati am pregatit un tabel:

In fila “sursa” selectam antetul tabelului si in Name box scriem “Judete” pentru a denumi in acest fel zona A1:AO1, cea cu denumirile de judete.

In foaia 2, pe care am denumit-o “Localitati DV” selectăm celulele A1:A15, si din Tab-ul Data, deschidem fereastra Data Validation si trecem, perntru optiunea List, ca sursa, lista cu judete.

Prima parte e gata. Acum să vedem cum putem alege doar din lista cu localitatile judetului introdus pe coloana A, indiferent de cat de multe sunt, sa se actualizeze lista cand in sursa introducem o localitate noua si sa nu avem in meniul derulant mai multe pozitii(celule goale) decat numarul de localitati din judetul respectiv.

Care sunt informatiile necesare?

1. ce coloana din tabelul cu localitati corespunde judetului ales?

2 de pe coloana respectiva, lista celulelor completate, incepand cu randul al doilea

Pentru aceasta avem nevoie de adresa primei celule si adresa ultimei celule ale domeniului ce va compune lista pentru data validation, adica: daca am selectat Arad coloana este a doua (B) si zona de celule necesara este B2:B11

Cum obtinem o lista care sa se expandeze automat?

Folosim functia Offset care permite referirea la o celula, sau un domeniu de celule, plecand dintr-o celula ca origine, cu un numar de randuri sau coloane in orice directie.

Daca dorim sa plecam dintr-o celula si sa “selectam” o intreaga zona, vom completa “inaltimea zonei”(numarul de celule)

exemplu: =OFFSET(B3,0,0,5) va returna continutul zonei B3:B8 (de la 3 inca 5 randuri in plus=8), sub forma de vector(sir)

Cum obtinem adresa celulei “de plecare” si cum obtinem “inaltimea” coloanei completate?

Pentru adresa celulei de plecare ca adresa absoluta, vom folosi intai functia Address care ne va returna o expresie de tip text cu denumirea referintei respective, pe care o transformam in referinta cu functia indirect.

Functia ADDRESS are urmatoarea sintaxa:

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

ADRESS(2,3,2,,”sursa”)

inseamna: randul 2, coloana a treia, cu referinta absoluta pentru rand, din fila “sursa”

si returneaza: sursa!C$2

Stim ca vom incepe cu randul al doilea, dar numarul coloanei cine il returneaza?

Pentru aceasta voi folosi functia Match, care va cauta numele judetului in antetul tabelului si va returna numarul de ordine al acestuia in antet ( numarul coloanei pe care se afla)

=MATCH(A3,Judete,0) va returna 1 (prima coloana) daca in A3 am ales judetul “Alba” si 4 daca am ales “Bacau”

Astfel, functia noastra Address va arata asa:

=ADDRESS(2,MATCH(A3,Judete,0),2,,”sursa”) si va returna sursa!B$2 daca in A3 am ales judetul Arad.

Pentru a obtine chiar adresa celulei aplicam functia Indirect:

=INDIRECT(ADDRESS(2,MATCH(A3,Judete,0),2,,”sursa”))

Am obtinut acum formula cu care  vom inlocui in Functia OFFSET adresa B3

Pana in acest moment formula noastra arata asa:

=OFFSET(INDIRECT(ADDRESS(2,MATCH(A3,Judete,0),2,,”sursa”)),0,0,5)

 Mai avem acum nevoie de o formula care sa ne returneze “inaltimea” coloanei cu nume de localitati. (una care sa inlocuiasca valoarea 5 din OFFSET cu dimensiunea zonei cu localitati)

Vom numara cate celule sunt de la prima (cea returnata de prima functie Address, pe aceeasi coloana, cate sunt completate, mai putin una, ccea de antet)

Formula:

=COUNTA(INDIRECT(ADDRESS(1,MATCH(A3,Judete,0),2,,”sursa”)&”:”&ADDRESS(ROWS(sursa!A:A),MATCH(A3,Judete,0),2)))-1

Va aparea in OFFSET, in locul lui 5.

Formula OFFSET, completa, o copiem, selectam coloana pe care dorim sa aplicam regula de validare si ca mai sus, selectam optiunea list si dam paste formulei noastre si… gata!

Alegem un judet si avem la dispoziie doar localitatile lui, fara randuri goale si cu posibilitatea actualizarii listei.

Dar pauza unde e?

La un curs organizat undeva in partea a doua a zilei. In sala, dupa o zi de munca, toti obositi. I-am tinut in priza cat am putut de mult pana cand s-au conectat.

“Dupa ce ati afisat rezultatul filtrarii, salvati fisierul si…. pauza!”

O doamna, foarte concentrata, cu ochii in ecranul monitorului:

“Dar pauza, unde e?”

 

Cum sa nu se mai afiseze eroarea #N/A la aplicarea functiei Vlookup?

Raspunsul este IFERROR

Functia IFERROR are doua argumente: expresia ce va fi evaluata si valoarea ce va fi afisata daca avem eroare.

Interesant este ca,  mai demult, pentru a obtine acelasi rezultat foloseam combinatia de functii IF si ISERROR. E drept, cu mai  multa risipa de timp si caractere.

Ceva de genul:

IF(ISERROR(VLOOKUP(…..), “E EROARE”, VLOOKUP(….))

Ceea ce insemna ca scriam de doua ori sintaxa functiei de cautare. Acum, cu IFERROR acest lucru nu mai este necesar. Scriu doar ce sa se intample cand e eroare. Cand nu este, se executa VLOOKUP-ul.

IFERROR(VLOOKUP(…),”Nu aveti valoare”). Daca nu vreau sa se afiseze nimic, scriu o pereche de ghilimele, fara spatii intre ele, “” (Ceea ce inseamna de fapt sirul null, sau blank)

 

Curs Excel 2007/2010/2013 Formule și funcții

Cum să aleg formula potrivită și ce funcții să folosesc pentru a-mi rezolva problema?

De ce îmi dă eroare? Ce n-am făcut bine? Ce înseamnă eroarea #N/A și ce anume a generat-o?

Iată doar câteva întrebări la care veți putea răspunde dacă participați la acest curs.

Și toate acestea cu multe exemple și exerciții practice!

 

Ce veți învăța?

• Să vă organizați corect datele
• Să utilizați referințele relative și referințe absolute
• Să identificați funcțiile necesare rezolvării problemei dumneavoastra
• Să utilizați formule și funcții, de la cele mai uzuale la cele mai complexe
• Să verificați sintaxa unei formule și să eliminați erorile
• Să creați reguli de validare complexe, bazate pe formulele și funcțiile învățate
• Să aplicați un format condiționat bazat pe reguli create de voi cu ajutorul funcțiilor studiate
• Să realizați formule complexe utilizand functii “în cascadă”

Modulul 1 (4 ore)

• Organizarea datelor in Excel, tipuri de date și specificații tehnice
• Operații și operatori, formule, ordinea efectuării operațiilor
• Adrese relative și absolute – cum să completezi un tabel cu o singură formulă
• Auditarea formulelor. Identificarea celulelor care participă la calcul, dependențe
• Erori: identificare și semnificație(#REF, #N/A, #VALUE,#DIV!0, #NAME etc).
• Verificarea formulelor și identificarea erorilor
• Setarea modului de calcul (automat sau manual) și influența acestuia asupra rezultatelor afișate.
• Ce sunt funcțiile Excel? Tipuri de funcții, categorii, construire sintaxă.
• Funcții de bază: Sum, Average, Count, Counta, Countblank, Min, Max, Today, Now
• Funcții de tip Date&Time
  • Functiile DATE si TIME
  • Functiile TODAY, NOW
  • Functiile WORKDAY si NETWORKDAYS
  • Functiile WEEKDAY si WEEKNUM
• Funcții de tip text
  • Functiile LEFT, MID, RIGHT
  • Functiile UPPER, PROPER, LOWER
  • Functiile LEN, TRIM, TEXT, CLEAN

Modulul 2 (4 ore)

• Funcții condiționale
  • Functiile COUNTIF, SUMIF, AVERAGEIF
  • Functiile COUNTIFS, SUMIFS, AVERAGEIFS
  • Funcții logice. IF, AND, OR, NOT
• Funcții de căutare. Referințe
  • VLOOKUP
  • INDEX
  • MATCH
  • CHOOSE
  • VLOOKUP cu cautare la stanga
  • ROW, ROWS, COLUMN, COLUMNS
  • INDIRECT, ADRESS, OFFSET
• Funcții de tip array (CSE)
  • Vectori, operații vectoriale
  • Funcții vectoriale
• Data validation
  • Instrumentul Data Validation-List
  • Liste de validare dependente
  • Data Validation cu selecții multiple
  • Liste de validare dinamice (search)
• Conditional Formatting.Utilizarea functiilor pentru a crea o regulă proprie de formatare condiționată

 

Pentru detalii și înscrieri mă puteți contacta pe  email, la adresa: diana.tanase@officetraining.ro

Special Cells – celule cu proprietăți speciale

Știm cu toții că, în Excel, într-o celulă, putem introduce numere și text. Dacă dorim să aplicăm un format, deschidem fereastra Format Cells și modificăm atributele celulei: formatul numărului, aliniamentul textului în celulă, font-ul, chenarul, culoarea de fundal sau atributele de securitate (protejarea celulelor).

Dar, unei celule îi putem aplica și un format condiționat sau o regulă de validare.

Cum putem selecta dintr-o zona(un tabel) toate celulele care conțin formule, sau toate celulele goale, sau pe cele cu regulă de validare?

Cum pot selecta și îndepărta simultan rândurile sau coloanele goale dintr-un tabel?

Iata doar câteva întrebari la care răspunsul este GO TO-Special.

Să luam spre exemplu cazul unui tabel pe care l-am importat dintr-o aplicație și care are rânduri goale intercalate. Dorim să le selectăm pe toate și să le ștergem. Pentru aceasta, selectăm zona de date, acționam comanda GO TO-Special ( CTRL+G și clic pe butonul Special sau, din fila Home, în meniul Find and select, Go to special)

În fereastra Go To Special, bifăm opțiunea Blanks, se selectează rândurile goale și ștergem rândurile selectate cu Delete> Entire Row.

 

Dacă  avem un tabel în care, pe mai multe coloane am impus reguli de validare diferite, putem selecta celulele care au o regulă de validare impusă oricare ar fi aceasta), sau pe cele care au aceeași regulă de validare.

Avem un tabel, în care, pe doua coloane am impus regula: ”să fie număr între 10 și 1000”, și pe o altă coloană avem o regulă de tip List, cu opțiunile a, b, c.

Cum putem selecta toate celulele care au prima regulă de validare?

Selectăm o celulă cu această proprietate, deschidem fereastra Go To Special cu una din metodele de mai sus, bifăm Data Validation cu opțiunea Same.

Se for selecta toate celulele din foaia de calcul care au aceeași regulă de validare ca și celula selectată.

In Access poti atasa un document Word sau un PDF?

Intrebarea zilei de astazi de la o fosta participanta la cursurile mele.

Sigur ca se poate, conteaza insa ce versiune de Access utilizezi.

Voi prezenta o solutie pentru Access 2010.

Avem un tabel cu angajati si dorim sa atasam CV-ul acestora. Unul il are intr-un fisier .doc altul intr-un PDF. Cum procedam?

Sunt 2 variante, fiecare cu bune si cu rele.

Varianta 1

Cream un camp de tip hyperlink si folosim doar calea  catre documentele respective. Neajunsul in acest caz ar fi “legatura” cu fisierul sursa care, daca se muta sau se sterge sau se redenumeste, strica lucrurile. Avantajul acestei variante ar fi economia de spatiu. Stim cu totii ca dimensiunea maxima a unei baze de date Access este de 2GB, si nu trebuie sa-i irosim.

In capmpul nou creat, dam click dreapta pe prima inregistrare si alegem optiunea Hyperlink>Edit Hyperlink

 

Varianta 2

Cream un camp de tip Atachment care permite, pentru un singur angajat(pentru o singura inregistrare) sa atasam bazei de date mai multe fisiere (CV-ul, o diploma, o recomandare, un scan dupa contract etc.) ca in imagine:

Atat pentru introducere, cat si pentru vizualizare utilizati dublu click in celula de pe coloana Atachment.

Funcția logică IF

Functia IF se utilizeaza pentru a obtine doua sau mai multe operatii, valori diferite, in functie de valoarea de adevar a unei conditii logice impuse.

Un exemplu clasic este cel legat de evaluarea unui chestionar sau  de rezultatul unui examen format din mai multe probe, sau aplicarea diferentiata a cotei de TVA pentru anumite produse sau servicii etc.

Functia IF are trei argumente:

IF(logical_test, value_if_true, [value_if_false])

logical_test- aici introducem o expresie logica (o comparatie) si in functie de valoarea ei de adevar (TRUE/FALSE), functia IF va returna fie valoarea specificata la al doilea argument (daca expresia logica returneaza TRUE) fie pe cea specificata la al treilea argument (daca expresia returneaza FALSE)

value_if_true, [value_if_false]- chiar daca denumirea acestor argumente sugereaza introducerea unei valori nu ne vom limita doar la aceasta optiune. Putem introduce numar, text, sau o expresie (o formula) pentru fiecare argument in parte.

Functia IF se poate utiliza ca argument pentru o alta functie IF, numarul de imbricari depinde insa de versiunea de Excel pe care o utilizati (pentru aceasta, consultati specificatiile tehnice ale aplicatiei Excel).

Sa luam urmatorul exemplu:

La un examen (test) se inregistreaza punctajul obtinut. Pentru candidatii care au luat peste 7 calificativul sa fie “AVANSAT” iar pentru ceilalti “INCEPATOR”

Sintaxa functiei IF va fi:

IF(nota>=7, “AVANSAT”,INCEPATOR”), unde nota este referinta celulei ce contine nota candidatului.

Dar daca dorim o clasificare pe 3 nivele, AVANSAT, MEDIU, INCEPATOR?

Vom avea nevoie de 2 functii IF imbricate.

IF(nota>=7, “AVANSAT”, IF(nota>=5,”MEDIU”,INCEPATOR”))

Sintaxa de mai sus se interpreteaza astfel:

Daca nota depaseste 7 atunci categoria este AVANSAT. Daca nu (adica este mai mica decat 7) si depaseste 5 atunci categori este MEDIU, iar daca nu (este mai mica decat 5) atunci este in categoria INCEPATOR.

Se poate vizualiza constructia functiei IF, mult mai usor, folosind o schema de genul:

Instrumentul Data Validation-List, crearea listelor dependente

Data Validation este un instrument de restrictionare a valorilor pe care le putem introduce intr-o celula. Sa zicem, ca intr-o celula dorim sa introducem denumirea unui judet. Ca sa nu avem surpriza de a scrie gresit acea denumire vom crea o lista de tip drop-down si vom alege denumirea judetului respectiv.

Sunt doua variante de a introduce o lista in meniul derulant.

Prima ar fi sa scriem “de mana” lista respectiva, iar a doua, sa o importam(sa o imprumutam) dintr-o zona a foii de calcul in care am scris-o in prealabil.

Cazul 1:

Selectam zona careia dorim sa-i impunem regula de validare, deschidem fereastra Data Vaidation(din tab-ul Data>grupul Data Tools> Data Validation)

Si din meniul Allow alegem optiunea List.

Introducem lista judetelor separate cu “,” (virgula) sau “;” (punct si virgula), in functie de setarile regionale ale sistemului de operare.

Dupa care apasam OK, si folosim lista.

Cazul 2

Daca avem o lista (lista judetelor, spre exemplu) intr-o foaie Excel, o putem utiliza ca sursa pentru Data Validation>List

In fisierul din imagine, avem un tabel structurat astfel:

• Pe prima coloana avem lista judetelor a caror denumire o regasim si pe coloane.
• Pe urmatoarele coloane avem listele localitatilor din judetele din antet.

Utilizam facilitatea  Create name from selection pentru a da numele din antet fiecarei coloane din tabel de sub acesta.

Pentru aceasta selectam tabelul, apoi dam un clic pe Create name from selection si debifam optiunea Left Column pastrand doar optiunea Top row.

 

In zona careia dorim sa-i aplicam o regula de validare vom utiliza lista “Judete” pentru a alege din lista judetelor.

 

Liste de validare dependente

Ne dorim acum ca dupa selectarea judetului sa putem alege orasul doar din lista oraselor judetului respectiv. Pentu aceasta, vom folosi functia Indirect care returneaza adresa zonei scrisa ca text in celula C3(in cazul nostru, adresa zonei cu denumirea judetului respectiv)

 

La alegerea unui judet, lista oraselor se va limita doar la orasele din judetul respectiv.

Fisierul excel judete si localitati.xlsb il gasiti aici:

Funcțiile COUNTIFS, SUMIFS, AVERAGEIFS

Dupa cum bine stim, pe un tabel Excel putem aplica mai multe filtre. Sa zicem, vanzarile produsului X  pe luna Y, necesita doua filtre: unul pe coloana cu produse (sa fie produsul X), si unul pe coloana cu date calendaristice (sa fie doar date din luna Y). Daca dorim sa numaram cate vanzari ale produsului X s-au inregistrat in luna Y, sau sa calculam totalul incasarilor sau cantitatea medie pentru perechea specificata, vom folosi cele trei functii: COUNTIFS, SUMIFS si AVERAGEIFS. Acestea sunt similare cu functiile COUNTIF, SUMIF, AVERAGEIF, cu deosebirea ca putem pune conditii de filtrare pe mai multe coloane si coloana pe care se efectueaza operatia (pentru functiile SUMIF si AVERGEIF) nu mai este optionala, ea trebuie specificata.

In exemplul prezentat anterior analizam doar vanzarile pe client. Sa facem o analiza similara pe client, detaliat pe produs, si anume: cate comenzi a facut fiecare client pentru fiecare produs, ce cantitate medie a comandat din fiecare si care a fost valoarea totala a comenzilor pentru fiecare produs.

Pentru a numara comenzile fiecarui client, pe fiecare produs, vom organiza tabelul de analiza astfel:

Lista clientilor ca si cap de rand, lista produselor, cap de coloana (sau invers)

Putem, de asemenea, sa cream doua liste de tip Data Validation si, in functie de alegerea pe care o facem, sa ni se afiseze informatia asociata perechii alese (Client-Produs)

In figura de mai jos, in sintaxa functiei Countifs, client este numele zonei C2:C149 (coloana cu clienti) sin tabelul sursa, iar Produs este coloana D2:D149 din tabelul sursa (coloana cu denumirile produselor.

Sintaxa functiei

=COUNTIFS(client,$B3,produs,C$2)

se interpreteaza astfel: “Numara daca pe coloana client am clientul din celula B3 si pe coloana produs am produsul din C2”

In mod analog, pentru SUMIFS

“Aduna valorile de pe coloana Total  pentru clientul din B3 si produsul din C2”

Fisierul sursa este disponibil aici.

 

Funcții condiționale. Funcțiile COUNTIF, SUMIF, AVERAGEIF

Functiile enumerate mai sus, chiar daca in Excel sunt prezentate in categorii diferite( statistice, matematice) au caracteristici si actiuni similare. Singura diferenta consta in operatia pe care o efectueaza (count-numarare, sum-suma, average-media aritmetica). Ati folosit de multe ori functia SUM, spre exemplu, pentru a calcula totalul valorilor inscrise pe o coloana. Dar, daca nu dorim sa calculam totalul pe toata coloana ci doar pentru acele valori care corespund unor informatii aflate pe o alta coloana?

In aceasta situatie aplicam un “filtru”. Ce inseamna, de fapt, un filtru? O comparatie, o conditie logica, pe care o impunem unui set de date pentru care dorim sa pastram doar informatiile care respecta conditia impusa.

 

Sa luam urmatorul exemplu:

Avem un tabel in care am inregistrat comenzile. Dorim sa numaram, pe fiecare client, cate comenzi a facut, care este valoarea totala, pe client, a produselor comandate, si care este cantitatea medie comandata de fiecare client.

 

Pentru prima cerinta, vom folosi functia countif, cu urmatoarea sintaxa:

COUNTIF(range, criteria)

Range -zona din tabelul sursa careia îi impunem o coniție de filtrare (poate fi denumirea unei zone)

Criteria-Conditia pe care va trebui sa o indeplineasca zona selectata si pentru care se face numararea

Criteriile pentru functile din aceasta categorie se construiesc sub forma de expresie si contin operatorul logic (de comparatie) si valoare cu care se compara daca nu se face comparatia prin egalitate, caz in care, operatorul “=” (egal cu), nu se scrie. Expresia se introduce intre ghilimele.

Exemple de criterii:

”>100” mai mare decat 100, “Ionescu” egal Ionescu, “<>P1” diferit de P1

Daca am scris criteriul intr-o celula, atunci se va folosi adresa celulei respective.

Functiile SUMIF si AVERAGEIF au un al treilea argument, optional, si anume, coloana(zona), pe care se face operatia.

SUMIF(range, criteria,[sum_range]), respectiv

AVERAGEIF(range, criteria, [average_range])

De ce este necesar? Daca impunem conditia de filtrare pe coloana Client, vom dori sa adunam valorile corespunzatoare acelui client, de pe coloana Total.

Daca, insa, aplicam conditia si operatia de adunare pe aceeasi coloana, al treilea argument nu mai este necesar. Spre exemplu, dorim sa adunam toate valorile care depasesc 100. Atat conditia(filtrul) cat si adunarea se fac pe aceeasi coloana( cea cu valori).

In exemplul propus, argumentul range il reprezinta coloana Client din tabelul sursa, criteriul pentru numarul de comenzi il reprezinta, de fapt, aparitia codului unui client anume in lista.

Formula va fi:

Countif(client,”Client1”), si numara de cate ori apare Client1 pe coloana cu codurile clientilor (aceasta aparitie, semnifica faptul ca acel client a facut o comanda)

Pentru functiile SUMIF si AVERAGEIF, coloana pe care punem filtrul si filtrul, sunt aceleasi ca pentru COUNTIF dar va trebui sa speificam, pe ce coloane din dreptul valorilor valide, sa se faca operatia.

La SUMIF pe coloana Total,

iar la AVERAGEIF pe coloana Cantitate.