Power Query: Porcentajes sobre el total

Seguimos avanzando con Power Query.
Hoy algo siempre útil, el cálculo porcentual sobre el total general.

Como vemos el objetivo es incluir para cada país, cada fila, el peso o porcentaje de cada país sobre el total acumulado de éstos.
Como siempre cargaremos nuestra tabla al editor de Power Query, y una vez en la consulta creada añadiremos una columna personalizada, desde la ficha Agregar Columna > General > Columna personalizada,, donde le asignaremos el nombre '% Total' y la fórmula:
= [VENTAS] / List.Sum(#"Tipo cambiado"[VENTAS])

Importante la forma en que nos referimos, con la función M List.Sum, al campo completo de VENTAS.. indicando la lista completa de elementos de la tabla de trabajo (paso previo) y de una de sus columnas '[VENTAS]'.

Muy rápido y simple, sin necesidad de variables o cálculos externos... al modo Excel :D

Calcularemos ahora los porcentajes de cada país respecto al subtotal de la REGIÓN a la que pertenece.
Seguimos en nuestra consulta de Power Query y añadiremos una nueva columna personalizada, que llamaré '% Región' con la fórmula:
= [VENTAS]/ Table.SelectRows(Table.Group(#"Tipo cambiado1", {"REGION"},
{{"Region Total", each List.Sum([VENTAS]), type number}})
, each ([REGION] = [REGION])){[REGION=[REGION]]}[Region Total]

En este caso el truco consiste en agrupar 'virtualmente' por REGION, y así poder trabajar sobre ese parcial en cada caso:
Table.Group(#"Tipo cambiado1", {"REGION"},{{"Region Total", each List.Sum([VENTAS]), type number}})
Para finalmente con Table.SelectRows trabajamos en cada fila con el parcial que nos interesa, esto es, cuando coincidan las REGIONES, recuperando el dato de la columna agrupada [Region Total].
Table.SelectRows(... , each ([REGION] = [REGION])){[REGION=[REGION]]}[Region Total]
Obteniendo finalmente el cociente buscado...

El código completo quedaría:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"PAIS", type text}, {"VENTAS", Int64.Type}}),
    
    #"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado", "% Total", each [VENTAS] / List.Sum(#"Tipo cambiado"[VENTAS])),

    #"Tipo cambiado1" = Table.TransformColumnTypes(#"Personalizada agregada",{{"% Total", Percentage.Type}}),
    
    #"Personalizada agregada1" = Table.AddColumn(#"Tipo cambiado1", "% Region", 
                each [VENTAS]/ Table.SelectRows(
                                    Table.Group(#"Tipo cambiado1", {"REGION"}, 
                                    {{"Region Total", each List.Sum([VENTAS]), type number}})
                                , each ([REGION] = [REGION])){[REGION=[REGION]]}[Region Total])
in
    #"Personalizada agregada1"

Seguir leyendo...

Power Query: Optimizar la opción Desde Carpeta

Seguramente todos en algún momento de nuestro trabajo hemos necesitado implementar una consulta de Power Query que recupere toda la información contenida en una carpeta.
Desde Power Query es sencillo (puedes ver cómo aquí)

Lo que todos hemos comprobado, además de la facilidad, es que (como punto negativo) nos genera un montón de consultas intermedias, parámetros o funciones que luego muestra en el panel de consultas, y nos 'ensucia' la limpieza visual que necesitamos para otras consultas...

Hoy veremos cómo podemos personalizar y automatizar este proceso de importar datos desde una carpeta para dejarlo únicamente en una consulta y una función.

Como antecedentes comentar que mis ficheros a anexar se encuentrar en una ruta: F:\excelforo\pq_memoria
que además para que sea más personalizable he escrito en la celda I2, a la cual he asignado como nombre definido 'ndRUTA'. Asi que accedemos al editor de Power Query creando una Consulta en blanco.
Desde la ficha Datos > grupo Obtener y Transformar > Obtener datos > Desde otras fuentes > Consulta en blanco.
Entonces desde el Editor avanzado escribiremos y crearemos la siguiente función, que llamo: fxGetFolder:

let
    // tomamos la ruta de la celda...
    ruta=Excel.CurrentWorkbook(){[Name="ndRUTA"]}[Content]{0}[Column1],

    // el primer fichero encontrado será el de ejemplo.
    ArchivoEjemplo=Folder.Files(ruta){0}[Content],

    // damos como valor el Archivo de Ejemplo y lo definimos como un parámetro real
    param=ArchivoEjemplo meta [IsParameterQuery=true, BinaryIdentifier=ArchivoEjemplo, Type="Binary", IsParameterQueryRequired=true],

    Origen = 
        (param as binary) => let
            // para cargar siempre primera hoja.. sea cual sea su nombre!
            Origen1 = Excel.Workbook(param, null, true){[Item=Excel.Workbook(param, null, true){0}[Item],Kind="Sheet"]}[Data],
            // promovemos la primera fila de cada fichero como encabezado
            EncabPromovidos=Table.PromoteHeaders(Origen1, [PromoteAllScalars=true])
            in
            EncabPromovidos
in
    Origen

Por otra parte, generamos una nueva consulta en blanco, que llamaré 'fuente_CARPETA' donde añadiremos las siguientes instrucciones:

