contingut
Formulació del problema
Com a dades d'entrada, tenim un fitxer Excel, on un dels fulls conté diverses taules amb dades de vendes de la forma següent:
Tingues en compte que:
- Taules de diferents mides i amb diferents conjunts de productes i regions en files i columnes sense cap tipus d'ordenació.
- Es poden inserir línies en blanc entre taules.
- El nombre de taules pot ser qualsevol.
Dos supòsits importants. Se suposa que:
- A sobre de cada taula, a la primera columna, hi ha el nom del gerent les vendes del qual il·lustra la taula (Ivanov, Petrov, Sidorov, etc.)
- Els noms de béns i regions de totes les taules s'escriuen de la mateixa manera, amb precisió de majúscules i minúscules.
L'objectiu final és recollir dades de totes les taules en una taula normalitzada plana, convenient per a l'anàlisi posterior i construir un resum, és a dir, en aquesta:
Pas 1. Connecteu-vos al fitxer
Creem un nou fitxer Excel buit i seleccionem-lo a la pestanya dades Comando Obteniu dades - Del fitxer - Des del llibre (Dades — Del fitxer — Del llibre de treball). Especifiqueu la ubicació del fitxer font amb les dades de vendes i després a la finestra del navegador seleccioneu el full que necessitem i feu clic al botó Converteix dades (Transformar dades):
Com a resultat, totes les dades s'han de carregar a l'editor de Power Query:
Pas 2. Netegeu les escombraries
Suprimeix els passos generats automàticament tipus modificat (Tipus canviat) и Capçaleres elevades (Capçaleres promocionades) i desfer-se de les línies buides i les línies amb totals mitjançant un filtre nul и Total per la primera columna. Com a resultat, obtenim la següent imatge:
Pas 3. Afegir gestors
Per entendre més endavant on són les vendes, cal afegir una columna a la nostra taula, on a cada fila hi haurà el cognom corresponent. Per això:
1. Afegim una columna auxiliar amb números de línia mitjançant l'ordre Afegeix una columna - Columna d'índex - Des de 0 (Afegeix columna — Columna índex — Des de 0).
2. Afegiu una columna amb una fórmula amb l'ordre Afegir una columna: columna personalitzada (Afegeix columna: columna personalitzada) i introduïu-hi la següent construcció:
La lògica d'aquesta fórmula és senzilla: si el valor de la cel·la següent de la primera columna és "Producte", això vol dir que ens hem trobat amb l'inici d'una taula nova, de manera que mostrem el valor de la cel·la anterior amb el nom del gerent. En cas contrari, no mostrem res, és a dir, nul.
Per obtenir la cel·la pare amb el cognom, primer ens referim a la taula del pas anterior #"Índex afegit", i després especifiqueu el nom de la columna que necessitem [Columna 1] entre claudàtors i el número de cel·la d'aquesta columna entre claudàtors. El número de cel·la serà un menys que l'actual, que prenem de la columna Index, Respectivament.
3. Queda per omplir les cel·les buides amb nul noms de cel·les superiors amb l'ordre Transformar - Omplir - Avall (Transformar — Omplir — Avall) i suprimiu la columna que ja no és necessària amb índexs i files amb cognoms a la primera columna. Com a resultat, obtenim:
Pas 4. Agrupació en taules separades pels gestors
El següent pas és agrupar les files de cada gestor en taules separades. Per fer-ho, a la pestanya Transformació, utilitzeu l'ordre Agrupa per (Transform – Agrupa per) i a la finestra que s'obre, seleccioneu la columna Gestor i l'operació Totes les files (Totes les files) per recollir simplement dades sense aplicar cap funció d'agregació a ells (suma, mitjana, etc.). P.):
Com a resultat, obtenim taules separades per a cada gestor:
Pas 5: transforma les taules imbricades
Ara donem les taules que es troben a cada cel·la de la columna resultant Totes les dades en forma decent.
Primer, suprimiu una columna que ja no és necessària a cada taula Manager. Tornem a utilitzar Columna personalitzada llengüeta Transformació (Transformació: columna personalitzada) i la següent fórmula:
Aleshores, amb una altra columna calculada, aixequem la primera fila de cada taula als encapçalaments:
I, finalment, realitzem la transformació principal: desplegant cada taula mitjançant la funció M Taula.UnpivotOtherColumns:
Els noms de les regions de la capçalera aniran a una nova columna i obtindrem una taula normalitzada més estreta, però alhora més llarga. Cel·les buides amb nul són ignorats.
Per desfer-se de columnes intermèdies innecessàries, tenim:
Pas 6 Amplieu les taules niuades
Queda per expandir totes les taules imbricades normalitzades en una única llista mitjançant el botó amb fletxes dobles a la capçalera de la columna:
… i finalment aconseguim el que volíem:
Podeu tornar a exportar la taula resultant a Excel mitjançant l'ordre Inici — Tancar i carregar — Tancar i carregar a... (Inici — Tancar i carregar — Tancar i carregar a...).
- Creeu taules amb diferents capçaleres a partir de diversos llibres
- Recollida de dades de tots els fitxers d'una carpeta determinada
- Recollida de dades de tots els fulls del llibre en una taula