Seminar Microsoft Excel partea 1n acest seminar vei utiliza urmtoarele funcionaliti ale pachetului software: Realizarea i formatarea unei foi de calcul Adrese absolute i relative Funcii: matematice, logice, financiare, matriceale, definite de utilizator

Exemplul 1Nr. 1. 2. Pas Creai un fiier MS Excel nou. Realizai i formatai o foaie de calcul din fiierul nou creat, astfel nct s arate ca n figura de mai jos: Mod de rezolvare OBS.

3.

Inserai o nou coloan n faa tabelului, cu titlul Nr.Crt. Generai numere n prima coloan

4.

Selectai coloana A Insert->Columns Scriei Nr. Crt. n celula A1 Scriei 1 in celula A2 Selectai celulele A2:A10 Home-> Editing-> Fill->Series: Step Value: 1 OK

5.

Redenumii foaia de calcul Tabel Introducei data curenta n coloana Data Salvai fiierul cu numele Excel1.xls Calculai valoarea fr TVA a fiecrui produs importat.

Clic-dreapta pe Sheet1, Rename: Tabel sau Home->Cells->Format-> Rename Sheet Formulas->Date&Time->Today() Office Button->Save sau Save As Calculul valorii produselor (celulele G2:G10) - Selectai celula G2 - Scriei formula de calcul, respectiv =E2*F2 - Copiai formula i n celelalte celule (tragei de colul din dreapta jos, cnd mouse-ul este sub forma unei cruci negre) In celula H2 introducei formula: H2= G2*M2 Copiai formula n celelalte celule (H2:H10) Observai adresarea relativ!

6. 7. 8.

9. 10. 11. 12.

In coloana H, calculai TVA-ul aferent. Studiai formula pentru a vedea dac TVA-ul aferent a fost calculat corect. Rescriei formula utiliznd adresarea absolut a celulelor Calculai accizele aferente buturilor alcoolice, n coloana I (Valoare accize)

H2=G2*$M$2 Folosii funcia logic IF, avnd urmtorii parametri: -Logical Test: testati daca produsul face parte din categoria Bauturi Alcoolice (C2="Bauturi Alcoolice") -Value if true: valoarea accizei se obine ca produs ntre valoarea fr TVA i nivelul de 0,2 al accizei -Value if false: nu se aplic acciz Se calculeaz valorile din coloana J. - Domeniul de celulele care trebuie calculate: G11:J11 - Formulas-> Insert Function: SUM, Parametru (Domeniu) Identificai corect ultimii doi parametri ai funciei!

13. 14.

Calculai valoarea total pentru fiecare tip de produs, ca sum dintre Valoarea fr TVA, TVA i Acciz . Realizai suma valorilor calculate anterior, pentru toate produsele importate, folosind funcia SUM. Salvai fiierul

15.

Exemplul 2 Funcii FinanciareNr. 1. Pas Deschideti fiierul Seminar1 Excel.xls, foia de calcul Financiar i rezolvai urmtoarele probleme O companie cumpr un utilaj de producie, care se estimeaz c va aduce n urmtorii 10 ani un venit anual de 28.600. Pentru finanarea utilajului, firma are nevoie de un credit. Banca selectat acord credite cu dobnda de 6, 75% pe an. Care ar trebui s fie preul maxim al utilajului, dac ar trebui s fie pltit numai din veniturile pe care le aduce. Cheltuielile de ntreinere nu sunt luate n considerare. Mod de rezolvare OBS.

2.

3.

Presupunem c dorim s economisim bani pentru un proiect care va ncepe peste un an. Facem n acest scop un depozit de 1000, cu o rat anual a dobnzii de 6%, pltibil lunar (rata lunara a dobnzii va fi 6%/12=0.5%). Vrem s depunem suma de 100 la nceputul fiecrei luni, timp de 1 an. Ci bani vom avea n cont la sfritul celor 12 luni? Presupunem c se face o investiie astfel: se pltesc 10.000 peste un an i se primesc anual venituri de 3.000, 4.000 i 6.800 n urmtorii 3 ani. Dac rata anual a inflaiei este de 10%, care va fi valoarea prezent net a investiiei?