let
    path=Excel.CurrentWorkbook(){[Name="ndRUTA"]}[Content]{0}[Column1],
    ArchivoEjemplo=Folder.Files(path){0}[Content],
    Origen = Folder.Files(path),
    
// si tuvieramos dudas de si existen ficheros ocultos en la carpeta dejaríamos activa la siguiente línea.
//    ArchivosOcultosFiltrados = Table.SelectRows(Origen, each [Attributes]?[Hidden]? <> true),
    AddCol_IncorporamosFicheros = Table.AddColumn(Origen, "Transformar archivo",  each fxGetFolder([Content])),

    // expandimos las columnas de los ficheros
    ExpandeCols = Table.ExpandTableColumn(AddCol_IncorporamosFicheros, "Transformar archivo", Table.ColumnNames(fxGetFolder(ArchivoEjemplo))),

    // Eliminamos columnas de información de los ficheros, que no necesitamos
    ColumnasQuitadas = Table.RemoveColumns(ExpandeCols,{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"})
in
    ColumnasQuitadas

Al final, lo importante, ejecutemos la consulta generada por el asistente o por nuestro proceso 'personalizado', tendremos lo mismo:

Seguir leyendo...

Números Primos y Divisores con fórmulas desbordadas

Los que me han leído alguna vez conocen de mi afición por las matemáticas... mi hija, igualmente, me 'sufre' bastante a menudo con este tema :D
Bien, pues hace un par de días, mi hija, estudia cuarto de primaria, me pidió ayuda para entender el tema de los divisores y los números primos.. obviamente con gusto se lo expliqué ;-)
Pero me hizo recordar que hace algún tiempo publiqué un par de artículos sobre estos temas:
aquí
y aquí
aunque en esos artículos empleaba algo de programación...

Por suerte para todos han llegado a nosotros las fórmulas desbordadas (de momento en Microsoft 365) que hasta cierto punto son capaces de replicar bucles de programación, pero sin picar códigos incomprensibles ;-)
Y aquí estamos... en el post de hoy listaremos los números primos existentes entre los 1.000 primeros números naturales, así como los divisores para cada valor de esos 1.000 números.

Recordemos los conceptos de divisor y número primo.
Los divisores de un número natural son los números naturales que lo pueden dividir, resultando de cociente otro número natural y de resto 0.
Por otra parte, un número primo es un número natural mayor que 1 que tiene únicamente dos divisores positivos distintos: él mismo y la unidad.
Por el contrario, los números compuestos son los números naturales que tienen algún divisor natural aparte de sí mismos y de la unidad, y por lo tanto pueden factorizarse.

Vamos con los cálculos...
Primero listaremos los 1000 primeros valores... así en la celda B2 insertamos: =SECUENCIA(1000;;2;1)
es decir, un vector de números naturales desde el 2 hasta el 1001 incrementados de 1 en 1. Dejo fuera la unidad (1).
Recuerda que en este caso la función se desbordará automáticamente hacia abajo.

En un segundo paso listaré para cada valor anterior sus divisores.
Así en la celda D2 escribiremos:
=TRANSPONER(LET(lista;SECUENCIA($B2);
resto;RESIDUO($B2;lista);
FILTRAR(lista;resto=0)))
luego la arrastraremos hasta la última fila con valores...
He empleado la función LET para facilitar la comprensión del cálculo...
Primero generamos un listado 'lista', con la función SECUENCIA, que iría desde el 1 hasta el valor de la columna B.
Esto es, 1,2 ó 1,2,3 ó 1,2,3,4 etcétera.
A continuación obtenemos el resto del cociente entre el valor de la columna B y cada uno de los elementos de la lista previa:
RESIDUO($B2;lista)
Finalmente, sobre los listados anteriores, aplicando la función FILTRAR nos quedamos solo con aquellos números cuyo resto sea cero (o sea, la definición de divisor):
FILTRAR(lista;resto=0)
El listado de esos divisores, para cada natural de la columna B, lo recuperamos horizontalmente mediante la función TRANSPONER.

Por acabar el artículo de hoy, en la celda C2 añadiremos una fórmula que identifique si un número es primo o compuesto.
El algoritmo que aplico es simple... ya que un número primo solo tiene dos divisores (a sí mismo y a la unidad), un número es primo si la suma de todos sus divisores coincide con la suma de 1 más su valor.
Por tanto en C2 y luego arrastrando hasta la última celda:
=SI(SUMA(LET(lista;SECUENCIA($B2);
resto;RESIDUO($B2;lista);
FILTRAR(lista;resto=0)))=B2+1;"primo";"compuesto")

Sencillo y fácil ;-)

Seguir leyendo...

Power Query: Table.SelectRows replicando SUMAR.SI.CONJUNTO

Aprenderemos hoy una función M de Power Query MUY interesante, enfocada a aplicar filtros sobre filas de manera formulada. Hablamos de:
Table.SelectRows(table as table, condition as function) as table
esta función nos devolverá una tabla con aquellas filas que coincidan con nuestras condiciones de filtro...

Para mostrar un ejemplo algo más avanzado de esta función aplicaremos un ejemplo que replica nuestra función SUMAR.SI.CONJUNTO de la hoja de cálculo.

