Excel VBA 複数行で1件のデータを1行で1件のデータベースに変更する

スポンサーリンク
スポンサーリンク

複数行で1件のデータを1行で1件のデータベースに変更する方法

1件のデータはレコード単位(行単位)で管理するのが基本ですが
他のソフトから読み込んだデータが
複数行で1件のデータになってる場合等があります。
そのような場合、Excelでデータベースとして扱えるように
1件のデータをレコード単位で管理するデータベース形式に変更する
必要があります。

例として
「コード」
「名前」
「住所」
「電話番号」
の4つの項目の1件のデータが
1列に繰り返し入力されているデータ形式を
Excelで利用できるように
1件のデータを1行(レコード)で管理する
データベース形式に変更します。

まずは、1件のデータがいくつの行に入力されているか
を調べる必要がありますので
Aplication【アプリケーション】オブジェクトの
InputBox【インプットボックス】メソッドを使用して
ユーザーに1件のデータ行数を教えてもらいます。
そのデータの行数を元に
データ行数分の繰り返し処理の中
選択セル範囲の位置を変更する
Range【レンジ】オブジェクトの
Offset【オフセット】プロパティを使用して
縦方向の1件のデータを行方向に転記します。

実行イメージ


コード例

Sub 形式変換()
 Dim 範囲 As Range
 Dim 行数 As Variant
 Dim i As Long
戻る:
  行数 = Application.InputBox("1件のデータ行数を入力してください。", Type:=2)
 If 行数 = 0 Then Exit Sub
 If 行数 = "" Then
    MsgBox "行数を入力してください"
  GoTo 戻る
 End If
 Set 範囲 = Range("A1").CurrentRegion
 If 範囲.Rows.Count Mod 行数 <> 0 Then
    MsgBox "選択範囲と行数が一致しません。"
  Exit Sub
 End If
    Worksheets.Add before:=Sheets(1)
    Range("A2").Select
 For i = 0 To 範囲.Rows.Count - 1
    ActiveCell.Offset(Int(i / 行数), i Mod 行数).Value = 範囲.Cells(i + 1).Value
 Next i
    Cells.EntireColumn.AutoFit
End Sub

コードの解説

2行目
【 Dim 範囲 As Range】

元のデータの範囲を格納する
オブジェクト変数「範囲」を
オブジェクト型Rnageで宣言します。


3行目【Dim 行数 As Variant】
InputBox【インプットボックス】メソッドで
ユーザーから受け取った
1件のデータの行数を格納する
変数「行数」を
バリアント型Variantで宣言します。
InputBox【インプットボックス】メソッドで
受け取る値は数値ですが
ユーザーがなにも入力しないで
OKボタンを押したとき返される
長さ0文字の文字列「””」も格納したいので
すべてのデータ型が格納できる
バリアント型で宣言します。


4行目
【 Dim i As Long】

繰り返し処理で使用する
データ範囲の行番号を順次格納する
カウンター変数「i」を
長整数型Longで宣言します。


5行目
【戻る:】

InputBoxにユーザーがなにも入力しないで
OKボタンを押したときに
GoTo【ゴートゥ】ステートメントで
この「戻る」ラベルまで
処理が戻ります。


6行目
【行数 = Application.InputBox(“1件のデータ行数を入力してください。”, Type:=2)】

Application【アプリケーション】オブジェクトの
InputBox【インプットボックス】メソッドを使用して
ユーザーに1件のデータの行数を入力してもらい
変数「行数」に代入します。


7行目
【If 行数 = 0 Then Exit Sub】

Ifステートメント(条件分岐)を使用して
変数「行数」の値が「0」だったときを定義して
条件が成立した場合
Exit【エグジット】ステートメントで
Sub【サブ】プロシージャーを途中で終了します。
Application【アプリケーション】オブジェクトの
InputBox【インプットボックス】メソッドは
キャンセルボタンが押されると
False(0)が返されます。
つまり、キャンセルボタンが押されたとき
処理を中断して終了することを定義します。


8行目
【If 行数 = “” Then】
さらにIfステートメントで条件分岐をします。
変数「行数」の値が「長さ0文字の文字列」だった場合を定義します。
つまり、InputBoxを未入力でOKボタンを押した場合


