（５）決算書
決算書の内容は合計残高試算表とほとんど同じであるが対外的に使うものである為
見やすい帳票にしなければならない。
エクセルを使えば罫線・文字フォント・均等割り付け・網掛けが自由に使えるために
便利である。
またプログラムも科目表・及び合計残高試算表の金額を使うためにほとんどコピーだけで
単純である。
但しＶＢＡ実践塾のデータベースの考え方は理解していないといけない。
（ａ）貸借対照表
（ｂ）損益計算書
（ｃ）販売費及び一般管理費内訳表
Sub 決算書()
Dim i As Long
Dim lastrow As Long
Dim lastrow1 As Long
Dim 現金及び預金 As Long
Dim 流動資産 As Long
Dim 固定資産 As Long
Dim 資産の部計 As Long
Dim 流動負債 As Long
Dim 負債の部計 As Long
Dim 資本金 As Long
Dim 前期繰越 As Long
Dim 当期利益 As Long
Dim 利益剰余金 As Long
Dim 純資産の部計 As Long
Dim 負債純資産の部計 As Long
Dim 売上高 As Long
Dim 仕入高 As Long
Dim 売上総利益 As Long
Dim 販売費及び一般管理費 As Long
Dim 営業利益 As Long
Dim 営業外収益 As Long
Dim 営業外費用 As Long
Dim kcode As Long
Worksheets("決算書").Cells(3, 4) = Worksheets("科目表").Cells(2, 13)
Worksheets("決算書").Cells(31, 4) = Worksheets("科目表").Cells(2, 12)
Worksheets("決算書").Cells(32, 4) = Worksheets("科目表").Cells(2, 13)
Worksheets("決算書").Cells(61, 4) = Worksheets("科目表").Cells(2, 12)
Worksheets("決算書").Cells(62, 4) = Worksheets("科目表").Cells(2, 13)
lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
lastrow1 = Worksheets("合計残高").Cells(Rows.Count, 2).End(xlUp).Row
'貸借対照表
'【流動資産】
For i = 2 To lastrow
If Worksheets("科目表").Cells(i, 5) = "流動資産" Then
流動資産 = 流動資産 + Worksheets("科目表").Cells(i, 9)
End If
Next
Worksheets("決算書").Cells(8, 2) = "【" & Format(流動資産, "#,###") & "】"
'現金及び預金（コード100から120）
For i = 2 To lastrow
If Worksheets("科目表").Cells(i, 1) >= 100 And Worksheets("科目表").Cells(i, 1) <= 120 Then
現金及び預金 = 現金及び預金 + Worksheets("科目表").Cells(i, 9)
End If
Next
Worksheets("決算書").Cells(9, 2) = 現金及び預金
'140受取手形142売掛金150 有価証券160 材料・商品170 前払金174 未収金177 預け金171 立替金173 短期貸付金
For i = 2 To lastrow
kcode = Worksheets("科目表").Cells(i, 1)
Select Case kcode
Case 140 '受取手形
Worksheets("決算書").Cells(10, 2) = Worksheets("科目表").Cells(i, 9)
Case 142 '売掛金
Worksheets("決算書").Cells(11, 2) = Worksheets("科目表").Cells(i, 9)
Case 150 '有価証券
Worksheets("決算書").Cells(12, 2) = Worksheets("科目表").Cells(i, 9)
Case 160 '材料・商品
Worksheets("決算書").Cells(13, 2) = Worksheets("科目表").Cells(i, 9)
Case 170 '前払金
Worksheets("決算書").Cells(14, 2) = Worksheets("科目表").Cells(i, 9)
Case 174 '未収金
Worksheets("決算書").Cells(15, 2) = Worksheets("科目表").Cells(i, 9)
Case 177 '預け金
Worksheets("決算書").Cells(16, 2) = Worksheets("科目表").Cells(i, 9)
Case 171 '立替金
Worksheets("決算書").Cells(17, 2) = Worksheets("科目表").Cells(i, 9)
Case 173 '短期貸付金
Worksheets("決算書").Cells(18, 2) = Worksheets("科目表").Cells(i, 9)
End Select
Next
'【固定資産】
For i = 2 To lastrow
If Worksheets("科目表").Cells(i, 5) = "固定資産" Then
固定資産 = 固定資産 + Worksheets("科目表").Cells(i, 9)
End If
Next
Worksheets("決算書").Cells(19, 2) = "【" & Format(固定資産, "#,###") & "】"
'203 機械装置204 車両運搬具205 工具器具備品
For i = 2 To lastrow
kcode = Worksheets("科目表").Cells(i, 1)
Select Case kcode
Case 203 '機械装置
Worksheets("決算書").Cells(20, 2) = Worksheets("科目表").Cells(i, 9)
Case 204 '車両運搬具
Worksheets("決算書").Cells(21, 2) = Worksheets("科目表").Cells(i, 9)
Case 205 '工具器具備品
Worksheets("決算書").Cells(22, 2) = Worksheets("科目表").Cells(i, 9)
End Select
Next
'資産の部計
資産の部計 = 流動資産 + 固定資産
Worksheets("決算書").Cells(23, 2) = 資産の部計
'【流動負債】
For i = 2 To lastrow
If Worksheets("科目表").Cells(i, 5) = "流動負債" Then
流動負債 = 流動負債 + Worksheets("科目表").Cells(i, 9)
End If
Next
Worksheets("決算書").Cells(8, 4) = "【" & Format(流動負債, "#,###") & "】"
'400 支払手形405 買掛金410 短期借入金420 未払金430 前受金427 預り金
For i = 2 To lastrow
kcode = Worksheets("科目表").Cells(i, 1)
Select Case kcode
Case 400 '支払手形
Worksheets("決算書").Cells(9, 4) = Worksheets("科目表").Cells(i, 9)
Case 405 '買掛金
Worksheets("決算書").Cells(10, 4) = Worksheets("科目表").Cells(i, 9)
Case 410 '短期借入金
Worksheets("決算書").Cells(11, 4) = Worksheets("科目表").Cells(i, 9)
Case 420 '未払金
Worksheets("決算書").Cells(12, 4) = Worksheets("科目表").Cells(i, 9)
Case 430 '前受金
Worksheets("決算書").Cells(13, 4) = Worksheets("科目表").Cells(i, 9)
Case 427 '預り金
Worksheets("決算書").Cells(14, 4) = Worksheets("科目表").Cells(i, 9)
End Select
Next
'負債の部計
負債の部計 = 流動負債
Worksheets("決算書").Cells(14, 4) = 負債の部計
'資本金
For i = 2 To lastrow
If Worksheets("科目表").Cells(i, 1) = 500 Then
資本金 = Worksheets("科目表").Cells(i, 9)
End If
Next
Worksheets("決算書").Cells(18, 4) = 資本金
'前期繰越
For i = 2 To lastrow
If Worksheets("科目表").Cells(i, 1) = 540 Then
前期繰越 = Worksheets("科目表").Cells(i, 4)
End If
Next
Worksheets("決算書").Cells(20, 4) = 前期繰越
'当期利益
For i = 2 To lastrow1
If Worksheets("合計残高").Cells(i, 2) = "《当期利益》" Then
当期利益 = Worksheets("合計残高").Cells(i, 6)
End If
Next
Worksheets("決算書").Cells(21, 4) = 当期利益
'[利益剰余金]
利益剰余金 = 前期繰越 + 当期利益
Worksheets("決算書").Cells(18, 4) = "[" & Format(利益剰余金, "#,###") & "]"
'純資産の部計
純資産の部計 = 資本金 + 利益剰余金
Worksheets("決算書").Cells(22, 4) = 純資産の部計
'負債・純資産の部計
負債純資産の部計 = 負債の部計 + 純資産の部計
Worksheets("決算書").Cells(23, 4) = 純資産の部計
'損益計算書
'売上高
For i = 2 To lastrow
If Worksheets("科目表").Cells(i, 1) = 700 Then
売上高 = Worksheets("科目表").Cells(i, 9)
End If
Next
Worksheets("決算書").Cells(37, 3) = 売上高
'仕入高
For i = 2 To lastrow
If Worksheets("科目表").Cells(i, 1) = 725 Then
仕入高 = Worksheets("科目表").Cells(i, 9)
End If
Next
Worksheets("決算書").Cells(39, 3) = 仕入高
'売上総利益
売上総利益 = 売上高 - 仕入高
Worksheets("決算書").Cells(40, 4) = 売上総利益
'【販売費及び一般管理費】
For i = 2 To lastrow1
If Worksheets("合計残高").Cells(i, 2) = "＜販売管理費計＞" Then
販売費及び一般管理費 = Worksheets("合計残高").Cells(i, 6)
End If
Next
Worksheets("決算書").Cells(41, 4) = 販売費及び一般管理費
'営業利益
営業利益 = 売上総利益 - 販売費及び一般管理費
Worksheets("決算書").Cells(42, 4) = 営業利益
'810 受取利息816 雑収入
For i = 2 To lastrow
kcode = Worksheets("科目表").Cells(i, 1)
Select Case kcode
Case 810 '受取利息
Worksheets("決算書").Cells(44, 3) = Worksheets("科目表").Cells(i, 9)
Case 816 '雑収入
Worksheets("決算書").Cells(46, 3) = Worksheets("科目表").Cells(i, 9)
End Select
Next
'【営業外収益】
For i = 2 To lastrow1
If Worksheets("合計残高").Cells(i, 2) = "＜営業外収益計＞" Then
営業外収益 = Worksheets("合計残高").Cells(i, 6)
End If
Next
Worksheets("決算書").Cells(46, 4) = 営業外収益
'830 支払利息832 貸倒損失営業外費用
For i = 2 To lastrow1
If Worksheets("合計残高").Cells(i, 2) = "＜営業外費用計＞" Then
営業外費用 = Worksheets("合計残高").Cells(i, 6)
End If
Next
Worksheets("決算書").Cells(48, 3) = 営業外費用
Worksheets("決算書").Cells(48, 4) = 営業外費用
'経常利益
Worksheets("決算書").Cells(49, 4) = 当期利益
'税引前当期純利益
Worksheets("決算書").Cells(50, 4) = 当期利益
'当期純利益
Worksheets("決算書").Cells(51, 4) = 当期利益
'販売費及び一般管理費内訳表
'741 給料手当746 福利厚生費750 外注費751 荷造運賃752 広告宣伝費753 接待交際費755 旅費交通費
'756 通信費760 消耗品費762 修繕費763 水道光熱費768 リース料770 保険料780 減価償却費
'781 地代家賃783 租税公課789 雑費810 受取利息
For i = 2 To lastrow
kcode = Worksheets("科目表").Cells(i, 1)
Select Case kcode
Case 741 '給料手当
Worksheets("決算書").Cells(67, 3) = Worksheets("科目表").Cells(i, 9)
Case 746 '福利厚生費
Worksheets("決算書").Cells(68, 3) = Worksheets("科目表").Cells(i, 9)
Case 750 '外注費
Worksheets("決算書").Cells(68, 3) = Worksheets("科目表").Cells(i, 9)
Case 751 '荷造運賃
Worksheets("決算書").Cells(69, 3) = Worksheets("科目表").Cells(i, 9)
Case 752 '広告宣伝費
Worksheets("決算書").Cells(70, 3) = Worksheets("科目表").Cells(i, 9)
Case 753 '接待交際費
Worksheets("決算書").Cells(71, 3) = Worksheets("科目表").Cells(i, 9)
Case 755 '旅費交通費
Worksheets("決算書").Cells(72, 3) = Worksheets("科目表").Cells(i, 9)
Case 756 '通信費
Worksheets("決算書").Cells(73, 3) = Worksheets("科目表").Cells(i, 9)
Case 760 '消耗品費
Worksheets("決算書").Cells(74, 3) = Worksheets("科目表").Cells(i, 9)
Case 762 '修繕費
Worksheets("決算書").Cells(75, 3) = Worksheets("科目表").Cells(i, 9)
Case 763 '水道光熱費
Worksheets("決算書").Cells(76, 3) = Worksheets("科目表").Cells(i, 9)
Case 768 'リース料
Worksheets("決算書").Cells(77, 3) = Worksheets("科目表").Cells(i, 9)
Case 770 '保険料
Worksheets("決算書").Cells(78, 3) = Worksheets("科目表").Cells(i, 9)
Case 780 '減価償却費
Worksheets("決算書").Cells(79, 3) = Worksheets("科目表").Cells(i, 9)
Case 781 '地代家賃
Worksheets("決算書").Cells(80, 3) = Worksheets("科目表").Cells(i, 9)
Case 783 '租税公課
Worksheets("決算書").Cells(81, 3) = Worksheets("科目表").Cells(i, 9)
Case 789 '雑費
Worksheets("決算書").Cells(82, 3) = Worksheets("科目表").Cells(i, 9)
Case 810 '受取利息
Worksheets("決算書").Cells(83, 3) = Worksheets("科目表").Cells(i, 9)
End Select
Next
Worksheets("決算書").Cells(84, 4) = 販売費及び一般管理費
Worksheets("決算書").Select
End Sub