PC関連

エクセルで作る複式簿記の家計簿・簡単な関数とピボットテーブル

エクセルでつくる複式簿記家計簿の初期設定と残高入力をします。

設定で使う機能と関数はとても簡単。

集計はピボットテーブル、使う関数は、VLOOKUP関数と文字列操作の関数です。

 

科目一覧表のテーブル変換

シート「科目」を選択します。

テーブル変換

(1)科目一覧表の任意のセルを選択します。例えばセルA2。

(2)リボン [ テーブル ] > [ 挿入 ] > [ テーブル ] >をクリックします。

エクセル家計簿・複式簿記・テーブル

→ 「テーブルの作成」が表示されます。

(3)「OK」をクリックします。

エクセル家計簿・複式簿記・テーブル

→ テーブルに変換されました。

エクセル家計簿・複式簿記・テーブル

テーブル名はそのままでも使用可能ですが、名前を変更しておく方がわかりやすいです。

テーブルの名前変更

(1)テーブル内の任意のセルを選択します。

(2)リボン [ テーブルデザイン ] > [ プロパティ ] >[ テーブル名 ] でテーブルの名前を変更します。

エクセル家計簿・複式簿記・テーブル

(3)名前は自由に設定可能です。ここでは「kamoku」に変更します。

エクセル家計簿・複式簿記・テーブル

テーブルスタイルの変更

テーブルスタイルを変更したい場合は、以下の手順で変更します。

(1)テーブル内の任意のセルを選択します。

(2)リボン [ テーブルデザイン ] > [ テーブルスタイル ]より好みのスタイルに変更できます。

エクセル家計簿・複式簿記・テーブル

赤い枠のスタイルは、「なし」です。

エクセル家計簿・複式簿記・テーブル

「なし」に設定すると、もとの表示になります。

エクセル家計簿・複式簿記・テーブル

 

ウィンドウ枠の固定

スクロールしたとき、項目名がわかるようにウィンドウ枠を設定します。

(1)セルA2を選択します。

(2)リボン [ 表示 ] > [ ウィンドウ枠の固定 ] > [ ウィンドウ枠の固定 ] をクリックします。

シート「科目」の設定は以上で終了です。

仕訳入力表のテーブル変換と関数の設定

シート「仕訳」を選択し、仕訳入力表を作成します。

仕訳入力表の作成

(1)1行目に項目名を入力します。項目名は自分がわかるもので構いません。

(2)1行目と2行目に罫線を設定します。

エクセル家計簿・複式簿記・仕訳入力表

ピンク色の項目に関数を設定します。

(3)ウィンドウ枠の固定をします。設定方法はシート「科目」と同じ。

セルA2を選択して、リボン [ 表示 ] > [ ウィンドウ枠の固定 ] > [ ウィンドウ枠の固定 ] をクリックします。

関数の設定・科目

項目「科目」にVLOOKUP関数を設定します。

VLOOK関数がどのような関数か知りたい場合は以下の記事を参照してください。

(1)セルE2をクリックし、以下の式を入力します。上記の仕訳入力表と同じく作成している場合は、以下の式をコピー&ペーストします。

=VLOOKUP(C2,kamoku,2,FALSE)

(2)「科目CD」に科目コード「100」を入力します。

エクセル家計簿・複式簿記・仕訳入力

式の設定が正しければ、セルE2に科目一覧表で設定している科目名が表示されます。

自分で式を設定する場合

(1)「=Vloo」等、途中まで入力すると、関数の候補が表示されます。

(2)VLOOKUPをクリックし、式の続きを設定し「Enter」キーを押します。

→ 科目コードが入力されていないのでエラー値の「#N/A」が表示されます。

エクセル家計簿・複式簿記・仕訳入力

エラー値を表示したくない場合は以下のような式にします。

=IFERROR(VLOOKUP(C2,kamoku,2,FALSE),"")

私はエラー値表示のまま使用してます。

関数の設定・グループ

項目「グループに」にLEFT関数を設定します。

セルC2をクリックし、以下の式を入力します。上記の仕訳入力表と同じく作成している場合は、以下の式をコピー&ペーストします。

=LEFT(C2,1)

LEFT関数がどのような関数か知りたい場合は以下の記事を参照してください。

  • 文字を取り出す関数

(1)「=lef」等、途中まで入力すると、関数の候補が表示されます。

(2)LEFTをクリックし、式の続きを設定し「Enter」キーを押します。

エクセル家計簿・複式簿記・LEFT関数

→ 科目コードの一番左の数字を表示します。

エクセル家計簿・複式簿記・LEFT関数

関数の設定・貸借区分

H列に貸借区分を表示する関数を設定します。集計に使う項目です。

集計方法について

この区分を使った集計方法は、応用編として記事を載せる予定でいましたが、サンプルデータを作るのが大変でまとめていません。

エクセルがわかる方なら、ピボットテーブルを使い自分で作ることができるかと思います。

(1)セルH2を選択します。

エクセル家計簿・複式簿記・仕訳入力表

(2)IF関数を設定します。

=IF(A2="K","繰越",IF(F2>0,"借方",IF(F2<0,"貸方")))

エクセル家計簿・複式簿記・仕訳入力表

F列の「金額」項目にプラス金額を入力すると「借方」が表示されます。

