hfgh

Situaţia stocurilor de mărfuri (produse) pe depozite (în buc.)Produse Depozit 1 Depozit 2 Depozit 3

Produs A 450 12 0Produs B 25 78 145Produs C 30 20 8Produs D 1400 300 20

Exemple de utilizareNumărul coloanei / liniei ce corespunde adresei de celulă C3

Numărul primei coloane / linii ce corespunde câmpului B3:B6

Numărul de coloane / linii ce corespunde câmpului B3:D6

Numarul de campuri aferenre tabelului de produse B3:D20

10 ?

Marca Nume Prenume

8 Popescu Marius 1,200 € 9 Albu Constanta 1,510 €

10 Popa Florin 2,100 € 11 Cucuiat Mihaela 1,350 € 12 Greus Marcel 850 € 13 Iorga Marian 1,200 € 14 Manta Carmen 1,800 € 15 Nedelcu Maria 2,500 €

16 Vasiliu Marcela 3,000 €

Cheia de consultare

(Marca) căutată în prima coloană a

tabelului de consultare

Valoare returnată din coloana 2, aferentă domeniului "Nume

Prenume"

Salariul incadrare

Coloana 2Coloana 3

G4

VLOOKUP 1: Se doreşte ca la tastarea unui cod de identificare (MARCA), existent într-un tablou să se recupereze automat anumite elemente cu care respectivul cod se găseşte în dependenţă funcţională (NUME si PRENUME; SALARIUL de INCADRARE).

H4


?

Valoare returnată din coloana 3, aferentă

domeniului "Salariul de incadrare"

I4


Data cotaţiei BNR Curs Euro Data cotaţiei BNR

0 1/12/2012 4.14 1/13/20121 1/13/2012 4.14 1/14/20122 1/14/2012 4.153 1/15/2012 4.134 1/16/2012 4.135 1/17/2012 4.146 1/18/2012 4.157 1/19/2012 4.188 1/20/2012 4.189 1/21/2012 4.20

10 1/22/2012 4.2011 1/23/2012 4.2012 1/24/2012 4.1813 1/25/2012 4.1614 1/26/2012 4.2315 1/27/2012 4.2216 1/28/2012 4.2217 1/29/2012 4.2318 1/30/2012 4.2419 1/31/2012 4.2420 2/1/2012 4.2521 2/2/2012 4.2622 2/3/2012 4.2623 2/4/2012 4.2724 2/5/2012 4.2825 2/6/2012 4.2926 2/7/2012 4.2927 2/8/2012 4.3028 2/9/2012 4.3129 2/10/2012 4.3130 2/11/2012 4.3231 2/12/2012 4.3332 2/13/2012 4.3333 2/14/2012 4.3434 2/15/2012 4.3535 2/16/2012 4.3536 2/17/2012 4.3637 2/18/2012 4.3738 2/19/2012 4.3739 2/20/2012 4.3840 2/21/2012 4.3941 2/22/2012 4.3942 2/23/2012 4.4043 2/24/2012 4.4144 2/25/2012 4.4145 2/26/2012 4.42

Ziua de cotaţie

Formatare personalizata de tipul Vineri 13 Ian 2012 (plaja B8:B72).Daca data cotatiei BNR introdusa pe plaja E8:E9 se regaseste in coloana B8:B72 sa se formateze conditional cu font alb pe fundal albastru.

Formatare conditionala de tipul 4,142 lei/Euro

Cheia de consultare

46 2/27/2012 4.4347 2/28/2012 4.4348 2/29/2012 4.4449 3/1/2012 4.4550 3/2/2012 4.4651 3/3/2012 4.4652 3/4/2012 4.4753 3/5/2012 4.4854 3/6/2012 4.4855 3/7/2012 4.4956 3/8/2012 4.5057 3/9/2012 4.5058 3/10/2012 4.5159 3/11/2012 4.5260 3/12/2012 4.5261 3/13/2012 4.5362 3/14/2012 4.5463 3/15/2012 4.5464 3/16/2012 4.5565 3/17/2012 4.5666 3/18/2012 4.5667 3/19/2012 4.5768 3/20/2012 4.5869 3/21/2012 4.5870 3/22/2012 4.5971 3/23/2012 4.6072 3/24/2012 4.60

Tabelul de consultare declarat pe coordonatele B7:C80 va fi

denumit "Cotatii"

Curs Euro recuperat

??

F8

