「いろんなサイトからネタを探してきて、勉強する」のがこのサイトの基本的な編集方針(^^;)なんですが
身内から、「Excelでこんなこと出来ないか？」という相談がありましたので、今回はそのネタで行きます。
いわく、 「名簿をExcelで作ってるんだけど、印刷したときに紙がもったいないので何とかならないか？」との事。
- 名簿はExcelで管理している
- 追加されたり削除されたりする
- 印刷するときには一定の決まりで並べ替えなければならない
どうもこんな感じのデータらしいです。
もちろんデータはダミーです。
良くある苗字と良くある名前でランダムに作りました。
こんな感じのデータが200件くらいあり、追加されたり削除されたりするらしいです。
んで、印刷するときには、
- まず、コードの5～6桁目の値で並べて、
- つぎに、コード全体の値で並べて
で、印刷する必要があるそうです。ん、じゃとりあえず並べ替えを。まず5～6桁目の値で並べるために、そこの桁を抜き出します。 C2のセルの計算式に
=MID(A2,5,2)
と入れて、最後の行までオートフィルで入れます。
ん、いい感じ(^^)
この値とA列の値を使って、データをソートします。
並べ替えるデータを選択して、 データ→並べ替え→ 
で、並べ替えの設定で、 1番目に優先されるキーに「列C」、2番目に優先されるキーに「コード」、それぞれ昇順で設定します。
ん、並び変わりました(^^) んでこれを印刷、と。プレビューで確認すると、
(^^;)まぁ、当然こうなります。全部で200件あるので、今回の場合4ページにもなります。
文字を小さくしたり、行の高さを低くすればもう少し１ページに入る量は増えますが、あんまり小さかったり、詰め詰めだと見難いし。。。
かといってこのまま印刷しても、紙がもったいないし、、、どうにかならないか？ってことらしいです。
Wordとかならページ設定で「段数」という指定が出来て、段組印刷が出来るんですけど、 Excelには今のところそういう機能はないらしいです。
んじゃ、今はどうやってるのか？と聞くと、
「並べ替えたデータを印刷用のシートに手動でコピーして、自分で段組を作ってる」
との事。んじゃ、とりあえず、手作業でやってみます。
印刷用のシートはSheet2とします。上のマージンや項目名を印刷することを考えて、データは２行目から表示するようにします。１ページに入る行数は、40行くらいにしておきます。
Sheet1のA2からB41までを選択し（C列はただ並べ替えのために使っているだけなので、印刷はしません）
それをSheet2のA2にコピーします。
次の40件、Sheet1のA42～B81まで選択して、 Sheet2のD2へコピーします。
１列開けたのは、印刷したときに段の間のスペースを調整できるようにするためです。
同じように、81件目以降もやっていきます。
横を5段以上にすると入りきらないので、最後の40件はA44から下に入っています。
ん、できました。後は印刷プレビューを確認しながらセル幅やセルの高さを調整したりしていけば、
ん、これは１枚目です。１段に40人ですから、１枚で40×4の160人分出ています。
200人分を２枚に収めることが出来ました(^^)。
しかし、激しくめんどくさいですね(^^;)
これだとデータが追加や削除されるたびに、印刷用のシートを１から作り直さなきゃいけないです。
んじゃ、この印刷用のシートを作るところをVBAを使って自動化できれば、少しは楽になるかな？
んでは、マクロを書いていきます。
「ツール」→「マクロ」→「Visual Basic Editor」でプロジェクトの中の「Sheet1」をダブルクリック、するとコードを記述するウィンドウが表示されます。
まずは何はともあれ、いつもやってるように全員のデータをイミディエイトウィンドウに表示させてみます。
イミディエイトウィンドウが表示されていない場合は、「表示」→「イミディエイトウィンドウ」で表示させます。
では、以下のプログラムをコードを記述する欄に書きます。
Sub test()
Dim lastgyou As Integer
Dim i As Integer
'Sheet1を選択します
Sheets("Sheet1").Select
'最後の人を求めます
ActiveSheet.Range("A2").End(xlDown).Select
lastgyou = ActiveCell.Row
'全員をイミディエイトウィンドウに表示します
For i = 2 To lastgyou
Debug.Print (ActiveSheet.Cells(i, 1).Value);
Debug.Print (ActiveSheet.Cells(i, 2).Value)
Next
End Sub
実行してみます。
ん、全員出ました(^^) これらの値をSheet2に書き出していけばよいわけですね。
今回は、最初の段はA列、B列の２行目からスタートして、A41、B41までの40行表示し、
次の段は、D列、E列の2行目からスタートして、D41、E41までの40行表示し、
３段目は、G列、H列の2行目からスタートして、G41、H41までの40行表示し、
４段目は、J列、K列の2行目からスタートして、J41、K41までの40行表示し、
５段目は、A列、B列の４４行目からスタートして、A83、B83までの40行を表示します。
さて、Sheet1を１行ずつ抜き出していって、それをSheet2のどこに表示すればよいか、それを求めなければなりません。
今回は、数式を使わずに、変数を用いて、どこに表示するかを求めていきたいと思います。
まず、Sheet1のコードの値をSheet2に表示する場所を 列をx、行をyとします。
1件目のデータは、x=1 (A列、つまり、左から１番目の列)、y=2 (2行目からスタートなので)となります。
2件目のデータは、x=1,y=3 3件目のデータは、x=1,y=4 ・・・ 39件目のデータは、x=1,y=40 40件目のデータは、x=1,y=41 です。
んでは、41件目は？ここで段が変わりますので、 41件目のデータは、x=4（D列、つまり左から4番目の列）,y=2 となります。
42件目のデータは、x=4,y=3 42件目のデータは、x=4,y=4 ・・・ 80件目のデータは、x=4,y=41です。
81件目でまた段が変わります。 81件目のデータは、x=7,y=2です。・・・ 121件目でも、段が変わります。
121件目のデータは、x=11,y=2です。・・・ 160件目のデータは、x=11,y=41です。
さて、161件目ですが、今回は横を４段までとしました。なので、これ以上、xの位置は増えません。
次のページの先頭に持っていきます。
161件目は、x=1,y=44となります。以下、200件目まで進み、最終的にx=1,y=83となります。
まとめると、こんな感じになります。
- x=1,y=2からスタート。
- 値を表示する。
- yを1つずつ足していって、41個目から、次の段へ進む。
- 次の段に進むと、xは3つ増える。ｙは上に40個戻る。
- 段が5つ目、9つ目、13つ目・・・（今回は5段までしかなりませんが）になったら、xを1に戻して、yを次のページの先頭に
なんか、複雑に見えますが、手作業でやるのと考え方に差はありません。プログラムにしてみると、
Sub test()
Dim lastgyou As Integer
Dim i As Integer
Dim dan As Integer
Dim x As Integer
Dim y As Integer
Dim code As String
Dim namae As String
'Sheet2を空にします
Sheets("Sheet2").Cells.Clear
'Sheet1を選択します
Sheets("Sheet1").Select
'最後の人を求めます
ActiveSheet.Range("A2").End(xlDown).Select
lastgyou = ActiveCell.Row
'段、横、縦の初期値を入れます
dan = 1
x = 1
y = 2
For i = 2 To lastgyou
'Sheet1を選択します
Sheets("Sheet1").Select
'code,namaeの変数に、コピーする値を代入します
code = ActiveSheet.Cells(i, 1).Value
namae = ActiveSheet.Cells(i, 2).Value
'Sheet2を選択します
Sheets("Sheet2").Select
'今の位置に値を代入します
ActiveSheet.Cells(y, x).Value = code
ActiveSheet.Cells(y, x + 1).Value = namae
'次の表示する位置を求めます
'まず、yを1つ増やします。
y = y + 1
'もし、yが42行目、84行目、126行目、・・・の場合
If y Mod 42 = 0 Then
'この場合、段が変わります
dan = dan + 1
'もし、段が、5段目、9段目、13段目、・・・の場合
If (dan - 1) Mod 4 = 0 Then
'この場合、ページが変わります。
x = 1
y = y + 2
Else
'そうでなければ、ページは変わりません。
x = x + 3
y = y - 40
End If
End If
Next
End Sub
やっぱり複雑ですかね？(^^;)
danという変数で、今何段目を表示しているかを記録しています。
yの値が、42で割り切れるとき、段が変わります。また、段数から1を引いた値が4で割り切れるとき、ページが変わります。
段が変わる際、ページが変わる場合は、yの値を2増やし、ページが変わらない場合は、yの値を40減らします。
では、実行してみます。さっき手作業で入れた、Sheet2のセルの中身を全部消して、プログラムを実行すると、、
ん、いい感じ(^^)
後は、印刷の調整は手作業でやってください。また、今回のプログラムは横4段、縦40行としていますが、これを変えるのであれば、プログラムに手を入れれば可能です。最後の青い部分の、modで余りを求める時の数字や、yに足す値を変えればOKです。
ここにアップしておきます→vbastudy_14.xls
