教科書原稿

Size: px

Start display at page:

Download "教科書原稿"

きのこよしくに
5 years ago
Views:

第章配列変数とその宣言変数の基本的な使い方は第章で登場しました変数は処理の途中の値を一時的に保存して, プログラム内で, 他の処理を行いたいときに使ってきましたつの変数には一時的にはつのデータだけ保存できますこの章では, 同じ目的で利用する複数の変数をまとめて扱うことのできるデータ構造の基本的な使い方について学びます VBA ではそのようなデータ構造を配列 (Array)

1 第章配列変数とその宣言変数の基本的な使い方は第章で登場しました変数は処理の途中の値を一時的に保存して, プログラム内で, 他の処理を行いたいときに使ってきましたつの変数には一時的にはつのデータだけ保存できますこの章では, 同じ目的で利用する複数の変数をまとめて扱うことのできるデータ構造の基本的な使い方について学びます VBA ではそのようなデータ構造を配列 (Array) と呼びますまず, 配列とは何かを理解するために, 変数を使って多数の文字列の中からある特定の文字列が何番目にあるかを探すマクロを作成し, その後に同じ問題について配列を使ってマクロを書き直します基本課題ワークシートに参考図 ( 表学生点数表 ) のように多数の学生の名前が順に並んでいる表があり, その表から特定の学生名 ( 例えば, 田中 ) が何番目にあるかを答えるマクロを,Sub プロシージャとして名前 Sub 学生名検索 () で作成します < 表学生点数表 > 探したい名前を B セルに入力し, 答えの番号を含め # 番目ですと C セルに出力するマクロは以下のようになりますデータ数は任意とし, 仮に最大値を N=00 としています qname を探したい文字列,sname を学生名とし,Do While~Loop 構文でセルからデータをつずつ入力し, 条件 qname<>sname を判定するという反復処理を記述しています qname に等しい sname が見つかったとき, 反復を終了しますそのときの変数 i の値が答えになります一方, この構文では,qname に等しい sname が見つからない (qname<>sname) 場合は反復が終了しない, つまりこのプログラムが暴走する危険性があるので, カウンタ i に条件 i>n を与えて, 行目の If~Then 構文と Exit Do 構文で強制終了しています反復のループから抜けるステートメント Do While~Loop 構文 Exit Do 他の Do ~Loop 構文 Exit Do For~Next 構文 Exit For Sub 学生名検索 () Worksheets("Sheet").Activate Dim sname, qname As String Dim i, N, ans As Integer /

2 Range("C").ClearContents qname = Range("B") N = 00 ' 最大のデータ数 ( ループの強制終了 ) i = 0 0 Do While sname <> qname i = i + sname = Range("B" & (i + )) If i > N Then Exit Do Loop ans = i Range("C") = ans & " 番目 " 注 : から行目は Range("C") = i & " 番目 " と書けばつの文にまとまり, 変数 ans の使用と宣言が不要になる上のプログラムのように, セルからデータをつずつ入力し, 条件判定するという反復処理の書き方では, セルからデータをつずつ入力する処理が遅いため効率的ではありません大量のデータを処理する場合には, まず外部記憶からデータの入力処理を行い, それらをコンピュータのメモリに格納しておき, 次に目的の処理を行い, すべてが終了したらデータを外部記憶に戻す, というように処理を分けて行いますでは次に,N 人の学生名が N 個の変数 sna~snan に格納されているとき, 特定の名前の番号を探すマクロプログラムを考えて見ます大きな N ではコードが長くなるので N= としておきます僅か人でも長いコードになりますが, 次に説明する配列を利用すると解決します条件分岐に Select Case 構文を使って, マクロは次の Sub プロシージャ学生名検索 () のようになりますこの問題では他の条件分岐の構文を使うとコードはより長く, かつ見通しが悪くなります 0 Sub 学生名検索 () Worksheets("Sheet").Activate Dim sna, sna, sna, sna, sna, sna, sna, _ sna, qname As String Dim i, ans As Integer sna = " 小林 ": sna = " 斉藤 ": sna = " 佐々木 ": sna = " 佐藤 ": sna = " 鈴木 ": sna = " 高橋 ": sna = " 田中 ": sna = " 長谷川 " Range("C").ClearContents qname = Range("B") 検索したい名前 Select Case qname Case sna ans = Case sna: ans = Case sna: ans = Case sna: ans = Case sna: ans = Case sna: ans = /

