contingut
Taules de pivot és una de les eines més potents d'Excel. Us permeten analitzar i resumir diversos resums de grans quantitats de dades amb només uns quants clics del ratolí. En aquest article, ens familiaritzarem amb les taules dinàmiques, entendrem què són, aprendrem a crear-les i personalitzar-les.
Aquest article s'ha escrit amb Excel 2010. El concepte de taules dinàmiques no ha canviat gaire al llarg dels anys, però la manera de crear-les és lleugerament diferent a cada nova versió d'Excel. Si teniu una versió d'Excel que no és 2010, estigueu preparats perquè les captures de pantalla d'aquest article diferiran de les que veieu a la pantalla.
Una mica d'història
En els primers dies del programari de fulls de càlcul, la bola de regla Lotus 1-2-3. El seu domini va ser tan complet que els esforços de Microsoft per desenvolupar el seu propi programari (Excel) com a alternativa a Lotus semblaven una pèrdua de temps. Ara endavant ràpid fins al 2010! Excel domina els fulls de càlcul més del que el codi Lotus ha fet mai en la seva història, i el nombre de persones que encara utilitzen Lotus és gairebé zero. Com podria passar això? Quina va ser la raó d'un gir tan dramàtic dels esdeveniments?
Els analistes identifiquen dos factors principals:
- Primer, Lotus va decidir que aquesta nova plataforma GUI anomenada Windows era només una moda passatgera que no duraria gaire. Es van negar a construir una versió de Windows de Lotus 1-2-3 (però només durant uns quants anys), predint que la versió DOS del seu programari seria tot el que necessitarien els consumidors. Microsoft naturalment va desenvolupar Excel específicament per a Windows.
- En segon lloc, Microsoft va introduir una eina a Excel anomenada Taules dinàmiques que no estava disponible al Lotus 1-2-3. Les taules dinàmiques, exclusives d'Excel, van resultar ser tan útils aclaparadorament que la gent va tendir a quedar-se amb la nova suite de programari Excel en lloc de continuar amb Lotus 1-2-3, que no les tenia.
Les taules dinàmiques, juntament amb subestimar l'èxit de Windows en general, van fer la marxa de la mort per a Lotus 1-2-3 i van marcar l'èxit de Microsoft Excel.
Què són les taules dinàmiques?
Aleshores, quina és la millor manera de caracteritzar què són les taules dinàmiques?
En termes senzills, les taules dinàmiques són resums d'algunes dades, creades per facilitar l'anàlisi d'aquestes dades. A diferència dels totals creats manualment, les taules dinàmiques d'Excel són interactives. Un cop creats, podeu modificar-los fàcilment si no donen la imatge que esperàveu. Amb només un parell de clics del ratolí, els totals es poden invertir de manera que els encapçalaments de columnes es converteixin en encapçalaments de fila i viceversa. Podeu fer moltes coses amb les taules dinàmiques. En lloc d'intentar descriure amb paraules totes les característiques de les taules dinàmiques, és més fàcil demostrar-ho a la pràctica...
Les dades que analitzeu amb les taules dinàmiques no poden ser aleatòries. Haurien de ser dades en brut, com una llista d'algun tipus. Per exemple, podria ser una llista de vendes realitzades per l'empresa en els últims sis mesos.
Mireu les dades que es mostren a la figura següent:
Tingueu en compte que no es tracta de dades en brut, ja que ja s'han resumit. A la cel·la B3 veiem 30000 dòlars, que probablement és el resultat total que va fer James Cook al gener. On són llavors les dades originals? D'on prové la xifra de 30000 dòlars? On és la llista original de vendes de la qual es va derivar aquest total mensual? És evident que algú ha fet una gran feina organitzant i ordenant totes les dades de vendes dels últims sis mesos i convertint-les en la taula de totals que veiem. Quant de temps creus que va trigar? Hores? Les deu en punt?
El fet és que la taula anterior no és una taula dinàmica. Va ser fet a mà a partir de dades en brut emmagatzemades en altres llocs i va trigar almenys un parell d'hores a processar-se. Només es pot crear una taula resum amb taules dinàmiques en pocs segons. Anem a esbrinar com...
Si tornem a la llista de vendes original, semblaria així:
Us pot sorprendre que a partir d'aquesta llista d'operacions amb l'ajuda de taules dinàmiques i en pocs segons, puguem crear un informe de vendes mensual a Excel, que hem analitzat anteriorment. Sí, podem fer això i més!
Com crear una taula dinàmica?
Primer, assegureu-vos que teniu algunes dades font en un full d'Excel. La llista de transaccions financeres és la més típica que es produeix. De fet, podria ser una llista de qualsevol cosa: dades de contacte dels empleats, una col·lecció de CD o les dades de consum de combustible de la vostra empresa.
Per tant, comencem l'Excel... i carreguem aquesta llista...
Després d'haver obert aquesta llista a Excel, podem començar a crear una taula dinàmica.
Seleccioneu qualsevol cel·la d'aquesta llista:
Després a la pestanya inserció (Insereix) l'ordre de selecció Taula dinàmica (Taula dinàmica):
Apareixerà un quadre de diàleg Crea una taula dinàmica (Creant una taula dinàmica) amb dues preguntes per a tu:
- Quines dades cal utilitzar per crear una taula dinàmica nova?
- On posar la taula dinàmica?
Com que al pas anterior ja hem seleccionat una de les cel·les de la llista, tota la llista es seleccionarà automàticament per crear una taula dinàmica. Tingueu en compte que podem seleccionar un rang diferent, una taula diferent i fins i tot alguna font de dades externa, com ara una taula de base de dades Access o MS-SQL. A més, hem de triar on col·locar la nova taula dinàmica: en un full nou o en un dels existents. En aquest exemple, triarem l'opció - Nou full de treball (a un full nou):
Excel crearà un full nou i hi col·locarà una taula dinàmica buida:
Tan bon punt fem clic a qualsevol cel·la de la taula dinàmica, apareixerà un altre quadre de diàleg: Llista de camps de la taula dinàmica (Camps de la taula dinàmica).
La llista de camps a la part superior del quadre de diàleg és una llista de tots els títols de la llista original. Les quatre àrees buides a la part inferior de la pantalla us permeten indicar a la taula dinàmica com voleu resumir les dades. Mentre aquestes zones estiguin buides, tampoc no hi ha res a la taula. Tot el que hem de fer és arrossegar els encapçalaments de l'àrea superior a les àrees buides de sota. Al mateix temps, es genera automàticament una taula dinàmica, d'acord amb les nostres instruccions. Si ens equivoquem, podem eliminar els encapçalaments de la zona inferior o arrossegar-ne d'altres per substituir-los.
Àrea Valors (Significats) és probablement el més important dels quatre. Quin encapçalament es col·loca en aquesta àrea determina quines dades es resumiran (suma, mitjana, màxima, mínima, etc.) Gairebé sempre són valors numèrics. Un excel·lent candidat per a una plaça en aquesta àrea són les dades de l'encapçalament quantitat (Cost) de la nostra taula original. Arrossegueu aquest títol a l'àrea Valors (Valors):
Tingueu en compte que el títol quantitat ara està marcat amb una marca de verificació, i a la zona Valors (Valors) ha aparegut una entrada Suma de la quantitat (camp Import Import), indicant que la columna quantitat resumit.
Si mirem la pròpia taula dinàmica, veurem la suma de tots els valors de la columna quantitat taula original.
Així doncs, es crea la nostra primera taula dinàmica! Convenient, però no especialment impressionant. Probablement volem obtenir més informació sobre les nostres dades de la que tenim actualment.
Passem a les dades originals i intentem identificar una o més columnes que es poden utilitzar per dividir aquesta suma. Per exemple, podem formar la nostra taula dinàmica de manera que la quantitat total de vendes es calculi per a cada venedor individualment. Aquells. s'afegiran files a la nostra taula dinàmica amb el nom de cada venedor de l'empresa i el seu import total de vendes. Per aconseguir aquest resultat, només cal arrossegar el títol persona de vendes (representant de vendes) a la regió Etiquetes de fila (Cordes):
Es fa més interessant! La nostra taula dinàmica comença a prendre forma...
Veus els beneficis? En un parell de clics, vam crear una taula que hauria trigat molt de temps a crear-se manualment.
Què més podem fer? Bé, en cert sentit, la nostra taula dinàmica està preparada. Hem creat un resum útil de les dades originals. Informació important ja rebuda! A la resta d'aquest article, veurem algunes maneres de crear taules dinàmiques més complexes, així com aprendrem a personalitzar-les.
Configuració de la taula dinàmica
Primer, podem crear una taula dinàmica bidimensional. Fem-ho utilitzant l'encapçalament de la columna Mètode de pagament (Mètode de pagament). Només arrossegueu el títol Mètode de pagament a la zona Etiquetes de columnes (Columnes):
Obtenim el resultat:
Sembla molt xulo!
Ara fem una taula tridimensional. Com seria una taula així? A veure…
Arrossegueu la capçalera paquet (Complex) a la zona filtres d'informes (Filtres):
Fixa't on és...
Això ens dóna l'oportunitat de filtrar l'informe en funció de "Quin complex de vacances es va pagar". Per exemple, podem veure un desglossament per venedors i per mètodes de pagament per a tots els complexos, o amb un parell de clics del ratolí, canviar la vista de la taula dinàmica i mostrar el mateix desglossament només per a aquells que van demanar el complex. Sunseekers.
Per tant, si ho enteneu correctament, la nostra taula dinàmica es pot anomenar tridimensional. Continuem configurant...
Si de sobte resulta que només s'ha de mostrar el pagament amb xec i targeta de crèdit (és a dir, pagament sense efectiu) a la taula dinàmica, podem desactivar la visualització del títol Efectiu (Efectiu). Per això, al costat Etiquetes de columnes feu clic a la fletxa avall i desmarqueu la casella del menú desplegable Efectiu:
Vegem com és ara la nostra taula dinàmica. Com podeu veure, la columna Efectiu va desaparèixer d'ella.
Format de taules dinàmiques en Excel
Les taules dinàmiques són, òbviament, una eina molt potent, però fins ara els resultats semblen una mica senzills i avorrits. Per exemple, els números que sumem no semblen quantitats en dòlars, sinó que només són números. Arreglem això.
És temptador fer el que esteu acostumats en aquesta situació i simplement seleccioneu tota la taula (o el full sencer) i utilitzeu els botons estàndard de format de números de la barra d'eines per definir el format desitjat. El problema d'aquest enfocament és que si mai canvieu l'estructura de la taula dinàmica en el futur (cosa que passa amb un 99% de possibilitats), el format es perdrà. El que necessitem és una manera de fer-ho (quasi) permanent.
Primer, anem a trobar l'entrada Suma de la quantitat in Valors (Valors) i feu-hi clic. Al menú que apareix, seleccioneu l'element Configuració del camp de valor (Opcions de camp de valor):
Apareixerà un quadre de diàleg Configuració del camp de valor (Opcions de camp de valor).
premsa Format de número (Format de nombre), s'obrirà un quadre de diàleg. Format de cel·les (format de cel·la):
De la llista categoria (Formats de nombre) seleccioneu Comptabilitat (Financer) i establiu el nombre de decimals a zero. Ara premeu unes quantes vegades OKper tornar a la nostra taula dinàmica.
Com podeu veure, els números tenen el format d'imports en dòlars.
Mentre estem amb el format, configurem el format per a tota la taula dinàmica. Hi ha diverses maneres de fer-ho. Utilitzem el que és més senzill...
Feu clic al Eines de taula dinàmica: Disseny (Treballant amb taules dinàmiques: Constructor):
A continuació, amplieu el menú fent clic a la fletxa de l'extrem inferior dret de la secció Estils de taula dinàmica (Estils de taula dinàmica) per veure l'extensa col·lecció d'estils en línia:
Trieu qualsevol estil adequat i mireu el resultat a la vostra taula dinàmica:
Altres paràmetres de la taula dinàmica a Excel
De vegades cal filtrar les dades per dates. Per exemple, a la nostra llista d'oficis hi ha moltes, moltes dates. Excel proporciona una eina per agrupar dades per dia, mes, any, etc. A veure com es fa.
Primer elimineu l'entrada. Mètode de pagament de la regió Etiquetes de columnes (Columnes). Per fer-ho, arrossegueu-lo de nou a la llista de títols i, al seu lloc, moveu el títol Data reservada (data de la reserva):
Com podeu veure, això va inutilitzar temporalment la nostra taula dinàmica. Excel va crear una columna separada per a cada data en què es va fer un comerç. Com a resultat, hem aconseguit una taula molt àmplia!
Per solucionar-ho, feu clic amb el botó dret a qualsevol data i seleccioneu-lo al menú contextual GROUP (Grup):
Apareixerà el quadre de diàleg d'agrupació. Triem nosaltres Mesos (mesos) i feu clic OK:
Voila! Aquesta taula és molt més útil:
Per cert, aquesta taula és gairebé idèntica a la que es mostra al principi de l'article, on els totals de vendes es van compilar manualment.
Hi ha un altre punt molt important que has de conèixer! Podeu crear no un, sinó diversos nivells d'encapçalaments de fila (o columna):
... i semblarà així...
El mateix es pot fer amb els encapçalaments de columnes (o fins i tot amb els filtres).
Tornem a la forma original de la taula i veiem com mostrar les mitjanes en lloc de les sumes.
Per començar, feu clic a Suma de la quantitat i del menú que apareix seleccionar Configuració del camp de valor (Opcions de camp de valor):
La llista Resumeix el camp de valor per (Operació) al quadre de diàleg Configuració del camp de valor (Opcions de camp de valor) seleccioneu Mitjana (Mitjana):
Al mateix temps, mentre som aquí, canviem Nom personalitzat (Nom personalitzat) amb Mitjana de la quantitat (camp Import Import) a una cosa més curta. Introduïu en aquest camp alguna cosa així Mitjana:
Premsa OK i a veure què passa. Tingueu en compte que tots els valors han canviat de totals a mitjanes i la capçalera de la taula (a la cel·la superior esquerra) ha canviat a Mitjana:
Si voleu, podeu obtenir immediatament la quantitat, la mitjana i el nombre (vendes) en una taula dinàmica.
Aquí teniu una guia pas a pas sobre com fer-ho, començant amb una taula dinàmica buida:
- Arrossegueu la capçalera persona de vendes (representant de vendes) a la regió Etiquetes de columnes (Columnes).
- Arrossegueu el títol tres vegades quantitat (Cost) a l'àrea Valors (Valors).
- Per al primer camp quantitat canviar el títol a Total (Quant) i el format del número en aquest camp és Comptabilitat (Financer). El nombre de decimals és zero.
- Segon camp quantitat nom Mitjanae, establiu-ne l'operació Mitjana (Mitjana) i el format de nombre d'aquest camp també canvien a Comptabilitat (Financer) amb zero decimals.
- Per al tercer camp quantitat posar un títol Comptar i una operació per a ell - Comptar (Quantitat)
- A la Etiquetes de columnes Camp (Columnes) creat automàticament Σ Valors (Σ Valors): arrossegueu-lo a l'àrea Etiquetes de fila (Línies)
Això és el que acabarem:
Import total, valor mitjà i nombre de vendes, tot en una taula dinàmica!
Conclusió
Les taules dinàmiques de Microsoft Excel contenen moltes funcions i configuracions. En un article tan petit, ni tan sols estan a prop de cobrir-los tots. Caldria un llibre petit o un lloc web gran per descriure completament totes les possibilitats de les taules dinàmiques. Els lectors atrevits i curiosos poden continuar explorant les taules dinàmiques. Per fer-ho, només cal que feu clic amb el botó dret a gairebé qualsevol element de la taula dinàmica i vegeu quines funcions i paràmetres s'obren. A la cinta trobareu dues pestanyes: Eines de taula dinàmica: Opcions (anàlisi) i disseny (Constructor). No tingueu por d'equivocar-vos, sempre podeu esborrar la taula dinàmica i tornar a començar. Teniu una oportunitat que els usuaris de DOS i Lotus 1-2-3 durant molt de temps mai van tenir.