Contidos
Formulación do problema
Vexamos unha fermosa solución para unha das situacións moi estándar ás que se enfrontan a maioría dos usuarios de Excel tarde ou cedo: cómpre recoller de forma rápida e automática datos dun gran número de ficheiros nunha táboa final.
Supoñamos que temos o seguinte cartafol, que contén varios ficheiros con datos de cidades sucursais:
O número de ficheiros non importa e pode cambiar no futuro. Cada ficheiro ten unha folla nomeada De vendasonde está a táboa de datos:
O número de filas (ordes) nas táboas, por suposto, é diferente, pero o conxunto de columnas é estándar en todas partes.
Tarefa: recoller datos de todos os ficheiros nun só libro coa actualización automática posterior ao engadir ou eliminar ficheiros da cidade ou filas nas táboas. Segundo a táboa consolidada final, entón será posible construír calquera informe, táboa dinámica, datos de clasificación por filtros, etc. O principal é poder recoller.
Seleccionamos armas
Para a solución, necesitamos a última versión de Excel 2016 (a funcionalidade necesaria xa está integrada por defecto) ou versións anteriores de Excel 2010-2013 co complemento gratuíto instalado. Consulta de enerxía de Microsoft (descárgueo aquí). Power Query é unha ferramenta súper flexible e súper poderosa para cargar datos en Excel desde o mundo exterior, despois eliminalos e procesalos. Power Query admite case todas as fontes de datos existentes, desde ficheiros de texto ata SQL e incluso Facebook 🙂
Se non tes Excel 2013 ou 2016, non podes ler máis (só bromeando). Nas versións anteriores de Excel, tal tarefa só se pode realizar programando unha macro en Visual Basic (moi difícil para os principiantes) ou mediante a copia manual monótona (que leva moito tempo e xera erros).
Paso 1. Importa un ficheiro como mostra
En primeiro lugar, imos importar datos dun libro como exemplo, para que Excel "capte a idea". Para iso, crea un novo libro de traballo en branco e...
- se tes Excel 2016, abre a pestana Data e despois Crear consulta - Desde o ficheiro - Desde o libro (Datos - Nova consulta - Do ficheiro - Desde Excel)
- se tes Excel 2010-2013 co complemento Power Query instalado, abra a pestana Consulta de enerxía e selecciona nel Do ficheiro: do libro (Do ficheiro — Desde Excel)
Despois, na xanela que se abre, vai ao noso cartafol con informes e selecciona calquera dos ficheiros da cidade (non importa cal, porque son todos típicos). Despois dun par de segundos, debería aparecer a xanela do Navegador, onde cómpre seleccionar a folla que necesitamos (Vendas) no lado esquerdo, e o seu contido aparecerá no lado dereito:
Se fai clic no botón da esquina inferior dereita desta xanela descargar (Carga), entón a táboa importarase inmediatamente á folla na súa forma orixinal. Para un só ficheiro, isto é bo, pero necesitamos cargar moitos destes ficheiros, polo que iremos un pouco diferente e prememos no botón Corrección (Editar). Despois diso, o editor de consultas de Power Query debería mostrarse nunha ventá separada cos nosos datos do libro:
Esta é unha ferramenta moi poderosa que che permite "acabar" a táboa ata a vista que necesitamos. Incluso unha descrición superficial de todas as súas funcións levaría unhas cen páxinas, pero, se é moi breve, usando esta xanela pode:
- filtrar datos innecesarios, liñas baleiras, liñas con erros
- ordenar os datos por unha ou máis columnas
- desfacerse da repetición
- divide o texto pegajoso por columnas (por delimitadores, número de caracteres, etc.)
- ordenar o texto (eliminar espazos adicionais, corrixir maiúsculas e minúsculas, etc.)
- converter os tipos de datos de todas as formas posibles (transforma números como texto en números normais e viceversa)
- transpoñer (xirar) táboas e expandir táboas cruzadas bidimensionais en planas
- engade columnas adicionais á táboa e use fórmulas e funcións nelas usando a linguaxe M integrada en Power Query.
- ...
Por exemplo, imos engadir unha columna co nome de texto do mes á nosa táboa, para que despois sexa máis doado construír informes de táboa dinámica. Para iso, fai clic co botón dereito do rato no título da columna datae seleccione o comando Columna duplicada (Columna duplicada), e despois fai clic co botón dereito na cabeceira da columna duplicada que aparece e selecciona Comandos Transformar - Mes - Nome do mes:
Débese formar unha nova columna cos nomes de texto do mes para cada fila. Facendo dobre clic no encabezado dunha columna, pode cambiar o seu nome Data de copia a un máis cómodo Mes, por exemplo.
Se nalgunhas columnas o programa non recoñeceu correctamente o tipo de datos, podes axudarlle facendo clic na icona de formato situada á esquerda de cada columna:
Podes excluír liñas con erros ou liñas baleiras, así como xestores ou clientes innecesarios, utilizando un filtro sinxelo:
Ademais, todas as transformacións realizadas están fixadas no panel dereito, onde sempre se poden facer retroceder (cruzar) ou cambiar os seus parámetros (engrenaxe):
Lixeiro e elegante, non?
Paso 2. Transformemos a nosa solicitude nunha función
Para repetir posteriormente todas as transformacións de datos realizadas para cada libro importado, necesitamos converter a nosa solicitude creada nunha función, que se aplicará, á súa vez, a todos os nosos ficheiros. Para iso é realmente moi sinxelo.
No Editor de consultas, vai á pestana Ver e fai clic no botón Editor avanzado (Ver — Editor avanzado). Debería abrirse unha xanela onde todas as nosas accións anteriores se escribirán en forma de código na linguaxe M. Teña en conta que o camiño ao ficheiro que importamos para o exemplo está codificado no código:
Agora imos facer un par de axustes:
O seu significado é sinxelo: a primeira liña (ruta do ficheiro) => converte o noso procedemento nunha función cun argumento ruta do ficheiro, e a continuación cambiamos o camiño fixo ao valor desta variable.
Todos. Prema en Rematar e debería ver isto:
Non teñas medo de que os datos desaparecesen; de feito, todo está ben, todo debería verse así 🙂 Creamos con éxito a nosa función personalizada, onde se lembra todo o algoritmo para importar e procesar datos sen estar vinculado a un ficheiro específico . Queda por darlle un nome máis comprensible (por exemplo getData) no panel da dereita no campo Nome e podes coller Inicio — Pecha e descarga (Inicio — Pechar e cargar). Teña en conta que o camiño ao ficheiro que importamos para o exemplo está codificado no código. Volverá á xanela principal de Microsoft Excel, pero debería aparecer á dereita un panel coa conexión creada coa nosa función:
Paso 3. Recollendo todos os ficheiros
Toda a parte máis difícil queda atrás, queda a parte agradable e fácil. Vaia á pestana Datos - Crear consulta - Desde o ficheiro - Desde o cartafol (Datos — Nova consulta — Do ficheiro — Do cartafol) ou, se tes Excel 2010-2013, de xeito similar á pestana Consulta de enerxía. Na xanela que aparece, especifique o cartafol onde se atopan todos os ficheiros da nosa cidade de orixe e prema OK. O seguinte paso debería abrir unha xanela onde se listarán todos os ficheiros de Excel que se atopan neste cartafol (e os seus subcartafoles) e os detalles de cada un deles:
prema Cambiar (Editar) e de novo entramos na xanela familiar do editor de consultas.
Agora necesitamos engadir outra columna á nosa táboa coa nosa función creada, que "extraerá" os datos de cada ficheiro. Para facelo, vai á pestana Engadir columna - Columna personalizada (Engadir columna — Engadir columna personalizada) e na xanela que aparece, introduce a nosa función getData, especificando como argumento a ruta completa a cada ficheiro:
Despois de premer OK a columna creada debería engadirse á nosa táboa da dereita.
Agora imos eliminar todas as columnas innecesarias (como en Excel, usando o botón dereito do rato - Eliminar), deixando só a columna engadida e a columna co nome do ficheiro, porque este nome (máis precisamente, a cidade) será útil para ter nos datos totais de cada fila.
E agora o "momento wow": fai clic na icona coas súas propias frechas na esquina superior dereita da columna engadida coa nosa función:
… desmarca Use o nome orixinal da columna como prefixo (Utiliza o nome da columna orixinal como prefixo)e prema OK. E a nosa función cargará e procesará os datos de cada ficheiro, seguindo o algoritmo rexistrado e recollendo todo nunha táboa común:
Para obter unha beleza completa, tamén podes eliminar as extensións .xlsx da primeira columna cos nomes de ficheiros, mediante a substitución estándar por "nada" (faga clic co botón dereito na cabeceira da columna - Suplente) e renomea esta columna a cidade. E tamén corrixe o formato de datos na columna coa data.
Todo! Prema en Inicio - Pechar e cargar (Inicio — Pechar e cargar). Todos os datos recollidos pola consulta para todas as cidades cargaranse na folla de Excel actual no formato de "táboa intelixente":
A conexión creada e a nosa función de montaxe non precisan gardarse por separado de ningún xeito: gárdanse xunto co ficheiro actual do xeito habitual.
No futuro, con calquera cambio no cartafol (engadindo ou eliminando cidades) ou nos ficheiros (cambiando o número de liñas), bastará con facer clic co botón dereito directamente na táboa ou na consulta do panel dereito e seleccionar o mando Actualizar e gardar (Actualizar) - Power Query "reconstruirá" todos os datos de novo nuns segundos.
PS
Emenda. Despois das actualizacións de xaneiro de 2017, Power Query aprendeu a recompilar libros de Excel por si só, é dicir, xa non é necesario crear unha función separada: ocorre automaticamente. Así, o segundo paso deste artigo xa non é necesario e todo o proceso faise notablemente máis sinxelo:
- Escoller Crear solicitude - Desde o ficheiro - Desde o cartafol - Seleccionar o cartafol - Aceptar
- Despois de que apareza a lista de ficheiros, prema Cambiar
- Na xanela do Editor de consultas, expanda a columna Binario cunha frecha dobre e seleccione o nome da folla que se vai tomar de cada ficheiro
E iso é todo! Canción!
- Redeseño da táboa cruzada nunha plana adecuada para construír táboas dinámicas
- Creación dun gráfico de burbullas animado en Power View
- Macro para montar follas de diferentes ficheiros de Excel nun só