ブログトップ

カクレ理系のやぶにらみ

tamm.exblog.jp

時間のある方はお読みください。軽い気持ちで読み始めると頭が痛くなります。

Excelで繰返し行う処理を自動化する(2)行数が変化する表の自動印刷

 前回ご説明したように、Excelでは、マクロを使うことで繰り返し行う処理を自動化することができます。
 マクロを使うと作業の効率が嘘みたいにアップするので、今後もマクロの活用方法(というよりは考えかた)について書いていくことにしますが、今回は、印刷範囲がその都度異なる表を自動的に印刷する方法についてご説明します。

 なお、今回のサンプルファイルもインターネットディスクに公開しています。ファイル名は「成績通知票・成績一覧表(マクロ).xslm」としていますので、下記のリンクからご覧ください。

http://bit.ly/JmX4VJ

 
c0136904_1044677.jpg



 この図は、前回使った表に、生徒の人数を増やしたものです。このクラスでは生徒が11人いる!と思ってください。実際に、クラスによって生徒の人数が異なるのは普通ですから、その都度範囲を指定してから印刷するということをしなければなりません。しかし、それでは手間がかかるので、なんとか自動化できないかというものです。そのためには、そもそも印刷範囲が変わるとはどういうことなのかを考えてみる必要があります。

(印刷範囲が変わるとはどういうことなのか)
 上の図をもう一度ご覧ください。クラスによって生徒の人数が変わっても、印刷する範囲の先頭は変わらないことに気づきます。それでは何が変わるのかというと、生徒の人数が変わることで印刷範囲の最後が変わるわけです。
 この図では、生徒が11人いるので、印刷範囲の最後はセルE15になります。(生徒が10人の場合はセルE14が最後になります。)
 したがって、印刷範囲の最後をマクロに教えてやれば、生徒の人数が変わっても自動印刷することが可能になるわけです。

(印刷範囲の最後の情報を取得するには)
 印刷範囲は、先頭セルの行列番号と末尾のセルの行列番号の2つを使って、範囲として指定します。先頭セルは変わらず、末尾のセルだけが変わるのですから、変数を使って末尾のセルの行列番号を指定することができれば印刷が可能になるはずです。
 本稿では、末尾のセルの行列番号を取得して印刷する方法と、最終行の番号を取得して印刷する方法の2種類をご紹介することにします。


1.末尾のセルの行列番号を取得して、マクロで印刷する方法
 
(マクロ作成の前準備)
 印刷範囲の末尾のセルの行番号を求めるには、次の3つのステップで計算式を設定します。

(1)クラスの生徒の人数を数える
 上の図のセルJ3には、=COUNTA(D5:D24)というふうに、生徒の氏名が入力されている数を数えるというものです。
    
(2)(1)に基づいて、最後の生徒の行番号を計算する
 上の図では、最初の生徒が入力されているのは5行目ですから、生徒の人数+4で最終行を求めることができます。ゆえに、セルj4には、=J3+4という式が入力されています。

(3)(2)の行番号の頭に列名をつける
 セルj4で求めた最終行を用いて、その頭に列名(ここではe列)をつけます。セルj7には、="e"&J4という式が入力されています。このように、文字列は " " で囲み、式とくっつけるときには&という記号(正確には「演算子」といいます。+や-と同じ類のものです。)を使います。

 以上で準備は完了ですので、いよいよマクロのコードを作成することにします。

(新しいマクロのコードを記述するには)
 Excel2010の「開発」タブからVisual Basic をクリックして、VBAエディター(Microsoft Visual Basic for Application )を開きます。Excel2003以前では、「ツール」メニューの「マクロ」から開くことができます。
 VBAエディターの左側のウィンドウから、「標準モジュール」の下位にある「Module1」をクリックして、「表示」メニューから「コード」を選択します。
 そうすると空白のウィンドウが表示されるので、そこに下記のコードを入力していきます。入力が面倒くさい人は、下記のコード(赤い文字の部分)をコピペしてください。

【マクロの記述】 マクロ名「成績表印刷2」


Sub 成績表印刷2()
'
' 成績表印刷2 Macro

Dim 最終セル As String

最終セル = Range("j7").Value

'
'印刷範囲を選択状態にします
Range("b2", 最終セル).Select

'選択した範囲を印刷します
Selection.PrintOut , , Copies:=1

End Sub



(コードの解説)
・Sub 成績表印刷2()
 Excelでは複数のマクロを記述することができるので、それぞれマクロのくくりを示す目印が必要になります。先頭の目印として、このように、Sub マクロの名称() という書き方をするように決められています。また、マクロの最後には、End Subという目印をつけることになっています。

