Excel の使用法と Excel VBA を用いたプログラミング入門

1. Excelの使用法とExcel VBAを用いたプログラミング入門 飯野雄一、豊島 有

2. 実験をするとデータがどんどん出る。→グラフにしてかっこよく発表するにはエクセルぐらい使いこなせなきゃ！それだけじゃない。今や大量データの時代！これからの生物学者はコンピュータによる解析やプログラミングができた方が絶対有利。実験をするとデータがどんどん出る。→グラフにしてかっこよく発表するにはエクセルぐらい使いこなせなきゃ！それだけじゃない。今や大量データの時代！これからの生物学者はコンピュータによる解析やプログラミングができた方が絶対有利。 本格的なプログラムでなくとも、いろんなソフトに付随しているマクロを使えるようになれば、繰り返し操作などが自動化できる。・・・ 手で何回も同じようなキー操作を打ち込むのはカッコよくない！ さらに、ソフトにもともと用意されていない処理を自分のニーズに合わせて作ることができる。

3. 「でもすぐにはなかなか、、、、」 ↓ １）プログラミングの考え方がわかればよい。 ２）日頃からちょくちょく短いプログラムを書くようにする。 →だんだん慣れる。 →いざやりたいことができたら勉強すればできると思えるところまで慣れる。

4. なぜ、この演習でまずExcelか？ • Excelはデータ処理の最も定番のソフト。関数による統計計算もできる上、ちゃんとしたプログラム言語（VBA）が備わっている。→上達すればレポート作成に有利。→日常使うExcelで気軽にプログラムが書けると大幅なパワーアップになる！ • Excelの表形式での視覚的なデータ表示はプログラミングを覚えるのに最適。

5. なぜ、この演習でまずExcelか？ • プログラム言語はどれも似たようなもの：英語とドイツ語のような関係。☆ただし、どの読者も恐ろしく文法にうるさい。---結局機械は石頭。 • その意味でもVBAはよい。ヘルプもデバッグ機能もあるので。しかもGUI（グラフィックユーザーインターフェース）が標準装備。

6. 一旦覚えればあとはなんとでもなる 本を買うのもよしExcel VBAなどという本は山のように出ている。 分からないことがあったらネットで検索。→山ほど答えが書いてある。 他のプログラム言語も似たようなものなので覚えるのは難しくない。 6

7. Excelの基本

8. Excelの基本的使い方 • 表形式にデータを並べて処理。 • 左端あるいは上端の番号、記号をクリックすると行や列全体が選択される。選択しておいて、上の「ホーム」タブの「セル」の「削除」をクリックすると行、列が削除される。逆に同じ場所の「挿入」で一行挿入。 • 「ホーム」タブの「セル」の「書式」の「非表示/再表示」でその行を表示しないようにすることも可。 • グラフにしたい部分を四角く囲って「挿入」タブをクリックする。→グラフの種類を選んでクリック。

9. Excelの基本的使い方 • 飛び飛びのセルの選択Controlを押しながら順次セル範囲を選んでいく。 • コピーの仕方1)一つのセルを選択してコピー、別のセルを選んでペースト。2)連続したセルへのコピーは、セルを選択し、右下端を持ってずりっと引きずる。どっち方向へも可。☆この際、数字は自動的に１ずつ増える場合がある。これをやめる（または強制的にやらせる）ためにはControlを押しながら引きずる。------ あとで使うので練習してみよう。

10. Excelの基本的使い方 • コピーの仕方ものすごく下まで同じものをコピーして埋めたいとき：1) コピーするもとのセルをクリック2) コピーしたい領域の一番下のセルをシフトを押しながらクリック（逆順でもよい）。3) 「ホーム」タブの「編集」の「フィル」（下矢印のアイコン）をクリック。 • エラーバーのつけかた1) グラフ上でデータ系列を選ぶ。2) 「グラフツール」「レイアウト」タブの「誤差範囲」で「その他の誤差範囲オプション」で設定ウィンドウを開く。3) 「両方向」を選び、「ユーザー設定」「値の指定」と進む。4) 「正の誤差の値」「負の誤差の値」で標準誤差の入力されているセル範囲を選ぶ。

