VLOOKUP intre doua foi de calcul

In aceasta prezentare vom vedea cum se foloseste functia de cautare pe verticala (VLOOKUP) atunci cand tabela de consultare se afla in alta foaie de calcul decat cea in care aplicam functia .

Avem asadar in prima foaie de calcul (Sheet 1) o tabela cu incasarile relaizate de mai multi agenti de vanzare ai unei firme in mai multe tranzactii efectuate . Codurile fiecaruia dintre agenti sunt trecute in prima coloana a tabelei , iar in a treia coloana sumele incasate in diferite tranzactii efectuate de acestia . Folosind functia vlookup va trebui ca pe cea de-a doua coloana sa completam numele fiecarui agent in functie de cod .

In imaginea de mai jos avem o parte a celei de-a doua foi de calcul , in care se gaseste tabela de cautare
Asadar pentru a completa coloana Nume in tabela din Sheet1 , vom folosi functia vlookup cu urmatoarele argumente : vlookup(A2;'Tabela de cautare'!$A$1:$B$10;2) .

Intai aplicam functia pe prima linie din coloana Nume . Primul argument , A2 , este cheia de consultare sau valoarea dupa care se cauta (in cazul nostru codul inscris in prima linie a tabelei) . Al doilea argument este tabela de cautare , iar al treilea este numarul coloanei din tabela de cautare de pe care vreau sa mi se returneze valoarea corespunzatoare codului ce apare ca prim argument al functiei .

Cum vom introduce aceste argumente ?

Pentru a introduce primul argument selectam celula A2 .

Pentru a introduce al doilea argument mergem in cealalta foaie de calcul si selectam aria de celule A1:B10 , iar in functie , inaintea acestei adrese , va aparea numele foii de calcul in care se afla tabela de cautare , astfel : 'Tabela de cautare'!$A$1:$B$10 .

Pentru a introduce cel de-al treilea argument , adica numarul coloanei de pe care vreau sa-mi fie returnata valoarea corespunzatoare cheii de consultare , raman in cea de-a doua foaie de calcul si introduc numarul coloanei in functia vlookup care apare sub meniu , in bara functiei .

Acest lucuru este reprezentat in imaginea de mai jos , unde se observa ca fisierul este deschis pe cea de-a doua foaie de calcul (nu pe prima , acolo unde am introdus initial functia vlookup) . Iar bara in care apare functia inscrisa de noi in prima foaie de calcul este indicata printr-o sageata .
ATENTIE! Trebuie respectati obligatoriu acesti pasi pentru ca vlookup-ul sa nu returneze o eroare . Adica trebuie neaparat ca atunci cand introducem in functie numarul coloanei , sa ramanem in foaia de calcul in care avem tabela de cautare si nu sa ne intoarcem in foaia de calcul in care aplicam functia .

Dupa aplicarea functiei in primul rand al coloanei Nume si dupa incrementarea pe restul coloanei , tabela din prima foaie de calcul va arata astfel :

Videotutoriale

Incepand din aceasta saptamana cursurile de Excel si PowerPoint dispun de videotutoriale.  Aveti doua episoade gratis din aceste cursuri mai jos:

PowerPoint

Excel

Lucram in continuare si pentru realizarea de videoturiale pentru cursurile de Java Fundamentals, Java Advanced si C# Training.

Filtru avansat – problema intampinata la copierea rezultatulul in alta foaie de calcul .

Sa se interogheze tabela de mai jos printr-un filtru avansat a.i. sa se afiseze doar angajatii care au un salariu mai mare decat salariul mediu , iar rezultatul obinut sa fie copiat in alta foaie de calcul .

Din tabelul initial (A1:D33) vreau sa pastrez doar inregistrarile pentru angajatii care au un salariu mai mare decat salariul mediu . Pentru asta am nevoie de un filtru avansat in care criteriul de filtrare sa fie criteriul definit in celula G8 (salariul sa fie mai mare decat rezultatul functiei AVERAGE aplicata pe intreaga coloana “Salariul lunar”) .

