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

マクロが覚えられないという初心者向けに理屈抜きのやさしい解説
最終更新日: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回.手作業で出来なければマクロは書けない
・エクセルの機能を知る ・ワークシート関数を知る ・「手作業で出来なければマクロは書けない」のまとめ ・Excelマクロ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での書き方が分からない時に調べる方法

第68回.シートの保護、ブックの保護(Protect)
・シートの保護 ・シートの保護解除 ・シート保護・解除の実践例 ・ブックの保護 ・ブックの保護解除 ・最後に
第70回.ウィンド枠の固定(FreezePanes)
ウィンドウ枠を固定し、行の上部や列の左側がスクロールされないようにする場合のマクロVBA解説になります。ワークシートの行の上部や列の左側は、通常は見出しとして使う事が多くなります。ウィンドウ枠を固定することで見出しの行や列を固定し、スクロールしても見出しが表示されたままにする場合が多くなります。
第71回.印刷(PrintOut)
・PrintOutメソッド ・PrintOutメソッドの使用例 ・印刷時にプリンターを選択したい場合 ・印刷総ページ数の取得 ・間違った印刷を大量に行わないわない為には
第73回.ページ設定(PageSetup)
・PageSetupオブジェクト ・PrintCommunicationプロパティ ・ヘッダー・フッターのプロパテの設定方法 ・余白の設定方法 ・印刷範囲の設定/クリア:PrintArea ・総ページ数:Pages.Count ・改ページの位置を設定:PageBreak ・最後に
第87回.WorksheetFunction(ワークシート関数を使う)
・ワークシート関数の使い方 ・WorksheetFunctionで使用できる関数 ・個別の関数の使い方 ・関数の結果(戻り値) ・WorksheetFunctionの使用例. ・検索系の関数での日付の扱い ・WorksheetFunctionのエラー対処 ・最後に
第88回.並べ替え(Sort)
・Range.Sortメソッド・・・Excel2003までのソート ・2007以降の並べ替え ・Excel2003までのSortとExcel2007以降のSortの使い分け
第89回.オートフィルタ(AutoFilter)
・Range.AutoFilterメソッド ・AutoFilterModeプロパティ ・AutoFilterオブジェクト ・オートフィルタのVBA使用例 ・日付のフィルタ ・オートフィルタまとめ
第90回.フィルタオプションの設定(AdvancedFilter)
・フィルター詳細設定の使い方 ・Range.AdvancedFilter メソッド ・フィルターオプションの設定の関連記事
第91回.条件付き書式(FormatCondition)
・FormatConditionsコレクション ・FormatConditionオブジェクト ・条件付き書式のマクロVBA実践例 ・マクロVBAの条件付き書式について
第126回.入力規則(Validation)
・Validationオブジェクト ・入力規則(Validation)の使用例 ・入力規則を設定しても無効データが入力されてしまう場合への対処
第92回.名前定義(Names)
・Namesコレクション ・Nameオブジェクト ・RangeオブジェクトのNameプロパティ ・名前定義を使ったRangeの書き方 ・シートコピー時は名前定義に注意 ・名前定義の実践例
第93回.ピボットテーブル(PivotTable)
・ピボットテーブル(PivotTable)を構成するオブジェクト群 ・Excel2010のピボットテーブル(PivotTable)のマクロ記録 ・Excel2003のピボットテーブル(PivotTable)のマクロ記録 ・Excel2010とExcel2003のピボットテーブルVBAの違いについて ・ピボットテーブル(PivotTable)のマクロVBA実践例
第94回.コメント(Comment)
・コメント(Comment)に関する各種オブジェクト ・コメント(Comment)の挿入 ・コメント(Comment)の削除 ・コメント(Comment)の編集 ・コメント(Comment)実践例
第95回.ハイパーリンク(Hyperlink)
・Hyperlinksコレクション ・Hyperlinkオブジェクト ・ハイパーリンクの追加 ・ハイパーリンクの削除 ・既に設定されているハイパーリンクの扱い方



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

第13回.セルのコピペ方法を知る(CopyとPaste、さらに)
第14回.セルの書式を設定する(NumberFormatLocal,Font,Borders,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 ・・・新着記事一覧を見る

構成比を合計しても100%にならないと言われた…|ツイッター出題回答 (2022-09-01)
一覧から複数条件(部分一致、範囲)に合致するデータを抽出する|ツイッター出題回答 (2022-08-30)
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)|エクセル入門(2022-08-27)
IF関数の論理式で比較演算子を省略したCOUNT系関数を書くのは|ツイッター出題回答 (2022-08-23)
LAMBDA以降の新関数の使用例|エクセル入門(2022-08-22)
数珠順列(配置に条件付き)を全て出力する|ツイッター出題回答 (2022-08-20)
日付時刻のマイナス表示に対応する方法|ツイッター出題回答 (2022-08-17)
LAMBDA以降の新関数について|エクセル入門(2022-08-16)
条件付きの最大値と中央値("A"が2文字の条件)|ツイッター出題回答 (2022-08-14)
VBAマクロと操作対象データの分離について|ツイッター出題回答 (2022-08-11)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.並べ替え(Sort)|VBA入門
8.マクロって何?VBAって何?|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.エクセルVBAでのシート指定方法|VBA技術解説




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


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



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