1. Filtre avansate

Instrumentul de Filtrare avansată îl găsim în meniul Data, apăsând pe butonul Advanced Filter.

Acesta reprezintă unul dintre cele mai complexe și puternice facilități din Excel, din păcate prea puțin cunoscut și folosit de către utilizatorii obișnuiți.

Avem un tabel cu foarte multe linii, apăsăm Ctrl + Săgeată jos pentru a ne poziționa la ultima sa linie, pentru a vedea cât de mare este acest tabel, apoi ne întoarcem la prima linie (cu Ctrl + Săgeată sus).

Dorim să aplicăm pe acest tabel nu mai puțin de cinci condiții logice, în sensul că dorim să găsim din el doar liniile specifice acestor cinci produse pe care le-am adăugat în acest tabel de mai sus.

După ce aplicăm filtrul cu cele 5 condiții logice, dorim să afișăm doar coloanele Company, Product și Cost. Observați că într-o foaie separată am copiat în prealabil aceste trei etichete de coloane: Compania, Produsul și Costul

O să dăm un nume acestui tabel cu condițiile logice: să-i spunem ConditiiLogice.

O să dam un nume prietenos și tabelului mare cu datele pe care urmează să-l filtrăm. Pentru aceasta, apăsăm Ctrl+A pentru a-l selecta: și o să-i spunem Tranzactii.

Iată, am creat două Named Range-uri, primul se numește ConditiiLogice, cu acele 5 condiții de căutare și al doilea se numește Tranzactii pentru tabelul pe care dorim să-l filtrăm.

Trebuie sa fim poziționați întotdeauna în foaia în care dorim să aducem rezultatul final, apăsam Advanced Filter și observați fereastra cu toate opțiunile ce ne permit filtrarea avansata.

In căsuța List Range trebuie să-i spunem care este tabelul pe care dorim să-l filtrăm. Apăsăm tasta F3 și îi indicăm named range-ul Tranzactii, apoi în căsuța Criteria Range din nou apăsam tasta F3 și-i dăm Named Range-ul ConditiiLogice. În continuare aapăsând Copy to another location, se activează căsuța Copy to, iar aici îi indicăm cele 3 etichete de coloană pentru care dorim rezultatul: Company, Product și Cost.

Incredibil, în acest moment avem pe ecran copiate doar produsele specificate și doar cele 3 coloane dorite din tabelul original cu tranzacțiile.

Filtrul avansat merită studiat în profunzime, deoarece acesta este singurul care ne poate filtra cu câteva click-uri de mouse tabele mari de date pe care dorim să aplicăm multiple condiții logice de căutare.

Ce ati vazut în acest demo reprezintă doar o foarte mica parte dintre facilitățile acestuia.

2. Named Range dinamic

Dacă dorim, de exemplu, aici, în acest centralizator să facem suma celulelor specifice Trim 2 și lucrăm standard, folosim funcția SUM, deschidem paranteza, selectăm cu tastele Ctrl + Shift + Săgeată jos toate celulele de pe coloana Trim 2 și apăsăm Enter.

Problema este că, dacă adăugăm un nou oraș, observați că nu se actualizează valoarea sumei pentru că rândurile noi nu duc la redimensionarea setului de celule din formula cu suma.

Ștergem rândul nou adăugat. Am sa selectez întreg tabelul, apoi din Formulas alegem Create From Selection din grupul de opțiuni Defined Names și o să creăm astfel named range-uri doar pentru coloane și observați că acum avem câte o denumire pentru fiecare dintre coloanele tabelului.