VLOOKUP 2: Exemplu de utilizare a funcţiei VLOOKUP, prin care se doreşte a se recupera cursul valutar al monedei Euro în funcţie de o dată calendaristică tastată de utilizator (şi declarată în acest caz în celulele E8, E9 drept cheie de consultare).

F9

VLOOKUP 2: Exemplu de utilizare a funcţiei VLOOKUP, prin care se doreşte a se recupera cursul valutar al monedei Euro în funcţie de o dată calendaristică tastată de utilizator (şi declarată în acest caz în celulele E8, E9 drept cheie de consultare).

Câmpul E2:K22 ="Nomenclator"

Data Factura Mon, 17 April 2023 1001 Produs11002 Produs2

Curs Euro 4.380 lei 1003 Produs31004 Produs41005 Produs5

Cod produs Cantitate livrată1006 Produs6

1002 1.2 t ? 1007 Produs71005 3.0 t ? 1008 Produs81007 2.5 t ? 1009 Produs91004 1.9 t ? 1010 Produs10

1011 Produs111012 Produs121013 Produs131014 Produs141015 Produs151016 Produs161017 Produs171018 Produs181019 Produs191020 Produs20

Identificator produs

Denumire produs

Valoare factură (lei)

=IF(AND(Data_factura > Data_debut_promotie; Data_factura < Data_sfarsit_promotie; Cantitate_livrata > Cantitate_discount);

(Cantitate_livrata - Cantitate_discount) * Pret_unitar_€ * (1-Discount) *Curs_euro + Cantitate_discount * Pret_unitar_€ * Curs_euro;

Cant_livrata * Pret_unitar_€ * Curs_euro)

B3

Data facturii=Data curentă

C8

VLOOKUP 3: Un alt exemplu de utilizare a funcţiei de consultare verticală VLOOKUP ilustrează calcularea valorii fiecarui produs facturat prin extragerea dintr-un tabel de consultare intitulat „Nomenclator” şi declarat pe coordonatele E22:K22 a tuturor elementelor ce compun calculul valorii facturii, în funcţie de cantitatea livrată conform unei politici de acordare a reducerilor comerciale. Aceste discounturi sunt acordate numai în condiţiile în care cantitatea livrată depăşeşte un anumit barem (specificat într-un tabel de consultare) şi sunt operaţionale numai pentru cantităşile comandate în plus faţă de acel barem. Astfel, în tabelul A8:C12 se regăsesc următoarele categorii informaţionale: - „Cod produs” – cheia de consultare, în funcţie de valorile căreia se recuperează „Valoare factură”; - „Cantitate livrată” – parametru în funcţie de care se acordă reducerile comerciale, după următorul algoritm: „în cazul în care cantitatea livrată depăşeşte baremul cantităţii de la care se acordă discount, (existent în tabelul de consultare), se aplică un preţ diminuat cu un anumit procent, altfel, se aplică preţul din tabel” - „Valoare factură” – se calculează în funcţie de elementele din tabelul „Nomenclator”, numai în condiţiile în care data curentă (B3) se încadrează în intervalul de timp dintre „Data debut promoţie” şi „Dată sfârşit promoţie” din tabelul de consultare. Esenţa calculului valorii este dat de faptul că toate elementele ce compun valoarea se regăsesc în tabelul „Nomenclator”, calculul facându-se doar dacă se respectă perioada de promoţie, iar în cazul în care „Cantitatea livrată” depăşeşte limita cantităţii de la care se acordă discount, valoarea se calculează cu un preţ diminuat cu procentul de dicount, la care se adaugă preţul standard multiplicat cu cantitatea până la care se acordă reduceri comerciale.

Câmpul E2:K22 ="Nomenclator"

36.25 € - -

19.00 € 1.0 t 7% ### ###

37.50 € - -

75.00 € - -

17.50 € - -

22.50 € 1.3 t 15% ### ###

55.00 € 2.0 t 3% ### ###

15.00 € 1.7 t 10% ### ###

41.00 € - -

33.75 € - -

12.50 € - -

16.00 € 2.5 t 15% ### ###

29.25 € - -

30.00 € - -

55.00 € - -

19.50 € - -

32.50 € 1.3 t 18% ### ###

18.00 € 2.0 t 10% ### ###

20.25 € 1.0 t 6% ### ###

17.33 € - -

Preţ unitar (€)Cantitate discount

Discount (%)

Data debut promoţie

Data sfârşit promoţie