・Dim 最終セル As String
 印刷範囲の末尾のセルは生徒の人数によって変わるので、変数を用いて行列番号をマクロに教えることにしています。Dimというのは、これから変数を定義する(変数の名前と型を決める)という定型句です。その次に変数の名前(ここでは「最終セル」というのが変数の名前になります)を記述してから、As と続けて、その変数の型を指定します。変数の型には数種類あり、ここではセルの行列番号を扱うので、文字型を示す Stringを指定しています。変数が数値であれば、前回用いたInteger(整数型)がよく使われます。他にも、後で出てきますが、Variant(何でもOK)という便利なオールマイティ型もあります。

・最終セル = Range("j7").Value
 変数「最終セル」に、セルj7の値を代入するためのコードです。Rangeでセルj7を指定して、Valueでその値を取得しています。なお、VBAでは単語と単語の間をピリオドでつなぐのが約束となっています。

・Range("b2", 最終セル).Select
 範囲を指定する際に用いるのがRangeで、それを選択するのがSelectです。範囲の指定のしかたには2種類の方法があり、通常は、("範囲の先頭セルの行列番号:範囲の最後のセルの行列番号")というように、カッコの中に文字列で範囲を指定するやり方がとられます。しかし、このコードのように、("先頭セルの行列番号","最後のセルの行列番号")と、コンマでつなぐこともでき、このやり方は状況に応じてセルの番地が変わる場合に便利です。
 なお、今回はRange().Selectとして範囲を選択しましたが、Range().Deleteとすれば範囲内の値の消去、Range().Copyとすれば範囲内の値のコピーとなります。
 このように、VBAでは用いられる用語はあらかじめ決められていて、Rangeとセットで用いられる語句も決まっています。たとえば、コードを入力しているときに、Range("b2", 最終セル).とピリオドを打ち込むとリストが表示されます。これを自動メンバ表示といい、Rangeの次に指定する語句としてあらかじめ登録されている一覧が表示されます。このリストを無視して、selectと入力すると、途中でリストもselectが反転表示されるので、Tabキーを押して確定すると打ち間違いを防ぐことができます。

・Selection.PrintOut , , Copies:=1
 選択した範囲を1部だけ印刷するコードです。Copies:=2とすれば2部印刷することができます。

・End Sub
 マクロの最後には必ずこのコードが記述されます。ですから、Sub マクロの名称()とこのEnd Subはセットで用いられると覚えた方がよいでしょう。
 End Subの入力が終わったら、VBAエディタのウィンドウの右上の×ボタンをクリックして、エディタを閉じればコードは自動的に保存されます。
 なお、Subで始まりEnd subで終了する一連のコードの塊を「プロシージャ」と呼ぶこともあります。


【印刷ボタンの配置】
 ここで登録したマクロ(プロシージャ)は、「マクロ」の一覧に「成績表印刷2」として表示されるようになります。これを選択して実行すれば、その通りの処理をしてくれます。しかし、いちいちメニューからのマクロの一覧を開くのも手間ですので、ボタンをクリックすれば印刷が実行されるようにした方が使い勝手がよくなります。

 Excel2007以降では、リボンの「挿入」タブから「図形」をクリックして、四角形を選択します。ワークシートの好きな位置でマウスをドラッグすると四角形が作図されるので、これを右クリックして「テキストの編集」を選ぶと、四角形の中に文字列を打ち込むことができます。ここでは、マクロ(プロシージャ)の名称である「成績表印刷2」と入力しておきます。
 次に、ボタンの見栄えをよくするために、四角形を再び右クリックして、「図形の書式設定」から「配置」で文字列の配置を縦横中央揃えにしておきます。
 さらに、もう一度右クリックして「マクロの登録」を選択すると、マクロの一覧が開くので、「成績表印刷2」を選択します。
 こうしておくと、マウスをこのボタンの上に持ってきたときに、ポインターの形が指先マークに変わり、そのままクリックすると登録されたマクロが実行されるようになります。
 なお、マクロの実行ボタンが印刷範囲内にあり、ボタンを印刷したくないときは、右クリックした上で「図形の書式設定」から「プロパティ」を選択し、「オブジェクトを印刷する」のチェックを外しておきます。


 印刷範囲が変化する表を、マクロを使って自動的に印刷する方法の1番目はこの通りとなります。説明は長くなりましたが、コードそのものは短く単純なものですので、その分応用も利くはずです。色々な場面で試してみてください。


2.印刷する最終行の番号を取得してマクロで印刷する方法
 いつも申し上げるように、Excelでは唯一の正解というものはありません。結果が同じになるのであれば、どのようなやり方をしても構わないのであり、むしろ複数のやり方を知っておいた方が、それだけExcelを使いこなせるようになるといえます。
 ここで、ご紹介するのは、もう一つの方法であり、最初のマクロに比べるとやや複雑になりますが、マクロを応用して使う際によく用いられる手法が使われています。

 1番目の印刷方法と、発想を変えてもう一度上の図を眺めてみると、次のことがわかります。


