Calcular varios resultados mediante una tabla de datos

En Microsoft Excel, las tablas de datos forman parte de un conjunto de comandos conocido como herramientas de análisis What-If. Al construir y analizar tablas de datos, está realizando análisis de hipótesis.

El análisis de hipótesis es el proceso de cambiar los valores de las celdas para ver cómo afectarán esos cambios al resultado de las fórmulas de la hoja de cálculo. Por ejemplo, puede usar una tabla de datos para variar la tasa de interés y la duración del período de un préstamo con el fin de evaluar los posibles importes de los pagos mensuales.

Tipos de análisis de hipótesis    

Existen tres tipos de herramientas de análisis de hipótesis en Excel: escenarios, tablas de datos y búsqueda de objetivos. Los escenarios y las tablas de datos usan conjuntos de valores de entrada para calcular posibles resultados. Buscar objetivo es claramente diferente, usa un único resultado y calcula los posibles valores de entrada que producirían ese resultado.

Al igual que los escenarios, las tablas de datos le ayudan a explorar un conjunto de posibles resultados. A diferencia de los escenarios, las tablas de datos muestran todos los resultados en una tabla de una hoja de cálculo. El uso de tablas de datos facilita examinar una amplia variedad de posibilidades de un vistazo. Dado que se centran en únicamente una o dos variables, los resultados son fáciles de leer y compartir en formato tabular.

Una tabla de datos no puede dar cabida a más de dos variables. Si desea analizar más de dos variables, en su lugar debe usar escenarios. Aunque está limitado solo a una o dos variables (una para la celda de entrada de fila y otra para la celda de entrada de columna), una tabla de datos puede incluir tantos valores variables diferentes como desee. Un escenario puede tener un máximo de 32 valores diferentes, pero puede crear tantos escenarios como desee.

Obtenga más información en el artículo Introducción al análisis de What-If.

Cree tablas de datos de una o dos variables, según el número de variables y fórmulas que necesite probar.

Tablas de datos de una variable    

Use una tabla de datos de una variable si desea ver cómo los diferentes valores de una variable en una o más fórmulas cambiarán los resultados de esas fórmulas. Por ejemplo, puede usar una tabla de datos de una variable para ver cómo afectan los distintos tipos de interés al pago mensual de una hipoteca mediante la función PAGO. Escriba los valores de la variable en una columna o fila y los resultados se mostrarán en una columna o fila adyacente.

En la siguiente ilustración, la celda D2 contiene la fórmula de pago, =PAGO(B3/12,B4,-B5), que hace referencia a la celda de entrada B3.

Tablas de datos de dos variables    

Use una tabla de datos de dos variables para ver cómo los diferentes valores de dos variables en una fórmula cambiarán los resultados de esa fórmula. Por ejemplo, puede usar una tabla de datos de dos variables para ver cómo afectarán las distintas combinaciones de tasas de interés y términos de préstamo al pago mensual de una hipoteca.

En la siguiente ilustración, la celda C2 contiene la fórmula de pago, =PAGO(B3/12,B4,-B5), que usa dos celdas de entrada, B3 y B4.

 

Cálculos de tablas de datos    

Siempre que se actualice una hoja de cálculo, las tablas de datos también se recalcularán, incluso si no se ha producido ningún cambio en los datos. Para acelerar el cálculo de una hoja de cálculo que contiene una tabla de datos, puede cambiar las opciones de cálculo para volver a calcular automáticamente la hoja de cálculo, pero no las tablas de datos. Para obtener más información, vea la sección Acelerar el cálculo en una hoja de cálculo que contiene tablas de datos.

Una tabla de datos de una variable contiene sus valores de entrada en una sola columna (orientada a columnas) o en una fila (orientada a filas). Cualquier fórmula de una tabla de datos de una variable solo debe hacer referencia a una celda de entrada.

Siga estos pasos:

1. Escriba la lista de valores que desea sustituir en la celda de entrada, ya sea hacia abajo una columna o a través de una fila. Deje algunas filas y columnas vacías en cualquiera de los lados de los valores.

2. Siga uno de estos procedimientos:

   • Si la tabla de datos está orientada a columnas (los valores de variables están en una columna), escriba la fórmula en la celda una fila encima y una celda a la derecha de la columna de valores. Esta tabla de datos de una variable está orientada a columnas y la fórmula se encuentra en la celda D2.
     
     
     
     Si desea examinar los efectos de varios valores en otras fórmulas, escriba las fórmulas adicionales en las celdas a la derecha de la primera fórmula.

   • Si la tabla de datos está orientada a filas (los valores variables están en una fila), escriba la fórmula en la celda una columna a la izquierda del primer valor y una celda debajo de la fila de valores.
     
     Si desea examinar los efectos de varios valores en otras fórmulas, escriba las fórmulas adicionales en celdas debajo de la primera fórmula.

3. Seleccione el rango de celdas que contiene las fórmulas y valores que desea sustituir. En la ilustración anterior, este rango es C2:D5.

4. En la pestaña Datos , haga clic en Análisis de hipótesis > tabla de datos (en el grupo Herramientas de datos o en el grupo Previsión de Excel 2016 ).