3 Case sna: ans = Case sna: ans = 0 Case Else: ans = 0 End Select Range("C") = ans & " 番目 " 注 : 行目は行目の継続行, 行目と行目はつの文から成り, 行 ~0 行はそれぞれつの文から成る 0 行目は見つからない場合の答えを 0 番目と表示するために用意される配列とは配列とは, 同じ種類の複数のデータをまとめて格納できるよう一連の記憶領域を確保する特別なデータ構造のことであり, 個々のデータの値の他に, データの個数 ( 要素数 ), 配列名およびデータ型を指... 定して利用します個々のデータ ( 要素 ) を配列要素といいますデータ型は普通の変数と同じ種類のものを利用できます配列の宣言の一般的な書き方は以下のとおりです (a)dim 配列名 ( 要素数 - ) As データ型 (b)dim 配列名 ( 下限値 To 上限値 ) As データ型配列名の規則は変数のそれと同じであり, 配列は普通の変数と殆ど同じですが, 少しだけ違います通常は (a) の書式で宣言し, 配列要素の個数を配列名のカッコの中に指定しますまた, 普通の変数は変数宣言を省略することが可能ですが,Option Explicit 文の有無によらず, 配列は必ず宣言してからでないと使用できません... 配列要素は配列名 ( 例えば A とする ) とインデックス番号 ( 要素番号 ) で, 配列名 ( インデックス番号 ), つまり A() のように指定します VBA でのインデックス番号は基本的には 0 番目から始まるので,A() は配列 A の番目の要素を意味しますしたがって, Dim A(0) As Integer と宣言した場合,A(0) から A(0) まで個の要素を整数型で利用すると宣言することになります (b) の書式では要素番号の下限値と上限値を指定します要素数は上限値 - 下限値 + になります例えば, Dim A(0 To 0) As Integer と書くと, 書式 (a) の Dim A(0) As Integer と同じ意味になります基本課題ここからは実際に配列を使ってみましょうまず, 以下のプログラムを見てください配列 season を Dim season() As String で宣言し, 文字列型の配列要素 season(0),season(),season(), season(),season() の領域を確保しています ~ 行目で 0 番目から番目までの配列要素に春, 夏, 秋, 冬の文字を代入しますここまでが配列宣言とデータ入力であり, 準備段階です Sub 配列の例 () Sheets("Sheet").Activate Cells.ClearContents /

4 0 Dim season() As String Dim i As Integer season(0) = " 春 " season() = " 夏 " season() = " 秋 " season() = " 冬 " For i = 0 To Cells(, i + ) = season(i) Next i このプログラムを実行すると,~ 行目の For~Next 構文により,A~D セルに春, 夏, 秋, 冬と入力されます配列要素は普通の変数と全く同じように使えます上の例のサブプロシージャの season(0) = " 春 " Cells(, i + ) = season(i) は配列 season の 0 番目の要素に, 春, という文字列を代入したり,season の i 番目の要素の値を Cells で指定したセルオブジェクトに代入しています普通の変数に代入したり, 参照したりするときの使い方と全く同じように, 配列は式の右辺と左辺で使えます配列の要素を 0 番目ではなく, 番目から始めるようにするには (b) の書式で宣言します例えば, 配列要素が ssn(),ssn(),ssn(),ssn(),ssn() からなる Single 型の配列 ssn を宣言するには Dim ssn( To ) As Single とします配列名 ssn の後ろのカッコの中に To というキーワードを使って, 要素番号の最初と最後を指定します Variant 型と Array 関数一般的な関数の利用については第章で扱いますが,Array 関数を使うと, 配列として宣言していない Variant 型の変数に, 配列データをまとめて格納することができますこのような Variant 変数は, 配列とまったく同じように扱われ, 要素番号を指定して個々の配列要素を参照することが可能ですただし,Array 関数を使用して作成した配列の要素番号は必ず 0 から始まりますまた, 次の配列の例 () の場合で言うと season() = 夏のように,Variant 型の変数に代入文でデータを与えると, エラーになります次の配列の例 () は, 上の配列の例 () と同じ内容を記述していますまず, 行目で変数 season を Variant 型の変数として宣言し, 行目で Array 関数を使用して,seasn(0)~season() に Array 関数の引数の順に値 " 春 ", " 夏 ", " 秋 ", " 冬 " を配列要素として格納していますこのように Array 関数を利用すると, 数十個程度のデータであれば数行でまとめて格納できるのでコードがかなりコンパクトになります /

