私の受講生やブログは、それぞれ、専門分野のスキルを伸ばしたいと思っている人に向けて発信しているのですが、そのエッセンスとしてコンピュータやExcelがあればいいなと思っています。
もちろんExcelを専門としたい人にとっても役立つ情報を発信していきたいのですが、それだけじゃなくて、一般職全体の大半を占める、「経理」「営業」「売上データ分析」「仕事のフローを考える」「マネジメント」など、パソコンはあくまでツールとして使う人に情報発信しています。
当然、パソコンのスキルが上がれば、いろいろなことができるようになるのですが、その人それぞれの最終的な仕上がりがパソコンのプロフェッショナルになるのではなく、それぞれの得意なことのプロフェッショナルになることのお手伝いがパソコンを通してできるといいと思っているのですね。
そんな中で、Excelというソフトを考えた時に、VBAまでの完全な知識が必要になるかというとそういうことではないと思っています。
VBAとなるとプログラムになってしまうので、Excel標準機能より一歩進まなければならないと思っています。対してマクロの記録までは並べ替えのようなExcelの標準機能だと私は考えていて、一般職の方もExcelはここまではわかりやすいし、知っておいてほしいと思っている部分です。
そこで、マクロの記録までのテクニックでここまでできるということを紹介してみたいと思います。
マクロの記録
マクロの記録とは、人間の行った操作手順を記録してくれ、その手順をいくら複雑でも人間が１操作するだけで実行してくれるようになる機能です。
それ以上でもそれ以下でもありません。
副産物として、VBAという、あまり完全ではないプログラムの形で記録されますが、それは意識しなくても全然操作できます。
これ、今回の一番大事なところです。
実際のマクロを記録したい操作手順
今回、次のような一覧表があります。
毎月、この中から、先月分の請求書を納品先ごとに発行したいのですが、そのために、次のようなシートがあります。
納入年月としてセルAB3に今日の日付がTODAY関数で入っていて、その日付の前の月の1日がセルAA7に、前の月の月末の日付がセルAB7に入っていて、納入先抽出条件を構成しています。
セルAD7には、セルAB3に入力された取引先を参照する式、セルAE7には前の月の1日を求めているセルAA7を参照する式、セルAF7には月末を求めているセルAB7を参照する計算式が入力されていて、請求内容抽出条件を構成しています。
セルAB2をクリックすると、その年月に納品した納品先から選択できるようにします。
そのためには、一覧表から該当の期間の取引をした納入先の一覧を重複せずにリスト化する必要があります。
手順は、一覧表のH列から、請求書のAA6からAB7の納入先抽出条件に当てはまるものを、重複なくセルAA9以降にリストします。
使う機能は、データタブの中の詳細設定フィルターです。これはオートフィルターと違い、元データ、条件、抽出先をセルやセル範囲で指定でき、なおかつ重複も削除してくれます。
詳細設定をクリックすると次のようなダイアログボックスが表示されます。
詳細設定フィルターはオートフィルターが登場する前にあったフィルター機能です。かなり歴史は古いのですね。
条件を表範囲で指定できるのでかなり複雑な条件まで設定できます。
DCOUNT関数などのデータベース関数でも同様に条件を指定しますので、データベース関数を使ったことがある方はそのようにイメージできるかもしれません。
では、この詳細設定フィルターの使い方を紹介していきます。
今回、シート間で設定しなければならないので、ちょっと面倒です。シートに跨がなければ結構簡単な機能です。
まず、抽出先のシートを選択しておきます。そしてAA9とその下のAA10のセルを範囲選択しておきます。シート間に跨る詳細設定フィルターは抽出先のシートのタイトル行がある範囲を選択しておかないといけないのです。
この状態で、データタブの中の詳細設定をクリックします。
こんなメッセージが出てしまうのですが、これはそのままOKします。
すると、フィルターオプションの設定の画面が出てくるので、リスト範囲は、売上データのH列からK列まで全部、検索条件範囲はAA6からAA7の範囲、抽出範囲はセルAA9を指定し、重複するレコードはすべて無視するのチェックを入れてOKボタンをクリックします。
該当期間内の納品先の一覧が重複なくリストされます。
セルAB2には次のような入力規則を設定します。
セルAB2がリストから選択できるようになりました。
来月になったら来月にも同じ作業をしなければならないのですが、いちいち面倒なので、ボタンを押したら一発で詳細設定フィルターの動作をするようにします。
この動作を設定するのに、マクロの記録をします。
マクロの記録をしてみよう
表示タブの中のマクロの中、マクロの記録をします。
マクロ名に「納入先抽出」と設定し、OKボタンをクリックします。
請求書シートのAA9からAA10までを選択してから、詳細設定フィルターの範囲を設定してOKするところまでの操作をします。
表示タブの中のマクロの記録終了をクリックします。
これで、フィルターをマクロに設定しました。
マクロの呼び出しをワンボタンで呼び出せるように、セルAC２くらいに「納入先抽出」と書いたテキストボックスを作成します。
このテキストボックスを右クリックして、マクロの登録をします。
「納入先抽出」を選択してOKボタンをクリックします。
これで、納入先抽出ボタンをクリックしただけで、先月に取引した取引先の一覧を表示できるようになりました。
では、今度はAC2で指定した取引先名に対する請求情報リストを作成します。
売上データのH列からK列のうち、請求書シートのAD6からAF7の範囲の条件に当てはまるものをAC9からAF９の範囲に出力するようにします。さっきと違って重複データがあっても削除はしません。
この動作を「請求内容抽出」という名前で記録して、セルAE2くらいに同名のボタンを作成し登録します。
アウトプットする画面の作成
請求書はレイアウトしやすくするために、いわゆるExcel方眼紙を使っています。
これも完璧なレイアウトってできないんですよね。やっぱりレイアウトする書類はWordには勝てない。
仕方ないので、Excelでやるのですが。
次のように計算式と書式を設定します。
あとは印刷するなり、PDFを作成するなりなのですが、それも「請求内容抽出」のマクロに組み込んでしまってもよかったのかもしれません。
繰り返し動作
上記の例では、請求先一件一件を手作業で変更し、請求先の数だけその作業を繰り返す必要がありました。
でも、その繰り返し作業って、毎回必ず行うものですよね。
そこで気になるのは、ここまで自動化されると、人間の操作ミスが油断によって出てしまうことがとてもありがちです。
一回の操作で、全部の請求先に向けた請求書が作成されるといいですよね。
PDF化も含めてそれも自動化してしまいたいと思います。
が、、、、
実はマクロの記録だけではできないことが２つあって、一つは、結果に対して行動を起こすかどうかの条件分岐です。もう一つが「繰り返し」なのです。
これは、VBAで行うことになります。
はじめのお話と趣旨が変わってしまいます。ごめんなさい。
でも、あまりハードルが上がらないように解説していきます。
まず、次の内容を「全請求書作成」という名前のマクロに記録します。マクロ記録中のマクロの実行は、作成したボタンではマクロ開始できないので、表示タブの中のマクロ、マクロの表示でそれぞれ実行してください。
「納入先抽出」マクロを実行、「請求内容抽出」マクロを実行、「1月日比谷文具」という名前でPDFファイルとしてエクスポート。
記録したら、繰り返し自動化するための計算式をシートに埋め込んでいきます。
セルAA1は納品先の個数を求めています。これが繰り返しの回数になります。
セルAB1には数値の1を入力しておいているのですが、これが現在処理する請求先の番号になります。
セルAC1には、PDFファイル名を求める計算式を入力しています。
セルAB2は、請求先を選択するようになっていましたが、セルAB１に対する請求先を参照できるINDEX関数を設定しています。
この計算式を入れたことで、セルAB1の数値を変えるだけで請求先が変わり、条件やPDFファイル名の情報が自動的に求められるようになっています。
では、ここからVBAを使っていきます。
表示の中のマクロの中のマクロの表示をクリックします。
こんな画面が表示されるので、「全請求書作成」を指定して、編集します。
こんな画面が出てきます。
この「Application.Run “Book5!納入先抽出”」「Application.Run “Book5!請求内容抽出”」と書いてあるのがVBAです。
なんとなくですけど、アプリケーションラン・・・自分で作ったアプリケーション＝マクロが走り出すと読めないですか？
このような形でマクロが記録されています。
で、今回繰り返すのは、マクロ「請求内容抽出」の実行から、マクロ終了である「End Sub」の直前までです。
繰り返しは、VBAに次のように書いていきます。
For i=1 to 繰り返し回数
繰り返し内容
Next
この繰り返し内容に繰り返す内容のVBAを書いていきます。
そして繰り返し回数は、セルAA1に記載されています。
セルAA1の値は、
Range(“AA1”).Value
でVBAで使えます。
上記の繰り返しのVBAで現在の繰り返し回数はiという文字であらわされています。
このiの何回目かという情報はセルAB1に書き込まなければなりません。その方法は
Range(“AB1”).Value=i
でVBAで書きこめます。
あと、PDFファイル名はセルAC1に入っていますが、これもVBA上で使うために、pdffileという名前で管理しておきたいと思います。
pdffile= Range(“AC1”).Value
そしてVBAに記録されたPDF作成時のPDFファイル名の部分をpdffileに置き換えます。その前の””を閉じ、&で結合するようにしましょう。
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
“C:\〇〇\〇〇\〇〇\ドキュメント\” & pdffile, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
ということで、次のようになります。赤丸の部分が手作業で変更したところです。
ここまで作成したら、セルAE4くらいに「全請求書作成」マクロ起動ボタンを作成するといいのではないかと思います。
ここまでの解説、いかがでしたでしょうか。
Excelの計算式を使って、マクロの記録をしてほんの少しだけVBAをいじるだけでかなり高度なツールが作れるということがお分かりになっていただけたでしょうか。
かなり丁寧な解説をしてきたと思います。正直、ここまで書くとは思っていなかったし、今振り返ると割りと大変でした。でもその分伝わればいいなあと思っています。
あと一つだけ、解説するものがあるので、最後まで読んでみてくださいね。
VBAの変数
上記の例だと、VBAでは、現在の繰り返し回数をiという文字で管理したり、PDFファイル名をpdffileという文字で管理したりしました。
この、何かを管理する文字のことを変数といいます。
変わっていく数ですね。あ、PDFファイル名のように数じゃないもの、文字とかも変数と言います。変ですよね。でもそう呼ぶのだから仕方ないです。
この変数ですが、今回やってはいなかったのですが、宣言というものを本当はしなければならないのです。
今回は、やらなくてもちゃんと動くものでしたが、場合によっては動かない場合もあるので、しっかりちゃんと宣言したほうが無難です。
変数の宣言はこちらでこの前、やらなきゃいけない理由を書きましたが、結構な高度な内容なので慣れてから読んでもいいと思います。
動作がおかしくしたくない時は宣言しようということを強くお勧めしておきます。ただ、今回は必ず宣言する明確な理由はないので、難しければしなくてもいいのかなって思います。
あとで誰かに宣言しなきゃダメだよって怒られるかもしれませんが、「だってちゃんと動くし、入れる理由ないし、変数を宣言してトラブルよりもいいじゃん」で済んじゃう話と思っています。
VBAの難しい要素のおかげで自動化できないよりは、簡単に考えて自動化するものを作って楽になった方がいいと思うのです。
変数の宣言は下の赤で囲んだところのようにします。
Dimではじめて変数名を書いてAsの後に変数の種類を書きます。Integerは整数で、Stringは文字列という意味です。この辺も英語なのかなと思います。安心してください、私も英語はさっぱり苦手です。
まとめ
今回の記事は軽い気持ちで書き始めたのですが、なんかまとめているうちに、自分は誰に向けて活動してるかというものの宣言になってしまったような気がします。
おそらく人に伝えることをしている人って、それぞれ違う層にむけて情報を発信してるんじゃないかなって。
特にExcelはその色が強くて、私はコンピュータを仕事で使っているけど、それはコンピュータが仕事なのではなくて他にスペシャルなことをしている人がコンピュータを活用することでよりスペシャルがスペシャルになる、という人をターゲットにしているので、ちょっとExcel情報発信者の皆様とは違う景色を見ているのかなって思っています。
だからExcel方眼紙も、VBAの変数の型宣言も、なんなら神Excelだって使う人が使いやすいと感じるなら、どっちでもいいタイプです。
いろんな教え方があって、ターゲットは違っても、目的はExcelを使って世の中をよくしていく、という目的は一緒だと思うので、方向的に全然違うことろを見ているわけではないと思っています。
で、この記事は、Excelの標準機能だけでどこまでのツールが作れるか。VBAなしで行ける限界点を解説しました。そしてVBAをちょっといじるだけでものすごく便利になるということも解説しました。
え？そのマクロに記録するものの作業の手順をどう考えればわからない？
わかります。確かにそれが一番わかりたい部分ですよね。またそれについては後日解説します。
あー、疲れた。でもかなりいい記事になった！！
コメント