(1)クラスによって生徒の人数は変わりますが、もっとも生徒数が多いクラスを基準に考えると、他のクラスはそれよりも人数が少ないことになります。

(2)そこで、印刷範囲は生徒数が最大のクラス(上の図では最大が20人)のものに固定しておきます。

(3)それよりも人数が少ないクラスの場合は、表に生徒の名前が載らないことになるので、空白の行として非表示にしてしまえば問題なく印刷できることになります。

(4)そのためには、そのクラスの生徒の人数と表の最終行の番号を取得する必要があります。

(5)上の図では、セルJ3で生徒の人数を、セルJ4で最終行の番号を計算しています。

(J3の計算式) =COUNTA(D5:D24)

 氏名欄に乗っている名前をCOUNTA関数を使って数えています。

(j4の計算式) =j3+4

 最初の生徒は表の5行目に乗っているので、印刷すべき最終行は(生徒の人数+4)という式で求めることができます。

(J5の計算式) =J4+1&":24"

 表の最後の行は24行目であることはおわかりいただけると思います。したがって、この表で非表示にすべき行の範囲は、表示されている最後の生徒の行+1である16行目から表の最終行である24行目であることになります。
 つまり、16行目から24行目までを非表示にしてしまおうというわけです。


(マクロの構成)
 以上の準備ができたら、クラスごとの成績一覧表を印刷するには、最低限次の処理が行わなければなります。

(1)空白行を非表示にする
(2)印刷する(印刷範囲は最大のものに固定)
(3)非表示とした部分を再表示する

 これら一連の処理を一つのマクロ(プロシージャ)にまとめてもよいのですが、そうするとやたら長くなってしまいます。以前も申し上げましたが、コンピューターを扱うときは、細かいパーツに分けて考えるというのが鉄則ですので、これら3つの処理をそれぞれ独立したマクロとしてコードを記述することにします。

 なお、そのクラスの生徒数が最大である20人の場合は、非表示と再表示の処理は不要になり、いきなり印刷しても構いません。

 これら一連の処理の流れを図にすると下図のようになります。


c0136904_10475542.jpg




 このように、一連の処理を図にすると、どのようにマクロを構成すればよいのかがわかるようになり、ミスの予防にも役立ちます。

 以下、順番にマクロのコードについてご説明します。



(新しいマクロのコードを記述するには)
 Excel2010の「開発」タブからVisual Basic をクリックして、VBAエディター(Microsoft Visual Basic for Application) を開きます。左側のウィンドウから、「標準モジュール」の下位にある「Module1」をクリックして、「表示」メニューから「コード」を選択します。
 そうすると空白のウィンドウが表示される(すでにプロシージャが登録されている場合は、一番最後のプロシージャのEnd Subの後ろでEnterキーを押して改行します)ので、そこに下記のコードを入力していきます。入力が面倒くさい人は、下記のコード(赤い文字の部分)をコピ-&ペーストしてください。

 
【マクロ1】マクロ名「成績表印刷」
 全体の処理のスタートにあたるマクロです。ここでは、生徒の人数が20人なのか、それともそれ未満なのかによって、処理が分かれます。


Sub 成績表印刷開始()

' 成績表印刷開始 Macro

'人数が20人のときは非表示を行なわず、いきなり印刷を行います

If Range("j3").Value = 20 Then

Call 成績表印刷

'人数が20人未満のときは、非表示の処理を行ってから印刷します

Else


Call 非表示

Call 成績表印刷

Call 再表示

End If


Range("e2").Select


End Sub



(コードの解説)
・If Range("j3").Value = 20 Then
 これは、ExcelのIF関数のようなもので、指定した条件が成立するときは処理1を、そうでないときは処理2を行うというものです。コードの書き方(文法)は決まっており、下記の通りとなります。

  IF(条件)Then(処理1)else(処理2)End If

 こうしてみると、IF関数によく似ていることにお気づきのことと思います。IFで条件を指定し、その条件が成立したとき(Then)に処理1を行い、そうでないときには(else)処理2を行うというものです(これを条件分岐といいます)。IF関数と異なるのは、最後にEnd Ifと記述して、条件分岐が完了したことを明示しなければならないということです。
 本来は、一つの命令文なのですが、コードの内容によっては長くなるために、区切りやすいところで次の行に改行した方が見やすくなります。この命令文(これをステートメントといいます)の場合、IfからThenまでを1行にして、処理1を次の行に、その次の行に、elseと記述して、さらに改行して処理2を記述するのが一般的です。そして最後に、もう一度改行してEnd Ifと記述すれば完了です。

 この例では、生徒数であるセルJ3の値 Range("j3").Value が20人であったならば、直接印刷して構わない(処理1)けれども、そうでない場合は、(非表示)(印刷)(再表示)を順番に行う(処理2)ということにしています。

