Cómo consolidar datos de varias hojas usando referencias 3D en Excel
Excel 3D Reference es una fórmula breve y flexible que le permite realizar cálculos haciendo referencia a la misma celda o un rango de celdas en varias hojas. El uso de fórmulas 3D elimina la necesidad de hacer referencia a cada celda, lo que facilita los cálculos. Cuando aplique la fórmula 3D una vez, incluirá datos de las hojas que agregue más adelante.
Este artículo explorará cómo la consolidación de datos en una hoja puede ayudar a reducir el tiempo que lleva calcular datos en varias hojas. La gestión de grandes cantidades de información dentro de una sola hoja de trabajo puede ser fácil y eficiente, lo que lleva a resultados precisos con exigencias mínimas.
Diferentes enfoques para sumar datos en Excel
En Excel, la forma tradicional de sumar información en varias hojas es sumar manualmente cada celda mediante la función Suma. Por el contrario, agregarlos manualmente puede volverse cada vez más problemático si tiene un gran conjunto de datos de ventas mensuales durante un período prolongado.
Este tampoco es un método dinámico, por lo que tendrá que cambiar las fórmulas nuevamente si agrega una hoja adicional. Veamos rápidamente cómo la consolidación de datos, en varias hojas, en una sola hoja con la ayuda de Excel 3D Reference, ayuda a resumir los datos de forma rápida y precisa.
Sintaxis de la referencia 3D de Excel
Eche un vistazo a la sintaxis de una fórmula de referencia 3D que suma los valores de las celdas B4 para cuatro hojas, denominadas enero, febrero, marzo y abril.
=SUM(January:April!B4)Como se muestra arriba, hay dos elementos principales en la Fórmula 3D de Excel. El primero se refiere a los nombres de la primera y última hoja separados por un punto y coma. En la segunda parte, se hace referencia a la misma celda o rango de celdas en las cuatro hojas.
En este caso, se utiliza SUM. También puede utilizar cualquier otra fórmula durante el cálculo. Sin embargo, la sintaxis de la referencia 3D sigue siendo la misma. Para utilizar la referencia 3D de Excel, los datos deben ser coherentes en las cuatro hojas.
Ejemplo de referencia 3D de Excel
Ahora, entendamos la razón de ser de la función 3D de Excel. Para evaluar esto, pongámoslo en práctica utilizando un conjunto de datos.
Eche un vistazo a los datos de muestra que contienen las ventas de los cuatro meses, el número de stock, el stock vendido, el stock comprado y el stock de cierre al final de cada mes.
Datos de enero:
Datos de febrero:
Datos de marzo:
Datos de abril:
En el primer paso, evalúe los datos disponibles en cuatro hojas para ver si son secuenciales. Solo entonces se pueden implementar las referencias 3D.
Implementación de la referencia 3D
Para calcular las ventas totales durante cuatro meses, seleccione la celda donde aparecerá la cifra.
Para este ejemplo, el valor de ventas reside en la celda B3 para las cuatro hojas; los datos de referencia en cuatro hojas estarán en la misma celda. El rango aparecerá de enero a abril porque ambos son nombres establecidos para las hojas inicial y final, respectivamente.
1. Agregue la celda B3 como celda de referencia .
2. De enero a abril, elija el rango a utilizar.
3. Presione Enter para ejecutar la fórmula.
Para asegurarse de que la fórmula se haya aplicado correctamente, puede sumar manualmente los valores de un elemento específico en el conjunto de datos. Una vez que sepa que es correcto, puede esperar que el resto de los cálculos sean correctos siempre que los valores sean consistentes.
El mismo enfoque para calcular el valor total de las ventas durante cuatro meses se aplica a las existencias, las acciones compradas, vendidas y las existencias de cierre al final de cada mes.
El rango permanece igual durante cuatro meses, mientras que la celda referenciada cambiará según su ubicación en diferentes hojas. Resumamos los datos de las acciones durante cuatro meses.
1. Agregue la celda B4 como celda de referencia .
2. Elija rango a partir de enero: abril .
Arrástrelo para implementar la fórmula de suma 3D para otras celdas en la fila usando la secuencia de autocompletar .
De la misma manera, puede aplicar referencias 3D a un rango de celdas en diferentes hojas de trabajo. En este caso, la selección de las hojas sigue siendo la misma.
Sin embargo, debe elegir el rango completo en lugar de una celda como referencia. Además, asegúrese de que los datos sean coherentes en todas las hojas.
Agregar nueva hoja en el libro de trabajo
La referencia 3D de Excel actualiza automáticamente los datos al agregar una o más hojas nuevas siempre que esas hojas se encuentren entre los rangos seleccionados en la fórmula 3D. Vamos a ver cómo funciona.
Como se muestra a continuación, las ventas totales durante cuatro meses son 250000 para los meses de enero a abril.
Agreguemos una nueva hoja que contenga los datos del mes de mediados de febrero.
Incorpore el valor de venta de la hoja de mediados de febrero. Los datos aparecerán automáticamente en la hoja principal del análisis cuatrimestral.
Como se muestra arriba, las ventas totales de todos los meses aumentan en una cantidad de 10,000. Así, sin alterar la fórmula, el resultado se ha actualizado automáticamente.
Esta flexibilidad resulta útil al realizar un seguimiento del registro de ventas de todo el año en conjuntos de datos masivos. Agreguemos el stock, la cantidad vendida, la cantidad comprada y los datos de stock final a la hoja de mediados de febrero.
Eche un vistazo a cómo la adición anterior afecta los datos en la hoja de análisis de cuatro meses.
Por lo tanto, la fórmula de referencia ha actualizado correctamente todos los datos de la hoja principal.
Haga que los datos sean consistentes para aplicar referencias 3D
Cuando planee consolidar datos de varias hojas en una, asegúrese de que sean consistentes en el número de celda. Si, por ejemplo, el stock comprado se encuentra en una celda determinada en la hoja 1, es necesario colocarlo en la misma celda en todas las demás hojas. Además, al agregar una nueva hoja, ingrese los datos correctamente en las celdas.
Simplifique los cálculos con las referencias 3D de Excel
La implementación de fórmulas 3D puede parecer difícil de implementar, pero es fácil de mantener una vez que las ejecuta correctamente. Le permite agregar nuevos datos cada mes sin tener que hacer los cálculos desde el principio.
La fórmula 3D también elimina las posibilidades de duplicación u omisión de ventas para un mes determinado. Esta funcionalidad será más beneficiosa para los contadores que actualizan paquetes de datos todos los meses.