11. "A Genome-Wide Transcriptional Analysis of the Mitotic Cell Cycle"

12. マイクロアレイ解析 0分 160分 10分 20分 ･　･　･ ･　･　･ mRNA抽出 mRNA抽出 mRNA抽出 ･････････ 蛍光ラベル 蛍光ラベル 蛍光ラベル 遺伝子1 ハイブリダイゼーション ハイブリダイゼーション ハイブリダイゼーション 遺伝子2 遺伝子3 　･ 　･ 　･

13. テキスト形式のデータ • 数値データを扱う際のテキスト形式のファイルの種類（万国共通）空白区切り gene1 10 20 30 gene2 120 140 160タブ区切り gene1 10 20 30　 gene2 120 140 160カンマ区切り gene1,10,20,30 gene2,120,140,160固定長データ gene1 10 20 30 gene2 120 140 160

14. テキストデータの読み込み • Excel画面から、「ファイル」／「開く」を選択。または「開く」アイコンをクリック。 • 選択対象を「すべての読み込み可能なファイル」として、 開きたいテキストファイルを指定。 • テキストファイルウィザードが開くので、「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」をチェック、「次へ」。 • 区切り文字のうち「スペース」をチェックして「完了」。

15. データの並べ替え（ソート） • 表形式のデータを行単位で入れ替え、特定の列の数字を指標に昇順（上から下へ数字の小さい順）または降順（上から下へ数字の大きい順）に並べ変える。1) 並べ変えたいデータ全体を選択。ワークシート全体で構わない場合は「コマンド‐Ａ」キー。2) 「データ」/「並べ替え」並べ替えウィンドウが開く。3) 選択範囲の一番上の行がタイトル行（入れ替えの対象にならない）であれば「先頭行をデータの見出しとして使用する」をチェックする。3) 「最優先されるキー」で指標とする列を選択。一番上の行がタイトル行であれば列のタイトルで選ぶ。そうでなければ列A、列B、…から選ぶ。昇順（値の小さい順）または降順（大きい順）を選び、「OK」で並べ替えが実行される。

16. 関数

17. Excelの使い方のポイント • 複数のセルの値から関数により計算して答えを別のセルに入れる。 • 式の入ったセルを別のセルにコピーすると、数字（値）がコピーされるわけではなく式だけがコピーされるので、縦一列、横一列などについて同じ計算をさせることができる。 • 関数で処理しきれないときには適宜VBAプログラミング言語によるマクロを使う。

18. Excelの関数 • 方法１）結果を書き込みたいセルを選択し、「=average(A2:R2)」などと書き込む。このとき、セルの名前"A2:R2"を入力するかわりに該当するセル範囲をドラッグで選択（またはセルをクリック）してもよい。そのあとに")"を手入力するのを忘れずに。入力後は必ずEnterを入力してボックスから出ておく。先頭の"="も忘れずに。方法１’）セルを選択してから数式バー（"fx"の右のボックス）に書き込んでもいい。 • 方法２）結果を書き込みたいセルを選択し、fxをクリック。関数の一覧が表示されるので、必要な関数を選ぶと、関数ウィザードが起動。これに従って必要な入力を行う。

19. Excel関数の調べ方 • 前記方法２）。関数の種類ごとに分類されているので、これかなと思った関数について、「クリックすると選択した関数のヘルプが表示されます」をクリックすると説明が現れる。 • 「ファイル」タブの「ヘルプ」／「Microsoft Officeヘルプ」をクリック、「ヘルプの検索」ボックスに質問の文章を入れると関連のある説明を検索してくれる。

20. Excelの関数の例（統計関数） • SUM（A2:R2) A2セルからR2セルまでの合計 • AVERAGE（A2:R2) 　A2セルからR2セルまでの平均 • COUNT（A2:R2) 　A2セルからR2セルまでのうち数字のはいったセルの数 • STDEV（A2:R2) 　A2セルからR2セルまでの標準偏差

