Tabla de datos en Excel: cree tablas de datos con una y dos variables

Cuando tenga una fórmula compleja que dependa de varias variables y desee comprender de forma eficaz cómo los cambios en esas entradas afectan a los resultados, una tabla de datos del análisis de hipótesis en Excel es una herramienta extremadamente útil. Le permite visualizar todos los posibles resultados de un solo vistazo. A continuación, le ofrecemos una guía paso a paso para crear una tabla de datos en Excel, junto con algunos puntos clave para utilizarla de manera eficaz, así como otras operaciones prácticas, como eliminar, editar y recalcular tablas de datos.

¿Qué es una tabla de datos en Excel?

Crear una tabla de datos con una variable

• Tabla de datos orientada a columnas
• Tabla de datos orientada a filas
• Múltiples fórmulas para una tabla de datos con una variable

Crear una tabla de datos con dos variables

Puntos clave al utilizar tablas de datos

Otras operaciones al utilizar tablas de datos

• Eliminar una tabla de datos
• Editar el resultado de una tabla de datos
• Recalcular manualmente una tabla de datos

¿Qué es una tabla de datos en Excel?

En Excel, una tabla de datos es una de las herramientas de Análisis de hipótesis que le permite probar distintos valores de entrada en sus fórmulas y observar de inmediato cómo varían los resultados. Esta herramienta es especialmente útil para explorar múltiples escenarios y realizar análisis de sensibilidad, sobre todo cuando una fórmula depende de varias variables.

Existen dos tipos de tablas de datos en Excel:

Tabla de datos de una variable: Le permite analizar cómo un valor diferente de una única variable afecta a una fórmula. Puede configurar una tabla de datos de una variable en formato orientado a filas u orientado a columnas, según si desea variar la entrada a lo largo de una fila o de una columna.

Tabla de datos de dos variables: Este tipo le permite ver el impacto que tienen dos variables distintas sobre el resultado de una fórmula. En una tabla de datos de dos variables, los valores se modifican tanto en una fila como en una columna.

Crear una tabla de datos con una variable

Crear una tabla de datos de una variable en Excel es una habilidad valiosa para analizar cómo los cambios en una única entrada afectan a distintos resultados. Esta sección le guiará paso a paso en la creación de tablas de datos orientadas a columnas, orientadas a filas y con múltiples fórmulas.

¡Asombroso! Use Tabs eficiente en Excel como Chrome, Edge, Firefox y Safari.
Ahorre 50 % de su tiempo y reduzca miles de clics del ratón cada día.

---

Tabla de datos orientada a columnas

Una tabla de datos orientada a columnas resulta útil cuando desea probar cómo distintos valores de una única variable afectan al resultado de una fórmula, con los valores de la variable dispuestos en una columna. Veamos un ejemplo financiero sencillo:

Supongamos que está considerando un préstamo de $50,000 que piensa devolver en un plazo de 3 años (equivalente a 36 meses). Para evaluar qué pago mensual sería asequible en función de su salario, le interesa explorar cómo afectarían distintas tasas de interés al importe que tendría que pagar cada mes.

Paso 1: configure la fórmula base

Para calcular el pago, en este caso estableceré el interés en el 5 %. En la celda B4, introduzca la fórmula PAGO, que calcula el pago mensual a partir de la tasa de interés, el número de periodos y el importe del préstamo. Consulte la captura de pantalla:

= -PMT($B$1/12, $B$2*12, $B$3)

Paso 2: enumere las tasas de interés en una columna

En una columna, enumere las distintas tasas de interés que desea probar. Por ejemplo, incluya valores del 4 % al 11 % en incrementos del 1 %, y deje al menos una columna en blanco a la derecha para los resultados. Consulte la captura de pantalla:

Paso 3: cree la tabla de datos

1. En la celda E2, introduzca esta fórmula:=B4.
   
   Nota: B4 es la celda donde se encuentra su fórmula principal; esta es la fórmula cuyos resultados desea ver cambiar al variar el tipo de interés.
   
2. Seleccione el rango que incluya su fórmula, la lista de tipos de interés y las celdas adyacentes destinadas a los resultados (por ejemplo, desde D2 hasta E10). Consulte la captura de pantalla:
   
3. Vaya a la Cinta de opciones, haga clic en la pestaña Datos, y después haga clic en Análisis de hipótesis>Tabla de datos, consulte la captura de pantalla:
   Tabla de datos"/>
4. En el cuadro de diálogo Tabla de datos, haga clic en el campo Celda de entrada de columna (ya que estamos utilizando una columna para los valores de entrada) y seleccione la celda variable a la que hace referencia su fórmula. En este ejemplo, seleccionamos la celda B1, que contiene la tasa de interés. Por último, haga clic en el botón Aceptar. Consulte la captura de pantalla:
   
5. Excel rellenará automáticamente las celdas vacías adyacentes a cada uno de sus valores variables (diferentes tasas de interés) con los resultados correspondientes. Consulte la captura de pantalla:
   