Nuestro punto de partida es:
1-la tabla 'TblDATOS',
2- un rango con un nombre definido asignado: 'ndRESUMEN' correspondiente al rango G2:H5
3- una celda con el año insertado, con nombre asignado 'ndAño' (celda I1).

Nuestro objetivo es, empleando Power Query (y la función Table.SelectRows en concreto), llegar a obtener el acumulado por PAÍS, PRODUCTO y AÑO detallado en G2:H5.

Así pues, cargaremos el rango 'ndRESUMEN' y la 'TblDATOS' al editor de consultas de Power Query.
Desde la ficha Datos > grupo Obtener y transformar > Desde Tabla o rango
Inicialmente cargaremos ambas tablas 'Solo como conexión'.

Ya en el editor abriremos el Editor avanzado del rango 'ndRESUMEN':

let
    Origen = Excel.CurrentWorkbook(){[Name="ndRESUMEN"]}[Content],
    pAño = Excel.CurrentWorkbook(){[Name="ndAño"]}[Content][Column1]{0},
    EncabezadosPromovidos = Table.PromoteHeaders(Origen, [PromoteAllScalars=true]),
    AddCol_SUMAR_SI_CONJUNTO = Table.AddColumn(EncabezadosPromovidos, "Acum", each List.Sum(
    Table.SelectRows(
        TblDATOS,
        (sumar_si) =>   sumar_si[País]=[Paises] and 
                        sumar_si[Producto]=[Pdto] and 
                        sumar_si[Año]=pAño)[Unidades])),
    OtrasColumnasQuitadas = Table.SelectColumns(AddCol_SUMAR_SI_CONJUNTO,{"Acum"})
in
    OtrasColumnasQuitadas

Donde cada paso implicaría lo siguiente...
El primer paso carga el rango 'ndRESUMEN'. Esto lo habría hecho el asistente al cargar el rango...
Origen = Excel.CurrentWorkbook(){[Name="ndRESUMEN"]}[Content],
En el segundo paso recuperamos el dato de la celda 'ndAño' que trataremos como un parámetro dentro de la consulta...
pAño = Excel.CurrentWorkbook(){[Name="ndAño"]}[Content][Column1]{0},
En el tercer paso promovemos la primera fila como encabezados (muy normal en los casos que cargamos rangos...)
EncabezadosPromovidos = Table.PromoteHeaders(Origen, [PromoteAllScalars=true]),
fijate que operamos sobre la primera línea 'Origen' !!
En el siguiente paso está la 'chicha' del ejercicio!!:
AddCol_SUMAR_SI_CONJUNTO = Table.AddColumn(EncabezadosPromovidos, "Acum", each List.Sum(
Table.SelectRows(
TblDATOS,
(sumar_si) => sumar_si[País]=[Paises] and
sumar_si[Producto]=[Pdto] and
sumar_si[Año]=pAño)[Unidades])),
Añadimos con este paso una nueva columna llamada 'Acum' resultante de Acumular/Sumar (List.Sum) los elementos de la lista resultantes de aplicar ciertas condiciones a la tabla 'TblDATOS'... condiciones, conseguidas en este caso, con la aplicación de una función personalizada:
(sumar_si) => sumar_si[País]=[Paises] and
sumar_si[Producto]=[Pdto] and
sumar_si[Año]=pAño)
Esta función aplica el triple criterio por País, Producto y Año.
Antes de aplicar List.Sum veríamos, tras insertar nuestra función Table.SelectRows:

donde observamos que para cada fila de 'ndRESUMEN' tenemos una Tabla solo con las filas de 'TblDATOS' que cumplen la triple condición...
Table.SelectRows(
TblDATOS,
(sumar_si) => sumar_si[País]=[Paises] and
sumar_si[Producto]=[Pdto] and
sumar_si[Año]=pAño)
Como de la 'TblDATOS' solo nos interesa el campo 'Unidades' para acumular esas cantidades, al código anterior le añadiremos dicho campo:
Table.SelectRows(
TblDATOS,
(sumar_si) => sumar_si[País]=[Paises] and
sumar_si[Producto]=[Pdto] and
sumar_si[Año]=pAño)[Unidades]
Lo que generará una Lista solo con los datos de la columna 'Unidades' y obviamente de los registros de la 'TblDATOS' que verificaran las tres condiciones...

Sobre esa lista de unidades aplicamos List.Sum para obtener el acumulado buscado!!... como veíamos en el código completo.

Termino en este caso mostrando solo la columna creada 'Acum' (con nuestro cálculo), y lo mostramos en la hoja de cálculo...
Tendremos que cambiar el tipo de carga de 'Solo conexión' a 'Tabla' en la hoja de cálculo.
Llegando al resultado buscado...

Especialmente interesante es el uso dentro de Table.SelectRows como argumento de condiciones de la función personalizada, que evita tener que expandir los campos de la TblDATOS para aplica posteriormente condiciones sobre la columnas (como hicimos en uno de los casos de este post); ganando en eficiencia y rapidez.

Seguir leyendo...

Power Query: Separar Nombres y Apellidos

