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 
    Dim WS As Worksheet
    Application.DisplayAlerts = False
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name = "EarnValue" Then WS.Delete
    Next WS
    With ThisWorkbook.Worksheets.Add
        .Name = "EarnValue"
    End With
    ActiveSheet.Unprotect
    Range("B3").FormulaR1C1 = "a"
    Range("B4").FormulaR1C1 = "b"
    Range("B5").FormulaR1C1 = "c"
    Range("B6").FormulaR1C1 = "d"
    Range("B7").FormulaR1C1 = "e"
    Range("B8").FormulaR1C1 = "f"
    Range("B9").FormulaR1C1 = "g"
    Range("B10").FormulaR1C1 = "h"
    Range("B11").FormulaR1C1 = "i"
    Range("B12").FormulaR1C1 = "j"
    Range("B13").FormulaR1C1 = "k"
    Range("B14").FormulaR1C1 = "l"
    Range("B15").FormulaR1C1 = "m"
    Range("B16").FormulaR1C1 = "n"
    Range("B17").FormulaR1C1 = "o"
    Range("B18").FormulaR1C1 = "p"
    Range("B19").FormulaR1C1 = "q"
    Range("B20").FormulaR1C1 = "r"
    Range("B21").FormulaR1C1 = "s"
    Range("B22").FormulaR1C1 = "s"
    Range("B23").FormulaR1C1 = "t"
    Range("B24").FormulaR1C1 = "u"
    Range("B25").FormulaR1C1 = "v"
    Range("B26").FormulaR1C1 = "w"
    Range("B27").FormulaR1C1 = "x"
    Range("B28").FormulaR1C1 = "y"
    Range("B29").FormulaR1C1 = "z"
    Range("B30").FormulaR1C1 = "aa"
    Range("B31").FormulaR1C1 = "ab"
    Range("C3").FormulaR1C1 = "Budget at Completion"
    Range("C4").FormulaR1C1 = "Planned Value [BCWS: Budget Cost of Work Scheduled]"
    Range("C5").FormulaR1C1 = "Actual Cost [ACWP: Actual Cost of Work Performed]"
    Range("C6").FormulaR1C1 = "Cost Spending Varians [c-b]"
    Range("C7").FormulaR1C1 = "Percentage Complete - Planning [b/a]"
    Range("C8").FormulaR1C1 = "Percentage Complete - Actual"
    Range("C9").FormulaR1C1 = "Percentage Complete Varians [f-e]"
    Range("C10").FormulaR1C1 = "Schedule Variance - Actual [(e-f)*(c/f)]"
    Range("C11").FormulaR1C1 = "Cost Variance - Actual [((b/e)-(c/f))*f]"
    Range("C12").FormulaR1C1 = "Schedule Variance - Actual [((b/e)-(c/f))*(e-f)]"
    Range("C13").FormulaR1C1 = "Variance at Completion - Actual Spending [h+i-j]"
    Range("C14").FormulaR1C1 = "Variance at Completion - Actual [i-j]"
    Range("C15").FormulaR1C1 = "Schedule Variance - Remind [((1-e)-(1-f))*(c/f)]"
    Range("C16").FormulaR1C1 = "Cost Variance - Remind [((b/e)-(c/f))*(1-f)]"
    Range("C17").FormulaR1C1 = "Schedule Variance - Remind [((1-e)-(1-f))*((b/e)-(c/f))]"
    Range("C18").FormulaR1C1 = "Variance at Completion - Remind Spending [m+n-o]"
    Range("C19").FormulaR1C1 = "Variance at Completion - Remind [n-o]"
    Range("C20").FormulaR1C1 = "Variance at Completion [l+q]"
    Range("C21").FormulaR1C1 = "Estimate at Completion [a-r] veya [c/f]"
    Range("C22").FormulaR1C1 = "Estimate to Completion [a-r] veya [s-c]"
    Range("C23").FormulaR1C1 = "Cost Performance Index [(c+i)/c]"
    Range("C24").FormulaR1C1 = "To Complete Performance Index [(a-(b-(h+j)))/(a-c)]"
    Range("C25").FormulaR1C1 = "Schedule Performance Index [(c+i)/b]"
    Range("C26").FormulaR1C1 = "Cost Schedule Index [SCI: Schedule Cost Index] [t*v]"
    Range("C27").FormulaR1C1 = "Earn Value - Actual [BCWP: Budget Cost of Work Performed] [c+i]"
    Range("C28").FormulaR1C1 = "Earn Value - Completion[BCWP: Budget Cost of Work Performed] [c+i+n]"
    Range("C29").FormulaR1C1 = "Independent Estimate at Completion [a/t]"
    Range("C30").FormulaR1C1 = "Standart Hour to Per Production"
    Range("C31").FormulaR1C1 = "Independent Schedule at Completion [aa/v]"
    Range("D3").FormulaR1C1 = "BAC"
    Range("D4").FormulaR1C1 = "PV"
    Range("D5").FormulaR1C1 = "AC"
    Range("D6").FormulaR1C1 = "CSV"
    Range("D7").FormulaR1C1 = "PCp"
    Range("D8").FormulaR1C1 = "PCa"
    Range("D9").FormulaR1C1 = "PCV"
    Range("D10").FormulaR1C1 = "SVa1"
    Range("D11").FormulaR1C1 = "CVa"
    Range("D12").FormulaR1C1 = "SVa2"
    Range("D13").FormulaR1C1 = "VACas"
    Range("D14").FormulaR1C1 = "VACa"
    Range("D15").FormulaR1C1 = "SVr1"
    Range("D16").FormulaR1C1 = "CVr"
    Range("D17").FormulaR1C1 = "SVr2"
    Range("D18").FormulaR1C1 = "VACrs"
    Range("D19").FormulaR1C1 = "VACr"
    Range("D20").FormulaR1C1 = "VAC"
    Range("D21").FormulaR1C1 = "EAC"
    Range("D22").FormulaR1C1 = "ETC"
    Range("D23").FormulaR1C1 = "CPI"
    Range("D24").FormulaR1C1 = "TCPI"
    Range("D25").FormulaR1C1 = "SPI"
    Range("D26").FormulaR1C1 = "CSI"
    Range("D27").FormulaR1C1 = "Eva"
    Range("D28").FormulaR1C1 = "Evc"
    Range("D29").FormulaR1C1 = "IEAC"
    Range("D30").FormulaR1C1 = "SHPP"
    Range("D31").FormulaR1C1 = "ISAC"
    Range("E3").FormulaR1C1 = "100000"
    Range("E4").FormulaR1C1 = "60000"
    Range("E5").FormulaR1C1 = "65000"
    Range("E6").FormulaR1C1 = "=R[-1]C-R[-2]C"
    Range("E7").FormulaR1C1 = "=R[-3]C/R[-4]C"
    Range("E8").FormulaR1C1 = "70%"
    Range("E9").FormulaR1C1 = "=R[-1]C-R[-2]C"
    Range("E10").FormulaR1C1 = "=(R[-3]C-R[-2]C)*(R[-5]C/R[-2]C)"
    Range("E11").FormulaR1C1 = "=((R[-7]C/R[-4]C)-(R[-6]C/R[-3]C))*R[-3]C"
    Range("E12").FormulaR1C1 = "=((R[-8]C/R[-5]C)-(R[-7]C/R[-4]C))*(R[-5]C-R[-4]C)"
    Range("E13").FormulaR1C1 = "=R[-3]C+R[-2]C-R[-1]C"
    Range("E14").FormulaR1C1 = "=R[-3]C-R[-2]C"
    Range("E15").FormulaR1C1 = "=((1-R[-8]C)-(1-R[-7]C))*(R[-10]C/R[-7]C)"
    Range("E16").FormulaR1C1 = "=((R[-12]C/R[-9]C)-(R[-11]C/R[-8]C))*(1-R[-8]C)"
    Range("E17").FormulaR1C1 = "=((1-R[-10]C)-(1-R[-9]C))*((R[-13]C/R[-10]C)-(R[-12]C/R[-9]C))"
    Range("E18").FormulaR1C1 = "=R[-3]C+R[-2]C-R[-1]C"
    Range("E19").FormulaR1C1 = "=R[-3]C-R[-2]C"
    Range("E20").FormulaR1C1 = "=R[-6]C+R[-1]C"
    Range("E21").FormulaR1C1 = "=R[-18]C-R[-1]C"
    Range("E22").FormulaR1C1 = "=R[-1]C-R[-17]C"
    Range("E23").FormulaR1C1 = "=(R[-18]C+R[-12]C)/R[-18]C"
    Range("E24").FormulaR1C1 = "=(R[-21]C-(R[-20]C-(R[-14]C+R[-12]C)))/(R[-21]C-R[-19]C)"
    Range("E25").FormulaR1C1 = "=(R[-20]C+R[-14]C)/R[-21]C"
    Range("E26").FormulaR1C1 = "=R[-1]C*R[-3]C"
    Range("E27").FormulaR1C1 = "=R[-22]C+R[-16]C"
    Range("E28").FormulaR1C1 = "=R[-23]C+R[-17]C+R[-12]C"
    Range("E29").FormulaR1C1 = "=R[-26]C/R[-6]C"
    Range("E30").FormulaR1C1 = "5"
    Range("E31").FormulaR1C1 = "=R[-1]C/R[-6]C"
    Range("B2:E2").FormulaR1C1 = "EARN VALUE (Kazanılmış Değer) ANALYSIS"
    With Range("B2:E2")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    With Range("B2:E31")
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .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
    End With
    With Range("B3:B31")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Range("D3:D31")
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("E7,E8,E9,E23,E24,E25,E26").NumberFormat = "0.00%"
    With Range("E3,E4,E5,E8,E30")
        With .Font
            .Name = "Arial"
            .FontStyle = "Normal"
            .Size = 12
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 32
        End With
        .Locked = False
        .FormulaHidden = False
    End With
    Columns("B:B").ColumnWidth = 3
    Columns("C:C").ColumnWidth = 74
    Columns("D:D").ColumnWidth = 8
    Columns("E:E").ColumnWidth = 16
    With Range("B2:E31").Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
    End With
    With Range("B2:E2").Font
        .Name = "Arial"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("E3,E4,E5,E6,E10,E11,E12,E13,E14,E15,E16,E17,E18,E19,E20,E21,E22,E27,E28,E29").NumberFormat = "#,##0.00 $"
    Range("E30,E31").NumberFormat = "#,##0.00 \h"
    ActiveSheet.Protect

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