付記
 プロシージャの中で、If関数を使いたい(条件によって値を変化させたい)という場合は、IIF関数を使えば同じことができるようになります。


・Call 成績表印刷
・Call 非表示
・Call 再表示


 Callというのは、他のマクロ(プロシージャ)を呼び出すためのコードです。このように、一連の処理を分解してそれぞれのくくりごとにマクロとして登録しておいたほうがわかりやすくなりますし、後のメンテナンスもやりやすくなるというメリットがあります。


・Range("e2").Select
 セルE2を選択状態にするというコードです。Rangeは範囲を指定するときに用い、セルの行列番号は、このように必ず " "で囲まなければなりません。


・End Sub
 マクロ(プロシージャ)の終了を示す必須のコードです。



【マクロ2】マクロ名「非表示」

Sub 非表示()
'
' 非表示 Macro

'データのない行を印刷範囲から除外するため非表示とします
'そのため、まず除外する範囲を取得するための変数を定義します
Dim 除外範囲 As Variant

'除外する範囲を変数に代入します
除外範囲 = Range("j5").Value2

'除外する範囲を選択状態にします
Rows(除外範囲).Select

'選択した範囲を非表示にします
'シートが保護されているとマクロが正常に動作しません
Selection.EntireRow.Hidden = True

End Sub



(コードの解説)
・Dim 除外範囲 As Variant
 非表示にする範囲をExcelに教えるために、変数「除外範囲」を使います。Dimというのは、これから変数を定義する(変数の名前と型を決める)という定型句です。また、As Variant というのは数値でも文字でも構わないというオールマイティ型になります。前回は、変数の型として Integer(整数型)を用いましたが、どちらでも構いません。他によく使われる型として、String(文字列型)があります。

・除外範囲 = Range("j5").Value2
 変数「除外範囲」にセルJ5の値を代入するためのコードです。Rangeでセルj5を指定し、Value2でその値を取得しています。変数に値を代入するというのはよく使われるだけに、このような書き方をするのだと覚えておいた方がよいと思います。

・Rows(除外範囲).Select
 Rowsは行番号を指定するコードです。最初の行と最後の行を指定してその間にあるすべての行を指定することができます。ただし、1行だけ指定する場合は、"2:2"というように、始まりと終わりの行番号を同じにしておきます。
 また、連続せずに飛び飛びの行を指定する場合はRangeを使い、たとえば、Range("1:3,5:7")という書き方をします。この場合だと、1行目から3行目、4行目を抜かして5行目から7行目を指定するということになります。
 Selectは指定された範囲を選択するというコードです。

・Selection.EntireRow.Hidden = True
 Selectionというのは、直前で選択されたものを指します。また、EntireRowは選択された行を示すコードです。そしてHiddenは表示と非表示を切り替えるコードで、Hidden = True であれば非表示となりますし、Hidden = False とすれば再表示となります。

・End Sub
 マクロ(プロシージャ)の終了を現すコードです。


【マクロ3】マクロ名「成績表印刷」

Sub 成績表印刷()
'
' 成績表印刷 Macro
'
'印刷範囲を選択状態にします
Range("b2:e24").Select

'選択した範囲を印刷します
Selection.PrintOut , , Copies:=1

End Sub



(コードの解説)
・Range("b2:e24").Select
 生徒数が20人(表の大きさが最大)である場合の範囲 b2:e24を指定し、Selectで選択しています。

・Selection.PrintOut , , Copies:=1
 選択した範囲(Selection)を印刷(PrintOut)するためのコードです。Copies:=2とすれば2部印刷することになります。


【マクロ4】マクロ名「再表示」

Sub 再表示()
'
' 再表示 Macro

'先ほど非表示とした範囲を再表示します
'そのため、まず再表示する範囲を取得するための変数を定義します
Dim 除外範囲2 As Variant

'再表示する範囲を変数に代入します
除外範囲2 = Range("j5").Value2

Rows(除外範囲2).Select

'選択した範囲を再表示します
Selection.EntireRow.Hidden = False

End Sub



(コードの解説)
 マクロ「非表示」と基本的には一緒ですが、Selection.EntireRow.Hidden = Falseというところが異なります。


3.まとめ
 状況に応じて印刷範囲が異なるというのはよくあることですが、今回ご紹介したマクロを使うと、自動的に印刷することが可能になります。
 また、マクロの中で他のマクロを呼び出して処理を行うというやり方を組み合わせると、かなり複雑な操作・処理でも自動的に実行させることができるようになります。その場合、今回示したように、処理の流れを図にしておくとわかりやすくなりますし、ミスもぼうしすることができます。
by t_am | 2012-04-29 10:52 | Excel のあの手この手