Funcia PV(rate,nper,pmt,fv,type) Returneaz valoarea prezent a unei investiii (valoarea n prezent a unei serii de pli viitoare) Rata reprezint rata dobnzii. De exemplu, dac se obine un mprumut cu o rat anual a dobnzii de 10%, iar plile sunt lunare, rata lunar a dobnzii va fi de 0,1/12 adic0,83%. Nper reprezint numrul total de perioade n care se efectueaz pli. Pentru un mprumut pe 4 ani cu pli lunare nper va fi 4*12=48 de perioade. Pmt reprezint plata efectuat n fiecare perioad i care rmne fix pe ntreaga durat a anuitii Fv reprezint valoarea viitoare sau ce valoare se vrea a se obine dup efectuarea ultimei pli. Valoarea implicit a acestui argument este 0. Tipul poate fi 0 sau 1 i indic momentul de efectuare a plilor, respectiv sfritul sau nceputul perioadei. Funcia FV(rata,nper,pmt,pv,tip) Returneaz valoarea viitoare a unei investiii bazate pe pli periodice i constante i cu o rata a dobnzii constant. Argumentele rata, nper, pmt i tip au aceeai semnificaie ca n cazul funciei PV Pv reprezint valoarea prezent sau ct valoreaz n momentul prezent o serie de pli viitoare (implicit este 0)

4.

Funcia NPV(rata,valoare1,valoare2, ...) Calculeaz valoarea prezent neta unei investiii utiliznd rata inflaei i o serie de pli (valori negative) i venituri (valori pozitive) viitoare. Rata reprezint rata inflaiei pe parcursul unei perioade. Valoare1, valoare2, ... argumente (de le 1 la 29) ce reprezint plile sau veniturile.

5.

Salvai fiierul i verificai rezultatele

Exemplul 3 Funcii FinanciareNr. 1. 2. 3. Pas Mod de rezolvare OBS. Activai foaia de calcul Salarii din fiierul Seminar1 Excel.xls Trebuie calculate veniturile totale lunare ale agenilor unei companii de asiguri, tiind c acetia primesc un salariu fix, un comision din cifra de afaceri pe care au realizat-o n acea lun i un spor de vechime. Calculai comisionul agenilor Inserai n coloana E funcia matriceal VLOOKUP, avnd Citii cu atenie din Helpul MS Excel modul de urmtorii parametri: utilizare a funciei! Lookup_value: valoarea care trebuie cutat n tabel (Cifra de afaceri) Table_array: tabelul de comision (atenie ca celulele care refer tabelul (D14 i E19) s aib adrese absolute) Col_index_num: coloana din tabelul de comision a crei valoare se returneaz (coloana procentelor) Range_lookup: dac se caut o valoare apropiat sau una exact (se las necompletat) Scrieti o funcie care, pe baza salariului fix i a vechimii unui angajat, s calculeze sporul de vechime ce i se cuvine acestuia. Algoritmul de calculare a sporului de vechime este urmtorul: pentru o vechime sub 3 ani nu se acord spor; pentru o vechime ntre 3 i 5 ani sporul reprezint 5% din salariu; pentru o vechime ntre 5 i 10 ani sporul reprezint 10% din salariu; pentru o vechime ntre 10 i 15 ani sporul este de 15% din salariu; pentru o vechime mai mare de 15 ani sporul este de 20% din salariu. Deschidei editorul Visual Basic Developer->Code->Visual Basic

4.

5.

6.

Activai proiectul VBAProject(Excel_Lucru) i scriei funcia

Insert->Module Copiai urmtorul cod surs: Function Spor(salariu, vechime) If vechime < 3 Then Spor = 0 Else If vechime >= 3 And vechime < 5 Then Spor = 0.05 * salariu Else If vechime >= 5 And vechime < 10 Then Spor = 0.1 * salariu Else If vechime >= 10 And vechime < 15 Then Spor = 0.15 * salariu Else Spor = 0.2 * salariu End If End If End If End If End Function

7. 8.

Salvai proiectul, nchidei editorul VB i revenii la foaia de calcul din Excel Calculai sporul, utiliznd funcia definit ca pe orice alt funcie Excel Calculai veniturile totale ale agenilor ca sum dintre salariul fix, cifra de afaceri nmulit cu procentul din comision i sporul de vechime nsumai cifra de afaceri i veniturile totale, n celulele corespunztoare. Salvai i nchidei fiierul

-Selectai celula E2 - Formulas-> Insert Function Category: User Defined-> Spor Se calculeaz coloana G.

9.

10. 11.