Hoy daremos una solución alternativa, con Power Query, al clásico problema de separar los nombres completos en: Nombre + Apellido 1 + Apellido 2
Hace ya unos cuantos años publiqué un artículo done, con una UDF de VBA para Excel, se daba solución a casi todas las posibilidades de nombres y apellidos compuestos. Puedes releerlo aquí... un artículo muy interesante!!

Hoy empleando una técnica similar, pero con Power Query, llegaremos al mismo resultado.

Aprenderemos funciones M de Power Query muy potentes, como por ejemplo:
List.ReplaceMatchingItems(lista as list, reemplazos as list, optional equationCriteria as any) as list
donde se realizan los reemplazos especificados en la 'lista'.
La lista de 'reemplazos' consta de una lista de dos valores, el anterior y el nuevo.

Comencemos nuestro trabajo como siempre cargando en el editor de consultas la 'Tabla1' con los nombres completos... observa que aparecen todo tipo de nombres y apellidos (simples y compuestos).
Con la Tabla de nombres cargada, y ya en el editor de consultas creamos una Consulta en blanco (desde el menú Inicio > nueva Consulta > Nuevo Origen > Otros orígenes > Consulta en Blanco).
En el editor avanzado escribiremos el siguiente código (en algunos pasos podríamos emplear el asistente, mientras en otro se requiere conocimientos de lenguaje M específico):

let
    /*Cargando datos de una tabla en la hoja de cálculo*/
    //reemplazos = TablaReplacements,
    //ListaReemp = Table.AddColumn(reemplazos, "Lista", each ({[Reemplaza], [por]})),
    
    /*Cargando datos de la lista creada en M  */
    ListaReemp=({{"De|","de"},{"Del|","del"},{"El|","el"},{"La|","la"},{"Las|","las"},{"Los|","los"},{"San|","san"},{"Y|","y"}}),
    /*Cargamos la tabla con los nombres completos a trabajar*/
    NombresCompletos = Tabla1,
    /*Aplicamos formato de 'Nombre propio'  */
    MayusculasCadaPalabra = Table.TransformColumns(NombresCompletos,{{"Nombre completo", Text.Proper, type text}}),
    /*Separamos cada nombre en una lista, usando el espacio en blanco... 
    cada palabra le incorporamos un caracter |  */
    TurnTextToList = Table.AddColumn(MayusculasCadaPalabra, "Split1", each List.Transform(Text.Split([Nombre completo], " "), each _ & "|")),
    /*Reemplazamos las coincidencias de ListaReemp
    y unimos de nuevo los elementos separados en el paso anterior  */
    Replacements = Table.AddColumn(TurnTextToList, "Nombre_1", each Text.Combine(List.ReplaceMatchingItems([Split1],ListaReemp)," ")),
    /*Eliminamos el último caracter | */
    MenosUltimoCaracter = Table.AddColumn(Replacements, "Nombre_2", each Text.Start([Nombre_1],Text.Length([Nombre_1])-1)),
    /*Separamos nuevamente por el separador |  */
    Ultimo_Split = Table.AddColumn(MenosUltimoCaracter, "UltimoSplit", each Text.Split([Nombre_2],"|")),
    /*Contabilizamos el número de elementos en cada nombre  */
    Cuenta_Partes = Table.AddColumn(Ultimo_Split, "CuentaElementos", each List.Count([UltimoSplit])),
    /*Y añadimos tres columnas Nombre, Apellido1 y Apellido2 en base al recuento anterior.  */
    AddNombre = Table.AddColumn(Cuenta_Partes, "Nombre", each if [CuentaElementos]>3 then 
            [UltimoSplit]{0}? & [UltimoSplit]{1}? else 
            [UltimoSplit]{0}?),
    Add1erApellido = Table.AddColumn(AddNombre, "1er Apellido", each if [CuentaElementos]>3 then 
            [UltimoSplit]{2}? else [UltimoSplit]{1}?),
    Add2doApellido = Table.AddColumn(Add1erApellido, "2do Apellido", each if [CuentaElementos]>3 then 
            [UltimoSplit]{3}? else [UltimoSplit]{2}?),
    /*Eliminamos columnas que nos 'sobran'  */
    ColumnasQuitadas = Table.RemoveColumns(Add2doApellido,{"Nombre completo", "Split1", "Nombre_1", "Nombre_2", "UltimoSplit", "CuentaElementos"})
in
    ColumnasQuitadas

Un código un poco largo, pero muy potente ;-)
Cerraremos y cargaremos sobre nuestra hoja de cálculo y listo.
Si comparamos con nuestra UDF comprobaremos que el resultado es el mismo... Asombroso!!.

Pero, ¿qué ha ocurrido en cada paso de nuestra consulta???.
En la primera sentencia o paso hemos creado una Lista:
ListaReemp=({{"De|","de"},{"Del|","del"},{"El|","el"},{"La|","la"},{"Las|","las"},{"Los|","los"},{"San|","san"},{"Y|","y"}}),
que muestra en distintas filas el valor a reemplazar seguido del valor nuevo. Lista necesaria para emplear la función M comentada List.ReplaceMatchingItems como segundo argumento...
El por qué de la barra vertical lo entenderás en los pasos siguientes...

El segundo paso es muy simple, sirve para cargar la 'Tabla1' previamente subida al editor de PQ.
NombresCompletos = Tabla1,

