Una de las tareas repetitivas que suelo tener que realizar en Microsoft Excel es la localización de valores repetidos en un listado.
Se puede realizar con fórmulas, pero existe un método más sencillo, rápido y visual.
Dentro de las posibilidades de la herramienta Formato condicional existe una opción para aplicar formato a los valores duplicados. En realidad aplica el formato a cualquier valor que aparezca dos o más veces en el listado.
En Inicio seleccionamos Formato Condicional, y ahí la primera ("Resaltar Reglas de Celdas") y luego la última opción ("Duplicar valores ...")
La utilidad de este truco aumenta si se combina con el filtro por color.
Una vez aplicado el filtro, si se modifica una fila duplicada el color desaparece.
Veamos cómo funcionan en conjunto.
A continuación os dejo la animación para aquello que no puedan ver el vídeo correctamente.
Ampliando el ejemplo de la semana pasada, os muestro un truco que permite que duplicando una consulta de Power Query hacer que distintas secciones de un fichero origen se importen a distintas hojas de un mismo libro de Microsoft Excel, burlando de esta manera la limitación de un millón de líneas por hoja de Excel.
En este caso disponemos de un único fichero de datos con tres millones de registros que deseamos importar completamente a Microsoft Excel.
Es bien sabido que Microsoft Excel tiene un límite de algo más de un millón de filas por hoja.
Aprovechando que podemos crear varias hojas y mezclándolo con el truco que vimos la semana pasada para "Tratar más de un millón de datos en Microsoft Excel", cargaremos tres fragmentos del mismo fichero en otras tantas hojas.
Y para se un poquito más productivos, crearemos una consulta que luego duplicaremos las veces necesarias.
De esta forma podremos indicar un destino distinto para los resultados de cada copia de la consulta.
Si el fichero se modifica, al abrir el Excel y actualizar la hoja, se volverán a importar los nuevos datos sin tener que repetir el proceso.
Sin embargo si nos limitamos a copiar ficheros en la carpeta del día, cuando tengamos que buscar uno concreto podrá ser un tanto incómodo.
Yo suelo aprovechar el mismo archivo .bat de proceso por lotes para copiar los ficheros que me interesan aprovechando la oportunidad para incluir en el nombre del fichero la fecha actual.
Este proceso se hace en cuatro pasos:
1. Se crea la carpeta con el nombre del día.
2. Se copian los ficheros con su nombre original.
3. Nuevo el directorio de trabajo a la carpeta recién creada.
4. Renombro los ficheros copiados.
Os muestro un ejemplo para un solo fichero.
Si el día es hoy 11/03/2021, creo una carpeta denominada 20210211datos, y a los nombres de los ficheros le añado la fecha en el mismo formato.
Por ejemplo si tuviese "facturas.docx" lo renombraría como "20200311facturas.docx".
Para ello uso un fichero bat, con el siguiente contenido:
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.
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.
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.
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.
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.
