見積作成、正直めんどくさいと思ってしまいませんか？作業自体は難しくない単価見積でも、毎日何通も作成するとやはり負担に感じるものですよね。
(*’ω’*)σ 「もっと楽に、簡単に済ませたい」…今回はこの願いが叶った経緯のお話です。
筆者の職場は鬼のように電話が鳴り続けるので、片手間感覚で見積作成を済ませる良い方法はないのか・・・と考えた時、毎日使用している見積フォーム自体に違和感を持ちました。いつから受け継がれているのか分からない古臭いExcelフォームをじっと見て、、、
(；’ω’*)σ 「・・・こいつがいけない！」と確信。理想のフォームを作ることを決意しました。
正直なところ既製品のダウンロードも検討したのですが、どうせなら自社の業務にしっくり来るオーダーメイドなフォームが欲しいと考えて、一から自作することにしました。この記事では秒速で作成できる見積フォームの作成過程を紹介しています。高度なマクロではありませんが初心者でも分かるプロシージャなので、コピペして少し書き変えれば応用できる内容となっています。
▼ 完成したフォームのデモ映像／60秒どころか22秒でほぼ完成
▲ (*’ω’*)σ ポチっと再生してみて下さい。マウスクリックのみで作成可能なため、片手でOK！
▼ 本記事は超速見積フォーム作成の前編です
- 1通60秒で完成する見積フォーム前編
↳ 全体の構想とレイアウト・必須となる価格反映のマクロ等について解説 - 1通60秒で完成する見積フォーム後編
＞＞ https://mushroom-blog.net/excel-vba-mitsumori4/
↳ 補助的な機能に関するマクロの解説
↳ ユーザーフォームを用いた汎用性の高いカスタマイズ方法
見積作成時に求める機能を書き出す｜VBAが願いを叶える
「もっと楽に、簡単に」を実現するための具体的な機能を書き出してみます。
人が想像したことに技術が追い付いてくるものなので、この段階では理想を求めます。
- 片手間で簡単に作成したい
🔹他の作業と同時進行したい。
🔹毎日100本以上もの電話をとるので片手で簡単に操作できるという点は必須
↳ マウスクリックだけで作成できる仕様にする
- ミスを発生させない仕様にしたい
🔹ミスの原因は手入力が圧倒的
🔹商品名や数字を手入力しなくても作成可能にしたい
↳ 既定のリストから反映させる仕組みにする
- 秒速で完成できる仕組みにしたい
🔹目標は1通60秒以内で、直感的に作業がしやすいデザインと機能を搭載したい
🔹プルダウンでは若干クリックしにくく、リスト設定以外の内容を打ち込めない
↳ 自作ボタンにマクロを登録する形にする
- QRコードを挿入できる機能が欲しい
🔹自社サイトの商品ページに飛べるQRコードを載せて、問合せ電話を減らしたい
🔹サイトアクセスの増加も狙えて一石二鳥
↳ ワンクリックで画像挿入できる仕様にする
- 柔軟な対応のためカスタマイズ性も欲しい
🔹商品や単価の変更を容易にしたい
🔹使い勝手に合わせてレイアウトを変更したい
↳ 別シートでマクロが分からなくても編集できるシートにする
↳ 自分が使うものだけ好きに配置できるマクロボタンにする
- 後輩やアナログ派も使いやすいフォームにしたい
🔹パッと見で分かりやすく、知識が無くても使いやすくしたい
↳ 直感的にクリックしやすいデザインにする
↳ シートの式を消してしまう子がいるので極力マクロで動かすように書き込む
- 楽しく仕事ができる仕上がりになれば最高
🔹ここまできたら「名前を付けて保存」もワンクリックにしたい
🔹仕事も楽しくできる仕上がりにしたい
↳ 保存はSAVEボタンを作ってマクロを設定する
↳ この7項目が達成されれば楽しくなるに違いない！！
結論を言うと、実装できるレベルの仕上がりになりました。
（現在は他県の事業所でも使われています。笑）
スキルアップしつつ時短ツールが作成できるなんて一石二鳥ですね！
それでは解説していきます。
やりましたニャ。
見積書テンプレートのレイアウトを決める
Excelであれば既存のフォームやダウンロードしたテンプレの転用も可能です。
(*’ω’*)σ マクロのセル番号を書き変えれば、お好みのレイアウトに対応可能です。
筆者はデザインにもこだわりたかったので、一から作りました。
印刷範囲内のレイアウト｜項目と配置の決定手順
まずメインとなる印刷範囲内のレイアウトを整えていきます。既存のフォームを転用してもOKですが、セルの結合が不規則に入っていたりすると後々マクロを設定しにくいので、表計算部分はすっきりとしたレイアウトがおすすめです。
1. 縦型か横型かを決めて印刷範囲の確認をする
最初にファイル→印刷→横方向(または縦方向)→A4→狭い余白とクリック。シートに戻ると点線が引かれています。この範囲に収まるように作成していくとA4サイズ1枚に収まります。
2. 列と行の幅を調整して表計算部分を確保
あらかじめシートを方眼紙のようにしておくとレイアウトしやすいです。列幅が2～3、行の高さが15～18ぐらいがちょうどいいかと思います。（画像は列3,行16.8）任意の項目を設定して、セルの結合をした書式をオートフィルで表の下までコピーします。▶オートフィルの記事はこちら。項目のセルに色をつけたら、ちょっとそれっぽい感じになってきました。
3. 表計算部分以外を整える
宛名や社名など、見積に必要な項目で埋めていきます。このあたりもプロシージャの書き換えで合わせられるので、お好みで進めて下さい。
一通り記載した状態がこちら ↓
▼ 追加した項目と仕様一覧
- 見積番号 保存するたびにカウントアップされる（左上の数字）
- 宛先社名 ユーザーフォームの顧客リストをクリックで入力される
- FAX 宛先社名と同時に自動で入力される
- 社名ロゴ 自社ホームページからスクショで拝借
- 作成日 TODAY関数
- 担当 プルダウンリスト ▶プルダウンの記事はこちら
- 担当携帯 事務に丸投げ癖のある営業は、余白に携帯番号載せる隠しコマンド有
見積番号・宛先社名・FAXの設定については後述します。
これで印刷範囲内の造作はひとまず完了です。
続いて、印刷範囲外を作っていきます。
印刷範囲外のレイアウト｜マクロボタンとリストの準備
1. 価格リストを作成
見積入力シートとは別のシートに価格表を作成します。
良く扱う20製品・オプション4種・送料という設定で進めます。商品名を縦に並べます。
客先を横に並べて、それぞれ基本料・月額・日割の数字を入れます。
実際に見積に使用する項目・単価の一覧を作れば大丈夫です。
この中の数字を使って今回は月額レンタル見積のフォームを作ります。
販売見積のフォームについては、別の記事で書きます。
2. 価格リスト転記エリアを確保
入力シートに、先程作った価格リストの転記エリアを確保します。
製品名を価格表と同じ順番で縦並びに記載して、その隣に1顧客分の価格転記エリアを空けておきます。
3. 客先リストを作成
リストボックスに設定し、クリックで客先名などを自動入力するために作成します。
（※必須ではありません）
表示したい宛先が300件以上あったので、上司の許可を得て自社システムの顧客マスタからデータを拝借しました。これについては用意が難しい場合もあると思うので必須ではありません。
汎用性を考えて顧客CDなども入れてありますが、実際には表示したい項目だけあれば大丈夫です。
4. 自作マクロボタンを作成して配置
つやつやボタン
挿入タブ→図形→テキストボックス横書を選択して商品名を入力。
図形の書式→図形の塗りつぶしで色を塗る。
図形の効果→面取り→丸をクリック。°˖✧◝(⁰▿⁰)◜✧˖°ツヤっ！
小さい矢印押して「セルに合わせて移動やサイズ変更をしない」にチェック。
テキストボックスの配置を中央、余白はゼロにしておく。これで完成。
直感カラーボタン
塗りつぶしまではつやつやボタンと同じ。
図形の書式→枠線色選択→枠線太さ4.5pt（枠線の色がしっかり見える）
文字の効果→光彩→その他の光彩の色で色選択。最後にテキスト位置などを整えて完成。
製品の色やイメージカラーを表現して直感的に素早くクリックできる効果が狙いです。
極端な例ですがこんな感じ ↓
作成したボタンを配置
顧客ボタンと製品ボタンを必要な数だけ作ったら、印刷範囲の外側に並べていく。
それ以外に【SAVE】【CREAR】【reverse】【月数】は必ず作る。
【備考】【QR】【送料】などは必要な場合だけ作ればOK
並べる時は価格転記エリアを避ける。
表示タブ→改ページプレビューをクリックすると印刷範囲以外がグレーになるので見やすい。
ボタンをきれいに敷き詰めるのが難しい場合 (´；ω；`)ｳｩｩ
表示倍率を大きくした状態（200％程度）で位置の調整を行うと簡単。
QRコードを準備｜URLから簡単作成
見積にQRコードを記載したい場合は事前に用意しておきます。
簡単に作成できるサイトがあるので紹介します。▶こちらから
URLを入力して作成するをクリックするだけ。色やファイル形式も選べます。
顧客ボタン用マクロ｜価格表転記のプロシージャ
任意の客先の価格を価格表シートから見積作成シートに転記する簡単な内容です。
必要に応じてシート名とセル範囲を書き換えて下さい。
サンプルプロシージャ
Sub 顧客Aの価格コピペ()
Sheets(“価格表”).Select
Range(“C2:E29”).Select ‘顧客Aの価格表の範囲を選択
Selection.Copy
Sheets(“レンタル月額”).Select
Range(“AM15”).Select ‘価格表転記先の左上のセル
ActiveSheet.Paste
End Sub
このマクロを顧客Aのボタンに設定します。
顧客Aボタン右クリック→マクロの登録→顧客Aの価格コピペを選択→OKです。
これで顧客Aボタンをクリックすると、価格転記エリアに顧客Aの価格帯が入ります。
同様に、他の顧客ボタン用のマクロも設定していきます。
プロシージャの書き換えが必要な部分は2か所（赤字の部分）だけなので簡単です。
Sub 顧客Bの価格コピペ()
Sheets(“価格表”).Select
Range(“F2:H29“).Select ‘顧客Bの価格表の範囲を選択
Selection.Copy
Sheets(“レンタル月額”).Select
Range(“AM15”).Select ‘価格表転記先の左上のセル
ActiveSheet.Paste
End Sub
商品ボタン用マクロ｜製品名･単価･数量カウントアップ
製品名と単価が入力されて、クリックするごとに数量が1づつ増えて自動計算されます。
①既に同じ製品が記載されていたらカウントアップ、なければ製品名を入力
②価格転記エリアを参照して製品の単価を入力
③単価×数量×月数の計算 （月数のカウントアップは後程）
という3段階構造になっています。少し長いですがこのフォーム一番のポイントです。
サンプルプロシージャ
Sub 製品1()
Dim FoundCell As Range ‘製品のセルなのでRangeオブジェクト
Dim 空欄セル As Range ‘空欄のセルなのでRangeオブジェクト
Set FoundCell = Range(“D12:L27”).Find(“製品1”) ‘品名の列から製品1を検索
Set 空欄セル = Range(“S11:S27”).Find(“”) ‘数量の列から空欄のセルを検索
If Not FoundCell Is Nothing Then ‘製品1があればS列をカウントアップ
Range(“S” & FoundCell.Row) = Range(“S” & FoundCell.Row) + 1 ‘基本料とレンタル料
Range(“S” & FoundCell.Offset(1, 0).Row) = Range(“S” & FoundCell.Offset(1, 0).Row) + 1
ElseIf FoundCell Is Nothing Then ‘製品1が無ければ
空欄セル.Offset(0, -15) = “製品1″ ‘空欄のセルを基準にOffSetで各項目を入力
空欄セル.Offset(0, -6) = “基本料”
空欄セル.Offset(1, -6) = “レンタル料”
空欄セル.Value = “1”
空欄セル.Offset(1, 0).Value = “1”
空欄セル.Offset(0, 1).Value = “個”
空欄セル.Offset(1, 1).Value = “個”
End If
Dim i As Integer
For i = 13 To 27 ’13行目から27行目に繰り返し処理
If Cells(i, “D”) <> “” Then ‘品名セルが空欄でなければ価格表参照でW列に単価を入力
Cells(i, “W”) = Application.VLookup(Cells(i, “D”), Range(“AL17:AO100”), 2, False)
Cells(i, “W”).Offset(1, 0).Value = Application.VLookup(Cells(i, “D”), Range(“AL17:AO100”), 3, False)
End If ‘基本料と月額レンタル料の単価がW列に入る
Next
Dim j As Integer
For j = 13 To 27 ’13行目から27行目に繰り返し処理
If Cells(j, “Q”) = “” Then ‘月数の列Qが空欄なら
Cells(j, “Z”) = Cells(j, “S”) * Cells(j, “W”) ‘金額の列Zは、数量の列S×単価の列W
Else ‘そうじゃなければ
Cells(j, “Z”) = Cells(j, “S”) * Cells(j, “W”) * Cells(j, “Q”)
End If ‘金額の列Zは、数量の列S×単価の列W×月数の列Q
Next
End Sub
冒頭に動画がありますが、クリックで数量がアップします。
私の実務では数量5以下の見積がほとんどなので1ずつカウントアップにしてあります。
Lotが100とかなら初期値100、加算値100にすれば転用できます。
・・・問題は中途半端な数の時ですが (´・ω・`)うー
日割り計算の見積で使っているプロシージャが使えるので、そちらの記事でご紹介します。
※近日リンク予定
Sub 製品2()
Dim FoundCell As Range ‘製品のセルなのでRangeオブジェクト
Dim 空欄セル As Range ‘空欄のセルなのでRangeオブジェクト
Set FoundCell = Range(“D12:L27”).Find(“製品2“) ‘品名の列から製品1を検索
Set 空欄セル = Range(“S11:S27”).Find(“”) ‘数量の列から空欄のセルを検索
If Not FoundCell Is Nothing Then ‘製品1があればS列をカウントアップ
Range(“S” & FoundCell.Row) = Range(“S” & FoundCell.Row) + 1 ‘基本料とレンタル料
Range(“S” & FoundCell.Offset(1, 0).Row) = Range(“S” & FoundCell.Offset(1, 0).Row) + 1
ElseIf FoundCell Is Nothing Then ‘製品1が無ければ
空欄セル.Offset(0, -15) = “製品2″ ‘空欄のセルを基準にOffSetで各項目を入力
空欄セル.Offset(0, -6) = “基本料”
空欄セル.Offset(1, -6) = “レンタル料”
空欄セル.Value = “1”
空欄セル.Offset(1, 0).Value = “1”
空欄セル.Offset(0, 1).Value = “個”
空欄セル.Offset(1, 1).Value = “個”
End If
第一段階部分の製品名3か所（赤字の部分）を書き変えればOKです。
必要であれば単位を個から枚にするとか、Lot品なら初期値と加算値の変更ぐらいです。
編集→置換→カレントプロシージャにチェック→全て置換でも書き換えが可能です。
量産したらボタンに設定するのをお忘れなく！
見積フォーム作成って思ったより時間かかるじゃん・・・
と思ったかもしれませんが、一度作れば本当に楽になります。
・とにかく早くて簡単になった
・手入力が少ないのでミスに対するメンタル負荷が軽減
・顧客満足度の向上
・即対応ができるので受注決定率が上がった
電話で内容聞きながら作成して、速攻メールで飛ばすなんていうことも可能です。
もうこれで70％完成です。
少し長くなったので続きは後編で書きます。
▶記事はこちらから