Siguiente paso. Transformamos la Tabla1 cargada, aplicandole la función Text.Proper
MayusculasCadaPalabra = Table.TransformColumns(NombresCompletos,{{"Nombre completo", Text.Proper, type text}}),
convirtiendo la inicial de cada palabra en mayúscula y el resto en minúsculas... se trata de homogeneizar el texto de la Tabla1 de nombres completos, para poder hacer coincidir con nuestra lista anterior de valores a reemplazar!.

En el paso siguiente añadimos una nueva columna 'Split1', donde cada registro se ha descompuesto como una lista de elementos; elementos que serán cada una de las palabras del nombre completo, pero seguido o concatenado de una barra vertical |
TurnTextToList = Table.AddColumn(MayusculasCadaPalabra, "Split1", each List.Transform(Text.Split([Nombre completo], " "), each _ & "|")),

Nuevamente un paso fundamental, ya que sobre esta lista aplicaremos la función List.ReplaceMatchingItems, siendo esta última lista generada el primer argumento.

Nuevo paso donde haremos uso por fin de List.ReplaceMatchingItems, y así efectuar el reemplazamiento necesario:
Replacements = Table.AddColumn(TurnTextToList, "Nombre_1", each Text.Combine(List.ReplaceMatchingItems([Split1],ListaReemp)," ")),
Añadimos una nueva columna 'Nombre_1' que combine el resultado de unir los distintos elementos de la lista descompuesta de cada nombre, que habíamos insertado en la columna 'Split1', con los reemplazamientos definidos en la 'ListaReemp'.

Aquí paramos para explicar qué está ocurriendo, y el por qué del uso de la barra vertical.
Habíamos descompuesto por palabras cada nombre completo, añadiendo al final la |... para luego reemplazar cada elemento coincidente con nuestra lista original:
{{"De|","de"},{"Del|","del"},{"El|","el"},{"La|","la"},{"Las|","las"},{"Los|","los"},{"San|","san"},{"Y|","y"}}
donde sustituimos las palabras clave, con su barra vertical |, por la misma palabra sin barra.
Esto supone, por tanto, que después del reemplazamiento únicamente quedan con | las palabras NO claves, las que no se emplean para componer los nombres o apellidos compuestos...

En el siguiente paso, de forma sencilla, eliminamos la última | incorporada, como último caracter:
MenosUltimoCaracter = Table.AddColumn(Replacements, "Nombre_2", each Text.Start([Nombre_1],Text.Length([Nombre_1])-1)),
En una nueva columna 'Nombre_2' obtenemos la cadena de texto sobre la que seguir trabajando.

Y vamos al siguiente paso. En una nueva columna separamos nuevamente, usando la barra vertical | como separador:
Ultimo_Split = Table.AddColumn(MenosUltimoCaracter, "UltimoSplit", each Text.Split([Nombre_2],"|")),
obteniendo una lista para cada registro de tantos elementos como palabras NO claves (ninguna de la de nuestra lista de reemplazos)

Si tenemos nombres completos debereíamos tener entre tres o cuatro elementos en esta lista...

Realizamos un conteo de elementos generados en cada lista en una nueva columna:
Cuenta_Partes = Table.AddColumn(Ultimo_Split, "CuentaElementos", each List.Count([UltimoSplit])),

Casi acabamos... paciencia ;-)

En los tres pasos siguientes obtendremos una columna para el 'Nombre', otra para el 'Apellido 1' y otro más para el 'Apellido 2'. Nos vasamos en el cálculo previo de número de elementos.
Si hay 4 elementos, parece claro que dos de ellos deberían ser el primer y segundo apellido, y los otros dos parte de un nombre compuesto...
Si solo hubiera 3 elementos, será un caso clásico de primer y segundo apellido junto con un nombre simple.
Esta idea es la que se plasma en los pasos:
AddNombre = Table.AddColumn(Cuenta_Partes, "Nombre", each if [CuentaElementos]>3 then
[UltimoSplit]{0}? & [UltimoSplit]{1}? else
[UltimoSplit]{0}?),
Add1erApellido = Table.AddColumn(AddNombre, "1er Apellido", each if [CuentaElementos]>3 then
[UltimoSplit]{2}? else [UltimoSplit]{1}?),
Add2doApellido = Table.AddColumn(Add1erApellido, "2do Apellido", each if [CuentaElementos]>3 then
[UltimoSplit]{3}? else [UltimoSplit]{2}?),
Nos debemos fijar en un par de aspectos relevantes.
El primero en la forma de trabajar con elementos concretos de una lista (recuerda que PQ trabaja en base 0), por ejemplo:
[UltimoSplit]{0} recupera el primer elemento
[UltimoSplit]{1} el segundo
[UltimoSplit]{2} el tercero
[UltimoSplit]{3} el cuarto
etc...

Por otra parte para evitar posibles 'errores' en caso de la NO existencia de dichos elementos aplicamos el operador ? al final de cada referencia
[UltimoSplit]{0}?
[UltimoSplit]{1}?
[UltimoSplit]{2}?
[UltimoSplit]{3}?
Muy práctico cuando el posible fallo está controlado...

