VBA再入門
第16回.エクセルの機能を上手に使う

マクロが覚えられないという初心者向けに理屈抜きのやさしい解説
最終更新日:2020-09-26

第16回.エクセルの機能を上手に使う


エクセルには便利な機能が豊富に用意されています。
マクロVBAでもエクセルの便利な機能を上手く使う事を考えましょう。


エクセルの便利な機能を上手く使い、組み合わせることで、
マクロVBAコードがとても簡単になり、すっきりしたVBAコードになります。

エクセルの機能を使った例

以下の表で考えてみましょう。

エクセル マクロ VBA サンプル

A列の単価にB列の数量を掛けてC列に金額を出力します。
ただし、金額の計算結果をセルに出力するのではなく、計算式をセルに設定したい場合です。

事前に説明しておきますが、
話をわかり易くするために、あくまで簡単な例にしています。
従って、以下のVBAサンプルは、どれが良いという事ではありません。
お伝えしたいのは、エクセルの機能を使う事で、色々な方法が考えられるという事です。
選択肢が増えれば、複雑な問題、難しい問題に直面した時に解決する手段が増えるという事です。

普通にマクロVBAを書くと

エクセル マクロ VBAコード

マクロの記録を使ったりして、
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
この部分を参考に、マクロVBAを書くと上記のようなコードになると思います。
.FormulaR1Cは省略できます。
もちろん、普通はこれで問題ありませんし、このように書く事が多いでしょう。

特に覚えなくても大丈夫ですが、説明の都合上簡単に書いておきます。
※R1C1参照形式
セルの相対位置、つまり、計算式を設定しようとしているセルからの移動量を、行位置をRに続けて記述し、列位置をCに続けて記述します。
R ・・・ Rの後に何も記述しない場合は同一行
C ・・・ Cの後に何も記述しない場合は同一列
R1 ・・・ Rの後に数値のみ記述した場合はその絶対行数
C1 ・・・ Cの後に数値のみ記述した場合はその絶対列数
R[1]・・・ Rに続けて[数値]とした場合は、数値行数分、行位置をづらした行
C[1]・・・ Cに続けて[数値]とした場合は、数値列数分、列位置をづらした列

手操作で色々な方法を試してみましょう。

・C2セルに計算式を入れ、フィルハンドルをダブルクリックでオートフィル
・C2セルに計算式を入れ、C2セルをコピー、C3~C11を選択し貼り付け
・C2~C11を選択し、計算式を入力しCtrl+Enterで一括入力


それぞれのマクロの記録のVBAコードをみていきましょう。

C2セルに計算式を入れ、フィルハンドルをダブルクリックでオートフィル

エクセル マクロ VBAコード

C2セルに計算式を入れ、C2セルをコピー、C3~C11を選択し貼り付け

エクセル マクロ VBAコード

C2~C11を選択し、計算式を入力しCtrl+Enterで一括入力

エクセル マクロ VBAコード

マクロの記録を参考にVBAを書いてみると、

今回の例なら、なんとなく最後が簡単そうです。

エクセル マクロ VBAコード

最終行取得のコードが長いので、一旦変数に入れてますが、実質は1行のVBAコードとなっています。

そもそもセルに手入力する時には、
=から書き始めれば数式となる訳ですから、R1C1形式である必要が無く、
普通にシート上と同じようにセル参照で書けば良いのです。

さらに、相対参照で入力すれば、Ctrl+Enterで全てのセルに正しく計算式が入ります。

このコードが書けるのは、
Ctrl+Enterで選択範囲内に数式を一括で入れられることを知っている人だけになります。

前回の、
第15回.手作業で出来なければマクロは書けない
エクセルの基本は、シート上で効率よく速やかに関数と機能を使い目的の出力を得る事です。マクロVBAは、この手作業を自動化するものであり決して特別なものではありません。まずは手作業でやってみて、それをVBAコードにするという手順が重要です。
ここで説明しましたが、手作業で出来ることが必要なのは、まさにこういう事です。

どのエクセル機能を使うかを考える

以下の表になります。

エクセル マクロ VBA サンプル

A列~C列に、日付順・商品コード順に金額の入った表になります。
F2~G11にそれぞれの商品の最大金額とその日付を求めます。

どんな方法でやれば良いか、ちょっとだけ考えてみて下さい。
・・・
色々な方法が考えられます。

愚直に、E列の商品コード1件ずつに対して最大値を捜していく方法や、
DMAX関数を使う方法もありますね。
オートフィルターを使って絞り込んでから・・・やりようはあると思います。

ちょっと発想を変えてみると、こんな方法もあります。
・A~C列を、C列降順で並べ替え
・MATCHで先頭行を取得し、F~G列に出力
MATCH関数やVLOOKUP関数は(完全一致の場合)、検索値が一致した先頭を返しますので、これを利用します。

マクロVBAコードは
エクセル マクロ VBAコード

処理後にA~C列を元の順に戻す必要があれば、再度Sortするようにします。
また、Matchの検索値が無い場合はエラーになってしまいますが、エラー処理については無視しています。

