地域によるワークシートの並べ替え(Microsoft Excel)
Koenには、会社の支店ごとに1つずつ、150のワークシートを含むワークブックがあります。彼は、各ブランチが属する地域に基づいてこれらのシートを並べ替える必要があります。 (彼の会社には5つの地域があります。)また、各ワークシートのタブの色が地域を反映できれば素晴らしいと述べ、Excelでそのような並べ替えが可能かどうか疑問に思います。
はい、そのような並べ替えは可能ですが、マクロを使用する必要があります。
マクロを作成する上で最も難しいのは、どのブランチがどのリージョンにあるかをどのように決定するかを定義することです。おそらくこれを行う最も簡単な方法は、ワークシートが地域とブランチの両方の情報を含む命名パターンを使用していることを確認することです。たとえば、ワークシートに「Reg01-Branch123」のような名前を付けることができます。次に、マクロは各ワークシートをステップスルーし、色付けと並べ替えの両方を実行できます。
Sub SortWorksheets() Dim iReg As Integer Dim I As Integer Dim J As Integer Dim K As Integer Dim ws As Worksheet Application.ScreenUpdating = False ' Set tab colors For Each ws in Worksheets iReg = Val(Mid(ws.Name,4,2)) Select Case iReg Case 1 ws.Tab.Color = vbRed Case 2 ws.Tab.Color = vbYellow Case 3 ws.Tab.Color = vbBlue Case 4 ws.Tab.Color = vbGreen Case 5 ws.Tab.Color = vbCyan Case Else ws.Tab.ColorIndex = xlColorIndexNone End Select Next ws ' Sort the worksheets For I = 1 To Sheets.Count - 1 K = I For J = I + 1 To Sheets.Count If UCase(Sheets(K).Name) > UCase(Sheets(J).Name) Then K = J Next J If K <> I Then Sheets(K).Move Before:=Sheets(I) Next I Application.ScreenUpdating = True End Sub
マクロは、Worksheetsコレクションを2回処理します。最初のパススルーで、領域番号がワークシートの名前から抽出されます。
次に、これを使用して(Select Case構造で)タブの色を設定します。
地域番号が1〜5の範囲外の場合、タブの色は変更されません。
どのブランチがどの領域にあるかを判断するのがより複雑な場合は、それに応じてマクロを調整する必要があります。たとえば、「Region Key」という名前のワークシートがあり、列Aに各ブランチの名前が含まれ、列Bにそれらの各ブランチに対応するリージョン番号が含まれている場合があります。実際のデータが行2で始まると仮定すると、次の方法でマクロを変更できます。
Sub SortWorksheets2() Dim sTemp As String Dim iReg As Integer Dim I As Integer Dim J As Integer Dim K As Integer Dim ws As Worksheet Dim key As Worksheet Application.ScreenUpdating = False Set key = Worksheets("Region Key") ' Set tab colors For Each ws in Worksheets sTemp = UCase(ws.Name) I = 2 ' Beginning row number iReg = 0 While key.Cells(I, 1) > "" If UCase(key.Cells(I, 1)) = sTemp Then iReg = key.Cells(I, 2) I = I + 1 Wend Select Case iReg Case 1 ws.Tab.Color = vbRed Case 2 ws.Tab.Color = vbYellow Case 3 ws.Tab.Color = vbBlue Case 4 ws.Tab.Color = vbGreen Case 5 ws.Tab.Color = vbCyan Case Else ws.Tab.ColorIndex = xlColorIndexNone End Select Next ws ' Sort the worksheets For I = 1 To Sheets.Count - 1 K = I For J = I + 1 To Sheets.Count If UCase(Sheets(K).Name) > UCase(Sheets(J).Name) Then K = J Next J If K <> I Then Sheets(K).Move Before:=Sheets(I) Next I Sheets("Region Key").Move Before:=Sheets(1) Application.ScreenUpdating = True End Sub
このマクロと前のマクロの最大の違いは、このマクロがワークシートから地域番号を取得することです。マクロの最後のアクションとして、「リージョンキー」ワークシートがワークシートコレクションの最初に移動されます。
この2番目のマクロは、プログラムウィンドウの下部にあるワークシートタブを色の万華鏡のように見せることもできることに注意してください。
これは、タブが色ではなく名前に基づいて並べ替えられているためです。これは、ワークシートがそのパターンを使用して命名されたため、地域、次にブランチで効果的にソートされた最初のマクロとは異なります。それでも「リージョンキー」を使用したい場合
アプローチして地域で並べ替えてから分岐します。マクロをもう少し調整することで、これを行うことができます。
Sub SortWorksheets3() Dim sTemp As String Dim sSortArray(499) As String Dim iReg As Integer Dim I As Integer Dim J As Integer Dim K As Integer Dim ws As Worksheet Dim key As Worksheet Application.ScreenUpdating = False Set key = Worksheets("Region Key") ' Set tab colors and build sort array J = 0 For Each ws in Worksheets sTemp = UCase(ws.Name) I = 2 ' Beginning row number iReg = 0 While key.Cells(I, 1) > "" If UCase(key.Cells(I, 1)) = sTemp Then iReg = key.Cells(I, 2) I = I + 1 Wend J = J + 1 sSortArray(J) = Right("00" & iReg, 2) & " " & ws.Name Select Case iReg Case 1 ws.Tab.Color = vbRed Case 2 ws.Tab.Color = vbYellow Case 3 ws.Tab.Color = vbBlue Case 4 ws.Tab.Color = vbGreen Case 5 ws.Tab.Color = vbCyan Case Else ws.Tab.ColorIndex = xlColorIndexNone ' Force into incorrect region area for sort sSortArray(J) = "00 " & ws.Name End Select Next ws ' Sort the worksheets For I = 1 To Sheets.Count - 1 K = I For J = I + 1 To Sheets.Count If UCase(sSortArray(K)) > UCase(sSortArray(J)) Then K = J Next J If K <> I Then Sheets(K).Move Before:=Sheets(I) sTemp = sSortArray(K) For J = K To I Step -1 sSortArray(J) = sSortArray(J-1) Next J sSortArray(I) = sTemp End If Next I Sheets("Region Key").Move Before:=Sheets(1) Application.ScreenUpdating = True End Sub
このマクロの反復は、ワークシート内の名前の並べ替え方法を追跡するためにヘルパー配列(sSortArray)に依存していることに注意してください。
注:
このページ(または_ExcelTips_サイトの他のページ)で説明されているマクロの使用方法を知りたい場合は、役立つ情報を含む特別なページを用意しました。
_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。
このヒント(13710)は、Microsoft Excel 2007、2010、2013、2016、2019、およびOffice365のExcelに適用されます。