viernes, 17 de enero de 2020

Excel Fórmulas Desbordadas-Dynamics Arrays

Hoy es un día especialmente importante para Excel, y para todos nosotros...
Si eres suscriptor de Office 365 en el canal mensual, y tienes la última versión 1912 instalada, desde hoy tienes a tu disposición las esperadas fórmulas desbordadas/dynamics arrays !!.

Si tienes Office 365 con canales semianual o anual tendrás que esperar un poco más.
:'((


Si te preguntas qué es esto de las fórmulas desbordadas/dynamics arrays, debes saber que en mi opinión personal es el salto tecnológico más importante en la evolución de Excel desde hace muchos años...
Ten presente que estas funciones han estado en beta desde octubre del 2018 para los usuarios del programa Insiders de Excel, por lo que el rodaje está hecho y testado con suficiente contundencia.


A partir de ahora dispondrás de algunas nuevas funciones:
FILTRAR(array;include;[if empty])

MATRIZALEAT([filas];[columnas];[min];[max];[entero])

SECUENCIA(filas;[columnas];[inicio];[paso])

ORDENAR(matriz;[ordenar_indice];[criterio_ordenación];[por_columnas])

ORDENARPOR(matriz;por_matriz1;[orden1];...)

UNICOS(matriz;[por_columna];[exactly_once])


Otra novedad es que desde ahora te acostumbrarás a dos caracteres:
# (almohadilla-numeral) y @ (arroba)

La @ (arroba) de uso habitual en las formulaciones de Tablas aplicará ahora especialmente con la fórmulas desbordadas como operador de intersección implícita.
Esta intersección implícita siempre ha existido, pero quizá no eramos conscientes, ya que se realizaba 'silenciosamente'.
OJO!!, por que es posible que, desde ahora, veas que @ aparecen en algunas fórmulas cuando se abre en las fórmulas desbordadas-dinamycs arrays.
Es importante tener en cuenta que las fórmulas continuarán calculando de la misma manera que siempre!!.


El otro caracter importante es # (almohadilla-numeral) que permite trabajar sobre los valores desbordados por estas nuevas funciones.

No entraré en detalle ahora, por que voy a escribir una serie de artículos sobre estas funciones ;-)

Como verás ahora hablamos de desbordamientos o derrames (spill en inglés)... esta idea consiste en que una fórmula ha generado varios valores y esos valores se han colocado en las celdas vecinas!!, tan solo introduciendo la fórmula una sola vez en la celda superior. Y presionando únicamente Enter... ya no aplica la combinación matricial CSE (Ctrl+Mayusc+Enter).
Obviamente solo se puede editar la primera celda del área desbordada... el resto aparecerán en gris, siendo no editables.
Además al seleccionar cualquier celda del rango desbordado, Excel colocará un borde resaltado en torno al rango.

Una curiosidad a tener en cuenta es que las fórmulas desbordadas-dynamics arrays no se admiten en las tablas de Excel, por lo que deberemos colocarlas en la cuadrícula fuera de la tabla.

Otro aspecto a considerar es la superposición de fórmulas desbordadas, y es que no se pueden escribir si hay algo que bloquea el rango de celdas de salida esperado. Si esto ocurriera, Excel devolverá un error #SPILL! que indica que hay bloqueo.

Las fórmulas matriciales heredada escritas mediante Ctrl+Mayusc+Enter (CSE) aún se admiten por razones de compatibilidad con versiones anteriores, pero ya no se pueden usar!!.

Y otro punto a considerar es cuando trabajamos entre distintos libros. Excel ofrece soporte limitado para las fórmulas desbordadas entre libros. Solo se ofrece soporte para este escenario cuando ambos libros están abiertos!!.
Si cerramos el libro de origen, las fórmulas desbordadas vinculadas devolverán un error #REF! al actualizarse.

martes, 27 de agosto de 2019

Power Query: Agregar Columna a partir de un ejemplo

Tiempo atrás, en un par de artículos del blog, expuse algunas maneras de recuperar información de una cadena de texto, la cual no seguía un patrón claro a la hora de la 'extracción' de caracteres.
Ver post1 y post2

Te recomiendo les eches un vistazo... ;-)


En el día de hoy emplearemos una herramienta muy potente para conseguir similares resultados, sin duda muy potente y versátil: Agregar columna a partir de los ejemplos

Esta herramienta trabaja de forma casi idéntica al Relleno rápido en la hoja de cálculo.

Iniciaremos a partir de un listado de alfanuméricos, de los cuales necesitamos separar la primera parte de dichas cadenas que sean caracteres de texto:

Power Query: Agregar Columna a partir de un ejemplo



Como siempre, en un primer paso Cargaremos la Tabla de datos en el Editor de consultas de Power Query.
Una vez cargados los datos, y dentro del Editor de consultas, accederemos a la Ficha Agregar columna > grupo General > botón Columna a partir de los ejemplo

Power Query: Agregar Columna a partir de un ejemplo



Seleccionaremos la columna a tratar y elegiremos la opción del botón desplegable: A partir de la selección.
esto abrirá el Asistente, con una columna 'personalizada' donde iremos completando cuantos elementos sean necesarios hasta visualizar los elementos como necesitáramos.

