En ocasiones es interesante poder localizar una fórmula en una hoja de cálculo, ya sea porque se nos ha perdido en el medio del documento o para localizar una columna con fórmulas.
Una de las fórmulas más sencillas de encontrar las fórmulas es usar la opción "Ir a..." de la sección "Buscar y Seleccionar" que también se puede acceder directamente, con la tecla F5.
Pulsando el botón "Especial" se abre una nueva ventana en la que podemos seleccionar "Celdas con fórmulas" y pulsar el botón "Aceptar"
Hecho esto se seleccionan todas las celdas con fórmulas de la hoja de cálculo.
Un dato curioso del formato XSLX, es que, en realidad, no es un simple archivo, en realidad es un contenedor de otros ficheros.
Podemos ver que ficheros forman parte de él, modificando la extensión por .ZIP y descomprimiendo el fichero resultante.
De esta fome obtendremos una carpeta con los archivos que forman el nuevo formato de Excel, los cuales son en su mayoría ficheros XML que pueden ser abiertos y modificados con un simple bloc de notas.
Esto puede permitir recuperar información de ficheros corruptos.
Esto también aplica a todos los formatos de Microsoft Office que acaban en x, como pueden ser DOCX de Microsoft Word, o PPTX de Microsoft PowerPoint.
De vez en cuando me preguntan cómo abrir un fichero adjunto porque tiene una extensión extraña, y el ordenador no lo reconoce.
Al comprobar el fichero adjunto resulta que la extensión comienza por un guión bajo.
Esto es debido a ciertos antivirus que para poder enviar un adjunto y que supere siempre las barreras anti spam lo que hacen es modificar la extensión del archivo poniéndole un guión bajo al lado del punto para evitar que los programas anti spam los detecten y borren el adjunto.
Esto suele suceder con los archivos pdf y los archivos de la Office de Microsoft.
Esto provoca que no sea posible abrir directamente desde el programa de email el fichero adjunto, que microsoft windows no reconoce la extensión del fichero y por lo tanto no sabe con que programa abrirlo.
La solución es tan simple cómo guardar el fichero en una carpeta por ejemplo el escritorio y con el botón derecho indicarle cambiar nombre y modificar, no el nombre, sino la extensión del fichero, eliminando el guión bajo.
Dependiendo de la configuración de Windows es posible que las extensiones es tengo ocultas por lo que habrá que modificar la opción adecuada para que se muestren.
En ocasiones el tamaño de la celda es más pequeño que el contenido y esto hace que, en Microsoft Excel, no se vea el contenido completo.
Es posible ajustar el ancho de la columna poniéndose sobre la línea que separa los nombres de las columnas.
En ese momento, aparecen unas flechas que podremos arrastrar para cambiar el ancho de la columna.
Si bien de esta forma podemos asegurarnos que las celdas visibles si se muestran completamente, podría ser que el contenido de alguna celda en una fila más abajo, que no estamos viendo no coja entero en la celda.
En lugar de arrastrar las flechas podremos hacerle doble clic de tal forma que Microsoft Excel se encarga de autoajustar el ancho de la columna para que cojan todos los contenidos de todas las celdas de esa columna.
Hoy traigo un ejemplo completo en el que se cruza una tarifa y un albarán para detectar diferencias en precios y artículos erróneos.
El mismo ejemplo se podría aplicar sobre un pedido y un albarán de entrega.
El prime paso es copiar en una nueva hoja un campo único y común a ambos listados. En el ejemplo se ha usado el código de artículo.
Se copian todos los códigos de la tarifa, y debajo todos los del albarán. Es posible que hayamos recibido en el albarán un artículo que no estaba en la tarifa.
Partimos de la presunción de que tanto en la tarifa como en el albarán no se repiten líneas con el mismo código.
El segundo paso es quedarnos con un solo valor para cada código de artículo, para lo cual usamos la opción "Eliminar duplicados"
Una vez que pulsamos aceptar nos indica cuántos elementos duplicados se han detectado, y cuántos valores únicos quedan.
En lugar de usar BUSCARV, vamos a usar la combinación de COINCIDIR + INDICE, que para este caso resulta mucho más rápid, ya que solo necesita realizar un búsqueda por cada documento.
El siguiente paso es localizar el número de fila en el que está el dato.
Para ello usaremos la función COINCIDIR.
En este caso usaremos
=COINCIDIR(A2;Tarifa!A:A;0)
Usamos la referencia relativa para el dato a buscar, y para referirnos al rango en el que buscar haremos referencia a la columna completa A:A
Una vez conocido el número de fila en el que se encuentra el dato buscado, podemos usar COINCIDIR para recuperar los datos.
En este ejemplo numerados.la primera fila comenzando desde 1 hasta el número de columnas del listado original.
De esta forma es posible con una única fórmula COINCIDIR recuperar todos los datos del listado original.
La formula usada es
=COINCIDIR(Albarán!$A:$G;$G2;H$1)
En este caso el rango a buscar debe ser absoluto, pues vamos a desplazar la fórmula tanto en horizontal como en vertical.
Para los números de fila y columna debemos usar referencias mixtas.
Para el número de fila debemos bloquear la letra $G2, y para el número de columna bloquear el número H$1
De esta forma con una única fórmula podemos recuperar todos los datos.
Finalmente para detectar las posibles diferencias de precios, hacemos una simple resta.
Si el resultado es cero, significará que no hay diferencia, y si es un valor numérico distinto de cero, representará la diferencia.
También es posible que el resultado sea un error del tipo #N/A (Not available), lo que indicará que ese artículo solo se encuentra en uno de los dos listados.
Para ver solo los.vallres con incidencia podemos.aplicar un filtro ocultando los.vaalores iguales a cero.
El resultado final al filtrar eliminando los valores cero es el que se muestra.
A continuación el proceso completo en un videotutorial en Youtube.
En el ejemplo haremos un listado de todos los fines de semana (sábados y domingos).
El truco consiste en indicar a mano los primeros días (en este caso el primer sábado, y arrastrando la celda Microsoft Excel nos propondrá el siguiente día, el domingo).
Luego aplicaremos la misma formula que para el listado de los sábados, pero en la tercera línea, haciendo referencia a la primera línea.
En la celda A3 introduciremos la fórmula
=A1 + 7
Al arrastrar está fórmula en la celda A3 tendremos el día indicado en la celda A3 más 7 dias.
Esto es el siguiente domingo.
Y así correlativamente.
Con este método, se puede hacer un listado de cualesquiera días, por ejemplo de los días que vamos al gimnasio (ej Lunes, Miércoles y Viernes).
Os dejo una animación con el proceso completo.
NOTA: No hay nada que nos obligue a hacer referencia a la misma fila o a la inmediatamente superior en Microsoft Excel. Con un poco de imaginación se pueden obtener resultados muy interesantes.
