curs excel- functii de consultare - exemple pentru studenti (1)
Post on 17-Jan-2016
63 Views
Preview:
DESCRIPTION
TRANSCRIPT
Nomenclator Clienti
Cod Client Nume Client Adresa Localitate Judeţ Cod Fiscal
100 Client1 Str. Florilor nr.36 bl.8 Pitesti Arges 74655645
101 Client2 Str. Sincai nr.45, sc. B Bucuresti Bucureşti 13246873
102 Client7 Str. Averescu Brasov Braşov 96325814
104 Client4 Str. 1 Mai nr.36, bl A8 Ploiesti Prahova 75315946105 Client3 Str. 9 Mai Bucuresti Bucureşti 23187946
107 Client7 Str. Plopilor nr.1 Bârlad Vaslui 64821973
108 Client8 Str. Luterană nr. 3 Deva Hunedoara 57351984
Căutarea poziţiei liniei pe care se regăseşte o informație
104 ?
Căutarea poziţiei coloanei in care se regăseşte o informație
Cod Fiscal ?
Căutarea unei valori în raport de coordonatele relative ale unei cereri de interogare
104 ?
Cod Fiscal ?
Alegeți din lista derulantă codul clientului 64821973
Cod Client Nume Client Adresa Localitate Judeţ Cod Fiscal
100 Client1 Str. Florilor nr.36 bl.8 Pitesti Argeş 74655645101 Client2 Str. Sincai nr.45, sc. B Bucuresti Bucureşti 13246873102 Client7 Str. Averescu Brasov Braşov 19632581104 Client4 Str. 1 Mai nr.36, bl A8 Ploiesti Prahova 75315946
MATCH
=MATCH(C14;B3:B10)
=MATCH(104;B3:B10)
=MATCH(C17;B3:G3;0)
=MATCH("Cod Fiscal";B3:G3;0)
INDEX
Care este codul fiscal al clientului cu codul 104 ?
=MATCH(cheie_consultare; vector_coloană [;constantă_consultare]) è
=MATCH(cheie_consultare; vector_linie [;constantă_consultare]) è
=INDEX(tabel_consultare; număr_linie; număr_coloană)
105 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
FALSE6
3 Cod produs
Cod Denumire Preț Produs38 Produs1 100 € 9 Produs2 150 €
10 Produs3 200 € 11 Produs4 250 € 12 Produs5 300 € 13 Produs6 350 € 14 Produs7 400 € 15 Produs8 450 € 16 Produs9 500 €
çNumărul liniei
101 113
102 112 ?104 111 ?105 110 ?106 108107 107
108 106
LINIA
COLOANA
=MATCH(103;I4:I10;1)
se pozitioneaza pe valoarea imediat inferioara cheii (valorile cheii de consultare sunt dispuse in ordine crescatoare)
se pozitioneaza pe valoarea imediat superioara cheii (valorile cheii de consultare sunt dispuse in ordine descrescatoare)
=MATCH(109;J4:J10;-1)
=MATCH(109;J4:J10;0)
200 €
inferioara cheii (valorile cheii de consultare sunt dispuse in ordine crescatoare)
superioara cheii (valorile cheii de consultare sunt dispuse in ordine descrescatoare)
10 ?
Marca Nume Prenume
8 Popescu Marius 1,200 € 9 Albu Constanta 1,510 €
10 Popa Florin 2,100 €
11 Sava Maria 1,350 € 10 ?
12 Grecu Marcel 850 € 13 Iorga Marian 1,200 € 15 Manta Carmen 1,800 € 16 Nedelcu Maria 2,500 € 17 Vasiliu Marcela 3,000 €
=VLOOKUP(cheie_consultare;tabel_consultare;nr_coloană[;valoare_logică])
Salariul de încadrare =VLOOKUP(G4;$B$5:$D$14;2)
=VLOOKUP(G9;$B$5:$D$14;3)
(cheie_consultare;tabel_consultare;nr_coloană[;valoare_logică])
G4;$B$5:$D$14;2)
G9;$B$5:$D$14;3)
Marca Nume Prenume a) 14 Iorga Marian8 Popescu Marius 1,200 €
9 Albu Constanta 1,510 €
10 Popa Florin 2,100 € b) 14 ?11 Sava Maria 1,350 € 12 Grecu Marcel 850 € 13 Iorga Marian 1,200 € 15 Manta Carmen 1,800 € 16 Nedelcu Maria 2,500 € 17 Vasiliu Marcela 3,000 €
Salariul de încadrare
=VLOOKUP(G8;$B$5:$D$14;2;False)
=VLOOKUP(G8;$B$5:$D$14;3;False)
1,200 €
?
=VLOOKUP(G8;$B$5:$D$14;2;False)
=VLOOKUP(G8;$B$5:$D$14;3;False)
=IF(ISERROR(…..
Marca Nume Prenume 14 Nu există Marca 148 Popescu Marius 1,200 € Nu există Marca 149 Albu Constanta 1,510 €
=IFERROR(…..10 Popa Florin 2,100 € 11 Sava Maria 1,350 € 12 Grecu Marcel 850 € 13 Iorga Marian 1,200 € 15 Manta Carmen 1,800 € 16 Nedelcu Maria 2,500 € 17 Vasiliu Marcela 3,000 €
Salariul de încadrare
0
Câmpul B4:J6 = Tabel de consultare orizontala
Cod Produs 100 101 102 103 104 105
Denumire Produs produs1 produs2 produs3 produs4 produs5 produs6
Preţ unitar 13.5 19 34 125 80 57.5
Cod Produs 102
Denumire Produs ? =HLOOKUP(B10;$B$4:$J$6;2)
Preţ Unitar ? =HLOOKUP(B10;$B$4:$J$6;3)
Cod Produs 108
Denumire Produs ? =HLOOKUP(B15;$B$4:$J$6;2)
Preţ Unitar ? =HLOOKUP(B15;$B$4:$J$6;3)
Cod Produs 108
Denumire Produs ? =HLOOKUP(B20;$B$4:$J$6;2;FALSE)
Preţ Unitar ? =HLOOKUP(B20;$B$4:$J$6;3;FALSE)
=HLOOKUP(cheie_consultare; tabel_consultare; nr_linie [;valoare_logică])
Cazul 1) Cheia de consultare (Cod Produs 102) este plasată în prima linie a tabelului de consultare orizontală
Cazul 2) Cheia de consultare (Cod Produs 107) nu există în prima linie a tabelului de consultare şi se returnează cea mai apropiată valoare a cheii de consultare
Cazul 3) Cheia de consultare (Cod Produs 107) nu există în prima linie a tabelului de consultare şi se returnează un mesaj de eroare de tip "Not Available" (nedisponibil)
Cazul 4) Test de existenţă a cheii de consultare în tabelul de consultare orizontală
=IF(ISERROR(HLOOKUP(B25;$B$4:$J$6;2;False));"Cod incorect!";HLOOKUP(B25;$B$4:$J$6;2;False))
Cod Produs 108
Denumire Produs ?
Preţ Unitar ?
=IF(ISERROR(HLOOKUP(B25;$B$4:$J$6;2;False));0; HLOOKUP(B25;$B$4:$J$6;2;False))
=IF(ISERROR(HLOOKUP(B25;$B$4:$J$6;2;False));"Cod incorect!";HLOOKUP(B25;$B$4:$J$6;2;False))
=IFERROR(HLOOKUP(B25;$B$4:$J$6;2;False);"Cod incorect!")
Câmpul B4:J6 = Tabel de consultare orizontala
109 110 114
produs7 produs8 produs9
34 45 60
=HLOOKUP(B10;$B$4:$J$6;2)
=HLOOKUP(B10;$B$4:$J$6;3)
=HLOOKUP(B15;$B$4:$J$6;2)
=HLOOKUP(B15;$B$4:$J$6;3)
=HLOOKUP(B20;$B$4:$J$6;2;FALSE)
=HLOOKUP(B20;$B$4:$J$6;3;FALSE)
(cheie_consultare; tabel_consultare; nr_linie [;valoare_logică])
) este plasată în prima linie a tabelului de consultare orizontală
) nu există în prima linie a tabelului de consultare şi se returnează cea mai apropiată valoare a cheii de consultare
) nu există în prima linie a tabelului de consultare şi se returnează un mesaj de eroare de tip "Not Available" (nedisponibil)
=IF(ISERROR(HLOOKUP(B25;$B$4:$J$6;2;False));"Cod incorect!";HLOOKUP(B25;$B$4:$J$6;2;False))
=IF(ISERROR(HLOOKUP(B25;$B$4:$J$6;2;False));0; HLOOKUP(B25;$B$4:$J$6;2;False))
=IF(ISERROR(HLOOKUP(B25;$B$4:$J$6;2;False));"Cod incorect!";HLOOKUP(B25;$B$4:$J$6;2;False))
=IFERROR(HLOOKUP(B25;$B$4:$J$6;2;False);"Cod incorect!")
Data cotaţiei BNR Curs Euro Data cotaţiei BNR
Tuesday 11 January 2011 4.1420 lei/Euro 1/23/20111 Wednesday 12 January 2011 4.1430 lei/Euro 1/17/20112 Thursday 13 January 2011 4.1480 lei/Euro3 Friday 14 January 2011 4.1315 lei/Euro
4 Monday 17 January 2011 4.1335 lei/Euro5 Tuesday 18 January 2011 4.1390 lei/Euro6 Wednesday 19 January 2011 4.1500 lei/Euro7 Thursday 20 January 2011 4.1750 lei/Euro
8 Friday 21 January 2011 4.1800 lei/Euro9 Monday 24 January 2011 4.2005 lei/Euro
10 Tuesday 25 January 2011 4.2010 lei/Euro11 Wednesday 26 January 2011 4.1980 lei/Euro12 Thursday 27 January 2011 4.1780 lei/Euro13 Friday 28 January 2011 4.1600 lei/Euro14 Monday 31 January 2011 4.2310 lei/Euro15 Tuesday 01 February 2011 4.2080 lei/Euro16 Wednesday 02 February 2011 4.2230 lei/Euro17 Thursday 03 February 2011 4.2230 lei/Euro18 Friday 04 February 2011 4.2230 lei/Euro19 Monday 07 February 2011 4.2230 lei/Euro
Ziua de cotaţie
Ziua cotației este o zi lucrătoare
Tabelul de consultare declarat pe coordonatele B7:C27 a fost
denumit "Cotatii"
Ziua cotației este o zi de weekend
Curs Euro
4.1800 lei/Euro4.1335 lei/Euro
Ziua cotației este o zi lucrătoare
Câmpul E2:K22 ="Nomenclator"
ID produs
Data Factura Wed, 12 March 2014 1001 Produs1
1002 Produs2Curs Euro 4.546 lei 1003 Produs3
1004 Produs41005 Produs5
ID produs Cantitate livrată1006 Produs6
1002 1.3 t ? 1007 Produs7
1005 3.0 t ? 1008 Produs8
1018 2.5 t ? 1009 Produs9
1009 1.9 t ? 1010 Produs10
1011 Produs111012 Produs121013 Produs131014 Produs141015 Produs151016 Produs161017 Produs171018 Produs181019 Produs191020 Produs20
110.465
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)
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)
1998 1999 2000 2003 2010 2011
1 0.150 0.168 0.186 0.240 0.866 0.984
2 0.100 0.125 0.150 0.225 0.750 0.925
3 0.080 0.100 0.120 0.180 0.620 0.940
4 0.075 0.870 0.665 0.050 0.862 0.941
5 0.100 0.160 0.220 0.400 0.820 0.880
6 0.050 0.110 0.170 0.350 0.770 0.830
7 0.200 0.220 0.240 0.300 0.840 0.960
8 0.085 0.114 0.143 0.230 0.833 0.962
9 0.427 0.450 0.475 0.550 0.725 0.850
25479 ? ? ?
?
Câmpul B2:O11 = "Indici"Categorie Mijloc
Fix
Număr de inventar
Valoare de inventar
Coeficient de
reevaluare
Valoare reevaluată
2 5479
5478 AAA 2/12/1998 2,523.70 lei
5479 BBB 2/18/2010 12,547.83 lei5480 CCC 9/3/2000 3,550.00 lei
5481 DDD 1/25/1999 3,695.00 lei
5482 EEE 6/6/2006 1,754.01 lei
5483 FFF 11/8/2000 6,500.00 lei
5484 GGG 4/12/2000 2,765.82 lei
5485 HHH 6/14/2007 19,469.52 lei
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 2/21/2011 6,500.00 lei
Câmpul Q2:U15 ="Imobilizari"Număr ordine
Denumire mijloc fix
Data punerii în funcţiune
Valoare de inventar
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 Denumire Produs 3
Cantitate ? ? ?
Clienti → Produse ¯
=VLOOKUP($D$15;$A$3:$F$12;MATCH($B$15;$A$3:$F$12;0);FALSE)
=HLOOKUP($B$15;$B$3:$F$12;MATCH($D$15;$A$3:$A$12;0);FALSE)
=INDEX($A$3:$F$12;MATCH($D$15;$A$3:$A$12;0);MATCH($B$15;$A$3:$F$3;0))
Nume Prenume Sucursala Departament
POPESCU Viorel Dan Doctor Felix Asigurari Apr-2007 16 ani ? MIHAI Carmen Ştefan cel Mare Creditare Sep-1980 42 ani ? POPA Florin Ştefan cel Mare Asigurari Jun-1998 24 ani ? ALBU Constanta Doctor Felix Oficiul juridic Feb-1970 53 ani ? MANESCU Andreea Ştefan cel Mare Corporaţii Apr-2006 17 ani ? VISAN Alexandra Plevnei Oficiul juridic Nov-1985 37 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 800 € 1,070 € 2,080 € 2,200 € 2,500 € 2,900 €
2,200 €
2,200 €
Data angajării
Vechimea (ani)
Salariu încadrare
Vechime → ¯
Departamente
Marca Nume Prenume
8 Popescu Marius 1,200 €
9 Albu Constanta 1,510 € 10 Popa Florin 2100
10 Popa Florin 2,100 €
11 Cucuiat Mihaela 1,350 €
12 Greus Marcel 850 €
13 Iorga Marian 1,200 €
15 Manta Carmen 1,800 €
16 Nedelcu Maria 2,500 €
17 Vasiliu Marcela 3,000 €
Cod Produs 100 101 102 103 104Denumire produs1 produs2 produs3 produs4 produs5Preţ unitar $14 $19 $34 $125 $80
102 produs3 $34
=LOOKUP(cheie_consultare; vector_cheie_consultare; vector_rezultat)
Salariul de încadrare
=LOOKUP(F7;B6:B14;C6:C14)
=LOOKUP(F7;B6:B14;D6:D14)
=LOOKUP(B20;D16:J16;D17:J17)
=LOOKUP(B20;D16:J16;D18:J18)
105 109produs6 produs7
$58 $34
(cheie_consultare; vector_cheie_consultare; vector_rezultat)
=LOOKUP(F7;B6:B14;C6:C14)
=LOOKUP(F7;B6:B14;D6:D14)
=LOOKUP(B20;D16:J16;D18:J18)
Curs Euro 4.207 lei
Destinaţia Braşov
Denumire produs
Produs1 1.2 t ?
Produs2 1.4 t ?Produs3 2.9 t ?
Cantitate transport 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 €
Cantitate livrată
Cheltuieli transp.
Câmpul B12:F33 ="Tarife"
=IF(ISERROR(HLOOKUP($B$4;Tarife;MATCH(B7;Cantitati_transportate;0);FALSE));0;HLOOKUP($B$4;Tarife;MATCH(B7;Cantitati_transportate;0)))*$B$2
=IF(ISERROR(VLOOKUP(B8;A12:F33;MATCH($B$4;B12:F12;0)+1;FALSE));0;VLOOKUP(B8;A12:F33;MATCH($B$4;B12:F12;0)+1))*$B$2
=INDEX($A$12:$F$33;MATCH(B9;Cantitati_transportate;0);MATCH($B$4;A12:F12;0))*$B$2
=IF(ISERROR(HLOOKUP($B$4;Tarife;MATCH(B7;Cantitati_transportate;0);FALSE));0;HLOOKUP($B$4;Tarife;MATCH(B7;Cantitati_transportate;0)))*$B$2
=IF(ISERROR(VLOOKUP(B8;A12:F33;MATCH($B$4;B12:F12;0)+1;FALSE));0;VLOOKUP(B8;A12:F33;MATCH($B$4;B12:F12;0)+1))*$B$2
=INDEX($A$12:$F$33;MATCH(B9;Cantitati_transportate;0);MATCH($B$4;A12:F12;0))*$B$2
Posibilităţi de referire a celulei B4
$B$4
$B$4
B$4
$B4
B4
=ADDRESS(nr_linie; nr_coloană [;indicativ_adresă])
=ADDRESS(4;2[;1]) ------------------------------>
=ADDRESS(ROW(B4);COLUMN(B4)) ------->
=ADDRESS(4;2;2) -------------------------------->
=ADDRESS(4;2;3) -------------------------------->
=ADDRESS(4;2;4) -------------------------------->
Formula de calcul Descrierea funcţiei de consultare
2 returnează linia corespunzătoare celulei curente
12 returnează linia corespunzătoare celulei precizate ca argument
7 returnează prima linie a câmpului de celule precizat ca argument
2 returnează coloana corespunzătoare celulei curente
4 returnează coloana corespunzătoare celulei precizate ca argument
8 returnează prima coloană a câmpului de celule precizat ca argument
7 returnează numărul total de linii din câmpul de celule precizat ca argument
4 returnează numărul total de coloane din câmpul de celule precizat ca argument
Rezultatul formulei
=ROW()
=ROW(D12)
=ROW(A7:A9)
=COLUMN()
=COLUMN(D1)
=COLUMN(H1:J2)
=ROWS(L1:L7)
=COLUMNS(I1:L8)
=ROW([referinţă_celulară])
=COLUMN([referinţă_celulară])
=ROWS(câmp_celule)
=COLUMNS(câmp_celule)
Descrierea funcţiei de consultare
returnează linia corespunzătoare celulei precizate ca argument
returnează prima linie a câmpului de celule precizat ca argument
returnează coloana corespunzătoare celulei curente
returnează coloana corespunzătoare celulei precizate ca argument
returnează prima coloană a câmpului de celule precizat ca argument
returnează numărul total de linii din câmpul de celule precizat ca argument
returnează numărul total de coloane din câmpul de celule precizat ca argument
top related