とします後者は Excel 上の指定したセル範囲を R 上にデータフレームとして保存しますたとえばセル A1 から B10 の範囲の値 ( 1 行目は変数名それ以外はデータとする ) を R 上にデータフレーム mydf として保存するには Rinterface.PutDataframe m

Size: px

Start display at page:

Download "とします後者は Excel 上の指定したセル範囲を R 上にデータフレームとして保存しますたとえばセル A1 から B10 の範囲の値 ( 1 行目は変数名それ以外はデータとする ) を R 上にデータフレーム mydf として保存するには Rinterface.PutDataframe m"

たつぞうあきくぼ
5 years ago
Views:

( 独 ) 農業食品産業技術総合研究機構農村工学研究所農村計画部主任研究員合崎英男 2000 年 3 月北海道大学大学院農学研究科博士後期課程修了博士 ( 農学 ) 農林水産省農業研究センター研究員農業工学研究所研究員同主任研究官を経て 06 年 4 月より現職専門分野は農業経済学 ( 主に環境配慮や食品安全性に関する意思決定分析 ) 1.

1 ( 独 ) 農業食品産業技術総合研究機構農村工学研究所農村計画部主任研究員合崎英男 2000 年 3 月北海道大学大学院農学研究科博士後期課程修了博士 ( 農学 ) 農林水産省農業研究センター研究員農業工学研究所研究員同主任研究官を経て 06 年 4 月より現職専門分野は農業経済学 ( 主に環境配慮や食品安全性に関する意思決定分析 ) 1. はじめに RExcel シリーズ第 4 回では Excel VBA で R の関数 ( 以下 R 関数 ) を利用する方法について紹介します Excel では利用者がさまざまな機能を独自のプログラムとして作成活用できるように Excel VBA( 以下 VBA) というマクロ言語を実装しています RExcel の導入によって VBA に新たなプロシージャ等が加わり VBA 上で R 関数が利用可能となります今号ではいくつかの計算例を紹介しますが VBA 自体の説明は最小限となっています VBA の利用経験によっては若干わかりにくい部分があるかもしれません VBA を解説した書籍等は多数刊行されていますので必要に応じて参照してください 2. プロシージャ RExcel を導入することで VBA に新たなプロシージャが加わりますがそれらの先頭部分には Rinterface. が付いていますここでは今号で使用する 10 のプロシージャを簡単に紹介します Rinterface.StartRServer と Rinterface.StopRServer はそれぞれ R サーバーを開始 / 終了させるために使用します Rinterface.RRun は R 関数で記述した命令文を実行しますたとえば一様乱数を 1 つ生成して変数 a に保存する命令文を R 上で実行するには Rinterface.RRun a<-runif(1) と記述します Excel から R に数値行列 / データフレームを渡すためには Rinterface.PutArray と Rinterface.PutDataframe を利用します前者は Excel 上の指定したセル ( 範囲 ) の値を R 上に変数として保存しますたとえばセル A1 に入力されている数値を R 上に変数 var1 として保存するには Rinterface.PutArray var1, Range( A1 ) 48 ESTRELA 2010 年 11 月 (No.200)

