Excelで見積書・請求書の明細を作成する時・・小計はどうする？

こんにちは、ちちくろです♪

前回・・Excelで項番を自動で挿入するための数式を詳しく説明させてもらったのですが・・投稿後に沢山の方がコメントをくれました・・。

頂いたコメントはすべて英語だったので・・Googleの翻訳を使い日本語に翻訳してチェックさせていただきましたが・・訳が完全でないのでコメントの内容を完全に理解しきれていない状況です。

ですが・・おおむねのコメントは私の記事が皆様の何かのお役に立てたようで・・少なくとも「何を書いているのだ！！馬鹿野郎」などというコメントがなかったので・・ほっと一安心といったところ・・。

コメントを頂いた方に個別に返信したほうが良いのでしょうが・・いただいたコメントの内容が理解しきれていない状態ではチグハグな返信になりそうなので個別に返信するのは控えることにいたします・・ゴメンナサイ・・。

しかし今回コメントを頂いた方全員に・・ありがとうございます・・とお礼申し上げます。

そして・・当ブログにリンク等は・・特に制限を設けるつもりはないので・・ご自由に    ・・。

私への問い合わせにつきましては・・下の図のところをクリックしてもらえると可能です。

私はこのブログを・・Word pressで・・テーマはサーバー契約時に勧められた有料テーマを使用しています。

IEで表示がおかしいということですが・・現在調査中です。

しかし・・IEはMicrosoftのサポートも終了しているので検証が難しい状態です・・私はHTMLのスキルが非常に低いため・・解消することは難しいかもしれません・・ゴメンナサイ・・。

個別に返信をしない方針にしたので・・早くブログを更新しようと・・頭の中の引き出しを引っ掻き回したのですが・・見つかるのはガラクタばかり・・。

ひとつだけ・・良さそうな物があったので記事にしてみました♪

Excelで作る書類

皆さんがお仕事でExcelを使うときは・・見積書や請求書が多かと・・。

かくいう私も現役時代はExcelを使って見積書などを作っていました♪

Excelを使うと・・見積等の明細を簡単に作ることが出来ます。

まぁ・・Excelは表計算ソフトなので・・当たり前なのですが・・。

明細の作り方

明細表を作る時ってほとんどの方が下の図のような形で作るかと・・。

この場合・・G列の価格の部分には数量と単価をかけたものを表示させるために数式を書き込むことに・・。

その時の数式は・・”=E2*F2″となり・・そしてG23には合計を表示させるためにSUM関数で合計を求めます。

ただ・・これだD列が空白の行に・・数字の【0】が表示されてしまうので・・Excelの【ファイル】→【オプション】→【詳細設定】でゼロを表示しないように設定するのですが・・何かの拍子に何故かゼロがひょっこり顔を出すので・・。

私はIF関数とISBLANK関数でゼロを表示しないようにします・・具体的には数式をこのように・・”=IF(ISBLANK(E2),””,E2*F2)”

これで数量を入れていない行は空白にすることが出来ます。

これが明細書作成の基本かと思うのですが・・。

小計は

明細の数が多いと・・項目ごとに分ける必要が出てきます・・そうなると項目ごとに小計を入れることも必要になりますよね。

図のように【果物】の小計を入れるために・・G5のセルにSUM関数を入れることに

これだとせっかく数式を入れたテンプレートを作成したのに・・明細書を作る途中で数式を変更する必要が出てきてしまいます。

Excelワークシートのテンプレートは基本数式を入れたセルはロックしておきたいのですが・・。

計算を裏で

そこで私が考えたのは・・計算を裏でしてもらうこと・・そして必要なものを表のセルに表示させるということです♪

どういうことか説明します。

まず・・ワークシートに価格を計算するH列を・・小計を計算するI列を作成します。

H列とI列の数式

価格を計算するH列の数式は・・”=E2*F2″と書き込みます・・この時ゼロが表示されますが、気にしないようにしましょう。

何故なら・・H列は最後には非表示にしてしまうので・・。

次に・・小計を計算するI列の数式は・・”=IF(D2=”小計”,0,I1+H2)”    と書き込みます。

これは少し複雑ですが・・数式の内容は、D列のセルに”小計”と書き込まれたら値を０に・・それ以外の時は、セルI1の値とセルH2の値をプラスするということ

すると    H列には価格が表示され・・I列には合計金額が表示されます・・。

そしてD5のセルに”小計”と書き込むと・・I５のセルの値は０になります

小計が書き込まれたら0にするのは・・新たな小計を計算するため・・。

G列の数式

最後にG列に数式を書き込みます・・これは少し複雑・・書き込む数式は”=IF(OR(ISBLANK(D2),LEFT(D2,1)=”【”),””,IF(D2=”小計”,I1,H2))”というもの・・。

G列のセルを空白にしたい時は・・D列のセルが空白もしくは項目が書き込まれている時なので・・青い線が引かれたIF(OR(ISBLANK(D3),LEFT(D3,1)=”【”,””となります。

この数式の意味は・・D3のセルが空白または1文字目が”【”ならば空白にするということに・・。

補足として・・IF関数では複数の条件で同じ動作をさせたい場合OR関数が使えます・・この場合・・D3のセルが空白（1つ目の条件）もしくは1文字目が”【”（2つ目の条件）ならば空白にする（同一の動作）ということに・・。

次に・・G列のセルに表示させるのを価格か小計かを選択するために・・赤い線が引かれたIF(D3=”小計”I2,H3・・。

この数式の意味は・・D3のセルが小計ならばI2を参照し・・それ以外ならばH3を参照するということになります。

試しにD5のセルに”小計”と書き込むと・・。

G5のセルにセルI4の値2500が表示されることに・・。

最後に・・合計を記入するG23のセルはH23のセルと同期させています・・H23にはSUM関数を使いH2からH22までの合計を計算するようにしています。

G23のセルにH2～H22までの範囲を指定してSUM関数を書き込んでも良いのですが・・パッと見た目が複雑になるのであえてこのようにしています♪

後はH列とI列を非表示にしてしまえば完成です♪

まとめ

Excelのワークシートで数式の入ったテンプレートを作った場合・・数式が書きこまれたセルはロックしておくことが望ましいと思います♪

何故なら・・数式が入ったセルをロックしておかないと・・何かの拍子で数式がおかしくなってしまい・・間違った計算結果となってしまうからです。

計算結果がおかしくなった原因を探すのは・・とても難しいものですぐに見つけることは困難なことです。

私は、そのためには・・計算を裏のセルに任せてしまい・・表のセルには必要に応じて参照先を変えてしまうという方法をよく使っていました♪

それから・・一応明細書を作成した後に検算しておくことをお勧めします・・。

数式を間違っていたら、大変なことになりかねません・・最低でも合計は検算するようにしましょう・・何事も過信は禁物です♪

この方法は私が個人的に試した方法です・・もしこの方法を採用されるなら・・自己責任でお願いします。

最後に・・Excelはとても多機能なソフトなので今回ご紹介したものよりもっと良い方法が見つかるかも・・もしご存じの方がおられたら教えてもらえると嬉しいです。

2024年6月30日・・追記

より分かりやすく説明するために・・YouTubeの動画を作りました下にリンクを張りますのでよろしければどうぞ