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.
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.
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.
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.
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.
Uno de los usos más habituales de Microsoft Excel es cruzar listados para saber si los datos de un listado existen en otro.
El enfoque más habitual es usar la función BUSCARV indicando que se desea devolver la columna 1, de esta forma si el dato existe en la otra lista, se devuelve el propio dato, y en caso contrario un error #N/A.
Si tenemos el listado en que buscar en la columna A y el de los datos a comprobar en la columna C la fórmula sería algo así.
=BUSCARV(C1;A:A;1;FALSO)
En este ejemplo asumimos que la lista en la que vamos a buscar los datos no están ordenados.
El último parámetro de la función BUSCARV se denomina ordenado, y realmente sirve para indicar el tipo de búsqueda a realizar.
Indicando que este último parámetro es FALSO le decimos a Excel que no podemos asegurar que la lista en la que buscamos está ordenada, pero también le indicamos que queremos realizar una búsqueda exacta. De forma que si el dato no se encuentra se devuelve un error #N/A.
Para realizar esto Microsoft Excel necesita buscar por toda la lista hasta encontrar el dato o llegar al final de la lista. Esto se denomina búsqueda secuencial.
Esto hace que este tipo de búsquedas sean muy lentas.
Imaginemos dos listados de 20 datos, si ambos listados contienen los mismos datos habrá una búsqueda que encuentre un resultado en cada posición, uno en la primera, otro en la segunda, otro en la tercera posición y así sucesivamente.
Eso implica que de media por cada dato se hacen 10 búsquedas, lo que representa un total de 200 búsquedas.
Si en el listado de datos a buscar hubiese datos que no apareciesen en el listado en el que se busca eso implicaría tantas búsquedas adicionales como datos en el listado multiplicado por el tamaño del listado en el que buscamos.
Si en el listado anterior hubiese cinco datos más, que no apareciesen en el listado de búsqueda, implicaría 100 búsquedas adicionales.
Esta es la razón por la que BUSCARV se puede volver tan lento al aumentar el tamaño de los listados.
En este vídeo muestro como un cruce de dos listados de 10.000 registros mediante una búsqueda secuencial con BUSCARV tarda 9 segundos.
Como alternativa se puede usar COINCIDIR que es un poco más rápido.
COINCIDIR devuelve la posición del dato buscado en la lista.
En el tercer parámetro le indicamos si queremos que devuelva el valor exacto, o si en caso de no existir devuelve el valor menor más cercano o el mayor más cercano.
Si deseamos saber si el valor existe, seleccionaremos el valor exacto, se representa con un cero.
=COINCIDIR(C1;A:A;0)
Con esta configuración si aparece el dato, devuelve un número que representa la fila en que se encuentra, si no aparece el dato devuelve un error #N/A.
Al devolver solo la posición del dato y no el propio dato COINCIDIR es un poco más rápido que BUSCARV.
En este vídeo muestro como un cruce de dos listados de 10.000 registros mediante una búsqueda secuencial con COINCIDIR tarda 6 segundos.
Como alternativa se puede realizar una búsqueda binaria.
Este tipo de búsquedas, exige que el listado en el que se va a buscar esté ordenado, y se puede aplicar cuando es posible ordenar el listado en el que se va a buscar.
y comienza por el centro del listado para descartar la mitad.
Supongamos que tenemos un listado de los 20 primeros números del 1 al 20 ordenados de forma ascendente.
Buscamos el número 20.
En el primer paso de la búsqueda se compara con el valor central de la lista, el número 10.
La primera comparación, será entre el 20 y el 10, sabemos que todos los valores de la primera parte del listado serán menores que el 10, por lo que podemos descartarlos.
La segunda búsqueda será en la mitad superior del listado.
La segunda comparación es con el 15, la siguiente con el 17, y la siguiente con el 19 y finalmente con el 20.
Esto implica que hemos localizado el último dato de la lista en solo cinco pasos, en lugar de los veinte necesarios en una búsqueda secuencial.
Podemos hacer una búsqueda binaria usando BUSCARV indicando VERDADERO en el último parámetro.
Existe un truco para simular la búsqueda exacta usando BUSCARV y la función SI.
El truco consiste en realizar una búsqueda secuencial devolviendo el valor encontrado en la primera columna y compararlo con el valor a buscar.
Si el valor devuelto es igual al buscado es que el dato existe, en caso contrario indicaremos que el dato no existe en el listado.
De esta forma reduciremos drásticamente las comparaciones necesarias para hacer el cruce de datos acelerando las búsquedas considerablemente.
En este vídeo muestro como un cruce de dos listados de 10.000 registros mediante una búsqueda binaria con BUSCARV es casi inmediata, en contra de los 9 segundos que llevaba la búsqueda secuencial.
