Microsoft Office Excel ® Kod Kılavuzu

Microsoft Office Excel® Code Guide

10 Kasım 2012 Cumartesi

Earned Value Analysis (Kazanılmış Değer Analizi)

'Module1


Sub Earned_Value_Analysis() 'Kazanılmış Değer Analizi
On Error Resume Next
ActiveSheet.Unprotect
Cells.Delete Shift:=xlUp
Range("B2").FormulaR1C1 = "Earned Value Analysis (Kazanılmış Değer Analizi)"
Range("B3").FormulaR1C1 = "İş Dağılım Ağacı"
Range("B4").FormulaR1C1 = "Work Breakdown Structure"
Range("B5").FormulaR1C1 = "WBS"
Range("B6").FormulaR1C1 = ""
Range("B7").FormulaR1C1 = "'1"
Range("B8").FormulaR1C1 = "'1.1"
Range("B9").FormulaR1C1 = "'1.2"
Range("B10").FormulaR1C1 = "'2"
Range("B11").FormulaR1C1 = "Toplam"
Range("C3").FormulaR1C1 = "Toplam Bütçe Maliyeti"
Range("C4").FormulaR1C1 = "Budget (Total)"
Range("C5").FormulaR1C1 = "BAC"
Range("C6").FormulaR1C1 = "a"
Range("C7").FormulaR1C1 = "=R[2]C+R[1]C"
Range("C8").FormulaR1C1 = "120"
Range("C9").FormulaR1C1 = "120"
Range("C10").FormulaR1C1 = "120"
Range("C11").FormulaR1C1 = "=R[-1]C+R[-4]C"
Range("D3").FormulaR1C1 = "Döneme Ait Bütçe Maliyeti"
Range("D4").FormulaR1C1 = "Budget Cost Work Scheduled"
Range("D5").FormulaR1C1 = "BCWS"
Range("D6").FormulaR1C1 = "b"
Range("D7").FormulaR1C1 = "=R[2]C+R[1]C"
Range("D8").FormulaR1C1 = "100"
Range("D9").FormulaR1C1 = "100"
Range("D10").FormulaR1C1 = "120"
Range("D11").FormulaR1C1 = "=R[-1]C+R[-4]C"
Range("E3").FormulaR1C1 = "Tamamlanma Yüzdesi"
Range("E4").FormulaR1C1 = "Percentage of Completion"
Range("E5").FormulaR1C1 = "POC"
Range("E6").FormulaR1C1 = "c"
Range("E7").FormulaR1C1 = "=IF(RC[-1]=0,0,RC[1]/RC[-1])"
Range("E8").FormulaR1C1 = "50%"
Range("E9").FormulaR1C1 = "50%"
Range("E10").FormulaR1C1 = "100%"
Range("E11").FormulaR1C1 = "=IF(RC[-1]=0,0,RC[1]/RC[-1])"
Range("F3").FormulaR1C1 = "Bütçe Fiyatlarıyla Gerçekleşen Maliyet"
Range("F4").FormulaR1C1 = "Budget Cost Work Performed"
Range("F5").FormulaR1C1 = "BCWP"
Range("F6").FormulaR1C1 = "d=b*c"
Range("F7").FormulaR1C1 = "=R[1]C+R[2]C"
Range("F8").FormulaR1C1 = "=RC[-1]*RC[-2]"
Range("F9").FormulaR1C1 = "=RC[-1]*RC[-2]"
Range("F10").FormulaR1C1 = "=RC[-1]*RC[-2]"
Range("F11").FormulaR1C1 = "=R[-1]C+R[-4]C"
Range("G3").FormulaR1C1 = "Bütçe Fiyatlarıyla Dönem Maliyet farkı"
Range("G4").FormulaR1C1 = "Schedulet Variance"
Range("G5").FormulaR1C1 = "SV"
Range("G6").FormulaR1C1 = "e=d-b"
Range("G7").FormulaR1C1 = "=R[1]C+R[2]C"
Range("G8").FormulaR1C1 = "=RC[-1]-RC[-3]"
Range("G9").FormulaR1C1 = "=RC[-1]-RC[-3]"
Range("G10").FormulaR1C1 = "=RC[-1]-RC[-3]"
Range("G11").FormulaR1C1 = "=R[-1]C+R[-4]C"
Range("H3").FormulaR1C1 = "Fiili Fiyatlarıyla Gerçekleşen Maliyet"
Range("H4").FormulaR1C1 = "Actual Cost Work Performed"
Range("H5").FormulaR1C1 = "ACWS"
Range("H6").FormulaR1C1 = "f"
Range("H7").FormulaR1C1 = "=R[2]C+R[1]C"
Range("H8").FormulaR1C1 = "75"
Range("H9").FormulaR1C1 = "25"
Range("H10").FormulaR1C1 = "100"
Range("H11").FormulaR1C1 = "=R[-1]C+R[-4]C"
Range("I3").FormulaR1C1 = "Yapılan İşin (Bütçe-Fiili) Maliyet farkı"
Range("I4").FormulaR1C1 = "Cost Variance"
Range("I5").FormulaR1C1 = "CV"
Range("I6").FormulaR1C1 = "g=d-f"
Range("I7, I8, I9, I10, I11").FormulaR1C1 = "=RC[-3]-RC[-1]"
Range("J3").FormulaR1C1 = "Bütçe Fiyatlarıyla Döneme Ait Maliyet Gerçekleşme Oranı"
Range("J4").FormulaR1C1 = "Schedulet Performance Index"
Range("J5").FormulaR1C1 = "SPI"
Range("J6").FormulaR1C1 = "h=d/b"
Range("J7, J8, J9, J10, J11").FormulaR1C1 = "=IF(RC[-6]=0,0,RC[-4]/RC[-6])"
Range("K3").FormulaR1C1 = "Döneme Ait Maliyet Performans Endeksi"
Range("K4").FormulaR1C1 = "Cost Performance Index"
Range("K5").FormulaR1C1 = "CPI"
Range("K6").FormulaR1C1 = "i=d/f"
Range("K7, K8, K9, K10, K11").FormulaR1C1 = "=IF(RC[-3]=0,0,RC[-5]/RC[-3])"
Range("L3").FormulaR1C1 = "Gerçekleşen Harcama Oranı"
Range("L4").FormulaR1C1 = "Harcama Oranı"
Range("L5").FormulaR1C1 = "%Spent"
Range("L6").FormulaR1C1 = "j=f/a"
Range("L7, L8, L9, L10, L11").FormulaR1C1 = "=IF(RC[-9]=0,0,RC[-4]/RC[-9])"
Range("M3").FormulaR1C1 = "Bütçe Fiyatlarıyla Maliyet Tamamlanma Orani"
Range("M4").FormulaR1C1 = "Tamamlanma Oranı"
Range("M5").FormulaR1C1 = "%Complate"
Range("M6").FormulaR1C1 = "k=d/a"
Range("M7, M8, M9, M10, M11").FormulaR1C1 = "=IF(RC[-10]=0,0,RC[-7]/RC[-10])"
Range("N3").FormulaR1C1 = "Bütçe Fiyatlarıyla Kalan Maliyet"
Range("N4").FormulaR1C1 = "Remaind of Budget"
Range("N5").FormulaR1C1 = "RBAC"
Range("N6").FormulaR1C1 = "l=a-d"
Range("N7, N8, N9, N10, N11").FormulaR1C1 = "=RC[-11]-RC[-8]"
Range("O3").FormulaR1C1 = "Gerçekleşen Fiyatlarla Kalan Maliyet"
Range("O4").FormulaR1C1 = "Estimate to Complete"
Range("O5").FormulaR1C1 = "ETC"
Range("O6").FormulaR1C1 = "m=l/i"
Range("O7, O8, O9, O10, O11").FormulaR1C1 = "=IF(RC[-4]=0,0,RC[-1]/RC[-4])"
Range("P3").FormulaR1C1 = "Fiili Fiyatlarla Toplam Tahmini Maliyet"
Range("P4").FormulaR1C1 = "Estimate at Completion"
Range("P5").FormulaR1C1 = "EAC"
Range("P6").FormulaR1C1 = "n=f+m"
Range("P7, P8, P9, P10, P11").FormulaR1C1 = "=RC[-1]+RC[-8]"
Range("Q3").FormulaR1C1 = "Kalan İşe Ait Maliyet Performans Endeksi"
Range("Q4").FormulaR1C1 = "To Complete Performance Index (Verification Index)"
Range("Q5").FormulaR1C1 = "TCPI"
Range("Q6").FormulaR1C1 = "o=l/m"
Range("Q7, Q8, Q9, Q10, Q11").FormulaR1C1 = "=IF(RC[-2]=0,0,RC[-3]/RC[-2])"
Range("R3").FormulaR1C1 = "Gerçekleşen + Bakiye İşler İçin Hesaplanan Maliyet Farkı"
Range("R4").FormulaR1C1 = "Variance at Completion"
Range("R5").FormulaR1C1 = "VAC"
Range("R6").FormulaR1C1 = "p=n-a"
Range("R7, R8, R9, R10, R11").FormulaR1C1 = "=RC[-2]-RC[-15]"
Range("T2").FormulaR1C1 = "Sapma Analizi"
Range("T3").FormulaR1C1 = "Gerçekleşen + Bakiye İşler İçin Hesaplanan Maliyet Farkı"
Range("T4").FormulaR1C1 = "Variance at Completion"
Range("T5").FormulaR1C1 = "VAC"
Range("T6").FormulaR1C1 = "q=(d-f)/(d/a)" '"q=g/k"

