必見! 事務員さんのための 「汎用見積書」


匠への技

 「匠への技」第12弾は、販売会社等に勤める事務員さんのための見積書(タイトルを変更すれば請求書にもなる)作成をポイントとしてExcelで構築する場合のサンプルを提供します。
いつも記述することですが、ハンドメイドの世界に「これしかない」と断言することができません。
WebMambow的作り込みですのでみなさんが構築する際の糧としてご活用下さい。

このサンプルは、プログラム製造の依頼で作成したものから抜粋したもので、実際には顧客管理、見積から精算までの流れを範囲としています。もちろん、顧客管理用のDB、見積用のDBも存在し顧客へのダイレクトメールや来歴照会なども対応しています。

サンプルは、簡単に作り直していますので容易に理解して頂けると思います。  ^^
また、実際の業務にも適用可能なように作っています。印刷はVBA(オープンソース)で用意しました。
強制するものではありませんが、ご利用に際しましてご意見・ご感想を掲示板やメールにてお寄せ頂ければ幸いです。


 画面デザイン       ブック名:takumivol-012.xls   シート名:Entry、見積、商品テーブル

    セル書式の説明へ →   1:C3〜C8 2:A列 3:B、C、D列 4:E列 5:F、H列 6:G、I列 7:J列 8:B33
  

■ 仕 様 設 計
 匠への技Vol−12の見積書作成では、3つのシートから構成されています。
見積データを入力するシート「Entry」、シート「Entry」から見積書を転記(代入)するシート「見積」、見積商品の一覧のシート「商品テーブル」です。
仕組みを簡単に説明すると、シート:Entryで入力したデータがシート:見積に自動転記され、印刷ボタンで見積書が発行される。この流れを基本としています。
直接見積書のフォームに入力しない点が注視すべき所と思います。「試算」をしながら見積書を作るといったワークフローをイメージした場合に見積入力とフォームの一体化では容易な形を作ることができません。
「試算値から見積書を作る」 この点がVol−12の察して頂くポイントです。


@ 事前準備
  • 見積の対象となる商品を登録します。  (シート:商品テーブルへ登録)
  • 自社名、住所、電話、FAX、URL、E-mailを事前に入力します。  (シート:見積へ入力)

A 見積データの入力
 常時カレントするシートは、「Entry」です。入力セルは、見積書鑑部のC3からC8までと11行目から始まる明細部のA列の商品コードとE列の数量となります。     
B 見積書のフォーム
ここ を参照ください。



 使用した関数  
  1. IF(論理式,真の条件,偽の条件)   
  2. VLOOKUP(検索値,範囲,列番号,検索の型)
  3. AND(論理式1, 論理式2, ...)
  4. TODAY()
・・・・・12回目登場
・・・・・6回目登場
・・・・・6回目登場
・・・・・2回目登場


 解説
  1. セルC3〜C8  見積書鑑部      (入力)                            <画面デザインへ>
    C3:見積書発行先  C4:件名  C5:受渡場所  C6:納期  C7:支払条件(代金決済)  C8:見積書の有効期限です。これらの項目は直接入力となります。
  2. A列  CD(商品コード)   (入力)                                 <画面デザインへ>
    商品テーブルに該当する商品コードを入力します。CDとはCODEの略です。
  3. B、C、D列 商品名(セルを結合)   (非入力)                         <画面デザインへ>
    A列で入力した値を検索キーとして商品テーブルから商品名を引用します。 
    関数式  =IF(A11="","",VLOOKUP(A11,商品,2,FALSE))
    • 上記で示した関数式は、商品名セルB11に定義したものです。論理式 A11="" 商品コードが入力されたか否かの判定式です。真の時は "" : 空白、偽の時はVLOOKUP関数により商品テーブルの範囲から該当する位置の2列目(商品名)の値を代入します。VLOOKUPの引数で範囲を「商品」としています。商品テーブルの範囲に名前の定義を行いました。その定義方法は下述する「おまけ」を参考ください。
  4. E列 数量    (入力)                                         <画面デザインへ>
    見積もる数量を入力します。
  5. F列:販売単価 と H列:原価単価  (非入力)                          <画面デザインへ>
    3.商品名と同様ですので説明を省略します。
  6. G列:販売金額  I列:原価金額   (非入力)                           <画面デザインへ>
    販売金額と原価金額を計算します。
    関数式  =IF(E11<>"",E11*F11,"")
    • 数量 セルE11 に入力されている時に数量×販売単価を計算し金額を求めます。原価金額も同様です。
  7. J列 利益   (非入力)                                        <画面デザインへ>
    販売金額から原価金額を計算し利益を求めます。
    関数式  =IF(AND(G11="",I11=""),"",G11-I11)
    • AND(G11="",I11="") は、販売金額と原価金額のセルが無記入の時を判断します。数式全体の意味は販売金額と原価金額のうちいずれかが無記入の時は "" 空白を返し以外は「販売金額−原価金額」を計算し代入します。
  8. セルB33 備考   (入力)                                      <画面デザインへ>
    備考を入力します。
  9. 操作ボタン
    見積データの入力を完了したらこの印刷ボタンをクリックします。
    このボタンの機能は、見積書の印刷と見積番号のカウントアップ更新を行います。
    VBAを使用していますが印刷後の入力明細の初期化等を組み込んでいません。
    活用する皆さんで機能を考案し追加してください。  ^^
  10. その他
    見積書の作成日は、TODAY関数でセットしています。


 おまけ
関数の引数「検索範囲」を名前で定義する方法
QAにも何度か登場させた名前の定義。今回は、商品テーブルの範囲に商品と名前を付してVLOOKUPの検索範囲としています。商品が増えた場合は、VLOOKUP関数に影響せず、名前の定義の範囲を広げることで解決します。

 メニュー「挿入」 → 「名前」 → 「定義」 を選択します。

以上


○「匠への技」VOL-12で作成したExcelブックのダウンロード

  演習できるExcelブックを用意しています。下図のダウンロードボタンをクリックして下さい。

     Excel Book Name: takumivol-012.xls  Download file: takumivol-012.lhz