21. Excelの演算子 • +　：足す • -　：引く • *　：掛ける • /　：割る • ^　：累乗 • MOD(A1,A2)　：A1をA2で割ったあまり(関数） • ABS(A1)　：A1の絶対値（関数） • SQRT(A1) :A1の平方根（関数） • " "　：文字列を指定。例：A2はセルの名前。 "A2"はA2という文字列。 • ＆　：文字列の連結　　　（"ABC" & "DEF" は "ABCDEF"になる） [例] A2 & “DEF” と “A2” & “DEF”の違いは？

22. 関数をコピーしたとき参照はどうなる？ • AVERAGE（A2:R2) を下にコピーしてみましょう。A2:R2の部分はどうなるでしょうか？ • では右にコピーしたら？ • このように、デフォルトでは相対的な位置関係を保つようになっています（相対参照）。 • 絶対参照にさせるためには、$をつけます。行だけまたは列だけに$をつけることもできます。AVERAGE（$A2:$R2)、AVERAGE（A$2:R$2) 、AVERAGE（$A$2:$R$2) と書き換えて、下と右にコピーしてみましょう。

23. Excelの関数の例（文字操作） • LEN（A1) A1セルに書かれた文字列の文字数。 • FIND（"t",A1) 　A1セルの文字列の中で、左から探してtが何番目に来るか。tがないときはエラー値となる。 • LEFT（A1,5) 　A1セルの文字列の左から5文字をとる。 • RIGHT（A1,7) 　A1セルの文字列の右から7文字をとる。 • MID(A1, 5, 3) 　A1セルの文字列の5文字目から３文字をとる。

24. Excelの関数の例（その他） • IF（A1=0,"Yes","No") A1=0が正しければ（式の結果がTrueであれば）"Yes"、正しくなければ"No"が表示される。 • ISERROR(A1)　A1セルの内容がエラーであればTrue。 • TTEST(A2:R2,A3:R3,1,2) T検定。A2~R2のデータとA3~R3のデータの平均の差異を検定しP値を表示。 • HYPERLINK("http://yahoo.co.jp","Yahoo")クリックするとブラウザでhttp://yahoo.co.jpにアクセスする。セルの表示はYahooとなる。 • 注：ハイパーリンクの設定されているセルを選択しようとしてクリックすると、ブラウザが開いてしまう。単にセルを選びたいだけなら、隣のセルを選んだ後に矢印で移動するか、複数のセルを選択する。

25. VBAマクロ

26. Excel VBAマクロについて • VBA = "Visual Basic for Applications"プログラム言語 VB (Visual Basic)をマイクロソフトのアプリケーション用にしたもの。 • VBA for Excel, VBA for Word, VBA for PowerPointなどがある。 • いずれもアプリケーションに付属でついている。（単なるVisual Basicは別個に販売されている。） • いずれも言語体系はVBと同じ。何に使うかが違う分、対象に関する記述の部分が違っている（後述）。 • ということで、本日はVBA for Excelを使う。Excel機能に依存しない(VBとしての)使い方もできる。

27. プログラムを書いて走らせるということ • 最初に認識しておいてください＜プログラムを書いて一発で動く人はいない＞たいてい、どこかに間違いがあって正しく動きません。動かないだけならいいが、暴走してウンともスンとも言わなくなることしきり。＝いわゆるフリーズ。これまでは人のせいだったがこれからは自分のせい。↓防衛策：プログラムを走らせる前には必ず苦労して書いたファイルを保存することをお勧めします。

28. VBA Editorの使い方 • プロジェクトウィンドウ • 現在開いているワークブックの構成が表示されている。 • ワークブック全体はVBAプロジェクトと呼ばれ、各ワークシートとModule、Formなどからなる。ダブルクリックするとそれぞれのコンポーネントに付随したマクロ（ソースコード）が表示される。 • メインウィンドウ • ここにVBAプログラム（「コード」）が表示される。現在表示されているものが何であるかは、ウィンドウの一番上のバーに表示されている。表示内容の切り替えはコマンドバーの「ウィンドウ」から行う。

