先日、質問掲示板に Excelで現金出納帳を作っている人の質問があったんだけど、その人の出納帳には右端に残高列があって、そこから月ごとの残高が計算できなくて困っているという質問だった。……いや計算に使えなくて困るくらいならそんな列いらんでしょうよ。あんなもの手計算だった時代の名残りでしかないのに。
そう思ったんで「紙書類じゃないんだから、その列いらないんじゃない？」と突っ込んだら、「私の表にはいるんです！」と返された。何のこだわりなんだろう。
さて愚痴は置いといて、「現金出納帳（お小遣い帳）」を基にして他の帳票をクエリで作ってみよう。もちろん出納帳に残高列なんていらないし、月別にシートを分けるようなこともしない。
こういう現金出納帳を基にして勘定元帳を作ってみよう。
下準備は勘定科目の入力セルを決めて、そこに「勘定科目」と名前定義しておくだけ。
勘定元帳
let
ブック = Excel.CurrentWorkbook(),
勘定科目名 = ブック{[Name="勘定科目"]}[Content]{0}[Column1],
ソース = ブック{[Name="テーブル1"]}[Content],
型の変更 = Table.TransformColumnTypes(ソース, {
{"日付", type date},
{"科目名", type text},
{"入金", Int64.Type},
{"出金", Int64.Type},
{"摘要", type text}
}),
繰越行の削除 = Table.Skip(型の変更),
行の抽出 = Table.SelectRows(繰越行の削除, each [科目名]=勘定科目名),
借方金額列の追加 = Table.AddColumn(
行の抽出, "借方金額", each List.Sum({[出金], 0}), Int64.Type
),
貸方金額列の追加 = Table.AddColumn(
借方金額列の追加, "貸方金額", each List.Sum({[入金], 0}), Int64.Type
),
連番列の追加 = Table.AddIndexColumn(
貸方金額列の追加, "連番", 1, 1, Int64.Type
),
累計列の追加 = Table.AddColumn(連番列の追加, "累計", each
List.Sum(List.FirstN(連番列の追加[借方金額], [連番])&{0})
),
列の削除 = Table.RemoveColumns(累計列の追加, {"入金", "出金", "連番"})
in
列の削除
以上。累計列以外はマウス操作でも普通に作れると思う。あくまでも現金出納帳から作る元帳なので、前期残はないものとする。
この形なら勘定科目セルの科目名を変更して「更新」してやれば抽出し直すので、シートを科目の数だけ増やすような無駄なことをしなくて済む。これを入力と同時に抽出される仕様にしたければ VBAで Worksheet_Changeイベントを入れてやればいい。
ついでに、科目別の月別収支表を作ってみよう。
行の並びを固定したいので、科目マスタを別に作っておこう。もちろん Excelで作ったテーブルを読み込んでもいい。
科目マスタ
let
ソース = Table.FromColumns(
{{"交通費", "燃料費", "通信費", "交際費", "福利厚生費"}},
type table [科目名=text]
)
in
ソース
科目別の収支結果を「列のピボット」→「科目マスタにマージ」してやれば完成。
科目別月別収支
let
ブック = Excel.CurrentWorkbook(),
ソース = ブック{[Name="テーブル1"]}[Content],
型の変更 = Table.TransformColumnTypes(ソース, {
{"日付", type date},
{"科目名", type text},
{"入金", Int64.Type},
{"出金", Int64.Type},
{"摘要", type text}
}),
月名の表示 = Table.TransformColumns(
型の変更, {"日付", each Date.MonthName(_), type text}
),
収支列の追加 = Table.AddColumn(
月名の表示, "収支", each List.Sum({[出金], -[入金], 0})
),
列の削除 = Table.RemoveColumns(
収支列の追加,{"入金", "出金", "摘要"}
),
列のピボット = Table.Pivot(
列の削除, List.Distinct(列の削除[日付]),
"日付", "収支", List.Sum
),
クエリのマージ = Table.NestedJoin(
科目マスタ, {"科目名"},
列のピボット, {"科目名"},
"勘定科目"
),
見出し = List.Skip(Table.ColumnNames(列のピボット)),
列の展開 = Table.ExpandTableColumn(
クエリのマージ, "勘定科目", 見出し
),
値の置換 = Table.ReplaceValue(
列の展開, null, 0, Replacer.ReplaceValue, 見出し
)
in
値の置換
こんな感じ。
