Excel のヒント: 列の値に基づいてデータを複数のワークシート/ワークブックに分割する
Excel で大規模なデータセットを管理する場合、特定の列の値に基づいてデータを複数のワークシートに分割することが非常に有益です。この方法により、データの編成が改善されるだけでなく、可読性が向上し、データ分析が容易になります。
製品名や第 1 四半期の販売数量などの複数のエントリを含む大規模な販売記録があるとします。目標は、このデータを各製品名に基づいて個別のワークシートに分割し、個々の販売実績を個別に分析できるようにすることです。
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 Sub3 次にを押します F5 キーを押してコードを実行すると、ヘッダー行を選択することを促すプロンプト ボックスが表示され、クリックします。 OK。 スクリーンショットを参照してください：
4. XNUMX 番目のプロンプト ボックスで、分割する列データを選択し、クリックします。 OK。 スクリーンショットを参照してください：
5. アクティブなワークシート内のすべてのデータは、列の値に基づいて複数のワークシートに分割されます。結果として得られるワークシートには、分割セルの値に従って名前が付けられ、ワークブックの最後に配置されます。スクリーンショットを参照してください:
Kutools for Excelを使用して列の値に基づいてデータを複数のワークシートに分割します
Kutools for Excel スマートな機能をもたらします – 分割データ Excel 環境に直接導入できます。データを複数のワークシートに分割することはもう難しいことではありません。当社の直感的なツールは、選択した列値または行数に基づいてデータセットを自動的に分割し、各情報が必要な場所に正確に配置されるようにします。スプレッドシートを手動で整理するという面倒な作業に別れを告げ、より速く、エラーのないデータ管理方法を採用してください。
インストールした後 Kutools for Excelをクリックし、データ範囲を選択して、 クツールズプラス > 分割データ を開く データを複数のワークシートに分割する ダイアログボックス。
- 選択 特定の列 内のオプション に基づいて分割 セクションに移動し、データを分割する基準となる列の値をドロップダウン リストから選択します。
- データにヘッダーがあり、それらを新しい分割ワークシートのそれぞれに挿入したい場合は、チェックを入れてください。 私のデータにヘッダーがあります オプション。 (データに基づいてヘッダー行の数を指定できます。たとえば、データに 2 つのヘッダーが含まれる場合は、「XNUMX」と入力します。)
- 次に、分割ワークシート名を下で指定できます。 新しいワークシート名 セクションで、「ルール」ドロップダウン リストからワークシート名ルールを指定すると、 接頭辞 or サフィックス シート名にも。
- クリック 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. 分割後、アクティブなワークシート内のすべてのデータが列の値に基づいて複数のワークブックに分割されます。すべての分割ワークブックは、指定したフォルダーに保存されます。スクリーンショットを参照してください:
関連記事：
- 行数でデータを複数のワークシートに分割
- 特定の行数に基づいて大規模なデータ範囲を複数の Excel ワークシートに効率的に分割すると、データ管理を合理化できます。たとえば、データセットを 5 行ごとに複数のシートに分割すると、データセットをより管理しやすく、整理することができます。このガイドでは、このタスクを迅速かつ簡単に実行するための XNUMX つの実用的な方法を紹介します。
- キー列に基づいて 2 つ以上のテーブルを 1 つにマージします
- ブックにXNUMXつのテーブルがあるとすると、次に示すスクリーンショットのように、対応するキー列に基づいてこれらのテーブルをXNUMXつのテーブルにマージし、結果を取得します。 これは私たちのほとんどにとって厄介な作業かもしれませんが、心配しないでください、この記事では、この問題を解決するためのいくつかの方法を紹介します。
- テキスト文字列を区切り文字で複数の行に分割する
- 通常、Text to Column 機能を使用して、コンマ、ドット、セミコロン、スラッシュなどの特定の区切り文字でセルの内容を複数の列に分割できます。ただし、区切りセルの内容を複数の行に分割する必要がある場合もあります。下のスクリーンショットに示すように、他の列のデータを繰り返します。 このタスクを Excel で処理するための良い方法はありますか? このチュートリアルでは、Excel でこのジョブを完了するための効果的な方法を紹介します。
- 複数行のセルの内容を別々の行/列に分割します。
- Alt + Enterで区切られた複数行のセルコンテンツがあり、複数行のコンテンツを分離された行または列に分割する必要がある場合、何ができますか？ この記事では、複数行のセルの内容を別々の行または列にすばやく分割する方法を学習します。
最高のオフィス生産性向上ツール
| 🤖 | Kutools AI アシスタント: 以下に基づいてデータ分析に革命をもたらします。 インテリジェントな実行 | コードを生成 | カスタム数式の作成 | データを分析してグラフを生成する | Kutools関数を呼び出す... |
| 人気の機能: 重複を検索、強調表示、または識別する | 空白行を削除する | データを失わずに列またはセルを結合する | 数式なしのラウンド ... | |
| スーパールックアップ: 複数の基準の VLookup | 複数の値の VLookup | 複数のシートにわたる VLookup | ファジールックアップ .... | |
| 詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する | 依存関係のドロップダウン リスト | 複数選択のドロップダウンリスト .... | |
| 列マネージャー: 特定の数の列を追加する | 列の移動 | Toggle 非表示列の表示ステータス | 範囲と列の比較 ... | |
| 注目の機能: グリッドフォーカス | デザインビュー | ビッグフォーミュラバー | ワークブックとシートマネージャー | リソースライブラリ (自動テキスト) | 日付ピッカー | ワークシートを組み合わせる | セルの暗号化/復号化 | リストごとにメールを送信する | スーパーフィルター | 特殊フィルター (太字/斜体/取り消し線をフィルター...) ... | |
| 上位 15 のツールセット: 12 テキスト ツール (テキストを追加, 文字を削除する、...） | 50+ チャート 種類 (ガントチャート、...） | 40+ 実用的 式 (誕生日に基づいて年齢を計算する、...） | 19 挿入 ツール (QRコードを挿入, パスから画像を挿入、...） | 12 変換 ツール (数字から言葉へ, 通貨の換算、...） | 7 マージ＆スプリット ツール (高度な結合行, 分割セル、...） | ... もっと |
Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。 最も必要な機能を入手するにはここをクリックしてください...
Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作業をはるかに簡単にします
- Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
- 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
- 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
