家計簿作成のpart2では、マクロ作成の事前準備としてファイルとシートの用意をしていきます！
ここで学習できることは以下の通りです。
- ファイルの種類
- シートに数式などを搭載したシステムの作成方法・思考回路
マクロに対応したファイルを作成しよう
マクロに対応したファイルとは？
エクセルファイルにはいくつか種類が存在します。
ファイルの種類とは、ファイル名の末尾部分で分類されるものです。
仕事でよく利用するものは以下の3つです。
- （ファイル名）.xlsx …ただのエクセルファイル
- （ファイル名）.xlsm …マクロを利用できるファイル
- （ファイル名）.csv …データを羅列するだけのファイル
マクロに対応したファイルの設定方法
では、上記のようなファイルの種類をどのように設定するのか。
これは、保存時に設定できます。
１ タブ「ファイル」をクリック
２ 「名前をつけて保存」または「コピーを保存」をクリック
３ ファイル名とファイルの種類を決定して保存
目的に応じたシートを作成しよう
ファイルを用意したら、次はシートを作成していきます。
今回の家計簿では、以下の3つのシートを用意します。
- 支払い方法をまとめて表示するシート…シート「口座」
- 家計簿の個別内容を貯めていくシート…シート「マスター」
- 集計結果をまとめて確認できるシート…シート「ホーム」
シート名の設定に関する注意点
ひとまず、３つのシートを用意の上、名前を付けましょう。
１ 画面下部分にあるシートタブの「＋」をクリック→新しいシート作成
２ シートタブで右クリック→「名前の変更」をクリック
※注意点
シート名は、そのシートには何があるか分かる端的な名前を設定しましょう。
関数やVBAコーディングを行う際にも、シート名を利用するため下準備としてシート名は短く分かりやすいものにする必要があります。
シート「口座」の作成
次にシート「口座」を作成します。
このシートの役割は、『支払い方法と引き落とし口座を紐付ける』というものです。
ここで紐付けをすれば、シート「マスター」にてVLOOKUP関数を利用して、引き落とし口座を自動で反映させることができます。
後にVLOOKUP関数の利用を想定しているので、支払い方法と引き落とし口座の順番に注意しましょう。
「支払い方法を指定したら引き落とし口座が反映される」状態にしたいので、支払い方法をキーにして検索できるように、左端に支払い方法を置いています。
このようにシートには「データを紐付けするだけのシート」を用意しておくと、他のシートでVLOOKUP関数を利用すれば自動で反映可能になり、入力ミスを防ぐことができるようになります。
以上、次のシート「マスター」のための下準備としてシート「口座」を作成しました。
シート「マスター」の作成
次にシート「マスター」を作成します。
このシートの役割は、「収支の全データを保管する」というものです。
ここで入力しておきたい内容は以下の9つです。
- 収支の発生した日付（YYYYMMDD)
- 収支の発生した年月（YYYYMM) ← １をMID関数
- 収入か支出か ← 入力規則「+」or「-」
- 固定費か変動費か ←入力規則「固定」or「変動」
- 収支の科目 ←入力規則
- 備考
- 金額
- 支払い方法
- 引き落とし口座 ← ８とシート「口座」でVLOOKUP関数
上記リストにも記載がありますが、入力方法として「関数を利用/入力規則/直接入力」の3種類を用意します。
直接入力については、セルを選択してキーボードで打ち込むだけです。
以下では、関数を利用するものと入力規制する項目について解説します。
MID関数で終始発生の年月を表示
列AにYYYYMMDDを入力していれば、MID関数で列BへYYYYMMを表示できます。
例：セルB2に「=MID(A2,1,6)」と入力すれば、「202506」が表示される
MID関数について詳しく知りたい方はこちらの記事を確認してください。
なお、数式は1度入力すれば、オートフィルを活用して数式を延長できます。
延長したい数式のあるセル右下にカーソルを合わせ、「＋」になったら、マウスで延長したいセルまでドラッグ（左クリックで掴んだまま下ろす）すると数式が延長されます。
VLOOKUP関数で引き落とし口座を反映
事前にシート「口座」で支払い方法と引き落とし口座を紐付けしているので、VLOOKUP関数を活用すれば、「支払い方法を検索値として、引き落とし口座を反映」できます。
例：セルI2に「=VLOOKUP(H2,口座！A:B,2,0)」と入力すれば、シート「口座」を読み取り、「銀行B」が反映される
VLOOKUP関数について詳しく知りたい方はこちらをご確認ください。
またMID関数と同様に、オートフィルを利用すれば、数式を延長ができます。
ただし、今回は、エラーも表示させたくないのでIFERROR関数を活用します。
IFERROR関数について詳しく知りたい方はこちらをご確認ください。
詳細は下記の動画のようになります。
なお、上記動画のオートフィルはセル右下にカーソルを合わせた後、「ダブルクリック」をすることで数式を延長しています。
入力規則をして入力内容を統一
「＋」か「-」 や 「固定」か「変動」 のように決まったものしか入力されたくない場合、入力規則を指定すると良いでしょう。
例えば、正確に「＋」「-」どちらかが入力されていないと集計時に困る場合（全角と半角の違いも許せない場合）には、入力規則で縛ることが有効です。
まさに家計簿における「＋」「-」が収支の集計に必須な項目のため、絶対に入力規則を利用すべきです。
では、どのように入力規則を設定するのか。その方法は以下の通りです。
１ 入力規則をしたいセルを選択（複数可） ここではセルB2以下全て選択
２ タブ「データ」データの入力規則をクリック
３ 入力種類「リスト」 元の値「O列」を指定 O列に「＋」「-」を入力
同様にして、列D「固定/変動」や列E「科目」も入力規則を設定できます。
入力規則を設定すれば、プルダウンで入力が可能になり、直接打ち込んでも設定外の内容であればエラーが発生します。
シート「ホーム」の作成
最後に、シート「ホーム」を作成します。
シート「ホーム」の役割は、『シート「マスター」のデータを集計』です。
今回の「家計簿ver1.0（無料）」のホーム画面では、以下の内容を確認できるようにします。
- 指定月の支出と収入
- 指定月が赤字か黒字か ←赤字なら文字を赤くする
ホーム画面の仕組み
作成前に「最終的な形（ホームで確認できるようにしたいこと）」から逆算して、何が必要かを考えます。
今回の場合、以下の3点が必要になります。思考回路とともに紹介します。
- 指定月のデータが欲しい→どうやって指定月を表示しよう→毎回当月を見たいならTODAY関数とMID関数の組み合わせでいい→でも前月実績を見たいこともありそう→じゃあ「手入力」が何にでも対応できる（毎回入力する訳じゃないから手間にもならないはず）
- 「指定月の収入と支出」を算出したい→どうやったら計算できるかな→条件に一致するデータの合計を知りたいから関数SUMIFSかな→じゃあ条件ってなんだろう→『シート「マスター」列Bの年月と列Cの＋か-』だけで欲しいものは抽出できそう
- 「差額が赤字か黒字か」の判定をしたい→とりあえず差額は「収入-支出」でいいだろう→せっかくだし赤字なら目立つように文字も赤くしたいな→毎回手作業で赤くするなら無駄な時間だし、自動で赤くしたい→条件付き書式を利用すればいけるかも
事前に上記のような想定をしておけば、作業の道筋が立ちます。
ただし、当然最初の想定通りに進まないこともあるので、その際には新たに軌道修正する必要があります。
「指定月の支出と収入」の集計
では、「指定月の支出と収入」を集計していきましょう。
先述した通り、方針は『条件【シート「マスター」の年月と「＋」「-」】として関数SUMIFSを活用』で進めます。
上図のように、「=SUMIFS(マスター！G:G , マスター！C:C “＋” , マスター！B:B , B2)」と立式すれば「指定した年月の収入を合計」できます。
同様にして、上記式の「＋」を「-」へ書き換えれば、支出も計算できます。
※SUMIFS関数に入力する「＋」「-」とシート「マスター」の「＋」「-」は同一の表記にしないと計算できないため注意
「指定月が赤字か黒字」の判定
では、本記事の最後、条件付き書式を駆使して赤字の場合には文字を赤くしましょう。
今回は「差額が０より小さい時、その数字を赤文字にする」という条件付き書式を設定します。
設定方法は以下の通りです。
１ タブ「ホーム」条件付き書式をクリック
２ 新しいルールをクリック
３ 下図のように、希望通りに設定する
すると、差額がマイナスになっていれば、赤文字になります。
まとめ
ここまででファイル・シートの用意は完了です。
適切に関数を用意するだけでも、十分に使いやすいエクセルが完成します。
次回は、ユーザーフォームの設定をしていきます。
コメント