エクセルで収支表を作る

text Excel

次の図は平成20年度パソコンＦ組の収支報告書です。


これがベストとは言えませんが、これの作り方を紹介します。

１．タイトル行

1行目です。通常、この種（飾り付け）の設定は全体が見えてから行います。
ポイントは、「全体の中央に表示する」ところです。

  1. セルA1に“パソコンＦ組 収支表”と入力します。
    • ひとまずこれで終わります。以下は、全体が見えてから行います。

  2. セルA1〜G1を選択し、
  3. ［書式］メニュー ⇒［セル］をクリックします。
  4. ［配置］タブの「セルを結合する」にチェックを入れ、
  5. ［OK］をクリックします。

  6. 結合したセルを選択（クリック）します。
  7. フォント：MS P明朝，　フォントサイズ：16pt.とし、［フォントの色］を「青」にします。
  8. ［太字］と［下線］、［中央揃え］をオンにして確定します。
    • この方法は選択したセルの設定です。
      　例えば、“パソコンＦ組”のみを設定するのであれば、数式バーの“パソコンＦ組”を選択して行います。
  9. 1行目を選択し、右クリック ⇒［行の高さ］をクリックして、「行の高さ」を 25 にして、［OK］をクリックします。
    • 行の高さは半角で入力します。単位は「ポイント」 pt.です。
    • これは、タイトルと次の項目行の間に隙間を空けるためで、任意です。
    • 設定後、再度［行の高さ］を見ると 25.1 になっている場合があります。
      　これは、Excel内で単位をピクセル（px.）に換算、その後、ポイントに換算したために生じた誤差と思われます。（0.1pt≒0.035mm）

２．項目行

2行目です。ポイントは、項目名が白抜きになっているところです。

  1. 項目名を入力します。
    • セルA2から順に“No”，“月日”，“摘要”，“収入”，“支出”，“残高”，“備考”です。
      • ［No］を作るのは任意ですが、「同じ日に払った」ことが見た目で判るよう［月日］に“〃”を入力することを考慮すると「並び」が重要になります。そこで、［No］に連番を入力しようとするものです。
    • ひとまずこれで終わります。以下の書式の設定は、全体が出来てから行った方がいいです。
      　今、行うとやり直しが発生するからです。

  2. セルA2〜G2を選択し、
  3. ［太字］，［中央揃え］をオンにします。
    • 白抜きにする時は、太めの字（フォントを変える方法もある）にした方が見やすいです。
  4. ［塗りつぶしの色］を「緑」にし、［フォントの色］を［白］にします。
    • 先にフォントの色を白にすると、項目名が消えたように見えます。
ヒント
  • 書式の設定は、この後 行う「列の書式設定」で変わる可能性があります。最後の仕上げ時に行う方が手間が省けます。

３．現在高

3行目です。「紙」の書類の場合、合計は最下段にあるのが普通でしょう（ソロバンをいれる関係？）が、Excelでは逆に不便なのです。

    • 上段に入れることにより、「開いたらすぐ状況が判る」のも良いのではないでしょうか？
    • 最下段に入れると、「データを追加する度にずらす」手間が必要になります。
  1. セルA3に“現在高 ＊”と入力します。（任意）
  2. セルA3〜C3を選択し、
  3. ［書式］メニュー ⇒［セル］をクリックします。
  4. ［配置］タブの、
    • 「セルを結合する」にチェックを入れ、
    • 「横位置」を［右詰め（インデント）］にし、
    • ［OK］をクリックします。

  5. セルD3に“=sum(d4:d500)”と入力します。
    • 入力は全て半角文字です。
    • 先頭にイコール“=”を入力すると、「今から入力するのは計算式」の意味になります。
    • 計算式で使う大文字/小文字は区別されません。確定後は自動的に大文字になります。
    • これは、セルD4〜D500の合計（sum ）という意味です。
      　最後をD500としてあるので、500行目までは対処できます。それ以上になる場合は、この値を大きくする必要があります。
      　最大は 65,536 です。必要以上に大きくすると「計算に時間が掛る」ことになります。
  6. セルE3に“=sum(e4:e500)”と入力します。
    • セルD3をアクティブにし、フィルハンドルをE3までドラッグしても同じ結果が得られます。
  7. セルF3に“=d3-e3”と入力します。
    • これは、「セルD3とセルE3の差をセルF3に入れる」という命令（計算式）です。

  8. セルD3〜F3を選択し、
  9. ［塗りつぶしの色］を「薄い黄色」にします。
    • 色は任意ですが、薄い色がいいでしょう。

４．明細行

§１行目（全体の4行目）

明細1行目は、すべて手入力します。
通常であれば「前年度繰越金」となるはずで、計算のしようがないからです。

§２行目（全体の5行目）

セルF5（残高）は、
　　セルF4（直前の残高）＋セルD5（収入）−セルE5（支出）
となります。

  • セルF5に“=f4+d5-e5” を入力します。
§３行目（全体の6行目）以降

セルF6（残高）は“=f5+d6-e6” となります。
これは、直上の式をコピー（セルF5をアクティブにし、フィルハンドルをセルF6までドラッグ）したのと同じです。これは1行のみのコピーですからショートカットキーを使った方が簡単です。

  • セルF6がアクティブになった時、［Ctrl］＋［D］（選択範囲内で下方向のセルに内容と書式をコピーする）を押下します。
