スプレッドシートで家計簿を作成する方法

Googleスプレッドシートで家計簿を作りたい。

そんな悩みはありませんか？

スプレッドシートとエクセルは見た目は似ていますが、エクセルで使える関数がスプレッドシートでは使えないなどの制限があります。

制限があるのであきらめるのではなく、他の数式を使ってエクセルと同等の機能がある家計簿の作り方を紹介します。

この家計簿は大量のデータを集計、分析できるピボットテーブルを使用します。

費目別や口座別、お店別などさまざまな視点で分析できるので、しっかり分析したい人におすすめです。

動画での説明はこちらから↓↓

家計簿の特徴

• 設定シートで費目、口座名、店名を入力
• 入力シートで日々の家計簿の記録
• 収支表シートで毎月の家計簿の比較
• 費目別、口座別、お店別など知りたい情報のみをピックアップ

入力シート

ピボットテーブル

スライサーで特定の項目を検索

家計簿の作り方

ファイルの新規作成

Googleスプレッドシートのページを開きます。

スプレッドシートを使用するのにアカウントが必要になります。
まだお持ちでない方は新規作成してからご使用ください。

スプレッドシートにログイン後、「空白のスプレッドシート」をクリック。

スプレッドシートに名前付けをして下さい。

設定シート

初めにすること

左下の＋ボタンをクリックして、シートを追加。
シート名を「設定」に変更。

表を見やすくするために、グリッド線を外します。

左上の四角をクリックして、すべての範囲を選択。
表示のグリッド線のチェックを外してください。

費目の設定

• 収入
• 税金
• 貯蓄
• 固定費
• 変動費
• 口座
• 店名

収入の下の行には必ず「収入1」と入力してください。

入力シートでの設定で必要になります。

枠組みを設定

枠線をつけたい範囲を選択して、メニューバーの表のマークをクリック。

表の種類とラインの種類を選択。

ラインの色を変更。

背景色の設定

背景色を変更したい範囲を選択して塗りつぶしのマークを選択。

気に入った色がない場合、「カスタム」をクリック。
オリジナルの色を作成。

文字のサイズ、書式の変更

文字の書式をクリックして、書式を選択。

サイズのーボタン、＋ボタンまたは直接文字サイズを入力し文字サイズを変更。

サンプル値の入力

入力シートを作成する際にサンプル値が必要になります。

枠組みを作成後、サンプル値を入力。

入力シート

シートを追加して名前付けとグリッド線を外す。（設定シートの初めにすることを参照）

見出し行の作成

• 日付：カレンダーを表示
• 項目１：収入か支出かを選択
• 項目２：収入1、税金、貯蓄、固定費、変動費から選択
• 費目：それぞれの項目２の費目名を表示
• 品名：手入力
• 金額：手入力
• 店名：設定の店名を表示
• 口座：設定の口座名を表示
• メモ：手入力

見出し行を作成して、枠組み、背景色を変更。

文字の配置を変更する場合は、範囲を選択して「水平方向の配置」をクリック。

日付の設定

日付の列にカレンダーを表示させます。

セルB3をクリックして、「データ」の「データの入力規制」を選択。

「＋ルールを追加」をクリック。

条件の項目から「有効な日付」をクリックして完了ボタンをクリック。

セルB3をダブルクリックするとカレンダーが表示されます。

項目１の設定

項目１では、収入か支出かを選択できるようにタブを設定します。

セルC3をクリックして、「データ」、「データの入力規制」をクリック。
「＋ルールの追加」をクリック。（カレンダー設定と同様の手順）

条件の項目は、「プルダウン」にしてオプション１に「収入」、オプション2に「支出」と入力。

収入、支出の左にある色ボタンで色変更をすると、タブから選択するときに色付けされます。

「完了」ボタンをクリック。

セルC３にタブが追加されて、「収入」「支出」を選択できるようになります。

項目２の設定

項目２では、項目１が収入の場合は、「収入１」、支出の場合は、「税金、貯蓄、固定費、変動費」から選択できるようにします。

エクセルでは、関数を使って簡単に設定できますが、スプレッドシートにはこの機能がありません。

そこでKからN列を使用して、項目１が収入の場合は、K列に「収入1」、支出の場合はKからN列に「税金、貯蓄、固定費、変動費」を表示させます。

その後、KからN列の値をタブから選択できるように設定します。

項目名を設定

設定シートの空いているスペースを使用して、項目名を設定します。

• 収入：収入1
• 支出：税金、貯蓄、固定費、変動費

