Microsoft

Resuma e informe fácilmente con tablas dinámicas de Excel

excel table hero

La funcionalidad de tabla dinámica de Excel le permite organizar y resumir datos en formatos de informes significativos sin cambiar el conjunto de datos. Además de generar informes, las tablas dinámicas brindan una forma rápida y fácil de analizar información que, de lo contrario, podría requerir un conocimiento especializado de las funciones y otras características. En este artículo, le mostraré los conceptos básicos para crear tablas dinámicas. Una vez que se familiarice con la funcionalidad de la tabla dinámica, puede que le resulte una gran solución para algo más que la funcionalidad de informes.

Puedes descargar ejemplos .xlsx o .xls Documentación para su comodidad. Estoy usando Excel 2013 en un sistema Windows 7, proporcionaré instrucciones específicas cuando otras versiones sean notablemente diferentes.

comienzo fácil

Una tabla dinámica es tan buena como sus datos. Para reducir los problemas, su conjunto de datos no debe contener filas o columnas en blanco, subtotales y debe estar en formato tabular. Cada columna debe tener un encabezado único (nombre). Una vez que esté satisfecho con el buen estado de sus datos de origen, considere diseñar algunos informes simples con lápiz y papel. Si lo hace, lo guiará mientras trabaja en un marco de tabla dinámica y enfrenta muchas opciones.

La buena noticia es que si puede hacer clic, puede crear una tabla dinámica. Es posible que deba modificar los resultados, pero comenzar es fácil:

  1. Seleccione cualquier celda del conjunto de datos, haga clic en la pestaña Insertar y luego haga clic en Tabla dinámica en el grupo Tablas. Si aún usa Excel 2003, elija «Tablas dinámicas y gráficos dinámicos» en el menú «Datos» para iniciar un asistente que lo guiará a través del proceso.
  2. En el cuadro de diálogo que aparece, verifique el rango y especifique si desea colocar la tabla dinámica en una hoja de trabajo existente o en una nueva hoja de trabajo (Figura A).
    Figura A
    02150611
  3. Haga clic en Aceptar.
  4. Cuando Excel muestra un marco de tabla dinámica vacío (Figura B), comience a arrastrar columnas desde la lista de Campos a las siguientes secciones: Filtros, Columnas, Filas y Valores. (El nombre era ligeramente diferente en versiones anteriores).
    Figura B
    02150612
LEER  Una forma rápida de eliminar o eliminar filas en blanco en Excel

Los pasos del 1 al 3 son simples y este artículo no los repetirá a medida que avanzamos en el ejemplo. El paso 4 requiere una comprensión específica de sus datos y necesidades de informes, y aquí es donde dedicaremos nuestro tiempo.

De forma predeterminada, el marco de la tabla dinámica está vacío. Sin embargo, la mayoría de las veces puede confiar en las siguientes pautas:

  • Agregue valores descriptivos (no numéricos) al área de la fila
  • Agregue el campo numérico a analizar al área de valor
  • Agregar campos de fecha y hora al área de la columna

contando

Resumir datos es el objetivo principal de las tablas dinámicas, que pueden contar valores y texto. Figura C Se muestra una configuración simple para calcular las cantidades de pedido por región; simplemente arrastre el campo de la región a la sección Filas y valores en el panel Campos de la tabla dinámica a la derecha. Debido a que el campo Región almacena texto, la sección Valores tiene como valor predeterminado un recuento simple. En Excel 2003, debe utilizar el control Agregar a para agregar campos al marco.

Figura C

02150613

Cuente el número de pedidos en cada región.

Al arrastrar el campo Fecha de transacción a la sección Columnas, puede proporcionar más detalles al devolver el recuento mensual. Después de arrastrar la Fecha de transacción a la sección Columnas, haga lo siguiente:

  1. Haga clic con el botón derecho en cualquier encabezado de fecha y seleccione Grupo (Figura D). En Excel 2003, elija Grupos y Mostrar detalles y luego elija Grupos.
    Figura D
    02150614
  2. En el cuadro de diálogo resultante, puede ver la configuración predeterminada: primera y última fecha en el conjunto de datos y opciones de mes (Figura E).
    Figura E
    02150615
  3. Haga clic en Aceptar sin realizar ningún cambio (Figura F).
    Figura F
    02150616

suma

Obtener totales es tan fácil como contar, pero con solo unos pocos clics, también puede mostrar estos subtotales como porcentajes. Figura G Se muestra una tabla dinámica que suma el campo Importe por región.

Figura G

20150617

Las ventas se agrupan por región.

