Borrar todos lo objetos de una hoja de cálculo de Microsoft Excel

En muchas ocasiones al pegar datos de una web en Microsoft Excel, se arrastran componentes de la web que no son deseados, como pueden ser botones, cuadros desplegables o imágenes.

Existe un método poco conocido para seleccionar todos lo objetos de una hoja de cálculo y eliminarlos en un único paso.

En la cinta de opciones, Modificar, Buscar y seleccionar, Ir a..., se puede elegir la opción "Objetos".

También se puede acceder directamente a esta ventana mediante F5.

Al elegir esa opción se seleccionan todos los objetos de la hoja activa, luego solo hace falta pulsar la tecla suprimir y se consigue eliminar en un solo paso todos los objetos incrustados desde la web.

A continuación en el vídeo https://youtu.be/LCURRvIKGNE podéis ver la secuencia completa, en este caso se accede a la ventana directamente con la tecla F5.

Para los que no podéis ver el vídeo correctamente os subo una animación.

Espero que con este método os resulte un poco más sencillo limpiar esos ficheros con restos no deseados de páginas web.

Crear carpeta con el nombre del día mediante archivo por lotes.

Una de las herramientas que siempre están presentes en los sistemas Windows es la consola del sistema, también conocida como MSDOS.

Aunque en mi día a día suelo usar VBA o Python, también recurro a la anticuada consola de comandos.

Lo comandos de este sistema se pueden escribir en un fichero de texto con extensión ".bat" lo cual nos permitirá ejecutarlos con solo hacer doble click en el fichero.

Os muestro como ejemplo una de las cosas que suelo usar para ahorrar unos segundos cada día, mejorando un poquito mi productividad.

Habitualmente creo una carpeta con el nombre de la fecha del día.

Si el día es hoy 24/02/2021, creo una carpeta denominada 20210224datos

Para ello uso un fichero bat, con el siguiente contenido:

@echo off

MKDIR %date:~6,4%%date:~3,2%%date:~0,2%" diario"

La primera instrucción @echo off evita que se muestre el eco en pantalla, esto significa que no se verá las instrucciones que se ejecutan.

En la siguiente línea MKDIR crea una carpeta con el nombre que se le indica.

El nombre de la carpeta se genera uniendo dos cosas, unas partes de la fecha del sistema %date:~6,4%%date:~3,2%%date:~0,2% y un texto fijo " diario".

La parte de la fecha es el mismo esquema repetido tres veces:

%date:~6,4%

Esto significa extraer de la fecha actual desde el sexto carácter los cuatro caracteres siguientes.

Es importante notar que en la fecha también se cuentan las barras como caracteres, de forma que el primer carácter del mes es el cuarto dentro del texto, ya que los dos primeros son el día, y el tercero es la barra.

Con la fecha de hoy sería 24/02/2021

Las dos siguientes repeticiones de date extraen el 02 y el 24.

Generando el nombre de la carpeta 20210214 diario

Os dejo un vídeo con el proceso completo https://youtu.be/u9g8ikup_js

Con esto demuestro lo importante que es controlar las herramientas que nuestro sistema trae por defecto, ya que nos pueden ayudar a salir de más de un problema, y este es un ejemplo de ellas.

Os dejo la versión del vídeo en animación por si no lo podéis ver correctamente.

Como recuperar las macros del libro Personal.xlsb corrupto

Lo peor que nos puede pasar con Microsoft Excel, es que el fichero de macros personal.xlsb se corrompa, y no nos permita acceder a nuestras macros (y no tener copia de seguridad de ellas claro!)

Pero con Libreoffice Calc podremos abrirlo y guardar una copia de las macros.

