miércoles, 11 de abril de 2012

Análisis de hipótesis con "Tabla de datos"

Solicitamos un crédito hipotecario de 120.000 € a pagar mensualmente en 20 años (240 mensualidades) a un interés del 3,50% anual. ¿Cuál será la cuota mensual?

Excel dispone de la función PAGO para hallar esta cuota. La fórmula que hay que poner en C6 es la siguiente:
=-PAGO(C3/12;C4;C2)     [Resultado: 695,95]

Ahora, queremos saber a cuánto ascenderán los pagos para otros intereses. Podemos resolver este nuevo problema creando una tabla donde el interés tome distintos valores.

En G3:
=-PAGO(F3/12;$C$4;$C$2)     [Resultado: 607,06]   

Extendemos la fórmula hasta la fila 15.

Hemos resuelto el ejercicio del modo convencional. A continuación, lo haremos con una Tabla de datos.

Nos ponemos en G2. Es imprescindible situar el cursor en esa celda. Cuando los valores de la variable (en nuestro ejemplo, el interés del préstamo) se encuentran en una columna, la fórmula que hay que usar como base para crear la Tabla de datos debe estar situada, obligatoriamente, en la celda que se encuentra una fila por encima de la lista y una columna a la derecha.

Además, debemos elegir una celda vacía que Excel utilizará como celda de entrada para los cálculos (será la A1). Esta celda la usaremos en el primer argumento de la función PAGO.

En G2:
=-PAGO(A1/12;C4;C2)     [Resultado: 500,00]

Seleccionamos el rango F2:G15 y accedemos a Datos + Análisis Y si + Tabla de datos. Se mostrará el cuadro de diálogo Tabla de datos.

Puesto que la lista de datos está en una columna, dejaremos en blanco la primera entrada y pondremos A1 en la segunda. El resultado será:

Colocando el cursor en cualquier celda del rango G3:G15 se observa que, en la barra de fórmulas, Excel ha insertado la siguiente fórmula matricial: {=TABLA(;A1)}

El valor de la celda G2 no interesa pero no se puede borrar, aunque es posible ocultarlo (cambiando el color de la fuente) o enmascararlo. Vamos a enmascararlo:

Hacemos clic con el botón derecho en G2, elegimos Formato de celdas y ponemos el formato personalizado siguiente: "P. mensual";;;

En otra hoja, calcularemos, por el método tradicional, los pagos mensuales para diferentes períodos de tiempo (180 meses, 240, 300, 360 y 420).

En F4:
=-PAGO($C$3/12;F3;$C$2)    [Resultado: 857,86]

Extendemos la fórmula hasta la celda J4.

Utilizando Tabla de datos, se hace así:

Nos ponemos en E4. Cuando los valores de la variable (en nuestro ejemplo, el número de pagos) se encuentran en una fila, la fórmula que hay que usar como base para crear la Tabla de datos debe estar situada, obligatoriamente, en la celda que se encuentra una fila por debajo de la lista y una columna a la izquierda (celda E4).

Volveremos a usar A1 como celda de entrada para los cálculos. Esta celda la utilizaremos en el segundo argumento de la función PAGO.

En E4:
=-PAGO(C3/12;A1;C2)    [Resultado: #¡NUM!]

Seleccionamos el rango E3:J4 y accedemos a Datos + Análisis Y si + Tabla de datos. Puesto que la lista de datos está en una fila, dejaremos en blanco la segunda entrada y pondremos A1 en la primera.

En la barra de fórmulas, Excel ha puesto: {=TABLA(A1;)}

El último caso que vamos a estudiar es el cálculo del pago mensual para diferentes tasas de interés y distintos períodos de tiempo. Lo haremos, como en los casos anteriores, primero, con fórmulas y, luego, con una Tabla de datos.

En G4:
=-PAGO($F4/12;G$3;$C$2)    [Resultado: 772,21]

Extendemos la fórmula al rango G4:L16

Veamos la solución con una Tabla de datos.

Cuando hay dos variables, la fórmula auxiliar de la Tabla de datos debe estar situada en el vértice superior izquierdo de las dos listas. En nuestro caso, la fórmula estará en F3. Además, necesitaremos dos celdas en blanco como celdas de entrada para los cálculos: una para las variables de la columna (será la A1) y otra para las variables de la fila (será la A2).

En F3:
=-PAGO(A1/12;A2;C2)    [Resultado: #¡NUM!]

Seleccionamos F3:L16 y accedemos a Datos + Análisis Y si + Tabla de datos. En el cuadro de diálogo Tabla de datos no podrá haber ninguna entrada vacía:

En la barra de fórmulas, Excel ha puesto una fórmula matricial con dos entradas: {=TABLA(A2;A1)}




3 comentarios:

  1. Excelente explicacion, me fue muy util, gracias.

    Saludos

    ResponderEliminar
  2. No entendí...¿Cómo saco el analisis de las tablas de frecuencia?

    ResponderEliminar
  3. Muy buenos ejemplos para entenderlo al 100%.
    Gracias

    ResponderEliminar