Excelで列の値を基準にシートを複数のシートへ分割する5つの方法

列の値を基準にExcelシートを複数のシートに分割したいとお探しの方に、この記事が役立ちます。

大量のデータを特定の列ごとに分割し、メインシートを分けた後にそれぞれのシートで作業を行いたい場面は少なくありません。この作業を効率的に行うための方法を、詳しく見ていきましょう。

ワークブックのダウンロード

この記事で使用するサンプルファイルは、以下からダウンロードできます。

列の値を基準にExcelシートを複数のシートに分割する5つの方法

ここでは、ある大学の学生の成績結果を含む次のようなデータテーブルを使用します。「Student Name(学生名)」列を基準に、3名の学生ごとのシートへ分割します。

なお、この記事ではMicrosoft Excel 365を使用していますが、お使いのバージョンでも同様の手順で実行できます。

方法1:FILTER関数を使ってシートを分割する

「Student Name」列を基準にデータシートを複数のシートに分割したい場合は、FILTER関数を活用できます。ここでは、シートをDaniel Defoe、Henry Jackson、Donald Paulの3名のデータを含む3つのシートに分割します。

ステップ1:
➤3人の学生の名前を付けた3つのシートを作成します。
➤Daniel Defoe用のシートでB3などのセルを選択します。

➤以下の数式を入力します。

=FILTER(Filter!B5:D16,Filter!B5:B16="Daniel Defoe")

Filter!B5:D16はメインシート(Filter)のヘッダー行を除くデータ範囲、Filter!B5:B16はメインシートのStudent Name列の範囲であり、その値が「Daniel Defoe」と一致する行を抽出するという意味です。

➤Enterキーを押します。
すると、Daniel Defoeのデータがこの学生用のシートに表示されます。

➤最後に、各列の上部に見出しを入力し、データテーブルに罫線を設定して見やすく整えましょう。

ステップ2:
残りのHenry JacksonとDonald Paul用の2つのシートでもステップ1と同じ手順を実行すると、それぞれのシートに以下のような表が作成されます。

関連記事: Excel VBAで行を基準にシートを複数のシートへ分割する方法

方法2:ピボットテーブルを使ってシートを分割する

ピボットテーブルを使えば、「Student Name」列を基準に、3名の学生ごとの3つのシートへ簡単に分割できます。

ステップ1:
➤挿入タブ >> ピボットテーブルを選択します。

すると「テーブルまたは範囲からピボットテーブルを作成」ダイアログボックスが表示されます。
➤テーブル/範囲を選択します。
➤新しいワークシートを選択します(ピボットテーブルは新しいシートに配置するのがおすすめです)。
➤OKをクリックします。

新しいシートが開き、PivotTable1とピボットテーブルフィールドの2つの領域が表示されます。

➤Student Name(分割の基準となる任意の列)をフィルターエリアにドラッグし、SubjectとGradeを行エリアにドラッグします。

➤デザインタブ >> レポートのレイアウトドロップダウン >> アウトライン形式で表示を選択します。

➤続けてデザインタブ >> 集計ドロップダウン >> 行と列の集計を表示しないを選択します。

➤次に、ピボットテーブル分析タブ >> オプションドロップダウン >> レポートフィルターページを表示を選択します。

「レポートフィルターページを表示」ウィザードが表示されます。
➤フィルターエリアにあるStudent Name列を選択します。
➤OKをクリックします。

結果:
これで、Daniel Defoe、Henry Jackson、Donald Paulの3名の学生ごとに、別々のシートが自動的に作成されます。

関連記事: 行を基準にExcelシートを複数のシートへ分割する方法

方法3:テーブル機能を使う

「Student Name」列を基準にメインシートを複数のシートへ分割するには、テーブル機能を利用することもできます。

ステップ1:
➤3人の学生の名前を付けた3つのシートを作成します。
➤メインシートからデータテーブルをコピーし、3つのシートにそれぞれ貼り付けます。

ステップ2:
➤挿入タブ >> テーブルを選択します。

「テーブルの作成」ダイアログボックスが表示されます。
➤テーブルにするデータ範囲を選択します。
➤先頭行をテーブルの見出しとして使用するにチェックを入れます。
➤OKをクリックします。

以下のようなテーブルが作成されます。

➤テーブルデザインタブ >> スライサーの挿入を選択します。

「スライサーの挿入」ダイアログボックスが表示されます。
➤Student Name列(分割の基準となる列)を選択します。
➤OKをクリックします。

すると、3つの選択肢(3人の学生名)を持つStudent Nameボックスが表示されます。

➤このシートではDaniel Defoeをクリックします。

結果:
Daniel Defoeのデータだけが、この学生用のシートに表示されます。