ヒント
  • F列の式をあらかじめコピー（フィルハンドルをドラッグ）しておく場合は、工夫が必要です。
    • セルF5の数式：　=if(and(d5="",e5=""),"",f4+d5-e5)
      • この意味は、「もし（if）、セルD5が未入力（d5=""）で、かつ（and）、セルE5が未入力の時は、空値（""）とし、そうでない時（カンマの次）は、計算（セルF4とセルD5の和からセルE5を引いた値に）する」です。

５．列の書式

セルの結合を行っている（1行目と3行目）と、ここの列の書式が上手く設定できないことがあります。セルの結合は最後に行うべきです。

§B列
  • セルの書式設定 ⇒［表示形式］タブで、
    • 分類：［ユーザー定義」を選択
    • 種類：“gee/mm/dd (aaa)”を入力
  • ［中央揃え］にします。
§D列，E列，F列
  • セルの書式設定 ⇒［表示形式］タブで、
    • 分類：［数値］を選択
    • 「桁区切り(,)を使用する」にチェックを入れる
    • 負の数の表示形式：　1,234
      　F列がマイナスになる可能性があるので、「赤字表示」とします。
§E列

支出は「赤字」の方向になるので、赤字で表示するものとします。

  1. E列を列選択し、
  2. ［フォントの色］を「赤」にします。
ヒント
  • この操作を行うとセルE2の“支出”も赤字になってしまいます。
    　このような場合、例えばセルD2の書式をコピーして修正する（貼り付ける）方が簡単です。
    1. セルD2をアクティブにします。
    2. ツールバーの （［書式のコピー/貼り付け］）をクリックし、
    3. セルE2をクリックします。
§G列

F列の金額が右揃えになりますので、見やすくするために１文字インデント（字下げ）します。

  1. G列を選択します。
  2. ［書式］メニュー ⇒［セル］をクリックします。
  3. ［配置］タブで、
    • 横位置：　［左詰め（インデント）］を選択
    • インデント：　1
  4. ［OK］をクリックします。

６．入力規制

「入力規制」とは、セルに入力する値を制限する機能です。
例えば、月日です。「月日の列は半角文字」に規制しておくと、自動的に半角文字になります。

§A列，B列，D列，E列，F列

日本語入力システムをオフ（半角文字入力）にします。
（これらの列は「数値項目」なので、この規制をしなくても問題ありません。練習と思ってください）

  1. A〜B列およびD〜F列を列選択します。
  2. ［データ］メニュー ⇒［入力規制］をクリックします。
  3. ［日本語入力］タブの、
    • IME/日本語入力 を［オフ（英語モード）］にします。
  4. ［OK］をクリックします。
§E列

E列にマイナス値で入力するとおかしなこと（収入）になります。そこでマイナス値は入力できないようにします。

  1. E列を列選択します。
  2. ［データ］メニュー ⇒［入力規制］をクリックします。
  3. ［設定］タブで、図のように設定します。

    ゼロの入力を認める時は、「データ」を［次の値以上］にします。

  4. ［エラーメッセージ］タブで、例えば、図のように入力します。

  5. ［OK］をクリックします。
ヒント
  1. 入力済のセルには、入力規制は適用されません。
  2. 例えばセルD2の“収入”を誤って消してしまったとします。すると全角文字は入力できませんので、再入力する場合は［入力規制」⇒［日本語入力］を戻す（［コントロールなし］にする）必要があります。
  3. F列の入力は計算式です。「数値の入力は不可」という入力規制はできません。下図のような規制をすると、セルを選択した時、メッセージを出すことができます。

７．罫線

  1. セルA2〜右下の最後のセルまで選択します。
    • セルA2をアクティブにし、［Shift］＋［Ctrl］＋［End］を押すと右下の最後のセルまで選択できます。
  2. ツールバーの［罫線］の［格子］を選択します。
  3. セルD3〜F3を選択し、
  4. ツールバーの［罫線］の［太枠外罫線］を選択します。
ヒント
  • ［Shift］＋［Ctrl］＋［End］を使った時、見た目以上に選択されるのは、データを入力した跡があるからです。行または列削除して消しておきましょう。
  • データを追加した時は、追加したセルを選択し、［格子］を引きます。
  • 予め［格子］を引いておくと、印刷したりウェブページに載せた時、見かけが悪くなります。

８．明細入力時のヒント

  1. 月日
    • 年の入力を省略する（例：5/10）と、自動的にパソコンの年になります。
    • 同じ日の入力がある場合、“〃”を入力し、判りやすくします。
  2. 摘要
    • 例えば“*月分”を［摘要］に入れるか［備考］に入れるか‥‥ は任意です。
      　例のように入力すると、オートコンプリートが効率的に使えます。
  3. 収入
  4. 支出
    • プラス値で入力します。
  5. 残高
    • 計算式で入力（コピー）します。
      　アクティブになった時、［Ctrl］＋［D］です。オートフィルを使っても入力できます。
  6. 備考
    • ［備考］に明細を入れる（例えば、会議室使用料1,680円、エアコン使用料100円）のであれば、［摘要］で分けるべきです。計算はエクセルの方が得意ですから...
  7. No.
    • 連番ですから、明細行入力後にオートフィルを使って「付与」します。