Contidos
Formulación do problema
Como datos de entrada, temos un ficheiro Excel, onde unha das follas contén varias táboas con datos de vendas da seguinte forma:
Teña en conta que:
- Táboas de diferentes tamaños e con diferentes conxuntos de produtos e rexións en filas e columnas sen ningunha clasificación.
- Pódense inserir liñas en branco entre táboas.
- O número de táboas pode ser calquera.
Dous supostos importantes. Suponse que:
- Enriba de cada táboa, na primeira columna, hai o nome do xestor cuxas vendas ilustra a táboa (Ivanov, Petrov, Sidorov, etc.)
- Os nomes de produtos e rexións en todas as táboas escríbense do mesmo xeito, cunha precisión entre maiúsculas e minúsculas.
O obxectivo final é recoller datos de todas as táboas nunha táboa normalizada plana, conveniente para a súa posterior análise e construír un resumo, é dicir, nesta:
Paso 1. Conéctate ao ficheiro
Imos crear un novo ficheiro de Excel baleiro e seleccionalo na pestana Data Mando Obter datos - Do ficheiro - Desde o libro (Datos — Do ficheiro — Do libro de traballo). Especifique a localización do ficheiro fonte cos datos de vendas e despois na xanela do navegador seleccione a folla que necesitamos e prema no botón Converter datos (Transformar datos):
Como resultado, todos os datos deste deberían cargarse no editor de Power Query:
Paso 2. Limpar o lixo
Elimina os pasos xerados automaticamente tipo modificado (Tipo modificado) и Cabeceiras elevadas (Cabeceiras promovidas) e desfacerse de liñas baleiras e liñas con totais mediante un filtro nulo и TOTAL pola primeira columna. Como resultado, obtemos a seguinte imaxe:
Paso 3. Engadir xestores
Para entender máis adiante onde están as vendas, é necesario engadir unha columna á nosa táboa, onde en cada fila haberá un apelido correspondente. Para isto:
1. Engademos unha columna auxiliar con números de liña usando o comando Engadir columna - Columna de índice - Desde 0 (Engadir columna — Columna de índice — De 0).
2. Engade unha columna cunha fórmula co comando Engadir unha columna - Columna personalizada (Engadir columna — Columna personalizada) e introduza alí a seguinte construción:
A lóxica desta fórmula é sinxela: se o valor da seguinte cela na primeira columna é "Produto", isto significa que nos topamos co inicio dunha nova táboa, polo que mostramos o valor da cela anterior co nome do xestor. En caso contrario, non mostramos nada, é dicir, nulo.
Para obter a cela nai co apelido, primeiro referímonos á táboa do paso anterior #"Índice engadido", e despois especifique o nome da columna que necesitamos [Columna 1] entre corchetes e o número de cela desa columna entre corchetes. O número de cela será un menos que o actual, que tomamos da columna Índice, Respectivamente.
3. Queda por encher as celas baleiras nulo nomes das celas superiores co comando Transformar - Encher - Abaixo (Transformar - Encher - Abaixo) e elimine a columna que xa non é necesaria con índices e filas con apelidos na primeira columna. Como resultado, obtemos:
Paso 4. Agrupación en táboas separadas polos xestores
O seguinte paso é agrupar as filas de cada xestor en táboas separadas. Para iso, na pestana Transformación, use o comando Agrupar por (Transformar – Agrupar por) e na xanela que se abre seleccione a columna Xestor e a operación Todas as filas (Todas as filas) para simplemente recoller datos sen aplicar ningunha función de agregación a eles (suma, media, etc.). P.):
Como resultado, obtemos táboas separadas para cada xestor:
Paso 5: transformar táboas anidadas
Agora damos as táboas que se atopan en cada cela da columna resultante Todos os datos en forma decente.
En primeiro lugar, elimine unha columna que xa non sexa necesaria en cada táboa director. Usamos de novo Columna personalizada aba Transformación (Transformar — Columna personalizada) ea seguinte fórmula:
Despois, con outra columna calculada, elevamos a primeira fila de cada táboa aos títulos:
E, finalmente, realizamos a transformación principal: desplegando cada táboa usando a función M Táboa.DesactivarOutrasColumnas:
Os nomes das rexións da cabeceira pasarán a unha nova columna e obteremos unha táboa normalizada máis estreita pero, ao mesmo tempo, máis longa. Celas baleiras con nulo ignóranse.
Desfacendonos das columnas intermedias innecesarias, temos:
Paso 6 Expande as táboas anidadas
Queda por expandir todas as táboas aniñadas normalizadas nunha única lista usando o botón con dobres frechas na cabeceira da columna:
... e por fin conseguimos o que queriamos:
Podes exportar a táboa resultante de novo a Excel usando o comando Inicio — Pechar e cargar — Pechar e cargar en… (Inicio — Pechar e cargar — Pechar e cargar en…).
- Constrúe táboas con diferentes cabeceiras a partir de varios libros
- Recopilación de datos de todos os ficheiros dun cartafol determinado
- Recopilación de datos de todas as follas do libro nunha soa táboa