Top > 財務管理> 合計残高試算表

VBA通信教材申込み

VBA課題解決型実践塾セミナー内容

申込 ヤフーショッピング

合計残高試算表

(5)合計残高試算表

合計残高試算表を作成したい期間を入力

合計残高試算表

科目表

合計残高試算表

合計残高表

合計残高試算表

合計残高表作成のプログラムの流れ

合計残高試算表

  Option Explicit
  Private zandaka As Long
  Private lastrow As Long
  Private lastrow1 As Long
  Private i As Long
  Private j As Long
  Private kcode As Long
  Private kei As Long
  Private kkubun As String
  Private kisyu As String
  Private kaisi As String
  Private syuuryou As String
  Private Sub txtkaisi_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
      If Len(txtkaisi.Text) <> 10 Then
        MsgBox "桁数は10桁です(2012/01/01) "
        Exit Sub
      End If
    End If
  End Sub
  Private Sub txtsyuuryou_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
      If Len(txtsyuuryou.Text) <> 10 Then
        MsgBox "桁数は10桁です(2012/01/01) "
        Exit Sub
      End If
    End If
  End Sub
  Private Sub cmdJikkou_Click()
'計算期間を記録
    Worksheets("科目表").Cells(2, 12) = txtkaisi.Text
    Worksheets("科目表").Cells(2, 13) = txtsyuuryou.Text
'科目表の期間前残高・借方金額・貸方金額・残高をクリア
    Call kamokuclear
'作業・作業1をクリア
    Call sagyouclear
'期首残高を期間前残高に更新
    lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
      Worksheets("科目表").Cells(i, 6) = Worksheets("科目表").Cells(i, 4)
    Next
'開始年月日が期首年月日の場合かどうか
    If txtkaisi.Text = Worksheets("メニュー").Cells(2, 2) Then
'借方の更新
      Call keisanr
'作業・作業1をクリア
      Call sagyouclear
'貸方の更新
      Call keisans
'残高計算
      Call zandakakeisan
    Else
'期首~開始日まえの計算
'借方の更新
      Call keisanrt
'作業・作業1をクリア
      Call sagyouclear
'貸方の更新
      Call keisanst
'残高計算
      Call zandakakeisan
'開始日~終了日の計算
'残高を期間前残高に更新
      lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
      For i = 2 To lastrow
        Worksheets("科目表").Cells(i, 6) = Worksheets("科目表").Cells(i, 9)
      Next
'科目表の借方金額・貸方金額・残高をクリア
      Call kamokuclear1
'作業・作業1をクリア
      Call sagyouclear
'借方の更新
      Call keisanr
'作業・作業1をクリア
      Call sagyouclear
'貸方の更新
      Call keisans
'残高計算
      Call zandakakeisan
    End If
    Unload Me
    Worksheets("科目表").Activate
  End Sub
  Private Sub kamokuclear()
'科目表の期間前残高・借方金額・貸方金額・残高をクリア
    lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
      For j = 6 To 9
        Worksheets("科目表").Cells(i, j) = ""
      Next
    Next
  End Sub
  Private Sub sagyouclear()
'作業・作業1をクリア
    Worksheets("作業").Cells.Clear
    Worksheets("作業").Cells(1, 1) = "科目コード"
    Worksheets("作業").Cells(1, 2) = "金額"
    Worksheets("作業1").Cells.Clear
    Worksheets("作業1").Cells(1, 1) = "科目コード"
    Worksheets("作業1").Cells(1, 2) = "金額"
  End Sub
  Private Sub kamokuclear1()
'科目表の借方金額・貸方金額・残高をクリア
    lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
      For j = 7 To 9
        Worksheets("科目表").Cells(i, j) = ""
      Next
    Next
  End Sub
  Private Sub keisanr()
'仕訳帳から期間のデータを取り出す(開始日=期首~終了日)
    Call kikantoridasi(2, 4)
'コードで並び替える
    Call narabikae
'同じデータを集約した別の表を作成
    Call hyousakusei
'作業1の金額を科目シートに更新する
    Call kamokukousin(7)
  End Sub
  Private Sub keisans()
'仕訳帳から期間のデータを取り出す(開始日=期首~終了日)
    Call kikantoridasi(5, 7)
'コードで並び替える
    Call narabikae
'同じデータを集約した別の表を作成
    Call hyousakusei
'作業1の金額を科目シートに更新する
    Call kamokukousin(8)
  End Sub
  Private Sub keisanrt()
