情報 ( 実習 )( 体育 4 班 ) 第 10 回エクセル篇 3 VBA を使ったプログラムによる関数のシミュレーション これまでの2 回で Excel の表計算ソフトとしての機能及び統計機能の初歩を実習しました Excel にはこれ以外にも大変有用な機能があります 今日はその中から VBA(Visual Basic Application) を使ったプログラムの初歩も習得しながら 関数のシミュレーションを行ってみたいと思います 1 エクセルでグラフを描く はじめに 関数の形をグラフに描くことを考えます たとえば y = x 2 という関数のグラフを エクセルを使って書いてみましょう Y = X 2 という関数のグラフ (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 であるという意 - 1 -
味で 範囲 として Sheet1 を選びます 同様に 列 B を Y と定義します (6) このデータでグラフを作ります グラフの種類は 散布図 を グラフの形式は データポイ ントを折れ線でつないだ散布図 を選んでみて下さい y=x^2 10 9 8 7 6 5 4 3 2 1 0-4 -2 0 2 4 x 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 になるまで下にドラッグする - 2 -
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 = X 2 に係数 a を付け この係数の値を変えるとグラフの形がどう変わるかを見てみよう (1) 新しいワークシートに移る (Sheet2 を選ぶ ) (2) Y = ax 2 の 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 の値です ) - 3 -
(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 X 2 + b のグラフ ) いままでの練習をもとにして Y = ax 2 +b のグラフを書いてみよう 但し 係数の値は a=-1, b=2 とします 挑戦しよう 放物線 (Y=X^2) のグラフは書けました では 円 ( あるいは楕円 ) のグラフをエクセルで書く場 合はどうしたらよいのでしょうか? 5 VBA の簡単なプログラム 情報処理の講義ですでに習われたと思いますが プログラムというのは コンピュータの動作を命 令する文の集まりです コンピュータはその文書を解釈し 命令を順番に実行してゆきます Excel の VBA プログラムは Excel にその動作を命令し Excel はその命令を解釈し実行します こ のため これまで習ったワークシート関数 (SUM,AVERAGE) のようなセルレベル ( ミクロ ) での動 作ではなく VBA は全体 ( マクロ ) レベルでの動作をコントロールするため マクロ言語とも呼ば - 4 -
れます グラフを Excel で書く際に あなたが手作業で行った動作を プログラムを書いてコンピュータ自 身に 自動的に行わせようというわけです ちょっとだけ面倒 ( ファイルの保存の仕方 ) VBA を使ったプログラムは このエクセルのファイルに含まれています Excel 2007 からは VBA プログラムのようなマクロを含む Excel ブックは そうでない Excel ブックと区別して ( 別の方法で ) 保存するようになりました ファイルを 新しく名前を付けて保存 する場合に これまでのように ファイルの種類を Excel ブック とするのではなく Excel マクロ有効ブック として保存してください VBA プログラムを始める前に 初期設定では VBA プログラムをするための機能が無効になっています まず (1) ファイル メニューから オプション をクリック (2) リボンのユーザー設定 メインタブ を選択し (3) 開発 のチェックボックスをオンにして OK をクリックする (4) これでメニューに 開発 タブが追加されていれば OK です (5)VBA を起動するには 開発 タブから Visual Basic をクリックします - 5 -
(6) すると 下図のような窓が エクセルの窓とは別に開きます (7) 新しくプログラムを作成するには この窓のメニュー 挿入 標準モジュール を選択 してください ここに現れる広い画面に以下のようなプログラムをタイプします 練習しよう ( プログラムの入力 ) プログラム ( 練習 1) Sub 練習 1 ( ) MsgBox( Hello ) End Sub このプログラムを実行するには ツールバーにある 実行ボタン ( 右向き三角形 ) をクリックし ます すると Excel のシートの上に メッセージボックスが現れます つまり MsgBox( Hello ) という命令文は メッセージボックスに Hello と書きなさい というものです まとめると プログラムの形は 以下のようになります - 6 -
Sub プログラム名 ( ) 実行する命令文 End Sub 6 グラフを動かしてみよう 先ほどの Y = ax 2 のグラフを書いた際に 係数 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 を一度に代入します ) - 7 -
プログラム ( 練習 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 の値を変化させて動かし - 8 -
(1) 先ほど作った, Y = ax 2 のグラフシートを準備します 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 軸 ) の目盛が 自動設定 となっていると グラフが動くに連れて縦軸の目盛 を変えてしまうので グラフの動きがきれいに見えません そんなときは グラフの縦軸にマウス - 9 -
を持っていって Y/ 数値軸 と出たら右クリックして 軸の書式設定 を選択し 目盛 を見 てください 自動 となっているチェックボックスをはずし 目盛を設定しましょう 皆さん気が付きましたか? Y = a X^2 のグラフの係数 a の値は プログラムの中で -3 < a < +3 の範囲内を 0.1 のステップで変化するようにと命令しました それでは 動き終わった後の係数 a の値は いくつになっていますか? なんと a = 2.99999 となっていて きっちりと a = 3 とはなっていません これは何故でしょう? コンピュータがサボったのでしょうか? そこで試しに step 0.1 となっているところを step 0.125 として 実行してみてください どう変化したでしょうか? 7 今週の課題について 課題 6 本日練習した Y = ax 2 のグラフを参考にして Y X 3 bx のグラフを Excel で描き さらに係数 b の値を変化させて このグラフ を動かすプログラムを VBA を使って作成して下さい X の範囲は -4 < X < +4, 係数 b の範囲は -5 < b < +25, にすると動きが美しく見えます ワークシートとグラフ VBA プログラムの 3 点が完成したら その Excel のファイルを 電子メールに添付して 講師宛に送って下さい VBA プログラムも保存したエクセルに 表やグラフと一緒に保存されます 今日の授業はこれで終了です お疲れ様でした - 10 -