Excelでvlookup関数とVBA(Visual Basic for Applications)を使って差し込み印刷
例として,名簿から送り状を作ります.
手順1 名簿を作る.(データテーブル,データベース)
①項目(列)を番号,名前,ふりがな,生年月日,住所1,住所2,住所3,郵便番号,電話番号として,10人程度の名簿を作成します.
②このSheetに名前を付けます.ここでは"データ"としました.
③名簿データの範囲(例A1:I11)に"住所録"と名前をつけます.
手順2 プリント書式(送り状)を作る
①B3のセルに入力した番号を検索データ(検索ワード)として,VLOOKUP関数を使って送り状を作ります.
②VLOOKUP関数のパラメータは次の通りです.
VLOOKUP(パラメータ1,パラメータ2,パラメータ3,パラメータ4,パラメータ5)
パラメータ1:検索するデータの値
パラメータ2:検索するデータ範囲
パラメータ3:出力する列番号
パラメータ4:(省略可)
TRUE:完全一致または,未満の最大値の行を出力,データをソートする必要がある.
FALSE:はじめに見つかった行のデータを出力,完全一致.値がなければ#N/Aを出力.
例
=VLOOKUP(7,A1:I11,2)
④データを出したいセル(送り状上の位置)に次の関数を入力する
氏名 =VLOOKUP($B$3,データ!住所録,2)&" 様"
ふりがな =VLOOKUP($B$3,住所録,3)&" さま"
住所1 =VLOOKUP($B$3,住所録,5)&""
住所2 =VLOOKUP($B$3,住所録,6)&""
住所3 =VLOOKUP($B$3,住所録,7)&""
郵便番号 ="〒"&VLOOKUP($B$3,住所録,8)&""
電話番号 =VLOOKUP($B$3,住所録,9)&""
以上の作業で,B3セルに入力した番号を名簿から検索し,その番号に属するデータがそれぞれのセルに自動的に入力されます.(更新されないときはF9キーを押す)
⑤このSheetに"プリント書式"と名前を付けておきます.
⑥セルB1→"開始",セルB2→"終了",セルB3→"番号"と範囲セルに名前をつけます.
手順3 VBA作成
①プログラムの方針
(1)プリント書式のB3セルの値を自動で変えていき,各人の名簿データで埋めた送り状を作成します.
(2)必要なデータで埋めた送り状をプリントアウトします.
②VBAコーディング開始
メニュー → ツール(T) → マクロ(M) → Visual Basic Editer(V)
でMicrosoft Visual Basicエディタが起動されます.
③プロジェクトウィンドウのツリー構造表示から"VBAProject(差し込み印刷基本.xls)"をクリックし選択後,右クリックし,挿入(N) → 標準モジュール(M)を選びます.以上で標準モジュールwが追加されます.
④ツリー構造表示から"Module1"をクリックし,その下側入力行の(オブジェクト名)をModule1からSASHIKOMIに変更します.
⑤右側のエディタ領域に下に示す例題のようにプログラムを入力し,ファイル保存(CTRL+S)します.(プログラム中の'以降はコメント行で行末まで無視されます.)
⑥sheetプリント書式を表示させ,プリントさせたい送り状部分を選択し
メニュー → ファイル(F) → 印刷範囲(T) → 印刷範囲の設定(S)
で印刷範囲を設定します.
⑦セルB1に開始(印刷を開始したい番号)をB2に終了(印刷を終了したい番号)を入力します.
⑧メニュー → ツール(T) →マクロ(M) →マクロ(M) でマクロダイアログを表示させ,"SASHIKOMI"を選択し実行(R)ボタンをクリックします.
プログラミング入力例
Sub SASHIKOMI()
'SNO = InputBox("開始No.を入力") ' ダイアログから開始番号を入力
'ENO = InputBox("終了No.を入力") ' ダイアログから終了番号を入力
SNO = Worksheets("プリント書式").Range("開始") 'セルから開始番号取り出す
ENO = Worksheets("プリント書式").Range("終了") 'セルから番号取り出す
Worksheets("プリント書式").Range("C1") = SNO
Worksheets("プリント書式").Range("D1") = ENO
For I = SNO To ENO
Worksheets("プリント書式").Range("番号") = I
Worksheets("プリント書式").Range("D22") = Now()
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next I
End Sub