5. Siga uno de estos procedimientos:

   • Si la tabla de datos está orientada a columnas, escriba la referencia de celda de la celda de entrada en el campo Celda de entrada de columna. En la ilustración anterior, la celda de entrada es B3.

   • Si la tabla de datos está orientada a filas, escriba la referencia de celda para la celda de entrada en el campo Celda de entrada de fila.

     Nota: Después de crear la tabla de datos, es posible que desee cambiar el formato de las celdas de resultado. En la ilustración, las celdas de resultado tienen formato de moneda.

Las fórmulas que se usan en una tabla de datos de una variable deben hacer referencia a la misma celda de entrada.

Siga estos pasos

1. Realice una de las siguientes acciones:

   • Si la tabla de datos está orientada a columnas, escriba la nueva fórmula en una celda en blanco a la derecha de una fórmula existente en la fila superior de la tabla de datos.

   • Si la tabla de datos está orientada a filas, escriba la nueva fórmula en una celda vacía debajo de una fórmula existente en la primera columna de la tabla de datos.

2. Seleccione el rango de celdas que contiene la tabla de datos y la nueva fórmula.

3. En la pestaña Datos , haga clic en Análisis de hipótesis > tabla de datos (en el grupo Herramientas de datos o en el grupo Previsión de Excel 2016 ).

4. Realiza una de las siguientes acciones:

   • Si la tabla de datos está orientada a columnas, escriba la referencia de celda para la celda de entrada en el cuadro Celda de entrada de columna.

   • Si la tabla de datos está orientada a filas, escriba la referencia de celda para la celda de entrada en el cuadro Celda de entrada de fila.

Una tabla de datos de dos variables usa una fórmula que contiene dos listas de valores de entrada. La fórmula debe hacer referencia a dos celdas de entrada diferentes.

Siga estos pasos:

1. En una celda de la hoja de cálculo, escriba la fórmula que hace referencia a las dos celdas de entrada.

   En el ejemplo siguiente, en el que se escriben los valores iniciales de la fórmula en las celdas B3, B4 y B5, escriba la fórmula =PAGO(B3/12,B4,-B5) en la celda C2.

2. Escriba una lista de valores de entrada en la misma columna, debajo de la fórmula.

   En este caso, escriba las diferentes tasas de interés en las celdas C3, C4 y C5.

3. Escriba la segunda lista en la misma fila que la fórmula, a su derecha.

   Escriba los términos del préstamo (en meses) en las celdas D2 y E2.

4. Seleccione el rango de celdas que contiene la fórmula (C2), la fila y la columna de valores (C3:C5 y D2:E2) y las celdas en las que desea los valores calculados (D3:E5).

   En este caso, seleccione el rango C2:E5.

5. En la pestaña Datos , en el grupo Herramientas de datos o en el grupo Previsión (en Excel 2016 ), haga clic en Análisis de hipótesis > Tabla de datos (en el grupo Herramientas de datos o en el grupo Previsión de Excel 2016 ).

6. En el campo Celda de entrada de fila, escriba la referencia a la celda de entrada para los valores de entrada en la fila.
   Escriba la celda B4 en el cuadro Celda de entrada de fila .

7. En el campo Celda de entrada de columna, escriba la referencia a la celda de entrada para los valores de entrada en la columna.
   Escriba B3 en el cuadro Celda de entrada de columna .

8. Haga clic en Aceptar.

Ejemplo de una tabla de datos de dos variables

Una tabla de datos de dos variables puede mostrar cómo afectarán las distintas combinaciones de tasas de interés y términos de préstamo a un pago mensual de una hipoteca. En la ilustración siguiente, la celda C2 contiene la fórmula de pago, =PAGO(B3/12,B4,-B5), que usa dos celdas de entrada, B3 y B4.

Al establecer esta opción de cálculo, no se realizan cálculos de tabla de datos cuando se realiza un nuevo cálculo en todo el libro. Para volver a calcular manualmente la tabla de datos, seleccione las fórmulas y presione F9.

Siga estos pasos para mejorar el rendimiento del cálculo:

1. Haga clic en Opciones de > dearchivo > Fórmulas.

2. En la sección Opciones de cálculo , en Calcular, haga clic en Automático excepto para tablas de datos.

   Sugerencia: Opcionalmente, en la pestaña Fórmulas , haga clic en la flecha en Opciones de cálculo y, a continuación, haga clic en Automático excepto tablas de datos (en el grupo Cálculo ).

Puede usar otras herramientas de Excel para realizar análisis de hipótesis si tiene objetivos específicos o conjuntos más grandes de datos variables.

Búsqueda de objetivos

Si sabe cuál es el resultado esperado de una fórmula, pero no sabe con exactitud qué valor de entrada necesita la fórmula para obtenerlo, use la característica Goal-Seek. Vea el artículo Usar Buscar objetivo para encontrar el resultado que desea ajustando un valor de entrada.

Excel Solver

Puede usar el complemento Solver de Excel para encontrar el valor óptimo para un conjunto de variables de entrada. Solver funciona con un grupo de celdas (denominadas variables de decisión o, simplemente, celdas de variables) que se usan para calcular las fórmulas en las celdas objetivo y de restricción. Solver ajusta los valores de las celdas de variables de decisión para que cumplan con los límites de las celdas de restricción y den el resultado deseado en la celda objetivo. Obtenga más información en este artículo: Definir y solucionar un problema con Solver.