家計簿に入力するときに、支出、食費、昼食など毎回キーボードで手打ちするのは面倒だと思います
そこで、入力する項目をリスト化して、そこから選択するだけで入力できるようにする方法を解説します
一般的にリスト化する方法は↓のように解説されています
かもしかの家計簿ではこの方法をもっと家計簿の入力に適した形で設定しています
かもしかの家計簿で使用しているリストは↓の画像のように、大分類や中分類に入力した値に応じて、プルダウンリストの項目が自動的に変わるように設定しています
３段階プルダウンリストの作り方
作り方を解説します
普段、使っている家計簿で作業してください
お使いのExcelバージョンやWindowsかMacかによって、機能の名前や手順に多少の違いがあります
画像を多く使って、わかりやすく解説しているのでせひチャレンジしてみてください
もしわからない箇所があったら、お気軽にお問い合わせください
↓のボタンをクリックして大中小分類の順番で作成してみてください
1.矢印部分をクリックして新しいシートを作成します
2.新しく出来たシートのシート名をダブルクリックして編集し、名前を「リスト」に変更
※変更するシート名は何でも大丈夫です
3.リストのシートを開き、記録表の大分類の列に入力している値を入力
※仮に記録表の見出しの「大分類」を別の名前に変更している場合は、リストのB2にはその名前を入力してください
1.入力した値をドラッグして全て選択
2.挿入を選択し、テーブルをクリック
3.ダイアログが表示されるので、データ範囲が正しいか確認し矢印部分にチェックを付ける
4.OKをクリック
このときテーブルにした項目が大分類のプルダウンリストの選択肢になります
1.作成したテーブルの中のどこかのセルを選択
2.するとテーブルデザインというタブが表示されるので、それをクリック
3.テーブル名に、B2に入力した値をそのまま入力
テーブル名を付けることで、プルダウンリストの設定が簡単になります
1.記録表に移動する
2.データをクリック
3.半角で「E3:E150000」を入力し、Enterを押す
※入力する枠が表示されていない場合は、「表示」タブの「数式バー」をチェックしてください
※「E3:E150000」の部分は大分類の項目を入力している列になるように、お使いの家計簿に合わせて自由に変更してください
4.先程、入力した範囲が選択される
※プルダウンリストを毎回設定するのは手間なので、E150000行目までリスト表示できるように設定します
5.E3～E150000行が選択されている状態で、データの入力規則をクリック
1.ダイアログが表示されるのでリストを選択
2.２つのチェックボックス両方にチェックを入れる
3.半角で「=INDIRECT($E$2)」と入力する
※$E$2の部分は見出しの「大分類」のセル番地になるように、お使いの家計簿に合わせて変更してください
「=INDIRECT($E$2)」は簡単に言えば、「E2セルの値と同名のリストを探して」という意味です
E2には大分類と入力しているので、先程、「大分類」とテーブル名を付けたリストを探してきてプルダウンリストにしてくれます
そのため、E2セルの値とテーブル名が一致していないとリスト化されません
1.まだ「OK」はクリックせずに、ダイアログのエラーメッセージを選択
2.矢印部分のチェックを外す
3.「OK」をクリック
大分類にプルダウンリスト以外の項目を入力できないようにしたい方は、ここのエラーメッセージのチェックを付けてください
1.▼部分を押すと、大分類テーブルの項目が選択肢として表示されます
次に大分類の項目に応じて、プルダウンリストの選択肢が変わる中分類の設定をしていきます
大分類の設定とほとんど同じ手順で、画像通りにやっていけば難しくないので安心してください
1.「リスト」のシートに移動して、大分類テーブルに入力した項目を矢印部分に入力
2.すると、自動的に大分類テーブルが広がってしまうので修正していきます
3.テーブルのどこかのセルを選択して、「テーブルデザイン」をクリック
4.テーブルのサイズ変更をクリック
※「テーブルのサイズ変更」がない場合は、テーブルの右下隅にカーソルを合わせてドラッグすることでサイズ変更できます
5.ダイアログが表示されるので、大分類のB2～B5の列をドラッグしてデータ範囲を変更し、「OK」をクリック
1.テーブル範囲を修正できたら、赤枠のように中分類の項目を入力
※お使いの家計簿で使用している項目を入力してください
2.挿入を選択し、テーブルをクリック
3.ダイアログが表示されるので、テーブル化したい支出の列をドラッグして選択
4.ダイアログでC2~C7がデータ範囲として指定されていることを確認し、チェックを付けてOKをクリック
1.支出テーブルのどこかのセルを選択
2.「テーブルデザイン」を選択
3.テーブル名にC2と同じ値を入力
収入と予算についても、支出と同じようにテーブル化し、↓の図のようにしてください
※収入と予算にもテーブル名を付けるのを忘れないようにしてください
ここからは記録表で中分類のプルダウンリストの設定をしていきます
1.記録表シートに移動して、「データ」を選択
2.半角で「F3:F150000」と入力し、Enterを押す
※入力する枠が表示されていない場合は、「表示」タブの「数式バー」をチェックしてください
※「F3:F150000」の部分は中分類の項目を入力している列になるように、お使いの家計簿に合わせて自由に変更してください
3.先程、入力した範囲が選択される
4.範囲が選択された状態で、「データの入力規則」をクリック
1.ダイアログが表示されるので、リストを選択
2.２つのチェックボックス両方にチェックを入れる
3.半角で「=INDIRECT(E3)」と入力する
※E3の部分は「大分類」の見出しの１つ下のセル番地になるように、お使いの家計簿に合わせて変更してください
「=INDIRECT(E3)」は、「E3」の大分類に入力している値が「収入」なら収入テーブルのリストを、「支出」なら支出テーブルのリストを探してきて、「F3」でプルダウンリストにしてくれます
ただ、「=INDIRECT(E3)」だと「E10」セルで支出と入力しても、「F10」セルで正しく表示されないのではと思うかもしれませんが、それは問題ありません
これにはセルの絶対参照と相対参照という機能が関係しています
名前だけ聞くと難しそうですが、とても簡単なので安心してください
大分類のプルダウンリストを設定するときは、「=INDIRECT($E$2)」と入力しました
$E$2のように「＄」マークがあるのが絶対参照、
E3のように「＄」マークがないのが相対参照です
絶対参照は字の通り、絶対に最初に指定したセルしか参照しない
相対参照はセル番地が変われば、いい感じに空気を読んで参照するセルを変えてくれます
つまり、「=INDIRECT(E3)」は相対参照なので、「F10」のリストを作るときは自動的に「=INDIRECT(E10)」となります
そのため「E10」セルで支出と入力したら「F10」セルで支出テーブルのリストを作ってくれます
1.まだ「OK」はクリックせずに、ダイアログのエラーメッセージを選択
2.矢印部分のチェックを外す
3.「OK」をクリック
中分類にプルダウンリスト以外の項目を入力できないようにしたい方は、ここのエラーメッセージのチェックを付けてください
これで中分類のプルダウンリストが完成しました
↓の画像のように、大分類に入力された値に応じて、中分類のリストが変わるようになっています
最後に小分類のプルダウンリストの設定をしていきます
ここは中分類を設定したときとほぼ同じやり方なので簡単に解説します
1.矢印部分に中分類の項目を入力します
※ここでは一例として食費と給与所得だけ設定します
2.テーブルのすぐ横に入力すると、予算テーブルが自動的に広がってしまうため、修正します
3.「テーブルデザイン」を選択
4.テーブルのサイズ変更をクリック
※「テーブルのサイズ変更」がない場合は、テーブルの右下隅にカーソルを合わせてドラッグすることでサイズ変更できます
5.ダイアログが表示されるので、予算テーブルのE2～E3の列をドラッグしてデータ範囲を変更し、「OK」をクリック
1.食費と給与所得に小分類の項目を入力します
2.挿入を選択し、テーブルをクリック
3.ダイアログが表示されるので、食費の列をドラッグして選択
4.ダイアログのデータ範囲がF2〜F6になったのを確認し、矢印部分にチェックをつけてOKをクリック
1.食費テーブルのどこかのセルを選択
2.テーブルデザインを選択
3.テーブル名に「食費」と入力
給与所得も同じように設定し↓の図のような形にします
1.記録表に移動して、「データ」を選択
2.半角で「G3:G150000」と入力し、Enterを押す
3.先程、入力した範囲が選択される
4.「G3:G150000」が選択された状態で「データの入力規則」をクリック
1.ダイアログが表示されるので「リスト」を選択
2.2つのチェックボックスにチェックをつける
3.半角で「=INDIRECT(F3)」と入力
※F3の部分は「中分類」の見出しの１つ下のセル番地になるように、お使いの家計簿に合わせて変更してください
1.まだ「OK」はクリックせずに、ダイアログのエラーメッセージを選択
2.矢印部分のチェックを外す
3.「OK」をクリック
小分類にプルダウンリスト以外の項目を入力できないようにしたい方は、ここのエラーメッセージのチェックを付けてください
これで大・中・小分類の3段階のプルダウンリストが作成できました
価値分類や支払先、支払方法のプルダウンリストを作成する場合は、大分類のプルダウンリストを作成したときと同じような手順で設定できます
先程、作成したプルダウンリストに新しい選択肢を追加する方法です↓
次に、プルダウンリストに新しい選択肢を追加する方法を解説します
1.すでに作成したリストに新しい選択肢を追加する場合は、テーブルのすぐ下に追加したい選択肢を入力します
1.自動的にテーブルが広がるので、これで選択肢を追加することができました
1.記録表で確認すると、選択肢が追加されています
合わせて設定しておくと便利な機能を紹介します
かもしかの家計簿では「支出」を赤文字にしていますが、↓の画像のようにプルダウンリストから選択すると、黒文字で入力されてしまいます
入力された値に応じて、自動的にデザインが変更されるように設定します
1.記録表シートに移動し、ホームを選択
2.赤矢印部分にカーソルを移動し、「⬇︎」マークになったらクリックして、大分類の列を全て選択
3.「条件付き書式」をクリック
4.「新しいルール」をクリック
1.ダイアログが表示されるので、「指定の値を含むセルだけを書式設定」を選択
2.赤枠と同じ選択肢になるように入力
3.「書式」をクリック
※もしダイアログの表示が↓の画像のような形の場合は、↓の画像と同じように入力してください
1.「セルの書式設定」ダイアログが表示されるので、「フォント」タブで「赤色」を指定
2.「OK」をクリックし、先程開いた「新しい書式ルール」ダイアログも「OK」をクリック
すると大分類の列で「支出」と入力すると、自動的に赤色になります
同じように「収入」「予算」も変更してみてください
「条件付き書式」では、
・指定した値でセルの色・フォント・大きさを変える
・指定した数値以上、以下の場合、セルを強調表示する
など、家計簿に便利な機能があるので、ぜひ使ってみてください