MBAで教えるエクセルマクロ　2010

MBAのビジネス講座 > エクセルを使いこなすテクニック

エクセルでマクロを使いこなすには、ある程度VBAについての知識が必要です。手順としては、まず表示からマクロを選択し、マクロの記録を取ります。その後、記録されたをVBAで加筆修正していきます。

無数に乱数を発生させるモンテカルロシミュレーションの実行

エクセルでは乱数を発生させる数だけ関数が必要となり、シミュレーション回数が多くなると固まってしまう。このため、乱数の発生をVBAで実行し、結果を数値として吐き出すことによりエクセルファイルのサイズを小さくし、フリーズが防げます。

Webからいくつもの株価の時系列データをダウンロードし、データベースを定期的にアップデート

エクセルWebクエリを使うと、エクセル上にデータを取り込めますが、VBAのloop関数を使ってエクセルWebクエリを繰り返し実行させることによりエクセルのセル上に打ち込まれた何十もの株式コードを自動的に読み込み、エクセルWebクエリでデータを取り込むという繰り返し処理を実行できます。

下はその事例です。赤の部分がその中で囲われた部分の繰り返しを意味し、青の部分がエクセルWebクエリの実行指示です。このコードは、yahoo financeからエクセル上で指定した複数銘柄の週次のヒストリカルデータをダウンロードする為のコードです。

スポンサーリンク

ここでもエクセルWebクエリについては「マクロの記録」を使えば勝手にマクロに記述されます。一方、やはり繰り返し計算に必要なFor～Next関数はVBAで手書きする必要があります。

以下はその一例で、マクロの記録で記述されたコードを挟むようにしてFor～Next関数を使っているのが分かると思います。

Dim i As Integer
For i = 1 To Finalrow

'get data
Sheets("download").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://table.yahoo.co.jp/t?c=2007&a=12&b=10&f=2020&d=12&e=31&g=w&s=" & lis.Cells(i, 1).Value & "" _
, Destination:=dow.Range("$A$1"))
.Name = "historicdata.aspx?symbol=1325"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

Range("g14g200").Copy Sheets("data").Cells(2, i + 1)

Next i

データの時系列化

横に時間、縦に日付というマトリックスで記録されたヒストリカルデータをエクセルで処理しやすいように縦一列のデータへ変換。イメージとしては下のようなデータをグラフ処理など出来るように縦一列にするマクロです。

このマクロは以下のとおり。「マクロの記録」を使って、「行列を入れ替えて貼り付け」を記録させればほとんどのマクロの自動作成ができます。一方、手作業であれ一行「行列を入れ替えて貼り付け」ては次の一行にとりかかる作業をFor～Next関数を使って繰り返し処理する記述をVBAでしてやる必要があります。

Sub マトリクスデータを縦に並べ替え()

Dim i As Integer
Dim j As Integer

For i = 1 To 1728

j = i - 1

'priceをコピー
Cells(i + 2, 5).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy

Cells(j * 24 + 1, 3).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

'日付をコピー
Cells(i + 2, 4).Select
Selection.Copy

Range(Cells(j * 24 + 1, 1), Cells(i * 24, 1)).Select
ActiveSheet.Paste

'時間をコピー
Cells(1, 5).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy

Cells(j * 24 + 1, 2).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Next i

End Sub

どの作業もVBAなしでできなくもないですが、データが膨大になると、時間とエクセルの処理能力から相当な力技となってしまいます。上記程度のテクニックであれば、基本的な知識のみで対応できますので『Excel VBAのプログラミングのツボとコツがゼッタイにわかる本』でVBAの基礎を学び、試行錯誤しながらわからないことをExcel VBA 逆引き辞典パーフェクトで都度調べてゆけばだれでもプログラミングは可能です。 統計やファイナンスの基礎知識を使い、VBAでちょっとした定量評価がでれば相当優秀に思われますが、実際は人から評価されるほど難易度は高くありません。

-他の使用者の評価- Amazonでの評判を見る 楽天での評判を見る