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

Excel で大規模なデータセットを管理する場合、特定の列の値に基づいてデータを複数のワークシートに分割することが非常に有益です。この方法により、データの編成が改善されるだけでなく、可読性が向上し、データ分析が容易になります。

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

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

• VBAコードを使用する
• 強力な機能 Kutools for Excel を使用することで

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

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

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

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

1。 押さえつける Alt + F11 キーを押して アプリケーション向け Microsoft Visual Basic 窓。

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。 スクリーンショットを参照してください：

4. XNUMX 番目のプロンプト ボックスで、分割する列データを選択し、クリックします。 OK。 スクリーンショットを参照してください：

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

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

Kutools for Excel スマートな機能をもたらします – 分割データ Excel 環境に直接導入できます。データを複数のワークシートに分割することはもう難しいことではありません。当社の直感的なツールは、選択した列値または行数に基づいてデータセットを自動的に分割し、各情報が必要な場所に正確に配置されるようにします。スプレッドシートを手動で整理するという面倒な作業に別れを告げ、より速く、エラーのないデータ管理方法を採用してください。

インストールした後 Kutools for Excelをクリックし、データ範囲を選択して、 クツールズプラス > 分割データ を開く データを複数のワークシートに分割する ダイアログボックス。

1. 選択 特定の列 内のオプション に基づいて分割 セクションに移動し、データを分割する基準となる列の値をドロップダウン リストから選択します。
2. データにヘッダーがあり、それらを新しい分割ワークシートのそれぞれに挿入したい場合は、チェックを入れてください。 私のデータにヘッダーがあります オプション。 (データに基づいてヘッダー行の数を指定できます。たとえば、データに 2 つのヘッダーが含まれる場合は、「XNUMX」と入力します。)
3. 次に、分割ワークシート名を下で指定できます。 新しいワークシート名 セクションで、「ルール」ドロップダウン リストからワークシート名ルールを指定すると、 接頭辞 or サフィックス シート名にも。
4. クリック OK ボタン。 スクリーンショットを参照してください：
   

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

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

場合によっては、データを複数のワークシートに分割するよりも、キー列に基づいてデータを個別のワークブックに分割する方が有益な場合があります。ここでは、VBA コードを使用して、特定の列値に基づいてデータを複数のワークブックに分割するプロセスを自動化する方法についてのステップバイステップ ガイドを示します。

1。 押さえつける Alt + F11 キーを押して アプリケーション向け Microsoft Visual Basic 窓。

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。 スクリーンショットを参照してください：

4. XNUMX 番目のプロンプト ボックスで、分割する列データを選択し、クリックします。 OK。 スクリーンショットを参照してください：

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

関連記事：