Nuevo Icono Hoja Protegida

En la última actualización de julio de 2020 Microsoft han lanzado una curiosa funcionalidad (que ya existía, por cierto, en la hoja de cálculo de Google) y solicitada por algunos usuarios de Excel: El icono de hoja protegida:

No hay que hacer nada especial ni excepcional, simplemente aparecerá en la pestaña de la hoja un 'candado' cuando la hoja se proteja desde la acción correspondiente.
Desde la Ficha Revisar > grupo Proteger > botón Proteger Hoja

Es meramente un icono informativo, pero muy visual ;-)

Seguir leyendo...

Power Query: Diferencia con el dato anterior

Veremos un buen truco con Power Query para poder calcular diferencias de un dato con el previo, bien en valor absoluto o bien la variación porcentual...
Tal como vemos en la imagen siguiente:

Partiremos de una tabla de ventas con diferentes años, de los cuales queremos conocer la diferencia de ventas de un año respecto al previo, e igualmente cuánto supone esa variación porcentualmente hablando.
En primer lugar cargamos la Tabla al Editor de Power Query, desde la Ficha Datos > Obtener y transformar > Cargar Tabla o Rango.
Una vez cargada, desde el menú Agregar Columna > General > Columna de Índice añadiremos dos columnas personalizadas de índice, una primera que comienze desde 0 y la segunda desde 1

Este punto es básico para el truco a emplear...

A continuación usaremos la herramienta de Combinar consultas para combinar nuestra query sobre si misma!!.
Entonces desde el menú Inicio > Combinar > Combinar consultas

Combinamos el 'Índice desde 0' con el 'Índice desde 1', y a continuación Expandimos el campo quedándonos solo con las 'Ventas'

Una vez expandido ya disponemos del dato de Ventas del periodo anterior!.
OJO, por que los registros se han desordenado!!.
Asegúrate de reordenarlos en Ascendente por el campo de 'Índice desde 0'

Ya podemos realizar nuestros cálculos a nivel de fila... Para ello añadiremos un par de Columnas personalizadas, así desde el menú Agregar Columna > General > Columna personalizada
El primer cálculo será una sencilla diferencia entre los valores de 'Venta' y 'Venta.1' (que corresponde al valor del año previo!)

El segundo cálculo que añadiremos es la variación porcentual respecto del año anterior, esto es, el 'Delta' recién calculado entre las 'Ventas' del año previo

Con los cálculos realizados Quitamos columnas no necesarias y terminamos Cerrando y cargando en la hoja de cálculo que queramos... obteniendo el resultado visto al inicio de la publicación.

Seguir leyendo...

Alternativas a MAX.SI.CONJUNTO

Hace bastante tiempo que no escribo nada sobre funciones, y revisando material publicado me percaté que nunca escribí sobre la función MAX.SI.CONJUNTO (operativa desde la versión 2016 y +).
Esta función es muy interesante, dentro de la familia de las funciones .SI.CONJUNTO, ya que permite recuperar un valor máximo de entre distintos registros que verifiquen hasta 127 criterios... muy potente, sin duda!!.
Veamos un ejemplo:

La función aplicada a este ejemplo que permite conocer la fecha mayor (más reciente) para un 'empleado' y un 'cliente' dado sería:
=MAX.SI.CONJUNTO(TblVisitas[Día];TblVisitas[Empleado];$F3;TblVisitas[Cliente];G$2)
función al uso, donde el primer argumento indica el rango de donde obtener el valor máximo, y el resto de argumentos alterna rango de criterio y criterio aplicado (ver funciones SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO o PROMEDIO.SI.CONJUNTO para mayor entendimiento).

La función es muy potente pero al no estar disponibles en versiones previas al 2016 nos da problemas de compatibilidad, que hoy veremos algunas formas de 'combatir'.

Una primera opción sería usar fórmulas matriciales (las clásicas CSE - ejecutadas presionando Ctrl Mayusc y Enter):
{=MAX(SI(TblVisitas[Empleado]=$F12;SI(TblVisitas[Cliente]=G$11;TblVisitas[Día])))}
donde hemos incluido tantos condicionales SI como criterios tengamos, para finalmente aplicar la función MAX.
Como inconveniente de este tipo de funciones tenemos el alto consumo de recursos que necesita para su recálculo.

