先日、質問掲示板に 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
値の置換
こんな感じです。
