Taula dinàmica en diversos intervals de dades

Formulació del problema

Les taules dinàmiques són una de les eines més sorprenents d'Excel. Però fins ara, malauradament, cap de les versions d'Excel pot fer una cosa tan senzilla i necessària sobre la marxa com crear un resum per a diversos rangs de dades inicials situats, per exemple, en diferents fulls o en diferents taules:

Abans de començar, aclarim un parell de punts. A priori, crec que es compleixen les condicions següents a les nostres dades:

• Les taules poden tenir qualsevol nombre de files amb qualsevol dada, però han de tenir la mateixa capçalera.
• No hi hauria d'haver dades addicionals als fulls amb taules font. Un full - una taula. Per controlar, us recomano que utilitzeu una drecera de teclat Ctrl+Final, que us trasllada a l'última cel·la utilitzada del full de treball. Idealment, aquesta hauria de ser l'última cel·la de la taula de dades. Si quan feu clic a Ctrl+Final es ressalta qualsevol cel·la buida a la dreta o a sota de la taula: suprimiu aquestes columnes buides a la dreta o les files que hi ha a sota de la taula després de la taula i deseu el fitxer.

Mètode 1: creeu taules per a un pivot mitjançant Power Query

A partir de la versió de 2010 per a Excel, hi ha un complement gratuït de Power Query que pot recopilar i transformar qualsevol dada i després donar-la com a font per crear una taula dinàmica. Resoldre el nostre problema amb l'ajuda d'aquest complement no és gens difícil.

En primer lloc, creem un nou fitxer buit a Excel; el muntatge es farà i després es crearà una taula dinàmica.

Després a la pestanya dades (si teniu Excel 2016 o posterior) o a la pestanya Power Consulta (si teniu Excel 2010-2013) seleccioneu l'ordre Crea una consulta - Des del fitxer - Excel (Obtén dades — Del fitxer — Excel) i especifiqueu el fitxer font amb les taules que cal recollir:

A la finestra que apareix, seleccioneu qualsevol full (no importa quin) i premeu el botó de sota Canviar (Edita):

La finestra de l'Editor de consultes de Power Query s'hauria d'obrir a sobre d'Excel. A la part dreta de la finestra del panell Paràmetres de sol·licitud suprimiu tots els passos creats automàticament excepte el primer: font (Font):

Ara veiem una llista general de tots els fulls. Si a més dels fulls de dades hi ha altres fulls laterals al fitxer, en aquest pas la nostra tasca és seleccionar només aquells fulls dels quals cal carregar informació, excloent tots els altres utilitzant el filtre de la capçalera de la taula:

Suprimeix totes les columnes excepte la columna dadesfent clic amb el botó dret en un encapçalament de columna i seleccionant Suprimeix altres columnes (Eliminar altres columnes):

A continuació, podeu ampliar el contingut de les taules recollides fent clic a la doble fletxa a la part superior de la columna (casella de selecció Utilitzeu el nom de la columna original com a prefix pots desactivar-lo):

Si ho heu fet tot correctament, en aquest punt hauríeu de veure el contingut de totes les taules recopilades una sota l'altra:

Queda per pujar la primera fila a la capçalera de la taula amb el botó Utilitzeu la primera línia com a capçaleres (Utilitzeu la primera fila com a capçaleres) llengüeta Home (Inici) i elimineu les capçaleres de taula duplicades de les dades mitjançant un filtre:

Desa tot el fet amb l'ordre Tancar i carregar: tancar i carregar a... (Tancar i carregar — Tancar i carregar a...) llengüeta Home (Inici), i a la finestra que s'obre, seleccioneu l'opció Només connexió (Només connexió):

Tot. Només queda fer un resum. Per fer-ho, aneu a la pestanya Insereix - Taula dinàmica (Insereix — Taula dinàmica), tria l'opció Utilitzeu una font de dades externa (Feu servir una font de dades externa)i després fent clic al botó Seleccioneu la connexió, la nostra petició. La creació i configuració posteriors del pivot es produeix d'una manera completament estàndard arrossegant els camps que necessitem a l'àrea de files, columnes i valors:

Si les dades d'origen canvien en el futur o s'afegeixen uns quants fulls de botiga més, n'hi haurà prou amb actualitzar la consulta i el nostre resum mitjançant l'ordre Actualitza-ho tot llengüeta dades (Dades — Actualitza-ho tot).

Mètode 2. Unim taules amb l'ordre UNION SQL en una macro

