Dividere una tabella in fogli

Microsoft Excel ha molti strumenti per raccogliere dati da più tabelle (da fogli diversi o da file diversi): collegamenti diretti, funzione INDIRETTI (INDIRETTO), componenti aggiuntivi Power Query e Power Pivot, ecc. Da questo lato della barricata, tutto sembra a posto.

Ma se ti imbatti in un problema inverso, ovvero la diffusione dei dati da una tabella a fogli diversi, tutto sarà molto più triste. Al momento, non ci sono strumenti integrati civili per tale separazione dei dati nell'arsenale di Excel, sfortunatamente. Quindi dovrai utilizzare una macro in Visual Basic o utilizzare la combinazione di registratore di macro + Power Query con un piccolo "perfezionamento del file" dopo.

Diamo un'occhiata più da vicino a come questo può essere implementato.

Formulazione del problema

Abbiamo come dati iniziali una tabella del genere con una dimensione di oltre 5000 righe per le vendite:

Compito: distribuire i dati di questa tabella per città su fogli separati di questo libro. Quelli. in output, devi ottenere su ogni foglio solo quelle righe della tabella in cui era la vendita nella città corrispondente:

Preparare

Per non complicare il codice della macro e renderlo il più semplice possibile, eseguiamo un paio di passaggi preparatori.

In primo luogo, la creare una tabella di ricerca separata, dove una singola colonna elencherà tutte le città per le quali desideri creare fogli separati. Naturalmente questa directory potrebbe non contenere tutte le città presenti nei dati di origine, ma solo quelle per le quali abbiamo bisogno di report. Il modo più semplice per creare una tabella di questo tipo è utilizzare il comando Dati: rimuovi i duplicati (Dati — Rimuovi duplicati) per la copia della colonna Città o funzione UNICO (UNICO) – se hai l'ultima versione di Excel 365.

Poiché i nuovi fogli in Excel vengono creati per impostazione predefinita prima (a sinistra di) quello corrente (precedente), ha senso anche ordinare le città in questa directory in ordine decrescente (dalla Z alla A), quindi dopo la creazione, la città i fogli saranno disposti in ordine alfabetico.

In secondo luogo, la пconvertire entrambe le tabelle in dinamiche ("intelligente") per semplificare il lavoro con loro. Usiamo il comando Home – Formatta come una tabella (Home — Formatta come tabella) o scorciatoia da tastiera Ctrl+T. Nella scheda che appare Costruttore (Design) chiamiamoli tablProdaji и TableCity, rispettivamente:

Metodo 1. Macro per la divisione per fogli

Nella scheda Avanzate sviluppatore (Sviluppatore) clicca sul pulsante Visual Basic o usa la scorciatoia da tastiera altro+F11. Nella finestra dell'editor di macro che si apre, inserisci un nuovo modulo vuoto tramite il menu Inserisci – Modulo e copia lì il seguente codice:

Sub Splitter() per ogni cella nell'intervallo("таблГорода") Intervallo("таблПродажи").Campo filtro automatico:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Cella successiva Fogli di lavoro ("Данные"). ShowAllData End Sub	  

Qui con un loop Per ciascuno... Avanti implementato il passaggio attraverso le celle della directory TableCity, dove per ogni città viene filtrato (met Filtro automatico) nella tabella di vendita originale e quindi copiando i risultati nel foglio appena creato. Lungo il percorso, il foglio creato viene rinominato con lo stesso nome della città e su di esso viene attivato l'adattamento automatico della larghezza delle colonne per la bellezza.

È possibile eseguire la macro creata in Excel nella scheda sviluppatore pulsante Macro (Sviluppatore — Macro) o scorciatoia da tastiera altro+F8.

Metodo 2. Crea più query in Power Query

Il metodo precedente, nonostante tutta la sua compattezza e semplicità, presenta un notevole inconveniente: i fogli creati dalla macro non vengono aggiornati quando vengono apportate modifiche alla tabella di vendita originale. Se è necessario l'aggiornamento al volo, dovrai utilizzare il bundle VBA + Power Query, o meglio, creare utilizzando una macro non solo fogli con dati statici, ma query Power Query aggiornate.

La macro in questo caso è in parte simile alla precedente (ha anche un ciclo Per ciascuno... Avanti per scorrere le città nella directory), ma all'interno del ciclo non ci saranno più filtri e copie, ma la creazione di una query Power Query e il caricamento dei risultati in un nuovo foglio:

Sub Splitter2() Per ogni cella In Range("City table") ActiveWorkbook.Queries.Add Nome:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Contenuto]," & Chr(13) & "" & Chr(10) & " #""Tipo modificato"" = Table.TransformColumnTypes(Source , {{""Categoria"", digita testo}, {""Nome"", digita testo}, {""Città"", digita testo}, {""Gestore"", digita testo}, {""Offerta date "", digita datetime}, {""Costo"", digita numero}})," & Chr(13) & "" & Chr(10) & " #""Righe con filtro applicato"" = Table.Se " & _ "lectRows(#""Tipo modificato"", each ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Righe con filtro applicato""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Origine dati=$Cartella di lavoro$;Posizione=" & cell.Value & ";Proprietà estese=""""" _ , Destinazione:=Intervallo("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False . SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False Fine con ActiveSheet.Name = cell.Value Cella successiva End Sub  

Dopo il suo lancio, vedremo gli stessi fogli per città, ma le query Power Query già create le formeranno:

Con eventuali modifiche ai dati di origine, sarà sufficiente aggiornare la tabella corrispondente con il tasto destro del mouse – il comando Aggiorna e salva (Ricaricare) o aggiorna tutte le città contemporaneamente in blocco utilizzando il pulsante Aggiorna tutto linguetta Dati (Dati — Aggiorna tutto).

• Cosa sono le macro, come crearle e usarle
• Salvataggio dei fogli della cartella di lavoro come file separati
• Raccolta dei dati da tutti i fogli del libro in un'unica tabella