Tabella pivot su più intervalli di dati

Formulazione del problema

Le tabelle pivot sono uno degli strumenti più sorprendenti di Excel. Ma finora, sfortunatamente, nessuna delle versioni di Excel può fare al volo una cosa così semplice e necessaria come costruire un riepilogo per diversi intervalli di dati iniziali situati, ad esempio, su fogli diversi o in tabelle diverse:

Prima di iniziare, chiariamo un paio di punti. A priori, credo che nei nostri dati siano soddisfatte le seguenti condizioni:

• Le tabelle possono avere un numero qualsiasi di righe con qualsiasi dato, ma devono avere la stessa intestazione.
• Non dovrebbero esserci dati aggiuntivi sui fogli con le tabelle di origine. Un foglio – un tavolo. Per controllare, ti consiglio di usare una scorciatoia da tastiera Ctrl+Fine, che ti sposta all'ultima cella utilizzata nel foglio di lavoro. Idealmente, questa dovrebbe essere l'ultima cella nella tabella dei dati. Se quando fai clic su Ctrl+Fine qualsiasi cella vuota a destra o sotto la tabella viene evidenziata: elimina queste colonne vuote a destra o le righe sotto la tabella dopo la tabella e salva il file.

Metodo 1: crea tabelle per un pivot usando Power Query

A partire dalla versione 2010 per Excel, è disponibile un componente aggiuntivo gratuito di Power Query in grado di raccogliere e trasformare qualsiasi dato e quindi fornirlo come origine per la creazione di una tabella pivot. Risolvere il nostro problema con l'aiuto di questo componente aggiuntivo non è affatto difficile.

Innanzitutto, creiamo un nuovo file vuoto in Excel: l'assemblaggio avrà luogo al suo interno e quindi verrà creata una tabella pivot.

Poi sulla scheda Dati (se hai Excel 2016 o versioni successive) o nella scheda Power Query (se hai Excel 2010-2013) seleziona il comando Crea query – Da file – Excel (Ottieni dati — Da file — Excel) e specificare il file sorgente con le tabelle da raccogliere:

Nella finestra che appare, seleziona un foglio qualsiasi (non importa quale) e premi il pulsante in basso Cambiamento di (Edit):

La finestra dell'editor di Power Query dovrebbe aprirsi sopra Excel. Sul lato destro della finestra sul pannello Richiedi parametri elimina tutti i passaggi creati automaticamente tranne il primo – Fonte (Fonte):

Ora vediamo un elenco generale di tutti i fogli. Se oltre alle schede tecniche sono presenti nel file anche altre schede laterali, a questo punto il nostro compito è selezionare solo quelle schede dalle quali è necessario caricare le informazioni, escludendo tutte le altre utilizzando il filtro nell'intestazione della tabella:

Elimina tutte le colonne tranne la colonna Datifacendo clic con il pulsante destro del mouse sull'intestazione di una colonna e selezionando Elimina altre colonne (Rimuovere altre colonne):

È quindi possibile espandere il contenuto delle tabelle raccolte facendo clic sulla doppia freccia nella parte superiore della colonna (casella di spunta Usa il nome della colonna originale come prefisso puoi spegnerlo):

Se hai fatto tutto correttamente, a questo punto dovresti vedere il contenuto di tutte le tabelle raccolte una sotto l'altra:

Resta da aumentare la prima riga all'intestazione della tabella con il pulsante Usa la prima riga come intestazioni (Utilizza la prima riga come intestazioni) linguetta Casa (Casa) e rimuovere le intestazioni di tabella duplicate dai dati utilizzando un filtro:

Salva tutto ciò che è stato fatto con il comando Chiudi e carica – Chiudi e carica in... (Chiudi e carica — Chiudi e carica in...) linguetta Casa (Casa)e nella finestra che si apre, seleziona l'opzione Solo connessione (Solo connessione):

Tutto quanto. Resta solo da costruire un riassunto. Per fare ciò, vai alla scheda Inserisci – Tabella pivot (Inserisci — Tabella pivot), scegli l'opzione Usa un'origine dati esterna (Usa origine dati esterna)e quindi facendo clic sul pulsante Selezionare collegamento, la nostra richiesta. L'ulteriore creazione e configurazione del pivot avviene in modo del tutto standard trascinando i campi di cui abbiamo bisogno nell'area righe, colonne e valori:

Se i dati di origine cambiano in futuro o vengono aggiunti alcuni fogli di negozio in più, sarà sufficiente aggiornare la query e il nostro riepilogo utilizzando il comando Aggiorna tutto linguetta Dati (Dati — Aggiorna tutto).

Metodo 2. Uniamo le tabelle con il comando UNION SQL in una macro

