Contidos
Un dos vídeos máis vistos na miña canle de YouTube é un vídeo sobre Flash Fill en Microsoft Excel. A esencia desta ferramenta é que se necesitas transformar dalgún xeito os teus datos de orixe, só tes que comezar a escribir o resultado que queres obter na columna adxacente. Despois de varias celas escritas manualmente (xeralmente son suficientes 2-3), Excel "comprenderá" a lóxica das transformacións que necesitas e continuará automaticamente o que escribiu, completando todo o traballo monótono para ti:
A quintaesencia da eficiencia. O botón máxico "faino ben" que tanto nos gusta a todos, non?
De feito, hai un análogo desta ferramenta en Power Query: alí chámase Columna de exemplos (Columna de Exemplos). De feito, esta é unha pequena intelixencia artificial integrada en Power Query que pode aprender rapidamente dos teus datos e despois transformalos. Vexamos máis de cerca as súas capacidades en varios escenarios prácticos para comprender onde nos pode ser útil en tarefas reais.
Exemplo 1. Pegar/cortar texto
Digamos que temos unha táboa "intelixente" en Excel con datos sobre empregados:
Cargao en Power Query da forma estándar: co botón De Táboa/Range aba Data (Datos: da táboa/rango).
Supoñamos que necesitamos engadir unha columna con apelidos e iniciais para cada empregado (Ivanov SV para o primeiro empregado, etc.). Para resolver este problema, pode usar un dos dous métodos:
- fai clic co botón dereito no encabezado da columna cos datos de orixe e selecciona o comando Engadir columnas a partir de exemplos (Engadir columna de exemplos);
- seleccione unha ou máis columnas con datos e na pestana Engadindo unha columna escolle un equipo Columna de exemplos. Aquí, na lista despregable, pode especificar se deben ser analizadas todas ou só as columnas seleccionadas.
Entón todo é sinxelo: na columna que aparece á dereita, comezamos a introducir exemplos dos resultados desexados e a intelixencia artificial integrada en Power Query trata de comprender a nosa lóxica de transformación e seguir adiante por si mesma:
Por certo, pode introducir as opcións correctas en calquera cela desta columna, é dicir, non necesariamente de arriba abaixo e nunha fila. Ademais, pode engadir ou eliminar facilmente columnas da análise máis tarde usando as caixas de verificación da barra de título.
Preste atención á fórmula na parte superior da xanela: isto é o que crea Smart Power Query para obter os resultados que necesitamos. Esta, por certo, é a diferenza fundamental entre esta ferramenta e Recheo instantáneo en Excel. O recheo instantáneo funciona como unha "caixa negra": non nos mostran a lóxica das transformacións, senón que simplemente dan resultados xa feitos e dámolos por feitos. Aquí todo é transparente e sempre podes entender con absoluta claridade o que está a suceder exactamente cos datos.
Se ves que Power Query "captou a idea", podes premer o botón con seguridade OK ou atallo de teclado Ctrl+Entrar – crearase unha columna personalizada cunha fórmula inventada por Power Query. Por certo, posteriormente pódese editar facilmente como unha columna creada manualmente (co comando Engadir unha columna - Columna personalizada) facendo clic na icona da engrenaxe situada á dereita do nome do paso:
Exemplo 2: Caso coma nas oracións
Se fai clic co botón dereito no encabezado da columna con texto e selecciona o comando Transformación (Transformar), entón podes ver tres comandos responsables de cambiar o rexistro:
Conveniente e xenial, pero nesta lista, por exemplo, persoalmente sempre me faltou unha opción máis: caso como nas oracións, cando a maiúscula (maiúscula) non se converte na primeira letra de cada palabra, senón só na primeira letra da cela, e o resto do texto cando Isto móstrase en minúsculas (minúsculas).
Esta función que falta é fácil de implementar con intelixencia artificial Columnas de exemplos – basta con introducir un par de opcións para que Power Query continúe co mesmo espírito:
Como fórmula aquí, Power Query usa unha morea de funcións Texto.Superior и Texto.Abaixo, convertendo texto en maiúsculas e minúsculas, respectivamente, e funcións Texto.Inicio и Texto.Medio – análogos das funcións de Excel LEFT e PSTR, capaces de extraer unha subcadea do texto dende a esquerda e dende o medio.
Exemplo 3. Permutación de palabras
Ás veces, ao procesar os datos recibidos, faise necesario reorganizar as palabras nas celas nunha secuencia determinada. Por suposto, pode dividir a columna en columnas de palabras separadas polo separador e despois pegala de novo na orde especificada (non esqueza engadir espazos), pero coa axuda da ferramenta Columna de exemplos todo será moito máis doado:
Exemplo 4: Só números
Outra tarefa moi vital é sacar só números (números) do contido da cela. Como antes, despois de cargar os datos en Power Query, vai á pestana Engadir unha columna - Columna de exemplos e enche un par de celas manualmente para que o programa comprenda exactamente o que queremos obter:
Bingo!
De novo, paga a pena mirar na parte superior da xanela para asegurarse de que Query xerou a fórmula correctamente; neste caso, contén unha función Texto. Seleccione, que, como podes adiviñar, extrae os caracteres indicados do texto fonte segundo a lista. Posteriormente, esta lista, por suposto, pódese editar facilmente na barra de fórmulas se é necesario.
Exemplo 5: só texto
Do mesmo xeito que no exemplo anterior, pode extraer e viceversa: só o texto, eliminando todos os números, signos de puntuación, etc.
Neste caso, utilízase unha función que xa ten un significado oposto: Texto.Eliminar, que elimina os caracteres da cadea orixinal segundo unha lista determinada.
Exemplo 6: extracción de datos dun mingau alfanumérico
Power Query tamén pode axudar en casos máis difíciles, cando precisa extraer información útil do mingau alfanumérico nunha cela, por exemplo, obter o número de conta a partir da descrición do propósito do pago nun extracto bancario:
Teña en conta que a fórmula de conversión xerada por Power Query pode ser bastante complexa:
Para facilitar a lectura e a comprensión, pódese converter nunha forma moito máis sensata mediante un servizo en liña gratuíto. Formateador Power Query:
Cousa moi útil: respecto aos creadores!
Exemplo 7: conversión de datas
Ferramenta Columna de exemplos tamén se pode aplicar ás columnas de data ou hora. Cando introduza os primeiros díxitos dunha data, Power Query mostrará unha lista de todas as opcións de conversión posibles:
Así, pode converter facilmente a data orixinal a calquera formato exótico, como "ano-mes-día":
Exemplo 8: categorización
Se usamos a ferramenta Columna de exemplos a unha columna con datos numéricos, funciona de forma diferente. Supoñamos que temos os resultados das probas dos empregados cargados en Power Query (puntuacións condicionais no intervalo 0-100) e usamos a seguinte gradación condicional:
- Masters: aqueles que marcaron máis de 90
- Expertos: puntuación de 70 a 90
- Usuarios: de 30 a 70
- Principiantes: aqueles que obtiveron menos de 30 puntos
Se engadimos unha columna dos exemplos á lista e comezamos a organizar estas gradacións manualmente, moi pronto Power Query recollerá a nosa idea e engadirá unha columna cunha fórmula, onde os operadores se aniñan entre si. if implementarase unha lóxica moi parecida á que necesitamos:
De novo, non podes presionar a situación ata o final, pero fai clic OK e, a continuación, corrixa os valores límite que xa están na fórmula; é máis rápido deste xeito:
Conclusións
Certamente unha ferramenta Columna de exemplos non é unha "pílula máxica" e, tarde ou cedo, haberá situacións non estándar ou casos especialmente desatendidos dunha "granxa colectiva" nos datos, cando Power Query fallará e non poderá determinar o que queremos correctamente para nós. Non obstante, como ferramenta auxiliar, é moi boa. Ademais, estudando as fórmulas que xerou, podes ampliar o teu coñecemento das funcións da linguaxe M, o que sempre será útil no futuro.
- Analizando texto con expresións regulares (RegExp) en Power Query
- Busca de texto difuso en Power Query
- Flash Fill en Microsoft Excel