'仕訳帳から期間のデータを取り出す(期首~開始日まえ)
    Call kikantoridasit(2, 4)
'コードで並び替える
    Call narabikae
'同じデータを集約した別の表を作成
    Call hyousakusei
'作業1の金額を科目シートに更新する
    Call kamokukousin(7)
  End Sub
  Private Sub keisanst()
'仕訳帳から期間のデータを取り出す
    Call kikantoridasit(5, 7)
'コードで並び替える
    Call narabikae
'同じデータを集約した別の表を作成
    Call hyousakusei
'作業1の金額を科目シートに更新する
    Call kamokukousin(8)
  End Sub
  Private Sub kikantoridasi(x As Long, y As Long)
'仕訳帳から期間のデータを取り出す(開始日=期首~終了日)借方
    Worksheets("仕訳帳").Activate
    lastrow = Worksheets("仕訳帳").Cells(Rows.Count, 1).End(xlUp).Row
    j = 2
    For i = 2 To lastrow
      If Worksheets("仕訳帳").Cells(i, 1) >= txtkaisi.Text And Worksheets("仕訳帳").Cells(i, 1) <= txtsyuuryou.Text Then
        Worksheets("作業").Cells(j, 1) = Worksheets("仕訳帳").Cells(i, x)
        Worksheets("作業").Cells(j, 2) = Worksheets("仕訳帳").Cells(i, y)
        j = j + 1
      End If
    Next
  End Sub
  Private Sub kikantoridasit(x As Long, y As Long)
'仕訳帳から期間のデータを取り出す(期首~開始日まえ)借方
    kisyu = Worksheets("メニュー").Cells(2, 2)
    kaisi = txtkaisi.Text
    syuuryou = txtsyuuryou.Text
    Worksheets("仕訳帳").Activate
    lastrow = Worksheets("仕訳帳").Cells(Rows.Count, 1).End(xlUp).Row
    j = 2
    For i = 2 To lastrow
      If Worksheets("仕訳帳").Cells(i, 1) >= kisyu And Worksheets("仕訳帳").Cells(i, 1) < kaisi Then
        Worksheets("作業").Cells(j, 1) = Worksheets("仕訳帳").Cells(i, x)
        Worksheets("作業").Cells(j, 2) = Worksheets("仕訳帳").Cells(i, y)
        j = j + 1
      End If
    Next
  End Sub
  Private Sub narabikae()
'コードで並び替える
    lastrow = Worksheets("作業").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("作業").Activate
    Range(Cells(2, 1), Cells(lastrow, 2)).Select
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Add Key:=Cells(2, 1), SortOn _
    :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("作業").Sort
      .SetRange Range(Cells(2, 1), Cells(lastrow, 2))
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
  End Sub
  Private Sub hyousakusei()
'同じデータを集約した別の表を作成
    j = 2
    kei = 0
    For i = 2 To lastrow
      kei = kei + Worksheets("作業").Cells(i, 2)
      If Worksheets("作業").Cells(i, 1) <> Worksheets("作業").Cells(i + 1, 1) Then
        Worksheets("作業1").Cells(j, 1) = Worksheets("作業").Cells(i, 1)
        Worksheets("作業1").Cells(j, 2) = kei
        j = j + 1
        kei = 0
      End If
    Next
  End Sub
  Private Sub kamokukousin(x As Long)
'作業1の金額を科目シートに更新する
    Worksheets("作業1").Activate
    lastrow = Worksheets("作業1").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("科目表").Activate
    lastrow1 = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
      kcode = Worksheets("作業1").Cells(i, 1)
      For j = 2 To lastrow1
        If Worksheets("科目表").Cells(j, 1) = kcode Then
          Worksheets("科目表").Cells(j, x) = Worksheets("作業1").Cells(i, 2)
          Exit For
        End If
      Next
      j = 2
    Next
End Sub
  Private Sub zandakakeisan()
'残高計算
    Worksheets("科目表").Activate
    lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
      kkubun = kamokukubunc(Worksheets("科目表").Cells(i, 1))
      If kkubun = "流動資産" Or kkubun = "固定資産" Or kkubun = "仕入" Or kkubun = "販売管理費" Or kkubun = "営業外費用" Then
        Worksheets("科目表").Cells(i, 9) = Worksheets("科目表").Cells(i, 6) + Worksheets("科目表").Cells(i, 7) - Worksheets("科目表").Cells(i, 8)
      Else
        Worksheets("科目表").Cells(i, 9) = Worksheets("科目表").Cells(i, 6) + Worksheets("科目表").Cells(i, 8) - Worksheets("科目表").Cells(i, 7)
      End If
    Next
  End Sub
  Private Sub cmdCancel_Click()
    Unload Me
  End Sub
  Private Sub cmdkisyu_Click()
    txtkaisi.Text = Worksheets("メニュー").Cells(2, 2)
  End Sub
