Ordinamento dei fogli di lavoro in base alla regione (Microsoft Excel)
Koen ha una cartella di lavoro contenente 150 fogli di lavoro, uno per ogni ramo aziendale. Deve ordinare quei fogli in base alla regione a cui appartiene ogni ramo. (Ci sono cinque regioni nella sua azienda.) Nota inoltre che sarebbe fantastico se il colore della scheda per ogni foglio di lavoro potesse riflettere la regione e si chiede se tale ordinamento sia possibile in Excel.
Sì, tale ordinamento è possibile, ma richiede l’uso di una macro.
La parte più difficile della creazione della macro è definire come determinare quali rami si trovano in quale regione. Forse il modo più semplice per farlo è assicurarsi che i fogli di lavoro utilizzino uno schema di denominazione che includa sia le informazioni sulla regione che sul ramo. Ad esempio, potresti denominare i fogli di lavoro qualcosa come “Reg01-Branch123”. La tua macro potrebbe quindi passare attraverso ogni foglio di lavoro e fare sia la colorazione che l’ordinamento.
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
La macro funziona due volte attraverso la raccolta dei fogli di lavoro. Al primo passaggio, il numero della regione viene estratto dal nome del foglio di lavoro.
Questo viene quindi utilizzato (nella struttura Seleziona caso) per impostare il colore della scheda.
Se il numero di regione non è compreso nell’intervallo 1-5, non viene apportata alcuna modifica al colore della scheda.
Se la tua determinazione di quale ramo si trova in quale regione è più complessa, dovrai regolare la macro di conseguenza. Ad esempio, potresti avere un foglio di lavoro denominato “Chiave regione” che contiene, nella colonna A, i nomi di ciascuno dei tuoi rami e, nella colonna B, il numero di regione corrispondente per ciascuno di questi rami. Supponendo che i dati effettivi inizino nella riga 2, è possibile modificare la macro nel modo seguente:
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
La più grande differenza tra questa macro e la precedente è che questa prende il numero di regione da un foglio di lavoro. Come ultima azione nella macro, il foglio di lavoro “Chiave regione” viene spostato all’inizio della raccolta Fogli di lavoro.
Nota che questa seconda macro può anche far apparire le schede del tuo foglio di lavoro, nella parte inferiore della finestra del programma, come un caleidoscopio di colori.
Il motivo è che le schede sono ordinate in base ai loro nomi, non ai loro colori. Questo differisce dalla prima macro, che effettivamente ordinava per regione e poi per ramo perché i fogli di lavoro erano denominati utilizzando quel modello. Se vuoi usare ancora la “Region Key”
avvicinati e ordina per regione e poi ramifica, puoi farlo aggiustando un po ‘di più la macro:
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
Si noti che questa iterazione della macro si basa su un array di supporto (sSortArray) per tenere traccia di come devono essere ordinati i nomi nei fogli di lavoro.
_Nota: _
Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.
ExcelTips è la tua fonte di formazione economica su Microsoft Excel.
Questo suggerimento (13710) si applica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 e Excel in Office 365.