情報(実習)(体育4班)
第 10 回 エクセル篇3
VBA を使ったプログラムによる関数のシミュレーション
これまでの2回で、Excel の表計算ソフトとしての機能及び統計機能の初歩を実習しました。 Excel にはこれ 以外にも大変有 用な機能があり ます。今 日はその中から 、 VBA(Visual Basic Application)を使ったプログラムの初歩も習得しながら、関数のシミュレーションを行ってみたい と思います。
1 エクセルでグラフを描く
・はじめに、関数の形をグラフに描くことを考えます。 ・たとえば、 y = x2 という関数のグラフを、エクセルを使って書いてみましょう。 Y = X2 という関数のグラフ (1) 新しいエクセルのデータシートを用意します。 (2) そのデータシートに、X の値を A 列に、またそれに対応する Y の値を B 列に入力します。 (3) X の値としては、−3〜+3の整数をとりましょう。 (4) 下のようなデータができましたか。 A B C D E 1 X Y=X^2 2 -3 9 3 -2 4 4 -1 1 5 0 0 6 1 1 7 2 4 8 3 9 9 10 11 (5) X の定義をします。 ・まず A 列を選択します。 ・次に 「数式」→「名前の定義」とクリックすると、「新しい名前」のボックスが現れる。 ここで「名前」に「X」と入力する。但しここでは「Sheet1」の上での「X」であるという意味で、「範囲」として「Sheet1」を選びます。 ・同様に、列 B を「Y」と定義します。 (6) このデータでグラフを作ります。グラフの種類は「散布図」を、グラフの形式は「データポイ ントを折れ線でつないだ散布図」を選んでみて下さい。 y=x^2 0 1 2 3 4 5 6 7 8 9 10 -4 -2 0 2 4 x y y (7) このようなグラフが書けましたか。 (8) よく見ると、グラフの細かさがまだ足りません。そこで、 (9) X の値の増分を「0.1」にしてみましょう。 (10) しかし、-3.0, -2.9, -2.8,・・・と、いちいち手で入力するのは面倒ですし、対応する Y の 値を計算するのも大変です。そのような時には、次に示すような、「連続データの入力」方 法があります。
2 連続するデータの入力方法
-3 から+3 までの、0.1 間隔で連続するデータの入力方法は、以下のとおりです。エクセルは賢い! 連続するデータの入力方法 (1) 最初の2つの値 -3 と -2.9 を入力する。 (2) 今入力した2つのセルを選択する。 (3) 選択範囲の右下の黒四角(フィルハンドル)にポインタを合わせ、表示される数値が +3 にな るまで下にドラッグする。3 数式の設定とコピー
X の値は連続データとして入力されましたが、その X に対応する Y(=X^2)の値も、同じように「自 動的に」計算させることができます。 Y の値の計算 (1) セル B2 に、セル A2 に対応する数式(=X^2)を入力する。 (2) セル B2 を選択して、右下の黒四角(フィルハンドル)をダブルクリックする。 (3) すると、入力されている X に対応するすべての Y のセルに、数式がコピーされ、表が完成しま す。 A B C 1 X Y = X^2 2 -3 9 3 -2.9 4 実習しよう(グラフの作成) 完成した表からグラフを作りましょう。 また、グラフのタイトルをつけたり、x軸やy軸などの名称もつけましょう。4 Y = a X
2のグラフを書こう
次に、Y = X2 に係数 a を付け、この係数の値を変えるとグラフの形がどう変わるかを見てみよう。 (1) 新しいワークシートに移る。(Sheet2 を選ぶ) (2) Y = aX2 の X と Y の表を作る。(下の表を参照) (3) X と Y の定義をする。範囲は、「Sheet2」とします。 A B C D 1 X Y = a X^2 a= 2 2 -3 18 3 -2.9 16.82 4 (4) セル C1 に「a=」と入力する。 (5) セル D1 に「2」と入力する。(係数 a の値です)(6) セル D1 の名前を「a」と定義する。セル D1 を選択した上で、 「数式」→「名前の定義」→「a」と入力する。 (7) これによって、セル D1 に入っている値が「a」の値となることが定義されました。 (8) A 列の X の値を連続したデータとして入力します。 (9) X の値としては、-3から+3までで、増分は 0.1 としましょう。 注意!!(その1) 「a」の定義を忘れずにおこなうこと。これを忘れると、後でグラフが動きません。 (10) セル B2 の数式「=a*X^2」を入力する。 注意!!(その2) 数式で「掛ける」を意味させる時は必ず「*」をつけること。 (11) セル B2 の右下をダブルクリックするとすべての Y に入力される。 (12) グラフウィザードを使ってグラフを作成して終了。 練習しよう ここで、セル D1 の a の値を、「a = 2」から別の値に変えてみましょう。 グラフはどう変化するのでしょうか? 実習しよう(Y = a X2 + b のグラフ) いままでの練習をもとにして、Y = aX2 +b のグラフを書いてみよう。 但し、係数の値は a=-1, b=2 とします。
挑戦しよう
放物線(Y=X^2)のグラフは書けました。では、円(あるいは楕円)のグラフをエクセルで書く場 合はどうしたらよいのでしょうか?5 VBA の簡単なプログラム
情報処理の講義ですでに習われたと思いますが、プログラムというのは、コンピュータの動作を命 令する文の集まりです。コンピュータはその文書を解釈し、命令を順番に実行してゆきます。Excel の VBA プログラムは、Excel にその動作を命令し、Excel はその命令を解釈し実行します。こ のため、これまで習ったワークシート関数(SUM,AVERAGE)のようなセルレベル(ミクロ)での動 作ではなく、VBA は全体(マクロ)レベルでの動作をコントロールするため、マクロ言語とも呼ば
れます。
グラフを Excel で書く際に、あなたが手作業で行った動作を、プログラムを書いてコンピュータ自 身に、自動的に行わせようというわけです。
ちょっとだけ面倒(ファイルの保存の仕方)
VBA を使ったプログラムは、このエクセルのファイルに含まれています。
Excel 2007 からは、VBA プログラムのようなマクロを含む Excel ブックは、そうでない Excel ブ ックと区別して(別の方法で)保存するようになりました。 ファイルを「新しく名前を付けて保存」する場合に、これまでのように、 ファイルの種類を「Excel ブック」とするのではなく、 「Excel マクロ有効ブック」として保存してください。
VBA プログラムを始める前に
・初期設定では VBA プログラムをするための機能が無効になっています。まず、 (1)「ファイル」メニューから「オプション」をクリック。 (2)「リボンのユーザー設定」→「メインタブ」を選択し、 (3)「開発」のチェックボックスをオンにして、「OK」をクリックする。 (4)これでメニューに「開発」タブが追加されていれば OK です。(6)すると、下図のような窓が、エクセルの窓とは別に開きます。 (7)新しくプログラムを作成するには、この窓のメニュー「挿入」→「標準モジュール」を選択 してください。ここに現れる広い画面に以下のようなプログラムをタイプします。
練習しよう(プログラムの入力)
プログラム(練習1)
Sub 練習 1 ( )
MsgBox(“Hello”)
End Sub
このプログラムを実行するには、ツールバーにある「実行ボタン」(右向き三角形)をクリックし ます。すると、Excel のシートの上に、メッセージボックスが現れます。つまり、「MsgBox(“Hello”)」 という命令文は、「メッセージボックスに Hello と書きなさい」、というものです。 まとめると、プログラムの形は、以下のようになります。Sub プログラム名 ( )
実行する命令文
・・・
End Sub
6 グラフを動かしてみよう
先ほどの Y = aX2 のグラフを書いた際に、係数 a の値を変化させると、グラフの形がそれにつれて 変化してゆきました。そこで、xの連続データ入力 → yの値の計算 → x-y のグラフを書く
↑ ↓a の 値 を 変 え る
という風に、連続して a の値を変更しながらグラフを書いてゆけば、あたかもグラフ
が動いているかのように
見えてきます。 しかしここで難しいのは、「連続して a の値を変える」ことです。これをプログラムで行うため の道具「For ~ Next 文」を実習します。そこで、まず簡単なプログラムの練習から始めましょう。 セル B1 に1を、セル B2 に2を、・・・B10 に 10 を、連続して
入力するプログラムの例をお見 せします。プログラム(練習2)
Sub 練習 2 ( )
Cells(1,”B”)=1
Cells(2,”B”)=2
Cells(10,”B”)=10
End Sub
しかし、10 回も同じ命令文を書かないで済ませられないでしょうか。 そういった時はこのようにします。(C 列の1~10 のセルに 1~10 を一度に代入します。)プログラム(練習3)
Sub 練習 3 ( )
Dim n As Single
For n=1 To 10
Cells(n,”C”)=n
Next n
End Sub
練習しよう(練習 2 と 練習 3 の入力)
上のプログラムを入力して実行し、数値が正しく入力されていることを確認しましょう。 その際に、スタートボタンも設定して使用しましょう。Dim 文の説明
上の練習3のプログラムには、「Dim n As Single 」という命令がありました。これは、プログラ ム中で使用する変数を定義する命令で、形式は、 Dim 変数名 As 型 となっています。変数の型としては以下のようなものがあります。 整数型 Integer 単精度型 Single (4バイト) 倍精度型 Double (8バイト) 文字列型 String (64kバイト)For 〜 Next 文の説明
この命令は指定する回数ある命令を繰り返すもので、一般的な形式は、 For 変数 = 初期値 To 停止値 Step 間隔値 実行する命令文 Next 変数 となっています。さて、これで準備ができたので、Y = aX
2のグラフを、a の値を変化させて動かし
てみましょう。
(1) 先ほど作った, Y = aX2 のグラフシートを準備します。 A B C D 1 X Y = aX^2 a= 2 2 -3 18 3 -2.9 16.82 4 (2) つぎに、VBA を起動し、以下のプログラムを入力します。
プログラム(問題 1)
Sub 問題 1 ( )
Dim a As Single
For a=-3 To 3 Step 0.01
Range(“a”)=a
Calculate
Next a
End Sub
これで、スタートボタンをクリックすれば、グラフが動き出します。 どうですか、うまくグラフが動きましたか? このプログラムに出てくる、残りの命令についても簡単に説明しておきます。Range 文の説明
これは、あるオブジェクト(定義した変数やセルなど)にある値を代入させる命令で、形式は、 Range(“オブジェクト”) = 値 となっています。「 Range(“a”) = a」という命令は、「a」と定義した変数に、今設定されている 値を代入しなさい、というものです。Calculate 文の説明
これは、指示されたワークシート上で計算を実行させる命令文です。グラフを美しく動かすための注意
→グラフの縦軸(Y 軸)の目盛が「自動設定」となっていると、グラフが動くに連れて縦軸の目盛 を変えてしまうので、グラフの動きがきれいに見えません。そんなときは、グラフの縦軸にマウスを持っていって、「Y/数値軸」と出たら右クリックして、「軸の書式設定」を選択し、「目盛」を見 てください。「自動」となっているチェックボックスをはずし、目盛を設定しましょう。