miércoles, 30 de diciembre de 2020

Mensaje con VBA (VISUAL BASIC FOR APPLICATIONS)

Como iniciación a la programación de macros en VBA mostraré como hacer aparecer un mensaje en pantalla.

Para ello solo es necesario incluir la siguiente línea en el momento en que desee, usualmente al final del código para avisar de que se ha finalizado la ejecución:

MsgBox ("Macro finalizada")




El resultado es una ventana flotante (un Message Box) en la que se muestra un aviso y se espera a la pulsación de un botón por parte del usuario.



Mostrar Ficha Desarrollador

Al trabajar con macros en Microsoft Excel es muy útil el uso de la Ficha Desarrollador, la cual está por defecto oculta.

El proceso es el siguiente:



Grabar macro automáticamente

 Se pueden grabar macros desde la pestaña Vista:



Pero existe una pestaña oculta denominada "Desarrollador" que nos dará más opciones.

Desde cualquiera de las dos opciones, pulsar en "Grabar macro", realizar las acciones que deseamos que se repitan y pulsar en "Finalizar grabación"



Ir a última columna

Para ir a la última columna, el proceso es similar a ir a la última fila.

Basta con usar el método CTRL + Flecha Derecha




Ir a última fila con datos

La forma más rápida de ir a la última fila en Microsoft Excel, es usando el teclado.

Se trata de ir a una columna vacía, en esa columna bajar hasta la última fila del documento con la combinación de teclas CTRL + Flecha Abajo, volver a colocarse en la columna a la que queremos ir, y subir hasta encontrar la primera fila desde abajo con datos CTRL + Flecha Arriba.



Reducir tamaño hoja Excel cambiando formato

 Aún a día de hoy sigo recibiendo una gran cantidad de hojas de cálculo en formato Microsoft Excel 2003.

El antiguo formato *.xls

Este formato entre otras características, como es el tener un máximo de 65000 filas frente a más de un millón del formato más reciente, tiene que ocupa mucho más tamaño.


Por lo tanto, con solo "Guardar como..." y elegir el format xlsx e ahorrará una cantidad importante de espacio, sin perder información.

Otro formato de menor tamaño todavía es el formato xlsb, el cual aún reduce más el tamaño necesario.


Como se puede ver, el antiguo formato xls ocupa casi tres veces más que el nuevo xlsx, y más de cuatro veces más que el xlsb.

Convertir texto a número desde datos

 En el post anterior "Convertir texto a número arrastrando y seleccionando convertir" se explicaba como convertir pequeñas cantidades de celdas de texto a número.

En el caso de grandes cantidades, por ejemplo una columna entera de datos, el proceso más rápido es simular que vamos a separar los datos mediante un separador inexistente, desde la opción datos, texto en columnas.

La primera opción de separador "el tabulador" es la solución perfecta para esta tarea.




Convertir texto a número arrastrando y seleccionando convertir

 Si tenemos un listado de números que Excel ha tratado como textos (aparece un pequeño triángulo verde en la esquina de la celda) se pueden transformar en números de diversas formas.

Si la cantidad de celdas no es muy grande lo más rápido es seleccionarlas. Al hacer esto aparecerá un icono de error en pantalla, que si pulsamos despliega un menú en el que podremos seleccionar "Convertir en número".


Como se ve en la animación, al principio en la barra de estado solo se muestra la información de recuento, eso es porque no se detecta ningún número. Tras pulsar el menú "convertir en número" se activan las opciones que tienen que ver con los datos numéricos como Promedio y Suma.


NOTA:

Este método no es cómodo para grandes cantidades de celdas, porque se vuelve lento y es fácil realizar mal la selección o que no se muestre el icono de error.

Diferenciar números de alfanuméricos

Una forma sencilla de detectar si en una columna solo existen datos numéricos, o también se incluyen datos alfanuméricos, es recurrir a comprobar si los datos de la barra de estado, Recuento y Recuento numérico, ofrecen el mismo valor.


Si ambos datos son iguales, entonces todos los dato son numéricos, en caso contrario alguna celda incluye un texto o un error.

La opción "Recuento numérico" no aparece por defecto, para mostrarla se seguirán los pasos indicados en la entrada "Contar números desde la barra de estado"

Contar números desde la barra de estado

La forma más sencilla de contar los números en una selección de celdas de Microsoft Excel es activando en la barra de estado la opción "Recuento numérico".

