![エクセル初心者](https://office-doctor.jp/powerpoint/wp-content/uploads/2021/01/3e47d3bcd2934e1e0b365a5b7517cbd4.jpg)
エクセル初心者
エクセルで家計簿を作るのって難しそう!簡単に作る方法ってないの?
テンプレートを使わないとダメ?
エクセルで家計簿を作るのって、意外と簡単だよ!
自分のスキルに応じて自作するほうがおススメ!
![Dr.オフィス](https://office-doctor.jp/word/wp-content/uploads/2020/04/fukidashi2.jpg)
Dr.オフィス
この記事を読むことで、簡単にエクセルで家計簿を作ることができます。
テンプレートを自分に合うように作り替えたり、項目が増えたり減ったりするたびに関数をいくつも直す必要はありません!
- 初心者なら最低限の手順と数式でシンプル家計簿を作ろう
- 中級者ならピボットテーブルで関数やテンプレートなしに家計簿を作成
- 上級者なら家計簿のダッシュボードも作ってみよう
MOSエクセルエキスパートの資格を持つ私が、エクセルで家計簿を作る方法について解説するよ!
Dr.オフィス
エクセルを活用して家計簿を自作するのは、初めは難しそうに見えますが、ひとつひとつの作業はそれほど難しくありません。
一度、仕組みを作ってしまえば、あとはレイアウト変更があった場合でも、とても簡単に修正できます。
家計簿ほど本格的でなくてもよい場合は≫エクセルでお小遣い帳を作るのもお勧めです。家計簿の補助としても活用できますので、併せてチェックしてくださいね。
※本記事は『OS:Windows11』画像は『Excelのバージョン:Microsoft365』を使用しています。
目次
エクセルで家計簿を作るメリット
エクセルで家計簿を作るメリットは以下の3つが挙げられます。
自由にレイアウトやデザインの変更ができる
エクセルで家計簿を作れば、簡単に自分の好みのデザインを反映させることができます。
また、ライフスタイルの変化などに合わせていつでも仕組みを修正できます。
アプリや紙の家計簿にはない「痒い所に手が届く」のはエクセルならでは。季節や年ごとに好きな色を使ってもいいですね。
データを蓄積して分析が可能
収入や支出のデータを蓄積しておくことで、年に1度の税金の支払いタイミングを事前に把握したり、家計の改善ポイントを分析したりすることができます。
分析するにしても、エクセルが計算やグラフ化までしてくれるので楽にできます。
長期的な保存ができる
家計簿アプリだと、別のアプリに乗り換える際はゼロからデータの蓄積をしないといけないことがほとんどです。
また、紙の家計簿をデータ化するには手間がかかります。
エクセルならデータの入力項目を工夫しておくことで、後で家計簿のレイアウトを変えた場合でも以前のデータをそのまま活用することができます。
家計簿の作成は、エクセルの勉強にもなるので一石二鳥だね!
Dr.オフィス
このように、エクセルで家計簿を作ることで、効率的かつ効果的な家計管理ができます。
データを蓄積して本格的に分析をしたい方から、エクセルの練習がてらまずは家計簿をつけてみたいという方まで、多くの人のニーズを満たしているといえるでしょう。
エクセルで家計簿の作り方【初心者向け】
まずは初心者向けの家計簿の作り方を解説します。
エクセルのシートを「毎月の収支12か月分」+「年間収支」の13シートを活用します。
- コピーアンドペースト
- オートフィル
- 関数の基本的な入力方法
この3点が分かれば簡単に作ることができますので、一緒に作ってみましょう!
年間収支が見れるシートを作成
まずは年間収支が見れるシートを作成しましょう。
このシートをもとに毎月の収支を入力するシートを作成するので、効率よく作業出来ます。
- STEP
家計簿に載せたい項目を書き出す
思いつく項目を書き出す まずは、エクセルシートに家計簿に載せたい項目を書き出してみましょう。
1行目は次のステップで入力しますので、2行目から入力を開始します。
A列に「収入」「支出」と見出しを入力し、B列に項目を入力しておくと作業効率が良いしょう。
- STEP
セルに1月から12月まで入力
1月から12月までのセルを作成 「C1」セルに1月と入力し、続けて2月から12月まで入力します。
「C1」セルを選択した状態でフィルハンドルを「N1」セルまでマウスドラッグすることで、12月までの連続データを入力することができます。
「フィルハンドル」とは、選択しているセルの右下に表示されている黒い小さな四角形のことだよ!
Dr.オフィス
今回の解説ではフィルハンドルを活用することが多いので、自信がない方は≫オートフィルの使い方をチェックしておくことをお勧めします。
- STEP
レイアウトなどを調整する
フォーマットを整える 見栄えがよくなるように、罫線をひいたりセルの色を変えてみましょう。
色はあまり濃くなりすぎないほうが見やすいです。
また、「収入」「支出」「収支」の行はフォントを大きくしておくと視認性が高くなるのでお勧めです。
あとになって「項目を追加したい」と思った場合は、以下の手順で追加しましょう。
- STEP
項目を追加したい行を右クリックし「挿入」をクリック
行を右クリック - 項目を追加したい行を右クリック
- 「挿入」を押す
- STEP
行が挿入された
行が挿入された 前のセルの書式設定が反映されますので、罫線やセルの色などを必要に応じて変更しましょう。
- STEP
追加したい項目を入力
項目を追加 追加したい項目名を入力して終了です。
月ごとの収支を入力するシートを作成
次に、年間収支が見れるシートをもとに、月々の収支を入力するシートを作成します。
紙の家計簿のように、給与や家賃などの固定費を別の表に作成する方法もあります。
ですが、1つの表にすべて記載する方式にしたほうが、お金の流れが見やすいのと、エクセルで作るのが簡単なのでお勧めです。
毎月15日引き落としなど、引き落とし日が土日祝日になった場合の対応もひと目で分かるのでお勧め!
![Dr.オフィス](https://excel-doctor.jp/wp-content/uploads/fukidashi2.jpg)
Dr.オフィス
まずは1か月分を作成し、のちほど、完成したシートを11回コピーしましょう。
- STEP
新しいシートを追加する
シートを追加する シート名の隣にある「+」のボタンを押し、シートを追加します。
- STEP
シート名を「月」に変更
シート名を変更 あとでシートをコピーするため、今の段階では「月」とシート名を変更しておきましょう。
- STEP
項目が入力されたセルを選択
項目を選択 項目が書かれているセルを範囲選択しましょう。
ただし、「収支」は使用しないため選択しません。
- STEP
セルをコピーする
項目をコピーする 選択したセルの上で右クリック→コピーを押します。
- STEP
「月」シートに移動
「月」シートに移動する - シート名「月」をクリックして移動
- 「B1」セルをクリック
- STEP
「形式を選択して貼り付け」を選択
形式を選択して貼り付け 「B1」セルを右クリック→「形式を選択して貼り付け」をクリック
- STEP
貼り付ける形式を選択
貼り付ける形式を選択 - 「罫線を除くすべて」ボタンを押す
- 「行/列の入れ替え」にチェック
- 「OK」をクリック
- STEP
レイアウトを整える
レイアウトを整える 行列を入れ替えて、毎日の入力がしやすいようにしました。
レイアウトを調整して項目を見やすいようにしましょう。
- STEP
「合計」欄と日付を追加
合計は一番上にすると効率が良い - 「A3」セルに「合計」と入力
- 「A4」セルに「1日」と入力し、フィルハンドルをドラッグして31日までの連続データを入力
合計欄を一番下(31日の下)にしてしまいがちだけど、合計を確認するたびに画面をスクロールすることとなり効率が悪い・・・
なので、「合計」を一番上(1日の上)にすることをお勧めするよ!
Dr.オフィス
- STEP
レイアウトを整える
レイアウトを調整 「収入」「支出」の列に色をつけて見やすくしましょう。
- STEP
「合計」を集計するための数式を挿入
オートサムを挿入 - 「B3」列(収入合計)を選択
- 「数式」タブをクリック
- 「オートSUM」をクリック
- STEP
1日から31日までのセルを選択
1日から31日までを選択 参照するセルを「B4:B34」とします。
- STEP
数式をフィルハンドルでコピー
右クリックしながらドラッグ - 「B3」セルを選択し、フィルハンドルを右クリックしながら「R3」セルまでマウスドラッグ
- 「書式なしコピー」をクリック
- STEP
コピーした数式を確認
数式の範囲を確認 コピーした数式のセルの範囲が間違っていないか確認します。
- STEP
「収入」欄を集計するための数式を挿入
収入の合計をオートサムで求める - 「B4」セルを選択
- 「数式」タブをクリック
- 「オートSUM」を押す
- STEP
数式の範囲指定をする
収入の項目の列を指定 「収入」の項目である給与・繰り越しの列を指定しましょう。
この場合は「C4:D4」を選択します。
- STEP
「支出」欄も同様に集計する
支出も同様に合計を求める 「E4」セルの「支出」欄についても同様に、項目の合計を求めましょう。
- STEP
数式をコピー
フィルハンドルでコピー - 「B4」セルから「E4」セルまでを範囲選択
- 「E4」セルの右下に表示されたフィルハンドルを右クリックしながらドラッグ
- 「書式なしコピー」をクリック
- STEP
数式が正しく反映されているか確認
セルの範囲を確認 数式が正しくコピーされているか、最終行の数式をチェックしましょう。
- STEP
ウインドウ枠の固定を設定
ウインドウ枠を固定する - 「B4」セルをクリック
- 「表示」タブを選択
- 「ウインドウ枠の固定」をクリック
- 「ウインドウ枠の固定」を押す
選択するセルに注意!固定したいセルの隣をクリックするよ!
この場合は、日付(A列)と合計より上の行(3行)を固定したいから、「B4」セルをクリックしよう!
Dr.オフィス
- STEP
正しく固定されているか確認
スクロールして確認 マウスで画面をスクロールさせ、固定位置が正しいかチェックします。
- STEP
「月」シートをコピー
シートをコピーする 「月」シートタブの上にマウスのカーソルを合わせ。Ctrlを押しながら右にドラッグします。
- STEP
「月」シートのコピーを11個作成する
コピー後にシート名を変更 「月」シートを11個コピーしましょう。
そのあとで、シート名を「1月」~「12月」に変更します。
簡単な数式で月別の収支を年間収支に反映
最後に、簡単な数式を使って月別のシートの収支を年間収支のシートに反映させましょう。
関数を1つ使用するのと、置換をするだけで簡単に反映させることができます。
- STEP
1月の収入・支出を範囲指定
D5からD21までを範囲指定 1月の収支を反映させるセルを範囲選択しましょう。
この場合は「D5」セルから「D21」セルまでを選択します。
- STEP
TRANSPOSE関数を選択する
TRANSPOSE関数の挿入 - 「関数の挿入」をクリック
- 関数の検索でtransposeの途中まで入力
- 「TRANSPOSE」関数を選択
- 「OK」を押す
TRANSPOSE関数は、表の行列を入れ替える関数だよ!
Dr.オフィス
- STEP
配列を指定する
矢印ボタンを押す 矢印ボタンを押して、表を指定しましょう。
- STEP
「1月」シートをクリック
シートを移動 「1月」シートをクリックします。
- STEP
- STEP
内容を確認してOKを押す
数式を確認する 数式にシート名が入っていること、セル番地に絶対参照が設定されていることを確認して「OK」をクリックしましょう。
- STEP
行・列が入れ替えて表示された
TRASPOSE関数で行列が入れ替わった 「1月」シートの内容が正しく反映されていることを確認します。
- STEP
収支を計算する数式を挿入
数式を手入力する 「D22」セルに収支を計算する数式を手入力します。
セルに直接「=D5-D8」と入力しましょう。
- STEP
数式を12月までコピー
フィルハンドルでコピー - 「D5」セルから「D22」セルまでを範囲選択
- 「D22」セルの右下に表示されたフィルハンドルをO列までマウスドラッグ
- STEP
1月のデータがコピーされた
「1月」シートの内容が反映 数式がコピーできましたが、これはすべて「1月」シートの内容です。
次のSTEPで、シートの参照先を2月~12月に変更しましょう。
- STEP
「置換」を選択
置換を選択する - 「E5」セルから「E21」セルまでを範囲選択
- 「ホーム」タブをクリック
- 「編集」を押す
- 「検索と選択」を押す
- 「置換」をクリック
ショートカットキーCtrl+Hでも置換が選択できるよ!
Dr.オフィス
- STEP
参照先を「1月」シートから「2月」シートに変更
シートの参照を2月に変更 - 検索する文字列に「1月」と入力
- 置換後の文字列に「2月」と入力
- 「すべて置換」を押す
シート名を「1月」「2月」・・・「12月」にしていない場合は、実際のシート名のとおりに入力してね!
Dr.オフィス
- STEP
シート名が変更されたことを確認
シート名を確認 - シート名が「1月」から「2月」に変更されたことを確認
- 「OK」を押す
- STEP
同様に3月~12月を置換
3月以降も置換 同様に、3月から12月までについても範囲選択→置換で変更させて完成です。
このように、エクセル初心者であっても、最低限の関数と操作で家計簿を作ることができました。
途中、≫表の行列を入れ替える操作が出てきました。特にTRASPOSE関数は参照元の数字が変わった場合でもその数値を自動更新してくれて便利ですので、ぜひマスターしてくださいね。
エクセルで家計簿の作り方【中級者向け】
エクセル初心者
エクセルの機能をもっと活用して、よりカッコいい家計簿を作れないかな?
ピボットテーブルを活用すると、関数不要で、しかも更新がラクな家計簿が作れるよ!
Dr.オフィス
エクセルはある程度使えて、初心者向けの家計簿よりも効率よいものが作りたい。
そんな方にオススメの作成方法が、ピボットテーブルを活用する方法です。
「ピボットテーブル」と聞くと難しそうなイメージがありますが、一度形を作ってしまえばあとはデータの更新も楽だし、なにより関数を使う必要がありません。事前に電卓で数字を足し上げておく必要もないです。
出来上がったピボットテーブルをコピーして活用することで、家計簿を元にしたダッシュボードも作れます。
エクセルのピボットテーブルを活用して、簡単に使い続けられる家計簿を作成しましょう!
家計簿入力用の表を作成
エクセルで家計簿を作るために、まずは日々の収支を入力するシートを作りましょう。
A列には今回の解説のなかで唯一の関数が入りますが、それ以外はすべて手入力で登録します。
I列・J列には、入力補助用のリストとして、収入・支出の項目をあらかじめ記載しておきましょう。
項目は後から追加・変更することもできるよ!
Dr.オフィス
- STEP
手入力する箇所、関数を挿入する箇所を確認
A列以外はすべて手入力 A列には「西暦年」+「00月」と表示されるよう関数を挿入します。
今回の解説のなかで唯一の関数となります。
もしも関数が苦手でしたら、手入力でもかまいません。
関数を使って、1月~9月の場合でも01月~09月と表示されるようにします。
これは、ピボットテーブルを作成した際に、カレンダーどおりの順番で並んでもらうためです。
- STEP
A列に年月を自動表示するための関数を挿入
- STEP
C列に入力用のリストを表示させる
データの入力規則を選択 - C列をクリック
- 「データ」タブをクリック
- 「データの入力規則」アイコンの下向き「く」の字を押す
- 「データの入力規則」をクリックする
C列には、I列にある「収入」「支出」をリストとして表示させるよ!
Dr.オフィス
- STEP
C列に入力用のリストを表示させる(続き)
データの入力規則を設定 - 入力値の種類を「リスト」にする
- 元の値の「↑」をクリックし、I列の「収入」「支出」を範囲選択する
- 「OK」を押す
- STEP
C列に入力用のリストを表示できた
- STEP
D列にも入力用のリストを表示させる
項目もリストで選べるようにする STEP3~4と同様、D列にも入力規則を登録し、リストから選択できるようにしましょう。
あらかじめリストから選択できるようにしておくことで、家計簿の項目がダブってしまうことを防げます。
「ガソリン代」と「ガソリン費」など、一文字違うだけでも別の項目になってしまうので、リストで選べるようにしておくのがおすすめだよ!
Dr.オフィス
このフォーマットを使って、収入や支出があるたびに1行ずつ入力します。レシートを溜めておいて1週間まとめて入力してもいいですね。
レイアウトや項目は使いやすいように適宜改良しましょう。
≫エクセルの入力規則の設定方法を覚えておくと、家計簿以外にも様々なツール作成に活用できて便利です。
入力規則をマスターすることで仕事の効率が格段に上がりますので、併せてご参照ください!
集計用の家計簿を作成
次に、入力がある程度たまってきたら集計用の表を作成しましょう。
これが、日ごろ私たちが目にする「家計簿」のフォーマットだね!
Dr.オフィス
ピボットテーブルを使えば自動的に集計してくれるため、関数を一切使わずに家計簿のフォーマットを作れて便利です。
- STEP
ピボットテーブルを挿入する
挿入→ピボットテーブルを選択 - 入力用の表の任意のセルをクリック
- 「挿入」タブをクリック
- 「ピボットテーブル」を押す
- STEP
ピボットテーブルの配置先を設定
表の範囲などを指定する - 「表または範囲の選択」の「↑」をクリックし、入力用の表のA列~G列を選択する
- ピボットテーブルの配置場所を「新規ワークシート」とする
- 「OK」を押す
範囲選択を列全体にしておくことで、データの更新がラクになるよ!
Dr.オフィス
- STEP
シート名を「ピボット」に変更
シート名を変更 ピボットテーブルがあるシートだと分かるように、シート名を「ピボット」に変更します。
シート名は「家計簿」など、自分が分かる名前でかまいません。
- STEP
ピボットテーブルのフィールドを設定する
表示させる項目を設定する - 「大分類」を「行」にドラッグ
- 「金額」を「値」にドラッグする
- STEP
ピボットテーブルに「収支」を表示させる
集計アイテムを選択 - ピボットテーブルの任意の場所をクリックする
- 「ピボットテーブル分析」タブを押す
- 「フィールド/アイテム/セット」の下向き「く」の字から、「集計アイテム」を選択
- STEP
ピボットテーブルに「収支」を表示させる(続き)
ピボットテーブル上で計算をする - 「名前」欄に「収支」と手入力
- 「数式」欄に「=収入-支出」を入力
- 「OK」ボタンを押す
フィールドは「収入」「支出」が入力されている「大分類」を使うよ!
Dr.オフィス
- STEP
ピボットテーブルフィールドに「年月」と「項目」を追加
「項目」は「大分類」の下にドラッグ - ピボットテーブルのフィールドの「列」に「年月」をドラッグ
- 「項目」を「行」に追加
見やすい家計簿にするため、「項目」は「大分類」の下にドラッグしよう!
Dr.オフィス
- STEP
「収支」フィールドを折りたたむ
マイナスを押して折りたたむ 「収支」フィールドに支出項目を表示させる必要はないため、左側の「-(マイナス)」ボタンを押してフィールドを折りたたみます。
- STEP
「空白」フィールドを非表示にする
空白データは非表示にする - 「空白」フィールドを右クリック
- 「フィルター」を選択
- 「選択した項目を表示しない」をクリック
- STEP
行の「総計」を削除する
総計は使用しないため削除 - 「総計」フィールドを右クリック
- 「総計の削除」をクリックする
同様に、列(右端)にある「総計」も削除しよう!
Dr.オフィス
- STEP
「収入」と「支出」の並び順を変更する
ドラッグで並び順を変更できる 「収入」フィールドの行を選択し、「支出」フィールドの上までドラッグします。
同様に、「支出」フィールド内の項目についても並び順を整えましょう。
- STEP
「大分類」フィールドの色を塗る
「大分類」フィールドの色を塗ることで見やすくなる 「収入」「支出」「収支」フィールドを任意の色で塗ることで視認性が高まります。
テンプレートを使わなくても、シンプルで見やすい家計簿が作れたね!
Dr.オフィス
年間収支の計算をしたい場合
家計簿の年間収支を計算したい場合も、ピボットテーブルの機能を使えば自動的に集計することができます。
手作業で列を挿入したり、関数を追加する必要はありません。
- STEP
年間の収支を計算したい場合は「グループ化」を行う
1月~12月をグループ化することで年間収支が求められる - 1月~12月の列を選択し右クリック
- 「グループ化」をクリック
- STEP
グループ名を変更
グループ名を任意の名前に変更 列ラベルの下に「グループ1」が作成されました。
これは好きな名前に変更できますので、分かりやすい名前に変更しましょう。
「グループ1」セルをクリックし、今回は「2024年」と入力します。
- STEP
年間収支を追加することができた
グループの最終列に「集計」が追加される グループの最終列に「2024年」の集計が表示されました。
- STEP
年間収支は折りたたみ/展開が可能
プラスボタンで展開できる 過去のデータがたまってきたら、STEP13~15の手順でグループ化することで年間収支を作成し、コンパクトに折りたたむことができます。
月別の収支が見たくなった場合は、グループ化の名前の左側にある「+(プラス)」ボタンを押せばデータを展開できます。
≫ピボットテーブルのグループ化は役立つ機能であるにも関わらず、操作方法を意外と忘れがちです。
これを機に操作方法などを復習しておきましょう!
データの更新をしたい場合
家計簿のデータを新たに追加した場合や金額を修正した際など、集計結果の更新をしたい場合は、次の手順で行います。
- STEP
「更新」を押す
右クリック→「更新」を押す - ピボットテーブルの任意の場所を右クリックする
- 「更新」をクリック
- STEP
列ラベルから追加するデータを選択
列ラベルから選択 - 「列ラベル」の下向き三角▼を押す
- 追加する年月のチェックボックスにチェックを入れる
- 「OK」を押す
この場合は「2026年1月」を追加しているよ!
Dr.オフィス
- STEP
データが更新された
2026年1月のデータが追加された 2026年1月のデータを追加することができました。
「集計」列は設定しない限り毎月表示されてしまうため、複数月たまってから改めてグループ化をすると良いでしょう。
エクセルで家計簿の作り方【上級者向け】
中級者向けの項目で作った家計簿表示用のピボットテーブルをベースに、家計簿のダッシュボードを作成してみましょう。
これは必須の作業ではありませんが、一度、型を作ってしまえば、あとはボタンを押すだけでグラフの更新を自動的に行うことができます。
よりよい家計管理のためにも、家計簿のダッシュボード作成はお勧めです!
今回は例として、次の5つの作成例を解説します。
- 収入・支出・収支差の表示
- 月別収支の推移をグラフ化
- 項目別支出割合をグラフ化
- 収支差の累計をグラフ化
- 項目別の推移をグラフ化
中級者向けで作成したピボットテーブルを活用するため、簡単にグラフを作れます。
事前準備として、5つのピボットテーブルを編集するための新しいシートを作成しておこう!
Dr.オフィス
グラフ作成用のピボットテーブルを作る
まずは、グラフのもととなるピボットテーブルを作ります。
家計簿として作成したピボットテーブルをコピーして編集するだけですので、イチからグラフを作る必要はありません!
- STEP
ステップ②で作成したピボットテーブルをコピーし、新しいシートに貼り付け(1つ目)
- STEP
収入・支出・収支差を表示するためのピボットテーブルを編集
貼り付けたピボットテーブルを編集 - ピボットテーブルのフィールドより、「大分類」を列にドラッグ
- 「金額」を値にドラッグ
- STEP
ピボットテーブルの上に任意のタイトルを入力
自分が分かりやすいタイトルでOK 後で分かりやすいように、ピボットテーブルの上のセルにタイトルを入力します。
今回は「収支」としました。
- STEP
ピボットテーブル名を設定
ピボットテーブルの上のセルと同じ名前を入力 - ピボットテーブルの任意の場所をクリック
- 「ピボットテーブル分析」タブを押す
- 「ピボットテーブル」の下向き「く」の字を押す
- ピボットテーブル名にSTEP3で入力したタイトルと同じ名前を入力
この「ピボットテーブル名」は後で使うので、忘れずに変更しておこう!
Dr.オフィス
- STEP
ピボットテーブルをコピー(2つ目)
2つ目のコピーを行う STEP4で作成したピボットテーブルを選択し、Ctrl+Cでコピーします。
- STEP
右側のセルに移動して貼り付け
- STEP
月別収支の推移用のピボットテーブルを編集
ピボットテーブルのフィールドを変更 ピボットテーブルのフィールドより、「年月」を行に追加します。
- STEP
ピボットテーブルの上にタイトルを入力
月別収支と入力 ピボットテーブルの上のセルにタイトルを入力しましょう。
今回は「月別収支」としました。
- STEP
ピボットテーブル名を設定
ピボットテーブルの上のセルと同じ名前を入力 - ピボットテーブルの任意の場所をクリック
- 「ピボットテーブル分析」タブを押す
- 「ピボットテーブル」の下向き「く」の字を押す
- ピボットテーブル名にSTEP8で入力したタイトルと同じ名前を入力
- STEP
ピボットテーブルをコピーし、フィールドを設定(3つ目)
3つ目のピボットテーブルをコピーし、フィールドを編集する - STEP9で作成したピボットテーブルをコピー貼り付けし、フィールド「年月」のチェックを外す
- 「項目」を行へドラッグ
- STEP
列に表示する項目を編集する
支出のみを表示させる - 列ラベルの右にある下向き三角▼をクリック
- 「収入」「収支」のチェックを外す
- 「OK」ボタンを押す
- STEP
値フィールドを「比率」に変更をする
値フィールドの設定を押す - 「ピボットテーブルのフィールド」より「値」にある「合計/金額」を右クリック
- 「値フィールドの設定」を押す
- STEP
計算の種類を変更する
列集計に対する比率に変更 - 「計算の種類」タブをクリック
- 計算の種類を「列集計に対する比率」に変更
- 「OK」ボタンを押す
- STEP
支出全体に対する構成割合が表示された
数式を入力せずに計算方法の変更ができた 支出全体に対する構成割合(比率)を表示させることができました。
- STEP
ピボットテーブルの上にタイトルを入力
タイトルを入力 ピボットテーブルの上のセルにタイトルを入力しましょう。
今回は「支出に対する割合」としました。 - STEP
ピボットテーブル名を設定
ピボットテーブルの上のセルと同じ名前を入力 - ピボットテーブルの任意の場所をクリック
- 「ピボットテーブル分析」タブを押す
- 「ピボットテーブル」の下向き「く」の字を押す
- ピボットテーブル名にSTEP15で入力したタイトルと同じ名前を入力
- STEP
ピボットテーブルをコピーし、フィールドを設定(4つ目)
4つ目のピボットテーブルをコピーし、フィールドを編集する - STEP16で作成したピボットテーブルをコピー貼り付けし、フィールド「年月」を行にドラッグ
- 「項目」をフィルターへドラッグ
- 値にある「合計/金額」を右クリック
- 「値フィールドの設定」を押す
- STEP
値フィールドの計算方法を変更する
値フィールドの設定をする - 「計算の種類」タブをクリック
- 計算の種類を「計算なし」に変更
- 「OK」ボタンを押す
- STEP
ピボットテーブルの上にタイトルを入力
タイトルを入力 ピボットテーブルの上のセルにタイトルを入力しましょう。
今回は「支出の推移」としました。 - STEP
ピボットテーブル名を設定
ピボットテーブルの上のセルと同じ名前を入力 - ピボットテーブルの任意の場所をクリック
- 「ピボットテーブル分析」タブを押す
- 「ピボットテーブル」の下向き「く」の字を押す
- ピボットテーブル名にSTEP19で入力したタイトルと同じ名前を入力
- STEP
ピボットテーブルをコピーし、フィールドを設定(5つ目)
5つ目のピボットテーブルをコピーし、フィールドを編集する - STEP20で作成したピボットテーブルをコピー貼り付けし、フィールド「大分類」を列にドラッグ
- 「項目」のチェックを外す
- STEP
値フィールドを「累計」に変更をする
値フィールドの設定を押す - 「ピボットテーブルのフィールド」より「値」にある「合計/金額」を右クリック
- 「値フィールドの設定」を押す
- STEP
値フィールドの計算方法を変更する
値フィールドの設定をする - 「計算の種類」タブをクリック
- 計算の種類を「累計」に変更
- 基準フィールドを「年月」に設定
- 「OK」ボタンを押す
- STEP
ピボットテーブルの上にタイトルを入力
タイトルを入力 ピボットテーブルの上のセルにタイトルを入力しましょう。
今回は「収支(貯蓄)累計」としました。この時点では「収支差」ではなく「支出」を累計している状態なので、STEP26で修正しよう!
Dr.オフィス
- STEP
ピボットテーブル名を設定
ピボットテーブルの上のセルと同じ名前を入力 - ピボットテーブルの任意の場所をクリック
- 「ピボットテーブル分析」タブを押す
- 「ピボットテーブル」の下向き「く」の字を押す
- ピボットテーブル名にSTEP24で入力したタイトルと同じ名前を入力
- STEP
累計する項目を「収支」に変更
「支出」から「収支」へ変更 - 列ラベルの右にある下向き三角▼をクリック
- 「収支」のみチェックする
- 「OK」ボタンを押す
- STEP
家計簿のダッシュボード作成の元となる表が5つできた
表が5つ出来た これで、ダッシュボード作成の元となるピボットテーブルが5つできました。
これらの表をもとに、ピボットグラフを作成しましょう。
グラフの作成
ピボットテーブルを使って、家計簿のダッシュボードに表示させるグラフを作成します。
「ピボットグラフ」機能を使えば、簡単にグラフを作ることができてデータの更新もラクに行えます。
- STEP
月別収支のピボットグラフを作成する
ピボットグラフを選択 - 「月別収支」ピボットテーブルの任意のセルをクリック
- 「ピボットテーブル分析」タブを押す
- 「ピボットグラフ」をクリック
- STEP
グラフの種類を選択
集合縦棒と折れ線の複合グラフを選択 - グラフの種類から「組み合わせ」を選択
- 「ユーザー設定の組み合わせ」をクリック
- 収支を「折れ線」に変更
- 収支は「第2軸」にチェック
- 「OK」ボタンを押す
「収支」は収入・支出よりも数が小さいので、2軸(右側の軸)を使うと見やすいよ!
Dr.オフィス
- STEP
フィールドボタンを非表示にする
ボタンを右クリックして選択 - 任意のフィールドボタンを右クリック
- 「グラフのすべてのフィールドボタンを非表示にする」をクリック
- STEP
月別収支のピボットグラフが出来た
複合グラフが簡単に出来た レイアウトの調整などはグラフを全て作成したあとで、まとめて行いましょう。
- STEP
構成割合を表すピボットグラフを作成する
ピボットグラフを選択 - 「支出に対する割合」ピボットテーブルの任意のセルをクリック
- 「ピボットテーブル分析」タブを押す
- 「ピボットグラフ」をクリック
- STEP
グラフの種類を選択
構成割合を表すため円グラフを使用 - グラフの種類から「円」を選択
- 「ドーナツ」をクリック
- 「OK」ボタンを押す
作例ではドーナツグラフにしたけれど、もちろん他の円グラフや、積み上げ横棒グラフでも構わないよ!
Dr.オフィス
- STEP
フィールドボタンを非表示にする
ボタンを右クリックして選択 - 任意のフィールドボタンを右クリック
- 「グラフのすべてのフィールドボタンを非表示にする」をクリック
- STEP
構成割合を表すグラフができた
円グラフもピボットグラフで簡単に作れる レイアウトの調整などはあとで行います。
- STEP
支出の推移を表すピボットグラフを作成する
ピボットグラフを選択 - 「支出の推移」ピボットテーブルの任意のセルをクリック
- 「ピボットテーブル分析」タブを押す
- 「ピボットグラフ」をクリック
- STEP
グラフの種類を選択
推移を表すため折れ線グラフを使用 - グラフの種類から「折れ線」を選択
- 「折れ線」をクリック
- 「OK」ボタンを押す
- STEP
フィールドボタンを非表示にして完成
ボタンを右クリックして選択 - 任意のフィールドボタンを右クリック
- 「グラフのすべてのフィールドボタンを非表示にする」をクリック
- STEP
収支の累計を表すピボットグラフを作成する
ピボットグラフを選択 - 「収支(貯蓄)累計」ピボットテーブルの任意のセルをクリック
- 「ピボットテーブル分析」タブを押す
- 「ピボットグラフ」をクリック
- STEP
グラフの種類を選択
累計を表すため面グラフを使用 - グラフの種類から「面」を選択
- 「面」をクリック
- 「OK」ボタンを押す
- STEP
フィールドボタンを非表示にして完成
ボタンを右クリックして選択 - 任意のフィールドボタンを右クリック
- 「グラフのすべてのフィールドボタンを非表示にする」をクリック
- STEP
データのフィルターを視覚的に行うため、スライサーを挿入
スライサーでフィルター処理が簡単にできるように - 任意のグラフを選択
- 「ピボットグラフ分析」タブをクリック
- 「スライサーの挿入」をクリック
選択するグラフはどれでもOKだよ!
Dr.オフィス
- STEP
スライサーとして使う項目を選ぶ
フィルター処理を行う項目を選ぶ - 今回は「年月ごとの収支」「項目ごとの支出の推移」を表示させたいため、「年月」「項目」にチェック
- 「OK」を押す
- STEP
スライサーが作成された
スライサーができた 「項目」「年月」と表示されたスライサーが作成されました。
このままの状態では、スライサーのボタンを押してもフィルターが反映されるのは「収支」グラフのみですので、他のグラフとスライサーの接続を行いましょう。
隣の「収支」グラフの表示がおかしくなっているけど、これから行う「レポートの接続」作業で修正されるから安心してね!
Dr.オフィス
- STEP
「レポートの接続」を選択
スライサーとレポートを接続させる - 「項目」スライサーを右クリック
- 「レポートの接続」を選択
- STEP
「項目」スライサーに接続するレポートを選択
接続するレポートにチェックを入れる - 支出項目ごとにデータを抽出したい「支出の推移」レポートにチェック
- 「OK」ボタンを押す
- STEP
「年月」スライサーに接続するレポートを選択
接続するレポートにチェックを入れる - 年月ごとに支出の構成割合を抽出したい「支出に対する割合」レポートにチェック
- 年月ごとの収支を抽出したい「収支」レポートにチェック
- 「OK」ボタンを押す
- STEP
スライサーのボタンを押すことでグラフが変化するかどうかチェック
グラフが変化することを確認 「項目」スライサー、「年月」スライサーそれぞれを押して、グラフが変化するかどうかチェックします。
もしもグラフに変化がない場合は、スライサーとレポートがうまく接続されていない可能性があります。STEP18以降を再度確認してください。
- STEP
家計簿のダッシュボード用のシートに「収入」を表示させる
セル番地は手入力で挿入 家計簿のダッシュボードを作成するためのシートを新たに挿入し、テキストボックスを挿入します。
数式バーに、ダッシュボード用のピボットテーブルを作成したシート名と、「収支」ピボットテーブルのうち「収入」が表示されているセル番地を入力します。
- STEP
「支出」「収支差」も表示させる
いずれもテキストボックスに表示させる STEP22と同様、「支出」「収支差」もテキストボックスに表示させましょう。
- STEP
グラフとスライサーを移動貼り付け
このように、ピボットテーブルやピボットグラフ、スライサーを活用することで、テンプレートをダウンロードしたり関数を新たに追加することなく、家計簿、および家計簿のダッシュボードを作ることが出来ました。
グラフの内容やレイアウトを変更したくなったら、直感的にすぐに修正できるのも嬉しいポイントです。
作業数は多いと感じるかもしれませんが、ひとつひとつは難しくありません。
特に、家計簿のダッシュボード作成については、ぜひ、チャレンジしてみてください!
なお、≫ピボットテーブルのスライサーは家計簿作成以外にも使える場面が多々ありますので、これを機にマスターしてしまうことをお勧めします!
エクセルで家計簿を作る際に知りたいQ&A
Q
家計簿はアプリかエクセルかどちらがいいですか?
A
家計簿はアプリを使うよりもエクセルで作成するほうをお勧めします。
レイアウトなど自由にカスタマイズでき、データ分析や長期的な保存もしやすいです。
ライフスタイルの変化に応じて家計にぴったり合わせられます。
形を作るのは少し大変ですが、エクセルの操作方法を学ぶ意味でも、エクセルで作成したほうがメリットが多いでしょう。
Q
家計簿をエクセルでつけるメリットは?
A
次のようなメリットがあります。
- 自由にレイアウトやデザインの変更ができる
- データを蓄積して分析が可能
- 長期的な保存ができる
一番最初に形を作る手間がデメリットですが、それも、ピボットテーブルを活用すればそれほど面倒ではありません。
Q
家計簿はつけた方がいいですか?
A
はい、家計簿をつけることをおすすめします。
家計簿をつけることで、どこにお金を使っているのか明確になり、無駄な出費を発見しやすくなります。
また、貯金や大きな買い物の計画を立てやすくなることに。
お金に対する意識が高まることで、国や自治体の行政に興味が湧くことにもつながります。
手間を少しでも軽くするためにも、エクセルで家計簿を作ることをお勧めします!
ずっと使える家計簿はエクセルで作ろう!
このように、エクセルで家計簿を作るのは、ご自身のスキルに応じた作成方法を選べば簡単にできます。
一度仕組みを作れば、日々の作業はレシートを入力するだけ。電卓を使ったりエクセルの関数を何度も作り直す必要もありません。
最後にエクセルで家計簿を作る3ステップのおさらいです。
- 初心者なら最低限の手順と数式でシンプル家計簿を作ろう
- 上級者ならピボットテーブルで関数やテンプレートなしに家計簿を作成
- さらに上級者なら家計簿のダッシュボードも作ってみよう
3ステップで、あなたのスキルに応じて無理なく家計簿を作りましょう。
≫ダッシュボードの作り方の詳細も参照にしつつ、ぜひ、あなたにぴったりな家計管理の仕組みを作ってくださいね!
1 件のコメント