複数行で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
元のデータの範囲を格納するオブジェクト変数「範囲」をオブジェクト型(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件のデータベースに変更する方法についての解説を終了します。ありがとうございました。