Visual Basic for Application

次週は祝日です。その次の週は休講で、次回の講義は5月13日です。

今日の内容

1. 先週の解説
2. 2次式の当てはめ。1次式との比較。
   
3. VBA入門

1次式をあてはめる場合の係数、定数項の求め方

を最小にするa,bを求めます。見ての通りa,bに関する2次式で、しかも2乗してから足しているのでf(a,b)は0以上、つまり必ず最小値を持ちますから、a,bで偏微分して0とおいた式を解けば良いです。
さらに、未知数はaとbの2個、式は「aで偏微分した式＝0」と「bで偏微分した式＝0」の2個ですから個数は一致していますし、2次式を偏微分すれば1次式、つまり2元連立一次方程式ですから、たまに例外もありますが殆どの場合解けます。
まず微分して

両辺を-2で割って、括弧を外して

Σを分けて、a,bで整理して

この時点で確かにa,bに関する2元連立一次方程式になっています。両辺をnで割って

ごちゃごちゃしてきましたが、平均をで表すと

下の式からとなります。これを上の式に代入して

aについて整理して

従って
となります。
このまま計算しても良いのですが、分母はxの分散、分子はx,yの共分散なので

となります。

この方法で数式を書くと準備にやたらと時間がかかり過ぎて肝心の部分が書けないので、次回から別の表示法を考えます。

2次式の当てはめ。1次式との比較。

先週と同じ方法で、今度は2次式をあてはめてみましょう。

この係数はどうやって求めましょうか？直線y=ax+bを当てはめた時同様、今度はを当てはめますので

を最小にするを求めます。
微分で求めるのは3週間の宿題として、まずは前回のようにソルバーを使って求めてみましょう。

VBA入門

左上の丸いボタンを押して、「Excelのオプション」をクリックします。そして「[開発]タブをリボンに表示する」にチェックをつけてOKを押します。

すると右端に「開発」が表示されるのでクリックして、マクロのセキュリティをクリックして、「すべてのマクロを有効にする」に●をつけてOKを押します。


これで準備は完了です。
ではマクロでどんなことが出来るか見てみましょう。「マクロの記録」をクリックしてください。マクロの名前を聞かれますので、ここはそのままOKを押します。


何か適当にExcelの操作をしてください。ここでは1から5の数字を入力してみました。
そして、記録終了をクリックしてください。

すると、Excelは今の操作を全部覚えています。確かめるために、今入力したものを全部消してから、今度は「マクロ」をクリックしてください。するとどのマクロを実行するか聞かれます。今は一つしかないのでそのまま「実行」を押します。
すると、先ほど自分でやったことがそのまま繰り返されたはずです。


一体どんな仕組みになっているか見るために「Visual Basic」のボタンを押します。するとVisual Basicの画面が開くので、左側のModule1をダブルクリックするとこの画面になります。


先ほど「マクロの記録」をクリックしてから「記録終了」をクリックするまでの操作が全てプログラムに書かれたのです。今すぐ覚える必要はありませんが、ActiveCell.FormulaR1C1 = "1"は選択しているセルに1を代入しなさい、Range("A2").SelectはA2セルを選択しなさい、という命令です。
このように自分の操作をプログラムに記録するのではなくて、今度は自分でプログラムを書いてみましょう。今、人によって色々違うことを書いたと思うので、一旦Excelを終了させて、もう一度立ち上げて、先ほど同様「開発」をクリックして「Visual Basic」をクリックしてください。
プログラムを書く部分を用意したいので「挿入」をクリックして「標準モジュール」をクリックしてください。

現れた画面にプログラムを書きます。
まず
sub 練習
とだけ書いて、Enterキーを押してください。すると自動的に

Sub 練習()

End Sub

となり、カーソルは真ん中の空行にあります。そこに
cells(1,1)=1
と書いてください。これは上から1番目、左から1番目のセルに1を代入しなさい、という命令です。
プログラムを書いたら、上の右向きの三角をクリックしてください。するとさっきと同じように、どのプログラムを実行するか聞かれます。今は一つしか書いていないのでそのまま実行を押すと、A1セルに1が代入されます。Excelの画面で「マクロ」を押しても実行できます。

このプログラムを保存する時の注意。
左上の「上書き保存」を押すと、どの形式でファイルを保存するか尋ねられます。ファイルの種類として「Excelマクロ有効ブック」を指定してください。
2階実習室で保存するときは、P:ドライブに保存してください。

さてこれでプログラムを書いて実行できるようになったので、具体的なプログラミングを始めましょう。
先ほどのCells(1,1)=1とEnd Subの間に
cells(2,1)=2
cells(3,1)=3
と書き込んで実行してみてください。
あるいは
cells(1,2)=-2
cells(1,3)=-3
と書いて、cellsの二つの添え字がExcelのどのセルに対応しているか確認しましょう。

10個続けて書くには、
cells(1,1)=1
cells(2,1)=2
cells(3,1)=3
cells(4,1)=4
cells(5,1)=5
cells(6,1)=6
cells(7,1)=7
cells(8,1)=8
cells(9,1)=9
cells(10,1)=10

と書くのは大変なので、これをFor文を使って

for i=1 to 10
　cells(i,1)=i
next　i

と書きます。

次に下のような九九の表ができるようにプログラムを考えて見ましょう。