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 las que puede cambiar los valores de algunas de las celdas y obtener respuestas diferentes a un problema. Un buen ejemplo de una tabla de datos emplea la función pago con diferentes cantidades de préstamos y tasas de interés para calcular la cantidad económica de un préstamo hipotecario. Experimentar con diferentes valores para observar la variación correspondiente en los resultados es una tarea común en el análisis de datos.

En Microsoft Excel, las tablas de datos forman parte de un conjunto de comandos denominado herramientas de análisis y si. Al construir y analizar tablas de datos, está haciendo el análisis de hipótesis.

Análisis de hipótesis es el proceso de cambiar los valores de las celdas para ver cómo dichos cambios afectarán 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 de un préstamo, para evaluar posibles montos de pago mensual.

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

Tipos de análisis de hipótesis    

Hay tres tipos de herramientas de análisis de hipótesis en Excel: escenarios, tablas de datosy búsqueda de objetivos. Escenarios y tablas de datos use conjuntos de valores de entrada para calcular los posibles resultados. La búsqueda de objetivo es claramente diferente, usa un único resultado y calcula los posibles valores de entrada que generarían ese resultado.

Como en 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 de una tabla en una hoja de cálculo. El uso de tablas de datos facilita la tarea de examinar una serie 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 contener más de dos variables. Si desea analizar más de dos variables, debe usar escenarios. Aunque solo se limite 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 de variable diferentes como desee. Un escenario puede tener un máximo de 32 valores diferentes, pero puede crear tantos escenarios como desee.

Para obtener más información, vea el artículo Introducción a análisis de hipótesis.

Cree tablas de datos de una o dos variables, dependiendo del 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 distintos valores de una variable de 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 las diferentes tasas de interés a un pago de hipoteca mensual 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.

Tabla de datos con una variable

Tablas 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 esa fórmula. Por ejemplo, puede usar una tabla de datos de dos variables para ver cómo las diferentes combinaciones de tasas de interés y condiciones del préstamo afectarán 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.

Tabla de datos con dos variables
 

Cálculos de tabla de datos    

Cada vez que se actualice una hoja de cálculo, las tablas de datos también volverán a calcularse, incluso si no ha habido cambios 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 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 contiene sus valores de entrada en una sola columna (orientación por columna) o en una fila (orientación de fila). Cualquier fórmula de una tabla de datos de una variable debe hacer referencia a un solo 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 una fila. Deje algunas filas y columnas vacías a cada lado de los valores.

  2. Siga uno de estos procedimientos:

    • Si la tabla de datos está orientada a columna (los valores de las variables están en una columna), escriba la fórmula en la celda situada 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 está incluida 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 situadas a la derecha de la primera fórmula.

    • Si la tabla de datos está orientada a fila (los valores de las variables están en una fila), escriba la fórmula en la celda situada una columna 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 situadas 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 ilustración anterior, este intervalo es C2: D5.

  4. En la pestaña datos , haga clic entabla de datos de análisis de hipótesis _GT_(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 el 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 fila, escriba la referencia de celda de la celda de entrada en el campo celda de entrada (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 el 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. Siga uno de estos pasos:

    • Si la tabla de datos está orientada a columnas, escriba la nueva fórmula en una celda en blanco situada 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 fila, 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 tabla de datos de análisis de hipótesis > (en el grupo herramientas de datos o en el grupo previsión de Excel 2016 ).

  4. Lleve a cabo 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 cuadro celda de entrada (columna ).

    • Si la tabla de datos está orientada a fila, escriba la referencia de celda de la celda de entrada en el cuadro celda de entrada (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 siguiente ejemplo, 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), tanto la fila como la columna de valores (C3: C5 y D2: E2), así como 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 entabla de datos de análisis y si >(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 de la fila.
    Escriba la celda B4 en el cuadro celda de entrada (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 (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 las distintas combinaciones de tasas de interés y condiciones del préstamo afectarán al pago mensual de una hipoteca. En la 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.

Tabla de datos con dos variables

Cuando se establece esta opción de cálculo, no se producen 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 sus fórmulas y, a continuación, presione F9.

Siga estos pasos para mejorar el rendimiento de los cálculos:

  1. Lleve a cabo uno de estos procedimientos:

    • En Excel 2007, haga clic en el botón Microsoft Office Imagen del botón de Microsoft Office , haga clic en Opciones de Excely, a continuación, haga clic en la categoría fórmulas .

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

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

    Sugerencia: De forma opcional, en la pestaña fórmulas, haga clic en la flecha de Opciones de cálculoy, a continuación, haga clic en automático excepto en las 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 el resultado de una fórmula, pero no sabe exactamente qué valor de entrada necesita la fórmula para obtener ese resultado, use la característica de búsqueda de objetivo. Vea el artículo sobre cómo usar Buscar objetivo para encontrar el resultado que desea ajustando un valor de entrada.

Solver de Excel

Puede usar el complemento Solver de Excel para buscar 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 variables) que se usan en el cálculo de las fórmulas de las celdas objetivo y 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. Para obtener más información, vea este artículo: definir y resolver un problema con Solver.

Al conectar números diferentes en una celda, puede encontrar rápidamente las diferentes respuestas a un problema. Un excelente ejemplo es el uso de la función pago con diferentes tasas de interés y períodos de préstamo (en meses) para calcular la cantidad de préstamo que puede permitirse para una casa o un coche. Escriba los números en un rango de celdas denominado tabla de datos.

Aquí, la tabla de datos es el rango de celdas B2: D8. Puede cambiar el valor de B4, el importe del préstamo y los pagos mensuales de la columna D actualizar automáticamente. Usando una tasa de interés de 3,75%, D2 devuelve un pago mensual de $1.042,01 con esta fórmula: = PMT (C2/12, $B $3, $B $4).

Este rango de celdas, B2:D8, es una tabla de datos

Puede usar una o dos variables, dependiendo del número de variables y fórmulas que desee probar.

Use una prueba de una sola variable para ver cómo los distintos valores de una variable de una fórmula cambiarán los resultados. Por ejemplo, puede cambiar la tasa de interés para un pago de hipoteca mensual mediante la función pago. Escriba los valores de la variable (las tasas de interés) en una columna o fila, y los resultados se mostrarán en una columna o fila cercana.

En este libro en vivo, la celda D2 contiene la fórmula de pago =pago (C2/12, $B $3, $B $4). La celda B3 es la celda variable , donde puede conectar una longitud de términos diferente (número de períodos de pago mensuales). En la celda D2, la función pago se conecta en la tasa de interés 3,75%/12, 360 meses y un préstamo de $225.000, y calcula una $1.042,01 de pago mensual.

Use una prueba de dos variables para ver cómo diferentes valores de dos variables en una fórmula cambiarán los resultados. Por ejemplo, puede probar distintas combinaciones de tasas de interés y números de períodos de pago mensuales para calcular el pago de una hipoteca.

En este libro en vivo, la celda C3 contiene la fórmula de pago, =pago ($B $3/12, $B $2, B4), que usa dos celdas variables, B2 y B3. En la celda C2, la función pago se conecta en la tasa de interés 3.875%/12, 360 meses y un préstamo de $225.000, y calcula una $1.058,03 de pago mensual.

¿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.

×