Las distintas columnas añadidas recuperan unas posiciones u otras de nuestra lista previa generada en el campo 'UltimoSplit' en un paso previo.

En el último paso, por estética y limpieza, quitamos todos los campos sobrantes y finalizamos.

Un proceso algo largo (no tanto en realidad) donde se emplean muchas de las funciones mas recurrentes en Power Query, y algunas nuevas sobre las que me he detenido.

Por concluir el artículo de hoy, quería dejar abierta una alternativa, donde el listado de reemplazamientos quede a disposición del usuario en la hoja de cálculo.
Para ello necesitaríamos cargar esa tabla de 'reemplazamientos' al editor de consultas, y retocar las primeras líneas de la consulta anterior

let
    /*Cargando datos de una tabla en la hoja de cálculo*/
    reemplazos = TablaReplacements,
    ListaReemp = Table.AddColumn(reemplazos, "Lista", each ({[Reemplaza], [por]})),
    
    /*Cargando datos de la lista creada en M  */
    //ListaReemp=({{"De|","de"},{"Del|","del"},{"El|","el"},{"La|","la"},{"Las|","las"},{"Los|","los"},{"San|","san"},{"Y|","y"}}),
    /*Cargamos la tabla con los nombres completos a trabajar*/
    NombresCompletos = Tabla1,
    // y el resto de la consulta sería igual...

Seguir leyendo...

FRAC.AÑO. Diferencias entre fechas

Aprenderemos en esta ocasión una función de Excel poco extendida para el cálculo de diferencias entre fechas: FRAC.AÑO(fecha_inicial;fecha_final;[base])

Sin duda su uso poco extendido viene motivado por su curiosidad en el proceso de cálculo para las diferencias entre fechas, donde la precisión no es todo lo que podríamos desear...

Como dato añadido emplearemos para comparar resultados la función SIFECHA (lee aquí más), donde desglosaremos el tiempo entre fechas por años+meses+días con sus diferentes argumentos:
"y" : devuelve la cantidad entera de años en el intervalo de fechas.
"m" : devuelve la cantidad entera de meses en el intervalo de fechas.
"d" : devuelve la cantidad entera de días entre ambas fechas.
"ym" : obtendríamos la cantidad de meses por encima de la cantidad entera de años.
"md" : obtendríamos la cantidad de días por encima de la cantidad entera de meses.

Veamos nuestra función del día:
FRAC.AÑO(fecha_inicial;fecha_final;[base])
siendo clave el tercer argumento opcional '[base]', donde los parámetros permitidos son:
0 u omitido US (NASD) 30/360
1. Real/real
2. Real/360
3. Real/365
4. Europea 30/360
Nos centraremos especialmente en los parámetros con valor 0 -US(NASD) 30/360 y 4 -Europea 30/360, las demás opciones devuelven resultados incompresibles (de los que no he sido capaz de encontrar demasiada información que la explique adecuadamente).
Y es que las opciones 0 (por defecto) o 4 están operando sobre el año comercial de 360 días (12 meses de 30 días)… PERO ambas gestionan el último día del mes de diferente manera!!!

Con 0 - US (NASD) 30/360
Si la fecha inicial es el último día del mes, se convierte al día 30 de ese mes.
Si la fecha final fuera el último día del mes, y la fecha inicial anterior al 30, Excel convierte la fecha final como el 1er día del siguiente mes… de cualquier otra forma la fecha final la trata como el 30 del mismo mes…
Con 4 - Europea 30/360
Tanto si la fecha inicial como la final fueran el 31 de un mes, Excel lo convierte al 30 de dicho mes.

Como se infiere de estos parámetros, para pasar de un año natural de 365/366 días a uno comercial de 360, Excel realiza una serie de reconversiones de fechas complicadas de seguir... haciendo en muchos casos perder días en nuestros cálculos!!.
Te invito a que pruebes el resto de parámetros 1,2 o 3 y verifiques resultados.

Veamos un ejemplo concreto desglosado para su mejor comprensión.
Partimos de una Fecha inicial en B2 y una Fecha final en C2
Sobre estas hemos aplicado las siguientes fórmulas con SIFECHA:
En E2 añadimos =SIFECHA(B2;C2;"y") para obtener la cantidad de años entera en el intervalo de fechas.
En F2 insertamos =SIFECHA(B2;C2;"ym") donde conseguimos la cantidad de meses por encima de la cantidad entera de años.
Y en G2 escribimos =SIFECHA(B2;C2;"md") donde vemos los días por encima de la cantidad entera de meses.

Para las fechas 29/03/2020 y 31/05/2020 tendríamos una diferencia de 2 meses completos (abril+mayo) y dos días (el 30 y 31 de marzo)... podríamos decir que un resultado aceptable...

Vayamos con el cálculo aplicando FRAC.AÑO. Lo desglosaré en distintas celdas para facilitar su entendimiento...
No olvidemos que FRAC.AÑO nos devuelve como resultado la fracción de año que representa el número total de días entre dos fechas dadas (inicial y final).