=IF(AND(Data_factura > Data_debut_promotie; Data_factura < Data_sfarsit_promotie; Cantitate_livrata > Cantitate_discount);

(Cantitate_livrata - Cantitate_discount) * Pret_unitar_€ * (1-Discount) *Curs_euro + Cantitate_discount * Pret_unitar_€ * Curs_euro;

Cant_livrata * Pret_unitar_€ * Curs_euro)

Nomenclator "Clienti"

Adresa Localitate Judeţ

100 Client1 Str. Florilor nr.36 bl.8 Pitesti Arges 4655645101 Client2 Str. Sincai nr.45, sc. B Bucuresti Bucureşti 13246873102 Client7 Str. Averescu Brasov Braşov 96325814104 Client4 Str. 1 Mai nr.36, bl A8 Ploiesti Prahova 75315946105 Client3 Str. 9 Mai Bucuresti Bucureşti 23187946107 Client7 Str. Plopilor nr.1 Bârlad Vaslui 64821973108 Client8 Str. Luterană nr. 3 Deva Hunedoara 57351984

Căutarea poziţiei liniei pe care se găseşte o valoare104 ?

Căutarea poziţiei coloanei pe care se găseşte o valoareCod Fiscal ?

Căutarea unei valori în raport cu coordonatele relative ale unei cereri de interogareCare este codul fiscal al clientului cu codul 104 ?

104 ?Cod Fiscal

Adresa Localitate Judeţ

100 Client1 Str. Florilor nr.36 bl.8 Pitesti Argeş 4655645101 Client2 Str. Sincai nr.45, sc. B Bucuresti Bucureşti 13246873102 Client7 Str. Averescu Brasov Braşov 9632581104 Client4 Str. 1 Mai nr.36, bl A8 Ploiesti Prahova 75315946105 Client3 Str. 9 Mai Bucuresti Bucureşti 23187946107 Client7 Str. Plopilor nr.1 Bârlad Vaslui 64821973108 Client8 Str. Luterană nr. 3 Deva Hunedoara 57351984

5 <===== Numărul liniei

Alegeti din lista derulanta

Cod Client

Nume Client

Cod Fiscal

MATCH

INDEX

Cod Client

Nume Client

Cod Fiscal

Combo Box

D23

INDEX si MATCH: Exemplu de utilizare a funcţiei INDEX prin care se recuperează o valoare şi anume codul fiscal al clientului 104. Această informaţie se regăseşte la intersecţia liniei 5 cu coloana 6 din tabloul de consultare. Cele două funcţii MATCH permit în acest context identificarea numărului de linie şi de coloană, iar funcţia INDEX extrage valoarea găsită la intersecţia celor două coordonate ale tabelului de consultare Clienţi.

Codul fiscal rezultat : 23187946

List Box

Adresa Localitate Judeţ Cod Fiscal

100 Client1 Str. Florilor nr.36 bl.8 Pitesti Arges 34655645101 Client2 Str. Sincai nr.45, sc. B Bucuresti Bucureşti 13246873102 Client7 Str. Averescu Brasov Braşov 96325814104 Client4 Str. 1 Mai nr.36, bl A8 Ploiesti Prahova 75315946105 Client3 Str. 9 Mai Bucuresti Bucureşti 23187946107 Client7 Str. Plopilor nr.1 Bârlad Vaslui 64821973108 Client8 Str. Luterană nr. 3 Deva Hunedoara 57351984

4

Cod Client

Nume Client

Să se formateze condițional codul fiscal ce corespunde unui cod client selectat dintr-o listă derulanta de tip Combo Box.

codul fiscal ce corespunde unui cod client selectat dintr-o listă derulanta de tip Combo Box.

Campul B4:J6 = Tabel_consultare_orizontala

Cod Produs 100 101 102 103 104Denumire Produs produs1 produs2 produs3 produs4 produs5Pret unitar 13.5 19 34 125 80

Sa se recupereze din tabelul de consultare Denumirea de produs si Pretul unitar

Cod Produs 102Denumire Produs ?

Pret Unitar ?

Campul B4:J6 = Tabel_consultare_orizontala

105 109 110 114produs6 produs7 produs8 produs9

57.5 34 45 60

Client 1 Client 2 Client 3 Client 4 Client 5

Produs 1 162 buc. 189 buc. 180 buc. 192 buc. 106 buc.Produs 2 114 buc. 104 buc. 18 buc. 20 buc. 124 buc.Produs 3 64 buc. 26 buc. 87 buc. 39 buc. 137 buc.Produs 4 57 buc. 123 buc. 51 buc. 200 buc. 74 buc.Produs 5 68 buc. 58 buc. 159 buc. 88 buc. 194 buc.Produs 6 99 buc. 103 buc. 115 buc. 154 buc. 105 buc.Produs 7 11 buc. 88 buc. 100 buc. 59 buc. 95 buc.Produs 8 36 buc. 7 buc. 193 buc. 7 buc. 70 buc.Produs 9 54 buc. 91 buc. 52 buc. 63 buc. 177 buc.

