martes, 17 de mayo de 2022

Excel e historia (I): Excel Macro Language (XLM) - Macros 4.0

El 30 de septiembre de 1985 Microsoft puso en venta la primera versión de Excel, es decir, la versión 1.0 y aunque a muchos les sorprenda, no era para MS-DOS 3.0, sistema operativo que en aquel entonces usaba la corporación, sino para las Macintosh (MAC) de Apple que tenían su propio sistema operativo. Dicho sea de paso, la primera versión de Windows no aparecería hasta unos meses después (para quienes no lo saben).

¿Cuál es el dato importante en esta ocasión? Pues desde dicha primera versión ya Excel traía macros. Sí, así como lo leen, Excel ya tenía incorporadas macros e incluso tenían su propia grabadora de macros.

Claro, algo importante a resaltar es que las macros en aquel entonces no eran basadas en Visual Basic for Application (VBA) como las que usamos hoy en día. En aquella primera versión de Excel existían tres tipos de archivos: hojas de cálculo, macros y gráficos.

Estas macros se programaban en aquellas Macro Sheet y si los archivos diversos de Excel estaban vinculados, podían tenerse abiertos y trabajar entre ellos haciendo uso de la memoria de la MAC. Este lenguaje de macros tenía sintaxis muy similar a las fórmulas y sus instrucciones se almacenaban en las celdas de las hojas de macros (Macro Sheet). Microsoft inicialmente llamó a este lenguaje como Excel Macro Language (XLM) y con la compra de Excel venía incluido un manual de referencia al uso propio de dicho programa, así como uno llamado “Arrays, Functions, and Macros for the Apple Macintosh”, mediante el cual se podían ver y aprender a usar también dicho lenguaje de macros mencionado.

Unos años después, Microsoft lanza la primera versión de Excel para Windows, aunque mantuvo la numeración correlativa con la versión para MAC y así nació Excel 2.0 el 31 de octubre de 1987.

Con esta nueva versión de Excel, la arquitectura de los archivos cambia y comienzan a usarse los formatos de archivos de intercambio binario, BIFF2 para este caso, pero dicho tipo sigue siendo de una sola hoja, aunque predetermina la extensión *.xls.

Esta versión de Excel también vino con su manual para macros “Microsoft Excel: Functions and Macros”. En dicho manual, así como el de la versión de Excel 1.0, se define que hay dos tipos de macros; las llamadas macros de comandos debido a su similitud con los comandos integrados en Microsoft Excel y las llamadas macro de función, que son parecidas a las funciones nativas de Excel.

Para que se entiendan como funcionaban dichas macros, vamos a un ejemplo de lo que le decían macro de comando, pero claro, lo haremos en nuestras versiones actuales de Excel, aunque será bastante parecido a lo que se hacía antaño. Abran un archivo nuevo, luego en cualquier pestaña den clic derecho y elijan “Insertar…”.

En el cuadro de dialogo que saldrá, elijan “Hoja internacional de macros” que será prácticamente igual a las hojas clásicas de Excel.

Escribe los siguiente en las celdas respectivas:

Aquí se los dejo como texto por si acaso:

Prueba
Minombre=INPUT("Ingresa tu nombre") 
=ALERT("Hola " & Minombre) 
=RETURN() 

Para ver como actúa, coloquen el cursor en la celda A1, den clic derecho y elijan “Ejecutar…”.

En el siguiente dialogo elije “Ejecutar”.

Si todo salió bien, te saldrá esto en pantalla:

Ingresen su nombre y denle clic al botón “Aceptar” y saldrá esto en pantalla:

Como ven, hemos ejecutado una macro en la que ingresamos valor a una variable a través de un Input y luego con una Alert mostramos el nombre ingresado. Sé que es simple, pero la idea es que vean cómo funcionan.

Ahora haremos lo propio, pero con una macro de función que como verán funcionaban muy parecido a lo que hoy conocemos como las Funciones Definidas por el Usuario (UDF por sus siglas en ingles) de VBA. Como las hojas de macro permiten más de un comando y funciones, usaremos la misma, pero en otras celdas. Escriban lo siguiente:

Nuevamente aquí también se los dejaré como texto:

Cambiodolar
=ARGUMENT("mivalor") 
=mivalor*3.81 
=RETURN(A9) 

En su época dichas macros de función se llamaban colocando así en la celda de una hoja de Excel en donde se deseaba obtener el resultado (En donde 1000 es la cantidad que multiplicará la función creada por 3.81 como se definió), aunque el archivo XLM necesariamente tenía que estar abierto:

= Nombredelarchivoconlasmacros.XLM!Cambiodolar(1000)

Como ya no usamos las versiones de Excel en donde funcionaban los archivos *.xlm, tenemos que hacer lo siguiente: Sitúate en la celda A8 y en la pestaña “Fórmulas”, en el grupo “Nombres definidos” activa el botón “Asignar nombre” y en el cuadro de dialogo llena tal cual aquí y le das clic al botón “Aceptar”.

Luego, en una hoja común del libro, en cualquier celda simplemente colocaremos esto y el resultado evidentemente será 3810.

=Cambiodolar(1000)

Después de estos ejemplos, volvamos nuevamente a la historia. En 1990 Microsoft lanza la versión 3.0 de Excel cuyos formatos de archivo eran esta vez del tipo BIFF3. Dos años después, en 1992 se lanza Excel 4.0 (arquitectura BIFF4), última versión de dicho programa en que solamente se usaban el tipo de macros de las que hoy hablamos y en la cual los archivos eran necesariamente independientes. La particularidad de dicha versión es que ya no solo se emite en inglés, sino en varios otros idiomas y lo mismo aplica para las instrucciones/funciones de sus macros. Yo tengo mi Excel en español/castellano, asumo que quienes están leyendo, mayoritariamente, también, así que vamos a probar estas macros en nuestro idioma.

Líneas arriba les mencioné como insertar las una “Hoja internacional de macros”, pero en esta ocasión elijan “Macro de Microsoft Excel 4.0”. Verán que es idéntica la internacional, pero en esta ocasión escriban esto:

El texto:

PruebaCastellano
minombrencastellano=INTRODUCIR("Ingresa tu nombre") 
=ALERTA("Hola " & minombrencastellano & " mira la barra de estado") 
=MENSAJE(VERDADERO;"Este Excel es de  " & minombrencastellano) 
=VOLVER() 

Ojo a que yo uso el punto y coma como separador de argumentos, si ustedes usan la coma, cambien por lo adecuado en la línea del “Mensaje”. Se supone que ya aprendieron como ejecutar dicha macro, así que se los dejo a ustedes esta vez.

En 1993 Microsoft lanza la versión 5.0 de Excel (arquitectura BIFF5), con dicha versión aparecen los archivos que podían tener varias hojas (libros) y sobre todo aparece Visual Basic for Application (VBA) y sus rutinas comienzan también a conocerse como macros. Para diferenciar las macros de VBA con las de XLM, a estas últimas comienzan a conocerlas como Macros 4.0 o macrofunciones. Si bien se mantiene la compatibilidad con las macros 4.0 en versiones posteriores de Excel (como por ejemplo con la inserción de hojas mencionadas en este artículo), la versión 5.0 es la última en que se mantiene la grabadora de dicho tipo de macros.

Estos años de lamentablemente pandemia, nuevamente se habla de las Macros 4.0 ya que, aprovechando que muchos antivirus dejaron de detectarlas, algunos hackers han estado haciendo de las suyas con ellas. Por cierto, para usar Macros 4.0 no olviden habilitar su activación: Enlace.

Por cierto, hay algunas otras formas de seguir usando Macros 4.0 sin necesidad de insertas las hojas comentadas. La primera que quiero comentarles es una que emula las macros de funciones a través del uso del “Administrador de nombres”.

Vamos a crear una función con Macros 4.0 que detecte cuando el texto de una celda tiene aplicado el formato de Tachado. En el “Administrador de nombres” vamos a agregar el siguiente nuevo nombre “Detectartachado”, pero previamente sitúen el cursor en la celda B1 y una vez hecho eso procedan a crearlo y en “Se refiera a” coloquen exactamente esto:

=INDICAR.CELDA(23+0*HOY();!A1)

No olviden cambiar mi punto y coma por una coma de ser el separador que ustedes usan. La función INDICAR.CELDA (GET.CELL) devuelve información acerca del formato, ubicación o contenido de una celda y su tipo 23 nos indica si todos los caracteres de la celda o solamente el primero están tachados, devolviendo VERDADERO o de lo contrario devuelve FALSO. Lo del “+0*HOY()” es solo para hacer volátil, en la medida de lo posible, dicha función. Eso sí, en este caso cuando se use la fórmula reconocerá si la celda inmediata de su lado izquierdo tiene el formato de "Tachado" activado o no. Hay formas de hacer que sea otra celda, pero lo probaremos en otra ocasión. Entonces, por ejemplo, si en la celda A3 tengo tento tachado, en B3 colocaré lo siguiente:

=Detectartachado

El resultado en la celda B3 será "VERDADERO". Por supuesto que podemos anidar dicha función con otras para cambiar y/o mejorar la respuesta /resultado en la celda, pero eso también será tarea para cada uno/a.

Una última forma es hacer uso de las Macros 4.0 a través de VBA, sí, no leyeron mal, vamos a usar VBA y en específico el método ExecuteExcel4Macro del Excel. Para que sea, creo yo, más fácil de entender usaremos nuevamente INDICAR.CELDA (GET.CELL). Supongamos una hoja con el CodeName Hoja1 y que en la celda B1 se ha aplicado formato “Negrita”, entonces, prueben esta macro:

Sub NegritaMacros4()

Application.Goto Hoja1.Range("B1") 
MsgBox "Celda con formato TACHADO: " & ExecuteExcel4Macro("GET.CELL(20)") 

End Sub 

Corran la macro y el MsgBox debería decir lo siguiente:

Y esto amigos/as son las Macros 4.0, o macrofunciones, o Excel Macro Language (XLM), espero les haya resultado entretenido. Hasta la próxima.

Abraham Valencia
Lima, Perú

Descargue el ejemplo aquí

sábado, 7 de mayo de 2022

El lado oscuro del Excel

Estimados/as amigos/as, este domingo 08 de mayo a las 11:00 a.m. hora de Perú y México, estaremos conversando con Fernando González (España) , Gerson Pineda (Honduras) y Leopoldo Blancas (México), de ese lado que casi nunca se comenta sobre Excel ¡Su lado oscuro! Esperamos puedan acompañarnos. https://youtu.be/qhjISm9PQbM

Un abrazo a todos y todas.

Abraham Valencia
Lima, Perú

miércoles, 20 de abril de 2022

Ranking (Top) con Slicers (Segmentación de datos) y Power Pivot

Hace un par de meses, en uno de los foros de Excel en los cuales participo, preguntaron si era posible tener un Top (Ranking) de vendedores que se visualice en algún gráfico y que no se necesite usar macros. Si bien yo no participé en dicho debate, la alternativa que le dieron al usuario fue, finalmente, usar macros. Me quedé con la curiosidad y hace algunos días logré algo muy cercano, pero necesariamente tuve que usar cuatro columnas auxiliares (en mi experiencia a la mayoría de usuarios no les gusta usarlas) y, además de la tabla de datos, tuve que usar una tabla adicional en la que coloqué los números Top.

Relacioné a ambas con una de las columnas auxiliares y logré que con un Slicer de los Top la tabla dinámica y el gráfico dinámico de datos cambien dependiendo de lo elegido en el Slicer.

Claro, el pequeño dilema es que para que todo se actualice, después de elegir el valor en el Slicer de los top, pues necesariamente hay que usar el botón de actualizar de la tabla dinámica. Por supuesto se puede hacer que se actualice con una macro, pero volveríamos al dilema inicial que les comenté: Hacerlo sin macros (por más que el actualizar con macros no involucre directamente lo del ranking). Pero bueno, no se preocupen, al final basándome en lo logrado, conseguí el objetivo a través de Power Pivot, pero eso sí, antes de explicarles esa solución, igual les comentaré lo previo, ya que fue la base para todo. Trataré de hacerlo de forma resumida:

Columna “Sumarsi”. Tiene la siguiente fórmula:

=SUMAR.SI(Hoja1!$B$2:$B$201;Hoja1!$B2;Hoja1!$C$2:$C$201)

Creo que para muchos/as es obvio que lo que intento con ella es que a cada vendedor tenga el total de sus ventas y por el momento no importa que se repita. Si bien eso se puede hacer con una tabla dinámica, en esta ocasión lo necesitaremos para lograr crear un ranking.


Columna RankingVendedor. Tiene la siguiente fórmula:

=SUMA(1/SI($E$2:$E$201>E2;CONTAR.SI($E$2:E201;$E$2:$E$201);9.999999999E+307))+1

Esta fórmula, apelando a el número más alto que puede calcular Excel, pues logra generar números de ranking sin repetidos a diferencia de JERARQUIA (tilde omitida como en la función). No, olvidar que se tiene que ingresar como fórmula matricial (excepto para Office 365).

Columna EligeRanking. Tiene la siguiente fórmula:

=SUBTOTALES(4;DatosRanking[NivelTop])

Esta columna es la que relaciona a la tabla de datos con la de los top. No olvidemos que los Slicer, al igual que los filtros, lo que hacen es ocultar filas, así que con SUBTOTALES podemos conseguir el valor de la tabla de los top, sea cual sea. Uso el 4 (equivalente a MAX), ya que, al ser valores distintos en dicha tabla, elijamos cual elijamos se mostrará siempre uno, es decir, siempre será el máximo (también podría usarse MIN).

Columna FiltrarVendedor. Tiene la siguiente fórmula:

=SI(F2 <= G2;1;0)

Es decir, si el RankingVendedor es menor o igual a EligeRanking, pues colocamos un 1 ¿Para qué? Para que al elegir el valor del Slicer del top/ranking, pues nos indique con un 1 (uno) cuales son los valores de los datos que son mayores o iguales y ese uno será lo que usaremos para mostrar o no los valores que necesitemos. ¿Por qué mayores? Porque para un ranking los mejores son los de menor número ¿O no recordaban eso? Igual cuando vean el archivo se entenderá mejor.

Luego, tipo dashboard, insertaremos una tabla dinámica (TD) y un gráfico dinámico. Eso sí, en la TD hagamos los siguiente. Usaremos la columna “C” (Monto) y sus valores sumados y la columna FiltrarVendedor (“H”), pero ojo con esta última, en la configuración del campo usaremos “Promedio”.

>

Ahora en la TD, en la columna de los vendedores, en el filtro eligen “Filtros de valor”, luego “Mayor que…”, en la primera lista desplegable elegimos “Promedio de FiltrarVendedor”, en la segunda “Es mayor que” y en el último casillero agregamos un cero (0).

Luego en la tabla de los Top agregamos un Slicer (asumo que saben cómo), lo cortan y lo pegan en la misma hoja de la TD y el gráfico dinámico. Para evitar, como comenté al inicio, estar eligiendo el valor top del slicer y actualizando la TD, agregamos una pequeña macro, pero todo esto lo podrán ver en el archivo que pueden descargar de aquí.

Ahora sí vamos a hacerlo con Power Pivot, que estoy seguro es lo que ustedes están esperando. Lo primero es que usaremos dos tablas similares a las del ejemplo anterior. Agreguen ambas al modelo de datos (igual asumo que saben cómo hacerlo).

Luego vamos a Power Pivot, a la tabla de datos que yo he llamado “TablaDatos” y vamos a agregar las siguientes funciones cuyos nombres serán similares a los del archivo anterior para que se entienda el porqué de su uso.

EligeRanking:=MAX('DatosRanking'[NivelTop])