SOLUTIA GRESITA :
Dupa stabilirea criteriului actionez Data – Filter – Advanced Filter , de pe bara de meniu din foaia de calcul in care se afla tabelul initial .Va aparea casuta de dialog pentru filtru avansat , in care voi stabili tabela pe care doresc sa o filtrez (List Range) , aria de celule in care se afla criteriul de filtrare (Crteria Rnage) si locul unde vreau sa copiez rezultaul filtrarii (Copy to) . Atentie! Pentru a activa rubrica Copy to , dintre cele doua optiuni dn partea de sus a casutei de dialog trebuie sa alegem Copy to another location .



- A1:D33 este adresa in care se afla tabela initala
- G7:G8 este adresa compusa din celula in care am stabilit criteriul (salariul sa fie mai mare decat salariul mediu) si celula aflata imediat deasupra acesteia (G7 ramane necompletata deoarece criteriul este stabilit pe baza unei formule)
- Sheet2!A1 este adresa in care vreau sa copiez rezultatul filtrarii .
Am spus la inceputul exercitiului ca vreau ca rezultatul filtrului avansat sa fie copiat in alta foaie de calcul (in cazul acesta Sheet2 , tabela initiala aflandu-se in Sheet1)

Pentru a inchea filtrarea se apasa butonul OK .
Insa in locul rezultatului asteptat vom primi din partea Excel-ului urmatoarea atentionare :
Asadar , ni se atrage atentia ca putem copia rezultatul filtrarii doar in foaia de calcul activa (adica acolo unde am actioant comanda Data – Filter - Advanced Filter)

SOLUTIA CORECTA :
Daca dorim copierea in alta foaie de calcul (in cazul acesta in Sheet2 , nu in Sheet 1 unde avem tabela) , va trebui sa actionam Data-Filter-Advanced Filter chiar in foaia in care dorim sa copiem , nu in cea in care avem tabela .
Pentru asta mergem in Sheet2 , iar casuta de dialog pentru filtru avansat va arata in felul urmator :

Microsoft Excel - Grafice combinate

Microsoft Excel are o istorie lunga, ce evolueaza si se schimba in mod constant.  Asadar, sper ca urmatoarele indicatii va vor fi de ajutor. Excel ofera o larga varietate de grafice, cum ar fi cele de tip: Line, Column, Area, Bar, Scatter sau Pie. Poti combina mai multe tipuri de diagrame pe acelasi grafic prin atribuirea acestora unor serii de date diferite. Acestea sunt graficele combinate, sau “Combo Charts”.

Iata cum cream grafice combinate. In Chart Wizard, cand alegi tab-ul Custom Types, ai doua posibilitati de alegere a unui grafic combinat: Column – Area sau Line – Column. Sa nu credeti ca acestea sunt singurele tipuri de grafice combinate ce se pot crea. De fapt, crearea de grafice combinate este foarte flexibila. Cheia este sa intelegi cum funtioneaza comenzile Chart – Chart Type. Cand selectezi aceasta comanda, ai doua posibilitati: daca selectezi o serie de date, atunci tipul graficul pe care-l alegi se aplica doar la seria selectata, iar daca selectezi totul , tipul graficului se aplica tuturor seriilor  din grafic. Insa prima varianta iti va asigura o flexibilitate mai mare.

De exemplu, sa presupunem ca vrei sa creezi un grafic sa contina o diagrame Line, Column si Area. Incepe prin a crea un grafic tip Column. Apoi selecteaza o a doua serie si alege Chart – Chart Type. Selecteaza un grafic de tip Line pentru seria respectiva. Selecteaza si o a treia serie, repeta comanda anterioara, dar alege un grafic de tip Area. Rezultatul va fi un grafic combinat ce cuprinde trei tipuri diferite de diagrame.

Atentie, nu toate tipurile de diagrame pot fi folosite spre a crea un grafic combinat. De exemplu, Excel nu permite diagramelor 3-D sa fie folosite pentru grafice combinate.

Figura de mai jos ilustreaza, cu scop demonstrativ, un grafic ce contine cinci tipuri de diagrame. Nu cred sa existe vreo situatie care sa necesite un grafic atat de confuz :).

Iata un alt exemplu, mai aproape de realitate: previziunea intr-o firma. Aceasta apare sub forma de coloane, in timp ce valorile actuale corespund unei linii. 

Daca valorile seriilor reprezentate variaza foarte mult,  sau graficul contine tipuri diferite de date, atunci va fi nevoie sa folosesti o axa Y secundara. Scala axei secundare reflecta valorile seriei asociate. De exemplu: datele de mai jos sunt din intervale diferite, au unitati de masura diferite (moneda si procente)si nu pot fi reprezentate  pe aceeasi axa.

