Tablas de datos en Excel (2 variables)

Como decíamos en el post "Análisis de datos en Excel", llamaremos "Tablas de datos" a una utilidad mediante la cual podemos obtener los resultados que arrojaría una operación en caso de que una o dos de las variables implicadas en dicha operación tomaran distintos valores. En este artículo se estudiarán las Tablas de datos con 2 variables.

Conozcamos pues, cómo utilizar las Tablas de datos en Excel:

A continuación, vemos una tabla de datos completada:

Vemos una tabla en la que para distintos valores de ventas (encabezados de columnas) que pudieran conseguir los comerciales de una empresa, se pagarían distintos importes (en rojo), según se aplique un porcentaje de comisión u otro (encabezados de filas). Excel rellena automáticamente todos los importes en rojo gracias a la aplicación de esta forma de análisis dinámico sobre:

• Los encabezados de columna, en los que ponemos a nuestra elección los valores que podrían tomar las ventas de los comerciales
• Los encabezados de fila, en los que ponemos a nuestra elección los porcentajes de comisión que la empresa podría pagar
• 2 celdas (sobreadas en verde), cuyos valores no son relevantes para el cálculo de la "Tabla de datos", de hecho, podrían estar vacías o tener cualquier valor. Lo que si es importante es su situación, pues estas celdas deben estar localizadas y fuera del ámbito de la Tabla de datos que queremos conseguir. El cometido de estas celdas es servir únicamente de referencia a Excel.
• 1 celda (sombreada para la ocasión en naranja), que debe tener una formula mediante la cual, Excel pueda saber qué relación tienen las celdas verdes.

¿Cómo se hace?

La forma de conseguir que Excel rellene automáticamente todos los importes que habría que pagar a los comerciales según porcentaje de comisión y según nivel de ventas, es la siguiente:

1) Introducimos en la celda naranja (DEBE ESTAR EN LA POSICIÓN QUE VEMOS EN LA IMAGEN, ES DECIR, EN LA ESQUINA SUPERIOR IZQUIERDA DE LA TABLA QUE DESEAMOS FORMAR) una fórmula que relacione de la forma que queramos a las celdas de color verde (en la imagen de abajo, podemos ver que la relación es promedio de ventas x % de comisión). Como ya se ha dicho antes, es indistinto qué valores contienen las celdas verdes, puesto que lo importante es tenerlas identificadas, fuera del rango de la Tabla de datos y que la celda naranja las relacione.

2) Seleccionamos todo el rango de la tabla que se formará:

3) Seleccionamos la "Ficha Datos", el botón "Análisis Y si" que hay dentro del grupo "Herramientas de datos", y después seleccionamos la opción "Tabla de datos...".

4) Una vez hecho, aparecerá un cuadro de diálogo en el que debemos hacer la siguiente referencia a las filas:

5) Después, hacemos la referencia a las columnas:

6) A aceptamos.... con el consiguiente resultado que veíamos al principio del artículo:

¿Otras opciones similares?

El mismo resultado que obtenemos con el análisis "Y si" de Excel, lo podemos conseguir introduciendo una fórmula en una de las celdas y haciendo uso de "Autorrellenar" y con la precaución de utilizar las "Referencias relativas" necesarias en las celdas. Por ejemplo, en la celda que une la columna 50.000 € y la fila 1 %, podríamos poner:  =$B9*C$8  y arrastrar hacia abajo y hacia la derecha para conseguir un "autorrellenado", teniendo al final el mismo resultado:

Artículos relacionados:

Análisis de datos en Excel
Administrar escenarios en Excel
La utilidad "Buscar objetivo" en Excel

  José Manuel Pomares Medrano