Hatena::ブログ(Diary)

まりふの別紙

MAPS 予定日予定一覧TA1TP2WA2WP2会員専用
FAQトップ携示板掲示板麻里府発まりふのひと工事中2
 | 

2009.05.10 収支表で学ぶエクセルの使い方

[][]エクセルで収支表を作る

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

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

1.タイトル行

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

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

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

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

2.項目行

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

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

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

3.現在高

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行目(全体の4行目)

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

§2行目(全体の5行目)

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

  • セルF5に“=f4+d5-e5” を入力します。
§3行目(全体の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を引いた値に)する」です。

5.列の書式

セルの結合を行っている(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文字インデント(字下げ)します。

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

6.入力規制

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

§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列の入力は計算式です。「数値の入力は不可」という入力規制はできません。下図のような規制をすると、セルを選択した時、メッセージを出すことができます。

7.罫線

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

8.明細入力時のヒント

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

ogohnohitoogohnohito 2009/06/02 06:32 自己レス。「エクセル収支表の入力できる範囲を制限(保護、ロック)する」にリンクを張った。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/marifunohito2/20090510/p1
 | 
カレンダー
<< 2009/05 >>
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31