Pivot table in parechje intervalli di dati

Formulazione di u prublema

Pivot tables sò unu di i strumenti più maravigghiusu in Excel. Ma finu à avà, sfurtunatamenti, nimu di e versioni di Excel ùn ponu fà una cosa cusì simplice è necessariu in a mosca cum'è custruisce un riassuntu per parechji intervalli di dati iniziali situati, per esempiu, nantu à diversi fogli o in diverse tavule:

Prima di principià, clarificà un paru di punti. A priori, crede chì e seguenti cundizioni sò scontri in i nostri dati:

• I tavulini ponu avè qualchì numeru di fila cù qualsiasi dati, ma anu da avè u stessu header.
• Ùn deve esse micca dati extra nantu à i fogli cù e tavule fonte. Una foglia - una tavola. Per cuntrullà, vi cunsigliu di utilizà una scurciatoia di tastiera Ctrl+End, chì vi move à l'ultima cellula utilizata in u fogliu di travagliu. Ideale, questu deve esse l'ultima cellula in a tabella di dati. Se quandu cliccate nantu Ctrl+End ogni cellula viota à a diritta o sottu à a tavula hè evidenziata - sguassate sti culonni vacanti à a diritta o fila sottu à a tavula dopu à a tavula è salvà u schedariu.

Metudu 1: Custruite tavule per un pivot cù Power Query

Partendu da a versione 2010 per Excel, ci hè un add-in Power Query gratuitu chì pò cullà è trasfurmà qualsiasi dati è poi dà cum'è fonte per custruisce una tabella pivot. Risolve u nostru prublema cù l'aiutu di questu add-in ùn hè micca difficiule.

Prima, creemu un novu schedariu viotu in Excel - l'assemblea si farà in questu è dopu una tabella di pivot serà creata in questu.

Allora nantu à a tabulazione Dati (se avete Excel 2016 o più tardi) o nantu à a tabulazione dumanda di putenza (se avete Excel 2010-2013) selezziunate u cumandamentu Crea Query - Da u File - Excel (Ottieni dati - Da u schedariu - Excel) è specificate u schedariu fonte cù e tavule per esse raccolte:

In a finestra chì appare, selezziunate qualsiasi fogliu (ùn importa quale) è appughjà u buttone sottu Cambia (Edità):

A finestra Power Query Query Editor deve esse aperta sopra à Excel. À u latu drittu di a finestra nantu à u pannellu Richiesta di Parametri sguassate tutti i passi creati automaticamente eccettu u primu - surghjente (Source):

Avà vedemu una lista generale di tutti i fogli. Se in più di e schede di dati ci sò altre schede laterali in u schedariu, allora in questu passu u nostru compitu hè di selezziunà solu quelli fogli da quale l'infurmazioni deve esse caricate, escludendu tutti l'altri chì utilizanu u filtru in l'intestazione di a tavola:

Sguassate tutte e culonne eccettu a colonna Daticlicchendu u dirittu right in un intestazione di colonna è selezziunate Elimina altre colonne (Elimine altre colonne):

