【Excel】エクセルで一覧表から個別シートを作成する方法
エクセルで顧客一覧、担当者別の売上一覧、部署別の進捗表などを管理していると、一覧表の内容を担当者や分類ごとの個別シートに分けたい場面があります。
元データをコピーしてシートを増やす作業を手作業で繰り返すと、件数が多いほど時間がかかり、転記漏れや更新忘れも起こりやすくなります。
そこで便利なのが、一覧表の見出しを基準にして個別シートを自動作成するVBA、またはFILTER関数を使って各シートへ該当データだけを表示する方法です。
一覧表から個別シートを作成する主な方法は、VBAでシートを自動生成する方法と、FILTER関数で元データを参照する方法です。
元データを配布用に分割したい場合はVBA、常に最新の一覧表を各シートへ反映したい場合はFILTER関数が向いています。
この記事では、1行目に見出しがある一覧表を使い、個別シートを効率よく作成する具体的な操作を解説します。
エクセルで一覧表から個別シートを作成する方法【VBAによる一括作成】
それではまず、一覧表の担当者名や部署名を基準に、個別シートをまとめて作成するVBAについて解説していきます。
| 担当者 | 日付 | 商品 | 売上 |
|---|---|---|---|
| 田中 | 2026/9/1 | 商品A | 12000 |
| 佐藤 | 2026/9/1 | 商品B | 8500 |
| 田中 | 2026/9/2 | 商品C | 15000 |
| 鈴木 | 2026/9/2 | 商品A | 9800 |
上のような一覧表が「一覧」シートにあり、A列の担当者ごとに田中、佐藤、鈴木という個別シートを作る例で進めます。
元データの表形式と見出しの確認
一覧表から分割する前に、データの形を整えておきましょう。
1行目には必ず見出しを置き、途中に空白行や結合セルを作らないことが重要です。
今回の例では、A1に担当者、B1に日付、C1に商品、D1に売上という見出しを入力します。
一覧表の途中に空白行があると、最終行を判断する処理が途中で止まることがあります。
また、シート名に使う担当者名には、半角スラッシュ、コロン、角かっこなど、Excelのシート名で使用できない文字を含めないようにします。
部署名や顧客名に使用禁止文字が入り得る場合は、マクロ側で置換する処理も検討しましょう。
作業用の一覧表は、1行目をヘッダー、2行目以降を明細データにする構成が基本です。
担当者など、個別シートの作成基準にする項目は、できれば表の左側に配置すると確認しやすくなります。
【操作のポイント】一覧表の1行目は見出し専用とし、データ範囲内の空白行、結合セル、重複した見出しを避けます。
開発タブとVisual Basicの表示
続いては、VBAを入力するためのVisual Basic Editorを開く操作を確認していきます。
Excelのリボンに開発タブが表示されている場合は、開発タブからVisual Basicを選択します。
開発タブが見当たらない場合は、ファイル、オプション、リボンのユーザー設定を開き、右側の一覧で開発にチェックを入れます。
ショートカットキーでは、Altキーを押しながらF11キーを押してもVisual Basic Editorを開けます。
VBAを保存するブックは、通常のxlsx形式ではなく、Excelマクロ有効ブックであるxlsm形式で保存してください。
xlsx形式のまま保存すると、作成したマクロが削除されてしまうため注意が必要です。
保存形式は、名前を付けて保存からExcelマクロ有効ブックを選択します。
元データの一覧シートを残したまま処理するため、作業前にブックの複製を保存しておくと安心です。
【操作のポイント】マクロを使うブックは、作成前または保存時にxlsm形式へ変更します。
個別シートを作成するマクロの実行
続いては、担当者ごとにデータを抽出し、個別シートを生成する処理を確認していきます。
Visual Basic Editorで対象ブックを右クリックし、挿入から標準モジュールを選択します。
標準モジュールへコードを貼り付けた後、Excel画面に戻って開発タブのマクロから実行できます。
初回は、同名のシートが存在する場合にどう扱うかを必ず確認しましょう。
既存シートを削除する設定のマクロは、必要なデータまで消してしまう可能性があります。
まずはコピーしたテスト用ブックで動作を確認し、シート名と抽出内容が正しいことを確かめるのがおすすめです。
【操作のポイント】本番データで実行する前に、ブックのバックアップを作り、少ない行数で試験実行します。
VBAコードによる担当者別シートの自動生成
続いては、一覧シートのA列を基準に担当者別のシートを作るVBAコードを確認していきます。
| 一覧シートの列 | 内容 | 処理での役割 |
|---|---|---|
| A列 | 担当者 | 個別シートの作成基準 |
| B列 | 日付 | 抽出して転記する項目 |
| C列 | 商品 | 抽出して転記する項目 |
| D列 | 売上 | 抽出して転記する項目 |
以下のコードは、一覧シートのA列にある担当者名を重複なく取得し、担当者ごとに新しいシートへ該当行をコピーします。
標準モジュールへ入力する基本コード
まずは、標準モジュールに次のコードを入力します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
Sub 担当者別シート作成() Dim ws一覧 As Worksheet Dim ws個別 As Worksheet Dim 最終行 As Long Dim 担当者範囲 As Range Dim セル As Range Dim 担当者名 As String Dim 辞書 As Object Dim キー As Variant Set ws一覧 = Worksheets("一覧") 最終行 = ws一覧.Cells(ws一覧.Rows.Count, "A").End(xlUp).Row Set 担当者範囲 = ws一覧.Range("A2:A" & 最終行) Set 辞書 = CreateObject("Scripting.Dictionary") For Each セル In 担当者範囲 担当者名 = Trim(セル.Value) If 担当者名 <> "" Then If Not 辞書.Exists(担当者名) Then 辞書.Add 担当者名, 担当者名 End If End If Next セル For Each キー In 辞書.Keys Set ws個別 = Worksheets.Add(After:=Worksheets(Worksheets.Count)) ws個別.Name = キー ws一覧.Rows(1).Copy ws個別.Rows(1) ws一覧.Range("A1:D" & 最終行).AutoFilter Field:=1, Criteria1:=キー ws一覧.Range("A2:D" & 最終行).SpecialCells(xlCellTypeVisible).Copy ws個別.Range("A2") ws一覧.AutoFilterMode = False ws個別.Columns("A:D").AutoFit Next キー End Sub |
このコードでは、一覧シートの名前を「一覧」とし、A列からD列までをコピー対象にしています。
実際の表で列数が異なる場合は、A1:DのDを最終列のアルファベットへ変更してください。
最終行は、Cells(Rows.Count, “A”).End(xlUp).Rowで取得しています。
これはA列の最も下にある入力済みセルから上方向へ移動し、最後のデータ行番号を求める書き方です。
【操作のポイント】一覧シート名と対象列の範囲は、実際のブックの構成に合わせて変更します。
重複を除いた担当者名の取得
続いては、同じ担当者名で複数のシートが作られない仕組みを確認していきます。
一覧表には田中という名前が複数行ありますが、田中シートは1枚だけ作成すれば十分です。
そこでコードではScripting.Dictionaryを利用し、A列に入力された名前を重複なしで記録しています。
辞書にまだ登録されていない担当者名だけを追加するため、明細行が何百行あっても同名シートが増えません。
セルの前後に不要な空白が含まれると、田中と田中のように見た目が同じでも別の名前として扱われる場合があります。
Trim関数は、文字列の先頭と末尾にある半角スペースを取り除く役割です。
表記ゆれも整理したい場合は、元の一覧表で担当者名を統一してからマクロを実行するとよいでしょう。
担当者名の入力規則を設定してプルダウンから選択できるようにすると、入力ミスや表記ゆれを減らせます。
【操作のポイント】個別シートの名前になる項目は、空白、表記ゆれ、使用禁止文字がないか事前に確認します。
フィルター抽出と書式の調整
続いては、担当者ごとの明細をシートへ転記する部分を確認していきます。
AutoFilterは、一覧表の見出し行を含む範囲へオートフィルターを設定し、A列が指定した担当者名と一致する行だけを表示する処理です。
Field:=1は、選択範囲の左端から数えて1番目の列、つまりA列を条件に使う指定です。
Criteria1:=キーでは、辞書から取り出した担当者名を抽出条件として渡しています。
抽出後にSpecialCells(xlCellTypeVisible)を使うことで、非表示行を除いた表示中のセルだけをコピーできます。
最後のColumns(“A:D”).AutoFitは、各列の幅を内容に応じて自動調整する命令です。
日付や金額の表示形式までそろえたい場合は、元データをテーブル形式に整え、転記後に表示形式を指定する処理を追加しましょう。
【操作のポイント】抽出後は必ずAutoFilterModeをFalseに戻し、次の担当者の抽出条件が残らないようにします。
FILTER関数による個別シートへの連動表示
続いては、Microsoft 365やExcel 2021以降で使えるFILTER関数によって、個別シートを元データと連動させる方法を確認していきます。
| 個別シート | 指定する担当者 | 表示されるデータ |
|---|---|---|
| 田中 | 田中 | 田中の全明細 |
| 佐藤 | 佐藤 | 佐藤の全明細 |
| 鈴木 | 鈴木 | 鈴木の全明細 |
FILTER関数は新規シートを自動で作る機能ではありませんが、あらかじめ作成した個別シートへ最新データを表示し続けたい場合に便利です。
個別シートの条件セルの準備
それではまず、個別シートに抽出条件となる担当者名を入力する方法について解説していきます。
田中という名前の個別シートを作成し、F1セルに担当者、G1セルに田中と入力します。
次に、A1からD1へ一覧シートと同じ見出しを入力します。
条件となる名前をG1セルへまとめておくと、数式の条件を変更しやすくなります。
シート名とG1セルの担当者名を一致させると、どのシートのデータかを確認しやすくなります。
個別シートのF1とG1は抽出条件の確認用です。
集計表や印刷用のレイアウトを作る場合も、条件セルを見える位置に置くと管理しやすくなります。
【操作のポイント】抽出条件は数式へ直接書かず、専用セルに入力して参照させると変更が簡単です。
FILTER関数による担当者別の抽出
続いては、一覧表から田中の明細だけを取り出すFILTER関数を確認していきます。
=FILTER(一覧!A2:D1000,一覧!A2:A1000=G1,”該当データなし”)
この数式を田中シートのA2セルに入力すると、一覧シートのA列がG1セルの値と一致する行だけがA列からD列まで表示されます。
数式はA2セルだけに入力します。
FILTER関数は該当する複数行を自動で下方向へ展開するスピル機能を持つため、A3以下へ同じ数式をコピーする必要はありません。
第1引数の一覧!A2:D1000は取り出すデータ範囲です。
第2引数の一覧!A2:A1000=G1は、A列の担当者がG1と同じかどうかを判定する条件です。
第3引数の該当データなしは、一致する行がないときに表示する文字列です。
データが1000行を超える可能性がある場合は、対象範囲を広げるか、一覧表をテーブルに変換して構造化参照を使いましょう。
【操作のポイント】FILTER関数の結果が広がる範囲には、あらかじめ文字や数式を入力しないようにします。
FILTER関数の操作画面イメージ
続いては、個別シートのA2セルへ数式を入力する画面イメージを確認していきます。
赤枠のG1セルを変更すると、A2セルから展開される一覧も自動で切り替わります。
一覧シート側へ明細を追加、修正、削除した場合も、参照範囲内であれば個別シートの表示が更新されます。
【操作のポイント】A2セルを起点に結果が展開されるため、周囲のセルを空けてスピル範囲を確保します。
個別シート作成後の集計とレイアウト調整
続いては、作成した個別シートを見やすくし、売上や件数を確認しやすくする方法を確認していきます。
| 確認項目 | 例 | おすすめの設定 |
|---|---|---|
| 金額 | 12000 | 桁区切り表示 |
| 日付 | 2026/9/1 | yyyy/m/d形式 |
| 見出し | 担当者、日付、商品 | 太字と背景色 |
SUM関数による個別シートの合計表示
それではまず、個別シートごとの売上合計を求める方法について解説していきます。
=SUM(D2:D1000)
売上がD列にある場合、空いているセルにこの数式を入力すると、その個別シートに表示されている売上の合計を求められます。
FILTER関数を使っているシートでは、表示行数が増減してもSUM関数の範囲内であれば合計が連動します。
個別シートで合計を管理すると、担当者別の実績をすぐ確認できるようになります。
VBAで転記したシートでも同じ数式を使用できます。
ただし、明細行が増える可能性を考え、SUMの範囲は余裕を持って指定するか、Excelテーブルを使う方法が便利です。
【操作のポイント】合計セルは明細の下ではなく、表の上部や右側に置くと、データ行が増えても見失いにくくなります。
テーブル化によるデータ範囲の自動拡張
続いては、一覧表をテーブルへ変換して、追加データを扱いやすくする方法を確認していきます。
一覧表の任意のセルを選択してから、挿入タブのテーブルをクリックします。
先頭行をテーブルの見出しとして使用するにチェックが入っていることを確認して作成します。
テーブル化すると、新しい明細を最終行の下へ入力した際に、データ範囲や書式が自動的に広がる点が大きな利点です。
FILTER関数の参照元もテーブル名で指定できるため、1000行までのような固定範囲をあらかじめ設定する必要がありません。
たとえばテーブル名が売上一覧で、担当者列が担当者の場合は、構造化参照を使った数式を組めます。
=FILTER(売上一覧,売上一覧[担当者]=G1,”該当データなし”)
【操作のポイント】元データが毎月追加される運用では、一覧表をテーブル化して参照範囲の漏れを防ぎます。
印刷設定と個別シートの見やすさ
続いては、個別シートを印刷やPDF保存に使う場合の整え方を確認していきます。
ホームタブで見出し行を太字にし、背景色と罫線を付けると、明細の区切りが分かりやすくなります。
売上列は桁区切りの数値形式にし、必要であれば円記号を表示します。
ページレイアウトタブでは、印刷の向き、余白、拡大縮小を設定できます。
列数が多い表では横向きを選び、幅を1ページに収める設定にすると読みやすくなります。
また、印刷タイトルで見出し行を指定すれば、複数ページにわたる表でも各ページの上部へ見出しを繰り返して表示できます。
【操作のポイント】個別シートを配布する場合は、印刷プレビューで列の切れ方、見出しの繰り返し、余白を確認します。
一覧表から個別シートを作成するときの注意点
続いては、一覧表を分割する際に起こりやすいエラーやデータ管理上の注意点を確認していきます。
| よくある問題 | 主な原因 | 対処の方向性 |
|---|---|---|
| シート名エラー | 使用禁止文字や長い名前 | 名前を修正する |
| 抽出漏れ | 空白や表記ゆれ | 元データを統一する |
| 数式エラー | スピル範囲に値がある | 周囲のセルを空ける |
シート名の重複と使用禁止文字
それではまず、VBAで個別シートを作成するときのシート名に関する注意点について解説していきます。
Excelでは、同じ名前のシートを同一ブック内に複数作成できません。
すでに田中シートがある状態で、同じ名前のシートを追加しようとするとエラーになります。
また、シート名は31文字以内であり、半角スラッシュ、逆スラッシュ、疑問符、アスタリスク、角かっこなどを使用できません。
顧客名や案件名をシート名にする場合は、使用禁止文字が含まれていないか特に注意しましょう。
定期的にマクロを実行する運用では、既存シートを削除して作り直すのか、既存シートを更新するのかを事前に決めておく必要があります。
【操作のポイント】シート名として使う列は、短く一意で、Excelで使えない文字を含まない値に統一します。
空白セルと表記ゆれによる抽出漏れ
続いては、担当者別の明細が正しく分かれない原因を確認していきます。
田中、田中のように、見た目では分かりにくい空白が含まれるデータは別の値として扱われます。
全角スペース、半角スペース、旧字体、入力ミスなども同様です。
元データに空白の担当者セルがあると、空白条件のシートが作成されたり、FILTER関数の結果が意図しないものになったりするかもしれません。
必要に応じてTRIM関数、CLEAN関数、置換機能を使い、データを整えてから分割します。
入力規則によるリスト選択を設定すれば、担当者名の入力方法を統一しやすくなります。
データを分割する前には、担当者列を並べ替え、空白や似た表記がないかを目視で確認する方法も有効です。
【操作のポイント】抽出の不一致は数式より元データの表記ゆれが原因であることが多いため、一覧表を先に整えます。
マクロ有効化とバックアップ管理
続いては、VBAで個別シートを作成する際の安全な運用を確認していきます。
VBAを含むブックはxlsm形式で保存し、信頼できる作成元のファイルだけでマクロを有効にします。
配布元が不明なマクロ付きファイルは、内容を確認せずに実行しないことが大切です。
また、マクロ実行前には必ずブックを複製し、元の一覧表を戻せる状態にしておくと安心です。
VBAでシートを追加、削除、上書きする処理は、元に戻す機能で取り消せない場合があります。
定期処理では、処理前のファイルと処理後のファイルを別名で保存するルールを設けると、誤操作への備えになります。
【操作のポイント】マクロを実行する前は、元データのバックアップと対象シート名の確認を習慣にします。
まとめ エクセルで個別シートを一覧表から作成する方法
エクセルの一覧表から個別シートを作成する方法は、用途によって使い分けると効率的です。
担当者や部署ごとのシートを一度に作成して配布したい場合は、VBAでオートフィルターとコピーを実行する方法が役立ちます。
一方で、一覧表の更新内容を個別シートにも継続して反映したい場合は、FILTER関数を使った連動表示が便利です。
どちらの方法でも、1行目に見出しを置き、空白行や表記ゆれを減らした一覧表を用意することが成功の土台になります。
VBAを使うときはxlsm形式で保存し、実行前のバックアップを残しましょう。
FILTER関数を使うときは、数式が展開されるスピル範囲を空け、条件セルを分かりやすく配置することがポイントです。
一覧表をテーブル化し、担当者名の入力方法も統一すれば、データ追加後の管理もさらに楽になります。
目的に合った方法を選び、一覧表の転記や個別集計にかかる時間を減らしていきましょう。
