情 報 ( 実 習 )( 芸 術 1 班 ) 第 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 -
y 2015 年 度 秋 学 期 芸 術 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-3 -
(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 X 2 + b のグラフ) いままでの 練 習 をもとにして Y = ax 2 +b のグラフを 書 いてみよう 但 し 係 数 の 値 は a=-1, b=2 とします 挑 戦 しよう 放 物 線 (Y=X^2)のグラフは 書 けました では 円 (あるいは 楕 円 )のグラフをエクセルで 書 く 場 合 はどうしたらよいのでしょうか? - 4 -
5 VBA の 簡 単 なプログラム 情 報 処 理 の 講 義 ですでに 習 われたと 思 いますが プログラムというのは コンピュータの 動 作 を 命 令 する 文 の 集 まりです コンピュータはその 文 書 を 解 釈 し 命 令 を 順 番 に 実 行 してゆきます Excel の VBA プログラムは Excel にその 動 作 を 命 令 し Excel はその 命 令 を 解 釈 し 実 行 します こ のため これまで 習 ったワークシート 関 数 (SUM,AVERAGE)のようなセルレベル(ミクロ)での 動 作 ではなく VBA は 全 体 (マクロ)レベルでの 動 作 をコントロールするため マクロ 言 語 とも 呼 ば れます グラフを Excel で 書 く 際 に あなたが 手 作 業 で 行 った 動 作 を プログラムを 書 いて コンピュータ 自 身 に 自 動 的 に 行 わせようというわけです ちょっとだけ 準 備 初 期 設 定 では VBA プログラムするために 便 利 ないくつかの 機 能 が 無 効 になっている ファイル から オプション をクリックし リボンのユーザ 設 定 メインタブ を 選 択 し 開 発 のチェックボックスをオンにし OK をクリックする 以 上 の 作 業 により リボンに 開 発 タブが 追 加 される マクロを 含 む Excel ブックの 保 存 Excel2007 からは マクロを 含 む Excel ブックは 他 のブックと 区 別 されるようになった 保 存 す る 際 には ファイルの 種 類 を Excel マクロ 有 効 ブック として 保 存 する 必 要 がある VBE(Visual Basic Editor)の 起 動 (1) VBE を 起 動 するには 開 発 タブ 左 端 の Visual Basic をクリックする (2) VBE ウィンドウが Excel とは 別 のウィンドウとして 表 示 されます 新 しくプログラムを 作 成 するためには メニューバー 挿 入 の 中 の 標 準 モジュール を 選 択 して 下 さい (3) プログラムの 本 文 は 右 側 の モジュールシート に 書 きます このモジュールシートには 複 数 のプログラムを 書 くことができます (4) VBA プログラムは Sub 文 と End Sub 文 との 間 に 書 くのが 原 則 です (5) 以 下 の 例 を 入 力 してみて 下 さい 練 習 しよう(プログラムの 入 力 ) プログラム( 練 習 1) Sub HelloVBA() MsgBox( Hello.VBA!! ) End Sub - 5 -
このプログラムを 実 行 するには ツールバーにある 実 行 ボタン ( 右 向 き 三 角 形 )をクリックし ます すると Excel のシートの 上 に メッセージボックスが 現 れます つまり MsgBox( Hello.VBA!! ) という 命 令 文 は メッセージボックスに Hello.VBA!!と 書 き なさい というものです まとめると プログラムの 形 は 以 下 のようになります Sub プログラム 名 ( ) 実 行 する 命 令 文 End Sub 6 グラフを 動 かしてみよう 先 ほどの Y = ax 2 のグラフを 書 いた 際 に 係 数 a の 値 を 変 化 させると グラフの 形 がそれにつれて 変 化 してゆきました そこで xの 連 続 データ 入 力 yの 値 の 計 算 x-y のグラフを 書 く a の 値 を 変 え る という 風 に 連 続 して a の 値 を 変 更 しながらグラフを 書 いてゆけば あたかもグラフ が 動 いているかのように 見 えてきます しかしここで 難 しいのは 連 続 して a の 値 を 変 える ことです これをプログラムで 行 うため の 道 具 For ~ Next 文 を 実 習 します そこで まず 簡 単 なプログラムの 練 習 から 始 めましょう セル B1 に1を セル B2 に2を B10 に 10 を 連 続 して 入 力 するプログラムの 例 をお 見 せします - 6 -
プログラム( 練 習 2) Sub EX2 ( ) Cells(1, B )=1 Cells(2, B )=2 Cells(10, B )=10 End Sub しかし 10 回 も 同 じ 命 令 文 を 書 かないで 済 ませられないでしょうか そういった 時 はこのようにします (C 列 の1~10 のセルに 1~10 を 一 度 に 代 入 します ) プログラム( 練 習 3) Sub EX3 ( ) 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バイト) - 7 -
For Next 文 の 説 明 この 命 令 は 指 定 する 回 数 ある 命 令 を 繰 り 返 すもので 一 般 的 な 形 式 は For 変 数 = 初 期 値 To 停 止 値 Step 間 隔 値 実 行 する 命 令 文 Next 変 数 となっています さて これで 準 備 ができたので Y = ax 2 てみましょう のグラフを a の 値 を 変 化 させて 動 かし (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 (3) スタートボタンを 設 定 します これで スタートボタンをクリックすれば グラフが 動 き 出 します どうですか うまくグラフが 動 きましたか? このプログラムに 出 てくる 残 りの 命 令 についても 簡 単 に 説 明 しておきます - 8 -
Range 文 の 説 明 これは あるオブジェクト( 定 義 した 変 数 やセルなど)にある 値 を 代 入 させる 命 令 で 形 式 は Range( オブジェクト ) = 値 となっています Range( a ) = a という 命 令 は a と 定 義 した 変 数 に 今 設 定 されている 値 を 代 入 しなさい というものです Calculate 文 の 説 明 これは 指 示 されたワークシート 上 で 計 算 を 実 行 させる 命 令 文 です グラフを 美 しく 動 かすための 注 意 グラフの 縦 軸 (Y 軸 )の 目 盛 が 自 動 設 定 となっていると グラフが 動 くに 連 れて 縦 軸 の 目 盛 を 変 えてしまうので グラフの 動 きがきれいに 見 えません そんなときは グラフの 縦 軸 にマウス を 持 っていって Y/ 数 値 軸 と 出 たら 右 クリックして 軸 の 書 式 設 定 を 選 択 し 目 盛 を 見 てください 自 動 となっているチェックボックスをはずし 目 盛 を 設 定 しましょう 皆 さん 気 が 付 きましたか? Y = a X^2 のグラフの 係 数 a の 値 は プログラムの 中 で -3 < a < +3 の 範 囲 内 を 0.1 のステップで 変 化 するようにと 命 令 しました それでは 動 き 終 わった 後 の 係 数 a の 値 は いくつになっていますか? なんと a = 2.99999 となっていて きっちりと a = 3 とはなっていません これは 何 故 でしょう? コンピュータがサボったのでしょうか? そこで 試 しに step 0.1 となっているところを step 0.125 として 実 行 してみてください どう 変 化 したでしょうか? - 9 -
7 今 週 の 課 題 について 課 題 10 本 日 練 習 した Y = ax 2 のグラフを 参 考 にして Y X 3 bx のグラフを Excel で 描 き さらに 係 数 b の 値 を 変 化 させて このグラフ を 動 かすプログラムを VBA を 使 って 作 成 して 下 さい X の 範 囲 は -4 < X < +4, 係 数 b の 範 囲 は -5 < b < +25,にすると 動 きが 美 し く 見 えます ワークシートとグラフ VBA プログラムの3 点 が 完 成 したら その Excel のファイ ルを 電 子 メールに 添 付 して 講 師 宛 に 送 って 下 さい VBA プログラムも 保 存 した エクセルに 表 やグラフと 一 緒 に 保 存 されます - 10 -