スポンサーリンク
VBAを使った別ブックからのデータ転記の際に使えるステートメント、関数、配列について紹介します。
前回記事はこちら↓
今回は、私が実務でよく行っているGoogleフォームのダウンロードデータの転記作業を事例に下記のうち配列、関数を使ってデータを加工して、結合したデータをカンマ区切りでセルに代入する方法を解説していきます。
■データ転記に使用するステートメント・関数・配列
サンプルデータを使ってコードを確認したい方はこちら
Googleフォームのダウンロードデータ(転記元ファイル)からExcelの規定フォームへの転記
上図の赤枠の転記元データを例に説明すると、転記元データは「回答番号」と「回答文言」が;(セミコロン)で区切られています。
Dim tmp
Dim x2 As Long
x2 = 0
'======問4======
If DLファイル.Sheets(1).Cells(r, "E").Value <> "" Then '問4が空欄でなければ(条件)
'=====複数回答時の処理=====
If InStr(DLファイル.Sheets(1).Cells(r, "E"), ";") >= 1 Then
tmp = Split(DLファイル.Sheets(1).Cells(r, "E"), ";")
Do While x2 <= UBound(tmp)
If Mid(tmp(x2), 2, 1) = "." Then '左から2文字にピリオドがあれば(=数字であれば)
tmp(x2) = Left(tmp(x2), 1)
Else
.Cells(inp行, CNo回答.問4_1) = tmp(x2)
tmp(x2) = 6 'その他の番号
End If
x2 = x2 + 1
Loop
.Cells(inp行, CNo回答.問4) = Join(tmp, ",") '回答欄へ転記
Else '==単数回答時の処理==
If Mid(DLファイル.Sheets(1).Cells(r, "E"), 2, 1) = "." Then '左から2文字にピリオドがあれば(=数字であれば)
.Cells(inp行, CNo回答.問4) = Left(DLファイル.Sheets(1).Cells(r, "E"), 1)
Else
.Cells(inp行, CNo回答.問4) = 6 'その他の番号
.Cells(inp行, CNo回答.問4_1) = DLファイル.Sheets(1).Cells(r, "E").Value
End If
End If
End If
If InStr(DLファイル.Sheets(1).Cells(r, "E"), ";") >= 1 Then
tmp = Split(DLファイル.Sheets(1).Cells(r, "E"), ";")
Do While x2 <= UBound(tmp)
If Mid(tmp(x2), 2, 1) = "." Then '左から2文字にピリオドがあれば(=数字であれば)
tmp(x2) = Left(tmp(x2), 1)
Else
.Cells(inp行, CNo回答.問4_1) = tmp(x2)
tmp(x2) = 6 'その他の番号
End If
x2 = x2 + 1
Loop
.Cells(inp行, CNo回答.問4) = Join(tmp, ",") '回答欄へ転記
Instr関数・・Instr(対象となる文字列、検索する文字列)
Instr関数は、検索する文字列が対象となる文字列の先頭から検索して、何文字目にあるかの文字位置を返す。
Split関数・・Split(文字列、区切り文字)
Spit関数は、配列を返します。よってバリアント型変数とセットで使って、文字列を区切り文字ごとに配列変数※に格納します。例)tmp = Split(文字列,区切り文字) ※tmpはバリアント型変数
※配列変数とは
・通常の変数は1つの変数に1つしか値が入りませんが、複数の値が入る変数
・複数の値を整理するために、要素数が振られる※要素数は通常「0」から始まる
先程のSplit関数を踏まえて例を挙げると、セルA1に「A、B、C、D」が入っていた場合、
tmp = Split(cells(1,1).value,”、”) とすると、バリアント型変数tmpは以下のようになります。
tmp(0):「A」
tmp(1):「B」
tmp(2):「C」
tmp(3):「D」
配列変数に回答を格納します。
If InStr(DLファイル.Sheets(1).Cells(r, “E”), “;”) >= 1 Then
Cells(r, “E”)の中に;(セミコロン)が1個以上あれば(複数回答の場合)=複数回答か単数回答かの条件分岐をします。
複数回答ならば、、、
tmp = Split(DLファイル.Sheets(1).Cells(r, “E”), “;”)
バリアント型変数Tmpに「;」で区切られた回答が格納されます。
Ubound関数/Lbound関数・・Ubound(配列変数の名前)/Lbound(配列変数の名前)
Ubound関数・・配列で使用できる添え字(要素数)の最大値を整数値で返します。
Lbound関数・・配列で使用できる添え字(要素数)の最小値を整数値で返します。
例)tmp(5)の場合、Lbound(tmp)とすれば「5」を返します。
MID関数・・(対象文字列、文字列を抜き出す先頭位置、抜き出す文字列の長さ【省略可】)
MID関数は、対象文字列の先頭位置から指定した長さの文字列を抜き出します。
例)MID(”ABCDE”,2,3)とすれば、ABCDEの左から2文字目から3文字抜き出して”BCD”を返します。※第3引数の「3」を省略した場合は、Bから全ての文字を抜き出します。
LEFT関数・・(対象文字列、抜き出す文字数)
LEFT関数は、対象文字列の左端から指定文字数を抜き出します。
例)LEFT(”ABCDE”,2)とすれば、ABCDEの左から2文字抜き出して”AB”を返します。
回答番号のみ配列変数に格納し直します。
ただ、先ほどのローカルウィンドウで見ていただいた通り、その他回答は番号がないので番号がある場合と無い場合(通常回答かその他回答で)条件分岐しています。
Do While x2 <= UBound(tmp)
配列変数tmpの要素数分ループします。
If Mid(tmp(x2), 2, 1) = “.” Then
左から2文字にピリオドがあれば(=数字であれば)
tmp(x2) = Left(tmp(x2), 1)
配列変数tmpに回答番号のみを格納し直します。
Else左端が数字ではなければ(その他回答の場合)
.Cells(inp行, CNo回答.問4_1) = tmp(x2)
その他回答列にtmpに格納されている値をセルに代入します。
tmp(x2) = 6 ‘その他の番号
tmpには「その他回答番号」として「6」を入れます。
End If
x2 = x2 + 1
Loop
Do While~Loopステートメントについてはこちら↓
Join関数・・Join(文字列型配列変数、区切り文字)
Join関数は、配列変数に入っている値を指定の区切り文字で結合します。
.Cells(inp行, CNo回答.問4) = Join(tmp, “,”)
tmpに入っている値をJoin関数を使ってセルに代入します。
筆者オススメのVBAの本はこちら↓↓