トップ > VBA・VBS > Excel VBA > 条件でデータを別シートに振り分ける

条件でデータを別シートに振り分ける

Excel VBA(担当者別・月別のシートを自動で作る)

「担当者ごとにシートを分けて渡して」と言われると、フィルターして、コピーして、新しいシートに貼って、名前を付けて…を担当者の人数分。人数が多いほど心が折れる作業です。このレシピでは、指定した列の値ごとに行を振り分け、値の名前のシートを自動で作って配るマクロを作ります。再実行すれば各シートは最新状態に作り直されるので、データが差し替わっても振り分け直しは1回で済みます。

✅ これができます:ボタン1つで、データを担当者別・月別などのシートに振り分けられるようになります

完成形(実行前 → 実行後)

実行前
実行前
実行後
実行後

実行前(左)は担当者が混ざった1枚のデータ。実行すると(右のように)担当者名のシートが自動で作られ、行が振り分けられます

これをコピペすれば動きます

Option Explicit
 
' ===== 設定エリア:変えるのはここだけ =====
' ① 振り分けもとになるデータのシート名
Const DATA_SHEET As String = "データ"
' ② 振り分けのキーになる列の番号(A列=1、B列=2…。例は担当者の列)
Const KEY_COL As Long = 2
' ③ データの見出し行の数
Const HEADER_ROWS As Long = 1
' ===== ここから下は変えなくてOK =====
 
Sub 担当者別に振り分ける()
    Dim wsData As Worksheet, wsOut As Worksheet
    Dim dicKeys As Object, dicRow As Object
    Dim lastRow As Long, lastCol As Long, r As Long
    Dim key As String, sheetName As String
    Dim k As Variant
    Dim skipSelf As Long, msg As String
 
    Set dicKeys = CreateObject("Scripting.Dictionary")
    Set dicRow = CreateObject("Scripting.Dictionary")
    Set wsData = ThisWorkbook.Worksheets(DATA_SHEET)
    lastRow = wsData.Cells(wsData.Rows.Count, KEY_COL).End(xlUp).Row
    lastCol = wsData.Cells(HEADER_ROWS, wsData.Columns.Count).End(xlToLeft).Column
 
    ' まず、振り分け先の値の顔ぶれを集める(データシートと同じ名前の値は、安全のため対象外)
    skipSelf = 0
    For r = HEADER_ROWS + 1 To lastRow
        key = Trim(CStr(wsData.Cells(r, KEY_COL).Value))
        If key <> "" Then
            If シート名に使える形に(key) = DATA_SHEET Then
                skipSelf = skipSelf + 1
            ElseIf Not dicKeys.Exists(key) Then
                dicKeys.Add key, True
            End If
        End If
    Next r
    If dicKeys.Count = 0 Then
        MsgBox "振り分けるデータがありませんでした。", vbExclamation, "振り分け"
        Exit Sub
    End If
 
    ' 値ごとにシートを用意し、見出しを写して、書き出し位置を初期化する
    Application.ScreenUpdating = False
    For Each k In dicKeys.Keys
        sheetName = シート名に使える形に(CStr(k))
        If Not dicRow.Exists(sheetName) Then
            Set wsOut = シートを用意する(sheetName)
            wsOut.Cells.Clear
            wsData.Range(wsData.Cells(1, 1), wsData.Cells(HEADER_ROWS, lastCol)).Copy wsOut.Cells(1, 1)
            dicRow.Add sheetName, HEADER_ROWS + 1
        End If
    Next k
 
    ' データを1行ずつ、行き先のシートへ写す
    For r = HEADER_ROWS + 1 To lastRow
        key = Trim(CStr(wsData.Cells(r, KEY_COL).Value))
        If key <> "" Then
            sheetName = シート名に使える形に(key)
            Set wsOut = ThisWorkbook.Worksheets(sheetName)
            wsOut.Cells(dicRow(sheetName), 1).Resize(1, lastCol).Value = wsData.Cells(r, 1).Resize(1, lastCol).Value
            dicRow(sheetName) = dicRow(sheetName) + 1
        End If
    Next r
    Application.ScreenUpdating = True
 
    wsData.Activate
    msg = dicKeys.Count & " 件のシートに振り分けました。"
    If skipSelf > 0 Then
        msg = msg & vbCrLf & "(データシートと同じ名前の値 " & skipSelf & " 行は、安全のため飛ばしました)"
    End If
    MsgBox msg, vbInformation, "振り分け"
End Sub
 