5 0 Sub 配列の例 () Sheets("Sheet").Activate Cells.ClearContents Dim season As Variant Dim i As Integer season = Array(" 春 ", " 夏 ", " 秋 ", " 冬 ") For i = 0 To Cells(, i + ) = season(i) Next i 無効なインデックス番号配列を使うときに注意しなければいけないことがありますそれは, 存在しない配列要素をうっかり使ってしまうことです配列の場合は, 配列要素のデータの型と要素番号の両方についてエラーチェックが行われます例えば, 以下の例を見てください Sub 配列の例注意 () Dim a() As Single a() = 0. a(0) =. Range("A") = a() + a(0) このサブプロシージャを実行するとエラーが出て, 図のメッセージが表示されます理由は配列 a の宣言文では要素数 0 にしている ( つまり a の配列要素は a(0)~a() だけ ) のに,a(0) に. を代入するという文がありますそのため, 行目でインデックスが有効範囲にありませんというエラーがでますデバッグボタンを押すとエラーの箇所を確認できます VB エディタの画面で実行時エラーが発生した行に背景色 ( 標準設定の時は黄色 ) が付きます基本課題配列の説明と基本課題の理解を基にして, 基本課題の Sub 学生名検索 () を配列と Array 関数を使って書き直してみましょうデータ数は 0 個に増やしてあります Sub 学生名検索 () と較べると, 反復の中でデータをつずつ変数に格納する代わりに, ここではまとめて配列に格納してあるので, 条件判定の考え方はまったく同じでよいことが分かりますただし,sname(i) の要素番号 i は 0 /

6 から始まるので, 見つかったときのデータの番号 (A 列の No) は i+ が答えになります 0 Sub 学生名検索 () <Array 関数の利用 > Worksheets("Sheet").Activate Dim sname As Variant sname = Array(" 小林 ", " 斉藤 ", " 佐々木 ", " 佐藤 ", " 鈴木 ", " 高橋 ", _ " 田中 ", " 長谷川 ", " 山本 ", " 吉田 ") Dim qname As String Dim i, N, ans As Integer Range("C").ClearContents qname = Range("B") ' 検索したい名前 N = 00 ' 最大のデータ数 ( ループの強制終了 ) i = 0 Do While sname(i) <> qname i = i + If i > N Then Exit Do Loop ans = i + Range("C") = ans & " 番目 " 大量のデータを処理する場合には, 格納する順番を間違えたり継続行が増えるなど,Array 関数は使いにくくなりますそのような場合は, 準備段階として外部記憶からデータの入力処理を行い, それらをコンピュータのメモリに格納しておき, 次に目的の処理を行います Excel の VBA マクロでは, 外部記憶として, ワークシートに記入された表形式のデータを処理の対象にしますまず, 表学生点数表のデータを配列に格納する部分のコードについて説明します表では表の一部だけが表示されています基本課題やのようにレコード数を適当な大きな数に設定する方法ではなく, 実際のレコード数を調べるにはどうしたらよいでしょうか表の最終セルの行番号を調べる Excel のマウス操作で表の最終セルを調べる機能があります表の任意のセル ( 例えば B とする ) を選択し,[Ctrl] キー + キーを押すと, 表内で下方向に B 列の最終セルにジャンプし, 最終セルが選択されるここで言う最終セルとは, これ以降は空白セルが続くことを意味する同様に,[Ctrl] キー + キーを押すと表の上,[Ctrl] キー + キーを押すと表の右,[Ctrl] キー + キーを押すと表の左, にある最終セルにジャンプするこれらの操作に対応したコードは以下の通りです Range("B").Select Selection.End(xlDown).Select 表の下端へジャンプ Selection.End(xlUp).Select 表の上端へジャンプ Selection.End(xlToRight).Select 表の右端へジャンプ Selection.End(xlToLeft).Select 表の左端へジャンプこの End は,End モードとも呼ばれるもので,Range オブジェクトのプロパティで,Range オブジェクトを返します上のオブジェクト式は第章の対象. 命令の文型です /

表の範囲を調べて, 上下左右の端のセルのアドレスを知りたい ( 取得する ) 場合は, 以下のように, ジャンプして選択したセルの End モードに Row または Column を付けます Selection.End(xlUp).Row 表の上端の行番号を取得 Selection.End(xlDown).Row 表の下端の行番号を取得 Selection.End(xlToLeft).

