Com crear taules dinàmiques a Excel (mitjançant la funció TABLE & OFFSET)
Les taules dinàmiques d’Excel són les taules en què, quan s’hi insereix un valor nou, la taula ajusta la seva mida per si mateixa, per crear una taula dinàmica en Excel tenim dos mètodes diferents, el que és el que és crear una taula de dades secció de la taula mentre que una altra és mitjançant la funció de desplaçament, a les taules dinàmiques els informes i les taules dinàmiques també canvien a mesura que canvien les dades de la taula dinàmica.
Taules dinàmiques a Excel
Dinàmic en si mateix, significa un sistema de processador caracteritzat per un canvi constant o un canvi d’activitat. De la mateixa manera, a Excel, quan creem llistes o dades en un llibre de treball i en fem un informe, però si afegim dades o en traiem una, en movem o en canviem les dades, tot l’informe pot ser inexacte. Excel té una solució per a això com a taules dinàmiques.
Ara sorgeix la pregunta de per què necessitem el rang dinàmic o les taules dinàmiques. La resposta és que cada vegada que s’actualitza o modifica una llista o un interval de dades, no se sap que es canviarà l’informe segons el canvi de dades.
Bàsicament, hi ha dos avantatges principals de les taules dinàmiques:
- Un rang dinàmic s'ampliarà o es contraurà automàticament segons el canvi de dades.
- Les taules dinàmiques basades en la taula dinàmica a Excel es poden actualitzar automàticament quan es refresca la taula dinàmica.
Com es crea una taula dinàmica a Excel?
Hi ha dues maneres bàsiques d’utilitzar taules dinàmiques a Excel: 1) Ús de TAULES i 2) Ús de la funció OFFSET.
Podeu descarregar aquesta plantilla Excel de taula dinàmica aquí: plantilla Excel de taula dinàmica# 1: utilitzar taules per crear taules dinàmiques a Excel
Mitjançant les taules podem construir una taula dinàmica en excel i basar un pivot sobre la taula dinàmica.
Exemple
Tenim les dades següents,
Si fem una taula dinàmica amb aquest rang de dades normal des d’A1: E6, si inserim dades a la fila 7 no es reflectirà a la taula dinàmica.
Per tant, primer farem un rang dinàmic.
#1 – Seleccioneu les dades, és a dir, A1: E6.
#2 – A la inserció, feu clic a la pestanya Taules a la secció Taules.
#3 – Apareix un quadre de diàleg.
Com que les nostres dades tenen capçaleres, recordeu que marqueu a la casella "La meva taula té capçaleres" i feu clic a D'acord.
#4 – Es crea el nostre rang dinàmic.
#5 – Seleccioneu les dades i feu clic a les taules dinàmiques a la pestanya Insereix de la secció de taules Excel.
#6 – A mesura que hem creat la taula, pren un rang com la Taula 2. Feu clic a D'acord i a les taules dinàmiques Arrossegueu el producte a les files i les vendes als valors.
#7 – Ara a Full on tenim la nostra taula, inseriu Altres dades al 7è
A la taula dinàmica actualitzeu la taula dinàmica.
La nostra taula dinàmica dinàmica ha actualitzat automàticament les dades del producte 6 a la taula dinàmica.
# 2: utilitzar la funció OFFSET per crear una taula dinàmica a Excel
També podem utilitzar la funció OFFSET per crear taules dinàmiques en excel. Vegem un exemple d’aquest tipus.
Exemple
Tinc una llista de preus per als meus productes que faig servir per als meus càlculs,
Seleccioneu les dades i doneu-li un nom
Ara, sempre que em referiré a la llista de preus del conjunt de dades, em portarà a les dades del rang B2: C7 que té la meva llista de preus. Però si actualitzo una altra fila a les dades, encara em portarà al rang de B2: C7 perquè la nostra llista és estàtica.
Utilitzarem la funció Offset per fer que l'interval de dades sigui dinàmic.
#1 – A la pestanya Fórmules de l'interval definit, feu clic a Nom definit i apareixerà un quadre de diàleg.
#2 – Al quadre de noms, escriviu qualsevol nom, faré servir el PriceA. L’abast és el llibre de treball actual i actualment fa referència a la cel·la actual seleccionada que és B2.
A Es refereix a escriure la fórmula següent,
= compensació (Full2! $ B $ 2,1,0, contrari (Full2! $ B: $ B) -1,2)
= desplaçament (
#3 – Ara seleccioneu la cel·la inicial que és B2,
#4 – Ara hem d’escriure 1,0, ja que comptarà quantes files o columnes heu d’anar
#5 – Ara el necessitem per comptar les dades que hi hagi a la columna B i utilitzar-lo com a nombre de files, així que utilitzeu la funció COUNTA i seleccioneu la columna B.
#6 – Com que no volem que es compti la primera fila que és la capçalera del producte (1).
#7 – Ara el nombre de columnes sempre serà de dues, per tant, escriviu 2 i feu clic a D'acord.
#8 – Aquest interval de dades no serà visible de manera predeterminada, de manera que, per veure-ho, hem de fer clic a Gestor de noms a la pestanya Fórmula i seleccionar Producte,
#9 – Si fem clic a es refereix, es mostra l'interval de dades,
#10 – Ara afegiu un altre producte a la taula Producte 6.
#11 – Ara feu clic a Taula de productes al gestor de noms, també fa referència a les dades noves inserides,
Així és com podem utilitzar la funció Offset per crear taules dinàmiques.
Coses que cal recordar
- Les taules dinàmiques basades en l’interval dinàmic s’actualitzen automàticament quan s’actualitzen.
- L'ús de la funció de desplaçament a noms definits es pot veure des del gestor de noms a la pestanya fórmula.
