ExcelマクロVBA再入門 | 第16回.エクセルの機能を上手に使う | マクロが覚えられないという初心者向けに理屈抜きのやさしい解説



最終更新日:2015-10-04

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


エクセルには便利な機能が豊富に用意されています、

マクロVBAでもエクセルの便利な機能を上手く使う事を考えましょう、

エクセルの便利な機能を上手く使い、組み合わせることで、

マクロVBAコードがとても簡単になり、すっきりしたVBAコードになります。

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




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

事前に説明しておきますが、
話をわかり易くするために、あくまで簡単な例にしています。
従って、以下の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で一括入力


それぞれのマクロの記録で記録されたコードは、

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



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



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



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

■これを参考にマクロVBAを書いてみると、



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

そもそもセルに手入力する時には、
=から書き始めれば数式となる訳ですから、R1C1形式である必要が無く、普通に、シート上と同じようにセル参照で書けば良いのです。
さらに、相対参照で入力すれば、Ctrl+Enterで全てのセルに正しく計算式が入ります。

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

前回の、
第15回.手作業で出来なければマクロは書けない
で説明しましたが、手作業で出来ることが、まず最初に必要なのは、こういう事です。



では、もう一つ例をやってみましょう。
以下の表になります。



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

どんな方法でやれば良いか、ちょっとだけ考えてみて下さい。

・・・

色々な方法が考えられます。

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

ちょっと発想を変えてみると、こんな方法もあります。

・A〜C列を、C列降順で並べ替え
・MATCHで先頭行を所得し、F〜G列に出力

MATCH関数やVLOOKUP関数は(完全一致の場合)、検索値が一致した先頭を返しますので、これを利用します。

マクロVBAコードは



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

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

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

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


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


■並べ替え

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

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


マクロの記録のSortオブジェクトは、少し難しいと感じるかもしれません。
sample3の2003までのSortメソッドをまずは覚えて下さい。
SortオブジェクトもSortメソッドも、
注意点としては、Headerの指定です。
Sortのダイアログ画面の右上にある、
先頭行をデータの見出しとして使用する」のチェックに対応しています。



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


■オートフィルター

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



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





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


エクセルには、まだまだ多くの機能があります。
しかし、それらのVBAコードを全て覚えようとは思わないでください。
必要になった時、都度マクロの記録で調べられれば良いです。
ただし、その為には、事前にマクロの記録して、
画面での指定と、VBAコードを対比しつつ見ておくようにして下さい。
決してVBAコードを暗記する必要はありません。
一度でも見ておけば実際に必要になった時に直ぐに使えるようになります。




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

第15回.手作業で出来なければマクロは書けない
第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回.複数ブックよりデータを集める

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

スプレッドシートが非常に遅い、高速化するには|Google Apps Script入門(1月17日)
画像のトリミング(PictureFormat,Crop)|ExcelマクロVBAサンプル集(12月27日)
シート保護|Google Apps Script入門(12月24日)
表示の固定|Google Apps Script入門(12月24日)
グラフ|Google Apps Script入門(12月21日)
入力規則|Google Apps Script入門(12月13日)
並べ替え|Google Apps Script入門(12月12日)
メモの挿入・削除と改行文字|Google Apps Script入門(12月6日)
リンクの挿入・編集・削除|Google Apps Script入門(12月6日)
セルに数式を入れる|Google Apps Script入門(12月1日)

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

1.RangeとCellsの使い方|ExcelマクロVBA入門
2.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
3.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.ひらがな⇔カタカナの変換|エクセル基本操作
8.CSVの読み込み方法|ExcelマクロVBAサンプル集
9.変数とデータ型(Dim)|ExcelマクロVBA入門
10.VBAのFindメソッドの使い方には注意が必要です|ExcelマクロVBA技術解説



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

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


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

    ↑ PAGE TOP