jueves, 6 de agosto de 2020

INDICAR.LIBRO para crear índice de hojas

Son muchos los cambios que en los últimos meses estamos viviendo en el entorno de Excel, y muchas discusiones sobre el fin de una época (Scripts versus VBA, entre otras).
Sin dejar pasar estas nuevas oportunidades, sin dejar de aprender todos estos nuevos recursos, no es necesario (en mi opinión personal) dejarse llevar por el nerviosismo.
Está más que demostrado que Excel, hasta la fecha, no abandona ni deja a nadie atrás... incluso 'tecnologías' desfasadas de un pasado remoto de nuestra hoja de cálculo siguen estando operativas. Hablo en particular de las funciones de Excel - macros 4.0 .
Hoy en particular hablaré de la función INDICAR.LIBRO (GET.WORKBOOK), con su sintáxis:
=INDICAR.LIBRO(tipo;[Libro])
El segundo argumento, opcional, indicaría el libro de trabajo sobre el que actúa la función.
Si no indicamos nada trabajará sobre el libro abierto.
El primer argumento 'Tipo' admite valores enteros entre 1 y 38, siendo los más útiles:
1 - para recuperar una matriz/listado horizontal de todas las hojas del libro, en la forma [Libro]Hoja
3 - matriz/listado horizontal de todas las hojas seleccionadas del libro
4 - el número de hojas en el libro
38 - el nombre de la hoja activa.

Recordemos que para interactuar con estas funciones macros 4.0 debemos incorporarlas dentro del contexto de Nombres definidos.
Hoy emplearemos esta posibilidad para obtener un índice de las hojas de nuestro libro de trabajo, sobre el que construiremos anexo a éste un hipervínculo para movernos entre hojas...
INDICAR.LIBRO para crear índice de hojas

El primer paso necesario será crear un Nombre definido ('VinculoHojas') con la siguiente fórmula:
=DERECHA(TRANSPONER(INDICAR.LIBRO(1));LARGO(TRANSPONER(INDICAR.LIBRO(1)))-ENCONTRAR("]";TRANSPONER(INDICAR.LIBRO(1)))) & T(AHORA())
INDICAR.LIBRO para crear índice de hojas

Aspectos relevantes de la fórmula empleada...
Primero: usamos TRANSPONER sobre la fúnción INDICAR.LIBRO(1) ya que esta devuelve una matriz horizontal de datos... y la necesitamos en Vertical!.
Segundo: al devolver el nombre de la hoja en su forma [Libro]Hoja aplicamos la combinación clásica:
=DERECHA(texto;LARGO(texto)-ENCONTRAR("]";TEXTO))
lo que permite recuperar únicamente el nombre de la hoja.
Tercero: concatenamos al final de la fórmula anterior la función T(AHORA()) para incorporarle volatilidad ante los cambios en el número de hojas... Si bien siempre habrá que Calcular (presionar F9) para refrescrar los datos si aparecen hojas nuevas...
Si trabajamos con Microsoft 365 operaremos con su forma desbordada y bastará escribir en B3 la llamada a nuestro nombre definido =VinculoHojas
INDICAR.LIBRO para crear índice de hojas

Por decorar un poco más el índice en A3 añadimos un ordinal:
=FILA(B3#)-FILA($B$3)+1
haciendo referencia a la fórmula desbordada creada en B3... lo que se hace empleando #.

Y por último, por añadir más funcionalidad, en C3 insertamos la fórmula HIPERVINCULO:
=HIPERVINCULO("#'"&B3#&"'!A1")
INDICAR.LIBRO para crear índice de hojas

Listo. Ya tenemos nuestro índice dinámico de hojas...

Como curiosidad observamos que solo la primera celda toma el aspecto de 'celda vinculada' típica; si bien todas ellas son operativas.
Si no usas MS 365 tendrás que emplear una combinación de la función INDICE con otras funciones (como FILA) para recuperar los diferentes elementos de nuestra matriz de INDICRA.LIBRO(1).

Revisa esta entrada del blog y te sorprenderas de otra aplicación de esta función macro 4.0

jueves, 30 de julio de 2020

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:
Nuevo Icono 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
Nuevo Icono Hoja Protegida

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

jueves, 16 de julio de 2020

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:
Power Query: Diferencia con el dato anterior
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
Power Query: Diferencia con el dato anterior

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
Power Query: Diferencia con el dato anterior
Combinamos el 'Índice desde 0' con el 'Índice desde 1', y a continuación Expandimos el campo quedándonos solo con las 'Ventas'
Power Query: Diferencia con el dato anterior
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'
Power Query: Diferencia con el dato anterior
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!)
Power Query: Diferencia con el dato anterior
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
Power Query: Diferencia con el dato anterior
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.