7 表の範囲を調べて, 上下左右の端のセルのアドレスを知りたい ( 取得する ) 場合は, 以下のように, ジャンプして選択したセルの End モードに Row または Column を付けます Selection.End(xlUp).Row 表の上端の行番号を取得 Selection.End(xlDown).Row 表の下端の行番号を取得 Selection.End(xlToLeft).Column 表の左端の列番号を取得 Selection.End(xlToRight).Column 表の右端の列番号を取得この, Row と Column は,Range オブジェクトのプロパティで, その行位置と列位置を番号で返します上のオブジェクト式は対象. プロパティの文型です例題として, 表学生点数表の最終セルの行番号を調べ, そのセルの A 列と B 列のデータ値をメッセージボックスに表示する Sub プロシージャを示します MsgBox の文字列式にある vbcrlf は, 改行を表す VBA の定数で, この定数と文字列連結演算子を組み合わせることによってつのデータを行に表示しますこの結果, 最終セルの行番号はと判明します Sub 表の大きさ () Worksheets("Sheet").Activate Dim nr As Integer ' 最終セルの行番号 Range("B").Select nr = Selection.End(xlDown).Row MsgBox "No=" & nr & vbcrlf & Cells(nr, ) & "," & Cells(nr, ) 注 : Cr + Lf は Shift_JIS 文字コードで書かれた Windows のテキストデータの行末と改行を表す特殊な記号 ASCII 文字コードで表すと ( 進表記で ) 0D + 0A となる以上ですべての準備ができたので, ここで, 学生名検索 () を書き直し, ワークシートの表から大量のデータを配列に入力する処理をその後の処理と分けて, 基本課題と同じ問題を解決するマクロを作成しますマクロ名を学生名検索 () とします Sub 学生名検索 () ' < 表からデータを配列に一括入力 > Worksheets("Sheet").Activate Dim qname As String Dim sname(00) As String Dim i, N, ans As Integer Range("C").ClearContents qname = Range("B") ' 検索したい名前 Range("B").Select N = Selection.End(xlDown).Row - ''N= 最大のデータ数 0 For i = To N sname(i) = Range("B" & (i + )) /

Next i 0 For i = To N If sname(i) = qname Then ans = i Exit For End If Next i Range("C") = ans & " 番目 " 今回はデータ数 N が行目で与えられるので,0~ 行目で文字列型配列 sname(i) に学生名をすべて格納できますまた, 条件判定は最大 N

8 Next i 0 For i = To N If sname(i) = qname Then ans = i Exit For End If Next i Range("C") = ans & " 番目 " 今回はデータ数 N が行目で与えられるので,0~ 行目で文字列型配列 sname(i) に学生名をすべて格納できますまた, 条件判定は最大 N 回行えばよいことが分かっているので,if~Then 構文の中で sname(i) = qname という条件判定が成立したときの配列番号 i が答えになりますこのとき For~Next ループを続ける必要はないので, ループを途中で終了します For~Next ループから途中で抜けるには, 上の行のように Exit For 文を使用します発展課題次に配列を使ったプログラムをもう一つ見ましょう配列変数を使うことにより, ワークシート上にある大量のデータを組織的にまとめてプログラムに読み込んだり, 逆にワークシートのセルに書きだすことができます. 第章の Excel 教材 chap0array_sample.xlsm(ceas/sakai から ) をダウンロードして開きましょう Module には以下に示しているマクロプログラムが入力されていますワークシート Sheet の B:B には ~ 月の電気使用量を入力してありますさてこのサブプロシージャの xxx 部分を修正してから実行してみましょう実行に際しては, 事前に電気料金を知りたい月の数字を D セルに入れておいてください実行すると D セルに入力されてる月の電気料金がメッセージボックスに表示されます ( 下図 ) プログラムの中の重要な部分にはコメントを書いてあります /

9 Sub 電気料金照会 () Dim kihon As Single Dim tanka, tanka, tanka As xxx Dim m As Single kihon = 0 tanka =. tanka =. tanka =. ' 基本料金 : 北電, 契約 0A の場合 Dim shiyo( To ) As Single 0 Dim i As Integer For i = To shiyo(i) = Cells(xxx, xxx) Next xxx m = Range("D") Dim ryokin As Integer 0 If shiyo(m) <= xxx Then ryokin = kihon + shiyo(m) * tanka ElseIf shiyo(m) <= xxx Then ryokin = kihon + 0 * tanka + (shiyo(m) - 0) * tanka Else ryokin = kihon + 0 * tanka + (0-0) * tanka + (shiyo(m) - 0) * tanka End If MsgBox m & " 月の料金は " & xxx & " 円です " 発展課題発展課題と同じ教材を使いますワークシート Sheet には 00 人分の学生番号と氏名が入力してある E セルに氏名を知りたい人の学生番号を入力して実行すると, 以下のようにメッセージボックスにその学生番号の名前が表示されるプログラムを作りなさい /

