このページは2018年10月19日に更新しました
簿記の知識がなくてもできる青色申告で
65万円の特別控除は受けられる！
がモットーのhihi1d.comでは
個人事業の青色申告を全力で応援！
こんにちは、片桐いつきです。
８年間の極貧個人事業主生活から大逆転、
今はいえから気ままに会社を経営中。
このページは
○当ブログでダウンロードしていただける、ふだんの記帳から青色申告まで便利にお使いいただけるエクセルファイルを使いながら
○同時にエクセルの使い方にも慣れてしまう
ことを目的にしています。
当ブログで扱う内容はすべて初心者さん・不慣れな方にも分かるように心がけて書いています。
「Excelは普段からバリバリ使いこなしている」
という方にはまったく必要のない内容も含まれているかと思いますので、うまく読みとばしつつご利用いただけると嬉しいです。
はじめに
ここで使う青色申告用エクセルファイルについて
１． 空の現金出納帳で初歩を知ろう
Excel操作の基礎です。
一度でもExcelを使ったことのある方は、
読み飛ばしていただいても問題ない内容だと思います。
２． 操作を間違ったら・・・「もとに戻す」ボタンの活用
Wordにも搭載されている「もとに戻す」ボタン。
「あ、間違えた!」の操作を取り消すことが可能です。
３． 複数の表をひとつのExcelファイルで管理
ワークシートを活用しよう
Excelで青色申告をするにはなくてはならない
ワークシート機能の紹介です。
４． さらにいろんな編集機能を使ってみよう
複数の「経費帳」を作成し、
使いやすいよう体裁を整えていきます。
５． Excel文書に名前を付けて保存・上書き保存
せっかく作成したワークシート、
こまめな保存を心がけましょう。
６． 計算式や関数を使いこなそう
いよいよ「表計算ソフト」の醍醐味です。
この機能を使うことにより、自動的な計算が可能になります。
７． 印刷・集計に備えて体裁を整える
A4縦の用紙に印刷するため、罫線を引き、
1ページごとの見栄えを整えます。
８． 固定資産台帳を作成しよう
備品などの管理をするための帳簿です。
９． 売上帳・仕入帳を作成しよう
売上高を管理するのが売上帳です。
商品や原材料の取り扱いがなければ仕入帳は必要ありません。
１０． 少し複雑な表作り 商品台帳
今までより複雑な表・計算式を作成します。
商品台帳自体は商品・原材料の扱いがなければ必要ありませんが、
少し高度に技術について説明しています。
１１． シートをまたがった計算式 月別売上・仕入金額の算出
複数のワークシートを参照し、
合計額の集計をおこないます。
１２． 損益計算書
申告書類の真打ち登場。
今までの作業で算出した数値を使う、
まさに総決算な表になります。
１３． 減価償却費の計算
損益計算書の「減価償却費」の欄に記入する金額を
こちらの表に記入して計算します。
１４． 青色申告特別控除額の計算
青色申告をすることで受けられる、
特別控除額の計算をする表です。
はじめに
当ブログでは、青色申告に必要な日常的に記入の必要になる帳簿から、実際の申告の際に提出の必要になる所定の用紙に記入する金額を、Excel形式の書類群で自動的に算出できるファイルをダウンロードいただけます。
この青色申告に必要なExcel形式の書類群をさらに使いこなしていただけるよう、青色申告書類の作成手順に必要なExcelの使い方を、別ページ「初心者さんの青色申告」をもとに說明していきます。
現在ダウンロードできるExcel形式の最新版は以下のとおりです。
それぞれのファイル名をクリックすると自動でダウンロードがはじまります。
初心者さんの青色申告 アフィリエイター向け
(10万円・65万円の特別控除を受けられる複式簿記式) Ver1.1
※アフィリエイター向けは、アフィリエイトをしていないと使わない表があるだけで、
一般用としてもご使用いただけます。
公開日 2013年11月27日
Excel形式ファイルをそのままダウンロード
初心者さんの青色申告 アフィリエイター向け
(10万円・65万円の特別控除を受けられる複式簿記式) Ver1.1
（ファイル名: aoiroafi11.xlsx ファイルの大きさ: 3.12MB）
このファイルの仕様は別ページ、「青色申告 ダウンロード」をご覧ください。
この様式は、あくまでも私自身が個人で使用しているものの改訂版です。
ご利用をお考えの方は記入を始める前に、ご自身のおこなわれている事業内容がこの様式で申告可能かどうか、印刷をして所轄の税務署でご確認ください。
一か月分くらいを記帳してみて、その時点で印刷、税務署で堪忍していただくと、よりそれぞれの状況に沿ったご助言をいただけるかもしれません。
申告に不備が生じましても責任は負いかねますことをご了承願います。
できるだけ汎用性の高い、どなたにもお使いいただけるものをご用意していますが、それでも
「自分が使うにはどうしても足りない部分がある」
「編集しているうちに書式を壊してしまって元に戻せない」
など不便な点は出てくるかと思います。
その時になって慌てると修復できるものもできなくなってしまうので、できれば記入項目の少ないうちに、ここでの青色申告に必要なExcelの使い方をご確認いただければ確実です。
また、ダウンロードできる書類群がどうしてもご自身の用途に合わないとき、中には最初からご自身で作った方があちこち変更するより単純に済む場合もあります。
１．空の現金出納帳で初歩を知ろう
最低限の項目だけで構成されたExcelの「現金出納帳」を使って、Excelの仕組み、どんなことができるかを見ていきます。
白紙の現金出納帳ダウンロードはこちらから
現金出納帳 白紙 (Excel形式)
ダウンロードしたExcelのファイルを開くとこんなふうに表示されます。
Wordやメモ帳と大きく違うのは、初めから縦横の罫線が入っている点です。
さらに矢印キーで移動をしてみてください。
罫線で囲まれた四角の上を、黒い枠線の四角が移動していきますね。
このひとつひとつのマスを「セル」と呼びます。
並んだセルのすぐ左側と上を見てください。
左側に数字、上にはアルファベットが並んでいます。
上の画像と同じように、「Eの列」の「6の行」のセルをクリックしてみてください。
p style=”margin-bottom:4em;”>それぞれEの部分と6の部分が他と違う色になりました。<
Excelではこれを”E6番地”と呼びます。
今だとABCのABあたりの上に「E6」と表示されていると思います。
試しに矢印キーで黒枠を移動すると、ここに表示されているセルの名前が変わります。
番地の呼び方は忘れてしまっても構いません。
ただ、この先
「A1のセルを選択してください」
などのように說明に出てきますから、それが何を示すのかだけ覚えておくようにしてください。
ではE6のセル上でダブルクリックをしてみてください。
黒枠で囲まれた四角の中に縦線の点滅しているのが現れると思います。
これがWordや、インターネットブラウザなどでもおなじみの「カーソル」を表示させる方法になります。
もう一度、今とは違うセル上で今度はクリックをしてください。
黒枠がそこに移動しましたね。
この状態で「単価」と打ってみてください。
やはりカーソルが表示され、さらに今打った文字も表示されます。
「たんか」とひらがなを打ち、それを「単価」という漢字に変換した直後は、まだカーソルが黒枠内で点滅しています。
この状態でエンターキーか矢印キーを押す、または他のセルをクリックすると先ほどのセルに「単価」と入力されます。
エクセルで文字を入力するにはふたつの方法がある
○入力したいセルをダブルクリックして
カーソルを表示させてから文字を打つ
○入力したいセルを選択した状態でいきなり文字を打つ
次にひとつ以上のセルを指定する方法です。先ほどあちこちで単価、単価と打った結果、私のExcelの画面はこんなふうになってしまいました。
4か所に入力した単価をいっぺんに消すために、「単価」と入力したすべてのセルを選択します。
まずはドラッグで範囲選択をする方法。マウスポインタが白の十字の状態で選択したい範囲の端から端までをドラッグして選択します。
C4からF9に向かってドラッグ、F9からC4に向かってドラッグ、どちらでも同じ結果になります。さらに言うと、F4からC9、C9からF4、とドラッグしても同じです。範囲を指定したらキーボードの「delete」キーを押すと、選択されたすべてのセルの内容が消去されます。
ドラッグを使わずに同じように範囲指定をする場合は、まずC4・F9・C9・F4の、範囲の端となるセルのうち、いずれかひとつをクリックして選択します。黒枠がいずれかのセルにある状態でキーボードの「shift」キーを押しながら対角となるセルをクリックします。
複数のセルを選択した状態で「消去」というひとつの処理をおこなうと、それが選択したすべてのセルに適応されるという仕組みです。
同じ処理をしたいセルがふたつ以上あるけれど、そのすべてを含む範囲には内容を消去をしたくないセルがある場合には、「shift」キーのかわりに「ctrl(control)」キーを使います。
選択したいセルのうちいずれかひとつをクリックし、そこに黒枠を移動します。その跡は「ctrl(control)」キーを押しながら内容を消去したいセルをひとつひとつクリック。
するとお互いから離れていても、クリックしたセルだけを選択することができますから、この状態で「delete」キーを押せば選択した4つのセルだけに消去の処理をすることが可能です。
２．操作を間違ったら・・・「もとに戻す」ボタンの活用
Excelでは、他の場所との関連付けをしたり、計算式(関数)を使ったりするので、ひとつの処理を間違うといっぺんにいろんなところに影響が出る可能性があります。
「今なにをしたんだろう」
そうなったときに慌てないために覚えておいていただきたいのが、「もとに戻す」ボタンです。
Excelのウインドウの左上、Excelアイコン・上書き保存(フロッピーの絵)アイコン、その右隣にある青い左を向いた矢印が「もとに戻す」ボタンです。
このボタンを一回クリックすると、今の状態になる直前にした処理を取り消すことができます。このページの１．から順にご覧の方は、試しにクリックしていただくと、消去した「単価」の文字が元に戻ると思います。
続けてクリックを繰り返すと、今までにしてきた処理が順にひとつずつ、巻き戻っていくような効果があります。また、この「もとに戻す」ボタンの上にマウスポインタを置いて一秒くらいすると、何の処理を取り消すかがポップアップで表示されます。
元に戻した処理をやはり有効にしたい、「もとに戻す」ボタンを使う前の状態に戻すには、「もとに戻す」ボタンのさらに右どなりにある右を向いた青い矢印、「やり直し」ボタンを使います。
「やり直し」ボタンは「もとに戻す」ボタンと真逆の効果を持っていて、一回クリックするごとに、元に戻した処理を元に戻してくれます。
試しに「もとに戻す」ボタンと「やり直し」ボタンを交互にクリックしてみてください。効果が分かりやすいと思います。
このふたつのボタンの組み合わせは最初に書いたような緊急時の他、見た目や処理を見比べるのにもとても便利です。
３． 複数の表をひとつのExcelファイルで管理
ワークシートを活用しよう
今度はExcelウインドウの左下にご注目ください。
インターネットエクスプローラーやグーグル・クロームなど、インターネットブラウザではお馴染みの「タブ」がここにあります。
タグを使ったことがない方のためにご説明すると、タブとは同じウインドウ内で複数のページを表示させるときのしおりのようなものです。
パソコン上ではなく、紙の書類を管理するためのファイルにはよくついていますね。ルーズリーフに見出しのシールを張った感じを想像していただいてもいいかもしれません。
１．で白紙の現金出納帳をダウンロードされた方は、ここが左から
「現金出納帳」
「Sheet2」
「Sheet3」
と順に表示されています。
試しに「Sheet2」のタブをクリックしてみてください。まっさらなセルが並んでいる画面に切り替わりましたか。「Sheet3」にも何も記入していませんから見た目の同じ画面が表示されます。
この、ひとつのタブにひとつ割り当てられているExcelの編集画面を「ワークシート」といいます。このワークシートを活用することで、2つ以上の表の管理や、表と表の間の関連付けなどを、ひとつのExcel文書ですることが可能です。
では早速ワークシートに慣れてみましょう。
まずはワークシートの複製(コピー)です。「現金出納帳」のタブをクリックして、最初の画面に戻ってください。「現金出納帳」タブを一秒くらいクリックしっぱなしにしていると、マウスポインタが
のように変化します。この状態で「現金出納帳」タブの左側に注目してください。
小さな下向きの黒三角が見えますか。この三角は「この位置に今選択しているワークシートを移動しますよ」という印です。今回は「現金出納帳」の右側、「Sheet2」との間に複製したワークシートを作成するので、マウスポインタを「現金出納帳」のタブ上でクリックしたまま「Sheet2」の上へ動かします。「現金出納帳」タプを「Sheet2」タブ上までドラッグする、ということです。
すると黒矢印の位置が下のように変わります。
このままだと「現金出納帳」ワークシートを移動する処理になってしまいます。試しに黒三角が「現金出納帳」と「Sheet2」の間にある状態で離して(ドロップ)してみてください。「現金出納帳」ワークシートの位置が移動するわけではないため、何も変化は起こりません。
今度は「現金出納帳」タブをドラッグするときに、キーボードの「ctrl(control)」キーを押してみます。するとマウスポインタが
のように、紙のアイコンの上に「+」が表示されたものに変化します。この状態でマウスポインタを「Sheet2」上まで移動、ctrl(control)」キーを押したままドロップすると、「現金出納帳」の右隣に「現金出納帳(2)」という新しいワークシートが複製されました。
「現金出納帳」と「現金出納帳(2)」のタブを交互にクリックしてみても、一見画面に変化がないように思えますが、それはもとの「現金出納帳」ワークシートの内容を、「現金出納帳(2)」のワークシートが保持しているからです。このワークシートの複製(コピー)機能を使えば、同じ様式のワークシートをいくつも複製することができます。
このままでは紛らわしいので、ワークシートの名前を変更してみましょう。
「現金出納帳(2)」のタブ上で右クリックをしてください。すると下のようなメニューが表示されますので、中から「名前の変更」をクリックしてください。
すると「現金出納帳(2)」の文字列が黒く反転しますから、これから使うことになる「経費帳」に名前を変えることにします。
これでタブの並びは左から、「現金出納帳」「経費帳」「Sheet2」「Sheet3」となりました。
タブを右クリックすることで表示されるメニューの中には他に
挿入
削除
(名前の変更)
移動またはコピー
すべてのシートを選択
などがあります。
先程は「ctrl(control)」キーを押しながらのドラッグ・ドロップでワークシートをコピーしましたが、もちろんこのメニューから「移動またはコピー」を選択することでもワークシートの複製を作成することができます。
４． さらにいろんな編集機能を使ってみよう
このままだと「経費帳」ワークシートの中身は「現金出納帳」とまったく同じですから、必要な点を編集して「経費帳」に仕上げます。
この手順を追いながら、さらにExcelでできることに慣れていきましょう。
現在の「経費帳」の見た目はこう。
そしてこちらが、目標とする「経費帳」の出来上がりです。
もう少し分かりやすく、変更点を見てみましょう。
（１）年月日が1列に表示されていたのが、1・2列に別れています。
（２）摘要欄の幅が広くなっています。
（３）D・E列の入金・出金欄の名前が変わり、上に「金額」と表示されています。
（４）F欄の「現金残高」がなくなっています。
それでは番号順に、手順を説明します。
（１）一列に表示されていた年月日を、1・2列に分ける
・ セルA1をダブルクリック
・ 「月日」部分をドラッグして反転
・ 「delete」キーを押して「月日」を消去
・ 「年」と「月日」の間にセル内での改行を使っているので、
「back space」キーで改行を削除
※改行はマークとして見えていません。
こうだったものが
こうなれば改行の削除は成功です。
思うようにいかなかったら、単にA1セルを選択して、
直接「年」と打ちこんでも同じ結果が得られます。
この方法だと、セルに今入力されている内容を変更するのではなく、
上書きすることになります。
ダブルクリックをしてからの内容変更と、セルを選択して直接の上書きは、
したいことによって使い分けると便利です。
A2セルに「月」、B2セルに「日」を入力したら①の手順は完了です。
（２）摘要欄の幅を広くする
ワークシートの上部、「C」列と「D」列の間にマウスポインタを置きます。
するとマウスポインタが
の形になります。
この状態で左右にドラッグしてみてください。
「幅: 34.00 (313ピクセル)」などと書かれたポップアップが表示され、ドラッグに応じて「摘要」欄のあるC列の幅が変わります。
今回はこの「幅: 」のところの数値が「50.00」になるまで右にドラッグしてドロップします。
もちろんご自身の使いやすい幅にしていただいても構いません。実際に「摘要」欄に項目を記入するようになってから、ちょうどいい幅に変えるといいと思います。
もうひとつ、列の幅を変更する方法をご紹介します。
今度は「C」列と「D」列の間で右クリックをします。
すると下のようなメニューが表示されますので、中から「列の幅」をクリックしてください。
するとこのような小さなウインドウが表示されますので、
「列幅」の右の欄に、「50」と入力、「OK」をクリックするか「enter(return)」キーを押すと、列の幅が50に変更されます。
ドラッグをする方が手軽に使えますが、右クリックからのメニューを使うと確実に変更したい列の幅を指定できます。こちらも場面に合わせて使い分けてみてください。
A1セルに「年」と「月日」を2行にわたって表示していたため、「1」の行だけ「2」以降の行より高さが広くなっています。
今度は行「2」の高さを確認するため、行「2」と行「3」の間にマウスポインタを置き右クリックします。表示されたメニューの中から「行の高さ」を選択してください。
すると表示される小さなウインドウに、現在(既定)の行の高さが表示されているますので、数値「13.2」を確認したら「キャンセル」か右上の「×」ボタンをクリックして小ウインドウを閉じます。
同じ手順で、行「1」と行「2」の間で右クリック、メニューから「列の高さ」を選択、表示される小ウインドウで行の高さを「13.2」と入力して「OK」。
これで行「1」の高さが他の行と揃いました。
（３）D・E列の入金・出金欄の名前を変えて
上に「金額」と表示する
もう一度、変更前と変更後の比較を画像で見てみます。
D・E列の行1・2とも内容が変わります。まずは簡単な行2の内容変更をしましょう。
セルD2をクリックし黒枠で選択、空白のセルですから「現金」と直接入力します。
同じように、セルE2にも「その他」と入力してください。
※「その他」とは銀行口座上での動きなどに使うと考えてください。
ここまででワークシートは以下のようになっています。
ここでひとつ、体裁を整える処理をしてみましょう。セルD2、E2の中で文字の「中央揃え」をしたいと思います。
セルD2、E2をいっぺんに選択したら、ウインドウ上部のいろんなボタンが並んでいるところに注目。下の画像のように「中央揃え」を選択してクリックします。
セル内で、文字列が中央に表示されるようになりました。
「中央揃え」 反映前
中央揃え 反映後
ついでに同じ要領で、セルA2、B2の「月」「日」も中央揃えしてしまいましょう。少しの差で見栄えがだいぶ違うと思いませんか。
もちろんこの処理は必須ではありませんから、お好みでご使用ください。
次は「金額」と表示されている部分の編集です。
まずはセルD1、D2をドラッグでいっぺんに選択し、入力されている内容、「入金」「出金」を消去して、空白のセルにしてください。
D1、D2がまっさらのセルになったらウインドウ上部に注目です。
先ほど使った「中央揃え」ボタンの右2つどなり、
の右側にある、小さな下向き三角をクリック、下のようなメニューを表示させ、「セルを結合して中央揃え」を選択してクリックします。
するとセルD1、D2の間にあった線がなくなり、ひとつのセルになりました。
※この場合、セルの番地(名前のようなもの)は「D1」となります。
このセルに「金額」と入力してみましょう。ふたつのセルを結合した際、一緒に「中央揃え」の書式も有効になりましたので、D1、D2のセルにまたがる形で中央に「金額」と表示されます。
（４）セルF1の「現金残高」の消去
ここまで来てセルを空白にする說明は不要ですね。F1のセルを選択、キーボードの「delete」キーを押せば、消去は完了です。
５． Excel文書に名前を付けて保存・上書き保存
では「現金出納帳」と「経費帳」のワークシートができたこのExcel文書を「名前をつけて保存」してみましょう。
Excelウインドウの左上にある「ファイル」のタブをクリックします。
左側のメニューから、「名前を付けて保存」を選んでクリック。
すると以下のようなウインドウが表示されます。
ここまでダウンロードしたファイルをそのまま使っていると、反転しているファイル名は「bigenner01plane.xlsx」となっています。
この「bigenner01plane.xlsx」の「.xlsx」以外の部分、「bigenner01plane」を好きな名前に変えて、ウインドウ下部の「保存」ボタンをクリックしてください。ダウンロードしたファイルはダウンロードしてきたときの状態のまま保たれ、新しく今つけた名前のExcelファイルが出来上がります。
編集画面に戻って作業を続ける場合は、「メニュー」タブの右隣にある「ホーム」タブをクリックしてください。
これ以降、新しくつけた名前のExcel文書に変更を加え、同じ文書名で変更後の内容を活かしたいときには、「上書き保存」機能を使います。
Excelウインドウの左上にあるフロッピーの絵のボタンをクリック。
たったこれだけです。「ファイル」メニュー画面に行く必要もなく、文書の保存ができてしまいます。
「ファイル」メニュー画面の「上書き保存」と同じ機能です。
「名前を付けて保存」は修正前と修正後の内容を両方保存したいとき
「上書き保存」は変更した内容だけをそのまま活かしたいとき
と使い分けてみてください。
６． 計算式や関数を使いこなそう
お待たせしました。
ここからがExcelの楽しいところです。
とは言っても、「関数」と聞いただけで拒絶反応を起こす方もいらっしゃるかもしれませんね。
ご安心ください。こちらでは最低限の計算式、一種類の関数しか使いません。
しかも、それだけ使えれば、半永久的に自動で申告書類に記入する数字を算出してくれる書式ができてしまいます。
「苦手だなぁ」と感じる方も、ここまで来たついでと思ってぜひ試してみてください。
（１）セルとセルの合計を出す計算式
（２）指定範囲の合計額を算出してくれる「SUM」関数
（１）セルとセルの「＋」「－」「×」「÷」をする計算式
まず準備として、計算するための数値を用意します。
現金出納帳に、以下の取引を記入してください。
摘要欄の記入が面倒だという方は、とりあえず金額の欄だけでも構いません。
・ 2月1日、事業準備金として10,000円を計上
・ 2月3日、印刷用紙を500円で購入
・ 2月5日、単価800円の商品を3個購入
・ 2月8日、青木様に商品を1,000円で売り上げ
では早速計算式を入れてみましょう。
一行目の「現金残高」が、「入金－出金」になる計算式を導入します。
・ セル「F2」を選択します。
・ 半角で「=」と打ちます。
・ カーソルがF2セル内で点滅している状態で、「D2」セルをクリック。
・ F2セルに「=D2」と表示されたら、そのあとに「－」を半角で記入
・ カーソルが「－」のとなりで点滅している状態で、「E2」セルをクリック
・ F2せるの内容が「=D2-E2」となったらキーボードの「enter(return)」キーで計算式を確定。
F2セルに表示された数値が「10,000-0」の結果である「10,000」、ワークシートの上部にある、F2セルの内容を表示する欄が「=D2-E2」となっていれば成功です。
今度はセルF3に入る計算式の作成です。
Fの列には「現金残高」が示されますから、最初の一行目以外は、「その前の行の現金残高＋その行の入金－出金」という計算になりますね。
・ セルF3を選択、「=」を入力します
・ 「前の行の現金残高」にあたる、F2セルをクリックします
・ そのあとに「＋」を入力、D3セルをクリック
・ さらに続けて「－」を入力、E3セルをクリックして確定(enterキー)
これでF3セルの数値は「10,000+0-500」の「9,500」、内包されている計算式は「=F2+D3-E3」となりました。
このように、
「半角の＝」から入力をはじめたセルの内容は、計算式として認識する
というのが、Excelを表計算ソフトたらしめている所以です。「半角の＝」から入力をはじめて、半角の演算子(＋－×÷)を使えば計算結果を表示させることができます。
この場合、演算子の間に入るのは数値でも構いません。数値とセルが混ざって入っていても、計算結果が計算式を入力したセルに数値として表示されます。
次に、計算式の入ったセルの内容をコピーする方法です。
この例ですと、セル「F4」「F5」も「F3」と同様の計算で数値が導き出せますので、それらふたつのセルにF3の数式をコピーしてしまいましょう。
セルの右下の角にマウスポインタを合わせると、マウスポインタの形が白十字
から黒十字
に変化します。
セルF3の右下でマウスポインタをこの黒十字にしたら、そこからセルF5までマウスをドラッグして離して(ドロップして)ください。
セルF4、F5の数値がそれぞれ、「7,100」「8,100」となれば成功です。
ここで注目していただきたいのは、数値そのものよりコピーされた数式です。例として、セルF4を選択してみてください。
コピー元のセルF3では「=F2+D3-E3」となっていた数式が、F4では「=F3+D4-E4」となっていますね。
この、ドラッグ・ドロップ方式で数式をコピーすると、簡単に該当するセルに合わせた内容に自動的に変更された数式を複製することができます。
（２）指定範囲の合計額を算出してくれる「SUM」関数
月の終わりには「入金」「出金」、それぞれの合計を出したいと思います。
例として入力している数値はたったの4つですから、（１）でしたようにひとつずつ足していってもそれほどの手間ではありませんが、これからもっと数値が増えていったときのためにも、ぜひこの「SUM関数」を使えるようにしておきましょう。
今回は入金の合計額をセルD7に、出金の合計額をE7に表示させるよう設定します。まずは入金の合計額からです。
D7セルを選択し、半角で「＝」と打ったら
・ Excelウインドウの上に並んでいるタブから「数式」タブをクリック。
・ 表示された「数式」メニューの「オートSUM」右どなりの下向き三角をクリック。
・ 表示されたドロップダウンリストから、「合計」を選択してクリック。
すると合計される範囲として、おそらくD5セルからD6セルが点線で囲まれた状態になると思います。
SUM関数で合計する範囲を拡大するため、点線で囲まれた範囲の右上にマウスポインタを置いて、ポインタが両矢印に変わったらD2上までドラッグします。
するとD7セルの計算式は「=SUM(D2:D6)」となり、D2からD6までの合計値を出す計算式の出来上がりです。「enter(return)」キーを押して確定させると、2月1日から8日までの入金の合計額である、「11,000」がD7に表示されます。
E7についても同じ手順をたどってもいいのですが、ここは（１）で習得したドラッグ・ドロップのコピーを利用してみましょう。
D7セルの右下で、マウスポインタを黒十字にしたら、そのままE7セルまでドラッグしてドロップ。
E7セルの数式がE2からE6までを合計する「=SUM(E2:E6)」、数値が「2,900」と表示されれば成功です。
SUM関数は、「=SUM(範囲のはじまり:範囲のおわり)」の形で、指定した範囲の合計を自動的に算出してくれる関数です。「＝」から範囲のおわりのセル名までを手で打ち込んでも同じ結果が得られます。
関数を一から手打ちするという使い方はほとんどないと思いますが、途中で何か変更点が出たとき手打ちで修正しても、関数の形さえ崩さなければ計算結果が導き出せますから、参考までに覚えておいてください。
今度は記入欄が足りなくなってしまった、合計額を表示させる行の前にいくつか行を挿入したい、と言う場合の対処法です。
今回は、今より5行、記入できる欄を合計の欄の上に挿入したいと思います。
まずはワークシートの左横に縦に並んだ数字、「行番号」にご注目ください。合計額を算出する計算式の入っているセルは7行目にありますから、そのひとつ上、6行目の行番号のところにマウスポインタを合わせます。
するとマウスポインタが右を向いた黒矢印に変わりますので、そのまま5行分、「10」の行番号までドラッグします。
上のようになっているのは行6から行10が選択されている状態です。この状態で、行番号かセルのいずれか選択されている範囲で右クリック、表示されるメニューから「挿入」を選択しクリックしてください。
合計額の入った行が、下に5行分ずれ、行12に表示されるようになりました。
D12、E12、いずれかのセルをクリックして選択してみてください。SUM関数で合計を指定した範囲も、挿入された行数に合わせて増えています。D12なら数式は「=SUM(D2:D11)」、E12なら「=SUM(E2:E11)」となっています。
帳簿の書式は一度決まってしまえば、事業の内容や規模に大幅な変化がない限り、変えずにずっと使っていけるものになります。帳簿のつけ始めは、何に、どのくらいの行数や項目が必要になるか分からないので、この挿入の機能を覚えておくと大変便利です。
ここでは例をあげて説明しませんが、列(A・B・Cの並んでいる横方向)についても同様の手順で挿入がおこなえます。
ウインドウ上部の「ホーム」タブをクリックすると、いつもの編集画面に戻ります。
７． 印刷・集計に備えて体裁を整える
試しにExcelの画面で下へスクロールしてみてください。どこまでもマス目が続いている一枚のワークシートに見えますよね。
このままだと、「どこで区切りをつけるのか」がとても決めにくいと思います。ここでは一番汎用性の高い、A4用紙に縦に印刷すると仮定して書式を決め、区切りをつけていきます。
同時に罫線を引く作業も説明します。作成している表全体に罫線を引くのは、実際に紙に印刷するとなれば必須の作業ですが、印刷の予定がなければ見た目の好みの問題です。
これから表やワークシートをコピー(複製)して使うことが増えるので、もし罫線なしで作業を進めていて、やっぱり印刷が必要だった、となると、個々に罫線を書き加えなければならないことになります。
罫線の有無については、それを頭に置いた上で選ぶと後の手間が省けると思います。ちなみに私自身は、印刷が必要になったときのことを考えて、すべての必要な罫線を引いた白紙のワークシートを作成して使っています。繰り返しになりますが、ここは完全に好みですね。
（１）A4用紙に印刷するとどうなるか見てみよう
（２）罫線を引く
（３）合計欄をページの最後に移動する
（４）表に名前やページ数をつける
（５）表をコピーして一年の集計に備える
（６）経費帳について
（７）預金出納帳について
（１）A4用紙に印刷するとどうなるか見てみよう
上にも書いたとおり、今見ているワークシートはどこまでも続くマス目に見えます。
それでは今あるワークシートが、A4用紙に印刷するとどんなふうになるのかを見てみることにしましょう。
・ Excelウインドウの下部で「現金出納帳」タブをクリックして、「現金出納帳」ワークシートを表示させる
・ Excelの上部から「フアイル」タブを選択してクリック
・ 「ファイル」メニュー画面で「印刷」をクリック
するとあらわれた「印刷」のメニュー画面の右側に、A4縦向きの用紙に印刷するとどんな見栄えになるかを確認できる、印刷プレビューが表示されます。
どうでしょう。ワークシートで作業していたときと違って、縦にも横にも線が一本も引かれていない、およそ「表」とは呼びがたい文字列が漠然と表示されているだけではないでしょうか。
もし紙に印刷して出力するとなれば、当然このままでは何がなんだか分からない書類になってしまいますよね。
それではこの現金出納帳に、罫線を引くことにしましょう。
（２）罫線を引く
Excelウインドウの上部から「ホーム」タブをクリックし、「現金出納帳」のワークシートに戻ります。
すると今まではなかった点線が、G列とH列の間(既定の書式をすでに変更している場合には多少左右にずれた位置)に表示されていますね。
実はこれ、「A4縦の用紙でこの列まで1ページに印刷されますよ」という印です。
試しに下にスクロールしていってみてください。行58と行59の間(かその付近)に横の点線が入っているのを見つけられると思います。
この場合、横は列Aから列Gまで、縦は行1から行58までが、1ページに印刷される書式となっていることが分かります。
この点線を基準にしつつ、罫線を引きましょう。ここでは「ページの最後の一行前に合計欄を入れる書式」と言う前提で、罫線を引いていきます。
まず、Excelのコツが少し掴めてきた方向けに、簡単に說明をします。「少しExcelが分かってきたぞ」という方は、この說明だけで、ご自身で試してみてください。「まだちょっと不安だ」という方は、その後に順を追って細かく說明をしていきますので、そちらを見ながら作業を進めてみてください。
詳しい手順の說明へ
Excelのワークシートに罫線を引く一番単純な方法は、「罫線を引く範囲」と「罫線の種類」を選択、実行することです。
下の画像をご覧ください。Excelウインドウの上部のボタンが並んでいる中に、田んぼの田のようなボタンがありますね。その右どなりの下向き三角をクリックすると、以下のようにダーッとメニューが表示されます。
このメニューでどんな罫線の種類があるのか確認したら、一旦ワークシート内で別の部分をクリックしメニューを消します。
ある種類の罫線を引きたい範囲を選択してからメニューを改めて表示させ、適応したい罫線の種類をクリックしてください。すると選択した範囲に、選択した種類の罫線が引かれます。
ここからはひとつひとつ、順に說明をしていきます。
まずはExcelウインドウ上部のいろんなボタンが並んでいる中から、漢字の「田」のようなボタンを見つけて、その右どなりの下向き三角をクリックしてみてください。下の画像のように、メニューが表示されると思います。
これはこのメニューで引くことのできる、罫線の種類の一覧です。今回はこの中から、
・ 格子
・ 外枠太罫線
・ 下二重罫線
・ 下太罫線
を使っていきます。
・ 格子
罫線のメニューを一旦閉じるため、ワークシート内のどこかをクリック、もしくはキーボードの「esc(エスケープ)」キーを押してください。
ワークシートの一番上に戻ったらセルA1をクリック。ついでに一番右側の項目である「現金残高」が列Fにあることも確認しておきましょう。
次に横方向の点線のある場所の一行上(この例では行57)が見えるところまでワークシートを下にスクロール。
「shift」キーを押しながら、セルF57をクリックして、セルA1からセルF57までを選択します。
この状態でもう一度、罫線のメニューを表示させる下向き三角をクリックして、一覧の中から「格子」をクリックします。
すると今まで見えていた縦横のマス線より濃い、黒の格子線が個々のセルを区切るように引かれます。
・ 外枠太罫線
同じ範囲を選択したまま、再度罫線のメニューを表示させ、今度は「外枠太罫線」を選択します。すると選択した範囲(表全体)の外側に、格子線より少し太い、枠線が引かれます。
ここで一度、印刷プレビューを見てみましょう。
だいぶ表、帳簿らしくなりましたね。ただ、2種類の太さの罫線を引いたはずなのに、このままだと同じようにしか見えません。
そこで印刷メニュー画面の右下のボタンの出番です。これを一度クリックすると、下の画像のように内容が大きく表示されます。
どうでしょう。表全体の外側を囲む線は、セルを区切ってたくさん引かれている線より太いことが分かりますね。右下のボタンは、クリックするたびに拡大表示とページ全体表示を切り替えることができます。
罫線の種類などに慣れない間はこんなふうに、プレビューが確認できる印刷メニューの画面と、実際の作業をしているワークシートを行ったり来たりして、自分の意図する見た目になっているかどうか確認しながら進めると、全体像がつかみやすいと思います。
・ 下二重罫線
では「ホーム」タブをクリックしワークシートに戻ったら、ページの最終行から一行前に二重線を引きます。(ページの最終行を合計欄にしてしまうと、罫線の種類の関係でうまくいかないので、最終行は罫線調整のために残しておきます。)
手順のとおりにここまで進めていると、合計欄は行12のところにありますが、ここは一旦行12の合計欄のことは忘れて、ページの最後から一行前を合計欄にするために罫線を引いてみてください。
・ セルA57からF57までを選択
・ 下向き三角をクリックして罫線メニューを表示
・ 「下二重罫線」をクリック
ページの最後から一行前に合計を出す数式を入れるには、行12にある計算式をすべて消去し、改めてページ下部に数式を導入するのが一番単純で確実な方法です。こちらは改めて、別の項で詳しく説明します。
豆知識として少しだけ帳簿のお話を。
手書きで帳簿をつける場合、月末前にページの一番下まで記入で埋まってしまったら、最終行でそこまでの金額をまとめて、次ページの最初の行にその金額を書き込み、そこから日常の記帳を続けます。
慣例と言えばそう言えないこともないかもしれませんが、月末に数ページに渡る帳簿の集計をすることを考えれば、区切りのいいところで一旦まとめておくというのは理にかなっていると思います。
(想像してみてください。例えば月末に3ページに渡る現金出納帳を集計しようとしたら、合計した額が間違っていないことを確認するためにも、きっと各ページの合計額をメモしますよね。)
記帳の慣習では、合計額を出したりそこまででまとめをしたりするとき、まとめの行の上に赤の一本線を、まとめの行の下に赤の二重線を引くことになっています。
ここではそれにならって、ページの最後から一行前に二重線を引くことにしました。
・ 下太罫線
この「下太罫線」は、外枠に使ったのと同じ太さの罫線を、選択した範囲のセルの下側だけに引く方法です。
まずは日々入力する部分と、合計の欄の間に太い線を引くことにします。
・ セルA56からF56までを選択
・ 下向き三角をクリックして罫線メニューを表示
・ 「下太枠線」をクリック
これで行56と行57の間に太線を引くことができました。
ワークシート上では変化が少し分かりにくいですが、印刷プレビューの拡大で見ると、行56と行57の間の線が他より太くなっているのが分かります。
同様の手順で「摘要」「現金残高」などの項目名の行と、実際の内容を入力していく部分の区別をはっきりさせるため、この間に太めの線を引くことにします。
・ セルA1からF1までを選択
・ 下向き三角をクリックして罫線メニューを表示
・ 「下太枠線」をクリック
ここまでの見た目を、印刷プレビューで確認してみましょう。
・現金出納帳のページ最初の部分
・現金出納帳ページ最後の部分
最初に印刷プレビューで見たときより、ずいぶん帳簿らしくなりましたね。
何度か書いているとおり、罫線の有無や太さ、種類などは好みで選んでもいいので、他にも違う種類の罫線を引いてみるなど、凝ってみてもいいと思います。
・ 罫線を消したいときは
引いた罫線を消したいときは、罫線のメニューの下の方にある、消しゴムマークのついた「罫線の削除」をクリックします。
マウスポインタが消しゴムの絵に変わったら、消したい部分の罫線をドラッグで選択すると罫線を消すことができます。「罫線の削除」を就留するには、キーボードの「esc(エスケープ)」キーを押してください。
（３）合計欄をページの最後に移動する
ここまでページ内の中途半端な位置にあった合計欄を、最終行の一行前に移します。
作業は「既存の合計欄に入っている計算式を最終行にコピー・貼り付けして」「SUM関数で合計する数値の範囲を変える」という手順でおこなってみましょう。
することは
６．の（２）指定範囲の合計額を算出してくれる「SUM」関数
の応用、という感じです。
まずは入金額を合計する数式を移動するため、セルD12を右クリック、表示されるメニューから「コピー」を選択しクリックします。
同様にセルD57を右クリックし、メニューの「貼り付けのオプション:」から「数式」をクリック。
するとセルD57には「0」と表示されます。これはコピーしてきた数式が、『その数式のあるセルの上10行の合計を算出する』という条件を保っているからです。試しにセルD57をダブルクリックしてみてください。『セルD46からD56の合計を算出します』という条件の数式、「=SUM(D47:D56)」が表示されるはずです。
この状態で、
・ セルD48からD56までを囲っている青い線をドラッグして合計する範囲をD2からD56までに拡大
・ セルD57の数式の「D47」の部分を「D2」に書き換え
のいずれかの方法で、最終行であるD57の計算式を、『セルD2からD56を合計する』という条件に変えます。
最終的な数式が
=SUM(D2:D56)
となれば正解です。
となりの出金の合計額を入れたいセルE57には、６．の（１）で習得した「計算式の入ったセルの内容をコピー」する手法を使います。
セルD57の右下にマウスポインタを当て、黒十字に変わったらE57の右下に向かってドラッグ、そこでドロップします。
現在の合計額がそれぞれ、D57=22,000、E57=5,800となっていれば正解です。
セルD12、D13を選択して「delete」キーで、ページの途中に入ってしまっている合計式を削除すると、ページ最終行の合計額はそれぞれ、D57=11,000、E57=2,900となり、これで行2から行56の純粋な合計額が最終行に表示されている状態になりました。
さらにその右どなり、セルF57に、常に現在の現金残高を表示させるよう、数式を導入します。
・ セルF57を右クリックし、「=」と入力
・ そのままD57をクリック、「-」と入力
・ 最後にE57をクリック、「enter(return)」で決定
これでセルF57には、「ここまでの入金額からここまでの出金額を引いた額」が表示されるようになりました。この数字は、いつも項目が入力されている最終行(現在の例ではセルF5)の「現金残高」の数字と同じ額になります。
今後さらに項目を記入していくのに備えて、行2から行56のすべてのF列に、現金残高額が表示されるようにするには、
・ セルF5の右下から黒十字をF56までドラッグしてドロップ
・ セルF5の内容をコピーしてF6からF56までを選択、「貼り付けのオプション:数式」をクリック
の二通りの方法があります。ただ、この処理をすると、まだ項目・金額を入力していないF列のすべての行に、常に現在の現金残高が表示されている状態になります。
見栄えとしてはあまり良くないので、項目・金額を入力した分だけ、数式をコピーする方が無難かもしれません。
（４）表に名前やページ番号をつける
こちらの項目も、印刷を考えていない場合には、つけるかつけないかは好みで構わないと思います。
・ 表に名前をつける
Excelウインドウ上部から、「表示」タブをクリック、メニューから「ページレイアウト」をクリック
するとワークシートの表示が以下のように変化するので、上部中央の「クリックしてヘッダーを追加」をクリック。さらにその状態で上部に表示されるようになる「デザイン」タブをクリック
「ヘッダー/フッター要素」の中から、「シート名」を選択してクリック。ワークシート内のヘッダー以外の白い部分をクリックして決定
するとはじめは「クリックしてヘッダーを追加」となっていたワークシート上部に、今編集中のワークシートの名前である「現金出納帳」が表示されました。
続けて用紙下部にページ番号を入れます。
ページの一番下までスクロールすると、「クリックしてフッターを追加」と表示されている部分があります。ここをクリック。
ヘッダーのときと同様にデザインをクリックしたら、「ヘッダー/フッター要素」から今度は「ページ番号」を選択。
白い部分でクリックして内容を決定。
するとフッター部分中央に、「1」と表示されるようになりました。
この「ヘッダー/フッター要素」というのは、『所定の項目に指定された内容を割り当てる決まり』です。
要素を使うと、例えばページが増えていったとき、いちいち手打ちでページ番号を入力剃る必要がありません。
また、この要素を使いながら、さらにヘッダー・フッターを装飾することも可能です。
例として、ページ番号の表示を現在の「1」というそっけない数字一文字から、「（1）」の表示に変えてみます。
現在ページ番号が表示されているフッター部分をクリックしてください。内容は「&[ページ番号]」となっていますね。これがページ番号『要素』になります。ページ番号要素ごと、「（）」の真ん中に挟んであげるように、カッコを前後に入力して「（&[ページ番号]）」のようにして決定してください。
「（1）」と表示されるようになりましたね。
ここで印刷プレビューを見てみましょう。
ページ全体を表示させると小さくて分かりづらいですが、確かにヘッダー部分に「現金出納帳」、フッター部分に「（1）」と表示されています。
さて、このくらいまでExcelを使ってくると、さらに見栄えにこだわりが出てきているかもしれませんね。
例えばここまでの例のとおりに作業を進めていると、印刷プレビューでページ全体を見たときに、右側がずいぶん空いているのが気になるかもしれません。
ここでは印刷プレビュー画面での「余白の表示」を使って、体裁を直接整えてみたいと思います。
印刷プレビュー画面右下のボタン、「余白の表示」をクリックすると、以下のように線がたくさん表示されます。
・ 上下左右、用紙の一番外側にあるのが「余白」を示す線です。
「ここから外側には何も印刷しませんよ」という範囲をあらわしています。
・ 上下の余白線の少し内側にあるのが「ヘッダー・フッター」の表示場所を示す線です。
余白とこの線の間にヘッダー・フッターが表示されます。
・ 印刷プレビューの上部、左右の余白を示している以外の黒い小さな四角は
「列の幅」を示すものです。
今回は、「摘要」のある列の幅を広くすることで、表が用紙のちょうど真ん中に表示されるよう調整します。
上の画像の矢印が指している四角(「摘要」欄の右側)を少しずつ右に向かってドラッグ・ドロップしてみてください。だんだんと「摘要」の欄が広くなり、表の右側の空白部分が狭くなっていきます。
微調整をしつつ、一番右の項目である「現金残高」の列が印刷範囲からはみ出て、消えてしまわないギリギリくらいになるように、「摘要』の列幅を合わせます。
ここでExcelウインドウ上部の「ホーム」タブをクリックして、編集画面に戻ってみます。
今調整をした列Cの列幅は「10.05cm」となっていました。このままでも構いませんが、きりのいい「10.00cm」に変更しておきます。
（５）表をコピーして一年の集計に備える
ここまでで自分好みに作った「現金出納帳」をコピーして、今後の記帳に備えます。ひと月ごとに1ページ使う、と仮定して、現金出納帳を合計12ページのワークシートにしていきます。
まずは1ページ目で作った表全体(セルA1からF58)を選択し、右クリックで表示されるメニューから「コピー」をクリックします。この前に一度、行1の高さを確認しておきましょう。今までの手順通りに進んできていると行1の高さは「0.93センチメートル(44ピクセル)」となっています。
2ページ目の一番左上のセル(ここまでの例どおりだとA59)を右クリックし、メニューから一枚の書類のようなアイコンの「貼り付け」をクリック。
2ページ目の1行目の高さを、1ページ目と同じに調整します。これで2ページ目も上から下まで、最終行は除いて表がびっしり入った書式になりました。
次に、2行めから5行目に記入されている内容を消して、白紙の表にします。
ワークシート左側の行番号で2行めから5行目を選択してキーボードの「delete」キーを押してみてください。表に引いた罫線はそのままで、記入してあった内容だけ消されて、一行目の項目以外は白紙の表になりました。
次に2行目の摘要欄(例だとセルC60)に「前月から繰越」と記入します。そして同じ行の入金欄(例だとセルD60)に半角で「=」を打ち、続けて前ページの最終行の預金残高欄(例だとセルF57)をクリックします。2ページ目の2行目の現金残高欄のセルに
=F57(1ページ目の現金残高合計欄)
と入ったらキーボードの「enter(return)」を押して内容を確定させてください。これでこのセルには、前ページの最終的な現金残高額が自動的に入るようになりました。
続けて現金残高欄にも数式を入れます。2ページ目以降の2行目・3行目の数式の様式(そのセルから見てどの位置関係にあるセルを加減算した値を表示させるのか)は、1ページ目の2行目・3行目と同じですからこれをコピーします。
まずは1ページ目の2行目・3行目の現金残高欄のセル(F2とF3)を選択、右クリックメニューから「コピー」をクリックします。
次に2ページ目の2行目の現金残高欄のセル(F60)を右クリック、今度は「貼り付けのオプション:」下にある「fx」のような文字が見える「数式」アイコンをクリック。
これで表の罫線などには干渉せず、計算式だけをコピーすることができました。
試しに2ページ目の2行目・3行目の現金残高欄セルをクリックし、内容をみてみてください。それぞれ、
=2行目のDセル-2行目のEセル (=D60-E60)
=2行目のFセル+3行目のDセル-3行目のEセル (=F60+D61-E61)
となっていれば成功です。
あとは2ページ目全体を3ページ目、4ページ目、と12ページ目までコピ゜ーし、それぞれに一行目の行の高さを調整すれば、12か月分の現金出納帳の準備が完了です。
「自分はそんなに現金の出入りがない、現金出納帳のほとんどが白紙になってしまうので、ページの途中で合計を出したい」と想われる方もいらっしやるかもしれません。
例えば手書きで記帳をしていれば、その月の最後に記帳した次の行で合計を出しますから、もちろんそれでも大丈夫です。
ここでは複製(コピー)すること、年末に備えて自動で集計を出すこと、を念頭に置いて、それぞれのページが同じ書式になる方法を使っています。
（６）経費帳について
もうひとつのワークシートである「経費帳」も「現金出納帳」と同様に編集を施しておきましょう。
必ず入れた方がよいと思われるのはページの最後での合計欄とヘッダー部分の「シート名」くらいかと思います。罫線はお好み、用途に合わせてどうぞ。
経費帳には現金出納帳ほどの書き込みはありませんから、単純に金額の「現金」欄と「その他」欄をページ下部で合計する書式で問題ないと思います。
1ページ目が自分の思い通りに編集できたら丸々2ページ目にコピー・貼り付け、2ページ目の2行目に1ページ目の合計が自動で表示されるよう、計算式と、摘要に「前ページから繰越」と入れておきます。
2ページ目を使うときは、使い始める前に2ページ目を3ページ目にコピーしておくと、後からコピーして内容を削除する、という手間が省けます。
・ ページ下部にそのページの合計が自動で算出されるようにする
・ 2ページ目以降は最初に前ページから繰越額を表示させる
このふたつの要素だけ備えていれば、年末には最終ページの下部にある合計金額が、その年のその経費の合計額となる、とても便利な書式になります。
ここまで整えたら、経費帳として必要な分だけワークシートをコピーし、シート名を変更して使います。
コピー元となる経費帳に、間違って入力してしまった、なんてことにならないように、ワークシート名を「経費帳(白紙)」などとしておくとより分かりやすいかもしれませんね。
３． 複数の表をひとつのExcelファイルで管理
ワークシートを活用しよう の「ワークシートの複製(コピー)部分」参照
ここでは誰もが使うであろう、「水道光熱費」と「消耗品費」のワークシートを「経費帳<白紙)」ワークシートから複製(コピー)し作成しておきます。
（７）預金出納帳について
代金・料金の受け取り、商品や備品購入の支払いに預金口座を利用している場合には、その口座についても現金出納帳と同じように預金の額を管理する帳簿が必要です。
これは現金出納帳を複製したものがそのまま使えますので、「現金出納帳」ワークシートを必要な分だけコピーし、口座によってワークシート名を変更します。
最初に「現金出納帳」ワークシートからひとつコピーをして、ヘッダーの「&[シート名]」前に、「預金出納帳」と入れてから必要口座分をコピーすると、さらに分かりやすい書式になります。
その際、例として入れている部分は削除しておきましょう。
現金出納帳ワークシートから直接複製を作るのが不安であれば、「経費帳(白紙)」のように、今のうちに現金出納帳から複製元を作成して、「預金出納帳(白紙)などとしておきます。ここでも例として記入している項目は削除し、白紙の書式を複製元としてとっておきましょう。
ワークシートの数が増えてきて、タブが一度にすべては表示されないようになってきます。タブの左側のボタンで表示されるワークシートタブを移動できます。
左から
・ 一番左のワークシートへ
・ ひとつ左のワークシートへ
・ ひとつ右のワークシートへ
・ 一番右のワークシートへ
こうしてExcelで帳簿をつけると、いくらでも複製がきく上に、書式が揃うので管理がとてもしやすく、また他の人が見ても整然としていて分かりやすい帳簿が作れます。
今度は横長の書式で固定資産台帳を作ってみます。
多少面倒な作業も含まれていますが、今までしてきたことをほとんど網羅しているので、復習のつもりで挑戦してみてください。
税務署でもらえる資料、「帳簿の記帳のしかた」の16ページにある書式を見本に作ります。
この見本には「償却期間」という欄が表示されていません。これから説明する手順では後から挿入しています。必ず必要な欄になりますので、ご自身で見本を元に作成される場合には、十分ご注意ください。
次の手順で固定資産台帳を作成します。
（１）用紙の向きを縦から横に変更
（２）セルを割り当てていく
（３）セルの結合・中央揃え
（４）罫線を引く
（５）「償却期間」列の挿入
（６）管理番号を入れる
（７）列の幅・行の高さを調整する
（１）用紙の向きを縦から横に変更
「Sheet2」を選択したら、Excelウインドウ上部の「表示」タブをクリック。
表示方法を「標準」から「ページレイアウト」に変更します。
さらに「ページレイアウト」タブをクリックし、「印刷の向き」を縦から横に変更します。
（２）セルを割り当てていく
使うセルの数の見当をつけていきます。
セルは分割するより結合する方が楽なので、同じ行・列に複数の行・列が存在する場合には、多い方のセル数を基準に左上から取っていきます。
（例）「年月日」の列は、年月日と入っているセルはひとつですが、
その下に続く実際の年と月と日を入れる部分は3つですから
「3つセルを使う」と考えます。
「木造店舗」の部分はヘッダーでシート名を入れるとして、
「取得年月日」、「所在」、「耐用年数」とその下に空欄で計4行を空けておきます。
セルA5に「年月日」と入力します。
B、Cはそれぞれ「月」「日」に使うセルとしてとっておき、
セルD5に「摘要」と入力します。
セルE5は「取得」です。
F、Gは同様に「単価」「金額」のために空けておきます。
セルH5が「償却額」です。
(波線部分は無視します。)
となりのセルI5に「現在」と入力、
Jは「金額」分として飛ばします。
そのとなり、セルK5が「備考」です。
6行目に移ります。
「取得」の下、セルE6に「数量」、となりのF6に「単価」、G6に「金額」と入力。
この辺りまで来ると、どんなふうに書式になっていくのか、何となく見えてくるかもしれません。
「現在」の下、I6に「数量」、となりのJ6に「金額」。
K6に「事業専用割合」と入れたら「事業専用」と「割合」の間にカーソルを移動し(矢印キーを押すとセルが移動してしまいますから、「事業専用」と「割合」の間をクリックするようにします)、キーボードの「Alt(option)」キーを押しながら「enter(return)」キーを押して、セル内に改行を入れます。
同様にL6でも「必要経費」と「算入額」の間に改行を入れる処理を施します。
「取得額」の上方、セルH1に「取得年月日」、続けてH2に「所在」、H3に「耐用年数」と入力。
「備考」の上方、セルK1に「償却方法」、K2に「償却率」と入力。
ここまでの見た目は下のようになっています。
（３）セルの結合・中央揃え
「年月日」や「取得」などのように、ひとつの項目の下に数個の列があるセルを、ひとつのセルにします。
セルA5からC5までを選択、Excelウインドウ上部の「ホーム」タブをクリックしたら、「セルを結合して中央揃え」ボタンをクリック。
同様に「取得」、「現在」、「備考」にも、「セルを結合して中央揃え」ボタンを使います。
セルH1の「取得年月日」は分かりづらいですがこのままだとセルからはみ出しています。それを見越してセルをひとつ多くとってありますので、H1とI1を選択したら、今度は「セルの結合」を使います。
(お好みで「セルを結合して中央揃え」をお使いいただいても構いません。)
同様に「所在」、「耐用年数」もとなりのセルと結合します。
「年月日」「摘要」「償却額」は下のセルと結合するときれいです。
その他、「摘要」、「数量」、「単価」、「金額」などの入っているセルも、好みにより中央揃えしていきます。
例では「帳簿の記帳のしかた」、16ページの記載例と同じように、普通の罫線と太罫線を使い分けています。
（５）「償却期間」列の挿入
「帳簿の記帳のしかた」の見本では省略されてしまっていますが、固定資産台帳には「償却期間」という項目が必要です。
「取得」の「金額」と「償却額」の間にひとつ、列を挿入します。
列Hを選択し、右クリックメニューから挿入を選択。
すると列Hに罫線の引かれた空白の列が挿入されます。
セルH5、H6を結合し、「償却期間」と入力してください。
（６）管理番号を入れる
固定資産台帳は、備品など一点につきひとつ作成します。ここまでの流れでいくと、ワークシート名をそれぞれの備品の名前にかえて管理したくなりますが、決算・申告に向けて集計をおこなうことを考えると、固定資産台帳のワークシート名がその都度変わると管理がしにくくなってしまいます。
ワークシート名は「固定資産台帳(1)」などにして、通し番号をつけて管理し、ヘッダーは空白にし、備品などの品名を上部中央のセルに入れるようにましす。これだとワークシート名をタブで見ただけではどの固定資産台帳がどの備品の分なのかが分かりにくいですが、自動で決算ができる書式にすることを優先したいと思います。
また自動計算用に、ワークシート内に管理番号の欄を設けます。これも決算・申告の準備のひとつです。セルA1・B1・C1、セルA2・B2・C2をそれぞれ結合し、一行目に「資産管理番号」、二行目に固定資産台帳の通し番号と同じ数字を入れます。
（７）列の幅・行の高さを調整する
年月日を記入する列などは最大で2桁数字が入ればいいので広すぎますね。逆に適用の列幅はできるだけ広くとりたいです。
また固定資産台帳はそれほど続けて記入をしていきませんから、行数がそう多くある必要はありませんので、見やすいよう、行の高さも広めにしようと思います。
列の幅は実際に数値や項目を入れてみて、
で調整するのがよさそうです。
※４．さらにいろんな機能を使ってみよう （２）摘要欄の幅を広くする参照
行の高さは、これから記入をしていく行について均等でいいので、7行目から22行目くらいまでを選択し、
でいっぺんに変更すると速いですね。
※同じく４．さらにいろんな機能を使ってみよう （２）摘要欄の幅を広くする参照
すると罫線を引いてある行が、次ページにはみ出してしまうと思います。きれいに1ページの書式になるよう、いらない行は削除しましょう。
2ページ目にはみ出した部分を行番号をドラッグして選択、行番号上で右クリックしてでてくるメニューから削除を選択します。
削除したい行を選択した状態でキーボードの「delete」キーを押しても、セル自体は削除されません。「delete」キーを押すことで削除できるのは、セルに入力されている内容だけです。
・選択した範囲の書式や罫線などを保ったまま、セルに入力した数値などを削除したい場合は「delete」キー
・選択した範囲の書式や罫線など、とにかくすべてを削除したい場合は右クリックメニューの「削除」
と覚えましょう。
1ページ目の最終行の下側の罫線は、このままだと普通の太さですから、ここを太罫線にすると書式としてまとまりが出ますね。
（８）最新の行の値を表示させる関数
ここで毎年の決算時に役立つ関数をひとつ導入したいと思います。
固定資産台帳は、下の画像のように「現在」の金額が「0」になるまで、償却する価値がその備品に残っている間はずっと続けて記入をしていくことになります。
すると上に示した償却期間をはじめ、償却額や現在の金額など、年によって違う数値が入る列が出てきます。
固定資産台帳から申告に必要になる数値は、いつでも同じ列の最新の数値ですから、それぞれの列で一番下の行に入力されている数値を表の最終行に表示させることにします。そこで使用するのが「LOOKUP関数」です。
・LOOKUP関数で最新年度の数値を表示させる行を下の画像のように設けます。
・設けた最終行にLOOKUP関数を使って、以下のような計算式を入力します。
| =LOOKUP(10^16,償却期間の数値を入力する範囲) |
| =LOOKUP(10^16,H7:H20) |
「10^16」は、Excelが数値と認識できる値の最大値の意味なのですが、LOOKUP関数を使った計算式の「検査値」となる部分に入れることによって、「,(カンマ)」のうしろで指定した範囲の最終行の値を表示させることができます。
・さらにIFERROR関数で計算式を囲む
ただ、上の画像のようにLOOKUP関数を使った計算式を入力して「enter(return)」キーを押すと、「#N/A」というエラーが表示されてしまいます。
これは「指定された範囲を見に行ったのに、値がありませんでしたよ」というExcelからの訴えです。
固定資産台帳で償却期間欄に数値の入力がない状態というのは、今のように空白の様式を編集したり、購入年の記入をまだしていないときですから、「最新年度の状態」も当然空白ということになります。
エラーが出る状態 ＝ 空白の状態 ＝ 数値の表示が不要な状態
ですから、IFERROR関数で先ほどのLOOKUP関数の計算式を以下のように囲んでしまいます。
| =IFERROR(エラーではないときの値(LOOKUP関数で導き出した値),エラーのときの値) |
| =IFERROR(LOOKUP(10^16,H7:H20),“”) |
・LOOKUP関数の計算式をコピー
償却額、現在の数量と金額、事業専用割合、必要経費算入額の最終行に、償却期間の最終行に入れたLOOKUP関数の計算式をコピーします。
（９）ワークシートのコピー
ワークシートのタブをダブルクリックし、シート名を「Sheet2」から「固定資産台帳(白紙)」に変更し、「上書き保存」しておきましょう。
固定資産台帳は購入したものごとにひとつ必要になるので、このワークシートは白紙のままとっておき、ワークシートをコピーして、シート名を「固定資産台帳(1)」、「固定資産台帳(2)」と変更して使っていくようにします。
最初の固定資産台帳のワークシート名で「(1)」とつけると、ワークシートを次にコピーしたときには「(2)」、その次は「(3)」と、自動で数字を替えてワークシート名をつけてくれます。
売上帳は売り上げを管理する帳簿ですから、事業を営んでいれば必ず必要になります。
仕入帳は、「商品」自体、またはその原材料を買って、商品を売っているという事業形態のときにだけ必要になる帳簿です。売上帳ができてしまえば、簡単に転用できます。
・売上帳の作成
税務署でもらえる資料の中に、「売上帳」の見本はありませんでしたので、現金と、預金口座を通して売り上げがあることを想定して作成することにします。
売上帳は売り上げを記録していくだけで、現金出納帳のように残高欄は必要ないので、経費帳のワークシートをコピーしてそれを元に作ります。
目指す出来上がりはこんな感じです。
これからの手順はこの出来上がりを目指して説明していきます。「現金での収入はまずない」という場合は「現金」の欄をなしにしたり、「返品・キャンセル」としたところに違う言葉を使ったり、ご自身で変えていただいて構いません。
・まずは「経費帳(白紙)」のワークシートをコピーして、ワークシート名を「売上帳(白紙)」にします。
・「金額」の入っている列D・Eを選択し右クリックメニューから「コピー」を選択します。
・F1セルを右クリックし、「貼り付けのオプション:」から「貼り付け」を選択します。
・今コピーしたF・G列がページから右にはみ出ますから、「摘要」の入っているC列の幅を調整して列Gまでが1ページに収まるようにします。
・ここで豆知識。
「摘要」欄の幅を調整しただけではうまく入り切らないかもしれませんね。全部を入れるためには「摘要」欄がかなり狭くなるかもしれません。
そんなときは「余白」も調整してみてください。
列のアルファベットのすぐ上、行番号のすぐ左にものさしの目盛りのようなものがありますね。これを「ルーラー」と呼びます。(ルーラーが表示されていないときは、ウインドウ上部の「表示」タブから「ページレイアウト」をクリックして表示方法を切り替えてください。)
下の画像のように、ルーラー上で濃い灰色になっている部分が「余白」です。印刷するとき、ここには印刷しませんよ、と示しています。
少し前まではプリンタの性能上、余白を既定からいじると印刷したとき端が切れてしまう、なんてことがよくありました。
最近ではプリンタもふちなし印刷が出来るくらいになっていて、この余白を多少いじったくらいではそんな問題は起こりません。
「あとちょっとで1ページに収まるんだけどこれ以上どの列幅も狭くしたくないなぁ」というときは、この余白で調整してください。このときだいたいで構わないので左右の余白を同じに保つと、表が用紙中央にある、きれいな書式が保てます。
・D・E、F・G列の「金額」をそれぞれ「売上」と「返品・キャンセル」に変更します。経費帳からのコピーで2ページ目にも同じ表があるので、この部分をコピーするか、同じように変更します。
・1ページにつき1か月の売り上げを記録できるよう、2ページ目のはじめにある「前ページから繰り越し」の行の摘要・計算式を削除し、2ページ目を11回コピーして13ページのワークシートにします。
(セルA60からG118を選択、コピーして、次のページの最初のセル(3ページ目ならA119を右クリックして貼り付け。この作業を11回繰り返します。)
1ページから12ページまではそれぞれの月用、13ページは月別売り上げの集計用です。
・年間の売り上げ集計を自動的にするために、13ページに計算式を入れていきます。計算式に引用するセルが分かりやすいように、ヘッダー下フッターに「&[ページ番号]月」などと入れてもいいかもしれません。
・13ページの「摘要」を、「月別売上(収入)金額」に変更します。
・13ページの空欄一行目(例だと行711)の「年月」に「1」「31」(1月31日)と記入。続く行にも2月28日から12月31日まで記入しておきます。
・13ページの1行目(例だとセルC711)をクリック、半角で「＝」と入力したら、そのまま1ページ目の最終行に移動し、金額の欄を順番に
=D58+E58-F58-G58
となるように選択していき「enter(return)」キーを押します。
・13ページの次の行には2月の、その次には3月の、ページ最終行の「売上」金額を足し、「返品・キャンセル」金額を引く計算式を12月分まで入れていきます。
ちょっと面倒な作業にはなりますが、この計算式を入れておくことによって、年末には月別の売上金額と、その年の売上金額総計が自動的に算出されるようになりますから、一度の手間だと思ってがんばってみてください。
12月分までの計算式が入ったら、そのすぐ下か一行空けたところに、12か月間の総合計を出す計算式を入れます。SUM関数を使います。
=SUM(1月分の合計が入っているセル:12月分の合計が入っているセル)
例であれば
=SUM(C711:C722)
で総合計が算出できる計算式が入りました。
試しに12月辺りの売上欄にどこでもいいので「500」、返品・キャンセル欄に「200」と打ってみてください。
13ページの12月分合計と、年間総合計額に「300」と表示されるはずです。
これで日頃は売り上げがあった都度売上帳に記入をするだけで、自動的に月別・年総合計売上額が表示される書式の完成です。
繰り返しになりますが、多少こまごまとした手間が必要ではありました。
ただ、一度罫線や計算式の入った書式を作ってしまえば、その先に渡り何年も、この書式をコピーして使っていくことができますから、最初に3年分、5年分の作業をしているのだと考えてみてください。
自動で算出する書式がなければ、事業を続ける限り毎年、年末から年明けにかけて、帳簿と計算機を使って膨大な作業をすることになります。
どちらを選ぶかはあなた次第です。
・仕入帳の作成
仕入帳は、「購入した商品を売って、購入額と販売額の差額で利益を得る」、あるいは「購入した材料から作品を作って売っている」という事業のときだけ必要になります。
事業のために使う事務用品・備品などの購入は「経費帳」か「固定資産台帳」に記入しますから、仕入帳は必要ありません。
売上帳をここまで完成できたら、ワークシートごとコピーしていくつかの項目を書き換えればそのまま「仕入帳」として使用できます。
・「売上帳(白紙)」ワークシートをコピーします。
・シート名を「仕入帳(白紙)」に変更します。
・Excelに備わっている「検索」と「置換」という機能を使って、ワークシート内の「売上」の文字列すべてを、一気に「仕入」に置き換えます。
ウインドウ上部の「検索と選択」ボタンをクリックし、「置換」を選択。
表示される小さなウインドウで、「検索」タブをクリック。「検索する文字列:」ボックスに『売上』(カッコは入れないように)と入力し、「すべて検索」ボタンをクリック。
表示された一覧の「シート」がすべて現在編集中のシート名「仕入帳(白紙)」であることを確認。違うシート名が混ざっていたら、「オプション」ボタンをクリックし、「検索場所(H):」を「ブック」から「シート」に変更し、再度検索。
「置換」タブに移動し、「置換後の文字列」に「仕入」と入力し、「すべて置換」をクリック。
上のようなウインドウが表示されたら「OK」をクリック、「検索と置換」ウインドウも「閉じる」をクリックして閉じます。
すると1ページ目から13ページ目まで、「返品・キャンセル」の左隣に入っていた「売上」が一気に「仕入」に書き換わりました。
13ページ目の項目、「月別仕入(収入)金額」のところだけ、「(収入)」を手動で削除すれば、仕入帳として利用できる書式の完成です。
１０．少し複雑な表作り 商品台帳
※「商品台帳」自体は、商品や原材料を買ったり売ったりしない事業形態には必要のないものですから、商品・原材料の売買がない事業を営んでいる場合には作成する必要はありません。ただ、少し高度な技術も紹介しているので、ご興味があればざっと読んでみてください。
商品台帳に特に決まった様式はありません。勤め先で商品や製品、原材料などの残高・残数を管理するのに、特別なシステムを使ったり、そのために作られた表を使った経験のある方もあるかもしれません。
それらを元にご自身で使いやすいように作成されるのもいいかと思います。その場合にも、以下の三点は念頭に置きながら作ってください。
今回は
・仕入れた商品・原材料が単体ではなく、
いっぺんに複数の同じ物を仕入れることに対応できる
・商品・原材料の出て行く先が顧客への販売だけではなく、
自分で使用したりする可能性もある
・申告時に必要な、「前年度からの繰越」「本年度の仕入れ」
「来年度への繰越」高を把握できる
ことを目標に、以下のような出来上がりを目指して作成していきます。
商品台帳 上部
商品台帳 下部
（１）罫線などの枠組みを作る
（２）計算式を入れる
※（１）には表作りに有用な情報がたくさん含まれていますが、
計算式も複雑なものをご紹介するので、
計算式に集中したい方のために
手順（１）で完成させた「商品台帳」をご用意しました。
Excel形式ファイルをダウンロード
(下の文字列をクリックすると、自動でダウンロードが始まります)
商品台帳(複桁) 1ページ目 計算式なし
（ファイル名: syohinfukukeisenpage1.xlsx ファイルの大きさ: 14.3KB）
（３）シートの保護とセルのロック
せっかく入れた複雑な計算式を
誤ってけしてしまわないために
シートの保護機能を使います
（４）決算に備えて
必要なページ数をコピーし
申告に必要な数値を算出する
計算式を作成します
（１）罫線などの枠組みを作る
上に挙げた点を網羅する表にしようとすると、ご覧いただけるとおり少し細かい区切りの表になります。そのため下準備として、「商品台帳」を作成するシート全体の文字(フォント)の大きさをまず変えてしまいます。
シート左上、「A」と「1」に挟まれた角の部分をクリックすると、シート全体が選択された状態になります。このままウインドウ上部の数字の入っている四角部分をクリックするか、そのとなりの小さな下向き三角をクリックして、今入っている数字を「8」に変更します。
すると自動で行の高さが先ほどより狭くなったのが分かると思います。
「商品台帳」は横長の表になりますから、A4の用紙を横向きで使うよう設定します。
ウインドウ上部の「ページレイアウト」タブをクリックしたら、「印刷の向き」を縦から横に変更します。さらに表全体がうまくページに収まっているかを確認しやすくするために、「表示」タブをクリックし、表示方法を「標準」から「ページレイアウト」に変更します。
下準備が整いました。８．固定資産台帳を作成しよう のときの要領で、使うセルの数・位置の見当をつけながら、項目を入力していきます。
この辺りまで入力したら、それぞれの列の幅を整えていきます。「月」や「日」などは絶対こんなに幅がいりませんよね。そのほか、数量や単価などは、どのくらいにしたらいいか、ぱっと決めるのは難しいと思います。
そんなときは一旦、例となる値を入力してみましょう。例えば「月」なら行4に「12」と入れます。数量や単価はご自身の用途に合わせて「100」や「100,000」などと入れてみてください。
このとき考えていただきたいのが、数値に「,(カンマ)」を入れるかどうかです。私自身は数値にカンマが入っていた方が絶対に見やすいので、数値には自動的にカンマが入るよう、設定しています。
カンマが自動的に入るように設定するには、「セルの表示形式」を変更します。
「セルの表示形式」はそのセルに入る項目がどんな性質を持っているかにより、セルの書式を設定するものです。
数値を記入したとき、例えば今の状態でセルB4に「12」と入力してキーボードの「enter(return)」キーを押し確定すると、即座に数字がセルの右端に移動して表示されると思います。
これはExcelの標準表示形式で、「数字は桁が揃って見やすいように右端に合わせて表示させる」ように設定されているからです。
ではカンマを自動的に入れる設定方法です。
カンマとは関係ない項目を入力するセルの表示形式まで変えてしまわないよう、仮で入れた数値の部分だけを選択したら、ウインドウ上部の「,」のボタンをクリックします。
するとちょうどこの「,」ボタンの上にある「標準」となっていた表示形式が
「通貨」に変わりました。
これで4桁以上の数値が入力されると自動的にカンマが振られる設定になり、数値の入る列におおよそ必要な幅の見当がつけやすくなりましたので、列と列の間でマウスポインタを
の形にして、そのままそこでダブルクリックします。
「月」を入力しているB列には「受け入れ」の文字列も入っているのでそちらに合わせて自動調整されますが、「日」を入力しているC列でこのダブルクリックを使うと、この列に入力されている一番幅の広い項目「31」に合わせてセルの幅がぐっと狭まったのがお分かりいただけると思います。
このダブルクリックでの自動列幅調整と、仮に入力した数値に合わせたドラッグでの列幅調整を用いて、下のように列幅を整えておきます。
それでは今列幅を整えた部分を、右側にコピーして活かしたいと思います。
ここまでで入力したのは、左側の「受け入れ」部分ですが、3行目をご覧いただくと、J列からQ列の書式がB列からI列と同じことが分かります。
まずB列からI列を列名で選択します。
列名で選択するには、ワークシート外側上部のアルファベットの並んでいる部分を、マウスポインタが下向き黒矢印になった状態でクリックします。ふたつ以上の列を選択したい場合は、マウスポインタが下向き黒矢印の状態で列名上をドラッグします。
下向き黒矢印拡大図
B列からI列を列名で選択したら、選択した列名上で右クリックして表示されるメニューから「コピー」を選びます(右クリックするときのマウスポインタも下向き黒矢印の状態です)。
すると列Bから列I全体が点線で選択された状態になりますので、今度はとなりのJ列列名上で右クリック、「コピーしたセルの挿入」を選択します。
するとセルに入力された内容だけでなく、整えた列幅もコピー元と同じまま、複製することができました。コピー元として選択した部分の点線で囲まれた状態を解除するには、キーボードの「esc」キーを押します。
同様にO・P・Q列を列名で選択し、R列名上で挿入します。R・S・T列までコピーした内容が入ったら、項目の足りない部分の入力や、コピーして違っている部分の修正を行います。
行2の高さを「0.93センチメートル(44ピクセル)にします。(見た目のごちゃごちゃしてしまうのを防ぐ処理なので、好みでしなくでも構いません)
ここで項目が1ページに入り切らないことが判明すると思います。
そんなときは「９．売上帳・仕入帳を作成しよう」で紹介したとおり、余白を調整してU列までを1ページに収まるようにします。お使いのプリンターによりますが、だいたい1cmほど余白があれば、印刷が可能だと思います。
それでも入りきらない場合には、この例では数量・単価・金額の桁数を多めに見積もっていますのでそちらをご自身の事業状況に合わせて減らすか、最初に紹介した方法で文字の大きさを変更し、セルの列幅自体を狭くすることで対応してください。
表上部の必要な項目はすべて入れ終わりました。必要な部分のセルの結合、好みに合わせた中央揃えなどをして、計線引きに備えます。
ここで作っている商品台帳では、一種類の商品・原材料につき、5行の記入欄を割り当てて、太線で区切っています。一種類につき、1項目の受け入れ(仕入れや顧客からの返品)、4項目の払い出し(売り上げや個人での使用)が記入できる仕様です。
さらに、行割りの細かい表になっていますので、項目が分かりやすいよう、ページの真ん中と下部にもページ上部と同じ「見出し部分」を入れています。
これら、一種類につき何行を当てるか、見出しを途中にも入れるか、などはお好みや用途に合わせて変えていただいて構いませんが、一番下にある「ページ合計」欄だけは決算用に必要になりますので入れるようにしてください。
ここでは見本通り、一種類の商品・原材料につき、5行を割り当てる書式で作成をしていきます。
まずはセルA1からU8までを選択し、罫線メニューから「格子」を入れ、さらに「外枠太罫線」も入れてしまいます。さらにA1からU3までを選択し、ここにも「外枠太罫線」を入れます。
するとこのように、ほぼ見本どおりの書式になりました。商品や原材料の名前を入れる「摘要」欄は、5行分のセルを結合します。また、目安として入れた仮の数値があったらここで消去しておきましょう。
先ほどは列名で使ったコピー・コピーしたセルの挿入を、今度は行名で使います。
行4から8までを行名上で選択、右クリックメニューから「コピー」を選択したら、行9の行名上で右クリック、「コピーしたセルの挿入」を選択します。行名で選択などするときのマウスポインタは、右向き黒矢印です。
行に関しては高さの変更を個別に施していませんから、これまでのようにセルで範囲を指定してコピー・貼り付けしても同じ結果が得られるのですが、今回のように横長で、ウインドウに左端から右端までが入りきっていないような場合には、行名で範囲を指定して複製した方が簡単です。
目指している様式ですと、真ん中に項目欄を挟む前に商品・原材料を入力できる部分をあとふたつ分、作ることになります。
先ほどコピー・挿入した直後は、まだ列4から8がコピー元として点線で選択されたままになっていますから、次に14行名上で右クリック・コピーしたセルの挿入、さらに19行名上で同様の処理をおこなうと、商品・原材料を記入できる同じ様式の欄が4つ並びました。
ここで見本の様式どおり、項目(表の一番上の「摘要」「月」「日」などの欄)を表中央に挟む場合は、今度は行2・3を行名で選択し、行名24上でセルの挿入をおこないます。
見本どおりに作ってきていると、コピー元の行2は高さを変えていますので、ワークシート内でセルを範囲指定してコピーする方法を使うと、この行の高さの変更は反映されません。
セルA24とA25を選択し、「セルを結合して中央揃え」ボタンで結合したら、「摘要」と入力します。
この「摘要」のセルは、コピー元では3つのセルを結合しています。そのためコピー先ではコピー元の罫線設定が反映されません。この結合されたセルA24とA25に、今までとは違う方法で罫線を入れたいと思います。
まず、セルA24とA25が選択された状態にし(先ほどの結合処理をした直後なら、すでに選択されています)、選択したセル上で右クリック、表示されるメニューから「セルの書式設定」を選択すると、以下のような小ウインドウが表示されます。
上部のタブの中から「罫線」を選択、左側の罫線の種類(「線」下の「スタイル」)中の太罫線をクリックして選択(選択されている種類の罫線はうっすらと四角で囲まれています)したら、右側のセルの罫線書式状況を示している四角内で、左側に当たる部分をクリックします。
すると選択している太罫線がセルの左側に引かれた状態になりましたので、「OK」ボタンでセルの書式設定ウインドウを閉じます。これでセルA24・A25の左側に太罫線を入れることができました。
すでにお気づきの方も多いかと思いますが、Excelのワークシートではなぜか一番外側の線を太罫線にしていても、太さが普通の罫線と同じに見えてしまいます。
今まではここで印刷プレビューを使って罫線の太さや種類を確認して来ましたが、このセルの書式設定ウインドウで確認したいセルを選択して、罫線の書式設定状況を確認することもできます。
それではさらに表作りを続けていきましょう。
行25の下側に罫線が入っていない結果になるかもしれませんが、これはこのままで次の処理に進みます。
行4から25までを行名で選択・コピーしたら、行名26上でコピーしたセルを挿入します。これで一気に、この表の枠組みの98パーセントくらいまでができてしまいました。
今回はすでにあるExcelの表を見ながら、見本と同じ見た目になるように表を作って来ましたが、ご自身で一から作成する場合にも、どんな表にするか、完成図を頭の中で描いたり、簡単に紙に描いてから作業にとりかかると、コピー・貼り付け・挿入などが多用でき、作業効率をぐんと上げることができます。
これからもExcelで表を作成するときには、常に完成した状態やその用途を頭の片隅に置きながら作業をしてみてください。
では、表の枠組みづくりの仕上げに、「このページの合計」欄を作成します。
※見本では行48・49を結合して「このページの合計」欄にしていますが、行48の高さを調整して「このページの合計」欄にしてもいいと思います。ここでは行48・49を結合させる方法の説明をします。
ここまでで(Excelの書式設定を既定から変更していなければ)、行48と49までが1ページ目に入るので、このふたつの行を結合させて「このページの合計」欄としたいと思います。
セルA48とA49を選択したら、ウインドウ上部の「セルを結合して中央揃え」ボタン右側の下向き三角をクリックし、「セルの結合」を選択します。
※「セルを結合して中央揃え」ボタンを直接押さず、必ず下向き三角から「セルの結合」を選択してください。「セルを結合して中央揃え」ボタンそのものをクリックしてしまうと、結合後のセルに入る数値が右側に寄り桁揃えされず、中央揃え(センタリング)されて表示する形式になってしまいます。
セルA48とA49を結合できたら、続いてセルB48・B49、C・・・と同様の処理をしていくのですが、ここでひとつ、Excelの編集機能を追加したいと思います。
追加するのは「繰り返し」ボタンです。このボタンは ２．操作を間違ったら・・・「もとに戻す」ボタンの活用 で紹介した「もとに戻す」ボタンと同じように設置でき、たった今した処理を場所や範囲を変えて繰り返しできるようにする機能を備えています。
特に必要ない、地道にひとつずつ複製する方が好きだ、という方は、次の部分まで読み飛ばしてください。
Excelウインドウの上部にある「ファイル」タブをクリックします。
表示される「ファイル」の左側に並ぶメニューから、「オプション」を選択してください。
すると「オプション」画面が別ウインドウで開きますので、左側に並ぶメニューから「クイックアクセスツールバー」をクリックしてください。すると画面が切り替わり、このような内容が表示されます。
左側の「コマンドの選択」のボックスを下に少しスクロールすると「繰り返し」という項目がありますから、それをクリックして選択したら画面中央にある「追加」ボタンをクリックしてください。
すると右側のボックスの一番下に「繰り返し」が追加されますので、画面右下の「OK」ボタンをクリックしてこのウインドウを閉じします。
するとExcelウインドウの左上に、「繰り返し」ボタンが追加されました。
列C以降はこの「繰り返し」ボタンを活用していきます。
※「繰り返し」ボタンを追加していない場合は、説明内で「繰り返し」ボタンを押しているところを「セルの結合」ボタンを押す、に入れ替えて読み進めてください。
まずはセルB48・B49を選択し、A列のときと同様に「セルの結合」をします。次にセルC48・C49を選択したら、先ほど追加した「繰り返し」ボタンをクリックします。するとセルC48・C49が結合されました。
同じ処理を列D・E・・・と列Uまで施してもいいのですが、さらに時間を短縮するため、セルD48・D49を選択したら、それ以降はキーボードの「CTRL(control)」ボタンを押しながら、それぞれの列の48行目・49行目を選択してみてください。
列ごとに2つずつのセルが選択できると思いますので、そのままT列まで別個に選択したら、「繰り返し」ボタンを押します。すると列Dから列Tまでの48行目と49行目を一度にそれぞれ結合させることができました。
それでは罫線を引いていきます。
まずセルA48に「このページの合計」と入力してください。文字列すべてが表示できない場合は
・列Aの幅を調整する
・「このページの合計」文字列の文字の大きさを小さくする
・「このページの」と「合計」の間に改行を入れ二行にする
などの方法で対応してください。
U列の「残数」はページ合計を出す必要のない項目(別個の商品・原材料を記入しているのだから、その総数を出す意味がありません)なので、セルU46からU49までを選択し、「セルを結合して中央揃え」ボタンを2回クリックします。
この処理では、一度目のクリックでまずセルU46とU47のセルの結合が解除され、二度目のクリックで選択したすべてのセルが改めて結合されます。
ここまでの下準備が済んだら、セルA48からT49を選択、ウインドウ上部の罫線ボタン、または選択範囲内で右クリックからのセルの書式設定で、「格子」と「外枠太罫線」を入れます。
U列は結合したセルを選択し、「外枠太罫線」を入れます。
「摘要」「受け入れ」「払い出し」や、真ん中・下部に入れた項目名(月・日・単価など)の欄も、「外枠太罫線」を使って区切るとより見やすい表になります。
次に、やはりベージ合計を算出する必要のない「数量」「単価」数値欄(例:D48・D49、E48・E49)に斜め線を入れたいと思います。これは好みなので、斜め線を入れず空白のままにしておいても構いません。
ウインドウ上部の罫線ボタン右側の下向き三角で表示されるメニューから「罫線の作成」を選択します。
するとマウスポインタがペンのような形に変化しますので、「数量」「単価」の数値欄にそれぞれ斜めの線を引くようにドラッグします。ペン型マウスポインタを元に戻すには、キーボードの「exc」ボタンを押してください。
この操作が細かい、難しいと感じる場合には、「数量」「単価」の数値欄を選択し、右クリックでセルの書式設定ウインドウを表示させ、斜め線ボタンをクリックすることでも同じ結果が得られます。
あとはD48・49、E48・49を残りの「単価」「数量」数値欄にコピーすれば表の枠組みは完成です。
（２）計算式を入れる
計算式を入れるところから使える、空の商品台帳をダウンロードしてご利用いただけます。
Excel形式ファイルをダウンロード
商品台帳(複桁) 1ページ目 計算式なし
（ファイル名: syohinfukukeisenpage1.xlsx ファイルの大きさ: 14.3KB）
最後の行にこのページの合計が表示されるよう計算式を入れます。最後の行に当たるセルを選択すると、行48・49が同時に選択されますが、計算式を入れるのはあくまでも48行目になります。
セルF48に、「前期からの繰越」の合計額を表示させるため、「SUM関数」を使います。
SUM関数はExcelでは一番良く使われる関数で、このサイトでも「６．計算式や関数を使いこなそう」の「（２）指定範囲の合計額を算出してくれる「SUM」関数」でご紹介してからすでに何度か用いています。
ただ今回は、表の真ん中に項目を入れる書式にしたため、SUM関数で上から下までの合計を単純に出すことができません。
このように離れた範囲の合計を算出する場合には、SUM関数の計算式の中に「,(カンマ)」を使います。
この商品台帳のF48の例だと、合計を出したい範囲は「F4からF23」と「F26からF45」です。
F4からF23の合計を出したいのであれば、数式は
| =SUM(F4:F23) |
ですね。
さらにF26からF45、のように離れた範囲の合計も算出したいときは、
| =SUM(F4:F23,F26:F45) |
とします。
方法はセルF48にまず
| =SUM( |
まで入力したら、F4からF23までをドラッグで選択し、そこで「,」を入力。
| =SUM(F4:F23, |
この状態で同様にF26からF45を選択し、最後にSUM関数を閉じる「)」を入力します。
| =SUM(F4:F23,F26:F45) |
上の画像の数式バー(F48の内容が表示されているところ)をご覧ください。
ExcelではSUMのような「関数」を使ったとき、この数式バーにポップアップで関数の内容が案内されます。
| =SUM(数値1,[数値2],[数値3], …) |
とあるとおり、SUM関数のカッコ内に「,」で区切って数値・セル・範囲を、最大30個まで並べることができます。
「,」で区切られていれば同じ列でなければいけない、などの制限はないので、例えば
| =SUM(F4:F23,I26:I45,T48,N4,100) |
| F4:F23＋I26:I45＋T48＋N4＋100 |
のように範囲・セル・数値の混ざった、合計額を算出する式を作ることも可能です。
そう考えると今回はわりと単純に、2つの範囲の連続したセルの合計を算出する数式になっています。セルF48に
| =SUM(F4:F23,F26:F45) |
と入力できたらキーボードの「enter(retrun)」キーで内容を確定します。
この表の最終行のそれぞれの項目の「金額」の数値欄は、列が違うだけで同じ範囲のセルの合計額になりますから、F48の数式をコピーして使います。
セルF48を右クリックでコピーを選択、点線でF48がコピー元に指定されている状態で、キーボードの「CTRL(control)」キーを押しながら、セルI48・N48・Q48・T48を選択します。
選択状態になっているセルのいずれかで右クリック、「貼り付けのオプション」から「数式」を選択してクリックすると、4か所にそれぞれの列の4行目から23行目と、26行目から45行目の合計を算出する数式が入力されました。
ページの合計を算出する計算式さえ入っていれば、あとは必要な数だけ表をコピーしてページ数を増やせばこの書式のままで決算に備えることができます。
ただせっかくExcelという表計算ソフトを使っているので、ここはもう少し、自動化できるところを自動化してみたいと思います。
とにかく決算に備えられればいい、という方は、「（３）シートの保護とセルのロック」まで飛ばしてください。
この「商品台帳」を実用で使うことを考えると、まず自動化したいのは、「数量」と「単価」から導き出される「金額」ではないかと思います。
では早速「金額」が最初に入ることになるセルF4に「数量」と「単価」を掛け算した金額を表示させる計算式を入力してみましょう。
| =D4*E4 |
| D4(数量)×E4(単価) |
ですね。あとはこの計算式をすべての「金額」が入る欄にコピーすれば、数量と単価を入力すると、自動的にその金額が算出される書式のできあがりです。
が、ここはもうひとひねりして、「数量」「単価」に金額が入っていない場合は「金額」欄に「0」を表示させない計算式にしてみようと思います。この「商品台帳」は桁が細かいので、もし上の数式をただコピーすると、金額の欄にバーっと「0」が並んでしまうことになりますから。
何も記入していない状態のとき、「金額」欄に「0」が並んでしまっても構わない、という方は、「残数を算出する」まで読み飛ばしてください。
「数量」「単価」に記入がないとき、「金額」欄を空白で表示させるために、今回は「IF関数」を使います。
「IF関数」とは英語の「if」の意味のまま、
「もし○○が☓☓だったらAA、そうでないときはBB」
という「条件」を指定する関数です。
「IF関数」の構造は、下のようになります。
| =IF(○○が☓☓だったら,☓☓のときはAA,☓☓でないときはBB) |
「=IF」で始まった計算式が「（）」の中で「,」で3つに区切られ、
・一番目の区切りで条件を
・二番目の区切りで条件に合ったときは何を表示するかを
・三番目の区切りで条件に合わなかったときは何を表示するかを
それぞれ指定しています。
具体的に今回の例であれば、D4(数量)とE4(単価)に何も記入されていなければ、D4×E4の答えを表示させない、という条件をつけたいので、まず一番目の区切りで
| D4×E4が0だったら |
| D4*E4=0 |
という形で条件を指定することにします。
そして二番目の区切りでは、D4×E4が0だった場合に何も表示させない、Excelで空白を意味する「””」を使って
| 空白を表示する(何も表示しない) |
| “” |
で、一番目の区切りの条件に合う場合には何も表示させない、という指定をします。
最後に三番目の区切りで、D4×E4が0ではなかったときにはD4×E4の答えを表示させるよう
| D4×E4の答えを表示する |
| D4*E4 |
と指定します。
これをひとつにまとめると、セルF4に入る計算式は
| もしD4×E4が0だったら,空白を表示する(何も表示しない),D4×E4の答えを表示する) |
| =IF(D4*E4=0,“”,D4*E4) |
となります。
この計算式をすべての「金額」の数値欄にコピーすれば、数量と単価を入力すると、金額が即座に表示される表のできあがりです。
ところでこの「商品台帳」では、商品・原材料を仕入れた(購入した)ときの単価で管理します。
例えば1,000円で仕入れた商品を1,500円で売っても、商品台帳上で「払い出し」欄には、「仕入れ値」1,000円の商品を1つ売り上げた、という記入をするわけです。
単価は仕入れのとき以外、記入する必要がないとなれば、仕入れの記入以外の単価は、自動で表示させるようにできそうですね。
これも先ほどの「IF関数」で設定が可能です。もう計算式はこのくらいで頭がいっぱい、単価を手動記入する方が計算式を考えるよりラク、という方は、「残数を算出する」まで読み飛ばしてください。
先ほど紹介した空白をあらわす「””」に加え、Excel内で「≠(ノットイコール)」をあらわす「<>」を使います。「<」と「>」を向かい合わせることで、「＝(イコール)ではない」ことを表現しています。
上の画像に添って説明していきます。
まず仕入れ時の単価、これは記入するよりほかありませんので、セルE4にはキーボードで手動記入します。
この商品を、2月5日にふたつ、売り上げたとします。そのとき、「数量」欄にあたるセルL5に入力があったら、セルM5に仕入れ時の単価である「1,000」を表示できれば、「金額」欄には上で紹介した計算式が入っていると仮定すると、ひとつのセルへの入力で、3つのセルへの表示が可能、ということになります。
ではセルM5にはどのような計算式を入れればよいでしょう。
目指すのは、
・L列に入力があったらE4に示されている
仕入れ時の単価を表示させる
・L列に入力がなければ空白を表示する
というIF関数を使った数式です。
| もし(L5が空白ではなかったら,E4の仕入れ時の単価を表示する,空白を表示する) |
| =IF(L5<>“”,E4,“”) |
となりますね。
仕入れ時の「単価」が「0」であることはほとんどないと思うので、この場合、売り上げのまだない状態のときには、「払い出し」側の単価を表示させないでおく、というのは、先ほどの「0」を表示させないよりも大きな意味を持ちます。
もしこの設定をしないと、仕入れ時に単価を入力した途端、その商品のすべての「単価」数値欄に同じ数字がダダっと並んでしまうことになりますから。
この数式の場合、めんどうなのはむしろコピーの作業です。
Excelの特性上、セルM5に入れた
| =IF(L5<>“”,E4,“”) |
の式を、すぐ下のセルM6にコピーすると
| =IF(L6<>“”,E5,“”) |
というふうに、計算式の入力される行が一段下に下がった分、計算式内で指し示している(Excelでは参照している、といいます)セルも、一行ずれてコピーされてしまうのです。
コピーされて「しまう」と言っても、一番目の区分の「L5」は「L6」になっていなければ、目的としている結果は得られませんからこれはこれでいいわけです。
問題になるのはどの行で単価を入力しても、参照したいセルは「E4」なのに、そこがコピー先により変わってしまうことです。
同じ計算式の中で、ひとつの参照先セルは変わらないようにし、もう一方の参照先セルはコピー先により変化させる、ということはできないので、ここは仕入れ欄以外の単価数値欄にセルM5の計算式をコピーし、二番目の区切りのセルをひとつずつ「E4」に書き換えていくしかありません。
と言っても、盲目的に「E」のついているところの数字を「4」に直していけばいいだけなので、時間もそれほどかかりません。
むしろ「金額」数値欄に「数量」と「単価」を掛け算した結果を自動表示させるようにしていると、下の画像のように一気に「#VALUE!」というエラーが表示されてしまうことにおどろかれると思います。
これは、金額欄に入っている計算式が参照している単価欄には、そのとなりの数量欄に数値の入力がないと数値が何も表示されず、かと言ってセルが完全に空でもないので、Excelが、「おい、単価欄に何だかワケの分からないものが入ってるぞ」と言っている状態です。
試しにどこでもいいので単価に数字を入れてみてください。Excelは機嫌よく、単価と金額を表示してくれるはずです。
こんなときに活躍してくれるのが、「IFERROR関数」です。
「IFERROR関数」の構造はこうです。
| =IFERROR(エラーではないとき表示する内容,エラーのとき表示する内容) |
つまり、数式のはじめに「IFERROR」を入れ、続くカッコ内の一番目の区切り部分に元々入れていた数式を、二番目の区切り部分に空白を示す「””」を入れることで、今のようにエラーになっていれば空白が、エラーでない場合は本来使いたかった計算式の答えが表示されるようになるわけです。
例: セルI5
| =IFERROR(IF(G5*H5=0,””,G5*H5),“”) |
カッコの中にカッコが入って、見た目にややこしい感じになりますが、数式バーに出るポップアップで、今どの関数のどの部分を編集しているのか、が確認できますので、参考にしながら挑戦してみてください。
「IFERROR関数」を加えた計算式は、そのまま他の「#VALUE!」と表示されている金額欄に「数式をコピー」する方法でコピーして問題ありません。
さらにこれまでの計算式の入っているセルD4からT8を、セルD9からT13にコピーすると、「単価」欄の計算式はきちんとセルE9を参照してくれるようになります。
ただ、このコピー後、セルI9・N9・Q9・T9には「警告」が表示されるかもしれません。内容は
「このセルにある数式が、セルの周辺の数式と異なっています」
というものです。
これは先ほど、エラーが表示されてしまうセルにだけ「IFERROR関数」を使うようにしたため、「IFERROR関数」を入れていない上記の4つのセルの計算式が「IFERROR関数」を入れた周りのセルの計算式と形式が違うので、確認を促すために表示されるもので放っておいても性能に問題はないのですが、あまり気持ちのよいものではありませんよね。
・この4つのセルにも「IFERROR関数」を使う
・一種類の商品・原材料の一行目は必ず仕入れが記入されるので、
計算式は入っている必要がないから削除してしまう
のどちらかの方法で対処します。個人的にはExcelが自動計算をすることでかかる負担を少しでも軽くするため、必要ない数式は消してしまうことをおすすめします。
ここまで整えたら、あとはセルD9からT13の計算式を残りのセルにコピーすれば、最小限の入力でかなりの部分を自動計算してくれる表になりました。
商品・原材料ごとに、残数を自動的に一番右の「残数」欄に表示させる計算式を作成します。使うのはSUM関数です。
「受け入れ」側の総数から、「払い出し」側の総数を差し引いたのが、現在のその商品・原材料の残数ということになりますね。
セルU4に入る計算式は、SUM関数を使い、セルD4からD8・G5からG8の合計から、セルL5からL8・O5からO8・R5からR8の合計を差し引くものになります。
どんな式になるか、頭に浮かんできたでしょうか。
「まだちょっと・・・」という方のために、ヒントを出します。
SUM関数をふたつ使います。
ひらめきましたか。
そう、そのとおり。
「受け入れ」側の合計と「払い出し」側の合計をそれぞれSUM関数で算出し、「－」演算子を間に入れます。
| =SUM(D4:D8,G5:G8)-SUM(L5:L8,O5:O8,R5:R8) |
まだ仕入れの記入のない欄の残高は表示させず、一度でも仕入れのあったものについては残数欄に「0」を表示させるようにするには、IF関数を以下のように用います。
| もし(D4が空白ではなかったら,残高を表示する,空白を表示する) |
| =IF(D4<>“”,SUM(D4:D8,G5:G8)-SUM(L5:L8,O5:O8,R5:R8),“”) |
残りの「残数」数値欄に上の計算式をコピーしたら、商品台帳の1ページ目が完成しました。
（３）シートの保護とセルのロック
今回の例では、かなり細かい区切りになる表を作成しているので、単純な表より、間違ってとなりのセルに入力をしてしまった、というようなことが起こりやすいと思います。
ここまででご紹介している数式をすべて用いていると、計算式が至るところに入っていますから、余計にそんな事態は避けたいですよね。
そこでこの「商品台帳」から、「シートの保護」と「セルのロック」という機能を使うことにします。いずれは「商品台帳」以外のシートにも使うことになります。
まずは「シートの保護」からご紹介しましょう。
ウインドウ上部のタブから「校閲」を選択し、表示されたメニュー中央辺りにある「シートの保護」をクリックしてください。
するとこのような小さなウインドウが表示されます。内容にはあとで触れますので、とりあえずそのまま「OK」をクリックしてください。
すると、現在編集中の商品台帳のワークシートが保護されました。
試しにどこでもかまわないのでセルを選択して、何か入力しようとしてみてください。下のようなウインドウが表示され、入力が受け付けられないと思います。警告ウインドウは「OK」か「×」ボタンで閉じます。
ご覧いただいたとおり、このままだと何もできないので、ウインドウ上部の「シート保護の解除」をクリックして、一旦保護を解除します。
解除したら、どこでもいいのでセル上で右クリックし、メニューから「セルの書式設定」を選択してください。
一番右の「保護」のタブを選ぶと、このような画面が表示されます。
上の「ロック」にレ点チェックが入っていますね。
これは「シートの保護を有効にしたとき、このセルにはロックをかけて変更できないようにします」という設定です。Excelではすべてのセルが既定でロックのかかった状態(「ロック」にレ点がついている状態)になっています。
ですから計算式を入れたセルや「月」「日」等の項目など、シートを保護したときに変更ができない状態にしたいセル以外の、日常の記帳で文字列や数値を記入するセルのロックを外すことで、シートの保護機能を有効に使うことができるようになるわけです。
ここまでの説明で、ご自身で不要と思われる計算式の説明は飛ばしてお読みいただいたので、どのセルに計算式が入っているかに差ができていますから、それぞれに日常の記帳で記入をするセルを選択し、解除する処理を行なってください。
数式の答えが「0」のとき、「0」を表示させない計算式を導入された方は、一見どのセルに計算式が入っているか分かりづらいと想います。
また、私自身ここまでで説明したすべての計算式を導入した商品台帳に記入をしてみて、いくら自分で計算式を入れているとは言え、これだけ細かい桁の表になると、「どこが手動記入するところでどこが自動なのか」をつい忘れてしまい、入れなくてもいい単価に数値を入力しようとして警告メッセージが表示されるということが何度か起こりました。
そのため、本当はできるだけ簡素な見た目が好きなのですが、この商品台帳ばかりは「塗りつぶし(所定のセルに淡く色をつける)」の書式設定を使用しました。それがこの「１０．少し複雑な表作り 商品台帳」の冒頭でご覧いただいた画像です。
「塗りつぶし」は、セルを右クリックしたとき表示されるメニューの中にある、「セルの書式設定」からおこないます。
・セルの範囲を選択
・選択したセル上で右クリック
・「セルの書式設定」をクリック
・表示される「セルの書式設定」ウインドウの上部で
「塗りつぶし」タブをクリック
色や塗りつぶしの濃さを選んだら「OK」で完了です。
「塗りつぶし」を使うかどうかはお好みです。
ただ、「0」を表示させない計算式も、「ロック」がかかっているかどうかも、両方とも一見しただけでは分からないので、「ロック」を外す処理を完了させるまで一時的に「塗りつぶし」をし、終わったら一気に「塗りつぶし」を解除してしまう、という使い方も有用かもしれません。
（４）決算に備えて
好みの見た目に書式・計算式とも1ページ目を作れたら、ご自身で必要と思われるページ数だけコピーをします。
が、1ページ目は「昨年度からの繰越」を専用に記入する書式にしていますので、いくつかの項目は変更する必要がありますから、まずは行名で1ページ目全体をコピーし、2ページ目に「コピーしたセルの挿入」をします。
2ページ目で項目を整えたらそれを必要なページ数分コピーしましょう。
コピー前に「セルのロックの解除」や「塗りつぶし」、そのた見た目の設定や計算式など、ご自身の好み・用途に合わせた作りになっているか、もう一度確認してください。確認には実際に一度「シートの保護」を有効にした状態でいくつか項目を入力してみて、期待しているとおりの結果になるかどうかを見るのが分かりやすいです。
そのほか、同じ書式であるはずの範囲を選択して「セルの書式設定」を開き、設定が揃っているかどうか確認する方法もあります。下の画像は、「セルの書式設定」の「ロック」項目で、ロックにレ点のついているセルとレ点のついていないセルが選択した範囲内に混ざって存在する場合の表示のされ方です。
「前年度からの繰越」のある商品や原材料が1ページでは収まりきらないくらいある場合は、当然「前年度からの繰越」分がすべて収まる量のページ数を1ページ目からのコピーで作成します。
ここでは「昨年度からの繰越」が1ページ分、1月に1ページ使うとして2ページ目の「日常使う書式」を12ページ分作ることにします。
2ページ目に挿入したセルのうち、「受け入れ」の「前期より繰越」となっている部分を「仕入」に変更します。例にそって作ってきていると上・中・下部に3か所あります。
変更が済んだら2ページ目を行名で選択してコピーし、13ページ目まで行名上で選択したセルの貼り付けをおこないます。
14ページ目に決算に必要な項目を入れる書式を作成します。目指す出来上がりは以下のようなものです。
・「期首商品棚卸高」には1月1日、または前年度中に事業を開始した場合は開業日時点での商品・原材料の合計額、すなわち項目を「仕入」ではなく「前期より繰越」としたページ分の合計額が入るようにします。
「前期より繰越」が1ページに収まっていれば単純に
| =F48 |
となり、複数ページに渡る場合は
| =SUM(1ページ目の合計,2ページ目の合計…) |
のようになります。
「期首商品棚卸高」はその年度の期首時点での商品・原材料の残高を示すものですから、「仕入」の欄にその合計額だけを示します。
・今年度計は今年度中にあったそれぞれの取り引きの合計額を出したいので、
「仕入」
「顧客からの返品」
「販売または加工」
「仕入先への返品」
「家事消費」
すべてに計算式を入れます。「仕入」にひとつ式を入れてしまえば、あとの4項目は「仕入」欄からコピーした計算式を使えます。
「今年度計」の「仕入」の金額欄に「SUM関数」を使った式を入れます。
| =SUM( |
ここまで入力したら、「期首商品棚卸高」に使っている以外のページ、その年に日常の取り引きを記入していくページの「このページの合計」欄を「,」で区切りながらすべて選択していきます。例のとおり12ページ日常の取り引き記入用ページを作っていれば、セル名が12個、SUM関数のカッコ内に並ぶことになります。
このとき、2ページ、3ページ・・・と素早く移動するコツをご紹介します。下の画像のように、「このページの合計」欄が表示されるようにしたら、ウインドウ右端の「スクロールバーの下の方をクリックしてみてください。
すると同じ表示範囲のまま、次のページ、そのまた次のページ、と表示していくことができます。この手法を使いながら、例ですと、2ページ目から13ページ目までの「仕入」の「このページの合計」欄を「,」で区切りながら選択していきます。
| =SUM(F97,F146,F195,F244,F293,F342,F391,F440,F489,F538,F587,F636) |
SUM関数のカッコ内に日常の取り引き記入用ページ数と同じだけの、セル名が並んでいるのを確認したら、上の計算式の入っているセルをコピーし、残りの4項目の合計欄に数式を貼り付けます。
・期末商品棚卸高には、その年の12月31日時点での商品・原材料の合計額を表示します。期末(12月31日)にどれだけの商品・原材料残高があるかは、その年に受け入れた商品・原材料の合計額から払い出した合計額を差し引くともとめることができます。
SUM関数を使って、「期首商品棚卸高」と「今年度計」の「受け入れ」側合計額を足した額から、「今年度計」の「払い出し」側合計額を差し引く計算式を作ります。
| =SUM(F640,F642,I642)-SUM(N642,Q642,T642) |
この「商品台帳」を紹介した計算式・関数をすべて使って作ると、かなりの部分が自動化された、なかなか使い勝手のよい表ができあがります。
ただ、Excelは計算式を入れたら入れた分だけ、動作が遅くなることでも知られていますから、そのあたりはご自身のパソコン環境と照らしあわせて、計算式の使用はあまり処理に時間がかかり過ぎない程度に抑えることも頭の片隅においておいていただければと思います。
ここまでで作成した帳簿類は以下のとおりです。
現金出納帳
預金出納帳
経費帳
固定資産台帳
売上帳・仕入帳
商品台帳
それぞれの帳簿ごとに、合計額や残高を算出するなど、決算・申告に備えた処理をおこなってきました。
この項からは、さらに複数のワークシートを参照し、その合計額等を導き出すことで、より本格的に申告に必要な数値を算出していきます。
まずは単純なものから手慣らししていきたいと思います。ここで作るのは「月別売上(収入)金額及び仕入金額」の評です。
こちらは申告の際提出する書類の、「所得税青色申告決算書」2ページ目にある、月別の売上(収入)額と、仕入れの額を一覧にした表で、したの画像のような書式です。これをもとに、年間の売上(収入)・仕入の総合計を算出します。
表の書式自体は単純ですね。計算式の入っていないExcelの表は、印刷することを前提に以下のようなものを準備しました。一から作るのがめんどうだ、という場合に、ダウンロードしてご利用ください。
Excel形式ファイルをダウンロード
(下の文字列をクリックすると、自動でダウンロードが始まります)
月別売上・仕入表
（ファイル名: uriagesireplane.xlsx ファイルの大きさ: 10.4KB）
※単独のワークシートを既存のExcelファイルに取り込む手順
(ご自身で作成した「月別売上・仕入表」をご使用になる場合は不要な手順です。「計算式の導入」まで読み飛ばしてください。)
・ダウンロードした単体の「月別売上仕入」シート名上で右クリック、メニューから「移動またはコピー」を選択。
・表示される小ウインドウで、「コピーを作成する」にレ点チェックを入れてから、「移動先ブック名」でここまで帳簿類を作成してきたブック名(Excelのファイル名のこと)を選択。
・次に表示される小ウインドウで、どこにワークシートを挿入するか指定。「挿入先」で青く選択したワークシートの「手前(左側)」に挿入されます。挿入したあと、ワークシートのタブをドラッグして移動もできるので、それほど神経質になる必要はありません。
できあがった「月別売上(収入)金額及び仕入金額の表に、計算式を入れます。仕入れや家事消費などがない場合は、該当欄に「0」を入力しておきます。
計算式といっても、月々の合計額は「売上帳」「仕入帳」で算出しているので、ここでおこなうのは「参照」という処理になります。
「参照」とは、ひとつのセルに、別のセルの値を表示させる処理です。例えば、1月の売り上げ金額の合計であれば、「売上帳」の13ページ目にまとめた1月31日時点での売り上げ合計額が自動的に「月別売上・仕入表」の1月の欄に表示されるようにします。
ワークシート「月別売上仕入」の1月分の売上(収入)金額欄(ダウンロードしたワークシートであればセルD2)に「＝」と入力します。
そのまま「売上帳」のワークシートタブをクリック。
1月分合計が算出されている欄(ダウンロードしたワークシートの場合はセルC711)をクリック、下の画像のようにセルが選択されたら「enter(return)「キーで確定。
すると「月別売上仕入」の1月の売上金額欄に「=売上帳!C711」の計算式が入り、参照先である「売上帳」の「セルC711」の数値が自動的に反映されるようになりました。試しに「売上帳」の1ページ目の「現金」欄に数値を入力してみてください。「月別売上仕入」の1月欄に、同じ金額が表示されます。
今回、ダウンロードいただいた「売上帳」を使っている場合、または当ベージの説明通りに「売上帳」を作成している場合には、「売上帳」の月別合計をひとつの表に一覧にしているので、1月の売上合計を算出しているセルのすぐ下(ダウンロード版であればセルC712)が、2月の売上合計になっています。
月別売上仕入の表もダウンロード版を使用しているか、説明通りに作成している場合には、1月の売上合計を示すセルのすぐ下が、2月の売上合計を表示するセルになっています。
このように参照先のワークシート(売上帳)と表示先のワークシート(月別売上仕入)のセルの位置関係が同じ場合には、「月別売上仕入」で合計を表示させるための計算式(=売上帳!C711)をコピーして利用できます。
「月別売上仕入」の1月分売上合計欄のセル右下で、マウスポインタが黒十字になったら、その状態で下方向にドラッグ、2月の売上合計欄の右下でドロップしてください。
すると、「月別売上仕入」ワークシートの2月の売上合計を示す欄に、「=売上帳!C712」のように、上のセルからひとつ下にずれたセルを参照する計算式が入りました。
このまま12月の合計欄まで同じ方法でコピーが可能です。
参照先のワークシート(売上帳)でこのように表示先(月別売上仕入)のセルと並びを揃えていない場合には、ひと月分ごとに表示先の計算式内で合計額を算出したり、飛び飛びになっているセルをひとつずつ選択していく作業が必要になります。
今回はじめて青色申告をする、というような場合には、なかなか難しいかと思いますが、このようにExcelでは複数のワークシートをまたがった計算式の導入も可能なため、できる限り「最終的に必要になる数値や表の形」を思い描きながら表の作成をしていくと、後々の手間を最小限に抑えることができます。
仕入金額の欄も、同様に仕入帳の13ページ目にある、月合計を参照する計算式で表示させます。
「家事消費」欄には、「商品台帳」の「家事消費」の年間合計を参照する計算式を入れます。
「雑収入」は主となる継続的な売り上げ(収入)以外の理由で入ってきたお金の合計を表示させます。
「雑収入」の例としては、例えば商品発送用に買っておいた梱包材を売った、とか、原材料をそのまま出荷した、ということがあれれば、「雑収入」として別個に表を作成します。
さあ、青色申告に向けた作業もいよいよ佳境に入ってまいりました。
ここまでコツコツと日々の取り引きを記入していると、私などは「損益計算書」を作るこの段階になると、もうわくわくしてしまうのですが、中にはここへ来て不安が高まる方もいらっしゃるかもしれませんね。
そんな不安を払拭すべく、内容を見ていきましょう。
まずは実際の青色申告に使う書式をご覧ください。
「青色申告決算書」の1ページ目、損益計算書
この損益計算書を元に作成するExcelでの書式は以下のようなものを目指します。これ以上縮小しても見づらいだけなので一部を表示しますが、もちろん1ページ(A4横)にすべての項目がおさまる書式になっています。
表の枠組み自体は、「セルの結合」を使用するくらいで単純なものです。要(かなめ)はやはり、他のワークシートで算出した数値の参照になるでしょう。項目の中には、金額を算出するために、さらに追加で表を作成する必要のあるものもありますが、それらは後述するとして、損益計算書全体を見ていきます。
計算式を入れるところから使える、空の損益計算書もご用意しています。
Excel形式ファイルをダウンロード
白紙の損益計算書
（ファイル名: sonekiplain.xlsx ファイルの大きさ: 14.9KB）
・売り上げ(収入)金額
ダウンロード版ではセルD2。
「月別売上(収入)金額及び仕入金額」の表の、「月別売上(収入)」の「計」欄を参照する数式を入れます。
（例）
| =月別売上仕入!D16 |
・期首商品(製品)棚卸高
ダウンロード版ではセルD4。
「商品台帳」の「前期より繰越」合計額を参照します。年度内に事業をあらたに開始した場合には、開業日当日にすでに手元にあったか、開業当日に仕入れた商品・原材料の合計額になります。
（例）
| =’商品台帳 ‘!F614 |
・仕入金額(製品製造原価)
ダウンロード版ではセルD5。
「商品台帳」の「仕入」の、年度計を参照します。
（例）
| =’商品台帳 ‘!F616 |
・売上原価の小計(②＋③)
ダウンロード版ではセルD6。
「損益計算書」内のセルの合計額を算出します。合計するのは見本にした申告用の「損益計算書」にあるとおり、「②＋③」、「期首商品棚卸高」と「仕入金額」の合計額です。
（例）
| =D4+D5 |
・期末商品(製品)棚卸高
ダウンロード版ではセルD7。
「商品台帳」の「期末商品棚卸高」欄を参照します。12月31時点で手元にある商品・原材料の合計額です。
（例）
| =’商品台帳 ‘!F618 |
・差引原価(④－⑤)
ダウンロード版ではセルD8。
ここも単純に、同じ「損益計算書」内のセルの差し引き額を算出する計算式が入ります。前期からの繰越と年度内の仕入額の合計から、年度末の残高を差し引くことにより、「今年度中の売り上げの元になる商品原材料の仕入れ値の合計」を算出しています。
（例）
| =D6-D7 |
・差引金額(①－⑥)
ダウンロード版ではセルD9。
実際の収入金額(①)からその売り上げの元となる商品・原材料の仕入れ値を差し引くことにより、今年度の純売上(収入)額を算出します。
商品や原材料を購入しない、サービスだけを提供するような事業形態の場合には、「売上原価」欄に記入する額がありませんから、①の「売上(収入)金額」と⑦の「差引金額」には同じ数値が入ります。
（例）
| =D2-D8 |
ここまで見てきたとおり、「損益計算書」は今までに作成した帳簿内のセルの参照と、損益計算書の表内での計算により成り立っています。
「損益計算書」という耳慣れない言葉のせいで、何を記入するのか見当もつかなかった表が、案外ただ日常の帳簿つけの総まとめをするだけだった、というのがお分かりいただけると思います。
このように、青色申告特別控除を受けるために提出する書類に、どんな数値を記入する必要があるのかが分かっていると、日頃の記帳もただ漠然としているのではなく、目的をはっきりと持って気分よくできるかもしれませんね。
・経費
それぞれの項目について、それぞれの「経費帳」の、年間合計額を参照する計算式を入れます。例外である⑱の「減価償却費」については、次の項「１３．減価償却費の計算」で説明します。
と言っても、「損益計算書」にある経費のおそらく半分ほどは、該当する必要経費の出費のない方がほとんどかと思います。
ここではどなたでも必ずありそうな「水道光熱費」の計算式例を示します。
（例）
| =水道光熱費!D117+水道光熱費!E117 |
下の画像のように、水道光熱費」の表に、「現金」と「その他(口座振替やクレジットカード払い)」の2項目があるため、損益計算書上でふたつのセルの合計を算出しています。
・減価償却費
プリンタやパソコンなど備品を購入し、かつ経費としての処理方法に「減価償却」を選んだ場合、償却額が0円になるまで毎年減価償却をし、その分を「減価償却費」として計上する処理が必要になります。
記入する金額を算出するためにも、提出する「青色申告決算書」の必要な別表「減価償却費の計算」に記入するためにも、追加で表を作成します。減価償却費が生じる場合は、次項「１３．減価償却費の計算」をあわせてご覧ください。
減価償却については、「初心者さんの青色申告」ページの「固定資産(減価償却費)について」、当ページの「８．固定資産台帳を作成しよう」にさらに詳しく記載しています。
・雑費
ダウンロード版ではセルH16。
経費のうち、すでに設定されているどの項目にも含められない支払いがある場合は、「雑費」としてまとめます。
経費帳の書式で、ワークシート名を「雑費」として使うことで対応できます。
・経費項目の計
ダウンロード版ではセルH17。
丸付き数字、8から31までにあたる、経費の合計を算出する計算式を入れます。
（例）
| =SUM(D11:D19,H2:H16) |
・差引金額（㉝）
ダウンロード版ではセルH17。
⑦で算出した純粋な売上(収入)金額から経費を引くことで、「純利益」を算出します。
（例）
| =D9-H17 |
・各種引当金・準備金等
「損益計算書」の右上にあたるこの部分は、こちらのサイトで例としているひとりでの小規模の事業では、いずれの項目の記入も不要な場合がほとんどです。
貸倒引当金とは・・・得意先にお金を貸したり、売掛金という代金をまとめてあとで受け取る方法を取り入れていると、相手先の倒産や資金繰りの都合により、そのまま返ってこなかったり支払いがなされない可能性がでてきます。現実にそうなってしまった場合、それが「貸し倒れ」と呼ばれ、「貸倒引当金」はそれに備えるため、貸し出しや売り掛けている金額のうち一定の額をあらかじめ準備しておくものです。「繰戻額等」欄には前年度の額を入れ収入扱いとし、「繰入額等」欄には今年度の額を入れ支出扱いとします。
・専従者給与とは・・・生計を一にしている家族や親族で、事業の手伝いなどをしている人がいる場合には、その人たちに給料・賞与(ボーナス)を支払い、その分を売上(収入)から差し引くことができます。給料・賞与を払いたい人がいる場合は事前に「青色申告専従者給与に関する届出書」の提出が必要です。また、通常パートなどで収入があっても受けられる「配偶者特別控除」が、この専従者給与を受け取っている場合には適用されません。
・青色申告特別控除前の所得金額
ダウンロード版ではセルM11です。
ここに入る金額が、その年度のあなたの事業での「純所得」となり、青色申告をしなければこの金額を確定申告書に所得として記入し、課税の計算のもととなる額になります。
㉝で算出した「純利益」に、各種引当金・準備金等の計を足し引きした金額が入りますので、各種引当金・準備金等に何もなければ㉝の差引金額がそのまま入ります。
（例）
| =H18+M5-M10 |
・青色申告特別控除額
「青色申告決算書の2ページに、計算用の表が準備されています。ここに直接記入しながら計算してもいいですが、Excelで自動計算できるようにしてしまった方が楽ですね。
手順は「１４．青色申告特別控除額の計算」で詳しくご説明します。
・所得金額
ダウンロード版ではセルM13です。
青色申告特別控除額を差し引いた、最終的な「純所得」を記入します。この金額を確定申告書に所得として記入します。
（例）
| =M11-M12 |
以上で青色申告の山場とも言える「損益計算書」部分の記入が完了です。
実際にはこうして算出した額を税務署でもらえる申告書類に手書きで書き写すか、国税庁ホームページの「確定申告書等作成コーナー(リンク先はその時点での最新の内容です。毎年１月のはじめくらいに新しい年度に対応した内容に更新されます。)」のサイトで金額等を入力し、印刷したものを提出します。
ここまでの見通しがついていても、決算と申告は年一回のことなので、毎回戸惑ってしまう、という方が多いです。
そのためにも、Excelでご自身の事業形態に合わせた帳簿類の形を作っておくことは、とても有意義であると思います。
私自身、ご紹介している手順で作成したExcelファイルを元に申告をしていて、記帳をこまめにしています(忘れっぽいので)。
帳簿と実際の金額や在庫残高を確認・一致させてしまえれば、毎年自動的に申告書類に記入の必要な数値が算出されている状態になっていて、申告書類が税務署で用意される1月の中旬ころには、あとはもう書類の受け付けが開始されるのを待つばかり、という状況になっています。
こちらではできるだけたくさんの方にご参照いただけるようにと、項目やページの数を多めに設定して説明しています。小規模であればあるほど、ページ数の減らせる帳簿も多くありますから、ご自身の事業に応じて調整していただくと、より使いやすいExcelファイルにできると思います。
備品を購入した歳、費用処理方法に「減価償却」を選択した場合には、購入した年度から毎年の償却額である償却残高が０になるまで、償却の処理が必要になります。
備品は個々に「固定資産台帳」で管理し、毎年の償却額の総額を経費として計上します。青色申告決算書の2ページ目にある「減価償却費の計算」に記入・計算することで、「損益計算書」の「減価償却費」に入る本年分の必要経費算入額を導き出せます。
減価償却費の計算 記入例
(国税庁ホームページ 青色申告決算書の書き方 4ページ)
「８．固定資産台帳を作成しよう」で手順をご紹介・説明したExcel形式の固定資産台帳は、すでにこの「減価償却費の計算」を念頭に置いて作成しましたので、あとは個々の固定資産台帳を参照する計算式を入れた「減価償却費の計算」の表を作成するだけとなります。
枠組みはこんな感じで、ほぼ提出用の「減価償却費の計算」と同じ表を作ります。
Excel形式ファイルをダウンロード
白紙の減価償却費の計算
（ファイル名: genkasyokyakuplain.xlsx ファイルの大きさ: 12.5KB）
減価償却の終わっていない備品の数だけ固定資産台帳がありますから、それぞれの項目をそれぞれのワークシートで参照する計算式を入れていきます。
・資産管理番号
ダウンロード版ではセルA2。
ワークシート「固定資産台帳(1)」のセルA2を参照する計算式を入れます。
（例）
| =’固定資産台帳 (1)’!A2 |
・減価償却資産の名称等(繰延資産を含む)
ダウンロード版ではセルB2。
参照先は固定資産台帳(1)の、備品の名前が入っているセルです。ここからは固定資産台帳の参照先セルに入力のない(空白の)ときには、「減価償却費の計算」での表示も空白になるよう、IF関数を用います。
（例）
| =IF(‘固定資産台帳 (1)’!E1=””,””,’固定資産台帳 (1)’!E1) |
・面積又は数量
ダウンロード版ではセルC2。
「固定資産台帳(1)」のセルE7を参照します。
（例）
| =IF(‘固定資産台帳 (1)’!E7=””,””,’固定資産台帳 (1)’!E7) |
・取得年月
ダウンロード版ではセルD2。
「固定資産台帳(1)のセルK1を参照します。
（例）
| =IF(‘固定資産台帳 (1)’!K1=””,””,’固定資産台帳 (1)’!K1) |
・取得価格
ダウンロード版ではセルE2。
「固定資産台帳(1)」のセルG21を参照します。
（例）
| =IF(‘固定資産台帳 (1)’!G21=””,””,’固定資産台帳 (1)’!G21) |
・償却の基礎になる金額
ダウンロード版ではセルF2。
となりのセルE2の金額がそのまま入ります。
（例）
| =E2 |
・償却方法
ダウンロードではセルG2。
「固定資産台帳(1)」のセルM1を参照します。
（例）
| =IF(‘固定資産台帳 (1)’!M1=””,””,’固定資産台帳 (1)’!M1) |
・耐用年数
ダウンロード版ではセルH2。
「固定資産台帳(1)」のセルK3を参照します。
（例）
| =IF(‘固定資産台帳 (1)’!K3=””,””,’固定資産台帳 (1)’!K3) |
・償却率
ダウンロード版ではセルI2。
「固定資産台帳(1)」のセルM2を参照します。
（例）
| =IF(‘固定資産台帳 (1)’!M2=””,””,’固定資産台帳 (1)’!M2) |
・本年中の償却期間
ダウンロード版ではセルJ2。
「固定資産台帳(1)」のセルH21を参照します。
（例）
| =IF(‘固定資産台帳 (1)’!H21=””,””,’固定資産台帳 (1)’!H21) |
・本年分の普通償却額
ダウンロード版ではセルK2。
「固定資産台帳(1)」のセルI21を参照します。
（例）
| =’固定資産台帳 (1)’!I21 |
・割増(特別)償却費
ダウンロード版ではセルL2。
通常該当しないので、空白のままにします。
・本年分の償却費合計
ダウンロード版ではセルM2。
普通償却費と割増(特別)償却費の合計額です。
（例）
| =IFERROR(K2+L2,””) |
・事業専用割合
ダウンロード版ではセルN2。
「固定資産台帳(1)」のセルL21を参照します。
（例）
| =IF(‘固定資産台帳 (1)’!L21=””,””,’固定資産台帳 (1)’!L21) |
・本年分の必要経費算入額
ダウンロード版ではセルO2。
「固定資産台帳(1)」のセルM21を参照します。
（例）
| =IF(‘固定資産台帳 (1)’!M21=””,””,’固定資産台帳 (1)’!M21) |
・未償却残高
ダウンロード版ではセルP2。
「固定資産台帳(1)」のセルK21を参照します。
（例）
| =IF(‘固定資産台帳 (1)’!K21=””,””,’固定資産台帳 (1)’!K21) |
・摘要
ダウンロード版ではセルQ2。
「固定資産台帳(1)」のセルM3を参照します。
（例）
| =IF(‘固定資産台帳 (1)’!M3=””,””,’固定資産台帳 (1)’!M3) |
・本年分の必要経費算入額の合計額
ここまでの手順で備品の個数分参照する計算式を入力したら、「本年分の必要経費算入額」の合計を最終行に表示させます。
（例）
| =SUM(O2:O9) |
これで今年度、必要経費として計上する減価償却費の合計額を算出することができました。１２．損益計算書の⑱「減価償却費」に合計額を記入します。
いよいよお待ちかねの、青色申告特別控除額の算出です。
ここでは簡易的な帳簿つけをおこなって青色申告をした際に得られる、最高10万円の特別控除を受ける場合を想定して説明します。
まずは申告書類の該当欄を見てみます。
それを元に、Excel形式で作成する表の例は、以下のようになります。
・本年度分の不動産所得の金額
あれば記入します。なければ空欄です。
・青色申告特別控除前の所得金額
カッコ内にあるとおり、1ページ目の「損益計算書」の㊸の額が自動的に入るように、該当のセルを参照する計算式を入れます。このとき「金額」欄のカッコ内にあるように赤字、つまり「損益計算書」の㊸の額がマイナスだったときには「0」を表示させるよう条件づけます。
（例）
| =IF(損益計算書の㊸が0より小さいなら,0を表示,そうでないなら損益計算書㊸の額を表示) |
| =IF(損益計算書!M11<0,0,損益計算書!M11) |
・65万円の青色申告特別控除を受ける場合
今回は該当しませんので2つの欄とも空白のままにします。
・上記以外の場合
ここでいう「上記」とは「65万円の青色申告特別控除を受ける場合」ですから、10万円の青色申告特別控除を受ける場合にはこちらの2つの欄に記入をします。
「10万円と⑥のいずれか少ない方の金額」
⑥は「不動産所得」です。不動産所得の額を参照し、不動産所得が10万円より少なければ不動産所得の金額を、10万円より多ければ「100,000」と表示されるようにします。
不動産所得がなければ「0」と表示されます。
（例）
| =IF(不動産所得が10万円より少ないなら,不動産所得額を表示,そうでないなら100000を表示) |
| =IF(D2<100000,D2,100000) |
「青色申告特別控除額」
カッコ内にあるとおり、「10万円－⑧」と⑦を比べて、少ない方の金額が表示されるようにします。
⑧は不動産所得がなければ「0」と表示される欄ですから、不動産所得のない方の場合は、10万円と「損益計算書」の「青色申告特別控除前の所得金額」のどちらか少ない方の金額が、青色申告特別控除額として入ります。
（例）
| =IF(10万円－⑧が⑦より小さいなら,10万円－⑧を,そうでないなら⑦を表示) |
| =IF((100000-D6) |
これで青色申告特別控除額の算出ができました。
「上記以外の場合」の「青色申告特別控除額」欄を参照する計算式を「損益計算書」の㊹、「青色申告特別控除額」に入れます。
（例）※「損益計算書」ワークシートに入力する計算式です。
| =特別控除額!D7 |
ここまで入ったら、「損益計算書」に必要な計算式がすべて出揃い、10万円の青色申告特別控除を受けるために必要な決算書に記入する金額が、帳簿に記帳を続けるだけで自動的に算出されるExcelファイルの出来上がりです。