6. Aplique el formato numérico que desee a los resultados (por ejemplo, moneda) según sus necesidades. Ahora se ha creado correctamente la tabla de datos orientada a columnas, lo que le permitirá revisar rápidamente los resultados y evaluar qué importes de pago mensual resultarían asequibles al variar la tasa de interés. Consulte la captura de pantalla:
   

Tabla de datos orientada a filas

Crear una tabla de datos orientada por filas en Excel consiste en organizar los datos de forma que los valores de las variables aparezcan en una fila, en lugar de en una columna. Tomando el ejemplo anterior como referencia, sigamos los pasos para completar la creación de una tabla de datos orientada por filas en Excel.

Paso 1: Liste las tasas de interés en una fila

Coloque los valores de la variable (tasas de interés) en una fila, asegurándose de dejar al menos una columna vacía a la izquierda para la fórmula y una fila vacía debajo para los resultados; consulte la captura de pantalla:

Paso 2: Cree la tabla de datos

1. En la celda A9, introduce esta fórmula:=B4.
   
2. Seleccione el rango que incluye su fórmula, la lista de tasas de interés y las celdas adyacentes para los resultados (por ejemplo, desde A8 hasta I9). A continuación, haga clic en Datos > Análisis de hipótesis > Tabla de datos.
3. En el cuadro de diálogo Tabla de datos, haga clic en el campo Celda de entrada de fila (ya que estamos utilizando una fila para los valores de entrada) y seleccione la celda variable a la que hace referencia su fórmula. En este ejemplo, seleccionamos B1, que contiene la tasa de interés. Por último, haga clic en el botón Aceptar. Consulte la captura de pantalla:
   
4. Aplique el formato numérico que desee a los resultados (por ejemplo, moneda) según sus necesidades. Ya se ha creado la tabla de datos orientada a filas. Consulte la captura de pantalla:
   

Múltiples fórmulas para una tabla de datos con una variable

Crear una tabla de datos con una variable y múltiples fórmulas en Excel le permite ver cómo un único valor de entrada afecta simultáneamente a varias fórmulas distintas. En el ejemplo anterior, ¿qué ocurre si desea observar el efecto de los cambios en las tasas de interés tanto en los pagos mensuales como en el interés total? A continuación se explica cómo configurarlo:

Paso 1: Añada una nueva fórmula para calcular el interés total

En la celda B5, introduzca la siguiente fórmula para calcular el interés total:

=B4*B2*12-B3

Paso 2: Organice la Datos de origen de la tabla de datos

En una columna, enumere las distintas tasas de interés que desee probar y deje al menos dos columnas en blanco a la derecha para los resultados. Consulte la captura de pantalla:

Paso 3: Cree la tabla de datos:

1. En la celda E2, introduzca esta fórmula: =B4 para crear una referencia al cálculo del pago en los datos originales.
2. En la celda F2, introduzca esta fórmula: =B5 para crear una referencia al interés total en los datos originales.
   
3. Seleccione el rango que incluya sus fórmulas, la lista de tasas de interés y las celdas adyacentes para los resultados (por ejemplo, desde D2 hasta F10). A continuación, haga clic en Datos > Análisis de hipótesis > Tabla de datos.
4. En el cuadro de diálogo Tabla de datos, haga clic en el campo Celda de entrada de columna (ya que estamos utilizando una columna para los valores de entrada) y seleccione la celda variable a la que hace referencia su fórmula. En este ejemplo, seleccionamos B1, que contiene la tasa de interés. Por último, haga clic en el botón Aceptar. Consulte la captura de pantalla:
   
5. Aplique el formato numérico que desee (por ejemplo, moneda) a los resultados según sus necesidades, y podrá ver cómo varían los resultados de cada fórmula en función de los distintos valores de la variable.
   

Descubra la magia de Excel con KUTOOLS AI

• Ejecución inteligente: Realice operaciones en celdas, analice datos y cree gráficos con comandos sencillos.
• fórmulas personalizadas: Cree fórmulas personalizadas para agilizar sus flujos de trabajo.
• Programación VBA: Escriba e implemente código VBA sin esfuerzo.
• Interpretación de fórmulas: Entienda las fórmulas complejas con facilidad.
• Traducción de texto: Rompa las barreras lingüísticas directamente en sus hojas de cálculo.

Potencie sus capacidades en Excel con herramientas impulsadas por inteligencia artificial.Descárguelo ahora¡y experimente una eficiencia sin precedentes!

Crear una tabla de datos con dos variables

Una tabla de datos con dos variables en Excel muestra el impacto que tienen distintas combinaciones de dos conjuntos de valores variables sobre el resultado de una fórmula, ilustrando claramente cómo las alteraciones simultáneas en ambas entradas influyen en dicho resultado.

Aquí he preparado un sencillo esquema para ayudarle a entender mejor el aspecto y la estructura de una tabla de datos con dos variables.

Partiendo del ejemplo para crear una tabla de datos con una variable, ahora descubriremos cómo crear una tabla de datos con dos variables en Excel.

