aplicatia - functii de consultare

18
Nomenclator Clienti Adresa Judeţ 100 Client1 Str. Florilor nr. Pitesti Arges 4655645 101 Client2 Str. Sincai nr.45 Bucuresti Bucureşti 13246873 102 Client7 Str. Averescu Brasov Braşov 96325814 104 Client4 Str. 1 Mai nr.36, Ploiesti Prahova 75315946 105 Client3 Str. 9 Mai Bucuresti Bucureşti 23187946 107 Client7 Str. Plopilor nr. Bârlad Vaslui 64821973 108 Client8 Str. Luterană nr. Deva Hunedoara 57351984 Căutarea poziţiei liniei pe care se găseşte o valoare 104 ? Căutarea poziţiei coloanei pe care se găseşte o valoare Cod Fiscal ? Căutarea unei valori în raport cu coordonatele relative ale unei cereri de inter Care este codul fiscal al clientului cu codul 104 ? 104 ? Cod Fiscal câmp denumit Linie_Antet Cod Client Nume Client Localitat e Cod Fiscal câmp denumit Cod_Client M A T C H I N D E X câmp denumi Client

Upload: anca-preda

Post on 19-Jan-2016

60 views

Category:

Documents


8 download

DESCRIPTION

tao ase

TRANSCRIPT

Page 1: Aplicatia - Functii de Consultare

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

câmp denumit Linie_Antet

Cod Client

Nume Client

Cod Fiscal

câmp denumit Cod_Client

MATCH

INDEX

câmp denumitClienti

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.
Page 2: Aplicatia - Functii de Consultare

câmp denumitClienti

Page 3: Aplicatia - Functii de Consultare

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
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).
Page 4: Aplicatia - Functii de Consultare

?

Valoare returnată din coloana 3, aferentă

domeniului "Salariul de incadrare"

I4
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).
Page 5: Aplicatia - Functii de Consultare

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

0 1/12/2009 4.142 1/18/20091 1/13/2009 4.143 1/13/20092 1/14/2009 4.1483 1/15/2009 4.132

4 1/16/2009 4.134 <------- A se vedea formatarea conditionala5 1/19/2009 4.1396 1/20/2009 4.1507 1/21/2009 4.1758 1/22/2009 4.1809 1/23/2009 4.201

10 1/26/2009 4.20111 1/27/2009 4.19812 1/28/2009 4.17813 1/29/2009 4.16014 1/30/2009 4.23115 2/2/2009 4.21716 2/3/2009 4.22417 2/4/2009 4.23118 2/5/2009 4.23819 2/6/2009 4.24520 2/9/2009 4.25121 2/10/2009 4.25822 2/11/2009 4.26523 2/12/2009 4.27224 2/13/2009 4.27925 2/16/2009 4.28526 2/17/2009 4.29227 2/18/2009 4.29928 2/19/2009 4.30629 2/20/2009 4.31330 2/23/2009 4.31931 2/24/2009 4.32632 2/25/2009 4.33333 2/26/2009 4.34034 2/27/2009 4.34635 3/2/2009 4.35336 3/3/2009 4.36037 3/4/2009 4.36738 3/5/2009 4.37439 3/6/2009 4.38040 3/9/2009 4.38741 3/10/2009 4.39442 3/11/2009 4.40143 3/12/2009 4.40844 3/13/2009 4.41445 3/16/2009 4.421

Ziua de cotaţie

Format-Cells-Number-Custom: Type:dddd dd.mmmm.yyyy

Format-Cells-Number-Custom: Type: #.##0" lei/Euro"

Cheia de consultare

Page 6: Aplicatia - Functii de Consultare

46 3/17/2009 4.42847 3/18/2009 4.43548 3/19/2009 4.44249 3/20/2009 4.44850 3/23/2009 4.45551 3/24/2009 4.46252 3/25/2009 4.46953 3/26/2009 4.47654 3/27/2009 4.48255 3/30/2009 4.48956 3/31/2009 4.49657 4/1/2009 4.50358 4/2/2009 4.51059 4/3/2009 4.51660 4/6/2009 4.52361 4/7/2009 4.53062 4/8/2009 4.53763 4/9/2009 4.54464 4/10/2009 4.55065 4/13/2009 4.55766 4/14/2009 4.56467 4/15/2009 4.57168 4/16/2009 4.57869 4/17/2009 4.58470 4/20/2009 4.59171 3/16/2004 4.59872 4/22/2009 4.605

