aplicatia 4 excel - functii de consultare (1)
TRANSCRIPT
Cod client 104
Adresa Localitate Judeţ
100 Client1 Str. Florilor nr.36 bl.8 Pitesti Arges
101 Client2 Str. Sincai nr.45, sc. B Bucuresti Bucureşti
102 Client7 Str. Averescu Brasov Braşov
104 Client4 Str. 1 Mai nr.36, bl A8 Ploiesti Prahova
105 Client3 Str. 9 Mai Bucuresti Bucureşti
107 Client7 Str. Plopilor nr.1 Bârlad Vaslui
108 Client8 Str. Luterană nr. 3 Deva Hunedoara
Cod Client
Nume Client
Să se formateze condițional codul fiscal (G4:G10) ce corespunde unui cod client selectat din lista derulantă definită în celula E2.
Cod Fiscal
34655645
13246873
96325814
75315946
23187946
64821973
57351984
) ce corespunde unui cod client selectat din lista derulantă definită în celula E2.
Cod ISBN Titlu carte
973-6842-67-3 Compania de librării Bucureşti
973-6842-67-4 Compania de librării Bucureşti
973-6842-67-3 Librăria "Central" Sibiu
973-6842-67-5 Vox 2000 Bucureşti
973-6842-67-6 Compania de librării Braşov
973-6842-67-6 Librăria "Central" Cluj
973-6842-67-5 RolCris Sibiu
973-6842-67-4 RolCris Bucureşti
973-6842-67-6 RolCris Bucureşti
973-6842-67-7 Vox 2000 Cluj
973-6842-67-7 Compania de librării Bucureşti
973-6842-67-6 Compania de librării Bucureşti
973-6842-67-7 Compania de librării Braşov
973-6842-67-8 RolCris Bucureşti
973-6842-67-7 RolCris Bucureşti
973-6842-67-3 Librăria "Central" Sibiu
973-6842-67-3 Compania de librării Bucureşti
973-6842-67-8 Vox 2000 Bucureşti
973-6842-67-4 Vox 2000 Sibiu
973-6842-67-7 Compania de librării Braşov
973-6842-67-4 Librăria "Central" Sibiu
973-6842-67-7 RolCris Sibiu
973-6842-67-8 RolCris Cluj
973-6842-67-5 RolCris Bucureşti
973-6842-67-9 RolCris Bucureşti
973-6842-67-3 Compania de librării Braşov
973-6842-67-7 Compania de librării Bucureşti
973-6842-67-5 Compania de librării Bucureşti
973-6842-67-7 Librăria "Central" Sibiu
973-6842-67-9 Librăria "Central" Cluj
973-6842-67-5 Vox 2000 Braşov
973-6842-67-6 Vox 2000 Braşov
Compania de distribuţie
Oraş de distribuţie
973-6842-67-9 Vox 2000 Braşov
973-6842-67-7 RolCris Bucureşti
973-6842-67-5 Compania de librării Braşov
973-6842-67-7 Compania de librării Bucureşti
973-6842-67-5 Vox 2000 Sibiu
973-6842-67-7 Librăria "Central" Braşov
973-6842-67-5 Librăria "Central" Braşov
973-6842-67-5 RolCris Sibiu
973-6842-67-6 Vox 2000 Sibiu
973-6842-67-6 Vox 2000 Braşov
973-6842-67-6 Vox 2000 Braşov
973-6842-67-5 Compania de librării Cluj
973-6842-67-7 RolCris Bucureşti
973-6842-67-7 RolCris Bucureşti
973-6842-67-5 Libraria "PlaCedo" Braşov
973-6842-67-4 Vox 2000 Cluj
973-6842-67-4 Vox 2000 Braşov
973-6842-67-8 Vox 2000 Braşov
973-6842-67-5 Vox 2000 Bucureşti
973-6842-67-9 Libraria "PlaCedo" Cluj
973-6842-67-8 Compania de librării Bucureşti
973-6842-67-5 Librăria "Central" Sibiu
973-6842-67-7 Librăria "Central" Cluj
973-6842-67-8 RolCris Bucureşti
973-6842-67-5 Librăria "Central" Cluj
Să se recupereze titlul cărţii (B3:B59) în funcţie de codul ISBN preluat din tabelul de consultare din câmpul J2:K9.În cazul în care codul ISBN nu se regăseşte în tabelul de consultare, se va afişa mesajul "Cheie inexistentă!".
Agent vânzare Cod ISBN
Radulescu Sorin 40 buc 38 lei 1,520 lei 973-6842-67-3
Radulescu Sorin 80 buc 34 lei 2,720 lei 973-6842-67-4
Radulescu Sorin 800 buc 38 lei 30,400 lei 973-6842-67-5
Popescu Mihnea 400 buc 42 lei 16,800 lei 973-6842-67-6
Ionescu Laura 900 buc 31 lei 27,900 lei 973-6842-67-7
Moisescu Adrian 850 buc 31 lei 26,350 lei 973-6842-67-8
Sava Sebastian 1300 buc 42 lei 54,600 lei 973-6842-67-9
Sava Sebastian 1000 buc 34 lei 34,000 lei
Sava Sebastian 30 buc 31 lei 930 lei
Moisescu Adrian 1400 buc 40 lei 56,000 lei
Radulescu Sorin 16 buc 40 lei 640 lei
Radulescu Sorin 600 buc 31 lei 18,600 lei
Ionescu Laura 650 buc 40 lei 26,000 lei
Popescu Mihnea 1050 buc 37 lei 38,850 lei
Popescu Mihnea 800 buc 40 lei 32,000 lei
Moisescu Adrian 1300 buc 38 lei 49,400 lei
Albu Marius 1000 buc 38 lei 38,000 lei
Ionescu Laura 750 buc 37 lei 27,750 lei
Ionescu Laura 60 buc 34 lei 2,040 lei
Moisescu Adrian 125 buc 40 lei 5,000 lei
Moisescu Adrian 600 buc 34 lei 20,400 lei
Moisescu Adrian 100 buc 40 lei 4,000 lei
Moisescu Adrian 250 buc 37 lei 9,250 lei
Ionescu Laura 90 buc 42 lei 3,780 lei
Ionescu Laura 100 buc 29 lei 2,900 lei
Moisescu Adrian 100 buc 38 lei 3,800 lei
Popescu Mihnea 360 buc 40 lei 14,400 lei
Ionescu Laura 2000 buc 42 lei 84,000 lei
Albu Marius 20 buc 40 lei 800 lei
Albu Marius 30 buc 29 lei 870 lei
Moisescu Adrian 40 buc 42 lei 1,680 lei
Moisescu Adrian 400 buc 31 lei 12,400 lei
Cantitate distribuită
Preţ carte
Valoare vânzare
Moisescu Adrian 200 buc 29 lei 5,800 lei
Moisescu Adrian 280 buc 40 lei 11,200 lei
Moisescu Adrian 300 buc 42 lei 12,600 lei
Popescu Mihnea 1200 buc 40 lei 48,000 lei
Radulescu Sorin 450 buc 42 lei 18,900 lei
Albu Marius 50 buc 40 lei 2,000 lei
Albu Marius 60 buc 42 lei 2,520 lei
Radulescu Sorin 490 buc 42 lei 20,580 lei
Radulescu Sorin 900 buc 31 lei 27,900 lei
Sava Sebastian 350 buc 31 lei 10,850 lei
Sava Sebastian 250 buc 31 lei 7,750 lei
Albu Marius 360 buc 42 lei 15,120 lei
Radulescu Sorin 750 buc 40 lei 30,000 lei
Radulescu Sorin 45 buc 40 lei 1,800 lei
Moisescu Adrian 75 buc 42 lei 3,150 lei
Albu Marius 20 buc 34 lei 680 lei
Moisescu Adrian 10 buc 34 lei 340 lei
Moisescu Adrian 40 buc 37 lei 1,480 lei
Popescu Mihnea 500 buc 42 lei 21,000 lei
Albu Marius 240 buc 24 lei 5,760 lei
Ionescu Laura 800 buc 37 lei 29,600 lei
Ionescu Laura 365 buc 42 lei 15,330 lei
Albu Marius 120 buc 40 lei 4,800 lei
Albu Marius 180 buc 37 lei 6,660 lei
Moisescu Adrian 320 buc 42 lei 13,440 lei
(B3:B59) în funcţie de codul ISBN preluat din tabelul de consultare din câmpul J2:K9.În cazul în care codul ISBN nu se regăseşte în tabelul de consultare, se va afişa mesajul "Cheie inexistentă!".
Titlu carte
Visual Basic for Application
Design Web
Baze de Date
Office 2010 - Aplicaţii
Tehnologia aplicaţiilor Office
Programarea calculatoarelor
SIAD
Nume Prenume
POPESCU Viorel Dan Oficiul juridic consilier juridic Apr-2012 11 ani
MIHAI Carmen IT designer Web Sep-2000 22 ani
POPA Florin Marketing agent publicitate Jun-2010 12 ani
ALBU Constanta Marketing analist date Feb-1991 32 ani
NEDELCU Mihai Contabilitate economist Jun-1985 37 ani
CHIRU Bogdan Contabilitate economist Dec-1981 41 ani
ALBITER Oana IT programator Oct-1995 27 ani
VLAD Petru IT designer Web Aug-2002 20 ani
CONSTANTIN Ana IT designer Web May-2005 17 ani
MONDREA Laura Oficiul juridic consilier juridic Apr-1986 37 ani
SAVU Cătălin Marketing analist date Mar-2012 11 ani
PANAIT Cornel Contabilitate economist Oct-1998 24 ani
COMAN Aura Oficiul juridic consilier juridic Dec-1987 35 ani
BLAGA Mădălina Contabilitate economist Aug-2004 18 ani
MIHAI Dinu Marketing agent publicitate Jul-1997 25 ani
DEDU Natalia IT programator Feb-2011 12 ani
GANEA Pavel Marketing agent publicitate Nov-2002 20 ani
SORESCU Liana Contabilitate economist Apr-2006 16 ani
PETRE Cristina Oficiul juridic consilier juridic Apr-1980 43 ani
MANESCU Andreea IT programator Apr-2003 20 ani
VISAN Alexandra Marketing agent asigurări Nov-1993 29 ani
< 5 ani 5-10 ani 10-15 ani 15-20 ani
0 ani 5 ani 10 ani 15 ani
analist date 1,000 € 1,200 € 1,900 € 2,350 €designer Web 1,050 € 1,180 € 2,300 € 2,550 €programator 1,120 € 1,400 € 2,450 € 2,600 €consilier juridic 1,100 € 1,180 € 2,500 € 2,670 €economist 1,000 € 1,070 € 2,080 € 2,200 €agent publicitate 1,034 € 1,100 € 1,980 € 2,250 €
Departament funcţíonal
Funcţia de încadrare
Data angajării
Vechimea (ani)
Să se calculeze Salariul de încadrare, diferenţiat pe departamente funcţionale şi pe tranşe de vechime (vezi domeniul A29:G35).Obs: dacă vechimea calculată nu se regăseşte în prima linie a tabelului (B29:G29), se va prelua salariul ce corespunde tranşei precedente.
Vechime → Funcţii ↓
20-25 ani >=25 ani
20 ani 25 ani
2,670 € 2,900 €2,710 € 2,800 €2,790 € 3,200 €3,030 € 3,350 €2,500 € 2,900 €2,610 € 3,000 €
Salariu încadrare
şi pe tranşe de vechime (vezi domeniul A29:G35).: dacă vechimea calculată nu se regăseşte în prima linie a tabelului (B29:G29), se va prelua salariul ce corespunde tranşei precedente.
Curs Euro 4.417 lei
Destinaţia Braşov
Produs1 1.2 t
Produs2 1.4 t (celula B4), cantitatea livrată (câmpul B7:B9) şi cursul Euro (celula B2).
Produs3 2.9 t
Braşov Bucureşti Ploieşti Predeal Sinaia1.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 € 1.5 t 32.50 € 22.50 € 45.00 € 77.50 € 75.00 € 1.6 t 35.00 € 26.25 € 52.50 € 90.00 € 87.50 € 1.7 t 37.50 € 30.00 € 60.00 € 102.50 € 100.00 € 1.8 t 40.00 € 33.75 € 67.50 € 115.00 € 112.50 € 1.9 t 42.50 € 37.50 € 75.00 € 127.50 € 125.00 € 2.0 t 45.00 € 41.25 € 82.50 € 140.00 € 137.50 € 2.1 t 47.50 € 45.00 € 90.00 € 152.50 € 150.00 € 2.2 t 50.00 € 48.75 € 97.50 € 165.00 € 162.50 € 2.3 t 52.50 € 52.50 € 105.00 € 177.50 € 175.00 € 2.4 t 55.00 € 56.25 € 112.50 € 190.00 € 187.50 € 2.5 t 57.50 € 60.00 € 120.00 € 202.50 € 200.00 € 2.6 t 60.00 € 63.75 € 127.50 € 215.00 € 212.50 € 2.7 t 62.50 € 67.50 € 135.00 € 227.50 € 225.00 € 2.8 t 65.00 € 71.25 € 142.50 € 240.00 € 237.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 transport
(lei)
Să se calculeze Cheltuielile de transport în funcţie de oraşul de destinaţie
Formula de calcul va cuprinde un test de existenţă a cheii de consultare (pentru o cheie incorecta se va returna valoarea nul).
(celula B4), cantitatea livrată (câmpul B7:B9) şi cursul Euro (celula B2).
Cheltuielile de transport în funcţie de oraşul de destinaţie
test de existenţă a cheii de consultare (pentru o cheie incorecta se va returna valoarea nul).
Data facturii Sat, 11 Oct 2014 1001 Produs1 36.25 € 1002 Produs2 19.00 €
Curs Euro 1003 Produs3 37.50 € 1004 Produs4 75.00 € 1005 Produs5 17.50 €
Cod produs Cantitate livrată1006 Produs6 22.50 €
1002 1.7 t 1007 Produs7 55.00 € 1018 1.2 t 1008 Produs8 15.00 € 1007 2.5 t 1009 Produs9 41.00 € 1020 1.9 t 1010 Produs10 33.75 €
1011 Produs11 12.50 € 1012 Produs12 16.00 € 1013 Produs13 29.25 € 1014 Produs14 30.00 € 1015 Produs15 55.00 € 1016 Produs16 19.50 € 1017 Produs17 32.50 € 1018 Produs18 18.00 € 1019 Produs19 20.25 € 1020 Produs20 17.33 €
* se va acorda un discount la preţ, în cazul în care produsul este facturat în perioada promoţională, iar cantitatea livrată (B9:B12) depăşeşte cantitatea începând de la care se acordă o reducere a preţului;
* dacă Data facturii nu se încadrează în perioada promoţională, atunci în calculul valorii se va lua în considerare preţul unitar din
Cod produs
Denumire produs
Preţ unitar (€)
Valoare factură (lei)
Să se calculeze Valoarea facturii în funcţie de cantitatea livrată şi data facturării produsului, ţinând cont de următoarele aspecte:
nomenclatorul de produse (câmpul E2:K22).
=IF(AND(Data_factura >= Data_debut_promoţie; Data_factura <= Data_sfârşit_promoţie; Cantitate_livrată > Cantitate_discount);
(Cantitate_livrată - Cantitate_discount) * Preţ_unitar * (1-Discount) * Curs_Euro + Cantitate_discount * Preţ_unitar * Curs_Euro;
Cantitate_livrată * Preţ_unitar * Curs_Euro)
Cursul EURO va fi preluat în funcţie de Data facturii (celula B3) din tabelul de consultare numit "Cotatii" localizat în foaia de calcul "Cursul EURO".
- -1.0 t 7% ### ###
- -- -- -
1.3 t 15% ### ###2.0 t 3% ### ###1.7 t 10% ### Sunday, June 1, 2014
- -- -- -
2.5 t 15% ### ###- -- -- -- -
1.3 t 18% ### ###2.0 t 10% ### ###1.0 t 6% ### ###
- -
* se va acorda un discount la preţ, în cazul în care produsul este facturat în perioada promoţională, iar cantitatea livrată (B9:B12)
* dacă Data facturii nu se încadrează în perioada promoţională, atunci în calculul valorii se va lua în considerare preţul unitar din
Cantitate Discount
Discount (%)
Data debut promoţie
Data sfârşit promoţie
facturării produsului, ţinând cont de următoarele aspecte:
=IF(AND(Data_factura >= Data_debut_promoţie; Data_factura <= Data_sfârşit_promoţie; Cantitate_livrată > Cantitate_discount);
(Cantitate_livrată - Cantitate_discount) * Preţ_unitar * (1-Discount) * Curs_Euro + Cantitate_discount * Preţ_unitar * Curs_Euro;
Cantitate_livrată * Preţ_unitar * Curs_Euro)
Data facturii Data scadenţei
101 Client1 Constanta Str. A 456,865 Fri, 27 Sep 2013 20 zile Fri, 25 Oct 2013
101 Client1 Constanta Str. A 456,854 Tue, 8 Oct 2013 - Tue, 8 Oct 2013
101 Client1 Constanta Str. A 456,869 Wed, 23 Oct 2013 20 zile Wed, 20 Nov 2013
101 Client1 Constanta Str. A 456,858 Thu, 14 Nov 2013 10 zile Thu, 28 Nov 2013
101 Client1 Constanta Str. A 456,859 Sat, 23 Nov 2013 20 zile Fri, 20 Dec 2013
102 Client2 Bucuresti Str. B 456,855 Mon, 2 Dec 2013 30 zile Mon, 13 Jan 2014
102 Client2 Bucuresti Str. B 456,861 Wed, 18 Dec 2013 15 zile Wed, 8 Jan 2014
103 Client3 Brasov Str. C 456,860 Sat, 15 Mar 2014 15 zile Fri, 4 Apr 2014
103 Client3 Brasov Str. C 456,857 Wed, 16 Apr 2014 - Wed, 16 Apr 2014
104 Client4 Cluj Str. D 456,852 Thu, 24 Apr 2014 - Thu, 24 Apr 2014
104 Client4 Cluj Str. D 456,862 Tue, 6 May 2014 8 zile Fri, 16 May 2014
104 Client4 Cluj Str. D 456,864 Fri, 20 Jun 2014 3 zile Wed, 25 Jun 2014
105 Client5 Arad Str. E 456,853 Mon, 11 Aug 2014 17 zile Wed, 3 Sep 2014
105 Client5 Arad Str. E 456,866 Mon, 6 Jan 2014 5 zile Mon, 13 Jan 2014
105 Client5 Arad Str. E 456,867 Fri, 10 Jan 2014 7 zile Tue, 21 Jan 2014
106 Client6 Timisoara Str. F 456,868 Tue, 4 Feb 2014 - Tue, 4 Feb 2014
106 Client6 Timisoara Str. F 456,856 Wed, 5 Mar 2014 9 zile Tue, 18 Mar 2014
107 Client7 Buzau Str. G 456,870 Tue, 1 Apr 2014 14 zile Mon, 21 Apr 2014
107 Client7 Buzau Str. G 456,871 Fri, 28 Mar 2014 6 zile Mon, 7 Apr 2014
107 Client7 Buzau Str. G 456,863 Sat, 22 Mar 2014 30 zile Fri, 2 May 2014
456,857
Cod client
Nume client
Localitate client
Adresa client
Număr factură
Nr. zile graţie
Care este luna în care a devenit scadentă factura cu nr. 456.857 ? Obs: rezultatul formulei va fi formatat personalizat, astfel încât, în celula destinaţie (G30) se va afişa luna scadenţei utilizând formatul abreviat pentru acest element al datei (de exemplu: Ian).
Plătit Majorări TVA
12,520 lei DA 0 lei 2,379 lei 14,899 lei
15,600 lei NU 530,712 lei 103,799 lei 650,111 lei
8,360 lei DA 0 lei 1,588 lei 9,948 lei
13,000 lei NU 435,630 lei 85,240 lei 533,870 lei
38,060 lei NU 1,267,017 lei 247,965 lei 1,553,042 lei
46,766 lei NU 1,545,616 lei 302,553 lei 1,894,935 lei
3,900 lei NU 129,090 lei 25,268 lei 158,258 lei
14,005 lei DA 0 lei 2,661 lei 16,666 lei
87,056 lei NU 2,796,239 lei 547,826 lei 3,431,121 lei
19,500 lei DA 0 lei 3,705 lei 23,205 lei
12,505 lei DA 0 lei 2,376 lei 14,881 lei
8,706 lei DA 0 lei 1,654 lei 10,360 lei
45,000 lei DA 0 lei 8,550 lei 53,550 lei
2,284 lei DA 0 lei 434 lei 2,718 lei
3,600 lei NU 118,692 lei 23,235 lei 145,527 lei
18,732 lei NU 614,972 lei 120,404 lei 754,107 lei
12,200 lei NU 395,402 lei 77,444 lei 485,046 lei
14,780 lei DA 0 lei 2,808 lei 17,588 lei
81,000 lei NU 2,609,010 lei 511,102 lei 3,201,112 lei
56,100 lei NU 1,792,956 lei 351,321 lei 2,200,377 lei
Valoare factură
Valoarea totală
: rezultatul formulei va fi formatat personalizat, astfel încât, în celula destinaţie (G30) se va afişa luna scadenţei utilizând formatul abreviat pentru acest element al datei (de exemplu: Ian).
Data cotaţiei Curs EURO
Wednesday 01 Oct 2014 4.4127
1 Thursday 02 Oct 2014 4.4106
2 Friday 03 Oct 2014 4.4114
3 Monday 06 Oct 2014 4.4136
4 Tuesday 07 Oct 2014 4.4067
5 Wednesday 08 Oct 2014 4.4089
6 Thursday 09 Oct 2014 4.4061
7 Friday 10 Oct 2014 4.4036
8 Monday 13 Oct 2014 4.4030
9 Tuesday 14 Oct 2014 4.4109
10 Wednesday 15 Oct 2014 4.4137
11 Thursday 16 Oct 2014 4.4300
12 Friday 17 Oct 2014 4.4209
13 Monday 20 Oct 2014 4.4176
14 Tuesday 21 Oct 2014 4.4170
15 Wednesday 22 Oct 2014 4.4166
16 Thursday 23 Oct 2014 4.4236
17 Friday 24 Oct 2014 4.4209
18 Monday 27 Oct 2014 4.4255
19 Tuesday 28 Oct 2014 4.4200Wednesday 29 Oct 2014 4.4226
Thursday 30 Oct 2014 4.4197
Ziua de cotaţie