Problema este că nici aceste named range-uri nu sunt dinamice. De exemplu, dacă îi zicem: =Sum (apasam tasta F3 și selectăm Trim_2, acum dacă venim din nou cu un Oraș nou, se vede că tot nu este actualizată  suma, așa după cum observați.

Apăsăm Ctrl+Z, ștergem formula și acum transformăm acest tabel cu Ctrl+T într-un tabel inteligent. În acest moment apar niște facilități în plus pentru acest tabel, printre care și acela ca TOATE NAMED RANGE-URILE DIN EL DEVIN DINAMICE.

De exemplu, scriem =SUM( apăsăm F3, alegem Trim_2, iar acum adăugăm un oraș nou și observați că tabelul s-a redimensionat automat.

=SUM(Trim_2)

Dacă adaug 100 la Trim 2, observați că și suma s-a actualizat automat.

Concluzia este că dacă transformăm un tabel obișnuit într-unul inteligent (apăsăm  Ctrl+T), automat toate named range-urile din el devin dinamice și nu mai trebuie să ne punem problema dacă calculele făcute pe coloanele sale vor include și noile rânduri.

3. Selectarea unei coloane cu celule necompletate (blanks)

Primesc foarte des această întrebare la cursurile mele de Excel.

Cum putem selecta o coloană ce conține multe celule necompletate. Cu Ctrl+Shift+Săgeată jos nu functionează direct, deoarece se oprește în fiecare celulă Blank. Dacă în coloana noastră avem sute de astfel de celule necompletate, devine aproape imposibil să folosim acest shortcut.

Există două metode pentru a rezolva această problemă.

Prima metodă presupune să transformăm tabelul într-unul inteligent (cu CTRL+T). În acest moment, tabelul nostru conține o serie de functionalități în plus față de tabelele obișnuite.

De exemplu, CTRL+Space selectează automat coloana cu datele din tabelul respectiv.

Sau apăsăm sus pe coloană imediat după ce vedem această săgeată neagră.

Sau apăsăm click dreapta pe coloana respectivă și alegem Select, apoi Column Data. Iată trei metode prin care putem să selectăm o coloană cu date dintr-un tabel inteligent.

Însă dacă tabelul este unul standard? O secundă, să-l fac din nou tabel normal.

Există o metodă mai complicată, însă ea functionează. Înghețăm prima linie din tabel: Freeze Top Row. Observați că prima linie a tabelului rămâne acum blocată pe ecran la scroll în jos. Ne poziționăm pe o coloană care sigur nu are celule necompletate, de pildă, pe prima coloană care se presupune că identifică rândurile unic și care este, de regulă, obligatorie de completat. Apăsăm Ctrl + Săgeată jos pentru a ne poziționa la finalul tabelului, apoi ne poziționăm pe ultima celulă de pe coloana pe care dorim sa o selectăm, apăsăm Shift apoi click pe eticheta Product de sus care a rămas vizibilă pe ecran datorită comenzii Freeze.

Se vede că, practic, în acest moment, avem toata coloana cu date selectată. Dacă nu ne convine că este selectată și eticheta coloanei, apăsăm și un click sageata jos și rămân selectate doar datele de pe coloana dorită.

4. Liste în cascadă

Dorim să creăm liste în cascadă. De pildă, în celula A2 să ne apară o listă derulantă cu toate mărcile: BMW, Dacia și Mercedes și, în funcție de ce selectăm din acea listă de la Model, să ne apară doar modelele Mărcii respective. De exemplu, dacă selectăm Dacia la Model, să apară Duster, Logan, Sandero.

După cum observați am făcut un tabel în care prima coloană reprezintă Mărcile iar apoi pentru fiecare Marcă avem o coloană cu Modelele.

Selectăm întregul tabel, iar din Formulas, alegem Create From Selection, debifăm Left pentru a rămâne doar TOP selectat și observați că s-a creat un Name Range pentru fiecare coloana: Marci conține mărcile și apoi, pentru fiecare Marca, avem modelele: BMW, Dacia și Mercedes.

La celula cu Marca o să adaugăm un Data Validation, și îi spunem că dorim o listă, iar în căsuța Source apăsăm tasta F3 și îi spunem că aici o să punem o listă cu Mărcile. Observați că a apărut lista cu toate marcile.

Acum la Model apăsăm tot Data Validation, iar la Source, scriem o formulă: =INDIRECT(  și alegem celula Marca.

=INDIRECT(A2)

Ce face functia Indirect?

INDIRECT(A2) – Dacia. INDIRECT(Dacia) creează named range-ul Dacia și vă aduc aminte că Name Range-ul Dacia are o listă cu modelele Dacia. De exemplu, dacă în A2 alegem BMW la Marca, INDIRECT(BMW) înseamnă mărcile de la BMW. Asta este tot!

Observați că, dacă acum selectez Marca BMW, aici apar doar modelele de la BMW, daca aici selectez Mercedes, apar doar modelele de la Mercedes. Și, în sfârșit, dacă selectăm Dacia, avem doar modelele de la Dacia.

Foarte interesante aceste liste în cascadă, mai ales că se fac, după cum ați văzut, fara programare, fără Macro sau VBA, doar cu câteva click-uri de mouse.

5. Power Query

Avem două tabele diferite care au o coloană în comun. Problema este că una dintre coloane are numele inversat cu prenumele.

De exemplu, dacă sortăm acest tabel, se vede că aici este Ramos Allan, iar în celălalt tabel apare Allan Ramos.

Dacă dorim să facem un Vlookup între cele două tabele , ne va fi imposibil deoarece nu există o regăsire exactă a valorilor SalesRep.

Power Query este un instrument foarte puternic implementat în Excel, ce ne permite să facem o serie de transformări automate în tabele cu date.

O să-l folosim aici urmatoarele operatii:

În acest tabel, vom sparge coloana SalesRep în două coloane, într-una apare Numele și, în cealaltă, Prenumele.

Apoi le vom concatena din nou în ordinea corectă, așa cum apare în celălalt tabel.

După ce avem cele două coloane, SalesRep din primul tabel și SalesRep din cel de-al doilea identice din punct de vedere structură, vom face operația de Merge între cele două tabele, adică echivalentul folosirii funcției VLOOKUP din Excel.

Dupa operația de Merge, vom avea un singur tabel ce va conține doar elementele comune din cele doua tabele inițiale. Vom grupa și agrega acest tabel cu liniile comune.

Pentru a lucra cu Power Query pentru partea de Transformare, mergem pe primul tabel, iar din ribbon-ul Data, grupul Get & Transform Data, alegem From table/Range, spunem ca Tabelul Are cap de tabel.

Observați că se deschide o fereastră dedicată, numită Power Query Editor. Vom da un nume acestei interogări, de exemplu Tari, selectăm coloana pe care dorim sa o spargem. Observați pentru aceasta butonul Split Column.

Split-ul se va face după un delimitator, Space in cazul noastru.

Observați că acum, dintr-o coloană inițială, avem două, iar aceste două coloane le vom lipi la loc în ordinea corectă. Pentru aceasta, selectăm mai întâi SalesRep 2, apoi SalesRep 1, mergem în Transform și apăsăm butonul Merge columns, cu separatorul Space.

Noua coloană se va numi SalesRep. Ați înțeles că le-am spart doar ca să le unim din nou în ordinea corectă. Apăsăm OK.

După ce am facut această primă transformare, din Home, alegem Close and Load To… și doar am vrut să facem în background aceste operații, adică încă nu dorim să punem tabelul transformat în Excel  pentru că încă nu am terminat ce aveam de făcut.

Mergem pe cel de-al doilea tabel și apăsăm același buton From table/Range pentru a-l importa în Power Query. Aici nu avem de făcut nicio transformare, poate doar să dăm un nume prietenos acestei ultime interogăr: Vanzari.

Ne poziționăm în prima interogare si îi spunem că dorim să o relaționăm cu interogarea Vanzari, unde coloana de legatură este SalesRep (care este comună celor doua tabele). În Excel, facem acest lucru cu o formulă de tip Vlookup, aici doar îi indicăm vizual care sunt cele două tabele legate. Cum dorim ca din cele doua tabele să facem unul singur, care contine liniile lor comune, din Home, alegem Merge Queries as New (adică într-o nouă interogare).

Coloana de legatură SalesRep din Tara este conectată cu coloana de lagătură SalesRep din Vanzari. Trebuie selectate cele două coloane comune din cele două interogări. Observați că ne spune că a găsit rânduri comune între cele două tabele.

Apăsăm OK și, iată, această nouă interogare creată căreia îi dăm numele Tranzactii.

Observați că se pornește de la interogarea Tari. Apăsăm pe acest buton din dreptul coloanei Vanzari și îi spunem că dorim să vedem Product și Unit Price.

După cum vedeți, avem pe ecran, pentru fiecare Persoana cu tranzacțiile sale și numele Tarii din care face parte.

Practic, în acest moment avem cele doua tabele consolidate într-un singur tabel numit Tranzactii care conține toate coloanele din cele două tabele inițiale.

Grupăm această nouă tabelă după coloana Country și ne interesează să vedem Suma unităților (pentru fiecare țară în parte).

Sortăm după coloana Country…

Practic, am pornit de la doua tabele diferite, am făcut o transformare în primul tabel, am spart coloana de legatură în două coloane, apoi le-am unit din nou în ordinea corectă. Am făcut un Vlookup între cele două tabele (aici se numeste Merge…), iar la final am făcut o grupare dupa coloana Country pentru a vedea suma unităților pentru fiecare țară în parte.

Importăm această ultimă interogare grupată în foaia curentă , facem o conexiune către ea pentru a apărea în lista din dreapta și, cu click dreapta pe ea, selectăm Load To …

Bifăm Table iar, în Existing Worksheet îi indicăm doar celula de unde să înceapă copierea.

Observați minunea. Dacă aș fi încercat toate aceste operații direct în Excel, ar fi fost destul de complicat de făcut. Cu ajutorul instrumentul Power Query, am reușit, doar cu câteva click-uri și NICIO formulă, să fac un centralizator grupat din două coloane care nici macar nu erau inițial relaționate cum trebuie.

Power Query împreună cu Power Pivot sunt cele mai în vogă instrumente de analiză a datelor din Excel. Merită să le aprofundați!

6. Power Pivot

Power Pivot reprezintă o extensie a instrumentului Power Query pe care l-ați vazut mai devreme, instrument ce ne permitea să facem „curățarea” și transformarea datelor. După ce datele au fost pregatite cu Power Query, le importăm în modelul de date Power Pivot. În primul rând, apăsăm, din Data pe Queries and Connections și cele două tabele cu care am lucrat mai devreme, Tari și Vanzari le adăugăm în Power Pivot , apăsând click dreapta pe ele și alegând Load To…

Bifăm opțiunea Add this Data in Data Model.

Pentru a intra în instrumentul Power Pivot, avem două variante: ori se apasă acest buton Go To Power Pivot Window.

…sau a doua metodă: există și o bară de instrumente dedicate Power Pivot, în care apăsăm Manage.

Observați cele două tabele, Tari și Vanzari. Problema este că aceste două tabele NU sunt relationate. Pentru a face acest lucru, mergem sus și apăsăm Diagram View, apoi tragem coloana SalesRep din Tari peste coloana SalesRep din Vanzari.

În acest moment putem să apăsăm Pivot Table pentru a crea un Tabel Pivot din cele două tabele.

Stabilim unde anume să deseneze Tabelul Pivot. Îl punem începând cu celula H7.

Observați instrumentul de pivotare. Diferența față de tabele Pivot standard este că acum, în dreapta, avem ambele tabele din care putem alege coloanele ce fac parte din pivot.

Acum desenăm tabelul pivot ca și cum am avea de-a face cu un singur tabel. De exemplu, punem țările pe coloane pentru că sunt mai puține, produsele le punem pe rânduri pentru că sunt mai multe, iar, de agregat, agregăm Unit Price.

Putem să adăugăm coloana Unit Price de mai multe ori, o data cu Suma și cealaltă cu Media.

După cum vedeți, aici este tot un Pivot Table numai că lucrăm cu mai multe tabele simultan, bineînțeles, tabele ce au coloane în comun și care au fost, în prealabil, relaționate.

Învățați să folosiți aceste două instrumente NOI atât de puternice (Power Query și Power Pivot) și veți putea să Transformați, Relaționați și să Agregați datele foarte ușor, chiar dacă acestea sunt situate în multiple surse tabelare. După cum ați văzut: DOAR cu mouse-ul, fără să fiți nevoiți să scrieți formule.

7. Array Formula

În acest tabel, dorim să vedem unde există în prima coloană valoarea identica cu cea din a doua coloană.

De exemplu, dacă aici scriem o formulă simpla: = valoarea din prima coloană = valoarea din a doua coloană, iată cum avem, ca și răspuns, TRUE.

Vom copia formula până jos și vedem, pentru fiecare rând, unde avem valoarea de pe prima coloană egală cu valoarea de pe a doua coloană.

Dar dacă schimbăm formula astfel: in loc de o celulă să scriem că o coloană este egală cu alta coloană?

Cum adică să întrebăm dacă o coloană este egală cu altă coloană?

În realitate, în acest caz, Excel va compara valorile de pe fiecare rand pentru fiecare coloană și va întoarce un vector de valori TRUE și FALSE

De exemplu, dacă selectăm acest predicat logic și apăsăm tasta F9 pentru a-l evalua, observați că ni se întoarce o listă de valori TRUE sau False. Pentru primul rând, ne dă True, pentru al doilea False și așa mai departe.

Apăsăm Ctrl + Z pentru a ne intoarce. Deci, ați înțeles că, în acest caz, dacă vom compara o coloană cu o altă coloană, ni se intoarce o listă cu valori TRUE și FALSE.

Ce ar fi să punem între paranteze acest predicat logic ce întoarce True și False și să adăugăm doua semne minus în fața acestei expresii?

Știți că, dacă punem doua semne minus în fața unui operand, practic operandul respectiv rămâne nemodificat, de pildă, minus minus 5 este tot 5.

Întrebarea este: Ce face minus minus pentru un vector?

Va aplica acel minus minus pentru fiecare element al vectorului.

Întrebarea este: Ce reprezintă minus minus True?

Minus Minus True este tot True, dar, pentru că s-a aplicat o operație matematică asupra lui True, se transformă în numarul 1. Cu alte cuvinte acei minus minus, puși în fața unui vector, vor transforma valorile True în 1 și valorile False în 0.

Iată, în cazul nostru, dacă selectăm formula și apăsăm tasta F9, vedem clar din ce este format acest vector. Va aparea o listă de 1 și 0, adica 1 pentru liniile unde valorile de pe linie sunt indentice și 0 pentru liniile unde acele valori sunt diferite.

Ce mai avem de făcut?

Să adunăm aceste valori de 1 și 0 (cu functia SUM).

Și rezultatul va fi: câte linii au valorile identice pe cele doua coloane. Aveți mare grijă când lucrați cu vectori, să NU apasam Enter, ci CTRL + Shift + Enter. O sa scriem aici: Cate randuri au SalesRep 1 egal SalesRep 2

Vă rog să vă uitați cu atenție și la această formulă. Iată ce întoarce Row de Indirect de 1 pana la 10. Apăs tasta F9 pentru a vedea mai bine: întoarce un vector de numere de la 2 la 10 (2, 3, 4, 5…pana la 10)

Apoi facem un Vlookup în care coloana de unde dorim rezultatul NU este un simplu numar ci un vector de Numere. Cu alte cuvinte, Vlookup va întoarce în acest caz încasarea de pe fiecare coloană indicată de vector pentru Filiala cautată. Adica, pentru Cluj, o să avem toate numerele de pe rândul său (corespunzatoare tuturor orelor). Dacă apăsăm tasta F9 pentru toata formula Vlookup, iată cum se vad toate acele valori corepunzatoare pentru Cluj. Evident că apoi funcția SUM adună toate aceste valori.

Dacă schimbăm și, in loc de Cluj punem Brașov, iată cum avem suma de la Brașov.

Dacă scriem București, avem suma pentru București.

Iată cum am reușit nu numai să gasesc rândul dorit cu Vlookup, dar cu ajutorul vectorilor, să și adun toate celulele de pe rândul găsit.

Array formulas sau, pe românește, tehnica vectorilor, este foarte puternică și necesită să aveți un nivel avansat spre expert, dacă doriți să o folosiți la adevărata sa putere.

Nu uitati că, atunci când lucrați cu vectori, să apăsați la finalul formulei Ctrl + Shift + Enter și nu un simplu Enter.

8. Formatarea condiționată avansată

În acest tabel ne propunem să colorăm cu rosu acele filiale la care incasarea de la Ora 5 este mai mica decat cea de la Ora 9.

Cu alte cuvinte, dorim să scoatem în evidență acele filiale care termina ziua mai prost decat au început-o.

De pildă, în cazul Iași, incasarile de la Ora 5 sunt mai mari decat cele de la Ora 9, asta înseamnă că NU dorim colorarea numelui de filiala. Însă, in cazul Clujului, încasarea de la Ora 5 este mai mica decat încasarea de la Ora 9, în acest caz dorim colorarea numelui Cluj cu rosu. Și tot așa pentru toate filialele.

Selectăm toate numele de filiala, apoi, din formatarea conditionata alegem New Rule și apoi Use Formula …

Trebuie să scriem o formulă care ia decizia de colorare după urmatoarea regulă:

Scriem formula doar în contextul celulei active, în cazul nostru, doar pentru Iași. Mai întâi, alegem culoarea rosu pentru a scapa de o grijă. Spuneam că trebuie să scriem o formulă care să întoarcă TRUE sau FALSE. True înseamnă că DA, dorim colorarea celulei, iar False că ramane celula necolorată.

În cazul nostru, când dorim să apară numele filialei Iasi cu rosu? Când Ora sa 5 (adica J2) este mai mică decât Ora sa 9 (adica celula B2). Asta este tot!

De exemplu, în cazul nostru, expresia J2<B2 o să întoarcă False pentru că numărul 11685 este mai mare decât 10889, cu alte cuvinte Iasi va ramane necolorat. Însă când o să apăsăm Enter, Excel va aplica formula pentru fiecare celulă de filială selectată. Pentru Cluj, o să aplice formula J3<B3 care întoarce True, cu alte cuvinte, Cluj va aparea cu rosu.

Apăsăm OK și observăm că doar Clujul apare cu rosu, însă, dacă ne apucăm să modificăm valorile din tabel, de exemplu, să facem valoarea de la Ora 5 din Constanta mai mica decat valoarea de la Ora 9, observați că, în mod automat, numele Constanta apare scos în evidență.

Formatarea condiționată avansată (bazată pe formule) este o tehnică foarte puternică, necesită ceva efort de a o întelege și a o aplica, însă merită!

9. Funcțiile Match și Index

Funcțiile Match și Index înlocuiesc cu succes funcția Vlookup pentru regăsirea datelor în Excel.

Avem numai avantaje dacă le folosim ca alternativă la celelalte funcții de tip Lookup.

Le vom lua pe rând, de pildă, având o filială (Brașov) care trebuie cautată pe verticală și o anumită Oră (12) care trebuie cautată pe orizontală, ne interesează valoarea aflată la intersecția filialei și a orei. Adică, în cazul nostru, acest 11356.

Dacă am ști numarul de linie și de coloană, îi putem da tabelul și ne interesează, ca la șah, valoarea aflată la intersecția liniei 4 și a colonei 5: 1,2,3,4 și coloana 1,2,3,4,5

Problema este că nu știm numarul de linie unde se află Brașov și nici numarul liniei de coloană unde se află ora 12. Pentru a afla aceste numere, le calculăm cu funcția Match.

Observați cum ajungem la acest 4, observați funcția Match, îi spunem ce anume cautăm, adică Brașov, coloana unde căutăm A1:A8 și cum căutăm (0 înseamnă că dorim o cautare exacta). În cazul nostru: 1,2,3,4, Brașov se afla pe a patra linie pe această coloană, iată, returnează 4.

Pe a câta coloană se află Ora 12? Ce căutăm, adica Ora 12, care este rândul, de data aceasta, unde se face căutarea și cum anume se caută (adică să caute exact). Deci, Ora 12 aici se află pe 1,2,3,4, pe a cincea coloană.

În loc de 4 scriem acest Match, în loc de 5 scriem acest Match și, practic, avem formula completă.

=INDEX(A1:J8,MATCH(F11,A1:A8,0),MATCH(G10,A1:J1,0))

După ce învățați să folosiți INDEX / MATCH, este posibil să nu vă mai întoarceți niciodată la funcția VLOOKUP.

Iată motivele mele ce stau la baza afirmației că „tandemul” INDEX / MATCH este mai bun decât funcția VLOOKUP:

A. INDEX / MATCH pot privi spre stânga (precum și spre dreapta) valoarea cautata.

Dacă avem o valoare situată în stânga valorii de cautare, nu o putem găsi prea ușor cu VLOOKUP.

După cum ați văzut, funcțiile INDEX / MATCH, pe de altă parte, sunt facute pentru căutări în orice direcție (sus/ jos sau stanga/dreapta). Cu funcția Vlookup NU putem să găsim în acest nou tabel valoarea căutată deoarece coloana unde se caută (Filiala) se află în dreapta coloanelor de unde dorim valorile.

B. INDEX / MATCH sunt independente de pozițiile actuale ale coloanelor.

Dacă avem un set de date și căutăm cu VLOOKUP, știți că argumentul al treilea reprezintă numărul coloanei de pe care se dorește rezultatul.

Dar dacă ștergem una dintre coloanele de dinaintea coloanei de unde dorim rezultatul? În acest caz, formula VLOOKUP va returna valoarea de pe altă coloană.

Folosind INDEX / MATCH, în acest caz, este mult mai indicat, deoarece putem să aflăm dinamic numarul coloanei cu funcția MATCH cu căutarea pe orizontală.

Când utilizăm INDEX / MATCH, putem introduce / șterge coloane în siguranță în setul de date.

10. VBA/MACRO

Cel mai puternic instrument dintre toate pe care un utilizator de Excel trebuie să-l cunoască, este vorba despre tehnica MACRO.

Dacă nu suntem multumiți de facilitățile native implementate în Excel, putem să ne creăm propriile instrumente scriind cod programatic în limbajul de programare Visual Basic for Applications.

De exemplu, în Excel nu există niciun shortcut de taste care să selecteze automat valorile cu date de pe o coloană dintr-un tabel.

Dacă avem un tabel cu foarte multe linii și dorim să selectăm valoarile de pe o coloana ce conține multe valori necompletate, nu putem să folosim singura combinatie de taste pe care o avem implicit la dispoziție: Ctrl + Shift + Săgeată jos deoarece această scurtatură se oprește de fiecare dată în prima celulă Blank.

Iată însă că am scris propriul cod VBA care reușește să treacă peste această limitare.

I-am spus SelectieColoana și iată cum arată codul.

Desi pare limba „chineza” la prima vedere eu vă spun că este învățabil cu puțin efort. Nu degeaba se numeste Visual Basic.

Sub SelectieColoana()
` Cum aflu numarul coloanei din currentregion unde se afla Activecell
Dim nrColCurenta As Integer
nrColCurenta = ActiveCell.Column – ActiveCell.CurrentRegion.Column + 1
Dim cr As Range
Set cr = ActiveCell.CurrentRegion
Dim nrLinii As Integer
nrLinii = cr.Rows.Count
Dim primaCelula As Range
Set primaCelula = cr.Cells(2, nrColCurenta)
Dim ultimaCelula As Range
Set ultimaCelula = cr.Cells(nrLinii, nrColCurenta)
Range(primaCelula, ultimaCelula).Select
` Am selectat toate celulele de la prima la ultima celula
End Sub

Iată că acestui cod i-am dat și un shortcut de taste: CTRL + Shift + C și, de fiecare dată cand sunt pe o anumită coloană și apăs combinația, se selectează automat toate valorile de pe acea coloană, indiferent câte celule necompletate conține acea coloană.

Codul pe care vi l-am arătat este relativ simplu însă necesită ceva efort până vă familiarizați cu el.

Însă cine se pricepe la scrierea unui astfel de cod se numește cu adevărat un Power User de Excel.