Range("T7, T8, T9, T10, T11").FormulaR1C1 = "=IF((RC[-14]/RC[-17])=0,0,(RC[-14]-RC[-12])/(RC[-14]/RC[-17]))" '"=IF(RC[-7]=0,0,RC[-11]/RC[-7])"
Range("U3").FormulaR1C1 = "Fiili Fiyatlarla Toplam Tahmini Maliyet"
Range("U4").FormulaR1C1 = "Estimate at Completion"
Range("U5").FormulaR1C1 = "EAC"
Range("U6").FormulaR1C1 = "r=a-q"
Range("U7, U8, U9, U10, U11").FormulaR1C1 = "=RC[-18]-RC[-1]"
With Range("B2:R2,T2:U2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = True
.Merge
End With
With Range("B3:R4,T3:U4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B2:R6, T2:U6, B11:R11, T11:U11").Font.Bold = True
Range("C7:D11, F7:I11, N7:P11, R7:R11, T7:U11, Q7:Q11").NumberFormat = "[$$-409]#,##0.00"
Range("J7:M11, E7:E11").NumberFormat = "0.0%"
With Range("B5:B6")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
.Merge
End With
With Range("C5:R6, T5:U6")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Call Draw_Line("B2:R11, T2:U11")
Call Draw_Line("B2:R2, T2:U2")
Call Draw_Line("B7:R11, T7:U11")
Call Draw_Line("B11:R11, T11:U11")
Range("B:R, T:U").ColumnWidth = 10
Range("S:S").ColumnWidth = 1
Range("B3:R11, T3:U11").Font.Size = 8
With Range("B2:R2, T2:U2")
.Font.Size = 12
.Interior.ColorIndex = 35
End With
With Range("C8:E10, H8:H10")
.Font.ColorIndex = 32
.Locked = False
End With
Range("T2").Select
ActiveSheet.Protect
End Sub
Private Sub Draw_Line(hRange As String) 'Çizgi çiz
On Error Resume Next
With Range(hRange)
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
End With
End Sub

Hiç yorum yok:

Blog Arşivi

Bu gadget'ta bir hata oluştu

Bu Blogda Ara

Contributor

Contributor
Mustafa ULUSARAÇ İstanbul, TÜRKİYE
free counters
T. C. Central Bank Indicative Exchange Rates
Currency Exchange Rate Widget,Currency Converter Widget
Borsa İstanbul