En este punto, puede formatear el campo Importe como moneda, pero mostremos los valores como porcentajes como este:

  1. Haga clic con el botón derecho en cualquier celda de la columna Suma del importe.
  2. Seleccione Configuración de campo de valor en el submenú Resultados. Excel tiene por defecto la opción Suma.
  3. Haga clic para mostrar los valores como pestañas. En Excel 2003, haga clic en Opciones.
  4. En la lista desplegable Mostrar valor como, seleccione la opción % del total. En 2007, seleccione un porcentaje del total.
  5. Haga clic en Aceptar para ver el resultado como se muestra en la figura Figura H.
    Figura H
    20150619

Al arrastrar el campo Personal a la sección Columnas, puede mostrar el porcentaje de cada vendedor en cada área, como se muestra a continuación Figura 1Luke tenía el porcentaje individual más alto con un 8,83 %, pero Martha tenía el porcentaje general más alto con un 34,39 %. Estos son detalles que también puede obtener de los subtotales, pero los porcentajes a menudo brindan una perspectiva diferente, y esta función los muestra rápidamente.

Figura 1

20150620

Porcentaje por región para cada vendedor.

agrupación personalizada

Anteriormente, agrupamos los registros por mes según el valor de la fecha en la fecha de la transacción. Los grupos no siempre serán tan genéricos. Es posible que desee contar o sumar valores en rangos no representados en datos naturales. Por ejemplo, calculemos el monto de la comisión sin varios rangos: 0 a 99, 100 a 199, 200 a 299, y así sucesivamente:

  1. Arrastre Comisión a la sección Columnas y Personal a la sección Filas.
  2. Arrastre la Comisión a la sección Valores, el valor predeterminado es la función de suma.
  3. Haga clic con el botón derecho en cualquier valor de la fila Etiquetas de fila y seleccione Grupo.
  4. En el cuadro de diálogo que aparece, especifique los valores Comenzar en y Finalizar en. Excel ayuda al establecer de manera predeterminada los valores de datos más apropiados, pero puede cambiarlos para que se ajusten a rangos más grandes o más pequeños. En este caso, ingrese 0,400.
  5. Introduzca el valor de incremento en el control Por. En este caso, queremos especificar 100 grupos de incrementos (Figura J).
    Figura J
    20150621
  6. Haga clic en Aceptar para ver el resultado como se muestra en la figura Figura K.
    Figura K
    20150622

¡Tan poco trabajo vale tanto!

notable

Al modificar un conjunto de datos, la tabla dinámica debe actualizarse para reflejar estos cambios; las tablas dinámicas no son dinámicas. Si usa Excel 2007 o posterior, puede convertir el conjunto de datos en una tabla y crear su tabla dinámica en esa tabla. Cuando usa una tabla como fuente de datos para una tabla dinámica, Excel actualiza automáticamente la tabla dinámica a medida que actualiza los datos en la tabla; ¡su tabla dinámica siempre está sincronizada!

Crear una tabla es simple:

  1. Seleccione cualquier celda en el conjunto de datos.
  2. Haga clic en la pestaña Insertar.En el grupo Tablas, haga clic en la tabla (o presione [Ctrl]+[T]).
  3. Antes de hacer clic en Aceptar, especifique si el conjunto de datos tiene una fila de encabezado. De forma predeterminada, Excel asume que la primera fila de datos es la fila del encabezado.
  4. Haga clic en Aceptar para convertir el conjunto de datos en una tabla.

Si está trabajando con tablas, puede crear una tabla dinámica usando la pestaña Insertar o haciendo clic en Resumir con tabla dinámica en el grupo Herramientas en la pestaña Diseño contextual. El uso de Table como origen de datos evita la molestia de recordar (u olvidar) actualizar la tabla dinámica después de modificar los datos de origen.

En este artículo, no estamos utilizando una fuente de datos de tabla, pero es importante saber que puede hacerlo y por qué debería considerar hacerlo.

Soluciones de mesa pivote

Vale la pena explorar las tablas dinámicas. Solo hemos revisado algunas opciones básicas, pero es fácil ver lo útiles que son. Hay mucho más en esta función. La próxima vez que se enfrente a un resumen o informe, pruebe con una tabla dinámica.

Enviarme preguntas sobre Office

Intentaré responder a las preguntas de los lectores lo mejor que pueda, pero no hay garantías. Cuando se comunique conmigo, sea lo más específico posible. Por ejemplo, «Por favor, resuelva mi libro de trabajo y solucione el problema» podría no obtener una respuesta, pero «¿Puede decirme por qué esta fórmula no devuelve el resultado esperado?», sí. Mencione la aplicación y la versión que está utilizando. Tecnopedia no me reembolsará por mi tiempo o experiencia, ni les cobraré a los lectores por ello. Puede ponerse en contacto conmigo en [email protected].

LEER  Cómo y por qué ejecutar chkdsk para escanear y reparar discos duros de Windows 11

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Botón volver arriba