Pentru a reprezenta cele doua serii pe axe diferite, trebuie mai intai sa le reprezinti pe acceasi axa. Apoi selecteaza seria ce trebuie reprezentata pe axa secundara si alege Format Data Series din meniul shortcut. Din tab-ul Axis alege optiunea Secondary Axis. Graficul va avea o axa secundara pe partea dreapta a graficului.

Scenarii in Microsoft Excel

Statistica? Previziuni? Interpretari? Posibilitati multiple..  Intr-un cuvant – scenarii. Aceasta este o functie utila si importanta a Microsoft Excel folosita in analizele “what if”, deoarece poti schimba intregul output / rezultat al calculului doar prin reeditarea unei singure celule. Ai posibilitatea de a salva diferite scenarii ce te vor ajuta sa iei decizii legate de cele mai bune valori pentru calculele tale. Prin invocarea unor scenarii, le poti demonstra clientilor sau managerilor impactul diferitelor evenimente sau posibilitati. Se pot de asemenea crea scenarii ce implica modificarea valorilor din multiple celule.

Vizualizarea scenariilor te ajuta in a vedea ce impact are cresterea sau descresterea valorilor din calculele tale. Un dezavantaj al acestui proces este acela ca nu este intotdeauna usor sa restaurezi datele initiale, mai ales la modificari multiple ale celulelor. Adaugarea de scenarii workbook-ului tau va modifica datele de la care porneste, asa ce este necesar o salvare a datelor initiale. 

Poti sa aplici orice scenariu oricand, iar celulele corespunzatoare se vor modifica spre valorile necesare.

Pentru a crea un scenariu, deschide aplicatia Excel. Intr-un worksheet nou, selectezi Tools – Scenarios pentru a afisa casuta Scenario Manager.  Initial, aceasta casuta va fi goala, iar pentru a adauga un scenariu se apasa Add.

Scenario name – numele scenariului;

Changing cells – celulele pentru care vrei sa vezi schimbarile previzionate. Poti specifica mai multe celule, prin separarea prin “,”;

Comment – permite descrierea tipului de scenariu;

Prevent changes – impiedica user-ul sa modifice ulterior scenariul. Acesta va avea efect numai daca woksheet-ul este protejat.

Hide – impiedica user-ul  sa vada scenariul atunci cand workbook-ul este deschis. Acesta va avea efect numai daca woksheet-ul este protejat.

Dupa ce selectezi Ok, casuta de dialog Scenario Values va aparea. Acum ai posibilitatea sa atribui noi valori celulelor din casuta Changing Cells. Aceste valori vor fi folosite in scenariul selectat.

Daca vrei sa creezi un nou scenariu, apasa Add din nou, si urmeaza pasii de mai sus. Cand ai terminat de creat toate scenariile dorite, alege Close din casuta Scenario Manager. 

Este posibila compararea rezultatelor tuturor scenariilor, prin crearea unui summary report. Acesta se creaza prin selectarea butonului Summary din casuta de dialog Scenario manager.

Acesta va afisa casuta Scenario Summary, avand astfel posibilitatea de a alege dintre cele doua tipuri de summary.

Scenario summary – creaza un tabel normal, ce este de folos atunci cand aceleasi celule sunt folosite pentru a crea scenarii diferite.

Scenario Pivot table report – creaza un tabel pivot, ce estede folos cand sunt folosite celule diferite pentru a crea scenarii.

Poti vizualiza scenariul creat, selectandu-l din casuta Scenario Manager si apasand Show.

Poti sa adaugi un Scenario dialog pe toolbar, prin click dreapta pe toolbar si selectarea tab-ului Customize – Commands.  Din categoria Tools, selecteaza Scenario listbox si drag and drop pe toolbar. Inchide Customize dialog. Acum poti vedea un scenariu la alegere, prin selectarea acestuia din Scenario list.

Pentru a edita un scenariu, alege Tools, apoi Scenario. Selecteaza scenariul ce va fi modificat si apasa butonul Edit, apoi Ok. Schimba valorile dorite si apasa Ok  pentru a salva schimbarile. Pentru a vizualiza rezultatele, alege scenariul din lista, si apasa Show.