Insertamos en E6 la fórmula:
=FRAC.AÑO(B2;C2;C6)
Para nuestro ejemplo de fechas devuelve 0,1722222, es decir, cero años y una parte...
Para recuperar el número de años aplicamos la función ENTERO, en la celda E8, sobre ese cálculo, obteniendo cero para el ejemplo.
Por diferencias en F6 incluímos:
=(E6-E8)*12
que sobre la parte decimal anterior, al multiplicarla por 12 resultamos un número de meses (2,06666 en el ejemplo).
En F8 con ENTERO tendremos un número de meses completos.

Finalment en G8 aplicamos con la fórmula:
=(F6-ENTERO(F6))*30
que retornaría el conteo de días excedentes de los meses calculados previos...

Comprobamos la coincidencia del cálculo en este caso entre ambas funciones... ya que las fechas entran dentro de ese juego de configuraciones previamente comentadas.
Si cambiamos nuestra 'base' de la UD (NASD) a la Europea, comprobamos el efecto retorcido de este tercer argumento!!

O este igualmente curioso...

donde con fechas 'límite' 31/03/2020 y 30/06/2020 (ambas entran en esas descripciones comentadas para el argumento [base]), nos devuelven un redondeo de dias y meses... nuevamente perdiendo/ganando un día en el cálculo

Conclusión. Si no buscas una precisión 'milimétrica', FRAC.AÑO es tu función... sin duda más fácil de emplear (y documentada) que SIFECHA (DATEDIF en inglés).

Seguir leyendo...

Contar en rangos NO adyacentes

Hoy toca algo de formulación que nos ayudará a realizar conteos sobre rangos No adyacentes

El objetivo será lograr contar coincidencias sobre rango no continuos tal y como se ve en la imagen anterior... El asunto no es tan simple como emplear funciones ya conocidas como CONTAR.SI o CONTAR.SI.CONJUNTO, y para dar solución mostraré alternativas con funciones como SUMAPRODUCTO, INDIRECTO o ELEGIR.

En primer lugar haremos uso de una fórmula ya empleada en este post, donde incluimos o asignamos la fórmula siguiente en un nombre definido 'ndProblemas':
ndProblemas = =LET(
rangos;(Tabla1[problema1];Tabla1[problema2];Tabla1[problema3]);
Areas;AREAS(rangos);
filas;FILAS(Tabla1[problema1]);
TotalFilas;SECUENCIA(Areas*filas);
NumArea;MULTIPLO.SUPERIOR.MAT(SECUENCIA(Areas*filas);filas)/filas;
Fila;TotalFilas-(NumArea-1)*filas;
INDICE(rangos;Fila;1;NumArea))
Sobre ese vector construido matricialmente podemos realizar la siguiente operación en C14:
=SUMAPRODUCTO((ndProblemas=$B14)*1)
donde evaluamos los VERDADEROS o las coincidencias del vector (ndProblemas) con el valor de B14, al que multiplicamos por 1 para convertirlo en valor y poder sumarlo...
Igualmente válido (y más 'profesional') sería la alternativa:
=SUMAPRODUCTO(--(ndProblemas=$B14))
esto es, empleando el doble menos.

Luego podremos arrastrar hacia abajo para conocer las coincidencias para B15 y B16.

Una segunda manera de lograr nuestro conteo será empleando la combinación de funciones clásicas como: SUMA, CONTAR.SI o INDIRECTO.
Veamos como... en C20 insertamos:
=SUMA(CONTAR.SI(INDIRECTO({"Tabla1[problema1]";"Tabla1[problema2]";"Tabla1[problema3]"});B20))
/ donde INDIRECTO transforma la matriz de constantes {"Tabla1[problema1]";"Tabla1[problema2]";"Tabla1[problema3]"} en los rangos sobre los que contar.. cosa que hará CONTAR.SI, obteniendo por tanto tres conteos!: {3;2;0} (para mi primer caso).
Finalmente sumaremos esos tres valores devueltos matricialmente con SUMA.

Vemos aquí una solución con un razonamiento de aplicación bastante clásico y fácil de comprender...

Vamos con una tercera alternativa.
En C26 insertamos una anidación de ELEGIR en SUMAPRODUCTO:
=SUMAPRODUCTO(((ELEGIR({1\2\3};Tabla1[problema1];Tabla1[problema2];Tabla1[problema3]))=B26)*1)
Donde la peculiaridad la aporta el uso matricial de ELEGIR que nos permite construir un rango o matriz continuo de tres columnas (en nuestro ejemplo) al llamar a la matriz de constantes {1\2\3}... para una vez montada dicha matriz proceder a compararlo con el valor de la ceda B26 y siguientes...
Si ejecutamos ELEGIR({1\2\3};Tabla1[problema1];Tabla1[problema2];Tabla1[problema3]) de manera separada tendríamos la matriz de 3 columnas por 10 filas:
{"ccc"\0\0;"aaa"\"aaa"\0;0\0\0;"ccc"\"aaa"\"bbb";"aaa"\"ccc"\0;0\0\0;"aaa"\0\"aaa";0\0\0;"bbb"\"ccc"\0;0\0\0}
Tras la comparativa aplicaremos SUMAPRODUCTO para obtener el conteo de coincidencias o VERDADEROS.