2 とします後者は Excel 上の指定したセル範囲を R 上にデータフレームとして保存しますたとえばセル A1 から B10 の範囲の値 ( 1 行目は変数名それ以外はデータとする ) を R 上にデータフレーム mydf として保存するには Rinterface.PutDataframe mydf, Range( A1:B10 ) とします一方 Rから Excelに数値行列 / データフレームを渡すためには Rinterface.GetArray と Rinterface.GetDataframe を利用します前者は R 上の変数に保存されたデータを Excel 上のセル ( 範囲 ) に返しますたとえば R 上の変数 var1 に保存されている値をセル A2 に返すには Rinterface.GetArray var1, Range( A2 ) とします後者は R 上のデータフレームを Excel 上のセル範囲に返しますたとえば R 上のデータフレーム mydf をセル A1 を左上端とするセル範囲に返すときには Rinterface.GetDataframe mydf, Range( A1 ) 紹介したワークシート関数 RApply と RCall に対応します ) たとえば前者を使ってセル A1 に入力された数値の 2 乗を計算しその結果をセル A2 に返すときには Rinterface.GetRApply function(x)x*x, Range( A2 ), Range( A1 ) とします Rinterface.InsertCurrentRPlot は R 関数で作成した図のコピーを幅 / 高さ等を指定して Excel 上の任意のセルを左上端とした領域に貼り付けますこれらのプロシージャの詳細な書式やそのほかのプロシージャ等については RExcel のヘルプ ( RExcel RExcel Help ) をご覧ください 3. 準備 Excel の起動後ツールバーの RExcel Start R を選択して R を立ち上げます次に VBA のプログラムを作成するため Visual Basic Editor( 以下 VBE) を立ち上げます Excel のツールバーからツールマクロ Visual Basic Editor を選択します VBE( 図 1) が立ち上がりましたら RExcel 図 1 Visual Basic Editor(VBE) とします R 関数を使った命令文を R 上で実行しその結果を得るためのプロシージャとして Rinterface.GetRApply と Rinterface. RunRCall があります前者は実行結果を Excel に返しますが後者は返さないという違いがあります ( それぞれ前回の 2010 年 10 月号で 2010 年 11 月 (No.200) ESTRELA 49

3 の機能を VBA で実行できるように設定します VBE のツールバーのツール参照設定を選択すると参照可能なライブラリーの一覧が表示されますその中から RExcelVBAlib を探して選択されているか確認しますライブラリー名の前のボックス ( ) にチェックマーク ( ) が入っていれば選択済みです選択されていない場合は同ボックスをクリックしてチェックマークを入れ [OK] ボタンを押します最後に VBE のツールバーから挿入標準モジュールを選択しプログラムを入力するコードウィンドウを開きます 4. プログラム例今回は簡単なプログラム例を 5 つ紹介します前回 (2010 年 10 月号 ) のワークシート関数を利用した例とほぼ同様な内容を VBA で行うこととします数 var2 に保存するよう指示しています 5 行は変数 var2 に保存されている値をセル A2 に転送するよう指示しています以上で R 関連のコード部分が終わりであることから 6 行で RServer を停止させています上記のプログラムを VBE に入力した上で Excel のシートに戻りセル A1 に 3 を入力して ( 図 3) このマクロを実行しますマクロの実行は Excel のツールバーのツールマクロマクロから現れるウィンドウで実行したいマクロ名を選択して [ 実行 ] をクリックしますマクロ名はプログラムの 1 行の Sub の後に設定した Example1 が該当します実行した結果 3 2 の値である 9 がセル A2 に出力されますなお図 2の3 行から5 行を 2 節で Rinterface.GetRApply の例として示した命令文で置き換えることもできます ⑴ 数値の計算例 1 はセル A1 に入力された数値の 2 乗した値をセル A2 に出力するプログラムです図 2 にプログラムを示します 1 行の Sub と7 行の End Sub は VBAの命令文ですこの 2 つの間に記述されたコードが 1 行の Sub の後に記述されている Example1 という名称のマクロとして処理されます ( Example1 の後の半角両括弧はどのような名称のときでも付けます ) 2 行から 6 行が RExcel によって新たに導入されたプロシージャを使ったコード部分です 2 行で RServer を開始させています 3 行はセル A1 に入力されている数値を変数 var1 として R に保存するよう指示し 4 行は R で変数 var1 の 2 乗を計算しその計算結果を変 ⑵ 複数の変数を使った計算例 2 では 2 つの変数の相関係数を求めます使用するデータはセル A4 から B10 の範囲に入力されている変数 x と y とします ( 図 3) 図 2 の 9 行から 14 行が本例のプログラムです 9 行 10 行 13 行 14 行は例 1 と同じですので説明は省略します 11 行から 12 行でセル A4 から B10 の範囲を変数名も含めてデータフレームとして扱い (AsSimpleDF(Range( A4:B10 ))) R 上で変数 xとyの相関係数を求めて ( function(mydf)with(mydf, cor(x,y)) ) その結果をセル A12 に返す (Range( A12 )) よう指示しています上記のプログラムを Example2 として先ほどの Example1 のプログラムの後に入 50 ESTRELA 2010 年 11 月 (No.200)

4 フリーソフトによるデータ解析マイニング図 2 Example1 5 のプログラム 1 行 Sub Example1() 2 行 Rinterface.StartRServer 3 行 Rinterface.PutArray "var1", Range("A1") 4 行 Rinterface.RRun "var2<-var1*var1" 5 行 Rinterface.GetArray "var2", Range("A2") 6 行 Rinterface.StopRServer 7 行 End Sub 8 行 9 行 Sub Example2() 10 行 Rinterface.StartRServer 11 行 Rinterface.GetRApply "function(mydf)with(mydf, cor(x,y))", _ 12 行 Range("A12"), AsSimpleDF(Range("A4:B10")) 13 行 Rinterface.StopRServer 14 行 End Sub 15 行 16 行 Sub Example3() 17 行 Rinterface.StartRServer 18 行 Rinterface.PutArray "mat1", Range("A5:B10") 19 行 Rinterface.RRun "mat2<-t(mat1)" 20 行 Rinterface.GetArray "mat2", Range("A14") 21 行 Rinterface.StopRServer 22 行 End Sub 23 行 24 行 Sub Example4() 25 行 Rinterface.StartRServer 26 行 Rinterface.RunRCall "function(mydf)with(mydf, plot(x,y))", _ 27 行 AsSimpleDF(Range("A4:B10")) 28 行 Rinterface.InsertCurrentRPlot Range("C17"), _ 29 行 widthrescale:=0.3, heightrescale:=0.3, closergraph:=true 30 行 Rinterface.StopRServer 31 行 End Sub 32 行 33 行 Sub Example5() 34 行 Rinterface.StartRServer 35 行 Rinterface.PutDataframe "mydf", Range("A4:B10") 36 行 Rinterface.RRun "out<-summary(lm(y~x, data=mydf))" 37 行 Rinterface.GetArray "out$coef", Range("B30") 38 行 Rinterface.GetArray "t(colnames(out$coef))", Range("B29") 39 行 Rinterface.GetArray "rownames(out$coef)", Range("A30") 40 行 Rinterface.StopRServer 41 行 End Sub 力しますセル A4 から B10 のデータを変数名も含めて入力した上で例 1 と同様な手順で Example2 マクロを実行すると図 3 の 12 行に示す結果が得られますなお図 2 の 11 行の末尾にある _ は VBA で定義されている記号でその行に書かれている命令文は次の行にも続いていることを表します 11 行と 12 行を 1 つの行で記述するときはこの _ を削除した上で 12 行の内容を 11 行の末尾から続けて記述します 2010 年 11 月 (No.200) ESTRELA 51

⑶ 行列の計算例 3 では行列の計算を行います図 3 の変数 x と y の数値部分のみに注目して 6 行 2 列の行列とみなしそれを R に転送して転置し転置行列を Excel に戻します図 2 の 16 行から 22 行が本例のプログラムです 18 行はセル A5 から B10 の範囲のデータ (Range( A5:B10 )) を R 上に行列 mat1 として保存し 19

5 ⑶ 行列の計算例 3 では行列の計算を行います図 3 の変数 x と y の数値部分のみに注目して 6 行 2 列の行列とみなしそれを R に転送して転置し転置行列を Excel に戻します図 2 の 16 行から 22 行が本例のプログラムです 18 行はセル A5 から B10 の範囲のデータ (Range( A5:B10 )) を R 上に行列 mat1 として保存し 19 行は行列 mat1 の転置行列 (t(mat1)) を行列 mat2 に保存し 20 行は行列 mat2 をセル A14 を左上端とする範囲 (Range( A14 )) に戻すよう指示していますこのプログラムを Example3 として例 2 のプログラムの後に入力実行すると図 3 の 14 行から 15 行に示す結果が得られます図 3 各 Example の実行結果 52 ESTRELA 2010 年 11 月 (No.200)

6 フリーソフトによるデータ解析マイニング ⑷ 作図例 4 では変数 x と y の散布図を描きます図 2の24 行から31 行が本例のプログラムです 26 行から27 行はセル A4 から B10 の範囲にあるデータをデータフレームとしてRに取り込み (AsSimpleDF(Range( A4:B10 ))) 関数 plot を使って作図 ( function(mydf) with(mydf, plot(x,y)) ) するよう指示していますこの命令によって新たなウィンドウに変数 x と y の散布図が描かれます 28 行から 29 行はその散布図のコピーを Excel 上のセル C17 を左上端とした範囲に指定した幅と高さ ( ともに 0.3) で貼り付けもとの散布図は削除 (closergraph:=true) するよう指示していますこのプログラムを Example4 として入力実行した結果が図 3 の 17 行から 27 行の範囲にある図です ⑸ 統計モデル分析最後の例は変数 x と y を利用した回帰分析です図 2の33 行から41 行が本例のプログラムです 35 行はセル A4 から B10 の範囲にある変数 xとyのデータセット (Range( A4:B10 )) を変数名も含めて R 上にデータフレーム ( mydf ) として転送するよう指示しています 36 行は R 上で関数 lm を利用して回帰分析を実行し (lm(y~x, data=mydf)) その結果の要約( 関数 summary) を out に保存するよう指示していますそして out に保存されている情報のうち 37 行では得られた推定値等 ( out$coef ) をセル B30(Range( B30 )) を左上端とする範囲に 38 行では Estimate などの項目名 ( t(colnames(out$coef ) をセル B29 (Range( B29 )) から右側のセル範囲に 39 行では変数名 ( rownames(out$coef) ) をセル A30(Range( A30 )) から下の範囲にそれぞれ返すよう指示しています上記のプログラムを Example5 として入力実行した結果が図 3 の 29 行から 31 行の部分になります 5. おわりに Excel のツールバーの RExcel Demo Worksheets Writing macros にはマクロを使った例が示されています今号の例と同じ R 関数を使った作業を異なるコードで実行しているケースもありますまた今回は紹介できなかったプロシージャも利用されています関心のある方は必要に応じて参照してください 2010 年 11 月 (No.200) ESTRELA 53

図 1 プロファイル作成プログラム ⑴ では各種情報を入力するためのダイアログボックスの表示中盤では R 関数を利用した直交配列表の探索後半ではプロファイルの作成をそれぞれ行います ⑴ 各種情報の入力プログラムの前半 ( 図 1) ではプロファイルを設計するために必要な各種情報を入力する機能を

図 1 プロファイル作成プログラム ⑴ では各種情報を入力するためのダイアログボックスの表示中盤では R 関数を利用した直交配列表の探索後半ではプロファイルの作成をそれぞれ行います ⑴ 各種情報の入力プログラムの前半 ( 図 1) ではプロファイルを設計するために必要な各種情報を入力する機能を ( 独 ) 農業食品産業技術総合研究機構農村工学研究所農村計画部主任研究員合崎英男 2000 年 3 月北海道大学大学院農学研究科博士後期課程修了博士 ( 農学 ) 農林水産省農業研究センター研究員農業工学研究所研究員同主任研究官を経て 06 年 4 月より現職専門分野は農業経済学 ( 主に環境配慮や食品安全性に関する意思決定分析 ) 1. はじめに RExcel シリーズ第 5 回では