Tabla de datos en Excel: Crear tablas de datos de una y dos variables

Cuando tienes una fórmula compleja que depende de múltiples variables y quieres entender cómo los cambios en esas entradas afectan los resultados de manera eficiente, la tabla de datos de análisis ¿Qué pasaría si... en Excel es una herramienta poderosa. Te permite ver todos los posibles resultados con un vistazo rápido. Aquí tienes una guía paso a paso sobre cómo crear una tabla de datos en Excel. Además, discutiremos algunos puntos clave para usar las tablas de datos de manera efectiva y demostraremos otras operaciones como eliminar, editar y recalcular tablas de datos.

¿Qué es una tabla de datos en Excel?

Crear una tabla de datos de una variable

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

Crear una tabla de datos de dos variables

Puntos clave al usar las tablas de datos

Otras operaciones para usar tablas de datos

• Eliminar una tabla de datos
• Editar el resultado de la tabla de datos
• Recalcular manualmente la 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 ¿Qué pasaría si... que te permite experimentar con diferentes valores de entrada para fórmulas y observar los cambios en la salida de la fórmula. Esta herramienta es increíblemente útil para explorar varios escenarios y realizar análisis de sensibilidad, especialmente cuando una fórmula depende de múltiples variables.

Existen dos tipos de tablas de datos en Excel:

Tabla de datos de una variable: Esto te permite analizar cómo diferentes valores de una variable impactan en una fórmula. Puedes configurar una tabla de datos de una variable en formato orientado a filas o columnas, dependiendo de si deseas variar tu entrada a lo largo de una fila o hacia abajo en una columna.

Tabla de datos de dos variables: Este tipo te permite ver el impacto de cambiar dos variables diferentes en el resultado de una fórmula. En una tabla de datos de dos variables, varías los valores tanto a lo largo de una fila como de una columna.

Crear una tabla de datos de una variable

Crear una tabla de datos de una variable en Excel es una habilidad valiosa para analizar cómo los cambios en una sola entrada pueden impactar en varios resultados. Esta sección te guiará a través del proceso de creación de tablas de datos orientadas a columnas, filas y múltiples fórmulas de una variable.

¡Increíble! ¡Usa pestañas eficientes en Excel como en Chrome, Edge, Firefox y Safari!
¡Ahorra el 50% de tu tiempo y reduce miles de clics de ratón cada día!

---

Tabla de datos orientada a columnas

Una tabla de datos orientada a columnas es útil cuando deseas probar cómo diferentes valores de una sola variable impactan en la salida de una fórmula, con los valores de la variable listados hacia abajo en una columna. Consideremos un ejemplo financiero simple:

Supongamos que estás considerando un préstamo de $50,000, que planeas pagar durante un período de 3 años (equivalente a 36 meses). Para evaluar qué pago mensual sería asequible según tu salario, estás interesado en explorar cómo varias tasas de interés afectarían la cantidad que necesitas pagar cada mes.

Paso 1: Configura la fórmula base

Para calcular el pago, aquí estableceré el interés en 5%. En una celda B4, ingresa la fórmula PMT, que calcula el pago mensual basado en la tasa de interés, el número de períodos y el monto del préstamo. Ver captura de pantalla:

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

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

En una columna, lista las diferentes tasas de interés que deseas probar. Por ejemplo, enumera valores desde el 4% hasta el 11% en incrementos del 1% en una columna, y deja al menos una columna en blanco a la derecha para los resultados. Ver captura de pantalla:

Paso 3: Crea la tabla de datos

1. En la celda E2, introduce esta fórmula: =B4.
   
   Nota: B4 es la celda donde está ubicada tu fórmula principal; esta es la fórmula cuyos resultados deseas ver cambiar a medida que varías la tasa de interés.
   
2. Selecciona el rango que incluye tu fórmula, la lista de tasas de interés y las celdas adyacentes para los resultados (por ejemplo, selecciona D2 a E10). Ver captura de pantalla:
   
3. Ve a la barra de herramientas, haz clic en la pestaña Datos , luego haz clic en Análisis Y si... > Tabla de datos, ver captura de pantalla:
   Tabla de datos"/>
4. En el cuadro de diálogo Tabla de datos , haz clic en el cuadro Celda de entrada de columna (ya que estamos utilizando una columna para los valores de entrada), y selecciona la celda variable referenciada en tu fórmula. En este ejemplo, seleccionamos B1 que contiene la tasa de interés. Finalmente, haz clic en Aceptar Ver captura de pantalla:
   
5. Excel llenará automáticamente las celdas vacías adyacentes a cada uno de tus valores de variable (diferentes tasas de interés) con los resultados correspondientes. Ver captura de pantalla:
   
