Excel VBA高效办公应用-第七章-VBA财务报表分析-Part2 （资产负债表分析）

资产负债表：

一些指标：

代码如下：

Public Sub 资产负债表计算()
    '选择“资产负债表”
    Sheets("资产负债表").Select
    '计算“流动资产合计”
    Cells(14, 3).Value = "=sum(C5:C13)"
    Cells(14, 3) = Cells(14, 3) - 2 * Cells(8, 3)
    '计算“固定资产净额”
    Cells(19, 3) = Cells(16, 3) - Cells(17, 3) - Cells(18, 3)
    Cells(23, 3) = "=sum(C19:C22)"
    '计算“固定资产合计”
    Cells(29, 3) = "=sum(C25:C28)"
    '计算“资产总计”
    Cells(30, 3) = Cells(14, 3) + Cells(23, 3) + Cells(29, 3)
    '计算“流动负债合计”
    Cells(15, 6) = "=sum(F5:F14)"
    '计算“长期负债合计”
    Cells(22, 6) = "=sum(F17:F21)"
    '计算“负债合计”
    Cells(23, 6) = Cells(22, 6)
    '计算“股东权益合计”
    Cells(29, 6) = "=sum(F25:F28)"
    '计算“负债及股东权益总计”
    Cells(30, 6) = Cells(15, 6) + Cells(23, 6) + Cells(29, 6)
    '判断是否平等，并输出相应的信息
    If Cells(30, 3) = Cells(30, 6) Then
        MsgBox "资产与负债数字平等!", vbOKOnly, "确定"
    Else
        MsgBox "资产与负债数字不平等" & Chr(10) & Chr(13) _
                & "资产与负债的差额是：" & Cells(30, 3) - Cells(30, 6) _
                & Chr(10) & Chr(13) & "请重新核对!", vbOKOnly, "确定"
    End If
End Sub

Public Sub 资产负债表总汇分析()
'定义一个保存记录列数的整型变量
Dim iCount As Integer
    '把工作表的记录列数赋予iCount
    iCount = Sheets("资产负债表总汇").[A3].CurrentRegion.Columns.Count
    '选择“损益统计总汇”工作表
    Sheets("资产负债表总汇").Select
    With Worksheets("资产负债表")
        '把“资产负债表”工作表中的“年份值”复制到“资产负债表总汇”工作表中
        Cells(2, iCount + 1) = .Cells(2, 2).Value
        Cells(3, iCount + 1) = .Cells(7, 3).Value
        Cells(4, iCount + 1) = .Cells(14, 3).Value
        Cells(5, iCount + 1) = .Cells(19, 3).Value
        Cells(6, iCount + 1) = .Cells(23, 3).Value
        Cells(7, iCount + 1) = .Cells(29, 3).Value
        Cells(8, iCount + 1) = .Cells(30, 3).Value
        Cells(9, iCount + 1) = .Cells(15, 6).Value
        Cells(10, iCount + 1) = .Cells(22, 6).Value
        Cells(11, iCount + 1) = .Cells(23, 6).Value
        Cells(12, iCount + 1) = .Cells(29, 6).Value
        Cells(13, iCount + 1) = .Cells(30, 6).Value
        '计算“流动比率”
        Cells(16, iCount + 1) = .Cells(14, 3) / .Cells(15, 6)
        '计算“速动比率”
        Cells(17, iCount + 1) = (.Cells(14, 3) - .Cells(11, 3) - .Cells(12, 3)) / .Cells(15, 6)
        '计算“资产负债率”
        Cells(19, iCount + 1) = .Cells(23, 6) / .Cells(30, 3)
        '计算“产权比率”
        Cells(20, iCount + 1) = .Cells(23, 6) / .Cells(29, 6)
        '计算“总资产周转率”
        Cells(22, iCount + 1) = 2 * Worksheets("损益表").Cells(5, 3) / (.Cells(30, 3) + Cells(8, iCount))
        '计算“流动资产周转率”
        Cells(23, iCount + 1) = 2 * Worksheets("损益表").Cells(5, 3) / (.Cells(14, 3) + Cells(4, iCount))
        '计算“应收账款周转率”
        Cells(24, iCount + 1) = 2 * Worksheets("损益表").Cells(5, 3) / (.Cells(7, 3) + Cells(3, iCount))
        '计算“资产净利率”
        Cells(26, iCount + 1) = 2 * Worksheets("损益表").Cells(20, 3) / (.Cells(30, 3) + Cells(8, iCount))
        '计算“权益报酬率”
        Cells(27, iCount + 1) = Worksheets("损益表").Cells(20, 3) / .Cells(29, 6)
    End With
End Sub

后面这几个指标有点陌生，有空学习学习！