Por ejemplo, para el primer elemento (A1234PQ) escribimos una A, y el asistente propone como regla o patrón: caracteres por la izquierda hasta el '1'.

Power Query: Agregar Columna a partir de un ejemplo


Como el resultado no es el esperado completaremos el segundo elemento (MX51234R) con MX, proponiendo como regla para mis datos: eliminar caracteres del 1 al 5 y de la P a la R

Power Query: Agregar Columna a partir de un ejemplo


Seguimos lejos del resultado buscado.
Así que informamos para el tercer elemento(MS612345AB) con un MS, lo que marca una regla un poco compleja a base de separadores... que en cualquier caso no responde a nuestro objetivo.

Power Query: Agregar Columna a partir de un ejemplo


Seguimos con el proceso...
ciertamente podrías pensar que para hacer elemento a elemento no necesitamos Power Query, pero piensa en un listado de 50.000 registros con elementos sin un patrón claro.


Repetimos el proceso una vez más para el quinto elemento (OPA7123456VW) donde indicamos como respuesta OPA... y las propuestas restantes sí parecen responder a lo que queremos.

Power Query: Agregar Columna a partir de un ejemplo



Aceptamos la fórmula propuesta. Podemos verla desde el Editor avanzado:
let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"datos", type text}}),
    #"Added Custom Column" = Table.AddColumn(#"Tipo cambiado", "Personalizado", 
            each let splitdatos = Splitter.SplitTextByCharacterTransition((c) => 
            not List.Contains({"0".."9"}, c), {"0".."9"})([datos]) in splitdatos{0}?, type text)
in
    #"Added Custom Column"

Cargamos y cerramos la consulta y la llevamos a la hoja de cálculo, viendo el resultado.

Además, al añadir nuevos elementos comprobamos cómo el funcionamiento de ésta consulta es correcto.

Power Query: Agregar Columna a partir de un ejemplo



Al fin y al cabo el asistente, con nuestras pistas ha sido de encontrar la regla, patrón o pauta deseada para devolvernos los caracteres hasta el primer caracter numérico.

De forma similar podríamos obtener la parte final, o la parte numérica intermedia.

miércoles, 21 de agosto de 2019

Power Query: Combinar consultas con Coincidencias aproximadas

En una reciente actualización de Microsoft Excel se ha implementado dentro de Power Query una interesante funcionalidad a la hora de Combinar Consultas, donde se permiten combinar elementos entre consultas basándose en coincidencias parciales o aproximadas.

Veamos su uso.
Partimos de una tabla inicial ('TblUNO') con todos los datos correctos y como deberían aparecer en 'todos los sitios'... con dos campos: Nombre y Valor.
En una segunda tabla ('TblRESULTADO') donde nos aparecen los nombres (que deberían coincidir) escritos de maneras muy dispares...
sin tildes, abreviados, orden cambiado, etc...



En primer lugar cargaremos como conexión solo las dos tablas comentadas.
En el siguiente paso, desde el Editor de consultas de Power Query procederemos a Combinar ambas consultas.
Desde la ficha Inicio > botón desplegable Combinar > Combinar consultas > Combinar consultas para crear una nueva

Power Query: Combinar consultas con Coincidencias aproximadas



En la ventana que se abrirá elegiremos nuestras dos tablas a combinar y seleccionaremos los campos clave de 'nombres'

Power Query: Combinar consultas con Coincidencias aproximadas


Si no marcásemos opción alguna, y dejáramos tal cual la combinación, el resultado sería equivalente a un BUSCARV, i.e., solo localizaría las coincidencias exactas.

Power Query: Combinar consultas con Coincidencias aproximadas


Nada novedoso.

Lo interesante viene ahora, si optamos por marcar la opción Usar las coincidencias aproximadas para comparar la combinación, donde automáticamente ofrece una combinación con un Umbral de similitud al 0,8 (umbral entre 0 y 1).
Este umbral de similitud es editable, e implica que valores próximos a uno-1 permitirá coincidencias exactas, y valores cercanos a cero-0 hará que todos los elementos coincidan unos con otros.

Otras opciones que se nos ofrecen son:
Ignorar mayúsculas y minúsculas
Hacer coincidir mediante la combinación de las partes del texto


Power Query: Combinar consultas con Coincidencias aproximadas


Dejaré para un futuro post las otras opciones..

Si realizamos algunas pruebas cambiando el Umbral de similitud comprobaremos de primera mano el comportamiento de esas coincidencias.

Power Query: Combinar consultas con Coincidencias aproximadas


Se observa que para elementos similares 'aparentemente' como 'Isabel Romero' e 'Ismael Romero' las coincidencias aproximadas devuelven coincidencia!!, lo que nos generará un problema...
Igualmente problemático son algunos elementos abreviados: 'J. Perez' y 'Juan Pérez' donde para forzar la coincidencia debemos indicar un Umbral de 0.

Por supuesto, lo interesante de esta funcionalidad es que poco a poco nos permitirá ir depurando nuestras bases de datos, con el peligro latente de continuar nuestros cálculos con coincidencias erróneas
:'(