10 以下のことを行うようにコードを書いていけば良い 0. ( データの入ってる Sheet をアクティベート ). 配列のサイズ ( 配列の要素の数 ) が 00 の String 型 ( 文字を入れるから, この型じゃないとダメ ) の配列を宣言する配列名は shimei としましょう. For Next を使って配列の各要素 shimei()~shime(00) に B~B0 の氏名を代入する Cells を使うこと. 氏名を知りたい人の学生番号を入れるための Integer 型 ( 学生番号は整数なので, この型 ) の変数を宣言する変数名は id としましょう. 上で宣言した変数 id に E セルに入ってる学生番号を代入. MsgBox を使って shime 配列の id 番目の配列要素を表示静的配列と動的配列... 静的配列とは, あらかじめ要素数を指定して宣言された配列で, マクロの実行中に要素数を変更することはできませんこの章のマクロではこれまではすべて静的配列を使っています基本課題の Sub 学生名検索 () では, せっかく事前にデータ数 N が分からなくても表の大きさから N の値を取得できるようになっているのに, 配列の宣言では Dim sname(00) As String と最大のデータ数の予測サイズ 00 以上を書く必要がありますこの点が気になりませんか... 動的配列とは, 要素数を指定せずに Dim 文で宣言しておいて, マクロの実行中に要素数を指定して再び ReDim ステートメントで宣言しながら使える配列です再宣言は何度でも許されますなお, ReDim 文を使うと, その時点で動的配列のすべての配列要素の内容はクリアされます次の例の行目は sname を文字列型の動的配列として宣言しています行目で N の値が確定するので,0 行目で ReDim 文を使って要素数を指定して再宣言しています Sub 動的配列の例 () Worksheets("Sheet").Activate 0 /

11 0 Dim qname As String Dim sname() As String ' 動的配列の宣言 Dim i, N, ans As Integer 以下, 途中省略 Range("B").Select N = Selection.End(xlDown).Row ''N= 最大のデータ数 ReDim sname(n) ' 動的配列の再宣言 For i = To N sname(i) = Range("B" & (i + )) Next i 以下, 途中省略は学生名検索 () と同じ配列の利用配列は多数の同じようなデータを一まとめにするときに用います反復処理を組織的に記述できる以外にも, データの検索をより効率的に行うとか, データを大小順に並べ替えるとか, 文章中の単語の出現頻度を数えるとか, 暗号で利用される大きな数の素数判定, などの問題を確実に解決する ( その処理... 手順をアルゴリズムという ) には配列の利用が必須になりますここでは第章の基本課題整列( ソート ) のマクロプログラムを配列を使って書き直してみます整列の対象となるデータを入れる配列は data としますデータ数 n に応じて扱えるように, 配列 data は動的配列として宣言していますデータの準備 : 整列 ( ソート ) のワークシート sorting の行目から行目のデータをこの章の基本課題を扱っているファイルの新規ワークシート (Sheet とします ) にコピーしておく第章では挿入法を理解してもらうために途中結果を, 順次, セルに書き出しましたが, ここでは整列後の結果だけを行目に出力しています 0 Sub ソート挿入法 () Dim n As Integer: n = 0 Dim data() As Integer ReDim data(n) Dim i, j, sentinel As Integer Range("C:C").ClearComments For j = To n ' 整列前のデータを行目から入力 data(j) = Cells(, j + ) Next j For i = To n sentinel = data(i) data(0) = sentinel ' 番兵を列外に置いておく j = i - /

12 0 Do While (sentinel < data(j)) data(j + ) = data(j) ' 右へずらす j = j - Loop data(j + ) = sentinel Next i For j = To n ' 整列後のデータを行目に出力 Cells(, j + ) = data(j) Next j 整列法の考え方 ( 第章のページの解説図 ) を記述しているコードは行目から行目までですここの部分にはセルの参照やセルへの代入など現れていない, つまり挿入法に関するコードとはこのようになるということが良く分かると思います /

PowerPoint プレゼンテーション

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