Cómo crear una tabla de datos de una variable en Excel 2016

Las tablas de datos le permiten ingresar una serie de valores posibles que Excel 2016 luego conecta en una sola fórmula. Una tabla de datos de una variable sustituye una serie de valores posibles por un único valor de entrada en una fórmula.

La siguiente figura muestra una hoja de cálculo de proyecciones de ventas de 2017 para la cual se creará una tabla de datos de una variable. En esta hoja de trabajo, el monto de ventas proyectado en la celda B5 se calcula sumando el total de ventas del año pasado en la celda B2 al monto que se espera que crezca en 2017 (calculado multiplicando el total del año pasado en la celda B2 por el porcentaje de crecimiento en la celda B3 ), dándote la fórmula

Hoja de cálculo de proyección de ventas con una columna de posibles porcentajes de crecimiento para insertar en una tabla de datos de una variable.

= B2 + (B2 * B3)

Debido a que hizo clic en el botón de comando Crear a partir de la selección en la pestaña Fórmulas de la cinta después de hacer A2: B5 la selección y aceptó la casilla de verificación Columna izquierda predeterminada, la fórmula usa los encabezados de fila en la columna A y dice:

= Ventas_2016 + (Ventas_2016 * Crecimiento_2017)

Como puede ver en esta figura, se ingresó una columna de posibles tasas de crecimiento que van desde el 1% hasta el 5.0% en la columna B en el rango B8: B24.

Hoja de cálculo de proyección de ventas después de crear la tabla de datos de una variable en el rango C8: C24.

Para crear la tabla de datos de una variable que se muestra que incluye cada uno de estos valores en la fórmula de crecimiento de las ventas, siga estos sencillos pasos:

Copie la fórmula original ingresada en la celda B5 en la celda C7 escribiendo = (igual a) y luego haciendo clic en la celda B5 para crear la fórmula = Projected_Sales_2017.

La copia de la fórmula original (para sustituir la serie de diferentes tasas de crecimiento en B8: B24 en) es ahora el encabezado de columna de la tabla de datos de una variable.

Seleccione el rango de celdas B7: C24.

El rango de la tabla de datos incluye la fórmula junto con las distintas tasas de crecimiento.

Haga clic en Datos → Análisis Y si ... → Tabla de datos en la cinta.

Excel abre el cuadro de diálogo Tabla de datos.

Haga clic en el cuadro de texto Celda de entrada de columna en el cuadro de diálogo Tabla de datos y luego haga clic en la celda B3, la celda Growth_2017 con el porcentaje original, en la hoja de trabajo.

Excel inserta la dirección de celda absoluta, $ B $ 3, en el cuadro de texto Celda de entrada de columna.

Haga clic en Aceptar para cerrar el cuadro de diálogo Tabla de datos.

Tan pronto como haga clic en Aceptar, Excel crea la tabla de datos en el rango C8: C24 ingresando una fórmula usando su función TABLE en este rango. Cada copia de esta fórmula en la tabla de datos usa el porcentaje de la tasa de crecimiento en la misma fila en la columna B para determinar el posible resultado.

Haga clic en la celda C7, luego haga clic en el botón de comando Copiar formato en el grupo Portapapeles en la pestaña Inicio y arrastre a través del rango de celdas C8: C24.

Excel copia el formato del número de contabilidad al rango de posibles resultados calculados por esta tabla de datos.

Un par de cosas importantes a tener en cuenta sobre la tabla de datos de una variable creada en esta hoja de cálculo:

• Si modifica cualquier porcentaje de tasa de crecimiento en el rango de celdas B8: B24, Excel actualiza inmediatamente el resultado de ventas proyectado asociado en la tabla de datos. Para evitar que Excel actualice la tabla de datos hasta que haga clic en el botón de comando Calcular ahora (F9) o Calcular hoja (Mayús + F9) en la pestaña Fórmulas, haga clic en el botón Opciones de cálculo en la pestaña Fórmulas y luego haga clic en Automático excepto para tablas de datos opción (Alt + MXE).

• Si intenta eliminar cualquier fórmula de TABLA en el rango de celdas C8: C24, Excel muestra una alerta No se puede cambiar parte de una tabla de datos. Debe seleccionar todo el rango de fórmulas (C8: C24 en este caso) antes de presionar Eliminar o hacer clic en el botón Borrar o Eliminar en la pestaña Inicio.