Private Function シート名に使える形に(ByVal s As String) As String
    Dim bad As Variant, b As Variant
    bad = Array("\", "/", "?", "*", "[", "]", ":")
    For Each b In bad
        s = Replace(s, CStr(b), "_")
    Next b
    If Len(s) > 31 Then s = Left(s, 31)
    If s = "" Then s = "無題"
    シート名に使える形に = s
End Function
 
Private Function シートを用意する(ByVal sheetName As String) As Worksheet
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = sheetName Then
            Set シートを用意する = ws
            Exit Function
        End If
    Next ws
    Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    ws.Name = sheetName
    Set シートを用意する = ws
End Function

コードの黄色い部分が「設定エリア」です。変えるのは、原則ここだけです。

変えるのはここだけ

変える場所を示した図
変えるのは設定エリアの3か所だけ。番号は下の表と対応しています
番号変える場所意味書き方・例
1DATA_SHEET振り分けもとになるデータのシート名自分のブックのデータシートの名前に書き換えます
2KEY_COL振り分けのキーになる列の番号担当者が B列なら 2、月が A列なら 1。この列の値が、そのままシート名になります
3HEADER_ROWSデータの見出し行の数見出しが1行なら 1。見出しは各シートの先頭にも写されます

組み込み手順

  1. データシートで、②に指定する列の値(担当者名など)を確認します。空欄の行は振り分け先が無いため飛ばされます
  2. Alt+F11 でVBAエディタを開き、「挿入」→「標準モジュール」を選びます
  3. このページのコードを貼り付け、設定エリア①〜③を書き換えます
  4. ファイルを .xlsm で保存し、マクロを実行します(ボタン登録が便利です)
  5. 値ごとのシートが作られ、行が振り分けられていれば成功です

⚠ VBAエディタの開き方:Alt+F11 で開きます。「開発」タブが出ていない場合は、ファイル→オプション→リボンのユーザー設定で「開発」にチェックを入れると出ます。

⚠ 保存形式:マクロは「Excel マクロ有効ブック(.xlsm)」で保存しないと、次に開いたときに消えています。いつもの .xlsx で保存すると、マクロだけ取り除かれます。

⚠ セキュリティ警告バー:開いたときに黄色いバーが出たら「コンテンツの有効化」を押してください。押すまではマクロは動きません。

⚠ ファイルのブロック解除:ネットから入手したファイルは、開く前に「右クリック→プロパティ→『許可する』にチェック」が先です。これをしないと、有効化のボタン自体が出ないことがあります。

⚠ 会社の設定で無効化されている場合:警告バーも出ずに無反応なときは、会社の設定でマクロが止められている可能性が高いです。設定を突破しようとせず、情シスに「業務でマクロを使いたい」と相談するのが正解です。

動かし方

実行のたびに、振り分け先の各シートはまっさらな状態から作り直されます(データシート自身は変更されません)。担当者が増えたら、その人のシートも自動で増えます。なお、以前はあった担当者がデータから消えた場合、その人の古いシートは残ったままになるので、不要なら手で消してください。

カスタマイズ例

月別に振り分けたい

②を「月」の列に変えるだけです。日付の列しか無い場合は、データ側に「月」を取り出す列(=TEXT関数や MONTH関数)を1列足してから、その列をキーにします。

振り分け先を別ブックに保存したい

振り分け後にシートごとに保存する処理を続けると、「担当者別ファイル」の配布まで一気に行けます。「シートごとに別ブックとして分割保存」のレシピと組み合わせる形です。

値の表記ゆれをまとめたい

「佐藤」と「佐藤 」のような表記ゆれは別のシートになります。前後の空白は自動で取り除きますが、全角・半角や敬称の有無は元データ側で揃えるのが確実です。

つまずきどころ

関連レシピ

次はこれ:全シートをPDFで一括出力する
振り分けたシートは、PDFで一括出力して配布するところまで自動化できます

よくある質問

Q. 行の書式(色や罫線)は引き継がれる?

A. 見出し行は書式ごと写しますが、データ行は値だけを写します。色分けなどの書式が重要な表では、実行後に必要な書式を足してください。

Q. 数千行あっても大丈夫?

A. 1行ずつ写す作りなので、数千行なら実用範囲です。数万行になると時間がかかる場合があります。

Q. 振り分け先のシートの並び順は?

A. データに最初に現れた順に作られます。名前順にしたい場合は、実行後にシート見出しを並べ替えてください。

PR(広告)

もっと体系的に学びたい人へ
自動化の幅を広げるなら、講座で体系化するのが近道です。

Udemy:Excelショートカット講座を見る