汎用的な帳簿 「出納帳をつくるぞ!」


匠への技

「匠への技」シリーズ 第10弾!
いろいろ使い道の多い「出納帳」の作り方です。注目すべき所は、残高の求め方。
2通りの残高の求め方で出納帳を作成します。名前の定義、条件付き書式設定の機能を活用していますが、これらの機能は出納帳に限らずいろいろな用途で活用できると思います。


■ 出納帳の基本設計

 

出納帳の管理項目は、年、月、日、摘要、収入、支出、残高とします。


■ 出納帳様式

セルF列が残高で、残高は「前行の残高」+同行の「収入」−同行の「支出」で求めます。
この後2つの方法に基づき説明を行いますが出納帳様式は共通です。


■ 出納帳作成  その1

その1では、数式を普通に表面化させて出納帳を作成します。
残高を求める →単純に残高を求める場合は、セルF4に 「=D4-E4」、次行のF5には 「=F4+D5-E5」 以降必要行フィルドラッグ。 で式を定義します。
この場合、先頭行と2行目以降の数式が相違するので単純にフィルドラッグできないことが歯がゆいところとなります。
そこで、先頭行から同じ数式で対応させることを考えると以下のようになります。

セルF4への数式:  =IF(ISNUMBER(F3),F3+D4-E4,D4-E4)

ISNUMBER関数により前行が数値であるか否かを判断します。セルF4の場合の前行の値はタイトルとなり数値でないためFALSE(偽)の条件 D4−E4 の式が成立します。セルF5以降の前行は数値となるのでTRUE(真)の条件の 前行+同行の収入−同行の支出 の式が成立します。この数式によりセルF4から必要行フィルドラッグで対応できるようになります。

これで残高のF列の数式が統一されました。がしかし、未記入行を見ると取引が無い場合でも前行の残高を引き継いで表示してしまいます。
その部分を =IF(OR(D4<>"",E4<>""),IF(ISNUMBER(F3),F3+D4-E4,D4-E4),0) で補完します。 IF(OR(D4<>"",E4<>"") はセルD4もしくはE4に値が記入されているかの判断です。いずれかが記入されている場合にTRUE(真)の条件の残高計算が実行されます。FALSE(偽)の時は 0 を代入します。0の代入により前行の残高は引用されなくなりますが今度は 0 が表面化します。(この時に0の代わりに "" を代入すれば空白になりますが "" は文字列扱いになるのでここでは引用せず別の方法で0隠しを行います。)

0 は表示形式を定義して非表示の状態にします。

 


@F列を選択します。
Aメニューバー「書式」 → 「セル(E)」  もしくは選択範囲内で右クリックショートカットメニューの「セルの書式設定」を選択します。
Bセルの書式設定ダイヤログの「表示形式」タブを選択します。
C分類から「ユーザー定義」を選択します。
D種類の入力ボックスへ  #,##0;-#,##0; 整数、負数のみの定義を行い3つめの0の定義を未定義とします。

この指定によりセル値が 0 の時に空白となります。


■ 出納帳作成  その2

その2では、「名前の定義」の機能を活用して残高を求めます。残高を求める場合の基本的な数式はその1と同様です。

 

残高を求めるための「名前の定義」の手順です。
@メニューバー「挿入」 → 「名前」 → 「定義」 を選択します。
A上図のようにセルF4をアクティブにします。
B名前に 「残高」 と入力し 参照範囲の入力ボックスに  =IF(ISNUMBER(名前!F3),名前!F3,0) を入力します。
意味は前行セルF3の値が数値の時にセルF3の値を名前「残高」に代入、そうでない時は0を名前「残高」へ代入する。
C追加ボタンをクリックします。
D同様に名前の入力ボックスに 「支出」を入力し 参照範囲の入力ボックスに  =名前!E4 を入力し追加ボタンをクリックします。
D同様に名前の入力ボックスに 「収入」を入力し 参照範囲の入力ボックスに  =名前!D4 を入力し追加ボタンをクリックします。
EOK ボタンをクリックします。

名前の定義では、絶対セルが標準です。ここで定義している内容は可変であることに注目して下さい。この状態で定義したセルをフィルドラッグすればオートフィル同様にセル列、行がドラッグの方向に従い自動的に増減します。

次に残高を求める数式です。ここでは、名前の定義を行った名前で式を組み立てます。

@残高の先頭セルF4をアクティブにします。
A =残高+収入-支出  式を入力します。
B必要行フィルドラッグします。

フィルドラッグした残高行のセルの値は全てセルF4と同様 =残高+収入-支出 が複写されます。
実際の変化は、名前定義の方できちんと行われています。ちなみに残高列セルF4以外のセルをアクティブにし名前の定義で3つの参照範囲を確認して下さい。

ここでその1と同様に移動(収入、支出)がない場合、前行の残高が引用されてしまいます。
その1では、0を埋めて表示形式で0を非表示にすることで対処しましたが、今度は条件付き書式の機能を使い、収入と支出が未記入の時、文字色をセル色と同じ白色にすることで見た目空白のように仕掛けます。

設定は、


@図のようにF列をアクティブにします。
Aメニューバー「書式」 → 「条件付き書式」 を選択します。
B条件付き書式の設定ダイヤログが表示され、条件1のリストボタンをクリックし 「数式が」 を選択します。
C条件入力ボックスへ =AND(収入="",支出="") を入力します。
D書式ボタンをクリックします。
Eセルの書式設定ダイヤログが表示され、その中の「フォント」タブを選択しフォント色 白を指定しOKボタンをクリックします。
F条件付き書式の設定のOKボタンをクリックし設定を終了させます。

以上の操作で完了です。


■ お試しブックのダウンロード

その1とその2の条件で作成したブックをダウンロードすることができます。

ブック名: takumivol-010.lzh  Download File: takumivol-010.lzh