入力シートのKからN列に数式を入力するための枠を設定。

数式の入力

縦列（収入、支出）に検索して、特定のデータを取り出す（収入1または税金、貯蓄、固定費、変動費）機能があるVLOOKUP関数を使用します。

K列に、項目１が収入の場合は「収入1」、支出の場合は「税金」が表示されるように数式を入力します。

=VLOOKUP(C3,'設定’!$L$3:$P$4,2,0)

範囲は、設定シートのL3からP4をドラッグ。
コピーして貼り付ける際にずれないように＄マークで固定（$L$3:$P$4)

検索の方は、完全に一致する場合のみ表示させるので、「0」と入力。

項目１が空白の場合は、エラーが表示されます。

エラー表示が気になる場合、IFERROR関数で空白にする処理を行います。

=IFERROR(VLOOKUP(C3,'設定’!$L$3:$P$4,2,0),"")

エラーの時に空白にする関数です。

「””」は空白を表す記号。

K3をコピーしてLからN列に貼り付け。

C3がズレるので、C3に修正してください。

列番号は、１ずつ足してください。（Lは３、Mは４、Nが５）

項目２にタブを設定

セルD3をクリックして、「データ」「データの入力規制」を選択。

条件は、「プルダウン（範囲内）」を選択。

その下のデータ範囲の選択からK3からN３を選択して「完了」ボタンをクリック。

セルD３にタブが追加されて、項目１の内容に沿った項目名が表示されます。

費目の設定

項目２の設定と同様に数式列を作成して、項目１に合わせた費目名を表示させます。

数式で使用する範囲は、設定シートのB3からF13を使用します。

数式列の作成

費目名は１０個まで登録できるので、１０列分（OからX列まで）数式枠を作成します。

数式の入力

横列（収入1、税金、貯蓄、固定費、変動費）に検索して、特定のデータ（費目名）を取り出す機能があるHLOOKUP関数を使用します。

O列に、項目2の内容に応じて、収入1、税金１、貯蓄１、固定費１、変動費１が表示されるように数式を入力します。

=IFERROR(HLOOKUP(D3,'設定’!$B$3:$F$13,2,0),"")

項目２の設定同様に、エラー回避のためにIFERROR("")を入力。

範囲は、設定シートのB3からF13をドラッグ。
コピーして貼り付ける際にずれないように＄マークで固定（$B$3:$F$13)

検索の方は、完全に一致する場合のみ表示させるので、「0」と入力。

O3の数式をP列からX列まで貼り付けます。

検索値D3がずれてしまうので、D3に修正。

行番号は、左から一つずつ足してください。

X３の行番号は11 になります。

費目にタブを設定

セルE3をクリックして、「データ」「データの入力規制」を選択。

条件は、「プルダウン（範囲内）」を選択。

その下のデータ範囲の選択からO3からX３を選択して「完了」ボタンをクリック。

E列にタブが追加されて、項目２の費目名が表示されます。

金額列に通貨単位を追加

G列を選択して、「表示形式」「数字」「通貨（端数切り捨て）」を選択。

金額欄に数字を入力すると、自動で円マークが表示されます。

店名、口座のタブを設定

店名のタブを設定

H3をクリックして、「データの入力規制」「＋ルールを追加」を選択。

条件の項目はプルダウン（範囲内）を選択して、範囲は設定シートに移動してJ3からJ13をドラッグして完了ボタンをクリック。

タブが追加されて、設定で入力した店名が表示されます。

口座のタブを設定

I3をクリックして、「データの入力規制」「＋ルールを追加」を選択。

条件の項目はプルダウン（範囲内）を選択して、範囲は設定シートに移動してH3からH13をドラッグして完了ボタンをクリック。

タブが追加されて、設定で入力した口座名が表示されます。

表の作成

3行目をコピーして貼り付け

セルB３からX3を選択して下のドラッグして貼り付けます。

この数式は日々の入力には必要のない項目なので、列を非表示にします。

K列からW列を選択して右クリック、「列K-Wを非表示」をクリック。

X列は行を増やす際に必要になるので残してください。

項目１と費目のタブを確認

タブの範囲が固定されている可能性があるので、編集マークから確認します。

D3のタブをクリックして一番下にある編集（ペンマーク）をクリック。

データの範囲がドルマークで固定されてしまっています。

範囲が固定されていると、下の行すべてがK3からN３の範囲が適用されてしまいます。

それぞれの行の範囲になるようにドルマークを消してください。

ドルマークを消して完了をクリック。

「すべてに適用」をクリックするとD列のすべての行の範囲が修正されます。

費目のE3も同様に範囲を変更。

フィルタを作成

項目別、費目別、店名別、口座別などに表示させるためにフィルタを設定します。

見出し（B2からX2)を選択。
「データ」「フィルタを作成」をクリック。

見出し行にフィルタが追加されます。

表示させたい項目のフィルタをクリックして、「クリア」をクリックしてから表示させたい項目にチェックを入れくてださい。

見出し行を固定

2行目を固定してそれ以後の行のみ動かすことで、日々の入力がしやすくなります。

「表示」「固定」「2行」をクリック。

「2行」は上から2行まで固定するという意味です。

スクロールで下に移動すると見出し行は固定されて、それ以降の行のみ可動します。

固定費シート

少しでも入力をかんたんにするため、固定費シートを用意します。

変動費以外の項目をこの欄に入力しておくことで、毎月この内容を左の表にコピーして貼り付けることができます。

入力シートをコピー

シート名「入力」を右クリックして「コピーを作成」をクリック。

コピーされたシートに名前付けをしてください。

サンプル値を入力

固定費シートでは、毎月必要になる項目を入力します。

収支表シートの作成に必要なため、サンプル値を入力します。

日付は空欄、支出金額はマイナス表示にしてください。

月が変わるタイミングで固定費シートの内容を入力シートに貼り付けます。

セルB3からI９を選択。

見出し行は選択しないでください。

入力シートに移動して、入力最終行の下の日付欄をクリック。

「編集」「特殊貼り付け」「値のみ貼り付け」をクリック。

値のみにしないとエラーが表示されます。

日付を入力して金額、口座に変更がある場合は修正してください。

収支表シート

入力シートのデータを基にピボットテーブルを作成します。

ピボットテーブルとは、大量のデータを項目ごとに集計してくれる表のことです。

ピボットテーブルの作成

入力シートの範囲を選択。

見出し行、空白行も含めた表の範囲を選択。

「挿入」「ピボットテーブル」をクリック。

挿入先は、「新しいシート」にチェック。

データ範囲は、選択した範囲が表示されます。

新しいシートにピボットテーブルが表示されます。

右のピボットテーブルエディタを使用して、項目を設定します。

ピボットテーブル表示項目

• 行：項目１、項目２、費目
• 列：日付
• 値：金額

行の追加ボタンをクリックして、「項目１」「項目２」「費目」を選択。

列は「日付」、値は「金額」を追加。

日付ごとの表示から月ごとの表示に変更。

任意の日付を選択して右クリック。

「ピボット日付グループを作成」「月」を選択。

月ごとの表示に変更されます。

収入と支出の順序を入れ替えます。

ピボットテーブルの下の「編集」マークをクリックして項目１の順序を「昇順」から「降順」に変更。

入力シートの表を追加した場合は、ピボットテーブルの範囲を変更。

表の下に表示されている「編集」ボタンをクリックして、範囲を変更してください。

スライサーの作成

費目別や店名別、口座別などに個別に表示させたい。

そんなときのためにスライサーを作成します。

スライサーの機能

例えば入力シートで品名に特定の商品名を入力してその特定の商品名のみを表示させることができます。

楽天やAmazonで月にいくら消費したのか、クレジットカードの月の使用量を知りたい場合にも便利です。

行を挿入

スライサーを表示させるスペースを作るために行を追加します。

５行目くらいまで選択して右クリック、「上に５行挿入」をクリック。

ピボットテーブルの範囲の一部を選択して右クリック。

「データ」「スライサーを追加」をクリック。

スライサーが追加されて、「最初に列を選択してください」という、文言が表示されます。

検索したい項目を選択してください。

同様の手順で検索したい項目を追加。

検索する際は、スライサーの「すべて」をクリック。

「クリア」をクリックした後、検索したい項目にチェックを入れてください。

検索した特定の項目のみ表示されます。

元に戻す場合は、「すべて選択」をクリック。

サンプルダウンロード

ダウンロード後、コピーを作成してからご使用ください。
ダウンロードファイルは閲覧専用で編集ができないようになっています。

サンプル家計簿は今回紹介したテンプレートと多少異なる点がございます。
ご了承のほどよろしくお願いたします。

無料ダウンロード

詳しい使い方はこちらから↓↓

GoogleスプレッドシートもOK!!ピボットテーブルでしっかり分析ができる家計簿(無料テンプレートあり）

Googleスプレッドシートで家計簿を記録したい。 ...

続きを見る