Adicionalmente a los cálculos previos, podemos obtener el conteo de celdas vacías en esos rango no adyacentes con alguna de las siguientes fórmulas:
=SUMAPRODUCTO((ndProblemas="")*1) (o también =SUMAPRODUCTO(--(ndProblemas="")))
otra manera
=SUMA(CONTAR.SI(INDIRECTO({"Tabla1[problema1]";"Tabla1[problema2]";"Tabla1[problema3]"});""))
o bien
=SUMAPRODUCTO((ELEGIR({1\2\3};Tabla1[problema1];Tabla1[problema2];Tabla1[problema3])=0)*1)
Algunas de estas funciones se deberán ejecutar, según la versión, matricialmente... esto es, presionando Ctrl+Mayusc+Enter OJO!!.

Como comentario final, vistas algunas de las alternativas, decir que la potencia de las fórmulas descritas está en que los rangos podrían estar incluso en hojas diferentes o ser de tamaños distintos... es decir, potencia en estado puro ;-)

Seguir leyendo...

Calendarios en Excel. Fórmulas desbordadas

Cada vez es más frecuente encontrar por estas fechas publicaciones de todo tipo ofertando o simplemente mostrando como elaborar un calendario laboral para el próximo año...
En este mismo blog puedes encontrar ejemplos de calendarios perpetuos:
- con macros
- con fórmulas
- con Power Query

El día de hoy daremos una vuelta al asunto empleando las fórmulas desbordadas (de momento solo válidas para Microsoft 365).
Con nuestras fórmulas y un poco de formato condicional y formato personalizado llegaremos a:

Comenzaremos montado la formulación para enero, y posteriormente poder copiar y pegar para el resto de meses...
En primer lugar en B1 añadimos el año, en el ejemplo 2020, pero servirá cualquier año que quieras...
En B2 añadimos un 1 que representará el mes del año (ya sabes, 1 - enero, 2 - febrero, ..., 12 - diciembre).
En B4 la fórmula desbordada que mostrará la fechas del mes...
=SI(MES(SECUENCIA(6;7;FECHA($B$1;B2;1)-DIASEM(FECHA($B$1;B2;1);2)+1;1))<>B2;
"";
SECUENCIA(6;7;FECHA($B$1;B2;1)-DIASEM(FECHA($B$1;B2;1);2)+1;1))
La clave de la fórmula reside en la matriz secuencial devuelta por:
SECUENCIA(6;7;FECHA($B$1;B2;1)-DIASEM(FECHA($B$1;B2;1);2)+1;1)

con el que obtenemos una secuencia de fechas empezando con la fecha del lunes anterior al primer día del mes:
FECHA($B$1;B2;1)-DIASEM(FECHA($B$1;B2;1);2)+1
Matriz de seis filas (una por cada posible semana del mes) por siete columnas (una por cada día de la semana).
Acabamos nuestra función SECUENCIA indicando que el incremento o paso será de +1.

Con el condicional conseguimos 'dejar fuera de la ecuación', esto es, no mostrar las fechas que no correspondan al mes desplegado:
SI(MES(SECUENCIA(6;7;FECHA($B$1;B2;1)-DIASEM(FECHA($B$1;B2;1);2)+1;1))<>B2;"";...)

Incorporamos un encabezado en B3:H3 con los días de la semana (lunes, martes, miércoles, ...)

En C2 añadimos la función
=TEXTO(C6;"mmmm-aa")
para visualizar el mes de trabajo...

Y estariamos listos para perfilar el aspecto.
Primero aplicaremos al rango desbordado B4:H9 un formato personalizado de número tipo 'd'

Copiamos el rango trabajado hasta completar la distribución 'típica' de los doce meses

Ahora aplicaremos algo de formato condicional básico para resaltar los fines de semana, así como el día corriente y los días festivos (si disponemos de un listado de ellos!!)

Son tres reglas de formato condicional con fórmulas:
- B4=HOY() para marcar el día corriente
- DIASEM(B4;2)>=6 para resaltar los sábados y domingos
- COINCIDIR(B4;ndFestivos;0) para subrayar los días festivos

Para mi ejemplo previamente he creado una tabla con un nombre definido asignado 'ndFestivos'

Le quitamos las líneas de cuadrícula de las celdas y listo!!

Una variante de este calendario perpetuo sería mostrarlo en vertical

El montaje y formulación es casi idéntico (no podría ser de otra manera).
En B1 añadimos el año.
En B2 la función =SECUENCIA(1;12)
En A2:A45 la fórmula =TEXTO(SI(MAX(B4:M4)=0;"";MAX(B4:M4));"dddd")
que genera una secuencia de los días de la semana acorde a las fechas mostradas...
Y finalmente en B4
=SI(MES(SECUENCIA(42;1;FECHA($B$1;B2;1)-DIASEM(FECHA($B$1;B2;1);2)+1;1))<>B2;
"";
SECUENCIA(42;1;FECHA($B$1;B2;1)-DIASEM(FECHA($B$1;B2;1);2)+1;1))
fórmula que copiaremos para el resto de meses en C4, D4, ..., M4
La diferencia con el primer calendario es el tamaño del rango desbordado que devuelve SECUENCIA:
SECUENCIA(6;7) para el primer ejemplo
SECUENCIA(42;1) para este último

Aplicaremos los mismos formatos personalizados y formatos condicionales que en el caso anterior, y ya tenemos un calendario perpetuo 'vertical'.

Seguir leyendo...