Nume Client Client 3 Den. Produs Produs 1

Cant. Produs ?

Nume Client → Denumire produs ¯

B17

Cantitate livrata produs: Procedura de consultare a unui tabel, prin precizarea elementelor informaţionale generice de identificare a clienţilor (pe prima linie) şi produselor (pe prima coloană), dimensiuni la intersecţia cărora se regăsesc cantităţile vândute (în bucăţi). Aceste elemente „cheie” se regăsesc precizate în celulele B15 şi D15 sub formă de liste derulante declarate prin comanda aferentă tabului Data > butonul Data Validation > opţiunea Data Validation > tabul Settings > secţiunea Allow > opţiunea List. Celula B17 conţine funcţia de consultare orizontală HLOOKUP, ce caută să identifice Clientul (B15) în prima linie a tabelului de consultare Cantitati (declarat pe coordonatele B3:F12). Numărul liniei de recuperat din funcţia HLOOKUP este returnat de identificarea poziţiei relative prin funcţia MATCH, a denumirii de produs în vectorul de consultare.

Nume Prenume Sucursala Departament

POPESCU Viorel Dan Doctor Felix Creditare Apr-2006 17 ani ? MIHAI Carmen Ştefan cel Mare Creditare Sep-1980 42 ani ? POPA Florin Ştefan cel Mare Corporaţii Jun-1999 23 ani ? ALBU Constanta Doctor Felix Creditare Feb-1970 53 ani ? MANESCU Andreea Ştefan cel Mare Corporaţii Apr-2006 17 ani ?

VISAN Alexandra Plevnei Oficiul juridic Nov-1975 47 ani ?

< 5 ani 5-10 ani 10-15 ani 15-20 ani 20-25 ani >=25 ani

0 ani 5 ani 10 ani 15 ani 20 ani 25 ani

Creditare 1,000 € 1,200 € 1,900 € 2,350 € 2,670 € 2,900 € Corporaţii 1,050 € 1,180 € 2,300 € 2,550 € 2,710 € 2,800 € Oficiul juridic 1,120 € 1,400 € 2,450 € 2,600 € 2,790 € 3,200 € Relaţii clienţi 1,100 € 1,180 € 2,500 € 2,670 € 3,030 € 3,350 €

Asigurari 1,000 € 1,070 € 2,080 € 2,200 € 2,500 € 2,900 €

Data angajării

Vechimea (ani)

Salariu încadrare

Vechime → ¯

Departamente

F3

Salariul de incadrare: Prin funcţia HLOOKUP se recupereaza salariul de încadrare declarat într-un tabel de consultare, în funcţie de precizarea vechimii şi a departamentului funcţional. Astfel, funcţia HLOOKUP caută să localizeze vechimea în prima linie a tabelului de consultare. Această primă linie conţine valori numerice formatate personalizat cu sufixul „ani”. Argumentul „numărul liniei de recuperat” este extras prin funcţia MATCH, care returnează valoarea poziţională a departamentului în vectorul de consultare.

Curs Euro 4.380 lei

Destinaţia Braşov

Produs1 1.2 t

Produs2 1.4 tProdus3 2.9 t

Braşov Bucureşti Ploieşti Predeal Sinaia

1.0 t 20.00 € 13.75 € 13.50 € 15.00 € 12.50 € 1.1 t 22.50 € 17.50 € 15.00 € 30.00 € 25.00 € 1.2 t 25.00 € 12.50 € 22.50 € 38.75 € 37.50 € 1.3 t 27.50 € 15.00 € 30.00 € 52.50 € 50.00 € 1.4 t 30.00 € 18.75 € 37.50 € 65.00 € 62.50 € 2.9 t 67.50 € 75.00 € 150.00 € 252.50 € 250.00 € 3.0 t 70.00 € 78.75 € 157.50 € 265.00 € 262.50 €

Denumire produs

Cantitate livrată

Cheltuieli transp.

Câmpul B12:F33 ="Tarife"

Cantitate transport

C6

Sa se extraga si sa se calculeze CHELTUIELILE DE TRANSPORT in lei, in functie de campurile Destinatia, Cantitate livrata si Curs Euro.

hfgh

Documents