El proceso os lo muestro en el vídeo (https://youtu.be/fhupO_mi6rQ).

El proceso es el siguiente:

Primero, localizar el fichero PERSONAL.XLSB.

En Windows 10 está en la carpeta XLSTART del usuario.

Esta carpeta y el fichero que contiene se crea cuando se guarda la primera macro.

C:\Users\UserName\AppData\Roaming\Microsoft\excel\XLSTART

Recomiendo hacer una copia del fichero PERSONAL.XLS en otra carpeta, ya que si no es posible acceder a él desde Microsoft Excel, procederemos a su borrado, para que al crear una nueva macro, se regenere automáticamente.

Luego pegaremos las macros que vamos a recuperar.

Con el botón derecho del ratón elegimos "Abrir con" y usamos Libreoffice Calc.

En mi caso me avisa de que el documento contiene macros y de como permitirlas en Libreoffice.

Procedo a bajar el nivel de seguridad de las macros.

El siguiente paso es abrir las macros desde Libreoffice Calc.

Herramientas, Macros, Editar Macros.

Buscamos PERSONAL.XLSB y dentro de él VBAProjet, Módulos.

Seleccionamos cada módulo, y podemos copiar y pegar, o desde el menú archivo exportar el módulo en formato .bas.

Para poder volver a acceder a las macros desde Microsoft Excel, borraremos el libro PERSONAL.XLSB, y cerraremos el programa.

Lo volvemos a abrir y creamos una nueva macro en el libro de macros personal.

Esto hace que el fichero PERSONAL.XLSB se regenere nuevamente.

Ahora solo habrá que pegar o importar las macros que acabamos de recuperar con Libreoffice Calc.

Os dejo una animación del proceso por si no podéis ver el vídeo correctamente.

Convertir fecha de formato AAAAMMDD a DDDMMAAAA de forma rápida y fácil

El enfoque típico pasa convertir una fecha en formato AAAAMMDD a formato DDMMAAAA suele pasar por extraer el día, el mes y el año y luego concatenarlas.

Eso implica el uso de cuatro fórmulas.

Sin embargo hay una forma mucho más rápida de hacerlo usando la opción "Texto en columnas", con solo seis clics.

Cómo habéis visto, en un primer momento Microsoft Excel no es capaz de asignar el formato de fecha larga a la columna con las fechas, porque no las reconoce como fecha.

Este tipo de formato de fecha es muy típico encontrarlo en las exportaciones desde bases de datos.

El proceso para solucionarlo es tan sencillo como, tras seleccionar la columna en la que están las fechas en formato AAAAMMDD, y seleccionar la opción "Texto a columnas".

En esta ocasión, a diferencia del método para "convertir texto a números", necesitaremos realizar un paso más, seleccionando el tipo de datos. Elegiremos tipo fecha y en el campo desplegable seleccionaremos el formato en el que se muestra. Puede ser AAAAMMDD o cualquier otro de los mostrados.

Aún se puede reducir más el número de clics necesarios para hacer esta transformación, automatizando estos pasos mediante una macro VBA.

Para los que no podáis ver correctamente el video subo una animación del proceso.

Coincidir + Indice como alternativa a buscarv

Una alternativa a la función BUSCARV en una hoja de cálculo como Microsoft Excel, Libreoffice Calc o Google Spreadsheets, puede ser la combinación de las formulas COINCIDIR e INDICE.

En muchas webs veréis como realizar una función anidada con ambas, de esta manera:

=INDICE(B:B;COINCIDIR(DE;A:A;0))

Sin embargo os las muestro por separado, ya que me parece mucho más fácil de entender para quien nunca las haya usado.

En un primer paso, localizo con COINCIDIR la fila original del dato buscado.

En el segundo paso, recupero el dato de la fila indicada en la casilla de al lado.

COINCIDIR  realiza una búsqueda secuencial, devolviendo la fila en la que aparece el resultado o un error si no lo encuentra.

Para ello el último parámetro debe ser cero. Configurada de esta forma busca los datos como BUSCARV con su último parámetro configurado como FALSE.

INDICE devuelve el dato que se encuentra en la fila y columna indicada dentro de un rango también indicado.

Si el rango solo tiene una columna, no es preciso especificarla.

Os dejo un videotutorial explicativo.

Como podéis ver en el vídeo, en un ejemplo para buscar 10 000 datos en una lista desordenada de otros 10 0000 la función COINCIDIR tarda en ejecutarse unos 7 segundos, mientras que INDICE es casi inmediata.

Para los que no podáis ver el vídeo, os cuelgo una animación.

La próxima semana os mostraré un ejemplo de cómo aprovechar mejor está estructura de dos funciones en Microsoft Excel.

Convertir texto a números en dos clics.

Se pueden convertir números a texto de varias formas.

Las más típicas son:

1. Usando la fórmula valor.

2. Arrastrando el icono de error.

3. Programando un script en VBA.

Pero la más rápida 🚀 es sin duda:

Usando la opción "texto en columnas".

Solo se necesitan dos clics para convertir una columna entera de textos a números.

El truco es seleccionar una columna y decirle a la hoja de cálculo Microsoft Excel que queremos que convierta el texto en columnas.

Sin embargo por defecto si indicamos un separador de columnas que no existe el resultado sigue siendo una única columna, pero durante el proceso convierte automáticamente los datos almacenados como texto a formato numérico.

Cruzar listados con Power Query

Power Query es un complemento de Microsoft Excel que inicialmente fué creado para importar datos, pero ha crecido hasta ofrecer algo parecido a una base de datos dentro del propio Excel, que incluso cuenta con su propio lenguaje de consultas.

En este caso muestro usando un Microsoft Excel 2016 una de las tareas más sencillas que se pueden hacer con Power Query, como es el cruce de dos listados.

Os dejo el paso a paso completo en mi canal de Youtube en el vídeo https://youtu.be/jSgcCNf9xRE

El primer paso, es convertir ambos listados en tablas, ya sea desde al menú correspondiente o con la combinación de teclas CTRL + T (esta combinación selecciona por defecto todo el rango continuo de celdas)

Luego, dependiendo de la versión de Excel, desde la pestaña Datos o desde la pestaña PowerQuery, seleccionamos la opción "desde tabla".

Esto abre la ventana de power query.

Solo tenemos que guardar e indicar crear solo conexión.

Repetimos este paso con la segunda tabla.

Nuevamente entramos a Power Query, indicando que deseamos combinar consultas.

Seleccionamos las dos tablas y el único campo de cada una de ellas y cerramos.

Luego solo queda indicar a Power query donde queremos que se muestre el resultado.

Este método es incluso más rápido que el tercero que mostraba la próxima semana usando una búsqueda binaria para resultados exactos mezclando BUSCARV y SI. 

Como ventaja sobre los métodos que mostré la semana pasada, recordar que no necesita que las tablas estén ordenadas.

Os dejo el video del paso a paso:

Insertar fecha y hora en Microsoft Excel y Libreoffice Calc mediante combinaciones de Teclas

Un dato que suele introducirse a menudo en una hoja de cálculo, es la fecha del día actual.

Muchas herramientas permiten escribirla con atajos de teclado y Microsoft Excel no es una excepción.

Esta combinación de teclas nos ahorrará unos segundos cada vez que tengamos que introducirla, y de paso evitará errores tipográficos.

Pulsando CTRL +  (coma), Microsoft Excel inserta la fecha del día actual en la celda activa.

También es posible escribir la hora actual, para ello pulsaremos CTRL + MAY + (punto)

Y uniendo los dos es posible escribir la fecha y hora actuales, también conocido como timestamp.

Para ello hay que pulsar la combinación de teclas para la fecha, un espacio, y luego la combinación de teclas para la hora.

CTRL + (coma)

Espacio

CTRL + MAY + (punto)

Nota: Está combinación de teclas para la hora también se puede usar en Libreoffice Calc.