Contidos
Táboas dinámicas é unha das ferramentas máis poderosas de Excel. Permítenche analizar e resumir varios resumos de grandes cantidades de datos con só uns poucos clics do rato. Neste artigo, familiarizarémonos coas táboas dinámicas, entenderemos cales son, aprenderemos a crealas e personalizalas.
Este artigo foi escrito usando Excel 2010. O concepto de táboas dinámicas non cambiou moito ao longo dos anos, pero a forma de crealas é lixeiramente diferente en cada nova versión de Excel. Se tes unha versión de Excel que non é 2010, prepárate para que as capturas de pantalla deste artigo serán diferentes das que ves na túa pantalla.
Un pouco de historia
Nos primeiros días do software de follas de cálculo, a bola de regra Lotus 1-2-3. O seu dominio foi tan completo que os esforzos de Microsoft por desenvolver o seu propio software (Excel) como alternativa a Lotus parecían unha perda de tempo. Agora adiante ata 2010! Excel domina as follas de cálculo máis do que o fixo o código de Lotus na súa historia, e o número de persoas que aínda usan Lotus é próximo a cero. Como puido pasar isto? Cal foi o motivo de tan dramático xiro dos acontecementos?
Os analistas identifican dous factores principais:
- En primeiro lugar, Lotus decidiu que esta nova plataforma GUI chamada Windows era só unha moda pasaxeira que non duraría moito. Rexeitáronse a construír unha versión para Windows de Lotus 1-2-3 (pero só durante uns anos), predicindo que a versión DOS do seu software sería todo o que necesitarían os consumidores. Microsoft naturalmente desenvolveu Excel especificamente para Windows.
- En segundo lugar, Microsoft presentou unha ferramenta en Excel chamada táboas dinámicas que non estaba dispoñible en Lotus 1-2-3. As táboas dinámicas, exclusivas de Excel, demostraron ser tan útiles que a xente tendía a seguir coa nova suite de software de Excel en lugar de continuar con Lotus 1-2-3, que non as tiña.
As táboas dinámicas, xunto con subestimar o éxito de Windows en xeral, xogaron a marcha da morte para Lotus 1-2-3 e marcaron o inicio do éxito de Microsoft Excel.
Que son as táboas dinámicas?
Entón, cal é a mellor forma de caracterizar o que son as táboas dinámicas?
En termos sinxelos, as táboas dinámicas son resumos dalgúns datos, creados para facilitar a análise destes datos. A diferenza dos totais creados manualmente, as táboas dinámicas de Excel son interactivas. Unha vez creadas, podes modificalas facilmente se non dan a imaxe que esperabas. Con só un par de clics do rato, os totais pódense cambiar para que os títulos das columnas se convertan en títulos de fila e viceversa. Podes facer moitas cousas coas táboas dinámicas. En lugar de tratar de describir con palabras todas as características das táboas dinámicas, é máis fácil demostralo na práctica...
Os datos que analiza con táboas dinámicas non poden ser aleatorios. Deberían ser datos en bruto, como unha lista dalgún tipo. Por exemplo, podería tratarse dunha lista de vendas realizadas pola empresa nos últimos seis meses.
Observa os datos que aparecen na seguinte figura:
Teña en conta que non se trata de datos en bruto, xa que xa se resumiu. Na cela B3 vemos 30000 dólares, que probablemente sexa o resultado total que fixo James Cook en xaneiro. Onde están entón os datos orixinais? De onde saíu a cifra de 30000 dólares? Onde está a lista orixinal de vendas da que se derivou este total mensual? Está claro que alguén fixo un gran traballo organizando e ordenando todos os datos de vendas dos últimos seis meses e convertelos na táboa de totais que vemos. Canto tempo cres que levou? Hora? Dez en punto?
O feito é que a táboa anterior non é unha táboa dinámica. Foi elaborado a man a partir de datos en bruto almacenados noutro lugar e tardou polo menos un par de horas en procesarse. Só unha táboa de resumo deste tipo pódese crear usando táboas dinámicas en só uns segundos. Imos descubrir como...
Se volvemos á lista de vendas orixinal, veríase algo así:
Poderíache sorprenderte de que a partir desta lista de comercios coa axuda de táboas dinámicas e en só uns segundos, poidamos crear un informe de vendas mensual en Excel, que analizamos anteriormente. Si, podemos facelo e moito máis!
Como crear unha táboa dinámica?
En primeiro lugar, asegúrate de ter algúns datos de orixe nunha folla de Excel. A lista de transaccións financeiras é a máis típica que se produce. De feito, podería ser unha lista de calquera cousa: datos de contacto dos empregados, unha colección de CD ou os datos de consumo de combustible da túa empresa.
Entón, iniciamos Excel... e cargamos esa lista...
Despois de abrir esta lista en Excel, podemos comezar a crear unha táboa dinámica.
Seleccione calquera cela desta lista:
Despois na pestana inserción (Inserir) comando de selección Táboa dinámica (Táboa dinámica):
Aparecerá unha caixa de diálogo Crear táboa dinámica (Creando unha táboa dinámica) con dúas preguntas para ti:
- Que datos usar para crear unha nova táboa dinámica?
- Onde poñer a táboa dinámica?
Dado que no paso anterior xa seleccionamos unha das celas da lista, seleccionarase automaticamente toda a lista para crear unha táboa dinámica. Teña en conta que podemos seleccionar un rango diferente, unha táboa diferente e mesmo algunha fonte de datos externa como unha táboa de base de datos de Access ou MS-SQL. Ademais, temos que escoller onde colocar a nova táboa dinámica: nunha folla nova ou nunha das existentes. Neste exemplo, escolleremos a opción - Nova folla de traballo (a unha nova folla):
Excel creará unha nova folla e colocará nela unha táboa dinámica baleira:
En canto facemos clic en calquera cela da táboa dinámica, aparecerá outra caixa de diálogo: Lista de campos da táboa dinámica (Campos da táboa dinámica).
A lista de campos na parte superior da caixa de diálogo é unha lista de todos os títulos da lista orixinal. As catro áreas baleiras na parte inferior da pantalla permítenche dicir á táboa dinámica como queres resumir os datos. Mentres estas zonas estean baleiras, tampouco hai nada na táboa. Todo o que temos que facer é arrastrar os títulos desde a zona superior ata as áreas baleiras de abaixo. Ao mesmo tempo, xérase automaticamente unha táboa dinámica, de acordo coas nosas instrucións. Se cometemos un erro, podemos eliminar os títulos da zona inferior ou arrastrar outros para substituílos.
Área Valores (Significados) é probablemente o máis importante dos catro. Que título se coloca nesta área determina que datos se resumirán (suma, media, máximo, mínimo, etc.) Case sempre son valores numéricos. Un excelente candidato para un lugar nesta área son os datos baixo o título cantidade (Custo) da nosa táboa orixinal. Arrastra este título ata a zona Valores (Valores):
Teña en conta que o título cantidade agora está marcado cunha marca de verificación, e na zona Valores (Valores) apareceu unha entrada Suma da cantidade (campo Importe Importe), indicando que a columna cantidade resumido.
Se observamos a propia táboa dinámica, veremos a suma de todos os valores da columna cantidade táboa orixinal.
Entón, creouse a nosa primeira táboa dinámica! Conveniente, pero non especialmente impresionante. Probablemente queremos obter máis información sobre os nosos datos da que temos actualmente.
Pasemos aos datos orixinais e intentemos identificar unha ou máis columnas que se poidan usar para dividir esta suma. Por exemplo, podemos formar a nosa táboa dinámica de tal xeito que o importe total das vendas se calcule para cada vendedor individualmente. Eses. engadiranse filas á nosa táboa dinámica co nome de cada vendedor da empresa e o seu importe total de vendas. Para conseguir este resultado, basta con arrastrar o título vendedor (representante de vendas) á rexión Etiquetas de filas (Cordas):
Faise máis interesante! A nosa táboa dinámica comeza a tomar forma...
Ver os beneficios? Nun par de clics, creamos unha táboa que levaría moito tempo creala manualmente.
Que máis podemos facer? Ben, en certo sentido, a nosa táboa dinámica está lista. Creamos un resumo útil dos datos orixinais. Información importante xa recibida! No resto deste artigo, analizaremos algunhas formas de crear táboas dinámicas máis complexas, ademais de aprender a personalizalas.
Configuración da táboa dinámica
En primeiro lugar, podemos crear unha táboa dinámica bidimensional. Imos facelo usando o título da columna Forma de pago (Método de pago). Só tes que arrastrar o título Forma de pago á zona Etiquetas de columnas (Columnas):
Obtemos o resultado:
Parece moi chulo!
Agora imos facer unha táboa tridimensional. Como sería unha mesa así? Vexamos…
Arrastra a cabeceira Paquete (Complexo) á zona filtros de informes (Filtros):
Fíxate onde está...
Isto dános a oportunidade de filtrar o informe en función de "Que complexo de vacacións se pagou". Por exemplo, podemos ver un desglose por vendedores e por métodos de pago para todos os complexos, ou nun par de clics do rato, cambiar a vista da táboa dinámica e mostrar o mesmo desglose só para os que pediron o complexo Sunseekers.
Entón, se entendes isto correctamente, a nosa táboa dinámica pódese chamar tridimensional. Seguimos configurando...
Se de súpeto resulta que só o pago con cheque e tarxeta de crédito (é dicir, pago sen efectivo) debería mostrarse na táboa dinámica, entón podemos desactivar a visualización do título Efectivo (Efectivo). Para iso, xunto Etiquetas de columnas prema na frecha cara abaixo e desmarque a caixa do menú despregable Efectivo:
Vexamos como é agora a nosa táboa dinámica. Como vedes, a columna Efectivo desapareceu dela.
Formato de táboas dinámicas en Excel
As táboas dinámicas son obviamente unha ferramenta moi poderosa, pero ata agora os resultados parecen un pouco sinxelos e aburridos. Por exemplo, os números que sumamos non parecen cantidades en dólares, son só números. Imos arranxar isto.
É tentador facer o que estás afeito en tal situación e simplemente seleccionar toda a táboa (ou a folla enteira) e usar os botóns estándar de formato de números da barra de ferramentas para definir o formato desexado. O problema con este enfoque é que se cambias a estrutura da táboa dinámica no futuro (o que ocorre cun 99 % de posibilidades), perderase o formato. O que necesitamos é un xeito de facelo (case) permanente.
Primeiro, imos atopar a entrada Suma da cantidade in Valores (Valores) e prema nel. No menú que aparece, seleccione o elemento Configuración do campo de valor (Opcións de campo de valor):
Aparecerá unha caixa de diálogo Configuración do campo de valor (Opcións de campo de valor).
Fai clic no botón Formato de número (Formato numérico), abrirase un cadro de diálogo. Formatear celas (formato de cela):
Da lista categoría (Formatos numéricos) seleccione Contabilidade (Financeiro) e establece o número de cifras decimais en cero. Agora preme algunhas veces OKpara volver á nosa táboa dinámica.
Como podes ver, os números están formateados como cantidades en dólares.
Mentres estamos no formato, imos configurar o formato para toda a táboa dinámica. Hai varias formas de facelo. Usamos o que é máis sinxelo...
Prema na Ferramentas de táboa dinámica: deseño (Traballando con táboas dinámicas: Construtor):
A continuación, expanda o menú facendo clic na frecha situada na esquina inferior dereita da sección Estilos de táboa dinámica (Estilos de táboa dinámica) para ver a extensa colección de estilos en liña:
Escolle calquera estilo axeitado e mira o resultado na túa táboa dinámica:
Outras opcións de configuración da táboa dinámica en Excel
Ás veces cómpre filtrar os datos por datas. Por exemplo, na nosa lista de comercios hai moitas, moitas datas. Excel ofrece unha ferramenta para agrupar datos por día, mes, ano, etc. A ver como se fai.
Primeiro elimina a entrada. Forma de pago da comarca Etiquetas de columnas (Columnas). Para iso, arrástrao de novo á lista de títulos e, no seu lugar, move o título Data reservada (data da reserva):
Como podes ver, isto inutilizou temporalmente a nosa táboa dinámica. Excel creou unha columna separada para cada data na que se realizou un intercambio. Como resultado, obtivemos unha mesa moi ampla!
Para solucionar isto, fai clic co botón dereito en calquera data e selecciona no menú contextual GRUPO (Grupo):
Aparecerá o cadro de diálogo de agrupación. Escollemos Meses (Meses) e prema OK:
Voila! Esta táboa é moito máis útil:
Por certo, esta táboa é case idéntica á que se mostra ao comezo do artigo, onde os totais de vendas foron compilados manualmente.
Hai outro punto moi importante que debes saber! Podes crear non un, senón varios niveis de títulos de fila (ou columna):
... e quedará así...
O mesmo pódese facer cos títulos das columnas (ou incluso cos filtros).
Volvamos á forma orixinal da táboa e vexamos como mostrar medias en lugar de sumas.
Para comezar, fai clic en Suma da cantidade e no menú que aparece seleccionar Configuración do campo de valor (Opcións de campo de valor):
A lista Resume o campo de valor por (Operación) no cadro de diálogo Configuración do campo de valor (Opcións de campo de valor) seleccione media (Media):
Ao mesmo tempo, mentres esteamos aquí, cambiemos Nome personalizado (Nome personalizado) con Media da cantidade (campo Importe Importe) a algo máis curto. Introduza neste campo algo así Promedio:
prensa OK e a ver que pasa. Teña en conta que todos os valores cambiaron de totais a medias e a cabeceira da táboa (na cela superior esquerda) cambiou a Promedio:
Se queres, podes obter inmediatamente a cantidade, a media e o número (vendas) nunha táboa dinámica.
Aquí tes unha guía paso a paso sobre como facelo, comezando cunha táboa dinámica baleira:
- Arrastra a cabeceira vendedor (representante de vendas) á rexión Etiquetas de columnas (Columnas).
- Arrastra o título tres veces cantidade (Custo) á zona Valores (Valores).
- Para o primeiro campo cantidade cambiar o título a Total (Cantidade) e o formato de número neste campo é Contabilidade (Financeiro). O número de cifras decimais é cero.
- Segundo campo cantidade nome Mediae, configure a operación para iso media (Promedio) e o formato de número neste campo tamén cambian a Contabilidade (Financeira) con cero cifras decimais.
- Para o terceiro campo cantidade establecer un título Contar e unha operación para el - Contar (Cantidade)
- No Etiquetas de columnas Campo (Columnas) creado automaticamente Σ Valores (Σ Valores) – arrástreo ata a zona Etiquetas de filas (liñas)
Isto é o que acabaremos:
Importe total, valor medio e número de vendas, todo nunha táboa dinámica.
Conclusión
As táboas dinámicas de Microsoft Excel conteñen moitas funcións e configuracións. Nun artigo tan pequeno, nin sequera están preto de cubrilos todos. Sería necesario un pequeno libro ou un sitio web grande para describir completamente todas as posibilidades das táboas dinámicas. Os lectores atrevidos e curiosos poden continuar coa súa exploración das táboas dinámicas. Para facelo, só tes que facer clic co botón dereito en case calquera elemento da táboa dinámica e ver que funcións e opcións se abren. Na cinta atoparás dúas pestanas: Ferramentas da táboa dinámica: opcións (análise) e Proxecto (Construtor). Non teñas medo de cometer un erro, sempre podes eliminar a táboa dinámica e comezar de novo. Tes unha oportunidade que os usuarios desde hai moito tempo de DOS e Lotus 1-2-3 nunca tiveron.