Este es el segundo artículo de la serie de cinco partes sobre análisis de datos en Excel. En esta sección, le mostraré cómo usar la tabla de datos de dos variables en Excel.
Otros artículos de esta serie:
- Una tabla de datos variables en Excel.
- Administrador de escenarios en Excel.
- Búsqueda de objetivos en Excel.
- Excel Solver.
Ver video - Tabla de datos de dos variables en Excel
La tabla de datos de dos variables es más adecuada en situaciones en las que desea ver cómo cambia el resultado final cuando dos de las variables de entrada cambian simultáneamente (en comparación con la tabla de datos de una variable, donde solo cambia una de las variables de entrada).
Si desea analizar datos cuando cambian más de 2 variables, el administrador de escenarios es el camino a seguir.
Cuándo usar la tabla de datos de dos variables en Excel
Suponga que tiene un conjunto de datos como se muestra a continuación:
En el conjunto de datos anterior, tenemos el monto del préstamo, la tasa de interés y el número de pagos mensuales. Con base en estas 3 variables de entrada, se calcula el Pago Mensual (está en rojo ya que es una salida de dinero). La siguiente fórmula se utiliza para calcular el pago mensual:
= PAGO (B2 / 12, B3, B1)
Ahora es posible que desee hacer un análisis para ver cuál debería ser la combinación ideal de Monto del préstamo y Número de pago mensual para satisfacer sus necesidades. Por ejemplo, es posible que desee mantener el Pago mensual en $ 500 o menos y analizar qué combinación de Monto del préstamo y Tenencia puede brindarle esto.
En tal situación, se debe utilizar una tabla de datos de dos variables.
Configuración de la tabla de datos de dos variables en Excel
Estos son los pasos para configurar una tabla de datos de dos variables en Excel:
- En una columna, tenga todos los valores diferentes que desea probar para Número de pagos mensuales. En este ejemplo, estamos probando 72, 84, 96… 240. Al mismo tiempo, tenga los diferentes valores del monto del préstamo en la fila justo encima de los valores de la columna (comenzando una celda a la derecha) como se muestra en la imagen de abajo.
- Escriba = B4 en la celda D1, que está una fila por encima de los valores de la columna. Esta es una construcción que debe seguirse cuando se trabaja con dos tablas de datos variables. Además, asegúrese de que el valor en la celda D1 dependa de ambas variables (Número de pagos mensuales y Monto del préstamo). No funcionará si ingresa manualmente el valor en la celda D1.
En este caso, la celda D1 se refiere a la celda B4, que tiene un valor calculado usando una fórmula que usa las celdas B1, B2 y B3..
- Ahora los datos están listos para ser usados para un cálculo de tabla de datos de dos variables.
- Seleccione los datos (D1: J16). Vaya a la pestaña Datos -> Herramientas de datos -> Qué pasa si Análisis -> Tabla de datos
- En el cuadro de diálogo Tabla de datos, utilice las siguientes referencias:
- Celda de entrada de fila: $ B $ 1
- Celda de entrada de columna: $ B $ 3
- Haga clic en Aceptar. Tan pronto como haga clic en Aceptar, instantáneamente llena todas las celdas vacías en el rango de datos seleccionado. Rápidamente le brinda una vista de los pagos mensuales para varias combinaciones de monto del préstamo y número de pagos mensuales.
Por ejemplo, si desea identificar las combinaciones de Monto del préstamo y Número de pago mensual que resultarían en un Pago mensual de menos de $ 500 por pago mensual, simplemente puede usar este método de tabla de 2 datos variables.
Nota:
- Una vez que haya calculado los valores usando la tabla de datos, no se puede deshacer usando Control + Z. Sin embargo, puede seleccionar manualmente todos los valores y eliminarlos.
- No puede eliminar / modificar ninguna celda en todo el conjunto de valores calculados. Dado que se trata de una matriz, deberá eliminar todos los valores.
