【最新】Excelマクロでテーブルを操作して見積を作成｜コピーして利用可能なVBAコード掲載
こんにちは、てつおです。今回はある商品表に記入された商品の数量に応じて、自動で見積を作成するようなマクロを作りたいと思います。どんな場面でもこのマクロをご利用いただくことは可能ですが、今回はわかりやすい例ということで結婚式場を想定したケーススタディを作成しました。
想定としては、結婚式場が見積を作成する際に、Excelの価格表へ必要な数量を入力してボタンを押すと自動で見積が出来るようなマクロを作って参りたいと思います。
目次
-
価格表シートと見積シートのテンプレートを事前に準備
-
テーブル操作して価格表シートから必要情報を見積シートへ転記するマクロの作成
-
Excel見積シートへ転記された情報をPDF出力するマクロの作成
1.価格表シートと見積シートのテンプレートを事前に準備
完成イメージ
まずは、完成イメージを共有します。Excelのシートを２つ準備し、１つは以下のような価格表シートを作成します。このシートに数量を入力することで、金額を算出できるイメージです。
そして、２つ目のシートは以下のような見積シートです。１つ目のシートで入力した情報を今回作成するマクロで転記して、正式な見積を作成します。
このような、価格表から必要な情報だけを抽出して別のシートへ転記するマクロはいろいろな場面で利用できるかと思いますので、ご興味のある方は是非最後までご覧になってください。
価格表シートに結婚式の費用がまとまった価格表を作成
まず初めに、結婚式にかかる費用がまとまった価格表を作成します。私自身、まだ結婚したことがないのですが、近いうち結婚しそうなので予習ということで色々と調べてみてちゃんとした価格表を作ろうと思います。
上図が実際に作成した価格表です。調べてみたところ、結婚式はかなり色々なことにお金がかかることがわかりました。。。
今回作成した価格表ですが、例えば「料理」についてはA～Cまであるので、この中から１つ選ぶ形になります。では、実際に数量を適当に入力してみましょう。もちろん、数量を入力しても小計は計算されません。そこで、数量を入力すると自動で小計が計算されるようにセルへ数式を書き込みます。また、今回は消費税は別途入力できるようにしてみました。
まず、数式を書く前にこの表をテーブル形式に変更します。A4からF29まで全体を選択し、Ctrl + Tを押してみましょう。以下のような画面が表示されましたでしょうか。
先頭行をテーブルの見出しとして使用するにチェックが入っていることを確認し、OKをクリックすると以下の様になります。
ここで、消費税（％）のセルに数値を入力すると「％」で表示されるように、D2のセルの書式を変更します。D2にカーソルを合わせて右クリックし、セルの書式設定をクリックします。以下の様な画面が出てくるので、パーセンテージを選んでOKを押すと、セルの書式が変更されます。試しに「１０」と入力してみて下さい。１０の後ろに「％」がつくはずです。
これで準備OKです。では、E5セルへ数式を入力していきます。E5セルは税抜き価格なので、単価×数量の式を書きます。E5セルにカーソルを持ってきた後、「=」を書いて、C5セルをクリックします。C5セルをクリックすると、[@単価（税抜）]と表示されるので、その後ろに×を意味する「*」を書き、そのあとにD5セルをクリックします。すると、以下のような式が完成しましたでしょうか。
これでEnterを押せば完成です。
続いて、税込み価格ですが、F5でまた「=」を書いて、E5セルをクリック、「*」を書く、D2セルをクリックとすると以下のようになるかと思います。
ここでこのままEnterを押さず、Dの両サイドに「＄」をつけて、税込み計算が行われるよう（）をつけて１を足します。
これでEnterを押せば完成です。では、試しに色々な項目に数量を入力してみましょう。料理はいいものにして、花は普通にしてなど、全て入力してみると以下の様になりました。
（以降の作業は飛ばしても問題ないです。）これだと合計金額がわからないので、右下に合計金額を表示します。以下の図の様に、テーブルの下に合計を算出しておくとわかりやすいかと思います。
税抜の方で数式を書くと、税込の方も自動で集計セルに代わるんですね。知らなかった。。。
最後に、シート名を「価格表シート」に変更して、価格表シートの下準備は完了です！
見積シートに見積のテンプレートを作成
価格表シートのタブの右にある⊕を押して、シートを追加します。シート名は「見積シート」とします。このシートでは、実際にPDF出力するためのシートのテンプレートを作成しておきます。あとから具体的な数値が転記されてくるイメージですね。以下の様に、見積っぽく作成してもらえればOKです。
テーブル操作して価格表シートから必要情報を見積シートへ転記するマクロの作成
テーブル操作のイメージ
マクロを作成する前に、まずはどのような動作をするマクロを作成するのか確認したいと思います。マクロの動作としては、以下の３つの手順を想定しています。
1. 数量列にフィルターをかけて、数値が入力されている行のみ表示する
2. 上から順に行をコピーし、見積シートへ貼り付ける
今回コピーする列は、「内容」、「単価（税抜）」、「数量」、「小計（税抜）」、「小計（税込）」の5つである
3. コピーしたものを見積シートへ貼り付ける。
では、早速マクロを作成していきましょう！！
マクロの記録
マクロを作成する手順は２ステップあります。まず初めに①動作を記録する、そしてその後、②記録したマクロを編集する、といった流れです。では、早速マクロの記録から始めたいと思います。
１．開発タブから、マクロの記録をクリック
２．マクロに名前を付けて、OKをクリック
３． 空白セルのみチェックを外してOKをクリック。
４．表示されている内容をすべてコピー。
５．隣の見積シートへ移動し、値のみ貼り付け。
６．以下の様に貼り付けが完了すればOK！
７．記録終了をクリック
以上でマクロの記録が終了です。では最後に、マクロの編集に進んで参りましょう。
マクロの編集
マクロの編集を行います。Visual Basicをクリックして、記録したマクロを開きます。
おそらく、以下の図の様に標準モジュールのModule1に記録されていると思います。
そのModule1をダブルクリックすると、右側にスクリプトが表示されます。
今回は、このスクリプトを以下の様に書き換えてください。
そのままコピペでも問題ございません。
Sub 見積転記マクロ()
'フィルターをかけて、数量が入力されているデータのみコピー
With Range("テーブル1").ListObject
.Range.AutoFilter Field:=4, Criteria1:="<>"
.ListColumns("内容").DataBodyRange.Copy Worksheets("見積シート").Range("C12")
.Range.AutoFilter Field:=4
.Range.AutoFilter Field:=4, Criteria1:="<>"
.ListColumns("単価（税抜）").DataBodyRange.Copy Worksheets("見積シート").Range("D12")
.Range.AutoFilter Field:=4
.Range.AutoFilter Field:=4, Criteria1:="<>"
.ListColumns("数量").DataBodyRange.Copy Worksheets("見積シート").Range("E12")
.Range.AutoFilter Field:=4
.Range.AutoFilter Field:=4, Criteria1:="<>"
.ListColumns("小計（税抜）").DataBodyRange.Copy Worksheets("見積シート").Range("F12")
.Range.AutoFilter Field:=4
.Range.AutoFilter Field:=4, Criteria1:="<>"
.ListColumns("小計（税込）").DataBodyRange.Copy Worksheets("見積シート").Range("G12")
.Range.AutoFilter Field:=4
End With
'概算見積書のデータ入力部分の背景色を白に変更し、格子の枠線を加え、外枠は太くする
Sheets("見積シート").Select
Range("B12:H35").Select
With Selection.Interior
.PatternThemeColor = xlThemeColorAccent1
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0.799981688894314
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub
スクリプトの中身は、上側がデータをコピーする領域で、下側がセルの色や枠線など、見た目を良くするためのスクリプトです。（つまり、データのコピーだけでよければ上側だけで問題ございません。）
これでマクロを実行すると以下のようにデータがコピーできるかと思います。
これで見積は完成です。これを印刷すれば早速利用できるかと思います。
最後に、PDF出力についてですが、これは別途まとめましたので以下の記事をご覧ください。
【PDF出力マクロ】Excelで作成・集計したデータを範囲指定してPDF出力する方法（全コード掲載）
そのほかのExcel-VBAに関する記事も、例えばExcelマクロでOutlookのメールを送信者（差出人）指定して送る方法などもございますので是非ご覧ください。
以上