作成したい機能の検討が完了したら、実際にExcelを使用して家計簿の実装を行っていきます。
今回の記事では、家計簿で必ず必要になる「お金の入出金の入力シート」を作成していきます。
※Excel家計簿を作成した方法についての記事の2つ目になります。その他の記事は以下にまとめています。
https://vbaexcel.slavesystems.com/vba/?p=936
入力シートのテンプレートになるワークシートを作成する
まずは金額を入力するためのシートを作成します。この記事で作成するワークシートの最終形は以下のようなイメージです。
今回、私が作成した家計簿では、口座ごとに入出金を管理したいと考えたため、1口座につき1ワークシートを作成する形となります。
そこで、まず口座シートのテンプレートとなるワークシートを作成します。このテンプレートをコピーして各口座のワークシートにする想定です。
ワークシートから口座名を取得して表示する
まず、現在開かれているワークシートがどの口座のワークシートであるかを明確にするため、ワークシート上に口座名を表示するようにしました。
ルールとして、下記のように「ワークシート名を口座を特定できるような名前になるようにする」と決めました。
そこで、関数式でワークシート名を取得してセル上に表示するようにします。A1列に以下の関数式を設定します。
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))これでワークシート名が表示できるようになりました。また、目立たせるように、セルを結合して太字に設定しました。
入出力用のテーブルを作成する
次に3行目にテーブルの見出し行を作成します。
私は、下記の通り6項目を作成しました。
用途としては、以下のようになります。
- 日付：入出金の発生した日を記載する
- 種別：支払い/収入のどちらであるかを記載する
- カテゴリ：食費、光熱費、交通費などの種別を記載する
- カテゴリ2：カテゴリより細かい分類を記載する 「外食」、「飲料」、「食材購入費」など
- 金額：支払いまたは収入が発生した金額を記載する
- メモ：自由に入力できるメモ欄 店名や品名などを記載する想定
私は対応していなかったのですが、3行目が見出し行になるため、常に3行目が見えるように「ウィンドウ枠の固定」をしておくのも良いかと思います。
テーブルとして書式設定
このままだと見た目が質素なので、表の書式を設定します。
今回私は、「テーブルとして書式設定」を使用して見た目を変更することにしました。
「テーブルとして書式設定」を使用する理由は以下になります。
- ストライプ表を簡単操作で作成することが出来る（1行おきに色が違う表。現在行がわかりやすくなる。）
- 行が増えた時に自動で範囲を拡張してくれる
- 予め大量に書式設定していたとしてもExcelの容量が抑えられる
今回は有効活用できていないですが、「テーブル書式設定を使用したときだけ使えるExcel関数やVBA関数が便利」というメリットもあります。
設定方法ですが、「ホームタブ – テーブルとして書式設定」を選択します。
※私はOffice365で作業しています。他のバージョンだと若干違うかもしれません。
候補が現れるので好きなテーブルデザインを選択します。
最後にテーブルに変換する範囲を指定します。今回はとりあえず10000行くらい設定しておきました。前述したとおりExcel容量にあまり影響与えないので安心です。
ワークシートの名前を決める
テンプレートシートは、口座シートを作るときのコピー元になるのでわかり易い名前をつけておきます。
今回は、「template」という名前にしました。
カテゴリを選択肢から選べるようにする
次にカテゴリ項目について、セルに値を入力するのではなく、下記のように選択肢から選べるようにして利便性をあげます。
カテゴリを管理するワークシートを作成する
まず、この選択肢を管理するための「カテゴリ」という名前のワークシートを作成します。
そしてカテゴリシートに以下のように、選択肢として使用したいカテゴリを記載していきます。後で使うことになるので、ついでに予算も入れておきましょう。
収入用のカテゴリの場合は、以下のように種別「収入」として設定しました。
カテゴリから重複データを削除した列を作成する
列Bがカテゴリになりますが、このままだと重複データがあって選択肢を作るのが面倒なのでF列に値の重複がないデータの一覧を作成します。
スピルが対応しているExcel(Office365、Excel2019以降）などであれば、Unique関数で簡単に対応することができます。引数にはカテゴリを記載しているB列を範囲指定します。
=UNIQUE([B列])
Unique関数が使えない場合は、「データタブ – 重複の削除」機能などを使用して、カテゴリが一意な列を作成します。
テンプレートシートにデータの入力規則を設定する
次に、カテゴリシートに設定した値をもとにしてテンプレートシートで選択リストを設定います。
カテゴリ列を全選択して「データタブ – データの入力規則」をクリックします。
以下のように設定を行います。
- 入力の種類：リスト
- 空白を無視する：チェックあり
- ドロップダウンリストから選択する：チェックあり
- 元の値：カテゴリシートで重複なしカテゴリを作成した範囲
これでカテゴリ列に選択肢が表示されるようになります。
次にカテゴリ2列の選択肢も設定します。こちらの設定ですが、カテゴリ1が決まったら、それに対応する選択肢だけを出すようにします。
元の値の設定値（D5セルに設定する場合）
=INDIRECT("カテゴリ!"&ADDRESS(MATCH(C5,カテゴリ!B:B,0),3)& ":" &ADDRESS(MATCH(C5,カテゴリ!B:B,0)+COUNTIF(カテゴリ!B:B,C5)-1,3))EXCELの式を利用して、自分の左のセル（カテゴリ列）の値をもとにカテゴリ2の範囲を動的に取得しています。
最後に、種別列にも選択肢を設定しておきます。こちらは固定値で設定しています。
あと今回は対応していませんが、日付列を日付ピッカー選択にするという方法も便利ですね。その方法は以下記事で解説しています。
新規口座シート作成用のVBAプログラムを組む
ここまででテンプレート用のシートは完成になります。テンプレートシートは変更したくないので、右クリックして非表示にしておくようにしました。
次に開発タブからVBEを開いてプログラムを実装していきます。
ワークシートの定数定義を行う
家計簿を作る時に限らないのですが、「4列目が金額の列」みたいな情報はすべて定数で定義しておくべきです。
仮に「5列目が金額の列」となった場合、修正箇所がわかりやすいためです。
個人的に定数は1つのモジュールに集めておくのが好きなので、「Consts」というモジュールを作成して、そこに定数定義を集約します。
↑モジュール名は「オブジェクト名」から変更できる。
'ワークシート名
Public Const SH_NAME_TEMPLATE = "template"
Public Const SH_NAME_CATEGORY = "カテゴリー"
'カテゴリシートの列定義
Public Enum shCateColumns
SHUBETU = 1
CATEGO
CATEGO2
YOSAN
End Enum
'カテゴリシートの行定義
Public Enum shCateRows
START_ROW = 2 '表データの開始行
End Enum
'口座のシートの列定義
Public Enum shKozaColumns
HIDUKE = 1
SHUBETU
CATEGO
CATEGO2
KINGAKU
MEMO
End Enum
'口座のシートの行定義
Public Enum shKozaRows
START_ROW = 4 '表データの開始行
End Enum
'口座シート種別列の設定値
Public Const SHUBETU_SHOKI = "初期残高"
Public Const SHUBETU_SHISHUTU = "支払い"
Public Const SHUBETU_SHUNYU = "収入"
Public Const SHUBETU_SHUKIN = "出金"
Public Const SHUBETU_NYUKIN = "入金"この記事で作成した口座用のシートとカテゴリ用のシートの設定値を記載します。
列定義にEnumを使用しています。完全に別の定数で定義するより使い勝手が良いです。
※Enumを使用することで、列数が自動で1ずつインクリメントされて定義することができます。
新規口座名入力用のフォームを作成する
ユーザフォームを新規作成して、新規口座名入力用のフォームとします。フォーム名は「frmKoza」としました。レイアウトは以下の通り。
初期金額とは、家計簿作成時点でその口座に入っている金額を入力する項目になります。
↓フォーム名は「frmKoza」、キャプションは「口座追加」とします。
またフォームに配置した各オブジェクト名は以下のようにします。
- 口座名の入力欄：txtKoza
- 初期金額の入力欄：txtKingaku
- 決定ボタン：cmdKettei
- キャンセルボタン：cmdCancel
「frmKoza」を右クリックし、コードの表示から各ボタンの動作を作成していきます。
キャンセルボタンのコード作成
まず簡単なキャンセルボタンから、クリック時のコードを書いていきます。
※cmdCancel_Clickという関数名とすると。クリック時に呼び出される
'キャンセルボタンクリック時
Private Sub cmdCancel_Click()
'フォームをアンロードする
Unload frmKoza
End Sub
キャンセルしたときには、何もせずにフォームを閉じるだけなのでアンロードの1行だけを記載します。汎用的に作るなら「Unload Me」でもいいかも。
テンプレートシートのオブジェクト名の変更
決定ボタンの処理の下準備として、テンプレート用のシートのオブジェクト名を「shKoza」としておきました。
こうすることにより、テンプレートシートからコピーされたシートは「shKoza1」、「shKoza2」といったような命名規則になっていきます。
これをしておくことにより、口座のシートだけに処理を行いたいときに、「ワークシートのオブジェクト名がshKozaから始まる」と言う条件が書けるので楽ができます。
決定ボタンのコード作成
次に決定ボタンの処理ですが…
- テンプレートシートをコピーして、新しい口座シートを作成する
- 新しい口座シートの名称を変更する
- 新しい口座シートを表示状態にする（テンプレートシートをコピーすると非表示状態になってる）
- 初期残高を設定する
上記の順番に実装していきます。実際に作成したコードが以下になります。
'決定ボタンクリック時
Private Sub cmdKettei_Click()
Dim shNew As Worksheet
'templateシートをコピーして、新しい口座シートを作成する
Call ThisWorkbook.Worksheets(SH_NAME_TEMPLATE).Copy(after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
Set shNew = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
'新しい口座シートの名称を変更して、表示状態にする
shNew.Name = txtKoza.Text
shNew.Visible = True
'初期残高を設定する
If txtKingaku.Text <> "" And txtKingaku.Text <> "0" Then
shNew.Cells(shKozaRows.START_ROW, shKozaColumns.HIDUKE).Value = Format(Date, "yyyy/mm/dd")
shNew.Cells(shKozaRows.START_ROW, shKozaColumns.SHUBETU).Value = SHUBETU_SHOKI
shNew.Cells(shKozaRows.START_ROW, shKozaColumns.KINGAKU).Value = txtKingaku.Text
End If
'フォームをアンロードする
Unload frmKoza
End Sub特に複雑なところはないですね。
作成したフォームを呼び出す用の関数は以下になります。フォーム出現中は、ワークシートを操作されたくないのでモーダルモードで呼び出しとします。
'口座追加フォームを呼び出す
frmKoza.Show vbModal次回の記事では、「口座間で金額の移動があった時」に使用する機能を作成していきます。
次記事
コメントを残す