Sheet1Factura nr.807din dataTuesday, 20 April 2004Transportul este fcut de furnizor?DADestinaiaBucuretiCod produsDenumire produsCantitate livratCheltuieli transportPre unitarPre unitarMROUND( )Pre unitarMROUND( )simplificatValoareTVA (19%)Total Valoare(MAGAZIN 11001) ()Produs13 t45,00028,50029,00029,000130,50024,795155,295 lei(MAGAZIN 21002) ()Produs23 t45,00045,00045,00045,000180,00034,200214,200 lei(MAGAZIN 31003) ()Produs310 t150,000225,000230,000230,0002,400,000456,0002,856,000 lei(MAGAZIN 11006) ()Produs621 t315,000102,600100,000100,0002,331,090442,9072,773,997 lei(MAGAZIN 21001) ()Produs112 t180,00031,25031,00031,000555,000105,450660,450 lei(MAGAZIN 21007) ()Produs78 t120,000125,000130,000130,0001,120,000212,8001,332,800 leiTotal factura nr. 807645,00092,89194,16694,1666,716,5901,276,1527,992,742 leiNr. Produse 4Cod produsDenumire produsPre produsCantitate discountDiscount (%)Destinaia / Cantitate transportBraovBucuretiPloietiPredealSinaia(MAGAZIN 11001) ()Produs128,5000.00.01t80,00015,00030,00060,00050,000(MAGAZIN 11002) ()Produs241,0400.00.02t90,00030,00060,000120,000100,000(MAGAZIN 11003) ()Produs3171,0000.00.03t100,00045,00090,000155,000150,000(MAGAZIN 11004) ()Produs4342,0000.00.04t110,00060,000120,000210,000200,000(MAGAZIN 11005) ()Produs57,9800.00.05t120,00075,000150,000260,000250,000(MAGAZIN 11006) ()Produs6102,60012 t15%6t130,00090,000180,000310,000300,000(MAGAZIN 11007) ()Produs7114,0002 t3%7t140,000105,000210,000360,000350,000(MAGAZIN 21001) ()Produs131,2500.00.08t150,000120,000240,000410,000400,000(MAGAZIN 21002) ()Produs245,0007 t15%9t160,000135,000270,000460,000450,000(MAGAZIN 21003) ()Produs3187,5000.00.010t170,000150,000300,000510,000500,000(MAGAZIN 21004) ()Produs4375,0000.00.011t180,000165,000330,000560,000550,000(MAGAZIN 21005) ()Produs58,7500.00.012t190,000180,000360,000610,000600,000(MAGAZIN 21006) ()Produs6112,5000.00.013t200,000195,000390,000660,000650,000(MAGAZIN 21007) ()Produs7125,00020 t12%14t210,000210,000420,000710,000700,000(MAGAZIN 31001) ()Produs137,5000.00.015t220,000225,000450,000760,000750,000(MAGAZIN 31002) ()Produs254,0005 t20%16t230,000240,000480,000810,000800,000(MAGAZIN 31003) ()Produs3225,0000.00.017t240,000255,000510,000860,000850,000(MAGAZIN 31004) ()Produs4450,0000.00.018t250,000270,000540,000910,000900,000(MAGAZIN 31005) ()Produs510,50015 t5%19t260,000285,000570,000960,000950,000(MAGAZIN 31006) ()Produs6135,0000.00.020t270,000300,000600,0001,010,0001,000,000(MAGAZIN 31007) ()Produs7150,0000.00.021t280,000315,000630,0001,060,0001,050,000Tabela "Magazine"Tabela "Catalog produse"Cod MagazinAdaosComercialIDProdusDenumire ProdusPre Unitar114%1,001Produs125,000225%1,002Produs236,000350%1,003Produs3150,0001,004Produs4300,0001,005Produs57,0001,006Produs690,0001,007Produs7100,000A. CERINE PENTRU TABELUL DE PE PLAJA DE CELULE A16:E37.Se cunosc datele din tabelele "Magazine - A40:B43" i "Catalog produse - D40:F47". n tabelul A16:E37 se vor tasta: codul de produs, cantitatea peste care se va acord un discount, procentul de discount.1. Codul produsului este o combinaie ntre "ID Produs" i "Cod Magazin". S se formateze plaja de celule A17:A37 astfel nct formatul de afiare s fie, de exemplu, "(MAGAZIN 1) (1001)" pentru produsul cu "ID Produs" = 1001 care se afl n magazinul ce are "Cod magazin" = 1.Rspuns: Selectai plaja de celule A17:A37 - > Click dreapta - >Format Cells -> Number -> Custom - >Type2. Plaja de celule A17:A37 va fi validat la un numr de 5 cifre, iar mesajul de eroare care va apare atunci cnd nu se respect condiia s fie: "Tastai obligatoriu un "Cod Produs" din 5 cifre ".Rspuns 1: Selectai plaja de celule A17:A27- > Data - > Validation -> Custom -> FormulaRspuns 2: Selectai plaja de celule A28:A37- > Data - > Validation -> Text Length3. Denumirea produsului i preul aferent acestuia se vor prelua automat din tabelul "Catalog produse" n funcie de "ID Produs".Observaie: La preluarea automat a preului se va ine cont i de "Adaos Comercial" specific pentru fiecare "Cod Magazin" n parte.Rspuns: Deoarece, codul de produs din tabelul A16:E37 este o combinaie ntre "ID Produs" i "Cod Magazin", iar denumirea i preul produsului trebuie preluate n funcie numai de "ID Produs", ca soluie se propune extragerea ultimelor 4 cifre din dreapta "Cod Produs" -ului cu ajutorul funciei RIGHT( ), rezultnd astfel numai "ID Produs". Funcia RIGHT( ) returneaz ntotdeauna un text. De exemplu, "1001" care-l va extrage funcia RIGHT() de pe celula A17 nu este o cifr, ci un text. Dar, n tabelul de consultare "Catalog Produse" pe prima coloan avem date de tip numeric, deci se impune convertirea datelor de tip text returnate de funcia RIGHT( ) n cifre cu ajutorul funciei VALUE( ).Observaie: Dac pe prima coloan a tabelului de consultare ar fi fost din start date de tip text (presupunem c au fost convertite cifrele n text, astfel: selectai plaja de celule D41:F47 -> click dreapta pe plaja selectat -> Format Cells-> Number ->Text ), atunci nu ar mai fi fost necesar utilizarea funciei VALUE( ).Selectai o celul de pe plaja de celule B17:B37 i vedei formula scris pentru preluarea n mod automat a denumirii produsului din tabelul "Catalog Produse" n funcie de "ID Produs"(extras din "Cod Produs").La preluarea automat a preului s-a mai fcut o verificare n plus, i anume dac "Cod Magazin" extras din "Cod Produs" exist n tabelul "Magazine", deoarece preul pe care trebuie s-l returneze trebuie s fie majorat cu "Adaos Comercial" specific pentru fiecare "Cod Magazin". Dar i acest "Adaos Comercial" trebuie preluat n mod automat n funcie de "Cod Magazin" extras din "Cod Produs". Extragerea "Cod Magazin" din "Cod Produs" se face cu ajutorul funciei LEFT( ). Aceast funcie returneaz ntotdeauna un text, deci trebuie s convertim rezultatul returnat din text n cifre cu funcia VALUE( ) (vezi explicaia de la preluarea automat a denumirii produsului).Selectai o celul de pe plaja de celule C17:C37 i vedei formula scris pentru preluarea n mod automat a preului din tabelul "Catalog Produse" n funcie de "ID Produs" extras din "Cod Produs", majorat cu "Adaos Comercial" preluat, la rndul lui n mod automat din tabelul "Magazine" n funcie de "Cod Magazin" extras tot din "Cod Produs".B. CERINE PENTRU TABELUL DE PE PLAJA DE CELULE A6:J14.Se cunosc datele din tabelul de pe plaja de celule A16:E37 (acesta este acum tabelul de baz). n tabelul A6:J14 se vor tasta: codul de produs i cantitatea livrat.1. Data facturii (celula I2) va fi data curent, utiliznd urmtorul format de afiare: "luni, 08 martie 2004".Rspuns: Selectai celula I2 - > Click dreapta pe celula selectat- >Format Cells -> Number -> Custom - >Type2. Denumirea produsului, precum i preul aferent acestuia se vor recupera din tabloul de consultare vertical A16:E37. Observaie: Preurile din factur (E7:E12) vor fi rotunjite la un multiplu de rotunjire, astfel: - preurile de ordinul miilor se rotunjesc la un multipli de 500; - preurile de ordinul zecilor de mii, se rotunjesc la un multiplu de 1.000; - preurile de ordinul sutelor de mii, se rotunjesc la un multiplu de 10.000; - preurile de ordinul milioanelor se rotunjesc la un multiplu de 100.000.Rspuns: Selectai o celul de pe plaja de celule B7:B12 i vedei formula scris pentru recuperarea n mod automat a denumirii produsului n funcie de codul produsului. Selectai o celul de pe plaja de celule E7:E12 i vedei formula scris pentru recuperarea n mod automat a preului produsului n funcie de codul produsului. Pentru a vedea rotunjirile la un multiplu verificai formulele de pe plajele de celule F7:F12 i G7:G12. La formulele de rotunjire s-a luat n calcul lungimea preului (cu ajutorul funciei LEN( )) pentru a observa la ct anume s se fac rotunjirea (dac lungimea preului este de 4 cifre, adic de ordinul miilor, rotunjirea se face la 500, dac lungimea preului este de 5 cifre, adic de ordinul miilor, rotunjirea se face la 1000, etc).Observaie: n cadrul coloanei "Pre unitar MROUND( )" s-a inut cont de faptul c preul se va prelua n funcie de codul de produs, deci acolo unde trebuia s calculez lungimea preului am plasat funcia Vlookup, care-mi prelua acest pre, n cadrul funciei LEN( ).n cadrul coloanei "Pre unitar MROUND( ) simplificat" ne-am folosit de preul unitar din celulele de pe plaja de celule E7:E12 (care au fost deja preluate cu ajutorul funciei Vlookup) pentru a vedea mai clar funciile LEN( ) i MROUND( ).3. Destinaia transportului (celula I4) va fi selectat dintr-o list derulant care va cuprinde toate oraele de destinaie.Rspuns: Selectai celula I4 ->Data - >Validation - >Allow: List - > Source: =$H$16:$L$16.4. Cheltuielile de transport vor fi recuperate din tabelul G16:L37, n funcie de destinaie i de cantitatea transportat.Rspuns: Selectai o celul de pe plaja de celule D7:D12 i vedei formula scris pentru recuperarea n mod automat a cheltuielilor de transport n funcie de destinaie i de cantitatea livrat. Observaie: Pentru a nelege funcia HLOOKUP verificai i rezolvarea unui alt exemplu care se afl n dreapta foii de calcul.5. S se formateze diferit (bold, chenar, culoare de fundal galben) codurile produselor la care cantitatea facturat depete media livrrilor.Rspuns: Selectai plaja de celule A7:A12 - >Format - > Conditional Formatting.6. Se va calcula totalul cheltuielilor de transport (n celula D13) pentru produsele la care cantitatea livrat depete 10 tone.Rspuns: Selectai celula D13 i vedei formula scris.7. S se numere produsele din factur a cror valoare depete cuantumul de 500 mii lei (celula A14), utiliznd concatenarea ntre o funcie i un ir de caractereRspuns: Selectai celula A14 i vedei formula scris.8. S se concateneze n celula A13 textul "TOTAL FACTURA NR. : " cu numrul facturii existent n celula E2.Rspuns: Selectai celula A13 i vedei formula scris.9. n celula E13 se va calcula un pre mediu, valoarea medie fiind rotunjit la ntreg.Rspuns: Selectai celula E13 i vedei formula scris.10. n celulele H13, I13 i J13 se va calcula suma valorilor aferente cmpurilor respective, folosind o procedur automat de calcul (AutoSum)Rspuns: Selectai celula H13, iar apoi de pe bara cu instrumente standard executai click pe butonul AutoSum (simbolul ). Automat n celula H13 apare formula de nsumare: =SUM(H7:H12). Repetai paii i pentru celulele I13 i J13.11. Asupra valorilor din coloana "Total Valoare" se va aplica formatul monetar (lei).Rspuns 1: Selectai plaja de celulel J7:J12 - > Click dreapta pe plaja selectat - >Format Cells -> Number -> Custom - >TypeRspuns 2: Selectai plaja de celulel J7:J12 - > Click dreapta pe plaja selectat - >Format Cells -> Number -> Currency - >Symbol12. S se formateze condiional (bold, italic, rou) produsele (cmpul B17:B37) pentru care se acord o reducere comercial.Rspuns: Selectai plaja de celule B17:B37 - >Format - > Conditional Formatting.13. S se formateze condiional (bold, italic, rou) produsele (cmpul B7:B12) pentru care se acord o reducere comercial.Rspuns: Selectai plaja de celule B7:B12 - >Format - > Conditional Formatting.14. Valoarea produsului se va calcula lund n considerare cantitatea livrat, preul unitar (care are acum adaosul comercial specific fiecrui magazin - vezi formula de la "Pret Produs" din tabelul A16:E37 i procentele de adoas comercial din tabelul "Magazine"), un discount care se va aplica difereniat pe produse, n raport de cantitatea comandat, dar i suma reprezentnd cheltuielile de transport. Reducerea se va aplica pentru livrrile care depesc cantitatea precizat n cmpul D17:D37.Rspuns: Selectai o celul de pe plaja de celule H7:H12 i vedei formula scris.

Sheet2

Sheet3