SumaMonto:=SUM(TablaDatos[Monto]) 'Esto de aquí es equivalente a la de columna “Sumasi” del anterior ejemplo

En esas dos no hay mucho que explicar ya que estoy seguro todo/as saben lo que hacen MAX y SUM pues en DAX hacen lo mismo que en Excel. Vamos con las otras:

RankingVendedor:=RANKX(ALLSELECTED(TablaDatos[Vendedor]); [SumaMonto]; ; 0)

En esta función sí vamos a detenernos un poco. RANKX es una función DAX que nos devuelve la clasificación de una lista para cada fila de la tabla elegida, basado en la expresión que se elija (muy parecido a JERARQUIA). En este caso la tabla es TablaDatos con el detalle que usamos ALLSELECTED para nos devuelva todas las filas de la tabla omitiendo los filtros que se apliquen y como ven para este caso usaremos el campo Vendedor. Para el argumento de elección del ranking, usaremos la función que hemos creado previamente “SumaMonto”.

La siguiente es:

FiltrarVendedor:=IF([RankingVendedor] <= [EligeRanking];1;0)

Creo que en esa tampoco hay mucho que detallar.

Ahora que ya tenemos las funciones, crearemos, desde Power Pivot, una TD. Ojo, si en la hoja de Excel en donde han creado la TD les sale un aviso así, ni caso.

Luego activen los siguientes campos:

Luego en el campo de Vendedor, usaremos el mismo filtro del anterior ejemplo.

Quiten el campo FiltrarVendedor para que no salga en el gráfico dinámico que vamos a insertar. Sugiero también ordenar de forma descendente el campo RankingVendedor de la TD y recién ahí insertar el gráfico. Luego insertamos un Slicer del Top, obivamente con Segmentación de Datos. Deberíamos tener algo así y ya sin necesidad de macros al elegir cualquier ítem del Slicer, automáticamente cambian tanto la TD como el gráfico.

Si ven el detalle del gráfico también se ven los valores de RankingVendedor, pero hay algunos trucos para ocultarlos, pero eso ya queda como tarea para cada uno.

Espero les haya gustado y, sobre todo, espero que les sea útil. Hasta la próxima.

Abraham Valencia
Lima, Perú

Descargue el ejemplo aquí

lunes, 28 de marzo de 2022

Tablas con barras de desplazamiento

Hoy vamos a ver un truco que, desde mi punto de vista y basándome en algunas preguntas recurrentes que he visto en varios foros, podría ser útil a muchas personas ¿Cuál es? Pues pasemos a comentarlo.

Muchas personas tienen datos con varias filas y columnas y a veces consideran que es molesto ir desplazándose por toda la hoja y si bien hay algunas alternativas que ayudan a visualizar encabezados y/o datos que algunos/as requieren, estos no terminan de convencer a los/as usuarios/as y, además, no necesariamente saben y/o quieren usar macros (VBA) para intentar otras alternativas como por ejemplo el manejo de datos a través de formularios (Userform de VBA). Hablando de alternativas, he visto algunas que plantean colocar barras de desplazamiento verticales en “réplicas” de las tablas, lo que es muy útil para ir viendo de un modo rápido todas las filas de datos, pero no termina de ayudar si se tienen muchas columnas. Entonces, en esta oportunidad vamos también a implementar una barra de desplazamiento horizontal justamente para poder ver todas las columnas de ser estas muchas. En mi caso he creado una base de datos con 30 columnas (campos) y 100 filas (registros); las columnas tienen encabezados y he convertido todo el rango en Tabla. Es evidente que siendo tantas columnas pues no todas se ven en la pantalla. En mi caso he comenzado la tabla a partir de la fila 15 para que de ese modo usemos las primeras filas libres para el ejercicio que haremos hoy.

Ahora vamos a decidir cuántas filas y columnas queremos ver en eso que he llamado “réplica” de la tabla. En mi caso colocaré todo a partir de la columna C y la fila 1 y tendrá, a su vez, 10 filas y 6 columnas en total. Para comenzar vamos a insertar barras de desplazamiento, una horizontal y una vertical. Ojo, usaremos las de formulario, no las de los objetos ActiveX.