Tabelul de consultare declarat pe coordonatele B7:C80 va fi

denumit "Cotatii"

Page 7: Aplicatia - Functii de Consultare

Curs Euro recuperat

??

<------- A se vedea formatarea conditionala

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).
Page 8: Aplicatia - Functii de Consultare

Câmpul E2:K22 ="Nomenclator"

Data Factura Fri, 21 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.
Page 9: Aplicatia - Functii de Consultare

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)

Page 10: Aplicatia - Functii de Consultare

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 ?

Page 11: Aplicatia - Functii de Consultare

Campul B4:J6 = Tabel_consultare_orizontala

105 109 110 114produs6 produs7 produs8 produs9

57.5 34 45 60

Page 12: Aplicatia - Functii de Consultare

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.
Page 13: Aplicatia - Functii 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 aniCreditare 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.
Page 14: Aplicatia - Functii de Consultare

1995 1996 1997 2000 2001 2002 2003

1 0.150 0.168 0.186 0.240 0.258 0.276 0.294

2 0.100 0.125 0.150 0.225 0.250 0.275 0.303

3 0.080 0.100 0.120 0.180 0.200 0.220 0.240

4 0.075 0.870 0.665 0.050 0.845 0.640 0.435

5 0.100 0.160 0.220 0.400 0.460 0.520 0.580

6 0.050 0.110 0.170 0.350 0.410 0.470 0.530

7 0.200 0.220 0.240 0.300 0.320 0.340 0.360

8 0.085 0.114 0.143 0.230 0.259 0.288 0.317

9 0.427 0.450 0.475 0.550 0.575 0.600 0.625

25479 ? ? ?

45490 ? ? ?

75485 ? ? ?

15478 ? ? ?

Câmpul B2:O11 = "Indici"Categorie Mijloc Fix

Număr de inventar

Valoare de inventar

Coeficient de

reevaluare

Valoare reevaluată

1. Valoarea de inventar se recuperează din tabelul de consultare "Imobilizari", în funcţie de numărul de ordine

