今回はExcel VBAの配列機能について、基本的な使い方とどういったケースで便利なのかを解説します。
配列とは何か
配列というのは、複数のデータを格納するための箱のようなものです。配列を理解するために、まずは変数の説明をします。変数というのは、データを格納するための箱です。整数、文字列など、ある特定の1つのデータを格納することができます。
それに対して配列というのは、複数のデータを格納することができます。以下のように、箱が連なっているイメージです。それぞれの箱には0から始まる番号が振られています。
その箱に、データを1つずつ、もしくは纏めて格納します。以下では０の箱に”リンゴ”を、1の箱に”いちご”を、2の箱に”バナナ”を入れています。
こうして完成した配列から、今度はデータを抽出することもできます。例えば以下では、０の箱から”リンゴ”を、1の箱から”いちご”を、2の箱から”バナナ”を抽出しています。これが、配列の基本的な機能です。
配列を作る(Array関数)
配列を使う際には、配列を返すArray関数を使用します。例えば、リンゴ・いちご・バナナを配列として設定するには以下のように関数を設定します。
Array(“リンゴ”,”いちご”,”バナナ”)
これを、Variant型の変数に格納することで使いやすくします。例えばfruitsという変数に入れてみましょう。
Dim fruits As Variant
fruits = Array(“リンゴ”,”いちご”,”バナナ”)
配列からデータを取り出す
上で設定した配列からデータを取り出してみます。方法は、”配列名(位置)”です。例えば上の配列からリンゴを取り出すときは以下のように記述します。
fruits(0)
配列の便利な使い方
さて、ここまでは一般的な配列の説明をしてきましたが、著者を含めて初めてこの説明を聞いた人は、”これ何に使うん？”という疑問を持つはずです。実際著者も、配列を使うメリットを感じるケースに遭遇するまでは、何度説明を聞いても頭に入ってきませんでした。
そこで以下では、配列のメリットと、それを体感できるような使い方を2つ紹介します。
配列のメリット
配列のメリットは何と言っても、コードが簡単に書けて見やすくなることと、処理の時間が早くなることです。特に初心者の内はコードが煩雑になるのを防ぐうえで非常に有用です。以下ではコードの点に焦点を絞って説明します。
ケース１
以下のように、国内の地域名とそのコードの表を作成することを想定します。
表の作成だけであれば手作業でいいと感じると思いますが、複雑な実務では、これらの表からXLOOKUP関数/VLOOKUP関数を用いて別表に引用するような作業が発生します。こういった実務をVBAで自動化する際には、毎回表を自動で生成するようなプロセスが必要になりえるのです。
上図の表を、1つずつ手打ちする感覚でコード化すると、以下のように冗長になります。
Cells(1, 1) = “地域名”
Cells(2, 1) = “北海道”
Cells(3, 1) = “東北”
Cells(4, 1) = “関東”
Cells(5, 1) = “東京”
Cells(6, 1) = “甲信越”
Cells(7, 1) = “北陸”
Cells(8, 1) = “東海”
Cells(9, 1) = “京阪神”
Cells(10, 1) = “近畿”
Cells(11, 1) = “中国”
Cells(12, 1) = “四国”
Cells(13, 1) = “九州”
Cells(14, 1) = “沖縄”
Cells(1, 2) = “コード”
Cells(2, 2) = “HKD”
Cells(3, 2) = “THK”
Cells(4, 2) = “KNT”
Cells(5, 2) = “TKY”
Cells(6, 2) = “KSE”
Cells(7, 2) = “HKR”
Cells(8, 2) = “TKI”
Cells(9, 2) = “KHS”
Cells(10, 2) = “KIK”
Cells(11, 2) = “CHG”
Cells(12, 2) = “SKK”
Cells(13, 2) = “KSH”
Cells(14, 2) = “OKW”
これを配列を用いて記述すると以下のように書けます。
Dim area_name As Variant, area_code As Variant, i As Integer
area_name = Array(“地域名”, “北海道”, “東北”, “関東”, “東京”, “甲信越”, “北陸”, “東海”, “京阪神”, “近畿”, “中国”, “四国”, “九州”, “沖縄”)
area_code = Array(“コード”, “HKD”, “THK”, “KNT”, “TKY”, “KSE”, “HKR”, “TKI”, “KHS”, “KIK”, “CHG”, “SKK”, “KSH”, “OKW”)
For i = 1 To 14
Cells(i, 1) = area_name(i – 1)
Cells(i, 2) = area_code(i – 1)
Next
上記のコードについて解説します。まず、配列を格納するためにarea_nameとarea_codeという変数を用意します。配列を扱うときはVariant型、つまりなんでもアリ型の変数を用意しましょう。次に、Arrayという関数を用いて、()内のデータを配列に変換します。A列の地域名に該当するデータを”area_name”に配列として格納し、B列のコードに該当するデータを”area_code”に配列として格納します。これで前準備は完了です。
次にFor分による繰り返しを応用して、配列から1つずつデータを取り出します。変数iは1To14、つまり1から14までの数字で変化するよう設定されています。最初はi＝1となり、続く処理内容は以下のようになります。
Cells(1, 1) = area_name(0)
Cells(1, 2) = area_code(0)
Cells(1, 1)はA1セル、Cells(1, 2)はB1セルです。area_name(0)というのは、”area_name”という配列の0番目のデータを意味します。配列は一番初めが0ですから、area_name(0)＝”地域名”となります。同様に、area_code(0)＝”コード”となります。
なおFor分による繰り返しについては以下の記事を参照ください。
ケース２
次に、以下のように担当者ごとの販売成績表を生成してみましょう。担当者名以外の部分はすべて同じデータ、担当者名だけは手入力するという仕様にしてみます。
コードは以下の通りです。
Sub test3()
Dim row As Variant, col As Variant, i As Integer, k As Integer
row = Array(“担当者：”, “令和3年度”, “リンゴ”, “いちご”, “バナナ”, “メロン”, “グレープフルーツ”, “ぶどう”, “マンゴー”)
col = Array(“4月”, “5月”, “6月”, “7月”, “8月”, “9月”)
For k = 0 To 20 Step 10
For i = 0 To 8
Cells(i + 2 + k, 1) = row(i)
Next
For i = 0 To 5
Cells(3 + k, i + 2) = col(i)
Next
Cells(2 + k, 2).Value = InputBox(“担当者の名前を入力してください”)
Next
End Sub
順に解説します。まず、以下の部分で今回使用する変数を宣言します。配列として使うのは、行方向の配列用に”row”、列方向の配列用に”col”を用意しました。iとkは後半でFor文の繰り返しに使用します。
Dim row As Variant, col As Variant, i As Integer, k As Integer
次に、2つの配列を設定し、準備しておいた変数に格納します。
row = Array(“担当者：”, “令和3年度”, “リンゴ”, “いちご”, “バナナ”, “メロン”, “グレープフルーツ”, “ぶどう”, “マンゴー”)
col = Array(“4月”, “5月”, “6月”, “7月”, “8月”, “9月”)
次にFor文による繰り返し処理を設定します。2つの変数を用いた入れ子(ネスト)構造によって効率的にデータを入力します。変数kは、0,10,20の2つの値を取ります。
For k = 0 To 20 Step 10
For i = 0 To 8
処理の中身としては、以下の2文が該当します。
Cells(i + 2 + k, 1) = row(i)
Cells(3 + k, i + 2) = col(i)
次に、InputBoxを使って、担当者の名前を任意に設定できるようにします。
Cells(2 + k, 2).Value = InputBox(“担当者の名前を入力してください”)
仮に、名前を”田中”->”鈴木”->”佐藤”と入力すると、以下のような実行結果となります。
コメント