6. Aplica el formato numérico deseado a los resultados (Moneda) según sea necesario. Ahora, la tabla de datos orientada a columnas ha sido creada exitosamente, y puedes revisar rápidamente los resultados para evaluar qué montos de pagos mensuales serían asequibles para ti cuando cambie la tasa de interés. Ver captura de pantalla:
   

Tabla de datos orientada a filas

Crear una tabla de datos orientada a filas en Excel implica organizar tus datos de modo que los valores de las variables se enumeren a lo largo de una fila en lugar de hacia abajo en una columna. Tomando el ejemplo anterior como referencia, sigamos los pasos para completar la creación de una tabla de datos orientada a filas en Excel.

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

Coloca los valores de las variables (tasas de interés) en una fila, asegurándote de dejar al menos una columna vacía a la izquierda para la fórmula y una fila vacía debajo para los resultados. Ver captura de pantalla:

Paso 2: Crear la tabla de datos

1. En la celda A9, introduce esta fórmula: =B4.
   
2. Selecciona el rango que incluye tu fórmula, la lista de tasas de interés y las celdas adyacentes para los resultados (por ejemplo, selecciona A8 a I9). Luego, haz clic en Datos > Análisis Y si... > Tabla de datos.
3. En el cuadro de diálogo Tabla de datos , haz clic en el cuadro Celda de entrada de fila (ya que estamos utilizando una fila para los valores de entrada), y selecciona la celda variable referenciada en tu fórmula. En este ejemplo, seleccionamos B1 que contiene la tasa de interés. Finalmente, haz clic en Aceptar Ver captura de pantalla:
   
4. Aplica el formato numérico deseado a los resultados (Moneda) según sea necesario. Ahora, la tabla de datos orientada a filas ha sido creada. Ver captura de pantalla:
   

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

Crear una tabla de datos de una variable con múltiples fórmulas en Excel te permite ver cómo el cambio de una sola entrada afecta varias fórmulas diferentes a la vez. En el ejemplo anterior, ¿qué pasa si quieres ver el cambio en las tasas de interés tanto en los pagos como en el interés total? Aquí está cómo configurarlo:

Paso 1: Agregar una nueva fórmula para calcular el interés total

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

=B4*B2*12-B3

Paso 2: Organiza los datos fuente de la tabla de datos

En una columna, lista las diferentes tasas de interés que deseas probar, y deja al menos dos columnas en blanco a la derecha para los resultados. Ver captura de pantalla:

Paso 3: Crea la tabla de datos:

1. En la celda E2, introduce esta fórmula: =B4 para crear una referencia al cálculo de los pagos en los datos originales.
2. En la celda F2, introduce esta fórmula: =B5 para crear una referencia al interés total en los datos originales.
   
3. Selecciona el rango que incluye tus fórmulas, la lista de tasas de interés y las celdas adyacentes para los resultados (por ejemplo, selecciona D2 a F10). Luego, haz clic en Datos > Análisis Y si... > Tabla de datos.
4. En el cuadro de diálogo Tabla de datos , haz clic en el cuadro Celda de entrada de columna (ya que estamos utilizando una columna para los valores de entrada), y selecciona la celda variable referenciada en tu fórmula. En este ejemplo, seleccionamos B1 que contiene la tasa de interés. Finalmente, haz clic en Aceptar Ver captura de pantalla:
   
5. Aplica el formato numérico deseado a los resultados (Moneda) según sea necesario. Y puedes ver los resultados para cada fórmula basada en los valores variables cambiantes.
   

Descubre la Magia de Excel con Kutools AI

• Ejecución Inteligente: Realiza operaciones en celdas, analiza datos y crea gráficos, todo impulsado por comandos simples.
• Fórmulas Personalizadas: Genera fórmulas adaptadas para optimizar tus flujos de trabajo.
• Codificación VBA: Escribe e implementa código VBA sin esfuerzo.
• Interpretación de Fórmulas: Comprende fórmulas complejas con facilidad.
• Traducción de Texto: Supera las barreras del idioma dentro de tus hojas de cálculo.

Potencia tus capacidades de Excel con herramientas impulsadas por IA. ¡Descarga Ahora y experimenta una eficiencia como nunca antes!

Crear una tabla de datos de dos variables

Una tabla de datos de dos variables en Excel muestra el impacto de diferentes combinaciones de dos conjuntos de valores variables en el resultado de una fórmula, demostrando cómo las alteraciones en dos entradas de una fórmula afectan simultáneamente su resultado.

Aquí, he dibujado un boceto simple para ayudarte a comprender mejor la apariencia y estructura de una tabla de datos de dos variables.

Basándonos en el ejemplo para crear una tabla de datos de una variable, ahora continuemos aprendiendo cómo crear una tabla de datos de dos variables en Excel.

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