Pulsando con el botón derecho en la barra de estado se accede al menú en el que se pueden seleccionar distintos cálculos a mostrar para las celdas seleccionadas.



Estos cálculos por defecto son Promedio, Recuento y Suma.

Recuento indica el número de celda no vacías entre las seleccionadas

Mientras que Recuento numérico indica las celdas con datos numéricos.

En la imagen vemos la diferencia:


lunes, 28 de diciembre de 2020

Nombrar Celdas

 En Microsoft Excel es posible dar nombre a una celda.

Ciertos autores indican que es mucho más sencillo localizar de esta forma las celdas.

El proceso es seleccionar la celda y pulsando en el desplegable en el que se indica el nombre de la celda actual escribir un nombre de celda. 

Deberemos usar nombres de celda descriptivos, ya que de no ser así, será más fácil localizar las celdas por su referencia original.

A partir de ese momento la celda puede ser referida por su nombre o por la referencia.


NOTA:
Aunque su uso en una fórmula no represente una gran ventaja, si que es muy cómodo de usar en scripts VBA, ya que estos no se dan cuenta del cambio de ubicación de una celda si se inserta, por ejemplo una columna.

Si en el caso del ejemplo tuviésemos un código que accediese a la celda B2 y a posteriori insertásemos una columna entra A y B, pasando esta celda a llamarse C2, el código VBA accedería a la nueva celda B2. Sin embargo si se hiciese referencia por el nombre "valorIva" el código seguiría funcionando correctamente, ya que este seguirá haciendo referencia a la misma celda.

Buscarv en un listado ordenado

Teniendo en cuenta que el último parámetro de la función Buscarv de Microsoft Excel es ORDENADO, os mostraré un ejemplo de su funcionamiento.

Se debe entender este último parámetro como una búsqueda por similitud numérica y no simplemente que implique una lista ordenada.

La búsqueda finalizará cuando se encuentre el primer valor mayor que el buscado, y se devolverá el anterior.

En este ejemplo se busca un empleado que cobre 1250 €, ese dato no existe en la tabla original, por lo tanto devuelve el dato inferior al buscado, en este caso Ramón.


Buscarv devolviendo datos de la segunda columna.

Quizás la fórmula más usada en Microsoft Excel sea Buscarv.

Os muestro la implementación más sencilla posible para entender su funcionamiento.

En este caso se busca un único valor "Ramón" y se devuelve el dato que se encuentra directamente a su derecha, su sueldo.

Como se puede ver por las celdas resaltadas en colores, el primer parámetro indica el valor a buscar, el cual dependiendo del último parámetro, tiene que estar en el listado o no.

En la implementación más sencilla, entenderemos que el dato buscado SI está en la lista, lo que se corresponde con indicar FALSO en el último parámetro [ORDENADO].

El segundo parámetro indica una selección de celdas que incluye tanto el dato a localizar como el dato a mostrar, los cuales no tienen porque ser consecutivos.

Es en el tercer parámetro donde le indicaremos en que número de columna de la selección indicada se encuentra el resultado. 


NOTA:

El último parámetro no es obligatorio, siendo su valor por defecto VERDADERO. Si no se modifica este valor, Microsoft Excel entenderá que deseamos encontrar un valor aproximado, produciendo resultados que pueden ser inesperados.




Formato porcentaje

Un error típico en las hojas de cálculo es no comprender cómo se tratan los valores en porcentaje.

Para una hoja de cálculo como Microsoft Excel el valor mostrado en porcentaje es un decimal.

En realidad no usa el tanto por ciento, sino si equivalente decimal.

Esto quiere decir que un valor representado como 25%, realmente lo trata como el valor 0,25.

Importante tenerlo en cuenta tanto al ingresar datos, como al realizar operaciones con el procentaje.



Arrastrar vs doble clic para rellenar fórmulas

Existen varias formas de rellenar una columna con fórmulas.

1. Atrástrando
2. Haciendo doble click
3. Copiar y pegar

Según el caso la forma más cómoda puede ser arrastrar o hacer doble click.

Para columnas con muchos datos el doble click es mucho más cómodo.

Pero esta forma de rellenar columnas presenta un inconveniente.

Al hacer doble click, dejamos en manos de la aplicación decidir hasta que fila rellenar la fórmula.

