Aplicatia 8 - Baze de Date Excel (Functii Database)
11
Centraliza 1 2 3 4 5 Cod Client Nume Client Adresa Număr factură 101 Client1 Constanţa Str. A 456,853 105 Client5 Arad Str. E 456,854 101 Client1 Constanţa Str. A 456,856 102 Client2 Bucureşti Str. B 456,858 106 Client6 Timişoara Str. F 456,859 105 Client5 Arad Str. E 456,855 102 Client2 Bucureşti Str. B 456,861 103 Client3 Braşov Str. C 456,852 107 Client7 Buzău Str. C 456,857 104 Client4 Cluj Str. D 456,860 104 Client4 Cluj Str. D 456,862 106 Client6 Timişoara Str. F 456,864 105 Client5 Arad Str. E 456,865 103 Client3 Braşov Str. C 456,866 102 Client2 Bucureşti Str. B 456,867 106 Client6 Timişoara Str. F 456,868 105 Client5 Arad Str. E 456,869 102 Client2 Bucureşti Str. B 456,870 107 Client7 Buzău Str. G 456,871 103 Client3 Braşov Str. C 456,863 CERINŢE INFORMAŢIONALE Bucureşti şi Braşov, în luna precedentă. in ultimele 6 luni? Localitate Client 1. Să se calculeze suma valorică a facturilor emise şi neîncasate de l 2. Să se calculeze suma datorată minimă (col. M) aferentă facturilor 3. Câte facturi au devenit scadente în ultima săptămână a lunii prece
-
Upload
andrei-agache -
Category
Documents
-
view
15 -
download
8
description
aplicatii excel
Transcript of Aplicatia 8 - Baze de Date Excel (Functii Database)
Functii Database SEMINAR
Centralizatorul facturilor emise12345678910111213Cod ClientNume
ClientLocalitate ClientAdresaNumr facturData facturiiNr zile
graieData scadeneiValoare facturPltitMajorriTVA
bgn: TVA=(Valoare+Majorri)*19%Suma datorat
bgn: Suma datorat=Valoare+Majorri+TVA
101Client1ConstanaStr. A456,853Wed 24 Nov 1020 zileWed 22 Dec 1012,520 leiDA0.02,379 lei14,899 lei105Client5AradStr. E456,854Sat 6 Feb 100.0Sat 6 Feb 1015,600 leiNU91,416 lei20,333 lei127,349 lei101Client1ConstanaStr. A456,856Sat 23 Jan 1020 zileFri 19 Feb 108,360 leiDA0.01,588 lei9,948 lei102Client2BucuretiStr. B456,858Tue 18 May 1010 zileTue 1 Jun 1013,000 leiNU61,230 lei14,104 lei88,334 lei106Client6TimioaraStr. F456,859Wed 25 Aug 1020 zileWed 22 Sep 1038,060 leiNU136,255 lei33,120 lei207,435 lei105Client5AradStr. E456,855Fri 22 Oct 1030 zileFri 3 Dec 1046,766 leiNU133,751 lei34,298 lei214,815 lei102Client2BucuretiStr. B456,861Sat 18 Sep 1015 zileFri 8 Oct 103,900 leiNU13,338 lei3,275 lei20,513 lei103Client3BraovStr. C456,852Sat 19 Feb 1115 zileFri 11 Mar 1114,005 leiDA0.02,661 lei16,666 lei107Client7BuzuStr. C456,857Wed 9 Mar 110.0Wed 9 Mar 1187,056 leiNU165,406 lei47,968 lei300,430 lei104Client4ClujStr. D456,860Sat 12 Mar 110.0Sat 12 Mar 1119,500 leiDA0.03,705 lei23,205 lei104Client4ClujStr. D456,862Tue 8 Mar 118 zileFri 18 Mar 1112,505 leiDA0.02,376 lei14,881 lei106Client6TimioaraStr. F456,864Sat 8 Jan 113 zileWed 12 Jan 118,706 leiDA0.01,654 lei10,360 lei105Client5AradStr. E456,865Thu 10 Feb 1117 zileMon 7 Mar 1145,000 leiDA0.08,550 lei53,550 lei103Client3BraovStr. C456,866Sat 5 Mar 115 zileFri 11 Mar 112,284 leiNU4,294 lei1,250 lei7,828 lei102Client2BucuretiStr. B456,867Thu 17 Mar 117 zileMon 28 Mar 113,600 leiDA0.0684 lei4,284 lei106Client6TimioaraStr. F456,868Fri 4 Feb 110.0Fri 4 Feb 1118,732 leiNU41,772 lei11,496 lei72,000 lei105Client5AradStr. E456,869Sat 5 Mar 119 zileThu 17 Mar 1112,200 leiNU22,204 lei6,537 lei40,941 lei102Client2BucuretiStr. B456,870Wed 3 Aug 1114 zileTue 23 Aug 1114,780 leiDA0.02,808 lei17,588 lei107Client7BuzuStr. G456,871Mon 28 Nov 110.0Mon 28 Nov 1181,000 leiNU972 lei15,575 lei97,547 lei103Client3BraovStr. C456,863Fri 14 Oct 1130 zileFri 25 Nov 1156,100 leiNU1,178 lei10,883 lei68,161 lei
CERINE INFORMAIONALEFORMULACMP CRITERIILocalitate ClientPltitluna precedenta1. S se calculeze suma valoric a facturilor emise i nencasate de la clienii din 0. factBucuretiNUFALSEBucureti i Braov, n luna precedent.BraovNUFALSE
2. S se calculeze suma datorat minim (col. M) aferent facturilor emise ultimele 6 luniin ultimele 6 luni?TRUE
3. Cte facturi au devenit scadente n ultima sptmn a lunii precedente?0. factFALSE
4. Cte facturi s-au emis n zilele de weekend ale anului precedent?8. factFALSE1900
5. S se calculeze media valoric a facturilor emise n primul semestru al anului curent.FALSEObservaie: n cazul n care nici o nregistrare nu ndeplinete criteriile enunate, mesajul de eroare #DIV/0! va fi nlocuit cu o celul vid.
6. Care este factura (Numr factur) la care s-au aplicat cele mai mari penaliti 456857Majorridatorit nerespectrii condiiilor contractuale? 165,406 lei
TEMA
Centralizatorul facturilor emise
Cod ClientNume ClientLocalitate ClientAdresaNumr facturData
facturiiNr zile graieData scadeneiValoare
facturPltitMajorriTVA
bgn: TVA=(Valoare+Majorri)*19%Suma datorat
bgn: Suma datorat=Valoare+Majorri+TVA
101Client1ConstanaStr. A456,853Wed 24 Nov 1020 zileWed 22 Dec 1012,520 leiDA0.02,379 lei14,899 lei105Client5AradStr. E456,854Sat 6 Feb 100.0Sat 6 Feb 1015,600 leiNU91,416 lei20,333 lei127,349 lei101Client1ConstanaStr. A456,856Sat 23 Jan 1020 zileFri 19 Feb 108,360 leiDA0.01,588 lei9,948 lei102Client2BucuretiStr. B456,858Tue 18 May 1010 zileTue 1 Jun 1013,000 leiNU61,230 lei14,104 lei88,334 lei106Client6TimioaraStr. F456,859Wed 25 Aug 1020 zileWed 22 Sep 1038,060 leiNU136,255 lei33,120 lei207,435 lei105Client5AradStr. E456,855Fri 22 Oct 1030 zileFri 3 Dec 1046,766 leiNU133,751 lei34,298 lei214,815 lei102Client2BucuretiStr. B456,861Sat 18 Sep 1015 zileFri 8 Oct 103,900 leiNU13,338 lei3,275 lei20,513 lei103Client3BraovStr. C456,852Sat 19 Feb 1115 zileFri 11 Mar 1114,005 leiDA0.02,661 lei16,666 lei107Client7BuzuStr. C456,857Wed 9 Mar 110.0Wed 9 Mar 1187,056 leiNU165,406 lei47,968 lei300,430 lei104Client4ClujStr. D456,860Sat 12 Mar 110.0Sat 12 Mar 1119,500 leiDA0.03,705 lei23,205 lei104Client4ClujStr. D456,862Tue 8 Mar 118 zileFri 18 Mar 1112,505 leiDA0.02,376 lei14,881 lei106Client6TimioaraStr. F456,864Sat 8 Jan 113 zileWed 12 Jan 118,706 leiDA0.01,654 lei10,360 lei105Client5AradStr. E456,865Thu 10 Feb 1117 zileMon 7 Mar 1145,000 leiDA0.08,550 lei53,550 lei103Client3BraovStr. C456,866Sat 5 Mar 115 zileFri 11 Mar 112,284 leiNU4,294 lei1,250 lei7,828 lei102Client2BucuretiStr. B456,867Thu 17 Mar 117 zileMon 28 Mar 113,600 leiDA0.0684 lei4,284 lei106Client6TimioaraStr. F456,868Fri 4 Feb 110.0Fri 4 Feb 1118,732 leiNU41,772 lei11,496 lei72,000 lei105Client5AradStr. E456,869Sat 5 Mar 119 zileThu 17 Mar 1112,200 leiNU22,204 lei6,537 lei40,941 lei102Client2BucuretiStr. B456,870Wed 3 Aug 1114 zileTue 23 Aug 1114,780 leiDA0.02,808 lei17,588 lei107Client7BuzuStr. G456,871Mon 28 Nov 110.0Mon 28 Nov 1181,000 leiNU972 lei15,575 lei97,547 lei103Client3BraovStr. C456,863Fri 14 Oct 1130 zileFri 25 Nov 1156,100 leiNU1,178 lei10,883 lei68,161 lei
CERINE INFORMAIONALEFORMULACMP CRITERII1. Suma valoric a facturilor nencasate de la clienii din Arad.
2. Maximul valoric al facturilor ncasate de la clienii din Braov.
3. Minimul valoric al facturilor emise ctre "Client1".
4. Media valoric a facturilor emise i nencasate.Observaie: n cazul n care nici o nregistrare nu ndeplinete criteriile enunate, mesajul de eroare #DIV/0! va fi nlocuit cu cratima.
5. Cte zile de graie s-au acordat pentru factura numrul 456.855?
6. Care este valoarea total a facturilor emise ctre Client2 i Client3n luna precedent pentru care s-au pltit penaliti?
