Las tablas dinámicas son buenas para todos: calculan rápidamente y se configuran de manera flexible, y el diseño se puede enrollar en ellas con elegancia, si es necesario. Pero también hay algunas moscas en el ungüento, en particular, la incapacidad de crear un resumen, donde el área de valor no debe contener números, sino texto.
Tratemos de sortear esta limitación y propongamos un “par de muletas” en una situación similar.
Supongamos que nuestra empresa transporta sus productos en contenedores a varias ciudades de Nuestro País y Kazajstán. Los contenedores se envían no más de una vez al mes. Cada contenedor tiene un número alfanumérico. Como dato inicial, existe una tabla estándar de envíos, a partir de la cual es necesario hacer algún tipo de resumen para poder ver claramente la cantidad de contenedores enviados a cada ciudad y cada mes:
Para mayor comodidad, hagamos la tabla con los datos iniciales "inteligentes" de antemano usando el comando Inicio – Formato como tabla (Inicio — Formatear como tabla) y dale un nombre Las entregas de la pestaña. Constructor (Diseño). En el futuro, esto simplificará la vida, porque. será posible utilizar el nombre de la tabla y sus columnas directamente en las fórmulas.
Método 1. El más fácil: usa Power Query
Power Query es una herramienta súper poderosa para cargar y transformar datos en Excel. Este complemento está integrado en Excel de forma predeterminada desde 2016. Si tiene Excel 2010 o 2013, puede descargarlo e instalarlo por separado (totalmente gratis).
Todo el proceso, para mayor claridad, lo analicé paso a paso en el siguiente video:
Si no es posible utilizar Power Query, puede optar por otras formas: a través de una tabla dinámica o fórmulas.
Método 2. Resumen auxiliar
Agreguemos una columna más a nuestra tabla original, donde usando una fórmula simple calculamos el número de cada fila en la tabla:
Obviamente, se necesita -1, porque tenemos un encabezado de una línea en nuestra tabla. Si su tabla no está al principio de la hoja, entonces puede usar una fórmula un poco más compleja pero universal que calcula la diferencia en los números de la fila actual y el encabezado de la tabla:
Ahora, de manera estándar, construiremos una tabla dinámica del tipo deseado en función de nuestros datos, pero en el campo de valor soltaremos el campo Número de línea en lugar de lo que queremos envase:
Dado que no tenemos varios contenedores en la misma ciudad en el mismo mes, nuestro resumen, de hecho, no dará la cantidad, sino los números de línea de los contenedores que necesitamos.
Además, puede desactivar los totales generales y los subtotales en la pestaña Constructor – Totales generales и Subtotales (Diseño: totales generales, subtotales) y en el mismo lugar cambie el resumen a un diseño de tabla más conveniente con el botón maqueta de informe (Diseño de informe).
Por lo tanto, ya estamos a la mitad del resultado: tenemos una tabla donde, en la intersección de la ciudad y el mes, hay un número de fila en la tabla de origen, donde se encuentra el código del contenedor que necesitamos.
Ahora copiemos el resumen (en la misma hoja o en otra) y péguelo como valores, y luego ingrese nuestra fórmula en el área de valor, que extraerá el código del contenedor por el número de línea que se encuentra en el resumen:
Función IF (SI), en este caso, comprueba que la siguiente celda del resumen no esté vacía. Si está vacío, genera una cadena de texto vacía "", es decir, deja la celda en blanco. Si no está vacío, extraiga de la columna Envase tabla fuente Las entregas contenido de celda por número de fila usando la función ÍNDICE (ÍNDICE).
Quizás el único punto no muy obvio aquí es la palabra doble Envase en la fórmula Qué extraña forma de escribir:
Suministros[[Envase]:[Envase]]
… solo es necesario para hacer referencia a la columna Envase era absoluta (como una referencia con signos de $ para tablas ordinarias "no inteligentes") y no se deslizaba a las columnas vecinas al copiar nuestra fórmula a la derecha.
En el futuro, al cambiar los datos en la tabla de origen Las entregas, debemos recordar actualizar nuestro resumen auxiliar con números de línea haciendo clic derecho sobre él y eligiendo el comando Actualizar y guardar (Actualizar).
Método 3. Fórmulas
Este método no requiere la creación de una tabla dinámica intermedia y la actualización manual, sino que utiliza el "arma pesada" de Excel: la función VERANO (SUMAR.SI.SI). En lugar de buscar números de fila en un resumen, puede calcularlos usando esta fórmula:
Con cierto volumen externo, de hecho, este es un caso de uso estándar para la función de suma selectiva VERANOA que suma los números de fila para la ciudad y el mes dados. Nuevamente, dado que no tenemos varios contenedores en la misma ciudad en el mismo mes, nuestra función, de hecho, no dará la cantidad, sino el número de línea en sí. Y luego la función ya familiar del método anterior ÍNDICE También puede extraer códigos de contenedores:
Por supuesto, en este caso, ya no necesita pensar en actualizar el resumen, pero en tablas grandes, la función SUMMESLI puede ser notablemente lento. Luego, deberá desactivar la actualización automática de fórmulas o utilizar el primer método: una tabla dinámica.
Si la apariencia del resumen no es muy adecuada para su informe, puede extraer números de fila de él a la tabla final no directamente, como hicimos nosotros, sino usando la función OBTENER DATOS DE LA TABLA PIVOTANTE (OBTENER.DATOS.PIVOTE). Cómo hacer esto se puede encontrar aquí.
- Cómo crear un informe utilizando una tabla dinámica
- Cómo configurar cálculos en tablas dinámicas
- Conteo selectivo con SUMAR.SI.CONJUNTO, CONTAR.SI, etc.