Las tablas de datos en Excel son un tipo de análisis “y si” que, siendo un aspecto muy potente de Excel es, quizá de los menos utilizados de manera sistemática. Básicamente es el clásico análisis de sensibilidad de algo con respecto a algunas variables. Nos brinda la posibilidad de ver cómo cambia el resultado devuelto por una fórmula cuando varío otra u otras. Si usas nombres de rango (aunque el rango sea de una sóla celda) en tus fórmulas o/y referencias a otras celdas, ya puedes hacer uso del análisis y si.
Análisis “y si” manual
Un ejemplo: necesito calcular los intereses anuales de un préstamo que estoy pidiendo por 5.000 euros. El tipo de interés estará en torno al 5% anual. Podría hacer simplemente = 5.000 x 5 / 100 y ya tengo el resultado. Pero también podría hacer:
Me lo conceden al 6% y por 4.500 euros. Lo único que tendría que hacer es modificar las cifras en B1 y B3 y ya tengo el resultado de nuevo. Todo bien clarito.
Bueno, esto, normalmente, seguro que ya lo hacemos. Subamos al siguiente escalón.
Tablas de datos en Excel – Ejemplo 1
Las tablas de datos en Excel son rangos dinámicos en las que vemos cómo varía un resultado con los cambios de un dato o de dos. Vamos a verlo con más detalle.
Supongamos que una empresa vende 140.000 euros, con un coste de ventas de 30% y gastos generales por 74.588 euros. Quiere saber cómo afecta al beneficio calculado según los datos anteriores, diversos cambios en el coste de ventas.
Se trata de una tabla de datos con una entrada porque sólo varía el coste de ventas, el beneficio lo hace como consecuencia de esa variación. Primero he preparado con las fórmulas necesarias en las celdas de la izquierda. A continuación veremos cómo preparar la tabla de datos de la derecha.
Ponemos en un rango los diferentes porcentajes de coste de ventas con los que queremos trabajar. En el ejemplo las celdas F6 a F16. En la celda G5 pondremos =C13. La celda F5 la dejamos vacía. Seleccionamos el rango completo que va desde la celda F5 a la G16.
Ahora vamos a la pestaña Datos y elegimos Análisis de hipótesis – tablas de datos en el grupo Herramientas de datos
Nos aparecerá la ventana Tabla de datos …
… y en la opción Celda de entrada (columna) pondremos =C8 que es donde tenemos el elemento que varía, o sea, Coste de ventas. Pulsamos Aceptar y Excel rellena automáticamente con una fórmula matricial las celdas del rango G6 a G16 con el cálculo del beneficio según el coste de ventas que aparece a la izquierda.
- ¿Cómo sabe Excel que queremos que calcule cómo varía el beneficio? Porque se lo hemos dicho en la celda G5.
- Como es natural, si el elemento que varía estuviera en una fila, habría que rellenar la opción Celda de entrada (fila).
No os preocupéis. Hacedlo una vez siguiendo estas instrucciones y veréis que es más sencillo de hacer que de contar.
Tablas de datos en Excel – Ejemplo 2
He preparado este otro ejercicio. Más o menos, continuación del anterior. Aquí veremos cuales son los requerimientos de Excel cuando tiene que crear una tabla de datos de dos elementos (hay dos conceptos que varían): en este caso, seguirá cambiando el coste de ventas e introduciremos el tipo de cambio como variable a tener en cuenta en el cálculo del beneficio en una moneda distinta de la de origen, por ejemplo, el euro.
La tabla de datos es un poco diferente:
- La celda F4 ya no está vacía, contiene una referencia a la celda C13 (=C13) donde está la fórmula que calcula el beneficio.
- En el rango G4 a L4 he puesto los valores del tipo de cambio que quiero analizar.
En la ventana Tabla de datos (que se habría desde Datos – Análisis de datos – Tabla de datos) ahora deberemos también rellenar la opción Celda de entrada(fila) con la referencia a la celda del tipo de cambio, que es el segundo elemento que varía
Para obtener una copia del archivo Excel de trabajo AnalisisYsi_TablaDatos