100行ごとにデータ分割して別シートへコピー
マクロ講座75回
大量のデータを指定行数ごとに分割して別シートに分ける作業をします。
100行ごとに別シートへコピー Excel2013 マクロ講座 75回
指定行数ごとに別シートにコピーする
今日、紹介するのは、データベースのように大量のデータが記載されているシートを、指定数ごと(仮に100行ごと)に分割して別シートにコピーするマクロです。
データがあまりにも大量にあると、シートも長くし、とても見づらくなりますね。
サンプルでは500件のデータを100件ごとに分割して、同じブック内の別シートにコピーするというマクロを作ります。
(サンプルファイルは、こちらから マクロ75回サンプルデータ)
膨大なデータの分割、タイトル行なしの場合
下図のようにタイトル行がない場合は、100行ごとに区切ってコピーする場合のコピー回数は、総行数を100で割れば求められます。
もし余りが1行でも出たら、コピー回数に+1します。
通常、場合分けをしたい場合、If~Then~Elseステートメントを使いますが、今回はコードを簡略にしたいのでInt関数とIIf関数を使うことにしましょう。
Int 関数は、引数に指定した数値の整数部を返すので、Int(データの総行数 / コピー行)で、コピーする回数を求めることができます。さらに IIf関数では、行数にあまりが出た場合に、コピー回数を1回増やすという処理をさせます。
Int 関数は、引数に指定した数値の整数部を返すので、Int(データの総行数 / コピー行)で、コピーする回数を求めることができます。さらに IIf関数では、行数にあまりが出た場合に、コピー回数を1回増やすという処理をさせます。
Int関数とIIf関数を使って、操作する回数を求めるコードを作ります。
変数としては、総行数、回数が必要です。
また分割する行数をコピー行と名前で宣言していますが、これは定数ですのでDimステートメントではなく、Constステートメントで宣言しています。
コピー行の数値を変更することで汎用的に利用できます。
Dim 総行数 As Long, 回数 As Long Const コピー行 = 100 回数 = Int(総行数 / コピー行) + IIf(総行数 Mod コピー行 > 0, 1, 0)
IIf 関数
IIf 関数構文
式の評価に基づいて、2 つの部分のいずれかを返します。
見間違いではありません。If関数の前にIがついて、IIf関数です。シート関数のIF版がIIf関数だと思って大丈夫です。
IIf(expr, truepart, falsepart)
引数truepartには、評価した式がTrueのときの値または、式を記述し、
引数Falsepartには、評価した式がFalseのとき値または式を記述します。
引数 | 説明 |
---|---|
expr | 必須です。 評価する式。 |
truepart | 必須です。exprがTrue の場合に返される値または式。 |
falsepart | 必須です。exprがFalseの場合に返される値または式。 |
上記コードでコピー回数がわかりますから、あとはワークシートを追加Sheets.Addして、それぞれのシートにデータ元があるシートからコピーして貼り付け
にはCopyメソッドを利用すればいいのです。
Range.Copy メソッド
名前 | データ型 | 説明 |
---|---|---|
Destination | Variant | コピー先のセル範囲。 この引数を省略すると、クリップボードにコピーされます。 |
100行ごとに追加シートへコピー
コピー回数分処理を繰り返すには、For~Nextステートメントを使います。
Sub データを100行ごとに分割する() Dim シート As Worksheet, 元 As Worksheet '元は元データのあるシート Dim 総行数 As Long, 回数 As Long, i As Long, 開始行 As Long Const コピー行 = 100 Set 元 = ActiveSheet '変数の元をActiveSheetにセットする 総行数 = 元.UsedRange.Rows.Count 回数 = Int(総行数 / コピー行) + IIf(総行数 Mod コピー行 > 0, 1, 0) 開始行 = 1 For i = 1 To 回数 Set シート = Sheets.Add シート.Name = 開始行 & "~" & 開始行 + コピー行 - 1 元.Rows(開始行 & ":" & 開始行 + コピー行 - 1).Copy シート.Range("A1") Columns("A:F").AutoFit 開始行 = 開始行 + コピー行 Next i End Sub指定行数をコピーして新規シートに貼り付けるマクロの結果です。
実務では、データを分割して複数シートに分けてチェックするなどということも、けっこうあるので便利なマクロだと思います。
次は、データの1行めにタイトル(見出し)行があり、それを分割したシートにもそれぞれコピーして表示させたい時はどうするかというマクロコードです。
次は、データの1行めにタイトル(見出し)行があり、それを分割したシートにもそれぞれコピーして表示させたい時はどうするかというマクロコードです。