標準モジュールに記入
  Function kamokukubunc(kcode As Long) As String
    Dim lastrow As Long
    Dim i As Long
    lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
      If kcode = Worksheets("科目表").Cells(i, 1) Then
        kamokukubunc = Worksheets("科目表").Cells(i, 5)
        Exit Function
      End If
    Next
    kamokukubunc = ""
  End Function
  Sub 合計残高試算表()
    goukeizandaka.Show
    Call 合計残高
  End Sub

科目表から合計残高表へ科目区分で集計しながら転記

科目表の区分が変わるタイミングで区分計を合計残高表に転記している。
  Sub 合計残高()
    Dim lastrow As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim 流動資産(3) As Long
    Dim 固定資産(3) As Long
    Dim 流動負債(3) As Long
    Dim 資本(3) As Long
    Dim 売上(3) As Long
    Dim 仕入(3) As Long
    Dim 販売管理費(3) As Long
    Dim 営業外収益(3) As Long
    Dim 営業外費用(3) As Long
'計算期間を記録
    Worksheets("合計残高").Cells(2, 9) = Worksheets("科目表").Cells(2, 12)
    Worksheets("合計残高").Cells(2, 10) = Worksheets("科目表").Cells(2, 13)
'合計残高のクリア
    lastrow = Worksheets("合計残高").Cells(Rows.Count, 2).End(xlUp).Row
    For i = 2 To lastrow
      For j = 1 To 6
        Worksheets("合計残高").Cells(i, j) = ""
      Next
    Next