Una alternativa diferente sería el uso de INDICE en su formato de referencia, donde al indicar como argumento de fila un cero 0, recuperamos un conjunto o matriz de valores...
=MAX(INDICE(TblVisitas[Día]*(TblVisitas[Empleado]=$F20)*(TblVisitas[Cliente]=G$19);0)) Además de forma interna evalúa las condiciones o criterios obtenidos del producto de rango condicionados.

Otra opción muy similar a la anterior vendría de la mano de SUMAPRODUCTO
=SUMAPRODUCTO(MAX((TblVisitas[Día]*(TblVisitas[Empleado]=$F28)*(TblVisitas[Cliente]=G$19))))
con una mecánica de cálculo casi idéntica a la anterior.

Y la última posibilidad que he trabajado responde al uso de las fórmulas desbordadas (disponibles a fecha de hoy a ciertos usuarios de 365):
=INDICE(ORDENAR(FILTRAR(TblVisitas[Día];(TblVisitas[Empleado]=$F36)*(TblVisitas[Cliente]=G$35);"");1;-1);1)
donde con la nuevas funciones básicamente se filtran por las condiciones necesarias de 'cliente' y 'empleado', para luego ordenar en sentido descendente... quedando la fecha mayor o más reciente en la primera celda/superior del rango desbordado.
Finalmente la funció INDICE recupera esa primera celda del rango desbordado obtenido.

Puedes ver en la imagen más arriba el resultado obtenido en cada caso.

Seguro habrá más formas y variantes, pero creo es un buen compendio de formulación avanzada ;-)

Seguir leyendo...

Power Query: Repetir y numerar filas como n de N

Recientemente una lectora preguntaba la posibilidad de pasar de un listado donde se indicaban repeticiones por registro a un listado desglosado con tantas filas repitas y numeradas como se indicase...
Fácil de entender viendo la imagen siguiente:

Vemos como pasamos de una sola fila donde se indican 5 repeticiones a cinco filas numeradas como 1 de 5, 2 de 5, ..., 5 de 5.. y así con cada registro.
La usuaria pedía una macro, pero pensé que Power Query podría ofrecer un resultado igual de válido... y así fue, y así lo voy a explicar. ;-)
Comenzamos cargando la tabla al Editor de consultas de Power Query, desde la Ficha Datos > grupo Obtener y transformar > botón Desde tabla o rango.
Ya en el editor empezaremos a incorporar etapas o pasos.
El primer paso es añadir una columna personalizada con la siguiente función, que emplea Table.Repeat:

Table.Repeat(
  		Table.FromRecords({
        	[Col1=1]
            }),
        [Repetición])

El resultado sería:

Donde vemos que Table.Repeat devuelve una tabla con tantas filas como se especifiquen en el segundo argumento.. en mi ejemplo el valor dado en el campo 'Repetición' Table.Repeat(table as table, count as number) as table
Este paso es básico, ya que nos permite repetir las filas tal como esperábamos...
En el siguiente paso expandiremos la columna recién añadida, y añadiremos una columna de índice:

Para la siguiente etapa debemos recordar cómo obtener un acumulado creciente condicionado con Power Query, para ello revisa esta entrada del blog
Así pues añadimos una nueva columna personalizada:

let Id=[Id],Índice=[Índice] in
        List.Sum(Table.SelectRows(ÍndiceAgregado, each [Índice]<=Índice and [Id]=Id )[Col1])

El resultado es un incremental basado en la anterior columna personalizada, obtenida con Table.Repeat, condicionada al campo 'Id' original...

Ya tenemos todo lo que necesitamos. La nueva columna 'Acum' que crece por cada registro, y el total para cada registro en el campo 'Repetición' de la tabla original.
Por tanto bastará seleccionar la columna 'Acum' y luego 'Repetición' y desde la ficha Transformar > Columna de texto > Combinar columnas, personalizando el separador por ' de '

Finalizamos eliminando columnas que ya no necesitamos... como 'Col1' e 'Índice'. LLegando al resultado mostrado al imagen del post.

El código completo visto desde el editor avanzado sería:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblVentas"]}[Content],
    ColADD = Table.AddColumn(Origen, "Personalizado", each Table.Repeat(
                                                                Table.FromRecords({
                                                                    [Col1=1]
                                                                }),
                                                                [Repetición])),
    Expandido = Table.ExpandTableColumn(ColADD, "Personalizado", {"Col1"}, {"Col1"}),
    ÍndiceAgregado = Table.AddIndexColumn(Expandido, "Índice", 1, 1),