Esto no es un problema si en la columna adyacente no hay huecos, pero en caso de que los haya, el relleno finaliza a las altura del primer hueco.

Esto puede provocar que tengamos filas sin fórmula, y Microsoft Excel asume por defecto el valor de cero para las celdas vacías, lo que puede generar errores en los resultados.



Copiar el color exacto de una celda con el código HSL

En la hola de calculo Microsoft Excel es posible consultar el color exacto de una celda.

Una de las formas es comprobando sus componentes HSL del inglés Hue, Saturation, Lightness – (Matiz, Saturación, Luminosidad).

El proceso es muy similar al usado para comprobar los componentes RGB (Red, Green, Blue).

Para ello accederemos a más colores en la paleta de colores de Excel, y seleccionamos la opción "más colores".



Luego seleccionamos la pestaña "Personalizado" que es donde se muestran los valores de cada componente del color.

Existen dos modos de color a seleccionar, la opción por defecto es "RGB".

Seleccionaremos la opción HSL en el control desplegable "Modelo de color".





De esta forma se pueden ver  y modificar los componentes Matiz, Saturación y Luminosidad de cada color.

Copiar el color exacto de una celda con el código RGB

En la hoja de calculo Microsoft Excel es posible consultar el color exacto de una celda.

Una de las formas es comprobando sus componentes RGB (Red, Green, Blue).

Para ello accederemos a más colores en la paleta de colores de Excel, y seleccionamos la opción "más colores".



Luego seleccionamos la pestaña "Personalizado" que es donde se muestran los valores de cada componente del color.

Existen dos modos de color a seleccionar, la opción por defecto es "RGB":


De esta forma se pueden ver  y modificar los componentes Rojo, Verde y Azul de cada color.

domingo, 27 de diciembre de 2020

Filtrar usando columna auxiliar

En ciertos casos aplicar todos los casos de un filtro puede ser complejo, en ese caso es útil usar una columna auxiliar que se irá rellenando con algún valor indicativo y que posteriormente será por la que se filtre.

Imaginemos que deseamos filtrar los datos de facturación de los clientes de los siguientes códigos postales:

36125
12458
12563
25666
12356
33241

La solución más sencilla podría pasar por tener una columna auxiliar en la que se indique un valor para los campos filtrados y otro para los que no se desee filtrar.

Otra posibilidad es que alguien esté punteando un listado contra otro documento y lo marque en una columna auxiliar.

Yo suelo usar una columna titulada Filtrar o Revisar, y poner como datos si o no.



Nota: Nunca es buena idea asumir que una celda vacía es un dato correcto. Por ejemplo asumir que no queremos filtrar las filas con la casilla vacía, ya que es fácil que una casilla no se haya cubierto, y aún esté pendiente decidir si se debe filtrar o no.

domingo, 13 de diciembre de 2020

Copiar formato de una celda a varias celdas o regiones

 La opción de copiar formato, tiene una forma de uso que mucha gente desconoce.

Es posible copiar el formato de una celda y pegarla en varias celdas consecutivamente, sin que estas tengan que ser consecutivas ni tener que volver a pulsar el botón de copiar formato.

El proceso es el mismo, pero en lugar de pulsar una vez el botón de copiar formato, es preciso hacer doble click en el mencionado botón.

Esto hace que hasta que se vuelva a pulsar el botón de copiar formato, se pegarán los formatos en todas las celdas o regiones que se seleccionen.



Copiar formato de una celda a varias celdas consecutivas (región, fila o columna)

 En el post "Copiar formato de una celda a otra" se explicaba como copiar en un único paso todos los formatos de una celda a otra.

También es posible aplicar a más estos formatos en un único paso a más de una celda, con la condición de que todas las celdas sean consecutivas, como puede ser una fila o una columna completas, o una selección de celdas.





Copiar formato de una celda a otra

Una de las herramientas que suele incluir cualquier herramienta ofimática, y más concretamente Microsoft Excel, e la opción de copiar formato.

En su forma de funcionamiento más sencilla, permite pulsando el botón copiar formato, aplicar todos los formatos que tenga la celda actual a la celda destino.

Esto permite aplicar en un único paso, tipos de letra, alineaciones, tamaño de fuente, colores de fondo y de primer plano, etc. 

Es una herramienta que puede ahorrar muchos pasos improductivos a la hora de formatear un documento.

El proceso es el siguiente.