Una altra solució al nostre problema està representada per aquesta macro, que crea un conjunt de dades (caché) per a la taula dinàmica mitjançant l'ordre UNITY Llenguatge de consulta SQL. Aquesta ordre combina taules de totes les especificades a la matriu Noms de fulls fulls del llibre en una única taula de dades. És a dir, en lloc de copiar i enganxar físicament intervals de diferents fulls a un, fem el mateix a la memòria RAM de l'ordinador. A continuació, la macro afegeix un nou full amb el nom donat (variable ResultSheetName) i en crea un resum complet (!) basat en la memòria cau recollida.

Per utilitzar una macro, utilitzeu el botó Visual Basic de la pestanya revelador (Desenvolupador) o drecera de teclat Alt+F11. A continuació, inserim un nou mòdul buit a través del menú Inserir – Mòdul i copieu-hi el codi següent:

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 'nom del full on es mostrarà el pivot resultant ResultSheetName = "Matriu del full de resultats" ' noms amb taules font SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'formem una memòria cau per a taules a partir de fulls de SheetsNames Amb ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Següent i Set objRS = CreateObject("ADODB.Recordset") objRS .Obre Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Finalitzeu amb 'torneu a crear el full per mostrar la taula dinàmica resultant En cas d'error Reprèn la següent aplicació.DisplayAlerts = Fals Fulls de treball (ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'mostra el resum de la memòria cau generada en aquest full Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Res amb wsPivot objPivotCache.=CreatePivotCache.=TablePivotRache. objPivotCache = Nothing Range("A3").Seleccioneu End With End Sub    

La macro acabada es pot executar amb una drecera de teclat Alt+F8 o el botó Macros de la pestanya revelador (Desenvolupador - Macros).

Contres d'aquest enfocament:

• Les dades no s'actualitzen perquè la memòria cau no té connexió amb les taules d'origen. Si canvieu les dades d'origen, heu d'executar la macro de nou i tornar a crear el resum.
• Quan es canvia el nombre de fulls, cal editar el codi macro (matriu Noms de fulls).
  

Però al final obtenim una autèntica taula dinàmica completa, construïda en diverses gammes de diferents fulls:

Voilà!

Nota tècnica: si obteniu un error com "Proveïdor no registrat" ​​quan executeu la macro, és probable que tingueu una versió d'Excel de 64 bits o una versió incompleta d'Office instal·lada (sense accés). Per solucionar la situació, substituïu el fragment del codi de macro:

	 Proveïdor=Microsoft.Jet.OLEDB.4.0;  

a:

	Proveïdor=Microsoft.ACE.OLEDB.12.0;  

I descarregueu i instal·leu el motor de processament de dades gratuït des d'Access des del lloc web de Microsoft: Microsoft Access Database Engine 2010 Redistributable

Mètode 3: Consolideu l'assistent de taula dinàmica a partir de versions antigues d'Excel

Aquest mètode està una mica obsolet, però val la pena esmentar-ho. Parlant formalment, en totes les versions fins al 2003 inclòs, hi havia una opció a l'assistent de taula dinàmica per "crear un pivot per a diversos intervals de consolidació". Tanmateix, un informe construït d'aquesta manera, malauradament, només serà una lamentable aparença d'un resum real i no admet molts dels "xips" de les taules dinàmiques convencionals:

En aquest pivot, no hi ha cap encapçalament de columnes a la llista de camps, no hi ha cap configuració d'estructura flexible, el conjunt de funcions utilitzades és limitat i, en general, tot això no s'assembla gaire a una taula dinàmica. Potser per això, a partir de l'any 2007, Microsoft va eliminar aquesta funció del diàleg estàndard en crear informes de taula dinàmica. Ara aquesta funció només està disponible mitjançant un botó personalitzat Assistent de taula dinàmica(Assistent de taula dinàmica), que, si es desitja, es pot afegir a la barra d'eines d'accés ràpid mitjançant Fitxer - Opcions - Personalitza la barra d'eines d'accés ràpid - Totes les ordres (Fitxer — Opcions — Personalitza la barra d'eines d'accés ràpid — Totes les ordres):

Després de fer clic al botó afegit, heu de seleccionar l'opció adequada al primer pas de l'assistent:

A continuació, a la finestra següent, seleccioneu cada interval al seu torn i afegiu-lo a la llista general:

Però, de nou, aquest no és un resum complet, així que no n'espereu massa. Només puc recomanar aquesta opció en casos molt senzills.

• Creació d'informes amb taules dinàmiques
• Configura els càlculs a les taules dinàmiques
• Què són les macros, com utilitzar-les, on copiar el codi VBA, etc.
• Recollida de dades de diversos fulls a un (complement PLEX)