Un'altra soluzione al nostro problema è rappresentata da questa macro, che crea un data set (cache) per la tabella pivot tramite il comando UNITA ' Linguaggio di interrogazione SQL. Questo comando combina le tabelle di tutte le tabelle specificate nell'array Nomi Foglio fogli del libro in un'unica tabella di dati. Cioè, invece di copiare e incollare fisicamente intervalli da fogli diversi a uno, facciamo lo stesso nella RAM del computer. Quindi la macro aggiunge un nuovo foglio con il nome dato (variabile NomeFoglio Risultato) e crea un riepilogo completo (!) su di esso basato sulla cache raccolta.

Per utilizzare una macro, utilizzare il pulsante di Visual Basic nella scheda sviluppatore (Sviluppatore) o scorciatoia da tastiera altro+F11. Quindi inseriamo un nuovo modulo vuoto tramite il menu Inserisci – Modulo e copia lì il seguente codice:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'nome del foglio in cui verrà visualizzato il pivot risultante ResultSheetName = "Pivot" 'un array di fogli nomi con tabelle di origine SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'formiamo una cache per le tabelle da fogli da SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Avanti i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Termina con 'ricrea il foglio per visualizzare la tabella pivot risultante In caso di errore Riprendi successiva Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'visualizza il riepilogo della cache generato su questo foglio Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3") Set objPivotCache = Nothing Range ("A3"). Seleziona Fine con End Sub    

La macro finita può quindi essere eseguita con una scorciatoia da tastiera altro+F8 o il pulsante Macro nella scheda sviluppatore (Sviluppatore — Macro).

Contro di questo approccio:

• I dati non vengono aggiornati perché la cache non ha alcuna connessione alle tabelle di origine. Se si modificano i dati di origine, è necessario eseguire nuovamente la macro e creare nuovamente il riepilogo.
• Quando si cambia il numero di fogli, è necessario modificare il codice della macro (array Nomi Foglio).
  

Ma alla fine otteniamo una vera tabella pivot a tutti gli effetti, costruita su diverse gamme di fogli diversi:

Voilà!

Nota tecnica: se viene visualizzato un messaggio di errore come "Provider non registrato" durante l'esecuzione della macro, molto probabilmente è installata una versione a 64 bit di Excel o è installata una versione incompleta di Office (nessun accesso). Per risolvere la situazione, sostituisci il frammento nel codice della macro:

	 Provider = Microsoft.Jet.OLEDB.4.0;  

di:

	Provider=Microsoft.ACE.OLEDB.12.0;  

E scarica e installa il motore di elaborazione dati gratuito da Access dal sito Web di Microsoft: Microsoft Access Database Engine 2010 Redistributable

Metodo 3: Consolidamento guidato tabella pivot da versioni precedenti di Excel

Questo metodo è un po' datato, ma vale comunque la pena menzionarlo. Formalmente, in tutte le versioni fino al 2003 incluso, c'era un'opzione nella procedura guidata tabella pivot per "costruire un pivot per diversi intervalli di consolidamento". Tuttavia, un report costruito in questo modo, purtroppo, sarà solo una pietosa parvenza di un vero e proprio riassunto e non supporta molti dei "chip" delle tabelle pivot convenzionali:

In un tale pivot, non ci sono intestazioni di colonna nell'elenco dei campi, non esiste un'impostazione della struttura flessibile, l'insieme di funzioni utilizzate è limitato e, in generale, tutto ciò non è molto simile a una tabella pivot. Forse è per questo che, a partire dal 2007, Microsoft ha rimosso questa funzione dalla finestra di dialogo standard durante la creazione di report di tabelle pivot. Ora questa funzione è disponibile solo tramite un pulsante personalizzato Procedura guidata tabella pivot(Procedura guidata tabella pivot), che, se lo si desidera, può essere aggiunto alla barra di accesso rapido tramite File – Opzioni – Personalizza barra degli strumenti di accesso rapido – Tutti i comandi (File — Opzioni — Personalizza barra degli strumenti di accesso rapido — Tutti i comandi):

Dopo aver fatto clic sul pulsante aggiunto, è necessario selezionare l'opzione appropriata al primo passaggio della procedura guidata:

E quindi nella finestra successiva, seleziona ogni intervallo a turno e aggiungilo all'elenco generale:

Ma, ancora una volta, questo non è un riassunto completo, quindi non aspettarti troppo da esso. Posso consigliare questa opzione solo in casi molto semplici.

• Creazione di report con tabelle pivot
• Imposta i calcoli nelle tabelle pivot
• Cosa sono le macro, come usarle, dove copiare il codice VBA, ecc.
• Raccolta dati da più fogli a uno (componente aggiuntivo PLEX)