29. 表の画面と裏の画面の行き来 • VBAエディタからExcel画面に行くには以下のいずれか。1)左上のエクセルアイコンをクリック2)Control-Tabまたはalt-Tabを使ってウィンドウを選ぶ3) 「表示」/「Microsoft Excel」4)「ファイル」／「終了してMicrosoft Excelに戻る」。この場合はVBA画面は閉じられる。 • Excel画面からVBA画面に行くには「開発」／「Visual Basic」

30. プロシジャー • Sub　Macro1()End Subの間が１つのプロシジャー（一度に実行される命令群）になる。これは正確にはSubプロシジャー（サブルーチン）である。このプロシジャーが受け取る値があるときには、それが（）内に書かれる。これ以外にFunctionプロシジャー（値を返すプロシジャー）、イベントプロシジャー（マウスクリック等、何かの事象が起こったときに実行されるプロシジャー）などがある。

31. マクロの実行とデバッグ(1) • マクロの実行は以下のいずれかの方法による。 • VBA Editor画面から • 　を押して起動。カーソルがある位置のマクロが走る。 • 「実行」/「マクロの実行」またはFn-F5 。 • Excel画面から • 「開発」/「マクロ」で実行するマクロの選択画面が出るので走らせたいマクロを選択。 • 「表示」/「マクロ」/「マクロの表示」で同じ画面を出す。 • 暴走してどうにもならないときは以下のいずれかの方法。 • 画面下のタスクバーのExcelアイコン上で右クリック、「すべてのウィンドウを閉じる」で開くウィンドウで「プログラムの再起動をします」を選択。 • Shift-Control-Escから実行中のエクセルファイルを選び「タスクの終了」

32. マクロの実行とデバッグ(2) • ファイルを開くとき • マクロプログラムを持つExcelファイルを一旦保存して再度開こうとすると、「マクロが無効にされました」などの警告が出る場合がある。これに対して「コンテンツの有効化」をクリックしてマクロを実行可能とする。 • どうしても開かせてくれない場合は「ファイル」/「オプション」/「セキュリティセンター」/「セキュリティーセンターの設定」/「マクロの設定」で適切なセキュリティーレベルを選択する。 • ちなみにWindows版Excel2007を使う人は、マクロを含むファイルは.xlsx形式では保存できません。.xlsm形式にします。

33. Excelファイルの呼称 • Excelの１つのファイルを「ワークブック」と呼ぶ。 • 下に出っ張っているタブで選択できる１ページずつを「ワークシート」と呼ぶ。 • つまり、ワークブックは１つあるいは複数のワークシートよりなる。それぞれのワークシートには名前がついている（タブに記載）。 • ワークシート上のひとつひとつのます目のことを「セル」と呼ぶ。セルには数字や式が書かれている。

34. セルの参照(1) • 例えば第２行第３列のセルであれば、Cells(2,3)。（参考） Range("C2")の表現も可能だがあまり実用性はない。列行の順番が逆なので注意。 • セル範囲の参照の場合（例えば左上の2行3列）はRange(Cells(1,1),Cells(2,3))Range("A1:C2")などを使う。

35. セルの参照(2) • 前頁の内容は正確にはWorksheets(“data”).Cells(2,3)（およびWorksheets(“data”).Range(“C2”) ）の表記が正しい。WorkSheetを指定しない場合は、現在アクティブなワークシート上のセル（=ActiveSheet.Cells(2,3)）を意味することになる。 • 通常はプログラム実行前に見ていたシートがアクティブシートである。 • 別のシートをアクティブにする方法WorkSheets("data2").Activate但し、頻繁に異なるシートをActivateすると、ちらちらしてプログラムの実行も遅くなるので注意。

