|
Proiect - Modelul Marcowitz de diversificare a portofoliuluiModelul Marcowitz de diversificare a portofoliului
Preocuparea cotidiana a investitorilor financiari si a gestionarilor portofoliilor de titluri este de a anticipa tendintele de crestere sau de scadere ale indicelui general al pietei bursiere. De aceste tendinte este legata evolutia valorii de piata a fiecarui titlu din portofoliu. Fiecare valoare mobiliara urmareste, mai mult sau mai putin, tendintele pietei. Cand indicele pietei este in crestere, majoritatea titlurilor au un curs crescator si invers. Anumite valoti mobiliare sunt mai sensibile decat altele la miscarile pietei bursiere. Aceasta relatie intre rentabilitatea realizata de o valoare mobiliara si rentabilitatea, ca indice general al valorilor mobiliare, este formalizata in cadrul conceptului de model de piata. Modelul de piata, in forma sa cea mai simpla, reprezinta relatia liniara ce poate exista intre ratele de rentabilitate constatate, intr-o perioada de timp, asupra unei actiuni sau asupra unui portofoliu de valori mobiliare si ratele reralizate in aceeasi perioada, prin indicele general al pietei bursiere. 35146ghc77ckk2e Ca toti cercetatorii de reputatie mondiala, H. Marcowitz a pornit de la negarea unei afirmatii care risca sa devina o paradigma in evaluarea portofoliului de active riscante(actiuni, obligatuni, polite de asigurare). Pana la cercetarea lui Marcowitz(1952), se incetatenise ideea ca valoarea unui portofoliu este determinata de speranta matematica a rentabilitatii acestuia. Obiectivul urmarit ar fi deci maximizarea sperantei matematice, un obiectiv indelung cautat in sfera asigurarilor. Maxixizarea sperantei matematice poate constitui un criteriu de evaluare numai sub ipoteze foarte restrictive:
Marcowitz afirma si convinge prin argumentatia sa ca, in cazul cel mai general al activelor riscante cu sperante de rentabilitate diferite si interdependente si, deci cu riscuri diferite si corelate, criteriul de evaluare este cel de “speranta de rentabilitate –dispersie “. Potrivit acestui criteriu, comportamentul investitional rational va urmari maximizarea rentabilitatii sperate pe unitatea de risc data(asumata) sau invers minimizarea riscului unitatea de rentabilitate data(asumata). Mai tarziu se va demonstra ca inclusiv criteriul “speranta –dispersie” este un caz particular al criteriului “speranta de utilitate” In continuare ne vom ocupa de portofoluiul de trei titluri, cu care na apropiem foarte mult de cazul general al portofoliului de “n” titluri. Procedurile de calcul pentru cazul a trei titluri ne ajuta in intelegerea mai buna a avaluarii portofoliului de “n” titluri.
In cazul a trei titluri, parametrii portofoliului sunt urmatorii :
Restrictiile modelului Marcowitz sunt urmatoarele :
Conform acestor restrictii se admite ca un titlu sa lipseasca din portofoliu, dar nu se admite detinerea unui titlu la descoperire(prin vanzarea lui pe debit, fara sa-l detina, x<0). Vanzarea pe debit a unui titlu este echivalenta cu o dobinda egala cu speranta de rentabilitate a titlului. Prin intermediul celor doua restrictii se poate aduce exprimarea modelului Marcowitz in “planul” determinat de doua necunoscute: x1 si x2. Astfel, daca x3=1-x1-x2, atunci, prin inlocuirea necunoscutei x3, perametrii portofoliului de trei titluri se pot exprima :
In planul a doua necunoscute se poate determina relativ usor portofoliul cu varianta minimala absuluta (PVMA) al celor trei titluri. Este suficient sa se caute minimul de risc al portofoliului a carui compozitie in x1 si x2 va fi data de sistemul celor doua derivate partiale egalate cu 0
Solutiile sistemului(x1 si x2 ) vor permite apoi deducerea ponderii titlului 3 si apoi parametrii Ep si σp ai PVMA. Satisfacem, in acest fel, exigentele investitorului cu aversiune(totala) de risc. El are acum compozitia optima a portofoliului de trei titluri care-i confera o anumita rentabilitate Ep la cel mai mic risc posibil. Dar suntem interesati sa oferim aceleasi informatii pentru toti investitorii, si deci sa construim frontiera eficienta. Conform criteriului “speranta –dispersie”, se cauta riscul minim la o speranta de rentabilitate scontata specifica fiecarui investitor :
Stiind ca : Riscul σp^2 il putem exprima in forma matriceala astfel :
Unde : V=matricea dispersii-covariante x=vextor coloana al ponderilor xi x’=transpusa vectorului x
Pentru determinarea frontierei eficiente se procedeaza la minimizarea functiei Lagrange:
In care constanta ½ este introdusa pentru simplificarea ecuatiei.
Rezolvarea functiei de minimizare se face prin sistemul de derivate partiale ale functiei Lagrange:
Sub forma matriciala, sistemul de ecuatii de mai sus se poate scrie astfel:
Solutiile cautate (x1, λ1, λ2)vor fi gasire prin rezolvarea sistemului matricial:
in care W este matricea inversata. Ecuatiile parametrice ce se vor construi din ultimul sistem matricial ne permit identificarea portofoliilor eficiente, pentru o speranta de rentabilitate scontata.
Aplicatia pentru estimarea portofoliului cu trei titluri este realizal in Visual Basic (aplicatie in Excel) Vom lua in considerare patru stari ale naturii : Avant economic, Crestere moderata, Stabilitate si Recesiune. Pentru valori ale probabilitatilor, corespunzatoare acestor stari, de 0.1, 0.4, 0.4, si respectiv 0.1, si pentru ricurile celor trei titluri: R1= 12, 8, 4, 0 R2= 8, 2, 2, 0 R3=8, 2.5, 5.5, 0 vom obtine urmatoarele valori , pentru PVMA, ale criteriului “speranta –dispersie”
In final vom obtine on tabel in care prin introducerea unor valori dorite ale sperantei de rentabilitate Ep vom putea lua o decizie asupra portofoliului. In cazul nostru, pentru Ep=3%, primul portofoliu este dominat de de multimea portofoliilor eficiente. La acelasi risc (σp=1.966),se poate obtine o rentabilitate superioara( intre 4% si 5%) Din multimea de portofolii eficiente, ultimul (pentru Ep=6%)este nelegitim (x2=-66,32%<0). Asta inseamna ca titlul doi este vandut la descoperire, iar banii incasati din aceasta operatiune sunt plasati in titlurile 1 si 3.
Codul sursa al aplicatiei
Private Sub tabelmaker_Click() tabel Worksheets("Calcule").Range("B5:B8").Name = "probabilitati" Worksheets("Calcule").Range("B5").FormulaR1C1 = CDbl(prob1.Text) Worksheets("Calcule").Range("B6").FormulaR1C1 = CDbl(prob2.Text) Worksheets("Calcule").Range("B7").FormulaR1C1 = CDbl(prob3.Text) Worksheets("Calcule").Range("B8").FormulaR1C1 = CDbl(prob4.Text) Worksheets("Calcule").Range("C5").FormulaR1C1 = CDbl(rent1.Text) Worksheets("Calcule").Range("C6").FormulaR1C1 = CDbl(rent2.Text) Worksheets("Calcule").Range("C7").FormulaR1C1 = CDbl(rent3.Text) Worksheets("Calcule").Range("C8").FormulaR1C1 = CDbl(rent4.Text) ' calcule Worksheets("Calcule").Range("C10").Select ActiveCell.FormulaR1C1 = "=AVERAGE(R[-5]C:R[-1]C)" ActiveCell.FormulaR1C1 = "=AVERAGE(R[-5]C:R[-2]C)" Worksheets("Calcule").Range("D5").Select ActiveCell.FormulaR1C1 = "=RC[-1]-R[5]C[-1]" Worksheets("Calcule").Range("D6").Select ActiveCell.FormulaR1C1 = "=RC[-1]-R[4]C[-1]" Worksheets("Calcule").Range("D7").Select ActiveCell.FormulaR1C1 = "=RC[-1]-R[3]C[-1]" Worksheets("Calcule").Range("D8").Select ActiveCell.FormulaR1C1 = "=RC[-1]-R[2]C[-1]" Worksheets("Calcule").Range("E5").Select ActiveCell.FormulaR1C1 = "=(POWER(RC[-1],2))*RC[-3]" Worksheets("Calcule").Range("E6").Select ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)" ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)*RC[-3]" Worksheets("Calcule").Range("E7").Select ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)" ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)*RC[-3]" Worksheets("Calcule").Range("E8").Select ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)" ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)*RC[-3]" ' ' Worksheets("Calcule").Range("F5").FormulaR1C1 = CDbl(rent21.Text) Worksheets("Calcule").Range("F6").FormulaR1C1 = CDbl(rent22.Text) Worksheets("Calcule").Range("F7").FormulaR1C1 = CDbl(rent23.Text) Worksheets("Calcule").Range("F8").FormulaR1C1 = CDbl(rent24.Text) ' calcule Worksheets("Calcule").Range("F10").Select ActiveCell.FormulaR1C1 = "=AVERAGE(R[-5]C:R[-1]C)" ActiveCell.FormulaR1C1 = "=AVERAGE(R[-5]C:R[-2]C)" Worksheets("Calcule").Range("G5").Select ActiveCell.FormulaR1C1 = "=RC[-1]-R[5]C[-1]" Worksheets("Calcule").Range("G6").Select ActiveCell.FormulaR1C1 = "=RC[-1]-R[4]C[-1]" Worksheets("Calcule").Range("G7").Select ActiveCell.FormulaR1C1 = "=RC[-1]-R[3]C[-1]" Worksheets("Calcule").Range("G8").Select ActiveCell.FormulaR1C1 = "=RC[-1]-R[2]C[-1]" Worksheets("Calcule").Range("H5").Select ActiveCell.FormulaR1C1 = "=(POWER(RC[-1],2))*RC[-6]" Worksheets("Calcule").Range("H6").Select ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)" ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)*RC[-6]" Worksheets("Calcule").Range("H7").Select ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)" ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)*RC[-6]" Worksheets("Calcule").Range("H8").Select ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)" ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)*RC[-6]" ' ' Worksheets("Calcule").Range("I5").FormulaR1C1 = CDbl(rent31.Text) Worksheets("Calcule").Range("I6").FormulaR1C1 = CDbl(rent32.Text) Worksheets("Calcule").Range("I7").FormulaR1C1 = CDbl(rent33.Text) Worksheets("Calcule").Range("I8").FormulaR1C1 = CDbl(rent34.Text) ' calcule Worksheets("Calcule").Range("I10").Select ActiveCell.FormulaR1C1 = "=AVERAGE(R[-5]C:R[-1]C)" ActiveCell.FormulaR1C1 = "=AVERAGE(R[-5]C:R[-2]C)" Worksheets("Calcule").Range("J5").Select ActiveCell.FormulaR1C1 = "=RC[-1]-R[5]C[-1]" Worksheets("Calcule").Range("J6").Select ActiveCell.FormulaR1C1 = "=RC[-1]-R[4]C[-1]" Worksheets("Calcule").Range("J7").Select ActiveCell.FormulaR1C1 = "=RC[-1]-R[3]C[-1]" Worksheets("Calcule").Range("J8").Select ActiveCell.FormulaR1C1 = "=RC[-1]-R[2]C[-1]" Worksheets("Calcule").Range("K5").Select ActiveCell.FormulaR1C1 = "=(POWER(RC[-1],2))*RC[-9]" Worksheets("Calcule").Range("K6").Select ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)" ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)*RC[-9]" Worksheets("Calcule").Range("K7").Select ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)" ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)*RC[-9]" Worksheets("Calcule").Range("K8").Select ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)" ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)*RC[-9]" ' ' Worksheets("Calcule").Range("E9").Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .ShrinkToFit = False .MergeCells = False End With Worksheets("Calcule").Range("H9").Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .ShrinkToFit = False .MergeCells = False End With ActiveWindow.SmallScroll ToRight:=1 Worksheets("Calcule").Range("K9").Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With Worksheets("Calcule").Range("H9").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With Worksheets("Calcule").Range("E9").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With Worksheets("Calcule").Range("E10").Select ActiveCell.FormulaR1C1 = "=SQRT(R[-1]C)" With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With Worksheets("Calcule").Range("H10").Select ActiveCell.FormulaR1C1 = "=SQRT(R[-1]C)" With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With Worksheets("Calcule").Range("K10").Select ActiveCell.FormulaR1C1 = "=SQRT(R[-1]C)" With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With ' ' Worksheets("Calcule").Range("L5").Select ActiveCell.FormulaR1C1 = "=RC[-8]*RC[-5]/RC[-10]*RC[-10]*R[1]C" Worksheets("Calcule").Range("L5").Select ActiveCell.FormulaR1C1 = "=RC[-8]*RC[-5]/RC[-10]*RC[-10]*RC[-10]" Worksheets("Calcule").Range("L6").Select ActiveCell.FormulaR1C1 = "=RC[-8]*RC[-5]/RC[-10]*RC[-10]*RC[-10]" Worksheets("Calcule").Range("L7").Select ActiveCell.FormulaR1C1 = "=RC[-8]*RC[-5]/RC[-10]*RC[-10]*RC[-10]" Worksheets("Calcule").Range("L8").Select ActiveCell.FormulaR1C1 = "=RC[-8]*RC[-5]/RC[-10]*RC[-10]*RC[-10]" Worksheets("Calcule").Range("L9").Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" ActiveWindow.SmallScroll ToRight:=4 Worksheets("Calcule").Range("M5").Select ActiveCell.FormulaR1C1 = "=RC[-9]*RC[-3]/RC[-11]*RC[-11]*RC[-11]" Worksheets("Calcule").Range("M6").Select ActiveCell.FormulaR1C1 = "=RC[-9]*RC[-3]/RC[-11]*RC[-11]*RC[-11]" Worksheets("Calcule").Range("M7").Select ActiveCell.FormulaR1C1 = "=RC[-9]*RC[-3]/RC[-11]*RC[-11]*RC[-11]" Worksheets("Calcule").Range("M8").Select ActiveCell.FormulaR1C1 = "=RC[-9]*RC[-3]/RC[-11]*RC[-11]*RC[-11]" Worksheets("Calcule").Range("M9").Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" ActiveWindow.SmallScroll ToRight:=1 Worksheets("Calcule").Range("N5").Select ActiveCell.FormulaR1C1 = "=RC[-7]*RC[-4]/RC[-12]*RC[-12]*RC[-12]" Worksheets("Calcule").Range("N6").Select ActiveCell.FormulaR1C1 = "=RC[-7]*RC[-4]/RC[-12]*RC[-12]*RC[-12]" Worksheets("Calcule").Range("N7").Select ActiveCell.FormulaR1C1 = "=RC[-7]*RC[-4]/RC[-12]*RC[-12]*RC[-12]" Worksheets("Calcule").Range("N8").Select ActiveCell.FormulaR1C1 = "=RC[-7]*RC[-4]/RC[-12]*RC[-12]*RC[-12]" Worksheets("Calcule").Range("N9").Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" ' ' Worksheets("Calcule").Range("L10").Select ActiveCell.FormulaR1C1 = "=R[-1]C/(RC[-7]*RC[-4])" Worksheets("Calcule").Range("M10").Select ActiveCell.FormulaR1C1 = "=R[-1]C/(RC[-8]*RC[-2])" Worksheets("Calcule").Range("N10").Select ActiveCell.FormulaR1C1 = "=R[-1]C/(RC[-6]*RC[-3])" Worksheets("Calcule").Range("A1").Select ActiveWindow.SmallScroll ToRight:=2 End Sub
Private Sub cont_Click()
continuare sist rezsis criteriu End Sub
Private Sub contin2_Click() continuare2 End Sub
Private Sub CommandButton1_Click() continuare3 matlagrange inversare End Sub
Private Sub CommandButton1_Click() continuare4 Worksheets("Calcule5").pvma.Text = CStr(Worksheets("Calcule5").Range("J108").Value) Worksheets("Calcule5").b.Text = CStr((Worksheets("Calcule5").Range("J111").Value) * 100) Worksheets("Calcule5").bb.Text = CStr((Worksheets("Calcule5").Range("J112").Value) * 100) Worksheets("Calcule5").bbb.Text = CStr((Worksheets("Calcule5").Range("J113").Value) * 100) End Sub
Private Sub ok1_Click() Worksheets("Calcule5").Range("I108").FormulaR1C1 = CDbl(ep1.Text) a.Text = CStr((Worksheets("Calcule5").Range("I111").Value) * 100) aa.Text = CStr((Worksheets("Calcule5").Range("I112").Value) * 100) aaa.Text = CStr((Worksheets("Calcule5").Range("I113").Value) * 100)
End Sub
Private Sub ok2_Click() pvma.Text = CStr(Worksheets("Calcule5").Range("J108").Value) b.Text = CStr((Worksheets("Calcule5").Range("J111").Value) * 100) bb.Text = CStr((Worksheets("Calcule5").Range("J112").Value) * 100) bbb.Text = CStr((Worksheets("Calcule5").Range("J113").Value) * 100) End Sub
Private Sub ok3_Click() Worksheets("Calcule5").Range("K108").FormulaR1C1 = CDbl(ep2.Text) c.Text = CStr((Worksheets("Calcule5").Range("J111").Value) * 100) cc.Text = CStr((Worksheets("Calcule5").Range("J112").Value) * 100) ccc.Text = CStr((Worksheets("Calcule5").Range("J113").Value) * 100) End Sub
Private Sub ok4_Click() Worksheets("Calcule5").Range("L108").FormulaR1C1 = CDbl(ep3.Text) d.Text = CStr((Worksheets("Calcule5").Range("L111").Value) * 100) dd.Text = CStr((Worksheets("Calcule5").Range("L112").Value) * 100) ddd.Text = CStr((Worksheets("Calcule5").Range("L113").Value) * 100) End Sub
Private Sub ok5_Click() Worksheets("Calcule5").Range("M108").FormulaR1C1 = CDbl(ep4.Text) e.Text = CStr((Worksheets("Calcule5").Range("M111").Value) * 100) ee.Text = CStr((Worksheets("Calcule5").Range("M112").Value) * 100) eee.Text = CStr((Worksheets("Calcule5").Range("M113").Value) * 100) End Sub
Private Sub Workbook_Open() start.Show Range("A1:T66").Select Selection.Interior.ColorIndex = 15 Range("A1").Select End Sub
Sub sist()
Range("E3").Select ActiveCell.FormulaR1C1 = "=Calcule!R[6]C-2*Calcule!R[6]C[8]+Calcule!R[6]C[6]" Range("E4").Select ActiveCell.FormulaR1C1 = _ "=Calcule!R[5]C[7]-Calcule!R[5]C[8]-Calcule!R[5]C[9]+Calcule!R[5]C[6]" Range("F3").Select ActiveCell.FormulaR1C1 = _ "=Calcule!R[6]C[6]-Calcule!R[6]C[7]-Calcule!R[6]C[8]+Calcule!R[6]C[5]" Range("F4").Select ActiveCell.FormulaR1C1 = _ "=Calcule!R[5]C[2]-2*Calcule!R[5]C[8]+Calcule!R[5]C[5]" Range("F4").Select ActiveCell.FormulaR1C1 = _ "=Calcule!R[5]C[2]-2*Calcule!R[5]C[8]+Calcule!R[5]C[5]" Range("E3:F4").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .ShrinkToFit = False .MergeCells = False End With Range("E3:E4").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Range("F3:F4").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Range("G3").Select ActiveCell.FormulaR1C1 = "X1" Range("G4").Select ActiveCell.FormulaR1C1 = "X2" Range("G3:G4").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .ShrinkToFit = False .MergeCells = False End With Range("G3:G4").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Range("I3").Select ActiveCell.FormulaR1C1 = "=-(Calcule!R[6]C[4]-Calcule!R[6]C[2])" Range("I4").Select ActiveCell.FormulaR1C1 = "=" Range("I4").Select Sheets("Calcule").Select Range("L9").Select Sheets("Calcule 2").Select Range("I4").Select ActiveCell.FormulaR1C1 = "=-(Calcule!R[5]C[5]-Calcule!R[5]C[2])" Range("I3:I4").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .ShrinkToFit = False .MergeCells = False End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Range("G3:G4").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Range("H4").Select End Sub
Sub criteriu()
Range("D19").Select ActiveCell.FormulaR1C1 = _ "=R[-6]C[-1]*Calcule!R[-9]C[-1]+'Calcule 2'!R[-5]C[-1]*Calcule!R[-9]C[2]+'Calcule 2'!R[-4]C[-1]*Calcule!R[-9]C[5]" Range("D19").Select Selection.NumberFormat = "0.000" With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .ShrinkToFit = False .MergeCells = False End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With With Selection.Font .Name = "Arial" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("D22").Select ActiveCell.FormulaR1C1 = "" ' Range("D21").Select 'ActiveCell.FormulaR1C1 = "=R[-8]C[-1]*R[-8]C[-1]*Calcule!R[-12]C[1]+R[-7]C[-1]*R[-7]C[-1]*Calcule!R[-12]C[4]+R[-6]C[-1]*R[-6]C[-1]*Calcule!R[-12]C[7]+2*R[-8]C[-1]*!R[-7]C[-1]*Calcule!R[-12]C[8] 2 " Range("D21").Select Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.NumberFormat = "0.000" With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .ShrinkToFit = False .MergeCells = False End With Range("D23").Select ActiveCell.FormulaR1C1 = "=SQRT(R[-2]C)" Selection.NumberFormat = "0.000" With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .ShrinkToFit = False .MergeCells = False End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End Sub
Sub continuare2() ' ' continuare2 Macro ' Macro recorded 5/15/00 by cristi '
' Sheets("Calcule3").Select
|