9行目~10行目
【MsgBox “行数を入力してください”
GoTo 戻る】
MsgBox【メッセージボックス】関数を使用して
ユーザにメッセージを出し
GoTo【ゴートゥ】ステートメントで
処理を「戻る」ラベルまで戻します。
つまり、最初の行数を入力するInputBoxを再表示させます。


12行目
【Set 範囲 = Range(“A1”).CurrentRegion】

Range【レンジ】オブジェクトの
CurrentRegion【カレントリジョン】プロパティを使用して
元データの連続したデータ範囲を取得して
オブジェクト変数「範囲」に
Setキーワードを使用してデータ範囲を代入します。


13行目
【If 範囲.Rows.Count Mod 行数 <> 0 Then】

Ifキーワードを使用して条件分岐します。
Range【レンジ】オブジェクトの
行を表すRows【ロウズ】プロパティで参照した
データ範囲の行をCount【カウント】プロパティで行数を参照し
Mod【モッド】演算子を使用して
データ範囲の行数/ユーザが入力した1件のデータ行数の余りを求めて
比較演算子の「<>」で余りが0ではない場合を定義します。


14行目~15行目
【MsgBox “選択範囲と行数が一致しません。”
Exit Sub】

条件が成立した場合
つまり、データ範囲の行数がユーザーが入力した1件のデータ行数
の倍数ではない場合(割り切れない)場合
MsgBox関数でメッセージをだして
Exit【エグジット】ステートメントで
Sub【サブ】プロシージャーを途中で終了します。


17行目
【 Worksheets.Add before:=Sheets(1)】

Worksheets【ワークシーツ】コレクションの
Add【アド】メソッドを使用して
新しいワークシートを追加します。


18行目
【Range(“A2”).Select】

追加した新しいワークシートのA2セルを選択します。
シートを追加した場合
追加したシートがアクティブになります。


19行目
【For i = 0 To 範囲.Rows.Count – 1】

For Next【フォーネクスト】ステートメントを使用して
繰り返し処理の始まりです。
繰り返し処理の中でカウンター変数「i」に
0~データ範囲の行数-1の値を代入します。


20行目
【ActiveCell.Offset(Int(i / 行数), i Mod 行数).Value = 範囲.Cells(i + 1).Value】

この処理で元データから新しいシートに列方向のデータを
行方向に転記します。
Range【レンジ】オブジェクトの
Offset【オフセット】プロパティを使用して
選択したA2セルを基準として
選択セル範囲の位置を変更して
データ範囲のデータを転記します。
Offset(移動する行数,移動する列数)なので
「移動する行数」には
小数点以下を切り捨てる
Int【イント】関数を使用して
カウンター変数「i」/1件のデータ行数の整数部分
「移動する列数」には
割り算の余りを求めるMod【モッド】演算子を使用して
カウンター変数「i」/1件のデータ行数の余りを設定します。
わかりやすいように
元データの1件のデータ行数が4行だった場合を例にして
値を当てはめてみます。
1回目
Offset(Int(0/4)=0,0Mod4=0) 移動無しA2セル
2回目
Offset(Int(1/4)=0,1Mod4=1) 列方向に1移動B2セル
3回目
Offset(Int(2/4)=0,2Mod4=2) 列方向に2移動C2
3回目
Offset(Int(3/4)=0,3Mod4=3) 列方向に3移動D2セル
4回目
Offset(Int(4/4)=1,4Mod4=0) 行方向に1移動A3セル
5回目
Offset(Int(5/4)=1,5Mod4=0) 行方向に1列方向に1移動B3セル
という形で繰り返し処理の中でカウンター変数「i」の値を
1ステップづつ変化させて
複数行で1件のデータを1行で1件のデータに転記します。


21行目
【Next i】

19行目からここまでの処理を
元データの行数の回数分繰り返します。


22行目
【 Cells.EntireColumn.AutoFit】

Range【レンジ】オブジェクトの
EntireColumn【エンターカラム】プロパティ
すべての列を参照して
Range【レンジ】オブジェクトの
AutoFit【オートフィット】メソッドを使用して
列幅を入力されている値の幅に合わせます。


以上で
複数行で1件のデータを1行で
1件のデータベースに変更する方法についての
解説を終了します。
ありがとうございました。

スポンサーリンク
スポンサーリンク

フォローする

スポンサーリンク
スポンサーリンク