Tablas de datos en Excel 2013

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:

Fórmulas

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.

Tablas de datos en Excel

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

La pestaña Datos

Nos aparecerá la ventana Tabla de datos …

Datos de entrada(columna)

… 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.

Tablas de datos Excel 2013 - ejemplo 2

La tabla de datos es un poco diferente:

  1. 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.
  2. 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

Ysi_TablaDatos

Para obtener una copia del archivo Excel de trabajo AnalisisYsi_TablaDatos

Enrique es un apasionado de Excel y de las tecnologías. Colabora asiduamente en ExcelyVBA.com como experto en análisis de datos. También ha escrito sobre Dynamics AX y otros temas tecnológicos. Su experiencia en el ámbito financiero hace que tenga un enorme conocimiento de Excel y finanzas.

Uso de cookies

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información ACEPTAR

Aviso de cookies
Shares
Share This

Comparte este post

Comparte si te resulta útil la información.