36. セルの値とプロパティ • ワークシート上の特定のセルに表示されている値のことを、Cells(2,3).Valueなどと表記する。これも「WorkSheets().」を省略した形である。さらに、「.Value」は省略可。つまりCells(2,3)と書くとアクティブシートの第2行第3列のセルの値を意味する。 • それ以外にも、セルはいろいろな属性（プロパティ）を持っている。Cells(2,3).Height　　　高さCells(2,3).Font　　　　フォントCells(2,3).Borders　　周りの罫線（の色、太さなど） Cells(2,3).Interior　　内部（塗り）

37. セルの操作 • セルの値を使った計算の例Cells(1,20) = Cells(1,1) / Cells(1,19) • セルの選択Cells(1,1).SelectSelection.Value = 5Cells(2,1).SelectSelection.Value = "Gene"

38. 列番号対応表

39. 変数 • 変数自由に値を代入して使えるもの。実態はコンピュータのメモリである。例：A = 1---- Aというメモリ領域に1という数字を入れる（＝代入する）B = 2　　　　”=”は右辺の値を左辺の変数に代入しろという意味。C = A + B--- Aと書くと変数Aの値、つまりメモリの内容を読み出すA = 3　　　　　　ことを意味する。変数AとBの内容を足してCに代入。D = A + BMsgBox D ----- Dの内容を表示する。以上の一連の命令を実行すると、Aの値は３、Bの値は２、Cの値は３、Dの値は５になる。 • 変数名には英字と数字が使える。但し先頭は数字であってはいけない。半角255文字以内。漢字も使える！ • 配列も使える。配列とは変数が番号付きで並んでいるようなもの。A(2) = A(1) + 1A(2,2) = B(2,2) + 1　など。

40. 宣言文（１） • 変数を使うためには、使う前に変数の型の宣言をする必要がある。例：Dim A As Integer("A" を整数として定義する。) • Integer：整数（-32768～32768） • Long：長整数（-2147483648～ 2147483648 ） • Single：単精度実数（有効数字約7桁） • Double：倍精度実数（有効数字約15桁） • String：文字列型変数 • Boolean：論理変数。TrueとFalseのいずれかの値をとる。 • Object：オブジェクト変数（ワークシート、セルなど何でも「物（オブジェクト）」を代入できる。）

41. 宣言文（２） • ただし、宣言しなくてもテキトーに処理される。--- 正確にいうと、型宣言を省略するとVariant型の変数となる（Variant型として明示的に宣言もできる）。この型は格納されるデータに応じて変数の型を変化させるので便利。 • 配列の宣言は要素の最大数を指定。一次元配列： Dim A(100) As Integer- A(1), A(2), A(3), .... A(100)の100個の変数が用意されることと同じ。二次元配列： Dim A(100,100) As Integer- A(1,1) から A(100,100)までの10000個の変数を用意。

42. 大文字と小文字 • Visual Basicでは大文字と小文字は区別されない。つまりNameとnameは同じ意味であり別の名前としては使えない。 • しかし、便宜上しばしば大文字小文字交じりの変数名を使う（例： NameString）。宣言文に変数名を大文字小文字交じりで書いておくと、以降、小文字のみで入力しても、 VBAエディターが自動的に大文字小文字交じりになおしてくれる。大文字に直らないときはミススペルの可能性がある。ミススペルのチェックに便利。 • 既定語（関数、制御文など）も通常大文字で始まる。これもVBAエディタが自動的に直してくれる。さらに既定語は自動的に青字になるので、書き方が間違っていないかのチェックに便利。

43. 演算子 • 以下のような計算式に使う。代入 A = 1 加算 A = B + 1 減算 A = B - 1 乗算 A = B * 2 割算 A = B / 3べき乗 A = B ^ 2 （Bの２乗）剰余 A = B mod 3 (Bを3で割ったあまり)絶対値 A = Abs(B) (Bの絶対値)文字列連結　A = B & “th data“ (&の両側は必ずスペースを空けること) • なお、オブジェクト変数にオブジェクトを代入する場合に限り、Set A = Worksheets("data")のように書く。これ以降、変数AはWorksheets("data")の意味となる。

