Excelのヒント：列の値に基づいてデータを複数のワークシート/ワークブックに分割する

Excelで大規模なデータセットを管理する際、特定の列の値に基づいてデータを複数のワークシートに分割することが非常に有益です。この方法は、データの整理だけでなく、読みやすさも向上させ、データ分析を容易にします。

たとえば、製品名や第1四半期の販売数量などの複数の項目を含む大規模な販売記録があるとします。ここでの目標は、このデータを各製品名ごとに別々のワークシートに分割し、個々の販売実績を個別に分析できるようにすることです。

列の値に基づいてデータを複数のワークシートに分割する

• VBAコードを使用して
• 強力な機能-Kutools for Excelを使用して

VBAコードを使用して列の値に基づいてデータを複数のワークブックに分割する

列の値に基づいてデータを複数のワークシートに分割する

通常、まずデータリストを並べ替え、それから新しいワークシートに一つずつコピーして貼り付けることができます。しかし、これには繰り返しの作業が必要となり、忍耐が必要です。このセクションでは、時間を節約し、エラーの可能性を減らすために、Excelでこのタスクを効率的に処理するための2つの簡単な方法を紹介します。

VBAコードを使用して列の値に基づいてデータを複数のワークシートに分割する

1. ALT + F11キーを押してMicrosoft Visual Basic for Applicationsウィンドウを開きます。

2. 「挿入」>「モジュール」をクリックし、モジュールウィンドウに次のコードを貼り付けます。

Sub Splitdatabycol()
'updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
Dim xWS As Worksheet
On Error Resume Next
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
Else
Sheets("xTRgWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
End If
Set xWSTRg = Sheets("xTRgWs_Sheet")
xTRg.Copy
xWSTRg.Paste Destination:=xWSTRg.Range("A1")
ws.Activate
For i = (titlerow + xTRg.Rows.Count) To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
xWS.Name = myarr(i) & ""
Else
xWS.Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
xWS.Paste Destination:=xWS.Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy xWS.Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
ws.AutoFilterMode = False
ws.Activate
Application.DisplayAlerts = True
End Sub

3. そして、 F5 キーを押してコードを実行すると、ヘッダ行を選択するように促すダイアログボックスが表示されます。その後、「OK」をクリックします。スクリーンショットをご覧ください: OKスクリーンショットをご覧ください:

4. 2番目のプロンプトボックスで、分割したい列データを選択し、「OK」をクリックします。 OKスクリーンショットをご覧ください:

5. アクティブなワークシート内のすべてのデータは、列の値に基づいて複数のワークシートに分割されます。結果として得られるワークシートは、分割されたセルの値に基づいて名前が付けられ、ワークブックの最後に配置されます。スクリーンショットをご覧ください:

Kutools for Excelを使用して列の値に基づいてデータを複数のワークシートに分割する

Kutools for Excelは、スマートな機能である「Split Data」をExcel環境に直接導入します。データを複数のワークシートに分割することはもはや難題ではありません。直感的なツールが、選択した列の値または行数に基づいてデータセットを自動的に分割し、必要な場所に正確に情報を配置します。手動でスプレッドシートを整理する煩わしい作業にさよならを言い、より速く、エラーのないデータ管理方法を取り入れましょう。

Kutools for Excelをインストール後、データ範囲を選択し、「Kutools Plus」>「Split Data」をクリックして「データを複数のワークシートに分割」ダイアログボックスを開きます。

1. 「 Split based on」セクションで「Specific column」オプションを選択し、ドロップダウンリストから分割基準となる列の値を選択します。
2. データにヘッダーがあり、それを各新規分割ワークシートに挿入したい場合は、「My data has headers」オプションをチェックしてください。（データに基づいてヘッダー行数を指定できます。例えば、データに2つのヘッダーがある場合、2を入力してください。）
3. 次に、新しいワークシート名を指定します。「New worksheets name」セクションで、「Rules」ドロップダウンリストからワークシート名のルールを指定し、必要に応じて接頭辞や接尾辞を追加することもできます。
4. 「 OK 」ボタンをクリックします。スクリーンショットをご覧ください:
   

これで、ワークシート内のデータが新しいワークブック内の複数のワークシートに分割されました。

VBAコードを使用して列の値に基づいてデータを複数のワークブックに分割する

時折、データを複数のワークシートに分割する代わりに、重要な列に基づいてデータを個別のワークブックに分割することがより有益な場合があります。以下は、特定の列の値に基づいてデータを複数のワークブックに分割するプロセスを自動化するためにVBAコードを使用するステップバイステップガイドです。

1. ALT + F11キーを押してMicrosoft Visual Basic for Applicationsウィンドウを開きます。

2. 「挿入」>「モジュール」をクリックし、モジュールウィンドウに次のコードを貼り付けます。

Sub SplitDataByColToWorkbooks()
    ' Updateby Extendoffice
    Dim lr As Long
    Dim ws As Worksheet
    Dim vcol, i As Integer
    Dim myarr As Variant
    Dim title As String
    Dim titlerow As Integer
    Dim xTRg As Range
    Dim xVRg As Range
    Dim xWS As Workbook
    Dim savePath As String
    ' Set the directory to save new workbooks
    savePath = "C:\Users\AddinsVM001\Desktop\multiple files\" ' Modify this path as needed
    Application.DisplayAlerts = False
    Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", Type:=8)
    If TypeName(xTRg) = "Nothing" Then Exit Sub
    Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", Type:=8)
    If TypeName(xVRg) = "Nothing" Then Exit Sub
    vcol = xVRg.Column
    Set ws = xTRg.Worksheet
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = xTRg.Address(False, False)
    titlerow = xTRg.Row
    ws.Columns(vcol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Cells(1, ws.Columns.Count), Unique:=True
    myarr = Application.Transpose(ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).Value)
    ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).ClearContents
    For i = 2 To UBound(myarr)
        Set xWS = Workbooks.Add
        ws.Range(title).AutoFilter Field:=vcol, Criteria1:=myarr(i)
        ws.Range("A" & titlerow & ":A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy
        xWS.Sheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteAll
        xWS.SaveAs Filename:=savePath & myarr(i) & ".xlsx"

        xWS.Close SaveChanges:=False
    Next i
    ws.AutoFilterMode = False
    Application.DisplayAlerts = True
    ws.Activate
End Sub

3. そして、 F5 キーを押してコードを実行すると、ヘッダ行を選択するように促すダイアログボックスが表示されます。その後、「OK」をクリックします。スクリーンショットをご覧ください: OKスクリーンショットをご覧ください:

4. 2番目のプロンプトボックスで、分割したい列データを選択し、「OK」をクリックします。 OKスクリーンショットをご覧ください:

5. 分割後、アクティブなワークシート内のすべてのデータは列の値に基づいて複数のワークブックに分割されます。すべての分割されたワークブックは、指定したフォルダに保存されます。スクリーンショットをご覧ください:

関連記事: