Cómo hacer una tabla de datos para análisis hipotéticos en Excel

Microsoft Excel ofrece algunas funciones para realizar análisis en escenarios hipotéticos. Una de estas herramientas es una tabla de datos. Con él, puedes experimentar con diferentes valores para ver varios resultados.

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

Crear una tabla de datos de una variable

Las tablas de datos en Excel pueden incluir una o dos entradas variables. Entonces, comencemos con una tabla de datos de una variable. Hemos calculado el valor futuro de nuestra inversión con 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ía el valor futuro de nuestra inversión con 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, coloca 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 debajo y a la izquierda.

Para nuestro ejemplo, estamos usando una tabla de datos orientada a columnas. Como puede ver en la captura de pantalla a continuación, movimos 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, esto es C2 a D6.

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

En el cuadro Tabla de datos que se abre, ingrese la referencia de celda para la variable cambiante y según su configuración. Para nuestro ejemplo, ingresamos la referencia de 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.

Haz clic en "Aceptar" y verás tus resultados. Ahora tenemos el valor futuro de nuestra inversión con cada tasa de interés que ingresamos.

Crear una tabla de datos de dos variables

Es posible que desee agregar dos variables a su tabla de datos para un análisis más detallado. Usando nuestro mismo ejemplo, no solo queremos el valor futuro de nuestra inversión con diferentes tasas de interés, sino que también nos gustaría ver diferentes valores futuros cambiando la cantidad de pagos.

RELACIONADO: Cómo usar el "Análisis rápido" de Excel para visualizar datos

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

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

Vaya a la pestaña Datos y seleccione Análisis hipotético > Tabla de datos. Cuando aparezca el cuadro, 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 nuestra cantidad de pagos.

Haga clic en "Aceptar" y debería ver todas las salidas de sus variables. Aquí, tenemos el valor futuro de nuestra inversión usando diferentes tasas de interés y diferentes cantidades de pagos, todo mostrado en una tabla bonita y ordenada.

La próxima vez que desee analizar datos en su hoja con números o cantidades variables, considere usar una tabla 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.

RELACIONADO: Cómo crear y usar una tabla en Microsoft Excel