エクセル家計簿・複式簿記・仕訳入力表

F列の「金額」項目にマイナス金額を入力すると「貸方」が表示されます。

エクセル家計簿・複式簿記・仕訳入力表

A列の「引落」項目に「K」を入力すると「繰越」が表示されます。

エクセル家計簿・複式簿記・仕訳入力表

※「K」は大文字と小文字どちらでも同じ。

関数の設定は以上です。

仕訳一覧表のテーブル変換

科目一覧表と同様にテーブル変換します。

(1)テーブル内の任意のセルを選択します。

(2)リボン [ テーブルデザイン ] > [ プロパティ ] >[ テーブル名 ] でテーブルの名前を変更します。

(3)名前は自由に設定可能です。ここでは「shiwake」に変更します。

エクセル家計簿・複式簿記・テーブル名

ウィンドウ枠の固定

スクロールしたとき、項目名がわかるようにウィンドウ枠を設定します。

(1)セルA2を選択します。

(2)リボン [ 表示 ] > [ ウィンドウ枠の固定 ] > [ ウィンドウ枠の固定 ] をクリックします。

シート「仕訳」の設定は以上で終了です。

 

シート「仕訳」・書式等の設定

入力しやすくするために、シート「仕訳」に書式設定等をします。

入力規則の設定

違う項目のセルを選択する度にIMEの切り替え(ひらがなや英数字)をする手間を省くための設定です。

  • GI列のセルを選択した場合:ひらがな
  • G列以外のセルを選択した場合:英語

(1)シート「仕訳」を選択します。

(2)A列~I列まで選択し、リボン [ データツール ] > [ データの入力規則 ] をクリックします。

エクセル家計簿・複式簿記・仕訳入力表

(3)「選択範囲には複数の入力規則が設定されています。」のメッセージが表示された場合は、「OK」をクリックします。

エクセル家計簿・複式簿記・書式設定

→ 「データの入力規則」が表示されます。

(4))タブ [ 日本語入力 ] > [ IME、日本語入力 ] の右下をクリックし、「オフ(英語モード)」を選択し「OK」をクリックします。

エクセル家計簿・複式簿記・書式設定

(5)G列を選択し、リボン [ データツール ] > [ データの入力規則 ] を選択し「OK」をクリックします。

エクセル家計簿・複式簿記・仕訳入力表

(6)タブ [ 日本語入力 ] > [ IME、日本語入力 ] の右下をクリックし、「ひらがな」を選択します。

エクセル家計簿・複式簿記・書式設定

入力規則の設定は以上で終わりです。

日付の書式設定

B列の日付の書式設定をします。

(1)A~B列を選択します。

(2)セルの書式設定を開きます。「Ctrl」キークリックした後、数字の「1」をクリックします。

他の方法:右クリック>「セルの書式設定」をクリックします。

(3)好みの表示を設定します。以下は、月日と曜日の設定方法です。

分類:ユーザー定義 > 種類「mm/dd(aaa)」

エクセル家計簿・複式簿記・仕訳入力表

年も表示したい場合は「yyyy」や「yy」を先頭に追加します。yyyy→2019、yy→19

(4)セルB2に開始する日付を入力します。

エクセル家計簿・複式簿記・書式設定

曜日の表示不要の場合は、分類の「日付」より好みの表示を選択します。

 

シート「仕訳」・開始残高の入力

シート「仕訳」に現預金の残高やクレジットカードの未払金等を入力します。

開始残高入力

(1)シート「科目」を選択します。

(2)110番~300番まで選択して、コピー(Ctrl + C)をします。

エクセル家計簿・複式簿記・残高入力

(3)シート「仕訳」を選択します。

(4)セルC3を選択して、右クリックより「値の貼り付け)をクリックします。

エクセル家計簿・複式簿記

→ データを貼り付けた値のある範囲までテーブル範囲が自動で拡張されます。

エクセル家計簿・複式簿記

値の貼り付けのショートカットは、早くなるとは思えないので使ってません。

  1. CtrlキーとAltキーを同時に押し、「V」キーを押す
  2. Vキーを押す。
  3. Enterキーを押す。

1番は片手じゃ厳しい。右クリックで操作した方が早い。マクロ利用でもそんなに早くなりませんでした。

(5)残高がある科目は、残高を入力します。残高がない場合は行を削除します。

エクセル家計簿・複式簿記・残高入力

(6)負債科目は金額をマイナスにします。

エクセル家計簿・複式簿記・残高入力

(7)F列の科目「元入」をクリックし、リボン[ 編集 ]>[ オートサム ]をクリックします。

テーブル変換している場合は、SUBTOTAL関数が設定されます。

=SUBTOTAL(109,F2:F11)

(8)元入れの金額を値のみにして、マイナス(-)金額にします。

(9)A列に「K」と入力します。
※A列の「引落」はクレジットの引き落としと集計表を作成するときに使います。

(10)摘要に「開始残高」等残高の入力とわかるような内容を入力します。

エクセル家計簿・複式簿記・仕訳入力表

(11)F列の金額の合計が「0」になっているか確認します。

設定は以上で終わりです。次は仕訳データの集計設定をします。

続き→ 集計表と月次繰越

 

-PC関連
-

© 2020 バイクOBA3