Contenido
Formulación del problema
Como datos de entrada tenemos un archivo de Excel, donde una de las hojas contiene varias tablas con datos de ventas de la siguiente forma:
Tenga en cuenta que:
- Tablas de diferentes tamaños y con diferentes conjuntos de productos y regiones en filas y columnas sin ninguna clasificación.
- Se pueden insertar líneas en blanco entre las tablas.
- El número de mesas puede ser cualquiera.
Dos suposiciones importantes. Se asume que:
- Encima de cada tabla, en la primera columna, está el nombre del gerente cuyas ventas ilustra la tabla (Ivanov, Petrov, Sidorov, etc.)
- Los nombres de productos y regiones en todas las tablas se escriben de la misma manera, con precisión de mayúsculas y minúsculas.
El objetivo final es recopilar datos de todas las tablas en una tabla plana normalizada, conveniente para el análisis posterior y la construcción de un resumen, es decir, en este:
Paso 1. Conéctese al archivo
Vamos a crear un nuevo archivo de Excel vacío y seleccionarlo en la pestaña Datos Comando Obtener datos – Desde archivo – Desde libro (Datos — Del archivo — Del libro de trabajo). Especifique la ubicación del archivo de origen con datos de ventas y luego, en la ventana del navegador, seleccione la hoja que necesitamos y haga clic en el botón Convertir datos (Transformar datos):
Como resultado, todos los datos deben cargarse en el editor de Power Query:
Paso 2. Limpiar la basura
Eliminar pasos generados automáticamente tipo modificado (Tipo cambiado) и Encabezados elevados (Encabezados promocionados) y deshágase de las líneas vacías y las líneas con totales usando un filtro nulo и TOTAL por la primera columna. Como resultado, obtenemos la siguiente imagen:
Paso 3. Agregar administradores
Para comprender más adelante dónde están las ventas de quién, es necesario agregar una columna a nuestra tabla, donde en cada fila habrá un apellido correspondiente. Para esto:
1. Agreguemos una columna auxiliar con números de línea usando el comando Agregar columna – Columna de índice – Desde 0 (Agregar columna — Columna de índice — Desde 0).
2. Agregue una columna con una fórmula con el comando Agregar una columna: columna personalizada (Añadir columna — Columna personalizada) e introducir allí la siguiente construcción:
La lógica de esta fórmula es simple: si el valor de la siguiente celda en la primera columna es "Producto", esto significa que nos hemos topado con el comienzo de una nueva tabla, por lo que mostramos el valor de la celda anterior con el nombre del gerente. De lo contrario, no mostraremos nada, es decir, nulo.
Para obtener la celda principal con el apellido, primero nos referimos a la tabla del paso anterior #”Índice agregado”, y luego especifique el nombre de la columna que necesitamos [Columna1] entre corchetes y el número de celda en esa columna entre corchetes. El número de celda será uno menos que el actual, que tomamos de la columna Home, respectivamente.
3. Queda por llenar las celdas vacías con nulo nombres de celdas superiores con el comando Transformar – Rellenar – Abajo (Transformar — Rellenar — Abajo) y elimine la columna que ya no se necesita con índices y filas con apellidos en la primera columna. Como resultado, obtenemos:
Paso 4. Agrupación en tablas separadas por gerentes
El siguiente paso es agrupar las filas de cada administrador en tablas separadas. Para hacer esto, en la pestaña Transformación, use el comando Agrupar por (Transformar – Agrupar por) y en la ventana que se abre, seleccione la columna Administrador y la operación Todas las filas (Todas las filas) para simplemente recopilar datos sin aplicar ninguna función de agregación a ellos (suma, promedio, etc.). PAGS.):
Como resultado, obtenemos tablas separadas para cada administrador:
Paso 5: transformar tablas anidadas
Ahora damos las tablas que se encuentran en cada celda de la columna resultante Toda la información en forma decente.
Primero, elimine una columna que ya no sea necesaria en cada tabla Manager . usamos de nuevo columna personalizada de la pestaña. (Transformar — Columna personalizada) y la siguiente fórmula:
Luego, con otra columna calculada, elevamos la primera fila de cada tabla a los encabezados:
Y finalmente, realizamos la transformación principal: desplegamos cada tabla usando la función M Tabla.UnpivotOtrasColumnas:
Los nombres de las regiones del encabezado irán a una nueva columna y obtendremos una tabla normalizada más estrecha, pero al mismo tiempo, más larga. Celdas vacías con nulo se ignoran
Deshacernos de columnas intermedias innecesarias, tenemos:
Paso 6 Expandir tablas anidadas
Queda por expandir todas las tablas anidadas normalizadas en una sola lista usando el botón con flechas dobles en el encabezado de la columna:
… y finalmente conseguimos lo que queríamos:
Puede exportar la tabla resultante a Excel usando el comando Inicio — Cerrar y cargar — Cerrar y cargar en… (Inicio — Cerrar y cargar — Cerrar y cargar a…).
- Cree tablas con diferentes encabezados de varios libros
- Recopilación de datos de todos los archivos en una carpeta dada
- Recopilación de datos de todas las hojas del libro en una tabla