Calcular varios resultados con una tabla de datos

Nota: Nos gustaría proporcionarle el contenido de ayuda actual lo más rápido posible en su idioma. Esta página se ha traducido mediante un sistema automático y es posible que contenga imprecisiones o errores gramaticales. Nuestro objetivo es que este contenido sea útil para usted. ¿Puede informarnos sobre si la información le ha sido útil al final de la página? Aquí tiene el artículo en inglés para que pueda consultarlo fácilmente.

Una tabla de datos es un rango de celdas en el que puede cambiar los valores de algunos en algunas de las celdas y dar con distintas respuestas a un problema. Un buen ejemplo de una tabla de datos utiliza la función pago con importes de préstamo diferentes y las tasas de interés para calcular la cantidad de un préstamo hipotecario asequible. Experimentar con diferentes valores para observar la variación correspondiente en los resultados es una tarea común de análisis de datos.

En Microsoft Excel, las tablas de datos son parte de un conjunto de comandos conocidos como herramientas de análisis de hipótesis. Cuando se crea y analizar tablas de datos, va a realizar análisis de hipótesis.

Análisis y si es el proceso de cambiar los valores de las celdas para ver cómo afectará el resultado de las fórmulas en la hoja de cálculo a esos cambios. Por ejemplo, puede usar una tabla de datos para variar la tasa de interés y la duración del plazo de un préstamo, para evaluar posibles cantidades de pago mensual.

Nota: Puede realizar cálculos más rápido con tablas de datos y Visual Basic para aplicaciones (VBA). Para obtener más información, vea tablas de datos de Excel hipótesis: cálculo más rápido con VBA.

Tipos de análisis de hipótesis   

Existen tres tipos de herramientas de análisis de hipótesis en Excel: escenarios, tablas de datosy Buscar objetivo. Tablas de datos y escenarios use conjuntos de valores de entrada para calcula los resultados posibles. Buscar objetivo es claramente diferenciados, se utiliza un único resultado y calcula los valores de entrada posibles que podrían producir ese resultado.

Al igual que los escenarios, las tablas de datos le ayudan a explorar un conjunto de resultados posibles. A diferencia de los escenarios, las tablas de datos muestran todos los resultados en una tabla en una hoja de cálculo. El uso de tablas de datos permite examinar fácilmente una variedad de posibilidades de un vistazo. Como es posible centrarse en sólo 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, debe utilizar en su lugar escenarios. Aunque está limitado a sólo 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 de variables diferentes como se desee. Un escenario puede tener un máximo de 32 valores diferentes, pero se pueden crear tantos escenarios como se desee.

Más información en el artículo Introducción a análisis de hipótesis.

Crear en tablas de datos de uno o dos variables, dependiendo del número de variables y fórmulas que necesite probar.

Tablas de datos de una variable   

Usar una tabla de datos de una variable si desea ver cómo diferentes valores de una variable en uno o más fórmulas afectará a los resultados de las fórmulas. Por ejemplo, puede usar una tabla de datos de una variable para ver cómo los diferentes tipos de interés afectan a un pago mensual de una hipoteca con la función pago. Escriba los valores de variables en una columna o fila, y se muestran los resultados en una fila o columna adyacente.

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

Tabla de datos con una variable

Tabla de datos de dos variables   

Use una tabla de datos de dos variables para ver cómo diferentes valores de dos variables en una fórmula cambiarán los resultados de la misma. Por ejemplo, puede usar una tabla de datos de dos variables para ver cómo diferentes combinaciones de tipos de interés y términos de préstamos afectarán al 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.

Tabla de datos con dos variables

Cálculos de las tablas de datos   

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

Una tabla de datos de una variable contienen sus valores de entrada en una sola columna (orientación de columna) o en una fila (orientación de fila). Cualquier fórmula en una tabla de datos de una variable deberá hacer referencia a un único celda de entrada.