最初に書きましたが、この方法が良いと言っているわけではありません。
実際のデータでは、もっと複雑なデータ・条件となり、都度ベストな方法を模索する必要があります。

この例題の最初に、「ちょっとだけ考えてみて下さい。」と書きましたが、
並べ替えてMATCHを思いついた方はいますでしょうか。
これは、マクロの発想ではありません。
シートでデータ処理を多数こなした経験からの発想になります。

前回と今回の2回を通して伝えたいことは、
マクロVBAを自在に書けるようになるには、その前にシートでエクセルの関数・機能を自在に使えるようになる事が必要です。
という事です。

「並べ替え」と「オートフィルター」はマクロVBAでは頻繁に利用します。
これらについては、手操作での指定とVBAコードを対比しつつ、しっかりと覚えて下さい。


並べ替え

先のsample3でのSortは、Excel2003までのSortメソッドを使っています。
Excel2007以降では、Sortオブジェクトが追加されましたので、マクロの記録ではSortオブジェクトでのVBAコードとなります。
実際にマクロの記録をしてVBAコードをご自身で確認してください。

マクロの記録と、整理して書き直したVBAコード
エクセル マクロ VBAコード

マクロの記録のSortオブジェクトは、少し難しいと感じるかもしれません。
sample3の2003までのSortメソッドをまずは覚えて下さい。

SortオブジェクトもSortメソッドも、
注意点としては、Headerの指定です。
Sortのダイアログ画面の右上にある、
先頭行をデータの見出しとして使用する」のチェックに対応しています。

エクセル マクロ VBA 参考画面

この画面とVBAコードを見比べて理解するようにして下さい。

オートフィルター

マクロの記録でのVBAコードをそのまま使えますが、いくつかの注意点を記しておきます。
先の表で、B列の商品コードを"S001"で絞り込んだマクロの記録です。

エクセル マクロ VBAコード

注意点は、
Field:=で指定する数値は、オートフィルターの範囲内での左からの列位置になります。
従って、Field:=2は必ずしもB列とは限りません。
データがB列から始まっていればその範囲の2番目のC列の意味になります。
以下の画面を良く確認しておいてください。

エクセル マクロ VBA サンプル

エクセル マクロ VBA サンプル

VBAコードの
Criteria1:=
この1は、上の画面の上のテキストボックスに対応しているので1となっています。
下のテキストボックスCriteria2になります。
マクロの記録で、ANDやORを使うような指定をしてみてVBAコードを確認してください。

エクセルの機能を上手に使う

エクセルには、まだまだ多くの機能があります。
しかし、それらのVBAコードを全て覚えようとは思わないでください。
必要になった時、都度マクロの記録で調べられれば良いです。

ただし、その為には、事前にマクロの記録を行って、
画面での指定と、VBAコードを対比しつつ見ておくようにして下さい。

決してVBAコードを暗記する必要はありません。
一度でも見ておけば実際に必要になった時に直ぐに使えるようになります。

ExcelマクロVBA入門の対応ページ

第3回.マクロの記録
エクセルのマクロを説明する上では、まず最初に、「マクロの記録」を説明しなければなりません。マクロの自動記録とも言われたりしますが、要は、エクセルの手動での操作が、自動記録されるものです、もちろん、マクロの言語であるVBAで記録されます。実際には、マクロの記録から作成されたVBAコードを修正して使う事はしません。