Pudete tandu espansione u cuntenutu di e tavule raccolte clicchendu nantu à a doppia freccia in cima di a colonna (checkbox Aduprate u nome di a colonna originale cum'è prefissu pudete disattivà):

Sè avete fattu tuttu bè, allora à questu puntu duvete vede u cuntenutu di tutte e tavule cullate una sottu à l'altru:

Resta à elevà a prima fila à l'intestazione di a tavula cù u buttone Aduprate a prima linea cum'è intestazioni (Usate a prima fila cum'è intestazioni) Insignia Home (Casa) è sguassate l'intestazione di tabella duplicata da i dati utilizendu un filtru:

Salvà tuttu fattu cù u cumandamentu Chiudi è carica - Chiudi è carica in... (Close & Load - Close & Load to...) Insignia Home (Casa), è in a finestra chì si apre, selezziunate l'opzione Cunnessione solu (Solu a cunnessione):

Tuttu. Resta solu à custruisce un riassuntu. Per fà questu, andate à a tabulazione Inserisce - PivotTable (Inserisci - Pivot Table), sceglite l'opzione Utilizà a fonte di dati esterni (Usà una fonte di dati esterna)è dopu clicchendu u buttone Sceglite a cunnessione, a nostra dumanda. A più creazione è cunfigurazione di u pivot si faci in modu cumpletamente standard trascinendu i campi chì avemu bisognu in l'area di fila, colonne è valori:

Se i dati di a fonte cambianu in u futuru o uni pochi di fogli di magazzini sò aghjuntu, allora serà abbastanza per aghjurnà a quistione è u nostru riassuntu cù u cumandimu. Refresh tuttu Insignia Dati (Dati - Refresh All).

Metudu 2. Uniscemu e tavule cù u cumandimu UNION SQL in una macro

Una altra suluzione à u nostru prublema hè rapprisintata da questa macro, chì crea un settore di dati (cache) per a tabella pivot cù u cumandimu. UNITÀ Lingua di dumanda SQL. Stu cumandimu combina tavule da tutti i specificati in u array Sheet Names fogli di u libru in una sola tabella di dati. Vale à dì, invece di cupià fisicamente è incollà e varieghja da diverse fogli à unu, facemu u listessu in a RAM di l'urdinatore. Allora a macro aghjunghje un novu fogliu cù u nome datu (variable ResultSheetName) è crea un riassuntu cumpletu (!) nantu à ellu basatu annantu à a cache cullighjata.

Per utilizà una macro, utilizate u buttone Visual Basic in a tabulazione documentaliste (Sviluppatore) o scorciatoia da tastiera Alt+F11. Allora inserimu un novu modulu viotu attraversu u menu Inserisce - Modulu è copià quì u codice seguente:

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 di fogliu induve u pivot resultante serà mostratu ResultSheetName = "Pivot di foglia" nomi cù e tabelle di origine SheetsNames = Array ("Alpha", "Beta", "Gamma", "Delta") 'formemu una cache per e tabelle da i 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) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Proprietà Estesa=""Excel 8.0;" ""), vbNullString ) End With 're-create the sheet to display the resulting pivot table On Error Resume Next Application.DisplayAlerts = False Worksheets (ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'mostra u riassuntu di cache generatu nantu à questa foglia Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Niente cù wsPivot objPivotCache.TablewPivotCache.TablewPivotCache. objPivotCache = Nothing Range ("A3").Select End With End Sub    

A macro finita pò esse eseguita cù una scurciatoia di tastiera Alt+F8 o u buttone Macros nantu à a tabulazione documentaliste (Sviluppatore - Macros).

Cons di stu approcciu:

• I dati ùn sò micca aghjurnati perchè u cache ùn hà micca cunnessione cù e tavule fonte. Se cambiate i dati fonte, duvete eseguisce a macro di novu è custruisce u riassuntu di novu.
• Quandu cambia u numeru di fogli, hè necessariu edità u codice macro (array Sheet Names).
  

Ma à a fine avemu una vera table pivot cumpleta, custruita nantu à parechje gamme da diverse fogli:

Voilà!

Nota tecnica: Se avete un errore cum'è "Provider micca registratu" quandu eseguite a macro, allora più prubabilmente avete una versione di 64 bit di Excel o una versione incompleta di Office hè installata (senza Accessu). Per riparà a situazione, rimpiazzà u frammentu in u codice macro:

	 Furnisseur=Microsoft.Jet.OLEDB.4.0;  

a:

	Fornitore=Microsoft.ACE.OLEDB.12.0;  

È scaricate è installate u mutore di trattamentu di dati gratuitu da Access da u situ Microsoft - Microsoft Access Database Engine 2010 Redistributable

Metudu 3: Consolidate PivotTable Wizard da Vecchie Versioni di Excel

Stu metudu hè un pocu anticu, ma vale a pena menzionatu. Parlendu formalmente, in tutte e versioni finu à u 2003 inclusu, ci era una opzione in u PivotTable Wizard per "custruì un pivot per parechji intervalli di cunsulidazione". In ogni casu, un rapportu custruitu in questu modu, sfurtunatamenti, serà solu una sembianza pietosa di un veru riassuntu cumpletu è ùn sustene micca assai di i "chips" di i tavoli pivot cunvinziunali:

In un tali pivot, ùn ci hè micca intestazioni di culonna in a lista di u campu, ùn ci hè micca un paràmetru di struttura flexible, u settore di funzioni utilizati hè limitatu, è, in generale, tuttu questu ùn hè micca assai simili à una table pivot. Forse hè per quessa chì, à partesi da u 2007, Microsoft hà eliminatu sta funzione da u dialogu standard quandu creava rapporti di tabella pivot. Avà sta funzione hè dispunibule solu per mezu di un buttone persunalizatu PivotTable Wizard(Assistant Table Pivot), chì, se vulete, pò esse aghjuntu à a Toolbar di Accessu Rapidu via File - Opzioni - Customize Quick Access Toolbar - Tutti i Cumandamenti (File - Opzioni - Customize Quick Access Toolbar - All Commands):

Dopu à cliccà nant'à u buttone aghjuntu, avete bisognu di selezziunà l'opzione apprupriata à u primu passu di l'assistente:

È dopu in a finestra dopu, selezziunate ogni intervallu à turnu è aghjunghje à a lista generale:

Ma, di novu, questu ùn hè micca un riassuntu cumpletu, cusì ùn aspettate micca troppu da ellu. Puderaghju cunsiglià sta opzione solu in casi assai simplici.

• Creazione di rapporti cù PivotTables
• Configurate i calculi in PivotTables
• Chì sò i macros, cumu si usanu, induve copià u codice VBA, etc.
• Raccolta di dati da parechje fogli à unu (PLEX add-on)