Frecuentemente recibo consultas sobre cómo copiar datos de una hoja a otra en Excel de una manera automática pero sin utilizar macros, y para atender ambos requerimientos solo tenemos la opción de utilizar ampliamente las fórmulas de Excel.
Antes de continuar debo mencionar que no existe una función de Excel que copie mágica y automáticamente los datos de una hoja a otra, así que será necesario crear varias fórmulas para lograr nuestro objetivo. Debido a la cantidad de fórmulas que podemos llegar a tener, esta alternativa de solución no será la más óptima para grandes cantidades de datos porque ocasionará que el libro de trabajo tome un tiempo considerable en el cálculo de las fórmulas. Así que, conociendo de antemano esta recomendación, iniciemos con el planteamiento del problema.
Planteamiento del problema
En una hoja de Excel tengo una base de datos de productos cuyos datos deseo copiar a una hoja independiente en base a su categoría. Para los datos de ejemplo necesitaré tres hojas adicionales para las categorías Fotografía, Libros y Software.
Al agregar nuevos registros a la hoja principal, los datos deberán reflejarse automáticamente en la hoja de su categoría.
Estrategia de solución
La estrategia que seguiremos para copiar datos de una hoja de Excel a otra será la siguiente:
Partiremos de una hoja que contiene todos nuestros datos y a partir de ella crearemos una hoja temporal para cada categoría de datos. El único inconveniente que tendrán las hojas temporales serán las filas entre los registros, pero dichas filas serán eliminadas al crear la ficha final dejando solo los registros con los datos que nos interesan.
Copiar datos a una hoja temporal
En base a los datos mostrados en la imagen inicial, he creado una hoja temporal llamada Temp_Fotografia y en la cual extraeré los datos de la categoría Fotografía ubicados en la hoja Datos. La fórmula que colocaré en la celda A2 será la siguiente:
=SI(Datos!$C2="Fotografía",Datos!A2,NOD())
Esta fórmula evalúa el valor de la celda C2 de la hoja Datos para saber si pertenece a la categoría “Fotografía” y en caso de ser verdadero trae el valor de la celda A2 de la hoja Datos. En caso contrario, obtendremos un error #N/A originado por la función NOD. Observa el resultado de esta fórmula en la celda A2 de la hoja Temp_Fotografia:
La fórmula nos devuelve correctamente el valor de la primera columna del primer registro ya que efectivamente pertenece a la categoría Fotografía. Para obtener el resto de las columnas será suficiente con arrastrar la fórmula hacia la derecha para obtener el siguiente resultado:
Quiero que pongas especial atención a la referencia mixta que he colocado en la fórmula como Datos!$C2 la cual fija solamente la columna C lo cual es de suma importancia al momento de copiar la fórmula hacia la derecha. Para saber si el resto de registros pertenecen a la categoría Fotografía solo debo arrastrar la fórmula hacia abajo y obtendré el siguiente resultado:
Si comparas los datos de la hoja temporal con la hoja de datos original observarás que solamente estarán presentes aquellos registros que pertenecen a la categoría Fotografía y el resto de filas se mostrarán con el error #N/A. Observa que he copiado la fórmula hasta la fila 20 haciendo previsión para cuando los datos originales también aumenten hasta la fila 20.
Siguiendo los mismos pasos mostrados hasta ahora debemos crear una hoja temporal para cada categoría de datos que deseemos copiar. La única modificación a la fórmula deberá ser el nombre de la categoría, por ejemplo, para la hoja temporal de la categoría Libros utilizaré la siguiente fórmula en la celda A2:
=SI(Datos!$C2="Libros",Datos!A2,NOD())
Copiar datos de una hoja a otra
El paso final es tomar como base las hojas temporales que hemos creado y eliminar las filas que tienen los errores #N/A para dejar solamente los registros que contienen datos. Para ello crearé una nueva hoja con el nombre de la categoría Fotografía y en la celda A2 colocaré la siguiente fórmula para copiar datos de una hoja a otra descartando las celdas con errores de la hoja temporal:
=SI.ERROR(INDICE(Temp_Fotografia!A$2:A$20, K.ESIMO.MENOR(SI(ESERROR(Temp_Fotografia!A$2:A$20), "", FILA(Temp_Fotografia!A$2:A$20)-MIN(FILA(Temp_Fotografia!A$2:A$20))+1), FILA(1:1))),"")
Esta no es una fórmula sencilla de explicar, pero la función principal es la función INDICE la cual obtiene el valor de un rango con solo especificar la fila. El rango sobre el cual se buscará el valor es el rango A2:A20 de la hoja Temp_Fotografia y que está indicado como el primer argumento de la función. El número de fila que será devuelto es calculado por la función K.ESIMO.MENOR que con la ayuda de la función ESERROR descarta las filas que contienen el error #N/A y solamente considera aquellas que tienen datos. Observa también que el resultado de la función INDICE está dentro de la función SI.ERROR para mostrar una cadena vacía en caso de encontrar un error.
Además, es necesario que esta fórmula sea ingresada como una fórmula matricial lo cual implica colocarla en la barra de fórmulas y pulsar las teclas Ctrl + Mayús + Entrar. En la siguiente imagen puedes observar el resultado de ingresar esta fórmula en la celda A2:
Después de haber ingresado esta fórmula en la primera celda la arrastraré hacia la derecha y posteriormente hacia abajo hasta la fila 20 y obtendré el siguiente resultado:
Puedes notar que las filas intermedias con errores que aparecían en la hoja temporal han sido eliminadas y ahora solo se muestran los registros con datos de la categoría correspondiente. De igual manera he copiado la fórmula hasta la fila 20 para que se reflejen los resultados automáticamente hasta un máximo de 20 registros. El procedimiento para las otras hojas será el mismo que hemos seguido hasta ahora y solo debemos cambiar la hoja temporal a la cual hace referencia la fórmula. Por ejemplo, para la hoja Libros utilizaré la siguiente fórmula en la celda A2:
=SI.ERROR(INDICE(Temp_Libros!A$2:A$20, K.ESIMO.MENOR(SI(ESERROR(Temp_Libros!A$2:A$20), "", FILA(Temp_Libros!A$2:A$20)-MIN(FILA(Temp_Libros!A$2:A$20))+1), FILA(1:1))),"")
Algo que no debes olvidar es que todos los rangos de las fórmulas que hemos creado hasta ahora los he limitado hasta la fila 20, pero si tienes una mayor cantidad de datos en tu libro, deberás adecuar dicho rango a la cantidad de filas necesarias.
Comprobar el funcionamiento de las fórmulas
Para comprobar que nuestras fórmulas funcionan correctamente será suficiente agregar nuevos registros en la hoja Datos y validar que se reflejen automáticamente en las hojas correspondientes. En la siguiente animación puedes observar cómo realizo esta comprobación:
Puedes descargar el libro de trabajo que utilicé en este ejemplo y comprobar que al agregar datos para las demás categorías se verán reflejados en sus propias hojas. Es así como hemos aprendido a copiar datos de una hoja a otra en Excel sin macros, solo recuerda que entre más datos tengas mayor será la cantidad de fórmulas contenidas en el libro lo cual podría en algún momento llegar a impactar en el desempeño de la hoja de cálculo.
Artículos relacionados
Cómo copiar y pegar celdas con VBA