PowerPoint プレゼンテーション

Size: px

Start display at page:

Download "PowerPoint プレゼンテーション"

ゆゆここいたばし
5 years ago
Views:

1 Excel VBA の基本 2 はじめに前回のおさらい反復処理 (For...Next) ステップ実行参考文献の転記処理 VBA 関数 ( 引数と戻り値 ) 参考文献立山秀利入門者の ExcelVBA 講談社,

2 ( はじめに )Excel でこんなこともできるよ! 1 開発メニュー挿入から [ フォームコントロール ] [ スピンボタン ] を選択 2 ワークシート上でドラッグドロップする 3 作られたスピンボタンの上で右クリックし [ コントロールの書式設定 ] を選択 4 [ リンクするセル ] として $A$1 を選択 2

3 フォームコントロールと INDEX 関数 B1 セルに次の関数を入力する =INDEX(C1:C5, A1, 1) =INDEX(C:C,A1,1) でも良い 3

4 4

5 条件に応じた処理の例もしチェックに1が入力されていたら対応するB列のセルの値を対応するC列のセルに転記する Sub Test() If Range("A2").Value = 1 Then Range("C2").Value= Range("B2").Value End If End Sub 5

6 同じ処理を A3 から A6 にも行うには If Range("A2").Value = 1 Then Range("C2").Value= Range("B2 ).Value If Range("A3").Value = 1 Then Range("C3").Value= Range("B3 ).Value If Range("A4").Value = 1 Then Range("C4").Value= Range("B4 ).Value If Range("A5").Value = 1 Then Range("C5").Value= Range("B5 ).Value If Range("A6").Value = 1 Then Range("C6").Value= Range("B6 ).Value 変数と反復処理を活用することでスマートに ( より汎用的に ) 書くことができる 6

7 変数とはプログラミングにおいての変数とはプログラムのソースコードにおいて扱われるデータを一定期間記憶し必要なときに利用できるようにするためにデータに固有の名前を与えたもの値を入れる箱のようなもの A = A セルの値とセル番地セルの名前の関係 7

8 変数を体験してみる 1 Sub Test() A=10 Range( A1 ).Value = A A=A+10 Range( A2 ).Value = A End Sub A=A+10 は A に 10 を足したものを A に代入せよという意味 A に 10 を足せ 8

9 変数を体験してみる 2 Sub Test() ABC = はじめに MsgBox(ABC) ABC = 次に MsgBox(ABC) End Sub 9

10 反復処理 (For...Next) For...Next ステートメント ( 構文書き方のルール ) 指定した処理を指定した回数だけ繰り返して実行する書き方 For 変数名 = 初期値 to 最終値処理 Next 変数は繰り返しの回数の管理に用いる処理を繰り返すたびに値が 1 ずつ増える 10

11 反復処理サンプル 1 Sub Test() For a = 1 To 3 MsgBox a Next End Sub 実行したいプロシージャにカーソルをあわせて実行ボタンをクリックしても実行できる 11

12 反復処理サンプル 2 Sub Test() For a = 1 To 5 Cells(a, 1).Value = a+10 Next End Sub Cells オブジェクト Range と同様にセルを表すオブジェクト位置をセル番地ではなく ( 行の位置列の位置 ) で表す 12

13 反復処理 p.3 の例を反復処理で書き直す Range 版 Sub Test() For cnt = 2 to 6 Next End Sub If Range( A & cnt).value = 1 Then Range("C & cnt).value=range("b" & cnt).value End If cnt & カウンタを表す変数名としてよく? 利用される左右の二つを文字として結合する 13

14 反復処理 p.3 の例を反復処理で書き直す Cells 版 Sub Test() For cnt = 2 to 6 Next End Sub If Cells(cnt,1).Value = 1 Then Cells(cnt,3).Value = Cells(cnt,2).Value End If 14

15 ステップ実行 :1 行ずつ結果を確かめながら実行できる 1 マクロの実行ダイアログからステップイン 2 F8 キーを押すたびに 1 行ずつ実行される 3 ワークシートのウィンドウと VBA のウィンドウを並べて表示するとわかりやすい 4 リセットボタンで停止する 15

16 ウォッチ : 変数の変化を見ることができる変化を見たい変数 ( 今回は cnt) の上で右クリックしてウォッチ式の追加を選択式とプロシージャを確認して OK ステップ実行をするとウォッチウィンドウに指定した変数の値が表示される 16

17 オブジェクトの中のオブジェクトを指定する Worksheets( Sheet1 ).Range("A1 ).Font.Size = 14 Sheet1 ワークシートのセルA1 のフォントのプロパティ Size を 14に設定せよヘルプの表示調べたい対象にカーソルを合わせて F1 を押す F1 17

18 演習 ( 参考文献より ) 転記処理よりファイルを入手実現したい処理の内容 [ 売上 ] ワークシートの B3 セル ( 顧客名 ) を [ 請求書 ] ワークシート A3 セルに転記する [ 売上 ] ワークシートの各項目について顧客名が B3 セルに一致するものを [ 請求書 ] ワークシートに転記する 18

19 どのようなアルゴリズムにすればよいか? アルゴリズム問題を解決するための方法や手順のこと 1 [ 売上 ] ワークシートの [B6] から下に向かって [B3] セルと一致するかどうか確認する 2 一致したらその行を [ 請求書 ] に転記するアルゴリズムのイメージ B6 B23 を調べるもし B3と一致したらそのときはその行を転記する 19

20 その行を転記する? 1 行目元の8 行目 2 行目元の10 行目 3 行目元の16 行目もし B3 と一致したらそのときはその行を転記する転記先の行は 1,2,3 cnt 20

21 転記先の行数を管理する変数を追加 rw を 1 とする B3 と一致した 8 行目を rw 行目に転記 rw に 1 を足す (=rw を 2 とする ) B3 と一致した 10 行目を rw 行目に転記 rw に 1 を足す (=rw を 3 とする ) B3 と一致した 16 行目を rw 行目に転記 rw に 1 を足す (=rw を 4 とする ) 21

22 作成するマクロ転記先は 7 行目からスタート ( 請求書ワークシート A7 から ) rw =7 から Sub 請求書作成 () Worksheets(" 請求書 ").Range("A3").Value = Range("B3").Value rw = 7 For cnt = 6 To 23 If Range("B" & cnt).value = Range("B3").Value Then Worksheets(" 請求書 ").Range("A"& rw).value=range("a"& cnt).value (B~F 列までの転記処理を省略 : 次スライド ) rw = rw + 1 End If Next End Sub [ 売上 ] ワークシートでマクロを実行することを想定している ( ワークシート名が明示されていないオブジェクトは実行したワークシートがその対象となる ) 22

23 省略した転記処理 Worksheets(" 請求書 ").Range("A" & rw).value = Range("A" & cnt).value Worksheets(" 請求書 ").Range("B" & rw).value = Range("C" & cnt).value Worksheets(" 請求書 ").Range("C" & rw).value = Range("D" & cnt).value Worksheets(" 請求書 ").Range("D" & rw).value = Range("E" & cnt).value Worksheets(" 請求書 ").Range("E" & rw).value = Range("F" & cnt).value 顧客の列を省略しているため 1 列ずつずれることに注意 Cellsを利用して書く場合 Worksheets(" 請求書 ").Cells(rw,1).Value = Cells(cnt,1).Value Worksheets(" 請求書 ").Cells(rw,2).Value = Cells(cnt,3).Value Worksheets(" 請求書 ").Cells(rw,3).Value = Cells(cnt,4).Value Worksheets(" 請求書 ").Cells(rw,4).Value = Cells(cnt,5).Value Worksheets(" 請求書 ").Cells(rw,5).Value = Cells(cnt,6).Value 23

24 ボタンの挿入とマクロの設定 1 マクロを実行したいワークシートを開く 2 開発メニュー挿入からフォームコントロールのボタンアイコンを選択 3 シート上でドラッグしボタンを作成する 4 一覧から登録するマクロ名を選んで OK 24

25 何かおかしい? 同じ音羽金属でも結果が異なることがある? 前に実行した結果を消していないのが原因なので Worksheets(" 請求書 ").Range("A7:E13").ClearContents を最初に ( 転記処理の前に ) 実行する 25

26 関数かんすう与えられた文字や数値に対し定められた処理を行って結果を返す機能のこと表計算ソフトやデータベースソフトプログラミング言語などで利用される関数ごとにさまざまな処理が割り当てられておりたとえばExcel でSUMという関数を用いると指定した範囲の合計が求められる与える情報を引数ひきすう結果を戻り値と呼ぶ複数の数値 SUM 関数合計値 26

27 ワークシート関数と VBA 関数ワークシート関数はワークシートで使うもの VBA 関数は VBA で記述するものワークシート関数 VBA 関数同じ機能を持つものも多くあるワークシートで事前に書いておくか VBA 内で書くかは用途による引数の指定方法が違う場合もある 27

28 VBA 関数の一覧 28

29 関数の構文使い方の説明 F1を押すと出てくる公式のヘルプ初心者にはわかりづらい 29

30 引数の指定方法引数の指定方法はいくつかパターンがあるので使用例を見て真似するとよい例 :Left 関数の引数は ( 文字列数値 ) Left(" たのしい VBA",4) Left(Range( A1 ),cnt) cnt は数値を代入した変数省略できる引数もある戻り値を使わない場合は括弧を省略する名前付き引数といったものも 30

31 戻り値戻り値のある関数は変数やセルの値として結果を代入する Moji = Left(" たのしい VBA",4) Range("B1").Value = Left(Range("A1"),cnt) 31

32 MsgBox関数より 32

33 MsgBox 関数の色々な書き方 MsgBox " こんにちは " メッセージ以外の引数は省略戻り値を使わないのでカッコを省略 MsgBox Date Date 関数 ( 今日の日付を返す ) の戻り値を MsbBox 関数の引数としてそのまま利用 MsgBox(" 良いですか?", vbyesno, " 確認 ") YES と NO が表示されるタイトルは確認となる 33

34 Yes/No による条件分岐 yn = MsgBox(" 良いですか?", vbyesno, " 確認 ") If yn = vbyes Then Range("A1").Value = " はい " Else Range("A1").Value = " いいえ " End If 変数 yn を使わずに次のような書き方もできる If MsgBox(" 良いですか?", vbyesno, " 確認 ") = vbyes Then 34

35 名前付き引数 ( の指定 ) MsgBox " 文字です ", Title:=" タイトルです " 引数 Title を設定するという意味 InputBox 関数のように指定する引数の種類が多い場合に使われる 35

36 INPUTBOX 関数 InputBox(Prompt,Title, Default, XPos, YPos) kekka = InputBox( 入力してください, _ Default := 初期値, _ Xpos:=50) MsgBox kekka _( アンダーバー ) はその文が次の行に続く場合に書く 36

37 Excel VBA の基本 3 VBA 関数 ( 引数と戻り値 ): 前回資料変数のきちんとした定義方法オブジェクトに関する記述をまとめる (With) フォームコントロールの活用 37

38 変数のきちんとした定義 VBA では基本的にどこでも変数を定義できるオブジェクト名などの VBA で利用しない文字列であれば全部変数として扱うその文字列が最初に出てきた時点で VBA はそれを新たな変数として定義するスペルミスをした場合わからない場合がある hoge = 100 hoge = 250 hoga = 200 以降 hoge を変数として扱う値は 100 とする変数 hoge の値を 250 に変更する以降 hoga を変数として扱う値は 200 とする 38

39 Option Explicit と Dim Option Explicit マクロの一番上 ( 先頭の Sub より前 ) に書くことで宣言された変数以外は使えない ( 勝手に定義しない ) ようにすることができる Dim 文字列 (as 型 ) この文字列を変数として使う! と宣言する ( 型を指定するとその型以外の情報を代入できなくなる ) 39

40 オブジェクトに関する記述をまとめる (With) 同じオブジェクト名についての処理が続くときにオブジェクト名を省略することができる書き方 With オブジェクト名. から始まるものに自動的にオブジェクト名が付けられる End With 例 With Range( A1 ).Value = セル A1.Font.Size = 15 End With わかりやすい例 :Office TANAKA With って何ですか? 40

41 転記処理の完成版を読み解いてみる ( 立山秀利入門者の ExcelVBA 講談社,2012. より ) Option Explicit Sub 請求書作成 () Dim cnt Dim rw With Worksheets(" 請求書 ").Range("A3").Value = Range("B3").Value.Range("E3").Value = Date rw = 7.Range("A7:E13").ClearContents For cnt = 6 To 23 Next If Range("B" & cnt).value = Range("B3").Value Then End If End With End Sub.Range("A" & rw).value = Range("A" & cnt).value.range("b" & rw).value = Range("C" & cnt).value.range("c" & rw).value = Range("D" & cnt).value.range("d" & rw).value = Range("E" & cnt).value.range("e" & rw).value = Range("F" & cnt).value rw = rw + 1 変数の宣言 Date は日付を取得する VBA 関数先頭にピリオドがついたものは請求書ワークシートに対しての処理ついていないものは今開いているワークシートに対しての処理 41

42 Excel の発展的な機能フォームコントロールユーザーフォームフォームを作成して VBA 内部から呼び出すことができるイベント処理ユーザーが何か操作を行ったときに処理を行うことができるファイルを開いたときボタンをクリックしたときなどなど 42

43 フォームコントロールを使ってみるスピンボタンでの例 1 開発メニュー挿入から [ フォームコントロール ] [ スピンボタン ] を選択 2 ワークシート上でドラッグドロップする 3 作られたスピンボタンの上で右クリックし [ コントロールの書式設定 ] を選択 4 [ リンクするセル ] として $A$1 を選択 43

44 セルの参照とは =B3 はセル B3 を参照せよという意味セル B3 に入っている値や数式などを持ってこいセル番地を直接指定しない方法もある 44

45 関数によるセルの参照 INDEX: セル範囲行番号列番号で指定 =INDEX(A1:D4, 3, 2) 範囲 A1:D4 の 3 行目 2 列目を参照 OFFSET: 基準位置下への移動数右への移動数で指定 =OFFSET(A1, 2, 1) A1 から下に 2 右に 1 移動したセルを参照 ( 高さと幅を指定することで範囲を返すこともできる ) INDIRCT: 文字列で指定 =INDIRECT(F1) F1 に書かれた文字列で指定されたセルを参照 ( セルの名前を指定することもできる ) 45

46 フォームコントロールと INDEX 関数 B1 セルに次の関数を入力する =INDEX(C1:C5, A1, 1) =INDEX(C:C,A1,1) でも良い 46

47 リストボックス ( コンボボックス ) の中身を動的に変更したい場合企業名ボックスを変更したときに部署名ボックスの中身を変えたい次のようなデータを準備する 47

48 部署名ボックスの入力範囲の指定 B2~B5 を企業名ボックスで指定した企業にあわせて (INDEX 関数によって ) 変更する 48

49 企業名ボックスの設定と INDEX 関数の引数 A2,A3 ではなく 1,2 でも良い 0 を表示したくない場合は IF 関数を併用するなど C1 の値 (= 企業名ボックスの選択 ) によって INDEX の指す場所が変わっていく 49

50 VBA についてのまとめ的にプログラム ( アルゴリズム ) とは人の書いたものをモノマネしていくことで学んでいくものきちんと動かない場合間違った部分 ( バグ ) を直していく作業が必要最も時間がかかる作業コツコツと学んでいきましょう! 50

PowerPoint プレゼンテーション

PowerPoint プレゼンテーション Excel VBA の基本 1 VBA Visual Basic for Applications Office シリーズに搭載されているプログラミング言語マクロを作成するために使われる 1 プログラミングとは人間の意図した処理を行うようにコンピュータに指示を与えることセル A1 の内容をセル B1 にコピーしなさいセル A1 の背景色を赤色にしなさいあれをしなさいこれをしなさい上から順番に実行