ステップ3:
➤他の2つのシートでもステップ2と同じ手順を繰り返します。
こうして、Henry JacksonとDonald Paul用のシートも以下のように作成できます。

関連記事: Excelシートを複数のファイルに分割する3つの手軽な方法

あわせて読みたい

• Excelで画面を分割する3つの方法
• [解決法] Excelの「並べて比較」が動作しないときの対処法
• Excelで縦方向の並べて表示を有効にする方法

方法4:フィルター機能を使う

この方法では、「Student Name」列を基準にメインシートを分割するために、フィルター機能を使用します。

ステップ1:
➤3人の学生の名前を付けた3つのシートを作成します。
➤メインシートからデータテーブルをコピーし、3つのシートにそれぞれ貼り付けます。

ステップ2:
➤データテーブルを選択します。
➤データタブ >> フィルターを選択します。

データテーブルにフィルターが適用されます。
➤Student Name列のドロップダウンボタンをクリックします。

➤このシートではDaniel Defoeのみにチェックを入れ、OKをクリックします。

結果:
これで、Daniel Defoeのデータだけがこの学生用のシートに表示されます。

ステップ3:
➤他の2つのシートでもステップ2と同じ手順を繰り返します。
すると、Henry JacksonとDonald Paul用のシートも以下のように完成します。

関連記事: Excelでシートを分ける6つの効果的な方法

方法5:VBAコードを使ってシートを分割する

VBAコードを使えば、列の値を基準にシートを複数のシートへ一括で分割することも可能です。

ステップ1:
➤開発タブ >> Visual Basicを選択します。

Visual Basic Editorが開きます。
➤挿入タブ >> 標準モジュールを選択します。

新しいモジュールが作成されます。

ステップ2:

➤以下のコードを記述します。

Sub Splitsheet()
Dim lr As Long
Dim sheet 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
On Error Resume Next
Set xTRg = Application.InputBox("Select the header row:", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox _
("Select the column on the basis of which split data:", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set sheet = xTRg.Worksheet
lr = sheet.Cells(sheet.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = sheet.Columns.Count
sheet.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")
sheet.Activate
For i = (titlerow + xTRg.Rows.Count) To lr
On Error Resume Next
If sheet.Cells(i, vcol) <> "" And Application.WorksheetFunction. _
Match(ws.Cells(i, vcol), sheet.Columns(icol), 0) = 0 Then
sheet.Cells(sheet.Rows.Count, icol).End(xlUp).Offset(1) = sheet.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(sheet.Columns(icol). _
SpecialCells(xlCellTypeConstants))
sheet.Columns(icol).Clear
For i = 2 To UBound(myarr)
sheet.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
Sheets(myarr(i) & "").Paste Destination:=Sheets(myarr(i) & "").Range("A1")
sheet.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr) _
.EntireRow.Copy Sheets(myarr(i) & "").Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
sheet.AutoFilterMode = False
sheet.Activate
Application.DisplayAlerts = True
End Sub

ここでSplitsheet()はSubプロシージャの名前であり、変数lr、sheet、vcol、i、icol、myarr、title、titlerow、xTRg、xVRg、xWSTRgがDim(Dimension)ステートメントによって異なるデータ型で宣言されています。

さらに、シートを複数のシートへ分割するために、複数のIf文とForループが使用されています。

➤F5キーを押します。

「Select the header row:(見出し行を選択してください)」というダイアログボックスが開きます。
➤見出し行の範囲を選択し、OKをクリックします。

次に「Select the column on the basis of which split data:(分割の基準となる列を選択してください)」というウィザードが表示されます。
➤Student Name列を選択し、OKをクリックします。

結果:
最終的に、Daniel Defoe、Henry Jackson、Donald Paulの3名の学生ごとのシートが以下のように作成されます。

なお、このコードでは貼り付け先をA1セルに指定しているため、分割されたデータはA1セルから始まります。

関連記事: VBAコードでワークブックを個別のExcelファイルに分割する方法

練習セクション

ご自身で練習できるよう、Practiceという名前のシートに以下のような練習セクションを用意しています。ぜひ実際に試してみてください。

まとめ

この記事では、Excelで列の値を基準にシートを複数のシートへ効率的に分割する、最も簡単な方法をご紹介しました。皆さんの業務にお役立ていただければ幸いです。ご意見やご質問があれば、ぜひお気軽にお寄せください。

さらに学ぶ

• Excelでシートを別のワークブックに分割する4つの方法
• 2つのExcelファイルを別々に開く5つの簡単な方法
• 複数のExcelファイルを1つのワークブックで開く4つの方法
• Excelのシートを別ウィンドウで表示する4つの方法