Siga estos pasos:

  1. Escriba la lista de valores que desea sustituir en la celda de entrada, debajo de una columna o en una fila. Dejar algunas filas y columnas vacías en ambos lados de los valores.

  2. Siga uno de estos procedimientos:

    • Si la tabla de datos está orientada a columnas (son de los valores de variables en una columna), escriba la fórmula en la fila de una celda encima y una celda a la derecha de la columna de valores. Esta tabla de datos de una variable está orientada a columnas y ocupa la fórmula en la celda D2.

      Tabla de datos con una variable

      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 por filas (son de los valores de variables en una fila), escriba la fórmula en la columna de una celda a la izquierda del primer valor y una celda por debajo de la fila de valores.

      Si desea examinar los efectos de varios valores en otras fórmulas, escriba las fórmulas adicionales en las celdas de debajo de la primera fórmula.

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

  4. En la ficha datos, haga clic en análisis de hipótesis >  Tabla de datos (en el grupo Herramientas de datos o grupo de 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 para la celda de entrada en el campo de la celda de entrada de columna. En la figura anterior, la celda de entrada es B3.

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

      Nota: Después de crear la tabla de datos, desea cambiar el formato de las celdas de resultado. En la ilustración, las celdas de resultado son con formato de moneda.

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

Siga estos pasos

  1. Realice una de las siguientes opciones:

    • 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 por filas, escriba la nueva fórmula en una celda vacía situada 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 fórmula nueva.

  3. En la ficha datos, haga clic en Análisis de hipótesis > Tabla de datos (en el grupo Herramientas de datos o grupo de previsión de Excel 2016 ).

  4. Siga uno estos pasos:

    • 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 por 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 la que se especifican los valores iniciales fórmulas en las celdas B3, B4 y B5, escriba la fórmula =PMT(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 los distintos tipos 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 de préstamo (en meses) en las celdas D2 y E2.

  4. Seleccione el rango de celdas que contiene la fórmula (C2), tanto la fila como 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 grupo de previsión (en Excel 2016 ), haga clic en análisis de hipótesis >  Tabla de datos (en el grupo Herramientas de datos o grupo de previsión de Excel 2016 ).

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

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

  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 las diferentes combinaciones de tasas de interés y plazos del préstamo al pago mensual de una hipoteca. En la ilustración, la celda C2 contiene la fórmula de pago, =PMT(B3/12,B4,-B5), que usa dos celdas de entrada, B3 y B4.

Tabla de datos con dos variables

Cuando se establece esta opción de cálculo, sin cálculos de tabla de datos se producen cuando se realiza una actualización en todo el libro. Para volver a calcular manualmente la tabla de datos, seleccione sus fórmulas y, a continuación, presione F9.

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

  1. Siga uno estos pasos:

    • En Excel 2007, haga clic en el botón Microsoft Office Imagen del botón de Microsoft Office , haga clic en Opciones de Excel y, después, haga clic en la categoría Fórmulas.

    • En las demás versiones, haga clic en archivo > Opciones > fórmulas.

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

    Sugerencia: Opcionalmente, en la ficha fórmulas, haga clic en la flecha en Opciones de cálculo y luego 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 mayores de datos variables.

Búsqueda de objetivos

Si conoce el resultado que va a pasar de una fórmula, pero no sabe exactamente con el valor de entrada la fórmula debe obtener dicho resultado, use la característica Buscar objetivo. Vea el artículo Usar que buscar objetivo para encontrar el resultado deseado ajustando un valor de entrada.

Solver de Excel

Puede usar el complemento Solver de Excel para buscar el valor óptimo de un conjunto de variables de entrada. Solver funciona con un grupo de celdas (denominado variables de decisión o celdas de variables simplemente) que se usan en el cálculo de las fórmulas en las celdas de la restricción y objetivo. Solver ajusta los valores en las celdas de variables de decisión para cumplir con los límites de las celdas de la restricción y producir el resultado que desee para la celda objetivo. Más información en este artículo: definir y solucionar un problema con Solver.

¿Necesita más ayuda?

Siempre puede preguntar a un experto en Excel Tech Community, obtener soporte técnico en la Comunidad de respuestas o sugerir una característica nueva o mejora en el UserVoice de Excel.

Ampliar sus conocimientos de Office
Explorar los cursos
Obtener nuevas características primero
Únase a los participantes de Office Insider

¿Le ha sido útil esta información?

¡Gracias por sus comentarios!

Gracias por sus comentarios. Quizá le interese ponerse en contacto con uno de nuestros agentes de soporte de Office.

×