2. Coeficientul de reevaluare se extrage din tabelul "Indici", în funcţie de codul categoriei mijlocului fix (primul caracter al agregatului "şi de anul punerii în funcţiune (care se extrage din tabelul de consultare "Imobilizari", în funcţie de

3. Valoarea reevaluată se calculează aplicând coeficientul de reevaluare la valoarea de inventar, fiind rotunjită la un multiplu de rotunjire de 1 leu.Observaţie: Dacă anul de punere în funcţiune este anul curent, valoarea reevaluată va fi valoarea de inventar.

2 5479

B16
Valoare de inventar: Valoarea de inventar este extrasă în celula B17, prin funcţia VLOOKUP din ultimele patru caractere ale numărului de inventar, localizat tabelul „Imobilizări”, coloana a 4-a („Valoare de inventar”).
C16
Coeficientul de reevaluare: Exemplul prezentat porneşte de la un tabel (declarat pe coordonatele B2:O11 şi intitulat „Indici”) ce cuprinde anumiţi coeficienţi (subunitari) de reevaluare a mijloacelor fixe, pe fiecare an în parte (prima linie a tabelului B2:O2 conţine anii: 1995 – 2008) corespunzător fiecărei categorii (descrise pe coloana A3:A11). Categoriile mijloacelor fixe corespund rândurilor (liniilor), identificate în tabel de la 1 la 9. Tabelul de consultare Q2:U15 (intitulat „Imobilizări”) conţine elemente de identificare pentru fiecare mijloc fix („Denumire mijloc fix”, „Data punerii în funcţiune”, „Valoare de inventar”), dependente funcţional de un cod de identificare („Număr de ordine”). Aplicaţia extrage din tabelul de consultare „Indici” în celulele câmpului C17:C20 coeficientul de reevaluare, în funcţie de valorile codului compozit „Număr de inventar” (ce este format din categoria mijlocului fix şi „Numărul de ordine”. Funcţia HLOOKUP editată în celula C17 identifică anul punerii în funcţiune în prima linie a tabelului „Indici”, extrăgând coeficientul de reevaluare ce corespunde categoriei mijlocului fix. Anul punerii în funcţiune este extras cu funcţia YEAR din ultimele patru caractere ale numărului de inventar, localizat prin funcţia VLOOKUP în tabelul „Imobilizări”, coloana a 3-a („Data punerii în funcţiune”). Categoria mijlocului fix (al treilea argument al funcţiei HLOOKUP) este în fapt primul caracter din stânga al numărului de inventar. Decalarea cu o unitate a rândului ce conţine categoria este explicată prin faptul că prima linie este rezervată anilor, a 2-a linie corespunde primei categorii, etc.
D16
Valoarea reevaluata: Valoarea reevaluată, calculată în celula D17 înmulţeşte valoarea de inventar cu coeficientul de reevaluare, dacă anul de punere în funcţiune (extras din tabelul „Imobilizări”) este anul curent, altfel se menţine aceeaşi valoare de inventar.
Page 15: Aplicatia - Functii de Consultare

2007 2008

0.866 0.984 5478 AAA 2/12/1998 2,523.70 lei

0.750 0.925 5479 BBB 2/18/2007 12,547.83 lei0.620 0.940 5480 CCC 9/3/2000 3,550.00 lei

0.862 0.941 5481 DDD 1/25/1999 3,695.00 lei

0.820 0.880 5482 EEE 6/6/2006 1,754.01 lei

0.770 0.830 5483 FFF 11/8/2000 6,500.00 lei

0.840 0.960 5484 GGG 4/12/2000 2,765.82 lei

0.833 0.962 5485 HHH 6/14/2007 19,469.52 lei

0.725 0.850 5486 III 8/30/2001 4,785.00 lei5487 JJJ 2/4/2002 1,548.78 lei5488 KKK 8/6/1998 21,547.80 lei5489 LLL 3/17/2004 3,583.00 lei5490 MMM 3/21/2005 6,500.00 lei

Câmpul Q2:U15 ="Imobilizari"Număr ordine

Denumire mijloc fix

Data punerii în funcţiune

Valoare de inventar

numărul de ordine aferent agregatului "Număr de inventar"

se extrage din tabelul "Indici", în funcţie de codul categoriei mijlocului fix (primul caracter al agregatului "Număr de inventar")Imobilizari", în funcţie de numărul de ordine aferent agregatului "Număr de inventar")

valoarea de inventar, fiind rotunjită la un multiplu de rotunjire de 1 leu.: Dacă anul de punere în funcţiune este anul curent, valoarea reevaluată va fi valoarea de inventar.

Page 16: Aplicatia - Functii 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
Se calculeaza CHELTUIELILE DE TRANSPORT in functie de Destinatie, Cantitate livrata si Curs Euro
C7
Cheltuieli de transport 1: O primă abordare de recuperare şi calcul a cheltuielilor de transport, identifică prin HLOOKUP destinaţia în prima linie a tabelului de consultare „Tarife”, corespunzător numărului de linie returnat indirect ca valoare poziţională de funcţia MATCH. Această abordare este operaţională în condiţiile unui test de existenţă a destinaţiei în tabelul de consultare şi a unor cantităţi valide pentru care există tarife de transport.
C8
Cheltuieli de transport 2: A doua abordare constă în localizarea cantităţii livrate într-un tabel de consultare extins (şi declarat pe coordonatele A12:F33), corespunzător unui număr de coloană ce corespunde destinaţiei transportului. Acest ultim argument este specificat indirect sub forma unei valori poziţionale date prin localizarea cu ajutorul funcţiei MATCH a destinaţiei.
C9
Cheltuieli de transport 3: A treia abordare utilizează pentru extragerea tarifului de transport funcţia INDEX care operează pe coordonatele tabelului de consultare A12:F33. Numărul de linie este returnat de funcţia MATCH, prin furnizarea valorii poziţionale a cantităţii livrate în campul „Cantitati_transportate” A12:A33. Numărul de coloană este specificat indirect sub forma unei valori poziţionale date prin localizarea cu ajutorul funcţiei MATCH a destinaţiei în tabelul de consultare A12:F12