sábado, 5 de diciembre de 2020

Copiar datos de una columna filtrada a otra, en las mismas filas, en una hoja de cálculo, aplicando lógica inversa.

En el post "Copiar datos de una columna filtrada a otra, en las mismas filas, en una hoja de cálculo, usando referencias a celdas." mostraba como pegar datos filtrados en una nueva columna conservando las filas originales.

En este artículo muestro otra opción para obtener el mismo resultado.

En este caso aplicaré la lógica inversa del filtro, para eliminar lo datos no deseados.

En el ejemplo había dos columnas con los mismos datos, en la primera se mostraban los datos que contenían el valor 608 y en la segunda los que contenían el 12, obteniendo de esta forma los datos que contenían ambas secuencias de números en cualquier orden.

Este tipo de filtros solo se puede aplicar sobre datos de tipo texto, no obre datos numéricos, como es este caso. 

Para solventar este inconveniente, se transforma el número en su equivalente en texto mediante la fórmula =TEXTO(A2;"000000000").

Una vez obtenida una copia de los datos originales en la columna de destino, se crea una nueva columna con la fórmula TEXTO, sobre la que se aplican dos filtros consecutivamente.

Primero se seleccionan las filas que no contienen 608, algunas de las cuales contendrán 12 pero al no contener ambos, no nos interesan, por lo que procedemos a eliminar las celdas visibles de la columna de destino F.

En este punto todos los datos de la columna F contienen el 608, y algunos de ellos el 12.

Se vuelve a aplicar el filtro, en este caso mostrando los datos que "no conienen" en valor 12.

Nuevamente se procede a eliminar todos los datos de las celdas visibles de la columna F. Quedando de esta forma en la columna F, de los dato que contenían el 608 solo los que también contienen el 12.



Copiar datos de una columna filtrada a otra, en las mismas filas, en una hoja de cálculo, usando referencias a celdas.

 En Ocasiones puede ser interesante pegar el resultado de un filtro en una columna diferente, pero manteniendo las mismas filas que los datos originales.

Lo primero que se nos ocurre es copiar los datos filtrados, seleccionar la columna destino y pegarlos.

En este caso Microsoft Excel lo que hará es pegar los resultados de forma contínua, sin mantener las filas originales.


Para mantener los datos pegados en las mismas filas, podemos adoptar tres enfoques diferentes.

1 - Aplicar la "lógica inversa". Esto implica pegar todos los datos y eliminar los que no cumplan en criterio del filtro. Este es un método cómodo, pero en ocasiones puede ser complejo entender cual es el criterio a aplicar para eliminar los datos sobrantes.

2 - Pegar los valore mediante código VBA.

3 - Usar una referencia a la primera celda filtrada y arrastrar o copiar y pegar la fórmula. En este caso la fórmula solo se pega en las celda visibles, generando el efecto deseado.

En la siguiente animación se ve la secuencia completa.

Al acabar, para evitar las fórmulas se copian los datos de la nueva columna y se pegan como valores, de esta forma se eliminan las fórmulas, dejando solo el resultado.



Truco para aplicar dos filtros, simultáneamente, sobre los mismos datos en Microsoft Excel

Si bien es posible aplicar varios filtros de texto a una columna en una hoja de cálculo como Microsoft Excel o Libreoffice Calc, no siempre resuelve nuestro problema.

En este ejemplo se dispone de un listado de teléfonos móviles entre los que está el que buscamos.

Dos personas han escuchado el número pero no estaban atentas, por lo que solo recuerdan alguno números, aunque no en el orden en el que están.

Uno de los usuarios recuerda que el número de teléfono contenía la secuencia 608 y el otro recuerda que contenía 12, pero ninguno de los dos tiene claro que secuencia iba antes de la otra.

Si se supiese el orden, se podría solucionar con un filtro del tipo 608*12, pero en este caso no es posible.

El truco consiste en duplicar la columna de datos, y aplicar cada filtro a una columna. De esta forma, la hoja de cálculo, aplicará una o lógica a ambos filtros. Este mismo resultado se podría obtener utilizando filtros avanzados.

En la imagen, muestro la secuencia completa.



Localizar casillas vacías mediante fórmulas en Microsoft Excel

Muestro una mejora sobre el "método de buscar celdas vacías con combinaciones de teclas", el cual no detecta celdas que solo contengan espacios