He colocado fondo rojo a aquellas celdas en donde replicaremos los encabezados y miren al lado izquierdo, de manera vertical, una de las barras de desplazamiento y abajo, de forma horizontal, la otra barra de desplazamiento.

Ahora, elegimos la barra vertical, le damo clic derecho y en el menú contextual elegimos “Formato de control…”.

En la caja de diálogo que se mostrará, en la pestaña “Propiedades” activemos “No mover, ni cambiar tamaño con las celdas”..

Ahora en la pestaña “Control” coloquemos en “Valor mínimo” un uno (1) y en “Valor máximo”, para mi caso, colocaré 91, en “Incremento” un uno (1) también y en “Cambio de página” pueden colocar un, por ejemplo, cinco (5). Por último, la parte más, importante diría yo, en “Vincular con la celda” elijan la de su predilección, y que esté fuera del área de la réplica y de la Tabla. Yo elegí la celda J2.

Repetimos lo mismo para la barra horizontal, pero evidentemente con otros valores.

Podríamos, si deseamos, tener las celdas vinculadas y/o sus datos ocultos, pero en mi caso voy a dejarlos a la vista, como verán cuando descarguen el ejemplo, para que se entienda mejor.

Ahora vamos a ver cómo obtener los encabezados. No olvidar que mi tabla se llama Tabla1 y que la barra de desplazamiento vertical, para las filas, está vinculada a la celda J2 y la barra de desplazamiento horizontal, para las columnas, está vinculada a la celda K2. Podríamos ocultar los valores o colocar en un lugar no visible, pero para el ejemplo dejaremos todo a la vista.

Para empezar, les comento que vamos a usar la función INDICE; asumo que la conocen y saben cómo se usa (Enlace). Entonces, en la celda C1, para mi caso vamos a colocar la siguiente fórmula:

=INDICE(Tabla1[#Encabezados];1;$K$2)

Insisto en que no voy a explicar cómo trabaja dicha función, pero sí aclararé que usar como matriz la Tabla1, nos permite por ejemplo colocar la referencia a la tabla y sus encabezados de modo fácil. El 1 es evidentemente relacionado a la fila de la matriz de los encabezados (son una sola fila, obviamente) y como recuerdan, la barra de columnas está relacionada a la celda K2, por lo que también la colocamos en esta fórmula. Si todo salió bien, tendremos esto en la celda respectiva:

Es decir, igual al primer encabezado de la Tabla1:

Luego, vamos a repetir lo mismo para todos los encabezados, pero sumándole algunos valores al tercer argumento de la fórmula, como verán a continuación:

Así, hasta el ultimo encabezado que sumará cinco (5). De ese modo tenemos todos los encabezados y el siguiente resultado cuando movemos la barra de desplazamiento horizontal.


Ahora sí la parte más interesante, llenar de datos el área en la que vamos a trabajar. Vamos a la primera celda, en mi caso la celda C2, y colocaremos la siguiente fórmula:

=INDICE(Tabla1;$J$2;K2)

En esta ocasión, a diferencia de la anterior, sí hacemos referencia a toda la Tabla1, no solo a los encabezados, luego referencia a J2, que es en donde nuestra barra vertical nos mostrará el número de fila que queremos de la matriz y por último a K2, que será el número de columna de esa misma matriz (Tabla1). Lo siguiente es colocar las fórmulas en las celdas de las filas que están debajo, pero, y ahí viene el “truco”, vamos sumándole uno (1) más a la fórmula en donde se hace referencia a la fila, justamente. Al final deberíamos tener esto:

Luego aplicaremos algo parecido a las columnas, pero sumaremos uno (1) a la referencia a las columnas:

Por último, llenamos todo con las fórmulas respectivas y tendríamos algo así:

Si queremos verlo en acción, pues así se verá:

Espero les haya gustado y, sobre todo, espero que les sea útil. Hasta la próxima.

Abraham Valencia
Lima, Perú

Descargue el ejemplo aquí