エクセルで、データリストからデータを読み込んで、伝票みたいなレイアウトで印刷したいって事ありますよね。
つまり読んで印刷、読んで印刷というように、一定の動作を繰り返す時に頼れる機能、それがマクロ！ＶＢＡ！プログラムバンザイ！
なわけですが、今回はマクロを使わずにやります。
VBAを使わないという価値
マクロ、しかもそれほど難しくない内容で済むのだから、マクロでいいじゃん。
と思われるかもしれませんが、関数のみで組み立てるメリットってあるんです。
思いつく限りでもこれくらい。
- 1ページずつの印刷じゃない
- VBA苦手な人でも仕組みが追える
- レイアウト変更に対応しやすい
- マクロ禁止環境でも使える
特に1番目の、1ページずつの印刷じゃないというは、メリットを実感しやすいですね。
マクロでの印刷って大抵、1ページ印刷を繰り返すじゃないですか。
あれって、他の人の印刷が割り込んできたり、プリンタトラブルとか印刷内容ミスった時のジョブ止めが面倒だったりしますよね。
今回紹介する関数だけで組み立てる場合は10ページまとめて印刷とかになるので、この不便さが解決できます。
実践
とにかくやってみましょう。
例題は当記事のサムネイルにもなっている、リストからデータを読んで請求書を印刷する仕組みを作ります。
レイアウトを用意してとりあえず参照式を入れる
まずは完成形をイメージします。
単純に参照式を入れるだけなので、こんな感じにしました。
（仕組みがわかってて、レイアウトもすでに整ってるなら飛ばしていい工程です）
ｎ件目のデータを参照するよう小細工
続いて、参照式をいじります。
氏名のセル：=INDIRECT("データリスト!A"&ROUNDUP(ROW()/999,0)+1)&" 様"
金額のセル：=INDIRECT("データリスト!B"&ROUNDUP(ROW()/999,0)+1)
期限のセル：=INDIRECT("データリスト!C"&ROUNDUP(ROW()/999,0)+1)
で、999の部分を後で変更します。
999を1枚の行数分にする
請求書1枚が何行分かを調べ、先ほどの式の999を変更します。
今回は12行なので999を12に変更します。
氏名のセル：=INDIRECT("データリスト!A"&ROUNDUP(ROW()/12,0)+1)&" 様"
金額のセル：=INDIRECT("データリスト!B"&ROUNDUP(ROW()/12,0)+1)
期限のセル：=INDIRECT("データリスト!C"&ROUNDUP(ROW()/12,0)+1)
今回作っている請求書、文字がある最終行は11行目ですが、このあとコピペして2枚目、3枚目を作っていくので、余白として12行目までを範囲にしています。
コピーして2枚目の請求書を用意
1枚目が完成したらもう簡単、必要な分だけコピーして貼り付けしましょう。
このように、1枚目をコピーしてすぐ下に貼り付けると、2件目のデータを読み込んだ2枚目が作られました。
後は同じ要領で、3枚目、4枚目と、必要な分だけ増やしていきましょう。
注意点
この方法を使う時、注意する事があります。
- 1枚あたりの行数は固定する事
- コピペの際に隙間を空けない事
要するに、1枚目は12行で作ったけど、2枚目は少しレイアウト変えて13行になっちゃったーはダメ。
そして、1枚を12行で作ったけど、印刷の都合で余白欲しいから13行目飛ばして14行目に貼り付けちゃえーもダメ。
とにかく等間隔にコピー、貼り付けして使ってくださいねっていう事。
守らないと、ｎ件目をデータを参照するっていう式の部分が機能してくれません。
解説
今回紹介した式、なぜｎ件目のデータを参照できているのか、実際の数値を代入して検証してみましょう。
まず、使った式はコチラでしたね。
=INDIRECT("データリスト!A"&ROUNDUP(ROW()/12,0)+1)
1枚目の場合、氏名のセルのROW()は4でしたので、解いていくと・・・
ROUNDUP(ROW()/12,0)+1
=ROUNDUP(4/12,0)+1
=ROUNDUP(0.333,0)+1
=1+1
つまり、=INDIRECT("データリスト!A2")となって、1件目の氏名を参照するようになりました。
ということはROW()が12以下ならこの式の答えは2になるので、金額のセルも期限のセルも、ROW()の結果は違うのに、ちゃんと1件目のデータを参照しているんです。
では続いて、2枚目になるとどうなるのか検証してみましょう。
２枚目の場合、氏名のセルのROW()は16でしたので、解いていくと・・・
ROUNDUP(ROW()/12,0)+1
=ROUNDUP(16/12,0)+1
=ROUNDUP(1.333,0)+1
=2+1
つまり、=INDIRECT("データリスト!A3")となって、2件目の氏名を参照するようになりました。
ちなみに、ROW()が13～24の間はちゃんとA3セルを参照するようになってます。
あとお気づきかもしれませんが、データリストにヘッダーが無い場合は、+1を無くしてあげれば、参照先が1つ上になって解決できます。
応用編
ひたすら縦にコピペしていく仕組みを作りましたが、この請求書、印刷すると右側が余白多くてもったいないですよね。
という事で、2枚1セット版も作ってみましょう。
直し方は簡単で、まずROUNDUP関数の後に×2を追加します。
すると2件目のデータを参照するようになるので、そのまま右にコピーします。
そして左側の請求書から+1を消します。（右側の請求書には+1を残します）
準備が整いました。
後はコピーして、必要な分だけ下に貼り付けていきましょう。
あとがき
わかってる。
わかってるんです、こういう記事って書いても辿り着く人は少ないって。
でもね、声を大にして言いたい。
コレ、なんとなくでも覚えておくと、急に刺さる事がある。
コメント