Tabla de datos Excel: juega con diferentes escenarios

1. Lo básico de tabla de datos Excel

¿Qué es una tabla de datos Excel? Una tabla de datos Excel es un conjunto de celdas que combinadas nos entregan una información determinada.

¿Para qué sirve una tabla de datos Excel? Sirve para ver el cambio en los resultados cuando se modifican diferentes variables.

Puede hacer tablas de datos Excel de una variable o de dos variables, según la cantidad de parámetros que queramos cambiar.

2. Tabla de datos Excel de una variable

Una tabla de datos Excel de una variable es una tabla en donde se cambia únicamente un parámetro para ver el cambio en el resultado final.

Imaginemos el siguiente ejemplo, donde tenemos un presupuesto de $ 5.000 para comprar el pan. El kilo de pan cuesta $ 1.500. Inicialmente compramos solo un kilo de pan.

En la siguiente imagen se muestran los datos.

Queremos saber cuánto del presupuesto nos quedará luego de comprar un kilo de pan. Por lo tanto, realizamos el siguiente cálculo que se muestra a continuación, quedándonos $ 3500 de excedente.

Ahora queremos realizar este mismo cálculo cambiando la variable cantidad. Queremos ver cuánto excedente nos quedará si compramos 2 o más kilos de pan, sin tener que realizar este cálculo denuevo.

¿Cómo lo hacemos? 

Para esto creamos otra tabla. En la primera celda escribimos el excedente. Luego en la celda siguiente ponemos el resultado que deseamos ver, según cambios en la variable. En este caso, nuestro resultado es el excedente. Seleccionamos la celda C5 que contiene el excedente que ya habíamos sacado anteriormente, como muestra la imagen.

Después, en la primera columna de nuestra tabla, escribimos hacia abajo las alteraciones en la variable. En este caso, la variable a modificar es la cantidad, por lo tanto, ponemos los kilos de pan que podemos comprar: 1, 2, 3, 4 y 5, como muestra la imagen.

Para crear nuestra tabla, seleccionamos todo este rango.

Nos vamos a “Datos”, “Hipótesis de Análisis”, y seleccionamos “Tabla de datos” como se muestra a continuación.

Nos aparece el siguiente recuadro que debemos rellenar para crear nuestra tabla.

¿Cómo rellenar una tabla de datos Excel?

En las celdas de entrada se seleccionan las celdas que se quieren reemplazar por los nuevos valores de la tabla. Si los nuevos valores de la variable se escriben en una fila en la tabla de datos Excel, se selecciona la celda a sustituir en “Celda de entrada (fila)”. Si los nuevos valores de la variable se escriben en una columna en la tabla se selecciona la celda a sustituir en “Celda de entrada (columna)”.

En nuestro ejemplo, hemos puesto las nuevas cantidades (kilos de pan) en una columna en la tabla. Entonces pondremos la celda con el valor de las cantidades (C4) en “Celda de entrada (columnas)”, como muestra la imagen.

Así, los valores 1, 2, 3, 4, 5, reemplazarán el valor 1 en “Cantidad”, cambiando el valor en la fórmula de “Excedente” y cambiando así el resultado. Damos clic en “Aceptar”.

Nos aparece la tabla completa, con todos los resultados en los diferentes escenarios, ¡así de simple!

Y ¡sin necesidad de tener que calcular uno por uno!

Vemos los distintos excedentes en nuestra tabla, según la cantidad de kilos de pan que compremos.

En este caso por ejemplo, no vamos a poder comprar 4 o 5 kilos de pan, porque no nos alcanza el presupuesto.

3. Tabla de datos Excel de dos variables

Una tabla de datos Excel de dos variables es una tabla en donde se cambian dos parámetros a la vez para ver el cambio en el resultado final.

En nuestro ejemplo, imaginemos que el precio del kilo de pan tendrá una variación en el próximo mes, disminuyendo un 20%.

Para esto, calculamos el nuevo precio, como muestra la imagen. Nos queda $ 1200.

También debemos calcular el nuevo excedente con el precio nuevo, como se muestra a continuación. Nos queda $ 3800.

Imaginemos que en los próximos 5 meses el precio del kilo de pan tendrá distintas variaciones.

El próximo mes habrá una disminución del 20% en el precio, el mes siguiente del -10%, luego 0%, después habrá un aumento del 10% y finalmente del 20%.

Ahora queremos ver en nuestra tabla de datos Excel, cómo cambia nuestro excedente cambiando 2 parámetros; las cantidades y el precio del kilo de pan.

Hacemos una tabla con doble entrada.

En primer lugar, insertamos en la primera celda el valor del excedente que hemos tenido, como se muestra en la imagen.

En la primera columna de nuestra tabla de datos Excel, rellenamos hacia abajo el cambio en la variable 1: “Cantidad”.

Por otro lado, en la primera fila de nuestra tabla de datos Excel, rellenamos los cambios en la variable 2: “Variación Precio”.

Como se muestra a continuación.

Para completar nuestra tabla de datos Excel, seleccionamos todo este rango.

Vamos a “Datos”, “Análisis de datos”, “Tabla de Datos”.

Nos aparece el recuadro para rellenar la tabla.

¿Cómo rellenamos una tabla de datos Excel con dos variables?

En las celdas de entrada se seleccionan las celdas que serán reemplazadas por los nuevos valores de la tabla de datos Excel, según dónde se encuentren estos (filas o columnas).

En nuestro ejemplo queremos que el valor de “Variación Precio” de -20% sea reemplazado por -10%, 0%, 10% y 20% para mostrar cómo cambia el excedente.

Como pusimos estos valores en una fila en la tabla de datos Excel, pondremos en “Celda de entrada (fila)”, la celda con el valor de la variación en el precio C5 (color amarillo como muestra la imagen).

Al igual que el ejemplo anterior, tenemos los valores de las nuevas cantidades, en una misma columna en la tabla.

Por lo tanto, pondremos en “Celda entrada (columna)” la celda con el valor de la cantidad C4 (color rojo como muestra la imagen).

Damos clic en “Aceptar” y ¡nos queda la tabla de datos Excel con todas las variaciones y los diferentes resultados!

Podemos observar, por ejemplo, que el próximo mes vamos a poder comprar 3 kilos de pan sin ningún problema, quedándonos con 1400 como excedente y poder comprar algo más.

Pero en 5 meses más no vamos a poder comprar 3 kilos de pan porque el precio aumentará y no nos alcanzará el presupuesto, quedándonos con excedente negativo -400. En ese caso tendríamos que comprar menos de 3 kilos.

Así podemos ver, en una simple tabla de datos Excel obtenida de manera rápida, distintos escenarios con cambios en las variables. Podemos comparar, analizar y sacar conclusiones de estos resultados.

Tip Ninja: Al ser una fórmula de matriz, si intentas eliminar un solo resultado en la tabla, Excel no te lo permitirá. Por lo tanto, para eliminar los resultados dentro de una tabla, debes eliminar todo el rango y suprimir.

Preguntas Frecuentes