Tablas Dinámicas de Excel: La Guía Más Completa con Consejos Profesionales
En este artículo, intentaremos compartir nuestra profunda experiencia con las tablas dinámicas de Excel. Aquí encontrará algunos consejos muy poco comunes, que no podrá ver en los tutoriales o posts ordinarios sobre tablas dinámicas.
Este artículo se ha creado a partir de los tutoriales de formación interna preparados para los becarios de Someka. ¡Así que aquí encontrará trucos expertos al grano!
¡Comencemos!
Tabla de contenido
1. ¿Cuál es la lógica de las tablas dinámicas de Excel?
2. Las 3 primeras cosas que hay que cambiar
3. Opciones de diseño rápido de las tablas dinámicas
4. Cómo nombrar las tablas dinámicas
5. Diseño avanzado de tablas dinámicas
6. Formato de fechas y números en tablas dinámicas
7. Consejo adicional: Encontrar Discrepancias de Datos con Tablas Dinámicas
8. Palabras finales
1. ¿Cuál es la lógica de las tablas dinámicas de Excel?
En primer lugar, vamos a hablar de la lógica básica que hay detrás de las tablas dinámicas.
¿Qué hace realmente una tabla dinámica de Excel? Esta es una de las preguntas que hacemos en las entrevistas a nuestros candidatos a desarrolladores de Excel:
Si tuviera que realizar las mismas operaciones que los pivotes utilizando fórmulas, ¿qué fórmulas utilizaría?
La respuesta es muy fácil. Además de otras funciones, las tablas dinámicas esencialmente hacen grupos de datos según criterios seleccionados y luego cuentan, suman, obtienen medias de esos grupos.
Esencialmente, lo que hacen las tablas dinámicas es lo siguiente: Realizan ciertas operaciones sobre los datos de una manera específica, como contar o realizar otras operaciones rápidamente.
Pongamos un ejemplo. A continuación se muestra nuestra fuente de datos sin procesar:
Ahora queremos calcular el importe total de las ventas de cada zona. Tenemos dos opciones. Podemos utilizar tablas dinámicas, o podemos utilizar la función SUMIF.
La mayor belleza de una tabla dinámica es su facilidad de manipulación y su rapidez. Su mecanismo de funcionamiento es diferente al de las fórmulas, almacenando los datos en una memoria caché única dentro del archivo Excel.
Puede que estos detalles no sean esenciales, pero tenga en cuenta que la funcionalidad básica de una tabla dinámica es esencialmente la distribución de frecuencias. Por supuesto, hay muchas otras funciones, pero comprender este aspecto fundamental es crucial, y por eso lo tratamos en primer lugar.
¿Cómo crear tablas dinámicas de Excel?
Cubriremos algunas cosas muy básicas sobre la creación de una tabla dinámica. Comencemos con un archivo de Excel en blanco y los datos de origen.
Es muy fácil añadir una tabla dinámica: Seleccione sus datos de origen y vaya a Insertar > Tabla dinámica > Desde tabla/rango.
Pero, ¿dónde añadir su tabla dinámica?
Automáticamente le sugiere ‘Nueva hoja de cálculo’. Pero no tiene por qué ser así. También podemos seleccionar la misma hoja con los datos de origen. Por ejemplo, podemos seleccionar los datos y ordenarlos inmediatamente en la misma hoja, sin crear una hoja nueva.
Nota: También existe la opción «Añadir estos datos al modelo de datos». Cubriremos esto más adelante.
Si va a vincular más de dos tablas, entonces debería dominar Power Pivot para Excel, que proporciona una forma sencilla de crear tablas y gráficos dinámicos a partir de modelos de datos complejos.
2. Las 3 primeras cosas a cambiar
Lo primero que recomendamos hacer justo después de crear una tabla dinámica es ir a opciones y:
- Quitar el ajuste automático del ancho de las columnas al actualizar
- Marque la casilla de Valores de error mostrar en blanco
- Vaya a la pestaña Datos y ponga Número de elementos a conservar por campo como Ninguno
Permítame explicarle lo que significan estos cambios:
Autoajuste
Si deja marcada esta opción de autoajuste, se ajustará automáticamente la anchura de las columnas. Auto-ajustar puede ser útil dependiendo de la situación, pero normalmente manejamos nuestras Tablas Dinámicas manualmente, así que no lo necesitamos. Es más bien para usuarios profanos que podrían estropear accidentalmente el diseño.
Así que la configuración por defecto es el ajuste automático en Excel, porque es mejor tener un diseño feo que uno invisible. Pero no lo necesitará cuando se convierta en un potente usuario de tablas dinámicas de Excel.
Mostrar espacios en blanco para valores de error
Es para condiciones de error. Digamos que estamos calculando algo y hay una división por cero. Normalmente, aparecería un error, pero no queremos eso en nuestra tabla dinámica.
En lugar de mostrar un error, queremos que muestre un espacio en blanco. Según nuestra experiencia, esto suele ser mejor, pero recuerde que puede haber situaciones en las que quiera ver los errores.
¿Conservar los elementos eliminados?
Como ya se ha mencionado, las tablas dinámicas de Excel funcionan con una lógica de almacenamiento en caché. Por eso son rápidas.
Pero al almacenar en caché, las Tablas dinámicas le preguntan: «¿Desea conservar los elementos eliminados de la fuente de datos?».
A veces, puede haber razones lógicas para conservarlos. Pero, por lo general, no queremos conservarlos porque causan confusión. Imagine que añade un dato ficticio o erróneo y luego lo borra. Pero si deja abierta esta fetaura, conservará todos los elementos borrados aunque ya no existan en sus datos de origen.
En resumen, al crear una tabla dinámica, las tres primeras cosas que hay que hacer son: Vaya a opciones y 1. Elimine el ajuste automático, 2. Muestre el espacio en blanco por error, y 3. Establezca el valor de retención en Sin datos. Estos pasos deberían volverse automáticos.
3. Opciones de diseño rápido de la tabla dinámica
No daremos algunos consejos sobre el diseño rápido de la tabla dinámica. Para obtener una vista limpia a primera vista, realizamos estos cuatro cambios:
- No mostrar subtotales
- Grandes totales desactivados para filas y columnas
- Mostrar el diseño del informe en forma tabular
- El diseño del informe repite todas las etiquetas de los elementos
He aquí el resumen de estos cuatro cambios:
El diseño tabular de los formularios es una preferencia, pero ayuda en el análisis claro de los datos. Porque, tiene un aspecto agradable y mantiene la claridad.
Además, también puedeeliminar los botones más/menos para expandir y contraer. Normalmente los eliminamos para obtener un aspecto más limpio.
Otras formas de presentación pueden tener un aspecto más elegante, pero por razones de análisis de datos, estas opciones facilitan la copia de sus datos pivotantes y la realización de un análisis por separado o la obtención de la tabla pivotante de su tabla pivotante.
Pero tal vez, una vez realizado el análisis, prefiera otros diseños de disposición más atractivos para su informe final o cuadro de mandos.
4. Cómo nombrar las tablas dinámicas
Además, ponga siempre nombres descriptivos a sus tablas dinámicas de Excel. Esto ayuda a identificarlas, especialmente en escenarios complejos. Es una buena práctica, aunque no siempre la utilice.
Especialmente al añadir rebanadores o gráficos, este nombre le ayudará mucho.
¿Cómo nombrar una tabla dinámica?
En Herramientas de tabla dinámica > Analizar tabla dinámica, puede sustituir el nombre predeterminado por uno más descriptivo.
5. Diseño avanzado de tablas dinámicas de Excel
Hablemos ahora del diseño de las tablas dinámicas, una característica que mucha gente no suele explorar. Puede realizar varios cambios desde la pestaña de diseño. Por ejemplo, si no le gustan los encabezados de fila en negrita, puede optar por eliminar ese formato para que la tabla tenga un aspecto más limpio.
En primer lugar, si va a añadir su tabla pivotante a una hoja aparte, no dude en eliminar las líneas de cuadrícula para que el diseño tenga un aspecto más bonito.
En segundo lugar, puede modificar la anchura de las columnas en función de sus datos. Además, puede decidir la alineación de sus datos. Si la longitud de los datos varía mucho, le recomendamos alinearlos a la izquierda. Si los datos de entrada tienen en su mayoría una longitud similar, puede utilizar la opción Centrar.
Al diseñar sus tablas dinámicas, utilice siempre las flechitas para seleccionar las columnas dinámicas. De lo contrario, cuando añada nuevos datos y actualice su tabla dinámica, ¡ésta no mantendrá su diseño!
A continuación, puede seleccionar sus Opciones de estilo con las casillas de verificación correspondientes. Puede, por ejemplo, desactivar los Encabezados sin procesar si no los necesita.
Es importante tener en cuenta la diferencia en la selección de rangos a la hora de aplicar cambios. Si selecciona toda la tabla dinámica y aplica un formato, éste se aplicará a toda la tabla. Pero si selecciona sólo una columna o un área específica, el formato se aplicará sólo allí. Esta distinción es crucial para comprender cómo afectarán sus cambios a la tabla.
Diseño personalizado de tablas dinámicas
Mucha gente no sabe que puede crear su diseño de tabla dinámica personalizado.
Para ello, primero duplique un diseño existente y luego modifíquelo según sus preferencias. Para crear un duplicado, haga clic con el botón derecho en cualquier diseño predeterminado bajo los Estilos de tabla dinámica y haga clic en Duplicar.
A continuación, dé un nombre personalizado a su diseño.
Por ejemplo, puede cambiar el texto de la fila de cabecera para que no esté en negrita o ajustar el formato de la franja de la primera fila. Así es como puede crear un diseño único que se adapte a sus necesidades.
Para modificar su diseño personalizado, haga clic en el elemento de diseño que desee cambiar y, a continuación, en Formato.
Exploremos más a fondo las tablas dinámicas de Excel. Digamos que desea añadir un borde alrededor de toda la tabla. Puede elegir un color y aplicarlo, que es una personalización poco frecuente pero útil. Otro consejo es eliminar las líneas de cuadrícula para conseguir un aspecto más limpio. También puede cambiar el color de las rayas de las filas. Al modificar, puede explorar diferentes opciones para encontrar la que mejor le funcione.
Recuerde que los diseños predeterminados de las tablas dinámicas vienen con ciertos estilos preestablecidos, como los encabezados en negrita. Al crear su diseño, es libre de cambiar estos elementos. Esta personalización puede ser muy útil, especialmente si necesita un aspecto específico para su tabla dinámica, como en una plantilla corporativa.
¿Cómo añadir bordes exteriores rojos a nuestra tabla dinámica?
Primero seleccionaremos toda la tabla como nuestro elemento de tabla:
Después haremos clic en Formato, y realizaremos el cambio deseado en el cuadro de diálogo Formato:
Ahora nuestra tabla tiene un borde rojo.
A continuación se muestra un buen ejemplo de tablas dinámicas personalizadas:
– Esta imagen es de la plantilla de diario comercial de Someka que tiene un aspecto profesional con diseños de tablas pivotantes personalizadas –
Un truco único que hay que saber es cómo aplicar un borde. Si aplica un borde directamente a la tabla dinámica y luego la actualiza, el formato podría estropearse.
En su lugar, aplique el borde a las celdas que rodean la tabla dinámica. De este modo, el borde permanecerá intacto incluso después de actualizar.
Sin embargo, tenga en cuenta un inconveniente de este método. Si se añaden nuevos datos a la tabla dinámica, el borde no se ajustará automáticamente para incluirlos. Para obtener un diseño más dinámico y adaptable, es mejor crear su diseño personalizado en los ajustes de diseño de la tabla dinámica. De esta forma, el diseño se ajustará automáticamente a los cambios en los datos.
Recuerde que sus elecciones de diseño deben facilitar la lectura e interpretación de los datos. Por ejemplo, cuando calcule promedios, asegúrese de que el formato se aplicará también a futuras entradas de datos. Esto mantiene la tabla dinámica y sensible a los nuevos datos.
En conclusión, la clave está en equilibrar la funcionalidad con la estética, teniendo en cuenta cómo se utilizarán e interpretarán los datos. De ese modo, creará una tabla dinámica que no sólo será informativa, sino también fácil de usar.
6. Formato de fechas y números en las tablas dinámicas de Excel
Un problema común con las tablas dinámicas es el formateo de los datos en la sección de filas, sobre todo cuando se intenta cambiar el formato de los números.
Puede que descubra que cambiar el formato del número de la forma habitual no siempre se refleja en la tabla dinámica, aunque parezca cambiar en la propia celda. Esto se debe a que las Tablas Dinámicas manejan los datos de forma diferente, y su diseño garantiza ciertas limitaciones.
Por lo tanto, siempre debe cambiar el formato de fecha y número en la Configuración del campo de valor de su tabla dinámica.
Pero, ¿qué ocurre si no puede ver estos ajustes?
Aquí tiene un consejo profesional:
Para cambiar el formato, debe asegurarse de que toda la columna de los datos de origen tiene formato de fecha, sin espacios en blanco ni texto. Si intenta cambiar el formato mientras hay espacios en blanco o elementos no fechados en la columna, no funcionará. Esto puede requerir que limpie sus datos, eliminando cualquier espacio en blanco o elemento no fechado de sus datos de origen.
Otro punto importante es que si tiene divisores conectados a su tabla dinámica, pueden impedirle actualizar la fuente de datos. Debe eliminar primero estas conexiones antes de realizar cambios en los datos fuente.
Así que si el formato de fecha no está activo, puede haber dos razones detrás de eso:
- Puede que tenga filas en blanco en sus datos fuente
- Puede que tenga conexiones slicer relacionadas con esa tabla pivotante
Por lo tanto, para cambiar el formato de fecha o de número, elimine primero las conexiones slicer y asegúrese de que no tiene filas en blanco en sus datos de origen.
Fuentes dinámicas en la tabla dinámica
He aquí un consejo útil:
Cree un rango dinámico para sus datos fuente. De este modo, su tabla dinámica se ajustará automáticamente a medida que crezcan sus datos, sin incluir espacios en blanco no deseados. Puede crear un rango dinámico utilizando una fórmula OFFSET en el Gestor de nombres que ajuste la altura y la anchura de su rango en función del tamaño real de sus datos.
En primer lugar, calcule la altura de su rango dinámico con la fórmula MAX, luego vaya al Administrador de nombres y utilice la fórmula OFFSET y haga referencia a la celda de la fórmula MAX como la altura de su rango de datos Offset.
Sólo para profesionales:
¿Por qué no se puede cambiar el formato de los datos de origen en blanco?
Aquí tiene la respuesta. Si descomprime su archivo Excel (¡sí, puede descomprimir sus archivos Excel!), verá que todos los elementos son en realidad documentos XLM en esa carpeta zip.
Las tablas dinámicas también son archivos XLM grabados, y si tiene espacios en blanco en sus datos brutos, entonces ve sus datos de origen como formato Texto. Pero, para mantener su columna pivotante en formato de fecha, todos sus datos deben estar en formato de fecha. Por eso no puede cambiar el formato si tiene filas en blanco dentro de sus datos.
Los puntos clave de esta sección:
- La comprensión de las limitaciones y restricciones causadas por los divisores en las tablas dinámicas
- La importancia de no dejar espacios en blanco en sus datos de origen para un formateo eficaz dentro de las tablas dinámicas
- Creación de un rango dinámico de datos de origen para sus tablas dinámicas
- Comprensión de cómo las tablas dinámicas procesan y almacenan los datos, lo que afecta a cómo puede darles formato
7. Consejo extra: Cómo encontrar discrepancias de datos con las tablas dinámicas de Excel
A continuación le mostraremos una operación muy útil pero sencilla con las Tablas Dinámicas.
Supongamos que tiene un conjunto de datos y quiere asegurarse de que ciertos códigos coinciden exactamente sin discrepancias.
Por ejemplo, si tiene un código 162 que corresponde a S102 en un lugar, quiere asegurarse de que no corresponde accidentalmente a S110 en otro. Esto es esencialmente asegurar una relación uno a uno.
Para ilustrarlo, suponga que tiene códigos de producto y valores correspondientes. Quiere asegurarse de que cada código de producto coincide con uno y sólo un valor.
Por ejemplo, el código 162 sólo debe coincidir con S102, y no debe encontrarse coincidencia con ningún otro valor del conjunto de datos. Esta verificación es crucial, sobre todo en los casos en los que está haciendo coincidir códigos de producto con algo más y necesita estar seguro de que cada código se utiliza de forma única.
Para comprobarlo en una tabla dinámica, primero debe disponer los códigos uno al lado del otro. Si configura la tabla dinámica en forma tabular y desactiva los totales generales y los subtotales, podrá ver fácilmente si hay una coincidencia uno a uno.
Si un código corresponde a varios valores (o viceversa), sería evidente.
Un estudio de caso:
Por ejemplo, si el código 162 está configurado para coincidir con S102, pero también lo encuentra coincidiendo con S106 en otro lugar, estaría claro que no existe una relación de uno a uno.
Puede comprobarlo observando la disposición de la tabla dinámica: si hay varias entradas para el mismo código, indica que hay varias coincidencias.
Sin embargo, para estar completamente seguro de una relación uno a uno, necesita comprobarlo desde ambas direcciones. Es decir, disponga los datos en un orden (por ejemplo, código 2 y luego código 3) y luego en el orden inverso (código 3 y luego código 2). Si en ambas disposiciones, cada código y cada valor aparecen una sola vez, puede estar seguro de que existe una relación uno a uno.
Este método, aunque un poco complejo de entender al principio, es una forma eficaz de garantizar la exactitud de las relaciones entre los datos de su conjunto de datos. Es particularmente útil en escenarios en los que mantener una estricta correspondencia uno a uno entre elementos es crucial.
8. Palabras finales
En este artículo, hemos intentado darle consejos rápidos y profesionales sobre las tablas dinámicas de Excel. Sin duda, las tablas dinámicas son una de las características más singulares de Microsoft Excel. Como herramientas de análisis de datos, las tablas dinámicas deberían ser su primera dirección en el camino para convertirse en un potente usuario de Excel.
Lecturas recomendadas:
¿Cómo hacer que sus archivos Excel tengan MUCHO mejor aspecto?
Puestos relacionados