///
RunTotal = Table.AddColumn(ÍndiceAgregado, "Acum", each let Id=[Id],Índice=[Índice] in
        List.Sum(Table.SelectRows(ÍndiceAgregado, each [Índice]<=Índice and [Id]=Id )[Col1])),
    #"Columnas combinadas" = Table.CombineColumns(Table.TransformColumnTypes(RunTotal, {{"Acum", type text}, 
        {"Repetición", type text}}, "es-ES"),
        {"Acum", "Repetición"},
        Combiner.CombineTextByDelimiter(" de ", QuoteStyle.None),"n de N"),
    #"Columnas quitadas" = Table.RemoveColumns(#"Columnas combinadas",{"Col1", "Índice"})
in
    #"Columnas quitadas"

Ya podemos Cerrar y cargar en... nuestra consulta en nuestra hoja de cálculo destino.

Seguir leyendo...

Power Query: Renombrar pasos de la consulta

Hoy hablaremos de una recomendación al trabajar con nuestras consultas de Power Query, en especial cuando éstas tienen muchos pasos intermedios grabados... la recomendación del día es: Renombra los pasos
Imagina una consulta de este estilo

Lo normal es que la asignación de los nombres de los pasos se asignen automáticamente en el idioma elegido; esto puedes definirlo en Archivo > Opciones y configuración > Opciones de consulta > GLOBAL > Configuración regional > Pasos de la consulta

Lo 'malo' es que los nombres de los pasos son demasiado genéricos y poco descriptivos... de ahí la recomendación de Renombrar los pasos, lo cual facilitará futuras modificaciones y búsquedas dentro de la consulta.

El proceso de cambio es simple, basta hacer clic derecho sobre cada paso y seleccionar la opción Cambiar nombre, repitiendo la acción en todos los pasos que consideremos... lo interesante es que todo el código M generado se ajustará a este cambio...

La diferencia que aporta este trabajo es grande... ya que con un simple vistazo, tras renombrar los distintos pasos, podemos localizar etapas o pasos a modificar o consultar muy rapidamente...

Se ve en la imagen anterios como los nuevos nombres describen algo mejor la acción realizada, tal como buscábamos...

Obviamente esto se debería realizar a medida vamos grabando los pasos ;-)

Seguir leyendo...

Excelforo: XI aniversario y VII Premio Microsoft MVP Excel 2020-2021

ONCE años... no son pocos ;-)
y por séptimo año consecutivo (desde el 2014) he sido premiado por Microsoft con el título MVP (Most Vauable Professional) en Excel... (ver perfil)

Empezar por agradecer el apoyo mostrado durante todo este tiempo, sin vosotros nada hubiera ocurrido.
Agradecer, también, a Microsoft por este nuevo premio... lo tomo como incentivo para seguir difundiendo todo el conocimiento posible de nuestra hoja de cálculo favorita: Excel.

Por otra parte, son más diez años!! ya prestando la mejor formación presencial y elearning(online).
Sin olvidar todos aquellos clientes a los que las horas de consultoría han ahorrado tiempo y dinero.
Visita mi web:
www.excelforo.com


Como en años pasados, en este nuevo aniversario, mostraré algunos datos estadísticos acumulados hasta la fecha; respecto al blog diré que son ya más de 11.350.000 páginas vistas, y un millones de usuarios de todo el mundo (España, México, Colombia, Perú, Chile, Argentina, Ecuador, Estados Unidos.. y un largo etcétera)...
Son ya más de 1.100 entradas publicadas, de casos prácticos propuestos por vosotros, solucionados y explicados; y más de 12.000 comentarios, y desde luego muchísimas horas dedicadas.

No quiero olvidar aventuras ya consolidadas, como el grupo de Facebook de Excel: Microsoft Excel en Español...
https://www.facebook.com/groups/ExcelEspanol/
donde lo especial es que trato de controlar al máximo los comentarios que no aporten nada (como publicidad de cursos, web, grupos, etc...), reduciendo el grupo a contenido de valor añadido.
Contento de alcanzar los 19.000 seguidores!!
ÚNETE.. no lo dudes!


No ha sido un año fácil en lo personal y esto ha repercutido en todos los aspectos de mi trabajo y de mis hobbies... lo que ha repercutido en mi imposibilidad en responder comentarios o correos, por desgracia el tiempo es finito para todos y en estos momentos tengo que gestionar lo que me queda.
Pido perdón por esto.
Como siempre mi agradecimiento hacia todos vosotros.

Seguir leyendo...