Paso 1: Configura las dos variables cambiantes

1. En una columna, lista las diferentes tasas de interés que deseas probar. Ver captura de pantalla:
   
2. En una fila, introduce los diferentes valores del monto del préstamo justo encima de los valores de la columna (comenzando desde una celda a la derecha de la celda de la fórmula), ver captura de pantalla:
   

Paso 2: Crea la tabla de datos

1. Coloca tu fórmula en la intersección de la fila y la columna donde enumeraste los valores de las variables. En este caso, introduciré esta fórmula: =B4 en la celda E2. Ver captura de pantalla:
   
2. Selecciona el rango que incluye tus montos de préstamos, tasas de interés, la celda de la fórmula y las celdas donde se mostrarán los resultados.
   
3. Luego, haz clic en Datos > Análisis Y si... > Tabla de datos. En el cuadro de diálogo Tabla de datos:
   • En el cuadro Celda de entrada de fila, selecciona la referencia de celda a la celda de entrada para los valores de la variable en la fila (en este ejemplo, B3 contiene el monto del préstamo).
   • En el cuadro Celda de entrada de columna, selecciona la referencia de celda a la celda de entrada para los valores de la variable en la columna (B1 contiene la tasa de interés).
   • Y luego, haz clic en Aceptar Botón.
     
4. Ahora, Excel llenará la tabla de datos con los resultados para cada combinación de monto de préstamo y tasa de interés. Proporciona una vista directa de cómo diferentes combinaciones de montos de préstamos y tasas de interés afectan el pago mensual, convirtiéndola en una herramienta valiosa para la planificación y análisis financieros.
   
5. Finalmente, deberías aplicar el formato numérico deseado a los resultados (Moneda) según sea necesario.
   

Puntos clave al usar las tablas de datos

• La tabla de datos recién creada debe estar en la misma hoja de trabajo que los datos originales.
• La salida de la tabla de datos depende de la celda de la fórmula en el conjunto de datos original, y cualquier cambio en esta celda de la fórmula actualizará automáticamente la salida.
• Una vez que se han calculado los valores usando la tabla de datos, no se pueden deshacer con Ctrl + Z. Sin embargo, puedes seleccionar manualmente todos los valores y eliminarlos.
• La tabla de datos genera fórmulas de matriz, por lo que no se pueden modificar ni eliminar celdas individuales dentro de la tabla.

Otras operaciones para usar tablas de datos

Una vez que has creado una tabla de datos, podrías necesitar operaciones adicionales para gestionarla de manera efectiva, incluyendo eliminar la tabla de datos, modificar sus resultados y realizar recalculaciones manuales.

Eliminar una tabla de datos

Debido a que los resultados de la tabla de datos se calculan con una fórmula de matriz, no puedes eliminar un valor individual de la tabla de datos. Sin embargo, solo puedes eliminar toda la tabla de datos.

Selecciona todas las celdas de la tabla de datos o solo las celdas con los resultados, y luego presiona la tecla Supr en el teclado.

Editar el resultado de la tabla de datos

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

Para hacer cambios, normalmente necesitarías eliminar la tabla de datos existente y luego crear una nueva con las modificaciones deseadas. Esto implica ajustar tu fórmula base o los valores de entrada y luego configurar nuevamente la tabla de datos para reflejar estos cambios.

Recalcular manualmente la tabla de datos

Normalmente, Excel recalcula todas las fórmulas en todos los libros abiertos cada vez que se realiza un cambio. Si una gran tabla de datos que contiene numerosos valores variables y fórmulas complejas está causando que tu libro de Excel se ralentice.

Para evitar que Excel realice automáticamente cálculos para todas las tablas de datos cada vez que se realiza un cambio en el libro, puedes cambiar el modo de cálculo de Automático a Manual. Por favor, sigue estos pasos:

Ve a la pestaña Fórmulas y luego haz clic en Opciones de cálculo > Automático excepto para tablas de datos, ver captura de pantalla:

Una vez que modifiques esta configuración, cuando recalculas todo tu libro, Excel ya no actualizará automáticamente los cálculos en tus tablas de datos.

Si necesitas actualizar manualmente tu tabla de datos, simplemente selecciona las celdas donde se muestran los resultados (celdas que contienen las fórmulas TABLA()) y luego presiona la tecla F9.

Siguiendo los pasos descritos en esta guía y teniendo en cuenta los puntos clave, puedes utilizar eficientemente las tablas de datos para tus necesidades de análisis de datos. Si estás interesado en explorar más consejos y trucos de Excel, nuestro sitio web ofrece miles de tutoriales, por favor haz clic aquí para acceder a ellos. ¡Gracias por leer, y esperamos proporcionarte más información útil en el futuro!