Tablas de datos en Excel - Tutorial Excel

Las tablas de datos forman parte de las herramientas de Análisis de hipótesis de la ficha Datos y nos permiten trabajar con varias hipótesis. Estas tablas nos permiten cambiar el valor de algunas celdas para ver cómo se vería afectado el resultado inicial. Una tabla de datos analiza un conjunto de valores y determina posibles resultados.

Sirven para poder formular diferentes hipótesis considerando variaciones en algunas variables para ver qué resultado arrojan en cada caso.

Se pueden crear tablas de datos de una o dos variables, según el número de variables que se quieren probar.

Tablas de datos de una variable

Las tablas de datos en Excel de una variable permiten obtener diferentes resultados según cambios hipotéticos en una sola variable.

Supongamos el caso en el que deseamos calcular la cuota de un préstamo de cuotas constantes. Queremos realizar varias hipótesis según la duración del préstamo.

Si consideramos que necesitamos solicitar un préstamo de 85.000€ a un tipo de interés del 6,50% anual y una duración de 25 años, con la función PAGO podemos calcular la cuota constante que vamos a pagar por ese préstamo. En este caso serían 573,93 euros.

Imaginemos que queremos realizar más hipótesis para ver si modificando la duración obtenemos una cuota que nos interese más.

Una forma manual de hacerlo sería ir probando manualmente. En la celda B3 iríamos incluyendo más o menos años para ver cómo se quedaría la cuota.

Pero una forma más inmediata y directa sería con la Tabla de datos.

Debemos tener en cuenta que en cómo queremos calcular la cuota según diversas hipótesis tendremos los nuevos resultados aparecerán debajo o a la izquierda de la celda que contiene la fórmula, según indiquemos las variable por columnas o por filas.

En este caso queremos calcular la cuota variando la duración, que escribiremos en una columna de la siguiente forma.

De esa forma dejamos la columna B libre para que se muestren los resultados calculados para cada hipótesis.

Tenemos que señalar todo el rango que incluye las variables y la fila con la fórmula que calcula la cuota.

Ahora iríamos a la pestaña Datos – Análisis de hipótesis – Tabla de datos…

Se nos abre la siguiente ventana.

En esa ventana tenemos que decidir si nuestras variables van en columnas y debemos referenciar la celda a la que se refieren las variables, es decir, la duración, que en nuestro caso se encuentra en la celda B3.

Por ello rellenaremos los datos de la siguiente forma.

Si le damos a Aceptar obtenemos el siguiente resultado.

Dejándolo en formato moneda vemos que tenemos el resultado para todos los casos de duración.

Es aconsejable comprobar que el resultado para la duración inicial que calculamos es el coincide con el resultado original.

Aquí vemos que el dato de la celda B5 y B11 coinciden.

Tablas de datos de dos variables

Las tablas de datos en Excel de dos variables permiten obtener diferentes resultados según cambios hipotéticos en una dos variables.

El funcionamiento es similar que en el caso anterior pero aquí la tabla nos mostrará los resultado en forma de tabla de doble entrada.

Vamos a hacer las hipótesis con el mismo caso anterior, pero en este caso queremos comprobar los resultados para variaciones de duración y de tipo de interés.

Es decir, queremos que nos rellene la siguiente tabla.

Aquí vemos la verdadera utilidad de las tablas de datos, dado que si lo hiciéramos manualmente tendríamos que hacer 70 cálculos diferentes.

De nuevo, lo más importante es que la celda con la fórmula se encuentre en la celda superior izquierda de la tabla, en la misma fila y columna en la que se encuentran los dos tipos de variables.

Ahora iríamos de nuevo a la pestaña Datos – Análisis de hipótesis – Tabla de datos… y se abre de nuevo la ventana de Tabla de datos.

Las variables que se indican por filas se referencian a la celda con el tipo de interés (B4), mientras que las variables por columnas se referencian a la celda con la duración (B5).