第68回.シートの保護、ブックの保護(Protect)
せっかく作ったExcelシートを間違って壊してしまったり、Excel操作に慣れない人が壊してしまったりしたら困ります。無用な改変、意図しない変更からシートを保護するには、ワークシートを保護します。ただしシートの削除は、シート保護では守れません、シートの保護に対応するには、ブックの保護が必要です。
第70回.ウィンド枠の固定(FreezePanes)
ウインド枠を固定し、行の上部や列の左側がスクロールされないようにする場合のマクロVBA解説になります。ワークシートの行の上部や列の左側は、通常は見出しとして使う事が多くなります。ウィンドウ枠を固定することで見出しの行や列を固定し、スクロールしても見出しが表示されたままにする場合が多くなります。
第71回.印刷(PrintOut)
シートの内容をマクロVBAで印刷する場合は、PrintOutメソッドを使用します。このPrintOutメソッドが使える対象オブジェクトは複数あります、ブック、シート、セル、それぞれにPrintOutメソッドが存在するので、印刷範囲によって使い分けてください。
第73回.ページ設定(PageSetup)
VBAで印刷のページ設定をするには、PageSetupオブジェクトのプロパィを設定することで行います。基本的には、事前に印刷するシートのページ設定をしておくことが望ましいのですが、VBAで作成したシート等、事前にページ設定することができない場合はマクロVBAで印刷のページ設定をします。
第87回.WorksheetFunction(ワークシート関数を使う)
VBA関数以外に、Excelワークシート関数をマクロVBAで使うことが出来ます、ワークシート関数は、VBA関数よりはるかに多くの関数があるので、ぜひ活用したいところです。。ワークシート関数を使う事で、VBAコードを非常に簡潔に記述することが出来る場合が多いものです。
第88回.並べ替え(Sort)
並べ替えは、データ処理の基本中の基本です、乱雑なデータを並べ替えることは、データ処理の第一歩です。マクロVBAで並べ替えを実行するには、シート操作の「並べ替え」の機能を使用することになります。そもそもデータを並べ替えるという事は、そのデータのキーが何かを考えるという事です。
第89回.オートフィルタ(AutoFilter)
オートフィルタはExcelのデータベースとしての非常に強力な機能を提供してくれています、VBAで、必要なデータだけに絞り込んで他のシートにコピーしたり、不要なデータを一括で削除したりする場合は、とても高速に処理することができます。VBAでオートフィルタを操作するには、以下のメソッド・プロパティおよびオブジェクトを使用します。
第90回.フィルタオプションの設定(AdvancedFilter)
ワークシートの操作「フィルタオプションの設定」のVBAの記述になります、便利な機能ではありますが、そもそも、ワークシートの操作が難しいこともあり、あまり有効に使われていないように感じます。フィルタ詳細設定の使い方 「データ」タブ→「並べ替えとフィルター」グループの「詳細設定」この機能を使う場合のVBAになります。
第91回.条件付き書式(FormatCondition)
条件付き書式は、シート上で設定しておいた方が良いのですが、事前に設定しておけない場合は、VBAで条件付き書式を設定します。VBAで条件付き書式を設定する場合は、セル(Rangeオブジェクト)のFormatConditionsコレクションにFormatConditionオブジェクトを追加することで行います。
第126回.入力規則(Validation)
入力規則は、Validationオブジェクトになります、ワークシート範囲の入力規則を表します、入力規則は、シート上で設定しておいた方が良い場合が多いですが、マクロVBAで設定する必要も出てきます。入力規則を設定するには、Validationオブジェクトを使います。
第92回.名前定義(Names)
名前定義をマクロVBAで扱う場合の解説になります、名前定義は、複数セル範囲や単一セルに対して名前を付けることで、そのセル範囲を参照する時に名前で参照できるようにするものです。名前で参照できることで、セル位置(行位置、列位置)を固定値で指定しなくて済むようになります。
第93回.ピボットテーブル(PivotTable)
ピボットテーブルをVBAで操作する事が良いかどうか、少々疑問な部分もあります。しかし、ピボットテーブル(PivotTable)はエクセルでは必須機能になりますので、少なくとも、ピボットテーブルの基本くらいは知っておくべきでしょう。今回はピボットテーブル(PivotTable)のオブシェクトをざっと見てから、
第94回.コメント(Comment)
VBAでセルにコメントを入れたり、編集したり、削除したりするVBAコードを解説します。セルに対する注意書き等をコメントとして入れておくことで、使いやすいシートにします。コメント(Comment)に関する各種オブジェクト Rangeオブジェクト RangeオブジャクトのCommentに関するプロパティとメソッド Commentオブジェクト セルのコメントを…
第95回.ハイパーリンク(Hyperlink)
VBAでハイパーリンク(Hyperlink)を追加したり削除したりする場合を解説します。ハイパーリンクは、Hyperlinkオブジェクトです、そして、Hyperlinkオブジェクトの集まりであるコレクションが、Hyperlinksコレクションになります。



同じテーマ「マクロVBA再入門」の記事

第13回.セルのコピペ方法を知る(CopyとPaste、さらに)
第14回.セルの書式を設定する(NumberFormatLocal,Font,Barders,Interior)
第15回.手作業で出来なければマクロは書けない
第16回.エクセルの機能を上手に使う
第17回.セルにブック・シートを指定する(Workbooks,Worksheets,With,Set)
第18回.シートをコピー・挿入・削除する(Worksheets,Copy,Add,Delete)
第19回.ブックを開く・閉じる・保存する(Workbooks,Open,Close,Save,SaveAs)
第20回.全てのシートに同じ事をする(For~Worksheets.Count)
第21回.ファイル一覧を取得する(Do~LoopとDir関数)
第22回.複数ブックよりデータを集める
第23回.複数のプロシージャーを連続で動かす(Callステートメント)


新着記事NEW ・・・新着記事一覧を見る

フォルダとファイルの一覧を取得(os,glob,pathlib)|Python入門(9月26日)
import文(パッケージ・モジュールのインポート)|Python入門(9月24日)
例外処理(try文)とexception一覧|Python入門(9月23日)
リスト内包表記|Python入門(9月22日)
Pythonの引数は参照渡しだが・・・|Python入門(9月21日)
lambda(ラムダ式、無名関数)と三項演算子|Python入門(9月20日)
関数内関数(関数のネスト)とスコープ|Python入門(9月18日)
関数の定義(def文)と引数|Python入門(9月18日)
組み込み関数一覧|Python入門(9月17日)
辞書(dict型)|Python入門(9月16日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