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.
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.
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.
Mucha gente comienza a hablar de la nueva función de Excel buscarx como un sustituto de buscarv.
Mi opinión es justo la contraria.
Buscarv está para quedarse.
Creo que sólo una minoría de usuarios medio avanzados los que comenzarán a usarla.
Cierto es que la función buscarx es más versátil que buscarv, pero tiene mucho en contra para conseguir sustituir a buscarv.
La mayor dificultad está en la no retrocompatibilidad. Buscarx solo estará disponible para versiones de Microsoft Excel 2019 y superiores (Office 365 incluido).
Esto hace que si se crea una hoja de cálculo con la función Buscarx, solo se pueda compartir con usuarios con esas versiones de Microsoft Excel. Cualquiera que tenga una versión anterior no podrá usar la hoja de cálculo.
La solución a este problema sería que todos los equipos de la empresa se actializasen.
Sin embargo, aunque eso fuese posible, seguiría existiendo el problema de compartir esa hoja de cálculo con usuarios de fuera de la organización.
Una de las tareas más habituales que se realizan en Microsoft Excel el cruzar / puntar listados, para comprobar que datos de un listado se muestran en el otro.
Cuando los datos a cruzar son numéricos aparece una problemática adicional.
En ocasiones los datos numéricos son almacenados como si de texto se tratase.
Es importante tener claro que para una aplicación informática los números y sus valores equivalentes en formato texto son cosas distintas y no comparables. Por ejemplo, el número 5 no es lo mismo que el texto '5'.
Una de las principales diferencias entre un número y una letra, a nivel de Microsoft Excel, es que los números se pueden sumar y las letras no.
Esto nos va a permitir comprobar fácilmente si el dato está almacenado como texto o como valor numérico.
En la imagen muestro dos columnas con el código postal de las Poblaciones.
En la columna A los datos están en formato numérico, y en la columna C están en formato texto.
Podríamos pensar que si tiene ceros a la izquierda está en formato texto, pero eso no es necesariamente cierto, ya que ese efecto se puede conseguir mediante un formato personalizado de la celda.
También podemos ver si el dato es texto por el triangulo verde que aparece en la parte superior de las celdas de la columna C, pero puede llegar a confundirse con el triángulo que muestra los comentarios.
Un truco infalible para saber si los datos son numéricos es seleccionar varias celdas y fijarse en la barra de estado, en la que se muestra un resumen de los datos seleccionados.
Si los datos seleccionados son numéricos, se mostrará tanto el recuento de celdas con datos como la suma de los valore almacenados.
En caso de ser texto solo se mostrará el recuento, ya que las palabras no se pueden sumar.
En un filtro en Microsoft Excel es posible filtrar por un texto.
Por ejemplo podemos filtrar la población "Cortijo Real" escribiendo en la casilla de texto el nombre de la población.
Sin embargo también aplicará el filtro si escribimos una parte del filtro, de esta manera se mostrarán todos los textos que contengan el conjunto de caracteres escritos, en el ejemplo todas las poblaciones que contienen "corti"
Aunque en el ejemplo anterior pueda parecer que solo se filtran las poblaciones que empiezan por los caracteres indicados, no es así, se muestran todas las poblaciones que contengan ese conjunto de caracteres, como demuestra la búsqueda de las poblaciones que contengan el texto "real"
El resultado de aplicar ese filtro es el mostrado en la siguiente imagen.
Cuando tenemos que seleccionar valores del listado del filtro, si trabajamos con pocos elementos es factible seleccionarlos haciendo scroll.
Sin embargo, esta forma de aplicar filtros puede ser un tanto incómoda, ya que si pinchamos fuera de la ventana de selección del filtro antes de haber pulsado el botón aceptar, no se aplica ningún filtro.
Esto es especialmente molesto cuando tenemos que seleccionar una gran cantidad de valores aislados.
Para evitar que nos pase esto, podemos seleccionar uno o varios elementos y pulsar aceptar.
Esto hará que una parte del filtro se aplique.
Para aplicar el resto de los valores, volvemos a desplegar el filtro y seleccionamos el siguiente o los siguientes valores y pulsamos el botón aceptar.
De esta forma los nuevos elementos se unirán a la selección anterior, y nos evitaremos perder la selección al completo.
En este ejemplo en la segunda ocasión que se desplegó el filtro se añadió el valor 25666.
Como ya hemos visto anteriormente, si se copia un rango de celdas filtradas y se pega en una zona vacía de la hoja de cálculo, los datos se pegan de forma continua, no al lado de los originales, como suele ser de esperar.
Para conseguir ese efecto se puede recurrir a dos técnicas:
1 - Invertir el filtro y eliminar los datos que no deseamos, manteniendo de esa forma los que si deseamos.
2- Mediante programación en VBA (Visual Basic for Applications)
En este post explicaremos el primer método.
Se trata simplemente de aplicar lógica negativa a nuestro problema.
Si tenemos que pegar los datos de edad de una columna de las personas de sexo masculino, lo que haremos será seleccionar las personas de sexo femenino y borrar los datos.
Mostraremos el proceso, paso a paso:
Primero creamos los datos.
Estos serán los datos que deseamos mantener, y que en principio intentaríamos copiar y pegar, pero como hemos visto en el post anterior (Pegar celdas filtradas en filas filtradas), esto provocará que no se peguen al lado de los datos originales, sinó consecutivamente desde la primera fila.
En su lugar, haremos una selección usando la lógica inversa, esto es, mostrando los datos que no deseamos mantener.
En el caso del ejemplo, los salarios de las personas de sexo Femenino.
Una vez mostrados, seleccionamos los datos que no deseamos.
Y lo eliminamos.
Un vez quitado el filtro, obtenemos el resultado deseado.
Nota:
En caso de tener que seleccionar una cantidad importante de datos, es útil, usar una columna auxiliar para indicar los datos a los que se aplicará el filtro.
Cuando se trabaja con filtros, en muchas ocasiones interesa aplicar un filtro para luego copiar los datos mostrados.
Por ejemplo, creamos una columna con datos de edades y aplicamos un Autofiltro, mediante el que mostramos los datos de edad de la gente "mayor de edad", esto es, aquellos que han cumplido 18 años.
Una vez aplicado el filtro, seleccionamos y copiamos los datos.
Nos colocamos en la celda C1 y pegamos.
En este contexto mucha gente espera que los dato se peguen al lado de los ya filtrados para obtener una lista con solo esos datos.
Lo que en realidad sucede es que Microsoft Excel, ha seleccionado ocho celdas, y las pegará en las primeras ocho filas de la columna C (en realidad lo hará en un rango que comienza en la celda seleccionada actualmente)
Este efecto se ve claramente al desactivar el filtro.
Es importante tener en cuenta esta forma de proceder, ya que es muy fácil, provocar un cruce de datos inadvertido, consiguiendo resultados indeseados.
Para obtener una columna con los datos filtrados y sin los datos no filtrados, existen diversas formas de proceder.
En la mayoría de los casos es suficiente con invertir el filtro y eliminar los datos no deseados.
Justo lo contrario a "Pegar Valores" es "Copiar Formato".
Esta opción nos puede ahorrar mucho tiempo a la hora de aplicar formatos, ya que en un único paso aplica todos los formatos de otra celda.
Los formatos copiados incluyen la fuente, con su tamaño, colores de fondo y fuente, así como todas las demás características que puedan tener (Negrita, Cursiva, etc)
También copia el formato condicional, así como alineaciones y formatos de bordes de celda.
Una vez aplicados todos los formato a una celda, en este caso la celda B2, seleccionamos esta celda.
Y pulsamos en el botón "Copiar Formato"
Una vez hecho esto, solo tenemos que pulsar en la celda que queremos que tenga el mismo conjunto de formatos.
También es posible aplicar el mismo formato a una selección de celdas, ya sea una Columna, Fila o Rango.
Para ello seleccionaremos la letra o letras de la columna, los números en el caso de Filas o un rectángulo de celdas en el caso de un Rango.
En el ejemplo, tras seleccionar la casilla B2 y pulsar el botón "Copiar Formato" se han seleccionado las columnas G:J. Al soltar el botón, se aplica el mismo formato a todas las celdas de las cuatro columnas seleccionadas (G, H, I, J).
Bonus:
No todo el mundo sabe que pulsando con doble clic el botón "Copiar Formato" este queda seleccionado hasta que se vuelve a pulsar en el, permitiendo de esta forma aplicar el formato a varia celdas aisladas con solo pulsar en ellas.
Según el uso que les demos a las fórmulas en una hoja de cálculo, podríamos definir tres tipos distintos de hojas.
Un primer tipo en el que no existen fórmulas y que se usa para filtrar, ordenar o agrupar datos ya conocidos.
Un segundo tipo en el que se necesita conocer unos datos finales a partir de unos dato originales conocidos y que no cambian.
Y un tercer tipo en el que los datos de partida pueden cambiar modificando de esta manera los resultados.
Es posible transformar el segundo tipo de hojas en el primero eliminando las fórmulas una vez calculadas.
Esto mejorará la velocidad de uso de la Hoja de cálculo, reducirá el tamaño, y mejorará la velocidad de guardado del documento, ya que, por defecto los documentos de Microsoft Excel se recalculan al guardarse.
En este caso solo hay que seleccionar las columnas deseadas, puede ser todo el documento, "Copiar" y luego, sobre las mismas celdas, seleccionar la opción "Pegar Valores", por ejemplo tras pulsar el botón derecho.
El icono es el que muestro a continuación.
En el ejemplo, en un fichero de 400 lineas se ha conseguido pasar de 57 KB a 46 KB, una reducción cercana al 20% de tamaño, in perder ninguna información útil.
En esta imagen podemos ver las fórmulas utilizadas.
Las cuales tras usar el comando "Pegar Valores" solo contienen los datos mostrados a continuación.
Nota: Usualmente en documentos de gran cantidad de líneas es útil, a medida que se van consiguiendo resultados intermedios, ir pegando como valores, para mejorar la velocidad de Microsoft Excel.
En ocasiones es muy cómodo poder operar con los valores de una comparación.
En muchos lenguajes de programación el valor Verdadero (también denominado True) se asocia al valor numérico 1, mientras que el Falso (False) se asocia principalmente a 0 (aunque en eso hay diferencias entre los distintos lenguajes).
Si Verdadero fuese 1 sería suficiente con seleccionar la columna y ver el recuento de valores y la suma de los mismos para saber cuantos resultados hay, y cuantos de ellos son Verdaderos, y por lo tanto restando también sabríamos cuantos son Falsos.
Supongamos que nos dan una columna A con edades de personas, y nos solicitan que en la columna B indiquemos si son mayores o menores de edad.
Podríamos resolverlo usando la función si, de una forma como esta:
=SI(A2>=18;"Mayor de edad";"Menor de edad")
Sin embargo esta solución, aunque es autoexplicativa, no nos permite ir más allá fácilmente.
Por ejemplo, si ahora quisiésemos indicar en una celda el número de mayores de edad, habría que usar una función contar.si, la cual tiene un alto coste de procesado, y puede retardar mucho la obtención del resultado en hojas con muchos datos.
Para mejorar la velocidad de procesado de la hoja, nos limitaremos ha hacer una comparación en la celda. Para Microsoft Excel el resultado de una comparación es Verdadero o Falso.
Se podría hacer de esta manera. =A2>=18
Como se puede ver, el resultado sigue siendo autoexplicativo, y se ha utilizado solo una comparación para obtenerlo, mejorando considerablemente el tiempo de calculo de la hoja.
Se puede dar un paso más, transformando el valor VERDADERO / FALSO en un número mediante la función N() de Excel, que entre otras cosas, transforma VERDADERO en 1 y FALSO en 0.
Se usaría así =N(A2>=18)
Además en este caso podemos apreciar, que con solo seleccionar los datos de la columna B, podemos comprobar en la barra de estado que el total de datos es 15 (Recuento: 15) y que el total de mayores de edad, esto es resultados verdaderos es de 7.
Eso permitiría usar de forma sencilla estos datos como fuente para otros cálculos.
La última celda de Microsoft Excel, es la intersección entre la fila inferior y la columna de la derecha con datos.
A priori e tan sencillo como fijarse en los datos, pero esto puede ser un tanto difícil si el número de filas o columnas es muy grande, o existen datos no visibles en las celdas, como pueden ser caracteres no imprimibles, espacios o fórmulas que tengan como resultado un dato vacío.
Para poder saber cual es la última celda de un documento de Microsoft Excel, por lo tanto, no es suficiente fijarse en las celdas.
Se puede localizar la última celda mediante el comando "Ir a" al que se puede acceder mediante la tecla de Función F5 o en Inicio, Modificar, Buscar y Seleccionar, Ir a.
Una vez accedido al menú Ir a..., se pulsa el botón Especial...
Posteriormente se selecciona la opción Última celda.
Aunque, aparentemente la última celda era la H12, vemos que la celda seleccionada es la J12.
Esto implica que en alguna celda de la columna J hay algún dato.
Como se puede ver, en la celda J1 hay un espacio, lo que hace que la última celda.
En estos casos eliminar las columnas con datos no visibles puede reducir el tamaño del documento final.
En ocasiones, vemos documentos de Microsoft Excel con muy poco contenido, pero un tamaño elevado.
Esto puede ser debido a diversas razones, por ejemplo, la existencias de hojas ocultas con contenido.
Sin embargo, una de las razones más habituales es que la hoja de cálculo esté usando más celdas de las necesarias.
En un programa como Microsoft Excel, al guardar el fichero, se almacena la información necesaria de una serie de celdas.
En concreto se almacenan el contenido de un rectángulo de celdas que tuviese su esquina superior izquierda en la celda A1, y su esquina inferior derecha en la intersección de la última columna y la última fila con datos. Esta celda se denomina comúnmente "última celda".
En el ejemplo de la imagen solo se ha puesto contenido en dos celdas (H1 y A12), sin embargo, Microsoft Excel almacenará el contenido de todo el rectángulo resaltado, desde la celda A1 hasta la celda H12.
Esto implica que para almacenar los datos de esas dos celdas, es preciso guardar en fichero las 96 celdas correspondientes a la región A1:H12.
Esto en ficheros pequeños no tiene demasiada relevancia, pero en ficheros grandes puede hacer que el tamaño del fichero aumente considerablemente.
Dependiendo del tipo de fichero usado para almacenar la información (xls, xlsx, xlsb, ods) la repercusión sobre el tamaño puede ser distinta, pero siempre es una buena idea intentar compactar lo máximo posible los datos a fin de reducir el tamaño del fichero final.
El efecto del incremento de tamaño, se observa principalmente, cuando se dejan in usar celdas que se han utilizado con anterioridad. En el caso de celdas vacías que nunca han contenido datos el efecto puede ser inapreciable.
Este caso es aplicable a todas las herramienta de hoja de cálculo como pueden ser Libreoffice Calc o Google Spreadsheets.
Mucho más interesante que ordenar por color, es la opción de filtrar por color.
Es muy habitual resaltar las celdas que nos interesa cambiando el color de fondo.
Esta opción nos permite solo ver las que se han marcado, o en caso contrario ocultarlas y ver solo las no resaltadas.
Existe la posibilidad de seleccionar el color de fondo de la celda o el del texto.
En la siguiente imagen hay textos en rojo y negro y fondos en diversos colores (como en la primera imagen si todos los tipos de letra son del mismo color, no aparece la opción para seleccionarlo.)
Esta opción se combina muy bien con el formato condicional.
A diferencia de la ordenación por color, con el filtrado no se pierde el orden original, ya que este se recupera al dejar de aplicar el filtro.