44. ループ • コンピュータプログラムは、書かれた命令を一行ずつこなしていく。 • しかし短い操作であれば手作業でもできる。 • 同じ作業を繰り返し（しかも正確に）行ってくれるのがコンピュータの得意技。 • それをさせるのがループです。→これがコンピュータプログラムのキモなのでよく習得してください。どういう順番で命令が実行されるか、よく考えながらプログラムを書いてください。

45. For文（ループ）（1） • N=0For I = 1 To 10 N=N+INext IMsgBox Nこの例ではIを1から10まで1ずつ増やしながら「N=N+I」を何度も実行する。つまり1から10までの和を計算。結果を表示する。 • 全体のプログラムの構造が分かりやすいように、ループの中はタブ一個分、字下げをするとよい。VBAエディターでは複数の行を選択し、タブキーを押すとまとめて字下げされる。逆に字上げするときはシフト-タブ。 • N=0For I = 1 To 10 Step 2 N=N+INext IStepが指定されている場合、指定した数ずつIを増やしていく。つまり、この例ではI=1, I=3, I=5, I=7, I=9の順に実行される。

46. For文（ループ）（2） • 二重ループN=0For Row = 1 To 100 For Column = 1 To 10 N=N+1 Next ColumnNext RowFor文を二重にする使い方もよく行われる。この例ではRowを1から100まで順に増やすが、それぞれのRowについて、Columnを1から10まで順に増やしながら実行する。つまりN=N+1は合計1000回実行される • 必ずFor文とNext文の対応がついていることが必要である。For Row...Next Rowの中にFor Column...Next Columnがはいっていることに注意。

47. マクロの実行とデバッグ(3) • VBAエディタ画面で「デバッグ」／「ステップイン」（Fn-F8）とやると、一行ずつ実行されるので、実行順の確認ができる。 「デバッグ」／「カーソル行の前まで実行」(Command-Fn-F8)ではカーソル位置の前まで実行される。 • 途中、一時停止の状態での各変数の値が知りたいときは、「表示」／「イミディエイトウィンドウ」でイミディエイトウィンドウを開く。ここに、「?A」などという形で知りたい変数を書いてリターンを押すと、そのときの変数の値が表示される。ちなみに、このウィンドウにはどんな命令でも入力できる。その命令が直ちに実行される。 • 予めプログラムの中に「Debug.Print A, B」などと書いておけば、その命令が実行されるとイミディエイトウィンドウに変数の値が書かれる。 • ウォッチウィンドウを使ってもよい。「表示」/「ウォッチウィンドウ」でウィンドウを開く。ウォッチウィンドウ上で右クリックして「ウォッチ式の追加」でAなどと書いておくと、実行中のAの値が逐次表示される。 • ブレークポイントを設定してから実行すると、ブレークポイントで止まるので、そのときの変数等の状態を調べることができる。コード画面の左側の枠をクリックするとその行にブレークポイントが設定される。

48. マクロの実行とデバッグ(4) • プログラム実行時にエラーが起こったとき表示されるウィンドウで「デバッグ」を選ぶとVBA画面が開く（選択される）。黄色い矢印のところで問題が生じている。 • イミディエイトウィンドウまたはウォッチウィンドウを使って変数の値を確認する。 • ■を選ぶとデバッグモードが終了する。これを忘れると次が実行できない。

49. If文(1) • If I = 0 Then J = 1Iが0のときのみJを1にする。 • If I = 0 Then J = 1End IfIが0のときのみJを1にする。 • If I = 0 Then J=1Else J=2End IfIが0のときにはJを1に、それ以外のときはJを2にする。

50. If文(2) • If I = 0 Then J=1ElseIf I=1 then J=2Else J=3End IfIが0のときにはJを1に、そうでなくIが1のときにはJを2に、それ以外のときはJを3にする。 • ☆☆最初の表記（一行で書いてしまう形式）以外のIF文ではすべて End If を忘れないように。☆☆