En este caso es preciso utilizar un par de fórmulas y luego filtrar el resultado.

En la imagen se muestra en la columna A un listado de números en los que existen celdas con casillas que contienen espacios (en este caso no se trata de celdas vacías)


Paso 1 - En la columna B, limpiar caracteres no imprimibles

=espacios(A1)


Paso 2 - En la columna C, contar caracteres 

=largo(B1)





Paso 3 - En la columna C, Filtrar por largo = 0


Las casillas filtradas son las que o están vacías o solo contienen espacios.



Localizar casillas vacías mediante combinaciones de teclas en Microsoft Excel

En ocasiones un listado de una hoja de cálculo incluye huecos sin datos, que pueden hacer que los resultados no sean los deseados.

Una de las formas de localizarlos sería usar alguna fórmula, por ejemplo LARGO, y luego filtrar el resultado.

Otra forma, quizás la peor, sería ir moviéndose por la pantalla hasta encontrar una casilla vacía.

Un método alternativo es usar una combinación de teclas, que nos moverá hasta la última casilla con datos antes de una vacía, y luego hasta la primera casilla con datos.

La combinación es tecla control + flecha.

Dependiendo de la flecha utilizada nos moveremos en una dirección distinta.

En el ejemplo muestro como buscar huecos en un listado vertical, comenzando por la primera fila y bajando hasta la última, utilizando la combinación de teclas CTRL + flecha abajo.

Nota:

Este método no permite localizar casillas con espacios. Las cuales, aunque visualmente parecen vacías, si que contienen un texto, aunque sea del tipo de caracteres no imprimibles (espacios, tabuladores, cambios de línea, etc.).

Herramienta de captura de pantalla a formato gif animado

Hoy presento una sencilla herramienta que permite realizar capturas de pantalla en formato gif animado.

Screen to Gif es una herramienta muy sencilla que permite fácilmente capturar secuencias en pantalla y grabarlas en formato gif.

Esto la hace ideal para grabar minimanuales y colgarlos en una página web o enviarlos por email como parte del contenido, de forma que con solo abrir el email, se comienza a reproducir la imagen.

Es Open Source y se puede descargar desde la web https://www.screentogif.com/

Una vez instalada y ejecutada permite generar videos o animaciones gif, a partir de grabaciones en pantalla, de la cámara web o desde una pizzarra integrada en la que se puede dibujar.



Si se elige la opción grabar, se muetra un recuadro vacío que marca la zona que se grabará.

Para iniciar a grabar, es posible pulsar los botones o usar una combinación de teclas.

En el caso de decantarse por la combinación de teclas esta se puede configurar al gusto.

Por ejemplo, para realizar grabaciones sobre Microsoft Excel, lo ideal será elegir combinaciones que no se usen en él. 

En el post "Combinaciones de teclas con la tecla Control no usadas en Microsoft Excel" muestro algunas combinaciones útiles para ese caso.






Moverse a la última celda en Microsoft Excel mediante combinación de teclas

 Una de la cosas que ahorra mucho tiempo en el uso de herramientas informáticas es el dominio de las combinaciones de teclas más usadas.

Una de las cosas más frustrantes en una hoja de cálculo es desplazarse con la rueda del ratón, una y otra vez hasta llegar al final de una columna con datos.

Existe una combinación de teclas que permite realizar esta tarea en un único paso ahorrando mucha tiempo.

Pulsando simultáneamente CTRL + flecha de dirección, si estamos en una celda con conteniro, se avanza hasta la primera celda vacía, y viceversa.

Esto permite moverse fácilmente hasta la última fila con contenido de un listado.

Esto es especialmente útil en el caso de listados muy largos.



Combinaciones de teclas con la tecla Control no usadas en Microsoft Excel

Usualmente nos interesamos por las combinaciones de teclas que se usan en un programa, pero en ocasiones es necesario saber cuales no e usan. 

En este caso concreto se trata de inciar y parar una captura de pantalla en otro programa, sin interactuar con Microsoft Excel, de forma que en el vídeo de la captura no se muestren acciones no deseadas.

Tras comprobar todas las combinaciones posibles con la tecla Control y una letra en Microsoft Excel 2016, he encontrado cinco combinaciones no usadas.

Estos son los atajos no usados en Microsoft Excel 2016:

CTRL+F

CTRL+H

CTRL+M

CTRL+Ñ

CTRL+O