En el conjunto de datos siguiente disponemos de la tasa de interés, el plazo del préstamo y el importe del préstamo, y hemos calculado el pago mensual mediante esta fórmula:=-PMT($B$1/12; $B$2*12; $B$3). Aquí nos centraremos en dos variables clave de nuestros datos: la tasa de interés y el importe del préstamo, para observar cómo los cambios simultáneos en ambos factores afectan al importe de los pagos.

Paso 1: Configure las dos variables que cambiarán

1. En una columna, enumere las distintas tasas de interés que desee probar. Consulte la captura de pantalla:
   
2. En una fila, introduzca los distintos importes del préstamo justo encima de los valores de la columna (comenzando en la celda inmediatamente a la derecha de la celda que contiene la fórmula). Consulte la captura de pantalla:
   

Paso 2: Cree la tabla de datos

1. Coloque su fórmula en la intersección de la fila y la columna donde haya enumerado los valores variables. En este caso, introduciré la siguiente fórmula:=B4 en la celda E2. Consulte la captura de pantalla:
   
2. Seleccione el rango que incluya los importes del préstamo, las tasas de interés, la celda con la fórmula y las celdas donde se mostrarán los resultados.
   
3. A continuación, haga clic en Datos > Análisis de hipótesis > Tabla de datos. En el cuadro de diálogo Tabla de datos:
   • En el cuadro Celda de entrada de fila, seleccione la referencia a la celda que contiene los valores variables de la fila (en este ejemplo, B3 contiene el importe del préstamo).
   • En el cuadro Celda de entrada de columna, seleccione la referencia de celda correspondiente a la celda de entrada para los valores variables de la columna (B1 contiene el tipo de interés).
   • A continuación, haz clic en el botón Aceptar.
     
4. Ahora, Excel rellenará la tabla de datos con los resultados correspondientes a cada combinación de importe del préstamo y tasa de interés, ofreciendo una visión clara de cómo estas variables influyen en el pago mensual y convirtiéndola en una herramienta esencial para la planificación y el análisis financieros.
   
5. Por último, aplique el formato numérico deseado (por ejemplo, moneda) a los resultados según sus necesidades.
   

Puntos clave al utilizar tablas de datos

• La tabla de datos recién creada debe estar en la misma hoja de cálculo que los datos originales.
• El resultado de la tabla de datos depende de la celda de fórmula del conjunto Datos de origen, y cualquier cambio en dicha celda actualizará automáticamente el resultado.
• Una vez calculados los valores mediante la tabla de datos, no se pueden deshacer con Ctrl + Z. No obstante, puede seleccionar manualmente todos los valores y eliminarlos.
• La tabla de datos genera fórmulas matriciales, por lo que no es posible modificar ni eliminar celdas individuales dentro de ella.

Otras operaciones al utilizar tablas de datos

Una vez creada una tabla de datos, es posible que necesite realizar operaciones adicionales para gestionarla de forma eficaz, como eliminarla, modificar sus resultados o recalcularla manualmente.

Eliminar una tabla de datos

Dado que los resultados de la tabla de datos se calculan mediante una fórmula matricial, no puede eliminar un valor individual de la tabla. Solo puede eliminar toda la tabla completa.

Seleccione todas las celdas de la tabla de datos o solo aquellas que contengan resultados y, a continuación, pulse la tecla Supr del teclado.

Editar el resultado de una tabla de datos

De hecho, no es posible editar directamente celdas individuales dentro de una tabla de datos, ya que estas contienen fórmulas matriciales generadas automáticamente por Excel.

Para realizar cambios, normalmente deberá eliminar la tabla de datos existente y crear una nueva con las modificaciones deseadas, ajustando primero su fórmula base o los valores de entrada y, posteriormente, volviendo a configurar la tabla de datos para reflejar dichos cambios.

Recalcular manualmente una tabla de datos

Normalmente, Excel recalcula todas las fórmulas de todos los libros abiertos cada vez que se realiza un cambio. Si una tabla de datos grande, que contiene numerosos valores variables y fórmulas complejas, está ralentizando su Libro de Excel.

Para evitar que Excel realice automáticamente cálculos en todas las tablas de datos cada vez que se modifica el libro, puede cambiar el modo de cálculo de Automático a Manual. Proceda de la siguiente manera:

Vaya a la pestaña Fórmulasy, a continuación, haga clic en Opciones de cálculo>Automático excepto en tablas de datos, consulte la captura de pantalla:

Una vez modificada esta configuración, cuando recalcule todo el libro, Excel ya no actualizará automáticamente los cálculos de sus tablas de datos.

Si necesita actualizar manualmente su tabla de datos, seleccione las celdas donde se muestran los resultados (aquellas que contienen las fórmulas)TABLA()) y pulse la tecla F9.

Siguiendo los pasos descritos en esta guía y teniendo en cuenta los puntos clave, podrá utilizar las tablas de datos de forma eficaz para sus necesidades en Análisis de datos. Si desea descubrir más consejos y trucos de Excel, nuestro sitio web ofrece miles de tutoriales;haga clic aquí para acceder a ellos. ¡Gracias por leer y esperamos seguir ofreciéndole información útil en el futuro!