'科目表から合計残高へ移行
    lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
    j = 2
    For i = 2 To lastrow
      If Worksheets("科目表").Cells(i, 5) = Worksheets("科目表").Cells(i + 1, 5) Then
        Worksheets("合計残高").Cells(j, 1) = Worksheets("科目表").Cells(i, 1)
        Worksheets("合計残高").Cells(j, 2) = Worksheets("科目表").Cells(i, 2)
        Worksheets("合計残高").Cells(j, 3) = Worksheets("科目表").Cells(i, 6)
        Worksheets("合計残高").Cells(j, 4) = Worksheets("科目表").Cells(i, 7)
        Worksheets("合計残高").Cells(j, 5) = Worksheets("科目表").Cells(i, 8)
        Worksheets("合計残高").Cells(j, 6) = Worksheets("科目表").Cells(i, 9)
        Select Case Worksheets("科目表").Cells(i, 5)
          Case "流動資産"
            For k = 0 To 3
              流動資産(k) = 流動資産(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "固定資産"
            For k = 0 To 3
              固定資産(k) = 固定資産(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "流動負債"
            For k = 0 To 3
              流動負債(k) = 流動負債(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "資本"
            For k = 0 To 3
              資本(k) = 資本(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "売上"
            For k = 0 To 3
              売上(k) = 売上(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "仕入"
            For k = 0 To 3
              仕入(k) = 仕入(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "販売管理費"
            For k = 0 To 3
              販売管理費(k) = 販売管理費(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "営業外収益"
            For k = 0 To 3
              営業外収益(k) = 営業外収益(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "営業外費用"
            For k = 0 To 3
              営業外費用(k) = 営業外費用(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
        End Select
        j = j + 1
      Else
        Worksheets("合計残高").Cells(j, 1) = Worksheets("科目表").Cells(i, 1)
        Worksheets("合計残高").Cells(j, 2) = Worksheets("科目表").Cells(i, 2)
        Worksheets("合計残高").Cells(j, 3) = Worksheets("科目表").Cells(i, 6)
        Worksheets("合計残高").Cells(j, 4) = Worksheets("科目表").Cells(i, 7)
        Worksheets("合計残高").Cells(j, 5) = Worksheets("科目表").Cells(i, 8)
        Worksheets("合計残高").Cells(j, 6) = Worksheets("科目表").Cells(i, 9)
        Select Case Worksheets("科目表").Cells(i, 5)
          Case "流動資産"
            For k = 0 To 3
              流動資産(k) = 流動資産(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "固定資産"
            For k = 0 To 3
              固定資産(k) = 固定資産(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "流動負債"
            For k = 0 To 3
              流動負債(k) = 流動負債(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "資本"
            For k = 0 To 3
              資本(k) = 資本(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "売上"
            For k = 0 To 3
              売上(k) = 売上(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "仕入"
            For k = 0 To 3
              仕入(k) = 仕入(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "販売管理費"
            For k = 0 To 3
              販売管理費(k) = 販売管理費(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "営業外収益"
            For k = 0 To 3
              営業外収益(k) = 営業外収益(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
          Case "営業外費用"
            For k = 0 To 3
              営業外費用(k) = 営業外費用(k) + Worksheets("科目表").Cells(i, 6 + k)
            Next
        End Select
        j = j + 1
'合計をコピー
        Select Case Worksheets("科目表").Cells(i, 5)
          Case "流動資産"
            Worksheets("合計残高").Cells(j, 2) = "<流動資産計>"
            Worksheets("合計残高").Cells(j, 3) = 流動資産(0)
            Worksheets("合計残高").Cells(j, 4) = 流動資産(1)
            Worksheets("合計残高").Cells(j, 5) = 流動資産(2)
            Worksheets("合計残高").Cells(j, 6) = 流動資産(3)
          Case "固定資産"
            Worksheets("合計残高").Cells(j, 2) = "<固定資産計>"
            Worksheets("合計残高").Cells(j, 3) = 固定資産(0)
            Worksheets("合計残高").Cells(j, 4) = 固定資産(1)
            Worksheets("合計残高").Cells(j, 5) = 固定資産(2)
            Worksheets("合計残高").Cells(j, 6) = 固定資産(3)
            j = j + 1
            Worksheets("合計残高").Cells(j, 2) = "《資産の部》"
            Worksheets("合計残高").Cells(j, 3) = 流動資産(0) + 固定資産(0)
            Worksheets("合計残高").Cells(j, 4) = 流動資産(1) + 固定資産(1)
            Worksheets("合計残高").Cells(j, 5) = 流動資産(2) + 固定資産(2)
            Worksheets("合計残高").Cells(j, 6) = 流動資産(3) + 固定資産(3)
          Case "流動負債"
            Worksheets("合計残高").Cells(j, 2) = "<流動負債計>"
            Worksheets("合計残高").Cells(j, 3) = 流動負債(0)
            Worksheets("合計残高").Cells(j, 4) = 流動負債(1)
            Worksheets("合計残高").Cells(j, 5) = 流動負債(2)
            Worksheets("合計残高").Cells(j, 6) = 流動負債(3)
          Case "資本"
            Worksheets("合計残高").Cells(j, 2) = "<資本計>"
            Worksheets("合計残高").Cells(j, 3) = 資本(0)
            Worksheets("合計残高").Cells(j, 4) = 資本(1)
            Worksheets("合計残高").Cells(j, 5) = 資本(2)
            Worksheets("合計残高").Cells(j, 6) = 資本(3)
            j = j + 1
            Worksheets("合計残高").Cells(j, 2) = "《当期利益》"
            Worksheets("合計残高").Cells(j, 3) = 流動資産(0) + 固定資産(0) - 流動負債(0) - 資本(0)
            Worksheets("合計残高").Cells(j, 4) = 流動資産(1) + 固定資産(1) - 流動負債(1) - 資本(1)
            Worksheets("合計残高").Cells(j, 5) = 流動資産(2) + 固定資産(2) - 流動負債(2) - 資本(2)
            Worksheets("合計残高").Cells(j, 6) = 流動資産(3) + 固定資産(3) - 流動負債(3) - 資本(3)
            j = j + 1
            Worksheets("合計残高").Cells(j, 2) = "《負債・資本の部》"
            Worksheets("合計残高").Cells(j, 3) = 流動負債(0) + 資本(0) + 流動資産(0) + 固定資産(0) - 流動負債(0) - 資本(0)
            Worksheets("合計残高").Cells(j, 4) = 流動負債(1) + 資本(1) + 流動資産(1) + 固定資産(1) - 流動負債(1) - 資本(1)
            Worksheets("合計残高").Cells(j, 5) = 流動負債(2) + 資本(2) + 流動資産(2) + 固定資産(2) - 流動負債(2) - 資本(2)
            Worksheets("合計残高").Cells(j, 6) = 流動負債(3) + 資本(3) + 流動資産(3) + 固定資産(3) - 流動負債(3) - 資本(3)
          Case "売上"
            Worksheets("合計残高").Cells(j, 2) = "<売上計>"
            Worksheets("合計残高").Cells(j, 3) = 売上(0)
            Worksheets("合計残高").Cells(j, 4) = 売上(1)
            Worksheets("合計残高").Cells(j, 5) = 売上(2)
            Worksheets("合計残高").Cells(j, 6) = 売上(3)
          Case "仕入"
            Worksheets("合計残高").Cells(j, 2) = "<仕入計>"
            Worksheets("合計残高").Cells(j, 3) = 仕入(0)
            Worksheets("合計残高").Cells(j, 4) = 仕入(1)
            Worksheets("合計残高").Cells(j, 5) = 仕入(2)
            Worksheets("合計残高").Cells(j, 6) = 仕入(3)
            j = j + 1
            Worksheets("合計残高").Cells(j, 2) = "《売上利益》"
            Worksheets("合計残高").Cells(j, 3) = 売上(0) - 仕入(0)
            Worksheets("合計残高").Cells(j, 4) = 売上(1) - 仕入(1)
            Worksheets("合計残高").Cells(j, 5) = 売上(2) - 仕入(2)
            Worksheets("合計残高").Cells(j, 6) = 売上(3) - 仕入(3)
          Case "販売管理費"
            Worksheets("合計残高").Cells(j, 2) = "<販売管理費計>"
            Worksheets("合計残高").Cells(j, 3) = 販売管理費(0)
            Worksheets("合計残高").Cells(j, 4) = 販売管理費(1)
            Worksheets("合計残高").Cells(j, 5) = 販売管理費(2)
            Worksheets("合計残高").Cells(j, 6) = 販売管理費(3)
            j = j + 1
            Worksheets("合計残高").Cells(j, 2) = "《営業利益》"
            Worksheets("合計残高").Cells(j, 3) = 売上(0) - 仕入(0) - 販売管理費(0)
            Worksheets("合計残高").Cells(j, 6) = 売上(3) - 仕入(3) - 販売管理費(3)
          Case "営業外収益"
            Worksheets("合計残高").Cells(j, 2) = "<営業外収益計>"
            Worksheets("合計残高").Cells(j, 3) = 営業外収益(0)
            Worksheets("合計残高").Cells(j, 4) = 営業外収益(1)
            Worksheets("合計残高").Cells(j, 5) = 営業外収益(2)
            Worksheets("合計残高").Cells(j, 6) = 営業外収益(3)
          Case "営業外費用"
            Worksheets("合計残高").Cells(j, 2) = "<営業外費用計>"
            Worksheets("合計残高").Cells(j, 3) = 営業外費用(0)
            Worksheets("合計残高").Cells(j, 4) = 営業外費用(1)
            Worksheets("合計残高").Cells(j, 5) = 営業外費用(2)
            Worksheets("合計残高").Cells(j, 6) = 営業外費用(3)
            j = j + 1
            Worksheets("合計残高").Cells(j, 2) = "《当期利益》"
            Worksheets("合計残高").Cells(j, 3) = 売上(0) - 仕入(0) - 販売管理費(0) + 営業外収益(0) - 営業外費用(0)
            Worksheets("合計残高").Cells(j, 6) = 売上(3) - 仕入(3) - 販売管理費(3) + 営業外収益(3) - 営業外費用(3)
        End Select
        j = j + 1
      End If
    Next
    Worksheets("合計残高").Activate
  End Sub

先頭へ

塾長あいさつ
基本方針(考え方)
通信教育内容
課題解決セミナー内容
初心者基礎コース内容
カリキュラム・料金
案内地図・各種リンク
問合せ・申込み
アベノ塾
システムの内製化
アサイコンピュータACS
応用部品集(有料)
販売管理(事例)
財務管理(事例)
顧客管理(事例)
見積もり(事例)
給与(事例)
介護(事例)
ゴースト暗算(事例)
部品集(部品の基礎)
部品集(シート関連)
部品集(データベース)
部品集(セル関連)
部品集(関数)
部品集(フォーム関連)
部品集(ブック関連)
部品集(その他)
変数の勉強
繰り返しと条件文
マスター登録.訂正.削除
フォルダー内のファイル
項目をシート名で作成
ブックの操作
CSVファイルの取り込み
呼び出し元・先
商品マスター検索
エクセル関数をVBAで
エクセル関数を使う
よくつかうプログラム
販売管理(ソース)
財務管理(ソース)
給与管理(概要・画面)
見積もり(概要・画面)
工程管理(概要・画面)
原価管理(概要・画面)