Cómo hacer una tabla de datos para el análisis «qué pasaría si» en Excel

Microsoft Excel ofrece varias funciones para analizar escenarios hipotéticos. Una de esas herramientas es la hoja de datos. Con él, puedes experimentar con diferentes valores para ver diferentes resultados.

Esta es una forma útil de ver cosas como préstamos, inversiones, márgenes de productos, etc., con diferentes variables. Por ejemplo, puede ingresar varias tasas de interés diferentes para un préstamo para ver cuál será su pago en cada tasa, todo en un solo lugar.

Crear una tabla de datos con una variable

Las tablas de datos en Excel pueden incluir una o dos entradas diferentes. Entonces, comencemos con una tabla de datos con una variable. Calculamos el valor futuro de nuestra inversión usando la función FV y constantes para la tasa de interés anual, el número de pagos y el monto del pago.

Aquí veremos cuál será el valor futuro de nuestras inversiones a diferentes tasas de interés.

Puede ingresar sus valores en una columna o fila, pero la ubicación afecta dónde debe colocar su fórmula. Si tus valores están en una columna, pon la fórmula una celda arriba y a la derecha. Si sus valores están en una fila, coloque la celda de fórmula uno hacia abajo y hacia la izquierda.

En nuestro ejemplo, estamos usando una tabla de datos orientada a columnas. Como puede ver en la captura de pantalla a continuación, hemos movido nuestra fórmula a la columna D y enumeramos nuestras tasas de interés en la columna C.

Seleccione las celdas que contienen la fórmula, los valores y los resultados esperados. Para nosotros es de C2 a D6.

Haga clic en la pestaña Datos, haga clic en la flecha desplegable Análisis hipotético y seleccione Tabla de datos.

En la ventana Tabla de datos que se abre, ingrese la referencia de celda para la variable a modificar y de acuerdo con su configuración. En nuestro ejemplo, estamos ingresando una referencia a la celda B3 para la tasa de interés cambiante en el campo Celda de entrada de columna. Nuevamente, estamos usando una tabla de datos basada en columnas.

Haga clic en Aceptar y verá los resultados. Ahora tenemos el valor futuro de nuestra inversión con cada tasa de interés ingresada.

Crear una tabla de datos con dos variables.

Puede agregar dos variables a la tabla de datos para un análisis más detallado. Usando el mismo ejemplo, no solo queremos obtener el valor futuro de nuestras inversiones con diferentes tasas de interés, sino que también queremos ver diferentes valores futuros cambiando la cantidad de pagos.

Dado que está utilizando dos variables, utilizará una tabla de datos orientada tanto a columnas como a filas. Por lo tanto, coloque la fórmula directamente encima de la columna de valor y a la izquierda de la fila de valor como se muestra a continuación.

Ahora seleccione todas las celdas que contengan fórmula, valores y resultados esperados. Para nosotros es de C2 a F6.

Haga clic en la pestaña Datos y seleccione Análisis hipotético > Tabla de datos. Cuando aparezca la ventana, ingrese las referencias de celda de entrada de fila y columna.

Usando nuestro ejemplo, ingresamos B3 para la celda de entrada de columna que es para nuestra tasa de interés y B4 para la celda de entrada de fila que es para nuestro monto de pago.

Haga clic en Aceptar y debería ver todos los resultados de sus variables. Aquí tenemos el valor futuro de nuestra inversión usando diferentes tasas de interés y diferentes números de pagos, todo mostrado en una tabla bonita y ordenada.

La próxima vez que desee analizar datos en una hoja de trabajo con diferentes números o cantidades, considere usar una hoja de datos. No solo se realizan los cálculos por usted, sino que el resultado le proporciona una tabla estructurada para facilitar el análisis.